SCN與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 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.LOGITPUB
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
如果在啟動的過程中，End SCN號為NULL，則需要做instance recovery. 這種情況一般是資料庫不正常關閉導致 測試如下:
SQL>shutdown abort;
SQL> startup mount;
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;
------------------
229936
229936
229936
229936
229936
SQL> select checkpoint_change#,last_change# from v$datafile;
------------------ ------------
229936
229936
229936
229936
229936 ----可以看到last_change# 下面的內容為null
SQL> select checkpoint_change# from v$database;
------------------
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
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=8I
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
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、SCN不同時恢復
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.
此時日誌檔案中內容如下:
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
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/751371/viewspace-715956/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- oracle mount_open_checkpoint(scn)啟動驗證測試Oracle
- 通過修改SCN來騙過ORACLE啟動驗證Oracle
- oracle 學習總結篇三:SCN號與資料庫啟動的關係Oracle資料庫
- Oracle資料庫啟動過程驗證檢查點SCNOracle資料庫
- Oracle：SCNOracle
- Oracle scnOracle
- 【聽海日誌】之SCN與OracleOracle
- ordebug 手動修改Oracle sga scnOracle
- oracle redo record scn與transaction相關block scn關係小記Oracle RedoBloC
- oracle啟動與關閉Oracle
- 【SCN】Oracle SCN 詳細介紹Oracle
- 探索Oracle SCNOracle
- oracle的SCNOracle
- Oracle RAC 啟動與關閉Oracle
- 【SCN】Oracle檢查scn值指令碼Oracle指令碼
- 【SCN】Oracle推薦scn命令參考Oracle
- Oracle安全:SCN可能最大值與耗盡問題Oracle安全:SCN可能最大值與耗盡問題Oracle
- oracle scn與備份恢復backup recovery(一)Oracle
- Oracle timestamp_to_scn and scn_to_timestampOracle
- ORACLE -詳解SCNOracle
- Oracle SCN詳解Oracle
- Oracle Scn 定義Oracle
- ORACLE scn 機制Oracle
- ORACLE SCN 查詢Oracle
- Oracle中的SCNOracle
- 啟動SCN不一致的資料庫資料庫
- Oracle SCN機制———在備份與恢復中Oracle
- Oracle10g中SCN與TimeStamp轉換Oracle
- Oracle SCN相關問題學習與測試Oracle
- oracle的scn及sequenceOracle
- 查詢Oracle的SCNOracle
- oracle資料庫SCNOracle資料庫
- 對ORACLE SCN的理解Oracle
- Oracle SCN機制解析Oracle
- Oracle HowTo:How to get Oracle SCN?Oracle
- SCN號與oracle資料庫恢復的關係Oracle資料庫
- ORACLE資料庫中SCN與時間的轉換Oracle資料庫
- linux下Oracle自動啟動與停止總結LinuxOracle