AUL/MyDUL修复Oracle

来源：互联网发布：侯赛因国王知乎编辑：程序博客网时间：2024/06/06 01:18

AUL恢复软件是针对无备份的oracle数据库受损后的终极恢复

下载地址

http://www.anysql.net/download

http://www.anysql.net/software/aul6_linux.zip

用oracle用户下载解压

SQL> col name format a40
SQL> select file#,rfile#,name from v$datafile;

     FILE#     RFILE# NAME
---------- ---------- ----------------------------------------
         1          1 /opt/oracle/oradata/duxiu/system01.dbf
         2          2 /opt/oracle/oradata/duxiu/undotbs01.dbf
         3          3 /opt/oracle/oradata/duxiu/sysaux01.dbf
         4          4 /opt/oracle/oradata/duxiu/users01.dbf
         5          5 /opt/oracle/oradata/duxiu/test01.dbf
         6          6 /opt/oracle/oradata/duxiu/test02.dbf
         7          7 /opt/oracle/oradata/duxiu/test04.dbf

7 rows selected.

模拟故障，shutdown immediate数据库

编辑配置文件，让AUL找到数据库文件

[oracle@hadoop-m1 ~]$ vim mycfg

1          1 /opt/oracle/oradata/duxiu/system01.dbf
2          2 /opt/oracle/oradata/duxiu/undotbs01.dbf
3          3 /opt/oracle/oradata/duxiu/sysaux01.dbf
4          4 /opt/oracle/oradata/duxiu/users01.dbf
~
"auldemo.cfg" 4L, 208C 已写入

[oracle@hadoop-m1 ~]$ ./aul6_linux.bin
Register Code: AWRX-WMOC-PVGY-MEEN-IAOK
AUL : AnySQL UnLoader(MyDUL) for Oracle 11g and ASM, release 6.2.0

AUL> open my.cfg
* ts# rfn ver bsize     blocks   sizemb filename
- ---- ---- --- ----- ---------- -------- -----------------------------------
Y    0    1 a2   8192     106240        0 /opt/oracle/oradata/duxiu/system01.dbf
Y    1    2 a2   8192      19200        0 /opt/oracle/oradata/duxiu/undotbs01.dbf
Y    2    3 a2   8192      52480        0 /opt/oracle/oradata/duxiu/sysaux01.dbf
Y    4    4 a2   8192     192000        0 /opt/oracle/oradata/duxiu/users01.dbf

AUL> UNLOAD TABLE USER$;
2013-04-16 13:17:25
2013-04-16 13:17:25
AUL> UNLOAD TABLE OBJ$;
2013-04-16 13:17:37
2013-04-16 13:17:38
AUL> UNLOAD TABLE TAB$;
2013-04-16 13:18:27
2013-04-16 13:18:27

AUL> UNLOAD TABLE COL$;
2013-04-16 13:17:46
2013-04-16 13:17:46

AUL> list table scott -----显示用户名下表，执行以下语句将转储各表数据到文本文件
UNLOAD TABLE scott.T5 TO T5.txt;
UNLOAD TABLE scott.TEST04 TO TEST04.txt;
UNLOAD TABLE scott.T001 TO T001.txt;
UNLOAD TABLE scott.EMP TO EMP.txt;
UNLOAD TABLE scott.T002 TO T002.txt;
UNLOAD TABLE scott.DEPT TO DEPT.txt;
UNLOAD TABLE scott.BONUS TO BONUS.txt;
UNLOAD TABLE scott.SALGRADE TO SALGRADE.txt;

AUL> UNLOAD TABLE scott.T5 TO T5.txt;
2013-04-16 13:19:20
Unload OBJD=65403 FILE=4 BLOCK=3003 CLUSTER=0 ...
Sucessfully unload 14 rows ...
2013-04-16 13:19:21
AUL> UNLOAD TABLE scott.EMP TO EMP.txt;
2013-04-16 13:21:35
Unload OBJD=65604 FILE=4 BLOCK=2971 CLUSTER=0 ...
Sucessfully unload 14 rows ...
2013-04-16 13:21:35
AUL>

开启新shell

[oracle@hadoop-m1 ~]$ ls EMP*
EMP_sqlldr.ctl EMP_syntax.sql EMP.txt
[oracle@hadoop-m1 ~]$ ls T5*
T5_sqlldr.ctl T5_syntax.sql T5.txt

[oracle@hadoop-m1 ~]$ more EMP_syntax.sql
CREATE TABLE "EMP" ( "EMPNO" NUMBER(4) NOT NULL , "ENAME" VARCHAR2(10) , "JOB" VARCHAR2(9) , "MGR" NUMBER(4) , "HIREDATE" DATE , "SAL" NUMBER(7,2) , "COMM"
NUMBER(7,2) , "DEPTNO" NUMBER(2) );
exit;
[oracle@hadoop-m1 ~]$ more EMP.txt
7369|SMITH|CLERK|7902|1980-12-17 00:00:00|921||20
7499|ALLEN|SALESMAN|7698|1981-02-20 00:00:00|1600|300|30
7521|WARD|SALESMAN|7698|1981-02-22 00:00:00|1250|500|30
7566|JONES|MANAGER|7839|1981-04-02 00:00:00|2975||20
7654|MARTIN|SALESMAN|7698|1981-09-28 00:00:00|1250|1400|30
7698|BLAKE|MANAGER|7839|1981-05-01 00:00:00|2850||30
7782|CLARK|MANAGER|7839|1981-06-09 00:00:00|2450||10
7788|SCOTT|ANALYST|7566|1987-04-19 00:00:00|3000||20
7839|KING|PRESIDENT||1981-11-17 00:00:00|5000||10
7844|TURNER|SALESMAN|7698|1981-09-08 00:00:00|1500|0|30
7876|ADAMS|CLERK|7788|1987-05-23 00:00:00|1100||20
7900|JAMES|CLERK|7698|1981-12-03 00:00:00|950||30
7902|FORD|ANALYST|7566|1981-12-03 00:00:00|3000||20
7934|MILLER|CLERK|7782|1982-01-23 00:00:00|1300||10
[oracle@hadoop-m1 ~]$ more EMP_sqlldr.ctl
--
-- Generated by AUL/MyDUL, for table scott.EMP
--
OPTIONS(BINDSIZE=8388608,READSIZE=8388608,ERRORS=-1,ROWS=50000)
LOAD DATA
INFILE 'EMP.txt' "STR X'0d0a'"
APPEND INTO TABLE EMP
FIELDS TERMINATED BY X'7c' TRAILING NULLCOLS
(
   EMPNO    CHAR ,
   ENAME    CHAR(10) ,
   JOB    CHAR(9) ,
   MGR    CHAR ,
   HIREDATE   DATE "YYYY-MM-DD HH24:MI:SS" ,
   SAL    CHAR ,
   COMM    CHAR ,
   DEPTNO    CHAR
)

接下来就可以很简单地用oracle自带的sqlldr工具将文本文件中的数据装入到数据库中了

sqlldr scott/tiger control=EMP_sqlldr.ctl

--------------------------------------------------------------

AUL> set
SET BLOCK_SIZE    {2048 | 4096 | 8192 | 16384 | 32768}
SET BYTE_ORDER    {BIG | LITTLE}
SET DELETED_ROW   {TRUE | FALSE}
SET COMMITED_ONLY {TRUE | FALSE}
SET FIELD_TAG     field_tag
SET RECORD_TAG    record_tag
SET CACHE_SIZE    kbytes (64 - 8192)
SET OUTPUT_STYLE {TXT | DMP}
SET CHARSET       charsetid
SET NLSCHARSET    charsetid
SET FIXED_CHARSET {true | false}
SET BLOCK_CHECK   {0 | 1}
SET HEAD_SIZE     header size (default 0)
SET VERBOSE       {0 | 1}
SET CLOB_EDIAN    {BIG | LITTLE}
SET LOB_CONVERT   {0:NONE | 1:GBK | 2:UTF8}
SET LOB_STORAGE   {0:INLINE | 1:FILE | 2:NONE}
SET MAXLOBDIR     values between 100 and 2000
SET MAXCHAINS     integer value
SET BIGFILE       {Yes | NO}
SET ICONV_NCHAR   from_iconv_charset to_iconv_charset
SET ICONV_NCLOB   from_iconv_charset to_iconv_charset
SET ICONV_CLOB    from_iconv_charset to_iconv_charset
AUL> scan
SCAN HEADER [OBJECT objd] [FILE rfn] [TO filename]
SCAN TABLE [OBJECT objd] [FILE rfn] [TO filename]
SCAN DATA   [OBJECT objd] [BLOCKS blks] [FILE rfn] [TO filename]
SCAN EXTENT [OBJECT objd] [FILE rfn]
SCAN LOB    [OBJECT objd] [FILE rfn]
SCAN PARALLELEXT
SCAN PARALLELLOB
SCAN MAP
AUL>