oracle logminer example by oracle doc
来源:互联网 发布:企业私有云计算 编辑:程序博客网 时间:2024/04/28 04:51
1.
SQL> conn system/oracle
Connected.
SQL> select supplemental_log_data_min fromv$database;
SUPPLEMENTAL_LOG_DATA_MI
------------------------
NO
SQL> alter database add supplemental log data;
Database altered.
SQL> select supplemental_log_data_min fromv$database;
SUPPLEMENTAL_LOG_DATA_MI
------------------------
YES
2.
SQL> alter session set nls_date_format ='yyyy-mm-dd hh24:mi:ss';
Session altered.
SQL> select sysdate from dual;
SYSDATE
-------------------
2009-11-17 22:14:53
3.确认设置了初始化参数:UTL_FILE_DIR
SQL> show parameter utl;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
utl_file_dir string /dataoracle/oracle/logminer
可以看到该参数的当前设置。如果没有值,必须修改数据库的initsid.ora文件,或者使用如下命令:
SQL> alter system set utl_file_dir='/dataoracle/oracle/logminer' scope=spifle;
重新启动数据库,使新加的参数生效
创建字典文件的语句:
SQL>exec sys.dbms_logmnr_d.build(dictionary_filename=>'dictionary.ora', dictionary_location =>'/dataoracle/oracle/logminer ');
其中,dictionary.ora是给字典文件起的文件名(可任意)。
整个创建过程,可能需要十几分钟到一个小时,视该数据库的object个数以及繁忙程度而定。完成后,会在/dataoracle/oracle/logminer目录下看到一个名为dictionary.ora的文件。
a1. Step 1 Determine which redo log file was mostrecently archived.
SQL> select name from v$archived_log wherefirst_time=(select max(first_time) fr
om v$archived_log);
NAME
--------------------------------------------------------------------------------
D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\SADB\ARCHIVELOG\2009_11_17\O1_MF_1_
23_5J5C8H6T_.ARC
a2. Step 2 Specify the list of redo log files to beanalyzed.
SQL> executesys.DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME=>'D:\ORACLE\PRODUCT\10.2.0\
FLASH_RECOVERY_AREA\SADB\ARCHIVELOG\2009_11_17\O1_MF_1_23_5J5C8H6T_.ARC',options
=>sys.DBMS_LOGMNR.NEW);
PL/SQL procedure successfully completed.
SQL> show user
USER is "SYSTEM"
a3. Step 3 Start LogMiner.
SQL> executesys.dbms_logmnr.start_logmnr(OPTIONS=>SYS.DBMS_LOGMNR.DICT_FROM_ONL
INE_CATALOG);
PL/SQL procedure successfully completed.
a4. Step 4 Query the V$LOGMNR_CONTENTSview.
SQL> executesys.dbms_logmnr.start_logmnr(OPTIONS=>SYS.DBMS_LOGMNR.DICT_FROM_ONL
INE_CATALOG);
PL/SQL procedure successfully completed.
SQL> col USR
SQL> col XID for a8
SQL> col sql_redo for a30
SQL> col sql_undo for a30
SQL> SELECT username as USR,(XIDUSN||'.'||XIDSLT||'.'||XIDSQN) AS XID, SQL_REDO
,SQL_UNDO FROM V$LOGMNR_CONTENTS WHERE username IN ('HR','OE');
USR
---- -------- ------------------------------------------------------------
HR
HR
HR
HR
HR
USR
---- -------- ------------------------------------------------------------
HR
HR
HR
HR
USR
---- -------- ------------------------------------------------------------
USR
---- -------- ------------------------------------------------------------
HR
USR
---- -------- ------------------------------------------------------------
HR
HR
HR
HR
HR
HR
HR
HR
HR
HR
HR
USR
---- -------- ------------------------------------------------------------
HR
22 rows selected.
a5 . Step 5 End the LogMiner session.
SQL> execute sys.dbms_logmnr.end_logmnr();
PL/SQL procedure successfully completed.
Example 2: Grouping DML Statements intoCommitted Transactions
SQL> insert into employees(employee_id,first_name, last_name, email, phone_numb
er,hire_date,job_id,salary,commission_pct,manager_id,department_id)values('306'
,'Nandini','Shastry','nshastry','1234567890',to_date('10-jan-2003','dd-mon-yyyy'
),'HR_REP','120000','.05','105','10');
1 row created.
SQL> insert into employees(employee_id,first_name, last_name, email, phone_numb
er,hire_date,job_id,salary,commission_pct,manager_id,department_id)values('307'
,'Johne','Simth','JSM','1234567890',to_date('10-jan-2003','dd-mon-yyyy'),'HR_REP
','120000','.05','105','10');
1 row created.
SQL> commit;
Commit complete.
====================== test 2 the online log test============================
=====================================================================
b1. the prepare steps
SQL> show parameter UTL_FILE_DIR;
NAME
------------------------------------ ------------------------------------
utl_file_dir
SQL> select supplemental_log_data_min fromv$database;
SUPPLEMENTAL_LOG_DATA_MI
------------------------
YES
IF UTL_FILE_DIR IS NULL
SQL> alter system SET UTL_FILE_DIR ='D:\oracle\product\10.2.0\logminer'scope=spfile;
SQL> shutdown immediate;
SQL> startup;
IF the system does not have dbms_logmnr, dbms_logmnr_dpackage
Please execute the follwong sql with sys user.
$ORACLE_HOME/rdbms/admin/dbmslm.sql
$ORACLE_HOME/rdbms/admin/dbmslmd.sql
$ORACLE_HOME/rdbms/admin/dbmslms.sql
b2). build the log dictionary file and add the onlinelog.
SQL> select group#, sequence#, archived, statusfrom v$log;
---------- ---------- ---------------------------------------------------------
SQL> col member for a40
SQL> col status for a9
SQL> col type for a6
SQL> col is_recove for a4
SQL> select * from v$logfile;
---------- --------- ----------------------------------------------
SQL> execsys.dbms_logmnr_d.build('test.ora','D:\oracle\product\10.2.0\logminer'
);
PL/SQL procedure successfully completed.
SQL> execsys.dbms_logmnr.add_logfile('D:\ORACLE\PRODUCT\10.2.0\ORADATA\SADB\RED
O02.LOG',sys.dbms_logmnr.new);
PL/SQL procedure successfully completed.
SQL> execsys.dbms_logmnr.add_logfile('D:\ORACLE\PRODUCT\10.2.0\ORADATA\SADB\RED
O01.LOG',sys.dbms_logmnr.addfile);
PL/SQL procedure successfully completed.
SQL> execsys.dbms_logmnr.add_logfile('D:\ORACLE\PRODUCT\10.2.0\ORADATA\SADB\RED
O03.LOG',sys.dbms_logmnr.addfile);
PL/SQL procedure successfully completed.
b3). Start the logmnr to analysit the onlinelog.
SQL> executesys.dbms_logmnr.start_logmnr(dictfilename=>'D:\oracle\product\10.2.
0\logminer\test.ora');
PL/SQL procedure successfully completed.
SQL> COL OPERATION FOR A8
SQL> col sql_undo for a30
SQL> col sql_redo for a30
SQL> select operation, sql_redo, sql_undo fromv$logmnr_contents where seg_name=
'EMPLOYEES';
OPERATIOSQL_REDO
-------- ------------------------------------------------------------
INSERT
OPERATIOSQL_REDO
-------- ------------------------------------------------------------
INSERT
OPERATIOSQL_REDO
-------- ------------------------------------------------------------
SQL> execute sys.dbms_logmnr.end_logmnr();
PL/SQL procedure successfully completed.
refer site: http://space.itpub.net/?uid-12045182-action-viewspace-itemid-432982
and oracle document (/b14215/logminer.htm)
- oracle logminer example by oracle doc
- LogMiner by Example
- ORACLE LOGMINER
- Oracle LogMiner
- oracle logminer
- Oracle Logminer
- Oracle PL/SQL by Example
- oracle logminer 实战
- Oracle Logminer 说明
- Oracle LogMiner 学习点滴
- Oracle LogMiner工具 SCN
- Oracle LogMiner 使用
- Oracle Logminer 说明
- oracle logminer 使用总结!
- oracle logMiner操作步骤
- Oracle 11g logminer
- Oracle Logminer使用
- Oracle logminer使用方法总结
- light 1024 Eid 大数除法+多个数的最小公倍数
- SEO优化网站需求综合哪些数据
- 程序员之四 设计模式
- 开始书写我的Csdn博客
- poj 3264——Balanced Lineup
- oracle logminer example by oracle doc
- error C2061: 语法错误 : 标识符“SHANDLE_PTR”
- Struts2学习笔记
- Hibernate怎么知道配置文件放置的位置,以及要加载那个配置文件?
- 在linux的shell脚本中,双引号,单引号,以及反单引号的作用介绍,以及env以及set的作用
- LDTP
- 检查点
- 解决firefox无法看视频的方法(centos 6.4)
- C++中const要点整理