PRM-DUL成功案例:为某电信运营商恢复了误truncate的一百多张表

PRM-DUL成功案例:为某电信运营商恢复了误删truncate的一百多张表。东南某电信运营商生产系统数据库部分数据表被误删除的问题,现场工作内容:采用PRM-DUL工具将所需要的共121张表恢复出来。

整个数据库大小在25T左右,由于还原的新环境存储空间有限,还原整个数据库不够现实。初步决定还原方案为先还原SYSTEM和data表空间,然后使用PRM-DUL直接读取数据文件,将数据导出。

实际这个case用户在带库里是由完整备份和归档的,但是实际情况所制约没有那么多空间和时间去从备份里恢复数据了,如果真的那么做,可能至少需要几天时间,而实际恢复业务要求在1天内。所幸的是业务对这些表的完整性要求不高,而且从后期来看在truncate后插入数据的量很少,所以这个case在协商后使用了PRM-DUL成功scan database字典模式下恢复truncate功能来恢复了。

 

最新版PRM-DUL下载地址: http://parnassusdata.com/sites/default/files/ParnassusData_PRMForOracle_3206.zip

免费的PRM-DUL License :http://www.parnassusdata.com/zh-hans/node/122 

PRM- A FULL GUI DUL data unloader For Oracle Database

TRY PRM-DUL Data Unloader For Oracle Database

 

FULL GUI supported, easy to use, written in Java cross platform . PRM can help user recover data from truncated table or corrupted database!

 

PRM-DUL Data Unloader – For Oracle Database, from ParnassusData Software System Inc., which can save your ORACLE database

 

PRM – a public DUL with GUI , is free to download trial version.

 
PRM is designed for Enterprise Database Recovery, which includes all Oracle DUL data recovery functionalities, and also easy-to-use GUI. Users can purchase PRM for its rich GUI on your recovery. Or, you can contact ParnassusData for professional service that is either onsite or remote for your request. Rich GUI wizard can guide your recovery process. PRM can recovery your data direct from your database file system (dirty read). If your data has not been covered, PRM can guarantee your 99.9% data back.

 

[Read more…]

DROP Tablespace的Oracle数据恢复 PRM-DUL

D公司的员工需要删除某个无用的表空间即DROP TABLESPACE INCLUDING CONTENTS操作,但是在操作DROP TABLESPACE后,开发部门反映该被DROP掉的TABLESPACE上其实有一个SCHEMA的数据是有用且重要的,但现在表空间被DROP了,且无任何备份。

此时可以利用PRM-DUL的No-Dict模式去抽取被DROP TABLESPACE的对应的所有数据文件中的数据。 通过这种方式可以恢复大部分数据,但由于是非字典模式所以需要将恢复出来的表与应用数据表一一对应起来,此时一般需要应用开发维护人员介入,通过人工识别来分辨哪些数据属于哪张表。由于DROP TABLESPACE操作修改了数据字典,并在OBJ$中删除了对应表空间上的对象,所以无法从OBJ$上获得DATA_OBJECT_ID与OBJECT_NAME之间的对应关系。此时我们可以利用如下的方法,尽可能多得获取DATA_OBJECT_ID与OBJECT_NAME之间的对应关系。

 

select tablespace_name,segment_type,count(*) from dba_segments where owner='PARNASSUSDATA'  group by tablespace_name,segment_type;


TABLESPACE SEGMENT_TYPE      COUNT(*)
---------- --------------- ----------
USERS      TABLE                  126
USERS      INDEX                  136

SQL> select count(*) from obj$;

  COUNT(*)
----------
     75698


SQL> select current_scn, systimestamp from v$database;

CURRENT_SCN
-----------
SYSTIMESTAMP
---------------------------------------------------------------------------
    1895940
25-4月 -14 09.18.00.628000 下午 +08:00



SQL> select file_name from dba_data_files where tablespace_name='USERS';

FILE_NAME
--------------------------------------------------------------------------------
H:\PP\MACLEAN\ORADATA\PARNASSUS\DATAFILE\O1_MF_USERS_9MNBMJYJ_.DBF


SQL> drop tablespace users including contents;

表空间已删除。


C:\Users\maclean>dir H:\APP\MACLEAN\ORADATA\PARNASSUS\DATAFILE\O1_MF_USERS_9MNBMJYJ_.DBF
 驱动器 H 中的卷是 entertainment
 卷的序列号是 A87E-B792

 H:\APP\MACLEAN\ORADATA\PARNASSUS\DATAFILE 的目录

找不到文件

因为drop tablespace 后该TABLESPACE对应的数据文件在 OS上被删除。



此时通过文件恢复工具例如Windows平台上可以使用UNDELETER将被误删除的数据文件还原出来

prm-dul-drop-tablespace1

 

启动PRM => recovery Wizard => 非字典模式

 

prm-dul-drop-tablespace2

 

prm-dul-drop-tablespace3

 

由于是非字典模式,所以需要自己选择合理的字符集!

 

prm-dul-drop-tablespace4

 

加入刚才恢复出来的数据文件并点击扫描

 

prm-dul-drop-tablespace5

 

prm-dul-drop-tablespace6

 

之后选择从段头/盘区扫描表,如果从段头扫描表未能找到所有表,则考虑用从盘区扫描:

prm-dul-drop-tablespace7

 

 

此时可以看到主界面树形图出现大量OBJXXXXX的表,这里的OBJXXXXX实际就是表的DATA_OBJECT_ID,一般如果有熟悉该套系统应用模式开发的技术人员可以通过浏览样本数据分析将该表与应用表对应起来:

 

prm-dul-drop-tablespace8

 

如果没有人可以帮忙对应数据与表之间的关系,则可以考虑使用如下的手段:

 

 

由于此例子中仅仅是DROP了TABLESPACE表空间,而数据库本身完全是可用的,则此时可以利用FLASHBACK QUERY来获得DATA_OBJECT_ID与表名之间的映射关系。

 

 

SQL>  select count(*) from sys.obj$;

  COUNT(*)
----------
     75436







SQL> select count(*) from sys.obj$ as of scn 1895940;
select count(*) from sys.obj$ as of scn 1895940
                         *
第 1 行出现错误:
ORA-01555: 快照过旧: 回退段号 0 (名称为 "SYSTEM") 过小

一开始想利用FLASHBACK QUERY来找出OBJ$上之前的记录,但是发现由于使用SYSTEM ROLLBACK SEGMENT所以会出现ORA-01555错误


此时可以考虑使用AWR视图DBA_HIST_SQL_PLAN,只要在最近7天中访问过该表一般可以从执行计划中获得OBJECT#和OBJECT_NAME的映射关系:

SQL> desc DBA_HIST_SQL_PLAN
 名称                                      是否为空? 类型
 ----------------------------------------- -------- -----------------------
 DBID                                      NOT NULL NUMBER
 SQL_ID                                    NOT NULL VARCHAR2(13)
 PLAN_HASH_VALUE                           NOT NULL NUMBER
 ID                                        NOT NULL NUMBER
 OPERATION                                          VARCHAR2(30)
 OPTIONS                                            VARCHAR2(30)
 OBJECT_NODE                                        VARCHAR2(128)
 OBJECT#                                            NUMBER
 OBJECT_OWNER                                       VARCHAR2(30)
 OBJECT_NAME                                        VARCHAR2(31)
 OBJECT_ALIAS                                       VARCHAR2(65)
 OBJECT_TYPE                                        VARCHAR2(20)
 OPTIMIZER                                          VARCHAR2(20)
 PARENT_ID                                          NUMBER
 DEPTH                                              NUMBER
 POSITION                                           NUMBER
 SEARCH_COLUMNS                                     NUMBER
 COST                                               NUMBER
 CARDINALITY                                        NUMBER
 BYTES                                              NUMBER
 OTHER_TAG                                          VARCHAR2(35)
 PARTITION_START                                    VARCHAR2(64)
 PARTITION_STOP                                     VARCHAR2(64)
 PARTITION_ID                                       NUMBER
 OTHER                                              VARCHAR2(4000)
 DISTRIBUTION                                       VARCHAR2(20)
 CPU_COST                                           NUMBER
 IO_COST                                            NUMBER
 TEMP_SPACE                                         NUMBER
 ACCESS_PREDICATES                                  VARCHAR2(4000)
 FILTER_PREDICATES                                  VARCHAR2(4000)
 PROJECTION                                         VARCHAR2(4000)
 TIME                                               NUMBER
 QBLOCK_NAME                                        VARCHAR2(31)
 REMARKS                                            VARCHAR2(4000)
 TIMESTAMP                                          DATE
 OTHER_XML                                          CLOB


例如:

select object_owner,object_name,object# from DBA_HIST_SQL_PLAN where sql_id='avwjc02vb10j4'

OBJECT_OWNER         OBJECT_NAME                                 OBJECT#
-------------------- ---------------------------------------- ----------

PARNASSUSDATA        TORDERDETAIL_HIS                              78688 




可以利用如下脚本获得较多OBJECT_ID与OBJECT_NAME的映射关系

Select * from 
(select object_name,object# from DBA_HIST_SQL_PLAN  
UNION select object_name,object# from GV$SQL_PLAN) V1 where V1.OBJECT# IS NOT NULL minus select name,obj# from sys.obj$;

select obj#,dataobj#, object_name from WRH$_SEG_STAT_OBJ where object_name not in (select name from sys.obJ$) order by object_name desc;


另一个查询:
SELECT tab1.SQL_ID,
  current_obj#,
  tab2.sql_text
FROM DBA_HIST_ACTIVE_SESS_HISTORY tab1,
  dba_hist_sqltext tab2
WHERE tab1.current_obj# NOT IN
  (SELECT obj# FROM sys.obj$
  )
AND current_obj#!=-1
AND tab1.sql_id  =tab2.sql_id(+);


注意以上方法仅仅在用户确实找不到所要恢复的数据表的任何定义信息时使用(即用户找任何对该应用模式设计有了解的人、脚本和文档),且由于依赖于AWR数据,所以并不十分准确。

Oracle PRM-DUL User Guide V0.3

Summary

 

ParnassusData Recovery Manager (PRM-DUL) is an enterprise Oracle database recovery tool, which can extract database datafile from Oracle 9i, 10g, 11g, 12c directly without any SQL execution on database. ParnassusData Recovery Manager was developed by Java, which can be used cross platforms. It can be run without any installation. Download it, and click to run

 

 

PRM-DUL has full rich GUI for any command. It is not necessary to learn script or master any skill in Oracle data structure. Recovery Wizard is integrated in the tool.

 

 

Download PRM-DUL:

http://parnassusdata.com/sites/default/files/ParnassusData_PRMForOracle_3206.zip

 

 

PRM-DUL-DUL1

 

Why PRM-DUL is necessary?

 

Isn’t RMAN enough for ORACLE database recovery? Why need PRM-DUL for Oracle recovery?

In modern growing IT systems, database size is growing geometrically. Oracle DBAs are facing the problem that disks are insufficient for full backup, and tape storages take much more time than usual expectation.

 

Truth been told, “Database, backup 1st” is the first lesson for DBAs, however that fact is : disk space is not sufficient, new storage is still on the way, even the backup image may not work.

 

In order to solve the above problems, PD Recovery Manager integrates the algorithm in Oracle database data structure, boot process which can solve system table lost, data dictionary error, and storage corruption impaction. In addition, it can also recovery the data from Truncate/DROP mistake.

 

No matter you are a professional DBA or new fish in Oracle world, you can master this user-friendly tool immediately. PRM-DUL is easy to install and use. You don’t need to have any Oracle deep knowledge or skill in scripts, but just click-by-click finishing all recovery process.

 

Comparing the traditional recovery tool like Oracle DUL that is an Oracle internal tool and only for Oracle employee usage. PRM-DUL can be used for any kind of IT professionals or geeks. It saves time, decreases the recovery failure, and cuts down the total cost of enterprise.

 

There are 2 modes for data recovery:

By traditional way, data has to be extract to text file and then insert to new DB by SQLLDR tools, which takes double time and occupies double storage size.

 

ParnassusData Recovery Manager integrates data bridge features, which can extract data from original source database and then insert into new destination database without any inter-media. This is a truly time and storage saver.

 

Oracle ASM is becoming popular in enterprise database implementation, due to its advantage in high performance, cluster support, and easy administration. However, for many IT professionals, ASM is a black box. Once ASM occurs error in disk group mounting, it means that all data is locked in ASM. In this circumstance, without PRM-DUL, only senior Oracle experts can patch ASM internal structure, which is also a problem for oracle normal user.

 

PRM-DUL now can support two kinds of ASM data recovery:

 

  1. Once Disk Group cannot be mounted, PRM-DUL can read metadata, and clone ASM file from Disk Group
  2. Once Disk Group cannot be mounted, PRM-DUL can read ASM file and extract data, which supports data export, and data bridge 2 modes

 

 

 

 

PRM-DUL Software Introduction

ParnassusData Recovery Manager (PRM-DUL) was developed by Java, which ensured cross-platform ability. No matter AIX, Solaris, HPUNIX, Red-Hat, Oracle Linux, SUSE, or Window, It can be run smoothly.

 

PRM-DUL Supports OS & Platform:

 

Platform Name Supported
AIX POWER ü
Solaris Sparc ü
Solaris X86 ü
Linux X86 ü
Linux X86-64 ü
HPUX ü
MacOS ü

 

PRM-DUL Supported Database Version:

 

ORACLE DATABASE VERSION Supported
Oracle 7 û
Oracle 8 û
Oracle 8i û
Oracle 9i ü
Oracle 10g ü
Oracle 11g ü
Oracle 12c ü

 

Considering many servers run early OS like AIX 4.3 that can not install the latest JD, PRM-DUL was developed by JDS 1.4.

 

In addition, Oracle 10g database integrated JDK 1.4, and 11g with JDK 1.5. Therefore, users can run PRM-DUL directly without any JDK updates or installation

 

For users who needs JDK 1.4, please download from below link:

http://www.oracle.com/technetwork/java/javasebusiness/downloads/java-archive-downloads-javase14-419411.html

 

ParnassusData strongly recommend user to use Open JDK on Linux, for less bug and performance purpose.

 

Open JDK For Linux download Link:

 

Open jdk x86_64 for Linux 5 http://pan.baidu.com/s/1qWO740O
Tzdata-java x86_64 for Linux 5 http://pan.baidu.com/s/1gdeiF6r
Open jdk x86_64 for Linux 6 http://pan.baidu.com/s/1mg0thXm
Open jdk x86_64 for Linux 6 http://pan.baidu.com/s/1sjQ7vjf
Open jdk x86 for Linux 5 http://pan.baidu.com/s/1kT1Hey7
Tzdata-java x86 for Linux 5 http://pan.baidu.com/s/1kT9iBAn
Open jdk x86 for Linux 6 http://pan.baidu.com/s/1sjQ7vjf
Tzdata-java x86 for Linux 6 http://pan.baidu.com/s/1kTE8u8n

 

 

JDK on Other platform downloads:

AIX JAVA SDK 7 http://pan.baidu.com/s/1i3JvAlv
JDK Windows x86 http://pan.baidu.com/s/1qW38LhM
JDK Windows x86-64 http://pan.baidu.com/s/1qWDcoOk
Solaris JDK 7 x86-64bit http://pan.baidu.com/s/1gdzgSvh
Solaris JDK 7 x86-32bit http://pan.baidu.com/s/1mgjxFlQ
Solaris JDK 7 Sparc http://pan.baidu.com/s/1pJjX3Ft

 

 

 

PRM-DUL runs at least on JAVA JDK 1.4. Parnassus Data strongly recommends you to run it on JDK 1.6, since comparing JDK 1.5, JDK 1.6 has a lot of performance tuning in code and running environment. Therefore, it can be faster for recovering on JDK 1.6

 

 

PRM-DUL hardware requirement:

 

CPU At least 800 MHZ
Memory At least 512 MB
Disk At least 50 MB

 

PRM-DUL recommended hardware requirement:

 

CPU 2.0 GHZ
Memory 2 GB
Disk 2 GB

 

 

PRM-DUL Language Support

 

 

Language Character Set Encoding
Simplified/Traditional Chinese ZHS16GBK GBK
Simplified/Traditional Chinese ZHS16DBCS CP935
Simplified/Traditional Chinese ZHT16BIG5 BIG5
Simplified/Traditional Chinese ZHT16DBCS CP937
Simplified/Traditional Chinese ZHT16HKSCS CP950
Simplified/Traditional Chinese ZHS16CGB231280 GB2312
Simplified/Traditional Chinese ZHS32GB18030 GB18030
Japanese JA16SJIS SJIS
Japanese JA16EUC EUC_JP
Japanese JA16DBCS CP939
Korean KO16MSWIN949 MS649
Korean KO16KSC5601 EUC_KR
Korean KO16DBCS CP933
French WE8MSWIN1252 CP1252
French WE8ISO8859P15 ISO8859_15
French WE8PC850 CP850
French WE8EBCDIC1148 CP1148
French WE8ISO8859P1 ISO8859_1
French WE8PC863 CP863
French WE8EBCDIC1047 CP1047
French WE8EBCDIC1147 CP1147
Deutsch WE8MSWIN1252 CP1252
Deutsch WE8ISO8859P15 ISO8859_15
Deutsch WE8PC850 CP850
Deutsch WE8EBCDIC1141 CP1141
Deutsch WE8ISO8859P1 ISO8859_1
Deutsch WE8EBCDIC1148 CP1148
Italian WE8MSWIN1252 CP1252
Italian WE8ISO8859P15 ISO8859_15
Italian WE8PC850 CP850
Italian WE8EBCDIC1144 CP1144
Thai TH8TISASCII CP874
Thai TH8TISEBCDIC TIS620
Arabic AR8MSWIN1256 CP1256
Arabic AR8ISO8859P6 ISO8859_6
Arabic AR8ADOS720 CP864
Spanish WE8MSWIN1252 CP1252
Spanish WE8ISO8859P1 ISO8859_1
Spanish WE8PC850 CP850
Spanish WE8EBCDIC1047 CP1047
Portuguese WE8MSWIN1252 CP1252
Portuguese WE8ISO8859P1 ISO8859_1
Portuguese WE8PC850 CP850
Portuguese WE8EBCDIC1047 CP1047
Portuguese WE8ISO8859P15 ISO8859_15
Portuguese WE8PC860 CP860

 

 

Features Supported

 

Features Supported
Cluster Table YES
Inline or out-of-line LOBS, different chunk version and size, LOB partition YES
Heap table, partitioned or non-partitioned YES
Partition and Subpartition YES
Table With chained rows ,migrated rows,intra-block chaining YES
Bigfile Tablespace YES
ASM Automatic Storage Management 10g,11g,12c,diskgroups are dismounted YES
ASM  11g  Variable Extent Size YES
IOT, partitioned or non-partitioned YES(Future)
Basic Compressed Heap table YES(Future)
Advanced Compressed Heap Table NO
Exudates HCC Heap Table NO
Encrypted Heap Table NO
Table with Virtual Column NO

 

 

Attention: for virtual column、11g optimized default column, it may lose some column, and these two are new feature and less used in production environment.

 

PRM-DUL supports data type:

 

Data Type Supported
BFILE No
Binary XML No
BINARY_DOUBLE Yes
BINARY_FLOAT Yes
BLOB Yes
CHAR Yes
CLOB and NCLOB Yes
Collections (including VARRAYS and nested tables) No
Date Yes
INTERVAL DAY TO SECOND Yes
INTERVAL YEAR TO MONTH Yes
LOBs stored as SecureFiles Future
LONG Yes
LONG RAW Yes
Multimedia data types (including Spatial, Image, and Oracle Text) No
NCHAR Yes
Number Yes
NVARCHAR2 Yes
RAW Yes
ROWID, UROWID Yes
TIMESTAMP Yes
TIMESTAMP WITH LOCAL TIMEZONE Yes
TIMESTAMP WITH TIMEZONE Yes
User-defined types No
VARCHAR2 and VARCHAR Yes
XMLType stored as CLOB No
XMLType stored as Object Relational No

 

 

PRM-DUL supports ASM:

 

 

Function Supported
Directly extract Table data from ASM YES
Directly copy datafile from ASM YES
Repair ASM metadata YES
Draw ASM Structure by GUI Future

 

 

PRM-DUL installation and boot

 

It is not necessary to install PRM-DUL since it is Java developed software. Extract the ZIP package and click to RUN

 

 unzip   PRM-DUL_latest.zip

 

ParnassusData recommends you to run PRM-DUL with command line, therefore it will show more diagnose information

 

Windows:

 

  1. Make sure you had installed JDK and add JAVA to profile
  2. Double click ‘PRM-DUL.bat’ which is in the folder

 

PRM-DUL-DUL2

 

PRM-DUL.bat will launch PRM-DUL in the back

PRM-DUL-DUL3

 

Then, it pops up PRM-DUL main interface:

 

PRM-DUL-DUL4

 

 

Linux/Unix:

 

In Linux/Unix, use X Server for GUI

 

  1. Make sure you had installed JDK and add Java to profile
  2. cd to PRM-DUL folder, and run./PRM-DUL.sh to start the tool

PRM-DUL-DUL5

 

PRM-DUL-DUL6

 

 

PRM-DUL License Registration

 

ParnassusData Recovery Manager (PRM-DUL) needs license for full use. ParnassusData provide community version for user testing and demo. (Community version has no limits on ASM close, and we will add more function on it)

 

It needs license for full use of PRM-DUL. Now, clients have two kinds of license: Standard Edition and Enterprise Edition,

 

PRM-DUL-DUL7

 

Clients can purchase license via office website: www.parnassusdata.com, and it needs Database name. After your purchasing, you will receive an email which includes DBNAME and License Key

 

Once you have License Key, please register the software as below;

Menu Help => Register

Input DB NAME and you License Key, then click Register button

 

After registration, you don’t need to input license key again on your next boot.

PRM-DUL-DUL8

PRM-DUL-DUL9

 

 

Your successful registration information is in Help=>about

 

PRM-DUL-DUL10

 

PRM-DUL-DUL11

 

Case Study on Oracle database recovery via PRM-DUL

 

 

CASE 1: Truncate table by mistake

User D had truncated a table by mistake on production environment. The DBA tried to recover table from RMAN backup, and accidently the backup is unavailable. Therefore DBA decided to use PRM-DUL for rescuing all truncated data.

Since all database system files are healthy, DBA just needs to load SYSTEM table data file in dictionary mode and TRUNCATED table file. For example:

 

create table ParnassusData.torderdetail_his1  tablespace  users asselect * from parnassusdata.torderdetail_his; 

 

SQL> desc  ParnassusData.TORDERDETAIL_HIS
 Name                    Null?    Type
 ———————– ——– ————–
SEQ_ID                NOT NULL   NUMBER(10)
 SI_STATUS                        NUMBER(38)
 D_CREATEDATE                     CHAR(20)
 D_UPDATEDATE                     CHAR(20)
 B_ISDELETE                       CHAR(1)
 N_SHOPID                         NUMBER(10)
 N_ORDERID                        NUMBER(10)
 C_ORDERCODE                      CHAR(20)
 N_MEMBERID                       NUMBER(10)
 N_SKUID                          NUMBER(10)
 C_PROMOTION                      NVARCHAR2(5)
 N_AMOUNT                         NUMBER(7,2)
 N_UNITPRICE                      NUMBER(7,2)
 N_UNITSELLINGPRICE               NUMBER(7,2)
 N_QTY                            NUMBER(7,2)
 N_QTYFREE                        NUMBER(7,2)
 N_POINTSGET                      NUMBER(7,2)
 N_OPERATOR                       NUMBER(10)
 C_TIMESTAMP                      VARCHAR2(20)
 H_SEQID                          NUMBER(10)
 N_RETQTY                         NUMBER(7,2)
 N_QTYPOS                         NUMBER(7,2)select count(*) from ParnassusData.TORDERDETAIL_HIS; 

COUNT(*)

———-

984359

 

select bytes/1024/1024 from dba_segments where segment_name=’TORDERDETAIL_HIS’ and owner=’PARNASSUSDATA’;

 

BYTES/1024/1024

—————

189.71875

 

 

 

SQL> truncate table ParnassusData.TORDERDETAIL_HIS;

 

Table truncated.

 

SQL> select count(*) from ParnassusData.TORDERDETAIL_HIS;

 

COUNT(*)

———-

0

 

 

 

Run PRM-DUL, and select Tools =>Recovery Wizard

 

PRM-DUL-DUL12

 

Click Next

 

PRM-DUL-DUL13

 

Client did not user ASM storage, therefore just select ‘Dictionary Mode’:

PRM-DUL-DUL14

 

Next, we need to select some characters: including Endian bit order and DBNAME

Since Oracle datafiles have different Endian bit orders on different OS, please choose accordingly:

 

Solaris[tm] OE (32-bit) Big
Solaris[tm] OE (64-bit) Big
Microsoft Windows IA (32-bit) Little
Linux IA (32-bit) Little
AIX-Based Systems (64-bit) Big
HP-UX (64-bit) Big
HP Tru64 UNIX Little
HP-UX IA (64-bit) Big
Linux IA (64-bit) Little
HP Open VMS Little
Microsoft Windows IA (64-bit) Little
IBM zSeries Based Linux Big
Linux x86 64-bit Little
Apple Mac OS Big
Microsoft Windows x86 64-bit Little
Solaris Operating System (x86) Little
IBM Power Based Linux Big
HP IA Open VMS Little
Solaris Operating System (x86-64) Little
Apple Mac OS (x86-64) Little

 

In traditional UNIX, AIX (64-bit), UP-UNIX (64-bit), it use Big Endian bit order,

 

PRM-DUL-DUL15

 

Usually, Linux X86/64, Windows remain default Little Endian:

PRM-DUL-DUL16

 

Attention: if your data file was generated on AIX, if you want to recover data on window, please select original Big Endian format.

Since the data file is on Linux X86, we select Little as Endian, and input database name. (The input database name can be different from DB_NAME found in datafile header,  the input database name is just an alias. PRM-DUL will check if your PRM-DUL license is valid , the valid license key is generated based on DB_NAME found in datafile header)

PRM-DUL-DUL17

 

Click Next =>Click Choose Files

 

Usually, if the database is not too big, we could select all data files together; if the database capacity is huge and DBA knows the data location, at least you should  select both SYSTEM tablespace and specified datafile.

 

Attention, the GUI Supports Ctrl + A & Shift short keys:

 

PRM-DUL-DUL18

 

PRM-DUL-DUL19

 

Specify the Block Size (Oracle data block size) according to the real circumstance. For example, if default DB_BLOCK_SIZE is 8K, but part of tablespaces’ block size is 16k,then user has to specify them as correct block size one by one.

 

OFFSET setting are just for raw device storage mode, for example: on AIX, based on LV of  normal VG, the offset will be 4k OFFSET.

 

If you are using raw device but don’t know what the OFFSET is, please use dbfsize tool which is under $ORACLE_HOME/bin

 

 

$dbfsize /dev/lv_control_01Database file: /dev/lv_control_01Database file type: raw device without 4K starting offsetDatabase file size: 334 16384 byte blocks

 

Since all data file block size here is 8K and there is no OFFSET, please click load:

 

PRM-DUL-DUL20

 

PRM-DUL read Oracle dictionary directly, and recreate a new dictionary in embedded database. It can help us to recuse most types of data in Oracle DB.

 

PRM-DUL-DUL21

 

After recreating dictionary, the dialog show character information:

PRM-DUL-DUL22

 

Attention: PRM-DUL supports multiple languages and multiple Oracle character set. However, the prerequisite is the OS had installed specified language packages. For example, on Windows, if you didn’t install Chinese language package, even Oracle database characters are independent and support ZHS16GBK, PRM-DUL would display Chinese as messy code. Once the Chinese language package is installed on OS, PRM-DUL can display multibyte character set properly.

 

Similarly, on Linux, it need font-Chinese language package.

 

[oracle@mlab2 log]$ rpm -qa|grep chinesefonts-chinese-3.02-12.el5

 

After loading, in PRM-DUL GUI, it displayed database tree diagram by database users.

 

Click Users, you can find more users, for example, if user want to recover a table under PARNASSUSDATA SCHEMA, click PARNASSUSDATA, and double click that table:

 

PRM-DUL-DUL23

 

Previously TORDERDETAIL_HIS had been truncated, so it  won’t show any data . Please select unload truncated Data:

 

PRM-DUL-DUL24

 

PRM-DUL will scan the tablespace and extract data from truncated table.

PRM-DUL-DUL25

 

PRM-DUL-DUL26

 

As in the above picture, the truncated TORDERDETAIL_HIS had exported 984359 record, and saved to specified falt file.

In addition, it generated SQLLDR control file for text data importing

 

$ cd /home/oracle/PRM-DUL/PRM-DULdata/parnassus_dbinfo_PARNASSUSDATA/$ ls -l ParnassusData*-rw-r–r– 1 oracle oinstall       495 Jan 18 08:31 ParnassusData.torderdetail_his.ctl-rw-r–r– 1 oracle oinstall 191164826 Jan 18 08:32 ParnassusData.torderdetail_his.dat.truncated

 

$ cat ParnassusData.torderdetail_his.ctl

LOAD DATA

INFILE  ‘ParnassusData.torderdetail_his.dat.truncated’

APPEND

INTO TABLE ParnassusData.torderdetail_his

FIELDS TERMINATED BY ‘ ‘

OPTIONALLY ENCLOSED BY ‘”‘

TRAILING NULLCOLS (

“SEQ_ID” ,

“SI_STATUS” ,

“D_CREATEDATE” ,

“D_UPDATEDATE” ,

“B_ISDELETE” ,

“N_SHOPID” ,

“N_ORDERID” ,

“C_ORDERCODE” ,

“N_MEMBERID” ,

“N_SKUID” ,

“C_PROMOTION” ,

“N_AMOUNT” ,

“N_UNITPRICE” ,

“N_UNITSELLINGPRICE” ,

“N_QTY” ,

“N_QTYFREE” ,

“N_POINTSGET” ,

“N_OPERATOR” ,

“C_TIMESTAMP” ,

“H_SEQID” ,

“N_RETQTY” ,

“N_QTYPOS”

)

 

 

When you import data to original table, ParnassusData strongly recommends you to modify SQLLDR table name as a temp table, it would not impact your previous environment.

 

 

 

 

$ sqlldr control=ParnassusData.torderdetail_his.ctl direct=yUsername:/ as sysdba//user SQLLDR to import data 

//Minus can be used for data comparing

select * from ParnassusData.torderdetail_his minus select * from parnassus.torderdetail_his;

 

no rows selected

 

 

 

After diffing, there is no difference between original data and PRM-DUL exported data.

PRM-DUL successfully recovered the truncated table

 

 

CASE 2: Recovery mis-truncated table by DataBridge

 

In Case 1, we use traditional unload+sqlldr for data recovery, but actually ParnassusData would like to strongly recommend using  DataBridge Feature for recovering.

 

Why use DataBridge?

 

  • Traditional unload+sqlldr means a copy of data needs to be saved as flat file on filesystem first, data has to be loaded into Unicode text file and then inserted into destination database by sqlldr, this will take double storage and double time.
  • DataBridge can extract data from source DB and export to destination DB without any intermediary.
  • Once the data arrived destination DB, user can begin to validate them.
  • If source and destination database located on different servers, then read/write IO will be balanced on two servers , MTTR will be saved.
  • If DataBridge is used in truncated table recovery, it is very convenient that truncated data can be exported back to problem database directly.

 

DataBridge is very simple and convenient. Right click the table on the left side, and select DataBridge:

 

PRM-DUL-DUL27

 

 

As the first time to use DataBridge, DB connection information is necessary, which is similar with SQL Developer connection, including: DB host, Port, Service_Name and Account information.

Attention: DataBridge will save data to the specified schema given in the DB connection.

 

PRM-DUL-DUL28

 

AS above G10R25 connection, user is maclean, and the corresponding Oracle Easy Connection is

192.168.1.191:1521/G10R25。

 

After inputting the account/connection information, you can use test for connection testing. If return message is “ Connect to DB server successfully “, the connection is done and click to save.

 

PRM-DUL-DUL29

 

After saving connection and go to DataBridge window, please select Connection G10R25 at the drop down list.

PRM-DUL-DUL30

 

If your DB connection is not in the drop down list, please click DB connection Button, which is highlighted in red.

PRM-DUL-DUL31

 

After selecting DB Connection, Tablespace dropdown list will be selectable:

PRM-DUL-DUL32

 

 

Attention on DataBridge recovering truncated/dropped table: when you recovering truncated/dropped and insert data back to source DB, users should choose another tablespace which diffs from the original tablespace. If export data into same tablespace, oracle will reuse space which stores truncated/dropped table, and can make data overwritten, we will lose the last resort to recover the data.

 

For example, we truncated a table and would like to user DataBridge to recover data back to source database, but we would like to use another table name. Original table name is torderdetail_his, and user can select “if need to remap table” and input proper destination name, as below:

PRM-DUL-DUL33

 

 

 

Attention: 1) For destination DB which already had the same table name, PRM-DUL will not recreate a table but append all recovered data. 2) For destination DB which did not have source table name, PRM-DUL would try to create table and recover the data.

 

In this case, we would recover Truncated data, therefore, please select “if data truncated?” checkbox, Or, PRM-DUL would do regular data extraction, but not Truncated data.

 

Truncate recovery methodology is: Oracle will only update table DATA_OBJECT_ID in data dictionary and segment header. Therefore, the real data will not be overwritten. Due to the difference between dictionary and DATA_OBJECT_ID, Oracle server process will not read truncated data while scanning table. But, the real data is still there.

 

PRM-DUL will try to scan 10M-bytes blocks which are behind of the table’s segment header, if some blocks with smaller DATA_OBJECT_ID than the object’s current DATA_OBJECT_ID, then PRM-DUL thinks it find something useful.

 

 

There is a blank input field called ”if to specify data object id”, which let user input Data Object ID. Usually, you don’t need to input any value, unless the recovery does not work. We suggest user to contact ParnassusData for help.

 

Click DataBridge button ,then it will start extracting if the configuration is done.

 

PRM-DUL-DUL34

 

DataBridge will display the successfully rescued rows and elapsed time.

 

PRM-DUL-DUL35

 

Case 3: Oracle Dictionary Corrupted, DB can not be open

 

DBA of company D deleted SYS.TS$ (A bootstrap Table) by mistake, this cause Oracle DB can not be open

 

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit ProductionWith the Partitioning, Automatic Storage Management, OLAP, Data Miningand Real Application Testing options 

INSTANCE_NAME

—————-

ASMME

 

SQL>

SQL>

SQL> select count(*) from sys.ts$;

 

COUNT(*)

———-

5

 

SQL> delete ts$;

 

5 rows deleted.

 

SQL> commit;

 

Commit complete.

 

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

 

Database mounted.

ORA-01092: ORACLE instance terminated. Disconnection forced

ORA-01405: fetched column value is NULL

Process ID: 5270

Session ID: 10 Serial number: 3

 

Undo initialization errored: err:1405 serial:0 start:3126020954 end:3126020954 diff:0 (0 seconds)

Errors in file /s01/diag/rdbms/asmme/ASMME/trace/ASMME_ora_5270.trc:

ORA-01405: fetched column value is NULL

Errors in file /s01/diag/rdbms/asmme/ASMME/trace/ASMME_ora_5270.trc:

ORA-01405: fetched column value is NULL

Error 1405 happened during db open, shutting down database

USER (ospid: 5270): terminating the instance due to error 1405

Instance terminated by USER, pid = 5270

ORA-1092 signalled during: ALTER DATABASE OPEN…

opiodr aborting process unknown ospid (5270) as a result of ORA-1092

 

 

 

In this circumstance, data dictionary had been damaged; therefore it would be very hard to open the database.

 

Then, we can use PRM-DUL rescue data in DB. Following processes as below:

 

 

  1. Recovery Wizard
  2. Select Data Dictionary Mode
  3. Choose Big or Little Endian , and input DB NAME
  4. Click Load for database loading
  5. Extract Tables

 

PRM-DUL-DUL36

 

 

Case 4: Deleted SYSTEM tablespace by mistake

 

A System Administrator of company D who deleted SYSTEM tablespace by mistake and make DB can not be open. Unfortunately, there is no RMAN backup available. Therefore, for company D try to use PRM-DUL to recover all data.

 

In this circumstance, run PRM-DUL and go into Recovery Wizard. Select “Non-Dictionary mode”:

PRM-DUL-DUL37

 

PRM-DUL-DUL38

In No-dictionary mode, we have to select DB Character Set and DB National Character Set. Because of while losing SYSTEM tablespace, database cannot find character set information.

 

Similarly as case 1, select all data (not including temp file), and correct Block Size and OFFSET

 

PRM-DUL-DUL39

 

Then click scan button. Then PRM-DUL will scan all segment header and extents in datafile, and record it into SEG$.DAT and EXT$.DAT. In Oracle, each partition table or non-partition table has a segment header. Once we find segment header, we could find the whole table extent map information. Via extent map, we can get all record.

 

There is one exception, for example, there is one non-partition table that is stored in two database files. The segment header and half data are stored in datafile A, and the others are on datafile B.  While system tablespace and datafile A are lost, PRM-DUL couldn’t find segment header associated with problem table, but it can scan datafile B and get the rest extent map.

In order to recover data via segment header and extent map in no-dictionary mode.

PRM-DUL will create two files: SEG$.DAT(stores segment header info) and EXT$.DAT(stores extent info) ,which is also recorded in PRM-DUL embedded database.

 

PRM-DUL-DUL41 PRM-DUL-DUL40

 

After scan, there is database icon on the left.

 

Meanwhile, there are 2 option:

 

  • Scan Tables From Segments:
    • System tablespace lost, but user tablespace datafiles are there
  • Scan Tables From Extents
    • Only used when truncated data can not be recovered by Dictionary-Mode
    • Both system tablespace and segment header are lost

 

It is not necessary to use mode “Scan Tables From Extents” at the first time, unless you can’t find your data by “Scan Tables From Segment”.
Scan tables From segments should be your first choice.

 

PRM-DUL-DUL42

 

After scanning tables from segments, there will be a tree diagram on the left.

 

PRM-DUL-DUL43

 

 

Scan Tables is for constructing the data based on segment header in SEG$. The name of each node in the diagram is named by obj+ DATA OBJECT ID.

Click on node and check right side:

 

 

PRM-DUL-DUL44

 

 

 

Intelligence on Data Analysis

 

Because of SYSTEM tablespace lost, there is not data structure information available in NO-Dictionary mode. The column information includes column name and data type. All these are storage in dictionary but not in table. Therefore, PRM-DUL need to guess the data type. PRM-DUL has a JAVA pre analysis algorithm, and has the ability to analysis more than 10 kinds of types.、

 

Intelligence analysis can successfully guess 90% of columns in most of circumstances

 

On the right side, the meaning of columns:

 

  • Col1 no
  • Seen Count
  • MAX SIZE
  • PCT NULL
  • String Nice
  • Number Nice
  • Date Nice
  • Timestamp Nice
  • Timestamp with timezone Nice

 

 

Sample Data Analysis:

 

PRM-DUL-DUL45

 

 

 

Intelligence Analysis will analyze 10 records and display the results. These results will help client to know the column information.

 

As in the picture, the there are 10 records which had been displayed all.

 

 

TRY TO ANALYZE UNKNOWN column type:

 

 

PRM-DUL-DUL46

 

 

If PRM-DUL cannot recognize the column’s data type , you can specify data type by yourself.

 

So far, PRM-DUL does not support below types:

XDB.XDB$RAW_LIST_T、XMLTYPE、Customized TYPE

 

Unload Statement:

PRM-DUL generated unload scripts, and these scripts can be only used by PRM-DUL support engineers.

 

 

 

PRM-DUL-DUL47

 

 

In “Non-Dictionary Mode”, Data Bridge is also applicable. Comparing ” Dictionary Mode”, the manger difference that the user can define the type in data transferring. As below picture, the column type is UNKNOW. These types might be PRM-DUL unsupported types for example: XML and etc.

 

If the user know the data type in this table (from schema design documents), it is necessary to specify the correct types manually.

 

PRM-DUL-DUL48

 

CASE 5:deleted System Tablespace and Part of User tablespace datafile by mistake

 

User D deleted the system tablespace and part of user tablespace datafile by mistake.

In this circumstance, part of user data table was deleted, and this might includes datafile which stored segment header. Therefore it is better to use “Scan Tables From Extents” than” Scan Tables From Segment Header”.

 

Steps as Below:

 

  1. Go to Recovery Wizard, select No-Dictionary mode,and added all usable data file. Then process them to scan database.
  2. Select database, and right click Scan Tables From Extents
  3. Analyze the data and implement data extraction and Data Bright
  4. Following steps are the same with Case 4

 

CASE 6: rescue datafile from damaged diskgroup which can’t be mounted

 

User D chooses ASM instead of other filesystem. Since there are many bugs in version 11.2.0.1, it may happen that ASM DISKGROUP cannot be mounted or it does not work after repairing ASM Disk Header.

In this circumstance, user can use ASM Files Clone feature of PRM-DUL to rescue datafile from damaged ASM DiskGroup directly.

 

  1. Open main interface, and select ASM File(s) Clone:

PRM-DUL-DUL49

 

 

  1. Enter ASM Disks Window, and click SELECt…to add ASM Disks. For example: /dev/asm-disk5(linux). And click ASM analyze.

PRM-DUL-DUL50

 

PRM-DUL-DUL51

 

PRM-DUL-DUL52

 

 

 

ASM Files Clone feature will analyze ASM Disk header, in order to finding Disk group file and File Extent Map. All the information is recorded into PRM-DUL embedded database.    PRM-DUL can collect all Metadata, and analyze to show diagram.

 

PRM-DUL-DUL53

 

 

  1. After analysis of ASM Analyze, PRM-DUL will find the file list in Disk groups. Users can select the datafile/archivelog which need to be cloned to destination folder.

 

Click ASM Clone to start…

 

PRM-DUL-DUL54

 

There is progress bar while file cloning.

PRM-DUL-DUL55

 

 

 

ASM File Clone log as below:

 

 

Preparing selected files…Cloning +DATA2/ASMDB1/DATAFILE/TBS2.256.839732369:……………………..1024MB………………………………..2048MB

………………………………..3072MB

………………………………….4096MB

………………………………..5120MB

………………………………….6144MB

……………………………….7168MB

…………………………………8192MB

…………………………………9216MB

…………………………………10240MB

…………………………………11264MB

…………………………………..12288MB

…………………………………….13312MB

…………………………….14336MB

……………………………………..15360MB

……………………………….16384MB

…………………………………17408MB

…………………………………18432MB

…………………………………………………………………………………………….19456MB

……………………………………

Cloned size for this file (in byte): 21475885056

 

Cloned successfully!

 

 

Cloning +DATA2/ASMDB1/ARCHIVELOG/2014_02_17/thread_1_seq_47.257.839732751:

……

Cloned size for this file (in byte): 29360128

 

Cloned successfully!

 

 

Cloning +DATA2/ASMDB1/ARCHIVELOG/2014_02_17/thread_1_seq_48.258.839732751:

……

Cloned size for this file (in byte): 1048576

 

Cloned successfully!

 

 

 

 

All selected files were cloned done.

 

 

 

 

 

  1. It is necessary to validate cloned data via “dbv” or “rman validate”, for example:

 

rman target /RMAN> catalog datafilecopy ‘/home/oracle/asm_clone/TBS2.256.839732369.dbf’; 

cataloged datafile copy

datafile copy file name=/home/oracle/asm_clone/TBS2.256.839732369.dbf RECID=2 STAMP=839750901

 

RMAN> validate datafilecopy ‘/home/oracle/asm_clone/TBS2.256.839732369.dbf’;

 

Starting validate at 17-FEB-14

using channel ORA_DISK_1

channel ORA_DISK_1: starting validation of datafile

channel ORA_DISK_1: including datafile copy of datafile 00016 in backup set

input file name=/home/oracle/asm_clone/TBS2.256.839732369.dbf

channel ORA_DISK_1: validation complete, elapsed time: 00:03:35

List of Datafile Copies

=======================

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN

—- —— ————– ———— ————— ———-

16   OK     0              2621313      2621440         1945051

File Name: /home/oracle/asm_clone/TBS2.256.839732369.dbf

Block Type Blocks Failing Blocks Processed

———- ————– —————-

Data       0              0

Index      0              0

Other      0              127

 

Finished validate at 17-FEB-14

 

 

When using PRM-DUL in ASM of ASMLIB?

Simple and Clear: asmlib related ASM DISK is stored in OS as ll /dev/oracleasm/disks. For example: Add files of /dev/oracleasm/disks into PRM-DUL ASM DISK

 

$ll /dev/oracleasm/diskstotal 0brw-rw—-  1 oracle dba 8,  97 Apr 28 15:20 VOL001brw-rw—-  1 oracle dba 8,  81 Apr 28 15:20 VOL002brw-rw—-  1 oracle dba 8,  65 Apr 28 15:20 VOL003

brw-rw—-  1 oracle dba 8,  49 Apr 28 15:20 VOL004

brw-rw—-  1 oracle dba 8,  33 Apr 28 15:20 VOL005

brw-rw—-  1 oracle dba 8,  17 Apr 28 15:20 VOL006

brw-rw—-  1 oracle dba 8, 129 Apr 28 15:20 VOL007

brw-rw—-  1 oracle dba 8, 113 Apr 28 15:20 VOL008

 

 

CASE 7: DB(stored in ASM) can not be opened

 

One of CRM database in company D can’t be opened due to adding disk which has I/O error into ASM diskgroup. This operation generated some corrupted block in datafile of system tablespace, and user failed to open DB any more.

 

In the circumstance, we can use PRM-DUL ASM Diskgroup to clone all datafile out of ASM.

 

Or, user can also use “Dictionary Mode(ASM)” to recover data from this ASM environment . Steps as below:

 

  1. Recovery Wizard
  2. Dictionary Mode(ASM)
  3. Add ASM DISK (all ASM DISK in your recovery disks)
  4. Click ASM analyze
  5. Select suitable Endian
  6. In ASM analyze, it lists all database file, or click “select all”

Click “load”, following steps are the same with case3

 

PRM-DUL-DUL56 PRM-DUL-DUL57 PRM-DUL-DUL58 PRM-DUL-DUL59 PRM-DUL-DUL60

 

 

CASE 8: Recover Lost system tablespace in ASM

 

User D deleted system tablespace FILE#=1 datafile and user tablespace. This make  alter database open failed.

In this circumstance, user can use” Non-Dictionary Mode (ASM)” to recover data.

 

Steps as below:

 

  1. Recovery Wizard
  2. Non-Dictionary Mode (ASM)
  3. Add necessary ASM Disk
  4. Click ASM analyze
  5. Select the suitable Endian and Character set. (Manually select character set due to Non-Dictionary Mode)
  6. Select all data file, or click “Select all”
  7. Click “scan”, following steps are the same with Case 3

PRM-DUL-DUL61

 

PRM-DUL-DUL62

 




PRM-DUL-DUL63

 

PRM-DUL-DUL64

 

CASE 9: Recover DROP TABLESPACE Data

 

User D dropped a tablespace(“DROP TABLESAPCE INCLUDING CONTENTS”) by mistake. They want to recover data resided in that tablespace, but there is no RMAN backup.

Therefore, we can use PRM-DUL No-Dictionary mode to recover data. In this way, we can extract most data. However, the data is not mapping to the dictionary. Users need to manually recognize the table. Since it changed data dictionary by DROPPING TABLE and deleted objects in OBJ$, we can not have the relationship between DATA_OBJECT_ID and OBJECT_NAME. Below is the instruction of getting mapping.

 

 

 

select tablespace_name,segment_type,count(*) from dba_segments where owner=’PARNASSUSDATA’  group by tablespace_name,segment_type;TABLESPACE SEGMENT_TYPE      COUNT(*)———- ————— ———-

USERS      TABLE                  126

USERS      INDEX                  136

 

SQL> select count(*) from obj$;

 

COUNT(*)

———-

75698

 

 

SQL> select current_scn, systimestamp from v$database;

 

CURRENT_SCN

———–

SYSTIMESTAMP

—————————————————————————

1895940

25-4月 -14 09.18.00.628000 下午 +08:00

 

 

 

SQL> select file_name from dba_data_files where tablespace_name=’USERS’;

 

FILE_NAME

——————————————————————————–

H:\PP\MACLEAN\ORADATA\PARNASSUS\DATAFILE\O1_MF_USERS_9MNBMJYJ_.DBF

 

 

SQL> drop tablespace users including contents;

 

 

C:\Users\maclean>dir H:\APP\MACLEAN\ORADATA\PARNASSUS\DATAFILE\O1_MF_USERS_9MNBMJYJ_.DBF

 

The volume is entertainment in drive H and SN is A87E-B792

 

H:\APP\MACLEAN\ORADATA\PARNASSUS\DATAFILE

 

The drive can not find the file

 

 

Here, we can use other file recovery tool for data file recovery, for example: Undeleter on Windows.

 

 

PRM-DUL-DUL65

 

 

Startup PRM-DUL => recovery Wizard => No-Dictionary

 

PRM-DUL-DUL66 PRM-DUL-DUL67

 

 

This is No-Dictionary mode, and please select correct character set

 

 

PRM-DUL-DUL68

 

 

Add the files recovered and click scan

 

 

PRM-DUL-DUL69

 

PRM-DUL-DUL70

 

 

Start from the head segments, if it can not find all table, try to use extend scan:

 

PRM-DUL-DUL71

 

 

You can find lots of node named OBJXXXXX,this name is combination of “OBJ” and DATA_OBJECT_ID.  We need some guy who is familiar with schema design and application data, he can clarify the relationship between data and table.

 

PRM-DUL-DUL72

 

 

If there is no body can clarify the relationship between data and table, try below methods:

 

In this case, only user tablespace had been dropped and Oracle still works, and to get the mapping of DATA_OBJECT_ID and table name by FLASHBACK QUERY.

 

SQL>  select count(*) from sys.obj$;COUNT(*)———-75436

 

 

 

 

 

 

 

SQL> select count(*) from sys.obj$ as of scn 1895940;

select count(*) from sys.obj$ as of scn 1895940

*

Error:

ORA-01555: Snapshot is too old,

 

Try to use DBA_HIST_SQL_PLAN of AWR and find the mapping between OBJECT# and OBJECT_NAME in recent 7 days.

 

SQL> desc DBA_HIST_SQL_PLAN

NAME                                        NULL? TYPE

—————————————– ——– ———————–

DBID                                      NOT NULL NUMBER

SQL_ID                                    NOT NULL VARCHAR2(13)

PLAN_HASH_VALUE                           NOT NULL NUMBER

ID                                        NOT NULL NUMBER

OPERATION                                          VARCHAR2(30)

OPTIONS                                            VARCHAR2(30)

OBJECT_NODE                                        VARCHAR2(128)

OBJECT#                                            NUMBER

OBJECT_OWNER                                       VARCHAR2(30)

OBJECT_NAME                                        VARCHAR2(31)

OBJECT_ALIAS                                       VARCHAR2(65)

OBJECT_TYPE                                        VARCHAR2(20)

OPTIMIZER                                          VARCHAR2(20)

PARENT_ID                                          NUMBER

DEPTH                                              NUMBER

POSITION                                           NUMBER

SEARCH_COLUMNS                                     NUMBER

COST                                               NUMBER

CARDINALITY                                        NUMBER

BYTES                                              NUMBER

OTHER_TAG                                          VARCHAR2(35)

PARTITION_START                                    VARCHAR2(64)

PARTITION_STOP                                     VARCHAR2(64)

PARTITION_ID                                       NUMBER

OTHER                                              VARCHAR2(4000)

DISTRIBUTION                                       VARCHAR2(20)

CPU_COST                                           NUMBER

IO_COST                                            NUMBER

TEMP_SPACE                                         NUMBER

ACCESS_PREDICATES                                  VARCHAR2(4000)

FILTER_PREDICATES                                  VARCHAR2(4000)

PROJECTION                                         VARCHAR2(4000)

TIME                                               NUMBER

QBLOCK_NAME                                        VARCHAR2(31)

REMARKS                                            VARCHAR2(4000)

TIMESTAMP                                          DATE

OTHER_XML                                          CLOB

 

 

For exmaple:

 

select object_owner,object_name,object# from DBA_HIST_SQL_PLAN where sql_id=’avwjc02vb10j4′

 

OBJECT_OWNER         OBJECT_NAME                                 OBJECT#

——————– —————————————- ———-

 

PARNASSUSDATA        TORDERDETAIL_HIS                              78688

 

 

 

Use below scrip for the mapping relationship between OBJECT_ID and OBJECT_NAME

 

Select * from

(select object_name,object# from DBA_HIST_SQL_PLAN

UNION select object_name,object# from GV$SQL_PLAN) V1 where V1.OBJECT# IS NOT NULL minus select name,obj# from sys.obj$;

 

select obj#,dataobj#, object_name from WRH$_SEG_STAT_OBJ where object_name not in (select name from sys.obJ$) order by object_name desc;

 

 

another script:

SELECT tab1.SQL_ID,

current_obj#,

tab2.sql_text

FROM DBA_HIST_ACTIVE_SESS_HISTORY tab1,

dba_hist_sqltext tab2

WHERE tab1.current_obj# NOT IN

(SELECT obj# FROM sys.obj$

)

AND current_obj#!=-1

AND tab1.sql_id  =tab2.sql_id(+);

 

 

 

Attention: Since it relies on AWR repository, the mapping table is not that accurate and exact.

 

 

 

 

CASE 10: Recover Data after Dropping Table by mistake.

 

User D dropped one most important application table in ASM without any backup. Oracle introduced recyclebin feature in 10g. Please check whether the dropped table is in recyclebin by DBA_RECYCLEBINS view. If there is , try to recover data back by “flashback to before drop”. Or, we can use PRM-DUL for recovery.

Recovery steps by PRM-DUL

  1. OFFLINE the table space that the dropped table locates.
  2. Find the DATA_OBJECT_ID of dropped table by query data dictionary or logminer. If not successfully, then user has to recognize this table in No-dictionary mode.
  3. Start PRM-DUL, go to No-dictionary mode, and add all data files of dropped data file. Then SCAN DATABASE+SCAN TABLE from Extent MAP
  4. Locate the data table by DATA_OBJECT_ID in object tress, and insert data back by DataBridge

 

SQL> select count(*) from “MACLEAN”.”TORDERDETAIL_HIS”;COUNT(*)———-984359

 

SQL>

SQL> create table maclean.TORDERDETAIL_HIS1 as select * from  maclean.TORDERDETAIL_HIS;

 

Table created.

 

SQL> drop table maclean.TORDERDETAIL_HIS;

 

Table dropped.

 

We can find the general DATA_OBJECT_ID by logminer or similar method in “CASE 9”

 

 

 

 

 

EXECUTE DBMS_LOGMNR.ADD_LOGFILE( LOGFILENAME => ‘/oracle/logs/log1.f’, OPTIONS => DBMS_LOGMNR.NEW);EXECUTE DBMS_LOGMNR.ADD_LOGFILE( LOGFILENAME => ‘/oracle/logs/log2.f’, OPTIONS => DBMS_LOGMNR.ADDFILE);Execute DBMS_LOGMNR.START_LOGMNR(DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG+DBMS_LOGMNR.COMMITTED_DATA_ONLY);

 

SELECT * FROM V$LOGMNR_CONTENTS ;

 

EXECUTE DBMS_LOGMNR.END_LOGMNR;

 

Although, there is no DATA_OBJECT_ID, if the table amount is not big, we can manually recognize the data table

 

OFFLINE table space of dropped table

 

SQL> select tablespace_name from dba_segments where segment_name=’TPAYMENT’;TABLESPACE_NAME——————————USERS

 

SQL> select file_name from dba_data_files where tablespace_name=’USERS’;

 

FILE_NAME

—————————————————————-

+DATA1/parnassus/datafile/users.263.843694795

 

SQL> alter tablespace users offline;

 

Tablespace altered.

 

 

Start PRM-DUL in NON-DICT mode, and add all data to SCAN DATABASE+SCAN TABLE From Extents:

 

PRM-DUL-DUL73

 

PRM-DUL-DUL74

 

 

Add related ASM Disks and click ASM Analyze

PRM-DUL-DUL75

 

Select the character set in Non-Dict mode

 

 

PRM-DUL-DUL76

 

 

Select the data files of dropped table, and click scan

PRM-DUL-DUL77

 

 

PRM-DUL-DUL78

 

Generate database name and right click scan tables from extents:

 

PRM-DUL-DUL79

 

 

PRM-DUL-DUL80

 

 

Recognize TORDERDETAIL_HIS table which is mapped to DATA_OBJECT_ID=82641 manually and insert back to the database by DataBridge


PRM-DUL-DUL81 PRM-DUL-DUL82

 

PRM-DUL-DUL83

 

FAQ

  1. How to get database character set?

 

You can find your database character by Oracle Alert.log

 

[oracle@mlab2 trace]$ grep  -i character alert_Parnassus.logDatabase Characterset is US7ASCIIDatabase Characterset is US7ASCIIalter database character set INTERNAL_CONVERT AL32UTF8Updating character set in controlfile to AL32UTF8

Synchronizing connection with database character set information

Refreshing type attributes with new character set information

Completed: alter database character set INTERNAL_CONVERT AL32UTF8

alter database national character set INTERNAL_CONVERT UTF8

Completed: alter database national character set INTERNAL_CONVERT UTF8

Database Characterset is AL32UTF8

Database Characterset is AL32UTF8

Database Characterset is AL32UTF8

 

 

  1. PRM-DUL failed with GC ” gc warning: Repeated allocation of very large block (appr.size 512000)”

 

So far, most of problem is caused by not recommended Java environment. Especially, on Linux, default java environment is redhat gcj java. ParnassusData suggest Open JDK 1.6 for PRM-DUL, and use $JAVA_HOME/bin/java –jar PRM-DUL.jar for PRM-DUL boot.

 

Open JDK For Linux download Link:

 

Open jdk x86_64 for Linux 5 http://pan.baidu.com/s/1qWO740O
Tzdata-java x86_64 for Linux 5 http://pan.baidu.com/s/1gdeiF6r
Open jdk x86_64 for Linux 6 http://pan.baidu.com/s/1mg0thXm
Open jdk x86_64 for Linux 6 http://pan.baidu.com/s/1sjQ7vjf
Open jdk x86 for Linux 5 http://pan.baidu.com/s/1kT1Hey7
Tzdata-java x86 for Linux 5 http://pan.baidu.com/s/1kT9iBAn
Open jdk x86 for Linux 6 http://pan.baidu.com/s/1sjQ7vjf
Tzdata-java x86 for Linux 6 http://pan.baidu.com/s/1kTE8u8n

 

JDK on Other platform downloads:

AIX JAVA SDK 7 http://pan.baidu.com/s/1i3JvAlv
JDK Windows x86 http://pan.baidu.com/s/1qW38LhM
JDK Windows x86-64 http://pan.baidu.com/s/1qWDcoOk
Solaris JDK 7 x86-64bit http://pan.baidu.com/s/1gdzgSvh
Solaris JDK 7 x86-32bit http://pan.baidu.com/s/1mgjxFlQ
Solaris JDK 7 Sparc http://pan.baidu.com/s/1pJjX3Ft

 

Oracle JDK downloads:

 

http://www.oracle.com/technetwork/java/javasebusiness/downloads/java-archive-downloads-javase6-419409.html#jdk-6u45-oth-JPR

 

  1. If you find PRM-DUL bug, how to report bug to ParnassusData?

ParnassusData recommend anyone to report bug, just send report_bugs@parnassusdata.com. Suggest submit bug environment, including Java environment and Oracle database Environment.

 

  1. What should I do if it PRM-DUL failed with

Error: no `server’ JVM at `D:\Program Files (x86)\Java\jre1.5.0_22\bin\server\jvm.dll’.

If user just installed JAVA Runtime Environment JRE, no JDK, please start PRM-DUL without –server option. This option does not exist in the version before JRE 1.5, and there is supposed to have an error.

ParnassusData suggests Open JDK 1.6 or above

 

Below link to download JDK 1.6

http://www.oracle.com/technetwork/java/javasebusiness/downloads/java-archive-downloads-javase6-419409.html#jdk-6u45-oth-JPR

 

  1. Why does PRM-DUL display Chinese as messy code?

 

So far, there are two reasons for Chinese encoding problem:

  • The OS does not have Chinese language pack, PRM-DUL can not display Chinese correctly
  • If OS have language package installed, please use Open JDK1.6 or above. There might be some problem in JDK1.4

 

  1. Is there any forum for PRM-DUL?

 

Now we have Chinese forum for PRM-DUL, below is the link:

http://t.askmaclean.com/forum-24-1.html

 

Find More

 

 

 

Resource:               http://www.parnassusdata.com/resources/

Technical Support:        service@parnassusdata.com

Sales:                      sales@parnassusdata.com

Download Software:     http://www.parnassusdata.com/

Contact:          http://www.parnassusdata.com/zh-hans/contact

 

 

 

 

Conclusion

 

 

 

 

ParnassusData Corporation, Shanghai, GaoPing Road No. 733 . China

Phone: (+86) 13764045638

ParnassusData.com

Facebook: http://www.facebook.com/parnassusData

Twitter: http://twitter.com/ParnassusData

Weibo: http://weibo.com/parnassusdata

 

 

Copyright©2013, ParnassusData and/or its affiliates. All rights reserved. This document is provided for information purposes only and the contents hereof are subject to change without notice. This document is not warranted to be error-free, nor subject to any other warranties or conditions, whether expressed orally or implied in law, including implied warranties and conditions of merchantability or fitness for a particular purpose. We specifically disclaim any liability with respect to this document and no contractual obligations are formed either directly or indirectly by this document. This document may not be reproduced or transmitted in any form or by any means, electronic or mechanical, for any purpose, without our prior written permission.

 

Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

 

AMD, Opteron, the AMD logo, and the AMD Opteron logo are trademarks or registered trademarks of Advanced Micro Devices. Intel and Intel Xeon are trademarks or registered trademarks of Intel Corporation. All SPARC trademarks are used under license and are trademarks or registered trademarks of SPARC International, Inc. UNIX is a registered trademark licensed through X/Open Company, Ltd. 0410

 

Copyright © 2014 ParnassusData Corporation. All Rights Reserved.

PRM-DUL For Oracle Database用户手册

ParnassusData Recovery Manager(以下简称PRM-DUL)是企业级ORACLE数据灾难恢复软件,可直接从Oracle 9i,10g,11g,12c的数据库数据文件(datafile)中抽取还原数据表上的数据,而不需要通过ORACLE数据库实例上执行SQL来拯救数据。ParnassusData Recovery Manager是一款基于JAVA开发的绿色软件,无需安装,下载解压后便可直接使用。

 

PRM-DUL采用GUI图形化界面(如图1)简单方便。使用者无需额外学习一套命令,或者了解ORACLE的底层数据结构原理即可以通过恢复向导(Recovery Wizard)来恢复数据库中的数据。

 

下载PRM-DUL:

http://parnassusdata.com/sites/default/files/ParnassusData_PRMForOracle_3206.zip

 

PRM-DUL-CN1

 

为什么要使用PRM-DUL?

 

 

难道使用RMAN这个传统ORACLE恢复管理器的备份恢复还不够吗?为什么用户需要选择购买PRM-DUL呢?您的心头或许仍有这种疑惑!

 

在企业日益增长的IT系统中,数据容量正以几何级数扩展。 Oracle DBA在保证数据完整性的课题上正面临着现有磁盘存储系统容量不足以存放全量备份,基于磁带的数据备份在恢复数据时往往要求远远超过预期的平均修复时间等实际问题。

 

“对于数据库而言,备份重于一切”是所有DBA心中谨记的格言,但现实环境千差万别,企业的数据库环境中数据备份空间不足,采购的存储设备短期内无法到货,甚至于虽然进行了备份但是却在数据恢复过程中发现备份实际不可用等问题均属常见的场景。

 

为了应对这些真实世界中常见的数据恢复困局,PRM-DUL 诗檀数据恢复管理软件充分发挥其对ORACLE数据库内部数据结构,核心启动流程等内部原理的理解,可以应对在完全没有备份情况下的SYSTEM表空间丢失、误操作ORACLE数据字典表、由于断电引起的数据字典不一致等数据库无法顺利打开的场景,也可以挽回误截断(Truncate)/删除(Delete)/业务数据表等人为的误操作,并从容恢复数据。

 

 

甚至于仅仅接触过ORACLE数据库几天的非DBA 人员也可以轻松地使用PRM-DUL,这得益于PRM-DUL简单的安装、和全程图形化的人机交互界面;实施恢复的人员不需要专业的数据库知识,不需要学习任何命令,更无需了解数据库底层的存储结构。仅仅需要轻轻点击几下鼠标就能从容恢复数据。

 

对比传统恢复工具DUL,DUL是ORACLE原厂内部恢复工具,其使用需要通过ORACLE内部流程,一般仅有购买了ORACLE原厂的现场服务的用户能够在原厂工程师的协助下使用该工具。PRM-DUL打破了只有少数专业人士才能实施数据库恢复任务的限制,极大地缩短了从数据库故障到完整恢复数据的失败时间,降低了企业恢复数据的总成本。

 

 

通过PRM-DUL恢复的数据可以分为2种形式,传统抽取方式是将数据从数据文件中完整抽取出来并写入到平面文本文件中,之后使用SQLLDR等工具再加载到数据库中。 传统方式简单易懂,但其缺点是需要2倍于现有数据容量的空间:即一份平面文本数据所占空间、以及之后将文本数据导入到数据库中所占空间;在时间上需要将原始数据从数据文件中抽取后,方能导入到新建数据库中,往往又需要2倍的时间。

 

 

另一种是诗檀强烈推荐您使用的是PRM-DUL独创的数据搭桥方式,即通过PRM-DUL直接将抽取出来的数据加载到新建或者其他可用数据库中,这样避免了数据落地存储,对比传统方式有效节省了数据恢复所需要的空间和时间成本。

 

ORACLE的ASM自动存储管理技术正被越来越多的企业采用,数据库采用ASM存储对比传统文件系统具有高性能、支持集群、管理方便等优势。 但ASM的问题在于,对于普通用户而言ASM的存储结构过于黑盒了,一旦ASM中的某个Disk Group的内部数据结构发生了损坏导致Disk Group无法被成功MOUNT,也就意味着用户重要的数据被锁死在这个ASM的黑盒中了。在这种场景中往往需要熟悉ASM内部数据结构的ORACLE原厂的资深工程师到达用户现场后通过手动修复ASM内部结构;而购买ORACLE原厂的现场服务对普通用户而言显得即昂贵又耗时。

 

 

基于PRM-DUL的研发人员(前ORACLE公司资深工程师)对ORACLE ASM内部数据结构的深入理解,PRM-DUL中加入了特别针对ASM的数据恢复功能。

 

PRM-DUL目前支持的ASM数据恢复功能包括:

 

  1. 即便Disk Group无法正常MOUNT,仍可以通过PRM-DUL直接读取ASM磁盘上的可用的元数据metadata,并基于这些元数据将Disk Group中的ASM文件拷贝出来

 

  1. 即便Disk Group无法正常MOUNT,仍可以通过PRM-DUL直接读取ASM上的数据文件,并抽取其中的数据,支持传统抽取方式和数据搭桥方式。

 

 

 

 

 

 

 

 

 

 

PRM-DUL软件介绍

 

 

ParnassusData Recovery Manager(PRM-DUL)基于JAVA开发,这保证了PRM-DUL可以跨平台运行,无论是AIX、Solaris、HPUX等Unix平台, Redhat、Oracle Linux、SUSE等Linux平台,还是Windows平台上均可以直接运行PRM-DUL。

 

PRM-DUL支持的操作系统平台:

 

Platform Name Supported
AIX POWER ü
Solaris Sparc ü
Solaris X86 ü
Linux X86 ü
Linux X86-64 ü
HPUX ü
MacOS ü

 

 

PRM-DUL目前支持的数据库版本

 

ORACLE DATABASE VERSION Supported
Oracle 7 û
Oracle 8 û
Oracle 8i û
Oracle 9i ü
Oracle 10g ü
Oracle 11g ü
Oracle 12c ü

 

 

 

 

 

 

 

 

考虑到部分陈旧服务器使用例如AIX 4.3 Linux 3等较早的操作系统,这些操作系统上可能无法安装最新的JDK 如1.6/1.7; PRM-DUL在研发过程中充分考虑了利旧性,任何可以运行JDK 1.4的平台均可以运行PRM-DUL。

 

此外由于ORACLE 10g 数据库服务器软件自带了JDK 1.4,11g自带了JDK 1.5,所以任何已安装ORACLE 10g及其以上版本的环境均可以顺利运行PRM-DUL,而且无需额外安装JDK。

 

对于没有安装JDK 1.4版本的环境,建议从以下地址下载

 

http://www.oracle.com/technetwork/java/javasebusiness/downloads/java-archive-downloads-javase14-419411.html

 

 

PRM-DUL 使用的最低JAVAJDK 1.4檀推荐您使用JDK 1.6,由于JDK 1.4以后对JAVA程序的性能做了很大,所以PRM-DUL在JDK 1.6下的恢复速度要比JDK 1.4下快一些。

 

 

 

PRM-DUL使用的最低硬件需求:

 

CPU中央处理器 至少800 MHZ
物理内存 至少512 MB
硬盘空间 至少50 MB

 

PRM-DUL推荐的硬件配置:

 

CPU中央处理器 2.0 GHZ
物理内存 2 GB
硬盘空间 2 GB

 

 

 

 

 

PRM-DUL目前支持的多语言:

 

 

语言 字符集 对应的编码
中文 简体/繁体 ZHS16GBK GBK
中文 简体/繁体 ZHS16DBCS CP935
中文 简体/繁体 ZHT16BIG5 BIG5
中文 简体/繁体 ZHT16DBCS CP937
中文 简体/繁体 ZHT16HKSCS CP950
中文 简体/繁体 ZHS16CGB231280 GB2312
中文 简体/繁体 ZHS32GB18030 GB18030
日文 JA16SJIS SJIS
日文 JA16EUC EUC_JP
日文 JA16DBCS CP939
韩语 KO16MSWIN949 MS649
韩语 KO16KSC5601 EUC_KR
韩语 KO16DBCS CP933
法语 WE8MSWIN1252 CP1252
法语 WE8ISO8859P15 ISO8859_15
法语 WE8PC850 CP850
法语 WE8EBCDIC1148 CP1148
法语 WE8ISO8859P1 ISO8859_1
法语 WE8PC863 CP863
法语 WE8EBCDIC1047 CP1047
法语 WE8EBCDIC1147 CP1147
德语 WE8MSWIN1252 CP1252
德语 WE8ISO8859P15 ISO8859_15
德语 WE8PC850 CP850
德语 WE8EBCDIC1141 CP1141
德语 WE8ISO8859P1 ISO8859_1
德语 WE8EBCDIC1148 CP1148
意大利语 WE8MSWIN1252 CP1252
意大利语 WE8ISO8859P15 ISO8859_15
意大利语 WE8PC850 CP850
意大利语 WE8EBCDIC1144 CP1144
泰语 TH8TISASCII CP874
泰语 TH8TISEBCDIC TIS620
阿拉伯语 AR8MSWIN1256 CP1256
阿拉伯语 AR8ISO8859P6 ISO8859_6
阿拉伯语 AR8ADOS720 CP864
西班牙语 WE8MSWIN1252 CP1252
西班牙语 WE8ISO8859P1 ISO8859_1
西班牙语 WE8PC850 CP850
西班牙语 WE8EBCDIC1047 CP1047
葡萄牙语 WE8MSWIN1252 CP1252
葡萄牙语 WE8ISO8859P1 ISO8859_1
葡萄牙语 WE8PC850 CP850
葡萄牙语 WE8EBCDIC1047 CP1047
葡萄牙语 WE8ISO8859P15 ISO8859_15
葡萄牙语 WE8PC860 CP860

 

 

PRM-DUL支持的表存储类型:

 

表存储类型 Supported
Cluster Table簇表 YES
索引组织表,分区或非分区 YES
普通堆表,分区或非分区 YES
普通堆表 启用基本压缩 YES(Future)
普通堆表 启用高级压缩 NO
普通堆表 启用混合列压缩 NO
普通堆表 启用加密 NO
带有虚拟字段virtual column的表 NO
链式行、迁移行 chained rows 、migrated rows YES

 

 

注意事项: 对于virtual column、11g optimized default column而言 数据抽取可能没问题,但会丢失对应的字段。 这2个都是11g之后的新特性,使用者较少。

 

 

PRM-DUL 支持的数据类型

 

数据类型 Supported
BFILE No
Binary XML No
BINARY_DOUBLE Yes
BINARY_FLOAT Yes
BLOB Yes
CHAR Yes
CLOB and NCLOB Yes
Collections (including VARRAYS and nested tables) No
Date Yes
INTERVAL DAY TO SECOND Yes
INTERVAL YEAR TO MONTH Yes
LOBs stored as SecureFiles Future
LONG Yes
LONG RAW Yes
Multimedia data types (including Spatial, Image, and Oracle Text) No
NCHAR Yes
Number Yes
NVARCHAR2 Yes
RAW Yes
ROWID, UROWID Yes
TIMESTAMP Yes
TIMESTAMP WITH LOCAL TIMEZONE Yes
TIMESTAMP WITH TIMEZONE Yes
User-defined types No
VARCHAR2 and VARCHAR Yes
XMLType stored as CLOB No
XMLType stored as Object Relational No

 

 

PRM-DUL对ASM的支持

 

 

功能 Supported
支持直接从ASM中抽取数据,无需拷贝到文件系统上 YES
支持从ASM中拷贝数据文件 YES
支持修复ASM metadata YES
支持图形化展示ASM黑盒 Future

 

 

 

 

 

PRM-DUL的安装与启动

 

 

由于PRM-DUL是基于JAVA开发的纯绿色软件,所以无需额外安装,用户仅需要在下载软件ZIP包后解压即可用于恢复数据。

 

 unzip   PRM-DUL_latest.zip

 

 

 

 

诗檀强烈推荐您使用命令行启动PRM-DUL,这样可以从命令行中获得更多诊断信息。

 

Windows平台下的启动方法

 

  1. 首先保证JDK已正确安装且java已经加入到环境变量中:
  2. 双击PRM-DUL解压目录下的PRM-DUL.bat

PRM-DUL-CN2

 

PRM-DUL.bat会在后台启动PRM:

PRM-DUL-CN3

 

 

并启动PRM-DUL 图形化主界面:

PRM-DUL-CN4

 

 

 

在Linux/Unix环境下的启动方法:

 

 

在Linux/Unix环境下可以在本机图形化界面或者通过Xmanager等远程图形化工具使用

 

  1. 首先保证JDK已正确安装且java已经加入到环境变量中
  2. Cd到PRM-DUL所在目录,并执行./PRM-DUL.sh启动程序主界面

 

PRM-DUL-CN5

 

 

PRM-DUL-CN6

 

 

PRM-DUL的许可证注册

 

ParnassusData Recovery Manager(以下简称PRM-DUL)是一款商业软件。ParnassusData提供PRM-DUL的社区版供用户测试和学习(社区版中ASM clone功能无任何限制,今后社区版将加入更多免费新特性)。

 

如想无限制地使用PRM-DUL软件恢复ORACLE数据库则需要购买对应的License软件许可证,目前我们提供2种License类型:Standard Edition标准版和Enterprise Edition 企业版;其具体规格如下:

 

PRM-DUL-CN7

 

 

用户可以从ParnassusData官网http://www.parnassusdata.com/上购买PRM-DUL License,购买时将需要您输入您要恢复的数据库名即DBNAME,完成购买后您将受到一封邮件,该邮件中包括一个对应您输入的DBNAME和购买的许可证类型的License Key。

 

 

用户获得License Key之后可以自行在软件中注册Register,具体使用方法为:

 

  1. 在菜单栏Help => Register
  2. 输入DB NAME和发送给您的License Key并点击Register按钮即可

 

完成注册后,今后重新启动PRM-DUL将自动检测License注册信息,无需重复注册。

 

PRM-DUL-CN8

 

 

PRM-DUL-CN9

 

成功注册的信息可以在Help=>About中找到:

PRM-DUL-CN10

 

 

PRM-DUL-CN11

 

基于不同的Oracle数据库恢复场景介绍如何使用PRM-DUL

 

 

恢复场景1 误Truncate表的常规恢复

 

D公司的业务维护人员由于误将产品数据库当做测试环境库导致错误地TRUNCATE了一张表上的所有数据,DBA尝试恢复但是发觉最近的备份不可用,导致无法从备份中恢复出该数据表上的记录。 此时DBA决定采用PRM-DUL来恢复已经被TRUNCATE掉的数据。

 

由于该环境中 所有数据库文件均是可用且健康的,用户仅需要 字典模式下加载SYSTEM表空间的数据文件以及被TRUNCATED表的数据文件即可,例如:

 

 

create table ParnassusData.torderdetail_his1 tablespace users asselect * from parnassusdata.torderdetail_his;

 

 

 

SQL> desc ParnassusData.TORDERDETAIL_HIS
 Name                    Null?    Type
 ———————– ——– ————–
SEQ_ID                NOT NULL   NUMBER(10)
 SI_STATUS                        NUMBER(38)
 D_CREATEDATE                     CHAR(20)
 D_UPDATEDATE                     CHAR(20)
 B_ISDELETE                       CHAR(1)
 N_SHOPID                         NUMBER(10)
 N_ORDERID                        NUMBER(10)
 C_ORDERCODE                      CHAR(20)
 N_MEMBERID                       NUMBER(10)
 N_SKUID                          NUMBER(10)
 C_PROMOTION                      NVARCHAR2(5)
 N_AMOUNT                         NUMBER(7,2)
 N_UNITPRICE                      NUMBER(7,2)
 N_UNITSELLINGPRICE               NUMBER(7,2)
 N_QTY                            NUMBER(7,2)
 N_QTYFREE                        NUMBER(7,2)
 N_POINTSGET                      NUMBER(7,2)
 N_OPERATOR                       NUMBER(10)
 C_TIMESTAMP                      VARCHAR2(20)
 H_SEQID                          NUMBER(10)
 N_RETQTY                         NUMBER(7,2)
 N_QTYPOS                         NUMBER(7,2) 

select count(*) from ParnassusData.TORDERDETAIL_HIS;

 

COUNT(*)

———-

984359

 

select bytes/1024/1024 from dba_segments where segment_name=’TORDERDETAIL_HIS’ and owner=’PARNASSUSDATA’;

 

BYTES/1024/1024

—————

189.71875

 

 

 

SQL> truncate table ParnassusData.TORDERDETAIL_HIS;

 

Table truncated.

 

SQL> select count(*) from ParnassusData.TORDERDETAIL_HIS;

 

COUNT(*)

———-

0

 

启动PRM-DUL ,并选择 Tools =>  Recovery Wizard

 

PRM-DUL-CN12

 

 

点击Next

 

PRM-DUL-CN13

 

 

在此TRUNCATE场景中并未采用ASM存储,所以仅需要选择 《Dictionary Mode》字典模式即可:

PRM-DUL-CN14

 

 

下一步骤 我们要选择几个参数 : 包括Endian 字节序和DB NAME

 

由于ORACLE数据文件在不同的操作系统平台上采用了不同的Endian字节序格式,字节序和平台对应列表如下:

 

Solaris[tm] OE (32-bit) Big
Solaris[tm] OE (64-bit) Big
Microsoft Windows IA (32-bit) Little
Linux IA (32-bit) Little
AIX-Based Systems (64-bit) Big
HP-UX (64-bit) Big
HP Tru64 UNIX Little
HP-UX IA (64-bit) Big
Linux IA (64-bit) Little
HP Open VMS Little
Microsoft Windows IA (64-bit) Little
IBM zSeries Based Linux Big
Linux x86 64-bit Little
Apple Mac OS Big
Microsoft Windows x86 64-bit Little
Solaris Operating System (x86) Little
IBM Power Based Linux Big
HP IA Open VMS Little
Solaris Operating System (x86-64) Little
Apple Mac OS (x86-64) Little

 

 

例如在传统Unix  AIX-Based Systems (64-bit) 、HP-UX (64-bit) 上使用的是Big Endian大端字节序,则这里要选为Big Endian:

 

PRM-DUL-CN15

 

 

否则例如常见的Linux x86-64 、Windows都保持为默认的Little Endian:

PRM-DUL-CN16

 

 

注意事项: 如果你的数据文件是在AIX(即Big Endian的)上生成的,你为了方便而将这些数据文件拷贝到Windows服务器上并使用PRM-DUL来恢复数据,那么你仍应当选择其原生的Big Endian格式。

 

 

 

这里由于我们的数据文件是在Linux x86上所以我们选择Endian为Little,并输入Database name数据库名字(注意这里输入的数据库名仅仅是一个别名,它不代表这个数据库真实的DBNAME,PRM-DUL的LICENSE检测机制使用的是真实的DBNAME,而非此处输入的Database Name):

 

PRM-DUL-CN17

 

点击Next

 

点击Choose Files, 一般我们推荐 如果数据库不大,那么将该库所有的数据文件都选择进来; 如果你的数据库很大,且你了解你的数据表位于哪些数据文件上,则你可以仅仅选择SYSTEM表空间的数据文件(必须!)以及数据所在的数据文件。

 

注意Choose界面支持Ctrl + A 和Shift等键盘操作:

 

 

PRM-DUL-CN18

 

PRM-DUL-CN19

 

之后需要为指定的数据文件指定其Block Size即ORACLE数据块的大小,这里根据实际情况修改即可, 例如你的DB_BLOCK_SIZE是8K,但是部分表空间指定16K作为数据块大小的,仅仅需要为那些不是8k的数据文件修改BLOCK_SIZE即可。

 

 

这里的OFFSET 参数主要是为了那些采用裸设备存放数据文件的场景,例如在AIX上基于普通VG的LV作为数据文件,则存在4k的OFFSET,需要在此处指定。

 

如果你恰巧正在使用裸设备数据文件,而又不知道OFFSET到底是多少? 则可以使用$ORACLE_HOME/bin下自带的dbfsize工具查看,如下面的例子高亮部分显示该裸设备具有4K的OFFSET

 

$dbfsize /dev/lv_control_01Database file: /dev/lv_control_01

Database file type: raw device without 4K starting offset

Database file size: 334 16384 byte blocks

 

 

由于此场景中所有数据文件均为8K的BLOCK SIZE,且基于文件系统所以均没有OFFSET,点击Load

 

PRM-DUL-CN20

 

Load阶段PRM-DUL会从SYSTEM表空间中读取ORACLE数据字典信息,并在自带的Derby中自建一个数据字典,这让PRM-DUL有能力操作ORACLE数据库中的各种数据。

 

PRM-DUL-CN21

 

Load完成后会在后台输出数据库 字符集和国家字符集等信息:

 

PRM-DUL-CN22

 

注意PRM-DUL是支持 多语言和ORACLE数据库的多字符集的, 但是前提是实施PRM-DUL数据恢复的操作系统要求已经安装了对应的语言包; 例如在Windows操作系统上没有安装中文语言包,但是由于ORACLE数据库字符集是独立于操作系统语言的,即ORACLE数据库的字符集可以为ZHS16GBK字符集,但是操作系统并不支持中文,此场景中不在本服务器上部署的ORACLE客户端并不受影响,可以正确显示数据库中的中文数据。

 

但是使用PRM-DUL则要求实施PRM-DUL数据恢复的操作系统已经安装了对应的语言包,例如用户要恢复ZHS16GBK的中文字符集数据库,则需要操作系统上已经安装了中文语言包才可以。

 

类似的 在Linux上需要安装fonts-chinese 中文字体包。

 

 

 

Load完成后 PRM-DUL界面左侧出现按照数据库用户分组的树形图

 

点开USERS,可以看到多个用户名,例如用户需要恢复PARNASSUSDATA SCHEMA下的一张表,则点开PARNASSUSDATA,并双击表名:

 

PRM-DUL-CN23

 

 

由于该TORDERDETAIL_HIS表之前已经被TRUNCATED掉了,所以双击没有显示有数据,此时在表上右键选择Unload truncated data:

 

PRM-DUL-CN24

 

 

PRM-DUL将尝试扫描该表所在表空间并将已经truncated掉的数据抽取出来:

 

PRM-DUL-CN25

 

PRM-DUL-CN26

 

 

如上图所示从已经被TRUNCATE过的TORDERDETAIL_HIS表中抽取出完整的984359条记录,并存放在提示指定的路径下。

这里还自动生成了将文本数据导入到数据库中使用的SQLLDR 控制文件。

 

 

$ cd /home/oracle/PRM-DUL/PRM-DULdata/parnassus_dbinfo_PARNASSUSDATA/$ ls -l ParnassusData*

-rw-r–r– 1 oracle oinstall       495 Jan 18 08:31 ParnassusData.torderdetail_his.ctl

-rw-r–r– 1 oracle oinstall 191164826 Jan 18 08:32 ParnassusData.torderdetail_his.dat.truncated

 

$ cat ParnassusData.torderdetail_his.ctl

LOAD DATA

INFILE  ‘ParnassusData.torderdetail_his.dat.truncated’

APPEND

INTO TABLE ParnassusData.torderdetail_his

FIELDS TERMINATED BY ‘ ‘

OPTIONALLY ENCLOSED BY ‘”‘

TRAILING NULLCOLS (

“SEQ_ID” ,

“SI_STATUS” ,

“D_CREATEDATE” ,

“D_UPDATEDATE” ,

“B_ISDELETE” ,

“N_SHOPID” ,

“N_ORDERID” ,

“C_ORDERCODE” ,

“N_MEMBERID” ,

“N_SKUID” ,

“C_PROMOTION” ,

“N_AMOUNT” ,

“N_UNITPRICE” ,

“N_UNITSELLINGPRICE” ,

“N_QTY” ,

“N_QTYFREE” ,

“N_POINTSGET” ,

“N_OPERATOR” ,

“C_TIMESTAMP” ,

“H_SEQID” ,

“N_RETQTY” ,

“N_QTYPOS”

)

 

 

将数据导入到源表中(注意 ParnassusData强烈建议你修改该SQLLDR控制文件中导入的表名字为一个临时表,这样不会覆盖原环境)。

 

$ sqlldr control=ParnassusData.torderdetail_his.ctl direct=yUsername:/ as sysdba//以上使用sqlldr导入了恢复的数据

 

 

//可以通过minus来对比恢复出来的数据:

 

select * from ParnassusData.torderdetail_his minus select * from parnassus.torderdetail_his;

 

no rows selected

 

 

 

测试TRUNCATE用例表与源数据表对比,发现记录完全一致。

说明PRM-DUL完整、丝毫不差地恢复了被TRUNCATE表上的记录。

 

恢复场景2 误Truncate表的DataBridge数据搭桥恢复

 

恢复场景1中我们采用了常规unload+sqlldr的恢复方式; 但实际上ParnassusData原厂更推荐您使用我们精心设计的DataBridge数据搭桥模式。

 

为什么要引入数据搭桥模式呢?

 

  • 普通的unload+sqlldr恢复方式意味着要保存一份源数据,一份抽取数据,和一份目标数据,即在恢复过程中可能需要扩容2倍于原来的存储空间,这对于甚至无法腾出备份空间的企业来说十分困难
  • 数据搭桥与普通unload+sqlldr模式的最大区别在于,数据搭桥直接从源库中抽取数据并传送到目标数据库中,无需在文件系统上保留一份抽取数据
  • 通过数据搭桥传送到目标数据库中的数据本身就是结构化的,可以立即使用SQL语句来验证其完整性和一致性
  • 如果数据搭桥的目标数据库库位于异机上,那么源数据库上仅仅做读取操作,读写IO将分布于2台服务器上,PRM-DUL恢复的速度将更快
  • 如果用户所需要恢复的是Truncate数据的话,那么可以马上搭桥回到源库中,恢复仅仅是鼠标点几下的工作

 

使用数据搭桥模式也十分简便,通常规模式一样,在左侧树形图中点中你需要的表,右键选择DataBridge选项:

 

PRM-DUL-CN27

 

首次使用数据搭桥模式时需要先创建目标数据库连接信息,这就和我们在SQLDEVELOPER中创建一个Connection是类似的工作,包括目标数据库的Host、端口、Service_Name以及用户登录信息;注意这里填选的用户信息,将会是稍后数据搭桥使用的目标数据库的User用户,即从源库这里抽取出来的表会传输到目标数据库中此处所指定的用户名下。

 

PRM-DUL-CN28

 

如上述建立了一个G10R25的连接,用户为maclean,对应的oracle Easy Connection连接串为 192.168.1.191:1521/G10R25。

 

 

完成上述数据库连接信息填写后可以点击Test按钮来测试该连接配置是否正确可用,如果返回 “ Connect to db server successfully “则说明连接可用,点击Save按钮保存即可。

 

PRM-DUL-CN29

 

Save后进入DataBridge主界面,首先在DB Connection下拉框中选择刚刚加入的Connection G10R25:

 

 

 

PRM-DUL-CN30

 

 

此处如果所需用的数据库连接并未在DB connection下拉框中出现,则需要点击DB connection旁的”…”按钮添加DB Connection:

PRM-DUL-CN31

 

正确选择DB Connection后可以Tablespace的下拉框将变得可用,选中合适的表空间:

PRM-DUL-CN32

 

 

 

使用Data Bridge恢复truncate时的注意事项:注意当从源库中恢复出truncate数据时,若使用databridge选项传输数据回到你的源库(如果回传数据不是到源库则没有该问题)时,需要注意 Databridge插入到新建表的所在位置应当不是源库中被truncate数据所在的表空间,否则会出现一边在恢复truncate数据一边我们所需恢复的数据被新数据所覆盖的问题,可能导致该恢复场景中的数据完全无法恢复。故请注意,当使用databridge+恢复数据到源库时,在databridge中指定表空间时千万不要使用需要恢复数据所在的表空间!!!!!!

 

 

 

用户可以选择是否要将从源库传输到目标库的表的表名做映射修改,例如我们在源库中Truncate掉了一张表,现在通过DataBridge将数据恢复回源库中,但是不想使用原来的表名字,如原来的表名为torderdetail_his,现在希望将恢复的数据以别的表名存放,则可以选中“if need to remap table”并填入合适的目标表名,如下图所示:

 

PRM-DUL-CN33

 

 

 

注意: 1)对于目标库中已经存在对应表名的情况,PRM-DUL不会重建表而是会在现有表的基础上插入所需恢复的数据,由于表已经建立了所以指定的表空间将无效  2)对于目标数据库中还不存在对应表名的情况,PRM-DUL会尝试在指定表空间上建表并插入恢复数据

 

 

此场景中由于我们是恢复Truncate掉的数据,所以需要选中“if data truncated”选项,否则PRM-DUL将以常规模式抽取数据,将无法抽取到已经被Truncate掉的数据。

 

Truncate数据的大致机理是,ORACLE会在数据字典和Segment Header中更新表的Data Object ID,而实际数据部分的块则不会做修改。由于数据字典与段头的DATA_OBJECT_ID与后续的数据块中的并不一致,所以ORACLE服务进程在读取全表数据时不会读取到已经被TRUNCATE但是实际仍未被覆盖的数据。

 

PRM-DUL通过自动扫描被TRUNCATE掉数据段头Segment Header后续的数据块智能判断TRUNCATE前数据段的DATA_OBJECT_ID,并根据字典中的表字段定义和自动获得的原始DATA_OBJECT_ID来抽取数据。

 

 

此处还存在一个”if to specify data object id”输入框,该输入框可以让用户指定要恢复的数据的Data Object ID。一般情况下不需要指定任何值,除非你发现恢复Truncate数据不成功时,建议在ParnassusData原厂工程师的帮助下指定该值。

 

 

如上正确完成DataBridge配置后即可证实开始数据搭桥,只需要点击DataBridge按钮即可:

 

PRM-DUL-CN34

 

数据搭桥完成后会显示成功传输的数据行数,以及耗时。

 

PRM-DUL-CN35

 

 

 

 

恢复场景3  ORACLE数据字典受损导致数据库无法打开

 

D 公司的DBA由于误操作删除了TS$数据字典基表导致数据库无法启动

 

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit ProductionWith the Partitioning, Automatic Storage Management, OLAP, Data Miningand Real Application Testing options

 

 

INSTANCE_NAME

—————-

ASMME

 

SQL>

SQL>

SQL> select count(*) from sys.ts$;

 

COUNT(*)

———-

5

 

SQL> delete ts$;

 

5 rows deleted.

 

SQL> commit;

 

Commit complete.

 

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

 

Database mounted.

ORA-01092: ORACLE instance terminated. Disconnection forced

ORA-01405: fetched column value is NULL

Process ID: 5270

Session ID: 10 Serial number: 3

 

Undo initialization errored: err:1405 serial:0 start:3126020954 end:3126020954 diff:0 (0 seconds)

Errors in file /s01/diag/rdbms/asmme/ASMME/trace/ASMME_ora_5270.trc:

ORA-01405: fetched column value is NULL

Errors in file /s01/diag/rdbms/asmme/ASMME/trace/ASMME_ora_5270.trc:

ORA-01405: fetched column value is NULL

Error 1405 happened during db open, shutting down database

USER (ospid: 5270): terminating the instance due to error 1405

Instance terminated by USER, pid = 5270

ORA-1092 signalled during: ALTER DATABASE OPEN…

opiodr aborting process unknown ospid (5270) as a result of ORA-1092

 

 

 

此场景中由于数据字典已经损坏,所以想要正常打开数据库是十分困难的。

 

此时则可以使用PRM-DUL来抽取数据库中的数据。具体步骤与场景1中的相似,用户仅仅需要输入该数据库的所有数据文件即可,其简要步骤如下:

 

  1. Recovery Wizard
  2. 选择字典模式 Dictionary Mode
  3. 合理选择Big或者Little Endian
  4. 加入数据文件并点击Load
  5. 根据实际需求恢复表中的数据

 

 

 

PRM-DUL-CN36

 

 

恢复场景4 误删除或丢失SYSTEM表空间

 

 

D公司的SA系统管理员误删除了某数据库的SYSTEM表空间所在数据文件,这导致数据库完全无法打开,数据无法取出。 在没有备份的情况下,可以使用PRM-DUL恢复接近100%的数据。

 

此场景中启动PRM-DUL后,进入Recovery Wizard后 选择《Non-Dictionary mode》非字典模式:

 

 

PRM-DUL-CN37

 

PRM-DUL-CN38

 

No-dictionary模式下需要用户指定 字符集和国家字符集,这是因为丢失了SYSTEM表空间后,数据库的字符集信息无法正常获得,所以需要用户的输入。 只有输入正确的字符集设置以及安装了必要的语言包才能保证No-Dictionary模式下正常抽取多国语言。

 

 

与场景演示1类似,输入用户目前可得的所有数据文件(不包括临时文件),并设置正确的Block Size和OFFSET:

 

PRM-DUL-CN39

 

 

之后点击SCAN,SCAN的作用是扫描所有数据文件上的Segment Header,并记录到SEG$.DAT和EXT$.DAT中;在ORACLE中一个非分区表或者一个分区表的分区都对应着一个SEGMENT HEADER数据段头,只要能找到SEGMENT HEADER就可以获得整个表数据段的盘区EXTENT MAP 信息,通过EXTENT MAP可以获得该表上的全部记录。

 

通常存在这样一种情况,例如一张非分区的单表存放在某个由2个数据文件组成的表空间上,其SEGMENT HEADER以及一半的数据存放在A数据文件上,另一半数据存放在B数据文件上。但是由于某些原因,SYSTEM表空间和存放有SEGMENT HEADER的A数据文件均丢失了,只剩下B数据文件了,此时若希望仅仅恢复B数据文件上该表的数据,则不能依赖于SEGMENT HEADER,而只能依赖于从B数据文件上扫描盘区图EXTENT MAP信息了。

 

为了同时满足 基于SEGMENT HEADER和EXTENT MAP数据的NO-Dictionary模式恢复需要,所以SCAN操作在这里会填充SEG$.DAT和EXT$.DAT2个文件(文本文件仅仅为了便于诊断,所有程序实际依赖于PRM-DUL自带嵌入数据库DERBY的数据),并记录到DERBY数据库中。

 

PRM-DUL-CN40

 

 

PRM-DUL-CN41

 

完成SCAN 后,主界面左侧出现数据库图标。

 

此时可以选择2种模式:

 

  • Scan Tables From Segments,此模式适用于
    • 丢失了SYSTEM表空间,但是所有的应用数据表空间均存在
  • Scan Tables From Extents
    • 不适用于Dictionary模式的Truncate表数据恢复
    • 丢失了SYSTEM表空间,而且丢失了SEGMENT HEADER所在数据文件

 

通俗地说 除非你无法使用场景2中的方式来恢复已经TRUNCATE掉的数据,否则总是优先使用Scan Tables From Segments模式,如果发现Scan Tables From Segments下找不到你要的数据,再考虑使用Scan Tables From Extents模式。

 

我们优先采用Scan Tables From Segments模式

 

PRM-DUL-CN42

 

 

Scan Tables From Segments完成后可以点开主界面左边的树形图:

PRM-DUL-CN43

 

 

Scan Tables操作基于SEG$中的SEGMENT HEADER信息来构建数据表信息,树形图上每一个节点表示一个数据表段,其名字为obj+ 数据段上记录的DATA OBJECT ID 。

 

点中一个节点 并观察主界面右侧边栏:

 

PRM-DUL-CN44

 

智能字段型解析

 

 

由于丢失了SYSTEM表空间,故NO-Dictionary模式下缺乏数据表的结构信息,这些结构信息包括表上的字段名字和字段类型,而且在ORACLE中这些信息均只保存为字典信息,不会在数据表上存放。当用户只有应用表空间时,需要基于数据段上的ROW行数据来猜测每一个字段的类型,PRM-DUL采用先进JAVA类型预判技术,可以解析多达10来种主流数据类型;、

 

智能解析准确度超过90%,可以自动解决大部分场景。

 

 

右侧边栏 上部各字段的含义:

 

  • Col1 no 字段号
  • Seen Count: 取到的行数
  • MAX SIZE: 最大长度,单位为字节
  • PCT NULL: 采样到的NULL的比例
  • String Nice: 将该字段解析为字符串,并成功的比例
  • Number Nice: 将该字段解析为数字,并成功的比例
  • Date Nice: 将该字段解析为Date,并成功的比例
  • Timestamp Nice: 将该字段解析为Timestamp,并成功的比例
  • Timestamp with timezone Nice: 将该字段解析为Timestamp with timezone Nice,并成功的比例

 

 

示例数据分析Sample Data Analysis:

 

PRM-DUL-CN45

 

该部分依据智能字段类型解析的结果来解析10条数据,并显示解析结果。通过示例数据可以帮助用户了解实际该数据段中存放数据的情况。

 

如果数据段上记录条数不足10条,则显示所有记录。

 

 

TRY TO ANALYZE UNKNOWN column type:

PRM-DUL-CN46

 

该部分是对于智能字段类型分析不能100%确认的字段,尝试用各种字段类型来解析,并呈现给用户,以便用户自行判断其究竟是什么类型。

 

目前PRM-DUL还不支持的类型包括:

XDB.XDB$RAW_LIST_T、XMLTYPE、用户自定义类型等

 

 

 

 

 

 

Unload Statement:

 

这部分是PRM-DUL生成的UNLOAD语句,此生成的UNLOAD语句仅作为系统内部使用和PRM-DUL开发团队以及诗檀软件原厂支持工程师使用。

PRM-DUL-CN47

 

在此《Non-Dictionary Mode》非字典模式下同样可以采用常规和数据搭桥模式,与字典模式相比,主要的区别在于在非字典模式下数据搭桥时用户可以自行执行字段的类型,如下图中中部分字段类型为UNKNOWN,即未知的。这些字段可能是PRM-DUL目前还不支持的例如XML字段,也可能是PRM-DUL的智能解析没有顺利分析器类型。

 

如果用户知道这张表设计时的结构(也可以来源于应用开发商的文档),那么可以自行去填选正确的Column Type类型,以便PRM-DUL顺利将该表数据搭桥到目标数据库。

 

PRM-DUL-CN48

 

恢复场景5  误删除了SYSTEM表空间和部分应用表空间数据文件

 

 

D公司的SA由于误操作将在线业务数据库的SYSTEM表空间上的数据文件,以及部分应用表空间数据文件意外删除了。

 

此场景中由于部分应用表空间数据文件被删除了,这其中可能包括含有数据表的SEGMENT HEADER的数据文件,所以使用Scan Tables From Segment Header可能不如使用Scan Tables From Extents来的合适。

 

其简要步骤如下:

 

  1. 进入Recovery Wizard ,选择No-Dictionary模,加入所有可用的数据文件,执行Scan Database
  2. 选中数据库,并右键Scan Tables From Extents
  3. 对于PRM-DUL主界面上生成的对象树形图中的数据进行分析和导出/数据搭桥
  4. 其余操作与恢复场景4中一样

 

 

 

恢复场景6 从被损坏的ASM Diskgroup中拷贝出数据库数据文件

 

 

 

D公司开始采用ASM方案来替代文件系统和裸设备,但是由于使用的11.2.0.1版本ASM上Bug较多导致ASM DISKGROUP磁盘组无法加载MOUNT,通过多方修复ASM Disk Header无果。

 

此场景可以使用PRM-DUL的ASM Files Clone文件克隆功能从受损的ASM Diskgroup中拷贝出数据库数据文件。

 

  1. 打开主界面,菜单栏Tools选择ASM File(s) Clone:

PRM-DUL-CN49

 

  1. 进入ASM Disks界面,点击SELECT…按钮加入仍可用的ASM Disks,如/dev/asm-disk5(linux);确保加入所有可用LUN后,点击ASM analyze按钮

 

 

PRM-DUL-CN50

 

 

PRM-DUL-CN51

 

 

PRM-DUL-CN52

 

 

  1. ASM Files Clone将分析指定的ASM Disk的磁盘头,以便找出对应Disk group磁盘组中的文件,以及这些文件的分布位置(File Extent Map); 这些信息均将记录到Derby数据库中以便今后使用; 可以说PRM-DUL将ASM的所有Metadata元数据均收集、分析、并存储起来,并通过各种形式来完善PRM-DUL的基本功能,并以图形化地方式展现给用户。

 

PRM-DUL-CN53

 

  1. ASM Analyze分析完成后,PRM-DUL将列出找到的ASM上文件的列表,用户可以勾选那些文件需要被克隆,并指定文件克隆的目标文件夹。

 

之后点击ASM Clone按钮,进入文件克隆阶段。

PRM-DUL-CN54

 

 

文件克隆阶段中,将列出ASM File的克隆进度,克隆完成后点击OK。

 

PRM-DUL-CN55

 

 

 

克隆阶段的进度日志输出如下:

 

 

Preparing selected files…Cloning +DATA2/ASMDB1/DATAFILE/TBS2.256.839732369:

……………………..1024MB

………………………………..2048MB

………………………………..3072MB

………………………………….4096MB

………………………………..5120MB

………………………………….6144MB

……………………………….7168MB

…………………………………8192MB

…………………………………9216MB

…………………………………10240MB

…………………………………11264MB

…………………………………..12288MB

…………………………………….13312MB

…………………………….14336MB

……………………………………..15360MB

……………………………….16384MB

…………………………………17408MB

…………………………………18432MB

…………………………………………………………………………………………….19456MB

……………………………………

Cloned size for this file (in byte): 21475885056

 

Cloned successfully!

 

 

Cloning +DATA2/ASMDB1/ARCHIVELOG/2014_02_17/thread_1_seq_47.257.839732751:

……

Cloned size for this file (in byte): 29360128

 

Cloned successfully!

 

 

Cloning +DATA2/ASMDB1/ARCHIVELOG/2014_02_17/thread_1_seq_48.258.839732751:

……

Cloned size for this file (in byte): 1048576

 

Cloned successfully!

 

 

 

 

All selected files were cloned done.

 

 

 

  1. 可以通过dbv或者rman validate命令来验证克隆出来的数据文件,例如:

 

rman target / 

RMAN> catalog datafilecopy ‘/home/oracle/asm_clone/TBS2.256.839732369.dbf’;

 

cataloged datafile copy

datafile copy file name=/home/oracle/asm_clone/TBS2.256.839732369.dbf RECID=2 STAMP=839750901

 

RMAN> validate datafilecopy ‘/home/oracle/asm_clone/TBS2.256.839732369.dbf’;

 

Starting validate at 17-FEB-14

using channel ORA_DISK_1

channel ORA_DISK_1: starting validation of datafile

channel ORA_DISK_1: including datafile copy of datafile 00016 in backup set

input file name=/home/oracle/asm_clone/TBS2.256.839732369.dbf

channel ORA_DISK_1: validation complete, elapsed time: 00:03:35

List of Datafile Copies

=======================

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN

—- —— ————– ———— ————— ———-

16   OK     0              2621313      2621440         1945051

File Name: /home/oracle/asm_clone/TBS2.256.839732369.dbf

Block Type Blocks Failing Blocks Processed

———- ————– —————-

Data       0              0

Index      0              0

Other      0              127

 

Finished validate at 17-FEB-14

 

 

 

对于使用ASMLIB的ASM环境要如何使用PRM-DUL呢?

 

其实也很简单,asmlib相关的ASM DISK在OS操作系统上会以ll /dev/oracleasm/disks 的形式存放,例如:直接将/dev/oracleasm/disks下的文件加入到PRM-DUL ASM DISK中即可

 

$ll /dev/oracleasm/diskstotal 0brw-rw—-  1 oracle dba 8,  97 Apr 28 15:20 VOL001

brw-rw—-  1 oracle dba 8,  81 Apr 28 15:20 VOL002

brw-rw—-  1 oracle dba 8,  65 Apr 28 15:20 VOL003

brw-rw—-  1 oracle dba 8,  49 Apr 28 15:20 VOL004

brw-rw—-  1 oracle dba 8,  33 Apr 28 15:20 VOL005

brw-rw—-  1 oracle dba 8,  17 Apr 28 15:20 VOL006

brw-rw—-  1 oracle dba 8, 129 Apr 28 15:20 VOL007

brw-rw—-  1 oracle dba 8, 113 Apr 28 15:20 VOL008

 

直接将/dev/oracleasm/disks下的文件加入到PRM-DUL ASM DISK中即可。

 

 

 

恢复场景7   ASM下数据库无法打开

 

D公司的某套核心CRM库由于加入到ASM Diskgroup中的少量磁盘存在I/O问题,导致SYSTEM表空间的DBF数据文件发生讹误,导致数据库无法打开。

 

此时即可以通过PRM-DUL恢复软件从ASM Diskgroup中将DATAFILE全部克隆到文件系统上,如恢复场景6中所述,并进一步修复数据库。

 

也可以通过PRM-DUL的《Dictionary Mode(ASM)》即基于ASM的字典模式来直接恢复问题数据库。其简要步骤如下:

 

  1. Recovery Wizard
  2. Dictionary Mode(ASM)
  3. 加入必要的ASM DISK(你所要恢复数据库的所在的ASM Disk Group的所有ASM DISK)
  4. 点击ASM analyze
  5. 为后面的数据文件选择合适的Endian
  6. 在ASM analze给出的数据文件列表中选中需要的数据文件,如果嫌麻烦且只有一套库,那么可以勾选”Select all”
  7. 点击load按钮,后续的恢复与《场景3》中类似

 

PRM-DUL-CN56

 

PRM-DUL-CN57

 

PRM-DUL-CN58

 

PRM-DUL-CN59

 

PRM-DUL-CN60

 

恢复场景8   ASM下误删或丢失SYSTEM表空间的恢复

 

 

D公司的运维人员误操作删除了核心数据库的SYSTEM表空间FILE#=1的数据文件以及部分应用表空间,导致数据库无法正常打开。

 

此场景下可以通过PRM-DUL的《Non-Dictionary Mode(ASM) 》ASM下的非字典模式基于现有的数据文件尽可能恢复出数据。

 

其简要的流程如下:

 

 

 

  1. Recovery Wizard
  2. Non-Dictionary Mode(ASM)
  3. 加入必要的ASM DISK(你所要恢复数据库的所在的ASM Disk Group的所有ASM DISK)
  4. 点击ASM analyze
  5. 为后面的数据文件选择合适的Endian以及字符集(由于是非字典模式所以需要手动选择字符集)
  6. 在ASM analyze给出的数据文件列表中选中需要的数据文件,如果嫌麻烦且只有一套库,那么可以勾选”Select all”
  7. 点击scan按钮,后续的恢复与《场景5》中类似

PRM-DUL-CN61

 

PRM-DUL-CN62

 

PRM-DUL-CN63

 

PRM-DUL-CN64

 

恢复场景9   对于误操作DROP TABLESPACE的数据恢复

 

D公司的员工需要删除某个无用的表空间即DROP TABLESPACE INCLUDING CONTENTS操作,但是在操作DROP TABLESPACE后,开发部门反映该被DROP掉的TABLESPACE上其实有一个SCHEMA的数据是有用且重要的,但现在表空间被DROP了,且无任何备份。

 

此时可以利用PRM-DUL的No-Dict模式去抽取被DROP TABLESPACE的对应的所有数据文件中的数据。 通过这种方式可以恢复大部分数据,但由于是非字典模式所以需要将恢复出来的表与应用数据表一一对应起来,此时一般需要应用开发维护人员介入,通过人工识别来分辨哪些数据属于哪张表。由于DROP TABLESPACE操作修改了数据字典,并在OBJ$中删除了对应表空间上的对象,所以无法从OBJ$上获得DATA_OBJECT_ID与OBJECT_NAME之间的对应关系。此时我们可以利用如下的方法,尽可能多得获取DATA_OBJECT_ID与OBJECT_NAME之间的对应关系。

 

 

 

select tablespace_name,segment_type,count(*) from dba_segments where owner=’PARNASSUSDATA’  group by tablespace_name,segment_type; 

TABLESPACE SEGMENT_TYPE      COUNT(*)

———- ————— ———-

USERS      TABLE                  126

USERS      INDEX                  136

 

SQL> select count(*) from obj$;

 

COUNT(*)

———-

75698

 

 

SQL> select current_scn, systimestamp from v$database;

 

CURRENT_SCN

———–

SYSTIMESTAMP

—————————————————————————

1895940

25-4月 -14 09.18.00.628000 下午 +08:00

 

 

 

SQL> select file_name from dba_data_files where tablespace_name=’USERS’;

 

FILE_NAME

——————————————————————————–

H:\PP\MACLEAN\ORADATA\PARNASSUS\DATAFILE\O1_MF_USERS_9MNBMJYJ_.DBF

 

 

SQL> drop tablespace users including contents;

 

表空间已删除。

 

 

C:\Users\maclean>dir H:\APP\MACLEAN\ORADATA\PARNASSUS\DATAFILE\O1_MF_USERS_9MNBMJYJ_.DBF

驱动器 H 中的卷是 entertainment

卷的序列号是 A87E-B792

 

H:\APP\MACLEAN\ORADATA\PARNASSUS\DATAFILE 的目录

 

找不到文件

 

因为drop tablespace 后该TABLESPACE对应的数据文件在 OS上被删除。

 

 

 

 

 

 

此时通过文件恢复工具例如Windows平台上可以使用UNDELETER将被误删除的数据文件还原出来

 

PRM-DUL-CN65

 

 

启动PRM-DUL => recovery Wizard => 非字典模式

 

 

PRM-DUL-CN66

 

PRM-DUL-CN67

 

由于是非字典模式,所以需要自己选择合理的字符集!

 

 

PRM-DUL-CN68

 

加入刚才恢复出来的数据文件并点击扫描

 

PRM-DUL-CN69

 

PRM-DUL-CN70

 

之后选择从段头/盘区扫描表,如果从段头扫描表未能找到所有表,则考虑用从盘区扫描:

 

 

 

PRM-DUL-CN71

 

 

此时可以看到主界面树形图出现大量OBJXXXXX的表,这里的OBJXXXXX实际就是表的DATA_OBJECT_ID,一般如果有熟悉该套系统应用模式开发的技术人员可以通过浏览样本数据分析将该表与应用表对应起来:

 

 

PRM-DUL-CN72

 

 

如果没有人可以帮忙对应数据与表之间的关系,则可以考虑使用如下的手段:

 

 

由于此例子中仅仅是DROP了TABLESPACE表空间,而数据库本身完全是可用的,则此时可以利用FLASHBACK QUERY来获得DATA_OBJECT_ID与表名之间的映射关系。

 

 

SQL>  select count(*) from sys.obj$;COUNT(*)

———-

75436

 

 

 

 

 

 

 

SQL> select count(*) from sys.obj$ as of scn 1895940;

select count(*) from sys.obj$ as of scn 1895940

*

第 1 行出现错误:

ORA-01555: 快照过旧: 回退段号 0 (名称为 “SYSTEM”) 过小

 

一开始想利用FLASHBACK QUERY来找出OBJ$上之前的记录,但是发现由于使用SYSTEM ROLLBACK SEGMENT所以会出现ORA-01555错误

 

 

此时可以考虑使用AWR视图DBA_HIST_SQL_PLAN,只要在最近7天中访问过该表一般可以从执行计划中获得OBJECT#和OBJECT_NAME的映射关系:

 

SQL> desc DBA_HIST_SQL_PLAN

名称                                      是否为空? 类型

—————————————– ——– ———————–

DBID                                      NOT NULL NUMBER

SQL_ID                                    NOT NULL VARCHAR2(13)

PLAN_HASH_VALUE                           NOT NULL NUMBER

ID                                        NOT NULL NUMBER

OPERATION                                          VARCHAR2(30)

OPTIONS                                            VARCHAR2(30)

OBJECT_NODE                                        VARCHAR2(128)

OBJECT#                                            NUMBER

OBJECT_OWNER                                       VARCHAR2(30)

OBJECT_NAME                                        VARCHAR2(31)

OBJECT_ALIAS                                       VARCHAR2(65)

OBJECT_TYPE                                        VARCHAR2(20)

OPTIMIZER                                          VARCHAR2(20)

PARENT_ID                                          NUMBER

DEPTH                                              NUMBER

POSITION                                           NUMBER

SEARCH_COLUMNS                                     NUMBER

COST                                               NUMBER

CARDINALITY                                        NUMBER

BYTES                                              NUMBER

OTHER_TAG                                          VARCHAR2(35)

PARTITION_START                                    VARCHAR2(64)

PARTITION_STOP                                     VARCHAR2(64)

PARTITION_ID                                       NUMBER

OTHER                                              VARCHAR2(4000)

DISTRIBUTION                                       VARCHAR2(20)

CPU_COST                                           NUMBER

IO_COST                                            NUMBER

TEMP_SPACE                                         NUMBER

ACCESS_PREDICATES                                  VARCHAR2(4000)

FILTER_PREDICATES                                  VARCHAR2(4000)

PROJECTION                                         VARCHAR2(4000)

TIME                                               NUMBER

QBLOCK_NAME                                        VARCHAR2(31)

REMARKS                                            VARCHAR2(4000)

TIMESTAMP                                          DATE

OTHER_XML                                          CLOB

 

 

例如:

 

select object_owner,object_name,object# from DBA_HIST_SQL_PLAN where sql_id=’avwjc02vb10j4′

 

OBJECT_OWNER         OBJECT_NAME                                 OBJECT#

——————– —————————————- ———-

 

PARNASSUSDATA        TORDERDETAIL_HIS                              78688

 

 

 

 

可以利用如下脚本获得较多OBJECT_ID与OBJECT_NAME的映射关系

 

Select * from

(select object_name,object# from DBA_HIST_SQL_PLAN

UNION select object_name,object# from GV$SQL_PLAN) V1 where V1.OBJECT# IS NOT NULL minus select name,obj# from sys.obj$;

 

select obj#,dataobj#, object_name from WRH$_SEG_STAT_OBJ where object_name not in (select name from sys.obJ$) order by object_name desc;

 

 

另一个查询:

SELECT tab1.SQL_ID,

current_obj#,

tab2.sql_text

FROM DBA_HIST_ACTIVE_SESS_HISTORY tab1,

dba_hist_sqltext tab2

WHERE tab1.current_obj# NOT IN

(SELECT obj# FROM sys.obj$

)

AND current_obj#!=-1

AND tab1.sql_id  =tab2.sql_id(+);

 

 

 

注意以上方法仅仅在用户确实找不到所要恢复的数据表的任何定义信息时使用(即用户找任何对该应用模式设计有了解的人、脚本和文档),且由于依赖于AWR数据,所以并不十分准确。

 

 

 

 

 

 

 

恢复场景10   对于误操作DROP  TABLE的数据恢复

 

D公司的应用开发人员在ASM存储环境下,在没有任何备份的情况下DROP了系统中一张核心应用表,此时第一时间采用PRM-DUL可以恢复该DROP掉数据表的绝大部分数据。10g以后提供了 recyclebin回收站特性,可以首先通过查询DBA_RECYCLEBINS视图来确定被DROP掉的表是否在回收站中,如果在则优先通过回收站flashback to before drop,如果回收站中也没有了,则第一时间使用PRM-DUL恢复。

 

恢复简要流程如下:

  1. 首先将被DROP掉的数据表所在的表空间OFFLINE
  2. 通过查询数据字典或者LOGMINER找到被DROP掉数据表的DATA_OBJECT_ID,如果此步骤中得不到这个DATA_OBJECT_ID,则需要在NON-DICT非字典模式下
  3. 启动PRM-DUL,进入NON-DICT非字典模式,并加入被DROP掉数据表所在的表空间的所有数据文件,之后SCAN DATABASE+SCAN TABLE from Extent MAP
  4. 通过DATA_OBJECT_ID定位到展开对象树形图中对应的数据表,采用DataBridge模式插回到源数据库中

 

SQL> select count(*) from “MACLEAN”.”TORDERDETAIL_HIS”;COUNT(*)

———-

984359

 

SQL>

SQL> create table maclean.TORDERDETAIL_HIS1 as select * from  maclean.TORDERDETAIL_HIS;

 

Table created.

 

SQL> drop table maclean.TORDERDETAIL_HIS;

 

Table dropped.

 

 

 

 

可以通过logminer或者《恢复场景9》中提供的方法得到大致的DATA_OBJECT_ID,使用LOGMINER则大致的脚本如下:

 

EXECUTE DBMS_LOGMNR.ADD_LOGFILE( LOGFILENAME => ‘/oracle/logs/log1.f’, OPTIONS => DBMS_LOGMNR.NEW);EXECUTE DBMS_LOGMNR.ADD_LOGFILE( LOGFILENAME => ‘/oracle/logs/log2.f’, OPTIONS => DBMS_LOGMNR.ADDFILE);

 

Execute DBMS_LOGMNR.START_LOGMNR(DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG+DBMS_LOGMNR.COMMITTED_DATA_ONLY);

 

SELECT * FROM V$LOGMNR_CONTENTS ;

 

EXECUTE DBMS_LOGMNR.END_LOGMNR;

 

 

即便这里得不到DATA_OBJECT_ID,在数据表不多的情况下还是可以通过人工识别数据来定位我们需要恢复的数据表。

 

首先将被DROP掉的数据表所在的表空间OFFLINE

 

SQL> select tablespace_name from dba_segments where segment_name=’TPAYMENT’;TABLESPACE_NAME

——————————

USERS

 

SQL> select file_name from dba_data_files where tablespace_name=’USERS’;

 

FILE_NAME

—————————————————————-

+DATA1/parnassus/datafile/users.263.843694795

 

SQL> alter tablespace users offline;

 

Tablespace altered.

 

启动PRM-DUL到NON-DICT模式,并加入对应的数据文件选择SCAN DATABASE+SCAN TABLE From Extents:

 

PRM-DUL-CN73

 

PRM-DUL-CN74

 

加入相关ASM Diskgroup所有相关的ASM Disks后点击ASM analyze

 

 

 

PRM-DUL-CN75

 

由于是在非字典模式下所有需要输入必要的字符集信息:

PRM-DUL-CN76

 

 

选择被DROP掉的表所在的数据文件即可,多余的数据文件可不选择,并点击SCAN:

 

PRM-DUL-CN77

 

PRM-DUL-CN78

 

点中生成的数据库名,并右键选择scan tables from extents:

 

 

PRM-DUL-CN79

 

PRM-DUL-CN80

通过人工识别发现DATA_OBJECT_ID=82641的数据对应于被DROP掉的TORDERDETAIL_HIS表,通过DataBridge技术将其传输回源库别的表空间中。

 

 

 

PRM-DUL-CN81

 

PRM-DUL-CN82

 

PRM-DUL-CN83

 

FAQ 常见问题解答

 

  1. 我不知道我的数据库的字符集信息怎么办?

 

你可以通过ORACLE告警日志alert.log来大致了解你的数据库字符集信息,例如:

 

 

[oracle@mlab2 trace]$ grep  -i character alert_Parnassus.logDatabase Characterset is US7ASCIIDatabase Characterset is US7ASCII

alter database character set INTERNAL_CONVERT AL32UTF8

Updating character set in controlfile to AL32UTF8

Synchronizing connection with database character set information

Refreshing type attributes with new character set information

Completed: alter database character set INTERNAL_CONVERT AL32UTF8

alter database national character set INTERNAL_CONVERT UTF8

Completed: alter database national character set INTERNAL_CONVERT UTF8

Database Characterset is AL32UTF8

Database Characterset is AL32UTF8

Database Characterset is AL32UTF8

 

 

 

  1. 为什么我使用PRM-DUL总是闪退或者报一些例如” gc warning: Repeated allocation of very large block (appr.size 512000)”的GC报错?

 

就目前ParnassuData看到的案例而言,绝大多数此类问题都是由于使用了非推荐的JAVA环境所造成的;特别是在Linux平台上使用了redhat gcj java的话很容易造成该问题。ParnassusData建议用户使用ORACLE提供的JDK1.6以上环境运行PRM-DUL,可以直接使用$JAVA_HOME/bin/java –jar PRM-DUL.jar来启动PRM-DUL。

 

各个操作系统平台的JDK 1.6的下载连接如下:

 

http://www.oracle.com/technetwork/java/javasebusiness/downloads/java-archive-downloads-javase6-419409.html#jdk-6u45-oth-JPR

 

  1. 如果我发现了PRM-DUL的bug,我应当如何report bug给ParnassusData?

 

ParnassusData欢迎任何人给我们report bug,只需要发邮件到report_bugs@parnassusdata.com就好了, 建议提交bug时附上您详细的运行环境 包括操作系统、JAVA运行环境和ORACLE数据库版本的环境信息。

 

 

  1. 我启动PRM-DUL出现下面的错误怎么办?

Error: no `server’ JVM at `D:\Program Files (x86)\Java\jre1.5.0_22\bin\server\jvm.dll’.

 

这是由于用户的环境中安装的是JAVA Runtime Environment JRE,而没有安装JDK。而启动PRM-DUL的脚本中加入了-sever的选项,该选项在JRE 版本1.5之前是没有的,所以会出现该错误。

 

ParnassusData建议用户使用ORACLE提供的JDK1.6以上环境运行PRM-DUL。

 

 

各个操作系统平台的JDK 1.6的下载连接如下:

 

http://www.oracle.com/technetwork/java/javasebusiness/downloads/java-archive-downloads-javase6-419409.html#jdk-6u45-oth-JPR

 

  1. 为什么我在使用PRM-DUL时中文显示乱码?

 

目前已知有2种可能导致该中文显示乱码问题:

  • PRM-DUL所运行操作系统环境没有安装中文语言包,例如PRM-DUL运行在没有安装中文语言包的Linux平台上,那么PRM-DUL作为一个软件是无法正常显示中文的。
  • 操作系统已经安装了必要的语言包,但启动PRM-DUL使用的是JDK 1.4的JAVA运行环境,同样可能出现中文乱码,该问题建议使用JDK 1.6或以上版本来解决

 

 

 

 

 

 

  1. PRM-DUL是否支持LOB大对象字段?

PRM-DUL支持CLOB、NCLOB、BLOB等大对象字段,包括分区表、Disable/Enable Storage in ROW等情况均支持对LOB的数据搭桥模式,LOB数据一样无需落地,就可以应用到远程目标数据库中。

 

 

对于LOB大对象字段不支持普通的UNLOAD抽取方式,因为抽取方式在数据导入时会十分麻烦;所以我们也推荐用户尽可能使用DataBridge数据搭桥模式。

 

 

 

  1. PRM-DUL有什么讨论的论坛吗?

 

目前我们有中文的PRM-DUL讨论BBS版面,地址为:

http://t.askmaclean.com/forum-24-1.html

 

Find More

 

 

 

Resource : http://www.parnassusdata.com/resources/

Technical Support:  service@parnassusdata.com

Sales: sales@parnassusdata.com

Download Software: http://www.parnassusdata.com/

Contact: http://www.parnassusdata.com/zh-hans/contact

 

 

 

Conclusion

 

 

 

 

ParnassusData Corporation ,  Shanghai , GaoPing Road No. 733 . China

Phone: (+86) 13764045638

ParnassusData.com

Facebook: http://www.facebook.com/parnassusData

Twitter: http://twitter.com/ParnassusData

Weibo: http://weibo.com/parnassusdata

 

 

Copyright © 2013, ParnassusData and/or its affiliates. All rights reserved. This document is provided for information purposes only and the contents hereof are subject to change without notice. This document is not warranted to be error-free, nor subject to any other warranties or conditions, whether expressed orally or implied in law, including implied warranties and conditions of merchantability or fitness for a particular purpose. We specifically disclaim any liability with respect to this document and no contractual obligations are formed either directly or indirectly by this document. This document may not be reproduced or transmitted in any form or by any means, electronic or mechanical, for any purpose, without our prior written permission.

 

Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

 

AMD, Opteron, the AMD logo, and the AMD Opteron logo are trademarks or registered trademarks of Advanced Micro Devices. Intel and Intel Xeon are trademarks or registered trademarks of Intel Corporation. All SPARC trademarks are used under license and are trademarks or registered trademarks of SPARC International, Inc. UNIX is a registered trademark licensed through X/Open Company, Ltd. 0410

 

Copyright © 2014 ParnassusData Corporation. All Rights Reserved.

 

 

 

DROP TABLE的Oracle数据恢复 PRM-DUL

D公司的应用开发人员在ASM存储环境下,在没有任何备份的情况下DROP了系统中一张核心应用表,此时第一时间采用PRM可以恢复该DROP掉数据表的绝大部分数据。10g以后提供了 recyclebin回收站特性,可以首先通过查询DBA_RECYCLEBINS视图来确定被DROP掉的表是否在回收站中,如果在则优先通过回收站flashback to before drop,如果回收站中也没有了,则第一时间使用PRM恢复。

 

恢复简要流程如下:

  1. 首先将被DROP掉的数据表所在的表空间OFFLINE
  2. 通过查询数据字典或者LOGMINER找到被DROP掉数据表的DATA_OBJECT_ID,如果此步骤中得不到这个DATA_OBJECT_ID,则需要在NON-DICT非字典模式下
  3. 启动PRM,进入NON-DICT非字典模式,并加入被DROP掉数据表所在的表空间的所有数据文件,之后SCAN DATABASE+SCAN TABLE from Extent MAP
  4. 通过DATA_OBJECT_ID定位到展开对象树形图中对应的数据表,采用DataBridge模式插回到源数据库中

 

 

 

SQL> select count(*) from "MACLEAN"."TORDERDETAIL_HIS"; 

  COUNT(*)
----------
    984359

SQL> 
SQL> create table maclean.TORDERDETAIL_HIS1 as select * from  maclean.TORDERDETAIL_HIS;

Table created.

SQL> drop table maclean.TORDERDETAIL_HIS;

Table dropped.


 

 

可以通过logminer或者《恢复场景9》中提供的方法得到大致的DATA_OBJECT_ID,使用LOGMINER则大致的脚本如下:

 

EXECUTE DBMS_LOGMNR.ADD_LOGFILE( LOGFILENAME => '/oracle/logs/log1.f', OPTIONS => DBMS_LOGMNR.NEW);

EXECUTE DBMS_LOGMNR.ADD_LOGFILE( LOGFILENAME => '/oracle/logs/log2.f', OPTIONS => DBMS_LOGMNR.ADDFILE);

Execute DBMS_LOGMNR.START_LOGMNR(DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG+DBMS_LOGMNR.COMMITTED_DATA_ONLY);

SELECT * FROM V$LOGMNR_CONTENTS ;

EXECUTE DBMS_LOGMNR.END_LOGMNR;




即便这里得不到DATA_OBJECT_ID,在数据表不多的情况下还是可以通过人工识别数据来定位我们需要恢复的数据表。

 

首先将被DROP掉的数据表所在的表空间OFFLINE

 

SQL> select tablespace_name from dba_segments where segment_name='TPAYMENT';

TABLESPACE_NAME
------------------------------
USERS

SQL> select file_name from dba_data_files where tablespace_name='USERS';

FILE_NAME
----------------------------------------------------------------
+DATA1/parnassus/datafile/users.263.843694795

SQL> alter tablespace users offline;

Tablespace altered.

 

 

 

 

启动PRM到NON-DICT模式,并加入对应的数据文件选择SCAN DATABASE+SCAN TABLE From Extents:

 

 

prm-dul-drop-table1

prm-dul-drop-table2

 

 

由于是在非字典模式下所有需要输入必要的字符集信息:

 

prm-dul-drop-table3

 

 

选择被DROP掉的表所在的数据文件即可,多余的数据文件可不选择,并点击SCAN:

 

prm-dul-drop-table4

 

prm-dul-drop-table5

 

 

点中生成的数据库名,并右键选择scan tables from extents:

prm-dul-drop-table6

prm-dul-drop-table7

 

通过人工识别发现DATA_OBJECT_ID=82641的数据对应于被DROP掉的TORDERDETAIL_HIS表,通过DataBridge技术将其传输回源库别的表空间中。

 

 

prm-dul-drop-table8

 


prm-dul-drop-table9

 

prm-dul-drop-table10

 

诗檀软件成功使用PRM-DUL帮助用户恢复了IMPDP TABLE_EXISTS_ACTION=REPLACE覆盖的数据表

某化工所用户数据库在无归档、无备份的情况下由于业务人员误操作IMPDP 且加上了 TABLE_EXISTS_ACTION=REPLACE选项导致原数据表被覆盖,由于该系统无归档和物理备份故上述操作导致丢失了数个月的业务数据。 诗檀软件工程师Biot.wang在接到该case后,很快通过PRM-DUL工具定位到了被IMPDP+TABLE_EXISTS_ACTION=REPLACE覆盖后仍剩余的表数据的EXTENT,采用PRM-DUL特定的在字典模式下的EXTENT恢复模式,仅仅使用一个小时不到就找回了大部分数据。

 

对于此种IMPDP TABLE_EXISTS_ACTION=REPLACE 或者 TABLE_EXISTS_ACTION=TRUNCATE 覆盖的数据,一定要在第一时间将  TABLESPACE OFFLINE掉,或者将数据库实例SHUTDOWN,避免后续的恢复希望也因为进一步的空间分配而被彻底覆盖。 这个例子中较为幸运的是,覆盖的数据很少,几乎恢复了100%的数据。

 

prm-dul TABLE_EXISTS_ACTION=REPLACE truncate

 

 

 

如果自己搞不定可以找诗檀软件专业ORACLE数据库修复团队成员帮您恢复!

诗檀软件专业数据库修复团队

服务热线 : 13764045638   QQ号:47079569    邮箱:service@parnassusdata.com

ORACLE PRM是诗檀软件独立研发的ORACLE数据库灾难恢复软件,其具有全程图形化界面、简单高效等特点。

欢迎下载使用ORACLE PRM。 下载地址:http://parnassusdata.com/sites/default/files/ParnassusData_PRMForOracle_3206.zip

PRM用户使用手册。http://www.parnassusdata.com/sites/default/files/ParnassusData%20Recovery%20Manager%20For%20Oracle%20Database%E7%94%A8%E6%88%B7%E6%89%8B%E5%86%8C%20v0.3.pdf

 

 

Oracle DUL Data Unloader数据恢复工具信息汇总

PRM-DUL是开放的ORACLE DUL 软件,点击下面的链接下载PRM-DUL

DUL FOR LINUX平台(已更新为PRM-DUL)

DUL FOR Windows平台 (已更新为PRM-DUL)

 

如果自己搞不定可以找诗檀软件专业ORACLE数据库修复团队成员帮您恢复!

诗檀软件专业数据库修复团队

服务热线 : 13764045638   QQ号:47079569    邮箱:service@parnassusdata.com

Oracle DUL Data Unloader数据恢复工具信息汇总

DULRULER

Oracle DUL 是Oracle公司内部的数据库恢复工具,由在荷兰的Oracle Support,Bernard van Duijnen开发:

  • DUL不是Oracle的一个产品
  • DUL不是一个受Oracle支持的产品
  • DUL被严格限制为Oracle Support售后支持部门内部使用
  • DUL的使用在国外需要经过Oracle公司的内部审批,首先你必须购买了Oracle的标准服务PS才可能用到DUL,否则甚至没有资格使用DUL
  • DUL被严格控制的一个原因是其采用了部分Oracle源代码,所以必须被严格控制

oracle-dul-1

大约从DUL 9开始,Bernard van Duijnen为了限制外界使用DUL,所以给DUL加上了软件时间锁,即他会定期编译不同平台上的DUL(DUL基于C语言编写)并定期上传到ORACLE 内部的DUL workspace(基于stbeehive的空间),Oracle Support可以使用内部VPN登陆后下载。就是说 好比bernard.van.duijnen 在10月1日发布了一个版本,日期锁是30天,那么这个版本到11月1日基本就失效了, DUL不是简单的读OS时间,所以改OS时间是没用的。 因为Oracle的datafile里也记录了一个当前时间,所以DUL读的是datafile里的时间。 一般用户不可能为了用DUL去改那个时间。

注意由于bernard.van.duijnen同学不提供HP-UX平台上的DUL,所以DUL没有HP-UX的对应版本。

同时早期的Oracle DUL版本用在现在的版本10g、11g、12c的数据库基本是用不了了,因为太老了。  在美国使用DUL是被严格控制的,在中国国内的话 基本就是Oracle ACS 高级客户服务部门对外在用,购买ORACLE ACS现场服务的价格还是很贵的。

附件为一个Oracle ACS提供DUL 服务的介绍文档(当然原厂现场服务是比较昂贵的,且前提是用户已经每年购买了PS标准服务,否则甚至无法购买ACS高级服务的现场服务):

http://www.askmaclean.com/wp-content/uploads/2014/01/DUL.pdf

 

 

DUL 10的英文版使用手册:

DUL User’s and Configuration Guide V10.2.4.27

http://www.askmaclean.com/wp-content/uploads/2014/01/DUL-Users-and-Configuration-Guide-V10.2.4.27.pdf

 

 

 

DUL可以从已经损坏严重的数据库中抽取数据, DUL可以直接扫描Oracle Datafile数据文件,并识别表头块segment header,访问Extent盘区信息,并从中读取实际行数据。 后续DUL能生成SQLLDR形式的导入文件,或者EXP格式的DMP文件。

如果SYSTEM表空间数据文件还在,那么 DUL读取Oracle数据字典。否则DUL采取采用的形式实际读取行,并根据内部算法判断字段类型,字段长度。

DUL可以基本上处理所有的常见行类型,包括常规行、迁移行、链式行、多盘区和簇表等;处理这些行时不需要额外人工介入。跨平台抽取也是OK的。  DUL直接从Oracle Datafile抽取数据,而无需Oracle数据库实例。 其实施脏读,假设每个事务均已经提交。 DUL不检测是否需要做介质恢复,即便是损坏的数据块也可以读出。 支持DMT和LMT表空间。 由于是脏读,所以DUL恢复数据后一般建议由应用验证数据。

兼容性方面DUL可以处理从不同操作系统上拷贝过来的数据文件。支持大多数数据库结构: 链式行、迁移行、hash/index 簇,LONG,RAW,ROWID,DATE,Number,多FreeList,高水位,NULL等等。 DUL 兼容ORACLE 6,7,8和9以及10g 11g 12c。

 

 

 

 

PRM Schema-level DataBridge (1)

 

 

 

诗檀软件(Maclean 所在的公司)开发了DUL的同类产品 ,PRM-DUL。 在DUL的基础上引入了图形化界面GUI和DataBridge(数据无需落地成为SQLLDR文件,直接像DBLINK一样传输到目标数据库)等功能;同时由于PRM-DUL是基于JAVA编写的,所以可以跨所有平台,包括HP-UX。

PRM-DUL的免费版本下载:

http://parnassusdata.com/sites/default/files/ParnassusData_PRMForOracle_3206.zip

PRM-DUL的使用手册 http://www.parnassusdata.com/sites/default/files/ParnassusData%20Recovery%20Manager%20For%20Oracle%20Database%E7%94%A8%E6%88%B7%E6%89%8B%E5%86%8C%20v0.3.pdf

 

PRM-DUL的免费版本默认每张表只能抽取一万行数据,如果你的数据库很小以至于没有超过一万行数据的表,那么可以直接使用免费的PRM-DUL。 如果你的数据库较大且数据十分重要,那么可以考虑购买企业版的PRM-DUL,企业版PRM-DUL 针对一套数据库提供一个License软件使用许可证,一个License的价格是7500元人民币(含17%增值税)。

同时PRM-DUL还提供部分免费的License:

免费开放几个PRM-DUL 企业版License Key

 

 

 

 

 

 

 

 

如果你的Oracle数据库恢复case在使用DUL后仍搞不定,那么可以考虑通过服务恢复:

诗檀软件目前提供几乎所有场景的Oracle恢复情况,包括:数据库无法打开,表被误DROP、TRUNCATE、DELETE等,ASM Diskgroup无法MOUNT等。

 

 

PRM-DUL 基于JAVA开发,这保证了PRM可以跨平台运行,无论是AIX、Solaris、HPUX等Unix平台, Redhat、Oracle Linux、SUSE等Linux平台,还是Windows平台上均可以直接运行PRM。

 

PRM-DUL支持的操作系统平台:

 

Platform Name Supported
AIX POWER Yes
Solaris Sparc Yes
Solaris X86 Yes
Linux X86 Yes
Linux X86-64 Yes
HPUX Yes
MacOS Yes

 

 

PRM-DUL目前支持的数据库版本

 

ORACLE DATABASE VERSION Supported
Oracle 7 Yes
Oracle 8 Yes
Oracle 8i Yes
Oracle 9i Yes
Oracle 10g Yes
Oracle 11g Yes
Oracle 12c Yes

 

 

PRM-DUL目前支持的多语言:

 

 

语言 字符集 对应的编码
中文 简体/繁体 ZHS16GBK GBK
中文 简体/繁体 ZHS16DBCS CP935
中文 简体/繁体 ZHT16BIG5 BIG5
中文 简体/繁体 ZHT16DBCS CP937
中文 简体/繁体 ZHT16HKSCS CP950
中文 简体/繁体 ZHS16CGB231280 GB2312
中文 简体/繁体 ZHS32GB18030 GB18030
日文 JA16SJIS SJIS
日文 JA16EUC EUC_JP
日文 JA16DBCS CP939
韩语 KO16MSWIN949 MS649
韩语 KO16KSC5601 EUC_KR
韩语 KO16DBCS CP933
法语 WE8MSWIN1252 CP1252
法语 WE8ISO8859P15 ISO8859_15
法语 WE8PC850 CP850
法语 WE8EBCDIC1148 CP1148
法语 WE8ISO8859P1 ISO8859_1
法语 WE8PC863 CP863
法语 WE8EBCDIC1047 CP1047
法语 WE8EBCDIC1147 CP1147
德语 WE8MSWIN1252 CP1252
德语 WE8ISO8859P15 ISO8859_15
德语 WE8PC850 CP850
德语 WE8EBCDIC1141 CP1141
德语 WE8ISO8859P1 ISO8859_1
德语 WE8EBCDIC1148 CP1148
意大利语 WE8MSWIN1252 CP1252
意大利语 WE8ISO8859P15 ISO8859_15
意大利语 WE8PC850 CP850
意大利语 WE8EBCDIC1144 CP1144
泰语 TH8TISASCII CP874
泰语 TH8TISEBCDIC TIS620
阿拉伯语 AR8MSWIN1256 CP1256
阿拉伯语 AR8ISO8859P6 ISO8859_6
阿拉伯语 AR8ADOS720 CP864
西班牙语 WE8MSWIN1252 CP1252
西班牙语 WE8ISO8859P1 ISO8859_1
西班牙语 WE8PC850 CP850
西班牙语 WE8EBCDIC1047 CP1047
葡萄牙语 WE8MSWIN1252 CP1252
葡萄牙语 WE8ISO8859P1 ISO8859_1
葡萄牙语 WE8PC850 CP850
葡萄牙语 WE8EBCDIC1047 CP1047
葡萄牙语 WE8ISO8859P15 ISO8859_15
葡萄牙语 WE8PC860 CP860

 

 

PRM-DUL支持的表存储类型:

 

表存储类型 Supported
Cluster Table簇表 YES
索引组织表,分区或非分区 YES
普通堆表,分区或非分区 YES
普通堆表 启用基本压缩 YES(Future)
普通堆表 启用高级压缩 NO
普通堆表 启用混合列压缩 NO
普通堆表 启用加密 NO
带有虚拟字段virtual column的表 NO
链式行、迁移行 chained rows 、migrated rows YES

 

 

注意事项: 对于virtual column、11g optimized default column而言 数据抽取可能没问题,但会丢失对应的字段。 这2个都是11g之后的新特性,使用者较少。

 

 

PRM-DUL支持的数据类型

 

数据类型 Supported
BFILE No
Binary XML No
BINARY_DOUBLE Yes
BINARY_FLOAT Yes
BLOB Yes
CHAR Yes
CLOB and NCLOB Yes
Collections (including VARRAYS and nested tables) No
Date Yes
INTERVAL DAY TO SECOND Yes
INTERVAL YEAR TO MONTH Yes
LOBs stored as SecureFiles Future
LONG Yes
LONG RAW Yes
Multimedia data types (including Spatial, Image, and Oracle Text) No
NCHAR Yes
Number Yes
NVARCHAR2 Yes
RAW Yes
ROWID, UROWID Yes
TIMESTAMP Yes
TIMESTAMP WITH LOCAL TIMEZONE Yes
TIMESTAMP WITH TIMEZONE Yes
User-defined types No
VARCHAR2 and VARCHAR Yes
XMLType stored as CLOB No
XMLType stored as Object Relational No

 

 

PRM-DUL对ASM的支持

 

 

功能 Supported
支持直接从ASM中抽取数据,无需拷贝到文件系统上 YES
支持从ASM中拷贝数据文件 YES
支持修复ASM metadata YES
支持图形化展示ASM黑盒 Future

 

dul1

 

 

以下 为ORACLE DUL 工具使用手册:

 

ORACLE DUL 的原理和功能列表:

 

独立的C-程序

DUL是一个独立的C程序,它直接从数据文件的表中检索行。 Oracle的RDBMS软件完全不被使用。 DUL进行脏读,它假定每个事务被提交。它也不检查/是否需要完成介质恢复。

最后一招

DUL旨在检索无法用其他方法检索的数据。它不是EXP,SQL *加等等的替代选择,而是最后的方案。不用于正常的生产环境。

在使用DUL之前,你必须知道RDBMS有许多隐藏的功能可以强制打开坏数据库。无记录的init.ora参数和事件可以用于跳过前滚,禁用回滚,禁用特定SMON行动,推进数据库scn等等。

 

数据库损坏 – 数据块OK

数据库可以被破坏,但单独的数据块必须是100%正确。在所有导出进行检查时,确保块没有损坏并属于正确的数据段。如果扫描时遇到损坏的块,一个错误信息被打印在加载程序(loader)文件和标准输出。导出将继续下一行或块。

cluster/表/索引中的行

DUL能且只能导出索引/表/cluster中数据。它不会转储触发器,存储过程,也不会创建表或视图的SQL脚本。 (但描述它们的数据字典表可以被导出)。该数据将被导出为在适合SQL * Loader或IMP的格式。同时,SQL * Loader的匹配控制文件也会生成。

DUL可以导出索引和索引组织表。索引的导出能用来确定表应该有多少行或标识缺失的行。

 

跨平台导出

 

支持跨平台导出。数据库可以从与DUL-主机不同的操作系统被复制。 (迄今所涉及的数据库/系统:Sequent/ptx, Vax Vms, Alpha Vms, MVS, HP9000/8xx, IBM AIX, SCO Unix, Alpha OSF/1, Intel Windows NT)。

在“init.dul”中的配置参数必须进行修改,以匹配原始平台和O / S,而非导出执行的平台。

 

强大功能

DUL不会转储,运行或挂起不论数据库的破坏有多严重;(几乎)支持所有Oracle功能

对于所有数据库结构的面支持:行链接,行迁移,散列/索引集群,longs,raws,行id,日期,数字,多空闲列表组(multiple free list groups),段高水位(segment high water mark),NULLs,显示NULL列尾部,无限扩展,Oracle8的新数据块布局,分区表。

后来增加的有lobs,压缩索引,9iR2压缩表。可变数组(varray)和抽象数据类型ADTs(用户定义的对象)在SQL * Loader模式下被部分支持。

ASM是完全支持的,文件可以从ASM磁盘组中提取。不使用装载的ASM实例,磁盘直接访问。支持非默认的ASM分配单元大小。

使用unexp命令套件,数据可以从导出的转储文件中恢复。Unpump的一些初步工作已完成,以支持数据pump文件。

 

支持的RDBMS版本

DUL能用于Oracle 6以上版本。 DUL已通过从6.0.26到10.2的所有版本。即使旧版数据块header布局(6.0.27.2前)也支持。

多字节支持

DUL本质上是一个单一字节的应用程序。它的命令解析器不理解多字节字符,但导出任何多字节的数据库是可以的。对于所有可能情况都有解决方法。

DUL可转换为UTF8。这是为了存储在UTF16的 NCLOBS。

 

限制

MLSLABELS

不支持受信任的Oracle多级安全标贴。

(LONG)RAW

DUL可以导出(long)raws。目前在SQL * Loader中合适的格式来保存所有的long raws。所以long raws和blobs可以在这两种模式下被导出。

 

ORACLE8对象选项和LOBS

 

尚不支持嵌套表,如果需要请告知我来添加。支持varray和ADT,以及储存为核心lob的。 在SQL * Loader模式和EXP模式下都支持CLOBS,NCLOBS。 BLOBS在EXP模式下最好处理,在SQL * Loader模式下产生的十六进制格式当前未能正确加载。

可移植性

通过ANSI-C编译器,DUL可以移植到任何操作系统。 DUL已经被移植到许多UNIX变体,VMS和WindowsNT。目前,所有构建都使用gcc和Linux的交叉编译环境完成。

 

RDBMS 内部知识

对Oracle RDBMS内部的良好掌握是成功地使用DUL的必要前提。例如数据服务器内部(DSI)文档和教程提供一个良好的基础。甚至还有专门的DUL模块。

设置和使用DUL

配置文件

针对DUL有两个配置文件。 “init.dul”包含所有的配置参数。 (缓存大小,header布局的详细内容,oracle数据块大小,输出文件格式)在控制文件中,“control.dul”,数据库的数据文件名和asm磁盘可以被指定。

可用数据字典

如果组成SYSTEM表空间的数据文件可用,那Oracle数据字典就可用。Oracle分配给这些文件的数字和你给定的名称(不需要是Oracle知道的原始名称),必须列入“control.dul”文件。您还需要包括文件数和其他表空间的文件名,这些表空间是最终想要导出表及其数据。不包含这些文件不会影响数据字典导出步骤,但会影响之后的表导出。

 

当USER$, OBJ$, TAB$ and COL$能被导出时,使用DUL

步骤如下:

  • 在目标数据库配置DUL。这表示要创建一个正确的dul和control.dul。 SYSTEM表空间的数据文件数量和名称必须与任何你要导出表和数据的表空间的数据文件一并包括在control.dul文件中。对于Oracle8及以上版本,表空间数和相关文件数必须在每个数据文件中指定。
  • 使用”BOOTSTRAP;”命令来准备导出。在引导过程中会发现一个兼容的段,找到bootstrap$表导出。不再需要旧的“dul dictv7.ddl”。

 

  • 导出数据文件被包括在“dul”文件中的表。使用以下命令之一:
    • “UNLOAD TABLE [ owner>.]table ;(别忘了分号)
      • 这将导出表定义和表的数据。
    • “UNLOAD USER user name ;
      • 这为指定的用户导出所有表和数据。
    • “UNLOAD DATABASE ;

这导出所有可用的数据库表。 (除了用户SYS)。

 

 

 

unload user SCOTT;

 

 

 

没有可用的数据字典

如果SYSTEM表空间的数据文件不可用,那么虽然可以unload 导出数据,但USER,TABLE和COLUM名称是未知的。识别表会是艰巨的任务。但它可以(并已经)被完成。你需要深入了解你的应用程序和应用程序表。列类型可以由DUL猜测,但表和列名丢失了。来自同一数据库但旧了几周的任何SYSTEM表空间可以有很大的帮助!DUL使用的多数信息不改变。 (只有dataobj#是在truncate或索引重建过程中)

 

使用DUL而不用SYSTEM表空间

步骤如下:

  • 在目标数据库配置DUL。这表示创建一个正确的dul和control.dul。 (见端口具体参数)。在这种情况下,control.dul文件需要将被导出的表和数据的数量和数据文件名,但它并不需要SYSTEM表空间信息。
  • SCAN DATABASE;  :扫描数据库,创建程度和分段图
  • SCAN TABLES; or SCAN EXTENTS; :收集行统计
  • 从步骤3的输出中识别丢失的表。
  • 导出识别出的表。

 

自动搜索

 

为了便于寻找丢失的表:在seen_tab.dat和seen_col.dat扫描的统计信息可以被加载到一个全新的数据库。如果你要重建表(但愿创建表的脚本仍可用),那么通过两个SQL * Plus脚本(fill.sql和getlost.sql),“丢失”表的结构信息可以匹配到“可见”表被扫描的信息。

 

提示和陷阱

 

  • 名称与DUL不是真正相关的,仅与必须加载数据的人相关。但是,如果你不知道被导出的数据来自哪个表,它就没有任何价值。
  • 猜测的列可能是错误的。即使算法保守,在不确定时决定UNKNOWN。
  • 显示NULL尾部列不存储在数据库中。因此,如果最后一列只包含空值,那扫描将无法找到。 (在导出显示NULL列尾部被正确处理时)。
  • 当一个表被删除,该描述仅从数据字典中被移除。除非数据块被重新用于新的段,否则不会被重写。所以扫描软件可以看到被删除的表。
  • 没有行的表会被忽视。较新的对象ID比旧对象更高。如果表被重建,或者如果有同一表的测试和生产版本,对象的id可用于决定。

DDLDUL描述语言)导出语句概述

DUL使用类似SQL的命令界面。有导出区段,表,用户或整个数据库的DDL语句。所需的数据字典信息可以在DDL语句中指定或取自先前导出的数据字典。以下三个语句将导出DEPT表。如果数据字典和区段地图可用,最常见的形式是:

UNLOAD TABLE scott.dept;

所有相关信息也能在语句中指定:

REM Columns with type in the correct order

REM The segment header loaction in the storage clause

UNLOAD TABLE dept( deptno NUMBER, dname CHAR, loc CHAR)               STORAGE( EXTENTS ( FILE 1 BLOCK 1205 ));

 

Oracle版本6:

REM version 6 data blocks have segment header location in each block        ALTER SESSION SET USE_SCANNED_EXTENT_MAP = TRUE;        UNLOAD TABLE dept( deptno NUMBER, dname CHAR, loc CHAR)               STORAGE( EXTENTS ( FILE 1 BLOCK 1205 ));

Oracle 7:

REM Oracle7 data blocks have object id in each block

ALTER SESSION SET USE_SCANNED_EXTENT_MAP = TRUE;        UNLOAD TABLE dept( deptno NUMBER, dname CHAR, loc CHAR)               STORAGE( OBJNO 1501 );

 

DUL输出格式

只有完好无损的行会被写入输出文件。对它来说,各行是缓存的。buffer的大小可以通过init.dul的参数BUFFER来更改。高BUFFER参数不等同于较快的速度,它应该大到足以容纳一个完整的行。不完整或损坏的行不会被写出。 init.dul参数FILE_SIZE_IN_MB可用于将输出(在适当的边界)分割成多个文件,每个文件可以单独加载。

有三种不同的输出格式模式

  • 导出模式
  • SQL * Loader模式:流数据文件
  • SQL * Loader模式:固定的物理记录数据文件

 

导出模式

生成的文件与由EXP生成的表模式导出完全不同!该文件是IMP能加载的最小格式。每个表中将生成单独的IMP可加载文件。它是单一的表转储文件。它包含一个header,一个insert table语句和表数据。表补助grant,存储子句,或触发器将不被包括在内。一个最小的create table语句被包含在内(无存储子句,只有列名和没有精度的类型)。在生成的header中文件中的字符集指示是V6类型。它被设置为表示基于ASCII的字符集。

要启用导出模式, 将init.dul参数EXPORT_MODE设置为TRUE。

由于生成的伪转储文件不包含字符集信息集NLS_LANG以匹配原始数据库。在导出模式,不进行字符集转换。

 

SQL * Loader模式

数据在此模式要么完全不能转换,要么全部被转换为UTF8,前提是设置了LDR_OUTPUT_IN_UTF8。在混合字符集环境中此设置是必需的,因为一个数据文件的内容必须有一个单独的字符集。<\ p>

在加载数据时,你可能需要设置NLS_LANG以匹配原始数据库,从而防止不必要的字符集转换。

在两种SQL * Loader输出格式中,列将被空格分隔并被双引号括起来。数据中的任何双引号将增加一倍。SQL * Loader会识别这点并只加载一个。使用init.dul参数LDR_ENCLOSE_CHAR,你能将包围列的字符从双引号更改为任何想要的字符。

有两种类型的物理记录模式:

数据流模式

 

在流模式下没什么特别的,每条记录后会有换行符打印出来。这是紧凑模式,可用于数据不包含换行符的情况。要启用数据流模式,设置LDR_PHYS_REC_SIZE = 0

init.dul

 

固定的物理记录

如果数据可包含换行符,这个模式是必不可少的。一个逻辑记录和一个完整的行可以由多个物理记录组成。默认的记录长度为81,这很适合VT220的屏幕。物理记录大小可用init.dul的LDR_PHYS_REC_SIZE指定。

输出文件名

生成的文件名是:owner name_table name.ext。IMP可加载文件的扩展名为“.dmp”。“.dat”和“.ctl”被用于SQL * Loader数据文件和控制文件。为了防止变量替换和其他不好的影响,奇怪的字符被剥离。(只有字母数字和’_’被允许)。

如果设置了FILE参数,生成的名称将是FILEnnn.ext。如果文件系统不支持足够长的文件名,可以用这个方法处理。 (旧的窗口,6.3文件名格式)

 

一些DUL INTERNALS

要求信息

 

要从数据库块中导出表数据必须了解以下信息:

  1. 列/cluster簇信息:列的数量和类型。CHAR或VARCHAR列的最大长度。集群列数以及集群中的表数量。这个信息可以由unload语句提供,或者从先前导出的USER $,OBJ $,TAB $和COL $中获得。
  2. 段/区段的信息:当导出表时,在数据段头块的extent表被用于定位所有数据块。这个数据段头块(文件号和块号)的位置来自数据字典或可在unload语句中指定。如果该段头不正确/不可用,那必须用另一种方法。 DUL能通过扫描整个数据库创建自己的区段地图。 (以DUL与扫描数据库语句单独运行。)

二进制HEADER

 

在块头的C-Structs不被直接复制,它们通过特殊功能被检索。结构成员的所有补偿都被编入DUL。这种方法使跨平台导出实现。(在HP导出MVS创建的数据文件)除了字节顺序,至今只有四个布局类型被发现。

  1. VAX VMS和Netware:在结构成员之间无对齐填充。
  2. 韩国Ticom Unix机器:结构成员的16位对齐。
  3. MS / DOS :16位对齐和16位字长。
  4. 世界上其他(包括Alpha VMS):结构成员对成员大小对齐。

 

机器依赖

 

(数据库)机器依赖用以下参数配置:

  • 一个word(大/小端)的字节顺序
  • 在DBA(块地址)FILE#的低部分的位数量
  • 在一个C-Struct中的成员对齐
  • oracle文件头块之前的块或字节数
  • 在段头结构中使用的词的大小

导出数据字典

 

如果数据字典的文件存在且未破坏,DUL可以导出数据库的数据字典。对于要使用的数据字典,内部表必须首先导出至外部文件:(USER $,OBJ $,TAB $和COL $)。bootstrap命令将找到并导出所需的表。

 

DDLDUL描述语言)规范

 

DDL(DUL描述语言)规范

DDL(DUL描述语言)规范
[ ALTER SESSION ] SET init.dul parameter =  value ;
     Most parameters can be changed on the fly. 

BOOTSTRAP [LOCATE | GENERATE | COMPLETE
           | UNLOAD   Bootstrap$ segment header block address ];
     Bootstraps the data dictionary. Default is COMPLETE.
     LOCATE finds and unloads the bootstrap$ table.
     GENERATE builds a ddl file based on inforation in the cache.
     COMPLETE is in fact LOCATE, followed by GENERATE (two times)

COMMIT;
     Writes the changed block to the data file.

CREATE BLOCK INDEX  index_name  ON  device ;


dul block index

 

块索引包含在损坏的文件系统中找到的有效Oracle块的地址。用于合并多个磁盘映像或从损坏的文件系统导出。只有在极端的文件系统损坏情况下很有用。

 

DESCRIBE  owner_name  . table_name ;

DUMP [ TABLESPACE  tablespace_no ]
     [ FILE  file_no  ]
     [ BLOCK  block_no  ]
     [ LEVEL  level_no  ] ;
     Not a complete blockdump, mainly used for debugging.
     The block address is remembered.

EXTRACT  asm file name  to  output file name  ;
     Copies any ASM file from a disk group to the file system.
     (there was a problem with online redologs this needs more testing)

MERGE block_index INTO [  segment  ];

 

合并命令使用索引文件中的信息来定位文件数和对象id的组合的可能数据块,每个候选块与在数据文件中的当前块进行比较。如果当前块是坏的,或有一个更旧的scn,候选人将被写入数据文件。这只有在极端的文件系统损坏情况下很有用。

 

 

REM any_text_you_like_till_End_Of_Line : comment
REM  NOT allowed inside ddl statements. ( To avoid a two layer lexical scan).

ROLLBACK; # Cancels the UPDATE statements.

SHOW     DBA  dba ;                # dba -> file_no block_no calculator
	   | DBA  rfile_no block_no ;  # file_no block_no -> dba calculator
	   | SIZES ;                   # show some size of important structs
	   | PARAMETER;                # shows the values of all parameters
	   | LOBINFO;                  # lob indexes found with SCAN DATABASE
       | DATAFILES;                # summary of configured datafiles
       | ASM DISKS;                # summary of configured asm disks
       | ASM FILES;                # summary of configured datafiles on asm
       | ASM FILE  cid      # extent information for asm file

UNEXP [TABLE] [  owner  . ]  table name 
       (  column list  ) [ DIRECT ]
       DUMP FILE  dump file name
       FROM  begin offset  [ UNTIL  end offset  ]
       [ MINIMUM  minimal number of columns  COLUMNS ] ;

       To unload data from a corrupted exp dump file. No special setup
       or configuration is required, just the compatible parameter.
       The start offset should be where a row actually begins.

UNPUMP
       To unload data from a corrupted expdp (datapump) dump file.
       This is still work in progress, the basic commands work
       but rather complex to use. Contact me if this is needed.

UNLOAD DATABASE;

UNLOAD USER user_name;

UNLOAD [TABLE]  [  schema_name . ]  table_name 
              [ PARTITION(  partition_name ) ]
              [ SUBPARTITION(  sub_partition_name ) ]
              [ (  column_definitions ) ]
              [  cluster_clause  ]
              [  storage_clause  ] ;

UNLOAD EXTENT  table_name 
              [ (  column_definitions  ) ]
              [ TABLESPACE  tablespace_no  ] 
              FILE  extent_start_file_number
              BLOCK extent_start_block_number 
              BLOCKS  extent_size_in oracle_blocks ;

UNLOAD LOB SEGMENT FOR [  schema_name . ]  table_name   [ (  column name  ) ] ;

UNLOAD LOB SEGMENT STORAGE ( SEGOBJNO data obj#) ;

UPDATE [ block_address ] SET UB1|UB2|UB4 @ offset_in_block = new_value ;
UPDATE [ block_address ] SET  block element name  = new_value ;
        Now and then we can repair something.
	Patches the current block and dumps it.
	You can issue multiple UPDATE commands.
	Block is not written yet, use COMMIT to write. 

storage_clause ::=
        STORAGE ( storage_specification  [ more_storage_specs ] )

storage_specification ::=
        OBJNO object_id_number 
|       TABNO cluster_table_number
|       SEGOBJNO cluster/data_object_number       /* v7/v8 style data block id */
|       FILE  data_segment_header_file_number     /* v6 style data block id */
        BLOCK data_segment_header_block_number )   
|       any_normal_storage_specification_but_silently_ignored

SCAN DATABASE;

 

扫描所有数据文件的所有块。生产了两个或三个文件:

  1. 找到的段头(索引/集群/表)的dat信息:(对象ID,文件号和块号)。
  2. 连续的表/群集的数据块的dat信息。 (对象ID(V7),段头的文件和块号(V6),文件好和第一个块的块号,块号,表号)
  3. 每个lob数据块的dat信息,这个文件(可选的,仅在init.dul:SCAN_DATABASE_SCANS_LOB_SEGMENTS= TRUE)可能很大。同时,需要的内存大小可能会产生问题。目的有两个:1:在导出表时可能解决损坏的lob索引。 2:导出lob段(已删除的lob或无lob索引或父表的lob段)。SCANNEDLOBPAGE.dat字段的含义:(segobj#,lobid,fat_page_no,version( wrap, base),ts#,file#,block#)

 

 

 

dul_intro1

 

 

DDLDUL描述语言)概述

UNLOAD EXTENTUNLOAD TABLE规则:

extent map 盘区地图

UNLOAD TABLE要求一个extent map 盘区地图。在99.99%的情况下,段头中的extent map 盘区地图是可用的。在罕见的0.01%的情况下,段头丢失。可以用扫描数据库命令创建一个区段地图。仅当参数USE_SCANNED_EXTENT_MAP被设为TRUE时,自生成的区段地图才在导出时被使用。

所有数据块有它们所属一些段的ID。但是V6和V7之间有根本区别。由Oracle版本6创建的数据块有段头块的地址。由Oracle7创建的数据块在header中有段对象ID。

 

列定义

列定义必须指定列存储在段中的顺序,由col$.segcol#指定。这不需要与在CREATE TABLE语句中指定的列顺序相同。集群列被移到前面,long移到后面。使用ALTER TABLE命令添加到表的列总是最后保存。

导出单个区段

UNLOAD EXTENT可用于导出一个或多个相邻块。要导出的区段必须用STROEAGE子句指定:要指定单个区段,使用:STORAGE(EXTENTS(FILE fno BLOCK bno BLOCKS #blocks))(FILE和BLOCK指定第一个块,BLOCKS 区段大小)

DUL具体列类型

有两个额外的DUL特定数据类型:

  1. IGNORE:该列将被跳过,就好像它不存在。
  2. UNKNOWN:每列会有一个启发式猜测将。

在SQL * Loader模式还有更多DUL特定的数据类型:

  1. HEXRAW:列是十六进制转储。
  2. LOBINFO:显示LOB定位的一些信息。
  3. BINARY NUMBER:作为在LOB索引中使用的机器字。

 

识别USER $OBJ $$ TABCOL $

DUL使用与RDBMS相同的bootstrap程序。即它使用系统数据文件头的root dba来定位bootstrap$ 表。由于版本不同,root dba可能是包含bootstrap$地址的兼容段位置,或较新版本中是bootstrap$表本身的地址。bootstrap$表被导出,它的内容是分析以查找头四表(USER $,OBJ $,$ TAB和COL $)。其他表是基于头四个表的信息被导出的。

 

SCAN 扫描命令概述

SCAN TABLES和SCAN EXTENTS扫描相同的信息,并生成类似的输出。所有行的所有列都被检查。每一列收集以下统计数据:

  • 行在数据块中多久显示一次。
  • 最大的内部列长度。
  • 列是NULL的情况有多久。
  • 列由至少75%可打印的ASCII组成的情况有多久。
  • 列由100%可打印的ASCII组成的情况有多久。
  • 列是一个有效的Oracle数字的情况有多久。
  • 列是一个不错的数字的情况有多久。 (没有多少以零开头或结尾 )
  • 列是一个有效的日期的情况有多久。
  • 列是一个可能有效的rowid的情况有多久。

这些统计被组合并有一种列类型被提出。使用该建议,五行被导出显示结果。这些统计数据转储到两个文件(seen_tab.dat和seen_col.dat)。有SQL * Loader和SQL * Plus脚本可以自动完成识别过程的一部分。 (目前称为getlost选项)。

 

描述describe

有一个描述命令。它会显示表格的字典信息,在DUL的字典缓存中可用。

DUL启动顺序

在启动过程中,DUL经过以下步骤:

  • 参数文件“dul”被处理。
  • DUL控制文件(默认“dul”)被扫描。
  • 尝试加载USER$,OBJ$,TAB $和COL $的转储,如果有则放到DUL的数据字典缓存中。
  • 尝试加载dat和col.dat。
  • 接受DDL语句或运行指定为第一个参数的DDL脚本。

 

init.dul中可以指定的DUL 参数

ALLOW_TRAILER_MISMATCH

BOOLEAN

强烈不推荐使用,不太会产生更多的行。只有在你完全理解它的意思及为什么想使用时才能使用。跳过正确块trailer的检查。在这个测试中失败的块是损坏的碎片。但这样省去了修补一些块的麻烦。

 

ALLOW_DBA_MISMATCH

BOOLEAN

强烈不推荐使用,不太会产生更多的行。只有在你完全理解它的意思及为什么想使用时才能使用。跳过正确块trailer的检查。在这个测试中失败的块是损坏的碎片。但这样省去了修补一些块的麻烦。

 

ALLOW_OTHER_OBJNO

BOOLEAN

如果你的字典比你的数据文件更旧,那数据对象ID对于被truncate的表有所不同。当这个参数设为true,它会发出警告,但使用来自段头的值。所有其他块都被全面检查。这只用于特例。

ASCII2EBCDIC

BOOLEAN

(var)char字段必须从 EBCDIC 转译为 ASCII。(针对在ASCII主机上导出MVS数据库)

BUFFER

NUMBER (字节)

在导出和SQL * Loader模式中使用的行输出缓冲区(buffer)的大小。它在各行被首先存储到该缓冲区中。只有没有错误的完整行被写入输出文件。

COMPATIBLE

NUMBER

数据库版本,有效值为 6,7,8或9。必须指定参数。

 

CONTROL_FILE

TEXT

DUL 控制文件名(默认:“ control.dul”)。

 

DB_BLOCK_SIZE

NUMBER

字节表示的Oracle块大小(最大32K)

 

DC_COLUMNS

NUMBER

DC_OBJECTS

NUMBER

DC_TABLES

NUMBER

DC_USERS

NUMBER

dul 字典缓存大小。如果其中一个过低,高速缓存将被自动调整大小。

 

EXPORT_MODE

BOOLEAN

使用export 导出模式或 SQL*Loader 模式。

 

FILE

TEXT

(转储或数据)文件名生成的基础。对类似8.3 DOS的文件系统使用。

 

FILE_SIZE_IN_MB

NUMBER (Megabytes)

最大转储文件大小。转储文件被分割成多个部分。每个文件都有一个完整的头,并能被单独加载。

 

LDR_ENCLOSE_CHAR

TEXT

括起SQL* Loader模式中字段的字符。The character to enclose fields in SQL*Loader mode.

LDR_PHYS_REC_SIZE

NUMBER

生成的loader数据文件的物理记录大小。

 

LDR_PHYS_REC_SIZE = 0没有固定的记录,每个记录以一个换行符结束。

LDR_PHYS_REC_SIZE > 2: 固定的记录大小。

MAX_OPEN_FILES

在操作系统级别同时保持开放的数据文件最大#。

 

OSD_BIG_ENDIAN_FLAG

机器语言表示的字节顺序。Big Endian一开始也被称为MSB。 DUL根据在运行的机器设置默认值。要了解这个被称为Big Endian的原因,阅读格列佛游记。

 

 

OSD_DBA_FILE_BITS
以位表示的DBA中文件编号大小。更精确的说是文件号的低序部分的大小。
OSD_FILE_LEADER_SIZE
bytes/blocks added before the real oracle file header block
OSD_C_STRUCT_ALIGNMENT
C 结构成员对齐(0,16或23)。默认的32对于大多数端口是正确的。 
OSD_WORD_SIZE
一个机器字的大小总是32,除了MS/DOS(16)
PARSE_HEX_ESCAPES
Boolean 默认 FALSE
在解析时,在字符串使用\\xhh十六进制转义序列。如果设置为true,则奇怪的字符可以使用转义序列来指定。该功能也用于指定多字节字符。
USE_SCANNED_EXTENT_MAP
BOOLEAN
导出表时,在ext.dat使用扫描好的区段地图。一般算法在段头使用区段地图。只有当一些段头丢失或不正确时,该参数才有用。
WARN_RECREATE_FILES
BOOLEAN (TRUE)
如果现有的文件被覆盖,设置为FALSE以取消警告信息。
WRITABLE_DATAFILES
BOOLEAN (FALSE)
DUL的一般使用只会读取数据库文件。然而,UPDATE和 SCAN RAW DEVICE也将写出。参数能防止有意外损坏。

示例 init.dul :
# sample init.dul configuration parameters
# these must be big enough for the database in question
# the cache must hold all entries from the dollar tables.
dc_columns = 200000
dc_tables = 10000
dc_objects = 10000
dc_users = 40

# OS specific parameters
osd_big_endian_flag = false
osd_dba_file_bits = 10
osd_c_struct_alignment = 32
osd_file_leader_size = 1

# database parameters
db_block_size = 8k

# loader format definitions
LDR_ENCLOSE_CHAR = "
LDR_PHYS_REC_SIZE = 81
配置端口相关的参数
从RDBMS版本10G 开始,osd参数易于配置。通常,所有参数都可以使用其默认值。唯一可能要注意的是osd_big_endian_flag,在原始数据库平台与当前机器不同的情况下进行跨平台导出时,如果osd_big_endian_flag设置不正确,当检查文件头时让会在启动时被检测。
已知参数集合
对于预10G数据库没有在OSD的OSD维基页面列表(操作系统Dependend),几乎每一个平台参数已知参数列表。如果你的平台不在列表中,你可以使用下面的建议确定的参数。 (然后请通知我,让我可以将它们添加到列表中。)
osd_big_endian_flag
big endian 或 little endian(机器表示的字节顺序):HP,SUN和大型机通常big endian:OSD_BIG_ENDIAN_FLAG = TRUE。 DEC和Intel平台是little endian :OSD_BIG_ENDIAN_FLAG = FALSE。默认值对DUL正在运行的平台是正确的。
这点没有标准的技巧,以下在UNIX系统上可能有效:
  echo dul | od -x
  If the output is like:
     0000000 6475 6c0a
     0000004
  You are on a big endian machine (OSD_BIG_ENDIAN_FLAG=TRUE).

  If you see:
     0000000 7564 0a6c
     0000004
  This is a little endian machine (OSD_BIG_ENDIAN_FLAG=FALSE).
osd_dba_file_bits
在DBA用于文件号的低位部分的位数量。执行以下查询:
SQL> select dump(chartorowid('0.0.1')) from dual;
  
  Typ=69 Len=6: 8,0,0,0,0,0    ->       osd_dba_file_bits =  5 (SCO)
  Typ=69 Len=6: 4,0,0,0,0,0    ->       osd_dba_file_bits =  6 (Sequent , HP)
  Typ=69 Len=6: 1,0,0,0,0,0    ->       osd_dba_file_bits =  8 (NCR,AIX)
  Typ=69 Len=6: 0,16,0,0,0,0   ->       osd_dba_file_bits = 12 (MVS)
  Typ=69 Len=10: 0,0,0,0,0,64,0,0,0,0      osd_dba_file_bits = 10 (Oracle8)      
OSD_C_STRUCT_ALIGNMENT
在数据文件头的结构布局。 0:在C-结构(VAX/ VMS只)成员之间没有填充16:一些韩国ticom机和MS/ DOS32:结构成员按成员大小排列。 (所有其他包括ALPHA / VMS)检查以下查询:
SELECT * FROM v$type_size
WHERE type IN ( 'KCBH', 'KTNO', 'KCBH', 'KTBBH', 'KTBIT', 'KDBH'
              , 'KTECT', 'KTETB', 'KTSHC') ;
一般情况下,会出现osd_c_struct_alignment=32以及如下:
K        KTNO     TABLE NUMBER IN CLUSTER                   1
KCB      KCBH     BLOCK COMMON HEADER                      20
KTB      KTBIT    TRANSACTION VARIABLE HEADER              24
KTB      KTBBH    TRANSACTION FIXED HEADER                 48
KDB      KDBH     DATA HEADER                              14
KTE      KTECT    EXTENT CONTROL                           44
KTE      KTETB    EXTENT TABLE                              8
KTS      KTSHC    SEGMENT HEADER                            8

8 rows selected.
仅对于VAX/ VMS和Netware,会出现 osd_c_struct_alignment=0和如下输出:
COMPONEN TYPE     DESCRIPTION                      SIZE
-------- -------- -------------------------------- ----------
K        KTNO     TABLE NUMBER IN CLUSTER                   1
KCB      KCBH     BLOCK COMMON HEADER                      20
KTB      KTBIT    TRANSACTION VARIABLE HEADER              23
KTB      KTBBH    TRANSACTION FIXED HEADER                 42
KDB      KDBH     DATA HEADER                              14
KTE      KTECT    EXTENT CONTROL                           39
KTE      KTETB    EXTENT TABLE                              8
KTS      KTSHC    SEGMENT HEADER                            7

8 rows selected.
如果有一个不同的列表,这将需要一些主要黑客攻击和嗅探,还可能对DUL有重大变化。 (电子邮件Bernard.van.Duijnen@oracle.com)
osd_file_leader_size

在Oracle文件头前的块/字节数。 Unix数据文件有一个额外的领先块(文件大小,块大小幻数)大量(>100)被看作是一个字节偏移,少数被看作Oracle块号。
Unix    :       osd_file_leader_size = 1
Vms     :       osd_file_leader_size = 0
Desktop :       osd_file_leader_size = 1 (or 512 for old personal oracle)
Others  :       Unknown ( Use Andre Bakker's famous PATCH utility to find out)
                An Oracle7 file header block starts with the pattern 0X0B010000.
你可以在可选的第三个字段的control.dul中添加一个额外的字节偏移(如在原始设备的AIX或DEC UNIX数据文件)
控制文件语法规范
控制文件(默认名称“control.dul”)用于指定ASM磁盘,块索引和数据文件名。control的格式已被扩展。
目前在DUL控制文件中有三种类型的定义。在单独的行中的每个条目。 ASM磁盘必须先于ASM文件。
control_file_line ::= asm_disk_spec | file_piece_spec | block_index_spec
如果兼容是10或更高,你也可以指定ASM磁盘。它对于指定设备名称通常足够。所有属性都是由头部检查自动检索。当头部检查是不可能时才需要完整的语法,用于有损坏的header的磁盘。语法是:
DISK  device name [  disk group options  ]

 disk group option  ::= GROUP  disk group name 
                           | DISK_NO  disk number in group 
                           | F1B1  File1 Block1 location
块索引是在损坏的文件系统上访问Oracle块的一种方式。通常,损坏的文件系统没有被全部删除,它不是空的。由于oracle块的特定布局,it is possible to datablocks an store their location in the block index。参见 create block index命令。block_index_name是一个普通的标识符,它用于构建一个唯一的文件名。
BLOCK INDEX  block_index_name
每个条目可以包含一个数据文件的一部分。最小单位是一个数据块。这种的话,将对于DUL太大的数据文件分割成多个部分是可能的,其中每个部分小于2GB。
一般指定的文件名就足够了。即使对于单个块。如果compatible为10或更高,文件号和表空间号将从文件头读取。
如果指定的细节与文件头不同,DUL会给出一个警告,但使用你的定义。这是为了能在文件损坏的头块导出文件。对于调试,可以转储文件头。
可选的额外leader 偏移是一个额外的字节偏移,它将被添加到该数据文件的所有lseek() 操作。这样就可以跳过一些AIX原始设备额外的4K块,或在原始设备上Tru64d 额外的64K。
file_piece_spec ::= 
         [ [ tablespace_no ] relative_file_number]data_file_name
         [ optional extra leader offset ]
         [ startblock block_no ]
         [ endblock block_no ]
示例
# AIX version 7 example with one file on raw device
   1 /usr/oracle/dbs/system.dbf
   8 /dev/rdsk/data.dbf 4096

# Oracle8 example with a datafile split in multiple parts, each part smaller than 2GB
   0  1 /fs1/oradata/PMS/system.dbf
   1  2 /tmp/huge_file_part1 startblock 1 endblock 1000000
   1  2 /tmp/huge_file_part2 startblock 1000001 endblock 2000000
   1  2 /mnt3/huge_file_part3 startblock 2000001 endblock 2550000

# ASM disks for two disk groups
disk /media/maxtor/asm/dgn1
disk /media/maxtor/asm/dgn2
disk /media/maxtor/asm/dgn3
disk /media/maxtor/asm/dgn4
disk /media/maxtor/asm/dgodd

# system datafile in the first asm disk group
+DGN/db102/datafile/system.257.621616979

# users datafile in a different disk group
+DGODD/db102/datafile/users.257.621616683

# a so called big file tablespace, use 1024 for the file#
 8 1024 /home/oracle/v102/dbs/bigfilets

# Or let DUL find out itself from the header
 /home/oracle/v102/dbs/bigfilets

# one tablespace with a different block size
/home/oracle/v102/dbs/ts16k.dbf block_size 16k

# or let DUL find out by header inspection
/home/oracle/v102/dbs/ts16k.dbf


Sample unload session: data dictionary usable for DUL

dul_bootstrap

示例导出会话:可用于DUL的数据字典
1.	创建一个合适的“init.dul”
2.	创建control.dul

sqlplus /nolog
    connect / as sysdba
    startup mount
    set trimspool on pagesize 0 linesize 256 feedback off
    column name format a200
    spool control.dul
    select ts#, rfile#, name from v$datafile;
    exit
edit the result

For Oracle8 a different query must be used:
   select ts#, rfile#, name from v$datafile;
1.	启动DUL和bootstrap;
$ dul

Data UnLoader 10.2.1.16 - Oracle Internal Only - on Thu Jun 28 11:37:24 2007
with 64-bit io functions

Copyright (c) 1994 2007 Bernard van Duijnen All rights reserved.

 Strictly Oracle Internal use Only
DUL> bootstrap;
Probing file = 1, block = 377
. unloading table                BOOTSTRAP$      57 rows unloaded
DUL: Warning: Dictionary cache DC_BOOTSTRAP is empty
Reading BOOTSTRAP.dat 57 entries loaded
Parsing Bootstrap$ contents
DUL: Warning: Recreating file "dict.ddl"
Generating dict.ddl for version 10
 OBJ$: segobjno 18, file 1
 TAB$: segobjno 2, tabno 1, file 1
 COL$: segobjno 2, tabno 5, file 1
 USER$: segobjno 10, tabno 1, file 1
Running generated file "@dict.ddl" to unload the dictionary tables
. unloading table                      OBJ$   52275 rows unloaded
. unloading table                      TAB$    1943 rows unloaded
. unloading table                      COL$   59310 rows unloaded
. unloading table                     USER$      70 rows unloaded
Reading USER.dat 70 entries loaded
Reading OBJ.dat
 52275 entries loaded and sorted 52275 entries
Reading TAB.dat 1943 entries loaded
Reading COL.dat 59310 entries loaded and sorted 59310 entries
Reading BOOTSTRAP.dat 57 entries loaded
...
Some more messages for all the other TABLES
...
Database character set is WE8ISO8859P1
Database national character set is AL16UTF16
DUL> unload user SCOTT;
About to unload SCOTT's tables ...
. unloading table                       EMP      14 rows unloaded
示例导出会话:无法用于DUL的数据字典
1.	创建一个合适的“init.dul”(见配置指南)
2.	创建control.dul见上文
3.	扫描数据库的段头和区段:
$ dul
UnLoader: Version 2.0.0.0 - Very Restricted on Tue May 16 11:10:16 1995
Copyright (c) 1994/95 Oracle Corporation, The Netherlands. All rights reserved.
DUL> scan database;
data file 1 20480 blocks scanned
data file 4 7680 blocks scanned
data file 5 512 blocks scanned
DUL>quit
1.	重启DUL和扫描找到的表获得列统计,这生成了大量输出:
echo scan tables \; | dul > scan.out&

[ many lines here]


Object id 1601 table number 0
UNLOAD TABLE T1601_0 ( C1 NUMBER, C2 UNKNOWN, C3 UNKNOWN, C4 NUMBER, C5 DATE
        , C6 NUMBER, C7 NUMBER, C8 NUMBER )
    STORAGE ( TABNO 0 EXTENTS( FILE 1 BLOCK 10530));

Colno  Seen MaxIntSz Null% C75% C100 Num% NiNu% Dat% Rid%
    1    14        3    0%   0%   0% 100% 100%   0%   0%
    2    14        6    0% 100% 100% 100%  14%   0%  21%
    3    14        9    0% 100% 100% 100%  14%   0%   0%
    4    14        3    7%   0%   0% 100% 100%   0%   0%
    5    14        7    0%   0%   0%   0%   0% 100%   0%
    6    14        3    0%   0%   0% 100% 100%   0%   0%
    7    14        2   71%   0%   0% 100% 100%   0%   0%
    8    14        2    0%   0%   0% 100% 100%   0%   0%

"7369" "SMITH" "CLERK" "7902" "17-DEC-1980 AD 00:00:00" "800" "" "20"

"7499" "-0.000025253223" "SALESMAN" "7698" "20-FEB-1981 AD 00:00:00" "1600" "30+

0" "30"

"7521" "WARD" "SALESMAN" "7698" "22-FEB-1981 AD 00:00:00" "1250" "500" "30"

"7566" "JONES" "MANAGER" "7839" "02-APR-1981 AD 00:00:00" "2975" "" "20"

"7654" "MARTIN" "SALESMAN" "7698" "28-SEP-1981 AD 00:00:00" "1250" "1400" "30"

[ many more lines here ]
这看着很眼熟,使用以上信息和你对emp表的了解来编写:
UNLOAD TABLE emp ( empno number, ename char, job char, mgr number,
        hiredate date, sal number, comm number deptno number)
 STORAGE ( TABNO 0 EXTENTS( FILE 1 BLOCK 10530));
1.	用这个语句来导出emp:
$ dul
UnLoader: Version 2.0.0.0 - Very Restricted on Tue May 16 11:46:33 1995
Copyright (c) 1994/95 Oracle Corporation, The Netherlands. All rights reserved.
Loaded 350 segments
Loaded 204 extents
Extent map sorted
DUL> UNLOAD TABLE emp ( empno number, ename char, job char, mgr number,
DUL 2> hiredate date, sal number, comm number deptno number)
DUL 3> STORAGE ( TABNO 0 EXTENTS( FILE 1 BLOCK 10530));
. unloading table                       EMP      14 rows unloaded
DUL>quit
 
示例导出会话:不正确的init.dul参数
错误的 osd_dba_file_bits大小
这会产生类似如下的输出。通常这不应该发生,因为你应该创建一个演示数据库,并通过DUL记录的(HTML页)查询进行检查。
在DBA的失配是只在文件号(括号内第一号)的部分。第二个数字,块号,是正确的。
Data UnLoader: Release 3.2.0.1 - Internal Use Only - on Wed Sep 3 10:40:33 1997
Copyright (c) 1994/95 Oracle Corporation, The Netherlands. All rights reserved.
Session altered.
Session altered.
Session altered.
Session altered.
Session altered.
DUL: Warning: Block[1][2] DBA in block mismatch [4][2]
DUL: Warning: Bad cache layer header file#=1, block#=2

DUL: Warning: Block[1][3] DBA in block mismatch [4][3]
DUL: Warning: Bad cache layer header file#=1, block#=3

...........and etc..........
错误的osd_file_leader_size
这可能会造成类似如下的输出,但还有许多其他可能。在这里,我们block off号是固定的。In this case we are a fixed number of blocks off.文件号是正确的。块号的差是恒定的:
Data UnLoader: Release 3.2.0.1 - Internal Use Only - on Wed Sep 3 10:44:23 1997
Copyright (c) 1994/95 Oracle Corporation, The Netherlands. All rights reserved.
Session altered.
Session altered.
Session altered.
Session altered.
Session altered.

DUL: Warning: Block[1][2] DBA in block mismatch [1][3]
DUL: Warning: Bad cache layer header file#=1, block#=2

DUL: Warning: Block[1][3] DBA in block mismatch [1][4]
DUL: Warning: Bad cache layer header file#=1, block#=3

...........and etc..........
错误的osd_c_struct_alignment
这可能会产生类似如下的输出:
Data UnLoader: Release 3.2.0.1 - Internal Use Only - on Wed Sep 3 10:46:10 1997
Copyright (c) 1994/95 Oracle Corporation, The Netherlands. All rights reserved.
Session altered.
Session altered.
Session altered.
Session altered.
Session altered.
. unloading table OBJ$

DUL: Warning: file# 0 is out of range
DUL: Warning: Cannot read data block file#=0, block# = 262145
OS error 2: No such file or directory

DUL: Warning: file# 0 is out of range
DUL: Warning: Cannot read data block file#=0, block# = 262146
OS error 2: No such file or directory

...........and etc..........
错误的db_block_size
当DB_BLOCK_SIZE设置过小时会生成以下输出。正确值是4096,而设置为2048。通常情况下,此参数值应取自Oracle实例的init.ora文件中,且将无法正确设置。
Data UnLoader: Release 3.2.0.1 - Internal Use Only - on Thu Sep 4 12:38:25 1997
Copyright (c) 1994/95 Oracle Corporation, The Netherlands. All rights reserved.
Session altered.
Session altered.
Session altered.
Session altered.
Session altered.
DUL: Warning: Block[1][2] DBA in block mismatch [513][1159680]
DUL: Warning: File=1, block 2: illegal block version 2
DUL: Warning: Block[1][2] Illegal block type[0]
DUL: Warning: Bad cache layer header file#=1, block#=2

DUL: Warning: Block[1][4] DBA in block mismatch [1][2]
DUL: Warning: File[1]Block[4]INCSEQ mismatch[90268!=0]
DUL: Warning: Bad cache layer header file#=1, block#=4

DUL: Warning: Block[1][6] DBA in block mismatch [1][3]
DUL: Warning: File[1]Block[6]INCSEQ mismatch[139591710!=86360346]
DUL: Warning: Bad cache layer header file#=1, block#=6

...........and etc..........
QUOTE MISSING
如果收到以下错误,这是由于数据字典表“USER$,OBJ$,$ TAB和COL$”没有被正确生成。要解决这个错误,简单地删除所有dictv6.ddl或dictv7.ddl创建.dat和.ctl文件并重启。
Data UnLoader: Release 3.2.0.1 - Internal Use Only - on Wed Sep 3 10:49:30 1997

Data UnLoader: Release 3.2.0.1 - Internal Use Only - on Wed Sep 3 10:49:30 1997

DUL: Error: Quote missing
从损坏的EXP转储文件拯救数据 - UNEXP教程
如果你不了解EXP转储文件的结构任何东西,这就比较困难。以下是一个快速解释。除了文件头,转储文件有识别各个部分的标记。在每个表部分会有SQL语句。最有趣的部分是create table语句,接下来是插入到表语句。插入语句后直接跟绑定信息,(列数,以及每列的类型和绑定长度和其他一些信息)。接下来是实际的列。每列之前是两个字节长度,后跟实际列数据。可能较长的列有几个技巧。列数据的结尾以特殊的长度标记OXFFFF标志。行的开头没有标记。损坏后的重新同步是试错。损坏一般不能立即检测到。该格式与DIRECT导出略有不同,所以你必须要对DIRECT导出使用DIRECT选项。指定的偏移量是一个行的开头。一般来说第一个直接在绑定数组之后,但为最佳的灵活性,你可以从行数据的任何地方开始。
第一步是扫描转储文件来找到偏移和SQL语句。每个输出行从项目被找到的偏移位置开始。


DUL>  scan dump file expdat.dmp;
0: CSET: 1 (US7ASCII)                # Character set info from the header
3: SEAL EXPORT:V10.02.01             # the Seal - the exp version tag
20: DBA SYSTEM                       # exp done as SYSTEM
8461: CONNECT SCOTT                  # section for user SCOTT
8475: TABLE "EMP"
                                     # complete create table staement
8487: CREATE TABLE "EMP" ("EMPNO" NUMBER(4, 0), "ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9), "MGR" NUMBER(4, 0), "HIREDATE" DATE, "SAL" NUMBER(7, 2),
"COMM" NUMBER(7, 2), "DEPTNO" NUMBER(2, 0))  PCTFREE 10 PCTUSED 40
INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1
FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING NOCOMPRESS

                                     # Insert statement
8829: INSERT INTO "EMP" ("EMPNO", "ENAME", "JOB", "MGR", "HIREDATE",
"SAL", "COMM", "DEPTNO") VALUES (:1, :2, :3, :4, :5, :6, :7, :8)

                                     # BIND information
8957: BIND information for 8 columns
 col[  1] type 2 max length 22
 col[  2] type 1 max length 10 cset 31 (WE8ISO8859P1) form 1
 col[  3] type 1 max length 9 cset 31 (WE8ISO8859P1) form 1
 col[  4] type 2 max length 22
 col[  5] type 12 max length 7
 col[  6] type 2 max length 22
 col[  7] type 2 max length 22
 col[  8] type 2 max length 22
Conventional export                  # Conventional means NOT DIRECT

9003: start of table data            # Here begins the first row
现在从create table语句和直接/常规信息和列数据的开头创建unexp语句。
UNEXP TABLE "EMP" ("EMPNO" NUMBER(4, 0), "ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9), "MGR" NUMBER(4, 0), "HIREDATE" DATE, "SAL" NUMBER(7, 2),
"COMM" NUMBER(7, 2), "DEPTNO" NUMBER(2, 0))
dump file expdat.dmp from 9003;

Unloaded 14 rows, end of table marker at 9670 # so we have our famous 14 rows
这将创建普通SQL * Loader文件和匹配的控制文件。在输出文件中一个额外的列被添加,这是与行的状态有关。 AP表示行是部分的,(缺失一些列)R指重新同步,这是一个再同步之后的第一行。 O表示重叠,之前的一行有错误,但新行与另一行部分重叠。






目录
~~~~~~~~~~~~~~~~~
1.简介
2.使用DUL
2.1创建一个适当的init.dul文件
2.2创建control.dul文件
2.3导出对象信息
2.4调用DUL
2.5重建数据库
3.如何重建存储在数据字典的对象定义?
4当段头块被损坏时,如何导出数据?
5. 当文件头块被损坏时,如何导出数据?
6.如何导出数据,而无需系统表空间?
7.附录A:哪里可以找到可执行文件?
8.参考
1.简介
~~~~~~~~~~~~~~~
本文档解释了如何使用DUL,而不是对Bernard的数据导出能力的完整解释。
本文件仅供内部使用,不应在任何时候给予客户,DUL应始终被分析师使用或在分析师监督下使用。
DUL(数据导出)尝试从Oracle数据库中检索无法检索的数据。这不是导出工具或
SQL * Loader的替代选择。该数据库可能被破坏,但一个单独的数据块必须是100%正确的。在所有导出时,块会被检查,以确保块没有损坏且属于正确的段。如果一个损坏的块被DUL检测到,错误信息会被打印到loader文件,并输出到标准输出,但是这不会终止下一行或块的导出。

2.使用DUL
~~~~~~~~~~~~
首先,你必须获得存在于数据块的对象所需的信息,这些统计将被加载到DUL字典以导出数据库对象。
这个信息是从在数据库创建时被创建的USER $,OBJ $,$ TAB和COL $表中检索的
,它们可以基于这一事实:由于SQL,BSQ的刚性性质,对象号在这些表是固定的而被导出。 DUL可以在系统的系统表空间中找到信息,因此,如果(多个)数据文件不存在,(多个)表数据文件必须包含在控制文件中,参见第6章。
2.1创建相应的“init.dul”文件
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
REM平台指定参数(NT)
REM能获取的最常见平台的一列参数。
osd_big_endian_flag=false
osd_dba_file_bits=10
osd_c_struct_alignment=32
osd_file_leader_size=1
osd_word_size = 32
REM DUL字典缓存的大小。如果其中某一个过低,启动将失败。
dc_columns=2000000
dc_tables=10000
dc_objects=1000000
dc_users=400
dc_segments=100000
控制文件的位置和文件名,默认值是control.dul
在当前目录control_file = D:\Dul\control_orcl.dul

数据库块大小,可以在init.ora中的文件中找到,或在服务器管理器中执行“show parameter %db_block_size%” 被检索到
(svrmgr23/ 30 /l)将该参数更改为损坏数据块的块大小。
db_block_size=4096。

当数据需要导出/导入格式,它可以/必须被指定。
 这将创建Oracle导入工具适用的文件,虽然生成的文件与由EXP工具生成的表模式导出完全不同。
它是有一个创建表结构语句和表数据的单个表转储文件。
grants,存储子句,触发器不包括在这个转储文件中!
export_mode=true

REM兼容参数可以被指定且可以是6,7或8
compatible=8

该参数是可选的并能在REM不支持的长文件名(e.g. 8.3 DOS)的平台,或当文件格式DUL使用 “owner_name.table_name.ext”不可接受时被指定。
在这里,转储文件会类似dump001.ext,dump002.ext,等。
file = dump

完整的列表可在HTML部分“DUL参数”获取,虽然这init.dul文件在大多数情况可行,且包含所有正确参数以成功完成导出。
2.2 创建“control.dul”文件
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
你需要对有关逻辑表空间和物理数据文件有一定了解,或你可以在数据库被加载时进行以下查询:
Oracle 6, 7
———–
> connect internal
> spool control.DUL
> select * from v$dbfile;
> spool off

Oracle 8
——–
> connect internal
> spool control.DUL
> select ts#, rfile#, name from v$datafile;
> spool off

如果需要的话,编辑spool文件和变化,数据文件的位置和stripe out不必要的信息,如表头,反馈行,等...
示例控制文件像这样:
Edit the spool file and change, if needed, the datafile location and stripe
out unnecessary information like table headers, feedback line, etc…
A sample control file looks something like this :
REM Oracle7 control file
1 D:\DUL\DATAFILE\SYS1ORCL.DBF
3 D:\DUL\DATAFILE\DAT1ORCL.DBF
7 D:\DUL\DATAFILE\USR1ORCL.DBF
REM Oracle8 control file
0 1 D:\DUL\DATAFILE\SYS1ORCL.DBF
1 2 D:\DUL\DATAFILE\USR1ORCL.DBF
1 3 D:\DUL\DATAFILE\USR2ORCL.DBF
2 4 D:\DUL\DATAFILE\DAT1ORCL.DBF

注:每个条目可以包含一个数据文件的一部分,当你需要拆分对于DUL太大的数据文件时,这就有用了,这样每个部分就小于比方说2GB了。 例如 :
REM Oracle8 其中一个数据文件被分割成多部分,每部分小于1GB! 
0 1 D:\DUL\DATAFILE\SYS1ORCL.DBF
1 2 D:\DUL\DATAFILE\USR1ORCL.DBF startblock 1 endblock 1000000
1 2 D:\DUL\DATAFILE\USR1ORCL.DBF startblock 1000001 endblock 2000000
1 2 D:\DUL\DATAFILE\USR1ORCL.DBF startblock 2000001 endblock 2550000
2.3 Unload the object information
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
以适当DDL(DUL描述语言)脚本启动BUL工具。由于数据库版本不同,有3个可用脚本来导出USER$,$ OBJ,TAB$和COL$表。
Oracle6 :> dul8.exe dictv6.ddl
Oracle7 :> dul8.exe dictv7.ddl
Oracle8 :> dul8.exe dictv8.ddl

Data UnLoader: Release 8.0.5.3.0 – Internal Use Only – on Tue Jun 22 22:19:
Copyright (c) 1994/1999 Bernard van Duijnen All rights reserved.

Parameter altered
Session altered.
Parameter altered
Session altered.
Parameter altered
Session altered.
Parameter altered
Session altered.
. unloading table OBJ$ 2271 rows unloaded
. unloading table TAB$ 245 rows unloaded
. unloading table COL$ 10489 rows unloaded
. unloading table USER$ 22 rows unloaded
. unloading table TABPART$ 0 rows unloaded
. unloading table IND$ 274 rows unloaded
. unloading table ICOL$ 514 rows unloaded
. unloading table LOB$ 13 rows unloaded

Life is DUL without it
这将 USER$, OBJ$, TAB$ and COl$ 数据字典表的数据导出到 SQL*Loader 文件,这不能被处理到导入格式的转储文件中。 , this can not be manipulated into dump files
of the import format. 参数 export_mode = false 被硬编码到ddl脚本且不能更改为值“true”,因为这会导致DUL产生错误而失败:

. unloading table OBJ$
DUL: Error: Column “DATAOBJ#” actual size(2) greater than length in column
definition(1)
………….etc……………

2.4 调用DUL
~~~~~~~~~~~~~~
在交互模式下启动DUL,你也可以准备一个包含所有ddl命令以导出数据库必要数据的脚本。我会在本文档中描述最常用的命令,但不是完整的可指定参数列表。完整的列表可以在 “DDL描述”部分找到。
DUL> unload database;
=> 这将导出整个数据库表(包括sys'tables)
DUL> unload user ;
=> 这将导出所有特定用户所拥有的表。
DUL> unload table ;
=> 这将卸载由用户名拥有的指定表
DUL> describe ;
=> 将表示表列以及指定用户所拥有的(多个)数据文件的指向。will represent the table columns with there relative pointers to the datafile(s) owned by the specified user.

DUL> scan database;
=>扫描所有数据文件的所有块。
生成两个文件:
1:找到的段头的seg.dat信息(索引/集群/表)
(对象ID,文件号和块号)。
2:连续的表/集群的数据块的ext.dat信息。
(对象ID(V7),文件和段头的块号(V6),文件号和第一个块的块号,块的数量,表数量)
DUL> scan tables;
=>使用seg.dat和ext.dat作为输入。
扫描所有数据段中的所有表(一个头块和至少匹配至少一个表的一个区段)。

2.5重建数据库
~~~~~~~~~~~~~~~~~~~~~~~~
创建新的数据库,并使用导入或SQL * Loader来恢复被DUL检索到的数据。需要注意的是,当你只导出表结构数据时,索引,grants,PL / SQL和触发器将不再在新的数据库中存在。为了获得与之前数据库的完全相同的副本,你需要重新运行表,索引,PL / SQL等的创建脚本。
如果你没有这些脚本,那么你将需要执行在文档第3部分描述的步骤。

3.如何重建存储在数据字典的对象定义
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~
要通过DUL重建PL / SQL(程序包,过程,函数或触发器),grants,索引,约束或存储子句(旧的表结构)是可以的,但就是有点棘手。您需要使用DUL导出相关的数据字典表,然后加载这些表到一个健康的数据库,一定要使用与SYS或(系统)不同的用户。加载损坏数据库的数据字典表到健康数据库字典可能也会破坏健康的数据库。

示例从损坏的数据库检索pl/sql packages / procedures /functions的详情:
1)按照在“使用DUL”一节中的步骤解释并导出数据字典表“source$”
2)创建一个新的用户登录到一个健康数据库,并指定所需的默认和临时表空间。
3)将连接,资源, imp_full_database授权给新用户。
4)导入/加载表“source$”到新创建的模式:
例如:imp80 userid=newuser/passw file=d:\dul\scott_emp.dmp
log=d:\dul\impemp.txt full=y
5)现在,您可以从表查询以在损坏的数据库中重建pl/sql packages / procedures /functions。在WebIv可以找到产生这样的PL / SQL创建脚本。
相同的步骤可以用于重建索引,约束,和存储参数,或者为相应的用户重新授权。请注意,你总是需要使用某种类型的脚本,可以重建对象并包括损坏的数据库版本的所有功能。例如:当损坏的数据库是7.3.4版本,你有几个位图索引,如果你会使用支持7.3.2版本或之前的脚本,那么你将无法成功重建位图索引!

4. 当段头块被损坏时,如何导出数据 
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~
当DUL不能以正常方式检索数据块信息,它可以扫描数据库来创建其自己的段/区段地图。要从数据文件导出数据,扫描数据库的过程是必要的。
(为了说明这个例子,我根据段头块复制一个空块)
1)创建一个适当的“init.dul”(见2.1)和“control.dul”(见2.2)的文件。
2)导出表。这将失败,并指出段头块有损坏:
DUL> unload table scott.emp;
. unloading table EMP
DUL: Warning: Block is never used, block type is zero
DUL: Error: While checking tablespace 6 file 10 block 2
DUL: Error: While processing block ts#=6, file#=10, block#=2
DUL: Error: Could not read/parse segment header
0 rows unloaded
3)运行扫描数据库命令:
DUL> scan database;
tablespace 0, data file 1: 10239 blocks scanned
tablespace 6, data file 10: 2559 blocks scanned
4)向DUL说明它应该使用自己的生成的区段地图,而不是段头信息。
DUL> alter session set use_scanned_extent_map = true;
Parameter altered
Session altered.
DUL> unload table scott.emp;
. unloading table EMP 14 rows unloaded

5. 当数据文件头块损坏时,如何导出数据 
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~
在你打开数据库时,数据文件头块的损坏总是会列出。这不像一个头段块的损坏(见第4点),其中的数据库可以成功打开,且当你进行表查询时,损坏会列出。DUL从这种情况中恢复没有问题,尽管有其他恢复这种情况的方法,如为数据文件头块打补丁。
你将收到如下错误:
ORACLE instance started.
Total System Global Area 11739136 bytes
Fixed Size 49152 bytes
Variable Size 7421952 bytes
Database Buffers 4194304 bytes
Redo Buffers 73728 bytes
Database mounted.
ORA-01122: database file 10 failed verification check
ORA-01110: data file 10: ‘D:\DATA\TRGT\DATAFILES\JUR1TRGT.DBF’
ORA-01251: Unknown File Header Version read for file number 10

6.如何卸载数据,而无需系统表空间
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
如果数据文件不能用于系统表空间,导出仍可继续,但对象信息无法从数据字典表USER $,OBJ $,$ TAB和COL $检索。所以所有者名称,表名和列名不会被加载到DUL字典。识别表会是一项艰巨的任务,且这里需要对RDBMS内部有很好的了解。
首先,你需要对你的应用和它的表有很好的了解。
列类型可以有DUL猜测,但表和列名都将丢失。
任何同一个数据库旧的系统表空间(可能是几周前的)可以是很大的帮助!
1)如上述的步骤1和2,创建 “init.dul”文件和“control.dul”文件。在这种情况下,控制文件将包含所有你想要恢复的数据文件,但不需要系统表空间的信息。
2)然后你调用DUL并输入以下命令:
DUL> scan database;
data file 6 1280 blocks scanned
这将创建区段和段地图。也许DUL命令解释程序也将被终止。
3)重新调用DUL命令解释器并执行以下操作:
Data UnLoader: Release 8.0.5.3.0 – Internal Use Only – on Tue Aug 03 13:33:
Copyright (c) 1994/1999 Oracle Corporation, The Netherlands. All rights res
Loaded 4 segments
Loaded 2 extents
Extent map sorted
DUL> alter session set use_scanned_extent_map = true;
DUL> scan tables; (or scan extents;)
Scanning tables with segment header
Oid 1078 fno 6 bno 2 table number 0
UNLOAD TABLE T_O1078 ( C1 NUMBER, C2 UNKNOWN, C3 UNKNOWN )
STORAGE ( TABNO 0 EXTENTS( FILE 6 BLOCK 2));
Colno Seen MaxIntSz Null% C75% C100 Num% NiNu% Dat% Rid%
1 4 2 0% 0% 0% 100% 100% 0% 0%
2 4 10 0% 100% 100% 100% 0% 0% 0%
3 4 8 0% 100% 100% 100% 0% 0% 50%
“10” “ACCOUNTING” “NEW YORK”
“20” “RESEARCH” “DALLAS”
“30” “SALES” “CHICAGO”
“40” “OPERATIONS” “BOSTON”
Oid 1080 fno 6 bno 12 table number 0
UNLOAD TABLE T_O1080 ( C1 NUMBER, C2 UNKNOWN, C3 UNKNOWN, C4 NUMBER,
C5 DATE, C6 NUMBER, C7 NUMBER, C8 NUMBER )
STORAGE ( TABNO 0 EXTENTS( FILE 6 BLOCK 12));
Colno Seen MaxIntSz Null% C75% C100 Num% NiNu% Dat% Rid%
1 14 3 0% 0% 0% 100% 100% 0% 0%
2 14 6 0% 100% 100% 100% 0% 0% 21%
3 14 9 0% 100% 100% 100% 0% 0% 0%
4 14 3 7% 0% 0% 100% 100% 0% 0%
5 14 7 0% 0% 0% 0% 0% 100% 0%
6 14 3 0% 0% 0% 100% 100% 0% 0%
7 14 2 71% 0% 0% 100% 100% 0% 0%
8 14 2 0% 0% 0% 100% 100% 0% 0%
“7369” “SMITH” “CLERK” “7902” “17-DEC-1980 AD 00:00:00″ “800” “” “20”
“7499” “ALLEN” “SALESMAN” “7698” “20-FEB-1981 AD 00:00:00″ “1600” “300” “30”
“7521” “WARD” “SALESMAN” “7698” “22-FEB-1981 AD 00:00:00″ “1250” “500” “30”
“7566” “JONES” “MANAGER” “7839” “02-APR-1981 AD 00:00:00″ “2975” “” “20”
“7654” “MARTIN” “SALESMAN” “7698” “28-SEP-1981 AD 00:00:00″ “1250” “1400” “30”
Note : it might be best that you redirect the output to a logfile since
commands like the “scan tables” can produce a lot of output.
On Windows NT you can do the following command :
C:\> dul8 > c:\temp\scan_tables.txt
scan tables;
exit;
4)从步骤3的输出中找到丢失的表;如果你仔细看上面的输出会发现,unload语法已经给出,但表的名称将是格式T_0 ,列名称将是格式的C ;数据类型不会精确匹配之前的数据类型。
特别查找像“Oid 1078 fno 6 bno 2 table number 0”的字符串,其中:
oid = object id, will be used to unload the object 对象id,会被用于导出对象
fno = (data)file number (数据)文件号
bno = block number 块号
5)使用“unload table”命令导出找出的表:
DUL> unload table dept (deptno number(2), dname varchar2(14),
loc varchar2(13)) storage (OBJNO 1078)
Unloading extent(s) of table DEPT 4 rows.







评论:
DUL盘头副本
盘头副本
盘头副本
最近有ASM磁盘头的额外副本。通过使用kfed与修复选项,此副本可以用来修复真正的头。
位置
此副本被存储为PST的最后一个块。这意味着它在分配单元1的最后一个块(原来是au 0的块0)。分配单位的默认大小为为1M,元数据块的默认大小为4K,这表示每个au中有256块。所以,通常副本在au1 block 254(ASM从零计数,原始的在分配单元0块0)
kfed修复
确定唯一的问题是与丢失/损坏的磁盘头后,该修复程序很简单,只要:
$ kfed repair
如果su大小为非标准的,以上会失败,显示如下:
KFED-00320: Invalid block num1 =  [3] , num2 =  [1] , error = [type_kfbh]
但这是预料之中的,且没有任何损害。你只需要指定正确的au大小。例如4MB AU的命令是:
$ kfed repair ausz=4194304 
DUL
DUL将检查/使用头复制(总是?或者只在主头损坏的情况下?)如果头损坏,但副本是好的,警告使用kfed?
参考
Bug 5061821 OS工具可以摧毁ASM磁盘头fixed 11.2.0.1,11.1.0.7,10.2.0.5和更高。
注意417687.1在现有的一个磁盘头损坏后创建一个新的ASM磁盘头
rdbms/src/client/tools/kfed/kfed.c




DUL导出扫描的lob

我们的想法是直接从LOB段导出LOB。
我们需要弄清楚:
1. 它是CLOB还是BLOB
2. 对于CLOB,它的字符集,单字节或UCS16,字节大小
3. 块大小,在头中的lob page#是一个fat page number
4. 缺失页应当被发出为全零
5. 大小未知,只需要剥离trailing zeroes(但对于字节大小)?
实施的变更:
1.添加LOB段ID来扫描lob页面信息,因为大多数属性适用于段。即扫描的lob页缓存的布局是:segid,lobid,fatpageno(chunk#),version wrap, vsn base, ts#, file#, block#
要做的事:
1.导出所有LOB的命令。一个LOB段提供命令中所有的常用属性。
2.从LOB段指定lobid,可选的大小,和可选的DBA列表中导出单个lob的命令
3.分析生成导出LOB段命令的命令
4.分析生成从段中导出每个LOB命令的命令
需要考虑的事项:
1.	更改 file#, block# 为单个dba?pro no calculations, contra more difficult to read?




我正与客户在处理一个数据打捞作业。从DUL网站下载的DUL工具不能正常工作!

dul4x86-linux.tar.gz给出错误:version ‘GLIBC_2.11’ not found

dul4i386-linux.as3.tar.gz给出错误:You need a more recent DUL version for this os.你需要一个较新的版本DUL这个操作系统。

客户端Linux版本:2.6.32-400.33.2.el5uek

请帮忙!!!


Linux有两个版本,这是第二个,被正常启动的。由于内建复制的保护,您必须确保从Bernard的网站上下载最新的版本。如果你确实有最新的下载,那么只有Bernard可以重新编译并重新分配一个新的可执行版本。 DUL大约每45天失效。




我们处在关键时刻,需要DUL从一个production down降数据库中提取数据。
- 数据库在NOARCHIVELOG模式/ Windows 64位平台。
- 由于go life,没有可用的数据库备份
- 数据库较小,但非常重要。
- 从早晨开始有介质故障损坏了除真正客户数据的数据文件之外的所有数据库的数据文件。
- 根据每db验证工具输出,系统表空间100%损坏。
- 任何地方都没有系统表空间的备份,甚至测试系统是作为新的数据库创建的,因此对象ID,rfile号是不一样的。

我们尝试了以下内容:
1.	使用系统数据文件从生产中导出数据(由于系统数据文件被破坏,无法bootstrap)
2.	使用系统数据文件从TEST中导出数据(成功bootstrap,但无法导出,因为不匹配rfile#,TS#,和对象ID ..预想到的结果,但值得一试)
3.	仅使用实际数据的数据文件导出数据,成功生成scaned_tables,并且我们已向客户请求提供表的列表来map,但我不确定他们能否提供清晰信息。

感谢提供任何建议,类似于:
- 有没有什么办法来修复损坏的系统表空间的数据文件,并用它进行数据导出。
- 或者,有没有办法从TEST使用系统数据文件(不同的数据库作为新安装),其中rfile#, ts#,和对象id的错配。


 

 

 

 

 

 

 

 

 

 

DUL’s PRINCIPLES and FEATURE LIST

STANDALONE C-PROGRAM

DUL is a standalone C program that directly retrieves rows from tables in data files. The Oracle RDBMS software is NOT used at all. DUL does dirty reads, it assumes that every transaction is committed. Nor does it check/require that media recovery has been done.

LAST RESORT

DUL is intended to retrieve data that cannot be retrieved otherwise. It is NOT an alternative for EXP, SQL*Plus etc. It is meant to be a last resort, not for normal production usage.

Before you use DUL you must be aware that the rdbms has many hidden features to force a bad database open. Undocumented init.ora parameters and events can be used to skip roll forward, to disable rollback, disable certain SMON actions, advance the database scn and more.

DATABASE CORRUPT – BLOCKS OK

The database can be corrupted, but an individual data block used must be 100% correct. During all unloading checks are made to make sure that blocks are not corrupted and belong to the correct segment. If during a scan a bad block is encountered, an error message is printed in the loader file and to standard output. Unloading will continue with the next row or block.

ROWS in CLUSTERS/TABLES/INDEXES

DUL can and will only unload index/table/cluster data. It will NOT dump triggers, stored procedures nor create sql scripts for tables or views. (But the data dictionary tables describing them can be unloaded). The data will be unloaded in a format suitable for SQL*Loader or IMP. A matching control file for SQL*Loader is generated as well.

DUL can unload indices and index organized tables. Index unload is usefull to determine how many rows a table should have or to identify the missing rows.

CROSS PLATFORM UNLOADING

Cross-platform unloading is supported. The database can be copied from a different operating system than the DUL-host. (Databases/systems done so far: Sequent/ptx, Vax Vms, Alpha Vms, MVS, HP9000/8xx, IBM AIX, SCO Unix, Alpha OSF/1, Intel Windows NT).

The configuration parameters within “init.dul” will have to be modified to match those of the original platform and O/S rather than the platform from which the unload is being done.

ROBUST

DUL will not dump, spin or hang no matter how badly corrupted the database is.

(NEARLY) ALL ORACLE FEATURES SUPPORTED

Full support for all database constructs: row chaining, row migration, hash/index clusters, longs, raws, rowids, dates, numbers, multiple free list groups, segment high water mark, NULLS, trailing NULL columns, and unlimited extents, new block layout of Oracle8, partitioned tables.

Later additions are lobs, compressed indexes, 9ir2 compressed tables. Varrays and ADTs (user defined objects) are partly supported in sql*loader mode.

ASM is fully supported, files can be extracted from an asm disk group. No mounted ASM instance is used, the disks are accessed directly. Non default asm allocation unit sizes are supported.

Data can be recovered from export dump files with the unexp command suite. Some initial work has been done for unpump to support data pump files.

SUPPORTED RDBMS VERSIONS

DUL should work with all versions starting oracle 6. DUL has been tested with versions from 6.0.26 up to 10.2. Even the old block header layout (pre 6.0.27.2) is supported.

MULTI BYTE SUPPORT

DUL itself is essentially a single byte application. The command parser does not understand multi byte characters, but it is possible to unload any multi byte database. For all possible caveats there is a work around.

DUL can optionally convert to UTF8. This is for NCLOBS that are stored in UTF16.

RESTRICTIONS

MLSLABELS

Multi Level Security Lables of trusted oracle are not supported.

(LONG) RAW

DUL can unload (long) raws. Nowadays there is suitable format in SQL*Loader to preserve all long raws. So Long raws and blobs can be unloaded in both modes.

ORACLE8 OBJECT OPTION AND LOBS

Nested tables are not yet supported, if they are needed let me know and it will be added. Varrays and ADTs are supported, also those that are stored as a kernel lob. CLOBS, NCLOBS are supported both in SQL*Loader mode and in exp mode. BLOBS are best handled in exp mode, the generated hex format in SQL*Loader mode is not loaded correctly currently.

PORTABLE

DUL can be ported to any operating system with an ANSI-C compiler. DUL has been ported to many UNIX variants, VMS and WindowsNT. Currently all builds are done using gcc and a cross compiler environment on Linux

RDBMS INTERNALS

A good knowledge of the Oracle RDBMS internals is a pre requisite to be able to use DUL successfully. For instance the Data Server Internals (DSI) courses give a good foundation. There is even a module dedicated to DUL

 

SETTING UP and USING DUL

CONFIGURATION FILES

There are two configuration files for DUL. “init.dul” contains all configuration parameters. (size of caches, details of header layout, oracle block size, output file format) In the control file, “control.dul”, the database data file names and the asm disks can be specified.

DATA DICTIONARY AVAILABLE

The Oracle data dictionary is available if the data files which made up the SYSTEM TableSpace are available and useable. The number which Oracle assigned to these files and the name you have given them, which does not have to be the original name which Oracle knew, must be included in the “control.dul” file. You also need to eventually include the file numbers and names of any files from other TableSpaces for which you wish to eventually unload TABLES and their data. The lack of inclusion of these files will not affect the data dictionary unload step but it will affect later TABLE unloading.

USING DUL WHEN USER$, OBJ$, TAB$ and COL$ CAN BE  UNLOADED

Steps to follow:

  1. configure DUL for the target database. This means creating a correct init.dul and control.dul. The SYSTEM TableSpace’s data file numbers and names must be included within the control.dul file along with any data files for TableSpaces from which you wish to unload TABLEs and their data. For Oracle8 and higher the tablespace number and the relative file number must be specified for each datafile.
  2. Use the ” BOOTSTRAP; ” command to prepare for unloading. The bootstrap process will find a compatibility segment, find the bootstrap$ table unload The old ” dul dictv7.ddl”re no longer needed.
  3. Unload the tables for which data files have been included within the “control.dul” file. Use one of the following commands:
    • “UNLOAD TABLE [ owner>.]table ; (do not forget the semicolon)
      • This will unload the one table definition and the table’s data.
    • “UNLOAD USER user name ;
      • This unloads all tables and data for the specified user.
    • “UNLOAD DATABASE ;
      • This unloads all of the database tables available. (except the user SYS).

NO DATA DICTIONARY AVAILABLE

If data files are not available for the SYSTEM TableSpace the unload can still continue but USER, TABLE and COLUM names will not be known. Identifying the tables can be an overwhelming task. But it can be (and has been) done. You need in depth knowledge about your application and the application tables. Column types can be guessed by DUL, but table and column names are lost. Any old SYSTEM tablespace from the same database but weeks old can be of great help!. Most of the information that DUL uses does not change. (only the dataobj# is during truncate or index rebuild)

USING DUL WITHOUT SYSTEM TABLESPACE

Steps to follow:

  1. configure DUL for the target database. This means creating a correct init.dul and control.dul. (See Port specific parameters ). In this case control.dul file will need the numbers and names of datafiles from which TABLEs and data will be unloaded but it does not require the SYSTEM TableSpace’s information.
  2. SCAN DATABASE; : scan the database, build extent and segment map
  3. SCAN TABLES; or SCAN EXTENTS; : gather row statistics
  4. Identify the lost tables from the output of step 3.
  5. UNLOAD the identified tables.
AUTOMATED SEARCH

To ease the hunt for the lost tables: the scanned statistical information in seen_tab.dat and seen_col.dat can be loaded into a fresh database. If you recreate the tables ( Hopefully the create table scripts are still available) then structure information of a “lost” table can be matched to the “seen” tables scanned information with two SQL*Plus scripts. (fill.sql and getlost.sql).

HINTS AND PITFALLS
  • Names are not really relevant for DUL, only for the person who must load the data. But the unloaded data does not have any value, if you do not know from which table it came.
  • The guessed column types can be wrong. Even though the algorithm is conservative and decides UNKNOWN if not sure.
  • Trailing NULL columns are not stored in the database. So if the last columns only contain NULL’s than the scanner will NOT find them. (During unload trailing NULL columns are handled correctly).
  • When a table is dropped, the description is removed from the data dictionary only. The data blocks are not overwritten unless they are reused for a new segment. So the scanner software can see a table that has been dropped.
  • Tables without rows will go unnoticed.
  • Newer objects have a higher object id than older objects. If an table is recreated, or if there is a test and a production version of the same table the object id can be used to decide.

DDL (DUL Description Language) UNLOAD STATEMENT OVERVIEW

DUL uses an SQL like command interface. There are DDL statements to unload extents, tables, users or the entire database. Data dictionary information required can be specified in the ddl statements or taken from the previously unloaded data dictionary. The following three statements will unload the DEPT table. The most common form is if the data dictionary and the extent map are available:

         UNLOAD TABLE scott.dept;

All relevant information can be specified in the statement as well:

        REM Columns with type in the correct order
        REM The segment header loaction in the storage clause
        UNLOAD TABLE dept( deptno NUMBER, dname CHAR, loc CHAR)
               STORAGE( EXTENTS ( FILE 1 BLOCK 1205 ));

Oracle version 6:

        REM version 6 data blocks have segment header location in each block
        ALTER SESSION SET USE_SCANNED_EXTENT_MAP = TRUE;
        UNLOAD TABLE dept( deptno NUMBER, dname CHAR, loc CHAR)
               STORAGE( EXTENTS ( FILE 1 BLOCK 1205 ));

Oracle7:

        REM Oracle7 data blocks have object id in each block

        ALTER SESSION SET USE_SCANNED_EXTENT_MAP = TRUE;
        UNLOAD TABLE dept( deptno NUMBER, dname CHAR, loc CHAR)
               STORAGE( OBJNO 1501 );

DUL’S OUTPUT FORMAT.

Only complete good rows are written to the output file. For this each row is buffered. The size of the buffer can changed with the init.dul parameter BUFFER. There is no speed gained with a high BUFFER parameter, it should just be big enough to hold a complete row. Incomplete or bad rows are not written out. The FILE_SIZE_IN_MB init.dul parameter can be used to split the output (at a proper boundary) into multiple files, each file can be loaded individually.

There are three different modes of output format.

  • Export mode
  • SQL*Loader mode: stream data files
  • SQL*Loader mode: Fixed physical record data files

EXPORT MODE

The generated file is completely different from a table mode export generated by EXP! The file is the minimal format that IMP can load. For each table a separate IMP loadable file will be generated. It is a single table dump file. It contains a header an insert table statement and the table data. Table grants, storage clauses, or triggers will not be included. An minimal create table statement is included (no storage clause just column names and types without precision). The character set indication in the file in the generated header is V6 style. It is set to mean ASCII based characterset.

To enable export mode, set the init.dul parameter EXPORT_MODE to TRUE.

As the generated pseudo dump file does not contain character set information set NLS_LANG to match that of the original database. In export mode no character set conversion is done.

SQL*LOADER MODES

The data in the is either not converted at all, or everthing is converted to UTF8 if LDR_OUTPUT_IN_UTF8 is set. This setting is required in mixed character set environments as the contents of a data file must have a single character set.<\P>

When loading the data you probably need to set NLS_LANG to match that of the original database to prevent unwanted character set conversion.

For both SQL*Loader output formats the columns will be space separated and enclosed in double quotes. Any double quote in the data will be doubled. SQL*Loader recognizes this and will load only one. The character used to enclose the columns can be changed from double quote to any character you like with the init.dul parameter LDR_ENCLOSE_CHAR.

There are two styles of physical record organization:

Stream Mode

Nothing special is done in stream mode, a newline is printed after each record. This is a compact format and can be used if the data does not contain newline characters. To enable stream mode set LDR_PHYS_REC_SIZE = 0 in init.dul.

Fixed Physical Records

This mode is essential if the data can contain newlines. One logical record, one comlete row, can be composed of multiple physical records. The default is record length is 81, this fits nicely on the screen of a VT220. The physical record size can be specified with LDR_PHYS_REC_SIZE in init.dul.

OUTPUT FILE NAMES

The file names generated are: owner name_table name.ext. The extension is “.dmp” for IMP loadable files. “.dat” and “.ctl” are used for the SQL*Loader datafile and the control file. To prevent variable substitution and other unwanted side effects, strange characters are stripped.(Only alpha numeric and ‘_’ are allowed).

If the FILE parameter is set the generated names will be FILEnnn.ext. This possibility is a work around if the file system does not support long enough file names. (Old windows with 6.3 filename format)

SOME DUL INTERNALS

REQUIRED INFORMATION

To unload table data from a database block the following information must be known:

  1. Column/Cluster Information: The number and type of the columns. For char or varchar columns the maximum length as well. The number of cluster columns and the table number in the cluster. This information can be supplied in the unload statement or it can be taken from the previously unloaded USER$, OBJ$, TAB$ and COL$.
  2. Segment/Extent information: When unloading a table the extent table in the data segment header block is used to locate all data blocks. The location of this segment header block (file number and block number) is taken from the data dictionary or can be specified in the unload statement. If the segment header is not correct/available then another method must be used. DUL can build its own extent map by scanning the whole database. (in a separate run of DUL with the scan database statement.)
BINARY HEADERS

C-Structs in block headers are not copied directly, they are retrieved with specialized functions. All offsets of structure members are programmed into DUL. This approach makes it possible to cross-unload. (Unload an MVS created data file on an HP) Apart from byte order only four layout types have been found so far.

  1. Vax VMS and Netware : No alignment padding between structure members.
  2. Korean Ticom Unix machines : 16 bit alignment of structure members.
  3. MS/DOS 16 bit alignment and 16 bit wordsize.
  4. Rest of the world (Including Alpha VMS) structure member alignment on member size.
MACHINE DEPENDENCIES

Machine dependencies (of the database) are configurable with parameters:

  • Order of bytes in a word (big/little endian).
  • Number of bits for the low part of the FILE# in a DBA (Block Address).
  • Alignment of members in a C-struct.
  • Number of blocks or bytes before the oracle file header block.
  • Size of a word used in the segment header structure.
UNLOADING THE DATA DICTIONARY

DUL can use the data dictionary of the database to be unloaded if the files for it exist and are uncorrupted. For the data dictionary to be used, internal tables must be unloaded first to external files: (USER$, OBJ$, TAB$ and COL$). The bootstrap command will find and unload the required tables.

DDL ( DUL DESCRIPTION LANGUAGE ) SPECIFICATION

[ ALTER SESSION ] SET init.dul parameter =  value ;
     Most parameters can be changed on the fly. 

BOOTSTRAP [LOCATE | GENERATE | COMPLETE
           | UNLOAD   Bootstrap$ segment header block address ];
     Bootstraps the data dictionary. Default is COMPLETE.
     LOCATE finds and unloads the bootstrap$ table.
     GENERATE builds a ddl file based on inforation in the cache.
     COMPLETE is in fact LOCATE, followed by GENERATE (two times)

COMMIT;
     Writes the changed block to the data file.

CREATE BLOCK INDEX  index_name  ON  device ;

A block index contains address of valid oracle blocks found in a corrupt file system. Useful to merge multiple disk images or to unload from corrupted file systems. This is only useful in extreme file system corruption scenarios.

DESCRIBE  owner_name  . table_name ;

DUMP [ TABLESPACE  tablespace_no ]
     [ FILE  file_no  ]
     [ BLOCK  block_no  ]
     [ LEVEL  level_no  ] ;
     Not a complete blockdump, mainly used for debugging.
     The block address is remembered.

EXTRACT  asm file name  to  output file name  ;
     Copies any ASM file from a disk group to the file system.
     (there was a problem with online redologs this needs more testing)

MERGE block_index INTO [  segment  ];

The merge command uses the information in the index file to locate possible data blocks it looks for a combination of file numbers and object id, each candidate block is compared to the current block in the datafile. If the current block is bad, or has an older scn the candidate will will be written into the datafile. This is only useful in extreme file system corruption scenarios.

REM any_text_you_like_till_End_Of_Line : comment
REM  NOT allowed inside ddl statements. ( To avoid a two layer lexical scan).

ROLLBACK; # Cancels the UPDATE statements.

SHOW     DBA  dba ;                # dba -> file_no block_no calculator
	   | DBA  rfile_no block_no ;  # file_no block_no -> dba calculator
	   | SIZES ;                   # show some size of important structs
	   | PARAMETER;                # shows the values of all parameters
	   | LOBINFO;                  # lob indexes found with SCAN DATABASE
       | DATAFILES;                # summary of configured datafiles
       | ASM DISKS;                # summary of configured asm disks
       | ASM FILES;                # summary of configured datafiles on asm
       | ASM FILE  cid      # extent information for asm file

UNEXP [TABLE] [  owner  . ]  table name 
       (  column list  ) [ DIRECT ]
       DUMP FILE  dump file name
       FROM  begin offset  [ UNTIL  end offset  ]
       [ MINIMUM  minimal number of columns  COLUMNS ] ;

       To unload data from a corrupted exp dump file. No special setup
       or configuration is required, just the compatible parameter.
       The start offset should be where a row actually begins.

UNPUMP
       To unload data from a corrupted expdp (datapump) dump file.
       This is still work in progress, the basic commands work
       but rather complex to use. Contact me if this is needed.

UNLOAD DATABASE;

UNLOAD USER user_name;

UNLOAD [TABLE]  [  schema_name . ]  table_name 
              [ PARTITION(  partition_name ) ]
              [ SUBPARTITION(  sub_partition_name ) ]
              [ (  column_definitions ) ]
              [  cluster_clause  ]
              [  storage_clause  ] ;

UNLOAD EXTENT  table_name 
              [ (  column_definitions  ) ]
              [ TABLESPACE  tablespace_no  ] 
              FILE  extent_start_file_number
              BLOCK extent_start_block_number 
              BLOCKS  extent_size_in oracle_blocks ;

UNLOAD LOB SEGMENT FOR [  schema_name . ]  table_name   [ (  column name  ) ] ;

UNLOAD LOB SEGMENT STORAGE ( SEGOBJNO data obj#) ;

UPDATE [ block_address ] SET UB1|UB2|UB4 @ offset_in_block = new_value ;
UPDATE [ block_address ] SET  block element name  = new_value ;
        Now and then we can repair something.
	Patches the current block and dumps it.
	You can issue multiple UPDATE commands.
	Block is not written yet, use COMMIT to write. 

storage_clause ::=
        STORAGE ( storage_specification  [ more_storage_specs ] )

storage_specification ::=
        OBJNO object_id_number 
|       TABNO cluster_table_number
|       SEGOBJNO cluster/data_object_number       /* v7/v8 style data block id */
|       FILE  data_segment_header_file_number     /* v6 style data block id */
        BLOCK data_segment_header_block_number )   
|       any_normal_storage_specification_but_silently_ignored

SCAN DATABASE;

Scans all blocks of all data files. Two or three files are generated:

  1. SEG.dat information of found segment headers (index/cluster/table): (object id, file number, and block number).
  2. EXT.dat information of contiguous table/cluster data blocks. (object id(V7), file and block number of segment header (V6), file number and block number of first block, number of blocks, number of tables)
  3. SCANNEDLOBPAGE.dat information for each lob datablock, this file (optional, only if init.dul:SCAN_DATABASE_SCANS_LOB_SEGMENTS=TRUE) can possibly be huge. Also the required memory size can be problematic. The purpose is twofold: 1: to possibly work around corrupt lob indexes during unload table. 2: unload lob segments (for deleted lobs or lob segments without lob index or parent table) Meaning of the fields in SCANNEDLOBPAGE.dat: (segobj#, lobid, fat_page_no, version( wrap, base), ts#, file#, block#)
SCAN DUMP FILE  dump file name
        [ FROM  begin offset  ]
        [ UNTIL  end offset  ];

        Scans an  export dump file to produce to provide the
        create/insert statements and the offsets in the dump file.

SCAN LOB SEGMENT    storage clause ;
SCAN LOB SEGMENT FOR  table name  [.  column name] ;
        Scans the lob segment to produce LOBPAGE.dat information,
        but then for this segment only. Probably quicker and
        smaller. For partitioned objects use scan database.

SCAN TABLES;
        Uses SEG.dat and EXT.dat as input.
        Scans all tables in all data segments (a header block and at least one
        matching extent with at least 1 table).

SCAN EXTENTS;
        Uses SEG.dat and EXT.dat as input.
        All extents for which no corresponding segment header has been found.
        (Only useful if a tablespace is not complete, or a segment header
        is corrupt).

EXIT QUIT and EOF all cause DUL to terminate.

DDL ( DUL DESCRIPTION LANGUAGE ) DESCRIPTION

Rules for UNLOAD EXTENT and UNLOAD TABLE:

Extent Map

UNLOAD TABLE requires an extent map. In 99.99% of the cases the extent map in the segment header is available. In the rare 0.01% that the segment header is lost an extent map can be build with the scan database command. The self build extent map will ONLY be used during an unload if the parameter USE_SCANNED_EXTENT_MAP is set to TRUE.

All data blocks have some ID of the segment they belong to. But there is a fundamental difference between V6 and V7. Data blocks created by Oracle version 6 have the address of the segment header block. Data blocks created by Oracle7 have the segment object id in the header.

Column Specification

The column definitions must be specified in the order the columns are stored in the segment, that is ordered by col$.segcol#. This is not necessarily the same order as the columns where specified in the create table statement. Cluster columns are moved to the front, longs to the end. Columns added to the table with alter table command, are always stored last.

Unloading a single extent

UNLOAD EXTENT can be used to unload 1 or more adjacent blocks. The extent to be unloaded must be specified with the STORAGE clause: To specify a single extent use: STORAGE ( EXTENTS( FILE fno BLOCK bno BLOCKS #blocks) ) (FILE and BLOCK specify the first block, BLOCKS the size of the extent)

DUL specific column types

There are two extra DUL specific data types:

  1. IGNORE: the column will be skipped as if it was not there at all.
  2. UNKNOWN: a heuristic guess will be made for each column.

In SQL*Loader mode there are even more DUL specific data types:

  1. HEXRAW: column is HEX dumped.
  2. LOBINFO: show some information from LOB locators .
  3. BINARY NUMBER: Machine word as used in a LOB index.

Identifying USER$, OBJ$, TAB$ and COL$

DUL uses the same bootstrap procedure as the rdbms. That is it uses the root dba from the system datafile header to locate the bootstrap$ table. Depending on the version this root dba is either the location of the compatibility segment containing the bootstrap$ address or for the newer versions the address of the bootstrap$ table itself. The bootstrap$ table is unloaded and its contents is parsed to find the first four tables (USER$, OBJ$, TAB$ and COL$). The other tables are unloaded based on information in these first four.

DESCRIPTION OF SCAN COMMANDS

SCAN TABLES and SCAN EXTENTS scan for the same information and produce similar output. ALL columns of ALL rows are inspected. For each column the following statistics are gathered:

  • How often the column is seen in a data block.
  • The maximum internal column length.
  • How often the column IS NULL.
  • How often the column consists of at least 75% printable ascii.
  • How often the column consists of 100% printable ascii.
  • How often the column is a valid oracle number.
  • How often the column is a nice number. (not many leading or trailing zero’s)
  • How often the column is a valid date.
  • How often the column is a possible valid rowid.

These statistics are combined and a column type is suggested. Using this suggestion five rows are unloaded to show the result. These statistics are dumped to two files (seen_tab.dat and seen_col.dat). There are SQL*Loader and SQL*Plus scripts available to automate a part of the identification process. (Currently known as the getlost option).

DESCRIBE

There is a describe command. It will show the dictionary information for the table, available in DUL’s dictionary cache.

DUL STARTUP SEQUENCE

During startup DUL goes through the following steps:

  • the parameter file “init.dul” is processed.
  • the DUL control file (default “control.dul”) is scanned.
  • Try to load dumps of the USER$, OBJ$, TAB$ and COL$ if available into DUL’s data dictionary cache.
  • Try to load seg.dat and col.dat.
  • Accept DDL-statements or run the DDL script specified as first arg.

DUL parameters to be specified in init.dul:

ALLOW_TRAILER_MISMATCH
BOOLEAN
Strongly discouraged to use, will seldom produce more rows. Use only if you fully understand what it means and why you want it. skips the check for correct block trailer. The blocks failing this test are split of corrupt. But it saves you the trouble to patch some blocks.
ALLOW_DBA_MISMATCH
BOOLEAN
Strongly discouraged to use, will seldom produce more rows. Use only if you fully understand what it means and why you want it. Skips the check for correct block address. The blocks failing this test are probably corrupt. But it saves you the trouble to patch some blocks.
ALLOW_OTHER_OBJNO
BOOLEAN
If your dictionary is older than your datafiles then the data object id’s can differ for truncated tables. With this parameter set to true it will issue a warning but use the value from segment header. All other blocks are fully checked. This is for special cases only.
ASCII2EBCDIC
BOOLEAN
Must (var)char fields be translated from EBCDIC to ASCII. (For unloading MVS database on a ASCII host)
BUFFER
NUMBER (bytes)
row output buffer size used in both export and SQL*Loader mode. In each row is first stored in this buffer. Only complete rows without errors are written to the output file.
COMPATIBLE
NUMBER
Database version , valid values are 6, 7, 8 or 9. This parameter must be specified
CONTROL_FILE
TEXT
Name of the DUL control file (default: “control.dul”).
DB_BLOCK_SIZE
NUMBER
Oracle block size in bytes (Maximum 32 K)
DC_COLUMNS
NUMBER
DC_OBJECTS
NUMBER
DC_TABLES
NUMBER
DC_USERS
NUMBER
Sizes of dul dictionary caches. If one of these is too low the cache will be automatically resized.
EXPORT_MODE
BOOLEAN
EXPort like output mode or SQL*Loader format
FILE
TEXT
Base for (dump or data) file name generation. Use this on 8.3 DOS like file systems
FILE_SIZE_IN_MB
NUMBER (Megabytes)
Maximum dump file size. Dump files are split into multiple parts. Each file has a complete header and can be loaded individually.
LDR_ENCLOSE_CHAR
TEXT
The character to enclose fields in SQL*Loader mode.
LDR_PHYS_REC_SIZE
NUMBER
Physical record size for the generated loader datafile.
LDR_PHYS_REC_SIZE = 0 No fixed records, each record is terminated with a newline.
LDR_PHYS_REC_SIZE > 2: Fixed record size.
MAX_OPEN_FILES
Maximum # of database files that are concurrently kept open at the OS level.
OSD_BIG_ENDIAN_FLAG
Byte order in machine word. Big Endian is also known as MSB first. DUL sets the default according to the machine it is running on. For an explanation why this is called Big Endian, you should read Gullivers Travels.
OSD_DBA_FILE_BITS
File Number Size in DBA in bits. Or to be more precise the size of the low order part of the file number.
OSD_FILE_LEADER_SIZE
bytes/blocks added before the real oracle file header block
OSD_C_STRUCT_ALIGNMENT
C Structure member alignment (0,16 or 32). The default of 32 is correct for most ports.
OSD_WORD_SIZE
Size of a machine word always 32, except for MS/DOS(16)
PARSE_HEX_ESCAPES
Boolean default FALSE
Use \\xhh hex escape sequences in strings while parsing. If set to true then strange characters can be specified using escape sequences. This feature is also for specifying multi-byte characters.
USE_SCANNED_EXTENT_MAP
BOOLEAN
Use the scanned extent map in ext.dat when unloading a table. The normal algorithme uses the extent map in the segment header. This parameter is only useful if some segment headers are missing or incorrect.
WARN_RECREATE_FILES
BOOLEAN (TRUE)
Set to FALSE to suppress the warning message if an existing file is overwritten.
WRITABLE_DATAFILES
BOOLEAN (FALSE)
Normal use of DUL will only read the database files. However the UPDATE and the SCAN RAW DEVICE will write as well. The parameter is there to prevent accidental damage.

SAMPLE init.dul :

# sample init.dul configuration parameters
# these must be big enough for the database in question
# the cache must hold all entries from the dollar tables.
dc_columns = 200000
dc_tables = 10000
dc_objects = 10000
dc_users = 40

# OS specific parameters
osd_big_endian_flag = false
osd_dba_file_bits = 10
osd_c_struct_alignment = 32
osd_file_leader_size = 1

# database parameters
db_block_size = 8k

# loader format definitions
LDR_ENCLOSE_CHAR = "
LDR_PHYS_REC_SIZE = 81

Configuring the port dependent parameters

Starting from rdbms version 10G osd parameters are easy to configure. Typically all parameters can be used at their defaults. The only one that might need attention is osd_big_endian_flag, when doing a cross platform unload, where the original database platform is different from the current machine. If osd_big_endian_flag is set incorrectly, it is detected at startup, when doing file header inspection.

Collection of known Parameters

For pre 10G databases there is a list of known parameters in the osd wiki page list of osd (Operating System Dependend) parameters for almost every platform. If your platform is not in the list you can use the suggestions below to determine the parameters. (And then please inform me so I can add them to the list.)

osd_big_endian_flag

big endian or little endian (byte order in machine words): HP, SUN and mainframes are generally big endian: OSD_BIG_ENDIAN_FLAG = TRUE. DEC and Intel platforms are little endian: OSD_BIG_ENDIAN_FLAG = FALSE. The default is correct for the platform where DUL is running on.

There is no standard trick for this, the following might work on a unix system:

  echo dul | od -x
  If the output is like:
     0000000 6475 6c0a
     0000004
  You are on a big endian machine (OSD_BIG_ENDIAN_FLAG=TRUE).

  If you see:
     0000000 7564 0a6c
     0000004
  This is a little endian machine (OSD_BIG_ENDIAN_FLAG=FALSE).
osd_dba_file_bits

The number of bits in a dba used for the low order part of file number. Perform the following query:

  SQL> select dump(chartorowid('0.0.1')) from dual;
  
  Typ=69 Len=6: 8,0,0,0,0,0    ->       osd_dba_file_bits =  5 (SCO)
  Typ=69 Len=6: 4,0,0,0,0,0    ->       osd_dba_file_bits =  6 (Sequent , HP)
  Typ=69 Len=6: 1,0,0,0,0,0    ->       osd_dba_file_bits =  8 (NCR,AIX)
  Typ=69 Len=6: 0,16,0,0,0,0   ->       osd_dba_file_bits = 12 (MVS)
  Typ=69 Len=10: 0,0,0,0,0,64,0,0,0,0      osd_dba_file_bits = 10 (Oracle8)       
OSD_C_STRUCT_ALIGNMENT

Structure layout in data file headers. 0: No padding between members in a C-struct (VAX/VMS only) 16: Some korean ticom machines and MS/DOS 32: Structure members are member size aligned. (All others including ALPHA/VMS) Check the following query:

SELECT * FROM v$type_size
WHERE type IN ( 'KCBH', 'KTNO', 'KCBH', 'KTBBH', 'KTBIT', 'KDBH'
              , 'KTECT', 'KTETB', 'KTSHC') ;

In general osd_c_struct_alignment = 32 and the following output is expected:

K        KTNO     TABLE NUMBER IN CLUSTER                   1
KCB      KCBH     BLOCK COMMON HEADER                      20
KTB      KTBIT    TRANSACTION VARIABLE HEADER              24
KTB      KTBBH    TRANSACTION FIXED HEADER                 48
KDB      KDBH     DATA HEADER                              14
KTE      KTECT    EXTENT CONTROL                           44
KTE      KTETB    EXTENT TABLE                              8
KTS      KTSHC    SEGMENT HEADER                            8

8 rows selected.

For VAX/VMS and Netware ONLY osd_c_struct_alignment = 0 and this output is expected:

COMPONEN TYPE     DESCRIPTION                      SIZE
-------- -------- -------------------------------- ----------
K        KTNO     TABLE NUMBER IN CLUSTER                   1
KCB      KCBH     BLOCK COMMON HEADER                      20
KTB      KTBIT    TRANSACTION VARIABLE HEADER              23
KTB      KTBBH    TRANSACTION FIXED HEADER                 42
KDB      KDBH     DATA HEADER                              14
KTE      KTECT    EXTENT CONTROL                           39
KTE      KTETB    EXTENT TABLE                              8
KTS      KTSHC    SEGMENT HEADER                            7

8 rows selected.

If there is a different list this will require some major hacking and sniffing and possibly a major change to DUL. (Email Bernard.van.Duijnen@oracle.com)

osd_file_leader_size

Number of blocks/bytes before the oracle file header. Unix datafiles have an extra leading block ( file size, block size magic number) A large number ( > 100) is seen as a byte offset, a small number is seen as a number of oracle blocks.

Unix    :       osd_file_leader_size = 1
Vms     :       osd_file_leader_size = 0
Desktop :       osd_file_leader_size = 1 (or 512 for old personal oracle)
Others  :       Unknown ( Use Andre Bakker's famous PATCH utility to find out)
                An Oracle7 file header block starts with the pattern 0X0B010000.

You can add an additional byte offset in control.dul in the optional third field (for instance for AIX or DEC UNIX data files on raw device)

Control file syntax specification

A control file (default name “control.dul”) is used to specify asm disks, block indexes and the data file names. The format of the control has been extended

Currently there are three types of specifications in the DUL control file. Each entry on a separate line. The asm disks must precede the asm files.

  control_file_line ::= asm_disk_spec | file_piece_spec | block_index_spec

If COMPATIBLE is 10 or higher you can also specify asm disks. Its generally sufficent to specify the device name. All properties are automatically retrieved by header inspection. The full syntax is only needed when header inspection is impossible, that is for disks with corrupt headers. The syntax is:

DISK  device name [  disk group options  ]

 disk group option  ::= GROUP  disk group name 
                           | DISK_NO  disk number in group 
                           | F1B1  File1 Block1 location

A block index is a way to access oracle blocks on corrupt file systems. In general a corrupt file system is not wiped out, its not empty. Due to the specific layout of oracle blocks it is possible to datablocks an store their location in the block index. See also the create block index command . A block_index_name is a normal identifier, it is used to construct an unique file name.

BLOCK INDEX  block_index_name

Each entry can contain a part of a datafile. The smallest unit is a single data block. This way it is possible to split datafiles that are too big for DUL in parts where each part is smaller than 2GB.

In general it is sufficient to specify the file name. Even for a single block. If compatible is 10 or higher the file numbers and the tablespace numbers will be read from the file header.

If the specified details are different from the file header DUL will give a warning but use your specification. This is to be able to unload files with a corrupted header block. For debugging it is possible to dump the file header.

The optional extra leader offset is an extra byte offset, that will be added to all lseek() operations for that datafile. This makes it possible to skip over the extra 4k block for some AIX raw devices, or the extra 64K on Tru64 on raw devices

  file_piece_spec ::= 
         [ [ tablespace_no ] relative_file_number]data_file_name
         [ optional extra leader offset ]
         [ startblock block_no ]
         [ endblock block_no ]

Examples

   # AIX version 7 example with one file on raw device
   1 /usr/oracle/dbs/system.dbf
   8 /dev/rdsk/data.dbf 4096
   # Oracle8 example with a datafile split in multiple parts, each part smaller than 2GB
   0  1 /fs1/oradata/PMS/system.dbf
   1  2 /tmp/huge_file_part1 startblock 1 endblock 1000000
   1  2 /tmp/huge_file_part2 startblock 1000001 endblock 2000000
   1  2 /mnt3/huge_file_part3 startblock 2000001 endblock 2550000
# ASM disks for two disk groups
disk /media/maxtor/asm/dgn1
disk /media/maxtor/asm/dgn2
disk /media/maxtor/asm/dgn3
disk /media/maxtor/asm/dgn4
disk /media/maxtor/asm/dgodd

# system datafile in the first asm disk group
+DGN/db102/datafile/system.257.621616979

# users datafile in a different disk group
+DGODD/db102/datafile/users.257.621616683

# a so called big file tablespace, use 1024 for the file#
 8 1024 /home/oracle/v102/dbs/bigfilets

# Or let DUL find out itself from the header
 /home/oracle/v102/dbs/bigfilets

# one tablespace with a different block size
/home/oracle/v102/dbs/ts16k.dbf block_size 16k

# or let DUL find out by header inspection
/home/oracle/v102/dbs/ts16k.dbf

Sample unload session: data dictionary usable for DUL

 

 

    1. create a suitable “init.dul”
    2. create a control.dul
    sqlplus /nolog
    connect / as sysdba
    startup mount
    set trimspool on pagesize 0 linesize 256 feedback off
    column name format a200
    spool control.dul
    select ts#, rfile#, name from v$datafile;
    exit
edit the result
For Oracle8 a different query must be used:
   select ts#, rfile#, name from v$datafile;
    1. start DUL and bootstrap;
$ dul

Data UnLoader 10.2.1.16 - Oracle Internal Only - on Thu Jun 28 11:37:24 2007
with 64-bit io functions

Copyright (c) 1994 2007 Bernard van Duijnen All rights reserved.

 Strictly Oracle Internal use Only
DUL> bootstrap;
Probing file = 1, block = 377
. unloading table                BOOTSTRAP$      57 rows unloaded
DUL: Warning: Dictionary cache DC_BOOTSTRAP is empty
Reading BOOTSTRAP.dat 57 entries loaded
Parsing Bootstrap$ contents
DUL: Warning: Recreating file "dict.ddl"
Generating dict.ddl for version 10
 OBJ$: segobjno 18, file 1
 TAB$: segobjno 2, tabno 1, file 1
 COL$: segobjno 2, tabno 5, file 1
 USER$: segobjno 10, tabno 1, file 1
Running generated file "@dict.ddl" to unload the dictionary tables
. unloading table                      OBJ$   52275 rows unloaded
. unloading table                      TAB$    1943 rows unloaded
. unloading table                      COL$   59310 rows unloaded
. unloading table                     USER$      70 rows unloaded
Reading USER.dat 70 entries loaded
Reading OBJ.dat
 52275 entries loaded and sorted 52275 entries
Reading TAB.dat 1943 entries loaded
Reading COL.dat 59310 entries loaded and sorted 59310 entries
Reading BOOTSTRAP.dat 57 entries loaded
...
Some more messages for all the other TABLES
...
Database character set is WE8ISO8859P1
Database national character set is AL16UTF16
DUL> unload user SCOTT;
About to unload SCOTT's tables ...
. unloading table                       EMP      14 rows unloaded

Example unload session: data dictionary UNUSABLE for DUL

    1. create a suitable “init.dul” (See config guide)
    2. create a control.dul See above
    3. scan the database for segment headers and extents:
$ dul
UnLoader: Version 2.0.0.0 - Very Restricted on Tue May 16 11:10:16 1995
Copyright (c) 1994/95 Oracle Corporation, The Netherlands. All rights reserved.
DUL> scan database;
data file 1 20480 blocks scanned
data file 4 7680 blocks scanned
data file 5 512 blocks scanned
DUL>quit
    1. Restart DUL and scan the found tables for column statistics this creates a huge amount of output:
echo scan tables \; | dul > scan.out&

[ many lines here]


Object id 1601 table number 0
UNLOAD TABLE T1601_0 ( C1 NUMBER, C2 UNKNOWN, C3 UNKNOWN, C4 NUMBER, C5 DATE
        , C6 NUMBER, C7 NUMBER, C8 NUMBER )
    STORAGE ( TABNO 0 EXTENTS( FILE 1 BLOCK 10530));

Colno  Seen MaxIntSz Null% C75% C100 Num% NiNu% Dat% Rid%
    1    14        3    0%   0%   0% 100% 100%   0%   0%
    2    14        6    0% 100% 100% 100%  14%   0%  21%
    3    14        9    0% 100% 100% 100%  14%   0%   0%
    4    14        3    7%   0%   0% 100% 100%   0%   0%
    5    14        7    0%   0%   0%   0%   0% 100%   0%
    6    14        3    0%   0%   0% 100% 100%   0%   0%
    7    14        2   71%   0%   0% 100% 100%   0%   0%
    8    14        2    0%   0%   0% 100% 100%   0%   0%

"7369" "SMITH" "CLERK" "7902" "17-DEC-1980 AD 00:00:00" "800" "" "20"

"7499" "-0.000025253223" "SALESMAN" "7698" "20-FEB-1981 AD 00:00:00" "1600" "30+

0" "30"

"7521" "WARD" "SALESMAN" "7698" "22-FEB-1981 AD 00:00:00" "1250" "500" "30"

"7566" "JONES" "MANAGER" "7839" "02-APR-1981 AD 00:00:00" "2975" "" "20"

"7654" "MARTIN" "SALESMAN" "7698" "28-SEP-1981 AD 00:00:00" "1250" "1400" "30"

[ many more lines here ]

This looks familiar, use the above information and your knowledge of the emp table to compose:

UNLOAD TABLE emp ( empno number, ename char, job char, mgr number,
        hiredate date, sal number, comm number deptno number)
 STORAGE ( TABNO 0 EXTENTS( FILE 1 BLOCK 10530));
    1. use this statement to unload emp:
$ dul
UnLoader: Version 2.0.0.0 - Very Restricted on Tue May 16 11:46:33 1995
Copyright (c) 1994/95 Oracle Corporation, The Netherlands. All rights reserved.
Loaded 350 segments
Loaded 204 extents
Extent map sorted
DUL> UNLOAD TABLE emp ( empno number, ename char, job char, mgr number,
DUL 2> hiredate date, sal number, comm number deptno number)
DUL 3> STORAGE ( TABNO 0 EXTENTS( FILE 1 BLOCK 10530));
. unloading table                       EMP      14 rows unloaded
DUL>quit
 

Example unload session: Incorrect init.dul Parameters

WRONG osd_dba_file_bits size

This can generate output similar to below. Normally this should not happen since you should create a demo database and check this via the DUL documented (in html page) query.

The mismatch in DBA’s is only in the file number (first number in brackets) part. The second number, the block number, is correct.

Data UnLoader: Release 3.2.0.1 - Internal Use Only - on Wed Sep 3 10:40:33 1997
Copyright (c) 1994/95 Oracle Corporation, The Netherlands. All rights reserved.
Session altered.
Session altered.
Session altered.
Session altered.
Session altered.
DUL: Warning: Block[1][2] DBA in block mismatch [4][2]
DUL: Warning: Bad cache layer header file#=1, block#=2

DUL: Warning: Block[1][3] DBA in block mismatch [4][3]
DUL: Warning: Bad cache layer header file#=1, block#=3

...........and etc..........

WRONG osd_file_leader_size

This may create output similar to below, but many other flavours are possible. In this case we are a fixed number of blocks off. The file number is correct. The difference in the block numbers is constant.:

Data UnLoader: Release 3.2.0.1 - Internal Use Only - on Wed Sep 3 10:44:23 1997
Copyright (c) 1994/95 Oracle Corporation, The Netherlands. All rights reserved.
Session altered.
Session altered.
Session altered.
Session altered.
Session altered.

DUL: Warning: Block[1][2] DBA in block mismatch [1][3]
DUL: Warning: Bad cache layer header file#=1, block#=2

DUL: Warning: Block[1][3] DBA in block mismatch [1][4]
DUL: Warning: Bad cache layer header file#=1, block#=3

...........and etc..........

WRONG osd_c_struct_alignment

This may generate output similar to the following:

Data UnLoader: Release 3.2.0.1 - Internal Use Only - on Wed Sep 3 10:46:10 1997
Copyright (c) 1994/95 Oracle Corporation, The Netherlands. All rights reserved.
Session altered.
Session altered.
Session altered.
Session altered.
Session altered.
. unloading table OBJ$

DUL: Warning: file# 0 is out of range
DUL: Warning: Cannot read data block file#=0, block# = 262145
OS error 2: No such file or directory

DUL: Warning: file# 0 is out of range
DUL: Warning: Cannot read data block file#=0, block# = 262146
OS error 2: No such file or directory

...........and etc..........

WRONG db_block_size

The following output was generated when the db_block_size was set too small. The correct value was 4096 and it was set to 2048. Normally, the value for this parameter should be taken from the Oracle instances’s init.ora file and will not be correctly set.

Data UnLoader: Release 3.2.0.1 - Internal Use Only - on Thu Sep 4 12:38:25 1997
Copyright (c) 1994/95 Oracle Corporation, The Netherlands. All rights reserved.
Session altered.
Session altered.
Session altered.
Session altered.
Session altered.
DUL: Warning: Block[1][2] DBA in block mismatch [513][1159680]
DUL: Warning: File=1, block 2: illegal block version 2
DUL: Warning: Block[1][2] Illegal block type[0]
DUL: Warning: Bad cache layer header file#=1, block#=2

DUL: Warning: Block[1][4] DBA in block mismatch [1][2]
DUL: Warning: File[1]Block[4]INCSEQ mismatch[90268!=0]
DUL: Warning: Bad cache layer header file#=1, block#=4

DUL: Warning: Block[1][6] DBA in block mismatch [1][3]
DUL: Warning: File[1]Block[6]INCSEQ mismatch[139591710!=86360346]
DUL: Warning: Bad cache layer header file#=1, block#=6

...........and etc..........

QUOTE MISSING

If you get the following error it is caused by the data dictionary tables “USER$, OBJ$, TAB$ and COL$” not being correctly generated. To fix this error simply delete all dictv6.ddl or dictv7.ddl created .dat and .ctl files and restart.

Data UnLoader: Release 3.2.0.1 - Internal Use Only - on Wed Sep 3 10:49:30 1997
Copyright (c) 1994/95 Oracle Corporation, The Netherlands. All rights reserved.
DUL: Error: Quote missing

Salvaging data from corrupt EXP dump files – UNEXP Tutorial

If you do not know anything about the structure of a EXP dump file this can be difficult. Here is a quick explanation. Apart from the file header a dump file has MARKERS that identify the various sections. In each table section there will be SQL statements. The most interrsesting part is the create table statement, followed by the insert into table statement. The insert statement is directly followed by the bind information, (number of columns, and for each column its type and bind length and a small bit more). Then it is followed by the actual columns. Each column is preceded by a two byte length, followed by the actual column data. There are several tricks for longer columns possible. The end of the column data is marked by the special length marker OXFFFF. There is no marker for the beginning of a row. Resynching after a corruption is trial and error. Corruption are generally not immediate detectable. The format is slightly different for DIRECT export, so you will have to use the DIRECT option for DIRECT exports. The offset to be specified is the beginning of a row. In general the first one directly behind the bind array, but for optimal flexibility you can start anywhere in the row data.

The first step is to scan the dump file to find the offsets and the sql statements. Each output line starts with the offset where the item is found.

DUL>  scan dump file expdat.dmp;
0: CSET: 1 (US7ASCII)                # Character set info from the header
3: SEAL EXPORT:V10.02.01             # the Seal - the exp version tag
20: DBA SYSTEM                       # exp done as SYSTEM
8461: CONNECT SCOTT                  # section for user SCOTT
8475: TABLE "EMP"
                                     # complete create table staement
8487: CREATE TABLE "EMP" ("EMPNO" NUMBER(4, 0), "ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9), "MGR" NUMBER(4, 0), "HIREDATE" DATE, "SAL" NUMBER(7, 2),
"COMM" NUMBER(7, 2), "DEPTNO" NUMBER(2, 0))  PCTFREE 10 PCTUSED 40
INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1
FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING NOCOMPRESS

                                     # Insert statement
8829: INSERT INTO "EMP" ("EMPNO", "ENAME", "JOB", "MGR", "HIREDATE",
"SAL", "COMM", "DEPTNO") VALUES (:1, :2, :3, :4, :5, :6, :7, :8)

                                     # BIND information
8957: BIND information for 8 columns
 col[  1] type 2 max length 22
 col[  2] type 1 max length 10 cset 31 (WE8ISO8859P1) form 1
 col[  3] type 1 max length 9 cset 31 (WE8ISO8859P1) form 1
 col[  4] type 2 max length 22
 col[  5] type 12 max length 7
 col[  6] type 2 max length 22
 col[  7] type 2 max length 22
 col[  8] type 2 max length 22
Conventional export                  # Conventional means NOT DIRECT

9003: start of table data            # Here begins the first row

Now build an unexp statement from the create table statement and the direct/conventional information and the start of the column data.

UNEXP TABLE "EMP" ("EMPNO" NUMBER(4, 0), "ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9), "MGR" NUMBER(4, 0), "HIREDATE" DATE, "SAL" NUMBER(7, 2),
"COMM" NUMBER(7, 2), "DEPTNO" NUMBER(2, 0))
dump file expdat.dmp from 9003;

Unloaded 14 rows, end of table marker at 9670 # so we have our famous 14 rows

This builds the normal SQL*Loader file and a matching control file. In the output file one extra column is added, this is related to the status of the row. A P means the row is Partial, (some columns missing) R means Resynch, it is the first row after a resynchronisation. O means Overlap, the previous row had errors, but the new row partly overlaps the other one.

 

 

 

Table Of Contents
~~~~~~~~~~~~~~~~~
1. Introduction
2. Using DUL
2.1 Create an appropriate init.dul file
2.2 Create the control.dul file
2.3 Unload the object information
2.4 Invoke DUL
2.5 Rebuild the database
3. How to rebuild object definitions that are stored in the data dictionary ?
4. How to unload data when the segment header block is corrupted ?
5. How to unload data when the file header block is corrupted ?
6. How to unload data without the system tablespace ?
7. Appendix A : Where to find the executables ?
8. References

1. Introduction
~~~~~~~~~~~~~~~

This document is to explain how to use DUL rather than to give a full
explanation of Bernard’s Data UnLoader capabilities.

This document is for internal use only and should not be given to customers at
any time, Dul should always be used by or under the supervision of a support
analyst.

DUL (Data UnLoader) is intended to retrieve data from the Oracle Database that
cannot be retrieved otherwise. This is not an alternative for the export
utility or SQL*Loader. The database may be corrupted but an individual data
block used must be 100% correct. During all unloading checks are made to make
sure that blocks are not corrupted and belong to the correct segment. If a
corrupted block is detected by DUL, an error message is printed in the loader
file and to the standard output, but this will not terminate the unloading of
the next row or block.

2. Using DUL
~~~~~~~~~~~~

First you must retrieve the necessary information about the objects that exists
in the database, these statistics will be loaded into the DUL dictionary to
unload the database objects.

This information is retrieved from the USER$, OBJ$, TAB$ and COL$ tables that
were created at database creation time, they can be unloaded based on the fact
that object numbers are fixed for these tables due to the rigid nature of sql.
bsq. DUL can find the information in the system tablespace, therefor the system
tablespace datafile(s) must be included in the control file, if this datafile(s)
is not present see chapter 6.
2.1 Create an appropriate “init.dul” file
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

REM Platform specific parameters (NT)
REM A List of parameters for the most common platforms can be obtained from
REM
osd_big_endian_flag=false
osd_dba_file_bits=10
osd_c_struct_alignment=32
osd_file_leader_size=1
osd_word_size = 32

REM Sizes of dul dictionary caches. If one of these is too low startup will
REM fail.
dc_columns=2000000
dc_tables=10000
dc_objects=1000000
dc_users=400
dc_segments=100000

REM Location and filename of the control file, default value is control.dul
REM in the current directory
control_file = D:\Dul\control_orcl.dul

REM Database blocksize, can be found in the init<SID>.ora file or can be
REM retrieved by doing “show parameter %db_block_size%” in server manager
REM (svrmgr23/30/l) changes this parameter to whatever the block size is of
REM the crashed database.
db_block_size=4096

REM Can/must be specified when data is needed into export/import format.
REM this will create a file suitable to use by the oracle import utility,
REM although the generated file is completely different from a table mode
REM export generated by the EXP utility. It is a single table dump file
REM with only a create table structure statement and the table data.
REM Grants, storage clauses, triggers are not included into this dump file !
export_mode=true

REM Compatible parameter must be specified an can be either 6, 7 or 8
compatible=8

REM This parameter is optional and can be specified on platforms that do
REM not support long file names (e.g. 8.3 DOS) or when the file format that
REM DUL uses “owner_name.table_name.ext” is not acceptable. The dump files
REM will be something like dump001.ext, dump002.ext, etc in this case.
file = dump

A complete list can be obtained at
html section “DUL Parameters” although this init.dul file will work in most
cases and contains all accurate parameters to succesfully complete the
unloading.

2.2 Create the “control.dul” file
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

A good knowledge about the logical tablespace and physical datafile
structure is needed or you can do the following queries when the database
is mounted :

Oracle 6, 7
———–
> connect internal
> spool control.DUL
> select * from v$dbfile;
> spool off

Oracle 8
——–
> connect internal
> spool control.DUL
> select ts#, rfile#, name from v$datafile;
> spool off

Edit the spool file and change, if needed, the datafile location and stripe
out unnecessary information like table headers, feedback line, etc…
A sample control file looks something like this :

REM Oracle7 control file
1 D:\DUL\DATAFILE\SYS1ORCL.DBF
3 D:\DUL\DATAFILE\DAT1ORCL.DBF
7 D:\DUL\DATAFILE\USR1ORCL.DBF

REM Oracle8 control file
0 1 D:\DUL\DATAFILE\SYS1ORCL.DBF
1 2 D:\DUL\DATAFILE\USR1ORCL.DBF
1 3 D:\DUL\DATAFILE\USR2ORCL.DBF
2 4 D:\DUL\DATAFILE\DAT1ORCL.DBF

Note : Each entry can contain a part of a datafile, this can be useful when
you need to split datafiles that are too big for DUL, so that each
part is smaller than for example 2GB. For example :

REM Oracle8 with a datafile split into multiple parts, each part is
REM smaller than 1GB !
0 1 D:\DUL\DATAFILE\SYS1ORCL.DBF
1 2 D:\DUL\DATAFILE\USR1ORCL.DBF startblock 1 endblock 1000000
1 2 D:\DUL\DATAFILE\USR1ORCL.DBF startblock 1000001 endblock 2000000
1 2 D:\DUL\DATAFILE\USR1ORCL.DBF startblock 2000001 endblock 2550000
2.3 Unload the object information
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Start the DUL utility with the appropriate ddl (Dul Description Language)
script. There are 3 scripts available to unload the USER$, OBJ$, TAB$ and
COL$ tables according to the database version.

Oracle6 :> dul8.exe dictv6.ddl
Oracle7 :> dul8.exe dictv7.ddl
Oracle8 :> dul8.exe dictv8.ddl

Data UnLoader: Release 8.0.5.3.0 – Internal Use Only – on Tue Jun 22 22:19:
Copyright (c) 1994/1999 Bernard van Duijnen All rights reserved.

Parameter altered
Session altered.
Parameter altered
Session altered.
Parameter altered
Session altered.
Parameter altered
Session altered.
. unloading table OBJ$ 2271 rows unloaded
. unloading table TAB$ 245 rows unloaded
. unloading table COL$ 10489 rows unloaded
. unloading table USER$ 22 rows unloaded
. unloading table TABPART$ 0 rows unloaded
. unloading table IND$ 274 rows unloaded
. unloading table ICOL$ 514 rows unloaded
. unloading table LOB$ 13 rows unloaded

Life is DUL without it

This will unload the data of the USER$, OBJ$, TAB$ and COl$ data dictionary
tables into SQL*Loader files , this can not be manipulated into dump files
of the import format. The parameter export_mode = false is hardcoded into
the ddl scripts and can not be changed to the value “true” since this will
cause DUL to fail with the error:

. unloading table OBJ$
DUL: Error: Column “DATAOBJ#” actual size(2) greater than length in column
definition(1)
………….etc……………
2.4 Invoke DUL
~~~~~~~~~~~~~~

Start DUL in interactive mode or you can prepare a scripts that contains all
the ddl commands to unload the necessary data from the database. I will
describe in this document the most used commands, this is not a complete list
of possible parameters that can be specified. A complete list can be found at
section “DDL Description”.

DUL> unload database;
=> this will unload the entire database tables(includes sys’tables as well)

DUL> unload user <username>;
=> this will unload all the tables owned by that particullarly user.

DUL> unload table <username.table_name>;
=> this will unload the specified table owned by that username

DUL> describe <owner_name.table_name>;
=> will represent the table columns with there relative pointers to the
datafile(s) owned by the specified user.

DUL> scan database;
=> Scans all blocks of all data files.
Two files are generated:
1: seg.dat information of found segment headers (index/cluster/table)
(object id, file number, and block number).
2: ext.dat information of contiguous table/cluster data blocks.
(object id(V7), file and block number of segment header (V6),
file number and block number of first block,
number of blocks, number of tables)

DUL> scan tables;
=> Uses seg.dat and ext.dat as input.
Scans all tables in all data segments (a header block and at least one
matching extent with at least 1 table).
2.5 Rebuild the database
~~~~~~~~~~~~~~~~~~~~~~~~

Create the new database and use import or SQL*Loader to restore the data
retrieved by DUL. Note that when you only unloaded the data that table
structures, indexation, grants, PL/SQL and triggers will no longer exist in
the new database. To obtain an exactly same copy of the database as before
you will need to rerun your creation scripts for the tables, indexes, PL/SQL,
etc.

If you don’t have these scripts then you will need to perform the steps
described in section 3 of this document.
3. How to rebuild object definitions that are stored in the data dictionary
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

You want to rebuild PL/SQL(packages, procedures, functions or triggers), grants,
indexes, constraints or storage clauses(old table structure) with DUL. This can
be done but is a little bit tricky. You need to unload the relevant data
dictionary tables using DUL and then load these tables into a healthy database,
be sure to use a different user than sys or (system). Loading the data
dictionary tables of the crashed database into the healthy database dictionary
could corrupt the healthy database as well.

Detailed explanation to retrieve for example pl/sql packages / procedures /
functions from a corrupted database :

1) Follow the steps explained in the “Using DUL” section and unload the data
dictionary table “source$”

2) Create a new user into a healthy database and specify the desired default
and temporary tablespace.

3) Grant connect, resource, imp_full_database to the new user.

4) Import/load the table “source$” into the new created schema:

e.g.: imp80 userid=newuser/passw file=d:\dul\scott_emp.dmp
log=d:\dul\impemp.txt full=y

5) You can now query from the table <newuser.source$> to rebuild the pl/sql
procedures/functions from the corrupted database. Scripts can be found on
WebIv to generate such PL/SQL creation scripts.

The same steps can be followed to recreate indexes, constraints, and storage
parameters or to regrant privileges to the appropiate users. Please notice that
you always need to use a script of some kind that can recreate the objects and
include all the features of the crashed database version. For example : when
the crashed database is of version 7.3.4 and you have several bitmap indexes,
if you would use a script that supports version 7.3.2 or prior, then you won’t
be able to recreate the bitmap indexes succesful !
4. How to unload data when the segment header block is corrupted
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

When DUL can’t retrieve data block information on the normal way, it can scan
the database to create its own segment/extent map. The procedure of scanning
the database is necessary to unload the data from the datafiles.
(to illustrate this example I copied an empty block ontop of the segment header
block)

1) Create an appropiate “init.dul” (see 2.1) and “control.dul” (see 2.2) file.

2) Unload the table. This will fail and indicate that there is a corruption in
the segment header block:

DUL> unload table scott.emp;
. unloading table EMP
DUL: Warning: Block is never used, block type is zero
DUL: Error: While checking tablespace 6 file 10 block 2
DUL: Error: While processing block ts#=6, file#=10, block#=2
DUL: Error: Could not read/parse segment header
0 rows unloaded

3) run the scan database command :

DUL> scan database;
tablespace 0, data file 1: 10239 blocks scanned
tablespace 6, data file 10: 2559 blocks scanned

4) Indicate to DUL that it should use its own generated extent map rather than
the segment header information.

DUL> alter session set use_scanned_extent_map = true;
Parameter altered
Session altered.
DUL> unload table scott.emp;
. unloading table EMP 14 rows unloaded
5. How to unload data when the datafile header block is corrupted
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

A corruption in the datafile header block is always listed at the moment you
open the database this is not like a header segment block corruption (see point
4) where the database can be succesfully openend and the corruption is listed
at the moment you do a query of a table. Dul has no problems with recovering
from such situations although there are other alternatives of recovering from
this situation like patching the datafile header block.

The error you will receive looks something like :
ORACLE instance started.
Total System Global Area 11739136 bytes
Fixed Size 49152 bytes
Variable Size 7421952 bytes
Database Buffers 4194304 bytes
Redo Buffers 73728 bytes
Database mounted.
ORA-01122: database file 10 failed verification check
ORA-01110: data file 10: ‘D:\DATA\TRGT\DATAFILES\JUR1TRGT.DBF’
ORA-01251: Unknown File Header Version read for file number 10
6. How to unload data without the system tablespace
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

If datafiles are not available for the system tablespace the unload can still
continue but the object information can’t be retrieved from the data dictionary
tables USER$, OBJ$, TAB$ and COL$. So ownername, tablename and columnnames will
not be loaded into the DUL dictionary. Identifying the tables can be an
overwhelming task and a good knowledge of the RDBMS internals are needed here.
First of all you need a good knowledge of your application and it’s tables.
Column types can be guessed by DUL, but table and column names will be lost.

Any old system tablespace from the same database (may be weeks old) can be a
great help !

1) Create the “init.dul” file and the “control.dul” file as explained in above
steps 1 and 2. In this case the control file will contain all the datafiles
from which you want to restore but it doesn’t require the system tablespace
information.

2) Then You invoke dul and type the following command :

DUL> scan database;
data file 6 1280 blocks scanned

This will build the extent and segment map. Probably the dul command
interpreter will be terminated as well.

3) reinvoke the dul command interpreter and do the following :

Data UnLoader: Release 8.0.5.3.0 – Internal Use Only – on Tue Aug 03 13:33:

Copyright (c) 1994/1999 Oracle Corporation, The Netherlands. All rights res
Loaded 4 segments
Loaded 2 extents
Extent map sorted
DUL> alter session set use_scanned_extent_map = true;
DUL> scan tables; (or scan extents;)

Scanning tables with segment header

Oid 1078 fno 6 bno 2 table number 0

UNLOAD TABLE T_O1078 ( C1 NUMBER, C2 UNKNOWN, C3 UNKNOWN )
STORAGE ( TABNO 0 EXTENTS( FILE 6 BLOCK 2));
Colno Seen MaxIntSz Null% C75% C100 Num% NiNu% Dat% Rid%
1 4 2 0% 0% 0% 100% 100% 0% 0%
2 4 10 0% 100% 100% 100% 0% 0% 0%
3 4 8 0% 100% 100% 100% 0% 0% 50%
“10” “ACCOUNTING” “NEW YORK”
“20” “RESEARCH” “DALLAS”
“30” “SALES” “CHICAGO”
“40” “OPERATIONS” “BOSTON”

Oid 1080 fno 6 bno 12 table number 0

UNLOAD TABLE T_O1080 ( C1 NUMBER, C2 UNKNOWN, C3 UNKNOWN, C4 NUMBER,
C5 DATE, C6 NUMBER, C7 NUMBER, C8 NUMBER )
STORAGE ( TABNO 0 EXTENTS( FILE 6 BLOCK 12));
Colno Seen MaxIntSz Null% C75% C100 Num% NiNu% Dat% Rid%
1 14 3 0% 0% 0% 100% 100% 0% 0%
2 14 6 0% 100% 100% 100% 0% 0% 21%
3 14 9 0% 100% 100% 100% 0% 0% 0%
4 14 3 7% 0% 0% 100% 100% 0% 0%
5 14 7 0% 0% 0% 0% 0% 100% 0%
6 14 3 0% 0% 0% 100% 100% 0% 0%
7 14 2 71% 0% 0% 100% 100% 0% 0%
8 14 2 0% 0% 0% 100% 100% 0% 0%
“7369” “SMITH” “CLERK” “7902” “17-DEC-1980 AD 00:00:00” “800” “” “20”
“7499” “ALLEN” “SALESMAN” “7698” “20-FEB-1981 AD 00:00:00” “1600” “300” “30”
“7521” “WARD” “SALESMAN” “7698” “22-FEB-1981 AD 00:00:00” “1250” “500” “30”
“7566” “JONES” “MANAGER” “7839” “02-APR-1981 AD 00:00:00” “2975” “” “20”
“7654” “MARTIN” “SALESMAN” “7698” “28-SEP-1981 AD 00:00:00” “1250” “1400” “30”

Note : it might be best that you redirect the output to a logfile since
commands like the “scan tables” can produce a lot of output.
On Windows NT you can do the following command :
C:\> dul8 > c:\temp\scan_tables.txt
scan tables;
exit;

4) Identify the lost tables from the output of step 3; if you look carefully to
the output above then you will notice that the unload syntax is already given
but that the table name will be of the format t_0<objectno> and the column
names will be of the format C<no>; datatypes will not be an exact match of
the datatype as it was before.

Look especially for strings like “Oid 1078 fno 6 bno 2 table number 0” where:
oid = object id, will be used to unload the object
fno = (data)file number
bno = block number

5) Unload the identified tables with the “unload table” command :

DUL> unload table dept (deptno number(2), dname varchar2(14),
loc varchar2(13)) storage (OBJNO 1078)
Unloading extent(s) of table DEPT 4 rows.

沪公网安备 31010802001379号

TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569