|
Log Miner - Use and Usage How and Why to use LogMiner Introduction Many new features were released with Oracle 8i, one of which, LogMiner, may prove very useful in the event of incorrect data input or when trying to identify when a certain change was made. This paper discusses what LogMiner provides, how to configure and use it and also gives some thoughts on how it can be used to provide options when there may be a requirement to perform a point-in-time recovery. What LogMiner can do LogMiner was released with Oracle 8i and runs with all the later releases. The utility can be used to analyze the contents of the redo log files and to obtain information about DML activities on standard tables. The information provided can help any decisions regarding database regression by identifying what was done by which user and when. The utility provides a means of auditing actions to either specific tables or by specific users and could be used for many purposes not all associated with corrupted or incorrect data. Most usefully of all it can be used to read redo logs or archived redo log files from any version of Oracle 8 not just 8i. (Note the restrictions below.) Restrictions on usage There are some restrictions on the use of LogMiner, of which the 2 major caveats are relevant where one Oracle instance is used to analyze redo logs from other instances. a) Redo logs can be read from other instances provided that the db_block_size is identical b) The same database character set and hardware platforms are used. Other limitations include a) Does not support ‘unconventional tables’ such as index-organised, clustered tables and non-scalar datatypes. b) Does not show data inserted via direct path (use of Append command) c) Although not a limitation, as it is part of the design, it does not show DDL operations d) Archive logging must be enabled on the instance used to read the redo logs. Configuration The use of a dictionary file is an option but one that it is highly recommended to take. A dictionary file is created to contain a mapping between Oracle internal object Ids and object names such as emp, company_name etc. The dictionary file is specific to an instance and contains an instance reference and creation date. To create a dictionary file, the PL/SQL package dbms_logmnr_d.build is used. Prior to that an utl_file_dir has to be created in the init.ora file indication where the dictionary file will reside. Metalink has several documents covering issues associated with the creation of a dictionary file. Care should be taken to ensure that the database user can read and write from the directory. There also appears to be a path/filename limitation of 44 characters. This is very easy to hit, especially if the example dictionary name of dictionary.ora is used. (Note limited testing on VMS indicates that a full directory name is required, not a logical). An example of the dictionary create is given below. As user SYS:
The next step is to inform LogMiner which redo logs will be analyzed. Use the logmnr_add_logfile package with one of the three options (NEW,ADDFILE,REMOVEFILE). Create a list of logs by adding them as in the following example
If further files require analysis they can be added by using the following syntax Next step is to tell LogMiner to analyze the log files that are in the list.
Options available are to specify STARTTIME/ENDTIME of transactions or STARTSCN/ENDSCN for the appropriate SCN numbers. An example of the syntax is COMPLETE THIS SECTION Usage LogMiner must be run as user SYS. Whenever the session is logged out, then on logging in again LogMiner must be restarted after the redo logs have been added. How to use in a multi-environment site COMPLETE THIS SECTION References: Oracle 8i Administrator’s Guide |
||