logmnr的使用（ddl）

1、创建测试表

SQL> conn scott/tiger;
Connected.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BONUS TABLE
DEPT TABLE
EMP TABLE
SALGRADE TABLE

SQL> create table test1 (id number(5));
Table created.
SQL> insert into test1 values(1);
1 row created.
SQL> insert into test1 values(2);
1 row created.
SQL> insert into test1 values(3);
1 row created.
SQL> commit;
Commit complete.

SQL> select * from test1;
ID
----------
1
2
3

2、查看归档情况
SQL> conn / as sysdba
Connected.
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 4
Current log sequence 6

3、如果没开归档，设置归档
SQL> alter system set log_archive_dest_1='location=/u01/arch' scope=spfile;
System altered.
SQL> alter system set log_archive_format='arch_%t_%s_%r.log' scope=spfile;
System altered.
SQL> shutdown immediate
SQL> startup mount;
SQL> alter database archivelog;
Database altered.

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/arch
Oldest online log sequence 4
Next log sequence to archive 6
Current log sequence 6
SQL> alter database open;

4、查看redo日志情况
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
1 1 4 52428800 512 1 YES INACTIVE 973464 22-JUL-14 994641 22-JUL-14
2 1 5 52428800 512 1 YES INACTIVE 994641 22-JUL-14 1004354 22-JUL-14
3 1 6 52428800 512 1 NO CURRENT 1004354 22-JUL-14 2.8147E+14

5、对当前redo日志进行归档
SQL> alter system archive log current;
System altered.

查看归档情况
SQL> col name for a60
SQL> select name from v$archived_log
NAME
------------------------------------------------------------
/u01/arch/arch_1_6_853583302.log

6、开启supplemental模式
SQL> alter database add supplemental log data;
Database altered.

7、进入到测试用户中，对表进行ddl操作
SQL> conn scott/tiger;
Connected.
SQL> select * from test1;
ID
----------
1
2
3

SQL> drop table test1 purge;
Table dropped.
SQL> create table test2 (id number(5));
Table created.
SQL> insert into test2 values(1);
1 row created.
SQL> commit;
Commit complete.

8、设置logmnr参数，指定位置存放数据字典文件
[oracle@CentOS6 ~]$ mkdir /u01/app/oracle/logmnr
SQL> show parameter utl
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
create_stored_outlines string
utl_file_dir string

SQL> alter system set utl_file_dir='/u01/app/oracle/logmnr' scope=spfile;
System altered.

SQL> show parameter utl;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
create_stored_outlines string
utl_file_dir string

重启库
SQL> startup force;
SQL> show parameter utl;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
create_stored_outlines string
utl_file_dir string /u01/app/oracle/logmnr

9、建立数据字典文件dict.ora
SQL> execute dbms_logmnr_d.build('dict.ora','/u01/app/oracle/logmnr',dbms_logmnr_d.store_in_flat_file);
PL/SQL procedure successfully completed.

10、再次查看归档情况
SQL> col name for a50
SQL> select name,sequence# from v$archived_log;
NAME SEQUENCE#
-------------------------------------------------- ----------
/u01/arch/arch_1_6_853583302.log 6
/u01/arch/arch_1_7_853583302.log 7

SQL> col status for a12
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ------------ ------------- --------- ------------ ---------
1 1 7 52428800 512 1 YES INACTIVE 1007859 23-JUL-14 1028172 23-JUL-14
2 1 8 52428800 512 1 NO CURRENT 1028172 23-JUL-14 2.8147E+14
3 1 6 52428800 512 1 YES INACTIVE 1004354 22-JUL-14 1007859 23-JUL-14

SQL> col member for a50
SQL> select * from v$logfile
GROUP# STATUS TYPE MEMBER IS_
---------- ------------ ------- -------------------------------------------------- ---
3 ONLINE /u01/app/oracle/oradata/zgdky/redo03.log NO
2 ONLINE /u01/app/oracle/oradata/zgdky/redo02.log NO
1 ONLINE /u01/app/oracle/oradata/zgdky/redo01.log NO

11、分析日志
使用当前redo日志文件，使用目标可能存在的归档文件
SQL> execute dbms_logmnr.add_logfile(logfilename=>'/u01/app/oracle/oradata/zgdky/redo02.log',options=>dbms_logmnr.new);
SQL> execute dbms_logmnr.add_logfile(logfilename=>'/u01/arch/arch_1_7_853583302.log',options=>dbms_logmnr.addfile);
SQL> execute dbms_logmnr.start_logmnr(dictfilename=>'/u01/app/oracle/logmnr/dict.ora',options=>dbms_logmnr.ddl_dict_tracking);
SQL> select sysdate from dual;
SYSDATE
---------
23-JUL-14

SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
Session altered.

12、查询相关视图，跟踪ddl操作
SQL> col username for a10
SQL> col sql_redo for a50
SQL> select username,scn,timestamp,sql_redo from v$logmnr_contents where username='SCOTT' and lower(sql_redo) like '%table%';
USERNAME SCN TIMESTAMP SQL_REDO
---------- ---------- ------------------- --------------------------------------------------
SCOTT 1007961 2014-07-23 11:28:42 drop table test1 purge;
SCOTT 1007964 2014-07-23 11:28:43 /* No SQL_REDO for temporary tables */
SCOTT 1008021 2014-07-23 11:29:11 create table test2 (id number(5));

13、结束分析
SQL> execute dbms_logmnr.end_logmnr;

ITPub博客

logmnr的使用（ddl）