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