Question:
I want to run custom queries against my RMAN catalog,
and I understand that the table names are rc_archived_log,
rc_backup_archivelog_details and rc_backup_archivelog_summary.
How do I create reports from the RMAN catalog, and
how can I write a custom report from the RMAN catalog?
Answer:
The RMAN recovery catalog comes with a full set of RMAN commands
that produce every conceivable report listing.
For example, these RMAN commands will generate RMAN catalog
reports:
rman> list backup;
rman> list
backup of database;
rman> list backup summary;
rman> list
incarnation;
rman> list backup by file;
rman> list copy of
database archivelog all;
rman> list copy of datafile 1, 2, 3;
rman>
list backup of datafile 11 summary;
rman> list backup of archivelog
from sequence 1234;
rman> list controlfilecopy
"/u01/app/oracle/ctrl1.cpy";
rman> list backupset of datafile 1;
You can also write custom queries against the
rc_archived_log, rc_backup_archivelog_details and
rc_backup_archivelog_summary tables:
select
session_key, db_name,
min(r.start_time) start_rman,
min(c.checkpoint_time) start_controlfile,
min(d.checkpoint_time)
start_datafile,
min(a.first_time) start_archivelog,
max(a.next_time) end_archivelog,
min(b.start_time)
start_set,
max(b.completion_time)
end_set, min(p.start_time)
start_piece, max(p.completion_time)
end_piece
from
See code
depot for full script
rman.rc_backup_controlfile_details c
join
rman.rc_backup_datafile_details d
on c.session_key = d.session_key
join
rman.rc_backup_archivelog_details a
on c.session_key =
a.session_key
join
rman.rc_backup_set_details b
on c.session_key = b.session_key
join
rman.rc_backup_piece_details p
on c.session_key =
p.session_key
join
rman.rc_rman_backup_job_details r
on c.session_key = r.session_key
where
db_key =
(select db_key
from rman.rc_database
where name = 'alice1')
and
c.checkpoint_time
>sysdate-14
and
d.checkpoint_time >sysdate-14
and
r.start_time >sysdate-14
and
a.first_time >sysdate-14
and
b.start_time
>sysdate-14
and
p.start_time >sysdate-14
and
a.next_time >sysdate-14
and
b.completion_time
>sysdate-14
and
p.completion_time >sysdate-14
group by
session_key,
db_name;
For more custom Oracle scripts and reports, see the
Oracle script
collection.
