重建控制檔案時resetlogs與noresetlogs的使用情況
控制檔案中記錄着資料庫的資料檔案,日志檔案,備份資料等資訊,更為重要的,控制檔案中還記錄了資料庫的檢查點
和scn資訊,這些資訊在資料恢複的過程中将起到關鍵性作用.
一個正常運作的資料庫,通常控制檔案都存在多份鏡像,這些鏡像的内容是完全相同的,oracle預設就建立多份控制
檔案更說明了控制檔案的重要:
sql> select name from v$controlfile;
name
--------------------------------------------------------------------------------
/u01/app/oracle/product/11.2.0/oradata/jingyong/control01.ctl
/u01/app/oracle/product/11.2.0/oradata/jingyong/control02.ctl
可以通過如下一條指令将控制檔案的建立語句備份到跟蹤檔案中:
sql> alter database backup controlfile to trace;
database altered.
sql> oradebug setmypid
statement processed.
sql> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/jingyong/jingyong/trace/jingyong_ora_18818.trc
sql> host sz /u01/app/oracle/diag/rdbms/jingyong/jingyong/trace/jingyong_ora_18818.trc
rz
starting zmodem transfer. press ctrl+c to cancel.
100% 8 kb 8 kb/s 00:00:01 0 errors
此跟蹤檔案中會記錄控制檔案的建立腳本,腳本包含兩個主要的段落,其中一段如下所示:
startup nomount
create controlfile reuse database "jingyong" resetlogs archivelog
maxlogfiles 16
maxlogmembers 3
maxdatafiles 100
maxinstances 8
maxloghistory 292
logfile
group 1 '/u01/app/oracle/product/11.2.0/oradata/jingyong/redo01.log' size 50m blocksize 512,
group 2 '/u01/app/oracle/product/11.2.0/oradata/jingyong/redo02.log' size 50m blocksize 512,
group 3 '/u01/app/oracle/product/11.2.0/oradata/jingyong/redo03.log' size 50m blocksize 512
-- standby logfile
datafile
'/u01/app/oracle/product/11.2.0/oradata/jingyong/system01.dbf',
'/u01/app/oracle/product/11.2.0/oradata/jingyong/sysaux01.dbf',
'/u01/app/oracle/product/11.2.0/oradata/jingyong/undotbs01.dbf',
'/u01/app/oracle/product/11.2.0/oradata/jingyong/users01.dbf',
'/u01/app/oracle/product/11.2.0/oradata/jingyong/example01.dbf',
'/u01/app/oracle/product/11.2.0/oradata/jingyong/jy01.dbf'
character set zhs16gbk
;
當資料庫處于nomount狀态下時,可以通過運作這段腳本建立控制檔案,控制檔案會自動建立到參數檔案中
記錄控制檔案的位置(原來的控制檔案在建立過程會被覆寫).這裡需要了解的一個主要選項是:
noresetlogs/resetlogs.在跟蹤檔案中包含如下注釋,詳細解釋了這兩個選項的含義:
-- below are two sets of sql statements, each of which creates a new
-- control file and uses it to open the database. the first set opens
-- the database with the noresetlogs option and should be used only if
-- the current versions of all online logs are available. the second
-- set opens the database with the resetlogs option and should be used
-- if online logs are unavailable.
-- the appropriate set of statements can be copied from the trace into
-- a script. file, edited as necessary, and executed when there is a
-- need to re-create the control file.
當資料庫目前的redo log都可用時,可以通過noresetlogs參數重建控制檔案,此時oracle能夠從日志檔案中
讀取redo資訊,記錄到控制檔案中,由于redo中記錄的資訊足以重演所有送出成功的事務,是以最終能夠實作
完全恢複,成功打開資料庫,這時的資料庫就如同進行了一次斷電之後的執行個體恢複,資料沒有損失,重做日志
可以繼續向前寫入:
下面測試來看一下以noresetlogs重建控制檔案進行資料庫恢複的過程
先在資料庫正常運作狀态下對控制檔案執行一次轉儲:
sql> alter session set events 'immediate trace name controlf level 12';
session altered.
/u01/app/oracle/diag/rdbms/jingyong/jingyong/trace/jingyong_ora_19350.trc
這個轉儲檔案中将包含資料庫的檢查點,redo thread資訊,資料檔案等資訊,看一下
log file records内容:
***************************************************************************
log file records
(size = 72, compat size = 72, section max = 16, section in-use = 3,
last-recid= 3, old-recno = 0, last-recno = 0)
(extent = 1, blkno = 10, numrecs = 16)
log file #1:
name #3: /u01/app/oracle/product/11.2.0/oradata/jingyong/redo01.log
thread 1 redo log links: forward: 2 backward: 0
siz: 0x19000 seq: 0x00000010 hws: 0x2 bsz: 512 nab: 0x1e flg: 0x1 dup: 1
archive links: fwrd: 0 back: 0 prev scn: 0x0000.000ea466
low scn: 0x0000.000ea474 05/02/2013 11:40:58
next scn: 0x0000.000ea4db 05/02/2013 11:44:07
log file #2:
name #2: /u01/app/oracle/product/11.2.0/oradata/jingyong/redo02.log
thread 1 redo log links: forward: 3 backward: 1
siz: 0x19000 seq: 0x00000011 hws: 0x1 bsz: 512 nab: 0xffffffff flg: 0x8 dup: 1
archive links: fwrd: 0 back: 0 prev scn: 0x0000.000ea474
low scn: 0x0000.000ea4db 05/02/2013 11:44:07
next scn: 0xffff.ffffffff 01/01/1988 00:00:00
log file #3:
name #1: /u01/app/oracle/product/11.2.0/oradata/jingyong/redo03.log
thread 1 redo log links: forward: 0 backward: 2
siz: 0x19000 seq: 0x0000000f hws: 0x2 bsz: 512 nab: 0x2 flg: 0x1 dup: 1
archive links: fwrd: 0 back: 0 prev scn: 0x0000.000e8ed8
low scn: 0x0000.000ea466 05/02/2013 11:40:52
next scn: 0x0000.000ea474 05/02/2013 11:40:58
從記錄資訊中我們可以看到redo02.log檔案的next scn:0xffff.ffffffff,是以redo02.log檔案是目前的
日志檔案,我們可以從v$log視圖中檢視目前的重做日志組
sql> select group#,status from v$log;
group# status
---------- ----------------
1 inactive
2 current
3 inactive
接下來通過shutdown abort模拟一次資料庫故障:
sql> shutdown abort;
oracle instance shut down.
啟動資料庫到nomount狀态,再來使用noresetlogs參數來重建控制檔案:
sql> startup nomount;
oracle instance started.
total system global area 238530560 bytes
fixed size 1335724 bytes
variable size 150998612 bytes
database buffers 83886080 bytes
redo buffers 2310144 bytes
sql> create controlfile reuse database "jingyong" noresetlogs archivelog
2 maxlogfiles 16
3 maxlogmembers 3
4 maxdatafiles 100
5 maxinstances 8
6 maxloghistory 292
7 logfile
8 group 1 '/u01/app/oracle/product/11.2.0/oradata/jingyong/redo01.log' size 50m blocksize 512,
9 group 2 '/u01/app/oracle/product/11.2.0/oradata/jingyong/redo02.log' size 50m blocksize 512,
10 group 3 '/u01/app/oracle/product/11.2.0/oradata/jingyong/redo03.log' size 50m blocksize 512
11 -- standby logfile
12 datafile
13 '/u01/app/oracle/product/11.2.0/oradata/jingyong/system01.dbf',
14 '/u01/app/oracle/product/11.2.0/oradata/jingyong/sysaux01.dbf',
15 '/u01/app/oracle/product/11.2.0/oradata/jingyong/undotbs01.dbf',
16 '/u01/app/oracle/product/11.2.0/oradata/jingyong/users01.dbf',
17 '/u01/app/oracle/product/11.2.0/oradata/jingyong/example01.dbf',
18 '/u01/app/oracle/product/11.2.0/oradata/jingyong/jy01.dbf'
19 character set zhs16gbk
20 ;
control file created.
此時再來對控制檔案進行一次轉儲,檢查log file records部分:
/u01/app/oracle/diag/rdbms/jingyong/jingyong/trace/jingyong_ora_19438.trc
last-recid= 0, old-recno = 0, last-recno = 0)
name #2: /u01/app/oracle/product/11.2.0/oradata/jingyong/redo01.log
siz: 0x19000 seq: 0x00000010 hws: 0x2 bsz: 512 nab: 0x1e flg: 0x0 dup: 1
archive links: fwrd: 2 back: 3 prev scn: 0x0000.000ea466
name #1: /u01/app/oracle/product/11.2.0/oradata/jingyong/redo02.log
siz: 0x19000 seq: 0x00000011 hws: 0x1 bsz: 512 nab: 0xffffffff flg: 0xa dup: 1
archive links: fwrd: 0 back: 1 prev scn: 0x0000.000ea474
name #3: /u01/app/oracle/product/11.2.0/oradata/jingyong/redo03.log
siz: 0x19000 seq: 0x0000000f hws: 0x2 bsz: 512 nab: 0x2 flg: 0x0 dup: 1
archive links: fwrd: 1 back: 0 prev scn: 0x0000.00000000
從上面的記錄我們可以看到重建的控檔案能夠從目前的日志檔案獲得正确的scn及時間點等資訊.同樣地,控制
檔案也能夠從資料檔案中獲得詳細的檢查點資訊:
data file records
(size = 520, compat size = 520, section max = 100, section in-use = 6,
(extent = 1, blkno = 11, numrecs = 100)
data file #1:
name #9: /u01/app/oracle/product/11.2.0/oradata/jingyong/system01.dbf
creation size=0 block size=8192 status=0x12 head=9 tail=9 dup=1
tablespace 0, index=1 krfil=1 prev_file=0
unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
checkpoint cnt:119 scn: 0x0000.000ea4db 05/02/2013 11:44:07
stop scn: 0xffff.ffffffff 05/02/2013 12:44:28
creation checkpointed at scn: 0x0000.00000007 08/13/2009 23:00:53
thread:0 rba:(0x0.0.0)
.....
data file #2:
name #8: /u01/app/oracle/product/11.2.0/oradata/jingyong/sysaux01.dbf
creation size=0 block size=8192 status=0x12 head=8 tail=8 dup=1
tablespace 1, index=2 krfil=2 prev_file=0
creation checkpointed at scn: 0x0000.00000874 08/13/2009 23:00:57
data file #3:
name #7: /u01/app/oracle/product/11.2.0/oradata/jingyong/undotbs01.dbf
creation size=0 block size=8192 status=0x12 head=7 tail=7 dup=1
tablespace 2, index=3 krfil=3 prev_file=0
checkpoint cnt:47 scn: 0x0000.000ea4db 05/02/2013 11:44:07
creation checkpointed at scn: 0x0000.000b7982 08/13/2009 23:56:54
data file #4:
name #6: /u01/app/oracle/product/11.2.0/oradata/jingyong/users01.dbf
creation size=0 block size=8192 status=0x12 head=6 tail=6 dup=1
tablespace 4, index=4 krfil=4 prev_file=0
checkpoint cnt:118 scn: 0x0000.000ea4db 05/02/2013 11:44:07
creation checkpointed at scn: 0x0000.00004743 08/13/2009 23:01:06
....
data file #5:
name #5: /u01/app/oracle/product/11.2.0/oradata/jingyong/example01.dbf
creation size=0 block size=8192 status=0x12 head=5 tail=5 dup=1
tablespace 6, index=5 krfil=5 prev_file=0
checkpoint cnt:43 scn: 0x0000.000ea4db 05/02/2013 11:44:07
creation checkpointed at scn: 0x0000.000bf3fe 04/25/2013 14:05:52
data file #6:
name #4: /u01/app/oracle/product/11.2.0/oradata/jingyong/jy01.dbf
creation size=0 block size=8192 status=0x12 head=4 tail=4 dup=1
tablespace 7, index=6 krfil=6 prev_file=0
checkpoint cnt:11 scn: 0x0000.000ea96d 05/02/2013 12:00:47
creation checkpointed at scn: 0x0000.000e9b4f 05/02/2013 08:43:22
從上面的資訊可以知道由于資料庫是異常關閉的,是以資料檔案的stop scn:為無窮大:
stop scn: 0xffff.ffffffff,接下來對資料庫執行恢複,當恢複完成後再對控制檔案進行轉儲:
sql> recover database;
media recovery complete.
/u01/app/oracle/diag/rdbms/jingyong/jingyong/trace/jingyong_ora_19450.trc
來觀察此跟蹤檔案中的資料檔案資訊:
creation size=0 block size=8192 status=0x2 head=9 tail=9 dup=1
checkpoint cnt:120 scn: 0x0000.000efd7d 05/02/2013 12:43:16
stop scn: 0x0000.000efd7d 05/02/2013 12:43:16
data file #2:
creation size=0 block size=8192 status=0x2 head=8 tail=8 dup=1
....
data file #3:
creation size=0 block size=8192 status=0x2 head=7 tail=7 dup=1
checkpoint cnt:48 scn: 0x0000.000efd7d 05/02/2013 12:43:16
data file #4:
creation size=0 block size=8192 status=0x2 head=6 tail=6 dup=1
checkpoint cnt:119 scn: 0x0000.000efd7d 05/02/2013 12:43:16
data file #5:
creation size=0 block size=8192 status=0x2 head=5 tail=5 dup=1
checkpoint cnt:44 scn: 0x0000.000efd7d 05/02/2013 12:43:16
data file #6:
creation size=0 block size=8192 status=0x2 head=4 tail=4 dup=1
checkpoint cnt:12 scn: 0x0000.000efd7d 05/02/2013 12:43:16
經過恢複之後,資料檔案達到了一緻狀态,checkpoint scn(0x0000.000efd7d)和stop scn(0x0000.000efd7d)
達到了一緻,此時資料庫就完成了恢複,資料庫可以順利啟動:
sql> alter system archive log all;
system altered.
sql> alter database open;
sql> alter tablespace temp add tempfile '/u01/app/oracle/product/11.2.0/oradata/jingyong/temp01.dbf'
2 size 30m reuse autoextend on next 655360 maxsize 40m;
tablespace altered.
現在我們來實驗使用resetlogs方式來重建控制檔案:
模拟資料庫故障
以resetlogs來重建控制檔案
sql> startup nomount
sql> create controlfile reuse database "jingyong" resetlogs archivelog
此時對控制檔案進行一次轉儲
/u01/app/oracle/diag/rdbms/jingyong/jingyong/trace/jingyong_ora_19598.trc
觀察轉儲的跟蹤檔案中的log file record的資訊:
siz: 0x19000 seq: 0x00000000 hws: 0x0 bsz: 512 nab: 0x0 flg: 0x1 dup: 1
archive links: fwrd: 0 back: 0 prev scn: 0x0000.00000000
low scn: 0x0000.00000000 01/01/1988 00:00:00
next scn: 0x0000.00000000 01/01/1988 00:00:00
siz: 0x19000 seq: 0x00000000 hws: 0x0 bsz: 512 nab: 0x2 flg: 0xb dup: 1
從上面的資訊可以看到此時控制檔案中的日志資訊都是空的,oracle認為resetlogs方式下,目前的日志檔案
已經損壞,那麼就意味着oracle可能會丢失送出成功的資料,恢複将是一次不完全的媒體恢複.
此時的資料檔案資訊如下:
checkpoint cnt:123 scn: 0x0000.000efd80 05/02/2013 12:53:11
stop scn: 0xffff.ffffffff 05/02/2013 13:01:12
checkpoint cnt:51 scn: 0x0000.000efd80 05/02/2013 12:53:11
checkpoint cnt:122 scn: 0x0000.000efd80 05/02/2013 12:53:11
checkpoint cnt:47 scn: 0x0000.000efd80 05/02/2013 12:53:11
checkpoint cnt:15 scn: 0x0000.000efd80 05/02/2013 12:53:11
從上面的資訊可以知道由于資料庫是異常關閉的,是以資料檔案的stop scn:為無窮大:
stop scn: 0xffff.ffffffff
不完全恢複最終要求資料庫通過resetlogs方式打開,resetlogs将會強制清空或重建聯機重做日志檔案.
此時執行恢複必須使用backup controlfile選項,否則将會報錯:
ora-00283: recovery session canceled due to errors
ora-01610: recovery using the backup controlfile option must be done
sql> recover database using backup controlfile;
ora-00279: change 982400 generated at 05/02/2013 12:53:11 needed for thread 1
ora-00289: suggestion :
/u01/app/oracle/product/11.2.0/flash_recovery_area/jingyong/archivelog/2013_05_0
2/o1_mf_1_18_%u_.arc
ora-00280: change 982400 for thread 1 is in sequence #18
specify log: {=suggested | filename | auto | cancel}
cancel
media recovery cancelled.
如果線上日志操壞,那麼恢複到最後,oracle提示的最後一個歸檔日志将是不存在的(如果線上日志沒有
損壞,則可以指定線上日志檔案執行恢複),此時可以輸入cancel取消恢複,然後可以強制打開資料庫:
alter database open resetlogs;
ora-00279: change 1003572 generated at 05/02/2013 13:20:06 needed for thread 1
2/o1_mf_1_19_%u_.arc
ora-00280: change 1003572 for thread 1 is in sequence #19
/u01/app/oracle/product/11.2.0/oradata/jingyong/redo01.log
log applied.
sql> alter database open resetlogs;
![【MySQL資料庫】資料庫索引事務1.索引2.事務[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)