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