If an instance is dirty closed (SHUTDOWN ABORT, reboot of the server, kill -9, etc), you have two scenario:
- Data blocks modified aren't yet written to disk into datafiles and the informations are only into the Redo Log File online
- Some data blocks into datafile contains data not yet committed
- In the first case, changes need to re-applied to the database (roll-forward)
- In the second case, the changes have to disappear (roll-back)
The V$DATABASE view, hase two columns that we are interested
When an instance is opened, V$DATAFILE.CHECKPOINT_CHANGE# is the SCN at last checkpoint (1) while V$DATAFILE.LAST_CHANGE# is NULL. Oracle set to NULL V$DATAFILE.LAST_CHANGE# so it know that the database is working.
If the database is clean closed, Oracle update the V$DATAFILE.LAST_CHANGE# column to the last SCN. But if the close is dirty, Oracle can't to do this update and the V$DATAFILE.LAST_CHANGE# column remain NULL, so at the next startup Oracle knows that last close was not clean and the instance recovery is necessary.
So, when an issue force a shutdown abort (for example), the db is closed before V$DATAFILE.LAST_CHANGE# can be updated to V$DATAFILE.CHECKPOINT_CHANGE#.
In other words, when both SCN are the same (V$DATAFILE.LAST_CHANGE# and V$DATAFILE.CHECKPOINT_CHANGE#) we have no problem, but if V$DATAFILE.LAST_CHANGE# is NULL then the instance need recovery.
Question.If V$DATAFILE.LAST_CHANGE# and V$DATAFILE.CHECKPOINT_CHANGE# have the same value, are we out of any issue?
The answer is no. Not entirely. This is because Oracle do another check on the datafiles. He verify that V$DATAFILE_HEADER.CHECKPOINT_CHANGE# (2) and V$DATABASE.CHECKPOINT_CHANGE# (3) are the same. If not, than media recovery is needed.