前提条件:
如果希望LOGMNR可以得到记录,应该设置SUPPLEMENTAL LOG DATA
PRIMARY KEY和UNIQUE
INDEX,这样Oracle才能确保LOGMNR可以获取SQL语句:
SQL> SELECT SUPPLEMENTAL_LOG_DATA_PK,
SUPPLEMENTAL_LOG_DATA_UI FROM V$DATABASE;
SUP SUP
--- ---
NO NO
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA
(PRIMARY KEY, UNIQUE INDEX) COLUMNS;
数据库已更改。
SQL> SELECT SUPPLEMENTAL_LOG_DATA_PK,
SUPPLEMENTAL_LOG_DATA_UI FROM V$DATABASE;
SUP SUP
--- ---
YES YES
具体步骤:
使用LOGMNR可以找到刚刚执行的DML操作.
SQL> SELECT
GROUP#, SEQUENCE#, STATUS FROM V$LOG;
GROUP# SEQUENCE# STATUS
---------- ---------- ----------------
1
245 INACTIVE
2
246 INACTIVE
3
247 CURRENT
SQL> SELECT GROUP#, MEMBER FROM V$LOGFILE;
GROUP#
MEMBER
---------- --------------------------------------------------
3 E:\ORACLE\ORADATA\YTK102\REDO03.LOG
2 E:\ORACLE\ORADATA\YTK102\REDO02.LOG
1 E:\ORACLE\ORADATA\YTK102\REDO01.LOG
SQL> DROP TABLE T PURGE; --------(purge选项为彻底删除)
表已删除。
SQL> CREATE TABLE T (ID NUMBER);
表已创建。
SQL> INSERT INTO T VALUES (1);
已创建1行。
SQL> COMMIT;
提交完成。
SQL> ALTER SYSTEM SWITCH LOGFILE;
系统已更改。
SQL> EXEC
SYS.DBMS_LOGMNR.ADD_LOGFILE('E:\ORACLE\ORADATA\YTK102\REDO03.LOG',
SYS.DBMS_LOGMNR.NEW)
PL/SQL过程已成功完成。
SQL> EXEC
SYS.DBMS_LOGMNR.START_LOGMNR(OPTIONS =>
SYS.DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG)
PL/SQL过程已成功完成。
SQL> SELECT SQL_REDO FROM
V$LOGMNR_CONTENTS WHERE SEG_OWNER = USER AND TABLE_NAME =
'T';
SQL_REDO
------------------------------------------------------------------
DROP TABLE T PURGE;
CREATE TABLE T (ID NUMBER);
insert into "YANGTK"."T"("ID") values ('1');
SQL>
EXEC
SYS.DBMS_LOGMNR.END_LOGMNR
PL/SQL过程已成功完成。
SQL> SELECT * FROM V$VERSION;
BANNER
----------------------------------------------------------------
Oracle Database10gEnterprise Edition Release10.2.0.1.0 -
Prod
PL/SQL
Release 10.2.0.1.0 - Production
CORE
10.2.0.1.0
Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
附录:
Version 10 need to turn on
SUPPLEMENTAL_LOG_DATA_PK &
SUPPLEMENTAL_LOG_DATA_UI, while version 11 no need.
select OPERATION,
SEG_TYPE_NAME, SQL_REDO, SEG_OWNER, from V$LOGMNR_CONTENTS WHERE
SEG_OWNER <> ‘SYS’;
|