This content has been marked as final. Show 14 replies
1: The <RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL> fits multiple restore/recover scenarios and in some cases it is not the only way to bring back the database.Thanks for the clarification, but I hoped more for some insight on why all redo logs are read.
2: Why don't you test your suggested recovery and let us know the result?I tried both scenarios (restore controlfile from backup and controlfile recreation). The restored controlfile scenario took some time beacuse of the redo logs it was necessary to read. Controlfile recreation (the CREATE CONTROLFILE statement) was "instantaneous". I'd be glad to hear your opinion anyway.
As for the pro/cons of one method over another, I read this quite interesting debate about that (you were also involved :-)) CA Arcserver RMAN
depends on yours scenario ,in some cases you have to recover database whether you create controlfile manually or not.
SCOTT ---------- SQL> create table p as select * from emp 2 / As within this session scot is creating a big table which is in progress at the same time i abort the instance from another session and will get create table p as select * from emp * ERROR at line 1: ORA-03113: end-of-file on communication channel SYS ----- SQL> shutdown abort ORACLE instance shut down. SQL> disconnect Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production With the Partitioning, OLAP and Data Mining options SQL> conn sys/sys as sysdba Connected to an idle instance. SQL> startup nomount ORACLE instance started. Total System Global Area 171966464 bytes Fixed Size 787988 bytes Variable Size 145750508 bytes Database Buffers 25165824 bytes Redo Buffers 262144 bytes Deleted alls controlfile from OS Recreate controlfile SQL> CREATE CONTROLFILE REUSE DATABASE "PROD" NORESETLOGS ARCHIVELOG 2 MAXLOGFILES 16 3 MAXLOGMEMBERS 3 4 MAXDATAFILES 100 5 MAXINSTANCES 8 6 MAXLOGHISTORY 454 7 LOGFILE 8 GROUP 1 'F:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\REDO01.LOG' SIZE 10M, 9 GROUP 2 'F:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\REDO02.LOG' SIZE 10M, 10 GROUP 3 'F:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\REDO03.LOG' SIZE 10M 11 -- STANDBY LOGFILE 12 DATAFILE 13 'F:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\SYSTEM01.DBF', 14 'F:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\UNDOTBS01.DBF', 15 'F:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\SYSAUX01.DBF', 16 'F:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\USERS01.DBF' 17 CHARACTER SET WE8MSWIN1252 18 ; Control file created. SQL> alter database open 2 / alter database open * ERROR at line 1: ORA-01113: file 1 needs media recovery ORA-01110: data file 1: 'F:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\SYSTEM01.DBF' SQL> SQL> recover database Media recovery complete. SQL> alter database open 2 / Database altered. SQL>
The reason why all arc files and redo logfiles need to be read is because of consistency.
The SCN should be in sync in the datafile header block, the controlfiles and the active redolog group.
All updates to the controlfile are written by the lgwr process to the redo logs (which become arc redo at some point).
So when you (gracefully) replace the controlfile with a backup to get everything in sync for oracle is to read all updates from the redo.
Can you confirm if you tested with :a. The datafiles were not restored from backup - I didn't run any RESTORE command and I think none is run implicitly.
a. NOT having restored any Datafiles -- ie All the Datafiles are as of the current point in time
b. HAVING ALL the Online Redo Logs still present on disk.
b. Yes, all online logs were present.
If my Database Files are all intact and I have all my Online Redo Logs, I can do a Complete Recovery.
Yes, I've tested the case where a restored Binary Backup controlfile still causes Oracle to go through all the ArchiveLogs.
However, if you didn't have TRACE backup of the controlfile, here's what you could do :
1. Restore the Binary Backup.
2. ALTER DATABASE MOUNT.
3. ALTER DATABASE BACKUP CONTROLFILE TO TRACE ;
4. Use this Trace file (generated at step 3) to CREATE CONTROLFILE;
5. RECOVER DATABASE ;
6. ALTER DATABASE OPEN RESETLOGS;
That way, you don't have to Roll-Forward through all the ArchiveLogs from the Binary Backup !
Yes, I've tested the case where a restored Binary Backup controlfile still causes Oracle to go through all the ArchiveLogs.That is exactly my doubt. In the documentation I don't see any recommendations on when it is better to restore a controlfile from backup and go through recovery or to simply recreate it. In the 11g docs it is even stated http://download.oracle.com/docs/cd/B28359_01/backup.111/b28270/osadvsce.htm#BRADV90042:
That way, you don't have to Roll-Forward through all the ArchiveLogs from the Binary Backup !
If the online logs contain redo necessary for recovery, then restore a backup control file and apply the logs during recovery. You must specify the filename of the online logs containing the changes in order to open the database. After recovery, open RESETLOGS.So from the above statement, both approaches seem to be equally good.
Note: If you re-create a control file, then it is not necessary to OPEN RESETLOGS after recovery when the online redo logs are accessible.
But as far as I understand the situation is (please correct me if I'm wrong!):
1) Restore the controlfile from backup:
good: no information stored in the controlfile is lost (rman backup info, ....)
bad: it's necessary to perform recover through all the (archived) redo logs newer then the controlfile to synchronize the control files' SCN with the datafiles. This can take some time if there are many archived logs to be read and even more time if they where possibly already moved to tertiary storage.
2) Recreate the controlfile:
good: it's faster, since we don't have to read through the redo logs; if all online redo logs are intact, no RESETLOGS is necessary
bad: we lose all additional info contained in the binary version of the controlfile; it is more error prone since we must ensure that the CREATE CONTROLFILE statement correctly specifies all the datafile and redo logs locations.
Any comments on the above would be very appreciated.
Your conclusions are valid.
A Binary Backup of the controlfile would generally be preferable -- but then, again, some would argue that an RMAN Catalog does continue to retain information. If you don't use a Catalog, then you should prefer to use a Binary Backup UNLESS you know that a roll-forward through many ArchiveLogs would take much longer. You can then opt to CREATE CONTROLFILE, do a quicker OPEN database and then -- here's the advantage, particularly from 10g onwards -- CATALOG your Backupsets to "put the information back into the controlfile" ! Of course, you would not go back far in time to catalog many backupsets !
Having said all that, this discussion is only about the very rare case where :
a. All the Controlfiles are lost
b. ALL the Datafiles are intact
c. ALL the Online Redo Log files are intact
AND, because of b. and c., No Recovery is really required.
Generally, most Restore and Recovery situations are when Datafiles and/or Redo Log files are lost.
Hemant K Chitale
Thank you for the explanation!
Just maybe another question regarding the documentation - unfortunately I don't understand the meaning of the contents in the 2nd row in the table:
There it's stated that if the status of the online logs is Unavailable, the restore procedure is: "If the online logs contain redo necessary for recovery, then re-create the control file. Because the online redo logs are inaccessible...."
But how can the online logs contain redo if they are unavailable? Maybe I don't distinguish between unavailable and inaccessible. To me both terms mean that the redo logs are permanently lost. Am I wrong?
At first I thought it was an error in the documentation, but I checked also the 9i and 11g docs and the same thing is written. So I concluded the documentation is right and I'm the one who doesn't understand it :-)
If the online logs contain redo necessary for recovery, then re-create the control file. Because the online redo logs are inaccessible...."
Jure read the first column where it specify the status of log is unavailable
unvailable log is identified by using v$logfile dynamic view where status of log file shows invalid see it here
-------------------------------------------------------------------------------------------------- Status of Online Logs | Status of Datafiles | Restore Procedure -------------------------------------------------------------------------------------------------- Unavailable Current If the online logs contain redo necessary for recovery, then re-create the control file. Because the online redo logs are inaccessible, open RESETLOGS (when the online logs are accessible it is not necessary to OPEN RESETLOGS after recovery with a created control file). --------------------------------------------------------------------------------------------------
http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/dynviews_1152.htm#REFRN30129above higlighted para says that if yours redo log file got corrupted and by all means it is not accessible and it had some redo for recovery then you have option to go with recreating controlfile.
online log file can contain redo and it got corrupted when it was being used.
By using "backup Control file" we tell database that control file is old and data files are new. For recovery , It then checks SCN on datafiles
Recovery will then be started from that SCN number.
Only thing is that now CF is old , so oracle do not know when to stop? Thus specify until cancel as you know where it need to be stopped.