SCN Explained

How Oracle transaction data change is written to a datafile

1. Transaction starts
2. Required data blocks are found in the buffer cache. If not found in the buffer cache, then found in the datafile
3. Transaction modifies the data blocks in buffer cache, identified as "dirty data" and is written to the log buffer
4. Transaction is committed and LGWR process writes the dirty data in the log buffer to the redo log file
5. CKPT process updates the headers of all the datafiles and DBWn process writes the dirty data from Buffer Cache to the datafile

SCN - system change number. the SCN is an internal number maintained by the database to log the changes made. it is sort of an Oracle clock - every time transaction commits, it increments. There are a total of 4 SCN : System checkpoint SCN, the datafile checkpoint SCN, Start SCN and Stop SCN.

System checkpoint SCN - when a checkpoint operation is cimpleted, the Oracle stores the system checkpoint SCN in the control file

SELECT checkpoint_change# FROM V$DATABASE

It is for the entire database.

Datafile checkpoint SCN - when checkpoint operation is completed, Oracle stores it separately in the control file

SELECT name, checkpoint_change# FROM V$DATAFILE

Start SCN - checkpoint SCN stored in the file header of each datfile. it is called start SCN because it is used to start the database instance

SELECT name, checkpoint_change# FROM V$DATAFILE_HEADER

Stop SCN - SCN termination of each datafile header are stored in the control file

SELECT name, last_change# FROM V$DATAFILE

During the normal operation, stop SCN (last_change# field) is an infinite number or is NULL.

Current SCN is retrieved using:

select dbms_flashback.get_system_change_number from dual;

Whenever a transaction is comitted, the log writer process (LGWR) writes the records from the redo log buffers in SGA to the online redo logs on disk. LGWR also writes the SCN for the transaction to that online redo log file.

When all modified database buffers are written out to datafiles byt the database writer (DBWn) process, a checkpoint occurs. Checkpoint (CKPT) updates all database files and control files with the SCN at the time of the checkpoint. A successful checkpoint guarantees that all database changes up to that SCN have been recorded in the datafiles. As a result, only those changes that occurred afetr the checkpoint, need to be applied during recovery.

Checkpoint typically occurs at intervals specified in LOG_CHECKPOINT_INTERVAl parameter or whenever redo log switch takes place. Oracle stores SCN associated with that checkpoint in 4 places: 3 in control files and 1 in the datafile header for each datafile.

How to determine whether clean shutdown has occurred?

Run

select name,checkpoint_change# from v$datafile_header where name like '%datafilenamehere%';

If a clean shutdown has occurred, the CHECKPOINT_CHANGE# and LAST_CHANGE# will match for each datafile. if an instance crashes at shutdown, the numbers will be different and instance crash recovery will be required at the next instance start up.

Upon the next instance startup Oracle will detect that the stop SCN for datafiles was not set in the control file during startup. So during crash recovery oracle will apply all the redo log records from online redo logs in a process of rolling forward - to ensure that all transactions committed before the crash are applied to the datafiles. Also active transactions that did not commit before the crash will be rolled back.

Instance recovery happens automatically at startup without DBA's intervention.

While the database is up and open for use, the system checkpoint in the control file, the datafile checkpoint SCN in the control file, and the start SCN in each datafile header all match.

During a clean shutdown, a checkpoint is performed and the stop SCN for each datafile is set to the start SCN from the datafile header. Upon startup, Oracle checks the start SCN in the file header with the datafile checkpoint SCN. If they match, Oracle checks the start SCN in the datafile header with the datafile stop SCN in the control file. If they match, the database can be opened because all block changes have been applied, no changes were lost on shutdown, and therefore no recovery is required on startup. After the database is opened, the datafile stop SCN in the control file once again changes to NULL to indicate that the datafile is open for normal use.

System crash - For database after a system crash, upon startup, the stop SCN is not set (LAST_CHANGE#) and thus the Oracle will know the instance crashed because the checkpoint on a shutdown was not performed. If it had been performed, the CHECKPOINT_CHANGE# and LAST_CHANGE# values would match for each datafile as they do during clean shutdown.

During crash recovery, Oracle applies redo log records from the online redo logs in a process referred to as roll forward to ensure that all transactions committed before the crash are applied to the datafiles. Following roll forward, active transactions that did not commit are identified from the rollback segments and are undone before the blocks involved in the active transactions can be accessed. This process is referred to as roll back.

Media failure - In case of a media failure, the datafile checkpointSCN and start SCN in the datafile header won't match at instance startup.