【故障處理】DG環境主庫丟失歸檔情況下資料檔案的恢復
【故障處理】DG環境主庫丟失歸檔情況下資料檔案的恢復
1 BLOG文件結構圖
2 前言部分
2.1 導讀和注意事項
各位技術愛好者，看完本文後，你可以掌握如下的技能，也可以學到一些其它你所不知道的知識，~O(∩_∩)O~：
① BBED的編譯
② BBED修改檔案頭讓其跳過歸檔從而可以ONLINE(重點)
③ OS命名格式轉換為ASM的命名格式
④ DG環境中備庫丟失資料檔案的情況下的處理過程(重點)
⑤ 資料檔案OFFLINE後應立即做一次RECOVER操作
⑥ BBED環境中kscnwrp的使用
⑦ 查詢表空間的大小，表空間大小為空，資料檔案大小為空的情況
Tips：
① 本文在itpub（http://blog.itpub.net/26736162）、部落格園(http://www.cnblogs.com/lhrbest)和微信公眾號（xiaomaimiaolhr）有同步更新。
② 文章中用到的所有程式碼，相關軟體，相關資料請前往小麥苗的雲盤下載（http://blog.itpub.net/26736162/viewspace-1624453/）。
③ 若網頁文章程式碼格式有錯亂，推薦使用360瀏覽器，也可以下載pdf格式的文件來檢視，pdf文件下載地址：http://blog.itpub.net/26736162/viewspace-1624453/，另外itpub格式顯示有問題，也可以去部落格園地址閱讀。
④ 本篇BLOG中命令的輸出部分需要特別關注的地方我都用灰色背景和粉紅色字型來表示，比如下邊的例子中，thread 1的最大歸檔日誌號為33，thread 2的最大歸檔日誌號為43是需要特別關注的地方；而命令一般使用黃色背景和紅色字型標注；對程式碼或程式碼輸出部分的注釋一般採用藍色字型表示。
|
List of Archived Logs in backup set 11 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- ------------------- ---------- --------- 1 32 1621589 2015-05-29 11:09:52 1625242 2015-05-29 11:15:48 1 33 1625242 2015-05-29 11:15:48 1625293 2015-05-29 11:15:58 2 42 1613951 2015-05-29 10:41:18 1625245 2015-05-29 11:15:49 2 43 1625245 2015-05-29 11:15:49 1625253 2015-05-29 11:15:53 [ZHLHRDB1:root]:/>lsvg -o T_XLHRD_APP1_vg rootvg [ZHLHRDB1:root]:/> 00:27:22 SQL> alter tablespace idxtbs read write; ====》2097152*512/1024/1024/1024=1G |
本文如有錯誤或不完善的地方請大家多多指正，ITPUB留言或QQ皆可，您的批評指正是我寫作的最大動力。
2.2 相關參考文章連線
|
BBED |
|
|
|
http://blog.itpub.net/26736162/viewspace-2084329/ |
|
|
http://blog.itpub.net/26736162/viewspace-2083621/ |
|
|
http://blog.itpub.net/26736162/viewspace-2079337/ |
|
|
http://blog.itpub.net/26736162/viewspace-2075216/ |
|
|
http://blog.itpub.net/26736162/viewspace-2123465/ |
3 故障分析及解決過程
3.1 故障環境介紹
|
專案 |
源庫 |
DG庫 |
|
db 型別 |
RAC |
RAC |
|
db version |
11.2.0.3.7 |
11.2.0.3.7 |
|
db 儲存 |
ASM |
ASM |
|
OS版本及kernel版本 |
AIX 64位 7.1.0.0 |
AIX 64位 7.1.0.0 |
|
關係 |
主備庫為RAC+RAC的物理DG環境 |
|
3.2 故障發生現象及報錯資訊
今天查詢一套DG環境的表空間大小的時候，發現一個表空間的返回值為空，很奇怪，起初我以為是自己的指令碼問題，可是這個指令碼是自己寫的，而且用了很長時間的了，還花了幾分鐘的時間又仔細稽核了一下指令碼，沒發現有什麼不對的地方。
查詢表空間大小的指令碼：
|
set pagesize 9999 line 9999 col TS_Name format a30 WITH WT1 AS (SELECT TS.TABLESPACE_NAME, DF.ALL_BYTES, DECODE(DF.TYPE, 'D', NVL(FS.FREESIZ, 0), 'T', DF.ALL_BYTES - NVL(FS.FREESIZ, 0)) FREESIZ, DF.MAXSIZ, TS.BLOCK_SIZE, TS.LOGGING, TS.FORCE_LOGGING, TS.CONTENTS, TS.EXTENT_MANAGEMENT, TS.SEGMENT_SPACE_MANAGEMENT, TS.RETENTION, TS.DEF_TAB_COMPRESSION, DF.TS_DF_COUNT, TS.BIGFILE, TS.STATUS FROM DBA_TABLESPACES TS, (SELECT 'D' TYPE, TABLESPACE_NAME, COUNT(*) TS_DF_COUNT, SUM(BYTES) ALL_BYTES, SUM(DECODE(MAXBYTES, 0, BYTES, MAXBYTES)) MAXSIZ FROM DBA_DATA_FILES D GROUP BY TABLESPACE_NAME UNION ALL SELECT 'T', TABLESPACE_NAME, COUNT(*) TS_DF_COUNT, SUM(BYTES) ALL_BYTES, SUM(DECODE(MAXBYTES, 0, BYTES, MAXBYTES)) FROM DBA_TEMP_FILES D GROUP BY TABLESPACE_NAME) DF, (SELECT TABLESPACE_NAME, SUM(BYTES) FREESIZ FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME UNION ALL SELECT TABLESPACE_NAME, SUM(D.BLOCK_SIZE * A.BLOCKS) BYTES FROM GV$SORT_USAGE A, DBA_TABLESPACES D WHERE A.TABLESPACE = D.TABLESPACE_NAME GROUP BY TABLESPACE_NAME) FS WHERE TS.TABLESPACE_NAME = DF.TABLESPACE_NAME AND TS.TABLESPACE_NAME = FS.TABLESPACE_NAME(+)) SELECT (SELECT A.TS# FROM V$TABLESPACE A WHERE A.NAME = UPPER(T.TABLESPACE_NAME)) TS#, T.TABLESPACE_NAME TS_NAME, ROUND(T.ALL_BYTES / 1024 / 1024) TS_SIZE_M, ROUND(T.FREESIZ / 1024 / 1024) FREE_SIZE_M, ROUND((T.ALL_BYTES - T.FREESIZ) / 1024 / 1024) USED_SIZE_M, ROUND((T.ALL_BYTES - T.FREESIZ) * 100 / T.ALL_BYTES, 3) USED_PER, ROUND(MAXSIZ / 1024 / 1024 / 1024, 3) MAX_SIZE_G, ROUND(DECODE(MAXSIZ, 0, TO_NUMBER(NULL), (T.ALL_BYTES - FREESIZ)) * 100 / MAXSIZ, 3) USED_PER_MAX, ROUND(T.BLOCK_SIZE) BLOCK_SIZE, T.LOGGING, T.BIGFILE, T.STATUS, T.TS_DF_COUNT FROM WT1 T UNION ALL SELECT TO_NUMBER('') TS#, 'ALL TS:' TS_NAME, ROUND(SUM(T.ALL_BYTES) / 1024 / 1024, 3) TS_SIZE_M, ROUND(SUM(T.FREESIZ) / 1024 / 1024) FREE_SIZE_M, ROUND(SUM(T.ALL_BYTES - T.FREESIZ) / 1024 / 1024) USED_SIZE_M, ROUND(SUM(T.ALL_BYTES - T.FREESIZ) * 100 / SUM(T.ALL_BYTES), 3) USED_PER, ROUND(SUM(MAXSIZ) / 1024 / 1024 / 1024) MAX_SIZE, TO_NUMBER('') "USED,% of MAX Size", TO_NUMBER('') BLOCK_SIZE, '' LOGGING, MAX(T.BIGFILE), MAX(T.STATUS), TO_NUMBER('') TS_DF_COUNT FROM WT1 T ORDER BY TS#; |
結果如下圖：
因為表空間是ONLINE的，若是OFFLINE的話，結果自然為空，由於只有一個資料檔案，那就看看資料檔案的狀態：
SELECT * FROM v$datafile d WHERE d.FILE#=64;
果然資料檔案是64，資料檔案為OFFLINE狀態，而且去備庫檢視的時候資料檔案也是OFFLINE的。這裡有一個LAST_TIME需要注意，日誌為2015年4月21號，而現在都2016年9月21號了，看來是很久很久很久沒有用這個資料檔案了。好吧，很久沒有寫BLOG了，今天就以這個案例為主，說說其修復過程把。
3.2.1 健康檢查報告
一、 執行用自己的健康檢查報告看一下能否發現這個問題呢？
跑完之後，生成的報告在當前目錄，報告的目錄大概如下所示：
|
|
|||||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|||||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|||||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|||||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|||||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|||||
|
|
|
|
|
|
|
先看看資料庫的概況:
再看看，健康檢查的結果：
有2個地方很重要，1個資料檔案有OFFLINE的，第二個是序列的CACHE值小於20，並且已經有enq: SQ - contention等待事件的發生了，說明比較嚴重，應該修改其cache值。我們點選到相應的位置可以檢視細節。
可以看到是64號檔案是OFFLINE狀態的。
另外，我們看看報告中提到的序列等待問題，可以看到有6個序列的cache值設定有問題，已經導致了會話阻塞了，這部分的cache值強烈建議修改，修改語句在報告中也已經給出。
告警日誌問題不是很大，可以忽略。
六、 4級告警：無效物件
無效物件也可以修改一下，報告中提供了具體的指令碼。
好了，報告不多看了，今天的主題是如何修復那個OFFLINE的資料問題，報告的指令碼內容可以私聊我。
3.3 故障分析及解決過程
因為是DG環境，所以首先我們來恢復主庫，然後再修復備庫的檔案問題。
|
SYS@oraLHRD1> select status from v$datafile d WHERE d.FILE#=64;
STATUS ------- OFFLINE
SYS@oraLHRD1> select file#,online_status,change#,ERROR from v$recover_file;
FILE# ONLINE_ CHANGE# ERROR ---------- ------- ---------- ----------------------------------------------------------------- 64 OFFLINE 1764555149
SYS@oraLHRD1> alter database datafile 64 online; alter database datafile 64 online * ERROR at line 1: ORA-01113: file 64 needs media recovery ORA-01110: data file 64: '+DATA1/oralhrs/datafile/tbs101.262.876578481'
SYS@oraLHRD1> recover datafile 64; ORA-00279: change 1764555149 generated at 03/27/2015 10:42:00 needed for thread 2 ORA-00289: suggestion : /arch/2_1128_868895513.arc ORA-00280: change 1764555149 for thread 2 is in sequence #1128
Specify log: {<RET>=suggested | filename | AUTO | CANCEL} AUTO ORA-00308: cannot open archived log '/arch/2_1128_868895513.arc' ORA-27037: unable to obtain file status IBM AIX RISC System/6000 Error: 2: No such file or directory Additional information: 3
ORA-00308: cannot open archived log '/arch/2_1128_868895513.arc' ORA-27037: unable to obtain file status IBM AIX RISC System/6000 Error: 2: No such file or directory Additional information: 3
SYS@oraLHRD1> ! ls /arch/2_1128_868895513.arc ls: 0653-341 The file /arch/2_1128_868895513.arc does not exist.
|
可以看到要恢復64號檔案需要的是1128號歸檔日誌，從之前的查詢我們也知道日誌最後一次訪問是2015年4月21，而現在系統的歸檔號為1W多了：
SELECT * FROM v$log d WHERE d.STATUS='CURRENT' ORDER BY thread#;
SELECT a.FILE#, a.NAME,a.CHECKPOINT_CHANGE#,a.LAST_CHANGE#,status FROM v$datafile a;
SELECT a.FILE#,a.NAME,a.RECOVER,a.CHECKPOINT_CHANGE#,status FROM v$datafile_header a;
那目前是資料檔案OFFLINE，而歸檔檔案又丟失了，如果想把該檔案ONLINE，我們必須採用BBED來推進資料檔案的SCN號到最近的日誌號才可以。有關該部分的理論知識可以參考： 【BBED】丟失歸檔檔案情況下的資料檔案的恢復：http://blog.itpub.net/26736162/viewspace-2079337/
這裡我們依然採用BBED來修復該問題。
注意：由於我們的環境是DG環境，所以先把備庫的監聽器停掉，以免恢復的過程中，主庫生成的日誌傳遞到備庫，而主庫日誌被刪除後，修復該檔案就又得往前推進了，所以先把備庫的監聽停掉，確保主庫的日誌不被刪除。
|
[ZFLHRSDB4:root]:/>crsctl stat res -t -------------------------------------------------------------------------------- NAME TARGET STATE SERVER STATE_DETAILS -------------------------------------------------------------------------------- Local Resources -------------------------------------------------------------------------------- ora.LISTENER.lsnr ONLINE ONLINE zflhrsdb3 ONLINE ONLINE zflhrsdb4 ora.LISTENER_DG.lsnr====>>>>> 這個是DG的監聽器 ONLINE ONLINE zflhrsdb3 ONLINE ONLINE zflhrsdb4 ora.asm ONLINE ONLINE zflhrsdb3 Started ONLINE ONLINE zflhrsdb4 Started ora.gsd OFFLINE OFFLINE zflhrsdb3 OFFLINE OFFLINE zflhrsdb4 ora.net1.network ONLINE ONLINE zflhrsdb3 ONLINE ONLINE zflhrsdb4 ora.ons ONLINE ONLINE zflhrsdb3 ONLINE ONLINE zflhrsdb4 ora.registry.acfs ONLINE ONLINE zflhrsdb3 ONLINE ONLINE zflhrsdb4 -------------------------------------------------------------------------------- Cluster Resources -------------------------------------------------------------------------------- ora.LISTENER_SCAN1.lsnr 1 ONLINE ONLINE zflhrsdb4 ora.cvu 1 ONLINE ONLINE zflhrsdb4 ora.oc4j 1 ONLINE ONLINE zflhrsdb4 ora.oralhrsg.db 1 ONLINE ONLINE zflhrsdb3 Open,Readonly 2 ONLINE ONLINE zflhrsdb4 Open,Readonly ora.scan1.vip 1 ONLINE ONLINE zflhrsdb4 ora.zflhrsdb3.vip 1 ONLINE ONLINE zflhrsdb3 ora.zflhrsdb4.vip 1 ONLINE ONLINE zflhrsdb4 [ZFLHRSDB4:root]:/> [ZFLHRSDB4:root]:/> [ZFLHRSDB4:root]:/> [ZFLHRSDB4:root]:/> [ZFLHRSDB4:root]:/>crsctl stop res ora.LISTENER_DG.lsnr CRS-2673: Attempting to stop 'ora.LISTENER_DG.lsnr' on 'zflhrsdb4' CRS-2673: Attempting to stop 'ora.LISTENER_DG.lsnr' on 'zflhrsdb3' CRS-2677: Stop of 'ora.LISTENER_DG.lsnr' on 'zflhrsdb4' succeeded CRS-2677: Stop of 'ora.LISTENER_DG.lsnr' on 'zflhrsdb3' succeeded [ZFLHRSDB4:root]:/> |
接下來就可以做恢復操作了。
3.3.1 修復主庫的OFFLINE檔案
首先，64號檔案當前的SCN號1764555149，我們需要將其修改為15760391176，而日誌號也需要轉換為11087號，這些都需要轉換為十六進位制，如下：
|
SYS@oraLHRD2> SELECT TO_CHAR(1764555149, 'xxxxxxxxxxxx') CUR_SCN, 2 TO_CHAR(15760391176, 'xxxxxxxxxxxx') TARGET_SCN, 3 TO_CHAR(11087, 'xxxxxxxxxxxx') TARGET_SEQ 4 FROM DUAL;
CUR_SCN TARGET_SCN TARGET_SEQ ------------- ------------- ------------- 692cf98d 3ab647c08 3275 |
692cf98d和後邊BBED查詢出來的資料檔案頭的結果一致。
一、 BBED修改檔案頭推進SCN號 1、 編譯BBED首先準備BBED的環境，編譯BBED，將以下4個檔案複製到Oracle的相關的目錄：
注意：檔案我已上傳到雲盤，可以去http://blog.itpub.net/26736162/viewspace-1624453/下載。
接下來我們編譯BBED:
|
[ZFLHRSDB1:oracle]:/oracle>ls -l $ORACLE_HOME/rdbms/lib/*sbbd* -rw-r--r-- 1 root system 1671 May 26 2010 /oracle/app/oracle/product/11.2.0/db/rdbms/lib/sbbdpt.o -rw-r--r-- 1 root system 900 May 26 2010 /oracle/app/oracle/product/11.2.0/db/rdbms/lib/ssbbded.o [ZFLHRSDB1:oracle]:/oracle>ls -l $ORACLE_HOME/rdbms/mesg/bbed* -rw-r--r-- 1 root system 8704 May 27 2010 /oracle/app/oracle/product/11.2.0/db/rdbms/mesg/bbedus.msb -rw-r--r-- 1 root system 10270 Jul 25 2000 /oracle/app/oracle/product/11.2.0/db/rdbms/mesg/bbedus.msg [ZFLHRSDB1:oracle]:/oracle>exit You have mail in /usr/spool/mail/root [ZFLHRSDB1:root]:/>chown oracle:dba /oracle/app/oracle/product/11.2.0/db/rdbms/lib/sbbdpt.o [ZFLHRSDB1:root]:/>chown oracle:dba /oracle/app/oracle/product/11.2.0/db/rdbms/lib/ssbbded.o [ZFLHRSDB1:root]:/>chown oracle:dba /oracle/app/oracle/product/11.2.0/db/rdbms/mesg/bbedus.msb [ZFLHRSDB1:root]:/>chown oracle:dba /oracle/app/oracle/product/11.2.0/db/rdbms/mesg/bbedus.msg [ZFLHRSDB1:root]:/>su - oracle [ZFLHRSDB1:oracle]:/oracle>make -f ORACLEHOME/rdbms/lib/insrdbms.mkBBED=ORACLE_HOME/bin/bbed$ORACLE_HOME/bin/bbed
Linking BBED utility (bbed) rm -f /oracle/app/oracle/product/11.2.0/db/bin/bbed ld -b64 -o /oracle/app/oracle/product/11.2.0/db/bin/bbed -L/oracle/app/oracle/product/11.2.0/db/rdbms/lib/ -L/oracle/app/oracle/product/11.2.0/db/lib/ /oracle/app/oracle/product/11.2.0/db/lib/s0main.o /oracle/app/oracle/product/11.2.0/db/rdbms/lib/ssbbded.o /oracle/app/oracle/product/11.2.0/db/rdbms/lib/sbbdpt.o -bI:/usr/lib/aio.exp `cat /oracle/app/oracle/product/11.2.0/db/lib/ldflags` -lncrypt11 -lnsgr11 -lnzjs11 -ln11 -lnl11 -ldbtools11 `cat /oracle/app/oracle/product/11.2.0/db/lib/ldflags` -lncrypt11 -lnsgr11 -lnzjs11 -ln11 -lnl11 -lnro11 `cat /oracle/app/oracle/product/11.2.0/db/lib/ldflags` -lncrypt11 -lnsgr11 -lnzjs11 -ln11 -lnl11 -lnnz11 -lzt11 -lztkg11 -lztkg11 -lclient11 -lnnetd11 -lvsn11 -lcommon11 -lgeneric11 -lmm -lsnls11 -lnls11 -lcore11 -lsnls11 -lnls11 -lcore11 -lsnls11 -lnls11 -lxml11 -lcore11 -lunls11 -lsnls11 -lnls11 -lcore11 -lnls11 `cat /oracle/app/oracle/product/11.2.0/db/lib/ldflags` -lncrypt11 -lnsgr11 -lnzjs11 -ln11 -lnl11 -lnro11 `cat /oracle/app/oracle/product/11.2.0/db/lib/ldflags` -lncrypt11 -lnsgr11 -lnzjs11 -ln11 -lnl11 -lclient11 -lnnetd11 -lvsn11 -lcommon11 -lgeneric11 -lpls11 -lrt -lsnls11 -lnls11 -lcore11 -lsnls11 -lnls11 -lcore11 -lsnls11 -lnls11 -lxml11 -lcore11 -lunls11 -lsnls11 -lnls11 -lcore11 -lnls11 -lclient11 -lnnetd11 -lvsn11 -lcommon11 -lgeneric11 -lsnls11 -lnls11 -lcore11 -lsnls11 -lnls11 -lcore11 -lsnls11 -lnls11 -lxml11 -lcore11 -lunls11 -lsnls11 -lnls11 -lcore11 -lnls11 -lld -lm `cat /oracle/app/oracle/product/11.2.0/db/lib/sysliblist` -lm -lsql11 /oracle/app/oracle/product/11.2.0/db/lib/nautab.o /oracle/app/oracle/product/11.2.0/db/lib/naeet.o /oracle/app/oracle/product/11.2.0/db/lib/naect.o /oracle/app/oracle/product/11.2.0/db/lib/naedhs.o ld: 0711-224 WARNING: Duplicate symbol: .aio_nwait_timeout64 ld: 0711-224 WARNING: Duplicate symbol: aio_nwait_timeout64 ld: 0711-224 WARNING: Duplicate symbol: .aio_nwait64 ld: 0711-224 WARNING: Duplicate symbol: aio_nwait64 ld: 0711-345 Use the -bloadmap or -bnoquiet option to obtain more information. ld: 0711-773 WARNING: Object /oracle/app/oracle/product/11.2.0/db/lib//libgeneric11.a[sdbgrfu.o], imported symbol timezone Symbol was expected to be local. Extra instructions are being generated to reference the symbol. |
編譯完成後可以使用BBED了：
|
SYS@oraLHRD1> set line 9999 pagesize 9999 SYS@oraLHRD1> col name format a80 SYS@oraLHRD1> select file#||' '||name||' '||bytes name from v$datafile where file#=64;
NAME -------------------------------------------------------------------------------- 64 +DATA1/oralhrs/datafile/tbs101.262.876578481 104857600 [ZFLHRSDB1:root]:/>su - grid [ZFLHRSDB1:grid]:/home/grid>asmcmd ASMCMD> cp +DATA1/oralhrs/datafile/tbs101.262.876578481 /tmp/a.dbf copying +DATA1/oralhrs/datafile/tbs101.262.876578481 -> /tmp/a.dbf ASMCMD> exit [ZFLHRSDB1:grid]:/home/grid>exit [ZFLHRSDB1:root]:/>su - oracle [ZFLHRSDB1:oracle]:/oracle>vi /tmp/file.txt [ZFLHRSDB1:oracle]:/oracle>cat /tmp/file.txt 1 /tmp/a.dbf [ZFLHRSDB1:oracle]:/oracle>bbed PASSWORD=blockedit mode=edit blocksize=8192 listfile=/tmp/file.txt BBED-00303: unable to open file '/tmp/a.dbf'
[ZFLHRSDB1:oracle]:/oracle>l /tmp/a.dbf -rw-r----- 1 grid dba 104865792 Sep 20 17:07 /tmp/a.dbf [ZFLHRSDB1:oracle]:/oracle>exit [ZFLHRSDB1:root]:/>chown oracle.dba /tmp/a.dbf [ZFLHRSDB1:root]:/>su - oracle [ZFLHRSDB1:oracle]:/oracle>bbed PASSWORD=blockedit mode=edit blocksize=8192 listfile=/tmp/file.txt BBED-00303: unable to open file 'log.bbd'
[ZFLHRSDB1:oracle]:/oracle>cd /tmp [ZFLHRSDB1:oracle]:/tmp>bbed PASSWORD=blockedit mode=edit blocksize=8192 listfile=/tmp/file.txt
BBED: Release 2.0.0.0.0 - Limited Production on Tue Sep 20 17:11:28 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED> info File# Name Size(blks) ----- ---- ---------- 1 /tmp/a.dbf 0
BBED> show FILE# 1 BLOCK# 1 OFFSET 0 DBA 0x00400001 (4194305 1,1) FILENAME /tmp/a.dbf BIFILE bifile.bbd LISTFILE /tmp/file.txt BLOCKSIZE 8192 MODE Edit EDIT Unrecoverable IBASE Dec OBASE Dec WIDTH 80 COUNT 512 LOGFILE log.bbd SPOOL No
BBED> p kcvfhckp struct kcvfhckp, 160 bytes @484 struct kcvcpscn, 8 bytes @484 ====>>>>>kscnbas，這裡是64號檔案的當前SCN號，和之前查詢來的是一致的，十進位制為：1764555149 ub4 kscnbas @484 0x692cf98d ub2 kscnwrp @488 0x0000 ub4 kcvcptim @492 0x342e3478 ub2 kcvcpthr @496 0x0002 union u, 12 bytes @500 struct kcvcprba, 12 bytes @500 ====>>>>>kcrbaseq，這裡是64號檔案的當前日誌號，468轉換為十進位制是1128 ub4 kcrbaseq @500 0x00000468 ub4 kcrbabno @504 0x0002c2fe ub2 kcrbabof @508 0x0010 ub1 kcvcpetb[0] @512 0x06 ub1 kcvcpetb[1] @513 0x00 ub1 kcvcpetb[2] @514 0x00
《《《《。。。。。。。。篇幅原因，有省略。。。。。。。。》》》》
====》資料檔案的scn被記錄在檔案1號block偏移量484位元組開始的四個位元組中
BBED> d /v dba 1,1 offset 484 count 64 File: /tmp/a.dbf (1) Block: 1 Offsets: 484 to 547 Dba:0x00400001 ------------------------------------------------------- 692cf98d 00000000 342e3478 00020000 l i,......4.4x.... 00000468 0002c2fe 00100001 06000000 l ...h............ 00000000 00000000 00000000 00000000 l ................ 00000000 00000000 00000000 00000000 l ................
<16 bytes per line>
====》AIX下儲存是正序，這個和linux正好相反 BBED> modify /x 3ab645ab3 dba 1,1 offset 484 BBED-00209: invalid number (3ab645ab3)
BBED> modify /x ab647c08 dba 1,1 offset 484 BBED-00209: invalid number (ab647c08) ====》484號不能直接修改，因為ab647c08是以字母開頭，必須以數字開頭，那麼我們從483號開始修改 BBED> modify /x 00ab647c dba 1,1 offset 483 File: /tmp/a.dbf (1) Block: 1 Offsets: 483 to 546 Dba:0x00400001 ------------------------------------------------------------------------ 00ab647c 7b000300 00342e34 78000200 0000002b 4f0002c2 fe000000 10060000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
<32 bytes per line>
BBED> modify /x 08 dba 1,1 offset 487 File: /tmp/a.dbf (1) Block: 1 Offsets: 487 to 550 Dba:0x00400001 ------------------------------------------------------------------------ 08000300 00342e34 78000200 0000002b 4f0002c2 fe000000 10060000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
<32 bytes per line>
BBED> d /v dba 1,1 offset 484 count 64 File: /tmp/a.dbf (1) Block: 1 Offsets: 484 to 547 Dba:0x00400001 ------------------------------------------------------- ab647c08 00030000 342e3478 00020000 l .d|.....4.4x.... 00002b4f 0002c2fe 00000010 06000000 l ..+O............ 00000000 00000000 00000000 00000000 l ................ 00000000 00000000 00000000 00000000 l ................
<16 bytes per line>
BBED>
BBED> p kcvfhckp struct kcvfhckp, 160 bytes @484 struct kcvcpscn, 8 bytes @484 ub4 kscnbas @484 0xab647c08 ub2 kscnwrp @488 0x0003 ====>>>日誌號過大，所以用到了kscnwrp ub4 kcvcptim @492 0x342e3478 ub2 kcvcpthr @496 0x0002 union u, 12 bytes @500 struct kcvcprba, 12 bytes @500 ub4 kcrbaseq @500 0x00002b4f ub4 kcrbabno @504 0x0002c2fe ub2 kcrbabof @508 0x0000 ub1 kcvcpetb[0] @512 0x06 ub1 kcvcpetb[1] @513 0x00
BBED> sum apply Check value for File 1, Block 1: current = 0xcb25, required = 0xcb25 |
要想跳過歸檔還需要資料檔案頭塊的rba。它由seq#、log block#、偏移量(固定為16)組成，決定了資料檔案從哪個歸檔日誌的哪個位置開始應用歸檔。Rba位於資料檔案頭塊偏移量500處開始連續的12個位元組，有關RBA的理論知識參考：http://blog.itpub.net/26736162/viewspace-2079337/
|
BBED> d /v dba 1,1 offset 500 count 64 File: /tmp/a.dbf (1) Block: 1 Offsets: 500 to 563 Dba:0x00400001 ------------------------------------------------------- 00000468 0002c2fe 00100001 06000000 l ...h............ 00000000 00000000 00000000 00000000 l ................ 00000000 00000000 00000000 00000000 l ................ 00000000 00000000 00000000 00000000 l ................
<16 bytes per line>
BBED> modify /x 00003275 dba 1,1 offset 500 File: /tmp/a.dbf (1) Block: 1 Offsets: 500 to 563 Dba:0x00400001 ------------------------------------------------------------------------ 00003275 0002c2fe 00000010 06000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
<32 bytes per line>
BBED> sum apply Check value for File 1, Block 1: current = 0xd21f, required = 0xd21f
BBED> modify /x 00000002 dba 1,1 offset 504 File: /tmp/a.dbf (1) Block: 1 Offsets: 504 to 567 Dba:0x00400001 ------------------------------------------------------------------------ 00000002 00000010 06000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
<32 bytes per line>
BBED> sum apply Check value for File 1, Block 1: current = 0x10e1, required = 0x10e1
BBED> modify /x 00000010 dba 1,1 offset 508 File: /tmp/a.dbf (1) Block: 1 Offsets: 508 to 571 Dba:0x00400001 ------------------------------------------------------------------------ 00000010 06000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
<32 bytes per line>
BBED> sum apply Check value for File 1, Block 1: current = 0xd21f, required = 0xd21f
BBED> d /v dba 1,1 offset 500 count 64 File: /tmp/a.dbf (1) Block: 1 Offsets: 500 to 563 Dba:0x00400001 ------------------------------------------------------- 00003275 00000002 00000010 06000000 l ..2u............ 00000000 00000000 00000000 00000000 l ................ 00000000 00000000 00000000 00000000 l ................ 00000000 00000000 00000000 00000000 l ................
<16 bytes per line>
BBED> p kcvfhckp struct kcvfhckp, 160 bytes @484 struct kcvcpscn, 8 bytes @484 ub4 kscnbas @484 0xab647c08 ub2 kscnwrp @488 0x0003 ub4 kcvcptim @492 0x342e3478 ub2 kcvcpthr @496 0x0002 union u, 12 bytes @500 struct kcvcprba, 12 bytes @500 ub4 kcrbaseq @500 0x00003275 ub4 kcrbabno @504 0x00000002 ub2 kcrbabof @508 0x0000 ub1 kcvcpetb[0] @512 0x06 ub1 kcvcpetb[1] @513 0x00 ub1 kcvcpetb[2] @514 0x00 ub1 kcvcpetb[3] @515 0x00 |
修復完畢，BBED的任務已經完成。
下邊將檔案從檔案系統複製到ASM中：
|
[ZFLHRSDB1:root]:/>l /tmp/a.dbf -rw-r----- 1 oracle dba 104865792 Sep 20 17:39 /tmp/a.dbf [ZFLHRSDB1:root]:/>chown grid.dba /tmp/a.dbf [ZFLHRSDB1:root]:/>su - grid [ZFLHRSDB1:grid]:/home/grid>asmcmd ASMCMD> cp /tmp/a.dbf +DATA1/oralhrs/datafile/a.dbf copying /tmp/a.dbf -> +DATA1/oralhrs/datafile/a.dbf ASMCMD> [ZFLHRSDB1:root]:/>su - oracle [ZFLHRSDB1:oracle]:/oracle>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Tue Sep 20 17:47:21 2016
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options
SYS@oraLHRD1> alter database rename file '+DATA1/oralhrs/datafile/tbs101.262.923076161' TO '+DATA1/oralhrs/datafile/a.dbf';
Database altered.
SELECT a.FILE#,a.NAME,a.RECOVER,a.CHECKPOINT_CHANGE#,status FROM v$datafile_header a WHERE A.FILE# IN (1,2,64);
====>>>>>從截圖可以看出雖然是OFFLINE狀態，但是CHECKPOINT_CHANGE#已經和其它檔案是一致的了。
SYS@oraLHRD1> COL CHANGE# FOR 999999999999999 SYS@oraLHRD1> select file#,online_status,change#,ERROR from v$recover_file;
FILE# ONLINE_ CHANGE# ERROR ---------- ------- ---------------- ----------------------------------------------------------------- 64 OFFLINE 15760391176
SYS@oraLHRD1> recover datafile 64; Media recovery complete.====>>>>>恢復操作成功完成。 SYS@oraLHRD1> alter database datafile 64 online;
Database altered.====>>>>>資料檔案成功ONLINE了。
SYS@oraLHRD1> SYS@oraLHRD1> select file#,online_status,change#,ERROR from v$recover_file;
no rows selected====>>>>>沒有需要恢復的檔案了 SYS@oraLHRD1> COL CHECKPOINT_CHANGE# FOR 999999999999999 SYS@oraLHRD1> SELECT a.FILE#,a.RECOVER,a.CHECKPOINT_CHANGE#,status FROM v$datafile_header a WHERE A.FILE# IN (1,2,64); FILE# REC CHECKPOINT_CHANGE# STATUS ---------- --- ------------------ ------- 1 NO 15760391176 ONLINE 2 NO 15760391176 ONLINE 64 NO 15760480489 ONLINE |
此時再次查詢表空間的佔用情況，已經可以看到了TBS101的大小了：
接下來建立一個表，看看錶空間是否正常：
|
SYS@oraLHRD1> CREATE TABLE T_TEST_LHR TABLESPACE TBS101 NOLOGGING AS SELECT * FROM DBA_OBJECTS;
Table created.
SYS@oraLHRD1> insert into t_test select * from T_TEST_LHR;
198881 rows created.
SYS@oraLHRD1> commit;
Commit complete.
SYS@oraLHRD1> ALTER SYSTEM CHECKPOINT;
System altered.
SYS@oraLHRD1> |
再次檢視錶空間大小：
表空間佔用從原來的11M到現在的63M，正常了。
二、 修改主庫的64號檔名稱為ASM格式表空間恢復了，但是檔名稱還是a.dbf，接下來我們修改a.dbf為ASM的命名格式：
|
SYS@oraLHRD1> alter tablespace TBS101 offline ;
Tablespace altered.
SYS@oraLHRD1> EXIT Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options [ZFLHRSDB1:oracle]:/oracle>rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Wed Sep 21 11:35:39 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORAIPPS (DBID=1344172889)
RMAN> convert datafile '+DATA1/oralhrs/datafile/a.dbf' format '+DATA1';
Starting conversion at target at 2016-09-21 11:36:12 using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile conversion input file name=+DATA1/oralhrs/datafile/a.dbf converted datafile=+DATA1/oralhrs/datafile/tbs101.262.923139373 channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01 Finished conversion at target at 2016-09-21 11:36:13
RMAN> exit
Recovery Manager complete. [ZFLHRSDB1:oracle]:/oracle>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Wed Sep 21 11:36:33 2016
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options
SYS@oraLHRD1> alter tablespace TBS101 rename datafile '+DATA1/oralhrs/datafile/a.dbf' to '+DATA1/oralhrs/datafile/tbs101.262.923139373';
Tablespace altered.
SYS@oraLHRD1> alter tablespace TBS101 online;
Tablespace altered.
SYS@oraLHRD1> SYS@oraLHRD1> col name for a50 SYS@oraLHRD1> col CHECKPOINT_CHANGE# for 9999999999999 SYS@oraLHRD1> SELECT a.FILE#,a.NAME,a.RECOVER,a.CHECKPOINT_CHANGE#,status FROM v$datafile_header a WHERE A.FILE# IN (1,2,64);
FILE# NAME REC CHECKPOINT_CHANGE# STATUS ---------- -------------------------------------------------- --- ------------------ ------- 1 +DATA1/oralhrs/datafile/system.331.876578243 NO 15760485734 ONLINE 2 +DATA1/oralhrs/datafile/sysaux.330.876578347 NO 15760485734 ONLINE 64 +DATA1/oralhrs/datafile/tbs101.262.923139373 NO 15760490379 ONLINE
SYS@oraLHRD1> alter system checkpoint;
System altered.
SYS@oraLHRD1> SELECT a.FILE#,a.NAME,a.RECOVER,a.CHECKPOINT_CHANGE#,status FROM v$datafile_header a WHERE A.FILE# IN (1,2,64);
FILE# NAME REC CHECKPOINT_CHANGE# STATUS ---------- -------------------------------------------------- --- ------------------ ------- 1 +DATA1/oralhrs/datafile/system.331.876578243 NO 15760492416 ONLINE 2 +DATA1/oralhrs/datafile/sysaux.330.876578347 NO 15760492416 ONLINE 64 +DATA1/oralhrs/datafile/tbs101.262.923139373 NO 15760492416 ONLINE ====>>>>> 執行完checkpoint後，SCN號已經一致了。 |
OK，成功！主庫修復完畢，接下來就剩下備庫了。
3.3.2 修復備庫的OFFLINE檔案
檢視備庫的檔案情況，發現64號檔案依然處於OFFLINE狀態。
|
SYS@oraLHRDG2> SELECT a.FILE#,a.NAME,a.RECOVER,a.CHECKPOINT_CHANGE#,status FROM v$datafile_header a WHERE A.FILE# IN (1,2,64);
FILE# NAME REC CHECKPOINT_CHANGE# STATUS ---------- ---------------------------------------------------- --- ------------------ ------- 1 +DATA1/oralhrsg/datafile/system.358.869055401 1.5760E+10 ONLINE 2 +DATA1/oralhrsg/datafile/sysaux.354.869047985 1.5760E+10 ONLINE 64 +DATA1/oralhrsg/datafile/tbs101.382.875442343 1764555149 OFFLINE SYS@oraLHRDG2> recover datafile 64; ORA-00283: recovery session canceled due to errors ORA-01153: an incompatible media recovery is active SYS@oraLHRDG2> recover managed standby database cancel; Media recovery complete. SYS@oraLHRDG2> recover datafile 64; ORA-00283: recovery session canceled due to errors ORA-01610: recovery using the BACKUP CONTROLFILE option must be done SYS@oraLHRDG2> alter database recover managed standby database using current logfile disconnect from session;
Database altered.
SYS@oraLHRDG2> alter database datafile 64 online; alter database datafile 64 online * ERROR at line 1: ORA-01113: file 64 needs media recovery ORA-01110: data file 64: '+DATA1/oralhrsg/datafile/tbs101.382.875442343' |
雖然可以開啟實時應用程式，但是64號檔案依然不能ONLINE，因為現在的日誌號已經到了1W多了，而64號檔案的日誌號卻還在1K多，這個用日誌必然不能恢復了，因為日誌早不存在了嘛，難道我又得用BBED？不！！！這裡我們可以從主庫複製資料檔案過來，且往下看。。。
主庫用CONVERT命令備份64號檔案：
|
[ZFLHRSDB1:oracle]:/oracle>rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Wed Sep 21 14:49:56 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORAIPPS (DBID=1344172889)
RMAN> convert datafile '+DATA1/oralhrs/datafile/tbs101.262.923139373' format '/tmp/tbs101.dbf_bk';
Starting conversion at target at 2016-09-21 14:51:16 using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile conversion input file name=+DATA1/oralhrs/datafile/tbs101.262.923139373 converted datafile=/tmp/tbs101.dbf_bk channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:03 Finished conversion at target at 2016-09-21 14:51:19 將備份的檔案複製到備庫： [ZFLHRSDB1:oracle]:/tmp>scp /tmp/tbs101.dbf_bk oracle@22.166.166.16:/tmp/tbs101.dbf_bk The authenticity of host '22.166.166.16 (22.166.166.16)' can't be established. RSA key fingerprint is 7b:d6:ba:ca:b3:71:b5:0b:bf:14:f4:e4:18:5f:51:45. Are you sure you want to continue connecting (yes/no)? yes Warning: Permanently added '22.166.166.16' (RSA) to the list of known hosts. tbs101.dbf_bk 100% 100MB 50.0MB/s 00:02
|
在備庫上轉換檔案為ASM格式：
|
[ZFLHRSDB4:root]:/>l /tmp/tbs101.dbf_bk -rw-r----- 1 oracle dba 104865792 Sep 21 14:52 /tmp/tbs101.dbf_bk [ZFLHRSDB4:root]:/>su - oracle [ZFLHRSDB4:oracle]:/oracle>rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Wed Sep 21 14:52:49 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORAIPPS (DBID=1344172889)
RMAN> convert datafile '/tmp/tbs101.dbf_bk' format '+DATA1';
Starting conversion at target at 2016-09-21 14:53:33 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=1542 instance=oraLHRDG2 device type=DISK channel ORA_DISK_1: starting datafile conversion input file name=/tmp/tbs101.dbf_bk converted datafile=+DATA1/oralhrsg/datafile/tbs101.382.923151215 channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01 Finished conversion at target at 2016-09-21 14:53:36
RMAN> exit
Recovery Manager complete.
|
備庫上進行重新命名操作，若是備庫上64號檔案被刪除了，我們此時也可以先重建64號檔案：
|
SYS@oraLHRDG2> alter system set standby_file_management='MANUAL' SID='*';
System altered. SYS@oraLHRDG2> alter database create datafile 64 as '+DATA1';
Database altered.
SYS@oraLHRDG2> SELECT a.FILE#,a.NAME,a.RECOVER,a.CHECKPOINT_CHANGE#,status FROM v$datafile_header a WHERE A.FILE# IN (1,2,64);
FILE# NAME REC CHECKPOINT_CHANGE# STATUS ---------- ------------------------------------------------ --- ------------------ ------- 1 +DATA1/oralhrsg/datafile/system.358.869055401 1.5761E+10 ONLINE 2 +DATA1/oralhrsg/datafile/sysaux.354.869047985 1.5761E+10 ONLINE 64 +DATA1/oralhrsg/datafile/tbs101.483.923151901 1.5761E+10 OFFLINE SYS@oraLHRDG2> ALTER DATABASE DATAFILE 64 ONLINE; ALTER DATABASE DATAFILE 64 ONLINE * ERROR at line 1: ORA-01113: file 64 needs media recovery ORA-01110: data file 64: '+DATA1/oralhrsg/datafile/tbs101.483.923151901' |
可以看到64號檔案有了，下邊進行重新命名，修改為我們從主庫複製過來的64號檔案：
|
SYS@oraLHRDG2> ALTER DATABASE RENAME FILE '+DATA1/oralhrsg/datafile/tbs101.483.923151901' TO '+DATA1/oralhrsg/datafile/tbs101.382.923151215'; ALTER DATABASE RENAME FILE '+DATA1/oralhrsg/datafile/tbs101.483.923151901' TO '+DATA1/oralhrsg/datafile/tbs101.382.923151215' * ERROR at line 1: ORA-01511: error in renaming log/data files ORA-01121: cannot rename database file 64 - file is in use or recovery ORA-01110: data file 64: '+DATA1/oralhrsg/datafile/tbs101.483.923151901' SYS@oraLHRDG2> ! oerr ora 01121 01121, 00000, "cannot rename database file %s - file is in use or recovery" // *Cause: Attempted to use ALTER DATABASE RENAME to rename a // datafile that is online in an open instance or is being recovered. // *Action: Close database in all instances and end all recovery sessions. |
檔案在使用，不能進行重新命名，該庫是RAC庫，我們先關閉DG，啟動到MOUNT狀態後再重新命名：
|
[ZFLHRSDB4:root]:/>srvctl stop db -d oralhrsg [ZFLHRSDB4:root]:/>srvctl start db -d oralhrsg -o mount
SYS@oraLHRDG2> conn / as sysdba Connected.
SYS@oraLHRDG2> ALTER DATABASE RENAME FILE '+DATA1/oralhrsg/datafile/tbs101.483.923151901' TO '+DATA1/oralhrsg/datafile/tbs101.382.923151215';
Database altered.
SYS@oraLHRDG2> ALTER DATABASE DATAFILE 64 ONLINE;
Database altered.<<<<<<<<<---------資料檔案可以ONLINE了
SYS@oraLHRDG2> col name for a50 SYS@oraLHRDG2> col CHECKPOINT_CHANGE# for 9999999999999 SYS@oraLHRDG2> SELECT a.FILE#,a.NAME,a.RECOVER,a.CHECKPOINT_CHANGE#,status FROM v$datafile_header a WHERE A.FILE# IN (1,2,64);
FILE# NAME REC CHECKPOINT_CHANGE# STATUS ---------- -------------------------------------------------- --- ------------------ ------- 1 +DATA1/oralhrsg/datafile/system.358.869055401 15760776695 ONLINE 2 +DATA1/oralhrsg/datafile/sysaux.354.869047985 15760776695 ONLINE 64 +DATA1/oralhrsg/datafile/tbs101.382.923151215 15760492416 ONLINE
SYS@oraLHRDG2> alter database open read only; alter database open read only * ERROR at line 1: ORA-10458: standby database requires recovery ORA-01194: file 64 needs more recovery to be consistent ORA-01110: data file 64: '+DATA1/oralhrsg/datafile/tbs101.382.923151215' <<<<<<<<<------------ 開啟資料庫依然報錯，我們手動恢復一下，看看需要哪些日誌，因為64號檔案已經是最新的了
SYS@oraLHRDG2> recover database; ORA-00283: recovery session canceled due to errors ORA-01610: recovery using the BACKUP CONTROLFILE option must be done
SYS@oraLHRDG2> recover standby database using backup controlfile; ORA-00279: change 15760492416 generated at 09/21/2016 11:38:54 needed for thread 1 ORA-00289: suggestion : /arch/1_12918_868895513.arc ORA-00280: change 15760492416 for thread 1 is in sequence #12918
Specify log: {<RET>=suggested | filename | AUTO | CANCEL} cancel ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below ORA-01194: file 64 needs more recovery to be consistent ORA-01110: data file 64: '+DATA1/oralhrsg/datafile/tbs101.382.923151215'
ORA-01112: media recovery not started
<<<<<<<<<------------ 缺少12918日誌，很欣慰，因為12918已經是最新的日誌了，而不是最初的1K號日誌，這裡解決起來就很簡單了，可以從主庫複製12918日誌到備庫，但是這樣太麻煩，我們可以開啟備庫的應用程式讓其自動解決備庫的gap問題
SYS@oraLHRDG2> alter database recover managed standby database using current logfile disconnect from session;
Database altered. |
此時檢視告警日誌，很欣慰看到了12918日誌過來了：
|
Wed Sep 21 15:24:33 2016 alter database recover managed standby database using current logfile disconnect from session Attempt to start background Managed Standby Recovery process (oraLHRDG2) Wed Sep 21 15:24:33 2016 MRP0 started with pid=44, OS id=12649040 MRP0: Background Managed Standby Recovery process started (oraLHRDG2) started logmerger process Wed Sep 21 15:24:39 2016 Managed Standby Recovery starting Real Time Apply Parallel Media Recovery started with 16 slaves Waiting for all non-current ORLs to be archived... All non-current ORLs have been archived. Wed Sep 21 15:24:40 2016 Media Recovery Log /arch/1_12918_868895513.arc Media Recovery Log /arch/2_12918_868895513.arc Completed: alter database recover managed standby database using current logfile disconnect from session Datafile 64 added to flashback set Media Recovery Log /arch/2_12919_868895513.arc Media Recovery Log /arch/1_12919_868895513.arc Media Recovery Log /arch/2_12920_868895513.arc Media Recovery Log /arch/1_12920_868895513.arc Media Recovery Log /arch/2_12921_868895513.arc Media Recovery Log /arch/1_12921_868895513.arc Media Recovery Waiting for thread 2 sequence 12922 (in transit) Recovery of Online Redo Log: Thread 2 Group 12 Seq 12922 Reading mem 0 Mem# 0: +DATA1/oralhrsg/onlinelog/group_12.353.869055809 Media Recovery Waiting for thread 1 sequence 12922 (in transit) Recovery of Online Redo Log: Thread 1 Group 8 Seq 12922 Reading mem 0 Mem# 0: +DATA1/oralhrsg/onlinelog/group_8.344.869055791 |
最後我們重啟備庫的2個節點：
|
[ZFLHRSDB4:root]:/>srvctl stop db -d oralhrsg [ZFLHRSDB4:root]:/>srvctl start db -d oralhrsg [ZFLHRSDB4:root]:/> [ZFLHRSDB4:root]:/>srvctl status db -d oralhrsg Instance oraLHRDG1 is running on node zflhrsdb3 Instance oraLHRDG2 is running on node zflhrsdb4 [ZFLHRSDB4:root]:/>crsctl stat res -t -------------------------------------------------------------------------------- NAME TARGET STATE SERVER STATE_DETAILS -------------------------------------------------------------------------------- Local Resources -------------------------------------------------------------------------------- ora.LISTENER.lsnr ONLINE ONLINE zflhrsdb3 ONLINE ONLINE zflhrsdb4 ora.LISTENER_DG.lsnr ONLINE ONLINE zflhrsdb3 ONLINE ONLINE zflhrsdb4 ora.asm ONLINE ONLINE zflhrsdb3 Started ONLINE ONLINE zflhrsdb4 Started ora.gsd OFFLINE OFFLINE zflhrsdb3 OFFLINE OFFLINE zflhrsdb4 ora.net1.network ONLINE ONLINE zflhrsdb3 ONLINE ONLINE zflhrsdb4 ora.ons ONLINE ONLINE zflhrsdb3 ONLINE ONLINE zflhrsdb4 ora.registry.acfs ONLINE ONLINE zflhrsdb3 ONLINE ONLINE zflhrsdb4 -------------------------------------------------------------------------------- Cluster Resources -------------------------------------------------------------------------------- ora.LISTENER_SCAN1.lsnr 1 ONLINE ONLINE zflhrsdb4 ora.cvu 1 ONLINE ONLINE zflhrsdb4 ora.oc4j 1 ONLINE ONLINE zflhrsdb4 ora.oralhrsg.db 1 ONLINE ONLINE zflhrsdb3 Open,Readonly 2 ONLINE ONLINE zflhrsdb4 Open,Readonly ora.scan1.vip 1 ONLINE ONLINE zflhrsdb4 ora.zflhrsdb3.vip 1 ONLINE ONLINE zflhrsdb3 ora.zflhrsdb4.vip 1 ONLINE ONLINE zflhrsdb4 [ZFLHRSDB4:root]:/> |
而資料庫中64號檔案已經正常了：
|
SYS@oraLHRDG2> SELECT a.FILE#,a.NAME,a.RECOVER,a.CHECKPOINT_CHANGE#,status FROM v$datafile_header a WHERE A.FILE# IN (1,2,64);
FILE# NAME REC CHECKPOINT_CHANGE# STATUS ---------- -------------------------------------------------- --- ------------------ ------- 1 +DATA1/oralhrsg/datafile/system.358.869055401 15760815694 ONLINE 2 +DATA1/oralhrsg/datafile/sysaux.354.869047985 15760815694 ONLINE 64 +DATA1/oralhrsg/datafile/tbs101.382.923151215 15760815694 ONLINE
SYS@oraLHRDG2> show parameter standby
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ standby_archive_dest string ?/dbs/arch standby_file_management string MANUAL SYS@oraLHRDG2> ALTER SYSTEM SET standby_file_management='AUTO' SID='*';====>>>>> 別忘記將該引數修改回來
System altered.
SYS@oraLHRDG2> set pagesize 9999 line 9999 SYS@oraLHRDG2> col TS_Name format a30 SYS@oraLHRDG2> WITH WT1 AS 2 (SELECT TS.TABLESPACE_NAME, 3 DF.ALL_BYTES, 4 DECODE(DF.TYPE, 5 'D', 6 NVL(FS.FREESIZ, 0), 7 'T', 8 DF.ALL_BYTES - NVL(FS.FREESIZ, 0)) FREESIZ, 9 DF.MAXSIZ, 10 TS.BLOCK_SIZE, 11 TS.LOGGING, 12 TS.FORCE_LOGGING, 13 TS.CONTENTS, 14 TS.EXTENT_MANAGEMENT, 15 TS.SEGMENT_SPACE_MANAGEMENT, 16 TS.RETENTION, 17 TS.DEF_TAB_COMPRESSION, 18 DF.TS_DF_COUNT, 19 TS.BIGFILE, 20 TS.STATUS 21 FROM DBA_TABLESPACES TS, 22 (SELECT 'D' TYPE, 23 TABLESPACE_NAME, 24 COUNT(*) TS_DF_COUNT, 25 SUM(BYTES) ALL_BYTES, 26 SUM(DECODE(MAXBYTES, 0, BYTES, MAXBYTES)) MAXSIZ 27 FROM DBA_DATA_FILES D 28 GROUP BY TABLESPACE_NAME 29 UNION ALL 30 SELECT 'T', 31 TABLESPACE_NAME, 32 COUNT(*) TS_DF_COUNT, 33 SUM(BYTES) ALL_BYTES, SUM(DECODE(MAXBYTES, 0, BYTES, MAXBYTES)) 34 35 FROM DBA_TEMP_FILES D 36 GROUP BY TABLESPACE_NAME) DF, 37 (SELECT TABLESPACE_NAME, SUM(BYTES) FREESIZ 38 FROM DBA_FREE_SPACE 39 GROUP BY TABLESPACE_NAME 40 UNION ALL 41 SELECT TABLESPACE_NAME, SUM(D.BLOCK_SIZE * A.BLOCKS) BYTES 42 FROM GV$SORT_USAGE A, DBA_TABLESPACES D 43 WHERE A.TABLESPACE = D.TABLESPACE_NAME 44 GROUP BY TABLESPACE_NAME) FS 45 WHERE TS.TABLESPACE_NAME = DF.TABLESPACE_NAME 46 AND TS.TABLESPACE_NAME = FS.TABLESPACE_NAME(+)) 47 SELECT (SELECT A.TS# 48 FROM V$TABLESPACE A 49 WHERE A.NAME = UPPER(T.TABLESPACE_NAME)) TS#, 50 T.TABLESPACE_NAME TS_NAME, 51 ROUND(T.ALL_BYTES / 1024 / 1024) TS_SIZE_M, 52 ROUND(T.FREESIZ / 1024 / 1024) FREE_SIZE_M, 53 ROUND((T.ALL_BYTES - T.FREESIZ) / 1024 / 1024) USED_SIZE_M, 54 ROUND((T.ALL_BYTES - T.FREESIZ) * 100 / T.ALL_BYTES, 3) USED_PER, 55 ROUND(MAXSIZ / 1024 / 1024 / 1024, 3) MAX_SIZE_G, 56 ROUND(DECODE(MAXSIZ, 0, TO_NUMBER(NULL), (T.ALL_BYTES - FREESIZ)) * 100 / 57 MAXSIZ, 58 3) USED_PER_MAX, 59 ROUND(T.BLOCK_SIZE) BLOCK_SIZE, 60 T.LOGGING, 61 T.BIGFILE, 62 T.STATUS, 63 T.TS_DF_COUNT 64 FROM WT1 T 65 UNION ALL 66 SELECT TO_NUMBER('') TS#, 67 'ALL TS:' TS_NAME, 68 ROUND(SUM(T.ALL_BYTES) / 1024 / 1024, 3) TS_SIZE_M, 69 ROUND(SUM(T.FREESIZ) / 1024 / 1024) FREE_SIZE_M, 70 ROUND(SUM(T.ALL_BYTES - T.FREESIZ) / 1024 / 1024) USED_SIZE_M, 71 ROUND(SUM(T.ALL_BYTES - T.FREESIZ) * 100 / SUM(T.ALL_BYTES), 3) USED_PER, 72 ROUND(SUM(MAXSIZ) / 1024 / 1024 / 1024) MAX_SIZE, 73 TO_NUMBER('') "USED,% of MAX Size", 74 TO_NUMBER('') BLOCK_SIZE, 75 '' LOGGING, 76 MAX(T.BIGFILE), 77 MAX(T.STATUS), 78 TO_NUMBER('') TS_DF_COUNT 79 FROM WT1 T 80 ORDER BY TS#;
TS# TS_NAME TS_SIZE_M FREE_SIZE_M USED_SIZE_M USED_PER MAX_SIZE_G USED_PER_MAX BLOCK_SIZE LOGGING BIG STATUS TS_DF_COUNT ---------- ------------------------------ ---------- ----------- ----------- ---------- ---------- ------------ ---------- --------- --- --------- ----------- 0 SYSTEM 4096 2613 1483 36.214 4 36.214 8192 LOGGING NO ONLINE 1 1 SYSAUX 4096 1845 2251 54.955 4 54.955 8192 LOGGING NO ONLINE 1 2 UNDOTBS1 122880 117985 4895 3.983 120 3.983 8192 LOGGING NO ONLINE 4 3 TEMP 4096 4093 3 .073 4 .073 8192 NOLOGGING NO ONLINE 1 4 UNDOTBS2 122880 117544 5336 4.342 120 4.342 8192 LOGGING NO ONLINE 4 5 USERS 4096 4083 13 .308 4 .308 8192 LOGGING NO ONLINE 1 6 IPPS_DATA 1544192 358905 1185287 76.758 1508 76.758 8192 LOGGING NO ONLINE 52 7 IPPS_INDEX 512000 495662 16338 3.191 500 3.191 8192 LOGGING NO ONLINE 17 9 TBS001 100 99 1 1.063 .098 1.063 8192 LOGGING NO ONLINE 1 10 TBS101 100 89 11 11 .098 11 8192 LOGGING NO ONLINE 1 ALL TS: 2318536 1102919 1215617 52.43 2264 NO ONLINE
11 rows selected. |
最後不要忘記執行：ALTER SYSTEM SET standby_file_management='AUTO' SID='*';將standby_file_management引數修改為AUTO。
4 環境修復之後的反思
結論：資料檔案OFFLINE之後必須要做的一件事就是緊接著立刻執行一次RECOVER操作（小Y之前告訴我們的~~o(∩_∩)o ~~）。
一個資料檔案OFFLINE為啥修復起來這麼麻煩呢？就是因為歸檔丟失了，但是若是我們剛開始將資料檔案OFFLINE之後若能立刻執行一次RECOVER操作的話，不管中間過了多久，歸檔丟失了多少，最後ONLINE資料檔案的時候都會直接ONLINE起來資料檔案而不用做RECOVER操作。廢話不多說，我們且做個實驗。
|
專案 |
source db |
|
db 型別 |
單例項 |
|
db version |
11.2.0.3.4 |
|
db 儲存 |
ASM |
|
OS版本及kernel版本 |
AIX 64位 7.1.0.0 |
4.1 資料檔案OFFLINE後沒有立刻做RECOVER操作
|
SYS@lhrdb> col name for a60 SYS@lhrdb> select file#,name,status from v$datafile;
FILE# NAME STATUS ---------- ------------------------------------------------------------ ------- 1 +DATA/lhrdb/datafile/system.347.916601927 SYSTEM 2 +DATA/lhrdb/datafile/sysaux.340.916601927 ONLINE 3 +DATA/lhrdb/datafile/undotbs1.353.916601927 ONLINE 4 +DATA/lhrdb/datafile/users.445.916601927 ONLINE 5 +DATA/lhrdb/datafile/example.416.916602001 ONLINE 6 +DATA/lhrdb/datafile/ts_mig_chain_lhr.471.919677645 ONLINE
6 rows selected.
SYS@lhrdb> alter database datafile 6 offline; alter database datafile 6 offline * ERROR at line 1: ORA-01145: offline immediate disallowed unless media recovery enabled ====>>>>> 資料庫必須歸檔才可以OFFLINE
SYS@lhrdb> archive log list; Database log mode No Archive Mode Automatic archival Disabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 265 Current log sequence 267 SYS@lhrdb> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SYS@lhrdb> startup mount ORACLE instance started.
Total System Global Area 1720328192 bytes Fixed Size 2247072 bytes Variable Size 486540896 bytes Database Buffers 1224736768 bytes Redo Buffers 6803456 bytes Database mounted. SYS@lhrdb> alter database archivelog;
Database altered.
SYS@lhrdb> alter database open;
Database altered.
SYS@lhrdb> alter database datafile 6 offline;
Database altered.
SYS@lhrdb> select file#,name,status from v$datafile;
FILE# NAME STATUS ---------- ------------------------------------------------------------ ------- 1 +DATA/lhrdb/datafile/system.347.916601927 SYSTEM 2 +DATA/lhrdb/datafile/sysaux.340.916601927 ONLINE 3 +DATA/lhrdb/datafile/undotbs1.353.916601927 ONLINE 4 +DATA/lhrdb/datafile/users.445.916601927 ONLINE 5 +DATA/lhrdb/datafile/example.416.916602001 ONLINE 6 +DATA/lhrdb/datafile/ts_mig_chain_lhr.471.919677645 RECOVER
6 rows selected.
SYS@lhrdb> select file#,online_status,change#,ERROR from v$recover_file;
FILE# ONLINE_ CHANGE# ERROR ---------- ------- ---------- ----------------------------------------------------------------- 6 OFFLINE 7485831
SYS@lhrdb> alter system switch logfile;
System altered.
SYS@lhrdb> alter system switch logfile;
System altered.
SYS@lhrdb> alter database datafile 6 online; alter database datafile 6 online * ERROR at line 1: ORA-01113: file 6 needs media recovery ORA-01110: data file 6: '+DATA/lhrdb/datafile/ts_mig_chain_lhr.471.919677645'
SYS@lhrdb> recover datafile 6; Media recovery complete. SYS@lhrdb> alter database datafile 6 online;
Database altered.
<<<<<<<<<----- 可以看到6號檔案必須先執行recover操作後才能執行ONLINE |
4.2 資料檔案OFFLINE後立刻做一次RECOVER操作
|
SYS@lhrdb> alter database datafile 6 offline;
Database altered.
SYS@lhrdb> recover datafile 6;<<<<<<<<<----- OFFLINE後接著執行recover操作 Media recovery complete. SYS@lhrdb> select file#,online_status,change#,ERROR from v$recover_file;<<<<<<<<<--該檢視查不到資料
no rows selected
SYS@lhrdb> select file#,name,status from v$datafile;
FILE# NAME STATUS ---------- ------------------------------------------------------------ ------- 1 +DATA/lhrdb/datafile/system.347.916601927 SYSTEM 2 +DATA/lhrdb/datafile/sysaux.340.916601927 ONLINE 3 +DATA/lhrdb/datafile/undotbs1.353.916601927 ONLINE 4 +DATA/lhrdb/datafile/users.445.916601927 ONLINE 5 +DATA/lhrdb/datafile/example.416.916602001 ONLINE 6 +DATA/lhrdb/datafile/ts_mig_chain_lhr.471.919677645 OFFLINE
6 rows selected.
SYS@lhrdb> alter system switch logfile;
System altered.
SYS@lhrdb> alter system switch logfile;
System altered.
SYS@lhrdb> alter system switch logfile;
System altered.
SYS@lhrdb> alter database datafile 6 online;<<<<<<<<<-----切換日誌後讓資料檔案做ONLINE操作並不需要執行RECOVER操作
Database altered.
SYS@lhrdb> select file#,name,status from v$datafile;
FILE# NAME STATUS ---------- ------------------------------------------------------------ ------- 1 +DATA/lhrdb/datafile/system.347.916601927 SYSTEM 2 +DATA/lhrdb/datafile/sysaux.340.916601927 ONLINE 3 +DATA/lhrdb/datafile/undotbs1.353.916601927 ONLINE 4 +DATA/lhrdb/datafile/users.445.916601927 ONLINE 5 +DATA/lhrdb/datafile/example.416.916602001 ONLINE 6 +DATA/lhrdb/datafile/ts_mig_chain_lhr.471.919677645 ONLINE
6 rows selected.
SYS@lhrdb> |
實驗結束，所以得養成習慣，若做了資料檔案的OFFLINE操作後需要接著執行一次RECOVER操作，這樣以後想啥時候ONLINE就啥時候ONLINE了。
5 總結
1、有關BBED的一些理論知識參考：http://blog.itpub.net/26736162/viewspace-2079337/
2、資料檔案做OFFLINE後需接著執行一次RECOVER操作
3、最後不要忘記執行：ALTER SYSTEM SET standby_file_management='AUTO' SID='*';將standby_file_management引數修改為AUTO
4、該故障過程可以進行模擬實驗，讀者可以在自己的測試環境或虛擬機器環境進行實驗，實驗操作很重要
About Me
|
............................................................................................................................... ● 本文作者：小麥苗，只專注於資料庫的技術，更注重技術的運用 ● 本文在itpub（http://blog.itpub.net/26736162）、部落格園(http://www.cnblogs.com/lhrbest)和個人微信公眾號（）上有同步更新 ● 本文itpub地址：http://blog.itpub.net/26736162/viewspace-2125336/ ● 本文部落格園地址：http://www.cnblogs.com/lhrbest/articles/5897530.html ● 本文pdf版： （提取碼：ed9b） ● 小麥苗雲盤地址：http://blog.itpub.net/26736162/viewspace-1624453/ ● QQ群：230161599 微信群：私聊 ● 聯絡我請加QQ好友(642808185)，註明新增緣由 ● 於 2016-09-20 10:00 ~ 2016-09-22 19:00 在中行完成 ● 文章內容來源於小麥苗的學習筆記，部分整理自網路，若有侵權或不當之處還請諒解！ ● 【版權所有，文章允許轉載，但須以連結方式註明源地址，否則追究法律責任】 ............................................................................................................................... 手機長按下圖識別二維碼或微信客戶端掃描下邊的二維碼來關注小麥苗的微信公眾號：xiaomaimiaolhr,免費學習最實用的資料庫技術。
|
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26736162/viewspace-2125336/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 【BBED】丟失歸檔檔案情況下的恢復
- oracle dg 歸檔日誌恢復情況Oracle
- DATA GUARD主庫丟失資料檔案的恢復(3)
- DATA GUARD主庫丟失資料檔案的恢復(1)
- DATA GUARD主庫丟失資料檔案的恢復(2)
- 測試在丟失歸檔日誌的情況下，跳過部分歸檔日誌進行資料恢復資料恢復
- dg丟失歸檔，使用rman增量備份恢復
- Sql Server資料庫檔案丟失的恢復方法SQLServer資料庫
- 生產環境故障處理演練-mysql資料庫主從恢復MySql資料庫
- 電腦檔案丟失資料恢復資料恢復
- 【資料庫資料恢復】Sql Server資料庫檔案丟失的資料恢復過程資料庫資料恢復SQLServer
- MongoDB資料庫報錯，資料庫檔案丟失資料恢復案例MongoDB資料庫資料恢復
- 【資料庫資料恢復】mdb_catalog.wt檔案丟失的MongoDB資料恢復案例資料庫資料恢復MongoDB
- 資料庫資料恢復—MongoDB資料庫檔案丟失，啟動報錯的資料恢復案例資料庫資料恢復MongoDB
- 剪下的檔案還能恢復嗎，恢復剪貼丟失的檔案
- OGG整合抽取模式丟失歸檔處理模式
- DG歸檔日誌缺失恢復
- 【伺服器資料恢復】xfs檔案系統資料丟失的資料恢復案例伺服器資料恢復
- 【北亞資料恢復】MongoDB資料遷移檔案丟失的MongoDB資料恢復案例資料恢復MongoDB
- macOS Big Sur系統如何恢復丟失的資料檔案？Mac
- 【資料庫資料恢復】LINUX EXT3檔案系統下ORACLE資料庫誤操作導致資料丟失的資料恢復案例資料庫資料恢復LinuxOracle
- 丟失的隨身碟檔案如何恢復？
- Oracle DataGuard歸檔日誌丟失處理方法Oracle
- Sqlserver系統資料庫和使用者資料庫日誌檔案全部丟失的恢復SQLServer資料庫
- 關於丟失表空間資料檔案的處理方式
- Oracle impdp遷移資料後主鍵丟失故障處理Oracle
- 檔案丟失不用怕：超實用的Mac資料恢復軟體！Mac資料恢復
- Oracle-真實環境的丟失current redo log file的故障恢復Oracle
- 【虛擬機器資料恢復】Hyper-V虛擬化檔案丟失的資料恢復案例虛擬機資料恢復
- 對歸檔模式下CLEAR 未歸檔日誌後恢復資料庫的一點看法模式資料庫
- 【資料庫資料恢復】EXT3檔案系統下MYSQL資料庫恢復案例資料庫資料恢復MySql
- DM7使用DMRAMN對多次故障恢復後使用不同資料庫的歸檔進行恢復資料庫
- MySQL異常恢復之無主鍵情況下innodb資料恢復的方法MySql資料恢復
- 【資料庫資料恢復】MongoDB資料庫檔案損壞的資料恢復案例資料庫資料恢復MongoDB
- 【資料庫資料恢復】透過恢復NDF檔案修復資料庫的資料恢復過程資料庫資料恢復
- 【資料庫資料恢復】LINUX環境下ORACLE資料庫誤刪除的資料恢復資料庫資料恢復LinuxOracle
- 【伺服器資料恢復】SAN LUN對映出錯導致檔案系統資料丟失的資料恢復案例伺服器資料恢復
- Oracle dg歸檔同步失敗Oracle
- oracle dg庫資料檔案建立失敗ORA-01111Oracle