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();
