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

I’m in the middle of running a migration of a number of 10.x databases to a new virtualized environment. During the course of this I ran across bug 7207932 (metalink login required). I summarized the situation in a mail to my coworkers as follows

XXXXX  is currently a database on ASM. It’s being moved to the new VM and hosted on a filesystem again. Restoring the database succeeded, but alter database open resetlogs failed with ORA-600 [KGEADE_IS_0]

This turns out to be the same as a bug when renaming a file ‘alter database rename file….’ in 10.2. Fortunately the filename really succeeds. In my case I’d forgotten about renaming the redo logs and got the error but manually renaming the files and then doing alter database open resetlogs from sqlplus succeeded. Anyway net result, if you are asked to restore (or duplicate or create a standby) an ASM hosted database to a filesystem in 10.2 expect the rename logfile step to “fail” with this error.

Possibly Related Posts:

• Recovery Catalog Views