Understanding and using Oracle 8i analysis Tools logminer_oracle

The Oracle tutorial you are looking at is: Understanding and Using Oracle 8i Analysis tool Logminer.

Oracle Logminer is an actual, useful analysis tool that Oracle has provided from the product 8i, which makes it easy to get the details of Oracle Redo log files (archived log files), especially The tool can analyze all the DML (INSERT, UPDATE, DELETE, etc.) statements for database operations, and can also parse out some necessary rollback SQL statements. This tool is especially useful for debugging, auditing, or rollback of a particular transaction.

The Logminer analysis tool is actually made up of a set of pl/sql packages and some dynamic views (oracle8i part of a built-in package) that is released as part of an Oracle database and is a completely free tool for 8i products. However, this tool is somewhat more complex to use than other Oracle built-in tools, mainly because the tool does not provide any graphical user interface (GUI). This article describes in detail how to install and use the tool.

First, the use of Logminer

The log file holds all data for database recovery, recording every change to the database structure, that is, all DML statements that operate on the database.

Prior to Oracle 8i, Oracle did not provide any tools to assist the database administrator in reading and interpreting the contents of the Redo log files. Problems with the system, the only job you can do for a typical data administrator is to package all of the log files and then send it to Oracle for technical support, and then quietly wait for Oracle's technical support to give us the final answer. Since 8i, however, Oracle has provided such a powerful tool for-logminer.

The Logminer tool can be used to analyze the online, or to analyze offline log files, which can be used to analyze the redo log files of their own databases, or to analyze the redo log files of other databases.

Overall, the main uses of Logminer tools are:

1. Tracking database changes: You can track changes to the database offline without affecting the performance of the online system.

2. Rollback database changes: Rollback of specific change data to reduce point-in-time recovery execution.

3. Optimization and expansion plan: You can analyze data growth patterns by analyzing the data in the log file.

Second, install Logminer

To install the Logminer tool, you must first run the following two scripts,

L $ORACLE _home/rdbms/admin/dbmslsm.sql

2 $ORACLE _home/rdbms/admin/dbmslsmd.sql.

Both scripts must be run as the SYS user. The first of these scripts is used to create a DBMS_LOGMNR package that analyzes the log files. The second script is used to create the Dbms_logmnr_d package, which is used to create the data dictionary file.

Iii. Use of Logminer tools

The following is a detailed description of how to use the Logminer tool.

1. Create a data dictionary file (data-dictionary)

As already mentioned, the Logminer tool is actually made up of two new Pl/sql (DBMS_LOGMNR and Dbms_ logmnr_d) and four v$ dynamic performance views (views are in use in the process DBMS_LOGMNR. START_LOGMNR starts when Logminer is created). Before using the Logminer tool to parse a redo log file, you can use the Dbms_logmnr_d package to export the data dictionary to a text file. The dictionary file is optional, but without it, the parts of the Logminer's interpretation of the data dictionary (such as table name, column name, and so on) and values will be in the form of 16, which we cannot directly understand. For example, the following SQL statement:


Logminer explained the result would be the following,


The purpose of creating a data dictionary is to let Logminer refer to their actual names when referring to parts of the internal data dictionary, rather than to the internal 16 system. A data dictionary file is a text file that is created using package dbms_logmnr_d. If the table in the database that we are analyzing changes, the data dictionary that affects the library changes, and the dictionary file needs to be recreated. Another scenario is that when you analyze a redo log for another database file, you also have to regenerate the data dictionary file of the parsed database.

First, in the Init.ora initialization parameter file, specify the location of the data dictionary file, which is to add a parameter utl_file_dir that is the directory where the data dictionary files are placed in the server. Such as:


Restart the database so that the newly added parameters take effect, and then create the data dictionary file:

[NextPage]

2. Create a list of log files to analyze

Oracle's redo logs are divided into two types: online and offline (offline) archive log files, which are discussed separately to create a list of these two different log files.

(1) Analysis of online redo log files

A. Creating A List


B. Adding additional log files to the list


(2) Analysis of offline log files

A. Creating A List


B. Add additional log files to the list


The number of log files that need to be analyzed in this log file list is entirely up to you, but it is advisable to add only one log file that needs to be analyzed at a time, and then add another file after the analysis of the file is complete.

and add a log analysis list, use the procedure ' Dbms_logmnr.removefile ' to remove a log file from the list. The following example removes the log file E:\Oracle\oradata\sxf\redo02.log added above.


You have created a list of log files to analyze, and you can analyze them below.

3, using Logminer for log analysis

(1) Unrestricted conditions


(2) Restricted conditions

Dbms_ LOGMNR through the process. The setting of several different parameters in START_LOGMNR (see table 1 for the parameter meaning) reduces the scope of the log file to be analyzed. By setting the start time and termination time parameters, we can limit the logs that analyze only a certain time range. As the following example, we only analyze the September 18, 2001 log:


You can also limit the scope of the log to be analyzed by setting the start SCN and up to SCN:


Table 1 DBMS_LOGMNR. START__LOGMNR process parameter meaning

[NextPage]

4. Observation and Analysis results (v$logmnr_contents)

So far, we have analyzed the contents of the Redo log file. Dynamic performance View V$logmnr_contents contains all the information that Logminer analysis obtains.


If we just want to know what a user is doing with a table, you can get it from the following SQL query, which can get all the work the user db_zgxt on the table SB_DJJL.


It should be emphasized that the results of the analysis in the view v$logmnr_contents only exist during the life of the session we are running ' DBMS_LOGMRN.START_LOGMNR '. This is because all Logminer storage is in PGA memory, all other processes are not visible to it, and as the process ends, the analysis results disappear.

Finally, use the process DBMS_LOGMNR. END_LOGMNR terminates the log parsing transaction, at which point the PGA memory area is cleared and the analysis results are no longer present.

Iv. Other matters of concern

We can use the Logminer Log Analyzer tool to analyze the redo log files generated by other database instances, not just the redo logs files that are used to analyze the database instance in which the Logminer is installed. When you use Logminer to analyze other database instances, there are several points to note:

1. The Logminer must use the dictionary file produced by the parsed database instance instead of the dictionary file generated by the Logminer database, and must ensure that the character set of the Logminer database is installed and the character set of the parsed database is the same.

2. The parsed database platform must be the same as the current Logminer database platform, that is, if the file we are analyzing is generated by Oracle 8i running on the UNIX platform, it must also run on an Oracle instance running on the UNIX platform Logminer , and you cannot run Logminer on other such as Microsoft NT. Of course the hardware conditions for both are not necessarily identical.

3. The Logminer Log Analysis tool is able to analyze only Oracle 8 products, and for 8 of previous products, the tool is also powerless.

Five, the conclusion

Logminer for database administrator (DB

[1] [2] Next page

The Oracle tutorial you are looking at is: Understanding and Using Oracle 8i Analysis tool Logminer. A is a very powerful tool and a tool that is often used in daily work, with the tools to get a lot of information about database activity. One of the most important uses is to recover a change in the database without having to recover the entire database. In addition, the tool can be used to monitor or audit user activity, such as you can use the Logminer tool to see who has modified the data and the state of the data before it is modified. We can also use the tool to analyze any of the redo log files generated by any Oracle 8 and later versions. Another important feature of the tool is that it can analyze the log files of other databases. In short, the tool is a very effective tool for database administrators, and a deep understanding and mastery of the tool is very helpful for the actual work of each database administrator.

Previous page

prev [1] [2]