Oracle執行個體恢複的初探

什麼時候會産生執行個體恢複呢？當你資料庫伺服器異常斷電，重新開機資料庫就會發生執行個體恢複。執行個體恢複是由資料庫自動完成的，無須DBA的幹涉。當然這裡有個前提條件：資料檔案、線上日志檔案、控制檔案不得有損壞。

我們用實驗來分析一下執行個體恢複的整個過程吧！

1、在關閉資料庫前，我們先看一下幾個檢查點的SCN

SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#

1455180

--控制檔案中儲存的資料庫檢查點SCN号實際上在所有資料檔案頭部中最小的檢查點SCN

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

FILE# CHECKPOINT_CHANGE#

1 1455180

2 1455180

3 1455180

4 1455180

5 1455180

6 1455180

--控制檔案中儲存的資料檔案檢查點SCN:當一個檢查點動作完成之後，Oracle就把每個資料檔案的scn單獨存放在控制檔案中

SQL> select file#,checkpoint_change# from v$datafile_header;

--每個資料檔案的檔案頭中的檢查點SCN

這三個檢查點的SCN一緻，接下來模拟異常斷電，重新開機機器

2、此指令可以模拟異常斷電

SQL> shutdown abort;

ORACLE instance shut down.

3、監控告警日志

[oracle@guoyj trace]$ tail -f alert_bxocp.log

Starting background process VKRM

Tue Dec 11 22:54:41 2012

VKRM started with pid=24, OS id=12500

Tue Dec 11 22:58:11 2012

Shutting down instance (abort)

License high water mark = 3

USER (ospid: 12479): terminating the instance

Instance terminated by USER, pid = 12479

Tue Dec 11 22:58:12 2012

Instance shutdown complete

4、資料庫啟動到MOUNT狀

SQL> startup mount;

ORACLE instance started.

Total System Global Area 839282688 bytes

Fixed Size 2233000 bytes

Variable Size 524291416 bytes

Database Buffers 310378496 bytes

Redo Buffers 2379776 bytes

Database mounted.

5、再确定一下這個時間的檢查點SCN

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

6 rows selected.

發現與異常斷電前的檢查點的SCN一緻，這裡一緻無須媒體恢複。

先不着急open資料庫，我們做一些dump

6、dump的控制檔案

alter session set events 'immediate trace name CONTROLF level 12';

取部分内容：

DATABASE ENTRY

(size = 316, compat size = 316, section max = 1, section in-use = 1,

last-recid= 0, old-recno = 0, last-recno = 0)

(extent = 1, blkno = 1, numrecs = 1)

12/07/2012 10:36:14

DB Name "BXOCP"

Database flags = 0x00404000 0x00001000

Controlfile Creation Timestamp 12/07/2012 10:36:15

Incmplt recovery scn: 0x0000.00000000

Resetlogs scn: 0x0000.000f30dc Resetlogs Timestamp 12/07/2012 10:36:16

Prior resetlogs scn: 0x0000.00000001 Prior resetlogs Timestamp 09/17/2011 09:46:04

Redo Version: compatible=0xb200000

Data files = 6, #Online files = 6

Database checkpoint: Thread=1 scn: 0x0000.0016344c --資料庫檢查點SCN＝16344c轉成10進制為1455180

Threads: #Enabled=1, #Open=1, Head=1, Tail=1

CHECKPOINT PROGRESS RECORDS

(size = 8180, compat size = 8180, section max = 11, section in-use = 0,

(extent = 1, blkno = 2, numrecs = 11)

THREAD #1 - status:0x2 flags:0x0 dirty:55

low cache rba:(0x13.3.0) on disk rba:(0x13.a6.0)

-- low cache rba:(0x13.3.0)執行個體恢複的起點：19号日志，第3個塊，第0個位元組

--on disk rba:(0x13.a6.0)：執行個體恢複的終點：19号日志，第166個塊，第0個位元組

on disk scn: 0x0000.0016359c 12/11/2012 22:57:42

resetlogs scn: 0x0000.000f30dc 12/07/2012 10:36:16

heartbeat: 801789080 mount id: 848836772

THREAD #2 - status:0x0 flags:0x0 dirty:0

low cache rba:(0x0.0.0) on disk rba:(0x0.0.0)

on disk scn: 0x0000.00000000 01/01/1988 00:00:00

resetlogs scn: 0x0000.00000000 01/01/1988 00:00:00

heartbeat: 0 mount id: 0

DATA FILE RECORDS

(size = 520, compat size = 520, section max = 100, section in-use = 6,

last-recid= 43, old-recno = 0, last-recno = 0)

(extent = 1, blkno = 11, numrecs = 100)

DATA FILE #1:

name #7: /oradata/bxocp/system01.dbf

creation size=0 block size=8192 status=0xe head=7 tail=7 dup=1

tablespace 0, index=1 krfil=1 prev_file=0

unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00

Checkpoint cnt:121 scn: 0x0000.0016344c 12/11/2012 22:54:36

--控制檔案中儲存的資料檔案檢查點SCN＝16344c轉成10進制為1455180

Stop scn: 0xffff.ffffffff 12/11/2012 22:53:05

--結束的SCN填無窮大，說明是異常關機的，重新開機資料庫必須做執行個體恢複

Creation Checkpointed at scn: 0x0000.00000007 09/17/2011 09:46:08

thread:0 rba:(0x0.0.0)

7、dump資料檔案頭

alter session set events 'immediate trace name file_hdrs level 10';

顯示資料檔案頭的部分内容：

V10 STYLE. FILE HEADER:

Compatibility Vsn = 186646528=0xb200000

Db ID=848459038=0x3292751e, Db Name='BXOCP'

Activation ID=0=0x0

Control Seq=2099=0x833, File size=79360=0x13600

File Number=2, Blksiz=8192, File Type=3 DATA

Tablespace #1 - SYSAUX rel_fn:2

Creation at scn: 0x0000.0000088c 09/17/2011 09:46:16

Backup taken at scn: 0x0000.00000000 01/01/1988 00:00:00 thread:0

reset logs count:0x2fc45da0 scn: 0x0000.000f30dc

prev reset logs count:0x2d6c775c scn: 0x0000.00000001

recovered at 12/11/2012 22:54:36

status:0x4 root dba:0x00000000 chkpt cnt: 121 ctl cnt:120

begin-hot-backup file size: 0

Checkpointed at scn: 0x0000.0016344c 12/11/2012 22:54:36

--資料檔案的檔案頭中的檢查點SCN＝16344c轉成10進制為1455180

thread:1 rba:(0x13.2.10)

--重做日志的位址0x13.2.10－> 19号日志，第2号塊，第16個位元組開始恢複

注意：

從控制檔案中得到重做日志恢複起始位址：

low cache rba:(0x13.3.0)：19号日志，第3個塊，第0個位元組開始恢複

從資料檔案頭部得到重做日志恢複起始位址：

thread:1 rba:(0x13.2.10) ：9号日志，第2号塊，第16個位元組開始恢複

8、最後我們打開資料庫，然後監控告警日志alert_bxocp.log日志，看是怎麼恢複的

alter database open

Beginning crash recovery of 1 threads

Started redo scan

Completed redo scan

read 81 KB redo, 55 data blocks need recovery

Started redo application at

Thread 1: logseq 19, block 3 --執行個體恢複開始的重做日志：19号日志第3個塊

Recovery of Online Redo Log: Thread 1 Group 1 Seq 19 Reading mem 0

Mem# 0: /oradata/bxocp/redo01.log

Completed redo application of 0.06MB

Completed crash recovery at

Thread 1: logseq 19, block 166, scn 1475516 --執行個體恢複結束點的重做日志：19号日志第166個塊

55 data blocks read, 55 data blocks written, 81 redo k-bytes read

Tue Dec 11 23:46:42 2012

Thread 1 advanced to log sequence 20 (thread open)

Thread 1 opened at log sequence 20

Current log# 2 seq# 20 mem# 0: /oradata/bxocp/redo02.log

Successful open of redo thread 1

MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set

[12867] Successfully onlined Undo Tablespace 2.

Undo initialization finished serial:0 start:20725234 end:20725294 diff:60 (0 seconds)

Verifying file header compatibility for 11g tablespace encryption..

Verifying 11g file header compatibility for tablespace encryption completed

SMON: enabling cache recovery

SMON: enabling tx recovery

Database Characterset is ZHS16GBK

No Resource Manager plan active

replication_dependency_tracking turned off (no async multimaster replication found)

Starting background process QMNC

Tue Dec 11 23:46:43 2012

QMNC started with pid=21, OS id=13839

Completed: alter database open

Tue Dec 11 23:46:44 2012

Starting background process CJQ0

CJQ0 started with pid=22, OS id=13851

Setting Resource Manager plan SCHEDULER[0x318A]:DEFAULT_MAINTENANCE_PLAN via scheduler window

Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter

Tue Dec 11 23:46:47 2012

VKRM started with pid=23, OS id=13857

9、可以看出，執行個體恢複的起始的重做日志是以控制檔案中的low cache rba:(0x13.3.0)：19号日志，第3個塊，第0個位元組開始恢複，而不是從檔案頭的thread:1 rba:(0x13.2.10)

10、最後總結一下執行個體恢複

(1)資料檔案、線上日志檔案、控制檔案不得有損壞

(2)資料庫自動恢複，無需DBA幹涉

(3)恢複隻需線上日志檔案，無需歸檔日志

(4)資料庫在open的時候開始執行個體恢複

實際上我做的這個執行個體恢實驗的還沒有寫完整， 還有最後一步復原！這個就留給你們思考！

執行個體恢複三步：前滾--->打開庫---->後滾(也叫復原)