Verification checkpoint SCN during Oracle Database startup

Verification checkpoint SCN during Oracle Database startup

SCN (System Change Number) is an important mechanism for Oracle databases. SCN is used when the database is started, read consistency, data recovery, and FLASHBACK operations.

1. the SCN number is an important time mechanism and can be converted between the two.

1). Convert the time to the SCN Number:
SQL> select timestamp_to_scn (sysdate) from dual;

TIMESTAMP_TO_SCN (SYSDATE)
-------------------------
920939

2). Convert the SCN number to the specific time:

SQL & gt; select scn_to_timestamp (920000) from dual;

SCN_TO_TIMESTAMP (920000)
---------------------------------------------------------------------------
07-FEB-10 01.06.11.000000000 AM

3) view the latest SCN Number of the current database:
SQL> select current_scn from v $ database;

CURRENT_SCN
-----------
920782
The latest SCN number, which is constantly refreshed in the memory and refreshed every 3 seconds for Oracle 10 Gb.

2. Check the SCN number during the database startup process.

The following describes four types of SCN. These four types of SCN are actually checkpoint SCN, which are updated by the CKPT process and stored in the control file and data file header, different types of SCN have different effects.

1). Database checkpoint SCN:

 

SQL> select checkpoint_change # from v $ database;

CHECKPOINT_CHANGE #
------------------
920459

The database checkpoint SCN. Each time a checkpoint is executed, the CKPT process updates the database checkpoint SCN in the control file.

2). Data File SCN:
 

SQL> select checkpoint_change # from v $ datafile;

CHECKPOINT_CHANGE #
------------------
920459
920459
920459
920459
920459
920459
920459
920459
920459
920459
920459

36 rows selected.

The data file SCN. Each data file has a data file SCN. Each time a checkpoint is executed, the CKPT process updates the data file SCN located in the control file.

3). Data File startup SCN:
 

SQL> select checkpoint_change # from v $ datafile_header;

CHECKPOINT_CHANGE #
------------------
920459
920459
920459
920459
920459
920459
920459
920459
920459
920459
920459

36 rows selected.

Start SCN. Each data file has a startup SCN. Every time a checkpoint is executed, the CKPT process updates the checkpoint SCN in the data file header, which is called Startup SCN.

4). Data File termination SCN:
 

SQL> select last_change # from v $ datafile;

LAST_CHANGE #
------------

36 rows selected.

Terminate SCN. Each data file has a terminate SCN, which is stored in the control file. When the database is OPEN, the value becomes # ffffff infinity.

During database startup, the verification of SCN is divided into two phases:
In the first stage, check whether the number of checkpoints of data files in the control file and data file is consistent (the number of checkpoints of different data files may be different). If they are inconsistent, it means that the control file is too old, if the data file is too old, you need to perform the recovery operation. If the data file is consistent, continue to perform the next check.

In the second stage, check whether the startup and termination of the data file are consistent. If the database is not shut down normally, the termination of the SCN must be infinite. In this case, you need to complete the instance recovery process; if the startup and termination of SCN are the same, the database can be OPEN.

In addition, database SCN, data file SCN, and startup SCN stored in the control file header are actually updated by the CKPT process on the control file and data file header when the check point is executed, the three must be the same. When shutdown immediate | NORMAL | TRANSANTIONAL is executed to close the database, the checkpoint is triggered again, at this time, the first three types of SCN and the termination SCN stored in the control file must be the same.

Next we will verify the above statement by dumping the control file and data file header:

1). The database is shut down normally.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 599785472 bytes
Fixed Size 2022600 bytes
Variable Size 230687544 bytes
Database Buffers 360710144 bytes
Redo Buffers 6365184 bytes
Database mounted.
SQL> @? /Rdbms/admin/gettrace

TRACE_FILE_NAME
--------------------------------------------------------------------------------
/U01/app/oracle1/admin/orcl/udump/orcl_ora_4132.trc

SQL> alter session set events 'immediate trace name controlf level 8 ';

Session altered.
After dumping the control file, let's take a look at the content of the orcl_ora_4132.trc tracking file:
A). database content stored in the control file:
**************************************** ***********************************
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)
02/19/2014 14:20:20
DB Name "ORCL"
Database flags = 0x50404001 0x00001000
Controlfile Creation Timestamp 02/19/2014 14:20:21
Incmplt recovery scn: 0x0000.00000000
Resetlogs scn: 0x0000.00080634 Resetlogs Timestamp 02/19/2014 14:20:23
Prior resetlogs scn: 0x0000.00000001 Prior resetlogs Timestamp 10/22/2005 21:44:08
Redo Version: compatible = 0xa200100
# Data files = 6, # Online files = 6
Database checkpoint: Thread = 1 scn: 0x0000. 00091eb4
Threads: # Enabled = 1, # Open = 0, Head = 0, Tail = 0
......
Max log members = 3, Max data members = 1
Arch list: Head = 2, Tail = 2, Force scn: 0x0000. 00091e72scn: 0x0000. 00091e88
Activation ID: 1367751332
Controlfile Checkpointed at scn: 0x0000. 00091e88 04/16/2014 13:54:24
Thread: 0 rba :( 0x0. 0.0)
......
The database checkpoint SCN number in bold and red.

B) The number of data file checkpoints, SCN numbers, and stop SCN numbers stored in the control file:
**************************************** ***********************************
DATA FILE RECORDS
**************************************** ***********************************
(Size = 428, compat size = 428, section max = 100, section in-use = 6,
Last-recid = 9, old-recno = 0, last-recno = 0)
(Extent = 1, blkno = 11, numrecs = 100)
Data file #1:
(Name #7)/u01/app/oracle1/oradata/orcl/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: 183 scn: 0x0000. 00091eb4 04/16/2014 13:54:27
Stop scn: 0x0000. 00091eb4 04/16/2014 13:54:27
Creation Checkpointed at scn: 0x0000.00000008 10/22/2005 21:44:31
Thread: 0 rba :( 0x0. 0.0)
Cnt indicates the number of checkpoints, scn indicates the data file checkpoint SCN, and Stop scn indicates that the data file stops SCN.

Data file #2:
(Name #6)/u01/app/oracle1/oradata/orcl/undotbs01.dbf
Creation size = 0 block size = 8192 status = 0xe head = 6 tail = 6 dup = 1
Tablespace 1, index = 2 krfil = 2 prev_file = 0
Unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
Checkpoint cnt: 145 scn: 0x0000. 00091eb4 04/16/2014 13:54:27
Stop scn: 0x0000. 00091eb4 04/16/2014 13:54:27
Creation Checkpointed at scn: 0x0000. 0007fd42 10/22/2005 22:24:27
Thread: 0 rba :( 0x0. 0.0)

Data file #3:
(Name #5)/u01/app/oracle1/oradata/orcl/sysaux01.dbf
Creation size = 0 block size = 8192 status = 0xe head = 5 tail = 5 dup = 1
Tablespace 2, index = 3 krfil = 3 prev_file = 0
Unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
Checkpoint cnt: 183 scn: 0x0000. 00091eb4 04/16/2014 13:54:27
Stop scn: 0x0000. 00091eb4 04/16/2014 13:54:27
Creation Checkpointed at scn: 0x0000.00001896 10/22/2005 21:44:46
Thread: 0 rba :( 0x0. 0.0)

Data file #4:
(Name #4)/u01/app/oracle1/oradata/orcl/users01.dbf
Creation size = 0 block size = 8192 status = 0xe head = 4 tail = 4 dup = 1
Tablespace 4, index = 4 krfil = 4 prev_file = 0
Unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
Checkpoint cnt: 182 scn: 0x0000. 00091eb4 04/16/2014 13:54:27
Stop scn: 0x0000. 00091eb4 04/16/2014 13:54:27
Creation Checkpointed at scn: 0x0000. Limit 27b9 10/22/2005 21:45:00
Thread: 0 rba :( 0x0. 0.0)

Data file #5:
(Name #9)/u01/app/oracle1/oradata/orcl/gguser. dbf
Creation size = 128000 block size = 8192 status = 0xe head = 9 tail = 9 dup = 1
Tablespace 6, index = 6 krfil = 5 prev_file = 0
Unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
Checkpoint cnt: 135 scn: 0x0000. 00091eb4 04/16/2014 13:54:27
Stop scn: 0x0000. 00091eb4 04/16/2014 13:54:27
Creation Checkpointed at scn: 0x0000.00083314 02/19/2014 14:33:57
Thread: 1 rba :( 0x2. c04b. 10)

Data file #6:
(Name #10)/u01/app/oracle1/oradata/orcl/ogg2.dbf
Creation size = 640 block size = 8192 status = 0x86 head = 10 tail = 10 dup = 1
Tablespace 7, index = 7 krfil = 6 prev_file = 0
Unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
Checkpoint cnt: 3 scn: 0x0000. 00089cfb 03/18/2014 13:32:52
Stop scn: 0x0000. 00089cfb 03/18/2014 13:32:52
Creation Checkpointed at scn: 0x0000. 00089bd3 03/18/2014 13:29:26
Thread: 1 rba :( 0x3. e2.10)

From the above content, we can see that the number of checkpoints for each data file is different; except for the SCN number different from data file 6, other DATA files are the same; the SCN numbers of each data file are the same as those of the Stop SCN. Except for data file 6, the SCN and stop scn numbers of other DATA files are the same as those of the previous database.

 

C). Dump the number of data file checkpoints stored in the data file header and enable SCN:
Data file #1:
(Name #7)/u01/app/oracle1/oradata/orcl/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: 183 scn: 0x0000. 00091eb4 04/16/2014 13:54:27
Stop scn: 0x0000. 00091eb4 04/16/2014 13:54:27
Creation Checkpointed at scn: 0x0000.00000008 10/22/2005 21:44:31

Data file #2:
(Name #6)/u01/app/oracle1/oradata/orcl/undotbs01.dbf
Creation size = 0 block size = 8192 status = 0xe head = 6 tail = 6 dup = 1
Tablespace 1, index = 2 krfil = 2 prev_file = 0
Unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
Checkpoint cnt: 145 scn: 0x0000. 00091eb4 04/16/2014 13:54:27
Stop scn: 0x0000. 00091eb4 04/16/2014 13:54:27
Creation Checkpointed at scn: 0x0000. 0007fd42 10/22/2005 22:24:27

Data file #3:
(Name #5)/u01/app/oracle1/oradata/orcl/sysaux01.dbf
Creation size = 0 block size = 8192 status = 0xe head = 5 tail = 5 dup = 1
Tablespace 2, index = 3 krfil = 3 prev_file = 0
Unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
Checkpoint cnt: 183 scn: 0x0000. 00091eb4 04/16/2014 13:54:27
Stop scn: 0x0000. 00091eb4 04/16/2014 13:54:27
Creation Checkpointed at scn: 0x0000.00001896 10/22/2005 21:44:46
Thread: 0 rba :( 0x0. 0.0)

Data file #4:
(Name #4)/u01/app/oracle1/oradata/orcl/users01.dbf
Creation size = 0 block size = 8192 status = 0xe head = 4 tail = 4 dup = 1
Tablespace 4, index = 4 krfil = 4 prev_file = 0
Unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
Checkpoint cnt: 182 scn: 0x0000. 00091eb4 04/16/2014 13:54:27
Stop scn: 0x0000. 00091eb4 04/16/2014 13:54:27
Creation Checkpointed at scn: 0x0000. Limit 27b9 10/22/2005 21:45:00
Thread: 0 rba :( 0x0. 0.0)

Data file #5:
(Name #9)/u01/app/oracle1/oradata/orcl/gguser. dbf
Creation size = 128000 block size = 8192 status = 0xe head = 9 tail = 9 dup = 1
Tablespace 6, index = 6 krfil = 5 prev_file = 0
Unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
Checkpoint cnt: 135 scn: 0x0000. 00091eb4 04/16/2014 13:54:27
Stop scn: 0x0000. 00091eb4 04/16/2014 13:54:27
Creation Checkpointed at scn: 0x0000.00083314 02/19/2014 14:33:57
Thread: 1 rba :( 0x2. c04b. 10)

Data file #6:
(Name #10)/u01/app/oracle1/oradata/orcl/ogg2.dbf
Creation size = 640 block size = 8192 status = 0x86 head = 10 tail = 10 dup = 1
Tablespace 7, index = 7 krfil = 6 prev_file = 0
Unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
Checkpoint cnt: 3 scn: 0x0000. 00089cfb 03/18/2014 13:32:52
Stop scn: 0x0000. 00089cfb 03/18/2014 13:32:52
Creation Checkpointed at scn: 0x0000. 00089bd3 03/18/2014 13:29:26
Thread: 1 rba :( 0x3. e2.10)

Here, the number of checkpoints for each data file is the same as the number of checkpoints for the data file dumped from the control file; the SCN and Stop SCN numbers of each data file are the same as those of the data file queried from the control file.

Because the database SCN number stored in the control FILE is exactly the same as the SCN and stop scn numbers of all DATA files except data file 6 (including those stored in the control FILE and data file header ).

SQL> select tablespace_name, file_id from dba_data_files where file_id = 6;

TABLESPACE_NAME FILE_ID
----------------------------------------
OGG2 6

SQL> select tablespace_name, status from dba_tablespaces;

TABLESPACE_NAME STATUS
---------------------------------------
SYSTEM ONLINE
UNDOTBS1 ONLINE
SYSAUX ONLINE
TEMP ONLINE
USERS ONLINE
TBS_OGG ONLINE
OGG2 READ ONLY

7 rows selected.

Data file 6 is different because the corresponding tablespace is read only, so the SCN of the data file will not change.

The content of $ ORACLE_HOME/rdbms/admin/gettrace. SQL script is as follows:
[Oracle1 @ RedHat5 admin] $ cat gettrace. SQL
SELECT d. value | '/' | lower (rtrim (I. instance, chr (0) | '_ ora _' |
P. spid | '. trc' trace_file_name
From (select p. spid
From sys. v $ mystat m, sys. v $ session s, sys. v $ process p
Where m. statistic # = 1
And s. sid = m. sid
And p. addr = s. paddr) p,
(Select t. instance
From sys. v $ thread t, sys. v $ parameter v
Where v. name = 'thread'
And (v. value = 0 or t. thread # = to_number (v. value) I,
(Select value from sys. v $ parameter where name = 'user _ dump_dest ') d;

-- End --