oracle10g logmnr 详细配置

最新推荐文章于 2021-04-04 08:57:46 发布

越烟

最新推荐文章于 2021-04-04 08:57:46 发布

阅读量485

点赞数

本文链接：https://blog.csdn.net/daiqiulong2/article/details/48158719

版权

有同事反应说 oracle 日志完全不会搞，老实这里报错，那里不对，今天哥把详细的过程贴出来，哥的环境还是10g 没有办法，只有做10g 的了...

先跑过程.... 用 sys 登陆。。。。。。。

@D:/Oracle/RDBMS/ADMIN/dbmslm.sql;

@D:/Oracle/RDBMS/ADMIN/dbmslmd.sql;

show parameter utl_file_dir
---- if value is null
alter system set utl_file_dir='D:/Oracle/logminer' scope=spfile;

---if logminer not exist built logminer dir

-- 创建 log数据字典
exec dbms_logmnr_d.build('dictionary.ora', 'D:/Oracle/logminer');

select SUPPLEMENTAL_LOG_DATA_MIN from v$database;
--- if is no
alter database add supplemental log data;
select SUPPLEMENTAL_LOG_DATA_MIN from v$database;
-- YES

exec dbms_logmnr.add_logfile('D:\ORADATA\ORCL\REDO01.LOG', dbms_logmnr.new);
exec dbms_logmnr.add_logfile('D:\ORADATA\ORCL\REDO02.LOG', dbms_logmnr.addfile);
exec dbms_logmnr.add_logfile('D:\ORADATA\ORCL\REDO03.LOG', dbms_logmnr.addfile);

exec dbms_logmnr.start_logmnr( dictfilename=>'D:/Oracle/logminer/dictionary.ora');

select filename from v$logmnr_dictionary;
select * from v$logmnr_parameters;
select filename from v$logmnr_logs;

select * from v$logmnr_contents where seg_owner = 'ROOT' and seg_name = 'T_EMP';

SQL> select * from v$logmnr_contents where seg_owner = 'ROOT' and seg_name = 'T_EMP';

SCN CSCN TIMESTAMP COMMIT_TIMESTAMP THREAD# LOG_ID XIDUSN XIDSLT XIDSQN PXIDUSN PXIDSLT PXIDSQN RBASQN RBABLK RBABYTE UBAFIL UBABLK UBAREC UBASQN ABS_FILE# REL_FILE# DATA_BLK# DATA_OBJ# DATA_OBJD# SEG_OWNER SEG_NAME TABLE_NAME SEG_TYPE SEG_TYPE_NAME TABLE_SPACE ROW_ID SESSION# SERIAL# USERNAME SESSION_INFO TX_NAME ROLLBACK OPERATION OPERATION_CODE SQL_REDO SQL_UNDO RS_ID SEQUENCE# SSN CSF INFO STATUS REDO_VALUE UNDO_VALUE SQL_COLUMN_TYPE SQL_COLUMN_NAME REDO_LENGTH REDO_OFFSET UNDO_LENGTH UNDO_OFFSET DATA_OBJV# SAFE_RESUME_SCN XID PXID AUDIT_SESSIONID
---------- ---------- ----------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- -------------------------------- -------------------------------------------------------------------------------- -------------------------------- ---------- -------------------------------- -------------------------------- ------------------ ---------- ---------- ------------------------------ -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ---------- -------------------------------- -------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------- ---------- ---------- ---------- -------------------------------- ---------- ---------- ---------- ------------------------------ ------------------------------ ----------- ----------- ----------- ----------- ---------- --------------- ---------------- ---------------- ---------------
3066107 2015-9-1 15 1 63 2 41 1337 2 41 1337 63 44232 16 2 0 0 0 2 4 420 52780 52780 ROOT T_EMP 2 TABLE USERS AAAM4sAAEAAAAGkAAB 137 452 SYS login_username=SYS client_info= OS_username=PC-201202011115\Administrator Machin 0 SELECT_FOR_UPDATE 25 select * from "ROOT"."T_EMP" where ROWID = 'AAAM4sAAEAAAAGkAAB' for update; 0x00003f.0000acc8.0010 1 0 0 0 325428 325429 0 0 0 0 1 0200290039050000 0200290039050000 4294967295
3066107 2015-9-1 15 1 63 2 41 1337 2 41 1337 63 44233 68 2 8389235 62 699 2 4 420 52780 52780 ROOT T_EMP 2 TABLE USERS AAAM4sAAEAAAAGkAAC 137 452 SYS login_username=SYS client_info= OS_username=PC-201202011115\Administrator Machin 0 SELECT_FOR_UPDATE 25 select * from "ROOT"."T_EMP" where ROWID = 'AAAM4sAAEAAAAGkAAC' for update; 0x00003f.0000acc9.0044 1 0 0 0 325430 325431 0 0 0 0 1 0200290039050000 0200290039050000 4294967295
3066107 2015-9-1 15 1 63 2 41 1337 2 41 1337 63 44233 288 2 8389235 63 699 2 4 420 52780 52780 ROOT T_EMP 2 TABLE USERS AAAM4sAAEAAAAGkAAE 137 452 SYS login_username=SYS client_info= OS_username=PC-201202011115\Administrator Machin 0 SELECT_FOR_UPDATE 25 select * from "ROOT"."T_EMP" where ROWID = 'AAAM4sAAEAAAAGkAAE' for update; 0x00003f.0000acc9.0120 1 0 0 0 325432 325433 0 0 0 0 1 0200290039050000 0200290039050000 4294967295
3066107 2015-9-1 15 1 63 2 41 1337 2 41 1337 63 44234 16 2 8389235 64 699 2 4 420 52780 52780 ROOT T_EMP 2 TABLE USERS AAAM4sAAEAAAAGkAAF 137 452 SYS login_username=SYS client_info= OS_username=PC-201202011115\Administrator Machin 0 SELECT_FOR_UPDATE 25 select * from "ROOT"."T_EMP" where ROWID = 'AAAM4sAAEAAAAGkAAF' for update; 0x00003f.0000acca.0010 1 0 0 0 325434 325435 0 0 0 0 1 0200290039050000 0200290039050000 4294967295
3066107 2015-9-1 15 1 63 2 41 1337 2 41 1337 63 44234 236 2 8389235 65 699 2 4 420 52780 52780 ROOT T_EMP 2 TABLE USERS AAAM4sAAEAAAAGkAAG 137 452 SYS login_username=SYS client_info= OS_username=PC-201202011115\Administrator Machin 0 SELECT_FOR_UPDATE 25 select * from "ROOT"."T_EMP" where ROWID = 'AAAM4sAAEAAAAGkAAG' for update; 0x00003f.0000acca.00ec 1 0 0 0 325436 325437 0 0 0 0 1 0200290039050000 0200290039050000 4294967295
3066107 2015-9-1 15 1 63 2 41 1337 2 41 1337 63 44234 456 2 8389235 66 699 2 4 420 52780 52780 ROOT T_EMP 2 TABLE USERS AAAM4sAAEAAAAGkAAI 137 452 SYS login_username=SYS client_info= OS_username=PC-201202011115\Administrator Machin 0 SELECT_FOR_UPDATE 25 select * from "ROOT"."T_EMP" where ROWID = 'AAAM4sAAEAAAAGkAAI' for update; 0x00003f.0000acca.01c8 1 0 0 0 325438 325439 0 0 0 0 1 0200290039050000 0200290039050000 4294967295
3066107 2015-9-1 15 1 63 2 41 1337 2 41 1337 63 44235 180 2 8389235 67 699 2 4 420 52780 52780 ROOT T_EMP 2 TABLE USERS AAAM4sAAEAAAAGkAAJ 137 452 SYS login_username=SYS client_info= OS_username=PC-201202011115\Administrator Machin 0 SELECT_FOR_UPDATE 25 select * from "ROOT"."T_EMP" where ROWID = 'AAAM4sAAEAAAAGkAAJ' for update; 0x00003f.0000accb.00b4 1 0 0 0 325440 325441 0 0 0 0 1 0200290039050000 0200290039050000 4294967295
3066107 2015-9-1 15 1 63 2 41 1337 2 41 1337 63 44235 400 2 8389235 68 699 2 4 420 52780 52780 ROOT T_EMP 2 TABLE USERS AAAM4sAAEAAAAGkAAL 137 452 SYS login_username=SYS client_info= OS_username=PC-201202011115\Administrator Machin 0 SELECT_FOR_UPDATE 25 select * from "ROOT"."T_EMP" where ROWID = 'AAAM4sAAEAAAAGkAAL' for update; 0x00003f.0000accb.0190 1 0 0 0 325442 325443 0 0 0 0 1 0200290039050000 0200290039050000 4294967295
3066107 2015-9-1 15 1 63 2 41 1337 2 41 1337 63 44236 124 2 8389235 69 699 2 4 420 52780 52780 ROOT T_EMP 2 TABLE USERS AAAM4sAAEAAAAGkAAN 137 452 SYS login_username=SYS client_info= OS_username=PC-201202011115\Administrator Machin 0 SELECT_FOR_UPDATE 25 select * from "ROOT"."T_EMP" where ROWID = 'AAAM4sAAEAAAAGkAAN' for update; 0x00003f.0000accc.007c 1 0 0 0 325444 325445 0 0 0 0 1 0200290039050000 0200290039050000 4294967295
3066191 2015-9-1 15 1 63 2 41 1337 2 41 1337 63 44297 308 2 8389235 71 699 4 4 422 52780 52780 ROOT T_EMP T_EMP 2 TABLE USERS AAAM4sAAEAAAAGmAAA 137 452 SYS login_username=SYS client_info= OS_username=PC-201202011115\Administrator Machin 0 INSERT 1 insert into "ROOT"."T_EMP"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM"," delete from "ROOT"."T_EMP" where "EMPNO" = '7489' and "ENAME" = 'DQL' and "JOB" 0x00003f.0000ad09.0134 1 0 0 0 325564 325565 0 0 0 0 1 0200290039050000 0200290039050000 4294967295
3066303 2015-9-1 15 1 63 2 28 1337 2 28 1337 63 44548 16 2 0 0 0 2 4 420 52780 52780 ROOT T_EMP T_EMP 2 TABLE USERS AAAM4sAAEAAAAGkAAG 159 29 ROOT login_username=ROOT client_info= OS_username=PC-201202011115\Administrator Machi 0 DELETE 2 delete from "ROOT"."T_EMP" where "EMPNO" = '7782' and "ENAME" = 'CLARK' and "JOB insert into "ROOT"."T_EMP"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM"," 0x00003f.0000ae04.0010 1 0 0 0 326140 326141 0 0 0 0 1 02001C0039050000 02001C0039050000 8663
3066303 2015-9-1 15 1 63 2 28 1337 2 28 1337 63 44549 228 2 8389245 1 699 2 4 420 52780 52780 ROOT T_EMP T_EMP 2 TABLE USERS AAAM4sAAEAAAAGkAAI 159 29 ROOT login_username=ROOT client_info= OS_username=PC-201202011115\Administrator Machi 0 DELETE 2 delete from "ROOT"."T_EMP" where "EMPNO" = '7839' and "ENAME" = 'KING' and "JOB" insert into "ROOT"."T_EMP"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM"," 0x00003f.0000ae05.00e4 1 0 0 0 326142 326143 0 0 0 0 1 02001C0039050000 02001C0039050000 8663
3066303 2015-9-1 15 1 63 2 28 1337 2 28 1337 63 44550 36 2 8389245 2 699 2 4 420 52780 52780 ROOT T_EMP T_EMP 2 TABLE USERS AAAM4sAAEAAAAGkAAN 159 29 ROOT login_username=ROOT client_info= OS_username=PC-201202011115\Administrator Machi 0 DELETE 2 delete from "ROOT"."T_EMP" where "EMPNO" = '7934' and "ENAME" = 'MILLER' and "JO insert into "ROOT"."T_EMP"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM"," 0x00003f.0000ae06.0024 1 0 0 0 326144 326145 0 0 0 0 1 02001C0039050000 02001C0039050000 8663

13 rows selected