Case 2: All control files lost
What if you lose all your control files? In that case you have no option but to use a backup control file. The recovery needs to be performed from within RMAN, and requires that all logs (archived and current online logs) since the last backup are available. The logs are required because all datafiles must also be restored from backup. The database will then have to be recovered up to the time the control files went missing. This can only be done if all intervening logs are available. Here’s an annotated transcript of a recovery session (as usual, lines in bold are commands to be typed, lines in italics are explanatory comments, other lines are RMAN feedback):
Connect to RMANC:rman
Recovery Manager: Release 9.0.1.1.1 – Production
(c) Copyright 2001 Oracle Corporation. All rights reserved.
RMAN> set dbid 4102753520
executing command: SET DBID
set DBID – get this from the name of the controlfile autobackup. For example, if autobackup name is CTL_SP_BAK_C-1507972899-20050124-00 the the DBID is
1507972899. This step will not be required if the instance is
RMAN> connect target sys/change_on_install
connected to target database: (not mounted)
Recovery Manager: Release 9.2.0.4.0 – Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to target database: ORCL (not mounted)
RMAN> restore controlfile from autobackup;
Finished restore at 26/JAN/05
— Now that control files have been restored, the instance can mount the database.
RMAN> mount database;
database mounted
— All datafiles must be restored, since the controlfile is older than the current
— datafiles. Datafile restore must be followed by recovery up to the current log.
RMAN> restore database;
–Database must be recovered because all datafiles have been restored from backup
RMAN> recover database;
media recovery complete
Finished recover at 26/JAN/05
— Recovery completed. The database must be opened with RESETLOGS
— because a backup control file was used. Can also use
— “alter database open resetlogs” instead.
RMAN> open resetlogs database;
database opened
Several points are worth emphasizing.
1. Recovery using a backup controlfile should be done only if a current control file is unavailable.
2. All datafiles must be restored from backup. This means the database will need to be recovered using archived and online redo logs. These MUST be available for recovery until the time of failure.
3. As with any database recovery involving RESETLOGS, take a fresh backup immediately.
1. Technically the above is an example of complete recovery – since all committed transactions were recovered. However, some references consider this to be incomplete recovery because the database log sequence had to be reset.
2. After recovery using a backup controlfile, all temporary files associated with locally-managed tablespaces are no longer available. You can check that this is so by querying the view V$TEMPFILE – no rows will be returned. Therefore tempfiles must be added (or recreated) before the database is made available for general use. In the case at hand, the tempfile already exists so we merely add it to the temporary tablespace. This can be done using SQLPlus or any tool of your choice:
SQL> alter tablespace temp add tempfile
‘D:oracle_datadatafilesORCLTEMP01.DBF’;
Tablespace altered.
SQL>
Check that the file is available by querying v$TEMPFILE.
Responses