Oracle Database Restoration Process: Step-by-Step Guide

Restoring an Oracle database is a crucial skill for Database Administrators (DBAs). This step-by-step guide will show you how to restore an Oracle database using RMAN (Recovery Manager), covering SPFILE, Control File, Data Files, and Archive Logs.

What is Oracle Database Restoration?

Database restoration is the process of recovering lost, corrupted, or dropped database components from a backup. This is essential for disaster recovery, ensuring minimal downtime and data integrity.

Essential Files Required for Restoration
Before starting, ensure you have a backup of these critical files:

✅ SP File (Server Parameter File)
✅ Control File
✅ Data Files
✅ Archive Log Files

⚠️ Pro Tip: Before taking a backup, always check the available free space in the backup location. If needed, manually delete old backups instead of using the DELETE BACKUP command, as it might remove all backups, including recent ones.

Step 1: Taking a Backup Using RMAN

Use the following RMAN script to take a full backup of the database:

RUN {
  ALLOCATE CHANNEL ch11 TYPE DISK MAXPIECESIZE 20G;
  ALLOCATE CHANNEL ch12 TYPE DISK MAXPIECESIZE 20G;
  ALLOCATE CHANNEL ch13 TYPE DISK MAXPIECESIZE 20G;

  BACKUP 
  FORMAT '/data/rman/%d_D_%T_%u_s%s_p%p'
  INCREMENTAL LEVEL 0 DATABASE
  CURRENT CONTROLFILE 
  FORMAT '/data/rman/%d_C_%T_%u'
  SPFILE 
  FORMAT '/data/rman/%d_S_%T_%u'
  PLUS ARCHIVELOG 
  FORMAT '/data/rman/%d_A_%T_%u_s%s_p%p';

  RELEASE CHANNEL ch11;
  RELEASE CHANNEL ch12;
  RELEASE CHANNEL ch13;
}

Understanding RMAN Backup File Naming

Each backup file has a piece handle, which indicates what type of backup was taken:

📌 Archive Log Backup:

piece handle=/data/rman/PRIM_A_20250325_7d3l7be4_s237_p1 tag=TAG20250325T071827

Here, _A_ indicates an Archive Log backup.

📌 Datafile Backup:

piece handle=/data/rman/PRIM_D_20250325_7o3l7bek_s248_p1 tag=TAG20250325T071837

Here, _D_ means Data Files are backed up.

📌 SPFILE & Control Files:
These are backed up automatically by RMAN.

✅ Best Practice: Always verify that backups are stored in a secure and accessible location before proceeding with restoration.

Step 2: Restoring an Oracle Database

1️⃣ Shutdown and Prepare for Restoration

Run the following commands to shutdown the database and prepare for restoration:

SHUTDOWN IMMEDIATE;

STARTUP MOUNT RESTRICT;

DROP DATABASE;

⚠️ Warning: Dropping the database will delete everything, including SPFILE, control file, data files, and archive logs.

Step 3: Restoring SP File (Server Parameter File)

If an SPFILE is not available, check if a PFILE exists. If not, create a minimal one:

db_name='prim'

Since the PFILE is already available, proceed with the restoration:

1️⃣ Start the database in NOMOUNT mode

STARTUP NOMOUNT PFILE='initprim.ora';

2️⃣ Restore the SP File

RESTORE SPFILE FROM '/data/rman/PRIM_S_20250325_7u3l7ben';

3️⃣ Restore the Control File

RESTORE CONTROLFILE FROM '/data/rman/PRIM_C_20250325_7t3l7ben';

4️⃣ Mount the Database

ALTER DATABASE MOUNT;

Step 4: Restoring the Database (Data Files & Archive Logs)

1️⃣ Catalog the backup location

CATALOG START WITH '/data/rman';

2️⃣ Restore the database

RESTORE DATABASE;

✅ Once restoration is complete, the following components are successfully restored:
✔️ SP File
✔️ Control File

✔️ Data Files

Step 5: Restoring Archived Logs

Once data files are restored, apply archived logs for media recovery:

📌 If archive logs are already available on disk, Oracle will automatically use them:

archived log file name=/data/oracle/oradata/PRIM/redo01.log thread=1 sequence=70

archived log file name=/data/oracle/oradata/PRIM/redo02.log thread=1 sequence=71

RECOVER DATABASE;

📌 If needed, manually restore archive logs:
RESTORE ARCHIVELOG ALL;

Step 6: Opening the Database

1️⃣ Open the database

ALTER DATABASE OPEN;

2️⃣ If SCN mismatch errors occur, open with RESETLOGS

ALTER DATABASE OPEN RESETLOGS;

Step 7: Verifying Pluggable Database (PDB) Status

📌 After opening the Container Database (CDB), check that all Pluggable Databases (PDBs) are mounted:

SELECT NAME, OPEN_MODE FROM V$PDBS;

If needed, manually open the Pluggable Database (PDB):

ALTER PLUGGABLE DATABASE pdbprim OPEN;

✅ At this stage, the database and all PDBs are fully restored, and you can access all data objects.

Final Thoughts & Best Practices

✅ Restoring an Oracle database using RMAN is a structured process that ensures data recovery in case of failures.

✅ This guide covered:

  • Taking RMAN backups of essential files

  • Step-by-step restoration of SPFILE, control file, data files, and archive logs

  • Opening and verifying the database after recovery

💡 Key Takeaways for DBAs:
🔥 Always keep multiple backups stored securely.
🔥 Regularly test the restoration process to ensure minimal downtime.
🔥 Automate RMAN backups for proactive disaster recovery.

🚀 Boost Your Oracle Expertise!

✅ Subscribe to our blog for more Oracle tips!
✅ Share this guide with fellow DBAs!

💬 Related Topics You Might Like


 

Comments

Post a Comment

Popular posts from this blog

Image
Oracle Export Metadata only And Import the Metadata only Guide – Step-by-Step Tutorial Scenario : Client is wanted to export and import the metadata In this guide, we will learn how to use expdp (Data Pump Export) and impdp (Data Pump Import) in Oracle Database to back up metadata and restore the same only. To create a backup, first we have to set up a physical backup location. Creating a Backup location: On Linux server: mkdir -p /data/backup On Oracle Database: Command: create a directory TEST_DIR2 as '/data/backup'; Here, TEST_DIR2 is a logical directory pointing to the physical backup location. To verify the directory: SELECT * FROM dba_directories WHERE directory_name = 'TEST_DIR2'; Exporting a Metadata using expdp: To backup the Metadata for Schema U2,  expdp schemas=U2 directory=TEST_DIR2 dumpfile=schdata_only2.dmp logfile=schdata_only.log content=METADATA_ONLY Note:   Dump File (.dump): A binary file generated by expdp that contains exported schema objects, data...
Read more
Image
How to Enable Archive Log Mode in Oracle (Switch to ARCHIVELOG Mode) If you want to disable archive log mode (switch to NOARCHIVELOG mode), follow these steps: 1. Check Current Archive Log Mode Before making changes, check if your database is in NO ARCHIVELOG mode: command -> select log_mode from v$database; The database in noarchivelog mode   If the output is NO ARCHIVELOG , it means archive logging is disabled. 2. Shutdown the Database You cannot switch to ARCHIVELOG mode while the database is running, so first shut it down: commnd -> shut immediate; 3. Start the Database in MOUNT Mode the database should be in mount stage to disable or enable it 4. Enable Archive Log Mode (Switch to ARCHIVELOG Mode) Run the following command: ALTER DATABASE ARCHIVELOG; 5. Open the Database Normally ALTER DATABASE OPEN; 6. Verify the Change Run this command again to confirm the mode: SELECT log_mode FROM v$database; Impact of Enabling Archive Log Mode ✅ Point-in-Time Recovery: With...
Read more
Image
Oracle Export Previous SCN Data & Import into an Existing Table – Step-by-Step Guide Scenario: In this guide, we will explore how to use Oracle Data Pump Export (expdp) and Import (impdp) to back up historical data from a previous SCN (System Change Number) and restore it into an existing table. This approach is useful when you need to retrieve older data while preserving the table structure. Step 1: Setting Up a Backup Location Before exporting data, we need to create a physical backup location where the dump files will be stored. On Linux server: mkdir -p /data/backup On Oracle Database: We create a logical directory pointing to the physical backup location: Command: create a directory TEST_DIR2 as '/data/backup'; TEST_DIR2 -->  Logical directory pointing to the physical backup location. To verify the directory: SELECT * FROM dba_directories WHERE directory_name = 'TEST_DIR2'; Step 2: Checking Current Table Data Inserted few more records Before exporting, ...
Read more