1、问题的提出
归档情形下的备份基本都是由RMAN 工作来完成，RMAN 工具可以非常方便的使DBA 完成数据库的BACKUP(备份) 、RESTORE(还原) 、RECOVERY(恢复) 等工作。数据库处于归档方式，对于数据库物理损坏（数据文件坏，坏块等）的恢复是非常方便的。而如果你的一个库达到了一定级别，例如我们的营帐系统的一个营业库就有3TB ，这样一个级别的数据库去恢复一张误被TRUNCATER 小表，我们当然不能把3TB 的数据整库做不完全恢复，因为时间空间都不允许，那如何做呢？这可能是很多DBA 都有些迷惑的问题，或许你也看过相关的文档，估计写的都很粗略，本文以测试来说明一下这个问题。
2、恢复误操作的表可能有如下几种恢复方式
1、 有每天的EXP 的备份、或是阵列级的快照，而EXP 到出问题这段时间表所丢失的数据你可以通过一些途径弥补，此时可以IMP 回来完成恢复。现实中很多人采用的还是Rman 与Exp 关键表这样的组合方式。
2、 你有DSG 等这样软件做的备份，据说其做表级的恢复很方便，可以用其恢复。
3、 你有RMAN 的备份，恢复方式即为本文所谈的。
4、 顺便说一下容灾系统，不论是应用级容灾还是阵列所做的物理级的容灾都是不能完成表级误操作恢复的。
3 、恢复的流程
3.1 大体流程
建立一个AUXILIARY 实例，对表所在的表空间或数据文件做不完全的恢复，然后把数据库EXP 出来IMP 到原库。对表所在的整个表空间的恢复适用于表空间不大的情况下，如果一个表空间300GB而你要恢复的表只有30M 的话，显然还是恢复表所在一个或几个数据文件方便。
需要说明的是，AUXILIARY 库必须包括SYSTEM 表空间、UNDO 表空间以及你误操作表的表空间。如果你的UNDO 表空间也比较大，且你有DUL 工具的话，UNDO 表空间也是不需要的。
3.2 注意事项
如果你的AUXILIARY 库与主库在一台机器，一定要小心操作，以免恢复中覆盖主库文件，使主库发生损坏。
3.3 模拟问题的产生
3.3.1 模拟环境的配置
系统环境为Solaris9+Oracle9.2.0.6 ，主库的SID 为orcl，归档方式
表空间：
SQL> select tablespace_name from dba_tablespaces;
TABLESPACE_NAME
------------------------------
SYSTEM
UNDOTBS1
TEMP
EXAMPLE
INDX
TOOLS
USERS
7 rows selected.
数据文件：
SQL> select file_id,file_name,tablespace_name from dba_data_files;
FILE_ID FILE_NAME TABLESPACE
---------- ---------------------------------------- ----------
1 /yang/oradata/orcl/system01.dbf SYSTEM
2 /yang/oradata/orcl/undotbs01.dbf UNDOTBS1
3 /yang/oradata/orcl/example01.dbf EXAMPLE
4 /yang/oradata/orcl/indx01.dbf INDX
5 /yang/oradata/orcl/tools01.dbf TOOLS
6 /yang/oradata/orcl/users01.dbf USERS
7 /yang/oradata/orcl/users02.dbf USERS
7 rows selected.
日志文件：
SQL> select member from v$logfile;
MEMBER
----------------------------------------
/yang/oradata/orcl/redo01.log
/yang/oradata/orcl/redo02.log
/yang/oradata/orcl/redo03.log
3.3.2 备份一下数据库
$ rman cmdfile=b0
Recovery Manager: Release 9.2.0.6.0 - 64bit Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
RMAN> connect target /
2> connect catalog
3>
4> run
5> {
6> allocate channel c1 type disk;
7> allocate channel c2 type disk;
8> allocate channel c3 type disk;
9>
10> backup incremental level 0
11> filesperset 3
12> format '/yang/backup/db_incr0_%t_%s_%p'
13> database;
14>
15> sql 'alter system archive log current';
16>
17> backup filesperset 5
18> format '/yang/backup/arch_%t_%s_%p'
19> archivelog all delete input;
20>
21> sql 'alter system archive log current';
22>
23> release channel c1;
24> release channel c2;
25> release channel c3;
26> }
27>
28>
connected to target database: ORCL (DBID=1153028581)
connected to recovery catalog database
allocated channel: c1
channel c1: sid=11 devtype=DISK
allocated channel: c2
channel c2: sid=15 devtype=DISK
allocated channel: c3
channel c3: sid=19 devtype=DISK
Starting backup at 15-JUN-07
channel c1: starting incremental level 0 datafile backupset
channel c1: specifying datafile(s) in backupset
input datafile fno=00003 name=/yang/oradata/orcl/example01.dbf
input datafile fno=00004 name=/yang/oradata/orcl/indx01.dbf
input datafile fno=00006 name=/yang/oradata/orcl/users01.dbf
channel c1: starting piece 1 at 15-JUN-07
channel c2: starting incremental level 0 datafile backupset
channel c2: specifying datafile(s) in backupset
input datafile fno=00002 name=/yang/oradata/orcl/undotbs01.dbf
input datafile fno=00005 name=/yang/oradata/orcl/tools01.dbf
input datafile fno=00007 name=/yang/oradata/orcl/users02.dbf
channel c2: starting piece 1 at 15-JUN-07
channel c3: starting incremental level 0 datafile backupset
channel c3: specifying datafile(s) in backupset
including current SPFILE in backupset
including current controlfile in backupset
input datafile fno=00001 name=/yang/oradata/orcl/system01.dbf
channel c3: starting piece 1 at 15-JUN-07
channel c1: finished piece 1 at 15-JUN-07
piece handle=/yang/backup/db_incr0_625323864_29_1 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:16
channel c2: finished piece 1 at 15-JUN-07
piece handle=/yang/backup/db_incr0_625323865_30_1 comment=NONE
channel c2: backup set complete, elapsed time: 00:00:22
channel c3: finished piece 1 at 15-JUN-07
piece handle=/yang/backup/db_incr0_625323865_31_1 comment=NONE
channel c3: backup set complete, elapsed time: 00:00:22
Finished backup at 15-JUN-07
sql statement: alter system archive log current
Starting backup at 15-JUN-07
current log archived
channel c1: starting archive log backupset
channel c1: specifying archive log(s) in backup set
input archive log thread=1 sequence=18 recid=20 stamp=625312589
channel c1: starting piece 1 at 15-JUN-07
channel c2: starting archive log backupset
channel c2: specifying archive log(s) in backup set
input archive log thread=1 sequence=19 recid=21 stamp=625312591
input archive log thread=1 sequence=20 recid=18 stamp=625311783
channel c2: starting piece 1 at 15-JUN-07
channel c3: starting archive log backupset
channel c3: specifying archive log(s) in backup set
input archive log thread=1 sequence=21 recid=19 stamp=625312046
input archive log thread=1 sequence=22 recid=22 stamp=625323889
channel c3: starting piece 1 at 15-JUN-07
channel c1: finished piece 1 at 15-JUN-07
piece handle=/yang/backup/arch_625323891_32_1 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:02
channel c1: deleting archive log(s)
archive log filename=/yang/arch1/arch1_18.arc recid=20 stamp=625312589
channel c2: finished piece 1 at 15-JUN-07
piece handle=/yang/backup/arch_625323891_33_1 comment=NONE
channel c2: backup set complete, elapsed time: 00:00:02
channel c2: deleting archive log(s)
archive log filename=/yang/arch1/arch1_19.arc recid=21 stamp=625312591
archive log filename=/yang/arch/arch1_20.arc recid=18 stamp=625311783
channel c3: finished piece 1 at 15-JUN-07
piece handle=/yang/backup/arch_625323891_34_1 comment=NONE
channel c3: backup set complete, elapsed time: 00:00:02
channel c3: deleting archive log(s)
archive log filename=/yang/arch/arch1_21.arc recid=19 stamp=625312046
archive log filename=/yang/arch/arch1_22.arc recid=22 stamp=625323889
channel c1: starting archive log backupset
channel c1: specifying archive log(s) in backup set
input archive log thread=1 sequence=23 recid=23 stamp=625323890
channel c1: starting piece 1 at 15-JUN-07
channel c1: finished piece 1 at 15-JUN-07
piece handle=/yang/backup/arch_625323893_35_1 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
channel c1: deleting archive log(s)
archive log filename=/yang/arch/arch1_23.arc recid=23 stamp=625323890
Finished backup at 15-JUN-07
sql statement: alter system archive log current
released channel: c1
released channel: c2
released channel: c3
Recovery Manager complete.
3.3.3 HR 用户下建一张表T1 模拟问题
先插入27 条数据，后删除7 条数据，最后truncate 这张表，要求把数据恢复到20 条数据的状态。
SQL> connect hr/hr
Connected.
SQL> show user
USER is "HR"
SQL> create table T1 tablespace users
2 as select * from departments;
Table created.
SQL> select count(*) from T1;
COUNT(*)
----------
27
SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
111349
SQL> delete from hr.T1 where department_id>200;
7 rows deleted.
SQL> commit;
Commit complete.
SQL> select count(*) from hr.T1;
COUNT(*)
----------
20
SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
111391
SQL>
SQL> truncate table hr.T1;
Table truncated.
SQL> select count(*) from hr.T1;
COUNT(*)
----------
0
SQL>
SCN 为111391 时HR.T1 中的数据为20 条
3.4 恢复一：以表空间级完成表数据的恢复
适用于表空间不大的状况
3.4.1 建立AUXILIARY 实例
3.4.1.1 建立AUXILIARY 的pfile
假定实例名为aux1，在$ORACLE_HOME/dbs 下拷备主库的pfile 生成initaux1.ora，变动如下条目：
变动background_dump_dest，core_dump_dest，user_dump_dest，log_archive_dest_N 参数，并在操作系统建立相应的目录。
建立控制文件及要恢复的数据文件、日志文件的存放目录
设定LOCK_NAME_SPACE 参数，可以为任意值，如’aux1’，如果你的AUXILIARY 实例与主库同一台主机，这个参数必须设置
添加/变动SERVICE_NAMES 参数为aux1
修订INSTANCE_NAME 参数为aux1
不需要变动DB_NAME 参数：
注释掉log_archive_start 参数
3.4.1.2 建立aux1 的口令文件
$cd $ORACLE_HOME/dbs
$orapwd file=orapwaux1 password=change_on_install entries=3
3.4.2 启动AUXILIARY 到nomount 状态
$ echo $ORACLE_SID
aux1
$ sqlplus '/ as sysdba'
SQL*Plus: Release 9.2.0.6.0 -Production on Fri Jun 15 13:23:05 2007
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to an idle instance.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 236423584 bytes
Fixed Size 731552 bytes
Variable Size 167772160 bytes
Database Buffers 67108864 bytes
Redo Buffers 811008 bytes
SQL>
3.4.3 aux1 上启动Rman 执行如下脚本完成恢复
3.4.3.1 脚本
$ echo $ORACLE_SID
aux1
$ rman target / catalog
Recovery Manager: Release 9.2.0.6.0 - 64bit Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to target database: orcl (not mounted)
connected to recovery catalog database
RMAN> run
2> {
3> allocate channel c1 type disk;
4> allocate channel c2 type disk;
5>
6> set until scn 111391;
7>
8> restore controlfile;
9>
10> sql 'alter database mount clone database';
11>
12> set newname for datafile 1 to '/yang/oradata/aux1/system01.dbf';
13> set newname for datafile 2 to '/yang/oradata/aux1/undotbs01.dbf';
14> set newname for datafile 6 to '/yang/oradata/aux1/users01.dbf';
15> set newname for datafile 7 to '/yang/oradata/aux1/users02.dbf';
16>
17> restore tablespace system,undotbs1,users;
18> switch datafile all;
19>
20> sql 'alter database datafile 1,2,6,7 online';
21> recover database skip forever tablespace TEMP,EXAMPLE,INDX,TOOLS;
22>
23> release channel c1;
24> release channel c2;
25> }
3.4.3.2 Rman 日志
allocated channel: c1
channel c1: sid=11 devtype=DISK
allocated channel: c2
channel c2: sid=12 devtype=DISK
executing command: SET until clause
Starting restore at 15-JUN-07
channel c1: starting datafile backupset restore
channel c1: restoring controlfile
output filename=/yang/oradata/aux1/control01.ctl
channel c1: restored backup piece 1
piece handle=/yang/backup/db_incr0_625323865_31_1 tag=TAG20070615T130424 params=NULL
channel c1: restore complete
replicating controlfile
input filename=/yang/oradata/aux1/control01.ctl
Finished restore at 15-JUN-07
sql statement: alter database mount clone database
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 15-JUN-07
channel c1: starting datafile backupset restore
channel c1: specifying datafile(s) to restore from backup set
restoring datafile 00006 to /yang/oradata/aux1/users01.dbf
channel c2: starting datafile backupset restore
channel c2: specifying datafile(s) to restore from backup set
restoring datafile 00002 to /yang/oradata/aux1/undotbs01.dbf
restoring datafile 00007 to /yang/oradata/aux1/users02.dbf
channel c1: restored backup piece 1
piece handle=/yang/backup/db_incr0_625323864_29_1 tag=TAG20070615T130424 params=NULL
channel c1: restore complete
channel c1: starting datafile backupset restore
channel c1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /yang/oradata/aux1/system01.dbf
channel c1: restored backup piece 1
piece handle=/yang/backup/db_incr0_625323865_31_1 tag=TAG20070615T130424 params=NULL
channel c1: restore complete
channel c2: restored backup piece 1
piece handle=/yang/backup/db_incr0_625323865_30_1 tag=TAG20070615T130424 params=NULL
channel c2: restore complete
Finished restore at 15-JUN-07
datafile 1 switched to datafile copy
input datafilecopy recid=6 stamp=625325256 filename=/yang/oradata/aux1/system01.dbf
datafile 2 switched to datafile copy
input datafilecopy recid=7 stamp=625325256 filename=/yang/oradata/aux1/undotbs01.dbf
datafile 6 switched to datafile copy
input datafilecopy recid=8 stamp=625325256 filename=/yang/oradata/aux1/users01.dbf
datafile 7 switched to datafile copy
input datafilecopy recid=9 stamp=625325256 filename=/yang/oradata/aux1/users02.dbf
sql statement: alter database datafile 1,2,6,7 online
Starting recover at 15-JUN-07
starting media recovery
channel c1: starting archive log restore to default destination
channel c2: starting archive log restore to default destination
channel c1: restoring archive log
archive log thread=1 sequence=22
channel c2: restoring archive log
archive log thread=1 sequence=23
channel c1: restored backup piece 1
piece handle=/yang/backup/arch_625323891_34_1 tag=TAG20070615T130450 params=NULL
channel c1: restore complete
archive log filename=/yang/arch1/arch1_22.arc thread=1 sequence=22
channel c2: restored backup piece 1
piece handle=/yang/backup/arch_625323893_35_1 tag=TAG20070615T130450 params=NULL
channel c2: restore complete
archive log filename=/yang/arch1/arch1_23.arc thread=1 sequence=23
channel c1: starting archive log restore to default destination
channel c1: restoring archive log
archive log thread=1 sequence=24
channel c1: restoring archive log
archive log thread=1 sequence=25
channel c1: restored backup piece 1
piece handle=/yang/backup/arch_625324785_36_1 tag=TAG20070615T131945 params=NULL
channel c1: restore complete
archive log filename=/yang/arch1/arch1_24.arc thread=1 sequence=24
archive log filename=/yang/arch1/arch1_25.arc thread=1 sequence=25
media recovery complete
Finished recover at 15-JUN-07
released channel: c1
released channel: c2
RMAN>
3.4.3.3 脚本说明
设定了SET UNTIL SCN 111391: 使USERS 表空间恢复到HR.T1 表数据存在的状态。
mount clone database：出于安全方面的考虑，使所有的数据文件都OFFLINE 。
Set newname：使Rman 恢复数据文件到aux1 的相应目录，其是与switch 配合使用的。
Switch datafile all: 相当于mount 状态下的alter database rename file … to ..; 即变更控制文件中相应数据文件的路径为set newname 设定的路径。
Recover database skip forever tablespace: 即跳过这些不需要恢复的表空间加快恢复的速度，forever 选项在做不完全恢时是必须的，相当于Rman 在恢复时对相应表空间的数据文件在offline 的同时加了drop 选项。
3.4.4 以RESETLOGS 选项打开AUXILIARY 库
3.4.4.1 变更controlfile 中的online redolog 的路径，resetlogs 打开时这些日志将会被创立
SQL> select status from v$instance;
STATUS
------------
MOUNTED
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>
3.4.4.2 打开数据库
SQL> alter database open resetlogs;
Database altered.
SQL> select count(*) from hr.T1;
COUNT(*)
----------
20
SQL>
可见，HR.T1 这张表已经恢复成功
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: {=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 情况下打开数据库的。
