how retention policy affects archive log
RMAN> show retention policy 2> ; RMAN configuration parameters are: CONFIGURE RETENTION POLICY TO REDUNDANCY 2; RMAN> report obsolete 2> ; RMAN retention policy will be applied to the command RMAN retention policy is set to redundancy 2 no obsolete backups found RMAN> list backup 2> ; AS documentation saysBesides affecting datafile and control file backups, the retention policy affects archived logs and archived log backups.First,RMAN decides which datafile and control file backups are obsolete. Then, RMAN considers as obsolete all archived log backups that are older than the oldest datafile or control file backup that must be retainedNow i connect to SQL and create two big table for filling the redo log file and then get it archived.Please consider it here that i am getting archived before any taking backup i.e backup database in order to make archive older then the oldest datafile. C:\>dir C:\oracle\product\10.1.0\flash_recovery_area\ORCL\ARCHIVELOG\2008_03_17 Volume in drive C is khurram Volume Serial Number is F49D-FF2B Directory of C:\oracle\product\10.1.0\flash_recovery_area\ORCL\ARCHIVELOG\2008_03_17 03/17/2008 03:44 PM <DIR> . 03/17/2008 03:44 PM <DIR> .. 03/17/2008 03:44 PM 9,750,528 O1_MF_1_15_3XWLVK6T_.ARC 1 File(s) 9,750,528 bytes 2 Dir(s) 62,714,875,904 bytes free Now i take backup RMAN> backup database 2> ; Starting backup at 17-MAR-08 using channel ORA_DISK_1 RMAN> report obsolete 2> ; RMAN retention policy will be applied to the command RMAN retention policy is set to redundancy 2 no obsolete backups found RMAN> backup database 2> ; Starting backup at 17-MAR-08 using channel ORA_DISK_1 RMAN> report obsolete 2> ; RMAN retention policy will be applied to the command RMAN retention policy is set to redundancy 2 Report of obsolete backups and copies Type Key Completion Time Filename/Handle -------------------- ------ ------------------ -------------------- Archive Log 402 17-MAR-08 C:\ORACLE\PRODUCT\10.1.0\FLASH_RE COVERY_AREA\ORCL\ARCHIVELOG\2008_03_17\O1_MF_1_15_3XWLVK6T_.ARC You can see the obsolete archived files which is the older then the oldest file which has been obsolete.It also does'nt make sense to keep the archived log files which is older then the oldest file cause it will no longer be useful for recovery process. now this time what i did i created the archived log files after first backup in order to not to make it older then the oldest datafile backup. RMAN> delete obsolete 2> ; RMAN retention policy will be applied to the command RMAN retention policy is set to redundancy 2 using channel ORA_DISK_1 Deleting the following obsolete backups and copies: Type Key Completion Time Filename/Handle -------------------- ------ ------------------ -------------------- Archive Log 402 17-MAR-08 C:\ORACLE\PRODUCT\10.1.0\FLASH_RE COVERY_AREA\ORCL\ARCHIVELOG\2008_03_17\O1_MF_1_15_3XWLVK6T_.ARC Do you really want to delete the above objects (enter YES or NO)? yes deleted archive log archive log filename=C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELO G\2008_03_17\O1_MF_1_15_3XWLVK6T_.ARC recid=402 stamp=649611842 Deleted 1 objects RMAN> delete backup 2> ; using channel ORA_DISK_1 RMAN> list backup 2> ; C:\>dir C:\oracle\product\10.1.0\flash_recovery_area\ORCL\ARCHIVELOG\2008_03_17 Volume in drive C is khurram Volume Serial Number is F49D-FF2B Directory of C:\oracle\product\10.1.0\flash_recovery_area\ORCL\ARCHIVELOG\2008_ 03_17 03/17/2008 03:59 PM <DIR> . 03/17/2008 03:59 PM <DIR> .. 0 File(s) 0 bytes 2 Dir(s) 62,724,440,064 bytes free RMAN> backup database 2> ; Starting backup at 17-MAR-08 using channel ORA_DISK_1 RMAN> report obsolete 2> ; RMAN retention policy will be applied to the command RMAN retention policy is set to redundancy 2 no obsolete backups found now i come to at SQL and make redo log file filled by creating 2 big tables in order to get it archived. SQL> create table a3 as select * from all_objects 2 / Table created. SQL> create table a4 as select * from all_objects 2 / Table created. C:\>dir C:\oracle\product\10.1.0\flash_recovery_area\ORCL\ARCHIVELOG\2008_03_17 Volume in drive C is khurram Volume Serial Number is F49D-FF2B Directory of C:\oracle\product\10.1.0\flash_recovery_area\ORCL\ARCHIVELOG\2008_ 03_17 03/17/2008 04:09 PM <DIR> . 03/17/2008 04:09 PM <DIR> .. 03/17/2008 04:09 PM 9,751,552 O1_MF_1_16_3XWNCGRS_.ARC 1 File(s) 9,751,552 bytes 2 Dir(s) 62,563,205,120 bytes free RMAN> backup database 2> ; Starting backup at 17-MAR-08 using channel ORA_DISK_1 channel ORA_DISK_1: starting compressed full datafile backupset channel ORA_DISK_1: specifying datafile(s) in backupset RMAN> report obsolete 2> ; RMAN retention policy will be applied to the command RMAN retention policy is set to redundancy 2 no obsolete backups found You can see no archived logs get obsolete yet cause the archivelog O1_MF_1_16_3 XWNCGRS_.ARC is not older then oldest datafile backup. But this archivelog file will get obsolete if its beyond the retention policy,lets see how ,just take one more backup ,as i have already taken two time backup and the moment i go to take third backup it will cross ours retetnion policy from the period of 2. RMAN> backup database 2> ; RMAN> report obsolete 2> ; RMAN retention policy will be applied to the command RMAN retention policy is set to redundancy 2 Report of obsolete backups and copies Type Key Completion Time Filename/Handle -------------------- ------ ------------------ -------------------- Backup Set 240 17-MAR-08 Backup Piece 231 17-MAR-08 C:\ORACLE\PRODUCT\10.1.0\FLASH_RE COVERY_AREA\ORCL\BACKUPSET\2008_03_17\O1_MF_NNNDF_TAG20080317T160604_3XWN4WTB_.B KP Backup Set 241 17-MAR-08 Backup Piece 232 17-MAR-08 C:\ORACLE\PRODUCT\10.1.0\FLASH_RE COVERY_AREA\ORCL\BACKUPSET\2008_03_17\O1_MF_NCSNF_TAG20080317T160604_3XWN6Z95_.B KP Archive Log 403 17-MAR-08 C:\ORACLE\PRODUCT\10.1.0\FLASH_RE COVERY_AREA\ORCL\ARCHIVELOG\2008_03_17\O1_MF_1_16_3XWNCGRS_.ARC
===============================================================
When having a rman retention policy based on REDUNDANCY is a bad idea...
Now, let's also suppose that every night you backup your database using the following script:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
rman {
backup database plus archivelog;
delete noprompt obsolete redundancy 2;
}
The backup task is quite simple: first of all it ensures that we have the controlfile autobackup feature on, then it backups the database and archives and, at the end, it deletes all obsolete backups using the REDUNDANCY 2 retention policy.
Using the above approach you might think that you can restore your database as it was two days ago, right? For example, if you have a backup taken on Monday and another one taken on Tuesday you may restore your database as it was within the (Monday_last_backup - Today) time interval. Well, that's wrong!
Consider the following scenario:
1. On Monday night you backup the database using the above script;
2. On Tuesday, during the day, you drop a tablespace. Because this is a structural database change a controlfile autobackup will be triggered. Ieeei, you have a new controlfile backup.
3. On Tuesday night you backup again the database... nothing unusual, right?
Well, the tricky part is regarding the DELETE OBSOLETE command. When the backup script will run this command, RMAN finds out three controlfile backups: one is originating from the Monday backup, one is from the structural change and the third is from our just finished Tuesday backup database command. Now according to the retention policy of "REDUNDANCY 2", RMAN will assume that it is safe to delete the backup of the controlfile taken on Monday night backup because it's out of our retention policy and because this backup is the oldest one. Uuups... this means that we gonna have a big problem restoring the database as it was before our structural change because we don't have a controlfile backup from that time.
So, if you intend to incomplete recover your database to a previous time in the past it's really a good idea to switch to a retention policy based on a "RECOVERY WINDOW" instead. In our case a RECOVERY WINDOW OF 2 DAYS would be more appropriate.
No comments:
Post a Comment