17 | 12 | 2017
Latest Articles
Popular Articles

Administration

Archivelogs in the flash recovery area

User Rating:  / 4
PoorBest 

Archivelogs in the flash recovery area

 

See also Which session is generating the redo ? 

 

Any database with a zero dataloss ambition should operate in archivelog mode

1) always

2) without exception

The overhead associated with archivelog mode is limited. The background process PMON automatically spawns additional archivelog processes up to a maximum configured with the instance initialization parameter log_archive_max_processes

 

SQL> show parameter log_archive_max

NAME TYPE VALUE


log_archive_max_processes integer 2.

 

A backup strategy backing up the archivelogs at a regular time interval is the recommendation. When there is a redo log switch an additional archivelog will be generated in the archive log destination. It is a well known possible issue that a database instance can hang when the archivelog destination is under space pressure. More precisely this error occurs whenever the archivelog destination is full

 

ORA-00257: archiver error. Connect internal only, until freed.

 

 

Prior to Oracle 10G this error mean there was not any more disk space left because the disk was physically full. From Oracle 10G R1 onwards a DBA can configure the flash recovery area as the archive destination. This is done with a somewhat particular configuration of one of the log archive destinations.

 

SQL> show parameter log_archive_dest_1
 
 
 
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
 
 
log_archive_dest_1  string
log_archive_dest_10 string LOCATION=USE_DB_RECOVERY_FILE_DEST

 

When the flash recovery area is used for the archivelog destination the ORA-00257: archiver error can occor

1. when there is no more space left because the disk hosting the flash recovery area is physically full.

2. when there is no more space left because the used space in the flash recovery area is at 100%. We have met a logical threshold.

With the below real life example I show how we can troubleshoot and resolve flash recovery area logically fullness

Here I show the ASM diskgroup DG1 is used for the flash recovery area

 

SQL> show parameter db_recovery_
 
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
 
db_recovery_file_dest                string      +DG1
db_recovery_file_dest_size                      big integer  3G

 

Here we show the archived redo logs are indeed created into the flash recovery area.

 

SQL> show parameter log_archive_dest_1

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

log_archive_dest_1 string log_archive_dest_10 string

LOCATION=USE_DB_RECOVERY_FILE_DEST

 

We check whether the ASM diskgroup where the archivelogs are created - in this case DG1 - is full

 

$ export ORACLE_SID=+ASM $
$ export ORACLE_HOME=$ASM_HOME
$ asmcmd lsdg
 
 
State   Type Rebal Unbal Sector Block      AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Name
 
 
MOUNTED EXTERN   N     N   1024  4096 1048576    15000    9927               0           9927             0 DG1/
MOUNTED EXTERN   N     N   1024  4096 1048576    40000    7870               0           7870             0 DG2/

 

The ASM diskgroup where the archivelogs are created - in this case DG1 - is apparently not full But the archivelogs are created into the flash recovery area. With the instance initialization parameter db_recovery_file_dest_size we can (re)size the flash_recovery_area.(logically) It' s time to take to take look. Note we use the lovely dynamic performance view v$flash_recovery_area_usage.

 

$ export ORACLE_SID=MYSID

$ . ./.profile

 

Hence we notice the flash recovery area is loically used for 100%.


$ sqlplus / as sysdba
 
 
SQL> select * from v$flash_recovery_area_usage;
 
 
FILE_TYPE    PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------ ------------------ ------------------------- ---------------
 
CONTROLFILE                 .49                         0               1
ONLINELOG                     0                         0               0  
ARCHIVELOG                97.53                         0              35
BACKUPPIECE                1.95                         0               4
IMAGECOPY                     0                         0               0
FLASHBACKLOG                  0                         0               0
 
6 rows selected.

 

So the flash recovery area is full and the vast majority is used by archived logs. however there is apparently still disk space left. Let us double the size of the flash recovery area

 

SQL> alter system set db_recovery_file_dest_size=6G scope=both;
 
System altered.

 

We notice the impact

 

SQL> select * from v$flash_recovery_area_usage;
 
 
FILE_TYPE    PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------ ------------------ ------------------------- ---------------
 
CONTROLFILE                 .24                         0               1
ONLINELOG                     0                         0               0  
ARCHIVELOG                55.94                         0              40
BACKUPPIECE                 .98                         0               4
IMAGECOPY                     0                         0               0
FLASHBACKLOG                  0                         0               0
6 rows selected.

 

As said above a backup strategy backing up the archivelogs at a regular time interval is the recommendation. Once the archivelogs are backed up — assuming they are created in the flash recovery area — they will be automatically purged as soon as the flash recovery area becomes under space pressure. note there are some RMAN options for archivelog deletion, after you backed them up you can

 

1) have them deleted automatically

2) haven' t them deleted automatically

3) have them deleted as soon as the flash recovery becomes under space pressure assuming they are backed up

4) have them deleted as soon as the flash recovery becomes under space pressure assuming they are backed up AND as soon they have been applied on your standby database.

 

I can' t hide my appreciation for the wonderfull RMAN option "configure archivelog deletion to applied on standby" One of my favourite 10G features.

See also Manage archive logs using RMAN