这篇文章主要讲解了“logminer怎么使用”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“logminer怎么使用”吧！ Log
这篇文章主要讲解了“logminer怎么使用”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“logminer怎么使用”吧！
LogMiner两种使用类型，一种是使用源数据库的数据字典分析DML操作，别一种是摘取LogMiner数据字典到字典文件分析DDL操作。
注意事项：1.使用logmnr工具最好配置补充日志，不然最终不到ddl操作
2.对于ddl操作必须配置utl_file_dir参数，这个参数为logmnr字典文件的目录,而对于查询dml操作可以不用配置。
LogMiner 工具即可以用来分析在线，也可以用来分析离线日志文件，即可以分析本身自己数据库的重作日志文件，也可以用来分析其他数据库的重作日志文件。
总的说来，LogMiner工具的主要用途有：
1、跟踪数据库的变化：可以离线的跟踪数据库的变化，而不会影响在线系统的性能。
2、回退数据库的变化：回退特定的变化数据，减少point-in-time recovery的执行。
3、优化和扩容计划：可通过分析日志文件中的数据以分析数据增长模式
一、确定数据库的逻辑损坏时间。假定某个用户执行drop table误删除了重要表sales，通过LogMiner可以准确定位该误操作的执行时间和SCN值，然后通过基于时间恢复或者基于SCN恢复可以完全恢复该表数据。
二、确定事务级要执行的精细逻辑恢复操作。假定某些用户在某表上执行了一系列DML操作并提交了事务，并且其中某个用户的DML操作存在错误。通过LogMiner可以取得任何用户的DML操作及相应的UNDO操作，通过执行UNDO操作可以取消用户的错误操作。
三、执行后续审计。通过LogMiner可以跟踪oracle数据库的所有DML、DDL和DCL操作，从而取得执行这些操作的时间顺序、执行这些操作的用户等信息。
1、LogMiner基本对象
源数据库（source database）：该数据库是指包含了要分析重做日志和归档日志的产品数据库。
分析数据库（mining database）：该数据库是指执行LogMiner操作所要使用的数据库。
LogMiner字典：LogMiner字典用于将内部对象ID号和数据类型转换为对象名和外部数据格式。使用LogMiner分析重做日志和归档日志时，应该生成LogMiner字典，否则将无法读懂分析结果。
2、LogMiner配置要求
（1）源数据库和分析数据库 （源数据库和分析数据库可以是同一个数据库）
源数据库和分析数据库必须运行在相同硬件平台上；
分析数据库可以是独立数据库或源数据库；
分析数据库的版本不能低于源数据库的版本；
分析数据库与源数据库必须具有相同的字符集。
（2）LogMiner字典：LogMiner字典必须在源数据库中生成。
（3）重做日志文件
当分析多个重做日志和归档日志时，它们必须是同一个源数据库的重做日志和归档日志；
当分析多个重做日志和归档日志时，它们必须具有相同的resetlogs scn；
当分析的重做日志和归档日志必须在Oracle8.0版本以上。
3、补充日志（suppplemental logging）
重做日志用于实现例程恢复和介质恢复，这些操作所需要的数据被自动记录在重做日志中。但是，重做应用可能还需要记载其他列信息到重做日志中，记录其他列的日志过程被称为补充日志。默认情况下，Oracle数据库没有提供任何补充日志，从而导致默认情况下LogMiner无法支持以下特征：
索引簇、链行和迁移行；
直接路径插入；
摘取LogMiner字典到重做日志；
跟踪DDL；
生成键列的sql_REDO和SQL_UNDO信息；
LONG和LOB数据类型。
因此，为了充分利用LogMiner提供的特征，必须激活补充日志。
语法：
理想情况下，LogMiner字典文件将在完成所有数据库字典更改后创建，并在创建要分析的任何重做日志文件之前创建。 从Oracle9i发行版本1（9.0.1）开始，可以使用LogMiner将LogMiner字典转储到重做日志文件或平面文件，执行DDL操作，并将DDL更改动态应用于LogMiner字典。
另外，应该启用补充日志记录（至少是最低级别），以确保您可以利用LogMiner提供的所有功能。 有关在LogMiner中使用补充日志记录的信息，请参见Oracle数据库实用程序。
实验一：开归档、不开启补充日志及不增加logminer数据字典，(使用DBMS_LOGMNR_D.BUILD)
--查看归档路径及路径下的日志：
SQL> arcHive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 14
Next log sequence to archive 16
Current log sequence 16
SQL>
SQL>
SQL> show parameter recover
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u01/app/oracle/fast_recovery_ area/DBDB/newback
db_recovery_file_dest_size big integer 9G
db_unrecoverable_scn_tracking boolean TRUE
recovery_parallelism integer 0
SQL>
SQL> alter session set nls_date_fORMat='yyyy-mm-dd hh34:mi:ss';
Session altered.
SQL> select *
from (SELECT NAME,
THREAD#,
SEQUENCE#,
APPLIED,
ARCHIVED,
COMPLETION_TIME
FROM V$ARCHIVED_LOG order by 6 desc) a
where rownum <= 10;
NAME THREAD# SEQUENCE# APPLIED ARC COMPLETION_TIME
--------------------------------------------------------------------------------------------------------- ---------- ---------- --------- --- -------------------
/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_15_f6m8flqg_.arc 1 15 NO YES 2018-01-25 17:21:56
/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_14_f6m5v9ts_.arc 1 14 NO YES 2018-01-25 16:38:03
/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_13_f6m56dgw_.arc 1 13 NO YES 2018-01-25 16:26:52
/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_12_f6m4nz1x_.arc 1 12 NO YES 2018-01-25 16:17:35
/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_11_f6l9phsx_.arc 1 11 NO YES 2018-01-25 08:37:35
/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_10_f6l9pgvh_.arc 1 10 NO YES 2018-01-25 08:37:34
/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_9_f6l9pb8o_.arc 1 9 NO YES 2018-01-25 08:37:30
/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_8_f6l9p9jl_.arc 1 8 NO YES 2018-01-25 08:37:29
/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_7_f6l9p6xp_.arc 1 7 NO YES 2018-01-25 08:37:26
/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_6_f6l9p5k2_.arc 1 6 NO YES 2018-01-25 08:37:25
10 rows selected.
查询得，当前将要归档的日志为16
--查询在线日志组
SQL> select group#,THREAD#,SEQUENCE#,MEMBERS,ARCHIVED,STATUS from v$log;
GROUP# THREAD# SEQUENCE# MEMBERS ARC STATUS
---------- ---------- ---------- ---------- --- ----------------
1 1 16 1 NO CURRENT
2 1 14 1 YES INACTIVE
3 1 15 1 YES INACTIVE
--查询日志组：
SQL> select t1.GROUP#,t1.STATUS,t2.MEMBER,t2.TYPE from v$log t1 inner join v$logfile t2 on t1.GROUP#=t2.GROUP# and t1.STATUS='CURRENT';
GROUP# STATUS MEMBER TYPE
---------- ---------------- ------------------------------------------------------------ -------
1 CURRENT /u01/app/oracle/oradata/DBdb/redo01.log ONLINE
查询得，当前的日志组为group#1，sequence#为16
--模拟操作：
SQL> conn hr/hr;
Connected.
SQL>
SQL> create table log_a (x int);
Table created.
SQL> insert into log_a values(1);
1 row created.
SQL> insert into log_a values(2);
1 row created.
SQL> insert into log_a values(3);
1 row created.
SQL> commit;
Commit complete.
SQL> update log_a set x=4 where x=1;
1 row updated.
SQL> commit;
Commit complete.
SQL> select * from log_a;
X
----------
4
2
3
--查询正在工作的日志组
SQL> select group#,THREAD#,SEQUENCE#,MEMBERS,ARCHIVED,STATUS from v$log;
GROUP# THREAD# SEQUENCE# MEMBERS ARC STATUS
---------- ---------- ---------- ---------- --- ----------------
1 1 16 1 NO CURRENT
2 1 14 1 YES INACTIVE
3 1 15 1 YES INACTIVE
SQL> select t1.GROUP#,t1.STATUS,t2.MEMBER,t2.TYPE from v$log t1 inner join v$logfile t2 on t1.GROUP#=t2.GROUP# and t1.STATUS='CURRENT';
GROUP# STATUS MEMBER TYPE
---------- ---------------- ------------------------------------------------------------ -------
1 CURRENT /u01/app/oracle/oradata/DBdb/redo01.log ONLINE
--然后去分析在线redo日志
SQL> exec dbms_logmnr.add_logfile('/u01/app/oracle/oradata/DBdb/redo01.log',dbms_logmnr.new);
PL/SQL procedure successfully completed.
SQL> exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);
PL/SQL procedure successfully completed.
SQL> create table l1_Z1 as select * from v$logmnr_contents;
Table created.
SQL> exec dbms_logmnr.end_logmnr();
PL/SQL procedure successfully completed.
--然后去查看分析的在线redo日志，在sql_redo里面可以看到曾经的操作。
SQL> select start_timestamp,sql_redo,sql_undo from l1_Z1 where sql_redo like '%LOG_A%';
START_TIMESTAMP SQL_REDO SQL_UNDO
------------------- ------------------------------------------------------------ ------------------------------------------------------------
delete from "SYS"."OBJ$" where "OBJ#" = '90306' and "DATAOBJ insert into "SYS"."OBJ$"("OBJ#","DATAOBJ#","OWNER#","NAME","
#" = '90306' and "OWNER#" = '84' and "NAME" = 'LOG_A' and "N NAMESPACE","SUBNAME","TYPE#","CTIME","MTIME","STIME","STATUS
AMESPACE" = '1' and "SUBNAME" IS NULL and "TYPE#" = '2' and ","REMOTEOWNER","LINKNAME","FLAGS","OID$","SPARE1","SPARE2",
"CTIME" = TO_DATE('2018-01-25 16:13:31', 'yyyy-mm-dd hh34:mi "SPARE3","SPARE4","SPARE5","SPARE6") values ('90306','90306'
:ss') and "MTIME" = TO_DATE('2018-01-25 16:13:31', 'yyyy-mm- ,'84','LOG_A','1',NULL,'2',TO_DATE('2018-01-25 16:13:31', 'y
dd hh34:mi:ss') and "STIME" = TO_DATE('2018-01-25 16:13:31', yyy-mm-dd hh34:mi:ss'),TO_DATE('2018-01-25 16:13:31', 'yyyy-
'yyyy-mm-dd hh34:mi:ss') and "STATUS" = '1' and "REMOTEOWNE mm-dd hh34:mi:ss'),TO_DATE('2018-01-25 16:13:31', 'yyyy-mm-d
R" IS NULL and "LINKNAME" IS NULL and "FLAGS" = '0' and "OID d hh34:mi:ss'),'1',NULL,NULL,'0',NULL,'6','1','84',NULL,NULL
$" IS NULL and "SPARE1" = '6' and "SPARE2" = '1' and "SPARE3 ,NULL);
" = '84' and "SPARE4" IS NULL and "SPARE5" IS NULL and "SPAR
E6" IS NULL and ROWID = 'AAAAASAABAAAVC0AAV';
START_TIMESTAMP SQL_REDO SQL_UNDO
------------------- ------------------------------------------------------------ ------------------------------------------------------------
insert into "SYS"."OBJ$"("OBJ#","DATAOBJ#","OWNER#","NAME"," delete from "SYS"."OBJ$" where "OBJ#" = '90317' and "DATAOBJ
NAMESPACE","SUBNAME","TYPE#","CTIME","MTIME","STIME","STATUS #" = '90317' and "OWNER#" = '84' and "NAME" = 'LOG_A' and "N
","REMOTEOWNER","LINKNAME","FLAGS","OID$","SPARE1","SPARE2", AMESPACE" = '1' and "SUBNAME" IS NULL and "TYPE#" = '2' and
"SPARE3","SPARE4","SPARE5","SPARE6") values ('90317','90317' "CTIME" = TO_DATE('2018-01-25 17:48:46', 'yyyy-mm-dd hh34:mi
,'84','LOG_A','1',NULL,'2',TO_DATE('2018-01-25 17:48:46', 'y :ss') and "MTIME" = TO_DATE('2018-01-25 17:48:46', 'yyyy-mm-
yyy-mm-dd hh34:mi:ss'),TO_DATE('2018-01-25 17:48:46', 'yyyy- dd hh34:mi:ss') and "STIME" = TO_DATE('2018-01-25 17:48:46',
mm-dd hh34:mi:ss'),TO_DATE('2018-01-25 17:48:46', 'yyyy-mm-d 'yyyy-mm-dd hh34:mi:ss') and "STATUS" = '1' and "REMOTEOWNE
d hh34:mi:ss'),'1',NULL,NULL,'0',NULL,'6','1','84',NULL,NULL R" IS NULL and "LINKNAME" IS NULL and "FLAGS" = '0' and "OID
,NULL); $" IS NULL and "SPARE1" = '6' and "SPARE2" = '1' and "SPARE3
" = '84' and "SPARE4" IS NULL and "SPARE5" IS NULL and "SPAR
START_TIMESTAMP SQL_REDO SQL_UNDO
------------------- ------------------------------------------------------------ ------------------------------------------------------------
E6" IS NULL and ROWID = 'AAAAASAABAAAVC0AAZ';
insert into "HR"."LOG_A"("X") values ('1'); delete from "HR"."LOG_A" where "X" = '1' and ROWID = 'AAAWDN
AAEAAAAzrAAA';
insert into "HR"."LOG_A"("X") values ('2'); delete from "HR"."LOG_A" where "X" = '2' and ROWID = 'AAAWDN
AAEAAAAzrAAB';
insert into "HR"."LOG_A"("X") values ('3'); delete from "HR"."LOG_A" where "X" = '3' and ROWID = 'AAAWDN
AAEAAAAzrAAC';
实验继续：
--查询：
SQL> select group#,THREAD#,SEQUENCE#,MEMBERS,ARCHIVED,STATUS from v$log;
GROUP# THREAD# SEQUENCE# MEMBERS ARC STATUS
---------- ---------- ---------- ---------- --- ----------------
1 1 16 1 NO CURRENT
2 1 14 1 YES INACTIVE
3 1 15 1 YES INACTIVE
SQL> select t1.GROUP#,t1.STATUS,t2.MEMBER,t2.TYPE from v$log t1 inner join v$logfile t2 on t1.GROUP#=t2.GROUP# and t1.STATUS='CURRENT';
GROUP# STATUS MEMBER TYPE
---------- ---------------- ------------------------------------------------------------ -------
1 CURRENT /u01/app/oracle/oradata/DBdb/redo01.log ONLINE
SQL> l
1 select *
2 from (SELECT NAME,
3 THREAD#,
4 SEQUENCE#,
5 APPLIED,
6 ARCHIVED,
7 COMPLETION_TIME
8 FROM V$ARCHIVED_LOG order by 6 desc) a
9* where rownum <= 10
SQL> /
NAME THREAD# SEQUENCE# APPLIED ARC COMPLETION_T
--------------------------------------------------------------------------------------------------------- ---------- ---------- --------- --- ------------
/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_15_f6m8flqg_.arc 1 15 NO YES 25-JAN-18
/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_14_f6m5v9ts_.arc 1 14 NO YES 25-JAN-18
/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_13_f6m56dgw_.arc 1 13 NO YES 25-JAN-18
/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_12_f6m4nz1x_.arc 1 12 NO YES 25-JAN-18
/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_11_f6l9phsx_.arc 1 11 NO YES 25-JAN-18
/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_10_f6l9pgvh_.arc 1 10 NO YES 25-JAN-18
/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_9_f6l9pb8o_.arc 1 9 NO YES 25-JAN-18
/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_8_f6l9p9jl_.arc 1 8 NO YES 25-JAN-18
/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_7_f6l9p6xp_.arc 1 7 NO YES 25-JAN-18
/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_6_f6l9p5k2_.arc 1 6 NO YES 25-JAN-18
10 rows selected.
--日志组切换：
SQL> alter system switch logfile;
System altered.
--再次查询
SQL> select group#,THREAD#,SEQUENCE#,MEMBERS,ARCHIVED,STATUS from v$log;
GROUP# THREAD# SEQUENCE# MEMBERS ARC STATUS
---------- ---------- ---------- ---------- --- ----------------
1 1 16 1 YES ACTIVE
2 1 17 1 NO CURRENT
3 1 15 1 YES INACTIVE
SQL> select t1.GROUP#,t1.STATUS,t2.MEMBER,t2.TYPE from v$log t1 inner join v$logfile t2 on t1.GROUP#=t2.GROUP# and t1.STATUS='CURRENT';
GROUP# STATUS MEMBER TYPE
---------- ---------------- ------------------------------------------------------------ -------
2 CURRENT /u01/app/oracle/oradata/DBdb/redo02.log ONLINE
SQL>
SQL> l
1 select *
2 from (SELECT NAME,
3 THREAD#,
4 SEQUENCE#,
5 APPLIED,
6 ARCHIVED,
7 COMPLETION_TIME
8 FROM V$ARCHIVED_LOG order by 6 desc) a
9* where rownum <= 10
SQL> /
NAME THREAD# SEQUENCE# APPLIED ARC COMPLETION_T
--------------------------------------------------------------------------------------------------------- ---------- ---------- --------- --- ------------
/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_16_f6mbk78z_.arc 1 16 NO YES 25-JAN-18
/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_15_f6m8flqg_.arc 1 15 NO YES 25-JAN-18
/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_14_f6m5v9ts_.arc 1 14 NO YES 25-JAN-18
/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_13_f6m56dgw_.arc 1 13 NO YES 25-JAN-18
/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_12_f6m4nz1x_.arc 1 12 NO YES 25-JAN-18
/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_11_f6l9phsx_.arc 1 11 NO YES 25-JAN-18
/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_10_f6l9pgvh_.arc 1 10 NO YES 25-JAN-18
/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_9_f6l9pb8o_.arc 1 9 NO YES 25-JAN-18
/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_8_f6l9p9jl_.arc 1 8 NO YES 25-JAN-18
/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_7_f6l9p6xp_.arc 1 7 NO YES 25-JAN-18
10 rows selected.
SQL>
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 15
Next log sequence to archive 17
Current log sequence 17
SQL>
--使用logminer,分析归档日志：
SQL> exec DBMS_LOGMNR.ADD_LOGFILE ('/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_16_f6mbk78z_.arc',dbms_logmnr.new);
PL/SQL procedure successfully completed.
SQL> exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);
PL/SQL procedure successfully completed.
SQL> create table dt1 as select * from v$logmnr_contents;
Table created.
SQL> exec dbms_logmnr.end_logmnr();
PL/SQL procedure successfully completed.
SQL> select start_timestamp,sql_redo,sql_undo from dt1 where sql_redo like '%LOG_A%';
START_TIMESTAMP SQL_REDO SQL_UNDO
------------------- ------------------------------------------------------------ ------------------------------------------------------------
delete from "SYS"."OBJ$" where "OBJ#" = '90306' and "DATAOBJ insert into "SYS"."OBJ$"("OBJ#","DATAOBJ#","OWNER#","NAME","
#" = '90306' and "OWNER#" = '84' and "NAME" = 'LOG_A' and "N NAMESPACE","SUBNAME","TYPE#","CTIME","MTIME","STIME","STATUS
AMESPACE" = '1' and "SUBNAME" IS NULL and "TYPE#" = '2' and ","REMOTEOWNER","LINKNAME","FLAGS","OID$","SPARE1","SPARE2",
"CTIME" = TO_DATE('2018-01-25 16:13:31', 'yyyy-mm-dd hh34:mi "SPARE3","SPARE4","SPARE5","SPARE6") values ('90306','90306'
:ss') and "MTIME" = TO_DATE('2018-01-25 16:13:31', 'yyyy-mm- ,'84','LOG_A','1',NULL,'2',TO_DATE('2018-01-25 16:13:31', 'y
dd hh34:mi:ss') and "STIME" = TO_DATE('2018-01-25 16:13:31', yyy-mm-dd hh34:mi:ss'),TO_DATE('2018-01-25 16:13:31', 'yyyy-
'yyyy-mm-dd hh34:mi:ss') and "STATUS" = '1' and "REMOTEOWNE mm-dd hh34:mi:ss'),TO_DATE('2018-01-25 16:13:31', 'yyyy-mm-d
R" IS NULL and "LINKNAME" IS NULL and "FLAGS" = '0' and "OID d hh34:mi:ss'),'1',NULL,NULL,'0',NULL,'6','1','84',NULL,NULL
$" IS NULL and "SPARE1" = '6' and "SPARE2" = '1' and "SPARE3 ,NULL);
" = '84' and "SPARE4" IS NULL and "SPARE5" IS NULL and "SPAR
E6" IS NULL and ROWID = 'AAAAASAABAAAVC0AAV';
START_TIMESTAMP SQL_REDO SQL_UNDO
------------------- ------------------------------------------------------------ ------------------------------------------------------------
insert into "SYS"."OBJ$"("OBJ#","DATAOBJ#","OWNER#","NAME"," delete from "SYS"."OBJ$" where "OBJ#" = '90317' and "DATAOBJ
NAMESPACE","SUBNAME","TYPE#","CTIME","MTIME","STIME","STATUS #" = '90317' and "OWNER#" = '84' and "NAME" = 'LOG_A' and "N
","REMOTEOWNER","LINKNAME","FLAGS","OID$","SPARE1","SPARE2", AMESPACE" = '1' and "SUBNAME" IS NULL and "TYPE#" = '2' and
"SPARE3","SPARE4","SPARE5","SPARE6") values ('90317','90317' "CTIME" = TO_DATE('2018-01-25 17:48:46', 'yyyy-mm-dd hh34:mi
,'84','LOG_A','1',NULL,'2',TO_DATE('2018-01-25 17:48:46', 'y :ss') and "MTIME" = TO_DATE('2018-01-25 17:48:46', 'yyyy-mm-
yyy-mm-dd hh34:mi:ss'),TO_DATE('2018-01-25 17:48:46', 'yyyy- dd hh34:mi:ss') and "STIME" = TO_DATE('2018-01-25 17:48:46',
mm-dd hh34:mi:ss'),TO_DATE('2018-01-25 17:48:46', 'yyyy-mm-d 'yyyy-mm-dd hh34:mi:ss') and "STATUS" = '1' and "REMOTEOWNE
d hh34:mi:ss'),'1',NULL,NULL,'0',NULL,'6','1','84',NULL,NULL R" IS NULL and "LINKNAME" IS NULL and "FLAGS" = '0' and "OID
,NULL); $" IS NULL and "SPARE1" = '6' and "SPARE2" = '1' and "SPARE3
" = '84' and "SPARE4" IS NULL and "SPARE5" IS NULL and "SPAR
START_TIMESTAMP SQL_REDO SQL_UNDO
------------------- ------------------------------------------------------------ ------------------------------------------------------------
E6" IS NULL and ROWID = 'AAAAASAABAAAVC0AAZ';
insert into "HR"."LOG_A"("X") values ('1'); delete from "HR"."LOG_A" where "X" = '1' and ROWID = 'AAAWDN
AAEAAAAzrAAA';
insert into "HR"."LOG_A"("X") values ('2'); delete from "HR"."LOG_A" where "X" = '2' and ROWID = 'AAAWDN
AAEAAAAzrAAB';
insert into "HR"."LOG_A"("X") values ('3'); delete from "HR"."LOG_A" where "X" = '3' and ROWID = 'AAAWDN
AAEAAAAzrAAC';
综上实验，在没有开启补充日志的情况下，ddl操作不能被logminer挖掘出来,且dml操作也不能完全被挖掘出来。
实验二：开区补充日志
语法：alter database add(drop) supplemental log data；
SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;
SUPPLEME
--------
NO
SQL> alter database add supplemental log data;
Database altered.
SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;
SUPPLEME
--------
YES
PS：这里如果不打开的话，在分析归档日志的时候，就看不到执行操作的Machine、os_name、user_name等等，对分析操作排查问题会产生很大困扰。supplemental lsogging（扩充日志）在通常情况下，redo log 只记录的进行恢复所必需的信息，但是这些信息对于我们使用redo log进行一些其他应用时是不够的，例如在 redo log中使用rowid唯一标识一行而不是通过Primary key，如果我们在另外的数据库分析这些日志并想重新执行某些dml时就可能会有问题，因为不同的数据库其rowid代表的内容是不同的。在这时候就需要一些额外的信息（columns）加入redo log，这就是supplemental logging。
--检查：
SQL> conn hr/hr;
Connected.
SQL> create table log_b (x int);
Table created.
SQL> insert into log_b values(1);
1 row created.
SQL> insert into log_b values(2);
1 row created.
SQL> insert into log_b values(3);
1 row created.
SQL> commit;
Commit complete.
SQL> update log_b set x=4 where x=1;
1 row updated.
SQL> commit;
Commit complete.
SQL> select * from log_b;
X
----------
4
2
3
SQL>
--检查日志组：
SQL> select t1.GROUP#,t1.STATUS,t2.MEMBER,t2.TYPE from v$log t1 inner join v$logfile t2 on t1.GROUP#=t2.GROUP# and t1.STATUS='CURRENT';
GROUP# STATUS MEMBER TYPE
---------- ---------------- ------------------------------------------------------------ -------
2 CURRENT /u01/app/oracle/oradata/DBdb/redo02.log ONLINE
SQL> select group#,THREAD#,SEQUENCE#,MEMBERS,ARCHIVED,STATUS from v$log;
GROUP# THREAD# SEQUENCE# MEMBERS ARC STATUS
---------- ---------- ---------- ---------- --- ----------------
1 1 16 1 YES INACTIVE
2 1 17 1 NO CURRENT
3 1 15 1 YES INACTIVE
SQL> select t1.GROUP#,t1.STATUS,t2.MEMBER,t2.TYPE from v$log t1 inner join v$logfile t2 on t1.GROUP#=t2.GROUP# and t1.STATUS='CURRENT';
GROUP# STATUS MEMBER TYPE
---------- ---------------- ------------------------------------------------------------ -------
2 CURRENT /u01/app/oracle/oradata/DBdb/redo02.log ONLINE
SQL>
--分析在线日志：
SQL> exec dbms_logmnr.add_logfile('/u01/app/oracle/oradata/DBdb/redo02.log',dbms_logmnr.new);
PL/SQL procedure successfully completed.
SQL> exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);
PL/SQL procedure successfully completed.
SQL> create table l1_Z2 as select * from v$logmnr_contents;
Table created.
SQL> exec dbms_logmnr.end_logmnr();
PL/SQL procedure successfully completed.
SQL> select start_timestamp,sql_redo,sql_undo from l1_Z2 where sql_redo like '%LOG_B%';
START_TIMESTAMP SQL_REDO SQL_UNDO
------------------- ------------------------------------------------------------ ------------------------------------------------------------
insert into "SYS"."OBJ$"("OBJ#","DATAOBJ#","OWNER#","NAME"," delete from "SYS"."OBJ$" where "OBJ#" = '90320' and "DATAOBJ
NAMESPACE","SUBNAME","TYPE#","CTIME","MTIME","STIME","STATUS #" = '90320' and "OWNER#" = '84' and "NAME" = 'LOG_B' and "N
","REMOTEOWNER","LINKNAME","FLAGS","OID$","SPARE1","SPARE2", AMESPACE" = '1' and "SUBNAME" IS NULL and "TYPE#" = '2' and
"SPARE3","SPARE4","SPARE5","SPARE6") values ('90320','90320' "CTIME" = TO_DATE('2018-01-25 18:16:06', 'yyyy-mm-dd hh34:mi
,'84','LOG_B','1',NULL,'2',TO_DATE('2018-01-25 18:16:06', 'y :ss') and "MTIME" = TO_DATE('2018-01-25 18:16:06', 'yyyy-mm-
yyy-mm-dd hh34:mi:ss'),TO_DATE('2018-01-25 18:16:06', 'yyyy- dd hh34:mi:ss') and "STIME" = TO_DATE('2018-01-25 18:16:06',
mm-dd hh34:mi:ss'),TO_DATE('2018-01-25 18:16:06', 'yyyy-mm-d 'yyyy-mm-dd hh34:mi:ss') and "STATUS" = '1' and "REMOTEOWNE
d hh34:mi:ss'),'1',NULL,NULL,'0',NULL,'6','1','84',NULL,NULL R" IS NULL and "LINKNAME" IS NULL and "FLAGS" = '0' and "OID
,NULL); $" IS NULL and "SPARE1" = '6' and "SPARE2" = '1' and "SPARE3
" = '84' and "SPARE4" IS NULL and "SPARE5" IS NULL and "SPAR
E6" IS NULL and ROWID = 'AAAAASAABAAAVC0AAb';
START_TIMESTAMP SQL_REDO SQL_UNDO
------------------- ------------------------------------------------------------ ------------------------------------------------------------
insert into "HR"."LOG_B"("X") values ('1'); delete from "HR"."LOG_B" where "X" = '1' and ROWID = 'AAAWDQ
AAEAAAAzzAAA';
insert into "HR"."LOG_B"("X") values ('2'); delete from "HR"."LOG_B" where "X" = '2' and ROWID = 'AAAWDQ
AAEAAAAzzAAB';
insert into "HR"."LOG_B"("X") values ('3'); delete from "HR"."LOG_B" where "X" = '3' and ROWID = 'AAAWDQ
AAEAAAAzzAAC';
update "HR"."LOG_B" set "X" = '4' where "X" = '1' and ROWID update "HR"."LOG_B" set "X" = '1' where "X" = '4' and ROWID
START_TIMESTAMP SQL_REDO SQL_UNDO
------------------- ------------------------------------------------------------ ------------------------------------------------------------
= 'AAAWDQAAEAAAAzzAAA'; = 'AAAWDQAAEAAAAzzAAA';
SQL>
SQL> select group#,THREAD#,SEQUENCE#,MEMBERS,ARCHIVED,STATUS from v$log;
GROUP# THREAD# SEQUENCE# MEMBERS ARC STATUS
---------- ---------- ---------- ---------- --- ----------------
1 1 16 1 YES INACTIVE
2 1 17 1 NO CURRENT
3 1 15 1 YES INACTIVE
SQL>
SQL> select t1.GROUP#,t1.STATUS,t2.MEMBER,t2.TYPE from v$log t1 inner join v$logfile t2 on t1.GROUP#=t2.GROUP# and t1.STATUS='CURRENT';
GROUP# STATUS MEMBER TYPE
---------- ---------------- ------------------------------------------------------------ -------
2 CURRENT /u01/app/oracle/oradata/DBdb/redo02.log ONLINE
SQL> alter system switch logfile;
System altered.
SQL> select group#,THREAD#,SEQUENCE#,MEMBERS,ARCHIVED,STATUS from v$log;
GROUP# THREAD# SEQUENCE# MEMBERS ARC STATUS
---------- ---------- ---------- ---------- --- ----------------
1 1 16 1 YES INACTIVE
2 1 17 1 YES ACTIVE
3 1 18 1 NO CURRENT
SQL> select t1.GROUP#,t1.STATUS,t2.MEMBER,t2.TYPE from v$log t1 inner join v$logfile t2 on t1.GROUP#=t2.GROUP# and t1.STATUS='CURRENT';
GROUP# STATUS MEMBER TYPE
---------- ---------------- ------------------------------------------------------------ -------
3 CURRENT /u01/app/oracle/oradata/DBdb/redo03.log ONLINE
SQL>
SQL> l
1 select *
2 from (SELECT NAME,
3 THREAD#,
4 SEQUENCE#,
5 APPLIED,
6 ARCHIVED,
7 COMPLETION_TIME
8 FROM V$ARCHIVED_LOG order by 6 desc) a
9* where rownum <= 10
SQL> /
NAME THREAD# SEQUENCE# APPLIED ARC COMPLETION_T
--------------------------------------------------------------------------------------------------------- ---------- ---------- --------- --- ------------
/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_17_f6mcyztb_.arc 1 17 NO YES 25-JAN-18
/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_16_f6mbk78z_.arc 1 16 NO YES 25-JAN-18
/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_15_f6m8flqg_.arc 1 15 NO YES 25-JAN-18
/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_14_f6m5v9ts_.arc 1 14 NO YES 25-JAN-18
/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_13_f6m56dgw_.arc 1 13 NO YES 25-JAN-18
/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_12_f6m4nz1x_.arc 1 12 NO YES 25-JAN-18
/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_11_f6l9phsx_.arc 1 11 NO YES 25-JAN-18
/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_10_f6l9pgvh_.arc 1 10 NO YES 25-JAN-18
/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_9_f6l9pb8o_.arc 1 9 NO YES 25-JAN-18
/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_8_f6l9p9jl_.arc 1 8 NO YES 25-JAN-18
10 rows selected.
SQL>
--分析归档日志：
SQL> exec dbms_logmnr.add_logfile('/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_17_f6mcyztb_.arc',dbms_logmnr.new);
PL/SQL procedure successfully completed.
SQL> exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);
PL/SQL procedure successfully completed.
SQL> create table l1_Z2 as select * from v$logmnr_contents;
Table created.
SQL> exec dbms_logmnr.end_logmnr();
PL/SQL procedure successfully completed.
--查分析结果：
select * from dt2 where sql_redo like '%LOG_B%' and table_name='LOG_B';
select table_name,USERNAME,SESSION#,SERIAL# ,start_timestamp,sql_redo,sql_undo from dt2 where table_name='LOG_B';
上述2个sql的START_TIMESTAMP字段、create表记录显示不同。。。
SQL> select table_name,USERNAME,SESSION#,SERIAL# ,start_timestamp,sql_redo,sql_undo from dt2 where table_name='LOG_B';
TABLE_NAME USERNAME SESSION# SERIAL# START_TIMESTAMP SQL_REDO SQL_UNDO
---------- ---------- ---------- ---------- ------------------- -------------------------------------------------- --------------------------------------------------
LOG_B HR 46 67 create table log_b (x int);
LOG_B HR 46 67 insert into "HR"."LOG_B"("X") values ('1'); delete from "HR"."LOG_B" where "X" = '1' and ROWID
= 'AAAWDQAAEAAAAzzAAA';
LOG_B HR 46 67 insert into "HR"."LOG_B"("X") values ('2'); delete from "HR"."LOG_B" where "X" = '2' and ROWID
= 'AAAWDQAAEAAAAzzAAB';
LOG_B HR 46 67 insert into "HR"."LOG_B"("X") values ('3'); delete from "HR"."LOG_B" where "X" = '3' and ROWID
= 'AAAWDQAAEAAAAzzAAC';
LOG_B HR 46 67 update "HR"."LOG_B" set "X" = '4' where "X" = '1' update "HR"."LOG_B" set "X" = '1' where "X" = '4'
TABLE_NAME USERNAME SESSION# SERIAL# START_TIMESTAMP SQL_REDO SQL_UNDO
---------- ---------- ---------- ---------- ------------------- -------------------------------------------------- --------------------------------------------------
and ROWID = 'AAAWDQAAEAAAAzzAAA'; and ROWID = 'AAAWDQAAEAAAAzzAAA';
增加补充日志实验证明，dml操作完全能够被记录挖掘出来。。。。。。。
实验三：在开启补充日志的基础上，增加logminer数据字典，(使用DBMS_LOGMNR_D.BUILD)
--设置参数utl_file_dir，此目录用户存储logminer数据字典
SQL> show parameter utl
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
create_stored_outlines string
utl_file_dir string
SQL>
SQL> alter system set utl_file_dir='/home/oracle/logminer' scope=spfile;
System altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2257840 bytes
Variable Size 549456976 bytes
Database Buffers 281018368 bytes
Redo Buffers 2371584 bytes
Database mounted.
Database opened.
SQL>
SQL>
SQL> show parameter utl
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
create_stored_outlines string
utl_file_dir string /home/oracle/logminer
SQL>
--创建logmnr数据字典文件
SQL> exec dbms_logmnr_d.build(dictionary_filename =>'dictionary.ora',dictionary_location =>'/home/oracle/logminer');
PL/SQL procedure successfully completed.
SQL>
--查询：
SQL> select group#,THREAD#,SEQUENCE#,MEMBERS,ARCHIVED,STATUS from v$log;
GROUP# THREAD# SEQUENCE# MEMBERS ARC STATUS
---------- ---------- ---------- ---------- --- ----------------
1 1 31 1 NO CURRENT
2 1 29 1 YES INACTIVE
3 1 30 1 YES INACTIVE
SQL> select t1.GROUP#,t1.STATUS,t2.MEMBER,t2.TYPE from v$log t1 inner join v$logfile t2 on t1.GROUP#=t2.GROUP# and t1.STATUS='CURRENT';
GROUP# STATUS MEMBER TYPE
---------- ---------------- ------------------------------------------------------------ -------
1 CURRENT /u01/app/oracle/oradata/DBdb/redo01.log ONLINE
SQL>
--实验开始：
SQL> conn hr/hr;
Connected.
SQL>
SQL> create table log_c (x int);
Table created.
SQL> insert into log_c values (4);
1 row created.
SQL> insert into log_c values (4);
1 row created.
SQL> insert into log_c values (4);
1 row created.
SQL> insert into log_c values (4);
1 row created.
SQL> commit;
Commit complete.
SQL> delete log_c where rownum<2;
1 row deleted.
SQL> commit;
Commit complete.
SQL> update log_c set x=5 where rownum<2;
1 row updated.
SQL> commit;
Commit complete.
SQL> create table log_c_bak as select * from log_c;
Table created.
SQL>
--再次查询：
SQL> select group#,THREAD#,SEQUENCE#,MEMBERS,ARCHIVED,STATUS from v$log;
GROUP# THREAD# SEQUENCE# MEMBERS ARC STATUS
---------- ---------- ---------- ---------- --- ----------------
1 1 31 1 NO CURRENT
2 1 29 1 YES INACTIVE
3 1 30 1 YES INACTIVE
SQL> select t1.GROUP#,t1.STATUS,t2.MEMBER,t2.TYPE from v$log t1 inner join v$logfile t2 on t1.GROUP#=t2.GROUP# and t1.STATUS='CURRENT';
GROUP# STATUS MEMBER TYPE
---------- ---------------- ------------------------------------------------------------ -------
1 CURRENT /u01/app/oracle/oradata/DBdb/redo01.log ONLINE
--执行分析在线日志：
SQL> exec DBMS_LOGMNR.ADD_LOGFILE(LogFileName=>'/u01/app/oracle/oradata/DBdb/redo01.log',Options=>dbms_logmnr.new);
PL/SQL procedure successfully completed.
SQL> exec sys.dbms_logmnr.start_logmnr(dictfilename=>'/home/oracle/logminer/dictionary.ora');
PL/SQL procedure successfully completed.
SQL> create table l1_Z5 as select * from v$logmnr_contents;
Table created.
SQL> exec dbms_logmnr.end_logmnr();
PL/SQL procedure successfully completed.
SQL> select table_name,USERNAME,SESSION#,SERIAL# ,start_timestamp,sql_redo,sql_undo from l1_Z5 where table_name like 'LOG_C%';
TABLE_NAME USERNAME SESSION# SERIAL# START_TIMEST SQL_REDO SQL_UNDO
---------- ---------- ---------- ---------- ------------ -------------------------------------------------- --------------------------------------------------
LOG_C HR 38 11 create table log_c (x int);
LOG_C_BAK HR 38 11 create table log_c_bak as select * from log_c;
--查询归档：
SQL> l
1 select *
2 from (SELECT NAME,
3 THREAD#,
4 SEQUENCE#,
5 APPLIED,
6 ARCHIVED,
7 COMPLETION_TIME
8 FROM V$ARCHIVED_LOG order by 6 desc) a
9* where rownum <= 10
SQL> /
NAME THREAD# SEQUENCE# APPLIED ARC COMPLETION_TIME
--------------------------------------------------------------------------------------------------------- ---------- ---------- --------- --- -------------------
/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_34_f6mjwpxs_.arc 1 34 NO YES 2018-01-25 19:46:32
/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_33_f6mjwjfq_.arc 1 33 NO YES 2018-01-25 19:46:26
/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_32_f6mjw7w9_.arc 1 32 NO YES 2018-01-25 19:46:18
/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_31_f6mjw4yn_.arc 1 31 NO YES 2018-01-25 19:46:14
/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_30_f6mh7zlf_.arc 1 30 NO YES 2018-01-25 19:18:23
/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_29_f6mgv88w_.arc 1 29 NO YES 2018-01-25 19:11:38
/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_28_f6mgv49x_.arc 1 28 NO YES 2018-01-25 19:11:34
/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_27_f6mgtvdf_.arc 1 27 NO YES 2018-01-25 19:11:26
/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_26_f6mgtrfy_.arc 1 26 NO YES 2018-01-25 19:11:22
/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_25_f6mghd5p_.arc 1 25 NO YES 2018-01-25 19:05:17
10 rows selected.
--分析归档:【使用exec sys.dbms_logmnr.start_logmnr(dictfilename=>'/home/oracle/logminer/dictionary.ora')分析归档】
SQL> exec DBMS_LOGMNR.ADD_LOGFILE(LogFileName=>'/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_31_f6mjw4yn_.arc',Options=>dbms_logmnr.new);
PL/SQL procedure successfully completed.
SQL>
SQL> exec sys.dbms_logmnr.start_logmnr(dictfilename=>'/home/oracle/logminer/dictionary.ora');
PL/SQL procedure successfully completed.
SQL> create table l1_Z6 as select * from v$logmnr_contents;
Table created.
SQL> exec dbms_logmnr.end_logmnr();
PL/SQL procedure successfully completed.
SQL> select table_name,USERNAME,SESSION#,SERIAL# ,start_timestamp,sql_redo,sql_undo from l1_Z6 where table_name like 'LOG_C%';
TABLE_NAME USERNAME SESSION# SERIAL# START_TIMEST SQL_REDO SQL_UNDO
---------- ---------- ---------- ---------- ------------ -------------------------------------------------- --------------------------------------------------
LOG_C HR 38 11 create table log_c (x int);
LOG_C_BAK HR 38 11 create table log_c_bak as select * from log_c;
--使用dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog)选项分析归档日志
SQL> exec DBMS_LOGMNR.ADD_LOGFILE(LogFileName=>'/u01/app/oracle/fast_recovery_area/DBDB/newback/DBDB/archivelog/2018_01_25/o1_mf_1_31_f6mjw4yn_.arc',Options=>dbms_logmnr.new);
PL/SQL procedure successfully completed.
SQL> exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);
PL/SQL procedure successfully completed.
SQL> create table l1_Z7 as select * from v$logmnr_contents;
Table created.
SQL> exec dbms_logmnr.end_logmnr();
PL/SQL procedure successfully completed.
SQL> select table_name,USERNAME,SESSION#,SERIAL# ,start_timestamp,sql_redo,sql_undo from l1_Z7 where table_name like 'LOG_C%';
TABLE_NAME USERNAME SESSION# SERIAL# START_TIMEST SQL_REDO SQL_UNDO
---------- ---------- ---------- ---------- ------------ -------------------------------------------------- --------------------------------------------------
LOG_C HR 38 11 create table log_c (x int);
LOG_C HR 38 11 insert into "HR"."LOG_C"("X") values ('4'); delete from "HR"."LOG_C" where "X" = '4' and ROWID
= 'AAAWDfAAEAAAA2EAAA';
LOG_C HR 38 11 insert into "HR"."LOG_C"("X") values ('4'); delete from "HR"."LOG_C" where "X" = '4' and ROWID
= 'AAAWDfAAEAAAA2EAAB';
LOG_C HR 38 11 insert into "HR"."LOG_C"("X") values ('4'); delete from "HR"."LOG_C" where "X" = '4' and ROWID
= 'AAAWDfAAEAAAA2EAAC';
LOG_C HR 38 11 insert into "HR"."LOG_C"("X") values ('4'); delete from "HR"."LOG_C" where "X" = '4' and ROWID
TABLE_NAME USERNAME SESSION# SERIAL# START_TIMEST SQL_REDO SQL_UNDO
---------- ---------- ---------- ---------- ------------ -------------------------------------------------- --------------------------------------------------
= 'AAAWDfAAEAAAA2EAAD';
LOG_C HR 38 11 delete from "HR"."LOG_C" where "X" = '4' and ROWID insert into "HR"."LOG_C"("X") values ('4');
= 'AAAWDfAAEAAAA2EAAA';
LOG_C HR 38 11 update "HR"."LOG_C" set "X" = '5' where "X" = '4' update "HR"."LOG_C" set "X" = '4' where "X" = '5'
and ROWID = 'AAAWDfAAEAAAA2EAAB'; and ROWID = 'AAAWDfAAEAAAA2EAAB';
LOG_C_BAK HR 38 11 create table log_c_bak as select * from log_c;
LOG_C_BAK HR 38 11 insert into "HR"."LOG_C_BAK"("X") values ('5'); delete from "HR"."LOG_C_BAK" where "X" = '5' and R
OWID = 'AAAWDgAAEAAAA2LAAA';
TABLE_NAME USERNAME SESSION# SERIAL# START_TIMEST SQL_REDO SQL_UNDO
---------- ---------- ---------- ---------- ------------ -------------------------------------------------- --------------------------------------------------
LOG_C_BAK HR 38 11 insert into "HR"."LOG_C_BAK"("X") values ('4'); delete from "HR"."LOG_C_BAK" where "X" = '4' and R
OWID = 'AAAWDgAAEAAAA2LAAB';
LOG_C_BAK HR 38 11 insert into "HR"."LOG_C_BAK"("X") values ('4'); delete from "HR"."LOG_C_BAK" where "X" = '4' and R
OWID = 'AAAWDgAAEAAAA2LAAC';
11 rows selected.
SQL>
实验证明，在开启补充日志和增加logminer数据字段文件的基础下，ddl/dml被完全记录下来和挖掘出来，注意：使用dbms_logmnr.start_logmnr时，如果指定参数为dbms_logmnr.dict_from_online_catalog记录ddl/dml操作，二而指定参数为dictfilename，则只记录ddl操作。
感谢各位的阅读，以上就是“logminer怎么使用”的内容了，经过本文的学习后，相信大家对logminer怎么使用这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是编程网，小编将为大家推送更多相关知识点的文章，欢迎关注！
--结束END--
本文标题: logminer怎么使用
本文链接: https://www.lsjlt.com/news/70189.html(转载时请注明来源链接)
有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
下载Word文档到电脑，方便收藏和打印～
2024-05-11
2024-05-11
2024-05-11
2024-05-11
2024-05-11
2024-05-11
2024-05-11
2024-05-11
2024-05-11
2024-05-11
回答
回答
回答
回答
回答
回答
回答
回答
回答
回答
官方手机版
微信公众号
商务合作
0