Oracle 資料庫恢複案例

 一、 資料庫伺服器基本情況
   OS：RHEL 3， Oracle Database 10g Enterprise Edition Release 10.1.0.3.0

二、 備份方式
    資料庫以archive模式運行，RMAN多級增量備份。策略如下：
    設定控制檔案自動備份。
    每三個月做一個資料庫的全備份（包括所有得資料庫和唯讀資料表空間）,並備份歸檔日誌。
    每一個月做一次零級備份（不包含唯讀資料表空間），並備份歸檔日誌。
    每周做一次一級備份，並備份歸檔日誌。
    每天做一次二級備份，並備份歸檔日誌。

三、 恢複案例
   所有恢複的前提：已經做過資料庫全備份（包括歸檔日誌），控制檔案和spfile自動備份。

   1． 損壞一個資料檔案
   （1）故障類比
        刪除資料檔案：rm /u02/oradata/dbnms/users01.dbf
        關閉資料庫：shutdown immediate;
        ORA-01116: error in opening database file 4
        ORA-01110: data file 4: '/u02/oradata/dbnms/users01.dbf'
        ORA-27041: unable to open file
        Linux Error: 2: No such file or directory
        Additional information: 3
        強行關閉：sutdown abort;
        啟動資料庫：startup;
        ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
        ORA-01110: data file 4: '/u02/oradata/dbnms/users01.dbf'
   （2）恢複步驟
       rman target sys@dbnms catalog rmanuser@cata
        run{
            allocate channel c1 type disk;
            restore datafile 4;
            recover datafile 4;
            sql 'alter database datafile 4 online';
            sql 'alter database open';
            release channel c1;
            }

        sqlplus sys as sysdba
        select instance_name,status from v$instance;
        INSTANCE_NAME STATUS
        ---------------- ------------
        dbnms OPEN

    恢複成功

   2． 損壞全部資料檔案
   （1）故障類比
       刪除資料檔案：rm /u02/oradata/dbnms/*.dbf
        強行關閉：sutdown abort;
        啟動資料庫：startup;
        ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
        ORA-01110: data file 1: '/u02/oradata/dbnms/system01.dbf'

   （2）恢複步驟
       rman target sys@dbnms catalog rmanuser@cata
        run{
            allocate channel c1 type disk;
            restore database;
            recover database;
            sql 'alter database open';
            release channel c1;
            }

        sqlplus sys as sysdba
        select instance_name,status from v$instance;
        INSTANCE_NAME STATUS
        ---------------- ------------
        dbnms OPEN

        恢複臨時檔案：
        alter database tempfile '/u02/oradata/dbnms/temp01.dbf' drop;
        alter tablespace temp add tempfile '/u02/oradata/dbnms/temp01.dbf' size 50M autoextend on next 5M maxsize unlimited;

    恢複成功

   3． 損壞非當前聯機日誌成員
   （1）故障類比
       刪除記錄檔：rm /u02/oradata/dbnms/redo01.log
        關閉資料庫：shutdown immediate;
        啟動資料庫：startup;
        select * from v$logfile;

        GROUP# STATUS TYPE MEMBER IS_
        ---------- ------- ------- ------------------------------ ---
        3 ONLINE /u02/oradata/dbnms/redo03.log NO
        2 STALE ONLINE /u02/oradata/dbnms/redo02.log NO
        1 INVALID ONLINE /u02/oradata/dbnms/redo01.log NO
        1 STALE ONLINE /u02/oradata/dbnms/redo11.log NO
        1 STALE ONLINE /u02/oradata/dbnms/redo21.log NO
        2 STALE ONLINE /u02/oradata/dbnms/redo12.log NO
        3 ONLINE /u02/oradata/dbnms/redo13.log NO
        2 STALE ONLINE /u02/oradata/dbnms/redo22.log NO
        3 ONLINE /u02/oradata/dbnms/redo23.log NO
        4 ONLINE /u02/oradata/dbnms/redo31.log NO
        4 ONLINE /u02/oradata/dbnms/redo32.log NO

        GROUP# STATUS TYPE MEMBER IS_
        ---------- ------- ------- ------------------------------ ---
        4 ONLINE /u02/oradata/dbnms/redo33.log NO

   （2）恢複步驟
        alter database drop logfile member '/u02/oradata/dbnms/redo01.log';
        alter database add logfile member '/u02/oradata/dbnms/redo01.log' to group 1;

    恢複成功

   4． 損壞非當前聯機日誌組
   （1）故障類比
        刪除記錄檔組1的所有檔案：
        rm /u02/oradata/dbnms/redo01.log
        rm /u02/oradata/dbnms/redo11.log
        rm /u02/oradata/dbnms/redo21.log
        關閉資料庫：shutdown immediate;
        啟動資料庫：startup;
        Database mounted.
        ORA-00313: open failed for members of log group 1 of thread 1
        ORA-00312: online log 1 thread 1: '/u02/oradata/dbnms/redo11.log'
        ORA-00312: online log 1 thread 1: '/u02/oradata/dbnms/redo21.log'
        ORA-00312: online log 1 thread 1: '/u02/oradata/dbnms/redo01.log'

   （2）恢複步驟
        alter database clear logfile group 1;
        如果該日誌組還沒有歸檔，則用：
        alter database clear unarchived logfile group 1;
        開啟資料庫：
        alter database open;

    恢複成功

   5． 損壞全部聯機日誌
   （1）故障類比
        刪除記錄檔：rm /u02/oradata/dbnms/*.log
        關閉資料庫：shutdown immediate;
        啟動資料庫：startup;
        Database mounted.
        ORA-00313: open failed for members of log group 1 of thread 1
        ORA-00312: online log 1 thread 1: '/u02/oradata/dbnms/redo11.log'
        ORA-00312: online log 1 thread 1: '/u02/oradata/dbnms/redo21.log'
        ORA-00312: online log 1 thread 1: '/u02/oradata/dbnms/redo01.log'

   （2）恢複步驟
        shutdown immediate;
        create pfile from spfile;
        cd $ORACLE_HOME/dbs
        vi initdbnms.ora
        加一個參數：_allow_resetlogs_corruption=true
        create spfile from pfile;
        startup;
        Database mounted.
        ORA-00313: open failed for members of log group 1 of thread 1
        ORA-00312: online log 1 thread 1: '/u02/oradata/dbnms/redo11.log'
        ORA-00312: online log 1 thread 1: '/u02/oradata/dbnms/redo21.log'
        ORA-00312: online log 1 thread 1: '/u02/oradata/dbnms/redo01.log'
        recover database until cancel;
        alter database open resetlogs;
        同步catalog：
        rman target sys@dbnms catalog rmanuser@cata
        reset database;

        new incarnation of database registered in recovery catalog
        starting full resync of recovery catalog
        full resync complete

        做一個full備份：
        /home/oracle/dbbat/backup_full.sh

    恢複成功

   6． 損壞一個控制檔案
   （1）故障類比
        刪除控制檔案：rm /u02/oradata/dbnms/control01.ctl
        關閉資料庫：shutdown immediate;
        ORA-00210: cannot open the specified controlfile
        ORA-00202: controlfile: '/u02/oradata/dbnms/control01.ctl'
        ORA-27041: unable to open file
        Linux Error: 2: No such file or directory
        Additional information: 3

   （2）恢複步驟
        拷貝一個好的控制檔案：
        cp control02.ctl control01.ctl
        shutdown immediate;
        startup;
        Database mounted.
        ORA-01122: database file 1 failed verification check
        ORA-01110: data file 1: '/u02/oradata/dbnms/system01.dbf'
        ORA-01207: file is more recent than controlfile - old controlfile

        rman target sys@dbnms catalog rmanuser@cata
        run{
            allocate channel c1 type disk;
            restore database;
            recover database;
            sql 'alter database open';
            release channel c1;
            }
        RMAN-00571: ===========================================================
        RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
        RMAN-00571: ===========================================================
        RMAN-03009: failure of sql command on default channel at 09/08/2005 17:29:04
        RMAN-11003: failure during parse/execution of SQL statement: alter database open
        ORA-00322: log 3 of thread 1 is not current copy
        ORA-00312: online log 3 thread 1: '/u02/oradata/dbnms/redo03.log'
        ORA-00312: online log 3 thread 1: '/u02/oradata/dbnms/redo13.log'
        ORA-00312: online log 3 thread 1: '/u02/oradata/dbnms/redo23.log'

        alter database clear unarchived logfile group 3;
        alter database open;

    恢複成功

   7． 損壞全部控制檔案
   （1）故障類比
        刪除控制檔案：rm /u02/oradata/dbnms/control01.ctl
        關閉資料庫：shutdown immediate;
        ORA-00210: cannot open the specified controlfile
        ORA-00202: controlfile: '/u02/oradata/dbnms/control01.ctl'
        ORA-27041: unable to open file
        Linux Error: 2: No such file or directory
        Additional information: 3

   （2）恢複步驟
        shutdown abort;
        startup nomount;

        rman target sys catalog rmanuser@cata
        run{
            allocate channel c1 type disk;
            restore controlfile;
            restore database;
            sql 'alter database mount';
            recover database;
            sql 'alter database open resetlogs';
            release channel c1;
            }

        RMAN-00571: ===========================================================
        RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
        RMAN-00571: ===========================================================
        RMAN-03002: failure of recover command at 09/08/2005 17:43:31
        RMAN-06054: media recovery requesting unknown log: thread 1 seq 9 lowscn 670233

        alter database open resetlogs;

        做一個full備份：
        /home/oracle/dbbat/backup_full.sh

    恢複成功

   8． 損壞臨時資料檔案
   （1）故障類比
        刪除臨時資料檔案：rm /u02/oradata/dbnms/temp01.dbf
        關閉資料庫：shutdown immediate;
        啟動資料庫：startup;

   （2）恢複步驟
        alter database tempfile '/u02/oradata/dbnms/temp01.dbf' drop;
        ERROR at line 1:
        ORA-01516: nonexistent log file, datafile, or tempfile
        "/u02/oradata/dbnms/temp01.dbf"

        alter tablespace temp add tempfile '/u02/oradata/dbnms/temp01.dbf' size 50M autoextend on next 5M maxsize unlimited;

    恢複成功

   9． 損壞spfile參數檔案
   （1）故障類比
        刪除spfile檔案：rm $ORACLE_HOME/dbs/spfiledbnms.ora
        關閉資料庫：shutdown immediate;
        啟動資料庫：startup;

   （2）恢複步驟
        startup nomount;

        rman target sys catalog rmanuser@cata
        restore spfile;

        shutdown immediate;
        startup;

    恢複成功

   10． 損壞全部檔案（包括全部資料檔案、控制檔案、臨時資料檔案、聯機記錄檔）
   （1）故障類比
        刪除全部檔案：rm /u02/oradata/dbnms/*
        關閉資料庫：shutdown immediate;
        ORA-03113: end-of-file on communication channel

   （2）恢複步驟
        sqlplus sys as sysdba
        startup nomount;

        rman target sys catalogrmanuser@cata
        還原控制檔案：restore controlfile;
        還原資料庫：restore database;
        mount 資料庫：alter database mount;
        恢複資料庫：recover database;
        RMAN-00571: ===========================================================
        RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
        RMAN-00571: ===========================================================
        RMAN-03002: failure of recover command at 09/09/2005 10:33:13
        RMAN-06054: media recovery requesting unknown log: thread 1 seq 19 lowscn 718284
        用resetlogs方式開啟資料庫：alter database open resetlogs;
        database opened
        new incarnation of database registered in recovery catalog
        starting full resync of recovery catalog
        full resync complete
        重建臨時檔案：
        sqlplus sys as sysdba
        alter database tempfile '/u02/oradata/dbnms/temp01.dbf' drop;
        ERROR at line 1:
        ORA-01516: nonexistent log file, datafile, or tempfile
        "/u02/oradata/dbnms/temp01.dbf"

        alter tablespace temp add tempfile '/u02/oradata/dbnms/temp01.dbf' size 50M autoextend on next 5M maxsize unlimited;

        執行一次全庫備份：/home/oracle/dbbat/backup_full.sh

    恢複成功