Role of SCN in Instance Recovery & Media Recovery

In last post we have discussed the different types of checkpoints and association of SCN. Also we have examined the status of datafile headers wrt End (checkpoint) SCN after a clean shutdown. In this post we are examining role of SCN in instance recovery and media recovery.

SCN after an Instance Crash

In this session we are going to demonstrate the behavior of the checkpoints after an instance crash, the following SQL creates a table (which performs an implicit commit) and inserts a row of data into it without a commit:

SQL> create table dummytable (x number) tablespace test;
Table created.

SQL> insert into dummytable values(100);
1 row created.


During subsequent startup after instance crash Oracle will realize before opening database that value for Last_Change# column for datafiles are not showing SCN value , which means END Checkpoint SCN was not done, which would be indication for Oracle that instance recovery is required.

Let’s mimic instance failure and then startup mount to examine the situation