ORACLE備份&恢復案例(5)

4.3丟失多個資料檔案，實現整個資料庫的恢復

4.3.1 OS備份方案

OS備份歸檔模式下損壞（丟失）多個資料檔案，進行整個資料庫的恢復

1、連線資料庫，建立測試表並插入記錄

SQL*Plus: Release 8.1.6.0.0 - Production on Tue May 6 13:46:32 2003

(c) Copyright 1999 Oracle Corporation. All rights reserved.

SQL> connect internal/password as sysdba;

Connected.

SQL> create table test(a int);

Table created

SQL> insert into test values(1);

1 row inserted

SQL> commit;

Commit complete

2、備份資料庫，備份除臨時資料檔案後的所資料檔案

SQL> @hotbak.sql 或在DOS下 svrmgrl @hotbak.sql

3、繼續在測試表中插入記錄

SQL> insert into test values(2);

1 row inserted

SQL> commit;

Commit complete

SQL> select * from test;

A

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

1

2

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

4、關閉資料庫，模擬丟失資料檔案

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down

C:>del D:ORACLEORADATATESTSYSTEM01.DBF

C:>del D:ORACLEORADATATESTINDX01.DBF

C:>del D:ORACLEORADATATESTTOOLS01.DBF

C:>del D:ORACLEORADATATESTRBS01.DBF

模擬媒體毀壞（這裡刪除多個資料檔案）

5、啟動資料庫，檢查錯誤

SQL> STARTUP

ORACLE instance started.

Total System Global Area 102020364 bytes

Fixed Size 70924 bytes

Variable Size 85487616 bytes

Database Buffers 16384000 bytes

Redo Buffers 77824 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 1 - see DBWR trace file

ORA-01110: data file 1: 'D:ORACLEORADATATESTSYSTEM01.DBF'

詳細資訊可以檢視報警檔案

ORA-1157 signalled during: ALTER DATABASE OPEN...

Thu May 08 09:39:36 2003

Errors in file D:OracleadmintestbdumptestDBW0.TRC:

ORA-01157: cannot identify/lock data file 1 - see DBWR trace file

ORA-01110: data file 1: 'D:ORACLEORADATATESTSYSTEM01.DBF'

ORA-27041: unable to open file

OSD-04002: unable to open file

O/S-Error: (OS 2) 系統找不到指定的檔案。

Thu May 08 09:39:36 2003

Errors in file D:OracleadmintestbdumptestDBW0.TRC:

ORA-01157: cannot identify/lock data file 2 - see DBWR trace file

ORA-01110: data file 2: 'D:ORACLEORADATATESTRBS01.DBF'

ORA-27041: unable to open file

OSD-04002: unable to open file

O/S-Error: (OS 2) 系統找不到指定的檔案。

Thu May 08 09:39:36 2003

Errors in file D:OracleadmintestbdumptestDBW0.TRC:

ORA-01157: cannot identify/lock data file 5 - see DBWR trace file

ORA-01110: data file 5: 'D:ORACLEORADATATESTTOOLS01.DBF'

ORA-27041: unable to open file

OSD-04002: unable to open file

O/S-Error: (OS 2) 系統找不到指定的檔案。

Thu May 08 09:39:36 2003

Errors in file D:OracleadmintestbdumptestDBW0.TRC:

ORA-01157: cannot identify/lock data file 6 - see DBWR trace file

ORA-01110: data file 6: 'D:ORACLEORADATATESTINDX01.DBF'

ORA-27041: unable to open file

OSD-04002: unable to open file

O/S-Error: (OS 2) 系統找不到指定的檔案。

透過查詢v$recover_file可以看到

SQL> select * from v$recover_file;

FILE# ONLINE ERROR CHANGE# TIME

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

1 ONLINE FILE NOT FOUND 0

2 ONLINE FILE NOT FOUND 0

5 ONLINE FILE NOT FOUND 0

6 ONLINE FILE NOT FOUND 0

有四個資料檔案需要恢復

6、複製備份回到原地點(restore)，開始恢復資料庫(recover)

restore過程:

C:>copy D:DATABAKSYSTEM01.DBF D:ORACLEORADATATEST

C:>copy D:DATABAKTESTINDX01.DBF D:ORACLEORADATATEST

C:>copy D:DATABAKTESTTOOLS01.DBF D:ORACLEORADATATEST

C:>copy D:DATABAKTESTRBS01.DBF.DBF D:ORACLEORADATATEST

Recover過程:

SQL> recover database;

ORA-00279: change 1073849 generated at 05/08/2003 08:58:35 needed for thread 1

ORA-00289: suggestion : D:ORACLEORADATATESTARCHIVETESTT001S00311.ARC

ORA-00280: change 1073849 for thread 1 is in sequence #311

Specify log: {=suggested | filename | AUTO | CANCEL}

auto

ORA-00279: change 1073856 generated at 05/08/2003 09:03:27 needed for thread 1

ORA-00289: suggestion : D:ORACLEORADATATESTARCHIVETESTT001S00312.ARC

ORA-00280: change 1073856 for thread 1 is in sequence #312

ORA-00278: log file 'D:ORACLEORADATATESTARCHIVETESTT001S00311.ARC' no

longer needed for this recovery

ORA-00279: change 1073858 generated at 05/08/2003 09:11:43 needed for thread 1

ORA-00289: suggestion : D:ORACLEORADATATESTARCHIVETESTT001S00313.ARC

ORA-00280: change 1073858 for thread 1 is in sequence #313

ORA-00278: log file 'D:ORACLEORADATATESTARCHIVETESTT001S00312.ARC' no

longer needed for this recovery

ORA-00279: change 1073870 generated at 05/08/2003 09:11:46 needed for thread 1

ORA-00289: suggestion : D:ORACLEORADATATESTARCHIVETESTT001S00314.ARC

ORA-00280: change 1073870 for thread 1 is in sequence #314

ORA-00278: log file 'D:ORACLEORADATATESTARCHIVETESTT001S00313.ARC' no

longer needed for this recovery

Log applied.

Media recovery complete.

7、開啟資料庫，檢查資料庫的資料（完全恢復）

SQL> alter database open;

Database altered.

SQL> select * from test;

A

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

1

2

說明：

1、只要有備份與歸檔存在，就可以實現資料庫的完全恢復（不丟失資料）

2、適合於丟失大量資料檔案，或包含系統資料檔案在內的資料庫的恢復

3、恢復過程在mount下進行，如果恢復成功，再開啟資料庫，down機時間可能比較長一些。

[@more@]