Instance Needs Recovery: Introduction (3/3)

Part 1
Part 2

If an instance is dirty closed (SHUTDOWN ABORT, reboot of the server, kill -9, etc), you have two scenario:

1. Data blocks modified aren't yet written to disk into datafiles and the informations are only into the Redo Log File online 
2. 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

• V$DATAFILE.CHECKPOINT_CHANGE#
• V$DATAFILE.LAST_CHANGE#

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.

(1) https://docs.oracle.com/cd/E11882_01/server.112/e40402/dynviews_1100.htm#REFRN30050
(2) https://docs.oracle.com/cd/E11882_01/server.112/e40402/dynviews_1102.htm#REFRN30052
(3) https://docs.oracle.com/cd/E11882_01/server.112/e40402/dynviews_1097.htm#REFRN30047

Part 1
Part 2