数据库正常关闭时将会触发完全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