Detailed description of SCN in the Database Control File

When the database is started and shut down, the control file plays an important role. From the official documents, we can know that the control file contains the following information: the names and locations of all data files and redo log files, such as the database name and database creation time. Tablespace information. Offline data file information. Redo logs and archive logs

When the database is started and shut down, the control file plays an important role. From the official documents, we can know that the control file contains the following information: the names and locations of all data files and redo log files, such as the database name and database creation time. Tablespace information. Offline data file information. Redo logs and archive logs

When the database is started and shut down, the control file plays an important role. From the official documents, we can know that the control file contains the following information:

Database Name and database creation time

The names and locations of all data files and redo log files.

Tablespace information.

Offline data file information.

Redo logs and archive logs.

Backup set and backup file information

Check Point and SCN information.

When the database is shut down normally, the SCN value in the control file

In the data file information section of the control file, each data file has a Checkpoint SCN and Stop SCN, which must be verified when the database is started.

We know how the database controls the SCN in the file when the consistency is disabled?

1. Close the database consistently. Dump Control File

SYS @ ENMOEDU> shutdown immediate

SYS @ ENMOEDU> startup mount

Database mounted.

SYS @ ENMOEDU> oradebug setmypid;

Statement processed.

SYS @ ENMOEDU> oradebug dump controlf 3;

Statement processed.

2. Information about the database. Information about the last successful completion of the database checkpoint

**************************************** ***********************************

DATABASE ENTRY

**************************************** ***********************************

(Size = 316, compat size = 316, p max = 1, p in-use = 1,

Last-recid = 0, old-recno = 0, last-recno = 0)

(Extent = 1, blkno = 1, numrecs = 1)

01/13/2014 16:13:27

DB Name "ENMOEDU"

Database flags = 0x00404001 0x00001000

Controlfile Creation Timestamp 01/13/2014 16:13:28

Incmplt recovery scn: 0x0000.00000000

Resetlogs scn: 0x0000. 000 fc451 Resetlogs Timestamp 02/05/2014 15:44:46

Prior resetlogs scn: 0x0000. 000fa657 Prior resetlogs Timestamp 02/05/2014 14:15:27

Redo Version: compatible = 0xb200000

# Data files = 6, # Online files = 6

Database checkpoint: Thread = 1 scn: 0x0000. 0011b73b

Threads: # Enabled = 1, # Open = 0, Head = 0, Tail = 0

3. Checkpoint Process records. Low cache rba is the starting point for recovery. fffffff indicates that the database does not need to be restored. On disk rba is the end point of recovery.

**************************************** ***********************************

CHECKPOINT PROGRESS RECORDS

**************************************** ***********************************

(Size = 8180, compat size = 8180, p max = 11, p in-use = 0,

Last-recid = 0, old-recno = 0, last-recno = 0)

(Extent = 1, blkno = 2, numrecs = 11)

THREAD #1-status: 0x1 flags: 0x0 dirty: 0

Low cache rba :( 0xffffffff. ffffffff. ffff) on disk rba :( 0x6. 6f7. 0)

On disk scn: 0x0000. 0011b58f 02/19/2014 21:58:48

Resetlogs scn: 0x0000. 000fc451 02/05/2014 15:44:46

Heartbeat: 839919586 mount id: 99161229

Flashback log tail log #2 thread #1 seq 2 block 5337 byte 0

4. The Redo thread record the checkpoint information, which is the same as the database checkpoint.

**************************************** ***********************************

REDO THREAD RECORDS

**************************************** ***********************************

(Size = 256, compat size = 256, p max = 8, p in-use = 1,

Last-recid = 0, old-recno = 0, last-recno = 0)

(Extent = 1, blkno = 9, numrecs = 8)

THREAD #1-status: 0xbe thread links forward: 0 back: 0

# Logs: 3 first: 1 last: 3 current: 3 last used seq #: 0x6

Enabled at scn: 0x0000. 000fc451 02/05/2014 15:44:46

Disabled at scn: 0x0000.00000000 01/01/1988 00:00:00

Opened at 02/19/2014 21:57:43 by instance ENMOEDU

Checkpointed at scn: 0x0000. 0011b73b 02/19/2014 21:58:52

5. The checkpoint information recorded in the data file is the same as that recorded in the database entry.

**************************************** ***********************************

DATA FILE RECORDS

**************************************** ***********************************

Data file #6:

Name #13:/u01/app/oracle/oradata/ENMOEDU/sample01.dbf

Creation size = 12800 block size = 8192 status = 0xe head = 13 tail = 13 dup = 1

Tablespace 7, index = 7 krfil = 6 prev_file = 0

Unrecoverable scn: 0x0000. 001143f4 02/10/2014 15:34:23

Checkpoint cnt: 7 scn: 0x0000. 0011b73b 02/19/2014 21:58:52

Stop scn: 0x0000. 0011b73b 02/19/2014 21:58:52

Creation Checkpointed at scn: 0x0000.00111736 02/10/2014 15:28:26

Thread: 1 rba :( 0x4. 1f5. 10)

Enabled threads: 01000000 00000000 00000000 00000000 00000000

Because the database is consistently shut down, the Checkpoint scn and Stop scn in the data file, the scn values recorded in the database scn and redo are the same, so you can start the database directly the next time you start it, no recovery is required.

The value of SCN when the database is shut down abnormally.

1. If the database is shut down abnormally, no checkpoints will be executed. You can use shutdown abort to simulate an exception. When you use the shutdown abort method to shut down the database, what is the SCN value at this time.

SYS @ ENMOEDU> shutdown abort

ORACLE instance shut down.

SYS @ ENMOEDU> startup mount

Database mounted.

SYS @ ENMOEDU> oradebug setmypid;

Statement processed.

SYS @ ENMOEDU> oradebug dump controlf 3;

Statement processed.

2. In the database entry, we can see the DATABASE thread checkpoint information.

**************************************** ***********************************

DATABASE ENTRY

**************************************** ***********************************

(Size = 316, compat size = 316, p max = 1, p in-use = 1,

Last-recid = 0, old-recno = 0, last-recno = 0)

(Extent = 1, blkno = 1, numrecs = 1)

01/13/2014 16:13:27

DB Name "ENMOEDU"

Database flags = 0x00404001 0x00001200

Controlfile Creation Timestamp 01/13/2014 16:13:28

Incmplt recovery scn: 0x0000.00000000

Resetlogs scn: 0x0000. 000 fc451 Resetlogs Timestamp 02/05/2014 15:44:46

Prior resetlogs scn: 0x0000. 000fa657 Prior resetlogs Timestamp 02/05/2014 14:15:27

Redo Version: compatible = 0xb200000

# Data files = 6, # Online files = 6

Database checkpoint: Thread = 1 scn: 0x0000. 0011b73e

Threads: # Enabled = 1, # Open = 1, Head = 1, Tail = 1

3. the checkpoint information includes low chache rba and on disk ora information. Low cache rba (recovery block address) is the start point of recovery, and on disk rba (determining the redo value that has been written to the disk) is the end point of recovery,

**************************************** ***********************************

CHECKPOINT PROGRESS RECORDS

**************************************** ***********************************

(Size = 8180, compat size = 8180, p max = 11, p in-use = 0,

Last-recid = 0, old-recno = 0, last-recno = 0)

(Extent = 1, blkno = 2, numrecs = 11)

THREAD #1-status: 0x2 flags: 0x0 dirty: 510

Low cache rba :( 0x6. 18e2. 0) on disk rba :( 0x6. ca99.0)

On disk scn: 0x0000. 0011e785 02/19/2014 22:55:08

Resetlogs scn: 0x0000. 000fc451 02/05/2014 15:44:46

Heartbeat: 839992040 mount id: 99202971

Flashback log tail log #3 thread #1 seq 3 block 2244 byte 0

4. Redo thread record.

**************************************** ***********************************

REDO THREAD RECORDS

**************************************** ***********************************

(Size = 256, compat size = 256, p max = 8, p in-use = 1,

Last-recid = 0, old-recno = 0, last-recno = 0)

(Extent = 1, blkno = 9, numrecs = 8)

THREAD #1-status: 0xbf thread links forward: 0 back: 0

# Logs: 3 first: 1 last: 3 current: 3 last used seq #: 0x6

Enabled at scn: 0x0000. 000fc451 02/05/2014 15:44:46

Disabled at scn: 0x0000.00000000 01/01/1988 00:00:00

Opened at 02/19/2014 22:49:27 by instance ENMOEDU

Checkpointed at scn: 0x0000. 0011b73e 02/19/2014 22:49:27

Thread: 1 rba :( 0x6. 9ce. 10)

Enabled threads: 01000000 00000000 00000000 00000000 00000000

5. Checkpoint information of data files;

Data file #6:

Name #13:/u01/app/oracle/oradata/ENMOEDU/sample01.dbf

Creation size = 12800 block size = 8192 status = 0xe head = 13 tail = 13 dup = 1

Tablespace 7, index = 7 krfil = 6 prev_file = 0

Unrecoverable scn: 0x0000. 001143f4 02/10/2014 15:34:23

Checkpoint cnt: 8 scn: 0x0000. 0011b73e 02/19/2014 22:49:27

Stop scn: 0xffff. ffffffff 02/19/2014 21:58:52

Creation Checkpointed at scn: 0x0000.00111736 02/10/2014 15:28:26

Thread: 1 rba :( 0x4. 1f5. 10)

Enabled threads: 01000000 00000000 00000000 00000000 00000000

It can be seen that the database is shut down abnormally, and the database does not complete the final checkpoint. The stop scn of the database file is still set to the maximum ffffffff. From the above information, we can see that the checkpoint scn of each part is the same, but the stop scn of the data file is not equal to the checkpoint scn, which means that the last close of the database does not execute a full checkpoint, the database is shut down abnormally. In this case, the database needs to be completely restored when it is started.

During the recovery, read the low cache rba value from the checkpoint information to determine the log file address. Read the log again. From the last checkpoint, apply all redo records. This process is called rollback. The database can be opened for access and use. However, the database contains committed and uncommitted transactions and uses the undo segment for rollback.

The information we can see from the alert Log when we restore the database instance.

Alter database open

Beginning crash recovery of 1 threads

Parallel recovery started with 2 processes

Started redo scan

Completed redo scan

Read 22747 KB redo, 510 data blocks need recovery

Started redo application

Thread 1: logseq 6, block 6370

Recovery of Online Redo Log: Thread 1 Group 3 Seq 6 Reading mem 0

Mem #0:/u01/app/oracle/oradata/ENMOEDU/redo03.log

Completed redo application of 3.07 MB

Weds Feb 19 23:13:55 2014

Completed crash recovery

Thread 1: logseq 6, block 51865, scn 1193381

510 data blocks read, 493 data blocks written, 22747 redo k-bytes read

Weds Feb 19 23:13:55 2014

When the instance is restored. In Alter, logseq 6 and block 6370 are the low cache rba (0x6. 18e2. 0) recorded in the checkpoint in the log ). When the restoration is complete, logseq 6 and block 51865 is on disk rba :( 0x6. ca99.0 ). Replace the hexadecimal number with the decimal number.

Conclusion: When the database is shut down consistently, the database performs a Checkpoint action before it is shut down. Your Stop SCN for each data file is set to Checkpoint SCN. The database does not need to be restored. When the database is shut down abnormally, the Stop SCN cannot be set to Checkpoint SCN, which will be infinite and need to be restored when the database is restarted.