3.4.5 exp 出HR.T1 这张表并在主库完成导入
3.5 恢复二：以数据文件级完成表数据的恢复
3.5.1 此种方法的局限
适用于表空间很大而要恢复的表很小的状况， 且存在一定的“局限性”， 适用于DELETE/UPDATE/INSERT 这样误操作的情况，或是你对你的表很了解明确知道它位于哪个数据文件。
在做这个实验之前我对T1 表在哪个数据文件已经做了记录
SQL> col owner format a3 SQL> col SEGMENT_NAME format a5 SQL> col tablespace_name a10 SQL> col tablespace_name format a10 SQL> select owner,segment_name,TABLESPACE_NAME,FILE_ID,BYTES 2 from dba_extents 3 where owner='HR' and segment_name='T1' 4/ OWN SEGME TABLESPACE FILE_ID BYTES --- ----- ---------- ---------- ---------- HR T1 USERS 6 65536 SQL>
3.5.2 此种方式与3.4 所提及的基本上是一致的，有如下的不同：
1、因数据只位于6 号数据文件中，所以7 号数据文件是不需要的。
2、去掉了rman 脚本中的recover database 这一行，原脚本变为：
RMAN> run { allocate channel c1 type disk; allocate channel c2 type disk; set until scn 111391; restore controlfile; sql 'alter database mount clone database'; set newname for datafile 1 to '/yang/oradata/aux1/system01.dbf'; set newname for datafile 2 to '/yang/oradata/aux1/undotbs01.dbf'; set newname for datafile 6 to '/yang/oradata/aux1/users01.dbf'; restore datafile 1,2,6; switch datafile all; sql 'alter database datafile 1,2,6 online'; release channel c1; release channel c2; } 如果加上recover database ..子句原脚本会提示USERS 表空间的另外一个数据文件datafile 7 也需要restore ，如下： RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of recover command at 06/15/2007 14:41:17 RMAN-06094: datafile 7 must be restored RMAN> 3、把archivelog restore 到aux1 相关的目录，以便手动recover 。 RMAN> run 2> { 3> allocate channel c1 type disk; 4> set ARCHIVELOG DESTINATION to '/yang/arch1/'; 5> restore archivelog all; 6> release channel c1; 7> } allocated channel: c1 channel c1: sid=12 devtype=DISK executing command: SET ARCHIVELOG DESTINATION Starting restore at 15-JUN-07 archive log thread 1 sequence 27 is already on disk as file /yang/arch/arch1_27.arc channel c1: starting archive log restore to user-specified destination archive log destination=/yang/arch1/ channel c1: restoring archive log archive log thread=1 sequence=18 channel c1: restored backup piece 1 piece handle=/yang/backup/arch_625323891_32_1 tag=TAG20070615T130450 params=NULL channel c1: restore complete channel c1: starting archive log restore to user-specified destination archive log destination=/yang/arch1/ channel c1: restoring archive log archive log thread=1 sequence=19 channel c1: restoring archive log archive log thread=1 sequence=20 channel c1: restored backup piece 1 piece handle=/yang/backup/arch_625323891_33_1 tag=TAG20070615T130450 params=NULL channel c1: restore complete channel c1: starting archive log restore to user-specified destination archive log destination=/yang/arch1/ channel c1: restoring archive log archive log thread=1 sequence=21 channel c1: restoring archive log archive log thread=1 sequence=22 channel c1: restored backup piece 1 piece handle=/yang/backup/arch_625323891_34_1 tag=TAG20070615T130450 params=NULL channel c1: restore complete channel c1: starting archive log restore to user-specified destination archive log destination=/yang/arch1/ channel c1: restoring archive log archive log thread=1 sequence=23 channel c1: restored backup piece 1 piece handle=/yang/backup/arch_625323893_35_1 tag=TAG20070615T130450 params=NULL channel c1: restore complete channel c1: starting archive log restore to user-specified destination archive log destination=/yang/arch1/ channel c1: restoring archive log archive log thread=1 sequence=24 channel c1: restoring archive log archive log thread=1 sequence=25 channel c1: restoring archive log archive log thread=1 sequence=26 channel c1: restored backup piece 1 piece handle=/yang/backup/arch_625324785_36_1 tag=TAG20070615T131945 params=NULL channel c1: restore complete Finished restore at 15-JUN-07 released channel: c1 4、手动做recover，rename redo logfile, 然后打开数据库。 SQL> recover database using backup controlfile until change 111391; ORA-00279: change 111010 generated at 06/15/2007 13:04:25 needed for thread 1 ORA-00289: suggestion : /yang/arch1/arch1_22.arc ORA-00280: change 111010 for thread 1 is in sequence #22 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} auto ORA-00279: change 111033 generated at 06/15/2007 13:04:48 needed for thread 1 ORA-00289: suggestion : /yang/arch1/arch1_23.arc ORA-00280: change 111033 for thread 1 is in sequence #23 ORA-00278: log file '/yang/arch1/arch1_22.arc' no longer needed for this recovery ORA-00279: change 111037 generated at 06/15/2007 13:04:49 needed for thread 1 ORA-00289: suggestion : /yang/arch1/arch1_24.arc ORA-00280: change 111037 for thread 1 is in sequence #24 ORA-00278: log file '/yang/arch1/arch1_23.arc' no longer needed for this recovery ORA-00279: change 111043 generated at 06/15/2007 13:04:55 needed for thread 1 ORA-00289: suggestion : /yang/arch1/arch1_25.arc ORA-00280: change 111043 for thread 1 is in sequence #25 ORA-00278: log file '/yang/arch1/arch1_24.arc' no longer needed for this recovery Log applied. Media recovery complete. SQL> alter database rename file '/yang/oradata/orcl/redo01.log' to '/yang/oradata/aux1/redo01.log'; Database altered. SQL> alter database rename file '/yang/oradata/orcl/redo02.log' to '/yang/oradata/aux1/redo02.log'; Database altered. SQL> alter database rename file '/yang/oradata/orcl/redo03.log' to '/yang/oradata/aux1/redo03.log'; Database altered. SQL> alter database open resetlogs; Database altered. SQL> select * from hr.t1; DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID ------------- ------------------------------ ---------- ----------- 10 Administration 200 1700 20 Marketing 201 1800 30 Purchasing 114 1700 40 Human Resources 203 2400 50 Shipping 121 1500 60 IT 103 1400 70 Public Relations 204 2700 80 Sales 145 2500 90 Executive 100 1700 100 Finance 108 1700 110 Accounting 205 1700 DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID ------------- ------------------------------ ---------- ----------- 120 Treasury 1700 130 Corporate Tax 1700 140 Control And Credit 1700 150 Shareholder Services 1700 160 Benefits 1700 170 Manufacturing 1700 180 Construction 1700 190 Contracting 1700 200 Operations 1700 20 rows selected. SQL>
3.6 补充
如果你的undo tablespace 也比较大，如我们的就是40 多G，如果你手头还是dul 工具，那undo表空间的数据文件也可以去掉，方法同3.5，完成手动的recover 即可用dul 来操作了。
Dul 需要system 表空间数据文件＋要恢复表的数据文件就可以工作了。
Eg:
$ cat control.dul
0 1 /yang/oradata/aux1/system01.dbf
6 6 /yang/oradata/aux1/users01.dbf
DUL> unload table hr.t1;
. unloading table T1 20 rows unloaded
DUL>
顺便再说一下，如果你的表是被truncate 或drop 掉的话，如果发现的及时，表所在空间未被重用的话，通过dul 数据也是会被找回来的。如果没有dul，此一节也可以通过_corrupted_rollback_segments 参数在不restore undo tablespace 情况下打开数据库的。
4 参考文档
Metalink: 223543.1
