咨询微信: dbservice1234 7 x 24 在线支持!

Oracle Using DUL : Some Examples

Oracle Using DUL : Some Examples

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
Copyright (c) 1994/98 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$ 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
Copyright (c) 1994/98 Bernard van Duijnen All rights reserved.
 
 
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
Copyright (c) 1994/98 Bernard van Duijnen All rights reserved.
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
Copyright (c) Oracle Corporation 1979, 1996. All rights reserved.
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.