RMAN Recovery Catalog in Oracle
4 min readRMAN recovery catalog is separate DB/ Server and which will save only the details of your backups in terms of metadata of the backup.
Whenever you trigger the backup, RMAN will Update the information in the control file. Your control files can store the backup details for the last 30 days only which is the default 30 days retention period.
Suppose you want to have the details of the backups which are taken before 30 days. The recovery catalog will help you in this case to store the history backup details.
When running backup, restore, and cross-checking commands, RMAN first updates the controlfile and then the metadata inside of the recovery catalog. This process of syncing data is known as “recovery catalog resynchronization.” This ensures that RMAN has the correct metadata from the controlfile.
Benefits of Recovery catalog:
- Helps in Database Recovery when you lose the control file.
- RMAN scripts can be stored in recovery catalog
- Store long term backup history
- Cloning with PITR (Point in time recovery) becomes easier as the recovery catalog knows which Control file is required
Recovery catalog configuration:
To create an RMAN catalog, you first need to create a user with the correct privileges. Connect to a new or existing database where the catalog will reside. You will then create a user and grant the user the RECOVERY_CATALOG_OWNER role. Starting with Oracle 12.1.0.2 the recovery catalog database must use Enterprise Edition. Your recovery catalog database needs to be the same version or higher than the database you are creating the catalog for.
Step # 1: Connect to Target database(Target DB: The database on which Backup & Recovery to be performed) as sysdba.
[oracle@ localhost ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Wed Oct 3 14:28:31 2018
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Step # 2: Ensure the database has been configured with ARCHIVELOG mode or not?
SQL> select log_mode from v$database;
LOG_MODE
------------
NOARCHIVELOG
Database is in NOARCHIVELOG mode.
Step # 3: If the database has been configured with ARCHIVELOG mode then skip the Step number 3 to 5, If not then Shutdown the database.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
Step # 4: Startup the database in mount state.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 308981760 bytes
Fixed Size 2212896 bytes
Variable Size 163580896 bytes
Database Buffers 138412032 bytes
Redo Buffers 4775936 bytes
Database mounted.
Step # 5: Configure database in ARCHIVELOG mode.
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ WRITE
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 2
Next log sequence to archive 4
Current log sequence 4
In case you wish to change default archive log destination then issue the following command.
SQL> alter system set log_archive_dest_1='location=/home/oracle/arch' scope=both;
System altered.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /home/oracle/arch
Oldest online log sequence 2
Next log sequence to archive 4
Current log sequence 4
Step # 6: Connect to RMAN prompt with target database.
[oracle@ localhost ~]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Wed Oct 3 15:00:23 2018
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: SHA (DBID=2156967212, not open)
RMAN>
Step # 7: Configure RMAN with controlfile auto-backup feature that will be auto-backup controlfile in case of major changes done in database.
RMAN> configure controlfile autobackup on;
Step # 8: To enable backup optimization run the following command, by default backup optimization has been configured OFF.
RMAN> configure backup optimization on;
Step # 9: Configure retention policy for backup.
RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
Step # 10: Connect to the recovery catalog database(RMAN Repository) & Create a tablespace to store RMAN catalog database objects.
SQL> create tablespace catalogtbs datafile '/home/oracle/dbfile/catalogtbs1.dbf' size 100M autoextend on maxsize unlimited;
Tablespace created.
Step # 11: Create a RMAN user, assign RMAN tablespace to RMAN user as a default & grant recovery catalog owner,connect & resource privileges to RMAN user.
SQL> create user catus identified by catus;
User created.
SQL> alter user catus default tablespace catalogtbs temporary tablespace temp;
User altered.
SQL> grant recovery_catalog_owner to catus;
Grant succeeded.
SQL> grant connect,resource to catus;
Grant succeeded.
Step # 12: Connect to RMAN on target and recovery catalog database.
[oracle@localhost~]$ rman target / catalog catus/catus@sha
Recovery Manager: Release 11.2.0.1.0 - Production on Wed Oct 3 15:11:53 2018
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: SHA (DBID=2156967212)
connected to recovery catalog databaseStep
RMAN>
Step # 13: create catalog by issuing the following command in RMAN prompt.
RMAN> create catalog;
recovery catalog created
Step # 14: After creating catalog, Ensure RMAN repository tables by logging into repository database as RMAN user. This is only for the first time.
[oracle@localhost ~]$ sqlplus "catus/catus@sha"
SQL> show user;
USER is "catus"
SQL> select table_name from user_tables;
Step # 15: Register database with recovery catalog.
RMAN> register database;
database registered in recovery catalog
starting full resync of recovery catalog
full resync complete
Step # 16: Check whether registration was successful.
RMAN> report schema;
Report of database schema for database with db_unique_name SHA
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 690 SYSTEM YES /u01/app/oracle/sha/system01.dbf
2 470 SYSAUX NO /u01/app/oracle/sha/sysaux01.dbf
3 40 UNDOTBS1 YES /u01/app/oracle/sha/undotbs01.dbf
4 5 USERS NO /u01/app/oracle/sha/users01.dbf
5 100 EXAMPLE NO /u01/app/oracle/sha/example01.dbf
6 250 TB1 NO /u01/app/oracle/catalog.dbf
7 100 CATALOGTBS NO /u01/app/oracle/catalogtbs1.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 20 TEMP 32767 /u01/app/oracle/sha/temp01.dbf
RMAN> LIST INCARNATION OF DATABASE;
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
2 22 SHA 2156967212 PARENT 1 15-AUG-09
2 4 SHA 2156967212 CURRENT 945184 01-OCT-18
Full resync complete means that your control files backup information and the recovery file backup information are updated.
