Oracle DBA

Synopsis:
This runbook demonstrates a pragmatic, production-oriented procedure to restore a lost control file using RMAN controlfile autobackup in Oracle 19c. The guidance is actionable, includes a lab transcript, and is suitable for documentation, runbooks, or knowledge-base articles. Replace identifiers, paths and DBID values with values from your environment before executing.

---

Assumptions & Preconditions

• You have RMAN backups and controlfile autobackup configured (recommended).
• You have operating system access to the database server and can run sqlplus and rman.
• You have appropriate privileges (OS account and SYSDBA).
• You understand the implications of opening the database after recovery and will take backups afterward.
• This procedure restores the control file from an RMAN autobackup and recovers the database. It is safe to test in non-production first.

---

Executive summary (one-line):

If one or more control files are lost, restore the control file from RMAN autobackup (or manual backup), mount the database, perform media recovery, then OPEN RESETLOGS if required — finally take a fresh full backup.

---

Step-by-step procedure (lab transcript — copy/paste)

Replace ORACLE_SID, DBID, paths and filenames to reflect your environment.

1 — Verify RMAN configuration and controlfile autobackup

export ORACLE_SID=odba
rman target /

RMAN> SHOW ALL;

Sample relevant output (example):

CONFIGURE CONTROLFILE AUTOBACKUP ON; 
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F';
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/19.0.0/dbhome_1/dbs/snapcf_odba.f';

Confirm autobackup is enabled (CONTROLFILE AUTOBACKUP ON). If not, enable it in a maintenance window:

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;

---

2 — Simulate or observe the control file loss

(Example show of current control file locations)

export ORACLE_SID=odba
sqlplus / as sysdba

SQL> SELECT OPEN_MODE, NAME FROM V$DATABASE;
SQL> SELECT NAME FROM V$CONTROLFILE;

Example:

/u01/app/oracle/oradata/ODBA/controlfile/o1_mf_g91q1y86_.ctl
/u01/app/oracle/fast_recovery_area/ODBA/controlfile/o1_mf_g91q1yow_.ctl

If the control files are deleted at OS level (lab only), you will begin seeing ORA-00210 / ORA-00202 errors.

Lab deletion example (DO NOT run on production):

cd /u01/app/oracle/oradata/ODBA/controlfile/
rm o1_mf_g91q1y86_.ctl
cd /u01/app/oracle/fast_recovery_area/ODBA/controlfile/
rm o1_mf_g91q1yow_.ctl

Attempting DDL or shutdown may produce:

ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/oradata/ODBA/controlfile/o1_mf_g91q1y86_.ctl'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory

---

3 — Place the instance in NOMOUNT

Start the instance in NOMOUNT so RMAN can restore the controlfile:

sqlplus / as sysdba
SQL> STARTUP NOMOUNT;

Sample output:

ORACLE instance started.
Total System Global Area ...
Database mounted.   -- (No: actually NOMOUNT — instance started and not mounted)

---

4 — Launch RMAN and set DBID (if necessary)

If the control file is lost and the instance is NOMOUNT, RMAN may not be able to identify the DBID automatically. If you know the DBID, set it:

rman target /

RMAN> SET DBID=1337475478;    -- use your DBID

If you do not know the DBID and you have a controlfile autobackup on disk, you may be able to let RMAN search (but SET DBID is reliable).

---

5 — Restore controlfile from autobackup

RMAN> RESTORE CONTROLFILE FROM AUTOBACKUP;

Sample RMAN output (abridged):

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20190516
channel ORA_DISK_1: AUTOBACKUP found: c-1337475478-20190516-06
channel ORA_DISK_1: restoring control file from AUTOBACKUP c-1337475478-20190516-06
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=/u01/app/oracle/oradata/ODBA/controlfile/o1_mf_g91q1y86_.ctl
output file name=/u01/app/oracle/fast_recovery_area/ODBA/controlfile/o1_mf_g91q1yow_.ctl
Finished restore at 16-MAY-19

If RMAN cannot find an autobackup on the local FRA, ensure the autobackup piece is available (copy from tape/remote location) or specify FROM 'path' if you have the file.

---

6 — Mount the database and perform recovery

After control file restore:

RMAN> ALTER DATABASE MOUNT;

RMAN> RECOVER DATABASE;

Sample output:

Starting media recovery
archived log for thread 1 with sequence 1 is already on disk as file /u01/app/oracle/oradata/ODBA/onlinelog/o1_mf_1_gftzchjx_.log
media recovery complete
Finished recover at 16-MAY-19

If RMAN requests missing archived logs, provide them (copy them into FRA or restore from tape) and re-run RECOVER.

---

7 — Open the database

If recovery was complete and you applied all redo to the desired point, open normally:

ALTER DATABASE OPEN;

If the recovery was incomplete (you used UNTIL TIME/SCN/SEQUENCE or could not apply all redo), open with RESETLOGS:

ALTER DATABASE OPEN RESETLOGS;

Sample (lab) command:

RMAN> ALTER DATABASE OPEN RESETLOGS;

---

8 — Post-recovery actions (mandatory)

1. Immediate full backup — after OPEN RESETLOGS take a fresh baseline backup:

RMAN> BACKUP DATABASE PLUS ARCHIVELOG;

2. Validate controlfile autobackup — confirm autobackup configuration and consider taking an explicit controlfile backup:

RMAN> SHOW ALL;
RMAN> BACKUP AS COPY CURRENT CONTROLFILE;

3. Document the recovery: record the incident, root cause, and remedial steps in your runbook and change management system.

---

Explanation & Rationale

• RMAN controlfile autobackup stores a copy of the control file (and server parameter file when configured) that RMAN can restore automatically. This is the most reliable method to recover from a lost or corrupted control file.
• Starting the instance NOMOUNT allows RMAN to write a restored control file to disk so Oracle can mount the database and proceed to media recovery.
• If the control file is restored from an autobackup and recovery is required, RECOVER DATABASE applies archived redo to make datafiles consistent with the restored control file.
• OPEN RESETLOGS is necessary after incomplete recovery or when the restored control file represents a different incarnation — this resets the online redo stream and starts a new incarnation. Immediately take a full backup after RESETLOGS to establish a new recovery baseline.

---

Troubleshooting & Caveats

• RMAN cannot find autobackup: ensure CONTROLFILE AUTOBACKUP was ON and the autobackup piece exists in the configured FRA or backup destination. Locate the autobackup piece on tape or another host and restore it to the FRA path.
• Unknown DBID: If the instance is NOMOUNT and you do not know DBID, you can:
  • Check RMAN catalog (if using one).
  • Retrieve DBID from backup labels, or saved scripts.
  • Use strings on backup pieces to identify DBID (advanced).
• Missing archived logs: If RECOVER fails due to missing archived logs, locate and restore the archives from secondary storage. If logs cannot be found and you must proceed, evaluate acceptance of data loss and document the decision — OPEN RESETLOGS after incomplete recovery will create a new incarnation.
• Multiple control file locations: Ensure all configured control file locations in init.ora/spfile are valid. After restore, verify V$CONTROLFILE paths and adjust files as needed.
• Controlfile contains wrong paths: If the restored control file contains file paths valid for another server (e.g., during restore to a clone), edit initialization parameters or use CATALOG / CHANGE RMAN commands to reconcile.

---

Best Practices & Preventive Measures

• CONFIGURE CONTROLFILE AUTOBACKUP ON is a must for reliable recovery.
• Keep frequent offline control file copies in a secure secondary location in addition to autobackup.
• Regularly test control file restore procedures in a DR or non-production environment.
• Ensure Fast Recovery Area (FRA) is adequately sized and archived logs are protected (secondary copy/tape/cloud).
• Maintain an RMAN catalog or a documented record of DBID and critical RMAN information.
• Automate periodic validation: RMAN> BACKUP VALIDATE DATABASE; and RMAN> LIST BACKUP OF CONTROLFILE;.