9 Replies Latest reply: Jan 30, 2013 9:37 PM by just a DBA RSS

    Database Point-in-Time Recovery

    just a DBA
      Hi, experts.
      I have interesting question. For example, I have consistent backup, backup cintrolfile and next archivelog. I want to recover database to some point in time or SCN in the middle of this log. How to check, before starting recovery, that this point in time or SCN will be consistent? How to find all consistent SCNs or SCN range that I can recover database to ?
        • 1. Re: Database Point-in-Time Recovery
          Paul M.
          How to find all consistent SCNs or SCN range that I can recover database to ?
          Not sure if we give the same meaning to "consistent", but all SCNs are "consistent" : see this AskTom thread, where you can read

          the system change number (SCN) is Oracle's clock - every time we commit, the clock
          increments. The SCN just marks a consistent point in time in the database.
          • 2. Re: Database Point-in-Time Recovery
            just a DBA
            Thank you for answer. I meant SCNs to which database could be recovered and opened without getting
            'RECOVER succeeded but OPEN RESETLOGS would get error below'.
            • 3. Re: Database Point-in-Time Recovery
              TSharma-Oracle
              Run this query to check the value of the fuzzy column, If the value of the fuzzy = NO,it means we can open our database without any error. It means datafiles are consistent.

              select status,checkpoint_change#,checkpoint_time,count(),fuzzy from v$datafile_header group by status,checkpoint_change#,checkpoint_time,fuzzy;*


              Whenever you see all the datafiles with FUZZY=NO, you can assume database is consistent and you can open the database without any error.
              Let me know if this helps.
              • 4. Re: Database Point-in-Time Recovery
                Hemant K Chitale
                After doing a RESTORE CONTROLFILE and RESTORE DATABASE, query for CHECKPOINT_CHANGE# in V$DATAFILE and NEXT_CHANGE#-1 in V$ARCHIVED_LOG.

                However, if the controlfile that you restore is from a point in time before the Archived Logs, you wouldn't be able to query V$ARCHIVED_LOG for the information (i.e. about ArchiveLogs that are later than the controlfile). If you are using an RMAN Recovery Catalog, you can query the Recovery Catalog Schema view RC_ARCHIVED_LOG for NEXT_CHANGE#-1

                NEXT_CHANGE#-1 is the last SCN in the ArchivedLogs that you have.


                Hemant K Chitale
                • 5. Re: Database Point-in-Time Recovery
                  just a DBA
                  Thanks to everybody for answers!
                  As far as I understand, if backup is already consistent (all the datafiles with FUZZY=NO), I can recover the backup to any SCN forward and this SCN is not required to be v$archived_log.FIRST_CHANGE#, i.e. checkpoint SCN ?
                  • 6. Re: Database Point-in-Time Recovery
                    Hemant K Chitale
                    You can recover to any SCN that is equal to or higher than the highest CHECKPOINT_CHANGE# in V$DATAFILE but equal to or less than the highest NEXT_CHANGE#-1 in V$ARCHIVED_LOG

                    Hemant K Chitale
                    • 7. Re: Database Point-in-Time Recovery
                      Mihael
                      can recover the backup to any SCN forward and this SCN is not required to be v$archived_log.FIRST_CHANGE#, i.e. checkpoint SCN ?
                      Checkpoint SCN is required for cache recovery in case of instance crash. It is irrelevant for media recovery - any SCN you recover database to will be checkpoint SCN. You only need archived logs that contains this SCN from all threads.
                      • 8. Re: Database Point-in-Time Recovery
                        TSharma-Oracle
                        No Practically if the database is big and there are lots of archives being generated during the RMAN Backups(for any reason could be the batch job), you will hardly get FUZZY=NO after restore. You will have to apply the archives to get the consistent SCN's to successfully open the database.
                        • 9. Re: Database Point-in-Time Recovery
                          just a DBA
                          Many thanks to all for the help!