1 2 Previous Next 28 Replies Latest reply: Mar 13, 2012 9:11 PM by Arunkumar Bejagam RSS

    Recover using backup controlfile

    Catch-22
      A binary restore of the controlfile requires incomplete database recovery and a database open using resetlogs.

      But I would like to understand why it is necessary to specify "using backup controlfile" with the recover command - what is the difference. Using Oracle 11.2.

      Since Oracle 10g it is no longer necessary to backup the database after a database open with resetlogs and the recover process is able to recover a database from a previous incarnation, provided archivelogs are available. What's the story please?
        • 1. Re: Recover using backup controlfile
          sybrand_b
          The story is the (SCN) information in the controlfile is normally Truth and Gospel, so Oracle will use it to recover the database, and disregard whatever (SCN) information is in the datafile headers.
          If the controlfile is the current controlfile this is not a problem.
          If you restored the controlfile from a backup this is a problem, as this would bring you back in time.
          Hence the 'using backup controlfile' which basically states: disregard whatever (SCN) information is in the restored controlfile and recover until I, the operator, say you have to stop. This can be beyond the 'end' of the controlfile.

          Maybe you didn't make a New Years resolution always to read the documentation, because obviously this is Yet Another Doc Question. Now is the time to make that New Years resolution.

          -------------
          Sybrand Bakker
          Senior Oracle DBA
          • 2. Re: Recover using backup controlfile
            sybrand_b
            The story is the (SCN) information in the controlfile is normally Truth and Gospel, so Oracle will use it to recover the database, and disregard whatever (SCN) information is in the datafile headers.
            If the controlfile is the current controlfile this is not a problem.
            If you restored the controlfile from a backup this is a problem, as this would bring you back in time.
            Hence the 'using backup controlfile' which basically states: disregard whatever (SCN) information is in the restored controlfile and recover until I, the operator, say you have to stop. This can be beyond the 'end' of the controlfile.

            Maybe you didn't make a New Years resolution always to read the documentation, because obviously this is Yet Another Doc Question. Now is the time to make that New Years resolution.

            -------------
            Sybrand Bakker
            Senior Oracle DBA
            • 3. Re: Recover using backup controlfile
              sybrand_b
              The story is the (SCN) information in the controlfile is normally Truth and Gospel, so Oracle will use it to recover the database, and disregard whatever (SCN) information is in the datafile headers.
              If the controlfile is the current controlfile this is not a problem.
              If you restored the controlfile from a backup this is a problem, as this would bring you back in time.
              Hence the 'using backup controlfile' which basically states: disregard whatever (SCN) information is in the restored controlfile and recover until I, the operator, say you have to stop. This can be beyond the 'end' of the controlfile.

              Maybe you didn't make a New Years resolution always to read the documentation, because obviously this is Yet Another Doc Question. Now is the time to make that New Years resolution.

              -------------
              Sybrand Bakker
              Senior Oracle DBA
              • 4. Re: Recover using backup controlfile
                Catch-22
                Hi,

                That's all good and true, but why is it necessary to inform the recovery process about the backup controlfile? It should be easy for oracle to determine that the SCN of the controlfile and datafile headers is not in sync. Why is a incomplete recovoery using "recover until..." not sufficient enough? Why does it require the extra "using backup controlfile" parameter, why bother?

                And what changed in Oracle 10g that it can recover past a resetlog operation?

                Regarding the new years resolution... I will try to keep that in mind, but I actually never make any new year resolutions, because they usually don't work after the drinks wear off ;-)

                Thanks.
                • 5. Re: Recover using backup controlfile
                  sb92075
                  It should be easy for oracle to determine that the SCN of the controlfile and datafile headers is not in sync.
                  It is easy to conclude files are not in synch.
                  The challenge is to "decide" which SCN should be believed as being "correct".
                  • 6. Re: Recover using backup controlfile
                    Catch-22
                    If the controlfile and datafiles SCN is not in sync the database will not open. So in case of a crash or shutdown abort, the instance has no trouble to determine which SCN to use. Is it assuming the controlfile is correct?

                    So it seems to me the automatic recovery does not work if the SCN in the controlfile is lower than what is stamped in the datafile headers?
                    The challenge is to "decide" which SCN should be believed as being "correct".
                    I assume that's why there is a "recover noredo", which resets the SCN to 1 right away, or a "recover until" which lets the dba decide how much redo to apply, if available, before resetting the SCN to 1. Is this correct? So why bother with "using backup controlfile" statement?
                    • 7. Re: Recover using backup controlfile
                      mbobak
                      I think I understand what you mean, in that if the controlfile is not current, Oracle should be able to determine that, and not need to be prompted with the 'using backup controlfile' directive.

                      I guess I'd argue that, in a recovery scenario, you don't want Oracle making any assumptions. What if, as part of your restore process, you accidentally stepped on the current controlfile? If Oracle saw the non-current controlfile and assumed it was ok to use it, you may not even notice what had happened till it was too late.

                      I'd argue the directive is there for clarity. "Yes Oracle, this is a non-current controlfile, please use it and proceed with recovery."

                      -Mark
                      • 8. Re: Recover using backup controlfile
                        Catch-22
                        Thanks for your reply.

                        Meanwhile I found some interesting information: http://www.lazydba.com/oracle/0__53231.html

                        "... If you specify a BACKUP CONTROLFILE then Oracle uses the file headers rather than control file information to control the restore..."

                        Well, I guess that would make sense. But on the other hand, "using backup control file" is required when appropriate. So why bother, and how is it determined? And shouldn't it be named rather "using datafile header"?

                        Edited by: Dude on Jan 2, 2011 6:25 PM
                        • 9. Re: Recover using backup controlfile
                          Hemant K Chitale
                          "+If you specify a BACKUP CONTROLFILE then Oracle uses the file headers rather than control file information to control the restore+" (from the URL you mention)
                          isn't correct.

                          I could have :
                          Scenario A-1 :_
                          Datafiles and Controlfile from a COLD BACKUP at 11:00am of 31-Dec (just before the YEAR-END Batch processing begins). I also have, subsequently, ArchiveLogs running into the late night of 01-Jan.
                          I lose the database sometime late night of 01-Jan.
                          How do I RECOVER the database ? The datafile headers are "consistent" with the controlfile. Yet, I want Oracle to ignore both sets of SCNs (those in the controlfile and those in the Datafile). The SCNs in the datafile are not used to determine the recovery point.
                          I can specify RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL.

                          Scenario A-2 :_
                          The backups are the same except that the database isn't down. It is up and running.
                          I need to build a Reporting database to present YEAR-END reports as at 02-Jan 01:00am (or as at some time when the YEAR-END Batch processing completes, and on 01-Jan I am not sure when it will complete).
                          So, I want to "recover" (i.e. CLONE or DUPLICATE) the database on an alternate server (similar to building a standby). I haven't done RMAN Backups. I use Storage Based Snapshot Backups. I am running V7/8/9/10. I do cannot use the RMAN DUPLICAT ACTIVE DATABASE command. I know that it will take me a long time to copy that backup to the reporting server. So, I starte with the 31-Dec 11:00am backup.
                          I can specify RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL ... and keep running this for as long as I want, copying ArchiveLogs from the primary server to the secondary / reporting server. I stop the Recovery at the the last ArchiveLog when the YEAR-END Batch completed. (It could actually be 01-Jan 11:45pm or 02-Jan 04:15am -- depending on how long the Batch job took to run).

                          In both of the above scenarios, the Controlfile and Datafiles used actually had the same SCN. Yet, I wanted Oracle to NOT use the Datafiles as the Recovery Point. I wanted Oracle to recover to a future point in time, relative to the Datafiles.

                          (Scenario A-2 is also commonly used when cloning a database for upgrade on a second server with reduced downtime -- eliminating the time it would take to copy the backup from one server to another server, as the backup is actually copied when the first server is still up and running).

                          Scenario B :_
                          I have lost the database and the controlfile. I do have backups of the controlfile and backups of datafiles spread over a few days (because each day's backup run can't backup the whole database) and subsequent archivelogs -- except that I am not sure whether all the subsequent archivelogs are readable (they are on suspected faulty tapes / disks). So I use RECOVER UNTIL CANCEL. However, my database files are also from the backup. I cannot use them to determine the Recovery Point, I don't wan't Oracle to stop recovery based on datafile headers (in fact, as I re-assert : Oracle won't use the datafile headers to determine the Recovery Point). So I use RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL.

                          Scenario C :_
                          I have OS-based backups of the datafiles (they may be the same point in time or different points in time, they may be Cold Backups or Hot Backups). But I don't have a Controlfile backup at all.
                          I can use the OS-based commands to restore the datafiles (not using RMAN). Then I can run a CREATE CONTROLFILE. Oracle does read the datafile headers when creating the controlfile. The datafile headers may point to the same time (from a cold backup) or different times (from hot backups). The controlfile has no knowledge of Archivelogs. How can I have a "one command for all possibilities" ? I use the USING BACKUP CONTROLFILE syntax.


                          Finally, in all the above scenarios : Because I did a RECOVER .. UNTIL, I did an Incomplete Recovery. This requires an OPEN RESETLOGS. Also, because I specified USING BACKUP CONTROLFILE, the controlfile is again resynced when I OPEN RESETLOGS.


                          Hemant K Chitale
                          http://hemantoracledba.blogspot.com

                          Edited by: Hemant K Chitale on Jan 3, 2011 2:37 PM
                          • 10. Re: Recover using backup controlfile
                            Catch-22
                            Thank you very much for your reply. I read it several times, but I'm not sure if I can follow.

                            I have a few questions please: (sorry I hope I don't take things out of context)
                            Also, because I specified USING BACKUP CONTROLFILE, the controlfile is again resynced when I OPEN RESETLOGS.
                            Is't this what a open resetlogs does, to reset the SCN to 1, hence syncing the control file and data file headers?

                            I believe this is why it was necessary, at least prior to Oracle 10g, to perform a full backup. However this is no longer necessary, and I wonder what changed?
                            The controlfile has no knowledge of Archivelogs. How can I have a "one command for all possibilities" ? I use the USING BACKUP CONTROLFILE syntax.
                            From what I understand, "recover until cancel" reads redo logs until there are no more redologs - all applied, hence complete recovery, or until I tell it to stop, i.e recover to previous point in time, or archivelogs are missing. The restored controlfile does not have info about the archivelog files as you say, but then I will need to add/catalog them in the controlfile unless I use FRA, so again why do I need "using backup controlfile"?
                            • 11. Re: Recover using backup controlfile
                              Catch-22
                              I found another link:

                              http://esemrick.blogspot.com/2006/02/backup-control-files-are-they-special.html

                              "A backup control file is an image of the database control file. But this image possesses very distinctive qualities. It contains a file type flag (value=4) that tells Oracle it is a backup control file."

                              "By instructing Oracle to recover using a backup control file you are telling Oracle to avoid the redo thread records and log file records in the control file – and for good reason.

                              Well, the above makes sense, although, since the backup controlfile is marked, it would mean the "using backup controlfile" statement is just to confirm that you know what you are doing..."

                              Edited by: Dude on Jan 3, 2011 4:12 AM
                              • 12. Re: Recover using backup controlfile
                                Hemant K Chitale
                                A Controlfile Backup that has been created with the "ALTER DATABASE BACKUP CONTROLFILE TO 'location'" command is a binary backup of the controlfile. Such a file does contain a flag that tells Oracle that it is a backup and not a current controlfile.


                                However, in a RECOVER DATABASE scenario, you might be using a "current" controlfile from a 3-day old COLD Backup. The only way you can have Oracle ignore the fact that it is a "current" controlfile (one that does not have the backup flag in it !), is to specify "USING BACKUP CONTROLFILE" in the RECOVER DATABASE command. This is what I have described in scenarios A-1 and A-2.



                                Hemant K Chitale

                                Edited by: Hemant K Chitale on Jan 4, 2011 11:41 PM
                                • 13. Re: Recover using backup controlfile
                                  Hemant K Chitale
                                  Is't this what a open resetlogs does, to reset the SCN to 1, hence syncing the control file and data file headers?
                                  In scenario C, the CREATE CONTROLFILE had been executed against Datafiles likely having with different SCNs and timestamps -- because the Datafiles were backed up at different times (the differences could be in minutes or days).
                                  So, the created controlfile does not have a "single" SCN as the database reference (as a CURRENT Controlfile would have). It wouldn't be correct to use the highest datafile SCN either because Recovery can and should proceed beyond that. Therefore, the controlfile has to be resynced after all the datafiles are recovered to a consistent point in time (SCN).

                                  but then I will need to add/catalog them in the controlfile unless I use FRA, so again why do I need "using backup controlfile"?
                                  Not every one uses or has to use RMAN. Those not using RMAN, those using scripts from earlier versions, scripts from vendors may not be using the "CATALOG" command from RMAN.


                                  Hemant K Chitale
                                  • 14. Re: Recover using backup controlfile
                                    Catch-22
                                    I just tried it with a cold backup of the controlfile:
                                    Errors in file /u01/app/oracle/diag/rdbms/clonedb/clonedb/trace/clonedb_rvwr_2271.trc:
                                    ORA-38739: Flashback log file is more recent than control file.
                                    ORA-38701: Flashback database log 9 seq 9 thread 1: "/scratch/fra/CLONEDB/flashback/o1_mf_6l1r5d54_.flb"
                                    Database mounted in Exclusive Mode
                                    Lost write protection disabled
                                    Completed: ALTER DATABASE   MOUNT
                                    Tue Jan 04 16:49:46 2011
                                    ALTER DATABASE OPEN
                                    Errors in file /u01/app/oracle/diag/rdbms/clonedb/clonedb/trace/clonedb_ora_2272.trc:
                                    ORA-38760: This database instance failed to turn on flashback database
                                    ORA-38760 signalled during: ALTER DATABASE OPEN...
                                    Tue Jan 04 16:50:14 2011
                                    ALTER DATABASE RECOVER  database using backup controlfile until cancel  
                                    Media Recovery Start
                                    Media Recovery failed with error 38760
                                    ORA-283 signalled during: ALTER DATABASE RECOVER  database using backup controlfile until cancel  ...
                                    
                                    
                                    SQL> alter database flashback off;
                                    
                                    Database altered.
                                    
                                    SQL> recover database using backup controlfile until cancel;
                                    ORA-00279: change 1814866 generated at 01/04/2011 16:45:27 needed for thread 1
                                    ORA-00289: suggestion :
                                    /scratch/fra/CLONEDB/archivelog/2011_01_04/o1_mf_1_1_6l6jbct3_.arc
                                    ORA-00280: change 1814866 for thread 1 is in sequence #1
                                    
                                    
                                    Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
                                    cancel
                                    ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
                                    ORA-01152: file 1 was not restored from a sufficiently old backup
                                    ORA-01110: data file 1:
                                    '/scratch/oradata/clonedb/datafile/system.256.737564331'
                                    
                                    
                                    
                                    SQL> recover database until cancel;
                                    ORA-00283: recovery session canceled due to errors
                                    ORA-01610: recovery using the BACKUP CONTROLFILE option must be done
                                    1 2 Previous Next