Yêu cầu phải có gói dmbs_logmnr, nếu chưa có hãy cài đặt:
SQL> connect sys/oracle as sysdba
SQL> @?\/rdbms/admin/dbmslm.sql
Package created.
Grant succeeded.
Synonym created.
Các redo log hiện tại:
SQL> SELECT distinct member LOGFILENAME FROM V$LOGFILE;
LOGFILENAME
-------------------------------------------------------
C:\ORACLE\ORADATA\ORDB1\REDO01.LOG
C:\ORACLE\ORADATA\ORDB1\REDO02.LOG
C:\ORACLE\ORADATA\ORDB1\REDO03.LOG
C:\ORACLE\ORADATA\ORDB1\REDO04.LOG
Đưa các redo log vào để phân tích
SQL> execute DBMS_LOGMNR.ADD_LOGFILE ('C:\ORACLE\ORADATA\ORDB1\REDO01.LOG');
SQL> execute DBMS_LOGMNR.ADD_LOGFILE ('C:\ORACLE\ORADATA\ORDB1\REDO02.LOG');
SQL> execute DBMS_LOGMNR.ADD_LOGFILE ('C:\ORACLE\ORADATA\ORDB1\REDO03.LOG');
SQL> execute DBMS_LOGMNR.ADD_LOGFILE ('C:\ORACLE\ORADATA\ORDB1\REDO04.LOG');
Bắt đầu phân tích:
execute DBMS_LOGMNR.START_LOGMNR (options => dbms_logmnr.dict_from_online_catalog);
PL/SQL procedure successfully completed.
Để hiển thị nội dung kiểm tra trong view v$logmnr_contents: 1 câu lệnh DELETE thì redo record tương ứng là INSERT, câu lệnh INSERT thì redo record là DELETE, câu lệnh UPDATE thì redo record là UPDATE giá trị cũ
SQL> select xid, start_scn, operation, table_name, undo_sql from v$logmnr_contents where start_timestamp>=sysdate-1 and username='BERT' and table_owner='BERT';
XID START_SCN OPERATION TABLE_NAME
---------------- ---------- ------------ ------------
UNDO_SQL
--------------------------------------------------------------
0200030052030000 475697 DELETE JUNK
insert into "BERT"."JUNK"("C1","C2") values ('5','6');
0200030052030000 475697 DELETE JUNK
insert into "BERT"."JUNK"("C1","C2") values ('3','4');
0200030052030000 475697 INSERT JUNK
delete from "BERT"."JUNK" where ROWID = 'AAAD94AAAAAAChOAAD';
0200030052030000 475697 INSERT JUNK
delete from "BERT"."JUNK" where ROWID = 'AAAD94AAAAAAChOAAC';
0200030052030000 475697 INSERT JUNK
delete from "BERT"."JUNK" where ROWID = 'AAAD94AAAAAAChOAAB';
0200030052030000 475697 INSERT JUNK
delete from "BERT"."JUNK" where ROWID = 'AAAD94AAAAAAChOAAA';
Kết thúc:
SQL> execute DBMS_LOGMNR.END_LOGMNR();;
PL/SQL procedure successfully completed.
Bỏ các redo log không phân tích nữa:
SQL> EXECUTE DBMS_LOGMNR.REMOVE_LOGFILE( -
LOGFILENAME => '/data1/oracle/logs/redolog2.dbf');
------------------------
@ Trần Văn Bình - Founder of "Oracle DBA Việt Nam" #OracleTutorial #OracleDBA #OracleDatabaseAdministration #học oracle database #oca #ocp #oce #ocm