Mining DDL from LogMiner

One may sometimes need to find the ddl which ran on the database at some certain period of time.If auditing is enabled for the you can get the details using various *_audit_* views. In case, its not and the database is in archivelog mode you can try using logminer.Here is an example for it —

From SYS session —

14:57:56 SQL> select supplemental_log_data_min from v$database;

SUPPLEME
--------
YES

14:58:11 SQL>
14:58:11 SQL>
14:58:11 SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            D:\oracle\archive\catdb
Oldest online log sequence     24
Next log sequence to archive   26
Current log sequence           26
14:58:36 SQL>
14:58:36 SQL>
14:58:36 SQL> alter system switch logfile;

System altered.

14:58:41 SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            D:\oracle\archive\catdb
Oldest online log sequence     25
Next log sequence to archive   27
Current log sequence           27
14:58:43 SQL>

Logged in as ANAND user and did some DDLs

14:58:59 SQL> show user
USER is "ANAND"
14:59:01 SQL>
14:59:01 SQL>
14:59:01 SQL> create table test_log (a number,b number);

Table created.

14:59:15 SQL>
14:59:15 SQL> insert into test_log values (1,1);

1 row created.

14:59:23 SQL> insert into test_log values (1,2);

1 row created.

14:59:25 SQL> insert into test_log values (2,2);

1 row created.

14:59:27 SQL> insert into test_log values (2,1);

1 row created.

14:59:29 SQL>
14:59:30 SQL>
14:59:30 SQL> commit;

Commit complete.

14:59:31 SQL>
14:59:31 SQL> select * from test_log;

         A          B
---------- ----------
         1          1
         1          2
         2          2
         2          1

14:59:36 SQL>
14:59:37 SQL>
14:59:37 SQL>
15:01:35 SQL>
15:01:35 SQL> alter table test_log add (c number);

Table altered.

15:01:49 SQL>
15:01:49 SQL>
15:01:50 SQL> insert into test_log values (1,1,1);

1 row created.

15:01:55 SQL> insert into test_log values (1,1,2);

1 row created.

15:01:58 SQL> insert into test_log values (1,2,2);

1 row created.

15:02:00 SQL> insert into test_log values (2,2,2);

1 row created.

15:02:03 SQL> insert into test_log values (2,1,1);

1 row created.

15:02:08 SQL> commit;

Commit complete.

15:02:10 SQL>
15:02:10 SQL>
15:02:11 SQL>
15:02:11 SQL> select * from test_log;

         A          B          C
---------- ---------- ----------
         1          1
         1          2
         2          2
         2          1
         1          1          1
         1          1          2
         1          2          2
         2          2          2
         2          1          1

9 rows selected.

15:02:16 SQL>
15:12:02 SQL>
15:12:03 SQL>
16:08:39 SQL>
16:08:39 SQL>
16:08:40 SQL>
16:08:40 SQL> truncate table test_log;

Table truncated.

16:08:50 SQL>
16:08:51 SQL>
16:08:51 SQL>
16:08:51 SQL>
16:21:53 SQL>
16:21:53 SQL>
16:21:53 SQL> drop table test_log;

Table dropped.

As sys user

15:13:27 SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            D:\oracle\archive\catdb
Oldest online log sequence     25
Next log sequence to archive   27
Current log sequence           27
16:22:01 SQL>
16:22:01 SQL>
16:22:02 SQL> alter system switch logfile;

System altered.

16:22:06 SQL> alter system switch logfile;

System altered.

16:22:27 SQL>

So archive log 27 has the details

16:23:28 SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';16:31:35 

SQL> EXECUTE dbms_logmnr.add_logfile(LogFileName=>'D:\oracle\archive\catdb\ARC0000000027_0785160155.0001',OPTIONS => DBMS_LOGMNR.NEW);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.29
16:31:48 SQL> EXECUTE dbms_logmnr.add_logfile(LogFileName=>'D:\oracle\archive\catdb\ARC0000000028_0785160155.0001',OPTIONS => DBMS_LOGMNR.ADDFILE);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01

16:33:21 SQL> select filename,low_time,high_time,DICTIONARY_BEGIN,DICTIONARY_END from v$LOGMNR_LOGS;

FILENAME                                                               LOW_TIME             HIGH_TIME            DIC DIC
---------------------------------------------------------------------- -------------------- -------------------- --- ---
D:\oracle\archive\catdb\ARC0000000027_0785160155.0001                  12-JUN-2012 14:58:41 12-JUN-2012 16:22:06 NO  NO
D:\oracle\archive\catdb\ARC0000000028_0785160155.0001                  12-JUN-2012 16:22:06 12-JUN-2012 16:22:27 NO  NO

16:35:01 SQL> EXECUTE dbms_logmnr.start_logmnr(OPTIONS =>DBMS_LOGMNR.DDL_DICT_TRACKING +DBMS_LOGMNR.PRINT_PRETTY_SQL);

PL/SQL procedure successfully completed.

16:41:44 SQL> select username,OPERATION,TIMESTAMP,TABLE_NAME,SESSION#,SERIAL#,SQL_REDO from  V$LOGMNR_CONTENTS where SEG_OWNER='ANAND';

USERNAME   OPERATION                        TIMESTAMP            TABLE_NAME                         SESSION#    SERIAL# SQL_REDO
---------- -------------------------------- -------------------- -------------------------------- ---------- ---------- ------------------------------------------------------------
ANAND      DDL                              12-JUN-2012 14:59:14 TEST_LOG                             191            95 create table test_log (a number,b number);
ANAND      DDL                              12-JUN-2012 15:01:49 TEST_LOG                             191            95 alter table test_log add (c number);
ANAND      DDL                              12-JUN-2012 16:08:50 TEST_LOG                             191            95 truncate table test_log;
ANAND      DDL                              12-JUN-2012 16:21:58 TEST_LOG                             191            95 ALTER TABLE "ANAND"."TEST_LOG" RENAME TO "BIN$R2yT2OHzRhGspu
                                                                                                                5YAsnXPw==$0" ;

ANAND      DDL                              12-JUN-2012 16:21:58 TEST_LOG                             191            95 drop table test_log AS "BIN$R2yT2OHzRhGspu5YAsnXPw==$0" ;

If supplemental logging is not enabled, some columns of the view would have UNKNOWN value.It could be difficult in that case to identify the user and session information details.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s