一、非歸檔模式下的備份與恢復(fù)
?
備份方案：采用OS冷備份
1、連接數(shù)據(jù)庫(kù)并創(chuàng)建測(cè)試表
?
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ù)庫(kù)
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ù)庫(kù)
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
5、毀壞一個(gè)或多個(gè)數(shù)據(jù)文件，如刪除user01.dbf
C:>del D:\ORACLE\ORADATA\DODO\USERS01.DBF
6、重新啟動(dòng)數(shù)據(jù)庫(kù)，Oracle報(bào)錯(cuò)
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'
在報(bào)警文件中，會(huì)有更詳細(xì)的信息
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: 無(wú)法打開(kāi)文件
O/S-Error: (OS 2) 系統(tǒng)找不到指定的文件。
7、拷貝備份復(fù)原到原來(lái)位置(restore過(guò)程)
C:>xcopy D:\DATABASE\BACK\USERS01.DBF D:\ORACLE\ORADATA\DODO/H/R/S
8、打開(kāi)數(shù)據(jù)庫(kù)，檢查數(shù)據(jù)
SQL> alter database open;
Database altered.
SQL> select * from test;
A
------------------------
1
這里可以發(fā)現(xiàn)，數(shù)據(jù)庫(kù)恢復(fù)成功，但在備份之后與崩潰之前的數(shù)據(jù)丟失了。
?
說(shuō)明：
1、非歸檔模式下的恢復(fù)方案可選性很小，一般情況下只能有一種恢復(fù)方式，就是數(shù)據(jù)庫(kù)的冷備份的完全恢復(fù)。僅僅需要拷貝原來(lái)的備份就可以(restore)，不需要recover。
2、這種情況下的恢復(fù)，可以完全恢復(fù)到備份的點(diǎn)上，但是可能是丟失數(shù)據(jù)的，在備份之后與崩潰之前的數(shù)據(jù)將全部丟失。
3、不管毀壞了多少數(shù)據(jù)文件或是聯(lián)機(jī)日志或是控制文件，都可以通過(guò)這個(gè)辦法恢復(fù)，因?yàn)檫@個(gè)恢復(fù)過(guò)程是Restore所有的冷備份文件，而這個(gè)備份點(diǎn)上的所有文件是一致的，與最新的數(shù)據(jù)庫(kù)沒(méi)有關(guān)系，就好比把數(shù)據(jù)庫(kù)又放到了一個(gè)以前的“點(diǎn)”上。
4、對(duì)于非歸檔模式下，最好的辦法就是采用OS的冷備份，建議不要用RMAN來(lái)作冷備份，效果不好，因?yàn)?font color="#ff0000">RMAN不備份聯(lián)機(jī)日志，restore不能根本解決問(wèn)題。
5、如果沒(méi)有備份聯(lián)機(jī)日志，如RMAN的備份，就需要利用不完全恢復(fù)(until cancel)的方法來(lái)重新創(chuàng)建聯(lián)機(jī)日志文件。
?
?
?
二、歸檔模式下丟失或損壞一個(gè)數(shù)據(jù)文件
?
1.
OS備份方案
?
在歸檔方式下?lián)p壞或丟失一個(gè)數(shù)據(jù)文件，如果存在相應(yīng)的備份與該備份以來(lái)的歸檔日志，恢復(fù)還是比較簡(jiǎn)單的，可以作到盡量少的Down機(jī)時(shí)間，并能作到數(shù)據(jù)庫(kù)的完全恢復(fù)。
?
1、連接數(shù)據(jù)庫(kù)，創(chuàng)建測(cè)試表并插入記錄
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ù)庫(kù)
SQL> @D:\test\hotbak.sql
3、繼續(xù)在測(cè)試表中插入記錄
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ù)庫(kù)，模擬丟失數(shù)據(jù)文件
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down
C:>del D:\ORACLE\ORADATA\DODO\USERS01.DBF
5、啟動(dòng)數(shù)據(jù)庫(kù)錯(cuò)誤，脫機(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'
還可以查看報(bào)警文件(見(jiàn)上一個(gè)恢復(fù)案例)或動(dòng)態(tài)視圖v$recover_file
idle> select * from v$recover_file;
?
???? FILE# ONLINE? ONLINE_ ERROR?????????????????? CHANGE# TIME
---------- ------- ------- -------------------- ---------- ----------
???????? 6 ONLINE? ONLINE? FILE NOT FOUND??????????????? 0
脫機(jī)數(shù)據(jù)文件:
SQL> alter database datafile?6 offline drop;
Database altered.
6、打開(kāi)數(shù)據(jù)庫(kù)，拷貝備份回來(lái)(restore)，恢復(fù)(recover)該數(shù)據(jù)文件，并聯(lián)機(jī)
?
打開(kāi)數(shù)據(jù)庫(kù)：
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)機(jī)該數(shù)據(jù)文件:
SQL> alter database datafile?6 online;
Database altered.
7、檢查數(shù)據(jù)庫(kù)的數(shù)據(jù)(完全恢復(fù))
SQL> select * from test;
A
--------------------------
1
2
說(shuō)明：
?
1、采用熱備份，需要運(yùn)行在歸檔模式下，可以實(shí)現(xiàn)數(shù)據(jù)庫(kù)的完全恢復(fù)，也就是說(shuō)，從備份后到數(shù)據(jù)庫(kù)崩潰時(shí)的數(shù)據(jù)都不會(huì)丟失。
2、可以采用全備份數(shù)據(jù)庫(kù)的方式備份，對(duì)于特殊情況，也可以只備份特定的數(shù)據(jù)文件，如只備份用戶表空間(一般情況下對(duì)于某些寫特別頻繁的數(shù)據(jù)文件，可以單獨(dú)加大備份頻率)
3、如果在恢復(fù)過(guò)程中，發(fā)現(xiàn)損壞的是多個(gè)數(shù)據(jù)文件，即可以采用一個(gè)一個(gè)數(shù)據(jù)文件的恢復(fù)方法(第5步中需要對(duì)數(shù)據(jù)文件一一脫機(jī)，第6步中需要對(duì)數(shù)據(jù)文件分別恢復(fù))，也可以采用整個(gè)數(shù)據(jù)庫(kù)的恢復(fù)方法。
4、如果是系統(tǒng)表空間的損壞，不能采用此方法
?
2.
RMAN備份方案
?
RMAN也可以進(jìn)行聯(lián)機(jī)備份，而且備份與恢復(fù)方法將比OS備份更簡(jiǎn)單可靠。
?
1、連接數(shù)據(jù)庫(kù)，創(chuàng)建測(cè)試表并插入記錄
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ù)庫(kù)表空間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ù)在測(cè)試表中插入記錄
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ù)庫(kù)，模擬丟失數(shù)據(jù)文件
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down
C:>del D:\ORACLE\ORADATA\DODO\USERS01.DBF
5、啟動(dòng)數(shù)據(jù)庫(kù)，檢查錯(cuò)誤
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、先打開(kāi)數(shù)據(jù)庫(kù)
SQL> alter database datafile?6 offline drop;
Database altered.
SQL> alter database open;
Database altered.
7、恢復(fù)該表空間
恢復(fù)腳本可以是恢復(fù)單個(gè)數(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;
}
過(guò)程如下：
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
?
說(shuō)明：
?
1、RMAN也可以實(shí)現(xiàn)單個(gè)表空間或數(shù)據(jù)文件的恢復(fù)，恢復(fù)過(guò)程可以在mount下或open方式下，如果在open方式下恢復(fù)，可以減少down機(jī)時(shí)間
2、如果損壞的是一個(gè)數(shù)據(jù)文件，建議offline并在open方式下恢復(fù)
3、這里可以看到，RMAN進(jìn)行數(shù)據(jù)文件與表空間恢復(fù)的時(shí)候，代碼都比較簡(jiǎn)單，而且能保證備份與恢復(fù)的可靠性，所以建議采用RMAN的備份與恢復(fù)
?
?
-The End-