oracle中logmnr用法

最新推荐文章于 2021-10-08 10:41:46 发布

cpquts879322

最新推荐文章于 2021-10-08 10:41:46 发布

阅读量176

点赞数

文章标签：数据库

1)set directory utl_file_dir in init.ora
2)restartup

来显示ult_file

SQL> show parameter utl_file

NAME TYPE VALUE
------------------------------------ ----------- ----------------------------
utl_file_dir string D:oracleadminfun1cdump

SQL> update scott.emp set comm=999 where empno='7566';

已更新 1 個資料列.

SQL> update scott.dept set dname='LJG.ERIC' where dname='DDA';

已更新 1 個資料列.

SQL> commit;

確認完成.

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ----------
1 1 50 104857600 1 NO INACTIVE 5.9760E+12 11-3月 -06
2 1 51 104857600 1 NO INACTIVE 5.9760E+12 18-3月 -06
3 1 53 104857600 1 NO CURRENT 5.9760E+12 22-3月 -06
4 1 0 10485760 1 YES UNUSED 0

--GROUP 3 為當前的日志組

SQL> execute dbms_logmnr_d.build('fun1dict.ora','D:oracleadminfun1cdump')

PL/SQL 程序順利完成.

SQL> execute dbms_logmnr.add_logfile('D:oracleoradatafun1REDO03.LOG',dbms_logmnr.new);

PL/SQL 程序順利完成.

SQL> execute dbms_logmnr.start_logmnr(dictfilename=>'D:oracleadminfun1cdumpfun1dict.ora');

PL/SQL 程序順利完成.

--查看v$logmnr_contents視圖(分別查sql_redo﹑sql_undo欄位
--EMP表的查詢
SQL> SQL> SELECT sql_redo FROM v$logmnr_contents WHERE username='SYS' AND seg_name='EMP';

SQL_REDO
----------------------------------------------------------------------------------------------------
update "SCOTT"."EMP" set "COMM" = '999' where "COMM" IS NULL and ROWID = 'AAAHW7AABAAAMUiAAD';

SQL> SELECT sql_undo FROM v$logmnr_contents WHERE username='SYS' AND seg_name='EMP';

SQL_UNDO
----------------------------------------------------------------------------------------------------
update "SCOTT"."EMP" set "COMM" = NULL where "COMM" = '999' and ROWID = 'AAAHW7AABAAAMUiAAD';

--DEPT表的查詢
SQL> SELECT sql_redo FROM v$logmnr_contents WHERE username='SYS' AND seg_name='DEPT';

SQL_REDO
----------------------------------------------------------------------------------------------------
update "SCOTT"."DEPT" set "DNAME" = 'LJG.ERIC' where "DNAME" = 'DDA' and ROWID = 'AAAHW5AABAAAMUSAAH

SQL> SELECT sql_undo FROM v$logmnr_contents WHERE username='SYS' AND seg_name='DEPT';

SQL_UNDO
----------------------------------------------------------------------------------------------------
update "SCOTT"."DEPT" set "DNAME" = 'DDA' where "DNAME" = 'LJG.ERIC' and ROWID = 'AAAHW5AABAAAMUSAAH

--結束日志分析
SQL> execute DBMS_LOGMNR.END_LOGMNR;