Administration
Archivelogs in the flash recovery area
User Rating: / 4
- Details
-
Parent Category: Articles
-
Created on Sunday, 05 July 2009 16:36
-
Last Updated on Tuesday, 10 December 2019 14:29
-
Published on Sunday, 05 July 2009 16:36
-
Written by Guy Lambregts
-
Hits: 29695
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