 |
|
Oracle LogMiner auditing tips
Oracle Tips by Burleson Consulting |
Question:
Where can I find tips for using Oracle LogMiner for auditing?
Answer: Oracle LogMiner is a redo log query engine
that is provided with the Oracle database server. LogMiner was introduced in
Oracle 8.1, and later releases of Oracle improved on the performance of the log
miner utility. In Oracle 9i Release 2, the log miner engine is the core of the
log apply service for the logical standby database.
Log Miner uses redo
entries embedded in online redo log files or archived redo log files and data
dictionary information to build the SQL statement. Log Miner keeps the contents
of the redo log file in the fixed view, v$logmnr_contents.
Understanding Log Miner Technology
Log Miner is a redo log query engine that is provided
with the Oracle database server. Log Miner was introduced in Oracle 8.1, and
later releases of Oracle improved on the performance of the log miner
utility. In Oracle, the log miner engine is the core of the log apply
service for the logical standby database.
Log Miner uses redo entries embedded in online redo log
files or archived redo log files and data dictionary information to build
the SQL statement. Log Miner keeps the contents of the redo log file in the
fixed view, v$logmnr_contents. The SQL interface can be used to query this
view in order to see the SQL statement executed on a database at any point
in time.
Together, the column SQL_REDO and the SCN of
v$logmnr_contents, can provide relevant information on the activity within
the database. In addition, this view contains the segment name and owner
which is useful in further identification of the objects being altered.
The redo log file of an Oracle database contains change
vectors in the form of internally generated numerical identifiers. Moreover,
the old and new values of columns are represented as hex bytes. These
internally generated identifiers or hex bytes are not easy to understand.
In order to generate SQL statements in external data
format, the log miner engine uses data dictionary information. This data
dictionary information can be extracted to an external operating system file
or redo logs using the
dbms_logmnr_d.build procedure. As an alternative, the data dictionary
can be read directly from an online catalog. In the latter case, the Log
Miner engine can only provide the SQL statements using the current structure
of objects within the database.
In the SQL apply mode of a Data Guard environment, the
dictionary is built using the dbms_logstdby.build procedure, which stores
the dictionary information to the redo log file. The extraction of
dictionary information from the redo log file for mapping of the internal
identifier is more efficient.
For the best results, a separate tablespace should be
used to create the objects required by the Log Miner utility. By default,
these objects are created in the system tablespace.
This may slow down the overall response time of the primary database.
The SQL interface can be used to query this view in order
to see the SQL statement executed on a database at any point in time.
There are several good books
that discuss Oracle LogMiner:Oracle LogMiner books:
Also, see my notes on LogMiner:
ORA-01341: LogMiner out-of-memory error
Data Guard Physical RAC with logminer
Oracle Data guard LogMiner Dictionary
Oracle10g Data Guard New logminer Features
Oracle LogMiner Switchover Failover
Oracle Data guard Tablespace LogMiner Objects
Oracle Data guard LogMiner Dictionary
Oracle Data guard LogMiner Oracle Net
Oracle Data guard Backup Primary Database LogMiner Dictionary
Oracle Data guard Apply Service for LogMiner
RAC Grid logminer Log Apply Services
RAC LogMiner v$logstdby SQL Apply
Oracle Data guard with logminer
Oracle Data guard Logical LogMiner dictionary
Oracle Auditing -
Oracle logminer audit with HIPAA
Oracle Snapshot
Replication & Logminer Training Course
Oracle Data Guard
Training Course
