Sev1: Oracle controlfile or online redo log lost. Recovery paths that keep data loss to zero where possible

#First: stop and protect what's left

Before running any recovery command, copy every remaining datafile, controlfile, online redo log and archived log to a safe place (or take storage snapshots). Several of the steps below can't be undone.

#Part 1: controlfile problems

ORA-00205: error in identifying control file, check alert log for more info
ORA-00210: cannot open the specified control file

SHOW PARAMETER control_files

#Case A: one copy lost, another copy fine (multiplexed)

# instance down
cp /u03/oradata/PROD/control02.ctl /u02/oradata/PROD/control01.ctl

Or remove the bad path from control_files (in the spfile via CREATE PFILE FROM SPFILE, edit, CREATE SPFILE FROM PFILE) and start. No data loss.

#Case B: all copies lost, so restore from autobackup

rman target /
RMAN> STARTUP NOMOUNT;
RMAN> SET DBID 1234567890;                -- from backup logs / RMAN catalog / old alert log
RMAN> RESTORE CONTROLFILE FROM AUTOBACKUP;
RMAN> ALTER DATABASE MOUNT;
RMAN> RECOVER DATABASE;
RMAN> ALTER DATABASE OPEN RESETLOGS;

RECOVER DATABASE applies archived and the current online redo logs if they're intact, so this is usually complete recovery even though it ends with RESETLOGS. With a recovery catalog, SET DBID isn't needed.

After RESETLOGS, take a full backup straight away. Older backups belong to the previous incarnation.

#Part 2: online redo log problems

SELECT l.group#, l.status, l.archived, f.member, f.status AS member_status
FROM v$log l JOIN v$logfile f ON f.group# = l.group# ORDER BY l.group#;

#Case C: one member of a multiplexed group lost

The database keeps running, and the alert log shows errors for that member. Fix online:

ALTER DATABASE DROP LOGFILE MEMBER '/u02/oradata/PROD/redo02a.log';
ALTER DATABASE ADD LOGFILE MEMBER '/u02/oradata/PROD/redo02a.log' TO GROUP 2;

(You can't drop a member of the CURRENT group. Switch logfile first.)

#Case D: whole group lost, status INACTIVE

No data needed for crash recovery is in it.

ALTER DATABASE CLEAR LOGFILE GROUP 3;
-- if it wasn't archived yet:
ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 3;

After CLEAR UNARCHIVED, take a full backup: the archive chain has a gap.

#Case E: whole group lost, status ACTIVE or CURRENT

The changes in it may not be in the datafiles yet.

• ACTIVE: try ALTER SYSTEM CHECKPOINT; if the instance is still up. If that works, the group becomes INACTIVE and you can follow Case D.
• CURRENT with the instance crashed: changes in that log are lost unless a standby received them. With Data Guard, fail over to the standby (in SYNC mode it has everything).
• No standby: incomplete recovery to the last available change:

RMAN> STARTUP MOUNT;
RMAN> RUN {
  SET UNTIL SEQUENCE 4512 THREAD 1;     -- the sequence of the lost CURRENT log
  RESTORE DATABASE;
  RECOVER DATABASE;
}
RMAN> ALTER DATABASE OPEN RESETLOGS;

Caution

Incomplete recovery loses all committed transactions in the lost log. Agree it with the application owners, and record the recovery point so they can reconcile.

#Decision table

#Prevent it next time

• At least two controlfile copies on different storage, plus CONFIGURE CONTROLFILE AUTOBACKUP ON;.
• Two members per redo group on different disks.
• Data Guard for any database where losing the current redo is unacceptable.
• Record the DBID in the runbook.