1、创建演示数据
2、查询重做日志相关信息
3、添加需要进行解析的日志文件
4、使用在线字典进行解析+只查询commit的数据
5、查询解析结果
6、结束整个Logminer
-
SQL> conn scott/tiger@neal_192.168.8.205;
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.4.0
Connected as scott@neal_192.168.8.205
SQL> create table t(id number);
Table created
SQL> insert into t values(1);
1 row inserted
SQL> insert into t values(2);
1 row inserted
SQL> commit;
Commit complete
SQL> update emp set sal=sal+1 where deptno=10;
3 rows updated
SQL> commit;
Commit complete
-
SQL> conn sys/oracle@neal_192.168.8.205 as sysdba
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.4.0
Connected as sys@neAL_192.168.8.205 AS SYSDBA
SQL> select group#,sequence#,archived,status,first_change# from v$log;
GROUP# SEQUENCE# ARCHIVED STATUS FIRST_CHANGE#
---------- ---------- -------- ---------------- -------------
1 34 YES INACTIVE 1368330
2 35 NO CURRENT 1368340
3 33 YES INACTIVE 1368318
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/u01/oracle/oradata/neal/redo03.log
/u01/oracle/oradata/neal/redo02.log
/u01/oracle/oradata/neal/redo01.log
-
SQL> exec dbms_logmnr.add_logfile('/u01/oracle/oradata/neal/redo01.log',dbms_logmnr.new);
-
PL/SQL procedure successfully completed
-
-
SQL> exec dbms_logmnr.add_logfile('/u01/oracle/oradata/neal/redo02.log',dbms_logmnr.addfile);
-
PL/SQL procedure successfully completed
-
-
SQL> exec dbms_logmnr.add_logfile('/u01/oracle/oradata/neal/redo03.log',dbms_logmnr.addfile);
- PL/SQL procedure successfully completed
-
SQL> exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog+dbms_logmnr.committed_data_only);
- PL/SQL procedure successfully completed
-
SQL> select sql_redo,sql_undo from v$logmnr_contents where table_name='T' and operation='INSERT';
SQL_REDO SQL_UNDO
------------------------------------------- ------------------------------------------------------------
insert into "SCOTT"."T"("ID") values ('1'); delete from "SCOTT"."T" where "ID" = '1' and ROWID = 'AAAVeY
insert into "SCOTT"."T"("ID") values ('2'); delete from "SCOTT"."T" where "ID" = '2' and ROWID = 'AAAVeY
SQL> select sql_redo,sql_undo from v$logmnr_contents where table_name='EMP';
SQL_REDO SQL_UNDO
------------------------------------------------------------ ------------------------------------------------------------
update "SCOTT"."EMP" set "SAL" = '2472' where "SAL" = '2462' update "SCOTT"."EMP" set "SAL" = '2462' where "SAL" = '2472'
update "SCOTT"."EMP" set "SAL" = '5022' where "SAL" = '5012' update "SCOTT"."EMP" set "SAL" = '5012' where "SAL" = '5022'
update "SCOTT"."EMP" set "SAL" = '1322' where "SAL" = '1312' update "SCOTT"."EMP" set "SAL" = '1312' where "SAL" = '1322'
-
SQL> exec dbms_logmnr.end_logmnr();
- PL/SQL procedure successfully completed
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/29812844/viewspace-1988825/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
