Controlfile Recovery WITHOUT Resetlogs

Well last week I had a few posts about controlfile recovery; one about recovering without a backup and one about recovering with a backup using RESETLOGS. In the second post I showed how when you restore a backup controlfile Oracle will always require you to recover then open the database with RESETLOGS.

Hemant Chitale pointed out that 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 (assuming of course a normal database shutdown). Naturally you will lose any information in your controlfile (RMAN configuration and records, the incarnation table, etc) so I would personally prefer keeping the old file. But it’s certainly possible – so here’s a demo of Hemant’s suggestion on Oracle 10 release 2. Really it’s abit of a rehash; Hemant actually posted all of this himself on oracle-l last week. (And more – he also posted a scenario of losing both the controlfile and online logs.) He also mentioned it in his blog. So for more detail you can also check out email.

The setup is exactly the same as recovery with a backup controlfile – I’m just going to do some different steps starting at the “Recovery” part:

Recovery

We’ll pick up from the other post with the attempt to open the database that fails. Remember that I 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

Now if you already have a backup of your controlfile then you’re all set. But of course if you don’t you can even make one using your controlfile backup. That’s what I did here:

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

Database altered.

Now one thing that we’re definitely going to lose is the incarnation table. Let’s have a quick look at the contents right now.

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-05                       0
PARENT     563434975                  0 NO

           2            524107 03-MAY-07                       1 12-JUL-05
PARENT     621607779                  1 NO

           3            565455 03-MAY-07                  524107 03-MAY-07
PARENT     621627183                  2 NO

           4            784376 09-MAY-07                  565455 03-MAY-07
CURRENT    622130726                  3 NO

Recreating the Control File

It’s worth quickly pointing out that you can of course do this 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. Of course the best way to get it is with “backup to trace”.

First you need to have the instance started but no controlfile mounted. When you recreate the controlfile it will read the CONTROL_FILES initialization parameter and overwrite any existing files.

SQL> shutdown immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.

SQL> startup nomount
ORACLE instance started.

Total System Global Area  629145600 bytes
Fixed Size                  1980712 bytes
Variable Size             180356824 bytes
Database Buffers          440401920 bytes
Redo Buffers                6406144 bytes

SQL> CREATE CONTROLFILE REUSE DATABASE "JT10G" NORESETLOGS  NOARCHIVELOG
  2      MAXLOGFILES 16
  3      MAXLOGMEMBERS 3
  4      MAXDATAFILES 100
  5      MAXINSTANCES 8
  6      MAXLOGHISTORY 292
  7  LOGFILE
  8    GROUP 1 '/u04/oracle/oradata/jt10g/redo01.log'  SIZE 50M,
  9    GROUP 2 '/u04/oracle/oradata/jt10g/redo02.log'  SIZE 50M,
 10    GROUP 3 '/u04/oracle/oradata/jt10g/redo03.log'  SIZE 50M
 11  DATAFILE
 12    '/u04/oracle/oradata/jt10g/system01.dbf',
 13    '/u04/oracle/oradata/jt10g/undotbs01.dbf',
 14    '/u04/oracle/oradata/jt10g/sysaux01.dbf',
 15    '/u04/oracle/oradata/jt10g/users01.dbf',
 16    '/u04/oracle/oradata/jt10g/example01.dbf'
 17  CHARACTER SET WE8ISO8859P1
 18  ;

Control file created.

SQL> ALTER DATABASE OPEN;

Database altered.

I got that statement out of a trace file. Also you need to remember to recreate your tempfiles; the appropriate statements will also be in your trace file.

SQL> ALTER TABLESPACE TEMP 
  2  ADD TEMPFILE '/u04/oracle/oradata/jt10g/temp01.dbf' REUSE;

Tablespace altered.

Now as I mentioned before you will lose all information in your controlfile. Let’s quickly verify this by examining the incarnation table.

SQL> select * from v$database_incarnation;

INCARNATION# RESETLOGS_CHANGE# RESETLOGS PRIOR_RESETLOGS_CHANGE# PRIOR_RES
------------ ----------------- --------- ----------------------- ---------
STATUS  RESETLOGS_ID PRIOR_INCARNATION# FLASHBACK_DATABASE_ALLOWED
------- ------------ ------------------ --------------------------
           1            784376 09-MAY-07                  565455 03-MAY-07
CURRENT    622130726                  0 NO

And there you have it. Controlfile recovery without a RESETLOGS. As I mentioned before I prefer the RESETLOGS case and keeping the original controlfile if at all possible. Perhaps the main reason is that incarnations exist for a reason; they help you keep track of changes to you database. It’s good to have that electronic record. But this is also a very important recovery method to be aware of!