Tag Archives: Query RMAN CATALOG

CATALOG

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 ~]$


IV. Query RMAN CATALOG

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';


V. Get CATALOG Version

[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>

5.1
V(1)Resync catalog

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>


VII. DROP CATALOG

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>


VIII. Useful views

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 : +65-94693551
Linkedin: https://www.linkedin.com/in/rajasekhar-amudala/