Skip to Main Content
  • Questions

  • RMAN Backup disappeared from catalog

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Marco.

Asked: January 10, 2022 - 11:28 am UTC

Last updated: January 14, 2022 - 6:05 am UTC

Version: 19.13.0.0.0

Viewed 10K+ times! This question is

You Asked

Hello,
I've configured RMAN backup with the following schedulation:
- FULL Level 0 , every Sunday at 01:05 AM
- INCREMENTAL Level 1, from Monday to Saturday at 01:05 AM
- Archive Log Backup every day, every 30 minutes
The retention policy is set to Redundancy 2:
RMAN> show all;
RMAN configuration parameters for database with db_unique_name DATABASE are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u01/app/oracle/backup/DATABASE/autobackup/cf_sp_%F';
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO BACKUPSET PARALLELISM 3;
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 RMAN OUTPUT TO KEEP FOR 7 DAYS; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO DISK;
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/19.0.0/dbhome_1/dbs/snapcf_DATABASE.f'; # default

The Backup script are the following:
- Level 0
CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u01/app/oracle/backup/DATABASE/autobackup/cf_sp_%F';
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO BACKUPSET PARALLELISM 3;
run {
     backup AS COMPRESSED BACKUPSET archivelog all format '/u01/app/oracle/backup/DATABASE/arch_%d_ARCH_bks%s_%T_%U.bck' delete input filesperset 10;
     backup AS COMPRESSED BACKUPSET incremental level 0 database format '/u01/app/oracle/backup/DATABASE/data_%d_FULL_bks%s_%T_%U.bck' include current controlfile;
     backup AS COMPRESSED BACKUPSET archivelog all format '/u01/app/oracle/backup/DATABASE/arch_%d_ARCH_bks%s_%T_%U.bck' delete input filesperset 10;
     crosscheck backup;
     delete noprompt obsolete;
}

- Level 1
CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u01/app/oracle/backup/DATABASE/autobackup/cf_sp_%F';
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO BACKUPSET PARALLELISM 3;
run {
     backup AS COMPRESSED BACKUPSET archivelog all format '/u01/app/oracle/backup/DATABASE/arch_%d_ARCH_bks%s_%T_%U.bck' delete input filesperset 10;
     backup AS COMPRESSED BACKUPSET incremental level 1 database format '/u01/app/oracle/backup/DATABASE/data_%d_FULL_bks%s_%T_%U.bck' include current controlfile;
     backup AS COMPRESSED BACKUPSET archivelog all format '/u01/app/oracle/backup/DATABASE/arch_%d_ARCH_bks%s_%T_%U.bck' delete input filesperset 10;
     crosscheck backup;
     delete noprompt obsolete;
}

- Archivelog
CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u01/app/oracle/backup/DATABASE/autobackup/cf_sp_%F';
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO BACKUPSET PARALLELISM 3;
run {
     backup AS COMPRESSED BACKUPSET archivelog all format '/u01/app/oracle/backup/DATABASE/arch_%d_ARCH_bks%s_%T_%U.bck' delete input filesperset 10;
     crosscheck backup;
     delete noprompt obsolete;
}

With Redundancy set to 2, I expected that RMAN keeps 2 FULL Level 0 copies of the database.
For example:
I took a first FULL Level 0 at 26/12/2021 at 01:05 AM
I took a second FULL Level 0 at 02/01/2022 at 01:05 AM
On 09/01/2022 I expected that RMAN make a new FULL Level 0 and delete the Level 0 of 26/12/2021.
Here comes the problems.
RMAN> list backup summary;

using target database control file instead of recovery catalog

List of Backups
===============
Key     TY LV S Device Type Completion Time     #Pieces #Copies Compressed Tag
------- -- -- - ----------- ------------------- ------- ------- ---------- ---
92897   B  A  A DISK        02/01/2022 01:30:05 1       1       YES        TAG20220102T013004
92899   B  0  A DISK        02/01/2022 01:47:39 1       1       YES        TAG20220102T010505
92901   B  0  A DISK        02/01/2022 01:54:04 1       1       YES        TAG20220102T010505
92902   B  A  A DISK        02/01/2022 02:00:06 1       1       YES        TAG20220102T020006
92904   B  0  A DISK        02/01/2022 02:04:25 1       1       YES        TAG20220102T010505
92905   B  A  A DISK        02/01/2022 02:04:26 1       1       YES        TAG20220102T020426
92907   B  A  A DISK        02/01/2022 02:30:04 1       1       YES        TAG20220102T023003
92909   B  A  A DISK        02/01/2022 03:00:04 1       1       YES        TAG20220102T030003
92911   B  A  A DISK        02/01/2022 03:30:04 1       1       YES        TAG20220102T033004
...
93693   B  0  A DISK        09/01/2022 01:44:32 1       1       YES        TAG20220109T010505
93695   B  0  A DISK        09/01/2022 01:50:08 1       1       YES        TAG20220109T010505
93696   B  A  A DISK        09/01/2022 02:00:04 1       1       YES        TAG20220109T020003
93698   B  0  A DISK        09/01/2022 02:00:37 1       1       YES        TAG20220109T010505
93699   B  A  A DISK        09/01/2022 02:00:42 1       1       YES        TAG20220109T020042
93701   B  A  A DISK        09/01/2022 02:30:04 1       1       YES        TAG20220109T023004
...

Ther RMAN catalog, actually, starts with the FULL Level 0 of 02/01/2022.
The problem is that the oldest FULL Level 0 are not deleted from disk, because I noticed that they are disappeared from RMAN catalog on 07/01/2022 at 14:30 PM:
- RMAN OUTPUT AT 07/01/2022 14:00 PM:
...
Finished Control File and SPFILE Autobackup at 07/01/2022 14:00:06

using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/backup/DATABASE/arch_DATABASE_ARCH_bks92141_20211226_vd0hli0s_92141_1_1.bck RECID=92099 STAMP=1092274204
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=<b>/u01/app/oracle/backup/DATABASE/data_DATABASE_FULL_bks92138_20211226_va0hlgi1_92138_1_1.bck</b> RECID=92101 STAMP=1092272705
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/backup/DATABASE/arch_DATABASE_ARCH_bks92144_20211226_vg0hljp4_92144_1_1.bck RECID=92104 STAMP=1092276004
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=<b>/u01/app/oracle/backup/DATABASE/data_DATABASE_FULL_bks92140_20211226_vc0hlgi1_92140_1_1.bck</b> RECID=92103 STAMP=1092272705
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/backup/DATABASE/arch_DATABASE_FULL_bks92146_20211226_vi0hlk5p_92146_1_1.bck RECID=92107 STAMP=1092276409
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=<b>/u01/app/oracle/backup/DATABASE/data_DATABASE_FULL_bks92139_20211226_vb0hlgi1_92139_1_1.bck</b> RECID=92106 STAMP=1092272705
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/backup/REPOSITORY/arch_REPOSITO_ARCH_bks92150_20211226_vm0hln9k_92150_1_1.bck RECID=92111 STAMP=1092279604
crosschecked backup piece: found to be 'AVAILABLE'
...

- RMAN OUTPUT AT 07/01/2022 14:30 PM:
..
Finished Control File and SPFILE Autobackup at 07/01/2022 14:30:12

using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=<b>/u01/app/oracle/backup/DATABASE/data_DATABASE_FULL_bks92140_20211226_vc0hlgi1_92140_1_1.bck</b> RECID=92103 STAMP=1092272705
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/backup/DATABASE/arch_DATABASE_ARCH_bks92144_20211226_vg0hljp4_92144_1_1.bck RECID=92104 STAMP=1092276004
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/backup/DATABASE/arch_DATABASE_FULL_bks92146_20211226_vi0hlk5p_92146_1_1.bck RECID=92107 STAMP=1092276409
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=<b>/u01/app/oracle/backup/DATABASE/data_DATABASE_FULL_bks92139_20211226_vb0hlgi1_92139_1_1.bck</b> RECID=92106 STAMP=1092272705
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/backup/DATABASE/arch_DATABASE_ARCH_bks92150_20211226_vm0hln9k_92150_1_1.bck RECID=92111 STAMP=1092279604
crosschecked backup piece: found to be 'AVAILABLE'
..

- RMAN OUTPUT AT 07/01/2022 15:00 PM:
..
Finished Control File and SPFILE Autobackup at 07/01/2022 15:00:12

using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=<b>/u01/app/oracle/backup/DATABASE/data_DATABASE_FULL_bks92139_20211226_vb0hlgi1_92139_1_1.bck</b> RECID=92106 STAMP=1092272705
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/backup/DATABASE/arch_DATABASE_FULL_bks92146_20211226_vi0hlk5p_92146_1_1.bck RECID=92107 STAMP=1092276409
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/backup/DATABASE/arch_DATABASE_ARCH_bks92148_20211226_vk0hllhb_92148_1_1.bck RECID=92109 STAMP=1092277803
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/backup/DATABASE/arch_DATABASE_ARCH_bks92150_20211226_vm0hln9k_92150_1_1.bck RECID=92111 STAMP=1092279604
crosschecked backup piece: found to be 'AVAILABLE'
..


Every 30 minutes, on the Archivelog Backup, a piece of Level 0 of 26/12/2021 disappear from catalog without being deleted from disk and RMAN Output doesn't log any delete operation (for some reason the file are not deleted on disk from 26/12/2021 01:05 AM until 27/12/2012 13:00 PM, and then jump directly to 02/01/2022 01:05 AM).

I'm doing something wrong?

and Connor said...

I'm doing something wrong?


Yes :-)

It is most likely down to "crosscheck backup" and its something I see so many places and wish I didn't.

crosscheck is for when something is wrong or out of sync. It is *not* part of your normal backup regime or scripts.

If the RMAN catalog (or controlfiles) is out of sync with reality, then you *want* to be alerted about it, because something has happened outside of RMAN's watch. In those circumstances, you would then rectify the conflict..and *maybe* use crosscheck to do that.

But having crosscheck in every script means the following is a very possible scenario:

- an archive log or backup or anything is missing (mistakenly deleted by someone, was moved, permissions wrong etc etc etc)
- RMAN cant see it, access it, etc etc and this is a problem
- you run crosscheck which tells RMAN..."Hey those problems you are having, I've acknowledged them and wont complain about them anymore"

Moreover, if someone has run any kind of 'delete expired' after any of those crosschecks, then that information is purged forever.

Some similar examples here

https://blog.dbi-services.com/oracle-12c-why-you-shouldnt-do-a-crosscheck-archivelog-all-in-your-regular-rman-backup-scripts/




Hence crosscheck (at some point in the past) it is most likely the cause.


Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Added After

A reader, January 13, 2022 - 8:25 am UTC

Hello Connor,
I've added the crosscheck after, just for output log purpose.
The issue was present before I've added it to the script.
Nobody has run a 'delete expired' command, indeed the file that are disappeared from catalog still exist on physical disk.
Connor McDonald
January 14, 2022 - 6:05 am UTC

If they are still on disk, then a catalog command can add them back in.

After that I'd monitor for recurrence and keep all logs until that occurs.

Then post back here (or raise an SR) and we can take a fresh look
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Backup/Recovery

Check out the complete guide to all of the Backup & Recovery techniques in the Oracle Database.