Oracle備份/恢復(fù)案例02——文件恢復(fù)
?
一、非歸檔模式下的備份與恢復(fù)
備份方案：采用OS冷備份
1、連接數(shù)據(jù)庫并創(chuàng)建測試表
?
SQL*Plus: Release 9.2.0.1.0 - Production on Tue Jan 13 10:03:27 2009
Copyright (c) 1982, 2002, Oracle Corporation.? All rights reserved.
?
SQL> connect sys/sys 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、備份數(shù)據(jù)庫
SQL> @D:\test\coldbak.sql
3、再插入記錄
SQL> insert into test values(2);
1 row inserted
SQL> commit;
Commit complete
SQL> select * from test;
A
------------------------
1
2
4、關(guān)閉數(shù)據(jù)庫
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
5、毀壞一個或多個數(shù)據(jù)文件，如刪除user01.dbf
C:>del D:\ORACLE\ORADATA\DODO\USERS01.DBF
6、重新啟動數(shù)據(jù)庫，Oracle報錯
SQL> startup
ORACLE instance started.
Total System Global Area? 135338868 bytes
Fixed Size?????????????????? 453492 bytes
Variable Size???????????? 109051904 bytes
Database Buffers?????????? 25165824 bytes
Redo Buffers???????????????? 667648 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: 'D:\ORACLE\ORADATA\DODO\USERS01.DBF'
在報警文件中，會有更詳細的信息
Errors in file d:\oracle\admin\dodo\bdump\dodo_dbw0_3648.trc:
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: 'D:\ORACLE\ORADATA\DODO\USERS01.DBF'
ORA-27041: unable to open file
OSD-04002: 無法打開文件
O/S-Error: (OS 2) 系統(tǒng)找不到指定的文件。
7、拷貝備份復(fù)原到原來位置(restore過程)
C:>xcopy D:\DATABASE\BACK\USERS01.DBF D:\ORACLE\ORADATA\DODO/H/R/S
8、打開數(shù)據(jù)庫，檢查數(shù)據(jù)
SQL> alter database open;
Database altered.
SQL> select * from test;
A
------------------------
1
這里可以發(fā)現(xiàn)，數(shù)據(jù)庫恢復(fù)成功，但在備份之后與崩潰之前的數(shù)據(jù)丟失了。
?
說明：
1、非歸檔模式下的恢復(fù)方案可選性很小，一般情況下只能有一種恢復(fù)方式，就是數(shù)據(jù)庫的冷備份的完全恢復(fù)。僅僅需要拷貝原來的備份就可以(restore)，不需要recover。
2、這種情況下的恢復(fù)，可以完全恢復(fù)到備份的點上，但是可能是丟失數(shù)據(jù)的，在備份之后與崩潰之前的數(shù)據(jù)將全部丟失。
3、不管毀壞了多少數(shù)據(jù)文件或是聯(lián)機日志或是控制文件，都可以通過這個辦法恢復(fù)，因為這個恢復(fù)過程是Restore所有的冷備份文件，而這個備份點上的所有文件是一致的，與最新的數(shù)據(jù)庫沒有關(guān)系，就好比把數(shù)據(jù)庫又放到了一個以前的“點”上。
4、對于非歸檔模式下，最好的辦法就是采用OS的冷備份，建議不要用RMAN來作冷備份，效果不好，因為RMAN不備份聯(lián)機日志，restore不能根本解決問題。
5、如果沒有備份聯(lián)機日志，如RMAN的備份，就需要利用不完全恢復(fù)(until cancel)的方法來重新創(chuàng)建聯(lián)機日志文件。
?
?
?
二、歸檔模式下丟失或損壞一個數(shù)據(jù)文件
?
OS備份方案
?
在歸檔方式下?lián)p壞或丟失一個數(shù)據(jù)文件，如果存在相應(yīng)的備份與該備份以來的歸檔日志，恢復(fù)還是比較簡單的，可以作到盡量少的Down機時間，并能作到數(shù)據(jù)庫的完全恢復(fù)。
?
1、連接數(shù)據(jù)庫，創(chuàng)建測試表并插入記錄
SQL*Plus: Release 9.2.0.1.0 - Production on Tue Jan 13 10:03:27 2009
Copyright (c) 1982, 2002, Oracle Corporation.? All rights reserved.
?
SQL> connect sys/sys 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、備份數(shù)據(jù)庫
SQL> @D:\test\hotbak.sql
3、繼續(xù)在測試表中插入記錄
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、關(guān)閉數(shù)據(jù)庫，模擬丟失數(shù)據(jù)文件
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down
C:>del D:\ORACLE\ORADATA\DODO\USERS01.DBF
5、啟動數(shù)據(jù)庫錯誤，脫機該數(shù)據(jù)文件
SQL> startup
ORACLE instance started.
Total System Global Area? 135338868 bytes
Fixed Size?????????????????? 453492 bytes
Variable Size???????????? 109051904 bytes
Database Buffers?????????? 25165824 bytes
Redo Buffers???????????????? 667648 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: 'D:\ORACLE\ORADATA\DODO\USERS01.DBF'
還可以查看報警文件(見上一個恢復(fù)案例)或動態(tài)視圖v$recover_file
idle> select * from v$recover_file;
?
???? FILE# ONLINE? ONLINE_ ERROR?????????????????? CHANGE# TIME
---------- ------- ------- -------------------- ---------- ----------
???????? 6 ONLINE? ONLINE? FILE NOT FOUND??????????????? 0
脫機數(shù)據(jù)文件:
SQL> alter database datafile 6 offline drop;
Database altered.
6、打開數(shù)據(jù)庫，拷貝備份回來(restore)，恢復(fù)(recover)該數(shù)據(jù)文件，并聯(lián)機
?
打開數(shù)據(jù)庫：
SQL> alter database open;
Database altered.
拷貝備份從備份處
C:>xcopy D:\DATABASE\BACK\USERS01.DBF D:\ORACLE\ORADATA\DODO/H/R/S
恢復(fù)該數(shù)據(jù)文件
SQL> recover datafile 6;
Media recovery complete.
恢復(fù)成功，聯(lián)機該數(shù)據(jù)文件:
SQL> alter database datafile 6 online;
Database altered.
7、檢查數(shù)據(jù)庫的數(shù)據(jù)(完全恢復(fù))
SQL> select * from test;
A
--------------------------
1
2
?
說明：
?
1、采用熱備份，需要運行在歸檔模式下，可以實現(xiàn)數(shù)據(jù)庫的完全恢復(fù)，也就是說，從備份后到數(shù)據(jù)庫崩潰時的數(shù)據(jù)都不會丟失。
2、可以采用全備份數(shù)據(jù)庫的方式備份，對于特殊情況，也可以只備份特定的數(shù)據(jù)文件，如只備份用戶表空間(一般情況下對于某些寫特別頻繁的數(shù)據(jù)文件，可以單獨加大備份頻率)
3、如果在恢復(fù)過程中，發(fā)現(xiàn)損壞的是多個數(shù)據(jù)文件，即可以采用一個一個數(shù)據(jù)文件的恢復(fù)方法(第5步中需要對數(shù)據(jù)文件一一脫機，第6步中需要對數(shù)據(jù)文件分別恢復(fù))，也可以采用整個數(shù)據(jù)庫的恢復(fù)方法。
4、如果是系統(tǒng)表空間的損壞，不能采用此方法
?
RMAN備份方案
?
RMAN也可以進行聯(lián)機備份，而且備份與恢復(fù)方法將比OS備份更簡單可靠。
?
1、連接數(shù)據(jù)庫，創(chuàng)建測試表并插入記錄
SQL*Plus: Release 9.2.0.1.0 - Production on Tue Jan 13 10:03:27 2009
Copyright (c) 1982, 2002, Oracle Corporation.? All rights reserved.
?
SQL> connect sys/sys 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、備份數(shù)據(jù)庫表空間users
C:>rman
Recovery Manager: Release 9.2.0.1.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation.? All rights reserved.
RMAN> connect rcvcat rman/rman
connected to recovery catalog database
RMAN> connect target sys/sys
connected to target database: DODO (DBID=472976704)
RMAN> run{
2> allocate channel c1 type disk;
3> backup tag 'tsuser' format 'D:\DATABASE\RMAN\tsuser_%u_%s_%p';
4> tablespace users;
5> release channel c1;
6> }
allocated channel: c1
channel c1: sid=18 devtype=DISK
?
Starting backup at 2009-01-13
channel c1: starting full datafile backupset
channel c1: specifying datafile(s) in backupset
input datafile fno=00006 name=D:\ORACLE\ORADATA\DODO\USERS01.DBF
channel c1: starting piece 1 at 2009-01-13
channel c1: finished piece 1 at 2009-01-13
piece handle=D:\DATABASE\RMAN\TSUSER_1NK4MUKK_55_1 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:35
Finished backup at 2009-01-13
?
Starting Control File and SPFILE Autobackup at 2009-01-13
piece handle=D:\ORACLE\ORADATA\DODO\RMANBACK\CTL_C-472976704-20090113-01 comment=NONE
Finished Control File and SPFILE Autobackup at 2009-01-13
?
released channel: c1
3、繼續(xù)在測試表中插入記錄
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、關(guān)閉數(shù)據(jù)庫，模擬丟失數(shù)據(jù)文件
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down
C:>del D:\ORACLE\ORADATA\DODO\USERS01.DBF
5、啟動數(shù)據(jù)庫，檢查錯誤
SQL> startup
ORACLE instance started.
?
Total System Global Area? 135338868 bytes
Fixed Size?????????????????? 453492 bytes
Variable Size???????????? 109051904 bytes
Database Buffers?????????? 25165824 bytes
Redo Buffers???????????????? 667648 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: 'D:\ORACLE\ORADATA\DODO\USERS01.DBF'
6、先打開數(shù)據(jù)庫
SQL> alter database datafile 6 offline drop;
Database altered.
SQL> alter database open;
Database altered.
7、恢復(fù)該表空間
恢復(fù)腳本可以是恢復(fù)單個數(shù)據(jù)文件
run{
allocate channel c1 type disk;
restore datafile 3;
recover datafile 3;
sql 'alter database datafile 3 online';
release channel c1;
}
也可以是,恢復(fù)表空間
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 9.2.0.1.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation.? All rights reserved.
RMAN> connect rcvcat rman/rman
connected to recovery catalog database
RMAN> connect target sys/sys
connected to target database: DODO (DBID=472976704)
RMAN> run{
2> allocate channel c1 type disk;
3> restore datafile 6;
4> recover datafile 6;
5> sql 'alter database datafile 6 online';
6> release channel c1;
7> }
allocated channel: c1
channel c1: sid=18 devtype=DISK
?
Starting restore at 2009-01-13
?
channel c1: starting datafile backupset restore
channel c1: specifying datafile(s) to restore from backup set
restoring datafile 00006 to D:\ORACLE\ORADATA\DODO\USERS01.DBF
channel c1: restored backup piece 1
piece handle=D:\DATABASE\RMAN\TSUSER_1NK4MUKK_55_1 tag=TSUSER params=NULL
channel c1: restore complete
Finished restore at 2009-01-13
?
Starting recover at 2009-01-13
?
starting media recovery
media recovery complete
?
Finished recover at 2009-01-13
?
sql statement: alter database datafile 6 online
?
released channel: c1
8、檢查數(shù)據(jù)是否完整
SQL> select * from test;
A
--------------------------------
1
2
?
說明：
?
1、RMAN也可以實現(xiàn)單個表空間或數(shù)據(jù)文件的恢復(fù)，恢復(fù)過程可以在mount下或open方式下，如果在open方式下恢復(fù)，可以減少down機時間
2、如果損壞的是一個數(shù)據(jù)文件，建議offline并在open方式下恢復(fù)
3、這里可以看到，RMAN進行數(shù)據(jù)文件與表空間恢復(fù)的時候，代碼都比較簡單，而且能保證備份與恢復(fù)的可靠性，所以建議采用RMAN的備份與恢復(fù)
?
?