Solution: Standard Operating Procedure: Oracle 11g Database Restore

Problem:

Restore the database from RMAN backup files.

Database Name: OPERA

Database Version: Oracle 11g

Phase 1: Service Management & Environment Preparation

1. Stop Application and Database Services

Before beginning the restore, ensure all connections are severed and services are stopped to prevent file locks.

• Stop WebLogic Services.
• Stop the Oracle Database Service (e.g., OracleServiceOPERA).
• Stop the Oracle Listener (e.g., OracleOraDb11g_home1TNSListener).

2. Archive Old Data Directories

Move the existing data files to a backup folder to prepare a clean slate for the restoration.

• Primary Data Location (D Drive):
  1. Rename existing folder: D:\ORACLE\oradata\opera $\rightarrow$ D:\ORACLE\oradata\opera_old
  2. Create a new, empty folder: D:\ORACLE\oradata\opera

• Secondary Data Location (C Drive):
  1. Rename existing folder: C:\oracle\oradata\opera $\rightarrow$ C:\oracle\oradata\opera_old
  2. Create a new, empty folder: C:\oracle\oradata\opera

3. Start Database Services

Start the Windows services to allow RMAN to connect. Note: The database instance will likely be in a NOMOUNT or started state, but not fully open since the data files are missing.

• Start the Oracle Listener.
• Start the Oracle Database Service (OracleServiceOPERA).

Phase 2: RMAN Restoration Process

Open a command prompt (CMD) as Administrator and execute the following steps.

4. Connect to RMAN

Connect to the target database using the SYS user.

Bash

rman target sys/Oracle11g@opera

Note: Ensure the database instance is in NOMOUNT state if the control file is missing. If the service started it but the instance is down, you may need to run startup nomount; inside RMAN before proceeding.

5. Restore the Control File

Restore the control file from your specific backup location.

• Replace [PATH_TO_CF_FILE] with the actual full path of the .CF file in your backup folder.

SQL

RESTORE CONTROLFILE FROM '[PATH_TO_CF_FILE]';

• After restoring the control file, mount the database:

SQL

ALTER DATABASE MOUNT;

6. Catalog the Backup Files

Register the backup files with the RMAN repository so the database knows where to find them.

• Replace [PATH_TO_BACKUP_FOLDER] with the directory containing your backup pieces.

SQL

CATALOG START WITH '[PATH_TO_BACKUP_FOLDER]';

Type YES when prompted to confirm the cataloging of files.

7. Restore the Database

This command restores the datafiles from the backup to the original location.

SQL

RESTORE DATABASE;

8. Recover the Database

Apply redo logs to bring the database to a consistent state. Choose Option A (Complete Recovery) or Option B (Point-in-Time Recovery) based on your requirements.

• Option A: Standard Recovery

SQL

RECOVER DATABASE;

• Option B: Point-in-Time Recovery (Until Sequence)

Use this if the standard recovery fails requesting a specific log sequence, or if you need to stop before a specific transaction. Replace [SEQUENCE_NUMBER] with the last sequence number mentioned in the error message.

SQL

RECOVER DATABASE UNTIL SEQUENCE [SEQUENCE_NUMBER];

Phase 3: Finalization

9. Open the Database

Because recovery was performed (and potentially incomplete recovery used), you must open the database with the RESETLOGS option. This resets the log sequence numbers.

SQL

ALTER DATABASE OPEN RESETLOGS;

10. Post-Restore Checks

• Verify the database status is OPEN.
• Start the WebLogic services.
• Perform a quick health check or sanity test on the application.