Oracle備份/恢復案例04——不完全恢復
?
一、OS備份下的基于時間的恢復
?
不完全恢復可以分為基于時間的恢復、基于改變的恢復、基于撤消的恢復，這里以基于時間的恢復為例子來說明不完全恢復過程。
基于時間的恢復：可以不完全恢復到現在時間之前的某一個時間，對于某些誤操作，如刪除了一個數據表，可以在備用恢復環境上恢復到表的刪除時間之前，然后把該表導出到正式環境，避免一個人為的錯誤。
1、連接數據庫，創建測試表并插入記錄
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、備份數據庫，這里最好備份所有的數據文件，包括臨時數據文件
SQL> @D:\test\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
-------------------
2009-01-14 08:41:11
SQL> drop table test;
Table dropped.
4、準備恢復到時間點T1，找回刪除的表，先關閉數據庫
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
5、拷貝剛才備份的所有數據文件回來
C:>copy D:\DATABASE\BACK\*.DBF D:\ORACLE\ORADATA\DODO
注：此處需要copy所有文件，因為會涉及到系統數據字典
6、啟動到mount下
SQL> startup mount;
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.
7、開始不完全恢復數據庫到T1時間
SQL> recover database until time 2009-01-14 08:41:11';
ORA-00279: change 5966020159195 generated at 01/13/2009 10:37:39 needed for thread 1
ORA-00289: suggestion : D:\ORACLE\ORADATA\DODO\ARCHIVE\1_39.DBF
ORA-00280: change 5966020159195 for thread 1 is in sequence #39
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
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、一般都是在測試機后備用機器上采用這種不完全恢復，恢復之后導出/導入被誤刪的表回生產系統
?
?
?
二、RMAN備份下的基于改變的恢復
?
以上用OS備份說明了一個基于時間的恢復，現在用RMAN說明一個基于改變的恢復
1、連接數據庫，創建測試表并插入記錄
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、備份數據庫
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 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> }
allocated channel: c1
channel c1: sid=18 devtype=DISK
?
Starting backup at 2009-01-14
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-14
channel c1: finished piece 1 at 2009-01-14
piece handle=D:\DATABASE\RMAN\FULL_1UK4PBLD_62_1 comment=NONE
channel c1: backup set complete, elapsed time: 00:02:35
Finished backup at 2009-01-14
?
Starting Control File and SPFILE Autobackup at 2009-01-14
piece handle=D:\ORACLE\ORADATA\DODO\RMANBACK\CTL_C-472976704-20090114-01 comment=NONE
Finished Control File and SPFILE Autobackup at 2009-01-14
?
sql statement: alter system archive log current
?
released channel: c1
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
---------------
? 5966020192446
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? 135338868 bytes
Fixed Size?????????????????? 453492 bytes
Variable Size???????????? 109051904 bytes
Database Buffers?????????? 25165824 bytes
Redo Buffers???????????????? 667648 bytes
Database mounted.
5、開始恢復到改變點SCN 5966020192446
RMAN> run{
2> allocate channel c1 type disk;
3> restore database;
4> recover database until scn 5966020192446;
5> sql 'ALTER DATABASE OPEN RESETLOGS';
6> release channel c1;
7> }
allocated channel: c1
channel c1: sid=12 devtype=DISK
?
Starting restore at 2009-01-14
?
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\FULL_1UK4PBLD_62_1 tag=DBFULL params=NULL
channel c1: restore complete
Finished restore at 2009-01-14
?
Starting recover at 2009-01-14
?
starting media recovery
?
archive log thread 1 sequence 55 is already on disk as file D:\ORACLE\ORADATA\DODO\ARCHIVE\1_55.DBF
archive log thread 1 sequence 56 is already on disk as file D:\ORACLE\ORADATA\DODO\ARCHIVE\1_56.DBF
archive log thread 1 sequence 57 is already on disk as file D:\ORACLE\ORADATA\DODO\ARCHIVE\1_57.DBF
archive log filename=D:\ORACLE\ORADATA\DODO\ARCHIVE\1_55.DBF thread=1 sequence=55
media recovery complete
Finished recover at 2009-01-14
?
sql statement: ALTER DATABASE OPEN RESETLOGS
?
released channel: c1
6、檢查數據
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)等。
?
?
?