Extracting Oracle DDL from online redo logs or archived redo logs with DDLDUMP

The oracle-l mailing list made me try a new tool, created by David Litchfield. It’s called DDLDUMP V. 0.1 and extracts DDL from redo logs or archived redo logs, the output format is XML. Since it’s a windows .exe file for now, I had to use wine to test it, but it works flawlessly, and David promised to provide Linux binaries as well.

Look here:


> wine ddldump.exe /opt/oracle/flash_recovery_area/KLM11G/archivelog/2010_06_30/o1_mf_1_906_62pdfkdf_.arc ddl
fixme:heap:HeapSetInformation (nil) 1 (nil) 0
<?xml version="1.0"?><LOG>
<FILENAME>/opt/oracle/flash_recovery_area/KLM11G/archivelog/2010_06_30/o1_mf_1_906_62pdfkdf_.arc</FILENAME>
<database_sid>KLM11G</database_sid>
<version>11.2</version>
<ltimestamp>04/09/2009 10:00:02</ltimestamp>
<blocksize>512</blocksize>
<nab>92906</nab>
<lowscn>5993511</lowscn>
<nextscn>5994352</nextscn>
<ENTRIES>
<ENTRY>
<TIMESTAMP>30/06/2010 13:45:30</TIMESTAMP>
<RDRCOFST>0x00008F44</RDRCOFST>
<CHVCOFST>0x00008F74</CHVCOFST>
<SESSION_USER>SYS</SESSION_USER>
<CURRENT_USER>SYS</CURRENT_USER>
<SQL_STATETMENT>ALTER DATABASE OPEN</SQL_STATETMENT>
<SCHEMA></SCHEMA>
<OBJECT></OBJECT>
</ENTRY>
<ENTRY>
<TIMESTAMP>30/06/2010 13:59:44</TIMESTAMP>
<RDRCOFST>0x0011A798</RDRCOFST>
<CHVCOFST>0x0011A7C8</CHVCOFST>
<SESSION_USER>KLM</SESSION_USER>
<CURRENT_USER>KLM</CURRENT_USER>
<SQL_STATETMENT>create table willi2 as select * from willi</SQL_STATETMENT>
<SCHEMA>KLM</SCHEMA>
<OBJECT>WILLI2</OBJECT>
</ENTRY>
</ENTRIES>
</LOG>

This output comes from stdout, and is easily to be redirected into a file by adding “> stuff.xml” on Unixoid OSses.

I’d say, this small program can be really useful, and avoids huge logminer efford for simple questions of a DBA life. The tool is said to work on 10g and 11g databases, my test was 11gR2.

Just one sad thing: It’s closed source, and just a kind of demo version. Download and product info is available here: http://www.v3rity.com/ddldump.php

Be careful, as always
Martin Klier




You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

2 Responses to “Extracting Oracle DDL from online redo logs or archived redo logs with DDLDUMP”

  1. Norman Dunbar Says:

    Hi Martin,

    I’m not sure if you care ;-) but I sent David a small XSLT to convert the raw HTML output into something that you could view with your favourite standards compliant browser.

    He was going to put it on tthe v3rity web site, once he had given it the once over, I don’t think it’s there yet – but it does make the output a lot more readable.

    Cheers,
    Norman.

  2. usn Says:

    Great idea!

Leave a Reply