Scenario: Control Files and SPFILE Lost in Oracle RAC

This blog explains how to recover an Oracle 19c RAC database when:

• ❌ All Control Files are lost
• ❌ SPFILE is lost
• ✅ Datafiles are safe
• ✅ RMAN autobackup is available

👉 Storage type: File System (NOT ASM)

Environment (Example)

Recovery Objective

✔ Restore SPFILE

✔ Restore Control Files

✔ Recover database

✔ Start all RAC instances

STEP-BY-STEP EASY RECOVERY

STEP 1: Stop All RAC Instances

srvctl stop database -d mydb -o abort

Check status:

srvctl status database -d mydb

📌 Database should show STOPPED on all nodes.

STEP 2: Create a Temporary PFILE

Create a simple PFILE:

vi /tmp/initmydb.ora

Add minimum required parameters:

db_name=mydb
memory_target=2G

Save and exit.

STEP 3: Start One Instance in NOMOUNT

Login to SQL*Plus on any one node:

startup nomount pfile='/tmp/initmydb.ora';

📌 Only one instance should be started.

STEP 4: Restore SPFILE from RMAN Autobackup

Connect RMAN:

rman target /
set dbid=3132869757;
restore spfile to '/tmp/spfile_mydb.ora' from autobackup;

STEP 5: Restart Database Using SPFILE

Shutdown database after that startup at nomount state

shutdown immediate;
Startup nomount;

STEP 6: Restore Control Files and Recover Database

-- Restore control files:
restore controlfile from autobackup;
-- Mount database:
alter database mount;
-- Recover database:
recover database;
-- Open database:
alter database open resetlogs;

STEP 7: Copy SPFILE to Shared Location

-- Copy SPFILE to shared directory (used by RAC):
cp /tmp/spfile_mydb.ora /oradata/mydb/
-- Update RAC configuration:
srvctl modify database -d mydb -spfile /oradata/mydb/spfile_mydb.ora
-- Verify:
srvctl config database -d mydb

STEP 8: Start Remaining RAC Instance

-- Check current status
srvctl status database -d mydb
-- Start second instance:
srvctl start instance -d mydb -i mydb2
--Final check:
srvctl status database -d mydb

✅ Both RAC instances are RUNNING.

Key Notes (Very Important)

• ✔ Restore SPFILE first, then control files
• ✔ Use only one instance during recovery
• ✔ RESETLOGS is mandatory
• ✔ SPFILE must be on shared storage
• ✔ Datafiles were not restored (already intact)

Conclusion

This is a real-time Oracle RAC recovery scenario used in production.
With RMAN autobackup, even complete loss of control files and SPFILE can be recovered safely.