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

在資料庫啟動過程中，當System Checkpoint SCN、Datafile Checkpoint SCN和
Start SCN號都相同時，資料庫可以正常啟動，不需要做media recovery.三者當中有
一個不同時，則需要做media recovery.

如果在啟動的過程中，End SCN號為NULL，則需要做instance recovery. 這種情況一般是資料庫不正常關閉導致
ORACLE在啟動過程中首先檢查是否需要media recovery，然後再檢查是否需要instance recovery.

以下是測試過程:

1.資料庫正常關閉,在資料庫啟動過程中:當System Checkpoint SCN、Datafile Checkpoint SCN和
Start SCN號都相同時，資料庫可以正常啟動

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#
------------------
            229935

SQL> select checkpoint_change# from v$datafile;

CHECKPOINT_CHANGE#
------------------
            229935
            229935
            229935
            229935
            229935

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

CHECKPOINT_CHANGE# LAST_CHANGE#
------------------ ------------
            229935       229935
            229935       229935
            229935       229935
            229935       229935
            229935       229935

SQL> select checkpoint_change# from v$datafile_header;

CHECKPOINT_CHANGE#
------------------
            229935
            229935
            229935
            229935
            229935

SQL> alter database open;

Database altered.

看此時database的日誌檔案:

Completed: ALTER DATABASE   MOUNT
Tue Mar 18 08:54:19 2008
alter database open
Tue Mar 18 08:54:19 2008
LGWR: Primary database is in CLUSTER CONSISTENT mode
Thread 1 opened at log sequence 15
  Current log# 3 seq# 15 mem# 0: D:\ORACLE\ORADATA\PUBTEST\REDO03.LOG
Successful open of redo thread 1.

Tue Mar 18 08:54:19 2008
SMON: enabling cache recovery
Tue Mar 18 08:54:20 2008
Undo Segment 1 Onlined
Undo Segment 2 Onlined
Undo Segment 3 Onlined
Undo Segment 4 Onlined
Undo Segment 5 Onlined
Undo Segment 6 Onlined
Undo Segment 7 Onlined
Undo Segment 8 Onlined
Undo Segment 9 Onlined
Undo Segment 10 Onlined
Successfully onlined Undo Tablespace 1.
Tue Mar 18 08:54:20 2008
SMON: enabling tx recovery
Tue Mar 18 08:54:20 2008
Database Characterset is ZHS16GBK
replication_dependency_tracking turned off (no async multimaster replication found)
Completed: alter database open

可以看到資料庫正常啟動沒有做任何instance recovery,和media recovery

2.如果在啟動的過程中，End SCN號為NULL，則需要做instance recovery. 這種情況一般是資料庫不正常關閉導致  測試如下:

SQL> shutdown abort;
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$datafile_header;

CHECKPOINT_CHANGE#
------------------
            229936
            229936
            229936
            229936
            229936

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

CHECKPOINT_CHANGE# LAST_CHANGE#
------------------ ------------  
            229936
            229936
            229936
            229936
            229936           可以看到last_change# 下面的內容為null

SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#
------------------
            229936

SQL> alter database open;   在這個過程中做了例項恢復

Database altered.

此時日誌檔案中記錄如下:

Shutting down instance (abort)
License high water mark = 1
Instance terminated by USER, pid = 5976
Tue Mar 18 08:56:21 2008
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
SCN scheme 2
Using log_archive_dest parameter default value
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up ORACLE RDBMS Version: 9.2.0.4.0.
System parameters with non-default values:
  processes                = 150
  timed_statistics         = TRUE
  shared_pool_size         = 50331648
  large_pool_size          = 8388608
  java_pool_size           = 0
  control_files            = D:\oracle\oradata\pubtest\control01.ctl, D:\oracle\oradata\pubtest\control02.ctl, D:\oracle\oradata\pubtest\control03.ctl
  db_block_size            = 8192
  db_cache_size            = 25165824
  compatible               = 9.2.0.0.0
  log_archive_start        = TRUE
  db_file_multiblock_read_count= 16
  fast_start_mttr_target   = 300
  log_checkpoints_to_alert = TRUE
  undo_management          = AUTO
  undo_tablespace          = UNDOTBS1
  undo_retention           = 10800
  remote_login_passwordfile= EXCLUSIVE
  db_domain                =
  instance_name            = pubtest
  hash_join_enabled        = TRUE
  background_dump_dest     = D:\oracle\admin\pubtest\bdump
  user_dump_dest           = D:\oracle\admin\pubtest\udump
  core_dump_dest           = D:\oracle\admin\pubtest\cdump
  sort_area_size           = 524288
  db_name                  = pubtest
  open_cursors             = 300
  star_transformation_enabled= FALSE
  query_rewrite_enabled    = FALSE
  pga_aggregate_target     = 25165824
PMON started with pid=2
DBW0 started with pid=3
LGWR started with pid=4
CKPT started with pid=5
SMON started with pid=6
RECO started with pid=7
Tue Mar 18 08:56:23 2008
ARCH: STARTING ARCH PROCESSES
ARC0 started with pid=8
ARC0: Archival started
ARC1 started with pid=9
ARC1: Archival started
Tue Mar 18 08:56:23 2008
ARCH: STARTING ARCH PROCESSES COMPLETE
Tue Mar 18 08:56:23 2008
ARC1: Thread not mounted
Tue Mar 18 08:56:24 2008
ALTER DATABASE   MOUNT
Tue Mar 18 08:56:24 2008
ARC0: Thread not mounted
Tue Mar 18 08:56:29 2008
Successful mount of redo thread 1, with mount id 794635960.
Tue Mar 18 08:56:29 2008
Database mounted in Exclusive Mode.
Completed: ALTER DATABASE   MOUNT
Tue Mar 18 08:57:12 2008
alter database open
Tue Mar 18 08:57:12 2008
Beginning crash recovery of 1 threads
Tue Mar 18 08:57:12 2008
Started first pass scan
Tue Mar 18 08:57:13 2008
Completed first pass scan
 82 redo blocks read, 49 data blocks need recovery
Tue Mar 18 08:57:13 2008
Started recovery at
 Thread 1: logseq 15, block 809, scn 0.0
Recovery of Online Redo Log: Thread 1 Group 3 Seq 15 Reading mem 0  --當前日誌檔案做例項恢復instance   recovery
  Mem# 0 errs 0: D:\ORACLE\ORADATA\PUBTEST\REDO03.LOG
Tue Mar 18 08:57:13 2008
Completed redo application
Tue Mar 18 08:57:13 2008
Ended recovery at
 Thread 1: logseq 15, block 891, scn 0.250029
 49 data blocks read, 49 data blocks written, 82 redo blocks read
Crash recovery completed successfully
Tue Mar 18 08:57:13 2008
LGWR: Primary database is in CLUSTER CONSISTENT mode
Thread 1 advanced to log sequence 16
Thread 1 opened at log sequence 16
  Current log# 1 seq# 16 mem# 0: D:\ORACLE\ORADATA\PUBTEST\REDO01.LOG
Successful open of redo thread 1.
Tue Mar 18 08:57:14 2008
SMON: enabling cache recovery
Tue Mar 18 08:57:14 2008
ARC0: Evaluating archive   log 3 thread 1 sequence 15
ARC0: Beginning to archive log 3 thread 1 sequence 15
Creating archive destination LOG_ARCHIVE_DEST_1: 'D:\ORACLE\ORA92\RDBMS\ARC00015.001'   --並且做了日誌切換和歸檔
kccrsz: expanded controlfile section 11 from 13 to 27 records
  requested to grow by 5 record(s); added 1 block(s) of records
ARC0: Completed archiving  log 3 thread 1 sequence 15
Tue Mar 18 08:57:15 2008
Undo Segment 1 Onlined
Undo Segment 2 Onlined
Undo Segment 3 Onlined
Undo Segment 4 Onlined
Undo Segment 5 Onlined
Undo Segment 6 Onlined
Undo Segment 7 Onlined
Undo Segment 8 Onlined
Undo Segment 9 Onlined
Undo Segment 10 Onlined
Successfully onlined Undo Tablespace 1.
Tue Mar 18 08:57:15 2008
SMON: enabling tx recovery
Tue Mar 18 08:57:15 2008
Database Characterset is ZHS16GBK
replication_dependency_tracking turned off (no async multimaster replication found)
Completed: alter database open

3.oracle在啟動過程中三者當中有一個不同時，則需要做media recovery,步驟是 首先檢查是否需要media recovery，然後再檢查是否需要instance recovery. 測試如下;

SQL> alter tablespace tools begin backup;

Tablespace altered.

此時複製tools01.dbf 到別處

SQL> create table tools (id number) tablespace tools;

Table created.

SQL> alter tablespace tools end backup;

Tablespace altered.

SQL> create table tools (id number) tablespace tools;

Table created.

SQL> insert into tools values(10);

1 row created.

SQL> commit;

Commit complete.

SQL> shutdown abort;
ORACLE instance shut down.

複製 tools01.dbf 回來
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#
------------------
            251084

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

CHECKPOINT_CHANGE# LAST_CHANGE#
------------------ ------------
            251084
            251084
            251084
            251084
            251084

SQL> select checkpoint_change# from v$datafile_header;

CHECKPOINT_CHANGE#
------------------
            251084
            251084
            251084
            250867
            251084

可以看到 資料檔案頭的scn為250867,小於資料檔案scn 251084

和系統檢查好scn 251084  

SQL> alter database open;     --提示做介質恢復
alter database open
*
ERROR at line 1:
ORA-01113: file 4 needs media recovery
ORA-01110: data file 4: 'D:\ORACLE\ORADATA\PUBTEST\TOOLS01.DBF'

SQL> recover datafile 4;
Media recovery complete.
SQL> alter database open;

Database altered.

SQL>

此時日誌檔案中內容如下:

ALTER DATABASE RECOVER  datafile 4 
Media Recovery Datafile: 4
Media Recovery Start
Starting datafile 4 recovery in thread 1 sequence 16
Datafile 4: 'D:\ORACLE\ORADATA\PUBTEST\TOOLS01.DBF'
Media Recovery Log
Recovery of Online Redo Log: Thread 1 Group 1 Seq 16 Reading mem 0
  Mem# 0 errs 0: D:\ORACLE\ORADATA\PUBTEST\REDO01.LOG
Recovery of Online Redo Log: Thread 1 Group 2 Seq 17 Reading mem 0
  Mem# 0 errs 0: D:\ORACLE\ORADATA\PUBTEST\REDO02.LOG
Media Recovery Complete
Completed: ALTER DATABASE RECOVER  datafile 4 
Tue Mar 18 09:48:50 2008
alter database open
Tue Mar 18 09:48:50 2008
Beginning crash recovery of 1 threads
Tue Mar 18 09:48:50 2008
Started first pass scan
Tue Mar 18 09:48:52 2008
Completed first pass scan
 83 redo blocks read, 50 data blocks need recovery
Tue Mar 18 09:48:52 2008
Started recovery at
 Thread 1: logseq 17, block 29, scn 0.0
Recovery of Online Redo Log: Thread 1 Group 2 Seq 17 Reading mem 0
  Mem# 0 errs 0: D:\ORACLE\ORADATA\PUBTEST\REDO02.LOG
Tue Mar 18 09:48:52 2008
Completed redo application
Tue Mar 18 09:48:52 2008
Ended recovery at
 Thread 1: logseq 17, block 112, scn 0.271189
 50 data blocks read, 50 data blocks written, 83 redo blocks read
Crash recovery completed successfully
Tue Mar 18 09:48:53 2008
LGWR: Primary database is in CLUSTER CONSISTENT mode
Thread 1 advanced to log sequence 18
Thread 1 opened at log sequence 18
  Current log# 3 seq# 18 mem# 0: D:\ORACLE\ORADATA\PUBTEST\REDO03.LOG
Successful open of redo thread 1.
Tue Mar 18 09:48:54 2008
SMON: enabling cache recovery
Tue Mar 18 09:48:54 2008
ARC0: Evaluating archive   log 2 thread 1 sequence 17
ARC0: Beginning to archive log 2 thread 1 sequence 17
Creating archive destination LOG_ARCHIVE_DEST_1: 'D:\ORACLE\ORA92\RDBMS\ARC00017.001'
ARC0: Completed archiving  log 2 thread 1 sequence 17
Tue Mar 18 09:48:54 2008
Undo Segment 1 Onlined
Undo Segment 2 Onlined
Undo Segment 3 Onlined
Undo Segment 4 Onlined
Undo Segment 5 Onlined
Undo Segment 6 Onlined
Undo Segment 7 Onlined
Undo Segment 8 Onlined
Undo Segment 9 Onlined
Undo Segment 10 Onlined
Successfully onlined Undo Tablespace 1.
Tue Mar 18 09:48:54 2008
SMON: enabling tx recovery
Tue Mar 18 09:48:54 2008
Database Characterset is ZHS16GBK
replication_dependency_tracking turned off (no async multimaster replication found)
Completed: alter database open