Exercise - Using the control file autobackup to recover from the lost of all control files - Oracle 10g

Environment: Oracle 10gR2

Summary of the testing steps

1. First, configure RMAN to perform a control file autobackup:
2. Next, perform a backup with the control file autobackup enabled:
3. Next, simulate the missing control files by deleting all the control files.
4. Next, start the database in NOMOUNT mode, which is required because there is no control file to mount.
5. Next, connect to RMAN and the target database. Specify the DBID to identify the database you are connecting to
6. Next, restore the control file from backup:
7. Next, mount the database and begin to recover the database:
8. Finally, open the database with RESETLOGS option for normal operations:

Detailed steps:

1. First, configure RMAN to perform a control file autobackup:

RMAN> connect target
connected to target database: TOY10G (DBID=3330944552)
RMAN> configure controlfile autobackup on;
using target database control file instead of recovery catalog

old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;

new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;

new RMAN configuration parameters are successfully stored

RMAN>
RMAN> show all;
RMAN>

RMAN configuration parameters are:

CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/ora01/orabkup/GENQ/.temp_toy10g/oracle/admin/TOY10G/backup/test_%t_%s_%p.dbf';
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/ora01/orabkup/GENQ/.temp_toy10g/oracle/admin/TOY10G/backup/snapcf_TOY10G.f';


RMAN>

2. Next, perform a backup with the control file autobackup enabled:


RMAN> run
2> {
3> backup database;
4> backup (archivelog all);
5> }

Starting backup at 06-JUL-2007 14:19:13

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=35 devtype=DISK

channel ORA_DISK_1: starting full datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

input datafile fno=00001 name=/ora01/orabkup/GENQ/.temp_toy10g/ora01/oradata/TOY10G/system01.dbf

input datafile fno=00003 name=/ora01/orabkup/GENQ/.temp_toy10g/ora01/oradata/TOY10G/sysaux01.dbf

input datafile fno=00004 name=/ora01/orabkup/GENQ/.temp_toy10g/ora01/oradata/TOY10G/users01.dbf

input datafile fno=00005 name=/ora01/orabkup/GENQ/.temp_toy10g/ora01/oradata/TOY10G/indx01.dbf

input datafile fno=00002 name=/ora01/orabkup/GENQ/.temp_toy10g/ora01/oradata/TOY10G/undotbs01.dbf

channel ORA_DISK_1: starting piece 1 at 06-JUL-2007 14:19:15

channel ORA_DISK_1: finished piece 1 at 06-JUL-2007 14:19:22

piece handle=/ora01/orabkup/GENQ/.temp_toy10g/oracle/admin/TOY10G/backup/test_627229154_17_1.dbf tag=TAG20070706T141914 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:08
Finished backup at 06-JUL-2007 14:19:22
Starting backup at 06-JUL-2007 14:19:25
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=478 recid=1 stamp=627229166
channel ORA_DISK_1: starting piece 1 at 06-JUL-2007 14:19:27
channel ORA_DISK_1: finished piece 1 at 06-JUL-2007 14:19:28
piece handle=/ora01/orabkup/GENQ/.temp_toy10g/oracle/admin/TOY10G/backup/test_627229166_18_1.dbf tag=TAG20070706T141926 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 06-JUL-2007 14:19:28
Starting Control File Autobackup at 06-JUL-2007 14:19:28
piece handle=/ora01/orabkup/GENQ/.temp_toy10g/ora01/orarcv/TOY10G/TOY10G/autobackup/2007_07_06/o1_mf_n_627229168_38x5g116_.bkp comment=NONE
Finished Control File Autobackup at 06-JUL-2007 14:19:30

RMAN>

3. Next, simulate the missing control files by deleting all the control files. (The database will need to be shut down to perform this simulated failure.)

sys@TOY10G> select name from v$controlfile;


NAME
----------------------------------------------------------------------------------------------------
/ora01/orabkup/GENQ/.temp_toy10g/ora01/oradata/TOY10G/control.001.dbf
/ora01/orabkup/GENQ/.temp_toy10g/ora01/oradata/TOY10G/control.002.dbf
/ora01/orabkup/GENQ/.temp_toy10g/ora01/oradata/TOY10G/control.003.dbf


after shutdown, do:

rm /ora01/orabkup/GENQ/.temp_toy10g/ora01/oradata/TOY10G/control.001.dbf
rm /ora01/orabkup/GENQ/.temp_toy10g/ora01/oradata/TOY10G/control.002.dbf
rm /ora01/orabkup/GENQ/.temp_toy10g/ora01/oradata/TOY10G/control.003.dbf


4. Next, start the database in NOMOUNT mode, which is required because there is no control file to mount:


idle> conn / as sysdba

Connected to an idle instance.

idle> startup nomount pfile='/ora01/orabkup/GENQ/.temp_toy10g/oracle/admin/TOY10G/pfile/initTOY10G.ora';

ORACLE instance started.


5. Next, connect to RMAN and the target database. Specify the DBID to identify the database you are connecting to, because the control file contains this information and failure causes the control file to be unavailable.

RMAN> connect target

connected to target database: TOY10G (not mounted)

RMAN> set dbid 3330944552

executing command: SET DBID

RMAN>

6. Next, restore the control file from backup:

RMAN> restore controlfile from autobackup;
Starting restore at 06-JUL-2007 14:35:11

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=47 devtype=DISK


recovery area destination: /ora01/orabkup/GENQ/.temp_toy10g/ora01/orarcv/TOY10G

database name (or database unique name) used for search: TOY10G

channel ORA_DISK_1: autobackup found in the recovery area

channel ORA_DISK_1: autobackup found: /ora01/orabkup/GENQ/.temp_toy10g/ora01/orarcv/TOY10G/TOY10G/autobackup/2007_07_06/o1_mf_n_627229168_38x5g116_.bkp

channel ORA_DISK_1: control file restore from autobackup complete

output filename=/ora01/orabkup/GENQ/.temp_toy10g/ora01/oradata/TOY10G/control.001.dbf

output filename=/ora01/orabkup/GENQ/.temp_toy10g/ora01/oradata/TOY10G/control.002.dbf

output filename=/ora01/orabkup/GENQ/.temp_toy10g/ora01/oradata/TOY10G/control.003.dbf

Finished restore at 06-JUL-2007 14:35:18



RMAN>


7. Next, mount the database and begin to recover the database:
RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN> recover database;


Starting recover at 06-JUL-2007 14:44:20
Starting implicit crosscheck backup at 06-JUL-2007 14:44:20
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=47 devtype=DISK
Crosschecked 3 objects
Finished implicit crosscheck backup at 06-JUL-2007 14:44:22
Starting implicit crosscheck copy at 06-JUL-2007 14:44:22
using channel ORA_DISK_1
Crosschecked 5 objects
Finished implicit crosscheck copy at 06-JUL-2007 14:44:22


searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
File Name: /ora01/orabkup/GENQ/.temp_toy10g/ora01/orarcv/TOY10G/TOY10G/autobackup/2007_07_06/o1_mf_n_627229168_38x5g116_.bkp
using channel ORA_DISK_1
starting media recovery
archive log thread 1 sequence 479 is already on disk as file /ora01/orabkup/GENQ/.temp_toy10g/ora01/oraredo/TOY10G/redo02.log
archive log filename=/ora01/orabkup/GENQ/.temp_toy10g/ora01/oraredo/TOY10G/redo02.log thread=1 sequence=479
media recovery complete, elapsed time: 00:00:03
Finished recover at 06-JUL-2007 14:44:28

RMAN>

8. Finally, open the database with RESETLOGS option for normal operations:

RMAN> alter database open resetlogs;
database opened
RMAN>