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)等。