| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Controlfile Recovery Requires RESETLOGS
My reply then was :
OR
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
2 MAXLOGFILES 16 3 MAXLOGMEMBERS 3 4 MAXDATAFILES 100 5 MAXINSTANCES 8 6 MAXLOGHISTORY 292
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 8M11 -- STANDBY LOGFILE
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'
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
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
2 MAXLOGFILES 16 3 MAXLOGMEMBERS 3 4 MAXDATAFILES 100 5 MAXINSTANCES 8 6 MAXLOGHISTORY 292
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 8M11 -- STANDBY LOGFILE
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'
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
2 MAXLOGFILES 16 3 MAXLOGMEMBERS 3 4 MAXDATAFILES 100 5 MAXINSTANCES 8 6 MAXLOGHISTORY 292
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 8M11 -- STANDBY LOGFILE
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'
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
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 8M11 -- STANDBY LOGFILE
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'
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-lReceived on Thu May 10 2007 - 09:41:29 CDT
 |
 |