【故障處理】DG環境主庫丟失歸檔情況下資料檔案的恢復

lhrbest發表於2016-09-22

【故障處理】DG環境主庫丟失歸檔情況下資料檔案的恢復

1  BLOG文件結構圖

wps50E2.tmp 

 

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

 

【推薦】 【BBED】 SYSTEM檔案頭損壞的恢復(4)

http://blog.itpub.net/26736162/viewspace-2084329/

【推薦】 【BBED】 sys.bootstrap$ 物件的恢復

http://blog.itpub.net/26736162/viewspace-2083621/

【推薦】 【BBED】丟失歸檔檔案情況下的恢復

http://blog.itpub.net/26736162/viewspace-2079337/

【推薦】 【BBED】編譯及基本命令(1)

http://blog.itpub.net/26736162/viewspace-2075216/

【BBED】bbed常用命令

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#;

 結果如下圖：

wps50E3.tmp

因為表空間是ONLINE的，若是OFFLINE的話，結果自然為空，由於只有一個資料檔案，那就看看資料檔案的狀態：

SELECT * FROM v$datafile d WHERE d.FILE#=64;

wps50E4.tmp 

果然資料檔案是64，資料檔案為OFFLINE狀態，而且去備庫檢視的時候資料檔案也是OFFLINE的。這裡有一個LAST_TIME需要注意，日誌為2015年4月21號，而現在都2016年9月21號了，看來是很久很久很久沒有用這個資料檔案了。好吧，很久沒有寫BLOG了，今天就以這個案例為主，說說其修復過程把。

3.2.1  健康檢查報告

一、 執行

用自己的健康檢查報告看一下能否發現這個問題呢？

wps50F5.tmp 

wps50F6.tmp 

跑完之後，生成的報告在當前目錄，報告的目錄大概如下所示：

巡檢服務概要

資料庫總體概況

資料庫基本資訊

資料庫大小

資源使用情況

元件和特性

庫

引數檔案

所有的初始化引數

關鍵的初始化引數

隱含引數

spfile檔案內容

Statistics Level

表空間情況

表空間狀況資訊

閃回空間使用情況

臨時表空間使用情況

Undo表空間使用情況

表空間擴充套件狀況

資料檔案狀況

控制檔案

ASM磁碟監控

ASM磁碟使用情況

ASM磁碟組使用情況

ASM磁碟組引數配置情況

ASM例項

JOB情況

作業執行狀況

資料庫job報錯資訊

 

巡檢服務明細

RMAN資訊

RMAN備份狀況

RMAN配置情況

RMAN所有備份

RMAN所有備份詳情

控制檔案備份

spfile檔案備份

RMAN歸檔檔案備份

資料庫閃回

歸檔資訊

歸檔日誌設定

歸檔日誌生成情況

歸檔日誌佔用率

近7天日誌切換頻率分析

每天日誌切換的量

日誌組大小

SGA資訊

SGA使用情況

SGA配置資訊

SGA建議配置

SGA動態元件

PGA TARGET 建議配置

檔案IO資訊

檔案IO分析

檔案IO時間分析

全表掃描情況

排序情況

SQL監控

邏輯讀TOP10的SQL

物理讀TOP10的SQL

執行時間TOP10的SQL

執行次數TOP10的SQL

解析次數TOP10的SQL

版本TOP10的SQL語句

記憶體TOP10的SQL語句

DISK_SORT嚴重的SQL

垃圾SQL之RUNNING_11G

垃圾SQL之RUNNING_10G

LAST快照中SQL情況

LAST快照中執行時間最長SQL

執行時間最長SQL

執行時間最長的SQL報告

閃迴歸檔

閃迴歸檔配置

開啟了閃迴歸檔的表

閃迴歸檔空間

DG庫

DG庫配置情況

DG庫執行情況

主庫DG程式

主庫standby日誌

備庫日誌應用情況

 

資料庫安全

資料庫使用者

資料庫使用者一覽

擁有DBA角色的使用者

擁有SYS角色的使用者

角色概況

密碼為系統預設值的使用者

整個使用者有多大

近一週登入錯誤的使用者

系統表空間使用者

SYSTEM為預設表空間的使用者

SYSTEM為臨時表空間的使用者

系統表空間上的物件

資料庫審計

審計引數配置

審計表情況

DB中所有審計記錄

 

資料庫物件

段情況

物件彙總

段的彙總

體積最大的10個段

擴充套件最多的10個段

LOB段

不能擴充套件的物件

擴充套件超過1/2最大擴充套件度的物件

Undo 段

表空間所有者

表情況

行連結或行遷移的表

超過10W行無主鍵的表

無資料有高水位的表

分割槽表情況

表大小超過10GB未建分割槽

分割槽最多的前10個物件

分割槽個數超過100個的表

無效物件

無效的物件

無效的普通索引

無效的分割槽索引

無效的觸發器

索引情況

索引個數超過5個的表

大表未建索引

組合索引與單列索引存在交叉

點陣圖索引和函式索引

外來鍵未建索引

大索引從未使用

索引列個數大於3

索引高度大於3

索引的統計資訊過舊

並行度

錶帶有並行度

索引帶有並行度

其他物件

告警日誌

資料庫目錄

回收站情況

資料庫鏈路(db_link)

外部表

所有的觸發器

序列cache小於20

物化檢視

type

資料泵

 

資料庫效能分析

AWR

AWR統計

AWR引數配置狀況

資料庫伺服器主機的情況

AWR檢視中的load profile

熱塊

最新的一次AWR報告

ASH

ASH快照狀況

最新的一次ASH報告

ADDM

最新的一次ADDM

統計資訊

統計資訊是否自動收集

需收集統計資訊的表

被收集統計資訊的臨時表

會話

會話概況

會話狀態一覽(當前)

歷史ACTIVE會話數

登入時間最長的10個會話

超過10小時無響應的會話

提交次數最多的會話

CPU或等待最長的會話

鎖

檢視LOCK鎖情況

檢視誰鎖住了誰

遊標使用情況

並行程式完成情況

記憶體佔用

查詢共享記憶體佔有率

PGA佔用最多的程式

命中率

其它

等待事件

OLAP

Networking

Replication

 

健康檢查結果

健康檢查結果

健康檢查結果

健康檢查過程中指令碼產生的錯誤

二、 概況

先看看資料庫的概況:

wps5116.tmp 

三、 1級告警：資料檔案OFFLINE

再看看，健康檢查的結果：

wps5117.tmp 

有2個地方很重要，1個資料檔案有OFFLINE的，第二個是序列的CACHE值小於20，並且已經有enq: SQ - contention等待事件的發生了，說明比較嚴重，應該修改其cache值。我們點選到相應的位置可以檢視細節。

可以看到是64號檔案是OFFLINE狀態的。

wps5118.tmp 

四、 2級告警：序列問題

另外，我們看看報告中提到的序列等待問題，可以看到有6個序列的cache值設定有問題，已經導致了會話阻塞了，這部分的cache值強烈建議修改，修改語句在報告中也已經給出。

wps5119.tmp 

五、 2級告警：告警日誌問題

wps512A.tmp 

告警日誌問題不是很大，可以忽略。

六、 4級告警：無效物件

wps512B.tmp 

無效物件也可以修改一下，報告中提供了具體的指令碼。

好了，報告不多看了，今天的主題是如何修復那個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#;

wps512C.tmp 

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; 

wps512D.tmp 

 

那目前是資料檔案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的相關的目錄：

wps513D.tmp 

wps513E.tmp 

注意：檔案我已上傳到雲盤，可以去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=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.

2、 修復檔案頭的scn號

編譯完成後可以使用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

3、 修復資料檔案頭的序列號

要想跳過歸檔還需要資料檔案頭塊的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);

 

wps514F.tmp 

====>>>>>從截圖可以看出雖然是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的大小了：

wps5150.tmp 

接下來建立一個表，看看錶空間是否正常：

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>

再次檢視錶空間大小：

wps5151.tmp 

表空間佔用從原來的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)和個人微信公眾號（xiaomaimiaolhr）上有同步更新

● 本文itpub地址：http://blog.itpub.net/26736162/viewspace-2125336/

● 本文部落格園地址：http://www.cnblogs.com/lhrbest/articles/5897530.html

● 本文pdf版：http://yunpan.cn/cdEQedhCs2kFz （提取碼：ed9b）

● 小麥苗雲盤地址：http://blog.itpub.net/26736162/viewspace-1624453/

● QQ群：230161599     微信群：私聊

● 聯絡我請加QQ好友(642808185)，註明新增緣由

● 於 2016-09-20 10:00 ~ 2016-09-22 19:00 在中行完成

● 文章內容來源於小麥苗的學習筆記，部分整理自網路，若有侵權或不當之處還請諒解！

● 【版權所有，文章允許轉載，但須以連結方式註明源地址，否則追究法律責任】

...............................................................................................................................

手機長按下圖識別二維碼或微信客戶端掃描下邊的二維碼來關注小麥苗的微信公眾號：xiaomaimiaolhr,免費學習最實用的資料庫技術。

wps5162.tmp


來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26736162/viewspace-2125336/，如需轉載，請註明出處，否則將追究法律責任。

相關文章