8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
LogMiner
LogMiner enables the analysis of the contents of archived redo logs. It can be used to provide a historical view of the database without the need for point-in-time recovery. It can also be used to undo operations, allowing repair of logical corruption.
Related articles.
- LogMiner Enhancements In Oracle9i
- Flashback and LogMiner Enhancements in Oracle Database 11g Release 1
Create Dictionary File
Without a dictionary file LogMiner displays all tables and columns using their internal object IDs and all values as hex data. The dictionary file is used to translate this data into a more meaningful format. For the dictionary file to be created the following initialization parameter must be set and the instance must be mounted or open.
UTL_FILE_DIR=C:\Oracle\Oradata\TSH1\Archive
The dictionary file is created using the BUILD
procedure in the DBMS_LOGMNR_D
package.
BEGIN DBMS_LOGMNR_D.build ( dictionary_filename => 'TSH1dict.ora', dictionary_location => 'C:\Oracle\Oradata\TSH1\Archive'); END; /
Adding Logs
A list of logs to be analyzed must be added to logminer using the DBMS_LOGMNR
package. The first log in the list is added using the NEW
procedure, while subsequent logs are added using the ADD_LOGFILE
procedure.
BEGIN DBMS_LOGMNR.add_logfile ( options => DBMS_LOGMNR.new, logfilename => 'C:\Oracle\Oradata\TSH1\Archive\TSH1\T001S00006.ARC'); DBMS_LOGMNR.add_logfile ( options => DBMS_LOGMNR.addfile, logfilename => 'C:\Oracle\Oradata\TSH1\Archive\TSH1\T001S00007.ARC'); END; /
Starting LogMiner
At this point LogMiner can be started using the overloaded START_LOGMNR
procedure. The analysis range can be narrowed using time or SCN.
BEGIN -- Start using all logs DBMS_LOGMNR.start_logmnr ( dictfilename => 'C:\Oracle\Oradata\TSH1\Archive\TSH1dict.ora'); -- Specifiy time range DBMS_LOGMNR.start_logmnr ( dictfilename => 'C:\Oracle\Oradata\TSH1\Archive\TSH1\dict.ora', starttime => TO_DATE('01-JAN-2001 00:00:00', 'DD-MON-YYYY HH:MI:SS'), endtime => TO_DATE('01-JAN-2001 10:00:00', 'DD-MON-YYYY HH:MI:SS')); -- Specifiy SCN range DBMS_LOGMNR.start_logmnr ( dictfilename => 'C:\Oracle\Oradata\TSH1\Archive\TSH1\dict.ora', startscn => 100, endscn => 150); END; /
Querying Log Information
Once LogMiner is started, the contents of the logfiles can be queried using the following views:
V$LOGMNR_DICTIONARY
- The dictionary file in use.V$LOGMNR_PARAMETERS
- Current parameter settings for LogMiner.V$LOGMNR_LOGS
- Which redo log files are being analyzed.V$LOGMNR_CONTENTS
- The contents of the redo log files being analyzed.
The following query displays the SQL issued along with the undo SQL to reverse it.
SELECT scn, operation, sql_redo, sql_undo FROM v$logmnr_contents;
The following query displays the number of hits for each object during the analyzed period.
SELECT seg_owner, seg_name, count(*) AS Hits FROM v$logmnr_contents WHERE seg_name NOT LIKE '%$' GROUP BY seg_owner, seg_name;
Stopping LogMiner
Once the analysis is complete, logminer should be stopped using the END_LOGMNR
procedure.
BEGIN DBMS_LOGMNR.end_logmnr; END; /
For more information see:
- Using LogMiner to Analyze Online and Archived Redo Logs
- LogMiner Enhancements In Oracle9i
- Flashback and LogMiner Enhancements in Oracle Database 11g Release 1
Hope this helps. Regards Tim...