🛡️ Oracle RMAN Backup and Recovery Guide

Step 1: Connect to RMAN

Initiate RMAN by connecting as a privileged user (usually sysdba):

rman target /

Note: Ensure your environment variables (ORACLE_SID, ORACLE_HOME) are correctly set before connecting.

Step 2: Database Backup

Create a comprehensive backup including the database and archived redo logs:

BACKUP AS COMPRESSED BACKUPSET DATABASE PLUS ARCHIVELOG;

Tip: The COMPRESSED BACKUPSET option reduces backup storage usage.

Step 3: Validate Backup Information

Confirm successful backup creation and contents:

LIST BACKUP SUMMARY;
LIST BACKUP OF DATABASE;
LIST BACKUP OF ARCHIVELOG ALL;

Step 3.1: Archive Log Sequence Verification

Check archive log sequences to facilitate point-in-time recovery:

LIST ARCHIVELOG ALL;

Alternatively, use SQL*Plus:

sqlplus / as sysdba
SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME, STATUS 
FROM V$ARCHIVED_LOG
ORDER BY SEQUENCE# DESC;

Step 4: Database Restoration Procedure

In case of database loss or corruption, follow these steps:

1. Start the instance in NOMOUNT mode:

   sqlplus / as sysdba
   STARTUP NOMOUNT;

2. Restore the control file (if lost):

   RESTORE CONTROLFILE FROM AUTOBACKUP;

3. Mount the database:

   ALTER DATABASE MOUNT;

4. Restore database data files:

   RESTORE DATABASE;

Step 5: Database Recovery

Apply archived redo logs to recover your database to the latest available state:

RECOVER DATABASE;

Step 6: Point-In-Time Recovery (Optional)

Specify exact recovery times for precise point-in-time recovery:

RUN {
  SET UNTIL TIME "TO_DATE('2025-03-10 14:30:00','YYYY-MM-DD HH24:MI:SS')";
  RESTORE DATABASE;
  RECOVER DATABASE;
}

Important: Ensure accuracy of the timestamp by reviewing archive log sequence information.

Step 7: Finalize Recovery and Open Database

After successful recovery, open your database with a resetlogs operation:

ALTER DATABASE OPEN RESETLOGS;

Note: Always perform a full database backup after resetlogs.

Step 8: Verify Database Status Post-Recovery

Confirm database availability and consistency post recovery:

SELECT OPEN_MODE, STATUS FROM V$DATABASE;

RMAN Best Practices & Recommendations

• Regularly test recovery processes to ensure reliability.
• Implement automated scheduled backups.
• Store backup files securely and consider remote/offsite backups.
• Maintain adequate archive log space and monitor usage to avoid disruptions.
• Keep RMAN catalog backups separate from primary database files for better disaster recovery.