oracle rman表空间基于时间点的不完全恢复
数据库版本：9204
目标数据库名称 paul
辅助实例 AUX1

1.备份数据库用带恢复目录的模式
C:\Documents and Settings\Paul Yi>rman target / catalog paultest/paultest@dev89
Recovery Manager: Release 9.2.0.4.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to target database: PAUL (DBID=1605165889)
connected to recovery catalog database
RMAN> backup database;
Starting backup at 02-JAN-08
starting full resync of recovery catalog
full resync complete
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=13 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=D:\ORACLE\ORADATA\PAUL\SYSTEM01.DBF
input datafile fno=00002 name=D:\ORACLE\ORADATA\PAUL\UNDOTBS01.DBF
input datafile fno=00003 name=D:\ORACLE\ORADATA\PAUL\INDX01.DBF
input datafile fno=00005 name=D:\ORACLE\ORADATA\PAUL\USERS01.DBF
input datafile fno=00004 name=D:\ORACLE\ORADATA\PAUL\TOOLS01.DBF
channel ORA_DISK_1: starting piece 1 at 02-JAN-08
channel ORA_DISK_1: finished piece 1 at 02-JAN-08
piece handle=D:\BACKUP\4BJ55C61_1_1 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:55
Finished backup at 02-JAN-08
Starting Control File and SPFILE Autobackup at 02-JAN-08
piece handle=D:\BACKUP\C-1605165889-20080102-09 comment=NONE
Finished Control File and SPFILE Autobackup at 02-JAN-08
RMAN>

2在paul数据库表空间ts_test下创建测试用户
C:\Documents and Settings\Paul Yi>sqlplus "/as sysdba"
SQL*Plus: Release 9.2.0.4.0 - Production on Wed Jan 2 14:15:45 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
sql> create tablespace ts_test datafile 'd:\oracle\oradata\paul\ts_test01.dbf' size 50M;
SQL> create user test identified by test default tablespace ts_test;
User created.
SQL> grant connect,resource to test;
Grant succeeded.
SQL> conn test/test
Connected.
SQL> create table stu(no number,name varchar2(10));
Table created.
SQL> insert into stu values(109,'abcd');
1 row created.
SQL> select * from stu;
NO NAME
---------- --------------------
109 abcd
SQL> commit;
Commit complete.
SQL> conn /as sysdba
Connected.
备份控制文件
SQL> alter database backup controlfile to 'd:\control02.ctl' reuse;
Database altered.
SQL>
SQL> conn test/test
Connected.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
STU TABLE
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
Session altered.
记住删除此表的时刻是
SQL> select sysdate from dual;
SYSDATE
-------------------
2008-01-02 15:14:08
SQL> drop table stu;
Table dropped.

3.为辅助实例创建初始化参数文件
新建 d:\oracle\oradata\aux1 目录
复制d:\oracle\ora92\database\INITpaul.ora 为initaux1.ora文件
然后修改其内容，将实例名字修改为：aux1, 添加lock_name_space=AUX1，修改control_files路径为：
control_files='d:\control02.ctl'
修改log_archive_start=false
辅助实例必须为非归档模式。
添加如下两个参数：
db_file_name_convert=("d:\oracle\oradata\paul", "d:\oracle\oradata\aux1")
log_file_name_convert=("d:\oracle\oradata\paul", "d:\oracle\oradata\aux1")

4. 创建辅助实例服务 用oradmin工具
C:\Documents and Settings\Paul Yi>oradim -new -sid aux1 -intpwd abcdefg -startmode manual

5.启动辅助实例到nomount状态
C:\Documents and Settings\Paul Yi>set oracle_sid=aux1
C:\Documents and Settings\Paul Yi>sqlplus "/as sysdba"
SQL*Plus: Release 9.2.0.4.0 - Production on Wed Jan 2 14:36:02 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to an idle instance.
SQL> create spfile from pfile;
file created
SQL> startup nomount
Total System Global Area 101785252 bytes
Fixed Size 454308 bytes
Variable Size 75497472 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes

6.开始表空间不完全恢复
C:\Documents and Settings\Paul Yi>set oracle_sid=aux1
C:\Documents and Settings\Paul Yi>rman target sys/abcdefg@paul catalog paultest/
paultest@dev89 auxiliary /
Recovery Manager: Release 9.2.0.4.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to target database: PAUL (DBID=1605165889)
connected to recovery catalog database
connected to auxiliary database: paul (not mounted)
RMAN> run{
2> allocate auxiliary channel a1 type disk;
3> allocate channel c1 type disk;
4> recover tablespace ts_test until time "to_date('2008-01-02 15:14:08','yyyy-mm
-dd hh24:mi:ss')";
5> }
allocated channel: a1
channel a1: sid=10 devtype=DISK
allocated channel: c1
channel c1: sid=9 devtype=DISK
Starting recover at 02-JAN-08
printing stored script. Memory Script
{
# set the until clause
set until time "to_date('2008-01-02 15:14:08','yyyy-mm-dd hh24:mi:ss')";
# restore the controlfile
restore clone controlfile to clone_cf;
# replicate the controlfile
replicate clone controlfile from clone_cf;
# mount the controlfile
sql clone 'alter database mount clone database';
# archive current online log for tspitr to a resent until time
sql 'alter system archive log current';
# avoid unnecessary autobackups for structural changes during TSPITR
sql 'begin dbms_backup_restore.AutoBackupFlag(FALSE); end;';
# resync catalog after controlfile restore
resync catalog;
}
executing script. Memory Script
executing command: SET until clause
Starting restore at 02-JAN-08
channel a1: starting datafile backupset restore
channel a1: restoring controlfile
output filename=D:\CONTROL02.CTL
channel a1: restored backup piece 1
piece handle=D:\BACKUP\C-1605165889-20080102-0D tag=null params=NULL
channel a1: restore complete
Finished restore at 02-JAN-08
replicating controlfile
input filename=D:\CONTROL02.CTL
sql statement: alter database mount clone database
sql statement: alter system archive log current
sql statement: begin dbms_backup_restore.AutoBackupFlag(FALSE); end;
starting full resync of recovery catalog
full resync complete
printing stored script. Memory Script
{
# generated tablespace point-in-time recovery script
# set the until clause
set until time "to_date('2008-01-02 15:14:08','yyyy-mm-dd hh24:mi:ss')";
plsql <<<-- tspitr_2 declare sqlstatement varchar2(512); offline_not_needed exception; pragma exception_init(offline_not_needed, -01539); begin sqlstatement := 'alter tablespace '|| 'TS_TEST' ||' offline for recover'; krmicd.writeMsg(6162, sqlstatement); krmicd.execSql(sqlstatement); exception when offline_not_needed then null; end; >>>;
# set a destination filename for restore
set newname for datafile 1 to
"D:\ORACLE\ORADATA\AUX1\SYSTEM01.DBF";
# set a destination filename for restore
set newname for datafile 2 to
"D:\ORACLE\ORADATA\AUX1\UNDOTBS01.DBF";
# set a destination filename for restore
set newname for datafile 6 to
"D:\ORACLE\ORADATA\PAUL\TS_TEST01.DBF";
# restore the tablespaces in the recovery set plus the auxilliary tablespaces
restore clone datafile 1, 2, 6;
switch clone datafile all;
#online the datafiles restored or flipped
sql clone "alter database datafile 1 online";
#online the datafiles restored or flipped
sql clone "alter database datafile 2 online";
#online the datafiles restored or flipped
sql clone "alter database datafile 6 online";
# make the controlfile point at the restored datafiles, then recover them
recover clone database tablespace "TS_TEST", "SYSTEM", "UNDOTBS1";
alter clone database open resetlogs;
# PLUG HERE the creation of a temporary tablespace if export fails due to lack
# of temporary space.
# For example in Unix these two lines would do that:
#sql clone "create tablespace aux_tspitr_tmp
# datafile ''/tmp/aux_tspitr_tmp.dbf'' size 500K";
}
executing script. Memory Script
executing command: SET until clause
sql statement: alter tablespace TS_TEST offline for recover
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 02-JAN-08
channel a1: starting datafile backupset restore
channel a1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to D:\ORACLE\ORADATA\AUX1\SYSTEM01.DBF
restoring datafile 00002 to D:\ORACLE\ORADATA\AUX1\UNDOTBS01.DBF
restoring datafile 00006 to D:\ORACLE\ORADATA\PAUL\TS_TEST01.DBF
channel a1: restored backup piece 1
piece handle=D:\BACKUP\4GJ55FO2_1_1 tag=TAG20080102T151058 params=NULL
channel a1: restore complete
Finished restore at 02-JAN-08
datafile 6 switched to datafile copy
input datafilecopy recid=33 stamp=642958383 filename=D:\ORACLE\ORADATA\PAUL\TS_T
EST01.DBF
sql statement: alter database datafile 1 online
sql statement: alter database datafile 2 online
sql statement: alter database datafile 6 online
Starting recover at 02-JAN-08
starting media recovery
archive log thread 1 sequence 206 is already on disk as file D:\ARCHPAUL\1_206.D
BF
archive log filename=D:\ARCHPAUL\1_206.DBF thread=1 sequence=206
media recovery complete
Finished recover at 02-JAN-08
database opened
printing stored script. Memory Script
{
# export the tablespaces in the recovery set
host 'exp userid =\"/@ as sysdba\" point_in_time_recover=y tablespaces=
TS_TEST file=
tspitr_a.dmp';
# shutdown clone before import
shutdown clone immediate
# import the tablespaces in the recovery set
host 'imp userid =\"sys/abcdefg@paul as sysdba\" point_in_time_recover=y file=
tspitr_a.dmp';
# online/offline the tablespace imported
sql "alter tablespace TS_TEST online";
sql "alter tablespace TS_TEST offline";
# enable autobackups in case user does open resetlogs from RMAN after TSPITR
sql 'begin dbms_backup_restore.AutoBackupFlag(TRUE); end;';
# resync catalog after tspitr finished
resync catalog;
}
executing script. Memory Script

Export: Release 9.2.0.4.0 - Production on Wed Jan 2 15:33:12 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
Note: table data (rows) will not be exported
About to export Tablespace Point-in-time Recovery objects...
For tablespace TS_TEST ...
. exporting cluster definitions
. exporting table definitions
. . exporting table STU
. exporting referential integrity constraints
. exporting triggers
. end point-in-time recovery
Export terminated successfully without warnings.
host command complete
database closed
database dismounted
Oracle instance shut down

Import: Release 9.2.0.4.0 - Production on Wed Jan 2 15:33:26 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
Export file created by EXPORT:V09.02.00 via conventional path
About to import Tablespace Point-in-time Recovery objects...
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing TEST's objects into TEST
. . importing table "STU"
. importing SYS's objects into SYS
Import terminated successfully without warnings.
host command complete
sql statement: alter tablespace TS_TEST online
sql statement: alter tablespace TS_TEST offline
sql statement: begin dbms_backup_restore.AutoBackupFlag(TRUE); end;
starting full resync of recovery catalog
full resync complete
Finished recover at 02-JAN-08
released channel: c1
RMAN>

C:\Documents and Settings\Paul Yi>sqlplus "/as sysdba"
SQL*Plus: Release 9.2.0.4.0 - Production on Wed Jan 2 15:35:58 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
SQL> alter tablespace ts_test online;
Tablespace altered.
SQL> conn test/test
Connected.
SQL> select * from stu;
NO NAME
---------- --------------------
109 abcd

至此，已经恢复