Consistency Test and open consistency test in the open stage

Consistency Test and open consistency test in the open stage
In the open stage, oracle requires consistency check before it can open the database. What tests have been done?
Check whether the Checkpoint CNT in the data file header is consistent with the Checkpoing CNT in the corresponding control file.
Check whether the starting SCN of the data file header is consistent with the ending SCN in the corresponding control file. If the ending SCN is equal to the starting SCN, you do not need to restore the data file.
The following is a dump of the analysis control file and the No. 1 data file:
SQL> select * from v $ version where rownum = 1;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-Production
SQL> startup force mount;
The ORACLE routine has been started.
Total System Global Area 422670336 bytes
Fixed Size 1336960 bytes
Variable Size 360712576 bytes
Database Buffers 54525952 bytes
Redo Buffers 6094848 bytes
The database has been loaded.
SQL> alter session set events 'immediate trace name controlf level 8 ';

The session has been changed.

SQL> select value from v $ diag_info where name = 'default Trace file ';

VALUE
--------------------------------------------------------------------------------
/U01/app/oracle/diag/rdbms/orcl3939/orcl3939/trace/orcl3939_ora_8858.trc


**************************************** ***********************************
DATABASE ENTRY
**************************************** ***********************************
(Size = 316, compat size = 316, section max = 1, section in-use = 1,
Last-recid = 0, old-recno = 0, last-recno = 0)
(Extent = 1, blkno = 1, numrecs = 1)
09/28/2014 17:41:29
DB Name "ORCL3939"
Database flags = 0x00404001 0x00001200
Controlfile Creation Timestamp 09/28/2014 17:41:31
Incmplt recovery scn: 0x0000.00000000
Resetlogs scn: 0x0000. 000b8338 Resetlogs Timestamp 09/28/2014 17:41:34
Prior resetlogs scn: 0x0000.00000001 Prior resetlogs Timestamp 08/13/2009 23:00:48
Redo Version: compatible = 0xb200000
# Data files = 10, # Online files = 10
Database checkpoint: Thread = 1 scn: 0x0000. 0076948a
Threads: # Enabled = 1, # Open = 0, Head = 0, Tail = 0
Enabled threads: 01000000 00000000 00000000 00000000 00000000
....
....
Max log members = 3, Max data members = 1
Arch list: Head = 3, Tail = 3, Force scn: 0x0000. 00746fa2scn: 0x0000. 00768e3
Activation ID: 3848061321
Controlfile Checkpointed at scn: 0x0000. 00768dc1 05/05/2015 12:24:31
Thread: 0 rba :( 0x0. 0.0)
Enabled threads: 00000000 00000000 00000000 00000000 00000000
....
....
 
**************************************** ***********************************
DATA FILE RECORDS
**************************************** ***********************************
(Size = 520, compat size = 520, section max = 100, section in-use = 13,
Last-recid = 2877, old-recno = 0, last-recno = 0)
(Extent = 1, blkno = 11, numrecs = 100)
Data file #1:
Name #7:/u01/app/oracle/oradata/orcl3939/system01.dbf
Creation size = 0 block size = 8192 status = 0xe head = 7 tail = 7 dup = 1
Tablespace 0, index = 1 krfil = 1 prev_file = 0
Unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
Checkpoint cnt: 1110 scn: 0x0000. 0076948a 05/05/2015 12:41:20
Stop scn: 0x0000. 0076948a 0
5/05/2015 12:41:20
Creation Checkpointed at scn: 0x0000.00000007 08/13/2009 23:00:53
Thread: 0 rba :( 0x0. 0.0)
Enabled threads: 00000000 00000000 00000000 00000000 00000000
....
....
Offline scn: 0x0000. 000b8337 prev_range: 0
Online Checkpointed at scn: 0x0000. 000b8338 09/28/2014 17:41:34
Thread: 1 rba :( 0x1. 2.0)
Enabled threads: 01000000 00000000 00000000 00000000 00000000
....
....
Hot Backup end marker scn: 0x0000.00000000
Aux_file is NOT DEFINED
Plugged readony: NO
Plugin scnscn: 0x0000.00000000
Plugin resetlogs scn/timescn: 0x0000.00000000 01/01/1988 00:00:00
Foreign creation scn/timescn: 0x0000.00000000 01/01/1988 00:00:00
Foreign checkpoint scn/timescn: 0x0000.00000000 01/01/1988 00:00:00
Online move state: 0





SQL> alter session set events 'immediate trace name file_hdrs level 10 ';
The session has been changed.
SQL> select value from v $ diag_info where name = 'default Trace file ';

VALUE
--------------------------------------------------------------------------------
/U01/app/oracle/diag/rdbms/orcl3939/orcl3939/trace/orcl3939_ora_8858.trc

Data file #1:
Name #7:/u01/app/oracle/oradata/orcl3939/system01.dbf
Creation size = 0 block size = 8192 status = 0xe head = 7 tail = 7 dup = 1
Tablespace 0, index = 1 krfil = 1 prev_file = 0
Unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
Checkpoint cnt: 1110 scn: 0x0000. 0076948a 05/05/2015 12:41:20
Stop scn: 0x0000. 0076948a 05/05/2015 12:41:20
Creation Checkpointed at scn: 0x0000.00000007 08/13/2009 23:00:53
Thread: 0 rba :( 0x0. 0.0)
Enabled threads: 00000000 00000000 00000000 00000000 00000000
....
....
Offline scn: 0x0000. 000b8337 prev_range: 0
Online Checkpointed at scn: 0x0000. 000b8338 09/28/2014 17:41:34
Thread: 1 rba :( 0x1. 2.0)
Enabled threads: 01000000 00000000 00000000 00000000 00000000
....
....
Hot Backup end marker scn: 0x0000.00000000
Aux_file is NOT DEFINED
Plugged readony: NO
Plugin scnscn: 0x0000.00000000
Plugin resetlogs scn/timescn: 0x0000.00000000 01/01/1988 00:00:00
Foreign creation scn/timescn: 0x0000.00000000 01/01/1988 00:00:00
Foreign checkpoint scn/timescn: 0x0000.00000000 01/01/1988 00:00:00
Online move state: 0
The above information comes from the Control File
The following information comes from the data file header (if the data file is lost, the data file header cannot be read)
V10 style file header:
Compatibility Vsn = 186646528 = 0xb200000
Db ID = 3848072073 = 0xe55ceb89, Db Name = 'orcl3939'
Activation ID = 0 = 0x0
Control Seq = 14952 = 0x3a68, File size = 96000 = 0x17700
File Number = 1, blksiz= 8192, File Type = 3 DATA
Tablespace #0-SYSTEM rel_fn: 1
Creation at scn: 0x0000.00000007 08/13/2009 23:00:53
Backup taken at scn: 0x0000. 00713a30 04/29/2015 13:41:44 thread: 1
Reset logs count: 0x333ab14e scn: 0x0000. 000b8338
Prev reset logs count: 0x296a3120 scn: 0x0000.00000001
Recovered at 05/05/2015 12:24:15
Status: 0x2000 root dba: 0x00400208 chkpt cnt: 1110 ctl cnt: 1109
Begin-hot-backup file size: 96000
Checkpointed at scn: 0x0000. 0076948a 05/05/2015 12:41:20
Thread: 1 rba :( 0x1ce. 1314.10)
Enabled threads: 01000000 00000000 00000000 00000000 00000000
....
....
Backup Checkpointed at scn: 0x0000. 00713a30 04/29/2015 13:41:44
Thread: 1 rba :( 0x1bd. b6c8.10)
Enabled threads: 01000000 00000000 00000000 00000000 00000000
....
....

First, check whether the Checkpoint CNT in the data file header is consistent with the Checkpoing CNT in the corresponding control file:
The chkpt cnt 1110 data file header is recorded in the previous knowledge control file, and the chkpt cnt 1110 ctl cnt 1109
Why is chkpt cnt 1 larger than ctl cnt in the data file header? This is because the checkpoint can obtain the current ctl cnt when updating the chkpt cnt in the control file and data file header, write the current ctl cnt to the data file header, that is, 1109
This verification has passed
Then, check whether the starting SCN of the data file header is consistent with the ending SCN in the corresponding control file:
Checkpointed at scn: 0x0000. 0076948a 05/05/2015 12:41:20
The two are the same and can be started normally.
The scn of the control file record is the scn of the database's last successful checkpoint. The scn of the data file header record is the scn of the last checkpoint completed. The two are equal, you do not need to restore your data files. If they are inconsistent, you need

. If the scn record in the data file header is old and is inconsistent with that in the control file after the database is shut down due to an exception, the database must be restored and the scn record in the data file header is the starting point of recovery.

SQL> shutdown immediate;
The database has been closed.
The database has been detached.
The ORACLE routine has been disabled.
SQL> startup mount;
The ORACLE routine has been started.
Total System Global Area 422670336 bytes
Fixed Size 1336960 bytes
Variable Size 360712576 bytes
Database Buffers 54525952 bytes
Redo Buffers 6094848 bytes
The database has been loaded.
SQL> select file #, checkpoint_change # from v $ datafile;


FILE # CHECKPOINT_CHANGE #
----------------------------
1 7771815
2 7771815
3 7771815
4 7771815
5 7771815
7 7771815
8 7771815
9 7771815
11 7771815
12 7771815


10 rows have been selected.


SQL> select file #, checkpoint_change # from v $ datafile_header;


FILE # CHECKPOINT_CHANGE #
----------------------------
1 7771815
2 7771815
3 7771815
4 7771815
5 7771815
7 7771815
8 7771815
9 7771815
11 7771815
12 7771815

10 rows have been selected.

The two are the same and there is no problem. You can simulate the case to be restored.