redo日志和归档日志挖掘

1.生产环境日志在测试环境挖掘

生产环境为11G,测试环境为12C

1.1生产库创建目录,生成字典文件

SQL> CREATE OR REPLACE DIRECTORY MYDATA AS '/path/to/directory';

set line 999 pages 999
col OWNER for a10
col DIRECTORY_NAME for a25
col DIRECTORY_PATH for a80
select * from dba_directories;
OWNER      DIRECTORY_NAME            DIRECTORY_PATH
---------- ------------------------- --------------------------------------------------------------------------------
SYS        GGS_DDL_TRACE             /u01/app/oracle/diag/rdbms/racdb/racdb1/trace
SYS        MYDATA                    /dump
SYS        XMLDIR                    /u01/app/oracle/product/11.2.0.4/db_1/rdbms/xml
SYS        ORACLE_OCM_CONFIG_DIR     /u01/app/oracle/product/11.2.0.4/db_1/ccr/hosts/rac1/state
SYS        DATA_PUMP_DIR             /u01/app/oracle/product/11.2.0.4/db_1/rdbms/log/
SYS        ORACLE_OCM_CONFIG_DIR2    /u01/app/oracle/product/11.2.0.4/db_1/ccr/state

SQL> exec dbms_logmnr_d.build(dictionary_filename=>'dic.ora',dictionary_location=>'MYDATA');

[oracle@rac1 dump]$ ll /dump/dic.ora
-rw-r--r-- 1 oracle asmadmin 46811703 Jun 20 14:10 /dump/dic.ora

1.2将字典文件dic.ora和日志文件传至测试环境

[root@oracledb ~]# chown oracle. /tmp/dic.ora
[root@oracledb ~]# ll /tmp/dic.ora
-rw-r--r-- 1 oracle oinstall 46811703 Jun 20 14:27 /tmp/dic.ora

[root@oracledb ~]# chown oracle. /tmp/redo01.log
[root@oracledb ~]# ll /tmp/redo01.log
-rw-r--r-- 1 oracle oinstall 35186688 Jun 20 14:34 /tmp/redo01.log

1.3 添加重做日志文件

SYS@ORCL>exec dbms_logmnr.add_logfile('/tmp/redo01.log',dbms_logmnr.new);

1.4启动 LogMiner 会话

SYS@ORCL>exec DBMS_LOGMNR.START_LOGMNR(DICTFILENAME => '/tmp/dic.ora');

--OPTIONS 为使用在线数据字典
--DICTFILENAME 为使用字典文件
--两选项互相冲突
SYS@ORCL>exec DBMS_LOGMNR.START_LOGMNR(DICTFILENAME => '/tmp/dic.ora',options=>dbms_logmnr.dict_from_online_catalog);
ERROR at line 1:
ORA-01298: conflicting dictionary option
ORA-06512: at "SYS.DBMS_LOGMNR", line 58
ORA-06512: at line 1

--logmnr包相关描述,根据需要添加选项
SYS@ORCL>desc DBMS_LOGMNR
PROCEDURE START_LOGMNR
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 STARTSCN                       NUMBER                  IN     DEFAULT
 ENDSCN                         NUMBER                  IN     DEFAULT
 STARTTIME                      DATE                    IN     DEFAULT
 ENDTIME                        DATE                    IN     DEFAULT
 DICTFILENAME                   VARCHAR2                IN     DEFAULT
 OPTIONS                        BINARY_INTEGER          IN     DEFAULT

1.5查询所需要的数据

--查询SCOTT用户下EMP表的DROP操作
select scn,sql_redo from v$logmnr_contents
where lower(sql_redo) like 'drop%'
and seg_name='EMP'
and seg_owner='SCOTT';

1.6结束 LogMiner 会话

EXEC DBMS_LOGMNR.END_LOGMNR();

2.直接在当前环境挖掘

2.1查询日志所在目录

--查询redo日志
SQL> select member from v$logfile;
--查询归档日志
SQL> archive log list

2.2添加重做日志文件

exec dbms_logmnr.add_logfile('+FRA/racdb/onlinelog/redo01.log',dbms_logmnr.new);
--如果是正在运行的数据库,最好是切换几次日志文件后使用归档日志挖掘
SQL> alter system switch logfile;

2.3启动 LogMiner 会话

exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);

--OPTIONS 为使用在线数据字典
--DICTFILENAME 为使用字典文件
--两选项互相冲突
SYS@ORCL>exec DBMS_LOGMNR.START_LOGMNR(DICTFILENAME => '/tmp/dic.ora',options=>dbms_logmnr.dict_from_online_catalog);
ERROR at line 1:
ORA-01298: conflicting dictionary option
ORA-06512: at "SYS.DBMS_LOGMNR", line 58
ORA-06512: at line 1

2.4查询数据

--查询SCOTT用户下EMP表的DROP操作
select scn,sql_redo from v$logmnr_contents
where lower(sql_redo) like 'drop%'
and seg_name='EMP'
and seg_owner='SCOTT';

2.5结束 LogMiner 会话

EXEC DBMS_LOGMNR.END_LOGMNR();