This content has been marked as final. Show 9 replies
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.
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'.
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.
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
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 ?
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_LOG1 person found this helpful
Hemant K Chitale
1 person found this helpful
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.
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.1 person found this helpful
Many thanks to all for the help!