I have restored the database and applied the archive log. How can I tell the latest transaction timestamp I have recovered to?

This is oracle 10g R2 standard (no flashback enable)


Copied from this page:

[...] ensure that all of the online datafiles are synchronized in terms of their SCN before we can normally open the database. So, run the following SQL statement, as shown below, to determine whether the datafiles are synchronized or not. Take note that we query the V$DATAFILE_HEADER, because we want to know the SCN recorded in the header of the physical datafile, and not the V$DATAFILE, which derives the information from the controlfile.

select status, checkpoint_change#, 
       to_char(checkpoint_time, 'DD-MON-YYYY HH24:MI:SS') as checkpoint_time, 
from v$datafile_header 
group by status, checkpoint_change#, checkpoint_time 
order by status, checkpoint_change#, checkpoint_time;

The results of the above query must return one and only one row for the online datafiles, which means that they are already synchronized in terms of their SCN. Otherwise, if the results return more than one row for the online datafiles, then the datafiles are still not synchronized yet. In this case, we need to apply archivelog(s) or redolog(s) to synchronize all of the online datafiles. By the way, take note of the CHECKPOINT_TIME in the V$DATAFILE_HEADER, which indicates the date and time how far the datafiles have been recovered.

To verify if an entire database has been recovered to the same SCN:

select checkpoint_change# from v$database;
  • scn_to_timestamp does not available before open. it said it is not defined. – J-16 SDiZ Nov 7 '10 at 16:09
  • 1
    yup, I've re-written my answer completely – kubanczyk Nov 8 '10 at 9:33
  • okay, this seems to be the best answer .. although it don't solve my real problem. – J-16 SDiZ Nov 8 '10 at 11:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.