Hi,
Happy new year (after almost a month). I was quite busy and lazy to write a new blog post but today I found the energy.
What I realized newly is a little bit shocking for me because I had no idea! Maybe this is just an ignorance of me but I never read anything about this (I had read entire backup and recovery documentation of oracle) and never heard of it. Here is the problem: “Report Obsolete” or “Delete Obsolete” commands don’t report or delete an old backup even if it is beyond the retention period.
At first, I thought probably I am missing something about retention period or maybe a datafile is not backed up correctly (which is not much a possibility). After debugging RMAN report obsolete command, some debug logs takes my attention and I think I found the issue but have no idea whether this is a bug or a feature. I will demonstrate it with a single non-cdb database instance to keep things easy. This is a 19.16 database in a virtual machine and this is happening in both Linux and windows. an empty database, here is the schema report:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
RMAN> report schema; Report of database schema for database with db_unique_name ORCL List of Permanent Datafiles =========================== File Size(MB) Tablespace RB segs Datafile Name ---- -------- -------------------- ------- ------------------------ 1 1030 SYSTEM YES /u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_j6xp8c17_.dbf 3 630 SYSAUX NO /u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_j6xp9r68_.dbf 4 355 UNDOTBS1 YES /u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_j6xpbkbc_.dbf 5 10 TBS_TEST NO /u01/app/oracle/oradata/ORCL/datafile/o1_mf_tbs_test_kwwz06qg_.dbf 7 5 USERS NO /u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_j6xpblfl_.dbf List of Temporary Files ======================= File Size(MB) Tablespace Maxsize(MB) Tempfile Name ---- -------- -------------------- ----------- -------------------- 1 106 TEMP 32767 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_temp_j6xpd6oz_.tmp |
Let’s create a dummy tablespace first:
|
1 2 3 |
SQL> create tablespace tbs_test datafile size 10M autoextend on next 10m; Tablespace created. |
Database has no backup at all at that point, Let’s get one:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
RMAN> list backup of database; specification does not match any backup in the repository RMAN> backup database; Starting backup at 23-JAN-23 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/ORCL/datafile/o1_mf_system_j6xp8c17_.dbf input datafile file number=00003 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_j6xp9r68_.dbf input datafile file number=00004 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_j6xpbkbc_.dbf input datafile file number=00005 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_tbs_test_kwwz06qg_.dbf input datafile file number=00007 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_j6xpblfl_.dbf channel ORA_DISK_1: starting piece 1 at 23-JAN-23 channel ORA_DISK_1: finished piece 1 at 23-JAN-23 piece handle=/u01/app/oracle/fast_recovery_area/ORCL/backupset/2023_01_23/o1_mf_nnndf_TAG20230123T152626_kwwz7m6f_.bkp tag=TAG20230123T152626 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15 Finished backup at 23-JAN-23 Starting Control File and SPFILE Autobackup at 23-JAN-23 piece handle=/u01/app/oracle/fast_recovery_area/ORCL/autobackup/2023_01_23/o1_mf_s_1126884402_kwwz8296_.bkp comment=NONE Finished Control File and SPFILE Autobackup at 23-JAN-23 |
our new test tablespace TBS_TEST is backup up along with the database. Now, my retention period is default which is REDUNDANCY 1:
|
1 2 3 4 |
RMAN> show retention policy; RMAN configuration parameters for database with db_unique_name ORCL are: CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default |
that means only 1 last backup for everything (database, tablespace, datafile) is enough and all previous backups are “obsolete”. Now before getting a second backup let’s delete TBS_TEST tablespace:
|
1 2 3 |
SQL> drop tablespace tbs_test including contents and datafiles; Tablespace dropped. |
and get a new backup:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
RMAN> backup database; Starting backup at 23-JAN-23 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/ORCL/datafile/o1_mf_system_j6xp8c17_.dbf input datafile file number=00003 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_j6xp9r68_.dbf input datafile file number=00004 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_j6xpbkbc_.dbf input datafile file number=00007 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_j6xpblfl_.dbf channel ORA_DISK_1: starting piece 1 at 23-JAN-23 list backup of database; channel ORA_DISK_1: finished piece 1 at 23-JAN-23 piece handle=/u01/app/oracle/fast_recovery_area/ORCL/backupset/2023_01_23/o1_mf_nnndf_TAG20230123T152709_kwwz8xqj_.bkp tag=TAG20230123T152709 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07 Finished backup at 23-JAN-23 Starting Control File and SPFILE Autobackup at 23-JAN-23 piece handle=/u01/app/oracle/fast_recovery_area/ORCL/autobackup/2023_01_23/o1_mf_s_1126884436_kwwz94v2_.bkp comment=NONE Finished Control File and SPFILE Autobackup at 23-JAN-23 |
Good, I have two backups now and TBS_TEST is not included on the last one. since my retention policy is “REDUNDANCY 1”, previous backup should be “obsolete”. List backups:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
RMAN> list backup of database; List of Backup Sets =================== BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 13 Full 1.73G DISK 00:00:07 23-JAN-23 BP Key: 13 Status: AVAILABLE Compressed: NO Tag: TAG20230123T152626 Piece Name: /u01/app/oracle/fast_recovery_area/ORCL/backupset/2023_01_23/o1_mf_nnndf_TAG20230123T152626_kwwz7m6f_.bkp List of Datafiles in backup set 13 File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name ---- -- ---- ---------- --------- ----------- ------ ---- 1 Full 2531160 23-JAN-23 NO /u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_j6xp8c17_.dbf 3 Full 2531160 23-JAN-23 NO /u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_j6xp9r68_.dbf 4 Full 2531160 23-JAN-23 NO /u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_j6xpbkbc_.dbf 5 Full 2531160 23-JAN-23 NO 7 Full 2531160 23-JAN-23 NO /u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_j6xpblfl_.dbf BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 15 Full 1.72G DISK 00:00:04 23-JAN-23 BP Key: 15 Status: AVAILABLE Compressed: NO Tag: TAG20230123T152709 Piece Name: /u01/app/oracle/fast_recovery_area/ORCL/backupset/2023_01_23/o1_mf_nnndf_TAG20230123T152709_kwwz8xqj_.bkp List of Datafiles in backup set 15 File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name ---- -- ---- ---------- --------- ----------- ------ ---- 1 Full 2531252 23-JAN-23 NO /u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_j6xp8c17_.dbf 3 Full 2531252 23-JAN-23 NO /u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_j6xp9r68_.dbf 4 Full 2531252 23-JAN-23 NO /u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_j6xpbkbc_.dbf 7 Full 2531252 23-JAN-23 NO /u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_j6xpblfl_.dbf |
as you see datafile 5 row in the first backup has no Datafile Name anymore because it is dropped. Now, lets report obsolete backups:
|
1 2 3 4 5 6 7 8 9 |
RMAN> report obsolete; RMAN retention policy will be applied to the command RMAN retention policy is set to redundancy 1 Report of obsolete backups and copies Type Key Completion Time Filename/Handle -------------------- ------ ------------------ -------------------- Backup Set 14 23-JAN-23 Backup Piece 14 23-JAN-23 /u01/app/oracle/fast_recovery_area/ORCL/autobackup/2023_01_23/o1_mf_s_1126884402_kwwz8296_.bkp |
Huh! Only autobackup is obsolete and report command doesn’t mention about previous backup! Please remember my retention is redundancy 1 so only last backup should be kept but RMAN is keeping previous backup, the one with dropped tablespace/datafile.
if I keep taking new backups and run report obsolete (or delete obsolete) commands, it doesn’t report the first backup, only the ones after that.
I tried this with a 19.3 databases and it didn’t happen! it marked old backup as obsolete immediately. So as I said, I don’t know if this is my ignorance, lack of knowledge of just missing something or a bug but on different systems (on virtual machines, linux and windows servers) for 19.16, I see this behavior. Also, it could be a Container database or a non-cdb. You might want to check your systems if there are older backups than your retention periods.
Thanks for reading, wish you all a happy, healthy new year. My next post will be about a ORA-00600 which drives me crazy for a few weeks.
EDIT: I should add one more thing, by the retention policy requirements all archivelogs between old “obsolete” backup and the current one are deleted! so, this old backup file is completely useless.