手动的表空间时间点恢复实验：
思想：通过辅助数据库，将目标数据库的部分文件以时间点恢复的方式恢复到辅助数据库，然后从辅助数据库中取出待恢复表空间的元数据，导入到目标数据库，以完成目标数据库的时间点恢复操作。
本实验其实就是目标数据库待恢复表空间在数据库中元数据的替换过程，怎么获得元数据，是本次试验的关键点，也是实验的主要步骤。
遇到的错误问题及解决：
1、 在试验中，必须先创建有效的备份，即备份完成时间必须要在恢复时间点之前。
2、 在Linux系统中，进行exp操作时，’sys/password@xxx as sysdba’ 必须使用转移字符 ’\’ 改写成：\’sys/password@xxx as sysdba\’，否则会报as sysdba格式的错误。
3、 在使得待恢复表空间联机之前，必须要关闭辅助数据库，否则会出现类似无法识别文件头的错误
具体实验步骤如下：
1、以sysdba的身份登录数据库，查看将要恢复的表空间的，检查其是否与其他表空间存在关联，或者存在不支持时间点恢复的对象。如果有，则需要进行相应的修复，例如导入导出、转移到别的表空间，或者将引用的对象转移到恢复表空间等等。
[oracle@RAC1 ~]$ sqlplus
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Feb 9 10:53:15 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
IDEL > conn /as sysdba
Connected to an idle instance.
IDEL > startup
ORACLE instance started.
Total System Global Area 134217728 bytes
Fixed Size 1218148 bytes
Variable Size 62917020 bytes
Database Buffers 67108864 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
IDEL > desc ts_pitr_check
Name Null? Type
----------------------------------------------------------------- -------- --------------------------------------------
OBJ1_OWNER VARCHAR2(30)
OBJ1_NA VARCHAR2(30)
OBJ1_SUBNAME VARCHAR2(30)
OBJ1_TYPE VARCHAR2(16)
TS1_NAME VARCHAR2(30)
OBJ2_NAME VARCHAR2(30)
OBJ2_SUBNAME VARCHAR2(30)
OBJ2_TYPE VARCHAR2(15)
OBJ2_OWNER VARCHAR2(30)
TS2_NAME VARCHAR2(30)
CONSTRAINT_NAME VARCHAR2(30)
REASON VARCHAR2(81)
如果下面的查询返回值是大于0的数字，则需要一些特殊的操作了，此处不需要。
IDEL > select count(*) from ts_pitr_check where ts1_name='USERS' or ts2_name='USERS';
COUNT(*)
----------
0
2、检查恢复操作将要影响到的对象，如果有在恢复时间点之后创建的比较重要的表，那么就需要导出或者做一些转移的操作，在此可以根据creation_time列与恢复时间点比较，并作出比较。
IDEL > desc ts_pitr_objects_to_be_dropped
Name Null? Type
----------------------------------------------------------------- -------- --------------------------------------------
OWNER NOT NULL VARCHAR2(30)
NAME NOT NULL VARCHAR2(30)
CREATION_TIME NOT NULL DATE
TABLESPACE_NAME VARCHAR2(30)
IDEL > select * from ts_pitr_objects_to_be_dropped where tablespace_name='USERS';
OWNER NAME CREATION_ TABLESPACE_NAME
------------------------------ ------------------------------ --------- ------------------------------
HONGYE BIN$myEaviBQtCXgQAB/AQA28A==$0 31-JAN-11 USERS
HONGYE T 31-JAN-11 USERS
IDEL > conn hongye/hongye
Connected.
HONGYE:159@HONGYE2 > purge recyclebin;
Recyclebin purged.
HONGYE:159@HONGYE2 > select table_name from user_tables;
TABLE_NAME
------------------------------
T
TT
3、构建本次试验的测试数据，选择当前的SCN为即将进行的时间点恢复的时间点，则在此之前所创建的对象都不会受到影响。此次，在t表中插入一些测试数据，并truncate表t，并删除tt表，然后做时间点恢复，如果成功了话，那么期望的结果是：t表中数据已经找回，且tt表还依然存在。
HONGYE:143@HONGYE2 > truncate table t
2 ;
Table truncated.
HONGYE:143@HONGYE2 > insert into t values(1,'name1');
1 row created.
HONGYE:143@HONGYE2 > insert into t values(2,'name2');
1 row created.
HONGYE:143@HONGYE2 > insert into t values(2,'name3');
1 row created.
HONGYE:143@HONGYE2 > insert into t values(3,'name3');
1 row created.
HONGYE:143@HONGYE2 > commit
2 ;
Commit complete.
HONGYE:143@HONGYE2 > select * from t;
ID NAME
---------- ----------
1 name1
2 name2
2 name3
3 name3
获取恢复时间点，此处的SCN非常重要，是后续恢复操作截止时间的参照。
HONGYE:143@HONGYE2 > select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
382237
HONGYE:143@HONGYE2 > truncate table t;
Table truncated.
HONGYE:143@HONGYE2 > drop table tt purge;
Table dropped.
HONGYE:143@HONGYE2 > exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
4、创建辅助实例，参数可以使用从别处拷贝的pfile进行一些修改，注意的是一定要使用和目标数据库一样的db_name，但是据说会有内存使用方面的问题，但是在本次实验中，我没有遇到这类的问题。
可以使用不同的db_unique_name。
可以在SQL*PLUS或者RMAN中将辅助实例启动到nomount状态。
此处属于基础内容，实验记录就不用保留了。
5、连接目标数据库和辅助数据库，本次实验中，由于我配置了与辅助实例相关的网络文件，所以可以将环境变量中的ORACLE_SID=，但是如果没有配置的话，则可以将ORACLE_SID=，前提是必须有目标数据库的网络配置。总之一句话：在目标数据库和辅助数据库中，至少有一个已经配置了正确的网络文件。
[oracle@RAC1 dbs]$ rman
Recovery Manager: Release 10.2.0.1.0 - Production on Wed Feb 9 11:16:20 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
RMAN> connect target /
connected to target database: HONGYE2 (DBID=860418810)
RMAN> connect auxiliary sys/hongye50@auxiliary
connected to auxiliary database: HONGYE2 (not mounted)
6、恢复辅助数据库的控制文件，
从主数据库的控制文件自动备份中恢复，此时切记：一定要设置好正确的恢复时间点，本次的实验中是以SCN为界的。
在恢复完辅助数据库的控制问件之后就可以mount辅助数据库了。
RMAN> run{
2> set until scn 382237;
3> restore clone controlfile;
4> sql clone 'alter database mount clone database';
5> sql 'alter system archive log current';
6> }
executing command: SET until clause
Starting restore at 09-FEB-11
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /oracle/flash_recovery_area/HONGYE2/autobackup/2011_02_09/o1_mf_s_742647952_6o414k9x_.bkp
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/oracle/flash_recovery_area/HONGYE2/autobackup/2011_02_09/o1_mf_s_742647952_6o414k9x_.bkp tag=TAG20110209T110552
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
output filename=/oracle/rmantest/auxiliary/control01.ctl
Finished restore at 09-FEB-11
sql statement: alter database mount clone database
sql statement: alter system archive log current
RMAN> exit
Recovery Manager complete.
7、以sysdba进入数据库，查看system表空间、undo表空间、temp表空间的所有相关文件号，还要查看将要做时间点恢复的表空间的文件名，这些信息将在后续操作中被用到。
将要进行时间点恢复的表空间离线。
[oracle@RAC1 dbs]$ sqlplus
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Feb 9 11:20:00 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
IDEL > conn / as sysdba
Connected.
SYS:143@HONGYE2 > desc dba_data_files
Name Null? Type
----------------------------------------------------------------- -------- --------------------------------------------
FILE_NAME VARCHAR2(513)
FILE_ID NUMBER
TABLESPACE_NAME VARCHAR2(30)
BYTES NUMBER
BLOCKS NUMBER
STATUS VARCHAR2(9)
RELATIVE_FNO NUMBER
AUTOEXTENSIBLE VARCHAR2(3)
MAXBYTES NUMBER
MAXBLOCKS NUMBER
INCREMENT_BY NUMBER
USER_BYTES NUMBER
USER_BLOCKS NUMBER
ONLINE_STATUS VARCHAR2(7)
SYS:143@HONGYE2 > select file_id,tablespace_name from dba_data_files;
FILE_ID TABLESPACE_NAME
---------- ------------------------------
1 SYSTEM
2 UNDOTBS1
3 SYSAUX
4 USERS
SYS:143@HONGYE2 > select file_name from dba_data_files where file_id=4;
FILE_NAME
------------------------------------------------------------------------------------------------------------------------
/oracle/oradata/hongye2/users01.dbf
SYS:143@HONGYE2 > select file_id,tablespace_name from dba_temp_files;
FILE_ID TABLESPACE_NAME
---------- ------------------------------
1 TEMP
将要恢复的表空间离线，等待恢复，如果在之前已经离线了，则不需要此步骤。
SYS:143@HONGYE2 > alter tablespace users offline for recover;
Tablespace altered.
SYS:143@HONGYE2 > exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@RAC1 dbs]$ rman
Recovery Manager: Release 10.2.0.1.0 - Production on Wed Feb 9 11:22:48 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
RMAN> connect target /
connected to target database: HONGYE2 (DBID=860418810)
RMAN> connect auxiliary sys/hongye50@auxiliary
connected to auxiliary database: HONGYE2 (DBID=860418810, not open)
8、执行辅助数据库的时间点恢复，注意设置不同文件的新文件名，其中system、undo、temp等表空间需要新的文件名，而已经离线的待恢复表空间则不需要，使用set newname来实现此重命名操作，还可以在init文件中使用db_file_name_convert参数进行转化。
切记：时间点的设置，一定要准确且和前面使用的一致。
恢复完成后，将辅助数据库的相关文件联机。
RMAN> run{
2> set until scn 382237;
3> set newname for datafile 4 to '/oracle/oradata/hongye2/users01.dbf';
4> set newname for clone tempfile 1 to new;
5> set newname for clone datafile 1 to new;
6> set newname for clone datafile 2 to new;
7> switch clone tempfile all;
8> restore clone datafile 1,2,4;
9> switch clone datafile all;
10> sql clone 'alter database datafile 1 online';
11> sql clone 'alter database datafile 2 online';
12> sql clone 'alter database datafile 4 online';
13> }
executing command: SET until clause
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
renamed temporary file 1 to /oracle/rmantest/auxiliary/AUXILIARY/datafile/o1_mf_temp_%u_.tmp in control file
Starting restore at 09-FEB-11
using channel ORA_AUX_DISK_1
skipping datafile 4; already restored to file /oracle/oradata/hongye2/users01.dbf
channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /oracle/rmantest/auxiliary/AUXILIARY/datafile/o1_mf_system_%u_.dbf
restoring datafile 00002 to /oracle/rmantest/auxiliary/AUXILIARY/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /oracle/flash_recovery_area/HONGYE2/backupset/2011_02_09/o1_mf_nnndf_TAG20110209T110503_6o41306k_.bkp
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/oracle/flash_recovery_area/HONGYE2/backupset/2011_02_09/o1_mf_nnndf_TAG20110209T110503_6o41306k_.bkp tag=TAG20110209T110503
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:26
Finished restore at 09-FEB-11
datafile 1 switched to datafile copy
input datafile copy recid=8 stamp=742649583 filename=/oracle/rmantest/auxiliary/AUXILIARY/datafile/o1_mf_system_6o42por8_.dbf
datafile 2 switched to datafile copy
input datafile copy recid=9 stamp=742649583 filename=/oracle/rmantest/auxiliary/AUXILIARY/datafile/o1_mf_undotbs1_6o42posj_.dbf
sql statement: alter database datafile 1 online
sql statement: alter database datafile 2 online
sql statement: alter database datafile 4 online
9、执行辅助数据库的介质恢复操作，确保将辅助实例恢复到指定的时间。
切记：时间点的设置，准确、一致。
介质恢复完成后，由于是不完全恢复，所以要使用resetlogs方式打开数据库。
RMAN> run{
2> set until scn 382237;
3> recover clone database tablespace "SYSTEM","UNDOTBS1","USERS" delete archivelog;
4> alter clone database open resetlogs;
5> }
executing command: SET until clause
Starting recover at 09-FEB-11
using channel ORA_AUX_DISK_1
starting media recovery
archive log thread 1 sequence 8 is already on disk as file /oracle/flash_recovery_area/HONGYE2/archivelog/2011_02_09/o1_mf_1_8_6o41yn3w_.arc
channel ORA_AUX_DISK_1: starting archive log restore to default destination
channel ORA_AUX_DISK_1: restoring archive log
archive log thread=1 sequence=7
channel ORA_AUX_DISK_1: reading from backup piece /oracle/flash_recovery_area/HONGYE2/backupset/2011_02_09/o1_mf_annnn_TAG20110209T110549_6o414h3y_.bkp
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/oracle/flash_recovery_area/HONGYE2/backupset/2011_02_09/o1_mf_annnn_TAG20110209T110549_6o414h3y_.bkp tag=TAG20110209T110549
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
archive log filename=/oracle/product/10.2.0/db_1/dbs/arch1_7_742402857.dbf thread=1 sequence=7
channel clone_default: deleting archive log(s)
archive log filename=/oracle/product/10.2.0/db_1/dbs/arch1_7_742402857.dbf recid=8 stamp=742649698
archive log filename=/oracle/flash_recovery_area/HONGYE2/archivelog/2011_02_09/o1_mf_1_8_6o41yn3w_.arc thread=1 sequence=8
media recovery complete, elapsed time: 00:00:04
Finished recover at 09-FEB-11
database opened
RMAN> exit
Recovery Manager complete.
10、实验接近尾声了，将辅助数据库中和待恢复表空间相关的源数据导出，指定exp中的关键字point_in_time_recover=y，此处需要注意的是Linux系统环境下单引号的转义问题，否则导出不会成功。
在进行导出之前，也可以进入辅助数据库中查看一下相关表空间是否恢复到了期望值，要确保ORACLE_SID=。
[oracle@RAC1 auxiliary]$ sqlplus
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Feb 9 11:41:18 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
IDEL > conn /as sysdba
Connected.
SYS:34@HONGYE2 > select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
UNDOTBS1 ONLINE
SYSAUX OFFLINE
TEMP ONLINE
USERS READ ONLY
SYS:34@HONGYE2 > select table_name from dba_tables where tablespace_name='USERS';
TABLE_NAME
------------------------------
T
TT
SYS:34@HONGYE2 > select * from hongye.t;
ID NAME
---------- ----------
1 name1
2 name2
2 name3
3 name3
SYS:34@HONGYE2 > select * from hongye.tt;
ID NAME
---------- ---------------
6 count(*) from t
SYS:34@HONGYE2 > shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS:34@HONGYE2 > exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
进行源数据的导出工作，要确保ORACLE_SID=。
[oracle@RAC1 dbs]$ exp \'sys/hongye50 as sysdba\' point_in_time_recover=y tablespaces=users file=/oracle/rmantest/auxiliary/users_pitr.dmp
Export: Release 10.2.0.1.0 - Production on Wed Feb 9 11:36:32 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)
Note: table data (rows) will not be exported
About to export Tablespace Point-in-time Recovery objects...
For tablespace USERS ...
. exporting cluster definitions
. exporting table definitions
. . exporting table TT
. . exporting table T
EXP-00091: Exporting questionable statistics.
. exporting referential integrity constraints
. exporting triggers
. end point-in-time recovery
Export terminated successfully with warnings.
导出成功了，可以看到目录下的导出文件users_pitr.dmp
[oracle@RAC1 dbs]$ cd /oracle/rmantest/auxiliary/
[oracle@RAC1 auxiliary]$ ls
adump AUXILIARY bdump cdump control01.ctl pfile.ora redo01.log redo02.log redo03.log udump users_pitr.dmp
导出完成后，要关闭辅助数据库，否则目标数据库的待恢复表空间将无法联机，因为辅助数据库和目标数据库对于待恢复的表空间，实际上使用的是同一个数据文件，如果不关闭，会有类似以下两个错误，因为同一个数据文件，不能同时被两个数据库打开。
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '/oracle/oradata/hongye2/users01.dbf'
[oracle@RAC1 auxiliary]$ sqlplus
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Feb 9 11:41:18 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
IDEL > conn /as sysdba
Connected.
SYS:34@HONGYE2 > shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS:34@HONGYE2 > exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
11、将第10步中得到了源数据导出文件导入到目标数据库，同样需要制定参数point_in_time_recover=y。
这其中，一定要记得改变系统变量ORACLE_SID=
[oracle@RAC1 auxiliary]$ export ORACLE_SID=hongye2
[oracle@RAC1 auxiliary]$ imp \'sys/hongye50 as sysdba\' point_in_time_recover=y file=/oracle/rmantest/auxiliary/users_pitr.dmp
Import: Release 10.2.0.1.0 - Production on Wed Feb 9 11:39:43 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export file created by EXPORT:V10.02.01 via conventional path
About to import Tablespace Point-in-time Recovery objects...
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P1 character set (possible charset conversion)
. importing SYS's objects into SYS
. importing HONGYE's objects into HONGYE
. . importing table "TT"
. . importing table "T"
. importing SYS's objects into SYS
Import terminated successfully without warnings.
12、结尾，在目标数据库SQL*PLUS中，使得待恢复的表空间联机，则表空间的时间点恢复完成，可以检查一下相关的数据，不过在前面其实已经检查过了，其实辅助数据库和目标数据库的users表空间的数据文件是同一个文件。
[oracle@RAC1 auxiliary]$ sqlplus
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Feb 9 11:40:00 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
IDEL > conn /as sysdba
Connected.
SYS:143@HONGYE
SYS:143@HONGYE2 > desc dba_tablespaces
Name Null? Type
----------------------------------------------------------------- -------- --------------------------------------------
TABLESPACE_NAME NOT NULL VARCHAR2(30)
BLOCK_SIZE NOT NULL NUMBER
INITIAL_EXTENT NUMBER
NEXT_EXTENT NUMBER
MIN_EXTENTS NOT NULL NUMBER
MAX_EXTENTS NUMBER
PCT_INCREASE NUMBER
MIN_EXTLEN NUMBER
STATUS VARCHAR2(9)
CONTENTS VARCHAR2(9)
LOGGING VARCHAR2(9)
FORCE_LOGGING VARCHAR2(3)
EXTENT_MANAGEMENT VARCHAR2(10)
ALLOCATION_TYPE VARCHAR2(9)
PLUGGED_IN VARCHAR2(3)
SEGMENT_SPACE_MANAGEMENT VARCHAR2(6)
DEF_TAB_COMPRESSION VARCHAR2(8)
RETENTION VARCHAR2(11)
BIGFILE VARCHAR2(3)
SYS:146@HONGYE2 > select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
UNDOTBS1 ONLINE
SYSAUX ONLINE
TEMP ONLINE
USERS OFFLINE
SYS:146@HONGYE2 > alter tablespace users online;
Tablespace altered.
SYS:146@HONGYE2 > conn hongye/hongye
Connected.
HONGYE:146@HONGYE2 > select table_name from user_tables;
TABLE_NAME
------------------------------
T
TT
HONGYE:146@HONGYE2 > select * from t;
ID NAME
---------- ----------
1 name1
2 name2
2 name3
3 name3
HONGYE:146@HONGYE2 > select * from tt;
ID NAME
---------- ---------------
6 count(*) from t
HONGYE:146@HONGYE2 >
最后，自己手动删除辅助实例和辅助数据库。
总结：本次实验纯属个人研究，实际上自动的表空间时间点恢复是一个很简单的操作命令就可以完成此次实验的所有步骤了，但是为了能够清晰的理解时间点恢复的具体流程，还是手动来一遍印象比较深刻，更能够加深自己的理解。同时，针对实验过程中问题的解决，也是一次很大的收获。
思想：通过辅助数据库，将目标数据库的部分文件以时间点恢复的方式恢复到辅助数据库，然后从辅助数据库中取出待恢复表空间的元数据，导入到目标数据库，以完成目标数据库的时间点恢复操作。
本实验其实就是目标数据库待恢复表空间在数据库中元数据的替换过程，怎么获得元数据，是本次试验的关键点，也是实验的主要步骤。
遇到的错误问题及解决：
1、 在试验中，必须先创建有效的备份，即备份完成时间必须要在恢复时间点之前。
2、 在Linux系统中，进行exp操作时，’sys/password@xxx as sysdba’ 必须使用转移字符 ’\’ 改写成：\’sys/password@xxx as sysdba\’，否则会报as sysdba格式的错误。
3、 在使得待恢复表空间联机之前，必须要关闭辅助数据库，否则会出现类似无法识别文件头的错误
具体实验步骤如下：
1、以sysdba的身份登录数据库，查看将要恢复的表空间的，检查其是否与其他表空间存在关联，或者存在不支持时间点恢复的对象。如果有，则需要进行相应的修复，例如导入导出、转移到别的表空间，或者将引用的对象转移到恢复表空间等等。
[oracle@RAC1 ~]$ sqlplus
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Feb 9 10:53:15 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
IDEL > conn /as sysdba
Connected to an idle instance.
IDEL > startup
ORACLE instance started.
Total System Global Area 134217728 bytes
Fixed Size 1218148 bytes
Variable Size 62917020 bytes
Database Buffers 67108864 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
IDEL > desc ts_pitr_check
Name Null? Type
----------------------------------------------------------------- -------- --------------------------------------------
OBJ1_OWNER VARCHAR2(30)
OBJ1_NA VARCHAR2(30)
OBJ1_SUBNAME VARCHAR2(30)
OBJ1_TYPE VARCHAR2(16)
TS1_NAME VARCHAR2(30)
OBJ2_NAME VARCHAR2(30)
OBJ2_SUBNAME VARCHAR2(30)
OBJ2_TYPE VARCHAR2(15)
OBJ2_OWNER VARCHAR2(30)
TS2_NAME VARCHAR2(30)
CONSTRAINT_NAME VARCHAR2(30)
REASON VARCHAR2(81)
如果下面的查询返回值是大于0的数字，则需要一些特殊的操作了，此处不需要。
IDEL > select count(*) from ts_pitr_check where ts1_name='USERS' or ts2_name='USERS';
COUNT(*)
----------
0
2、检查恢复操作将要影响到的对象，如果有在恢复时间点之后创建的比较重要的表，那么就需要导出或者做一些转移的操作，在此可以根据creation_time列与恢复时间点比较，并作出比较。
IDEL > desc ts_pitr_objects_to_be_dropped
Name Null? Type
----------------------------------------------------------------- -------- --------------------------------------------
OWNER NOT NULL VARCHAR2(30)
NAME NOT NULL VARCHAR2(30)
CREATION_TIME NOT NULL DATE
TABLESPACE_NAME VARCHAR2(30)
IDEL > select * from ts_pitr_objects_to_be_dropped where tablespace_name='USERS';
OWNER NAME CREATION_ TABLESPACE_NAME
------------------------------ ------------------------------ --------- ------------------------------
HONGYE BIN$myEaviBQtCXgQAB/AQA28A==$0 31-JAN-11 USERS
HONGYE T 31-JAN-11 USERS
IDEL > conn hongye/hongye
Connected.
HONGYE:159@HONGYE2 > purge recyclebin;
Recyclebin purged.
HONGYE:159@HONGYE2 > select table_name from user_tables;
TABLE_NAME
------------------------------
T
TT
3、构建本次试验的测试数据，选择当前的SCN为即将进行的时间点恢复的时间点，则在此之前所创建的对象都不会受到影响。此次，在t表中插入一些测试数据，并truncate表t，并删除tt表，然后做时间点恢复，如果成功了话，那么期望的结果是：t表中数据已经找回，且tt表还依然存在。
HONGYE:143@HONGYE2 > truncate table t
2 ;
Table truncated.
HONGYE:143@HONGYE2 > insert into t values(1,'name1');
1 row created.
HONGYE:143@HONGYE2 > insert into t values(2,'name2');
1 row created.
HONGYE:143@HONGYE2 > insert into t values(2,'name3');
1 row created.
HONGYE:143@HONGYE2 > insert into t values(3,'name3');
1 row created.
HONGYE:143@HONGYE2 > commit
2 ;
Commit complete.
HONGYE:143@HONGYE2 > select * from t;
ID NAME
---------- ----------
1 name1
2 name2
2 name3
3 name3
获取恢复时间点，此处的SCN非常重要，是后续恢复操作截止时间的参照。
HONGYE:143@HONGYE2 > select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
382237
HONGYE:143@HONGYE2 > truncate table t;
Table truncated.
HONGYE:143@HONGYE2 > drop table tt purge;
Table dropped.
HONGYE:143@HONGYE2 > exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
4、创建辅助实例，参数可以使用从别处拷贝的pfile进行一些修改，注意的是一定要使用和目标数据库一样的db_name，但是据说会有内存使用方面的问题，但是在本次实验中，我没有遇到这类的问题。
可以使用不同的db_unique_name。
可以在SQL*PLUS或者RMAN中将辅助实例启动到nomount状态。
此处属于基础内容，实验记录就不用保留了。
5、连接目标数据库和辅助数据库，本次实验中，由于我配置了与辅助实例相关的网络文件，所以可以将环境变量中的ORACLE_SID=，但是如果没有配置的话，则可以将ORACLE_SID=，前提是必须有目标数据库的网络配置。总之一句话：在目标数据库和辅助数据库中，至少有一个已经配置了正确的网络文件。
[oracle@RAC1 dbs]$ rman
Recovery Manager: Release 10.2.0.1.0 - Production on Wed Feb 9 11:16:20 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
RMAN> connect target /
connected to target database: HONGYE2 (DBID=860418810)
RMAN> connect auxiliary sys/hongye50@auxiliary
connected to auxiliary database: HONGYE2 (not mounted)
6、恢复辅助数据库的控制文件，
从主数据库的控制文件自动备份中恢复，此时切记：一定要设置好正确的恢复时间点，本次的实验中是以SCN为界的。
在恢复完辅助数据库的控制问件之后就可以mount辅助数据库了。
RMAN> run{
2> set until scn 382237;
3> restore clone controlfile;
4> sql clone 'alter database mount clone database';
5> sql 'alter system archive log current';
6> }
executing command: SET until clause
Starting restore at 09-FEB-11
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /oracle/flash_recovery_area/HONGYE2/autobackup/2011_02_09/o1_mf_s_742647952_6o414k9x_.bkp
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/oracle/flash_recovery_area/HONGYE2/autobackup/2011_02_09/o1_mf_s_742647952_6o414k9x_.bkp tag=TAG20110209T110552
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
output filename=/oracle/rmantest/auxiliary/control01.ctl
Finished restore at 09-FEB-11
sql statement: alter database mount clone database
sql statement: alter system archive log current
RMAN> exit
Recovery Manager complete.
7、以sysdba进入数据库，查看system表空间、undo表空间、temp表空间的所有相关文件号，还要查看将要做时间点恢复的表空间的文件名，这些信息将在后续操作中被用到。
将要进行时间点恢复的表空间离线。
[oracle@RAC1 dbs]$ sqlplus
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Feb 9 11:20:00 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
IDEL > conn / as sysdba
Connected.
SYS:143@HONGYE2 > desc dba_data_files
Name Null? Type
----------------------------------------------------------------- -------- --------------------------------------------
FILE_NAME VARCHAR2(513)
FILE_ID NUMBER
TABLESPACE_NAME VARCHAR2(30)
BYTES NUMBER
BLOCKS NUMBER
STATUS VARCHAR2(9)
RELATIVE_FNO NUMBER
AUTOEXTENSIBLE VARCHAR2(3)
MAXBYTES NUMBER
MAXBLOCKS NUMBER
INCREMENT_BY NUMBER
USER_BYTES NUMBER
USER_BLOCKS NUMBER
ONLINE_STATUS VARCHAR2(7)
SYS:143@HONGYE2 > select file_id,tablespace_name from dba_data_files;
FILE_ID TABLESPACE_NAME
---------- ------------------------------
1 SYSTEM
2 UNDOTBS1
3 SYSAUX
4 USERS
SYS:143@HONGYE2 > select file_name from dba_data_files where file_id=4;
FILE_NAME
------------------------------------------------------------------------------------------------------------------------
/oracle/oradata/hongye2/users01.dbf
SYS:143@HONGYE2 > select file_id,tablespace_name from dba_temp_files;
FILE_ID TABLESPACE_NAME
---------- ------------------------------
1 TEMP
将要恢复的表空间离线，等待恢复，如果在之前已经离线了，则不需要此步骤。
SYS:143@HONGYE2 > alter tablespace users offline for recover;
Tablespace altered.
SYS:143@HONGYE2 > exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@RAC1 dbs]$ rman
Recovery Manager: Release 10.2.0.1.0 - Production on Wed Feb 9 11:22:48 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
RMAN> connect target /
connected to target database: HONGYE2 (DBID=860418810)
RMAN> connect auxiliary sys/hongye50@auxiliary
connected to auxiliary database: HONGYE2 (DBID=860418810, not open)
8、执行辅助数据库的时间点恢复，注意设置不同文件的新文件名，其中system、undo、temp等表空间需要新的文件名，而已经离线的待恢复表空间则不需要，使用set newname来实现此重命名操作，还可以在init文件中使用db_file_name_convert参数进行转化。
切记：时间点的设置，一定要准确且和前面使用的一致。
恢复完成后，将辅助数据库的相关文件联机。
RMAN> run{
2> set until scn 382237;
3> set newname for datafile 4 to '/oracle/oradata/hongye2/users01.dbf';
4> set newname for clone tempfile 1 to new;
5> set newname for clone datafile 1 to new;
6> set newname for clone datafile 2 to new;
7> switch clone tempfile all;
8> restore clone datafile 1,2,4;
9> switch clone datafile all;
10> sql clone 'alter database datafile 1 online';
11> sql clone 'alter database datafile 2 online';
12> sql clone 'alter database datafile 4 online';
13> }
executing command: SET until clause
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
renamed temporary file 1 to /oracle/rmantest/auxiliary/AUXILIARY/datafile/o1_mf_temp_%u_.tmp in control file
Starting restore at 09-FEB-11
using channel ORA_AUX_DISK_1
skipping datafile 4; already restored to file /oracle/oradata/hongye2/users01.dbf
channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /oracle/rmantest/auxiliary/AUXILIARY/datafile/o1_mf_system_%u_.dbf
restoring datafile 00002 to /oracle/rmantest/auxiliary/AUXILIARY/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /oracle/flash_recovery_area/HONGYE2/backupset/2011_02_09/o1_mf_nnndf_TAG20110209T110503_6o41306k_.bkp
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/oracle/flash_recovery_area/HONGYE2/backupset/2011_02_09/o1_mf_nnndf_TAG20110209T110503_6o41306k_.bkp tag=TAG20110209T110503
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:26
Finished restore at 09-FEB-11
datafile 1 switched to datafile copy
input datafile copy recid=8 stamp=742649583 filename=/oracle/rmantest/auxiliary/AUXILIARY/datafile/o1_mf_system_6o42por8_.dbf
datafile 2 switched to datafile copy
input datafile copy recid=9 stamp=742649583 filename=/oracle/rmantest/auxiliary/AUXILIARY/datafile/o1_mf_undotbs1_6o42posj_.dbf
sql statement: alter database datafile 1 online
sql statement: alter database datafile 2 online
sql statement: alter database datafile 4 online
9、执行辅助数据库的介质恢复操作，确保将辅助实例恢复到指定的时间。
切记：时间点的设置，准确、一致。
介质恢复完成后，由于是不完全恢复，所以要使用resetlogs方式打开数据库。
RMAN> run{
2> set until scn 382237;
3> recover clone database tablespace "SYSTEM","UNDOTBS1","USERS" delete archivelog;
4> alter clone database open resetlogs;
5> }
executing command: SET until clause
Starting recover at 09-FEB-11
using channel ORA_AUX_DISK_1
starting media recovery
archive log thread 1 sequence 8 is already on disk as file /oracle/flash_recovery_area/HONGYE2/archivelog/2011_02_09/o1_mf_1_8_6o41yn3w_.arc
channel ORA_AUX_DISK_1: starting archive log restore to default destination
channel ORA_AUX_DISK_1: restoring archive log
archive log thread=1 sequence=7
channel ORA_AUX_DISK_1: reading from backup piece /oracle/flash_recovery_area/HONGYE2/backupset/2011_02_09/o1_mf_annnn_TAG20110209T110549_6o414h3y_.bkp
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/oracle/flash_recovery_area/HONGYE2/backupset/2011_02_09/o1_mf_annnn_TAG20110209T110549_6o414h3y_.bkp tag=TAG20110209T110549
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
archive log filename=/oracle/product/10.2.0/db_1/dbs/arch1_7_742402857.dbf thread=1 sequence=7
channel clone_default: deleting archive log(s)
archive log filename=/oracle/product/10.2.0/db_1/dbs/arch1_7_742402857.dbf recid=8 stamp=742649698
archive log filename=/oracle/flash_recovery_area/HONGYE2/archivelog/2011_02_09/o1_mf_1_8_6o41yn3w_.arc thread=1 sequence=8
media recovery complete, elapsed time: 00:00:04
Finished recover at 09-FEB-11
database opened
RMAN> exit
Recovery Manager complete.
10、实验接近尾声了，将辅助数据库中和待恢复表空间相关的源数据导出，指定exp中的关键字point_in_time_recover=y，此处需要注意的是Linux系统环境下单引号的转义问题，否则导出不会成功。
在进行导出之前，也可以进入辅助数据库中查看一下相关表空间是否恢复到了期望值，要确保ORACLE_SID=。
[oracle@RAC1 auxiliary]$ sqlplus
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Feb 9 11:41:18 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
IDEL > conn /as sysdba
Connected.
SYS:34@HONGYE2 > select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
UNDOTBS1 ONLINE
SYSAUX OFFLINE
TEMP ONLINE
USERS READ ONLY
SYS:34@HONGYE2 > select table_name from dba_tables where tablespace_name='USERS';
TABLE_NAME
------------------------------
T
TT
SYS:34@HONGYE2 > select * from hongye.t;
ID NAME
---------- ----------
1 name1
2 name2
2 name3
3 name3
SYS:34@HONGYE2 > select * from hongye.tt;
ID NAME
---------- ---------------
6 count(*) from t
SYS:34@HONGYE2 > shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS:34@HONGYE2 > exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
进行源数据的导出工作，要确保ORACLE_SID=。
[oracle@RAC1 dbs]$ exp \'sys/hongye50 as sysdba\' point_in_time_recover=y tablespaces=users file=/oracle/rmantest/auxiliary/users_pitr.dmp
Export: Release 10.2.0.1.0 - Production on Wed Feb 9 11:36:32 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)
Note: table data (rows) will not be exported
About to export Tablespace Point-in-time Recovery objects...
For tablespace USERS ...
. exporting cluster definitions
. exporting table definitions
. . exporting table TT
. . exporting table T
EXP-00091: Exporting questionable statistics.
. exporting referential integrity constraints
. exporting triggers
. end point-in-time recovery
Export terminated successfully with warnings.
导出成功了，可以看到目录下的导出文件users_pitr.dmp
[oracle@RAC1 dbs]$ cd /oracle/rmantest/auxiliary/
[oracle@RAC1 auxiliary]$ ls
adump AUXILIARY bdump cdump control01.ctl pfile.ora redo01.log redo02.log redo03.log udump users_pitr.dmp
导出完成后，要关闭辅助数据库，否则目标数据库的待恢复表空间将无法联机，因为辅助数据库和目标数据库对于待恢复的表空间，实际上使用的是同一个数据文件，如果不关闭，会有类似以下两个错误，因为同一个数据文件，不能同时被两个数据库打开。
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '/oracle/oradata/hongye2/users01.dbf'
[oracle@RAC1 auxiliary]$ sqlplus
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Feb 9 11:41:18 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
IDEL > conn /as sysdba
Connected.
SYS:34@HONGYE2 > shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS:34@HONGYE2 > exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
11、将第10步中得到了源数据导出文件导入到目标数据库，同样需要制定参数point_in_time_recover=y。
这其中，一定要记得改变系统变量ORACLE_SID=
[oracle@RAC1 auxiliary]$ export ORACLE_SID=hongye2
[oracle@RAC1 auxiliary]$ imp \'sys/hongye50 as sysdba\' point_in_time_recover=y file=/oracle/rmantest/auxiliary/users_pitr.dmp
Import: Release 10.2.0.1.0 - Production on Wed Feb 9 11:39:43 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export file created by EXPORT:V10.02.01 via conventional path
About to import Tablespace Point-in-time Recovery objects...
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P1 character set (possible charset conversion)
. importing SYS's objects into SYS
. importing HONGYE's objects into HONGYE
. . importing table "TT"
. . importing table "T"
. importing SYS's objects into SYS
Import terminated successfully without warnings.
12、结尾，在目标数据库SQL*PLUS中，使得待恢复的表空间联机，则表空间的时间点恢复完成，可以检查一下相关的数据，不过在前面其实已经检查过了，其实辅助数据库和目标数据库的users表空间的数据文件是同一个文件。
[oracle@RAC1 auxiliary]$ sqlplus
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Feb 9 11:40:00 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
IDEL > conn /as sysdba
Connected.
SYS:143@HONGYE
SYS:143@HONGYE2 > desc dba_tablespaces
Name Null? Type
----------------------------------------------------------------- -------- --------------------------------------------
TABLESPACE_NAME NOT NULL VARCHAR2(30)
BLOCK_SIZE NOT NULL NUMBER
INITIAL_EXTENT NUMBER
NEXT_EXTENT NUMBER
MIN_EXTENTS NOT NULL NUMBER
MAX_EXTENTS NUMBER
PCT_INCREASE NUMBER
MIN_EXTLEN NUMBER
STATUS VARCHAR2(9)
CONTENTS VARCHAR2(9)
LOGGING VARCHAR2(9)
FORCE_LOGGING VARCHAR2(3)
EXTENT_MANAGEMENT VARCHAR2(10)
ALLOCATION_TYPE VARCHAR2(9)
PLUGGED_IN VARCHAR2(3)
SEGMENT_SPACE_MANAGEMENT VARCHAR2(6)
DEF_TAB_COMPRESSION VARCHAR2(8)
RETENTION VARCHAR2(11)
BIGFILE VARCHAR2(3)
SYS:146@HONGYE2 > select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
UNDOTBS1 ONLINE
SYSAUX ONLINE
TEMP ONLINE
USERS OFFLINE
SYS:146@HONGYE2 > alter tablespace users online;
Tablespace altered.
SYS:146@HONGYE2 > conn hongye/hongye
Connected.
HONGYE:146@HONGYE2 > select table_name from user_tables;
TABLE_NAME
------------------------------
T
TT
HONGYE:146@HONGYE2 > select * from t;
ID NAME
---------- ----------
1 name1
2 name2
2 name3
3 name3
HONGYE:146@HONGYE2 > select * from tt;
ID NAME
---------- ---------------
6 count(*) from t
HONGYE:146@HONGYE2 >
最后，自己手动删除辅助实例和辅助数据库。
总结：本次实验纯属个人研究，实际上自动的表空间时间点恢复是一个很简单的操作命令就可以完成此次实验的所有步骤了，但是为了能够清晰的理解时间点恢复的具体流程，还是手动来一遍印象比较深刻，更能够加深自己的理解。同时，针对实验过程中问题的解决，也是一次很大的收获。
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24465008/viewspace-688034/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/24465008/viewspace-688034/
1141
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
