orawin.info

I’ve recently run into an issue where the recovery catalog views (RC_xxx) in an 11.1.0.7 catalog may contain inaccurate information. We have a client with multiple databases all of which are backed up using RMAN. Rather than reading the logfile of each and every backup, each and every day I wrote a small script to query the recovery catalog to obtain the latest backup date (from RC_BACKUP_SET)  for each database. The script, if anyone wishes to ‘borrow’ it is

select
   distinct -- because sometimes autobackup gets same time
   db_name
,  last_backup
from
(select
   db.name db_name
,  to_char(bs.completion_time,'DD-MON-YYYY HH24:MI') completion_time
,  to_char(max(bs.completion_time) over (partition by bs.db_key),'DD-MON-YYYY HH24:MI') last_backup
from
  rc_database db
, rc_backup_set bs
where bs.db_key = db.db_key
order by 1,2
)
where
completion_time = last_backup;

I was reasonably pleased that this worked for the first few databases, but then puzzled as to why it was apparently showing some databases backups never succeeding when the logs showed that they have. We eventually discovered that the RC_BACKUP_SET view does not show all backup sets for 11.1.0.7 databases, at least for us, unless a list backup command is run (the output below shows this happening for us). Our databases where this happens are also 11.1.0.7 (same as the catalog) and are in noarchivelog mode. This latter fact may be relevant.

select db_key from rc_database
where name = ‘DB_NAME’;

DB_KEY
———-
60900

select max(completion_time) from rc_backup_set
where db_key=60900;

MAX(COMPL
———
16-DEC-10

SQL> host
[oracle@HOSTNAME ~]$ export ORACLE_SID=DB_NAME

[oracle@HOSTNAME ~]$ . oraenv
ORACLE_SID = [DB_NAME] ?

The Oracle base for ORACLE_HOME=/apps/oracle/product/11.1.0/db_1 is /apps/oracle

[oracle@HOSTNAME ~]$ $ORACLE_HOME/bin/rman target / catalog rman_username/rman_pwd@catalog

Recovery Manager: Release 11.1.0.7.0 – Production on Mon Dec 20 10:15:19 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: DB_NAME (DBID=3038288275)
connected to recovery catalog database

RMAN> list backup summary;

?

List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
——- — – – ———– ————— ——- ——- ———- —
245768 B F A DISK 19-DEC-10 1 1 YES TAG20101219T230053
245769 B F A DISK 19-DEC-10 1 1 NO TAG20101219T231009

RMAN> exit

Recovery Manager complete.

[oracle@HOSTNAME ~]$ exit

exit

SQL>;

1 select max(completion_time) from rc_backup_set
2* where db_key=60900

SQL> /

MAX(COMPL
———
19-DEC-10

SQL>

Important Update:
It's not an Oracle problem, but a dba scheduling the wrong backup script. One that doesn't connect to the catalog!

Possibly Related Posts:

• Moving from ASM to a Filesystem