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
[color=#0080ff]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