Oracle Using DUL : Some Examples

Posted by PDSERVICE on Nov 19, 2015 In

Oracle Using DUL : Some Examples

The purpose of this note is to outline what is required in order to setup

and use DUL and situations in which DUL is useful. - Handling

Block corruptions (Oracle 7) details what should be done under ora-1578

situations so refer to this first before considering DUL.

SCOPE & APPLICATION

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

This note serves an a quick reference regarding a number of situations for

which DUL will likely be able to pull data out of a 'corrupted' database;

other methods may have failed or the database may be in such a state that it

cannot be opened - and no backups exist. The note is a secondary reference

regarding DUL and therefore users of DUL should refer to the following site

For the purposes of this exercise, Release 8.0.0.2.5 of DUL was used. The

examples outlined here were run on Sun Solaris 2.5.1 against a 7.3.4.2

Oracle database created on the same platform.

DISCLAIMER

==========

For more details please refer to the DUL Users' and Configuration Guide

V8.0.0.3.

Until DUL is officially released as part of the rdbms server bundle or a

public announcement is made regarding DUL, it should NOT be used outwith

support.

Running DUL indicates the current state of the product :

"Data UnLoader: Release 8.0.0.2.5 - Internal Use Only" !

Contents

========

1. Corrupt segment header block

1.1. Set up prior to running DUL

1.2. Running DUL

2. Other examples

etc.

1. Corrupt segment header block

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

Consider the following table - which has a corrupted segment header. There

are 1000 rows in the table. , we are unable to pull the data out of the table.

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

SVRMGR> describe dul_test;

Column Name Null? Type

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

COL1 NUMBER

COL2 CHAR(200)

SVRMGR> select count(*) from dul_test;

COUNT(*)

----------

1000

1 row selected.

SVRMGR>

SVRMGR> select header_file, header_block from dba_segments where segment_name = 'DUL_TEST';

HEADER_FIL HEADER_BLO

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

3 177

1 row selected.

SVRMGR>

SVRMGR> select file_id, block_id from dba_extents where segment_name = 'DUL_TEST';

FILE_ID BLOCK_ID

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

3 177

3 42

3 47

3 182

3 197

3 217

SVRMGR> select * from dul_test;

select * from dul_test

ORA-01578: ORACLE data block corrupted (file # 3, block # 177)

ORA-01110: data file 3: '/export/home/mmckella/malcdb/mm734db2/dbf2/users.dbf'

SVRMGR>

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

The trace file indicates that extent of the problem : Block type 16 = unlimited

data segment header :

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

*** SESSION ID:(8.3) 1998.10.16.08.47.33.000

***

Corrupt block dba: 0x0c0000b1 file=3. blocknum=177. found during buffer read

on disk type:16. ver:1. dba: 0x0c0000b1 inc:0x00000034 seq:0x00000000 incseq:0x003400d6

Reread of block=c0000b1 file=3. blocknum=177. found same corupted data

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

Only way forward is to drop the object and recreate or restore from a backup.

In the event that this is not possible, i.e. where there isn't a backup (likely

user negligence), DUL can be used as follows.

1.1. Set up prior to running DUL

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

init.dul + control.dul need to be created. The following example (Sun Solaris)

may change according to your platform.

+ init.dul

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

osd_big_endian_flag=true

osd_dba_file_bits=6

osd_c_struct_alignment=32

osd_file_leader_size=1

db_block_size=2048

compatible=7

export_mode=true

file=out

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

For more details refer to the DUL User and Configuration guide relating to your

platform.

+ control.dul - point to the datafile which are available (5 in my case)

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

1 /export/home/mmckella/malcdb/mm734db2/dbf/systemmm734db2.dbf

2 /export/home/mmckella/malcdb/mm734db2/dbf/temp.dbf

3 /export/home/mmckella/malcdb/mm734db2/dbf2/users.dbf

4 /export/home/mmckella/malcdb/mm734db2/dbf/rbs.dbf

5 /export/home/mmckella/malcdb/mm734db2/dbf/new.dbf

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

1.2. Running DUL

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

+ Where Oracle's data dictionary is available, unload it as follows :

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

uks606[mm734db2]> dul dictv7.ddl

Data UnLoader: Release 8.0.0.2.5 - Internal Use Only - on Fri Oct 16 09:08:19 1998

Parameter altered

Session altered.

Parameter altered

Session altered.

Parameter altered

Session altered.

Parameter altered

Session altered.

. unloading table OBJ$ 1033 rows unloaded

. unloading table TAB$ 85 rows unloaded

. unloading table COL$ 4050 rows unloaded

. unloading table USER$ 13 rows unloaded

Life is DUL without it

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

+ Now invoke DUL and scan the database.

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

uks606[mm734db2]> dul

Data UnLoader: Release 8.0.0.2.5 - Internal Use Only - on Fri Oct 16 09:08:48 1998

3408086!=3407872]

DUL: Error: While checking file 3 block 177

data file 3 5120 blocks scanned

data file 4 5120 blocks scanned

data file 5 512 blocks scanned

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

+ Now invoke DUL and attempt to unload the table we have a problem with. This

does not work which will not work.

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

uks606[mm734db2]> dul

Data UnLoader: Release 8.0.0.2.5 - Internal Use Only - on Fri Oct 16 09:10:35 1998

DUL: Warning: Recreating file "dul.log"

Loaded 1033 objects

Loaded 85 tables

Loaded 4050 columns

Loaded 13 users

Loaded 182 segments

Loaded 274 extents

Extent map sorted

DUL> unload table scott.dul_test;

. unloading table DUL_TEST

DUL: Error: Processing block that has been marked soft corrupt

DUL: Error: While processing block file#=3, block#=177

DUL: Error: INCSEQ mismatch[3408086!=3407872]

DUL: Error: While checking file 3 block 177

DUL: Error: While processing block file#=3, block#=177

DUL: Error: Could not read/parse segment header

0 rows unloaded

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

+ In this case (segment header corruption), we need to use the extent map

produced by DUL - since that it the segment header is corrupted. Do this as

follows :

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

DUL> alter session set use_scanned_extent_map = true;

Parameter altered

Session altered.

DUL> unload table scott.dul_test;

. unloading table DUL_TEST 1000 rows unloaded

DUL> exit

DUL 2>

Life is DUL without it

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

+ Restart the instance

+ connect as table owner (scott/tiger in this case); drop the table; and

do the import.

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

uks606[mm734db2]> imp userid=scott/tiger file=out002.dmp tables=dul_test;

Import: Release 7.3.4.2.0 - Production on Fri Oct 16 09:17:20 1998

Connected to: Oracle7 Server Release 7.3.4.2.0 - Production

With the distributed, replication and parallel query options

PL/SQL Release 2.3.4.2.0 - Production

Export file created by EXPORT:V07.00.07 via conventional path

Warning: the objects were exported by Bernard's DUL, not by you

. importing Bernard's DUL's objects into SCOTT

. . importing table "DUL_TEST" 1000 rows imported

Import terminated successfully without warnings.

uks606[mm734db2]>

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

Table now recovered.

You are here

Oracle Using DUL : Some Examples

Oracle Using DUL : Some Examples