Difference between Media Recovery  & Crash Recovery

To be simple and easy to remember. Its main funda is below. 

SCN in datafiles - v$datafile_header

SCN of datafiles in controlfile - v$datafile

If the SCN in datafile header is not matching with the Control file that means datafile need recovery

Crash recovery vs media recovery

STOP SCN equal NULL ==> CRASH RECOVERY NEED

DATAFILE HEADER START SCN SCN CONTROLFILE not equal ==> MEDIA NEED RECOVERY

Difference Between "SCN and ORA_ROWSCN" -

SCN is like ticker/end of every transaction in database.

whereas ORA_ROWSCN is an observed point in time. The ora_rowscn is a value associated to a block or a row on a block that represents the “time” the block/row was last modified.

What happens inside RECOVERY of oracle database ?

Well it commonly deals with 2 problems in general.

RECOVERY DATABASE two common problems

1) RECOVER DATABASE UNTIL CANCEL ==> OPEN DATABASE RESETLOG

==> DATAFILE HEADER SCN will be less than the DATAFILE SCN CONTROLFILE

If you have performed RESTORE DATAFILE, of the RESTORE DATAFILE HEADER SCN will be less than the current CONTROLFILE the DATAFILE SCN, this time will be unable to open the database, you must redo archive log media recovery ~~ until the datafile header of SCN = current scn. After the restore datafile, you can mount database and then went to check the controlfile and datafile header SCN

2) RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE; ===> OPEN DATABASE RESETLOG

==> DATAFILE HEADER SCN will be greater than the DATAFILE SCN CONTROLFILE

this time after Oracle RESTOREC CONTROL FILE SYSTEM SCN CONTROL FILE'll find inside will be less than the current DATAFILE HEADER SCN, nor equal to the current stored in the LOG SCN FILE inside, then you must use the RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE to DROP DATAFILE OR DROP TABLESPACE before the SCN.

Appendix:

Each rollback segment is composed of two distinct components, the undo header and undo entry. The first block of a rollback segment is reserved for the undo header. When a rollback segment is created, only the header is created. Initially, the remaining blocks are allocated to the segment, but no other structures are created. One of the main features in the rollback segment header is the Transaction Table. Key components are the state, scn, and dba.

 By default, the ora_rowscn pseudo column reports the scn at block level. This means that rows sharing the same block will have the same ora_rowscn, and if one row were to be changed, then scn would be altered for both blocks.

As a query enters the execution stage, the current system change number (SCN) is determined. this system change number is <123>. As data blocks are read on behalf of the query, only blocks written with the observed SCN are used. Blocks with changed data (more recent SCNs) are reconstructed from data in the rollback segments, and the reconstructed data is returned for the query. Therefore, each query returns all committed data with respect to the SCN recorded at the time that query execution began. Changes of other transactions that occur during a query's execution are not observed, guaranteeing that consistent data is returned for each query.By default a block-level SCN is recorded for each block. That SCN is the latest SCN for any record in the block.

 The checkpoint records the highest SCN of all changes to blocks such that all data blocks with changes below that SCN have been written to disk by DBWn. The control file also contains a record of the checkpoint SCN contained in the header of each datafile in the database. Whenever a discrepancy occurs between the SCN that is actually in a datafile header and the datafile header SCN listed in the control file, Oracle requires media recovery.

Every redo log file has both a log sequence number and low and high SCN. The low SCN records the lowest SCN recorded in the log file, while the high SCN records the highest SCN in the log file.

A consistent backup of a database or part of a database is a backup in which all read-write datafiles and control files have been checkpointed with respect to the same SCN.

An inconsistent backup s a backup in which all read-write datafiles and control files have not been checkpointed with respect to the same SCN. For example, one read-write datafile header may contain an SCN of 100 while others contain an SCN of 95. Oracle cannot open the database until these SCNs are consistent, that is, until all changes recorded in the online redo logs have been made to the datafiles.

Oracle enables you to restore an older backup and apply partial redo data, thereby recovering the database to a specified non-current time or SCN. This type of recovery is called incomplete recovery. You must open your database with a RESETLOGS operation after performing incomplete recovery in order to reset the online redo logs.

SCN & Instance Crash - the stop SCN is not set, which is indicated by the NULL value in the LAST_CHANGE# column. This information enables Oracle, at the time of the next startup, to determine that the instance crashed because the checkpoint on shutdown was not performed. If it had been performed, the LAST_CHANGE# and CHECKPOINT_CHANGE# values would match for each datafile as they did during a clean shutdown. If an instance crashes at shutdown, then instance crash recovery is required the next time the instance starts up.