Re: Controlfile Recovery Requires RESETLOGS


My reply then was :

--------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------
Question : In your recovery testing,  what was the exact sequence ?  Was it ?
a) Restore an older controlfile binary backup -- only controlfile restored, datafiles being current b) Issue RECOVER DATABASE USING BACKUP CONTROLFILE -- did you also specify "UNTIL CANCEL" ? 

OR
  a)  Create Controlfile
  b)  Issue RECOVER DATABASE ... commands.
What if you had also restored DataFiles from the older backup ? -- ie even the DataFiles were not "current" ? Then, if that was a Hot Backup, you would have had to apply some ArchiveLog(s). If it was a Cold Backup, ... (ie ControlFile and DataFiles from Monday) 
you should have been able to CANCEL and OPEN RESETLOGS without having
any online redo log file (as today when you do the restore, it is Wednesday so your 
ondisk online redo logs are Wednesday's files).
--------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------
If you have done a SHUTDOWN IMMEDIATE, the proper way to do Recovery is the second method above ie *CREATE* the Controlfile and then OPEN NORESETLOGS (a RECOVER command, optional, 
would just return "no recovery required").
You do NOT need to actually Restore the controlfile.
If you DO restore the controlfile then, quite obviously, it IS a Backup Controlfile (being older than the datafiles that were shutdown immediate _after_ the controlfile backup was made). 
Once you use a BACKUP CONTROLFILE,  you MUST also do a RESETLOGS.
Why ??  Check the documentation on the RECOVER command.  Read it again.

Anyway :  Here is how you should do a Database Recovery (without even having
to restore your controlfile backup) if you have done a Normal or Immediate Shutdown : 

SQL>
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> STARTUP NOMOUNT
ORACLE instance started.
Total System Global Area 83886080 bytes Fixed Size 1247420 bytes Variable Size 54527812 bytes Database Buffers 25165824 bytes Redo Buffers 2945024 bytes 
SQL> CREATE CONTROLFILE REUSE DATABASE "OR10G2DB" NORESETLOGS  ARCHIVELOG
  2      MAXLOGFILES 16
  3      MAXLOGMEMBERS 3
  4      MAXDATAFILES 100
  5      MAXINSTANCES 8
  6      MAXLOGHISTORY 292
  7  LOGFILE
  8    GROUP 1 'C:\OR10G2DB\REDO01.DBF'  SIZE 8M,
  9    GROUP 2 'C:\OR10G2DB\REDO02.DBF'  SIZE 8M,
 10    GROUP 3 'C:\OR10G2DB\REDO03.DBF'  SIZE 8M
 11  -- STANDBY LOGFILE
 12  DATAFILE
 13    'C:\OR10G2DB\SYSTEM01.DBF',
 14    'C:\OR10G2DB\UNDOTBS01.DBF',
 15    'C:\OR10G2DB\SYSAUX01.DBF',
 16    'C:\OR10G2DB\USERS01.DBF',
 17    'C:\OR10G2DB\EXAMPLE01.DBF'
 18  CHARACTER SET WE8MSWIN1252
 19  ;

Control file created.

SQL>
SQL> -- Commands to re-create incarnation table
SQL> -- Below log names MUST be changed to existing filenames on
SQL> -- disk. Any one log file from each branch can be used to
SQL> -- re-create incarnation records.
SQL> -- ALTER DATABASE REGISTER LOGFILE 'C:\OR10G2DB\ARCH\ARC00001_0567697796.001'; SQL> -- ALTER DATABASE REGISTER LOGFILE 'C:\OR10G2DB\ARCH\ARC00001_0589074881.001'; 
SQL> -- Recovery is required if any of the datafiles are restored backups,
SQL> -- or if the last shutdown was not normal or immediate.
SQL> RECOVER DATABASE
ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery required


SQL>
SQL> -- All logs need archiving and a log switch is needed.
SQL> ALTER SYSTEM ARCHIVE LOG ALL;

System altered.

SQL>
SQL> -- Database can now be opened normally.
SQL> ALTER DATABASE OPEN;

Database altered.

SQL>
SQL> -- Commands to add tempfiles to temporary tablespaces.
SQL> -- Online tempfiles have complete space information.
SQL> -- Other tempfiles may require adjustment.
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE 'C:\OR10G2DB\TEMP01.DBF'
  2       SIZE 22020096  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;

Tablespace altered.

SQL> exit


I did not have to "apply" the Online Redo Log file.
I did not have to "apply" any ArchiveLog file.
I did not have to Open Resetlogs.
The RECOVER command was unnecessary. (Try the same steps without the RECOVER command)
Where did I get these commands from ? From a Controlfile Trace !! -- see Set 1 
of the Trace file generated by a BACKUP CONTROLFILE TO TRACE command.
Read the documentation on the ALTER DATABASE command.
OK, now you say that you only have a Binary Backup of the Controlfile but do not have a Tracefile backup. Guess what? You can create a Tracefile even from that 
(older) Binary Backup.  Startup Mount with that (older) Binary Backup as your
controlfile and issue an ALTER DATABASE BACKUP CONTROLFILE TO TRACE.
Use Set 1 from the Tracefile subsequently generated, as I have done.
[The obvious catch is that you must be sure to identify *all* your datafiles in
the CREATE .. statement.  Datafiles added after the Binary Backup would
not be included in the Tracefile, but you  can add them in to the script
before you run it]
Next Scenario: What if I have lost the online Redo Logs as well as the Controlfile -- 
but the Datafiles are all consistent from a Shutdown Immediate ??
Here's what I have to do : (I have removed the Online Redo Logs as well, after a Shutdown Immediate) 

SQL> STARTUP NOMOUNT
ORACLE instance started.
Total System Global Area 83886080 bytes Fixed Size 1247420 bytes Variable Size 54527812 bytes Database Buffers 25165824 bytes Redo Buffers 2945024 bytes 
SQL> CREATE CONTROLFILE REUSE DATABASE "OR10G2DB" RESETLOGS  ARCHIVELOG
  2      MAXLOGFILES 16
  3      MAXLOGMEMBERS 3
  4      MAXDATAFILES 100
  5      MAXINSTANCES 8
  6      MAXLOGHISTORY 292
  7  LOGFILE
  8    GROUP 1 'C:\OR10G2DB\REDO01.DBF'  SIZE 8M,
  9    GROUP 2 'C:\OR10G2DB\REDO02.DBF'  SIZE 8M,
 10    GROUP 3 'C:\OR10G2DB\REDO03.DBF'  SIZE 8M
 11  -- STANDBY LOGFILE
 12  DATAFILE
 13    'C:\OR10G2DB\SYSTEM01.DBF',
 14    'C:\OR10G2DB\UNDOTBS01.DBF',
 15    'C:\OR10G2DB\SYSAUX01.DBF',
 16    'C:\OR10G2DB\USERS01.DBF',
 17    'C:\OR10G2DB\EXAMPLE01.DBF'
 18  CHARACTER SET WE8MSWIN1252
 19  ;

Control file created.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


SQL> alter database open resetlogs;

Database altered.

SQL> spool of


Again, I do NOT have to issue RECOVER Commands.
I had to use OPEN RESETLOGS _because_  the CREATE CONTROLFILE
was with a RESETLOGS !


One more test :.  I do not have OnlineRedo logs.  Can I
do without RESETLOGS ? Do I need to issue RECOVER commands ?

Let's see :  [having removed the Online Redologs after a Shutdown Immediate]

SQL> set echo on
SQL> STARTUP NOMOUNT
ORACLE instance started.
Total System Global Area 83886080 bytes Fixed Size 1247420 bytes Variable Size 54527812 bytes Database Buffers 25165824 bytes Redo Buffers 2945024 bytes 
SQL> CREATE CONTROLFILE REUSE DATABASE "OR10G2DB" NORESETLOGS  ARCHIVELOG
  2      MAXLOGFILES 16
  3      MAXLOGMEMBERS 3
  4      MAXDATAFILES 100
  5      MAXINSTANCES 8
  6      MAXLOGHISTORY 292
  7  LOGFILE
  8    GROUP 1 'C:\OR10G2DB\REDO01.DBF'  SIZE 8M,
  9    GROUP 2 'C:\OR10G2DB\REDO02.DBF'  SIZE 8M,
 10    GROUP 3 'C:\OR10G2DB\REDO03.DBF'  SIZE 8M
 11  -- STANDBY LOGFILE
 12  DATAFILE
 13    'C:\OR10G2DB\SYSTEM01.DBF',
 14    'C:\OR10G2DB\UNDOTBS01.DBF',
 15    'C:\OR10G2DB\SYSAUX01.DBF',
 16    'C:\OR10G2DB\USERS01.DBF',
 17    'C:\OR10G2DB\EXAMPLE01.DBF'
 18  CHARACTER SET WE8MSWIN1252
 19  ;
CREATE CONTROLFILE REUSE DATABASE "OR10G2DB" NORESETLOGS  ARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01565: error in identifying file 'C:\OR10G2DB\REDO01.DBF'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.


SQL> STARTUP NOMOUNT
ORA-01081: cannot start already-running ORACLE - shut it down first
SQL> CREATE CONTROLFILE REUSE DATABASE "OR10G2DB" RESETLOGS  ARCHIVELOG
  2      MAXLOGFILES 16
  3      MAXLOGMEMBERS 3
  4      MAXDATAFILES 100
  5      MAXINSTANCES 8
  6      MAXLOGHISTORY 292
  7  LOGFILE
  8    GROUP 1 'C:\OR10G2DB\REDO01.DBF'  SIZE 8M,
  9    GROUP 2 'C:\OR10G2DB\REDO02.DBF'  SIZE 8M,
 10    GROUP 3 'C:\OR10G2DB\REDO03.DBF'  SIZE 8M
 11  -- STANDBY LOGFILE
 12  DATAFILE
 13    'C:\OR10G2DB\SYSTEM01.DBF',
 14    'C:\OR10G2DB\UNDOTBS01.DBF',
 15    'C:\OR10G2DB\SYSAUX01.DBF',
 16    'C:\OR10G2DB\USERS01.DBF',
 17    'C:\OR10G2DB\EXAMPLE01.DBF'
 18  CHARACTER SET WE8MSWIN1252
 19  ;

Control file created.

SQL> REM let's see if RECOVER is needed ??
SQL> pause Is RECOVER required ?
Is RECOVER required ?

SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done


SQL> REM  Let's just OPEN (obviously, RESETLOGS)
SQL> pause LEt's just OPEN (obviously, RESETLOGS)
LEt's just OPEN (obviously, RESETLOGS)

SQL> alter database open resetlogs;

Database altered.

SQL> spool off

So, again the RECOVER was not required.


Final scenario :  Which I leave to you as an exercise.
What if I have a Cold Backup of the Datafiles of Monday
and I have all the ArchiveLogs till Wednesday but I do not
have the ControlFile and OnlineRedoLogs as of Wednesday ?
How I can "roll-forward"  {obviously, using the RECOVER command}
from Monday's backup to Wednesday's last available ArchiveLog ?

Hint : I use the " USING BACKUP CONTROLFILE" recovery method.



At 03:49 AM Thursday, Jeremy Paul Schneider wrote:

Hey all...
In response to a bit of discussion last week - I put together the test and output showing how controlfile recovery requires recovery and a RESETLOGS even if the database was closed normally, in a consistent state. I posted the output here: 

<http://www.ardentperf.com/2007/05/09/controlfile-recovery-requires-resetlogs/>http://www.ardentperf.com/2007/05/09/controlfile-recovery-requires-resetlogs/

=====
Most questions can be answered by looking at the post since I walked through the whole process. But to answer a few specific questions that various people asked... 

<<deleted>>



--
Jeremy Schneider
Chicago, IL
<http://www.ardentperf.com/category/technical>http://www.ardentperf.com/category/technical 


Hemant K Chitale
http://web.singnet.com.sg/~hkchital
and
http://hemantscribbles.blogspot.com
and
http://hemantoracledba.blogspot.com
"First they ignore you, then they laugh at you, then they fight you, then you win" !" Mohandas Gandhi Quotes : http://www.brainyquote.com/quotes/authors/m/mohandas_gandhi.html 

--
http://www.freelists.org/webpage/oracle-l

Other related posts: