Oracle Tips and Tricks — David Fitzjarrell

October 31, 2008

Workin’ In The Mines

Filed under: flashback,General — dfitzjarrell @ 13:42

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('ortofon')

SQL_UNDO
-------------------------------------------------------------------------------------------------------------
update "ORTOFON"."EMP" set "COMM" = '1000' where "COMM" = '999' and ROWID = 'AAAanJAAEAAAAIvAAA';
update "ORTOFON"."EMP" set "COMM" = '1000' where "COMM" = '999' and ROWID = 'AAAanJAAEAAAAIvAAD';
update "ORTOFON"."EMP" set "COMM" = '1000' where "COMM" = '999' and ROWID = 'AAAanJAAEAAAAIvAAF';
update "ORTOFON"."EMP" set "COMM" = '1000' where "COMM" = '999' and ROWID = 'AAAanJAAEAAAAIvAAG';
update "ORTOFON"."EMP" set "COMM" = '1000' where "COMM" = '999' and ROWID = 'AAAanJAAEAAAAIvAAH';
update "ORTOFON"."EMP" set "COMM" = '1000' where "COMM" = '999' and ROWID = 'AAAanJAAEAAAAIvAAI';
update "ORTOFON"."EMP" set "COMM" = '1000' where "COMM" = '999' and ROWID = 'AAAanJAAEAAAAIvAAK';
update "ORTOFON"."EMP" set "COMM" = '1000' where "COMM" = '999' and ROWID = 'AAAanJAAEAAAAIvAAL';
update "ORTOFON"."EMP" set "COMM" = '1000' where "COMM" = '999' and ROWID = 'AAAanJAAEAAAAIvAAM';
update "ORTOFON"."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 direct path loads and inserts using the /*+ append */ hint however other insert/update transactions will generate redo. 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('ortofon')

SQL_UNDO
-------------------------------------------------------------------------------------------------------------
update "ORTOFON"."EMP" set "COMM" = '1000' where "COMM" = '999' and ROWID = 'AAAanJAAEAAAAIvAAA';
update "ORTOFON"."EMP" set "COMM" = '1000' where "COMM" = '999' and ROWID = 'AAAanJAAEAAAAIvAAD';
update "ORTOFON"."EMP" set "COMM" = '1000' where "COMM" = '999' and ROWID = 'AAAanJAAEAAAAIvAAF';
update "ORTOFON"."EMP" set "COMM" = '1000' where "COMM" = '999' and ROWID = 'AAAanJAAEAAAAIvAAG';
update "ORTOFON"."EMP" set "COMM" = '1000' where "COMM" = '999' and ROWID = 'AAAanJAAEAAAAIvAAH';
update "ORTOFON"."EMP" set "COMM" = '1000' where "COMM" = '999' and ROWID = 'AAAanJAAEAAAAIvAAI';
update "ORTOFON"."EMP" set "COMM" = '1000' where "COMM" = '999' and ROWID = 'AAAanJAAEAAAAIvAAK';
update "ORTOFON"."EMP" set "COMM" = '1000' where "COMM" = '999' and ROWID = 'AAAanJAAEAAAAIvAAL';
update "ORTOFON"."EMP" set "COMM" = '1000' where "COMM" = '999' and ROWID = 'AAAanJAAEAAAAIvAAM';
update "ORTOFON"."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.

Advertisements

3 Comments »

  1. hi dfitzjarrell!
    your solution is good. but you only add 3 files redo01->03.log to logmnr dictionary. when switch logfile occur, the redo0X.log archived to archivelog on archivelog mode and you can’t analyze when data is not in redo0x.log

    Comment by mrD — September 22, 2015 @ 10:05 | Reply

    • You didn’t read on and see how to continuously mine the archivelogs, as that was also presented. Please read it through again; you apparently missed half of the information posted.

      Comment by dfitzjarrell — September 22, 2015 @ 10:47 | Reply

  2. I’ve learned something new šŸ™‚ Thank you for this post.

    Comment by jrphdba — November 21, 2016 @ 00:17 | Reply


RSS feed for comments on this post. TrackBack URI

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

Create a free website or blog at WordPress.com.

%d bloggers like this: