Fans Or Not

今天试验了把在只有数据文件的条件下恢复。我用的是AUL免费版本。

先建立试验环境，创建一个数据文件和两个表，插入少量数据：

SQL> conn / as sysdba
Connected.
SQL> create tablespace test
2  datafile 'd:\test.dbf' size 2M
3  extent management local;

Tablespace created.

SQL>  create table zeeno1 (text varchar(100))
2  tablespace test;

Table created.

SQL> insert into zeeno1 values('Hello World!');

1 row created.

SQL> create table zeeno2 (id number(5))
2  tablespace test;

Table created.

SQL> insert into zeeno2 values(250);

1 row created.

SQL> commit;

Commit complete.
SQL> alter system checkpoint;

System altered.

接下来写个AUL的配置文件。配置文件需要FILE#,RFILE#和文件路径：

SQL> column name for a40
SQL> select file#,rfile#,name from v$datafile;

FILE#     RFILE# NAME
---------- ---------- ----------------------------------------
1          1 D:\ORACLEXE\ORADATA\XE\SYSTEM01.DBF
2          2 D:\ORACLEXE\ORADATA\XE\UNDOTBS01.DBF
3          3 D:\ORACLEXE\ORADATA\XE\SYSAUX01.DBF
4          4 D:\TEST.DBF
5          5 D:\ORACLEXE\ORADATA\XE\USERS.DBF

此次环境是在有SYSTEM表空间的前提下，因而可以直接unload数据出来。

D:\>aul
Register Code: 0W2L-AI50-16DQ-DLLS-RP58
AUL : AnySQL UnLoader(MyDUL) for Oracle 8/8i/9i/10g, Trial Version 3.2.2

(C) Copyright Lou Fangxin 2005-2006 (AnySQL.net), all rights reserved.

AUL> open aul.cfg
*  ts#  fno  rfn ver bsize     blocks filename
- ---- ---- ---- --- ----- ---------- -----------------------------------
Y    0    1    1 a2   8192      41600 D:\oraclexe\oradata\XE\SYSTEM01.DBF
Y    6    4    4 a2   8192        256 D:\TEST.DBF

AUL> unload table user$;
2006-09-05 10:27:51
2006-09-05 10:27:51
AUL> unload table obj$;
2006-09-05 10:27:55
2006-09-05 10:27:57
AUL> unload table tab$;
2006-09-05 10:28:02
2006-09-05 10:28:05
AUL> unload table col$;
2006-09-05 10:28:09
2006-09-05 10:28:11

AUL> desc sys.zeeno1

Storage(OBJ#=9606 OBJD=9606 TS=6 FILE=4 BLOCK=11 CLUSTER=0)
No. SEQ INT Column Name                   Type
--- --- --- ----------------------------- ----------------
1   1   1 TEXT                          VARCHAR2(100)

AUL> desc sys.zeeno2

Storage(OBJ#=9607 OBJD=9607 TS=6 FILE=4 BLOCK=19 CLUSTER=0)
No. SEQ INT Column Name                   Type
--- --- --- ----------------------------- ----------------
1   1   1 ID                            NUMBER(5)

AUL> unload table sys.zeeno1;
2006-09-05 10:29:43
Unload OBJD=9606 FILE=4 BLOCK=11 CLUSTER=0 ...
Hello World!
2006-09-05 10:29:43
AUL> unload table sys.zeeno2;
2006-09-05 10:29:49
Unload OBJD=9607 FILE=4 BLOCK=19 CLUSTER=0 ...
250
2006-09-05 10:29:49

这样会在当前目录下生成ZEENO1_sqlldr.ctl和ZEENO2_sqlldr.ctl两个文件。如

--
-- Generated by AUL/MyDUL, for table sys.zeeno1
--
OPTIONS(DIRECT=TRUE,READSIZE=4194304,ERRORS=-1,SKIP=1,ROWS=50000)
LOAD DATA
INFILE 'd:\zeeno1.txt' "STR X'0d0a'"
INTO TABLE ZEENO1
FIELDS TERMINATED BY X'7c' TRAILING NULLCOLS
(
TEXT    CHAR(100)
)

如果需要数据，还需要加上to file参数：

AUL> unload table sys.zeeno1 to d:\zeeno1.txt;
2006-09-05 10:36:23
Unload OBJD=9606 FILE=4 BLOCK=11 CLUSTER=0 ...
2006-09-05 10:36:23

zeeno1.txt文件就是表sys.zeeno1的内容了。

本次试验只是尝试一下AUL的工作方式，实际上，对于产品数据库的恢复恐怕得购买许可证了或相关服务了。