Oracle備份/恢復(fù)案例03——全庫恢復(fù)
?
一、
OS備份方案
OS備份歸檔模式下?lián)p壞(丟失)多個數(shù)據(jù)文件，進行整個數(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ù)庫，備份除臨時數(shù)據(jù)文件后的所數(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\SYSTEM01.DBF
C:>del D:\ORACLE\ORADATA\DODO\TOOLS01.DBF
C:>del D:\ORACLE\ORADATA\DODO\INDX01.DBF
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 1 - see DBWR trace file
ORA-01110: data file 1: 'D:\ORACLE\ORADATA\DODO\SYSTEM01.DBF'
詳細信息可以查看報警文件
Errors in file d:\oracle\admin\dodo\bdump\dodo_dbw0_2256.trc:
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: 'D:\ORACLE\ORADATA\DODO\SYSTEM01.DBF'
ORA-27041: unable to open file
OSD-04002: 無法打開文件
O/S-Error: (OS 2) 系統(tǒng)找不到指定的文件。
?
Tue Jan 13 16:58:04 2009
Errors in file d:\oracle\admin\dodo\bdump\dodo_dbw0_2256.trc:
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: 'D:\ORACLE\ORADATA\DODO\INDX01.DBF'
ORA-27041: unable to open file
OSD-04002: 無法打開文件
O/S-Error: (OS 2) 系統(tǒng)找不到指定的文件。
?
Tue Jan 13 16:58:04 2009
Errors in file d:\oracle\admin\dodo\bdump\dodo_dbw0_2256.trc:
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: 'D:\ORACLE\ORADATA\DODO\TOOLS01.DBF'
ORA-27041: unable to open file
OSD-04002: 無法打開文件
O/S-Error: (OS 2) 系統(tǒng)找不到指定的文件。
?
Tue Jan 13 16:58:04 2009
Errors in file d:\oracle\admin\dodo\bdump\dodo_dbw0_2256.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)找不到指定的文件。
通過查詢v$recover_file可以看到
SQL> select * from v$recover_file;
?
???? FILE# ONLINE? ONLINE_ ERROR?????????????????? CHANGE# TIME
---------- ------- ------- -------------------- ---------- ----------
???????? 1 ONLINE? ONLINE? FILE NOT FOUND??????????????? 0
???????? 4 ONLINE? ONLINE? FILE NOT FOUND??????????????? 0
???????? 5 ONLINE? ONLINE? FILE NOT FOUND??????????????? 0
???????? 6 ONLINE? ONLINE? FILE NOT FOUND??????????????? 0
有四個數(shù)據(jù)文件需要恢復(fù)
6、拷貝備份回到原地點(restore)，開始恢復(fù)數(shù)據(jù)庫(recover)
restore過程:
C:>copy D:\DATABASE\BACK\SYSTEM01.DBF D:\ORACLE\ORADATA\DODO
C:>copy D:\DATABASE\BACK\INDX01.DBF D:\ORACLE\ORADATA\DODO
C:>copy D:\DATABASE\BACK\TOOLS01.DBF D:\ORACLE\ORADATA\DODO
C:>copy D:\DATABASE\BACK\USERS01.DBF D:\ORACLE\ORADATA\DODO
?
Recover過程:
SQL> recover database;
Media recovery complete.
?
7、打開數(shù)據(jù)庫，檢查數(shù)據(jù)庫的數(shù)據(jù)(完全恢復(fù))
SQL> alter database open;
Database altered.
SQL> select * from test;
A
---------------------------------
1
2
?
說明：
1、只要有備份與歸檔存在，就可以實現(xiàn)數(shù)據(jù)庫的完全恢復(fù)(不丟失數(shù)據(jù))
2、適合于丟失大量數(shù)據(jù)文件，或包含系統(tǒng)數(shù)據(jù)文件在內(nèi)的數(shù)據(jù)庫的恢復(fù)
3、恢復(fù)過程在mount下進行，如果恢復(fù)成功，再打開數(shù)據(jù)庫，down機時間可能比較長一些。
?
二、RMAN備份方案
RMAN備份歸檔模式下?lián)p壞(丟失)多個數(shù)據(jù)文件，進行整個數(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ù)庫
C:> rman cmdfile=bakup.rcv msglog=backup.log;
backup.log中記錄的備份信息如下：
Recovery Manager: Release 9.2.0.1.0 - Production
?
Copyright (c) 1995, 2002, Oracle Corporation.? All rights reserved.
?
RMAN> # script:bakup.rcv
2> # desc:backup all database datafile in archive with rman
3> # connect database
4> # set oracle_sid=dodo;
5> connect target sys/sys;
6> # start backup database
7> run{
8> allocate channel c1 type disk;
9> backup full tag 'dbfull' format 'D:\DATABASE\RMAN\full%u_%s_%p' database
10> include current controlfile;
11> sql 'alter system archive log current';
12> release channel c1;
13> }
14> # end
15>
connected to target database: DODO (DBID=472976704)
?
using target database controlfile instead of recovery catalog
allocated channel: c1
channel c1: sid=17 devtype=DISK
?
Starting backup at 2009-01-13
channel c1: starting full datafile backupset
channel c1: specifying datafile(s) in backupset
including current controlfile in backupset
input datafile fno=00008 name=D:\ORACLE\ORADATA\DODO\WXQ_TBS.ORA
input datafile fno=00006 name=D:\ORACLE\ORADATA\DODO\USERS01.DBF
input datafile fno=00001 name=D:\ORACLE\ORADATA\DODO\SYSTEM01.DBF
input datafile fno=00002 name=D:\ORACLE\ORADATA\DODO\UNDOTBS01.DBF
input datafile fno=00007 name=D:\ORACLE\ORADATA\DODO\XDB01.DBF
input datafile fno=00004 name=D:\ORACLE\ORADATA\DODO\INDX01.DBF
input datafile fno=00003 name=D:\ORACLE\ORADATA\DODO\DRSYS01.DBF
input datafile fno=00009 name=D:\ORACLE\ORADATA\DODO\RECOVERY_TBS.ORA
input datafile fno=00005 name=D:\ORACLE\ORADATA\DODO\TOOLS01.DBF
channel c1: starting piece 1 at 2009-01-13
channel c1: finished piece 1 at 2009-01-13
piece handle=D:\DATABASE\RMAN\FULL1QK4NINF_58_1 comment=NONE
channel c1: backup set complete, elapsed time: 00:02:37
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-03 comment=NONE
Finished Control File and SPFILE Autobackup at 2009-01-13
?
sql statement: alter system archive log current
?
released channel: c1
?
Recovery Manager complete.
?
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\SYSTEM01.DBF
C:>del D:\ORACLE\ORADATA\DODO\TOOLS01.DBF
C:>del D:\ORACLE\ORADATA\DODO\INDX01.DBF
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 1 - see DBWR trace file
ORA-01110: data file 1: 'D:\ORACLE\ORADATA\DODO\SYSTEM01.DBF'
查詢v$recover_file
SQL> select * from v$recover_file;
?
???? FILE# ONLINE? ONLINE_ ERROR?????????????????? CHANGE# TIME
---------- ------- ------- -------------------- ---------- ----------
???????? 1 ONLINE? ONLINE? FILE NOT FOUND??????????????? 0
???????? 4 ONLINE? ONLINE? FILE NOT FOUND??????????????? 0
???????? 5 ONLINE? ONLINE? FILE NOT FOUND??????????????? 0
???????? 6 ONLINE? ONLINE? FILE NOT FOUND??????????????? 0
可以知道有四個數(shù)據(jù)文件需要恢復(fù)
6、利用RMAN進行恢復(fù)
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 database;
4> recover database;
5> sql 'alter database open';
6> release channel c1;
7> }
allocated channel: c1
channel c1: sid=12 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 00001 to D:\ORACLE\ORADATA\DODO\SYSTEM01.DBF
restoring datafile 00002 to D:\ORACLE\ORADATA\DODO\UNDOTBS01.DBF
restoring datafile 00003 to D:\ORACLE\ORADATA\DODO\DRSYS01.DBF
restoring datafile 00004 to D:\ORACLE\ORADATA\DODO\INDX01.DBF
restoring datafile 00005 to D:\ORACLE\ORADATA\DODO\TOOLS01.DBF
restoring datafile 00006 to D:\ORACLE\ORADATA\DODO\USERS01.DBF
restoring datafile 00007 to D:\ORACLE\ORADATA\DODO\XDB01.DBF
restoring datafile 00008 to D:\ORACLE\ORADATA\DODO\WXQ_TBS.ORA
restoring datafile 00009 to D:\ORACLE\ORADATA\DODO\RECOVERY_TBS.ORA
channel c1: restored backup piece 1
piece handle=D:\DATABASE\RMAN\FULL1QK4NINF_58_1 tag=DBFULL params=NULL
channel c1: restore complete
Finished restore at 2009-01-13
?
Starting recover at 2009-01-13
?
starting media recovery
?
archive log thread 1 sequence 43 is already on disk as file D:\ORACLE\ORADATA\DODO\ARCHIVE\1_43.DBF
archive log thread 1 sequence 44 is already on disk as file D:\ORACLE\ORADATA\DODO\ARCHIVE\1_44.DBF
archive log thread 1 sequence 45 is already on disk as file D:\ORACLE\ORADATA\DODO\ARCHIVE\1_45.DBF
archive log filename=D:\ORACLE\ORADATA\DODO\ARCHIVE\1_43.DBF thread=1 sequence=43
media recovery complete
Finished recover at 2009-01-13
?
sql statement: alter database open
?
released channel: c1
7、檢查數(shù)據(jù)庫的數(shù)據(jù)(完全恢復(fù))
?
SQL> select * from test;
A
--------------------------
1
2
?
?
說明：
?
1、只要有備份與歸檔存在，RMAN也可以實現(xiàn)數(shù)據(jù)庫的完全恢復(fù)(不丟失數(shù)據(jù))
2、同OS備份數(shù)據(jù)庫恢復(fù)，適合于丟失大量數(shù)據(jù)文件，或包含系統(tǒng)數(shù)據(jù)文件在內(nèi)的數(shù)據(jù)庫的恢復(fù)
3、目標數(shù)據(jù)庫在mount下進行，如果恢復(fù)成功，再打開數(shù)據(jù)庫。
4、RMAN的備份與恢復(fù)命令相對比較簡單并可靠，建議有條件的話，都采用RMAN進行數(shù)據(jù)庫的備份。
?