LogMining

We know that any changes to database is recored in online redo logfiles. If your database archival mode on then online redo log files are archived which contains the database changes. With these logfile we can analysis any changes in database.

In simple we can say an undo entry provides the values of data stored before a change and the redo entry provides the values of data stored after a change. So we can get them from online redo logs and then to archived logs.

• So from online redo logs and from archived redo logs we can get database redo and undo information. But online and archived logs have an unpublished format and are not human-readable. With the DBMS_LOGMNR package we can analysis redo log files and can get back undo and redo information in a human readable format.

• Another scenario of use of logminer is to investigate database past in time. With Flashback Query we can get prior values of the record in the table at some point in the past but is limited to UNDO_RETENTION parameter (which is often as short as 30 minutes for an OLTP database.).So in order to analysis past activity on the database logminer is a good choice.

Definition:

• Every change made to an Oracle database by default generates undo and redo information which is accumulated in Oracle redo log files. LogMiner is an integrated feature of the Oracle Database that provides DBA's and auditors with the infrastructure required for relational access to Oracle's redo stream.

• The Oracle LogMiner utility enables you to query redo logs through a SQL interface. Redo logs contain information about the history of activity on a database.

Why LogMiner:

You can use the Oracle LogMiner utility to enable you to query redo log files through a SQL interface. Database administrators can use LogMiner to:

• Identify the time of a database-event

• Isolate transactions carried out in error by users

• determine steps needed for the recovery of inadvertent (done unintentionally) changes to data

• assemble data on actual usage for use in performance-tuning and capacity-planning

• auditing the operation of any commands run against the database

Note that LogMiner uses Oracle logs to reconstruct exactly how data changed, whereas the complementary utility Oracle Flashback addresses, reconstructs and presents the finished results of such changes, giving a view of the database at some point in time.

Before start using LogMiner:

• Log in with the sys account using sysdba role

• Never escape from current session because the data viewing of LogMiner will no be available from other session.

LogMiner Restrictions:

The following are not supported:

• Data types LONG and LOB
• Simple and nested abstract data types ( ADTs)
• Collections (nested tables and VARRAYs)
• Object Refs
• Index Organized Tables (IOTs)

LogMiner Configuration

There are four basic objects in a LogMiner configuration that you should be familiar with: the source database, the mining database, the LogMiner dictionary, and the redo log files containing the data of interest:

• The redo log files contain the changes made to the database or database dictionary.

• The source database is the database that produces all the redo log files that you want LogMiner to analyze.

• The mining database is the database that LogMiner uses when it performs the analysis.

• The LogMiner dictionary allows LogMiner to provide table and column names, instead of internal object IDs, when it presents the redo log data that you request. LogMiner uses the dictionary to translate internal object identifiers and datatypes to object names and external data formats. Without a dictionary, LogMiner returns internal object IDs and presents data as binary data.

For example, consider the following the SQL statement: INSERT INTO HR.JOBS(JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY) VALUES('IT_WT','Technical Writer', 4000, 11000);

Without the dictionary, LogMiner will display: insert into "UNKNOWN"."OBJ#45522"("COL 1","COL 2","COL 3","COL 4") values(HEXTORAW('45465f4748'),HEXTORAW('546563686e6963616c20577269746572'),HEXTORAW('c229'),HEXTORAW('c3020b'))

LogMiner Dictionary Options:

LogMiner requires a dictionary to translate object IDs into object names when it returns redo data to you. LogMiner gives you three options for

supplying the dictionary.

1. Using the Online Catalog:

Oracle recommends that you use this option when you will have access to the source database from which the redo log files were created and when no changes to the column definitions in the tables of interest are anticipated. This is the most efficient and easy-to-use option.
The online catalog contains the latest information about the database and may be the fastest way to start your analysis. Because DDL operations that change important tables are somewhat rare, the online catalog generally contains the information you need for your analysis. Remember, however, that the online catalog can only reconstruct SQL statements that are executed on the latest version of a table. As soon as a table is altered, the online catalog no longer reflects the previous version of the table. This means that LogMiner will not be able to reconstruct any SQL statements that were executed on the previous version of the table.

2. Extracting a LogMiner Dictionary to the Redo Log Files:

Oracle recommends that you use this option when you do not expect to have access to the source database from which the redo log files were created, or if you anticipate that changes will be made to the column definitions in the tables of interest.

3. Extracting the LogMiner Dictionary to a Flat File:

This option is maintained for backward compatibility with previous releases. This option does not guarantee transactional consistency. Oracle recommends that you use either the online catalog or extract the dictionary from redo log files instead.