The Recovery Area: Why it is recommended
With 10g, the concept of the Recovery Area – associated with the parameter DB_RECOVERY_FILE_DEST – was introduced. The picture below illustrates it:
The name Recovery Area indicates that this is the place to find everything needed to do a successful recovery in case of a damage (or a logical mistake) in the Database Area.
The Recovery Area was designed in the first place for customers who use Disk Storage as their primary backup media. Meanwhile, due to the ever decreasing price of Disk Storage, this should be the majority of customers. In spite of that, there seems to be some kind of reluctance to actually make use of the multiple benefits, the Recovery Area delivers.
This posting aims to show how deal with the Recovery Area in order to
a) Protect Controlfiles & Online Logfiles
b) Implement a robust Backup Strategy easily
c) Manage Flashback Logs
Let’s start with
a) How does the Recovery Area help to protect Controlfiles & Online Logfiles?
It should be consensus that the Controlfiles and especially the Online Logfiles are sensitive and must be mirrored. If we use the parameter DB_CREATE_FILE_DEST (introduced in 9i already) together with DB_RECOVERY_FILE_DEST, this is done automatically during the Database creation. It is typically but not necessarily so that we use DB_CREATE_FILE_DEST together with ASM. My demo Database is without ASM here:
[[email protected] ~]$ cat skripte/initprima.ora compatible=11.2.0.2 db_block_size=8192 db_name='prima' db_create_file_dest='/u01/app/oracle/oradata' db_recovery_file_dest='/u02/fra' db_recovery_file_dest_size=2g memory_target=640m undo_management=auto undo_tablespace=undotbs1 db_domain='' diagnostic_dest='/u01/app/oracle/diag'
That is my minimalistic init.ora. Notice the absence of the parameter CONTROL_FILES. Now look how the two parameters make it easy for me to create my Database with mirrored Control- and Online Logfiles:
[[email protected] ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.2.0 Production on Tue Aug 23 11:48:08 2011 Copyright (c) 1982, 2010, Oracle. All rights reserved. Connected to an idle instance. SQL> create spfile from pfile='/home/oracle/skripte/initprima.ora'; File created. SQL> startup nomount ORACLE instance started. Total System Global Area 668082176 bytes Fixed Size 2229480 bytes Variable Size 398461720 bytes Database Buffers 264241152 bytes Redo Buffers 3149824 bytes SQL> create database undo tablespace undotbs1 default tablespace users default temporary tablespace temp character set al32utf8; Database created.
That was it already and it took less than a minute. Some more minutes will it take to run catalog & catproc, though. Let’s investigate the new Database:
SQL> select status from v$instance; STATUS ------------ OPEN SQL> select name,bytes/1024/1024 as mb from v$datafile union select name,bytes/1024/1024 from v$tempfile; NAME MB ---------------------------------------------------------------------- ---------- /u01/app/oracle/oradata/PRIMA/datafile/o1_mf_sysaux_75d3os4b_.dbf 100 /u01/app/oracle/oradata/PRIMA/datafile/o1_mf_system_75d3on35_.dbf 100 /u01/app/oracle/oradata/PRIMA/datafile/o1_mf_temp_75d3oynt_.tmp 100 /u01/app/oracle/oradata/PRIMA/datafile/o1_mf_undotbs1_75d3oxw5_.dbf 10 /u01/app/oracle/oradata/PRIMA/datafile/o1_mf_users_75d3oyp3_.dbf 100
We see above the effect of specifying the Database Area with DB_CREATE_FILE_DEST: The system created a subdirectory according to the DB_UNIQUE_NAME (derived from DB_NAME in this case) as well as another subdirectory beneath according to the file type.
Now to the mirroring of our most sensitive files:
SQL> select name,is_recovery_dest_file from v$controlfile; NAME IS_ ---------------------------------------------------------------------- --- /u01/app/oracle/oradata/PRIMA/controlfile/o1_mf_756xwk4p_.ctl NO /u02/fra/PRIMA/controlfile/o1_mf_756xwk70_.ctl YES SQL> select member,is_recovery_dest_file from v$logfile; MEMBER IS_ ---------------------------------------------------------------------- --- /u01/app/oracle/oradata/PRIMA/onlinelog/o1_mf_1_756xwk95_.log NO /u02/fra/PRIMA/onlinelog/o1_mf_1_756xwkfr_.log YES /u01/app/oracle/oradata/PRIMA/onlinelog/o1_mf_2_756xwkn5_.log NO /u02/fra/PRIMA/onlinelog/o1_mf_2_756xwl1t_.log YES
We see only with this approach a ‘YES’ in the IS_REVOVERY_DEST_FILE column of v$controlfile. If you specify the CONTROL_FILES parameter manually (with one file pointing to the Recovery Area) before the CREATE DATABASE command, this does not count. I admit that in earlier versions of 10g, the systems handling of the CONTROL_FILES parameter was not optimal, because it did not write the automatically created Controlfiles into the spfile. But the newer releases do:
SQL> select sid,name,value from v$spparameter where isspecified='TRUE'; SID NAME VALUE ------ ------------------------------ ---------------------------------------------------------------------- * processes 100 * memory_target 671088640 * control_files /u01/app/oracle/oradata/PRIMA/controlfile/o1_mf_756xwk4p_.ctl * control_files /u02/fra/PRIMA/controlfile/o1_mf_756xwk70_.ctl * db_block_size 8192 * compatible 11.2.0.2 * db_create_file_dest /u01/app/oracle/oradata * db_recovery_file_dest /u02/fra * db_recovery_file_dest_size 2147483648 * undo_management auto * undo_tablespace undotbs1 * db_domain * db_name prima * diagnostic_dest /u01/app/oracle/diag 14 rows selected.
A valid reason not to go with this method (mirroring Controlfiles & Online Logfiles into the Recovery Area) would be if the devices there a significantly slower than the ones that make up the Database Area. We may still use the Recovery Area to keep our Backups, Archivelogs & Flashback Logs there, though. Because the devices underneath /u01 and /u02 have similar performance characteristics, it is perfectly OK as it is now on my demo system. Apart from performance aspects, it is of course of key importance to have the Recovery Area placed on different physical devices than the Database Area.
We have two V$-Views that reflect the usage of the Recovery Area:
SQL> select * from V$RECOVERY_FILE_DEST; NAME SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES ------------------------------ ----------- ---------- ----------------- --------------- /u02/fra 2147483648 218316800 0 3
The SPACE* columns have the unit bytes, unfortunately. SPACE_LIMIT is derived from the dynamic parameter DB_RECOVERY_FILE_DEST_SIZE that was set here to the (very small) value 2G. SPACE_RECLAIMABLE is derived from the size of the Backups/Archivelogs, being obsolete according to our RMAN retention policy. The next view is newer and shows also the file types, consuming space in the Recover Area:
SQL> select * from V$RECOVERY_AREA_USAGE; FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES -------------------- ------------------ ------------------------- --------------- CONTROL FILE .36 0 1 REDO LOG 9.77 0 2 ARCHIVED LOG 0 0 0 BACKUP PIECE 0 0 0 IMAGE COPY 0 0 0 FLASHBACK LOG 0 0 0 FOREIGN ARCHIVED LOG 0 0 0 7 rows selected.
Because the Database is neither in Archivelog mode nor in Flashback mode yet and we did not take backups, the only files in the Recovery Area are the mirrors of the Online Logs and the Controlfile. We wil change that with the next step.
b) How does the Recovery Area help to implement a robust Backup Strategy?
We will put the Database in Archivelog mode and see that the Archivelogs are being created automatically into the Recovery Area:
SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount ORACLE instance started. Total System Global Area 668082176 bytes Fixed Size 2229480 bytes Variable Size 444599064 bytes Database Buffers 218103808 bytes Redo Buffers 3149824 bytes Database mounted. SQL> alter database archivelog; Database altered. SQL> alter database open; Database altered. SQL> alter system switch logfile; System altered. SQL> select name from v$archived_log; NAME ---------------------------------------------------------------------- /u02/fra/PRIMA/archivelog/2011_08_25/o1_mf_1_7_75d56p79_.arc 1 row selected. SQL> select * from v$recovery_area_usage; FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES -------------------- ------------------ ------------------------- --------------- CONTROL FILE .36 0 1 REDO LOG 9.77 0 2 ARCHIVED LOG 1.46 0 1 BACKUP PIECE 0 0 0 IMAGE COPY 0 0 0 FLASHBACK LOG 0 0 0 FOREIGN ARCHIVED LOG 0 0 0 7 rows selected.
Notice that I did not need to specify a single initialization parameter of the LOG_ARCHIVE* range for this; easy, isn’t it? Now we come to the almost only remaining difficulty: Specifying an appropriate value for DB_RECOVERY_FILE_DEST_SIZE. That parameter sets a logical space limit, our Database is allowed to consume inside of the Recovery Area. We must set it large enough for our Backups & Archivelogs to fit in, according to our retention policy. Let’s have a look how large our backups get for this Database:
SQL> select name,bytes/1024/1024 as mb from v$datafile union select name,bytes/1024/1024 from v$tempfile; NAME MB ---------------------------------------------------------------------- ---------- /u01/app/oracle/oradata/PRIMA/datafile/o1_mf_sysaux_75d3os4b_.dbf 100 /u01/app/oracle/oradata/PRIMA/datafile/o1_mf_system_75d3on35_.dbf 200 /u01/app/oracle/oradata/PRIMA/datafile/o1_mf_temp_75d3oynt_.tmp 100 /u01/app/oracle/oradata/PRIMA/datafile/o1_mf_undotbs1_75d3oxw5_.dbf 180 /u01/app/oracle/oradata/PRIMA/datafile/o1_mf_users_75d3oyp3_.dbf 100 5 rows selected. SQL> select sum(bytes)/1024/1024 as mb from v$datafile; MB ---------- 580 1 row selected.
After catalog & catproc did run, the files grew little but still this is a very tiny Database. With Image Copies, my full Backup would take 580 MB in size, while Backup Sets would be smaller, depending on the amount of empty space inside of the datafiles that Backup Sets would not contain. We are going to take an ordinary default online backup:
SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production [[email protected] ~]$ rman target / Recovery Manager: Release 11.2.0.2.0 - Production on Thu Aug 25 11:40:26 2011 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. connected to target database: PRIMA (DBID=1992392973) RMAN> show all; using target database control file instead of recovery catalog RMAN configuration parameters for database with db_unique_name PRIMA are: CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default CONFIGURE BACKUP OPTIMIZATION OFF; # default CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default CONFIGURE MAXSETSIZE TO UNLIMITED; # default CONFIGURE ENCRYPTION FOR DATABASE OFF; # default CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0.2/db_1/dbs/snapcf_prima.f'; # default RMAN> CONFIGURE CONTROLFILE AUTOBACKUP on; new RMAN configuration parameters: CONFIGURE CONTROLFILE AUTOBACKUP ON; new RMAN configuration parameters are successfully stored RMAN> backup database; Starting backup at 25-AUG-11 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=29 device type=DISK channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00001 name=/u01/app/oracle/oradata/PRIMA/datafile/o1_mf_system_75d3on35_.dbf input datafile file number=00003 name=/u01/app/oracle/oradata/PRIMA/datafile/o1_mf_undotbs1_75d3oxw5_.dbf input datafile file number=00002 name=/u01/app/oracle/oradata/PRIMA/datafile/o1_mf_sysaux_75d3os4b_.dbf input datafile file number=00004 name=/u01/app/oracle/oradata/PRIMA/datafile/o1_mf_users_75d3oyp3_.dbf channel ORA_DISK_1: starting piece 1 at 25-AUG-11 channel ORA_DISK_1: finished piece 1 at 25-AUG-11 piece handle=/u02/fra/PRIMA/backupset/2011_08_25/o1_mf_nnndf_TAG20110825T114118_75d65z8m_.bkp tag=TAG20110825T114118 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03 Finished backup at 25-AUG-11 Starting Control File and SPFILE Autobackup at 25-AUG-11 piece handle=/u02/fra/PRIMA/autobackup/2011_08_25/o1_mf_s_760102882_75d6633f_.bkp comment=NONE Finished Control File and SPFILE Autobackup at 25-AUG-11
We could see that the Backup Set as well as the Controlfile Autobackup (a must to switch on!) were taken into the Recovery Area. Now to our calculation about the Recovery Area sizing:
SQL> select * from v$recovery_area_usage; FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES -------------------- ------------------ ------------------------- --------------- CONTROL FILE .36 0 1 REDO LOG 9.77 0 2 ARCHIVED LOG 1.46 1.46 1 BACKUP PIECE 19.07 0 2 IMAGE COPY 0 0 0 FLASHBACK LOG 0 0 0 FOREIGN ARCHIVED LOG 0 0 0 7 rows selected. SQL> alter system set DB_RECOVERY_FILE_DEST_SIZE=1g; System altered. SQL> select * from v$recovery_area_usage; FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES -------------------- ------------------ ------------------------- --------------- CONTROL FILE .73 0 1 REDO LOG 19.53 0 2 ARCHIVED LOG 2.93 2.93 1 BACKUP PIECE 38.14 0 2 IMAGE COPY 0 0 0 FLASHBACK LOG 0 0 0 FOREIGN ARCHIVED LOG 0 0 0 7 rows selected.
With this (very small) value of DB_RECOVERY_FILE_DEST_SIZE, it should still be possible for RMAN to take another full backup into the Recovery Area – because only about 50 % of space is consumed by not obsolete files – after which the previous backup becomes obsolete, together with the Archivelogs, belonging to that backup. Our goal is to just take backups (every night, for example) and let the obsolete backups & archivelogs get deleted automatically. We must keep in mind that it is of key importance that archive logs can be created always to avoid archiver stuck problems, though. Let’s look at the desired behavior:
RMAN> backup database; Starting backup at 25-AUG-11 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=1 device type=DISK channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00001 name=/u01/app/oracle/oradata/PRIMA/datafile/o1_mf_system_75d3on35_.dbf input datafile file number=00003 name=/u01/app/oracle/oradata/PRIMA/datafile/o1_mf_undotbs1_75d3oxw5_.dbf input datafile file number=00002 name=/u01/app/oracle/oradata/PRIMA/datafile/o1_mf_sysaux_75d3os4b_.dbf input datafile file number=00004 name=/u01/app/oracle/oradata/PRIMA/datafile/o1_mf_users_75d3oyp3_.dbf channel ORA_DISK_1: starting piece 1 at 25-AUG-11 channel ORA_DISK_1: finished piece 1 at 25-AUG-11 piece handle=/u02/fra/PRIMA/backupset/2011_08_25/o1_mf_nnndf_TAG20110825T115312_75d6w8bd_.bkp tag=TAG20110825T115312 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03 Finished backup at 25-AUG-11 Starting Control File and SPFILE Autobackup at 25-AUG-11 piece handle=/u02/fra/PRIMA/autobackup/2011_08_25/o1_mf_s_760103595_75d6wcng_.bkp comment=NONE Finished Control File and SPFILE Autobackup at 25-AUG-11 RMAN> exit Recovery Manager complete. [[email protected] ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.2.0 Production on Thu Aug 25 11:53:23 2011 Copyright (c) 1982, 2010, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production SQL> set lines 200 SQL> select * from v$recovery_area_usage; FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES -------------------- ------------------ ------------------------- --------------- CONTROL FILE .73 0 1 REDO LOG 19.53 0 2 ARCHIVED LOG 0 0 0 BACKUP PIECE 38.89 .74 3 IMAGE COPY 0 0 0 FLASHBACK LOG 0 0 0 FOREIGN ARCHIVED LOG 0 0 0 7 rows selected.
We took another backup and the previous backup got obsolete. Automatically, obsolete files got deleted because of space pressure inside of the Recovery Area:
[[email protected] ~]$ adrci ADRCI: Release 11.2.0.2.0 - Production on Thu Aug 25 11:56:19 2011 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. ADR base = "/u01/app/oracle/diag" adrci> show alert -tail 2011-08-25 11:24:26.308000 +02:00 Starting background process CJQ0 CJQ0 started with pid=25, OS id=2495 2011-08-25 11:24:38.156000 +02:00 Thread 1 advanced to log sequence 8 (LGWR switch) Current log# 2 seq# 8 mem# 0: /u01/app/oracle/oradata/PRIMA/onlinelog/o1_mf_2_75d3og50_.log Current log# 2 seq# 8 mem# 1: /u02/fra/PRIMA/onlinelog/o1_mf_2_75d3ogms_.log Archived Log entry 1 added for thread 1 sequence 7 ID 0x76c1760d dest 1: 2011-08-25 11:34:24.262000 +02:00 Starting background process SMCO SMCO started with pid=23, OS id=2529 2011-08-25 11:39:10.821000 +02:00 Stopping background process CJQ0 2011-08-25 11:46:00.516000 +02:00 ALTER SYSTEM SET db_recovery_file_dest_size='1G' SCOPE=BOTH; 2011-08-25 11:53:14.365000 +02:00 Deleted Oracle managed file /u02/fra/PRIMA/archivelog/2011_08_25/o1_mf_1_7_75d56p79_.arc 2011-08-25 11:53:15.647000 +02:00 Deleted Oracle managed file /u02/fra/PRIMA/backupset/2011_08_25/o1_mf_nnndf_TAG20110825T114118_75d65z8m_.bkp
Although it worked well in this scenario, the logical limit is set a little too optimistic here; a peak in Archivelog creation could easily lead to Archiver Stuck problems. Therefore, we increase the Recovery Area to a safer size and look at the consequences of that:
SQL> alter system set db_recovery_file_dest_size=2g; System altered. RMAN> backup database; Starting backup at 25-AUG-11 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=30 device type=DISK channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00001 name=/u01/app/oracle/oradata/PRIMA/datafile/o1_mf_system_75d3on35_.dbf input datafile file number=00003 name=/u01/app/oracle/oradata/PRIMA/datafile/o1_mf_undotbs1_75d3oxw5_.dbf input datafile file number=00002 name=/u01/app/oracle/oradata/PRIMA/datafile/o1_mf_sysaux_75d3os4b_.dbf input datafile file number=00004 name=/u01/app/oracle/oradata/PRIMA/datafile/o1_mf_users_75d3oyp3_.dbf channel ORA_DISK_1: starting piece 1 at 25-AUG-11 channel ORA_DISK_1: finished piece 1 at 25-AUG-11 piece handle=/u02/fra/PRIMA/backupset/2011_08_25/o1_mf_nnndf_TAG20110825T120213_75d7f5wk_.bkp tag=TAG20110825T120213 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03 Finished backup at 25-AUG-11 Starting Control File and SPFILE Autobackup at 25-AUG-11 piece handle=/u02/fra/PRIMA/autobackup/2011_08_25/o1_mf_s_760104136_75d7f8wb_.bkp comment=NONE Finished Control File and SPFILE Autobackup at 25-AUG-11 RMAN> backup database; Starting backup at 25-AUG-11 using channel ORA_DISK_1 channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00001 name=/u01/app/oracle/oradata/PRIMA/datafile/o1_mf_system_75d3on35_.dbf input datafile file number=00003 name=/u01/app/oracle/oradata/PRIMA/datafile/o1_mf_undotbs1_75d3oxw5_.dbf input datafile file number=00002 name=/u01/app/oracle/oradata/PRIMA/datafile/o1_mf_sysaux_75d3os4b_.dbf input datafile file number=00004 name=/u01/app/oracle/oradata/PRIMA/datafile/o1_mf_users_75d3oyp3_.dbf channel ORA_DISK_1: starting piece 1 at 25-AUG-11 channel ORA_DISK_1: finished piece 1 at 25-AUG-11 piece handle=/u02/fra/PRIMA/backupset/2011_08_25/o1_mf_nnndf_TAG20110825T120231_75d7fqcq_.bkp tag=TAG20110825T120231 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03 Finished backup at 25-AUG-11 Starting Control File and SPFILE Autobackup at 25-AUG-11 piece handle=/u02/fra/PRIMA/autobackup/2011_08_25/o1_mf_s_760104154_75d7ftjw_.bkp comment=NONE Finished Control File and SPFILE Autobackup at 25-AUG-11 SQL> select * from v$recovery_area_usage; FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES -------------------- ------------------ ------------------------- --------------- CONTROL FILE .36 0 1 REDO LOG 9.77 0 2 ARCHIVED LOG 0 0 0 BACKUP PIECE 58.05 38.75 7 IMAGE COPY 0 0 0 FLASHBACK LOG 0 0 0 FOREIGN ARCHIVED LOG 0 0 0 7 rows selected.
After increasing the logical limit, we took two new backups. Although our retention policy is still redundancy 1, the accordingly obsolete backups did not get deleted yet, because there is no space pressure. Keep in mind that the system will consume almost the complete space that we allow with DB_RECOVERY_FILE_DEST_SIZE, therefore! The background of that is that we may make use of the old obsolete backups for a conventional Database Point In Time Recovery (DBPITR) without Flashback Logs. From now on, we will just take our (nightly) backups and let the system take care about obsolete backups & archivelogs. We should monitor V$RECOERY_AREA_USAGE or rely on an Enterprise Manager Alert that raises automatically if space in the Recovery Area gets scarce.
In other words: We just need to figure out once an appropriate logical limit and the system takes care of itself subsequently, while we just monitor it. This approach is much easier than other methods used before the introduction of the Recovery Area.
c) How to manage Flashback Logs together with the Recovery Area?
If we want to be able to do Flashback Database, we must have a Recovery Area to place the Flashback logs into:
SQL> alter database flashback on; Database altered. SQL> select flashback_on,open_mode from v$database; FLASHBACK_ON OPEN_MODE ------------------ -------------------- YES READ WRITE SQL> show parameter flashback_retention_target NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_flashback_retention_target integer 1440
From now on, we will be able to do a very fast DBPITR with Flashback Database, by default one day (1440 minutes) back into the past. Notice that this target is not mandatory and will not lead to a “Flashback Stuck” if space is consumed already by non obsolete backups & archivelogs. In order to show that, I will again reduce the logical limit to create a space pressure:
SQL> alter system set db_recovery_file_dest_size=700m; System altered. SQL> select * from v$recovery_area_usage; FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES -------------------- ------------------ ------------------------- --------------- CONTROL FILE 1.07 0 1 REDO LOG 28.57 0 2 ARCHIVED LOG 13.72 0 1 BACKUP PIECE 58.63 1.08 4 IMAGE COPY 0 0 0 FLASHBACK LOG 3.35 0 3 FOREIGN ARCHIVED LOG 0 0 0 7 rows selected. SQL> insert /*+ append */ into sales select * from sales; 3200000 rows created. SQL> commit; Commit complete.
The above insert would normally create Flashback Logs but it runs in spite of the full Recovery Area. There is no such thing as “Flashback Stuck” (unless you create a guaranteed restore point, that is). That scenario is somewhat artificial because the command above creates almost no Redo Protocol (the table sales is on NOLOGGING) and also this is the only activity on the system. With the Recovery Area 100% full, the Database would normally get to a hold immediately – not because of the impossibility to create Flashback Logs, but because of Archiver Stuck:
SQL> connect / as sysdba Connected. SQL> alter system switch logfile; System altered. SQL> r 1* alter system switch logfile Waits ... Second terminal: [[email protected] ~]$ adrci ADRCI: Release 11.2.0.2.0 - Production on Thu Aug 25 12:36:18 2011 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. ADR base = "/u01/app/oracle/diag" adrci> show alert -tail 2011-08-25 12:36:24.236000 +02:00 ************************************************************************ You have following choices to free up space from recovery area: 1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard, then consider changing RMAN ARCHIVELOG DELETION POLICY. 2. Back up files to tertiary device such as tape using RMAN BACKUP RECOVERY AREA command. 3. Add disk space and increase db_recovery_file_dest_size parameter to reflect the new space. 4. Delete unnecessary files using RMAN DELETE command. If an operating system command was used to delete files, then use RMAN CROSSCHECK and DELETE EXPIRED commands. ************************************************************************ ARC1: Error 19809 Creating archive log file to '/u02/fra/PRIMA/archivelog/2011_08_25/o1_mf_1_9_%u_.arc'
Exactly. LGWR cannot write into the Online Logs anymore because no more Archivelogs can be stored in the Recovery Area. Remedy:
adrci> exit [[email protected] ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.2.0 Production on Thu Aug 25 12:39:53 2011 Copyright (c) 1982, 2010, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production SQL> alter system set db_recovery_file_dest_size=2g; System altered.
That resolved the Archiver Stuck problem immediately.
In short: Although it is desireable to have enough space to store flashback logs, it is not mandatory. That is extremely different to archive logs. We should try to make our Recovery Area large enough to store our backups & archive logs according to our Backup Retention, and our Flashback Logs according to our Flashback Retention Target. The space required is partly depending on the Database Load. Therefore, it would be a good idea to start with a somewhat gracefully large DB_RECOVERY_FILE_DEST_SIZE and a relatively short DB_FLASHBACK_RETENTION_TARGET and then monitor V$RECOVERY_AREA_USAGE to come up with an appropriate value for the two parameters.
Coming to the end of this posting, let me try to anticipate the most common objectives against the usage of the Recovery Area:
1. Our Database is too large to go with Disk Storage as the Primary Backup Location!
Agreed. You will only take backups to tape. In spite of that, you may consider to use the Recovery Area to put mirrors of your Controlfiles and Online Logfiles on and to create your Archivelogs into. You must use it if you want to be able to do Flashback Database anyway.
2. We don’t use RMAN!
Agreed. The Recovery Area is not for you. You should have a very good reason not to use RMAN, though.
3. This is too good to be true! We have never done it like that!
Believe it or not, but that is (in slightly other wording, of course) the most common objection that I hear, typically from seasoned DBAs who have crafted their own scripts & methods in 8i already and now hesitate to change that. I have much understanding for the “never touch a running system” axiom. It should not mislead us to discredit new concepts, just because they are different from what we are used to, though. Keep an open mind for (not so new, meanwhile) New Features that are designed to help you to do your job and consider using it at least for the next Database that you create 🙂
