Oracle RMAN 11g: SQL queries against Catalog Database for backup info

With this blog I am sharing the queries that I use in my environment to gather details about database backup, backup sets. The queries can be executed on the target as well as catalog databases but there are difference in view name.

If you do not use a catalog database(which I highly recommend) and all information are storied in control file, you can use the SQL for target database to find the information. Note: Make sure the DB is in mount state.

For a newbie, 
Target: It is usually the PROD database or the database of which we need to take backup of.
Catalog: This is normal Oracle database where the backup metadata are stored. If you are not using catalog database, the same information can be storied in the Target's controlfile

In target, you can connect as sys as sysdba to run the SQL. In the Catalog Database, connect using the catalog schema.

Query the details for a single archive log:


Target :

select r.sequence#,r.set_stamp,p.handle,p.tag,p.start_time,p.completion_time,r.first_change#,r.next_change#
from V$BACKUP_PIECE p, V$BACKUP_REDOLOG r
where r.set_stamp = p.set_stamp
and r.set_count = p.set_count
and r.sequence# = 743399;

Here the archive log # that I am checking is 743399.

Catalog DB:

select r.sequence#,r.set_stamp,p.handle,p.tag,p.start_time,p.completion_time,r.first_change#,r.next_change#
from RC_BACKUP_PIECE p, RC_BACKUP_REDOLOG r
where r.set_stamp = p.set_stamp
and r.set_count = p.set_count
and r.sequence# = 743399;

Find last archive log backed up:

Target:

select r.sequence#,r.set_stamp,p.handle,p.tag,p.start_time,p.completion_time,r.first_change#,r.next_change#
from V$BACKUP_PIECE p, V$BACKUP_REDOLOG r
where r.set_stamp = p.set_stamp
and r.set_count = p.set_count
order by sequence# desc;

Catalog DB:

select r.sequence#,r.set_stamp,p.handle,p.tag,p.start_time,p.completion_time,r.first_change#,r.next_change#
from RC_BACKUP_PIECE p, RC_BACKUP_REDOLOG r
where r.set_stamp = p.set_stamp
and r.set_count = p.set_count
order by sequence# desc;

Backup Job Details:

Target:

select session_key,input_type,status, to_char(start_time,'yyyy-mm-dd hh24:mi') start_time,to_char(end_time,'yyyy-mm-dd hh24:mi') end_time,output_bytes_display,time_taken_display
from V$RMAN_BACKUP_JOB_DETAILS
order by session_key desc;


Catalog DB:

select session_key,input_type,status, to_char(start_time,'yyyy-mm-dd hh24:mi') start_time,to_char(end_time,'yyyy-mm-dd hh24:mi') end_time,output_bytes_display,time_taken_display
from RC_RMAN_BACKUP_JOB_DETAILS
order by session_key desc;


You can get all these information by standard RMAN commands like LIST, REPORT. All the commands have been tested in Oracle 11g (11.2.0.3).

No comments:

Post a Comment