rman 全庫恢復asm資料庫

1、做全庫備份

 

注意：rman 可以把asm上的資料檔案備份到檔案系統，也可恢復

 

RMAN> run{
 allocate channel c1 type disk maxpiecesize=500m;
 backup current controlfile format '+dgasm/backup/ctl_%d_%s';
 backup full database format '+dgasm/backup/db_%d_%s_%p_%t';
 sql 'alter system archive log current';
 release channel c1;
 }2> 3> 4> 5> 6> 7>

allocated channel: c1
channel c1: SID=31 device type=DISK

Starting backup at 24-JUN-12
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
including current control file in backup set
channel c1: starting piece 1 at 24-JUN-12
channel c1: finished piece 1 at 24-JUN-12
piece handle=+DGASM/backup/ctl_ora11_19 tag=TAG20120624T172227 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:03
Finished backup at 24-JUN-12

Starting backup at 24-JUN-12
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
input datafile file number=00001 name=+DGASM/ora11/datafile/system.257.785186755
input datafile file number=00002 name=+DGASM/ora11/datafile/sysaux.258.785186845
input datafile file number=00003 name=+DGASM/ora11/datafile/undotbs1.259.785186901
input datafile file number=00004 name=+DGASM/ora11/datafile/users.261.785186921
channel c1: starting piece 1 at 24-JUN-12
channel c1: finished piece 1 at 24-JUN-12
piece handle=+DGASM/backup/db_ora11_20_1_786820952 tag=TAG20120624T172232 comment=NONE
channel c1: starting piece 2 at 24-JUN-12
channel c1: finished piece 2 at 24-JUN-12
piece handle=+DGASM/backup/db_ora11_20_2_786820952 tag=TAG20120624T172232 comment=NONE
channel c1: backup set complete, elapsed time: 00:01:22
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel c1: starting piece 1 at 24-JUN-12
channel c1: finished piece 1 at 24-JUN-12
piece handle=+DGASM/backup/db_ora11_21_1_786821034 tag=TAG20120624T172232 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 24-JUN-12

sql statement: alter system archive log current

released channel: c1

RMAN>

2、shutdown immediate  關閉資料庫，然後刪除user資料檔案，模擬資料檔案損壞

ASMCMD> ls
SYSAUX.258.785186845
SYSTEM.257.785186755
UNDOTBS1.259.785186901
USERS.261.785186921
ASMCMD>
ASMCMD>
ASMCMD> rm -f USERS.261.785186921 
ASMCMD> ls
SYSAUX.258.785186845
SYSTEM.257.785186755
UNDOTBS1.259.785186901
ASMCMD>

3、啟動資料到mount，然後open 出現下面的問題

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '+DGASM/ora11/datafile/users.261.785186921'

4、說明資料檔案出現問題，檢視具體的問題

SQL> select * from v$recover_file
  2  ;

     FILE# ONLINE  ONLINE_ ERROR
---------- ------- ------- -----------------------------------------------------------------
   CHANGE# TIME
---------- ------------
         4 ONLINE  ONLINE  FILE NOT FOUND
         0

FILE NOT FOUND 說明該檔案丟失或者損壞，然後執行全庫恢復

5、檢視備份資訊

RMAN> list backup    
2> ;

using target database control file instead of recovery catalog

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

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1       Full    9.33M      DISK        00:00:04     05-JUN-12     
        BP Key: 1   Status: AVAILABLE  Compressed: NO  Tag: TAG20120605T141421
        Piece Name: /oracle/backup/ctl_ORA11_1
  Control File Included: Ckp SCN: 796843       Ckp time: 05-JUN-12

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2       Full    1005.39M   DISK        00:02:07     05-JUN-12     
  List of Datafiles in backup set 2
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 796853     05-JUN-12 +DGASM/ora11/datafile/system.257.785186755
  2       Full 796853     05-JUN-12 +DGASM/ora11/datafile/sysaux.258.785186845
  3       Full 796853     05-JUN-12 +DGASM/ora11/datafile/undotbs1.259.785186901
  4       Full 796853     05-JUN-12 +DGASM/ora11/datafile/users.261.785186921

  Backup Set Copy #1 of backup set 2
  Device Type Elapsed Time Completion Time Compressed Tag
  ----------- ------------ --------------- ---------- ---
  DISK        00:02:07     05-JUN-12       NO         TAG20120605T141429

    List of Backup Pieces for backup set 2 Copy #1
    BP Key  Pc# Status      Piece Name
    ------- --- ----------- ----------
    2       1   AVAILABLE   /oracle/backup/db_ORA11_2_1_785168069
    3       2   AVAILABLE   /oracle/backup/db_ORA11_2_2_785168069
    4       3   AVAILABLE   /oracle/backup/db_ORA11_2_3_785168069

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
3       Full    9.33M      DISK        00:00:04     05-JUN-12     
        BP Key: 5   Status: AVAILABLE  Compressed: NO  Tag: TAG20120605T141429
        Piece Name: /oracle/backup/db_ORA11_3_1_785168198
  Control File Included: Ckp SCN: 796891       Ckp time: 05-JUN-12

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4       Full    80.00K     DISK        00:00:01     05-JUN-12     
        BP Key: 6   Status: AVAILABLE  Compressed: NO  Tag: TAG20120605T192553
        Piece Name: +DGASM/ora11/backupset/2012_06_05/nnsnf0_tag20120605t192553_0.262.785186923
  SPFILE Included: Modification time: 05-JUN-12
  SPFILE db_unique_name: ORA11

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
5       Full    9.33M      DISK        00:00:02     18-JUN-12     
        BP Key: 7   Status: AVAILABLE  Compressed: NO  Tag: TAG20120618T170921
        Piece Name: /oracle/backupasm/ctl_ORA11_10
  Control File Included: Ckp SCN: 839167       Ckp time: 18-JUN-12

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
6       Full    943.18M    DISK        00:01:35     18-JUN-12     
  List of Datafiles in backup set 6
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 839176     18-JUN-12 +DGASM/ora11/datafile/system.257.785186755
  2       Full 839176     18-JUN-12 +DGASM/ora11/datafile/sysaux.258.785186845
  3       Full 839176     18-JUN-12 +DGASM/ora11/datafile/undotbs1.259.785186901
  4       Full 839176     18-JUN-12 +DGASM/ora11/datafile/users.261.785186921

  Backup Set Copy #1 of backup set 6
  Device Type Elapsed Time Completion Time Compressed Tag
  ----------- ------------ --------------- ---------- ---
  DISK        00:01:35     18-JUN-12       NO         TAG20120618T170924

    List of Backup Pieces for backup set 6 Copy #1
    BP Key  Pc# Status      Piece Name
    ------- --- ----------- ----------
    8       1   AVAILABLE   /oracle/backupasm/db_ORA11_11_1_786301764
    9       2   AVAILABLE   /oracle/backupasm/db_ORA11_11_2_786301764

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
7       Full    9.36M      DISK        00:00:04     18-JUN-12     
        BP Key: 10   Status: AVAILABLE  Compressed: NO  Tag: TAG20120618T170924
        Piece Name: /oracle/backupasm/db_ORA11_12_1_786301867
  SPFILE Included: Modification time: 18-JUN-12
  SPFILE db_unique_name: ORA11
  Control File Included: Ckp SCN: 839215       Ckp time: 18-JUN-12

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
8       Full    9.33M      DISK        00:00:03     24-JUN-12     
        BP Key: 11   Status: AVAILABLE  Compressed: NO  Tag: TAG20120624T163428
        Piece Name: /oracle/backupasm/ctl_ORA11_13
  Control File Included: Ckp SCN: 875884       Ckp time: 24-JUN-12

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
9       Full    950.97M    DISK        00:01:36     24-JUN-12     
  List of Datafiles in backup set 9
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 875893     24-JUN-12 +DGASM/ora11/datafile/system.257.785186755
  2       Full 875893     24-JUN-12 +DGASM/ora11/datafile/sysaux.258.785186845
  3       Full 875893     24-JUN-12 +DGASM/ora11/datafile/undotbs1.259.785186901
  4       Full 875893     24-JUN-12 +DGASM/ora11/datafile/users.261.785186921

  Backup Set Copy #1 of backup set 9
  Device Type Elapsed Time Completion Time Compressed Tag
  ----------- ------------ --------------- ---------- ---
  DISK        00:01:36     24-JUN-12       NO         TAG20120624T163434

    List of Backup Pieces for backup set 9 Copy #1
    BP Key  Pc# Status      Piece Name
    ------- --- ----------- ----------
    12      1   AVAILABLE   /oracle/backupasm/db_ORA11_14_1_786818074
    13      2   AVAILABLE   /oracle/backupasm/db_ORA11_14_2_786818074

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
10      Full    9.36M      DISK        00:00:02     24-JUN-12     
        BP Key: 14   Status: AVAILABLE  Compressed: NO  Tag: TAG20120624T163434
        Piece Name: /oracle/backupasm/db_ORA11_15_1_786818176
  SPFILE Included: Modification time: 24-JUN-12
  SPFILE db_unique_name: ORA11
  Control File Included: Ckp SCN: 875932       Ckp time: 24-JUN-12

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
11      Full    9.33M      DISK        00:00:03     24-JUN-12     
        BP Key: 15   Status: AVAILABLE  Compressed: NO  Tag: TAG20120624T164029
        Piece Name: +DGASM/backup/ctl_ora11_16
  Control File Included: Ckp SCN: 876056       Ckp time: 24-JUN-12

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
12      Full    950.97M    DISK        00:01:38     24-JUN-12     
  List of Datafiles in backup set 12
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 876064     24-JUN-12 +DGASM/ora11/datafile/system.257.785186755
  2       Full 876064     24-JUN-12 +DGASM/ora11/datafile/sysaux.258.785186845
  3       Full 876064     24-JUN-12 +DGASM/ora11/datafile/undotbs1.259.785186901
  4       Full 876064     24-JUN-12 +DGASM/ora11/datafile/users.261.785186921

  Backup Set Copy #1 of backup set 12
  Device Type Elapsed Time Completion Time Compressed Tag
  ----------- ------------ --------------- ---------- ---
  DISK        00:01:38     24-JUN-12       NO         TAG20120624T164034

    List of Backup Pieces for backup set 12 Copy #1
    BP Key  Pc# Status      Piece Name
    ------- --- ----------- ----------
    16      1   AVAILABLE   +DGASM/backup/db_ora11_17_1_786818434
    17      2   AVAILABLE   +DGASM/backup/db_ora11_17_2_786818434

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
13      Full    9.36M      DISK        00:00:02     24-JUN-12     
        BP Key: 18   Status: AVAILABLE  Compressed: NO  Tag: TAG20120624T164034
        Piece Name: +DGASM/backup/db_ora11_18_1_786818542
  SPFILE Included: Modification time: 24-JUN-12
  SPFILE db_unique_name: ORA11
  Control File Included: Ckp SCN: 876107       Ckp time: 24-JUN-12

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
14      Full    9.33M      DISK        00:00:03     24-JUN-12     
        BP Key: 19   Status: AVAILABLE  Compressed: NO  Tag: TAG20120624T172227
        Piece Name: +DGASM/backup/ctl_ora11_19
  Control File Included: Ckp SCN: 877939       Ckp time: 24-JUN-12

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
15      Full    951.52M    DISK        00:01:18     24-JUN-12     
  List of Datafiles in backup set 15
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 877947     24-JUN-12 +DGASM/ora11/datafile/system.257.785186755
  2       Full 877947     24-JUN-12 +DGASM/ora11/datafile/sysaux.258.785186845
  3       Full 877947     24-JUN-12 +DGASM/ora11/datafile/undotbs1.259.785186901
  4       Full 877947     24-JUN-12 +DGASM/ora11/datafile/users.261.785186921

  Backup Set Copy #1 of backup set 15
  Device Type Elapsed Time Completion Time Compressed Tag
  ----------- ------------ --------------- ---------- ---
  DISK        00:01:18     24-JUN-12       NO         TAG20120624T172232

    List of Backup Pieces for backup set 15 Copy #1
    BP Key  Pc# Status      Piece Name
    ------- --- ----------- ----------
    20      1   AVAILABLE   +DGASM/backup/db_ora11_20_1_786820952
    21      2   AVAILABLE   +DGASM/backup/db_ora11_20_2_786820952

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
16      Full    9.36M      DISK        00:00:02     24-JUN-12     
        BP Key: 22   Status: AVAILABLE  Compressed: NO  Tag: TAG20120624T172232
        Piece Name: +DGASM/backup/db_ora11_21_1_786821034
  SPFILE Included: Modification time: 24-JUN-12
  SPFILE db_unique_name: ORA11
  Control File Included: Ckp SCN: 877981       Ckp time: 24-JUN-12

RMAN>

6、restore database，以最近的備份來恢復資料庫，

(1)轉儲資料庫(RESTORE)：
按照RMAN儲存庫的條目從備份檔案恢復需要的檔案，是指利用備份的資料庫檔案來
替換已經損壞的資料庫檔案或者將其恢復到一個新的位置。RMAN在進行修復操作時，
會利用恢復目錄(沒有建立恢復目錄的話就使用目標資料庫的控制檔案)來獲取備份資訊，
並從中選擇最合適的備份進行修復操作。
選擇備份時有兩個原則(1、選擇距離恢復目錄時刻最近；2、優先選擇映象複製，其次才是備份集)

(2)資料庫恢復(RECOVER)：使用資料檔案和REDO檔案進行完全的或者時間點的資料庫媒體恢復，
是指利用重做日誌或增量備份來重建丟失的資料。

RMAN> restore database;

Starting restore at 26-JUN-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=27 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to +DGASM/ora11/datafile/system.257.785186755
channel ORA_DISK_1: restoring datafile 00002 to +DGASM/ora11/datafile/sysaux.258.785186845
channel ORA_DISK_1: restoring datafile 00003 to +DGASM/ora11/datafile/undotbs1.259.785186901
channel ORA_DISK_1: restoring datafile 00004 to +DGASM/ora11/datafile/users.261.785186921
channel ORA_DISK_1: reading from backup piece +DGASM/backup/db_ora11_20_1_786820952
channel ORA_DISK_1: piece handle=+DGASM/backup/db_ora11_20_1_786820952 tag=TAG20120624T172232
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: reading from backup piece +DGASM/backup/db_ora11_20_2_786820952
channel ORA_DISK_1: piece handle=+DGASM/backup/db_ora11_20_2_786820952 tag=TAG20120624T172232
channel ORA_DISK_1: restored backup piece 2
channel ORA_DISK_1: restore complete, elapsed time: 00:01:51
Finished restore at 26-JUN-12

SQL> select file#,error from v$recover_file;

     FILE# ERROR
---------- -----------------------------------------------------------------
         1
         2
         3
         4
        
檢視已經沒有錯

7、恢復資料庫，應用重做日誌，然後開啟資料庫。

RMAN> recover database;

Starting recover at 26-JUN-12
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:20

Finished recover at 26-JUN-12

RMAN> alter database open;

database opened

RMAN>

總結：用rman做全庫恢復，首先保證控制檔案是完好的，在恢復的過程中不會恢復控制檔案，是以控制檔案為標準來恢復資料檔案。應用redo日誌直到控制檔案中記錄的資料檔案的scn和資料檔案頭中記錄的scn一致。這種恢復如果歸檔和當前redo都完好，就不會丟資料。