
Using the LogMiner
- First appeared in Oracle 8i
- Log Miner allows for analysis of online and archived redo log files.
- LogMiner provides a well-defined, easy-to-use, and comprehensive relational interface to redo log files
Enhancements in Oracle 11g
- LogMiner was integrated with OEM
- Also integrated with the new flashback transaction feature
- Can be used from command line or through OEM
Possible uses:
- Find out when a logical error happened and who did it.
- This information in turn can be used to determine when to initiate time-based or change-based recovery (
V$LOGMNR_CONTENTS
) - Determine what actions are needed to perform fine-grained recovery at the transaction level (flashback transaction)
- Support for trend analysis (find out which databases get most DMLs)
- Support on post-facto auditing.
- Useful to find out who has updated a given table (or set of tables).
- Explore what happens when a DML/DDL is issued against the database
![]() | Configuring the Log Miner |
- The Source database
- The Mining database
- The LogMiner dictionary - used to translate internal object identifiers to object names and external data formats.
- Redo log files that will be analyzed
![]() | Managing LogMiner operation: |
- Use
SYS.DBMS_LOGMNR
andSYS.DBMS_LOGMNR_D
packages to (a) select redo log files for analysis, (b) begin and (c) end log miner section. - Use
V$LOGMNR_CONTENTS
view to query the contents of redo logs. - You must be connected as
SYS
or haveEXECUTE_CATALOG_ROLE
role
DBMS_LOGMNR
package subprograms: SQL> dbms_logmnr.add_logfile(LOGFILENAME => logname, OPTIONS => dbms_logmnr.new); -- Add redo log file to a list of redo logs for LogMiner to process.
- OPTIONS:
- new -- implicit call to dbms_logmnr.end_logmnr and start a new log miner session. Creates a new list and add the redo log.
- addfile -- Adds the redo log to the existing list
SQL> dbms_logmnr.remove_logfile(LOGFILENAME => logname); -- Remove redo from list
SQL> dbms_logmnr.start_logmnr(OPTIONS => dbms_logmnr.dict_from_online_catalog); -- Initialized the LogMiner
- OPTIONS:
committed_data_only
- Return committed statements only. (none rolled back or in progress)
- Group statements of a committed transaction.
- Transactions returned in order.
skip_corruption
- skips corruptions in the redo log (redo log block corruption)
ddl_dict_tracking
- Used when dictionary is in a flat file. Updates dict references as DDL are processed
dict_from_online_catalog
- directs log miner to use the online dictionary of the database when translating object names
dict_from_redo_logs
no_sql_delimiter
- Useful if you want to use the reconstructed statements directly in a open cursor
no_rowid_in_stmt
print_pretty_sql
continuous_mine
- Instructs log miner to add redo log files, as needed, until find the data on the specified SCN or timestamp
string_literals_in_stmt
SQL> dbms_logmnr.end_logmnr(); -- Finishes a LogMiner session
- Source and Mining databases
- Both must run on the same platform
- mining db can be the same or separate from source db
- mining db must be same or later release of the source db
- mining db must use same characterset (or superset) of source db
- LogMiner dictionary
- Must be produced from the source db
- Used to translate redo log information into a meaningful format
- Used to display table and column names instead of their internal IDs.
- Without a dictionary, LogMiner also display all values in hexadecimal format.
- Redo Logs
- must be produced by the same source db
- associated with the same database RESETLOGS SCN
- Must be from oracle 8.0 or later (mostly 9i or later)
- Supplemental Logging
- must be enabled before redo logs are generated
- Enable with:
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
- Logs the extra information necessary to reconstruct the SQL statement as well
as one to revert the original action. - Without supplemental logging instance and media recovery is possible, but log mining,
or reconstruction of the original SQL statement, is not possible.
SQL> select supplemental_log_data_min,supplemental_log_data_pk, supplemental_log_data_fk, supplemental_log_data_all from v$database; SUPPLEMENTAL_LOG_DATA_MIN SUPPLEMENTAL_LOG_DATA_PK SUPPLEMENTAL_LOG_DATA_FK SUPPLEMENTAL_LOG_DATA_ALL ------------------------- ------------------------ ------------------------ ------------------------- YES YES YES NO
![]() | About the LogMiner dictionary Used to translate the information in the redo log files into object names and display the data in alpha numeric, instead of hexadecimal format. |
- For this, the source database must be online and available
- To use the online catalog:
SQL> execute dbms_logmnr.start_logmnr( options => dbms_logmnr.dict_from_online_catalog);
- Used when the source database is not available
- In order to extract dictionary information, the source database must be (1) open, (2) in archivelog mode, (3) with enabled archiving.
- To extract dictionary:
SQL> execute dbms_logmnr_d.build( options => dbms_logmnr_d.store_in_redo_logs);
- The dictionary information is stored in the archived redo logs. To find out which:
SQL> select name from v$archived_log where dictionary_begin = 'YES';
SQL> select name from v$archived_log where dictionary_end = 'YES';
- Available for backward compatibility only. no trasactional consistency.
![]() | Example 1: Find all modifications in the last archived redo log
|
No comments:
Post a Comment