Workin' In The Mines

Flashback query, available since Oracle 9i, can really be a lifesaver when a need arises to resurrect data. But, what if the UNDO has been overwritten by another process? To the rescue comes DBMS_LOGMNR, the LogMiner package. This utility dredges through the redo logs (and archive logs, if they are still available) to return both the SQL statements to redo the transactions and SQL statements to undo those same transactions. Since Oracle will automatically replay any in-doubt transactions occuring at the time of a crash retrieving the redo SQL is usually not necessary. Returning the undo SQL, however, may be useful when flashback query cannot be used.

DBMS_LOGMNR has several procedures available, of which we'll use three in this example: ADD_LOGFILE, START_LOGMNR and END_LOGMNR. You can start up LogMiner then add the logfiles you wish to 'mine', or add the logfiles then start the utility. I prefer the latter method, which is illustrated here. We'll start by updating the EMP table:

SQL> update emp set comm = 999 where comm = 1000;

10 rows updated.

SQL> commit;

Commit complete.

SQL>

Now let's mine the redo logs and see if we can undo that change:

SQL> --
SQL> -- Add every redo log to the 'mix' so LogMiner can
SQL> -- use them
SQL> --
SQL> select 'exec dbms_logmnr.add_logfile('''||member||''')'
  2  from v$logfile
  3
SQL>
SQL> spool add_logfiles.sql
SQL> /

'EXECDBMS_LOGMNR.ADD_LOGFILE('''||MEMBER||''')'
-------------------------------------------------------------------------------------
exec dbms_logmnr.add_logfile('/zing/flork/dapplenap/redo01.log')
exec dbms_logmnr.add_logfile('/zang/flork/dapplenap/redo02.log')
exec dbms_logmnr.add_logfile('/zong/flork/dapplenap/redo03.log')

SQL> spool off
SQL>
SQL>
SQL> @add_logfiles
SQL> exec dbms_logmnr.add_logfile('/zing/flork/dapplenap/redo01.log')

PL/SQL procedure successfully completed.

SQL> exec dbms_logmnr.add_logfile('/zang/flork/dapplenap/redo02.log')

PL/SQL procedure successfully completed.

SQL> exec dbms_logmnr.add_logfile('/zong/flork/dapplenap/redo03.log')

PL/SQL procedure successfully completed.

SQL>
SQL> --
SQL> -- Fire up LogMiner
SQL> --
SQL> exec dbms_logmnr.start_logmnr(options => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG)

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> --
SQL> -- Prepare the environment for the output
SQL> --
SQL> set linesize 5000 trimspool on
SQL>
SQL>
SQL> --
SQL> -- Retrieve the SQL statements to 'undo' the
SQL> -- committed changes
SQL> --
SQL> select sql_undo
  2  from v$logmnr_contents
  3  where seg_owner = upper('&1')
  4
SQL>
SQL> spool undo_committed_changes.sql
SQL> /
Enter value for 1: ortofon
old   3: where seg_owner = upper('&1')
new   3: where seg_owner = upper('bing')

SQL_UNDO
-------------------------------------------------------------------------------------------------------------
update "BING"."EMP" set "COMM" = '1000' where "COMM" = '999' and ROWID = 'AAAanJAAEAAAAIvAAA';
update "BING"."EMP" set "COMM" = '1000' where "COMM" = '999' and ROWID = 'AAAanJAAEAAAAIvAAD';
update "BING"."EMP" set "COMM" = '1000' where "COMM" = '999' and ROWID = 'AAAanJAAEAAAAIvAAF';
update "BING"."EMP" set "COMM" = '1000' where "COMM" = '999' and ROWID = 'AAAanJAAEAAAAIvAAG';
update "BING"."EMP" set "COMM" = '1000' where "COMM" = '999' and ROWID = 'AAAanJAAEAAAAIvAAH';
update "BING"."EMP" set "COMM" = '1000' where "COMM" = '999' and ROWID = 'AAAanJAAEAAAAIvAAI';
update "BING"."EMP" set "COMM" = '1000' where "COMM" = '999' and ROWID = 'AAAanJAAEAAAAIvAAK';
update "BING"."EMP" set "COMM" = '1000' where "COMM" = '999' and ROWID = 'AAAanJAAEAAAAIvAAL';
update "BING"."EMP" set "COMM" = '1000' where "COMM" = '999' and ROWID = 'AAAanJAAEAAAAIvAAM';
update "BING"."EMP" set "COMM" = '1000' where "COMM" = '999' and ROWID = 'AAAanJAAEAAAAIvAAN';

SQL> spool off
SQL>
SQL>
SQL> --
SQL> -- Shut down LogMiner
SQL> --
SQL> exec dbms_logmnr.end_logmnr

PL/SQL procedure successfully completed.

SQL>

And we have displayed the statements necessary to undo the updates made to the EMP table earlier. This won't work if the table is created NOLOGGING as no redo entries will be written for such transactions. Also notice that the original update was one statement, and the undo (from the redo logs) generates 10 statements, one for each row updated.

Redo logs were used in this example, however you can also use archivelogs as well (as noted earlier, they must still be available on the server). And you can ask Oracle to add redo logs and archivelogs as necessary; the CONTINUOUS_MINE option provides that functionality, requiring only that the first redo log be added via ADD_LOGFILE or the starting SCN for the transactions of interest be provided:

SQL> --
SQL> -- Add one redo log to the 'mix'
SQL> --
SQL> select 'exec dbms_logmnr.add_logfile('''||member||''')'
  2  from v$logfile
  3  where rownum = 1
  4
SQL>
SQL> spool add_logfiles.sql
SQL> /

'EXECDBMS_LOGMNR.ADD_LOGFILE('''||MEMBER||''')'
-------------------------------------------------------------------------------------exec dbms_logmnr.add_logfile('/zing/flork/dapplenap/redo01.log')

SQL> spool off
SQL>
SQL>
SQL> @add_logfiles
SQL> exec dbms_logmnr.add_logfile('/zing/flork/dapplenap/redo01.log')

PL/SQL procedure successfully completed.

SQL>
SQL> --
SQL> -- Fire up LogMiner
SQL> --
SQL> -- The CONTINUOUS_MINE option cannot be used if the database 
SQL> -- is not running in ARCHIVELOG mode
SQL> --
SQL> -- But, hey, we are, so we're good to go
SQL> --
SQL> exec dbms_logmnr.start_logmnr(options => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.CONTINUOUS_MINE)

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> --
SQL> -- Prepare the environment for the output
SQL> --
SQL> set linesize 5000 trimspool on
SQL>
SQL>
SQL> --
SQL> -- Retrieve the SQL statements to 'undo' the
SQL> -- committed changes
SQL> --
SQL> select sql_undo
  2  from v$logmnr_contents
  3  where seg_owner = upper('&1')
  4
SQL>
SQL> spool undo_committed_changes.sql
SQL> /
Enter value for 1: ortofon
old   3: where seg_owner = upper('&1')
new   3: where seg_owner = upper('bing')

SQL_UNDO
-------------------------------------------------------------------------------------------------------------
update "BING"."EMP" set "COMM" = '1000' where "COMM" = '999' and ROWID = 'AAAanJAAEAAAAIvAAA';
update "BING"."EMP" set "COMM" = '1000' where "COMM" = '999' and ROWID = 'AAAanJAAEAAAAIvAAD';
update "BING"."EMP" set "COMM" = '1000' where "COMM" = '999' and ROWID = 'AAAanJAAEAAAAIvAAF';
update "BING"."EMP" set "COMM" = '1000' where "COMM" = '999' and ROWID = 'AAAanJAAEAAAAIvAAG';
update "BING"."EMP" set "COMM" = '1000' where "COMM" = '999' and ROWID = 'AAAanJAAEAAAAIvAAH';
update "BING"."EMP" set "COMM" = '1000' where "COMM" = '999' and ROWID = 'AAAanJAAEAAAAIvAAI';
update "BING"."EMP" set "COMM" = '1000' where "COMM" = '999' and ROWID = 'AAAanJAAEAAAAIvAAK';
update "BING"."EMP" set "COMM" = '1000' where "COMM" = '999' and ROWID = 'AAAanJAAEAAAAIvAAL';
update "BING"."EMP" set "COMM" = '1000' where "COMM" = '999' and ROWID = 'AAAanJAAEAAAAIvAAM';
update "BING"."EMP" set "COMM" = '1000' where "COMM" = '999' and ROWID = 'AAAanJAAEAAAAIvAAN';

SQL> spool off
SQL>
SQL>
SQL> --
SQL> -- Shut down LogMiner
SQL> --
SQL> exec dbms_logmnr.end_logmnr

PL/SQL procedure successfully completed.

SQL>

We, of course, found the same records as in the prior example, but we didn't need to include every log file in the database to get this to work; the CONTINUOUS_MINE option kept adding logs to the mix to find the information we requested. Yes, we supplied more than one option to the options parameter; we simply added the values together and DBMS_LOGMNR was able to know we wanted both options enabled.

LogMiner won't solve every data resurrection problem, nor will flashback query, however knowing these options are available may make your life as a DBA a bit less hectic and stressful.

Well, we can dream.