oracle 學習總結篇三:SCN號與資料庫關閉的關係

資料庫正常關閉時將會觸發完全checkpoint ,會同時將資料檔案的Start SCN號等於資料檔案的end scn 號。

可以在啟動到mount 狀態時查出來

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

Total System Global Area  101785252 bytes
Fixed Size                   454308 bytes
Variable Size              75497472 bytes
Database Buffers           25165824 bytes
Redo Buffers                 667648 bytes
Database mounted.
SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#
------------------
            271827

SQL> select checkpoint_change#,last_change# from v$datafile;

CHECKPOINT_CHANGE# LAST_CHANGE#
------------------ ------------
            271827       271827
            271827       271827
            271827       271827
            271827       271827
            271827       271827

SQL> select checkpoint_change# from v$datafile_header;

CHECKPOINT_CHANGE#
------------------
            271827
            271827
            271827
            271827
            271827

SQL> alter database open;  --資料庫正常開啟

Database altered.

在資料庫開啟時 資料檔案的end scn 號為null

SQL> select checkpoint_change# from v$datafile_header;

CHECKPOINT_CHANGE#
------------------
            271928
            271928
            271928
            271928
            271928

SQL> select checkpoint_change#,last_change# from v$datafile;

CHECKPOINT_CHANGE# LAST_CHANGE#
------------------ ------------
            271928                         null
            271928                         null
            271928
            271928
            271928

SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#
------------------
            271928

SQL>

要是資料庫不正常關閉時 則end scn 號為null , 可以在啟動到mount 狀態時查出來 則需要做instance recovery

SQL> startup mount;
ORACLE instance started.

Total System Global Area  101785252 bytes
Fixed Size                   454308 bytes
Variable Size              75497472 bytes
Database Buffers           25165824 bytes
Redo Buffers                 667648 bytes
Database mounted.
SQL> select checkpoint_change#,last_change# from v$datafile;

CHECKPOINT_CHANGE# LAST_CHANGE#
------------------ ------------
            271928
            271928
            271928
            271928
            271928

SQL> alter database open;

Database altered.

日誌記錄如下:

Started recovery at
 Thread 1: logseq 18, block 214, scn 0.0
Recovery of Online Redo Log: Thread 1 Group 3 Seq 18 Reading mem 0
  Mem# 0 errs 0: D:\ORACLE\ORADATA\PUBTEST\REDO03.LOG
Tue Mar 18 10:30:55 2008
Completed redo application
Tue Mar 18 10:30:55 2008
Ended recovery at
 Thread 1: logseq 18, block 216, scn 0.292022
 3 data blocks read, 3 data blocks written, 2 redo blocks read
Crash recovery completed successfully