CATALOG DATABASE
I. How to Create an RMAN Recovery Catalog Database
II. How to do manual-backup-registration
III. How to export the Catalog
IV. Query RMAN CATALOG
V. Get CATALOG Version
V(1). Resync catalog
VI. Unregister database from catalog db
VII. DROP CATALOG
VIII. Useful views
I. How to Create an RMAN Recovery Catalog Database
Create the rman user that will be the owner of the rman catalog, on the catalog database. The catalog database is usually a small database it contains and maintains the metadata of all rman backups performed using the catalog. Step 0: Environment Target Database: w148p, Version: 11.2.0.3.0 Catalog Database: cat, Version: 11.2.0.3.0 , catalog user: rman Step 1: Create user and grant privs SQL> select name,open_mode from v$database; NAME OPEN_MODE --------- -------------------- CAT READ WRITE SQL> CREATE USER rman IDENTIFIED BY cat TEMPORARY TABLESPACE temp DEFAULT TABLESPACE users QUOTA UNLIMITED ON users; 2 3 4 User created. SQL> GRANT RECOVERY_CATALOG_OWNER TO rman; Grant succeeded. SQL> Step 2: Create Recovery Catalog [oracle@rac2 ~]$ . oraenv ORACLE_SID = [cata] ? cat The Oracle base has been set to /u01/app/oracle [oracle@rac2 ~]$ rman catalog rman/cat Recovery Manager: Release 11.2.0.3.0 - Production on Wed Jul 20 12:38:59 2016 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to recovery catalog database RMAN> create catalog; recovery catalog created RMAN> exit Recovery Manager complete. [oracle@rac2 ~]$ Step 3: Register catalog database with target database [oracle@rac2 ~]$ rman target sys/sys@w148p catalog rman/cat@cat Recovery Manager: Release 11.2.0.3.0 - Production on Wed Jul 20 12:48:12 2016 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database: W148P (DBID=3175693255) connected to recovery catalog database RMAN> REGISTER DATABASE; database registered in recovery catalog starting full resync of recovery catalog full resync complete RMAN> Step 4: Verify registration [oracle@rac2 ~]$ rman target sys/sys@w148p catalog rman/cat@cat Recovery Manager: Release 11.2.0.3.0 - Production on Wed Jul 20 12:48:12 2016 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database: W148P (DBID=3175693255) connected to recovery catalog database RMAN> REPORT SCHEMA; Report of database schema for database with db_unique_name W148P List of Permanent Datafiles =========================== File Size(MB) Tablespace RB segs Datafile Name ---- -------- -------------------- ------- ------------------------ 1 700 SYSTEM YES +DATA/w148p/datafile/system.260.906753423 2 520 SYSAUX NO +DATA/w148p/datafile/sysaux.259.906753425 3 30 UNDOTBS1 YES +DATA/w148p/datafile/undotbs1.258.906753425 4 5 USERS NO +DATA/w148p/datafile/users.256.906753425 List of Temporary Files ======================= File Size(MB) Tablespace Maxsize(MB) Tempfile Name ---- -------- -------------------- ----------- -------------------- 1 20 TEMP 32767 +DATA/w148p/tempfile/temp.283.906753489 RMAN> exit Recovery Manager complete. [oracle@rac2 ~]$
II. How to do manual-backup-registration
Manual backups, made using Rman without a recovery catalog, can be cataloged and made available to the rman catalog.In this example a controlfile backup is made using rman in NOCATALOG mode, afterwards the backup is catalogued. Step 1: Simulate the issue: [oracle@rac2 ~]$ rman TARGET sys/sys@w148p NOCATALOG Recovery Manager: Release 11.2.0.3.0 - Production on Wed Jul 20 14:22:30 2016 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database: W148P (DBID=3175693255) using target database control file instead of recovery catalog RMAN> backup tablespace users; Starting backup at 20-JUL-16 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=40 device type=DISK channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00004 name=+DATA/w148p/datafile/users.256.906753425 channel ORA_DISK_1: starting piece 1 at 20-JUL-16 channel ORA_DISK_1: finished piece 1 at 20-JUL-16 piece handle=/u01/app/oracle/product/11.2.0/db_1/dbs/04rb65de_1_1 tag=TAG20160720T142238 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 20-JUL-16 RMAN> exit Recovery Manager complete. [oracle@rac2 ~]$ Step 2: Verify SQL> select name, open_mode from v$database; NAME OPEN_MODE --------- -------------------- CAT READ WRITE SQL> select DB_NAME, BS_KEY, BACKUP_TYPE, STATUS from rman.rc_backup_datafile; no rows selected SQL> Step 3: Manual registration [oracle@rac2 ~]$ rman target sys/sys@w148p catalog rman/cat@cat Recovery Manager: Release 11.2.0.3.0 - Production on Wed Jul 20 14:24:56 2016 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database: W148P (DBID=3175693255) connected to recovery catalog database RMAN> CATALOG backuppiece '/u01/app/oracle/product/11.2.0/db_1/dbs/04rb65de_1_1'; cataloged backup piece backup piece handle=/u01/app/oracle/product/11.2.0/db_1/dbs/04rb65de_1_1 RECID=7 STAMP=917706306 RMAN> list backup; List of Backup Sets =================== BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 105 Full 1.30M DISK 00:00:00 20-JUL-16 BP Key: 113 Status: AVAILABLE Compressed: NO Tag: TAG20160720T142238 Piece Name: /u01/app/oracle/product/11.2.0/db_1/dbs/04rb65de_1_1 List of Datafiles in backup set 105 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- --------- ---- 4 Full 1063017 20-JUL-16 +DATA/w148p/datafile/users.256.906753425 RMAN> Step 4: Verify after manual registration SQL> select name, open_mode from v$database; NAME OPEN_MODE --------- -------------------- CAT READ WRITE SQL> select DB_NAME, BS_KEY, BACKUP_TYPE, STATUS from rman.rc_backup_datafile; DB_NAME BS_KEY B S -------- ---------- - - W148P 105 D A SQL>
III. How to export the Catalog
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';
[oracle@rac2 ~]$ sqlplus rman/cat@cat SQL*Plus: Release 11.2.0.3.0 Production on Wed Jul 20 15:58:25 2016 Copyright (c) 1982, 2011, Oracle. 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 SQL> SELECT * FROM rcver; VERSION ------------ 11.02.00.03 SQL>
Step 1: Take backup without catalog [oracle@rac2 ~]$ rman TARGET sys/sys@w148p NOCATALOG Recovery Manager: Release 11.2.0.3.0 - Production on Wed Jul 20 17:11:41 2016 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database: W148P (DBID=3175693255) using target database control file instead of recovery catalog RMAN> backup spfile; Starting backup at 20-JUL-16 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=47 device type=DISK channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set including current SPFILE in backup set channel ORA_DISK_1: starting piece 1 at 20-JUL-16 channel ORA_DISK_1: finished piece 1 at 20-JUL-16 piece handle=/u01/app/oracle/product/11.2.0/db_1/dbs/07rb6fan_1_1 tag=TAG20160720T171151 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 20-JUL-16 RMAN> exit Recovery Manager complete. [oracle@rac2 ~]$ Step 2: Verify [oracle@rac2 ~]$ sqlplus rman/cat@cat SQL*Plus: Release 11.2.0.3.0 Production on Wed Jul 20 17:11:01 2016 Copyright (c) 1982, 2011, Oracle. 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 SQL> select count(*) from rc_backup_spfile; COUNT(*) ---------- 0 SQL> Step 3: Resync catalog [oracle@rac2 ~]$ rman target sys/sys@w148p catalog rman/cat@cat Recovery Manager: Release 11.2.0.3.0 - Production on Wed Jul 20 17:12:54 2016 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database: W148P (DBID=3175693255) connected to recovery catalog database RMAN> resync catalog; starting full resync of recovery catalog full resync complete <---- RMAN> exit Recovery Manager complete. [oracle@rac2 ~]$ Step 4: Verify after resync [oracle@rac2 ~]$ sqlplus rman/cat@cat SQL*Plus: Release 11.2.0.3.0 Production on Wed Jul 20 17:17:48 2016 Copyright (c) 1982, 2011, Oracle. 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 SQL> select count(*) from rc_backup_spfile; COUNT(*) ---------- 1 <---- SQL>
VI.Unregister database from catalog db
If a database is not longer required on the rman catalog it can be unregistered.Note that rman metadata is always stored also on the local controlfile, if a database is unregistered from the catalog, it can be afterwards registered again and all backups contained in the controlfile will be cataloged [oracle@rac2 ~]$ rman target sys/sys@w148p catalog rman/cat@cat Recovery Manager: Release 11.2.0.3.0 - Production on Wed Jul 20 16:27:19 2016 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database: W148P (DBID=3175693255) connected to recovery catalog database RMAN> UNREGISTER DATABASE; database name is "W148P" and DBID is 3175693255 Do you really want to unregister the database (enter YES or NO)? YES database unregistered from the recovery catalog RMAN> exit Recovery Manager complete. [oracle@rac2 ~]$ [oracle@rac2 ~]$ sqlplus rman/cat@cat SQL*Plus: Release 11.2.0.3.0 Production on Wed Jul 20 16:30:51 2016 Copyright (c) 1982, 2011, Oracle. 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 SQL> select NAME from rc_database; no rows selected SQL>
An rman catalog can be removed with the drop catalog command. [oracle@rac2 ~]$ rman catalog rman/cat@cat Recovery Manager: Release 11.2.0.3.0 - Production on Wed Jul 20 16:33:01 2016 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to recovery catalog database RMAN> DROP CATALOG; recovery catalog owner is RMAN enter DROP CATALOG command again to confirm catalog removal RMAN> DROP CATALOG; <--- recovery catalog dropped RMAN> exit Recovery Manager complete. [oracle@rac2 ~]$ [oracle@rac2 ~]$ sqlplus rman/cat@cat SQL*Plus: Release 11.2.0.3.0 Production on Wed Jul 20 16:39:19 2016 Copyright (c) 1982, 2011, Oracle. 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 SQL> select * from tab; no rows selected <-- SQL>
rc_archived_log: Historical information about archived and non archived redo logs (Corresponds to the v$archived_log view). rc_backup_controlfile: Contains information about control files in backup sets rc_backup_corruption: Contains list of corrupted blocs in datafile backups. Corresponds to the v$backup_corruption view. rc_database_block_corruption: Lists information about data blocks that were corrupted after last backup. rc_backup_datafile: Contains information about datafiles in backup sets. rc_backup_piece: Lists information about backup pieces. rc_backup_set: Contains information about backup sets of all incarnations of the database. rc_backup_spfile: Lists information about server parameter file in backup sets. rc_controlfile_copy: Lists information about controlfile copy in backup sets. rc_database: Contains information about all databases that registered in the recovery catalog. rc_database_incarnation: Lists information about all databases registered in the recovery catalog. rc_datafile: Lists information about all datafiles registered in the recovery catalog. rc_resync: Lists information about recovery catalog resynchronizations. rc_rman_configuration: Contains information about RMAN configuration settings. rc_rman_status: Contains information about all operations on all databases made by connecting to the recovery catalog. rc_stored_script and rc_stored_script_line: Both contain information about the scripts that stored in the recovery catalog.
Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.
Thank you,
Rajasekhar Amudala
Email: br8dba@gmail.com
WhatsApp : +
Linkedin: https://www.linkedin.com/in/rajasekhar-amudala/