【管理篇備份恢復】rman恢復測試（一）表空間資料檔案

1,表空間資料檔案丟失

[oracle@limmTest tpdata]$ cd /tpdata/database/oradata/test1/

[oracle@limmTest test1]$ mv taipinglife.dbf taipinglife.bak

任意的一個查詢：

SQL> select count(*) from test.gp_acct_type t;

COUNT(*)

----------

0

Execution Plan

----------------------------------------------------------

Plan hash value: 1501901367

----------------------------------------------------------------------------

----------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 0 (0)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | | |

| 2 | INDEX FULL SCAN| PK_GP_ACCT_TYPE | 1 | 0 (0)| 00:00:01 |

----------------------------------------------------------------------------

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

1 consistent gets

0 physical reads

0 redo size

410 bytes sent via SQL*Net to client

400 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

發現並沒有出現期待的不能開啟資料檔案的錯誤，但是下列語句則報錯：

SQL> select /*+ rule*/count(*) from test.gp_acct_type t;

select /*+ rule*/count(*) from test.gp_acct_type t

*

ERROR at line 1:

ORA-01116: error in opening database file 6

ORA-01110: data file 6: '/tpdata/database/oradata/test1/taipinglife.dbf'

ORA-27041: unable to open file

Linux Error: 2: No such file or directory

Additional information: 3

SQL>

SQL> select a.num_rows from all_tables a where a.table_name = 'GP_ACCT_TYPE';

NUM_ROWS

----------

0

通過分析使用rule hint和不使用rule hint的語句的執行計劃，可以發現：

SQL> explain plan for select /*+ rule*/count(*) from test.gp_acct_type t;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 925279237

-------------------------------------------

| Id | Operation | Name |

-------------------------------------------

| 0 | SELECT STATEMENT | |

| 1 | SORT AGGREGATE | |

| 2 | TABLE ACCESS FULL| GP_ACCT_TYPE |

-------------------------------------------

Note

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

-----

- rule based optimizer used (consider using cbo)

13 rows selected.

使用cbo優化策略的語句走了INDEX FULL SCAN的索引，索引為：PK_GP_ACCT_TYPE

而這個索引並不在刪除的資料檔案taipinglife.dbf中：

SQL> select a.tablespace_name, c.NAME file_name

2 from all_indexes a, v$tablespace b, v$datafile c

3 where a.index_name = upper('PK_GP_ACCT_TYPE')

4 and a.tablespace_name = b.name

5 and b.TS# = c.TS#;

TABLESPACE_NAME

------------------------------

FILE_NAME

--------------------------------------------------------------------------------

USERS

/tpdata/database/oradata/test1/users01.dbf

USERS

/tpdata/database/oradata/test1/users02.dbf

但是使用rule hint之後，將對錶GP_ACCT_TYPE執行全表掃描，故這時候可以看到報錯了。

下面來嘗試恢復資料檔案6：taipinglife.dbf

RMAN> recover datafile 6;

Starting recover at 06-JUN-10

using channel ORA_DISK_1

starting media recovery

media recovery failed

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of recover command at 06/06/2010 01:21:31

ORA-00283: recovery session canceled due to errors

RMAN-11003: failure during parse/execution of SQL statement: alter database recover if needed

datafile 6

ORA-00283: recovery session canceled due to errors

ORA-01124: cannot recover data file 6 - file is in use or recovery

ORA-01110: data file 6: '/tpdata/database/oradata/test1/taipinglife.dbf'

這裡恢復報錯：資料檔案6目前正在使用，或者恢復

將這個資料檔案離線：

SQL> alter database datafile 6 offline;

Database altered.

然後嘗試一下，告知恢復失敗，必須先轉儲資料檔案

RMAN> recover datafile 6;

Starting recover at 06-JUN-10

using channel ORA_DISK_1

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of recover command at 06/06/2010 01:27:02

RMAN-06094: datafile 6 must be restored

之前已經有過資料庫的全備，檢視備份的情況：

RMAN> list backup of datafile 6;

List of Backup Sets

===================

BS Key Type LV Size Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ ---------------

13 Full 3.33G DISK 00:04:50 26-MAY-10

BP Key: 13 Status: AVAILABLE Compressed: NO Tag: TAG20100526T034614

Piece Name: /tpdata/database/flash_recovery_area/TEST1/backupset/2010_05_26/o1_mf_nnndf_TAG20100526T034614_5zrb8723_.bkp

List of Datafiles in backup set 13

File LV Type Ckp SCN Ckp Time Name

---- -- ---- ---------- --------- ----

6 Full 6410727214353 26-MAY-10 /tpdata/database/oradata/test1/taipinglife.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ ---------------

44 Full 92.56M DISK 00:00:15 05-JUN-10

BP Key: 44 Status: AVAILABLE Compressed: NO Tag: TAG20100605T054817

Piece Name: /tpdata/database/flash_recovery_area/TEST1/backupset/2010_06_05/o1_mf_nnndf_TAG20100605T054817_60lx51yo_.bkp

List of Datafiles in backup set 44

File LV Type Ckp SCN Ckp Time Name

---- -- ---- ---------- --------- ----

6 Full 6410727855233 05-JUN-10 /tpdata/database/oradata/test1/taipinglife.dbf

使用restore從備份集中轉儲資料檔案

RMAN> restore datafile 6;

在轉儲開始時檢視v$recover_file 發現error由之前的找不到資料檔案到資料檔案格式不正確

restore完成時，可以看到error已經變為空。

SQL> select * from v$recover_file a;

FILE# ONLINE ONLINE_

---------- ------- -------

ERROR CHANGE#

----------------------------------------------------------------- ----------

TIME

---------

6 OFFLINE OFFLINE

6.4107E+12

05-JUN-10

完整的restore資訊如下：

RMAN> restore datafile 6;

Starting restore at 06-JUN-10

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backupset restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

restoring datafile 00006 to /tpdata/database/oradata/test1/taipinglife.dbf

channel ORA_DISK_1: reading from backup piece /tpdata/database/flash_recovery_area/TEST1/backupset/2010_06_05/o1_mf_nnndf_TAG20100605T054817_60lx51yo_.bkp

channel ORA_DISK_1: restored backup piece 1

piece handle=/tpdata/database/flash_recovery_area/TEST1/backupset/2010_06_05/o1_mf_nnndf_TAG20100605T054817_60lx51yo_.bkp tag=TAG20100605T054817

channel ORA_DISK_1: restore complete, elapsed time: 00:00:36

Finished restore at 06-JUN-10

這些資訊也可以使用下列查詢語句得到：

select output from v$rman_output a where a.SESSION_KEY = 123;--123是本例中rman的session id

注意到上面的

restored backup piece 1 piece handle=...

rman實際使用的是最近一次資料檔案6的備份，

SQL> select a.btype, a.SESSION_KEY, a.TSname, a.CHECKPOINT_TIME

2 from v$backup_datafile_details a where a.FILE# = 6;

BTYPE SESSION_KEY TSNAME CHECKPOIN

--------- ----------- ------------------------------ ---------

BACKUPSET 123 TAIPINGLIFE 05-JUN-10

BACKUPSET 82 TAIPINGLIFE 26-MAY-10

對應的備份片的詳細資訊如下：

SQL> select substr(a.HANDLE, instr(a.HANDLE, '/', -1) + 1) es,

2 a.IS_RECOVERY_DEST_FILE irf,

3 a.SIZE_BYTES_DISPLAY

4 from v$backup_piece_details a

5 where a.SESSION_KEY = 123

6 and a.TAG = 'TAG20100605T054817';

ES

--------------------------------------------------------------------------------

IRF

---

SIZE_BYTES_DISPLAY

--------------------------------------------------------------------------------

o1_mf_nnndf_TAG20100605T054817_60lx51yo_.bkp

YES

92.56M

restore成功以後，就可以使用recover來恢復資料檔案：

RMAN> recover datafile 6;

恢復完成後可以看到這個檢視中沒有記錄了，即當前沒有需要恢復的檔案：

SQL> select * from v$recover_file a;

no rows selected

然後將資料檔案online

SQL> alter database datafile 6 online;

Database altered.

SQL> select name from v$datafile where file#= 6;

NAME

--------------------------------------------------------------------------------

/tpdata/database/oradata/test1/taipinglife.dbf

SQL> host ls -l /tpdata/database/oradata/test1/taipinglife.dbf;

-rw-r----- 1 oracle oinstall 524296192 Jun 6 02:42 /tpdata/database/oradata/test1/taipinglife.dbf

查詢之前的語句，可以看出資料檔案恢復成功了：

SQL> select /*+ rule*/count(*) from test.gp_acct_type t;

COUNT(*)

----------

0

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

【管理篇備份恢復】rman恢復測試（一） 表空間資料檔案

相關文章

【管理篇備份恢復】rman恢復測試（一）表空間資料檔案