Detailed logs of Oracle's tools - LogMiner

2010-12-22  来源:本站原创  分类:Database  人气:115 

Oracle's redo log is divided into two types of online redo log files (online Redo log) and archived redo logs (Archive Redo log) files; this paper, archived redo log (Archive Redo log) files, online log in the analysis are similar.
Due to various factors, Oracle redo log (redo log) file can not be read directly through a variety of text software, and Oracle LogMiner is to analyze the log comes the best choice.
LogMiner: Oracle from 8i after provided by the form of SQL commands to query and resolve redo (redo) and undo (undo) logging tool.

1 Installation LogMiner - simple
LogMiner is Oracle's own log analysis tools to install as long as you can run the sql script, once installed ok.

First, run the following two such scripts, which are two scripts to be run as the SYS user.
The first script used to create DBMS_LOGMNR package, which is used to analyze log files.
The second script used to create DBMS_LOGMNR_D package, the package used to create data dictionary files.

Two files:
1 $ ORACLE_HOME / rdbms / admin / dbmslm.sql
2. $ ORACLE_HOME / rdbms / admin / dbmslmd.sql.

Operation command:
SQL> @ d: \ oracle \ product \ 10.2.0 \ db_2 \ RDBMS \ ADMIN \ dbmslm.sql
Package has been created.
Grant succeeded.

SQL> @ d: \ oracle \ product \ 10.2.0 \ db_2 \ RDBMS \ ADMIN \ dbmslmd.sql
Package has been created.

(2) create a data dictionary (data-dictionary) - according to actual situation <br /> data dictionary used to parse the log of the hexadecimal data items (such as log sql statement column names).

Three kinds of optional ways to create access for the LogMiner data dictionary.
1) access the database in the online data dictionary.
This approach is simple, convenient, but because the data dictionary is the most current, may not match the information in the last log.
Usage: start logMiner analysis when using DICT_FROM_ONLINE_CATALOG options. As follows:
SQL> EXECUTE dbms_logmnr.start_logmnr (options => dbms_logmnr.DICT_FROM_ONLINE_CATALOG);
Log in to sys sysdba best execute the command.

2) export the data as a dictionary file.
This approach is a point in time the data dictionary export file; logMiner start the analysis time (see Step 4) through the options parameter to specify the file name and path to resolve logs.
If we have to analyze the database tables have changed, affecting the library's data dictionary are changed, then you need to re-create the dictionary file. In addition, the analysis of another database redo log file, it must be analyzed again to re-generate the database data dictionary file.
a. The first adjustment spfile parameters;
b. Then create a data dictionary file;
SQL> EXECUTE (dictionary_filename => 'logmn_ora817.dat', dictionary_location => 'D: Oraclelogs');
# Note, dictionary_location UTL_FILE_DIR parameter must be referred to the same directory.

3) Export the data dictionary to a log file.
To extract database dictionary information to the redo log files, databases must be in ARCHIVELOG mode.
Export, use STORE_IN_REDO_LOGS options. For example:

3, create a list of log files to analyze the log --- <br /> parameters logfilename absolute path value is the absolute path to the log file (string).

A. Adding a log file
SQL> execute dbms_logmnr.add_logfile (options
=> Dbms_logmnr.addfile, logfilename => 'E: \ app \ Administrator \ product \ 11.1.0 \ db_1 \ RDBMS \ ARC00013_0736960501.001');
B. Continue to add. . .
SQL> execute dbms_logmnr.add_logfile (options
=> Dbms_logmnr.addfile, logfilename => 'E: \ app \ Administrator \ product \ 11.1.0 \ db_1 \ RDBMS \ ARC00014_0736960501.001');
Continue to add. . .
SQL> execute dbms_logmnr.add_logfile (options
=> Dbms_logmnr.addfile, logfilename => 'E: \ app \ Administrator \ product \ 11.1.0 \ db_1 \ RDBMS \ ARC00015_0736960501.001');
You can also delete (not used)
SQL> execute dbms_logmnr.add_logfile (options => dbms_logmnr.removefile, logfilename => 'E: \ app \ Administrator \ product \ 11.1.0 \ db_1 \ RDBMS \ ARC00015_0736960501.001');
The number of log files to analyze completely determined by the caller.
In addition, Oracle offers an archive log view (v $ archived_log), through select * from v $ archived_log can query the archived log files (absolute path, archiving time, etc.); (DBA authority is required login)
The v $ archived_log view details please refer to the official Oracle documentation:

4 start LogMiner analysis - the key

(1) unrestricted
SQL> EXECUTE dbms_logmnr.start_logmnr (options => dbms_logmnr.DICT_FROM_ONLINE_CATALOG);

(2) limits the caller's time by dbms_logmnr.start_logmnr command (including StartTime and EndTime) parameters and SCN parameters (including StartScn and EndScn) set, will be screened according to the log.

Time range (use more): the dbms_logmnr.start_logmnr command StartTime and EndTime parameters.
SCN (statement sequence number) Scope: dbms_logmnr.start_logmnr command StartScn and EndScn parameters.

For example, if you want only the analysis of the log 2010-12-07 (Of course, the previous step to add the log file that contains the required time frame, otherwise it will error):
SQL> EXECUTE dbms_logmnr.start_logmnr (StartTime => to_date ('2010-12-07 00:00:00 ',' YYYY-MM-DD HH24: MI: SS '), EndTime => to_date ('2010-12-08 23:59:59 ',' YYYY-MM-DD HH24: MI: SS '), options

If want to just analyze the log within a SCN:
SQL> EXECUTE dbms_logmnr.start_logmnr (options => dbms_logmnr.DICT_FROM_ONLINE_CATALOG, StartScn => 20, EndScn => 50);

DBMS_LOGMNR.START__LOGMNR Command Parameters Parameter Type Default meaning of meaning
StartScn numeric (Number) 0 redo log analysis log file part of the SCN ≥ StartScn
EndScn numeric (Number) 0 redo log analysis log file part of the SCN ≤ EndScn
StartTime date type (Date) 1998-01-01 analyze redo log file timestamp ≥ StartTime part of the log
EndTime date type (Date) 2988-01-01 analyze redo log file timestamp ≤ EndTime part of the log
DictFileName character (VARCHAR2) dictionary file, which contains a snapshot of the database directory. This file can be used to get the results of the analysis is understandable text form, rather than the system's internal hex

Options BINARY_INTEGER 0 system debugging parameters, actually rarely used

The steps involved in data dictionary to use choice:
a) Use the exported text file data dictionary.
SQL> EXECUTE dbms_logmnr.start_logmnr (
DictFileName => 'D: Oraclelogslogmn_ora817.dat');

b) Direct use of online data dictionary.
EXECUTE dbms_logmnr.start_logmnr (options => dbms_logmnr.DICT_FROM_ONLINE_CATALOG);

c) use log files exported to the data dictionary.
EXECUTE dbms_logmnr.start_logmnr (options => dbms_logmnr.DICT_FROM_REDO_LOGS);

5, view the log results of the analysis - the key

Dynamic performance view v $ logmnr_contents LogMiner analysis contains all the information obtained through the Select XX from v $ logmnr_contents can query.
For example:

select SQL_REDO, to_char (TIMESTAMP, 'YYYY-MM-DD HH24: MI: SS'), to_char (COMMIT_TIMESTAMP, 'YYYY-MM-DD HH24: MI: SS') from v $ logmnr_contents

SQL> select sql_redo from v $ logmnr_contents

a, see the DML operation, example:
SELECT operation, sql_redo, sql_undo FROM V $ logmnr_contents
WHERE operation IN ('INSERT', 'UPDATE', 'DELETE') and seg_name = 'QIUYB';

---------- -------------------------- -------------- ------------
INSERT inser into qiuyb.qiuyb ... delete from qiuyb.qiuyb ...

operation - the type of operation.
sql_redo - record execute SQL scripts,
sql_undo - made for the recovery operation, the SQL script with sql_redo opposite.

The above three fields is more critical field, v $ logmnr_contents view more property field can check the official Oracle documentation (11g).

b, view the DDL operation, example:
SELECT timstamp, sql_redo
FROM v $ logmnr_contents WHERE operation IN ('DDL');

LogMiner process of analyzing all of the stores are in the PGA memory is based on the view v $ logmnr_contents dbms_logmrn.start_logmnr session. With the end of the process, after the end of the session, the view v $ logmnr_content disappeared.

6. LogMiner analysis of end to end this logMiner session. - Simple
DBMS_LOGMNR.END_LOGMNR termination of log analysis services, then the PGA memory area is cleared, the LogMiner process of analyzing all disappear, the results also will no longer exist.

2 dbms_logmnr.end_logmnr;
3 end;
4 /

7 Note <br /> in the use of LogMiner log analysis tools to analyze the database instance redo log files generated, not just to analyze the database itself installed instance of LogMiner redo logs files.

LogMiner analysis using other database instance, there are several points to note:
1). LogMiner analysis of the database instance must be produced using the dictionary file (the second step way to create data dictionary), rather than have the database installed LogMiner dictionary file, the other must ensure that the installation and LogMiner database character set is analysis of the same database character set.

2) must be analyzed and the current LogMiner database platform where the database platform, that is if we have to analyze the file by running Oracle 8i on UNIX platforms have, then must also be a run on Oracle on UNIX platforms instance run LogMiner, but not in others such as Microsoft NT running on LogMiner. Of course, the conditions of both the hardware does not necessarily require the same.

3). LogMiner log analysis tools to analyze only the product after Oracle 8i, 8i for the previous product, the tool can not do anything.

4). LogMiner log analysis tools to the data dictionary only way to choose is more important, according to the actual situation carefully chosen.
5). LogMiner log analysis process is somewhat cumbersome, are more involved in the knowledge database, you need patience to debug.

  • Detailed logs of Oracle's tools - LogMiner 2010-12-22

    Foreword Oracle's redo log is divided into two types of online redo log files (online Redo log) and archived redo logs (Archive Redo log) files; this paper, archived redo log (Archive Redo log) files, online log in the analysis are similar. Due to va

  • Oracle log analysis tools - LogMiner Detailed 2010-12-22

    LogMiner ---- Oracle 8i provided after the self through the form of SQL commands to query and resolve redo (redo) and undo (remove) log tool. 1. Install LogMiner Oracle LogMiner is a log analysis tool that comes with the installation as long as you c

  • A detailed description of Oracle indexes finishing 2010-09-08

    1.index need storage space and I / O operations. 2.index is designed to accelerate the speed of the select. 3.insert, update, delete data in oracle while the index will be adjusted accordingly, thereby increasing the consumption of certain. 4 Use the

  • oracle log mining logminer 2011-07-11

    REVIEW: First, listen to the story section of it ~ Two, logminer the basic method 3, the actual examples are likely to be used First, listen to the story section of it ~ p109 oracle redo log file contains the user data and database data dictionary al

  • 30 Detailed rules for Oracle statement optimization 2010-08-26

    1 use for the Oracle Optimizer Oracle's optimizer total of three kinds: a.RULE (rule-based) b.COST (based on cost) c.CHOOSE (optional) Set the default optimizer, you can OPTIMIZER_MODE init.ora file parameters for the various statements, such as RULE

  • Oracle client tools use PL / SQL Developer-User object comparison 2011-06-08

    In the actual project implementation process, from testing to production, in order to ensure that the test used when the database table fields, indexes, views and other elements of production consistent with the need to conduct some more. This can us

  • 深入了解Oracle数据库工具LogMiner 2014-04-13

    LogMiner是Oracle数据库自带的一个工具,可以通过它分析在线日志和归档日志获取数据库过往详细.具体的操作,非常有用. 1 为什么会用到LogMiner? 主要出于以下几个缘由: 1)当数据库发生了误操作,需要不完全恢复,为确认误操作准确的时间点或SCN号,此时需用到LogMiner. 2)传统恢复一个上TB或是恢复一个几百GB表空间中的一个小表,标准的操作是把整个表空间恢复到之前的状态,然后再应用归档日志,加上搭建恢复环境的时间,整个时间会很长.通过LogMiner可以换一种恢复思维,

  • Oracle Performance Tools: Explain plan.Autotrace.Tkprof 2010-09-14

    Oracle EXPLAIN PLAN implementation plan Using EXPLAIN PLAN: # PFGRF009 By analyzing the SQL statement execution plan optimization of SQL (summary):

  • Detailed use of Oracle in the null 2010-11-11

    Q: What is NULL? A: We do not know what specific data, that is unknown, can be NULL, We call it empty, ORACLE in the table with an empty zero-length values. ORACLE allows any type of data field is empty, except for the following two situations: 1, th

  • The simplest connection Oracle Java Tools 2010-11-25

    import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; / * * Admin: lisong * / public class OracleConnectionMgr { public static Connection getConnection () { Conn

  • Detailed information about Oracle outer join 2011-07-06

    Outer join. Outer joins can be left outer join, right outer join or full outer join. Specified in the FROM clause outer join can be conducted by the following groups of keywords in a group designated: LEFT JOIN or LEFT OUTER JOIN. Left outer join res

  • Detailed build.xml Ant build tools 2010-01-29

    A, Ant Introduction Ant is used to compile / run / test java program, building, packaging and distribution process is almost everything can be handled by the Ant tasks, such as: optimization of the code, compile, package, etc. the works. Ant is based

  • Oracle archive log analysis - LogMiner (rpm) 2010-12-03

    Log Analysis Technical Overview: As the Oracle DBA, we sometimes need to track the malicious user data accidentally deleted or operating conditions, then we need not only perform these operations identify the database account, you also need to know w

  • Oracle LISTENER listener file arguments detailed overview of the command and Lsnrctl 2011-02-18

    Oracle LISTENER listener file arguments detailed overview of the command and Lsnrctl # Listener.ora Network Configuration File: F: \ oracle \ product \ 10.2.0 \ db_1 \ network \ admin \ listener.ora # Generated by Oracle configuration tools. LISTENER

  • Detailed listener.ora.sqlnet.ora.tnames.ora Oracle configuration files 2010-12-23

    Three configuration files listener.ora, sqlnet.ora, tnsnames.ora, are placed in $ oracle_home \ network \ admin directory. Focus: the role and use of the three documents sqlnet.ora----- act like linux or other unix's nsswitch.conf file through this f

  • Detailed Oracle bind variables 2011-08-23

    Before finishing off an article about binding variables, less detailed, re-add. Oracle bind variables One. Bind variables bind variable: A variable in a SQL statement that must be re

  • Oracle concepts (Oracle 10.2) 2010-02-20

    1, Oracle introduced This chapter provides an overview for the Oracle database server, contains the following topics Oracle database architecture Oracle Database Features Oracle Database Application Development Oracle database architecture Oracle dat

  • Understand and use Oracle log analysis tools-LogMiner 2010-04-29

    This article is reproduced, the first author is unknown, please contact me. Oracle LogMiner is Oracle 8i from the product after the company provided a very useful analysis of the actual tool, the tool can be easily obtained using the Oracle redo log

  • Oracle architecture: the memory structure and process structure 2010-06-28

    (A) the structure of memory structures and processes Oracle database, the overall structure of the following diagram: 1: Oracle instance (Instance) In a server , each running an Oracle database, database instance are associated with the instance is a

  • From "How to modify the oracle system connections," the summary (b) 2011-06-22

    session & process wrote What is a session Popular terms, session is communicating parties from the beginning to the end of the communication traffic during a context (context). In this cont