附官方文档:https://docs.oracle.com/cd/E11882_01/server.112/e22490/logminer.htm#SUTIL1553
打开补充日志
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
SQL> select * from test;
ID NAME
---------- --------------------
1 test
2 test1
3 test3
4 test
SQL> insert into test values(88,'iloveyou');
已创建 1 行。
SQL> insert into test values(99,'yloveyou');
已创建 1 行。
SQL> commit;
提交完成。
SQL> update test set name='kk' where id=1;
已更新 1 行。
SQL> commit;
提交完成。
SQL> delete from test where id=2;
已删除 1 行。
SQL> commit;
提交完成。
SQL> insert into test values(2,'china');
已创建 1 行。
SQL> commmit;
SP2-0042: 未知命令 "commmit" - 其余行忽略。
SQL> commit;
提交完成。
SQL> alter system switch logfilel;
alter system switch logfilel
*
第 1 行出现错误:
ORA-01900: 需要 LOGFILE 关键字
SQL> alter system switch logfile;
alter system switch logfile
*
第 1 行出现错误:
ORA-01031: 权限不足
SQL> conn / as sysdba
已连接。
SQL> alter system switch logfile;
系统已更改。
SQL> archive log list;
数据库日志模式 存档模式
自动存档 启用
存档终点 +FRA
最早的联机日志序列 98
下一个存档日志序列 100
当前日志序列 100
SQL> select name from v$archived_log where sequence# in (select max(sequence#)-1 from v$archived_log);
NAME
--------------------------------------------------------------------------------
+FRA/pnetdata/archivelog/2015_06_25/thread_1_seq_98.339.883304095
snetdata
SQL> select name from v$archived_log where sequence# in (select max(sequence#) from v$archived_log);
NAME
--------------------------------------------------------------------------------
+FRA/pnetdata/archivelog/2015_06_25/thread_1_seq_99.340.883325807
snetdata
SQL> execute dbms_logmnr.add_logfile(logfilename=>'+FRA/pnetdata/archivelog/2015_06_25/thread_1_seq_99.340.883325807',options=>dbms_logmnr.new);
PL/SQL 过程已成功完成。
SQL> execute dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);
PL/SQL 过程已成功完成。
SQL> col sql_redo format a50;
SQL> col username format a8
SQL> set line 200;
SQL> select username,scn,timestamp,sql_redo from v$logmnr_contents where seg_name='TEST';
USERNAME SCN TIMESTAMP SQL_REDO
-------- ---------- -------------- --------------------------------------------------
KING 1702578 25-6月 -15 insert into "KING"."TEST"("ID","NAME") values ('88
','iloveyou');
KING 1702594 25-6月 -15 insert into "KING"."TEST"("ID","NAME") values ('99
','yloveyou');
KING 1702668 25-6月 -15 update "KING"."TEST" set "NAME" = 'kk' where "NAME
" = 'test' and ROWID = 'AAAVlQAAGAAAACDAAA';
KING 1702685 25-6月 -15 delete from "KING"."TEST" where "ID" = '2' and "NA
ME" = 'test1' and ROWID = 'AAAVlQAAGAAAACDAAB';
USERNAME SCN TIMESTAMP SQL_REDO
-------- ---------- -------------- --------------------------------------------------
KING 1702707 25-6月 -15 insert into "KING"."TEST"("ID","NAME") values ('2'
,'china');
结束日志挖取
打开补充日志
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
SQL> select * from test;
ID NAME
---------- --------------------
1 test
2 test1
3 test3
4 test
SQL> insert into test values(88,'iloveyou');
已创建 1 行。
SQL> insert into test values(99,'yloveyou');
已创建 1 行。
SQL> commit;
提交完成。
SQL> update test set name='kk' where id=1;
已更新 1 行。
SQL> commit;
提交完成。
SQL> delete from test where id=2;
已删除 1 行。
SQL> commit;
提交完成。
SQL> insert into test values(2,'china');
已创建 1 行。
SQL> commmit;
SP2-0042: 未知命令 "commmit" - 其余行忽略。
SQL> commit;
提交完成。
SQL> alter system switch logfilel;
alter system switch logfilel
*
第 1 行出现错误:
ORA-01900: 需要 LOGFILE 关键字
SQL> alter system switch logfile;
alter system switch logfile
*
第 1 行出现错误:
ORA-01031: 权限不足
SQL> conn / as sysdba
已连接。
SQL> alter system switch logfile;
系统已更改。
SQL> archive log list;
数据库日志模式 存档模式
自动存档 启用
存档终点 +FRA
最早的联机日志序列 98
下一个存档日志序列 100
当前日志序列 100
SQL> select name from v$archived_log where sequence# in (select max(sequence#)-1 from v$archived_log);
NAME
--------------------------------------------------------------------------------
+FRA/pnetdata/archivelog/2015_06_25/thread_1_seq_98.339.883304095
snetdata
SQL> select name from v$archived_log where sequence# in (select max(sequence#) from v$archived_log);
NAME
--------------------------------------------------------------------------------
+FRA/pnetdata/archivelog/2015_06_25/thread_1_seq_99.340.883325807
snetdata
SQL> execute dbms_logmnr.add_logfile(logfilename=>'+FRA/pnetdata/archivelog/2015_06_25/thread_1_seq_99.340.883325807',options=>dbms_logmnr.new);
PL/SQL 过程已成功完成。
SQL> execute dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);
PL/SQL 过程已成功完成。
SQL> col sql_redo format a50;
SQL> col username format a8
SQL> set line 200;
SQL> select username,scn,timestamp,sql_redo from v$logmnr_contents where seg_name='TEST';
USERNAME SCN TIMESTAMP SQL_REDO
-------- ---------- -------------- --------------------------------------------------
KING 1702578 25-6月 -15 insert into "KING"."TEST"("ID","NAME") values ('88
','iloveyou');
KING 1702594 25-6月 -15 insert into "KING"."TEST"("ID","NAME") values ('99
','yloveyou');
KING 1702668 25-6月 -15 update "KING"."TEST" set "NAME" = 'kk' where "NAME
" = 'test' and ROWID = 'AAAVlQAAGAAAACDAAA';
KING 1702685 25-6月 -15 delete from "KING"."TEST" where "ID" = '2' and "NA
ME" = 'test1' and ROWID = 'AAAVlQAAGAAAACDAAB';
USERNAME SCN TIMESTAMP SQL_REDO
-------- ---------- -------------- --------------------------------------------------
KING 1702707 25-6月 -15 insert into "KING"."TEST"("ID","NAME") values ('2'
,'china');
结束日志挖取
EXECUTE DBMS_LOGMNR.END_LOGMNR();
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/24486203/viewspace-1712422/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/24486203/viewspace-1712422/