May 7: DUDE, Where's My Data?
One of my highlights of the Hotsos Symposium 2006 was meeting Kurt Van Meerbeeck. There are some people I think I'm going to be friends with as soon as I meet them. Kurt is one of the those guys. It turns out we have quite a lot of tastes and views in common (and I don't just mean smoking - something that Kurt has given up now but certainly made an impressive job of in the past. Read that page and you'll see what I mean!). However, unlike me, he's an ber-geek! He won't like me for saying this because he's a pretty modest guy who keeps a low profile, but he's one of the few people I've met who operates at the same level as the best games programmers I used to work with.
He's the author of a tool that I'd been vaguely aware of in the past called jDUL which was a tool designed to do a similar job to Oracle's own tool, DUL. I think I probably first heard about it on Pete Finnigan's website. It's been renamed now to DUDE - Database Unloading by Data Extraction.
DUDE and other DUL-type utilities are tools of last resort when you've been a bad DBA and find yourself with a corrupt database and inadequate backups. Reading data directly from your data files (because the database is unlikely to be open at this stage), it gives you one last chance of recovering the situation by building SQL*Loader compatible data files containing your data. Hopefully you'll then be able to load the data into a working database. It's not a cool alternative to exports or fast data unloaders, but a serious recovery tool.
I offered to help Kurt test it out on my server at home because it would give me an opportunity to learn more about the tool, should I ever need to use it. I was just interested in it, too. That involved Kurt building a special time-limited server-specific version of the tool, which is one way you can buy it. I'll come back to that later. During the testing the main things I learned are
1) You need to do a bit of up-front configuration. It's not too hard and it's all in a single dude.cfg file but, let's face it, we're not in SQL Developer territory here
2) It works best with Sun's JDK rather than some Linux open source variant. This one had us stumped for ages - firing emails back and forward until I got Kurt access to my server and he solved it the same evening. He does some very smart security obfuscation stuff with Java classes and the like which my Linux Java Runtime Environment didn't like.
3) Once the configuration has been done it's a breeze, once you get used to having a space and semi-colon at the end of all the command lines. Here's how it looks (and remember, all of this is happening on a closed or corrupt database)
Most people, most of the time, aren't going to need a tool like DUDE but every DBA should at least know that it exists in case they ever need it. If you do, there are several options. One is to licence DUDE for a short while and do the recovery yourself. That will involve Kurt putting together a server-specific build for you to run yourself that is time-limited. You could also get Kurt or the guys at Miracleto do the recovery for you. Now that I've played around with it for a while I would suggest you go for the latter approach when your business depends on it. (Then again, if your business depends on your unrecoverable database, you might ask yourself how you got in that situation in the first place.) Kurt is a super-smart guy with insane levels of commitment to his tool and your data. When we were at Hotsos, he would be working late into the night to help people out in different timezones. I won't go into details of costs, but the DUDE approach is likely to be substantially cheaper than getting Oracle consulting in to do the job. In any case, I think the rates are more than reasonable for those situations when the doo-dah hits the fan.
Just to emphasise, you can always go to Oracle Consulting for this service, but DUDE is another option.
In closing, I wanted to point out that the positive nature of this review is entirely down to my personal experiences and perhaps a little friendship mixed in with the respect I have for Kurt and absolutely nothing, I repeat nothing to do with being bribed
Trust me, Kurt offered me this because he was having some test T-Shirts printed and because he's a warm, friendly guy. It was nothing to do with this blog. Having said that, I'm absurdly chuffed with this T-shirt, which only goes to prove I might be a bit more of a geek than I thought I was
Kudos to Kurt - I wish I'd written something like DUDE in my spare time!
P.S. To turn a common British joke around a bit - there are some good things to come out of Belgium apart from chocolate and beer
He's the author of a tool that I'd been vaguely aware of in the past called jDUL which was a tool designed to do a similar job to Oracle's own tool, DUL. I think I probably first heard about it on Pete Finnigan's website. It's been renamed now to DUDE - Database Unloading by Data Extraction.
DUDE and other DUL-type utilities are tools of last resort when you've been a bad DBA and find yourself with a corrupt database and inadequate backups. Reading data directly from your data files (because the database is unlikely to be open at this stage), it gives you one last chance of recovering the situation by building SQL*Loader compatible data files containing your data. Hopefully you'll then be able to load the data into a working database. It's not a cool alternative to exports or fast data unloaders, but a serious recovery tool.
I offered to help Kurt test it out on my server at home because it would give me an opportunity to learn more about the tool, should I ever need to use it. I was just interested in it, too. That involved Kurt building a special time-limited server-specific version of the tool, which is one way you can buy it. I'll come back to that later. During the testing the main things I learned are
1) You need to do a bit of up-front configuration. It's not too hard and it's all in a single dude.cfg file but, let's face it, we're not in SQL Developer territory here
2) It works best with Sun's JDK rather than some Linux open source variant. This one had us stumped for ages - firing emails back and forward until I got Kurt access to my server and he solved it the same evening. He does some very smart security obfuscation stuff with Java classes and the like which my Linux Java Runtime Environment didn't like.
3) Once the configuration has been done it's a breeze, once you get used to having a space and semi-colon at the end of all the command lines. Here's how it looks (and remember, all of this is happening on a closed or corrupt database)
[oracle@ISP4400 dude]$ java -Xmx512m dudeThe result is a SQL*Loader data file like this. (Note that this is a table I use during PX testing which contains a couple of numeric columns and a long, random string. So that garbled-looking data is, in fact, the real thing.
License information :
User : Doug Burns
Email : dougburns@yahoo.com
Company : Independent
Phone : +44-495-xxxxxxx
Fax : +32-15-xxxxxxx
Host : ISP4400.localdomain (127.0.0.1)
Platform : Linux i386
Valid for 30 days.
Current size logfile = 1274037 bytes
jDUL/DUDE : 2.3.3
NOT FOR DISTRIBUTION 2001 - 2005 Kurt Van Meerbeeck - ORA600 -
dude@ora600.org
FOR EDUCATIONAL USE ONLY !
PERSONAL COPY of Doug Burns / Independent / dougburns@yahoo.com
Roads ? Where we're going we don't need roads ...
DUDE> Initialising ...
DUDE> DEMO MODE - restrictions apply !
DUDE> Init : creating filenumber map ...
DUDE> Scanning tablespace SYSTEM : BLOCKSIZE = 8192
DUDE> File : /home/oracle/product/10.2.0/oradata/TEST1020/system01.dbf
resolves to number : 1
DUDE> Scanning tablespace DUDE : BLOCKSIZE = 8192
DUDE> File : /u01/oradata/TEST1020/dude_01.dbf resolves to number :
10
DUDE> Init : checking datafile blocksizes ...
DUDE> DATAFILE =
/home/oracle/product/10.2.0/oradata/TEST1020/system01.dbf OK : BLOCKSIZE
= 8192 equals internal blocksize !
DUDE> DATAFILE = /u01/oradata/TEST1020/dude_01.dbf OK : BLOCKSIZE =
8192 equals internal blocksize !
DUDE> Init done.
DUDE> dump dictionary ;
DUDE> Dumping OBJ$...
VALID1 = 1146002293000 tsl = 1145981242000 VALID2 = 1148594293000
ORA600:10 - timestamps do not match probed timestampsVALID1 =
1146002293000 tsl = 1145981242000 VALID2 = 1148594293000
ORA600:10 - timestamps do not match probed timestampsVALID1 =
1146002293000 tsl = 1145981243000 VALID2 = 1148594293000
ORA600:10 - timestamps do not match probed timestampsVALID1 =
1146002293000 tsl = 1145981243000 VALID2 = 1148594293000
ORA600:10 - timestamps do not match probed timestampsVALID1 =
1146002293000 tsl = 1145981242000 VALID2 = 1148594293000
ORA600:10 - timestamps do not match probed timestampsVALID1 =
1146002293000 tsl = 1145981242000 VALID2 = 1148594293000
ORA600:10 - timestamps do not match probed timestampsDUDE> Reading
blocks done !
DUDE> Please wait for output stream to finish ...
DUDE>
Normal rows = 10533
Migrated rows = 0
Chained rows = 0
Deleted rows = 16 (not extracted!)
Skipped rows = 0 (not extracted!)
DUDE> Done !
DUDE> Dumping TAB$...
DUDE> Reading blocks done !
DUDE> Please wait for output stream to finish ...
DUDE> Done !
DUDE> Dumping COL$...
DUDE> Reading blocks done !
DUDE> Please wait for output stream to finish ...
DUDE> Done !
DUDE> Dumping USER$...
DUDE> Reading blocks done !
DUDE> Please wait for output stream to finish ...
DUDE> Done !
DUDE> create dictionary ;
DUDE> show users ;
DUDE> user# = 30 - name = PERFSTAT
DUDE> user# = 19 - name = DIP
DUDE> user# = 18 - name = SCHEDULER_ADMIN
DUDE> user# = 17 - name = GLOBAL_AQ_USER_ROLE
DUDE> user# = 16 - name = AQ_USER_ROLE
DUDE> user# = 15 - name = AQ_ADMINISTRATOR_ROLE
DUDE> user# = 14 - name = LOGSTDBY_ADMINISTRATOR
DUDE> user# = 13 - name = GATHER_SYSTEM_STATISTICS
DUDE> user# = 12 - name = RECOVERY_CATALOG_OWNER
DUDE> user# = 11 - name = OUTLN
DUDE> user# = 10 - name = IMP_FULL_DATABASE
DUDE> user# = 9 - name = EXP_FULL_DATABASE
DUDE> user# = 8 - name = DELETE_CATALOG_ROLE
DUDE> user# = 28 - name = PLUSTRACE
DUDE> user# = 7 - name = EXECUTE_CATALOG_ROLE
DUDE> user# = 27 - name = TESTUSER
DUDE> user# = 6 - name = SELECT_CATALOG_ROLE
DUDE> user# = 26 - name = WM_ADMIN_ROLE
DUDE> user# = 5 - name = SYSTEM
DUDE> user# = 25 - name = WMSYS
DUDE> user# = 4 - name = DBA
DUDE> user# = 24 - name = DBSNMP
DUDE> user# = 3 - name = RESOURCE
DUDE> user# = 23 - name = OEM_MONITOR
DUDE> user# = 2 - name = CONNECT
DUDE> user# = 22 - name = OEM_ADVISOR
DUDE> user# = 1 - name = PUBLIC
DUDE> user# = 21 - name = TSMSYS
DUDE> user# = 0 - name = SYS
DUDE> user# = 20 - name = HS_ADMIN_ROLE
DUDE> create blockmap for tablespace DUDE ;
DUDE> ID := 0 BLOCKMAPPER for TABLESPACE NAME = DUDE
OFFSET = 0
ASSM = true
BIGFILE = false
BLOCKSIZE = 8192
NUMBER = 10 FILENAME = /u01/oradata/TEST1020/dude_01.dbf IBS = 8192
DUDE> Datafile /u01/oradata/TEST1020/dude_01.dbf : start reading
blocks ...
DUDE> Datafile /u01/oradata/TEST1020/dude_01.dbf : reading blocks done
!
DUDE> Please wait for output stream to finish ...
DUDE> Done !
DUDE> dump tablespace DUDE ;
DUDE> Found table : OWNER = TESTUSER NAME = DUDE
OBJECTID = 10751 DATA OBJECTID = 10751
COL 1 [1] PK_ID NUMBER
COL 2 [2] NUM_CODE NUMBER
COL 3 [3] STR_PAD VARCHAR2
start dumping ...
VALID1 = 1146002293000 tsl = 1145981246000 VALID2 = 1148594293000
ORA600:10 - timestamps do not match probed timestampsVALID1 =
1146002293000 tsl = 1145981246000 VALID2 = 1148594293000
ORA600:10 - timestamps do not match probed timestamps
DUDE> Reading blocks done !
DUDE> Please wait for output stream to finish ...
DUDE>
Normal rows = 1599984
Migrated rows = 0
Chained rows = 0
Deleted rows = 0 (not extracted!)
Skipped rows = 0 (not extracted!)
DUDE> Done !
DUDE> OBJECTID is not a table :10750
"1","1","orwRVUTmTQMKHOVkPhRAvgLtvYTCLnVevaBxXrnXoAWdfIIZXyRdVFmkPPTgHoQOrOHeGIHDhoeaoTTSWlggtIOLqVThere's a lot more information on Kurt's website, including a full DUDE primer in PDF format.
dfgDlfOeIl"|||
"2","2","RQdFmwEKZGCmBzMAxfZhjlJmMtjwTnLGkxXJikqSORxsVqgdjKNxQSYeJNBFkPQJDavkkCTLwWaEcpQwExujFjktLY
OSAGbRrtcF"|||
"3","3","tzpghoCsIoaOLvppzADOzKClJudoQDKVpFcmlxbNYKIWUDIzHegrDfrCtSUyrNNxuCAwsQDXLLkTOKkRZKgTiBXjYw
kcKpLSMITM"|||
"4","4","ULXTqRLRqbUTcARZPlgTzkZDGOqYTEgiWIkPbrlYdrCBxCCaarlMstPaaOrJVrAMkEiEsDytdiUxmcdhGckMxwJwXc
ZwjmkOwkFq"|||
"5","5","FAUEaGJfVtHhrbVSnJIIgHBbpjIZTansdxlhjtrSpRdPEuPXvtXdsGRvjcdtgwXzrsOaVNldbmXlCQylcfDLhEnNZO
bQqORhKsqZ"|||
Most people, most of the time, aren't going to need a tool like DUDE but every DBA should at least know that it exists in case they ever need it. If you do, there are several options. One is to licence DUDE for a short while and do the recovery yourself. That will involve Kurt putting together a server-specific build for you to run yourself that is time-limited. You could also get Kurt or the guys at Miracleto do the recovery for you. Now that I've played around with it for a while I would suggest you go for the latter approach when your business depends on it. (Then again, if your business depends on your unrecoverable database, you might ask yourself how you got in that situation in the first place.) Kurt is a super-smart guy with insane levels of commitment to his tool and your data. When we were at Hotsos, he would be working late into the night to help people out in different timezones. I won't go into details of costs, but the DUDE approach is likely to be substantially cheaper than getting Oracle consulting in to do the job. In any case, I think the rates are more than reasonable for those situations when the doo-dah hits the fan.
Just to emphasise, you can always go to Oracle Consulting for this service, but DUDE is another option.
In closing, I wanted to point out that the positive nature of this review is entirely down to my personal experiences and perhaps a little friendship mixed in with the respect I have for Kurt and absolutely nothing, I repeat nothing to do with being bribed
Trust me, Kurt offered me this because he was having some test T-Shirts printed and because he's a warm, friendly guy. It was nothing to do with this blog. Having said that, I'm absurdly chuffed with this T-shirt, which only goes to prove I might be a bit more of a geek than I thought I was Kudos to Kurt - I wish I'd written something like DUDE in my spare time!
P.S. To turn a common British joke around a bit - there are some good things to come out of Belgium apart from chocolate and beer
Trackbacks
Trackback specific URI for this entry
No Trackbacks
Comments
Display comments as
(Linear | Threaded)
#2 - Doug Burns said:
2006-05-07 13:37 - (Reply)
Tell me about it
(Although I would have needed a V6-compatible version and I'm not sure there ever was one)
#4 - Doug Burns said:
2007-07-15 15:25 - (Reply)
For more information of a DUDE success story - see here
Oh, and my psoriasis is clearing up, so I might be able to start wearing my T-shirt out in public soon
