|
QuickStart
Oracle LogMiner is a tool that can do a little magic when needed. What can it do for you? A couple of its uses are retrieving data that has been deleted or changed, it can do a low level audit to find out what happend, or it can be used to create a rollback of statements for data recovery. This is only a couple of uses of this valuable tool that each and every DBA needs to have at their disposal.
Overview
• Initial Setup "utl_file_dir" Set Parameter (will require recycling the database)
• Add Logfiles (online or archivelogs)
• Start the LogMining Session
• Run the Queries or "Mine the Logs"
• End the LogMining Session
Initial Setup
1. Why utl_file_dir parameter? LogMiner uses a dictionary to inventory objects to external data formats. This will allow you to create flat files, which are the most comon types for a LogMining session. Before beginning, ensure that utl_file_dir is actually set in the database.
SQL> show parameter utl_file_dir;
This is not a dynamic value and if you need to set you will have to edit an initSID.ora file, update and start database with the updated initSID.ora file.
2. Create the Directory! (required in 8i, optional in 9i and 10g)
10g and 9i
SQL> exec dbms_logmnr_d.build('dictionary.ora',-
'/admin/SID/utl_dir', options =>dbms_logmnr_d.store_in_flat_file);
8i, 9i, and 10g
SQL> exec dmbs_logmnr_d.build(dictionary_filename => 'dictionary.ora',-
dictionary_location => '/admin/SID/utl_dir');
Add Logfiles
1. Add your first logfile, note there is a difference between the first and additional. Also, it does not matter if it is an online redo log or archive log.
SQL> exec dbms_logmnr.add_logfile( -
logfilename => '/lg01/oradata/SID/redoA_01.log', -
options => dbms_logmnr.new);
2. Add additional redo logs and/or archive logs (optional)
SQL> exec dbms_logmnr.add_logfile( -
logfilename => '/lg01/archives/SID/arch_4356.log', - options => dbms_logmnr.addfile);
Start LogMining
1. Start the LogMining process. The first example is if you do not use a dictionary.ora file.
SQL> exec dbms_logmnr.start_logmnr( -
options => dbms_logmnr.dict_from_online_catalog);
or
SQL> exec dbms_logmnr.start_logmnr( -
dictfilename => '/admin/SID/utl_dir/dictionary.ora');
Run Queries
1. Perform your LogMining, the main view you will query against is V$logminer_contents. Some of the more important columns are (sql_redo, sql_undo,username, seg)
SQL> select username, seg, sql_redo, sql_undo from v$logminer_contents where username = 'SCOTT' and seg = 'EMP';
End LogMiner
1. Stop the LogMiner process.
SQL> exec dbms_logminer.end_logmnr();
Bryan Cooper
Senior Consultant
|
