Great Tips on Recovering Controlfile without Resetlogs!

 Author: Hemant Chitale

Page: 1 2

In this article I will discuss some great tips on restoring a backup controlfile. Oracle always requires you to recover and then open the database with resetlogs. However you do not always need to do a resetlogs when you open the database. If you recreate the controlfile rather than restoring a backup then Oracle allows you to open the database normally. Normally all information in controlfile like RMAN configuration and records, incarnation table etc will be lost so it is preferred to keep the old file.

Opening Database:

Consider a scenario where our attempt to open a database has failed. We have already restored a backup controlfile and mounted it.

SQL> alter database open noresetlogs;
alter database open noresetlogs
*
ERROR at line 1:
ORA-01588: must use RESETLOGS option for database open

Controlfile Backup:

You can make a backup of your controlfile by using below command.

SQL> alter database backup controlfile to trace
2 as '/u04/oracle/oradata/jt10g/newctl.sql';
Database altered.

Incarnation Table:

The contents of the incarnation table will be lost. You can view the contents of incarnation table by running below command.

SQL> select * from v$database_incarnation;

INCARNATION# RESETLOGS_CHANGE# RESETLOGS PRIOR_RESETLOGS_CHANGE# PRIOR_RES
------------ ----------------- --------- ----------------------- ---------
STATUS RESETLOGS_ID PRIOR_INCARNATION# FLASHBACK_DATABASE_ALLOWED
------- ------------ ------------------ --------------------------
1 1 12-JUL-06 0
PARENT 563434975 0 NO
2 524107 03-DEC-07 1 12-JUL-06
PARENT 621607779 1 NO
3 565455 03-DEC-07 524107 03-JAN-08
PARENT 621627183 2 NO
4 784376 09-DEC-07 565455 03-JAN-08
CURRENT 622130726 3 NO

Recreating Control File:

You can recreate the control file as long as you can generate the proper CREATE CONTROLFILE statement. If you know your datafile and logfile layout then you can generate this statement even with no backup of anything. The best way to re-create control file is with backup to trace. Continued...

Page: 1 2

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   Oracle Recovery Manager - RMAN
   Easy, Efficient & Quick Oracle Cloning!!
   Oracle Guide: Recovering accidentally dropped tables!!
   Oracle Sorting for Oracle Tuning