SCN and RBA of Oracle

These two names are very common in alert log. Apart from Oracle, they are inevitably prone to a variety of problems and some are well solved, but some are heartbroken.

Example: ORA-00600

The description given by OERR is as follows:

// * Cause: Autologin unable to open port
// * Action: Check log file for OS-specific error code

Internal code error. Basically no swimming information is provided. At this time, you must check the alert log to obtain the information you want.

Generally, this problem caused by instance shutdown involves two concepts: scn and rba.

SCN (system change number) system change number

Redo block address of RBA (redo block address) onlin redo log

Use: select dbms_flashback.get_system_change_number from dual; to obtain the current SCN

The forward triggering of SCN is performed by commit, and the SCN is refreshed every 3 seconds.

When a checkpoint occurs, the CKPT process updates the current SCN of the database to the database file header and control file. The DBWn process writes the dirty data block (dirty block) in the buffer cache to the data file, instead, ckpt notifies the DBWn process after updating the control file and the data file header to generate a checkpoint, the DBWn process writes the dirty data in the buffer cache (the dirty data corresponding to the current online redo log) to our data file, which is basically such a cyclical process, the ckpt process also records the RBA in the control file. This address is used to indicate the location where the instance needs to be recovered from the log, that is, a checkpoint corresponds to an rba, when the instance recovery (instance recovery), oracle will know that the media recovery starts from the rba in the redo from that checkpoint!

Conditions for creating a checkpoint:

1. alter system checkpoint

2. alter system switch logfile

3. DBWn process writes dirty Blocks

View the scn in the control file: After the checkpoint is executed (stored in the control file), Oracle updates the System checkpoint SCN in the current control file.

Select checkpoint_change # from v $ database;

View the scn in the data file information recorded in the control file: (stored in the control file) the control file records the location and information of each database file in Oracle, of course, the Datafile checkpoint SCN is also included. Therefore, when the checkpoint is executed, Oracle will update the datafile checkpoint SCN of each data file recorded in the control file.

Select checkpoint_change # from v $ datafile;

View the start scn in the data file header: (saved in the data file header) when the checkpoint is executed, Oracle updates the Start SCN stored in each actual data file header (note that it is definitely not in the control file ), this SCN is used to check whether media recovery is required during database startup)

Select checkpoint_change # from v $ datafile_header;

View the end scn of the data file in the control file: (stored in the control file) each recorded data file header has a corresponding End SCN, which must exist in the control file. The absolute significance of this SCN is to verify whether instance recovery (instance recovery) is required during database startup ).

Select name, last_change # from v $ datafile