資料庫常規恢復案例
一、非歸檔模式下的備份與恢復
備份方案：採用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> @coldbak.sql 或在DOS下 svrmgrl @coldbak.sql
3.再插入記錄
SQL> insert into test values(2);
1 row inserted
SQL> commit;
Commit complete
SQL> select * from test;
A
---------------------------------------
1
2
4.關閉資料庫
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
5.毀壞一個或多個資料檔案，如刪除user01.dbf
C:>del D:ORACLEORADATATESTUSERS01.DBF
模擬媒體毀壞
6.重新啟動資料庫，會發現如下錯誤
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 3 - see DBWR trace file
ORA-01110: data file 3: 'D:ORACLEORADATATESTUSERS01.DBF'
在報警檔案中，會有更詳細的資訊
Errors in file D:OracleadmintestbdumptestDBW0.TRC:
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3: 'D:ORACLEORADATATESTUSERS01.DBF'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) 系統找不到指定的檔案。
7.複製備份復原到原來位置(restore過程)
C:>xcopy d:database*.* d:oracleoradatatest/H/R/S
8.開啟資料庫，檢查資料
SQL> alter database open;
Database altered.
SQL> select * from test;
A
---------------------------------------
1
這裡可以發現，資料庫恢復成功，但在備份之後與崩潰之前的資料丟失了。
說明：
1、非歸檔模式下的恢復方案可選性很小，一般情況下只能有一種恢復方式，就是資料庫的冷備份的完全恢復，僅僅需要複製原來的備份就可以(restore)，不需要recover。
2、這種情況下的恢復，可以完全恢復到備份的點上，但是可能是丟失資料的，在備份之後與崩潰之前的資料將全部丟失。
3、不管毀壞了多少資料檔案或是聯機日誌或是控制檔案，都可以透過這個辦法恢復，因為這個恢復過程是Restore所有的冷備份檔案，而這個備份點上的所有檔案是一致的，與最新的資料庫沒有關係，就好比把資料庫又放到了一個以前的“點”上。
4、對於非歸檔模式下，最好的辦法就是採用OS的冷備份，建議不要用RMAN來作冷備份，效果不好，因為RMAN不備份聯機日誌，restore不能根本解決問題。
5、如果沒有備份聯機日誌，如RMAN的備份，就需要利用不完全恢復(until cancel)的方法來重新建立聯機日誌檔案
二、歸檔模式下丟失或損壞一個資料檔案
1.1 OS備份方案
在歸檔方式下損壞或丟失一個資料檔案，如果存在相應的備份與該備份以來的歸檔日誌，恢復還是比較簡單的，可以作到儘量少的Down機時間，並能作到資料庫的完全恢復。
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) tablespace users;
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:ORACLEORADATATESTUSERS01.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 3 - see DBWR trace file
ORA-01110: data file 3: 'D:ORACLEORADATATESTUSERS01.DBF'
還可以檢視報警檔案（見上一個恢復案例）或動態檢視v$recover_file
如SQL> select * from v$recover_file;
FILE# ONLINE ERROR CHANGE# TIME
---------- ------- ------------------ ---------- -----------
3 ONLINE 1013500 2003-05-07
離線資料檔案
SQL> alter database datafile 3 offline drop;
Database altered.
6、開啟資料庫，複製備份回來(restore)，恢復(recover)該資料檔案，並聯機
SQL> alter database open;
Database altered.
複製備份從備份處
copy d:databak users01.dbf d:oracleoradatatest;
恢復該資料檔案
SQL> recover datafile 3;
ORA-00279: change 1053698 generated at 05/07/2003 17:51:26 needed for thread 1
ORA-00289: suggestion : D:ORACLEORADATATESTARCHIVETESTT001S00304.ARC
ORA-00280: change 1053698 for thread 1 is in sequence #304
Specify log: {
AUTO
ORA-00279: change 1053701 generated at 05/07/2003 17:51:39 needed for thread 1
ORA-00289: suggestion : D:ORACLEORADATATESTARCHIVETESTT001S00305.ARC
ORA-00280: change 1053701 for thread 1 is in sequence #305
ORA-00278: log file 'D:ORACLEORADATATESTARCHIVETESTT001S00304.ARC' no
longer needed for this recovery
Log applied.
Media recovery complete.
恢復成功，聯機該資料檔案
SQL> alter database datafile 3 online;
Database altered.
7、檢查資料庫的資料（完全恢復）
SQL> select * from test;
A
---------------------------------------
1
2
說明：
1、採用熱備份，需要執行在歸檔模式下，可以實現資料庫的完全恢復，也就是說，從備份後到資料庫崩潰時的資料都不會丟失。
2、可以採用全備份資料庫的方式備份，對於特殊情況，也可以只備份特定的資料檔案，如只備份使用者表空間（一般情況下對於某些寫特別頻繁的資料檔案，可以單獨加大備份頻率）
3、如果在恢復過程中，發現損壞的是多個資料檔案，即可以採用一個一個資料檔案的恢復方法（第5步中需要對資料檔案一一離線，第6步中需要對資料檔案分別恢復），也可以採用整個資料庫的恢復方法。
4、如果是系統表空間的損壞，不能採用此方法
1.2 RMAN備份方案
RMAN也可以進行聯機備份，而且備份與恢復方法將比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) tablespace users;
Table created
SQL> insert into test values(1);
1 row inserted
SQL> commit;
Commit complete
2、備份資料庫表空間users
C:>rman
Recovery Manager: Release 8.1.6.0.0 - Production
RMAN> connect rcvcat
RMAN-06008: connected to recovery catalog database
RMAN> connect target internal/virpure
RMAN-06005: connected to target database: TEST (DBID=1788174720)
RMAN> run{
2> allocate channel c1 type disk;
3> backup tag 'tsuser' format 'd:backuptsuser_%u_%s_%p'
4> tablespace users;
5> release channel c1;
6> }
RMAN-03022: compiling command: allocate
RMAN-03023: executing command: allocate
RMAN-08030: allocated channel: c1
RMAN-08500: channel c1: sid=16 devtype=DISK
RMAN-03022: compiling command: backup
RMAN-03025: performing implicit partial resync of recovery catalog
RMAN-03023: executing command: partial resync
RMAN-08003: starting partial resync of recovery catalog
RMAN-08005: partial resync complete
RMAN-03023: executing command: backup
RMAN-08008: channel c1: starting full datafile backupset
RMAN-08502: set_count=5 set_stamp=494177612 creation_time=16-MAY-03
RMAN-08010: channel c1: specifying datafile(s) in backupset
RMAN-08522: input datafile fno=00003 name=D:ORACLEORADATATESTUSER01.DBF
RMAN-08013: channel c1: piece 1 created
RMAN-08503: piece handle=D:BACKUPTSUSER_05EN93AC_5_1 comment=NONE
RMAN-08525: backup set complete, elapsed time: 00:00:01
RMAN-03023: executing command: partial resync
RMAN-08003: starting partial resync of recovery catalog
RMAN-08005: partial resync complete
RMAN-03022: compiling command: release
RMAN-03023: executing command: release
RMAN-08031: released channel: c1
RMAN>
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>r
1* alter system switch logfile;
System altered.
4、關閉資料庫，模擬丟失資料檔案
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down
C:>del D:ORACLEORADATATESTUSER01.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 3 - see DBWR trace file
ORA-01110: data file 3: 'D:ORACLEORADATATESTUSER01.DBF'
6、先開啟資料庫
SQL> alter database datafile 3 offline drop;
Database altered.
SQL> alter database open;
Database altered.
7、恢復該表空間
恢復指令碼可以是恢復單個資料檔案
run{
allocate channel c1 type disk;
restore datafile 3;
recover datafile 3;
sql 'alter database datafile 3 online';
release channel c1;
}
也可以是,恢復表空間
run{
allocate channel c1 type disk;
restore tablespace users;
recover tablespace users;
sql 'alter database datafile 3 online';
release channel c1;
}
過程如下：
C:>rman
Recovery Manager: Release 8.1.6.0.0 - Production
RMAN> connect rcvcat
RMAN-06008: connected to recovery catalog database
RMAN> connect target internal/virpure
RMAN-06005: connected to target database: TEST (DBID=1788174720)
RMAN> run{
2> allocate channel c1 type disk;
3> restore datafile 3;
4> recover datafile 3;
5> sql 'alter database datafile 3 online';
6> release channel c1;
7> }
RMAN-03022: compiling command: allocate
RMAN-03023: executing command: allocate
RMAN-08030: allocated channel: c1
RMAN-08500: channel c1: sid=13 devtype=DISK
RMAN-03022: compiling command: restore
RMAN-03022: compiling command: IRESTORE
RMAN-03023: executing command: IRESTORE
RMAN-08016: channel c1: starting datafile backupset restore
RMAN-08502: set_count=5 set_stamp=494177612 creation_time=16-MAY-03
RMAN-08089: channel c1: specifying datafile(s) to restore from backup set
RMAN-08523: restoring datafile 00003 to D:ORACLEORADATATESTUSER01.DBF
RMAN-08023: channel c1: restored backup piece 1
RMAN-08511: piece handle=D:BACKUPTSUSER_05EN93AC_5_1 tag=TSUSER params=NULL
RMAN-08024: channel c1: restore complete
RMAN-03023: executing command: partial resync
RMAN-08003: starting partial resync of recovery catalog
RMAN-08005: partial resync complete
RMAN-03022: compiling command: recover
RMAN-03022: compiling command: recover(1)
RMAN-03022: compiling command: recover(2)
RMAN-03022: compiling command: recover(3)
RMAN-03023: executing command: recover(3)
RMAN-08054: starting media recovery
RMAN-03022: compiling command: recover(4)
RMAN-06050: archivelog thread 1 sequence 332 is already on disk as file D:ORACLEORADATATESTARCHIVETESTT001S00332.ARC
RMAN-06050: archivelog thread 1 sequence 333 is already on disk as file D:ORACLEORADATATESTARCHIVETESTT001S00333.ARC
RMAN-06050: archivelog thread 1 sequence 334 is already on disk as file D:ORACLEORADATATESTARCHIVETESTT001S00334.ARC
RMAN-03023: executing command: recover(4)
RMAN-08515: archivelog filename=D:ORACLEORADATATESTARCHIVETESTT001S00332.ARC thread=1 sequence=332
RMAN-08055: media recovery complete
RMAN-03022: compiling command: sql
RMAN-06162: sql statement: alter database datafile 3 online
RMAN-03023: executing command: sql
RMAN-03022: compiling command: release
RMAN-03023: executing command: release
RMAN-08031: released channel: c1
RMAN>
8、檢查資料是否完整
SQL> alter database open;
Database altered.
SQL> select * from test;
A
---------------------------------------
1
2
說明：
1、RMAN也可以實現單個表空間或資料檔案的恢復，恢復過程可以在mount下或open方式下，如果在open方式下恢復，可以減少down機時間
2、如果損壞的是一個資料檔案，建議offline並在open方式下恢復
3、這裡可以看到，RMAN進行資料檔案與表空間恢復的時候，程式碼都比較簡單，而且能保證備份與恢復的可靠性，所以建議採用RMAN的備份與恢復
三、丟失多個資料檔案，實現整個資料庫的恢復
1.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: {
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機時間可能比較長一些。
1.2 RMAN備份方案
RMAN備份歸檔模式下損壞（丟失）多個資料檔案，進行整個資料庫的恢復
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、備份資料庫
DOS下 C:> rman cmdfile=bakup.rcv msglog=backup.log;
以下是backup.log內容。
Recovery Manager: Release 8.1.6.0.0 - Production
RMAN> # script:bakup.rcv
2> # creater:chenjiping
3> # date:5.8.2003
4> # desc:backup all database datafile in archive with rman
5>
6> #connect database
7> connect rcvcat ;
8> connect target internal/virpure;
9>
10> #start backup database
11> run{
12> allocate channel c1 type disk;
13> backup full tag 'dbfull' format 'd:backupfull%u_%s_%p' database
14> include current controlfile;
15> sql 'alter system archive log current';
16> release channel c1;
17> }
18> #end
19>
RMAN-06008: connected to recovery catalog database
RMAN-06005: connected to target database: TEST (DBID=1788174720)
RMAN-03022: compiling command: allocate
RMAN-03023: executing command: allocate
RMAN-08030: allocated channel: c1
RMAN-08500: channel c1: sid=15 devtype=DISK
RMAN-03022: compiling command: backup
RMAN-03023: executing command: backup
RMAN-08008: channel c1: starting full datafile backupset
RMAN-08502: set_count=4 set_stamp=494074368 creation_time=15-MAY-03
RMAN-08010: channel c1: specifying datafile(s) in backupset
RMAN-08522: input datafile fno=00002 name=D:ORACLEORADATATESTRBS01.DBF
RMAN-08522: input datafile fno=00001 name=D:ORACLEORADATATESTSYSTEM01.DBF
RMAN-08011: including current controlfile in backupset
RMAN-08522: input datafile fno=00005 name=D:ORACLEORADATATESTTOOLS01.DBF
RMAN-08522: input datafile fno=00004 name=D:ORACLEORADATATESTTEMP01.DBF
RMAN-08522: input datafile fno=00006 name=D:ORACLEORADATATESTINDX01.DBF
RMAN-08522: input datafile fno=00003 name=D:ORACLEORADATATESTUSER01.DBF
RMAN-08013: channel c1: piece 1 created
RMAN-08503: piece handle=D:BACKUPFULL04EN5UG0_4_1 comment=NONE
RMAN-08525: backup set complete, elapsed time: 00:01:16
RMAN-03023: executing command: partial resync
RMAN-08003: starting partial resync of recovery catalog
RMAN-08005: partial resync complete
RMAN-03022: compiling command: sql
RMAN-06162: sql statement: alter system archive log current
RMAN-03023: executing command: sql
RMAN-03022: compiling command: release
RMAN-03023: executing command: release
RMAN-08031: released channel: c1
Recovery Manager complete.
到這裡表示備份成功。
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'
查詢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、利用RMAN進行恢復
C:>rman
Recovery Manager: Release 8.1.6.0.0 - Production
RMAN> connect rcvcat
RMAN-06008: connected to recovery catalog database
RMAN> connect target internal/virpure
RMAN-06005: connected to target database: TEST (DBID=1788174720)
RMAN> run{
2> allocate channel c1 type disk;
3> restore database;
4> recover database;
5> sql 'alter database open';
6> release channel c1;
7> }
RMAN-03022: compiling command: allocate
RMAN-03023: executing command: allocate
RMAN-08030: allocated channel: c1
RMAN-08500: channel c1: sid=17 devtype=DISK
RMAN-03022: compiling command: restore
RMAN-03025: performing implicit partial resync of recovery catalog
RMAN-03023: executing command: partial resync
RMAN-08003: starting partial resync of recovery catalog
RMAN-08005: partial resync complete
RMAN-03022: compiling command: IRESTORE
RMAN-03023: executing command: IRESTORE
RMAN-08016: channel c1: starting datafile backupset restore
RMAN-08502: set_count=4 set_stamp=494074368 creation_time=15-MAY-03
RMAN-08089: channel c1: specifying datafile(s) to restore from backup set
RMAN-08523: restoring datafile 00001 to D:ORACLEORADATATESTSYSTEM01.DBF
RMAN-08523: restoring datafile 00002 to D:ORACLEORADATATESTRBS01.DBF
RMAN-08523: restoring datafile 00003 to D:ORACLEORADATATESTUSER01.DBF
RMAN-08523: restoring datafile 00004 to D:ORACLEORADATATESTTEMP01.DBF
RMAN-08523: restoring datafile 00005 to D:ORACLEORADATATESTTOOLS01.DBF
RMAN-08523: restoring datafile 00006 to D:ORACLEORADATATESTINDX01.DBF
RMAN-08023: channel c1: restored backup piece 1
RMAN-08511: piece handle=D:BACKUPFULL04EN5UG0_4_1 tag=DBFULL params=NULL
RMAN-08024: channel c1: restore complete
RMAN-03023: executing command: partial resync
RMAN-08003: starting partial resync of recovery catalog
RMAN-08005: partial resync complete
RMAN-03022: compiling command: recover
RMAN-03022: compiling command: recover(1)
RMAN-03022: compiling command: recover(2)
RMAN-03022: compiling command: recover(3)
RMAN-03023: executing command: recover(3)
RMAN-08054: starting media recovery
RMAN-03022: compiling command: recover(4)
RMAN-06050: archivelog thread 1 sequence 327 is already on disk as file D:ORACLEORADATATESTARCHIVETESTT001S00327.ARC
RMAN-06050: archivelog thread 1 sequence 328 is already on disk as file D:ORACLEORADATATESTARCHIVETESTT001S00328.ARC
RMAN-06050: archivelog thread 1 sequence 329 is already on disk as file D:ORACLEORADATATESTARCHIVETESTT001S00329.ARC
RMAN-06050: archivelog thread 1 sequence 330 is already on disk as file D:ORACLEORADATATESTARCHIVETESTT001S00330.ARC
RMAN-03023: executing command: recover(4)
RMAN-08515: archivelog filename=D:ORACLEORADATATESTARCHIVETESTT001S00327.ARC thread=1 sequence=327
RMAN-08515: archivelog filename=D:ORACLEORADATATESTARCHIVETESTT001S00328.ARC thread=1 sequence=328
RMAN-08055: media recovery complete
RMAN-03022: compiling command: sql
RMAN-06162: sql statement: alter database open
RMAN-03023: executing command: sql
RMAN-03022: compiling command: release
RMAN-03023: executing command: release
RMAN-08031: released channel: c1
RMAN>
7、檢查資料庫的資料（完全恢復）
SQL> select * from test;
A
---------------------------------------
1
2
說明：
1、只要有備份與歸檔存在，RMAN也可以實現資料庫的完全恢復（不丟失資料）
2、同OS備份資料庫恢復，適合於丟失大量資料檔案，或包含系統資料檔案在內的資料庫的恢復
3、目標資料庫在mount下進行，如果恢復成功，再開啟資料庫。
4、RMAN的備份與恢復命令相對比較簡單並可靠，建議有條件的話，都採用RMAN進行資料庫的備份。
四、不完全恢復案例
1.1 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、刪除測試表，假定刪除前的時間為T1，在刪除之前，便於測試，繼續插入資料並應用到歸檔。
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;
Statement processed.
SQL> alter system switch logfile;
Statement processed.
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
TO_CHAR(SYSDATE,'YY
-------------------
2003-05-21 14:43:01
SQL> drop table test;
Table dropped.
4、準備恢復到時間點T1，找回刪除的表，先關閉資料庫
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
5、複製剛才備份的所有資料檔案回來
C:>copy D:DATABAK*.DBF D:ORACLEORADATATEST
6、啟動到mount下
SQL> startup mount;
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.
7、開始不完全恢復資料庫到T1時間
SQL> recover database until time '2003-05-21:14:43:01';
ORA-00279: change 30944 generated at 05/21/2003 14:40:06 needed for thread 1
ORA-00289: suggestion : D:ORACLEORADATATESTARCHIVETESTT001S00191.ARC
ORA-00280: change 30944 for thread 1 is in sequence #191
Specify log: {
auto
Log applied.
Media recovery complete.
8、開啟資料庫，檢查資料
SQL> alter database open resetlogs;
Database altered.
SQL> select * from test;
A
---------------------------------------
1
2
說明：
1、不完全恢復最好備份所有的資料，冷備份亦可，因為恢復過程是從備份點往後恢復的，如果因為其中一個資料檔案的時間戳(SCN)大於要恢復的時間點，那麼恢復都是不可能成功的。
2、不完全恢復有三種方式，過程都一樣，僅僅是recover命令有所不一樣，這裡用基於時間的恢復作為示例。
3、不完全恢復之後，都必須用resetlogs的方式開啟資料庫，建議馬上再做一次全備份，因為resetlogs之後再用以前的備份恢復是很難了。
4、以上是在刪除之前獲得時間，但是實際應用中，很難知道刪除之前的實際時間，但可以採用大致時間即可，或可以採用分析日誌檔案(logmnr)，取得精確的需要恢復的時間。
5、一般都是在測試機後備用機器上採用這種不完全恢復，恢復之後匯出/匯入被誤刪的表回生產系統
1.2 RMAN備份下的基於改變的恢復
以上用OS備份說明了一個基於時間的恢復，現在用RMAN說明一個基於改變的恢復
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、備份資料庫
C:>rman
Recovery Manager: Release 8.1.6.0.0 - Production
RMAN> connect rcvcat
RMAN-06008: connected to recovery catalog database
RMAN> connect target internal/virpure
RMAN-06005: connected to target database: TEST (DBID=874705288)
RMAN> run{
2> allocate channel c1 type disk;
3> backup full tag 'dbfull' format 'd:backupfull%u_%s_%p' database
4> include current controlfile;
5> sql 'alter system archive log current';
6> release channel c1;
7> }
RMAN-03022: compiling command: allocate
RMAN-03023: executing command: allocate
RMAN-08030: allocated channel: c1
RMAN-08500: channel c1: sid=12 devtype=DISK
RMAN-03022: compiling command: backup
RMAN-03023: executing command: backup
RMAN-08008: channel c1: starting full datafile backupset
RMAN-08502: set_count=1 set_stamp=494607834 creation_time=21-MAY-03
RMAN-08010: channel c1: specifying datafile(s) in backupset
RMAN-08522: input datafile fno=00001 name=D:ORACLEORADATATESTSYSTEM01.DBF
RMAN-08011: including current controlfile in backupset
RMAN-08522: input datafile fno=00002 name=D:ORACLEORADATATESTRBS01.DBF
RMAN-08522: input datafile fno=00003 name=D:ORACLEORADATATESTUSERS01.DBF
RMAN-08522: input datafile fno=00004 name=D:ORACLEORADATATESTTEMP01.DBF
RMAN-08522: input datafile fno=00005 name=D:ORACLEORADATATESTTOOLS01.DBF
RMAN-08522: input datafile fno=00006 name=D:ORACLEORADATATESTINDX01.DBF
RMAN-08013: channel c1: piece 1 created
RMAN-08503: piece handle=D:BACKUPFULL01ENM7EQ_1_1 comment=NONE
RMAN-08525: backup set complete, elapsed time: 00:00:16
RMAN-03023: executing command: partial resync
RMAN-08003: starting partial resync of recovery catalog
RMAN-08005: partial resync complete
RMAN-03022: compiling command: sql
RMAN-06162: sql statement: alter system archive log current
RMAN-03023: executing command: sql
RMAN-03022: compiling command: release
RMAN-03023: executing command: release
RMAN-08031: released channel: c1
RMAN>
3、刪除測試表，在刪除之前，便於測試，繼續插入資料並應用到歸檔，並獲取刪除前的scn號。
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;
Statement processed.
SQL> alter system switch logfile;
Statement processed.
SQL> select max(ktuxescnw * power(2, 32) + ktuxescnb) scn from x$ktuxe;
SCN
----------
31014
SQL> drop table test;
Table dropped.
4、準備恢復到SCN 31014，先關閉資料庫，然後啟動到mount下
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 53126412 bytes
Fixed Size 70924 bytes
Variable Size 26763264 bytes
Database Buffers 26214400 bytes
Redo Buffers 77824 bytes
Database mounted.
5、開始恢復到改變點SCN 31014
RMAN> run{
2> allocate channel c1 type disk;
3> restore database;
4> recover database until scn 31014;
5> sql 'ALTER DATABASE OPEN RESETLOGS';
6> release channel c1;
7> }
RMAN-03022: compiling command: allocate
RMAN-03023: executing command: allocate
RMAN-08030: allocated channel: c1
RMAN-08500: channel c1: sid=10 devtype=DISK
RMAN-03022: compiling command: restore
RMAN-03022: compiling command: IRESTORE
RMAN-03023: executing command: IRESTORE
RMAN-08016: channel c1: starting datafile backupset restore
RMAN-08502: set_count=1 set_stamp=494613682 creation_time=21-MAY-03
RMAN-08089: channel c1: specifying datafile(s) to restore from backup set
RMAN-08523: restoring datafile 00001 to D:ORACLEORADATATESTSYSTEM01.DBF
RMAN-08523: restoring datafile 00002 to D:ORACLEORADATATESTRBS01.DBF
RMAN-08523: restoring datafile 00003 to D:ORACLEORADATATESTUSERS01.DBF
RMAN-08523: restoring datafile 00004 to D:ORACLEORADATATESTTEMP01.DBF
RMAN-08523: restoring datafile 00005 to D:ORACLEORADATATESTTOOLS01.DBF
RMAN-08523: restoring datafile 00006 to D:ORACLEORADATATESTINDX01.DBF
RMAN-08023: channel c1: restored backup piece 1
RMAN-08511: piece handle=D:BACKUPFULL01ENMD5I_1_1 tag=DBFULL params=NULL
RMAN-08024: channel c1: restore complete
RMAN-03023: executing command: partial resync
RMAN-08003: starting partial resync of recovery catalog
RMAN-08005: partial resync complete
RMAN-03022: compiling command: recover
RMAN-03022: compiling command: recover(1)
RMAN-03022: compiling command: recover(2)
RMAN-03022: compiling command: recover(3)
RMAN-03023: executing command: recover(3)
RMAN-08054: starting media recovery
RMAN-03022: compiling command: recover(4)
RMAN-06050: archivelog thread 1 sequence 191 is already on disk as file D:ORACL
EORADATATESTARCHIVETESTT001S00191.ARC
RMAN-06050: archivelog thread 1 sequence 192 is already on disk as file D:ORACL
EORADATATESTARCHIVETESTT001S00192.ARC
RMAN-03023: executing command: recover(4)
RMAN-08515: archivelog filename=D:ORACLEORADATATESTARCHIVETESTT001S00191.AR
C thread=1 sequence=191
RMAN-08515: archivelog filename=D:ORACLEORADATATESTARCHIVETESTT001S00192.AR
C thread=1 sequence=192
RMAN-08055: media recovery complete
RMAN-03022: compiling command: sql
RMAN-06162: sql statement: ALTER DATABASE OPEN RESETLOGS
RMAN-03023: executing command: sql
RMAN-03022: compiling command: release
RMAN-03023: executing command: release
RMAN-08031: released channel: c1
6、檢查資料
Database altered.
SQL> select * from test;
A
---------------------------------------
1
2
可以看到，表依然存在
說明：
1、RMAN也可以實現不完全恢復，方法比OS備份恢復的方法更簡單可靠
2、RMAN可以基於時間，基於改變與基於日誌序列的不完全恢復，基於日誌序列的恢復可以指定恢復到哪個日誌序列，如
run {
allocate channel ch1 type disk;
allocate channel ch2 type 'sbt_tape';
set until logseq 1234 thread 1;
restore controlfile to '$ORACLE_HOME/dbs/cf1.f' ;
replicate controlfile from '$ORACLE_HOME/dbs/cf1.f';
alter database mount;
restore database;
recover database;
sql "ALTER DATABASE OPEN RESETLOGS";
}
3、與所有的不完全恢復一樣，必須在mount下，restore所有備份資料檔案，需要resetlogs
4、基於改變的恢復比基於時間的恢復更可靠，但是可能也更復雜，需要知道需要恢復到哪一個改變號(SCN)，在正常生產中，獲取SCN的辦法其實也有很多，如查詢資料庫字典表(V$archived_log or v$log_history)，或分析歸檔與聯機日誌(logmnr)等。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/7319461/viewspace-1027112/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 【資料庫資料恢復】SAP資料庫資料恢復案例資料庫資料恢復
- 【資料庫資料恢復】Sql Server資料庫資料恢復案例資料庫資料恢復SQLServer
- 資料庫恢復案例大全資料庫
- 【資料庫資料恢復】Oracle資料庫誤truncate table的資料恢復案例資料庫資料恢復Oracle
- nbu恢復oracle資料庫案例Oracle資料庫
- 練習：資料庫恢復案例資料庫
- MySQL 資料庫增量資料恢復案例MySql資料庫資料恢復
- 【資料庫資料恢復】SqlServer資料庫無法讀取的資料恢復案例資料庫資料恢復SQLServer
- 【資料庫資料恢復】sql server資料庫連線失效的資料恢復案例資料庫資料恢復SQLServer
- 【資料庫資料恢復】linux系統下MYSQL資料庫資料恢復案例資料庫資料恢復LinuxMySql
- 【資料庫資料恢復】MongoDB資料庫檔案損壞的資料恢復案例資料庫資料恢復MongoDB
- 伺服器資料恢復—透過拼接資料庫碎片恢復SqlServer資料庫資料的資料恢復案例伺服器資料恢復資料庫SQLServer
- 【資料庫資料恢復】ASM磁碟組掉線的Oracle資料庫資料恢復案例資料庫資料恢復ASMOracle
- 【資料庫資料恢復】SQL Server資料庫磁碟空間不足的資料恢復案例資料庫資料恢復SQLServer
- 【資料庫資料恢復】MongoDB資料庫服務啟動失敗的資料恢復案例資料庫資料恢復MongoDB
- 資料庫資料恢復—附加資料庫錯誤823的SQL Server資料恢復案例資料庫資料恢復SQLServer
- 【資料庫資料恢復】ASM例項不能掛載的Oracle資料庫資料恢復案例資料庫資料恢復ASMOracle
- 【北亞資料恢復】sqlserver資料庫被加密的資料恢復案例分享資料恢復SQLServer資料庫加密
- 【資料庫資料恢復】EXT3檔案系統下MYSQL資料庫恢復案例資料庫資料恢復MySql
- 【資料庫資料恢復】斷電導致Oracle資料庫資料丟失的資料恢復案例資料庫資料恢復Oracle
- 【資料庫資料恢復】華為雲ECS網站伺服器mysql資料庫資料恢復案例資料庫資料恢復網站伺服器MySql
- Oracle資料庫的非常規恢復Oracle資料庫
- 【資料庫資料恢復】無法啟動MongoDB服務的資料恢復案例資料庫資料恢復MongoDB
- 【資料庫資料恢復】ORACLE常見資料災難&資料恢復可能性資料庫資料恢復Oracle
- 【資料庫資料恢復】突然斷電造成Syabse資料庫無法啟動的資料恢復案例資料庫資料恢復
- 【資料庫資料恢復】Oracle資料庫檔案出現壞塊報錯的資料恢復案例資料庫資料恢復Oracle
- 【oracle資料庫資料恢復】誤操作導致的資料庫誤刪除的資料恢復案例Oracle資料庫資料恢復
- 資料庫資料恢復—MongoDB資料庫檔案丟失，啟動報錯的資料恢復案例資料庫資料恢復MongoDB
- 資料庫資料恢復—無備份，binlog未開啟的Mysql資料庫資料恢復案例資料庫資料恢復MySql
- AUL恢復資料案例
- 【Vsan資料恢復】Vsan資料恢復案例資料恢復
- Vsan資料恢復—Vsan資料恢復案例資料恢復
- 【資料庫資料恢復】Oracle ASM例項無法掛載的資料恢復案例資料庫資料恢復OracleASM
- 資料庫資料恢復——Windows無法啟動MongoDB服務的資料恢復案例資料庫資料恢復WindowsMongoDB
- RMAN備份恢復典型案例——資料庫卡頓資料庫
- 虎年初十ORACLE資料庫恢復案例Oracle資料庫
- 使用netbackup進行資料庫恢復案例資料庫
- 寶塔資料庫恢復 mysql資料庫丟失恢復 mysql資料庫刪除庫恢復 寶塔mysql資料庫恢復資料庫MySql