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