Setting Up an RMAN Recovery Catalog Database in Oracle

Oracle's Recovery Manager (RMAN) Recovery Catalog is a centralized storage system that keeps metadata about database backups, archived redo logs, and schema changes. Unlike the control file, which holds backup information locally, the Recovery Catalog maintains a long-term history of backup operations across multiple databases. This makes backup and recovery management more efficient, particularly in enterprise environments with multiple databases.

Prerequisites

Before proceeding with the setup, ensure the following conditions are met:

• An Existing Oracle Database: A running Oracle database that will store the Recovery Catalog.

• Sufficient Storage Space: Ensure enough disk space is available to hold backup metadata and archived logs.

• Database User Permissions: A dedicated user must be created with the necessary privileges to store RMAN metadata.

• Listener Configuration: The Oracle Net Listener should be properly set up and running to enable database connections.

Step 1: Start the Database and Listener

1.1 Start the Database

Log in to SQL*Plus as a privileged user and start the database:

sqlplus / as sysdba

STARTUP;

1.2 Start the Listener

Ensure the Oracle Net Listener is running:

lsnrctl start

Step 2: Create the Recovery Catalog Schema

2.1 Connect to SQL*Plus

sqlplus / as sysdba

2.2 Create a Dedicated Tablespace

Allocate a tablespace specifically for the Recovery Catalog:

CREATE TABLESPACE rcat_tbs DATAFILE '/home/oracle/oradata/orcl/rcat01.dbf' SIZE 500M AUTOEXTEND ON;

2.3 Create a User for the Recovery Catalog

Set up a new user that will store RMAN metadata:

CREATE USER rman IDENTIFIED BY rman_password DEFAULT TABLESPACE rcat_tbs QUOTA UNLIMITED ON rcat_tbs;

2.4 Grant Required Privileges

GRANT CONNECT, RESOURCE, RECOVERY_CATALOG_OWNER TO rman;

Step 3: Initialize the RMAN Recovery Catalog

3.1 Connect to RMAN as the Catalog User

rman catalog rman/rman_password@orcl

3.2 Create the RMAN Catalog Schema

CREATE CATALOG;

This command initializes the necessary tables inside the Recovery Catalog schema.

3.3 Exit RMAN

EXIT;

Step 4: Register the Target Database with the Recovery Catalog

4.1 Connect to RMAN with Both Target and Catalog Databases

rman target / catalog rman/rman_password@orcl

4.2 Register the Database

REGISTER DATABASE;

This process adds the target database's metadata to the Recovery Catalog.

4.3 Exit RMAN

EXIT;

Step 5: Configure RMAN Backup Policies

5.1 Connect to RMAN

rman target / catalog rman/rman_password@orcl

5.2 Define Backup Retention Policies

Make sure to CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;

5.3 Enable Control File Autobackup

CONFIGURE CONTROLFILE AUTOBACKUP ON;

5.4 Verify RMAN Configuration Settings

SHOW ALL;

5.5 Exit RMAN

EXIT;

Step 6: Perform a Backup Using the Recovery Catalog

6.1 Connect to RMAN

rman target / catalog rman/rman_password@orcl

6.2 Execute a Full Database Backup

BACKUP DATABASE PLUS ARCHIVELOG;

6.3 Exit RMAN

EXIT;

Step 7: Verify the Recovery Catalog

7.1 Connect to RMAN

rman target / catalog rman/rman_password@orcl

7.2 List Registered Databases

LIST INCARNATION;

7.3 View Backup Summary

LIST BACKUP SUMMARY;

7.4 Check Stored Schema Information

REPORT SCHEMA;

7.5 Exit RMAN

EXIT;

Optional: Ongoing Maintenance of the Recovery Catalog

To ensure the Recovery Catalog remains up to date and optimized, periodic maintenance is recommended:

• Sync the Catalog with the Target Database

• RESYNC CATALOG;

• Remove Unused Database Entries

• UNREGISTER DATABASE;

• Delete Outdated Backup Metadata

• DELETE OBSOLETE;

• Cross-Check Backup Entries and Remove Expired Ones

CROSSCHECK BACKUP;

DELETE EXPIRED BACKUP;

Optional: Disaster Recovery Testing

In case of database failure, follow these steps to restore and recover the database using RMAN:

Restore and Recover the Entire Database

RESTORE DATABASE;

RECOVER DATABASE;

Restore and Mount the Control File (If Needed)

RESTORE CONTROLFILE FROM AUTOBACKUP;

ALTER DATABASE MOUNT;

RECOVER DATABASE;

Open the Database After Recovery

ALTER DATABASE OPEN RESETLOGS;

Conclusion

Setting up an RMAN Recovery Catalog provides a centralized backup management strategy, allowing for better backup retention, easier recovery, and improved disaster preparedness. By following these steps, you ensure a structured approach to handling RMAN backups across multiple databases.

Regular maintenance, backup verification, and periodic disaster recovery testing are essential for ensuring data integrity and availability in any Oracle environment. 🚀