The database holding the rman catalog should be protected against information loss, the minimum level of protection can be implemented by executing a full export of the catalog schema. In case of catalog loss an import of the schema on a new or existing database will make the catalog available again.
[oracle@rac2 ~]$
expdp \" / as sysdba\" directory=DATA_PUMP_DIR schemas=rman dumpfile=rman_schemas.dmp logfile=rman_schemas.log
Export: Release 11.2.0.3.0 - Production on Wed Jul 20 14:49:58 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_SCHEMA_01": "/******** AS SYSDBA" directory=DATA_PUMP_DIR schemas=rman dumpfile=rman_schemas.dmp logfile=rman_schemas.log
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 1.562 MB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/SEQUENCE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/PACKAGE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/FUNCTION/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/VIEW/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
Processing object type SCHEMA_EXPORT/TYPE/TYPE_BODY
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
. . exported "RMAN"."CFS" 89.91 KB 157 rows
. . exported "RMAN"."AL" 15.19 KB 3 rows
. . exported "RMAN"."BDF" 16.41 KB 1 rows
. . exported "RMAN"."BP" 14.84 KB 1 rows
. . exported "RMAN"."BS" 12.32 KB 1 rows
. . exported "RMAN"."CKP" 8.710 KB 2 rows
. . exported "RMAN"."CONFIG" 5.429 KB 1 rows
. . exported "RMAN"."DB" 5.820 KB 1 rows
. . exported "RMAN"."DBINC" 7.484 KB 2 rows
. . exported "RMAN"."DF" 14.95 KB 4 rows
. . exported "RMAN"."NODE" 20.60 KB 1 rows
. . exported "RMAN"."OFFR" 8.789 KB 4 rows
. . exported "RMAN"."ORL" 7.593 KB 3 rows
. . exported "RMAN"."RCVER" 5.023 KB 1 rows
. . exported "RMAN"."RLH" 8.726 KB 3 rows
. . exported "RMAN"."ROUT" 25.75 KB 324 rows
. . exported "RMAN"."RSR" 17.61 KB 32 rows
. . exported "RMAN"."RT" 7.828 KB 1 rows
. . exported "RMAN"."SITE_DFATT" 6.015 KB 4 rows
. . exported "RMAN"."SITE_TFATT" 8.265 KB 1 rows
. . exported "RMAN"."TF" 8.632 KB 1 rows
. . exported "RMAN"."TS" 9.640 KB 5 rows
. . exported "RMAN"."TSATT" 7.515 KB 5 rows
. . exported "RMAN"."BCB" 0 KB 0 rows
. . exported "RMAN"."BCF" 0 KB 0 rows
. . exported "RMAN"."BCR" 0 KB 0 rows
. . exported "RMAN"."BRL" 0 KB 0 rows
. . exported "RMAN"."BSF" 0 KB 0 rows
. . exported "RMAN"."CCB" 0 KB 0 rows
. . exported "RMAN"."CCF" 0 KB 0 rows
. . exported "RMAN"."CDF" 0 KB 0 rows
. . exported "RMAN"."CONF" 0 KB 0 rows
. . exported "RMAN"."FB" 0 KB 0 rows
. . exported "RMAN"."GRSP" 0 KB 0 rows
. . exported "RMAN"."NRSP" 0 KB 0 rows
. . exported "RMAN"."RR" 0 KB 0 rows
. . exported "RMAN"."SCR" 0 KB 0 rows
. . exported "RMAN"."SCRL" 0 KB 0 rows
. . exported "RMAN"."TEMPRES" 0 KB 0 rows
. . exported "RMAN"."VPC_DATABASES" 0 KB 0 rows
. . exported "RMAN"."VPC_USERS" 0 KB 0 rows
. . exported "RMAN"."XAL" 0 KB 0 rows
. . exported "RMAN"."XCF" 0 KB 0 rows
. . exported "RMAN"."XDF" 0 KB 0 rows
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
/u01/app/oracle/admin/cat/dpdump/rman_schemas.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at 14:50:32
[oracle@rac2 ~]$
Scrip 1:
col OBJECT_TYPE for a20
col status for a15
set lines 180
select db_name, object_type, status,
round((end_time - start_time) * 24 * 60, 2) duration_minutes,
to_char(start_time, 'mm/dd/yyyy hh:mi:ss') start_time,
to_char(end_time, 'mm/dd/yyyy hh:mi:ss') end_time,
round((input_bytes/(1024*1024*1024)),2) input_gb,
round((output_bytes/(1024*1024*1024)),2) output_gb
from rc_rman_status
where operation = 'BACKUP';
Scrip 2:
Run below query on target database. <--
on Target database
sqlplus sys/sys@w148p as sysdba
SELECT DBID FROM v$database;
Run below two commands on Catalog database.
SELECT DB_KEY FROM RC_DATABASE WHERE DBID='&DBID';
SELECT DBINC_KEY,BS_KEY, BACKUP_TYPE, COMPLETION_TIME
FROM RC_DATABASE_INCARNATION i, RC_BACKUP_SET b
WHERE i.DB_KEY='&DB_KEY'
AND i.DB_KEY = b.DB_KEY ;
Script 3: Run as catalog user
This script will query the RMAN catalog and report on the backup status of every database in the catalog.
set lines 80
set pages 250
ttitle "Daily Backup........"
select DB NAME,dbid,
NVL(TO_CHAR(max(backuptype_db),'DD/MM/YYYY HH24:MI'),'01/01/0001:00:00') DBBKP,
NVL(TO_CHAR(max(backuptype_arch),'DD/MM/YYYY HH24:MI'),'01/01/0001:00:00') ARCBKP
from (
select a.name DB,dbid,
decode(b.bck_type,'D',max(b.completion_time),'I',
max(b.completion_time)) BACKUPTYPE_db,
decode(b.bck_type,'L',
max(b.completion_time)) BACKUPTYPE_arch
from rc_database a,bs b
where a.db_key=b.db_key
and b.bck_type is not null
and b.bs_key not in(Select bs_key from rc_backup_controlfile
where AUTOBACKUP_DATE is not null or AUTOBACKUP_SEQUENCE is not null)
and b.bs_key not in(select bs_key from rc_backup_spfile)
group by a.name,dbid,b.bck_type
) group by db,dbid
ORDER BY least(to_date(DBBKP,'DD/MM/YYYY HH24:MI'),
to_date(ARCBKP,'DD/MM/YYYY HH24:MI'));
Script 4: No catalog
RMAN No catalog - get backup information from local controlfile
col OBJECT_TYPE for a20
col status for a15
select sid, object_type, status,
round((end_time - start_time) * 24 * 60, 2) duration_minutes,
to_char(start_time, 'mm/dd/yyyy hh:mi:ss') start_time,
to_char(end_time, 'mm/dd/yyyy hh:mi:ss') end_time,
round((input_bytes/(1024*1024*1024)),2) input_gb,
round((output_bytes/(1024*1024*1024)),2) output_gb
from v$rman_status
where operation = 'BACKUP';