All controlfiles are lost. Now what? Fortunately you have a last full backup plus all archivelogs since last backup...

All controlfiles are lost. Now what? Fortunately you have a last full backup plus all arAll controlfiles are lost. Now what? Fortunately you have the flashback on with full backup plus all archivelogs since last backup.......

A student called me this morning where he had lost all the controlfiles. Fortunately he had full RMAN backup with all the archivelogs since last backup. Lets create the scenario.

Take a full backup

Linux-69:(emrep)$ export ORACLE_SID=mdb

Linux-69:(mdb)$ rman target /

RMAN> backup database plus archivelogs;

Get the name and location of the controlfile

RMAN> list backup of controlfile;

.

..

Piece Name: /u99/mdb/backup/control_n_spfile_c-976010081-20101214-10

..

.

Generate some archivelogs by inserting some data into scott.t100 table.

Linux-69:(mdb)$ sqlplus / as sysdba

SQL> create table scott.t100 as select * from scott.emp;

SQL>

DECLARE

  v_count   number := 0;

BEGIN

  LOOP

  EXIT WHEN v_count > 15

  INSERT INTO scott.t100

       SELECT * FROM scott.t100;

  COMMIT;

  v_count := v_count + 1;

  END LOOP;

END;

/

Shutdown database

sqlplus / as sysdba

shutdown immediate;

Move all controlfiles to any backup location.

Linux-69:(mdb)$ mv /u02/oradata/mdb/control01.ctl /u99/mdb/controlfiles/.

Linux-69:(mdb)$ mv /u03/oradata/mdb/control02.ctl /u99/mdb/controlfiles/.

Linux-69:(mdb)$ mv /u04/oradata/mdb/control03.ctl /u99/mdb/controlfiles/.

Try starting the database

Linux-69:(mdb)$ sqlplus / as sysdba

SQL> Startup

and you will get an error like this.

ORA-00205: error in identifying control file, check alert log for more info

This is because we moved the controlfiles. And without controlfiles, database cannot be mounted.

Shutdown database:

shutdown immediate;

Recover the controlfile from the full RMAN backup taken earlier:

Linux-69:(mdb)$ rman target /

RMAN> startup nomount;

RMAN> restore controlfile from '/u99/mdb/backup/control_n_spfile_c-976010081-20101214-10';

Starting restore at 15-DEC-10

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=156 devtype=DISK

channel ORA_DISK_1: restoring control file

channel ORA_DISK_1: restore complete, elapsed time: 00:00:05

output filename=/u02/oradata/mdb/control01.ctl

output filename=/u03/oradata/mdb/control02.ctl

output filename=/u04/oradata/mdb/control03.ctl

Finished restore at 15-DEC-10

Mount and recover  the database:

while still in the rman utility,

RMAN> Alter database mount;

RMAN> recover database;

Open the database with resetlogs:

while still in the rman utility,

RMAN> Alter database open resetlogs;

Backup database:

Since we open the database with resetlogs, perform an immediate backup.

RMAN> backup database plus archivelog;

Verify scott.t100 existance

Verify the scott.t100 (which we created after the full backup was performed.) is available.

select count(*) from scott.t100.

Hope this helps someone in need someday.

--Moid Muhammad

chivelogs since last backup


...