只存在RMAN備份片的資料庫恢復過程

尛樣兒發表於2011-11-24

只存在RMAN備份片的資料庫恢復過程。
一.之前的資料庫狀態。
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL> archive log list;
Database log mode        Archive Mode
Automatic archival        Enabled
Archive destination        USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     1
Next log sequence to archive   3
Current log sequence        3
SQL> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
/dev/raw/raw10
/dev/raw/raw9
/dev/raw/raw8
/dev/raw/raw7

SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/dev/raw/raw1
/dev/raw/raw2
/dev/raw/raw3

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/dev/raw/raw6
/dev/raw/raw5
/dev/raw/raw4

資料檔案、控制檔案和Redo日誌檔案都存放在裸裝置上。

二.手動建立測試資料。

SQL> create user xiaoyang identified by xiaoyang;

User created.

SQL> grant connect,resource to xiaoyang;

Grant succeeded.

SQL> connect xiaoyang/xiaoyang
Connected.
SQL> create table xiaoyang(id number);

Table created.

SQL> insert into xiaoyang values(1);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from xiaoyang.xiaoyang;

 ID
----------
  1

三.手動執行線上RMAN備份:
connected to target database: ORCL (DBID=1295557632)

RMAN> backup database;

Starting backup at 2011-11-21 07:34:07
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/dev/raw/raw7
input datafile fno=00003 name=/dev/raw/raw9
input datafile fno=00002 name=/dev/raw/raw8
input datafile fno=00004 name=/dev/raw/raw10
channel ORA_DISK_1: starting piece 1 at 2011-11-21 07:34:07
channel ORA_DISK_1: finished piece 1 at 2011-11-21 07:34:32
piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2011_11_21/o1_mf_nnndf_TAG20111121T073407_7dm3mhy3_.bkp tag=TAG20111121T073407 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 2011-11-21 07:34:33
channel ORA_DISK_1: finished piece 1 at 2011-11-21 07:34:34
piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2011_11_21/o1_mf_ncsnf_TAG20111121T073407_7dm3n9lv_.bkp tag=TAG20111121T073407 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2011-11-21 07:34:34

四.模擬損壞情況
        刪除整個資料庫,模擬資料庫被破壞,只將之前的RMAN備份集複製到/u02/目錄下,用於恢復目的。
[oracle@rhel2 ~]$ cp /u01/app/oracle/flash_recovery_area/ORCL/backupset/2011_11_21/o1_mf_nnndf_TAG20111121T073407_7dm3mhy3_.bkp /u02/
[oracle@rhel2 ~]$ cp /u01/app/oracle/flash_recovery_area/ORCL/backupset/2011_11_21/o1_mf_ncsnf_TAG20111121T073407_7dm3n9lv_.bkp /u02/
使用dbca刪除orcl資料庫,模擬資料庫丟失。

五.建立一個臨時資料庫環境
        由於RMAN必須工作在MOUNT模式,所有的資料檔案都丟失,無法透過只重建控制檔案將其啟動到MOUNT模式,所以這裡利用dbca建立一個臨時資料庫環境,資料庫的名稱與原有名稱保持不變,檔案存放到預設位置即可。

六.將備份片的後設資料加入到控制檔案中
      使用RMAN的restore命令,RMAN會自己從控制檔案中找資料檔案的後設資料資訊,為了能夠正常的恢復資料檔案,需要提前將備份片後設資料資訊記錄到控制檔案中,這樣RMAN能夠順利的找到備份檔案,備份檔案還保留了資料庫的後設資料資訊,使得之後透過list命令就可以檢視到原有資料庫的後設資料資訊,從而能夠很容易的編寫恢復指令碼。
1.執行以下命令將之前資料庫的備份片加入到臨時資料庫的控制檔案中。
RMAN> catalog backuppiece '/u02/o1_mf_ncsnf_TAG20111121T073407_7dm3n9lv_.bkp';

ORA-19870: error reading backup piece /u02/o1_mf_ncsnf_TAG20111121T073407_7dm3n9lv_.bkp
ORA-19691: /u02/o1_mf_ncsnf_TAG20111121T073407_7dm3n9lv_.bkp is from different database: id=1295557632, name=ORCL
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of catalog command at 11/21/2011 07:51:16
RMAN-06209: List of failed objects
RMAN-06211: ==========================
RMAN-06212:   Object Type   Filename/Handle
RMAN-06213: --------------- ---------------------------------------------------
RMAN-06214: Backup Piece    /u02/o1_mf_ncsnf_TAG20111121T073407_7dm3n9lv_.bkp
        將備份片的資訊加入到控制檔案的時候報錯,原因在於原有資料庫和臨時資料庫的DBID不同,下面首先修改臨時資料庫的DBID,使它與原有資料庫DBID保持一致。
2.由於臨時資料庫的DBID與原有資料庫的DBID不同,導致RMAN無法執行RESTORE操作,所以需要修改臨時資料庫的DBID為原有資料庫的DBID。
當前資料庫DBID:
SQL> select dbid from v$database;

      DBID
----------
1295559190

將1295559190修改為1295557632,執行下面的修改DBID步驟:
SQL> @?/rdbms/admin/dbmsbkrs.sql

Package created.

SQL> select dbid from v$database;

      DBID
----------
1295560714

SQL> exec dbms_backup_restore.nidbegin('orcl','orcl','1295557632','1295560714',0,0,10);

PL/SQL procedure successfully completed.

下面是dbms_backup_restore.nidbegin儲存過程引數的說明:
SQL> desc dbms_backup_restore.nidbegin
Parameter Type           Mode Default?
--------- -------------- ---- --------
NEWDBNAME VARCHAR2       IN           
OLDDBNAME VARCHAR2       IN           
NEWDBID   NUMBER         IN           
OLDDBID   NUMBER         IN           
DOREVERT  BINARY_INTEGER IN           
DORESTART BINARY_INTEGER IN           
EVENTS    NUMBER         IN

SQL> variable a number;
SQL> variable b number;
SQL> variable c number;
SQL> exec dbms_backup_restore.nidprocessdf(0,0,:a,:b,:c);
BEGIN dbms_backup_restore.nidprocessdf(0,0,:a,:b,:c); END;

*
ERROR at line 1:
ORA-00600: internal error code, arguments: [krbnpdf_wrngname_3],
[/u01/app/oracle/oradata/orcl/system01.dbf], [ORCL], [orcl], [], [], [], []
ORA-06512: at "SYS.DBMS_BACKUP_RESTORE", line 6161
ORA-06512: at line 1


SQL> exec dbms_backup_restore.nidprocesscf(:a,:b);

PL/SQL procedure successfully completed.

SQL> exec dbms_backup_restore.nidend;

PL/SQL procedure successfully completed.

SQL> select dbid from v$database;

      DBID
----------
1295557632

        這裡是使用DBMS_BACKUP_RESTORE包來修改指定的DBID,這是一個恢復中常用的包,由dbmsbkrs.sql檔案建立。這裡雖然有報錯,但DBID還是修改成功了。
        對於DBID和DBNAME的修改還可以使用nid工具,這個工具除了可以修改DBID和DBNAME外,還可以對修改錯誤的情況進行恢復。下面是nid的幫助資訊:
[oracle@rhel2 u02]$ nid

DBNEWID: Release 10.2.0.1.0 - Production on Mon Nov 21 09:07:38 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Keyword     Description                    (Default)
----------------------------------------------------
TARGET      Username/Password              (NONE)
DBNAME      New database name              (NONE)
LOGFILE     Output Log                     (NONE)
REVERT      Revert failed change           NO
SETNAME     Set a new database name only   NO
APPEND      Append to output log           NO
HELP        Displays these messages        NO

        例如,執行以下的命令用來恢復錯誤的DBID或DBNAME的修改,nid target=sys/oracle revert=yes
        nid工具執行的命令實際也是呼叫DBMS_BACKUP_RESTORE包相應的儲存過程實現的。

七.再次將備份片的後設資料加入到控制檔案中
RMAN> catalog backuppiece '/u02/o1_mf_nnndf_TAG20111121T073407_7dm3mhy3_.bkp';

cataloged backuppiece
backup piece handle=/u02/o1_mf_nnndf_TAG20111121T073407_7dm3mhy3_.bkp recid=3 stamp=767780955
RMAN-08132: WARNING: cannot update recovery area reclaimable file list
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of REFAF command on default channel at 11/21/2011 08:29:15
ORA-00237: snapshot operation disallowed: control file newly created

RMAN> exit

[oracle@rhel2 ~]$ sqlplus / as sysdba

SQL> shutdown abort
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  369098752 bytes
Fixed Size      2020864 bytes
Variable Size    117443072 bytes
Database Buffers   247463936 bytes
Redo Buffers      2170880 bytes
Database mounted.
SQL> exit

[oracle@rhel2 ~]$ rman target /

connected to target database: ORCL (DBID=1295557632, not open)

RMAN> catalog backuppiece '/u02/o1_mf_nnndf_TAG20111121T073407_7dm3mhy3_.bkp';

using target database control file instead of recovery catalog
cataloged backuppiece
backup piece handle=/u02/o1_mf_nnndf_TAG20111121T073407_7dm3mhy3_.bkp recid=4 stamp=767781072

RMAN> catalog backuppiece '/u02/o1_mf_ncsnf_TAG20111121T073407_7dm3n9lv_.bkp';

cataloged backuppiece
backup piece handle=/u02/o1_mf_ncsnf_TAG20111121T073407_7dm3n9lv_.bkp recid=5 stamp=767781095

RMAN> list backup;


List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time   
------- ---- -- ---------- ----------- ------------ -------------------
1       Full    6.80M      DISK        00:00:00     2011-11-21 07:34:33
        BP Key: 5   Status: AVAILABLE  Compressed: NO  Tag: TAG20111121T073407
        Piece Name: /u02/o1_mf_ncsnf_TAG20111121T073407_7dm3n9lv_.bkp
  Control File Included: Ckp SCN: 535269       Ckp time: 2011-11-21 07:34:33
  SPFILE Included: Modification time: 2011-11-21 07:32:31

BS Key  Type LV Size       Device Type Elapsed Time Completion Time   
------- ---- -- ---------- ----------- ------------ -------------------
2       Full    513.27M    DISK        00:00:00     2011-11-21 07:34:07
        BP Key: 4   Status: AVAILABLE  Compressed: NO  Tag: TAG20111121T073407
        Piece Name: /u02/o1_mf_nnndf_TAG20111121T073407_7dm3mhy3_.bkp
  List of Datafiles in backup set 2
  File LV Type Ckp SCN    Ckp Time            Name
  ---- -- ---- ---------- ------------------- ----
  1       Full 535260     2011-11-21 07:34:07 /u01/app/oracle/oradata/orcl/system01.dbf
  2       Full 535260     2011-11-21 07:34:07 /u01/app/oracle/oradata/orcl/undotbs01.dbf
  3       Full 535260     2011-11-21 07:34:07 /u01/app/oracle/oradata/orcl/sysaux01.dbf
  4       Full 535260     2011-11-21 07:34:07 /u01/app/oracle/oradata/orcl/users01.dbf

八.執行還原操作:
RMAN> restore database ;

Starting restore at 2011-11-21 08:32:45
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 11/21/2011 08:32:46
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 4 found to restore
RMAN-06023: no backup or copy of datafile 3 found to restore
RMAN-06023: no backup or copy of datafile 2 found to restore
RMAN-06023: no backup or copy of datafile 1 found to restore

        雖然能用list backup命令看到相關資料檔案的後設資料資訊,但是執行restore database卻無法找到相應資料檔案的備份,這時可以用到強大的DBMS_BACKUP_RESTORE包執行恢復操作,執行如下PL/SQL命令:
SQL> DECLARE
 devtype varchar2(256);
 done boolean;
 BEGIN
 devtype:=sys.dbms_backup_restore.deviceAllocate (type=>'',ident=>'t1');
 sys.dbms_backup_restore.restoreSetDatafile;
 sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>01,toname=>'/u02/system01.dbf');
 sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>02,toname=>'/u02/sysaux01.dbf');
 sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>03,toname=>'/u02/users01.dbf');
 sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>04,toname=>'/u02/undotbs01.dbf');
 sys.dbms_backup_restore.restoreBackupPiece(done=>done,handle=>'/u02/o1_mf_nnndf_TAG20111121T073407_7dm3mhy3_.bkp', params=>null);
 sys.dbms_backup_restore.deviceDeallocate;
END;

PL/SQL procedure successfully completed.

相應的資料檔案已經在/u02目錄下restore成功:
[oracle@rhel2 ~]$ cd /u02
[oracle@rhel2 u02]$ ll
total 1301912
-rw-r----- 1 oracle oinstall   7143424 Nov 21 07:36 o1_mf_ncsnf_TAG20111121T073407_7dm3n9lv_.bkp
-rw-r----- 1 oracle oinstall 538214400 Nov 21 07:36 o1_mf_nnndf_TAG20111121T073407_7dm3mhy3_.bkp
-rw-r----- 1 oracle oinstall  26222592 Nov 21 08:35 sysaux01.dbf
-rw-r----- 1 oracle oinstall 503324672 Nov 21 08:36 system01.dbf
-rw-r----- 1 oracle oinstall   5251072 Nov 21 08:35 undotbs01.dbf
-rw-r----- 1 oracle oinstall 251666432 Nov 21 08:36 users01.dbf

        在資料檔案全部丟失的情況下,如果使用RMAN工具不能從備份集中還原資料庫,就只能使用dbms_backup_restore包來完成還原資料庫的工作,因為sqlplus工具是沒有restore命令的。另外,在還原的過程中還需要注意資料庫的生命週期(list incarnation
)。

九.重建控制檔案:
SQL> alter database backup controlfile to trace;

Database altered.

SQL> !
[oracle@rhel2 u02]$ cd $ORACLE_BASE/admin/orcl/udump
[oracle@rhel2 udump]$ ls -altr *| tail -1
-rw-r----- 1 oracle oinstall    6707 Nov 21 08:37 orcl_ora_9711.trc

重啟資料庫到NOMOUNT狀態,執行以下控制檔案建立命令:

SQL> shutdown abort 
ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.

Total System Global Area  369098752 bytes
Fixed Size      2020864 bytes
Variable Size    117443072 bytes
Database Buffers   247463936 bytes
Redo Buffers      2170880 bytes

從orcl_ora_9711.trc檔案中獲取控制檔案的建立命令:
SQL> CREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u02/redo01.log'  SIZE 50M,
  GROUP 2 '/u02/redo02.log'  SIZE 50M,
  GROUP 3 '/u02/redo03.log'  SIZE 50M
-- STANDBY LOGFILE
DATAFILE
  '/u02/system01.dbf',
  '/u02/undotbs01.dbf',
  '/u02/sysaux01.dbf',
  '/u02/users01.dbf'
CHARACTER SET ZHS16GBK
;

Control file created.

        如果控制檔案重建之後還需要執行恢復操作,請參考文章:
http://space.itpub.net/23135684/viewspace-721996

十.開啟資料庫,測試恢復的資料庫:
SQL> alter database open resetlogs;

Database altered.

SQL> select * from xiaoyang.xiaoyang;

 ID
----------
  1

十一.指定臨時表空間
        由於RMAN恢復是不會恢復臨時表空間(查詢dba_temp_files為空),所以需要手動重新建立臨時表空間,並制定為預設的臨時表空間。執行以下的命令建立臨時表空間和制定為資料庫預設的臨時表空間:
SQL> create temporary tablespace TEMP01 tempfile  '/u02/temp01.dbf' size 100m autoextend on next 10m;

Tablespace created.

SQL> alter database default temporary tablespace temp01;

Database altered.

        至此利用僅存的備份集恢復了資料庫。如果資料庫備份的時候不一致,那麼可能不能直接開啟,可以考慮強制開啟,然後將資料匯出的辦法來恢復資料。

        這裡主要討論的是一種思路,具體命令執行返回的結果會因不同的環境、不同的情況會有所差別,實踐中可能會遇到更多的問題,需要根據具體的環境和情況來試驗。

參考文章:



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

相關文章