After incomplete recovery and opening a 9i database with resetlogs we ran a full backup which completed successfully. The backup includes a command to delete obsolete backups after it finishes:
delete noprompt obsolete device type sbt;
RMAN is configured to use REDUNDANCY 2:
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
However all the backups from the old incarnation were immediately marked obsolete and deleted without regard to redundancy.
- Would this behaviour have been different if we had a
RECOVERY WINDOWconfigured instead ofREDUNDANCY 2? - Is this behaviour the same in later versions of Oracle?
edit: added output of LIST INCARNATION:
RMAN> list incarnation;
List of Database Incarnations
DB Key Inc Key DB Name DB ID CUR Reset SCN Reset Time
------- ------- -------- ---------------- --- -------------- ----------
1 1 LIVE 3494832994 NO 1 19-JAN-04
2 2 LIVE 3494832994 NO 11966702870498 01-JAN-14
3 3 LIVE 3494832994 YES 12041003378277 04-JUL-18
Best Answer
For The Interested RMAN Beginners
The RMAN policies
REDUNDANCYandRECOVERY WINDOWare mutually exclusive. This means you can either set one or the other.Redundancy Policy
Having set
REDUNDANCY 2will always keep only the last two backups and deleting (or marking obsolete) any other previous backups, that are no longer required to bring back the database into a consistent state.Note:
If you have LEVEL 1 and ARCHIVELOG backups in between the FULL and/or LEVEL 0 backups, then they should be retained until no longer required.
Additional Information
According to the PDF file Oracle Database - Backup and Recovery Reference your retention policy
REDUNDANCY 2should not have deleted your older backups:The above example uses a value of 1 but is clearly stating that earlier backups of full backups are not deleted until they are larger than the value configured.
Answering your questions
It depends, .... (see 2. and "Possible Issues")
According to the documentation the
retention policysetting still seems to be the same for all versions:Possible Issues
Reproducing with Oracle 12c
Because Oracle RDBMS 9i is pretty much obsolete and our environment is nearly up-to-date, I was only able to re-iterate/reproduce the steps in a 12c environment.
RMAN Configuration
RMAN was set up using the defaults:
RMAN Backups
RMAN backups were performed by issuing a simple
backup database;command:Verifying Backups, Check Obsolete Backups and List Incarnations
After a certain period of backups had been performed I double-checked the RMAN catalog:
Verified that no obsolete backups were lying around:
And listed the current incarnations:
Perform First Restore / Recovery
The following commands were issued to restore/recover the database into a consistent state:
RMAN> shutdown immediate database closed database dismounted Oracle instance shut down RMAN> startup mount; connected to target database (not started) Oracle instance started database mounted Total System Global Area 1610612736 bytes Fixed Size 8747368 bytes Variable Size 385877656 bytes Database Buffers 1207959552 bytes Redo Buffers 8028160 bytes RMAN> run 2> { 3> set until time '2018-07-17 14:36:14'; 4> restore database; 5> recover database; 6> } executing command: SET until clause Starting restore at 2018-07-17 16:04:30 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=12 device type=DISK channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00001 to C:\ORACLE_12\ORADATA\ORACLE12JN\SYSTEM01.DBF channel ORA_DISK_1: restoring datafile 00003 to C:\ORACLE_12\ORADATA\ORACLE12JN\SYSAUX01.DBF channel ORA_DISK_1: restoring datafile 00005 to C:\ORACLE_12\ORADATA\ORACLE12JN\UNDOTBS01.DBF channel ORA_DISK_1: restoring datafile 00007 to C:\ORACLE_12\ORADATA\ORACLE12JN\USERS01.DBF channel ORA_DISK_1: reading from backup piece C:\ORACLE_12\RECOVERY_AREA\ORACLE12JN\BACKUPSET\2018_07_17\O1_MF_NNND0_TAG20180717T143558_FNVROH49_.BKP channel ORA_DISK_1: piece handle=C:\ORACLE_12\RECOVERY_AREA\ORACLE12JN\BACKUPSET\2018_07_17\O1_MF_NNND0_TAG20180717T143558_FNVROH49_.BKP tag=TAG20180717T143558 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:07 Finished restore at 2018-07-17 16:04:38 Starting recover at 2018-07-17 16:04:38 using channel ORA_DISK_1 starting media recovery media recovery complete, elapsed time: 00:00:00 Finished recover at 2018-07-17 16:04:39 RMAN> alter database open resetlogs; Statement processed RMAN> list incarnation; List of Database Incarnations DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time ------- ------- -------- ---------------- --- ---------- ---------- 1 1 ORACLE12 2083073378 PARENT 1 2017-03-08 15:57:31 2 2 ORACLE12 2083073378 PARENT 1490582 2018-07-16 09:27:03 3 3 ORACLE12 2083073378 CURRENT 1701490 2018-07-17 16:05:13 RMAN> list backup summary; List of Backups =============== Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag ------- -- -- - ----------- ------------------- ------- ------- ---------- --- 3 B F A DISK 2018-07-16 10:03:01 1 1 NO TAG20180716T100301 5 B F A DISK 2018-07-16 10:27:44 1 1 NO TAG20180716T102744 7 B F A DISK 2018-07-16 10:49:50 1 1 NO TAG20180716T104950 9 B F A DISK 2018-07-16 10:50:08 1 1 NO TAG20180716T105007 11 B F A DISK 2018-07-16 10:50:22 1 1 NO TAG20180716T105022 13 B F A DISK 2018-07-16 10:51:21 1 1 NO TAG20180716T105121 15 B F A DISK 2018-07-16 13:06:13 1 1 NO TAG20180716T130612 17 B F A DISK 2018-07-16 15:26:23 1 1 NO TAG20180716T152623 19 B F A DISK 2018-07-16 15:57:07 1 1 NO TAG20180716T155707 20 B 0 A DISK 2018-07-17 08:22:01 1 1 NO TAG20180717T082154 21 B F A DISK 2018-07-17 08:22:02 1 1 NO TAG20180717T082202 22 B 0 A DISK 2018-07-17 14:36:07 1 1 NO TAG20180717T143558 23 B F A DISK 2018-07-17 14:36:14 1 1 NO TAG20180717T143614 24 B F A DISK 2018-07-17 15:16:15 1 1 NO TAG20180717T151608 25 B F A DISK 2018-07-17 15:16:23 1 1 NO TAG20180717T151623 26 B A A DISK 2018-07-17 15:55:52 1 1 NO TAG20180717T155551 27 B F A DISK 2018-07-17 15:55:53 1 1 NO TAG20180717T155553 28 B F A DISK 2018-07-17 16:05:17 1 1 NO TAG20180717T160517 RMAN> report obsolete; RMAN retention policy will be applied to the command RMAN retention policy is set to redundancy 2 no obsolete backups foundThe restore was successful and no backups were marked as obsolete even though the incarnation changed after the restore and with the
ALTER DATABASE OPEN RESETLOGSissued.Alternate Incarnation Listing
An alternate representation of the incarnations can be achieved with the following command:
Output:
INCARNATION# RESETLOGS_CHANGE# RESETLOGS_TIME PRIOR_INCARNATION# STATUS LEVEL PATH ------------ ----------------- ------------------- ------------------ ------- ---------- -------------------- 1 1 2017-03-08 15:57:31 0 PARENT 1 -> 1 2 1490582 2018-07-16 09:27:03 1 PARENT 2 -> 1 -> 2 3 1701490 2018-07-17 16:05:13 2 CURRENT 3 -> 1 -> 2 -> 3The advantage of querying the local catalog in the database instance is the representation of the incarnation as a path to follow
Perform Second Restore / Recovery
The following commands were issued to restore/recover the database into a consistent state again:
RMAN> shutdown immediate database closed database dismounted Oracle instance shut down RMAN> startup mount connected to target database (not started) Oracle instance started database mounted Total System Global Area 1610612736 bytes Fixed Size 8747368 bytes Variable Size 520095384 bytes Database Buffers 1073741824 bytes Redo Buffers 8028160 bytes RMAN> run 2> { 3> set until time '2018-07-17 08:22:01'; 4> restore database; 5> recover database; 6> } executing command: SET until clause Starting restore at 2018-07-17 16:14:37 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=11 device type=DISK RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of restore command at 07/17/2018 16:14:37 RMAN-20207: UNTIL TIME or RECOVERY WINDOW is before RESETLOGS timeOk, we've hit an incarnation "bump" in the restore path. We are currently on incarnation 3 as can be seen in the incarnation listing below which I copied from above:
INCARNATION# RESETLOGS_CHANGE# RESETLOGS_TIME PRIOR_INCARNATION# STATUS LEVEL PATH ------------ ----------------- ------------------- ------------------ ------- ---------- -------------------- 1 1 2017-03-08 15:57:31 0 PARENT 1 -> 1 2 1490582 2018-07-16 09:27:03 1 PARENT 2 -> 1 -> 2 3 1701490 2018-07-17 16:05:13 2 CURRENT 3 -> 1 -> 2 -> 3...and the backup we are aiming for is after the RESETLOGS for incarnation 2. Let's reset the incarnation to 2 and proceed with the backup:
Seems to work. Let's restart the restore/recover again:
RMAN> run 2> { 3> set until time '2018-07-17 08:22:01'; 4> restore database; 5> recover database; 6> } executing command: SET until clause Starting restore at 2018-07-17 16:16:12 using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00001 to C:\ORACLE_12\ORADATA\ORACLE12JN\SYSTEM01.DBF channel ORA_DISK_1: restoring datafile 00003 to C:\ORACLE_12\ORADATA\ORACLE12JN\SYSAUX01.DBF channel ORA_DISK_1: restoring datafile 00005 to C:\ORACLE_12\ORADATA\ORACLE12JN\UNDOTBS01.DBF channel ORA_DISK_1: restoring datafile 00007 to C:\ORACLE_12\ORADATA\ORACLE12JN\USERS01.DBF channel ORA_DISK_1: reading from backup piece C:\ORACLE_12\RECOVERY_AREA\ORACLE12JN\BACKUPSET\2018_07_17\O1_MF_NNND0_TAG20180717T082154_FNV2R328_.BKP channel ORA_DISK_1: piece handle=C:\ORACLE_12\RECOVERY_AREA\ORACLE12JN\BACKUPSET\2018_07_17\O1_MF_NNND0_TAG20180717T082154_FNV2R328_.BKP tag=TAG20180717T082154 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:07 Finished restore at 2018-07-17 16:16:20 Starting recover at 2018-07-17 16:16:20 using channel ORA_DISK_1 starting media recovery archived log for thread 1 with sequence 3 is already on disk as file C:\ORACLE_12\RECOVERY_AREA\ORACLE12JN\ARCHIVELOG\2018_07_17\O1_MF_1_3_FNVXC7R7_.ARC archived log file name=C:\ORACLE_12\RECOVERY_AREA\ORACLE12JN\ARCHIVELOG\2018_07_17\O1_MF_1_3_FNVXC7R7_.ARC thread=1 sequence=3 media recovery complete, elapsed time: 00:00:00 Finished recover at 2018-07-17 16:16:21 RMAN> alter database open resetlogs; Statement processed 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>The restore was successful and no backups were marked as obsolete even though the incarnation changed after the restore and the
ALTER DATABASE OPEN RESETLOGSwas issued again. Now we are on a newer incarnation and still noObsolete Backupsreported.List of Current Incarnations After Second Restore (SQL)
SQL> select INCARNATION#, RESETLOGS_CHANGE#, RESETLOGS_TIME, PRIOR_INCARNATION#, STATUS, LEVEL, SYS_CONNECT_BY_PATH(INCARNATION#, ' -> ') Path from v$database_incarnation WHERE LEVEL >=1 START WITH INCARNATION# = '1' CONNECT BY PRIOR INCARNATION# = PRIOR_INCARNATION# order by Level, Path, RESETLOGS_TIME; INCARNATION# RESETLOGS_CHANGE# RESETLOGS_TIME PRIOR_INCARNATION# STATUS LEVEL PATH ------------ ----------------- ------------------- ------------------ ------- ---------- -------------------- 1 1 2017-03-08 15:57:31 0 PARENT 1 -> 1 2 1490582 2018-07-16 09:27:03 1 PARENT 2 -> 1 -> 2 3 1701490 2018-07-17 16:05:13 2 ORPHAN 3 -> 1 -> 2 -> 3 4 1691865 2018-07-17 16:16:29 2 CURRENT 3 -> 1 -> 2 -> 4Initiate Backup
After restoring the second time let's backup the database again:
Check Obsolete
We'll now check which (backup) files have become obsolete:
Check Incarnations (SQL)
SQL> select INCARNATION#, RESETLOGS_CHANGE#, RESETLOGS_TIME, PRIOR_INCARNATION#, STATUS, LEVEL, SYS_CONNECT_BY_PATH(INCARNATION#, ' -> ') Path from v$database_incarnation WHERE LEVEL >=1 START WITH INCARNATION# = '1' CONNECT BY PRIOR INCARNATION# = PRIOR_INCARNATION# order by Level, Path, RESETLOGS_TIME; INCARNATION# RESETLOGS_CHANGE# RESETLOGS_TIME PRIOR_INCARNATION# STATUS LEVEL PATH ------------ ----------------- ------------------- ------------------ ------- ---------- -------------------- 1 1 2017-03-08 15:57:31 0 PARENT 1 -> 1 2 1490582 2018-07-16 09:27:03 1 PARENT 2 -> 1 -> 2 3 1701490 2018-07-17 16:05:13 2 ORPHAN 3 -> 1 -> 2 -> 3 4 1691865 2018-07-17 16:16:29 2 CURRENT 3 -> 1 -> 2 -> 4As can be seen the incarnation 3 is now orphaned, because its direct line of heritage in regards to the current state of the database is broken. After the first restore we went back in time an re-restored the database again which results in the incarnation path
1 -> 2 -> 4being the direct line of current ancestors for the open database.Because the direct line for restoring the current database is along the incarnations of 1, 2, 4 there is no need for RMAN to keep the obsolete backups listed above. They can safely be deleted.
Delete Obsolete
Let's go ahead and delete the obsolete backups:
List Backup Summary
Now that we have deleted some backup files and RMAN has performed some internal cleaning, let's have a look at the backup summary:
As we can see we still have "at least" two complete backup that will allow us to restore the database two backups in the past. RMAN (in 12c) did not delete any other backups along the incarnation path or outside of the orphaned incarnation.
Conclusion
Regarding the deleted backups after your initial restore in 9i I believe there are two possible scenarios:
Reference Material