重建控制檔案時resetlogs與noresetlogs的使用情況
重建控制檔案時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.
SQL> oradebug setmypid
Statement processed.
SQL> oradebug tracefile_name
/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部分:
SQL> alter session set events 'immediate trace name controlf level 12';
Session altered.
SQL> oradebug setmypid
Statement processed.
SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/jingyong/jingyong/trace/jingyong_ora_19438.trc
***************************************************************************
LOG FILE RECORDS
***************************************************************************
(size = 72, compat size = 72, section max = 16, section in-use = 3,
last-recid= 0, old-recno = 0, last-recno = 0)
(extent = 1, blkno = 10, numrecs = 16)
LOG FILE #1:
name #2: /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: 0x0 dup: 1
Archive links: fwrd: 2 back: 3 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 #1: /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: 0xa dup: 1
Archive links: fwrd: 0 back: 1 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 #3: /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: 0x0 dup: 1
Archive links: fwrd: 1 back: 0 Prev scn: 0x0000.00000000
Low scn: 0x0000.000ea466 05/02/2013 11:40:52
Next scn: 0x0000.000ea474 05/02/2013 11:40:58
從上面的記錄我們可以看到重建的控檔案能夠從當前的日誌檔案獲得正確的SCN及時間點等資訊.同樣地,控制
檔案也能夠從資料檔案中獲得詳細的檢查點資訊:
***************************************************************************
DATA FILE RECORDS
***************************************************************************
(size = 520, compat size = 520, section max = 100, section in-use = 6,
last-recid= 0, old-recno = 0, last-recno = 0)
(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
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.00000874 08/13/2009 23:00:57
thread:0 rba:(0x0.0.0)
.....
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
unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
Checkpoint cnt:47 scn: 0x0000.000ea4db 05/02/2013 11:44:07
Stop scn: 0xffff.ffffffff 05/02/2013 12:44:28
Creation Checkpointed at scn: 0x0000.000b7982 08/13/2009 23:56:54
thread:0 rba:(0x0.0.0)
.....
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
unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
Checkpoint cnt:118 scn: 0x0000.000ea4db 05/02/2013 11:44:07
Stop scn: 0xffff.ffffffff 05/02/2013 12:44:28
Creation Checkpointed at scn: 0x0000.00004743 08/13/2009 23:01:06
thread:0 rba:(0x0.0.0)
....
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
unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
Checkpoint cnt:43 scn: 0x0000.000ea4db 05/02/2013 11:44:07
Stop scn: 0xffff.ffffffff 05/02/2013 12:44:28
Creation Checkpointed at scn: 0x0000.000bf3fe 04/25/2013 14:05:52
thread:0 rba:(0x0.0.0)
....
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
unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
Checkpoint cnt:11 scn: 0x0000.000ea96d 05/02/2013 12:00:47
Stop scn: 0xffff.ffffffff 05/02/2013 12:44:28
Creation Checkpointed at scn: 0x0000.000e9b4f 05/02/2013 08:43:22
thread:0 rba:(0x0.0.0)
.....
從上面的資訊可以知道由於資料庫是異常關閉的,所以資料檔案的Stop scn:為無窮大:
Stop scn: 0xffff.ffffffff,接下來對資料庫執行恢復,當恢復完成後再對控制檔案進行轉儲:
SQL> recover database;
Media recovery complete.
SQL> alter session set events 'immediate trace name controlf level 12';
Session altered.
SQL> oradebug setmypid
Statement processed.
SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/jingyong/jingyong/trace/jingyong_ora_19450.trc
來觀察此跟蹤檔案中的資料檔案資訊:
***************************************************************************
DATA FILE RECORDS
***************************************************************************
(size = 520, compat size = 520, section max = 100, section in-use = 6,
last-recid= 0, old-recno = 0, last-recno = 0)
(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=0x2 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:120 scn: 0x0000.000efd7d 05/02/2013 12:43:16
Stop scn: 0x0000.000efd7d 05/02/2013 12:43:16
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=0x2 head=8 tail=8 dup=1
tablespace 1, index=2 krfil=2 prev_file=0
unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
Checkpoint cnt:120 scn: 0x0000.000efd7d 05/02/2013 12:43:16
Stop scn: 0x0000.000efd7d 05/02/2013 12:43:16
Creation Checkpointed at scn: 0x0000.00000874 08/13/2009 23:00:57
thread:0 rba:(0x0.0.0)
....
DATA FILE #3:
name #7: /u01/app/oracle/product/11.2.0/oradata/jingyong/undotbs01.dbf
creation size=0 block size=8192 status=0x2 head=7 tail=7 dup=1
tablespace 2, index=3 krfil=3 prev_file=0
unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
Checkpoint cnt:48 scn: 0x0000.000efd7d 05/02/2013 12:43:16
Stop scn: 0x0000.000efd7d 05/02/2013 12:43:16
Creation Checkpointed at scn: 0x0000.000b7982 08/13/2009 23:56:54
thread:0 rba:(0x0.0.0)
....
DATA FILE #4:
name #6: /u01/app/oracle/product/11.2.0/oradata/jingyong/users01.dbf
creation size=0 block size=8192 status=0x2 head=6 tail=6 dup=1
tablespace 4, index=4 krfil=4 prev_file=0
unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
Checkpoint cnt:119 scn: 0x0000.000efd7d 05/02/2013 12:43:16
Stop scn: 0x0000.000efd7d 05/02/2013 12:43:16
Creation Checkpointed at scn: 0x0000.00004743 08/13/2009 23:01:06
thread:0 rba:(0x0.0.0)
....
DATA FILE #5:
name #5: /u01/app/oracle/product/11.2.0/oradata/jingyong/example01.dbf
creation size=0 block size=8192 status=0x2 head=5 tail=5 dup=1
tablespace 6, index=5 krfil=5 prev_file=0
unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
Checkpoint cnt:44 scn: 0x0000.000efd7d 05/02/2013 12:43:16
Stop scn: 0x0000.000efd7d 05/02/2013 12:43:16
Creation Checkpointed at scn: 0x0000.000bf3fe 04/25/2013 14:05:52
thread:0 rba:(0x0.0.0)
....
DATA FILE #6:
name #4: /u01/app/oracle/product/11.2.0/oradata/jingyong/jy01.dbf
creation size=0 block size=8192 status=0x2 head=4 tail=4 dup=1
tablespace 7, index=6 krfil=6 prev_file=0
unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
Checkpoint cnt:12 scn: 0x0000.000efd7d 05/02/2013 12:43:16
Stop scn: 0x0000.000efd7d 05/02/2013 12:43:16
Creation Checkpointed at scn: 0x0000.000e9b4f 05/02/2013 08:43:22
thread:0 rba:(0x0.0.0)
....
經過恢復之後,資料檔案達到了一致狀態,checkpoint scn(0x0000.000efd7d)和Stop scn(0x0000.000efd7d)
達到了一致,此時資料庫就完成了恢復,資料庫可以順利啟動:
SQL> ALTER SYSTEM ARCHIVE LOG ALL;
System altered.
SQL> ALTER DATABASE OPEN;
Database altered.
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方式來重建控制檔案:
模擬資料庫故障
SQL> shutdown abort;
ORACLE instance shut down.
以resetlogs來重建控制檔案
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" RESETLOGS 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.
此時對控制檔案進行一次轉儲
SQL> alter session set events 'immediate trace name controlf level 12';
Session altered.
SQL> oradebug setmypid
Statement processed.
SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/jingyong/jingyong/trace/jingyong_ora_19598.trc
觀察轉儲的跟蹤檔案中的log file record的資訊:
***************************************************************************
LOG FILE RECORDS
***************************************************************************
(size = 72, compat size = 72, section max = 16, section in-use = 3,
last-recid= 0, 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: 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
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: 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
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: 0x00000000 hws: 0x0 bsz: 512 nab: 0x2 flg: 0xb 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
從上面的資訊可以看到此時控制檔案中的日誌資訊都是空的,oracle認為resetlogs方式下,當前的日誌檔案
已經損壞,那麼就意味著oracle可能會丟失提交成功的資料,恢復將是一次不完全的介質恢復.
此時的資料檔案資訊如下:
***************************************************************************
DATA FILE RECORDS
***************************************************************************
(size = 520, compat size = 520, section max = 100, section in-use = 6,
last-recid= 0, old-recno = 0, last-recno = 0)
(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:123 scn: 0x0000.000efd80 05/02/2013 12:53:11
Stop scn: 0xffff.ffffffff 05/02/2013 13:01:12
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
unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
Checkpoint cnt:123 scn: 0x0000.000efd80 05/02/2013 12:53:11
Stop scn: 0xffff.ffffffff 05/02/2013 13:01:12
Creation Checkpointed at scn: 0x0000.00000874 08/13/2009 23:00:57
thread:0 rba:(0x0.0.0)
....
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
unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
Checkpoint cnt:51 scn: 0x0000.000efd80 05/02/2013 12:53:11
Stop scn: 0xffff.ffffffff 05/02/2013 13:01:12
Creation Checkpointed at scn: 0x0000.000b7982 08/13/2009 23:56:54
thread:0 rba:(0x0.0.0)
....
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
unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
Checkpoint cnt:122 scn: 0x0000.000efd80 05/02/2013 12:53:11
Stop scn: 0xffff.ffffffff 05/02/2013 13:01:12
Creation Checkpointed at scn: 0x0000.00004743 08/13/2009 23:01:06
thread:0 rba:(0x0.0.0)
....
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
unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
Checkpoint cnt:47 scn: 0x0000.000efd80 05/02/2013 12:53:11
Stop scn: 0xffff.ffffffff 05/02/2013 13:01:12
Creation Checkpointed at scn: 0x0000.000bf3fe 04/25/2013 14:05:52
thread:0 rba:(0x0.0.0)
....
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
unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
Checkpoint cnt:15 scn: 0x0000.000efd80 05/02/2013 12:53:11
Stop scn: 0xffff.ffffffff 05/02/2013 13:01:12
Creation Checkpointed at scn: 0x0000.000e9b4f 05/02/2013 08:43:22
thread:0 rba:(0x0.0.0)
....
從上面的資訊可以知道由於資料庫是異常關閉的,所以資料檔案的Stop scn:為無窮大:
Stop scn: 0xffff.ffffffff
不完全恢復最終要求資料庫透過resetlogs方式開啟,resetlogs將會強制清空或重建聯機重做日誌檔案.
此時執行恢復必須使用backup controlfile選項,否則將會報錯:
SQL> recover database;
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: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
如果線上日誌操壞,那麼恢復到最後,oracle提示的最後一個歸檔日誌將是不存在的(如果線上日誌沒有
損壞,則可以指定線上日誌檔案執行恢復),此時可以輸入cancel取消恢復,然後可以強制開啟資料庫:
alter database open resetlogs;
SQL> recover database using backup controlfile;
ORA-00279: change 1003572 generated at 05/02/2013 13:20:06 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_19_%u_.arc
ORA-00280: change 1003572 for thread 1 is in sequence #19
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/product/11.2.0/oradata/jingyong/redo01.log
Log applied.
Media recovery complete.
SQL> alter database open resetlogs;
Database altered.
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.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/25462274/viewspace-2057139/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- sql 重建控制檔案resetlogs和noresetlogsSQL
- Controlfile 重建控制檔案 noresetlogs, resetlogs..
- 重建控制檔案的案例（RESETLOGS模式和NORESETLOGS模式）模式
- 重建控制檔案的恢復(noresetlogs)
- 使用NORESETLOGS重建控制檔案恢復資料庫資料庫
- 控制檔案丟失恢復例項(3) - 使用重建控制檔案方式(noresetlogs)
- 使用RESETLOGS重建控制檔案恢復資料庫資料庫
- 使用RESETLOGS重建控制檔案恢復資料庫（二）資料庫
- 重建控制檔案
- 重建控制檔案--
- Oracle 控制檔案的重建Oracle
- Oracle重建控制檔案Oracle
- DataGuard重建控制檔案
- oracle 重建控制檔案Oracle
- 重建控制檔案時，與資料檔案相關的Checkpoint資訊來自何處
- 備份與恢復--重建控制檔案
- 如何重建RAC的控制檔案
- ORACLE控制檔案的重建 (轉)Oracle
- 所有控制檔案損壞的恢復--noresetlogs方式
- 利用trace重建控制檔案
- 重建控制檔案後,對臨時表空間(temporary tablespace)進行重建
- [20121105]重建控制檔案少一個資料檔案的情況.txt
- RAC環境重建控制檔案
- 控制檔案重建以及備份
- 查詢Oracle資料檔案的使用情況Oracle
- 所有控制檔案損壞的恢復--resetlogs方式
- 重建Oracle資料庫控制檔案Oracle資料庫
- Oracle RESETLOGS 和 NORESETLOGS 區別說明Oracle
- oracle10g_備份控制檔案_得到重建控制檔案的指令碼Oracle指令碼
- 備份與恢復--重建控制檔案後資料檔案損壞的恢復
- 恢復控制檔案避免使用resetlogs選項
- 重建控制檔案與 datafile offline,tablespace read only
- 重建控制檔案 recreate control file
- 控制檔案損壞重建實驗（上）
- 控制檔案損壞重建實驗（下）
- 用重建控制檔案的方法修改oracle資料檔案路徑Oracle
- 控制檔案重建後的不完全恢復
- oracle之 利用 controlfile trace檔案重建控制檔案Oracle