This discussion is archived
14 Replies Latest reply: Aug 26, 2012 10:16 AM by 958046 RSS

Restore time using backup controlfile

user633661 Newbie
Currently Being Moderated
Hi,

if I lose all current controlfiles but all other database files are intact, I have to restore an "old" controlfile from backup and then perform recovery as described in http://download.oracle.com/docs/cd/B19306_01/backup.102/b14191/osrecov.htm#i1011129 or Re: BACKUP CONTROLFILE. What is this?

1) What puzzles me is what actually happens when "RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL" is executed and all the archived redo logs newer than the controlfile are processed? I mean, since the datafiles don't need any recovery, the only thing I can imagine is that all those archived logs and online redo logs are scanned to check if any structural changes were made to the database since the time the controlfile was backuped, and to synchronize the controlfile's SCN with the datafile's. I'm asking that because if it's necessary to read a lot of (archived) redo logs, it takes a considerable amount of time.


2) Would it be maybe better (and certainly much faster!) to recreate the controlfile, if a trace file with the result of "ALTER DATABASE BACKUP CONTROLFILE TO TRACE" is available (of course with all the current datafiles/redo log files locations)? I know all the information contained in the controlfile is lost (backups, etc.) that way, but if recovery time is a concern and no "fresh" backup of the controlfile is available.... And it also may not be necessary to open the database with resetlogs, thus not creating a new incarnation.

Thanks in advance for any answer.

Regards,
Jure
  • 1. Re: Restore time using backup controlfile
    tychos Expert
    Currently Being Moderated
    Hi Jure,
    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.

    2: Why don't you test your suggested recovery and let us know the result?

    Regards,
    Tycho
  • 2. Re: Restore time using backup controlfile
    user633661 Newbie
    Currently Being Moderated
    Hi Tycho,
    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

    Regards,
    Jure
  • 3. Re: Restore time using backup controlfile
    26741 Oracle ACE
    Currently Being Moderated
    Can you confirm if you tested with :
    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.
  • 4. Re: Restore time using backup controlfile
    316993 Pro
    Currently Being Moderated
    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> 
    Khurram
  • 5. Re: Restore time using backup controlfile
    tychos Expert
    Currently Being Moderated
    Hi Jure,
    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.
    Regards,
    Tycho
  • 6. Re: Restore time using backup controlfile
    user633661 Newbie
    Currently Being Moderated
    Hi Tycho,

    Thank you for the explanation.

    Regards,
    Jure
  • 7. Re: Restore time using backup controlfile
    user633661 Newbie
    Currently Being Moderated
    Can you confirm if you tested with :
    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.
    a. The datafiles were not restored from backup - I didn't run any RESTORE command and I think none is run implicitly.
    b. Yes, all online logs were present.
  • 8. Re: Restore time using backup controlfile
    26741 Oracle ACE
    Currently Being Moderated
    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 !
  • 9. Re: Restore time using backup controlfile
    user633661 Newbie
    Currently Being Moderated
    Yes, I've tested the case where a restored Binary Backup controlfile still causes Oracle to go through all the ArchiveLogs.
    ....
    That way, you don't have to Roll-Forward through all the ArchiveLogs from the Binary Backup !
    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:
    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.
    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.
    So from the above statement, both approaches seem to be equally good.

    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.

    Regards,
    Jure
  • 10. Re: Restore time using backup controlfile
    26741 Oracle ACE
    Currently Being Moderated
    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
    http://hemantoracledba.blogspot.com
  • 11. Re: Restore time using backup controlfile
    user633661 Newbie
    Currently Being Moderated
    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:
    http://download.oracle.com/docs/cd/B19306_01/backup.102/b14191/osrecov.htm#i1011093

    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 :-)
    9i: http://download.oracle.com/docs/cd/B10501_01/server.920/a96572/osrestore.htm#26899
    11g: http://download.oracle.com/docs/cd/B28359_01/backup.111/b28270/osadvsce.htm#BRADV90042

    Regards,
    Jure
  • 12. Re: Restore time using backup controlfile
    316993 Pro
    Currently Being Moderated

    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
    --------------------------------------------------------------------------------------------------
    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).
     
    --------------------------------------------------------------------------------------------------
    unvailable log is identified by using v$logfile dynamic view where status of log file shows invalid see it here
    http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/dynviews_1152.htm#REFRN30129
    above 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.

    Khurram
  • 13. Re: Restore time using backup controlfile
    user633661 Newbie
    Currently Being Moderated
    Thanks for the explanation.

    Regards,
    Jure
  • 14. Re: Restore time using backup controlfile
    958046 Newbie
    Currently Being Moderated
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points