dbms_logmnr.的使用

最新推荐文章于 2022-06-20 13:37:14 发布

cuie0761

最新推荐文章于 2022-06-20 13:37:14 发布

阅读量160

点赞数

文章标签：操作系统数据库

学习使用logmnr对日志文件进行信息挖掘

环境准备：

操作系统：

solaris 10 vm

数据库版本：

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Prod
PL/SQL Release 10.2.0.2.0 - Production
CORE 10.2.0.2.0 Production
TNS for Solaris: Version 10.2.0.2.0 - Production
NLSRTL Version 10.2.0.2.0 - Production

日志文件：

SQL> select * from v$logfile;

GROUP# STATUS TYPE MEMBER IS_RECOVERY_DEST_FILE
---------- ------- ------- -------------------------------------------------------------------------------- ---------------------
3 ONLINE /export/home/oracle/oradata/ora144/redo03.log NO
2 STALE ONLINE /export/home/oracle/oradata/ora144/redo02.log NO
1 STALE ONLINE /export/home/oracle/oradata/ora144/redo01.log NO

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- -------- ---------------- ------------- -----------
1 1 2 52428800 1 YES INACTIVE 535769 2011/10/24
2 1 3 52428800 1 YES INACTIVE 561039 2011/10/25
3 1 4 52428800 1 NO CURRENT 585055 2011/10/27

归档文件：

SQL> select destination from v$archive_dest;

DESTINATION
--------------------------------------------------------------------------------
/export/home/oracle

10 rows selected

1）切换online redo logfile

SQL> alter system switch logfile;

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------------
1 1 5 52428800 1 YES ACTIVE 599559 01-11月-2011 10:48:07
2 1 6 52428800 1 NO CURRENT 600192 01-11月-2011 11:06:28
3 1 4 52428800 1 YES INACTIVE 585055 27-10月-2011 15:40:19

2）添加归档文件到logmnr：

SQL> exec dbms_logmnr.add_logfile('/export/home/oracle/arch_1_5_765385696.arc');

PL/SQL 过程已成功完成。

3）开启logmnr分析logmnr中的归档文件

SQL> exec dbms_logmnr.start_logmnr();

PL/SQL 过程已成功完成。

SQL> select count(*) from v$logmnr_contents;

COUNT(*)
----------
10210

4）在视图v$logmnr_contents中查询seg_name为temp经过解析的信息：

SQL> select abs_file#,rel_file#,data_blk#,data_obj#,seg_name,session#,rs_id from v$logmnr_contents where seg_name='TEMP';

ABS_FILE# REL_FILE# DATA_BLK# DATA_OBJ# SEG_NAME SSION# RS_ID
---------- ---------- ---------- ---------- -------------------- ---------- --------------------------------
0 0 0 51436 TEMP 0 0x000005.0000093e.00f8

SQL> select timestamp,abs_file#,rel_file#,data_blk#,data_obj#,session#,sql_redo from v$logmnr_contents where seg_name='TEMP';

TIMESTAMP ABS_FILE# REL_FILE# DATA_BLK# DATA_OBJ# SESSION#
------------------------- ---------- ---------- ---------- ---------- ----------
SQL_REDO
--------------------------------------------------------------------------------------------------------------------------------------------
01-11月-2011 11:01:17 0 0 0 51436 0
create table temp as select * from emp
;

视图v$logmnr_contents （下面的链接是oracle 11.2g 官方文档对该视图各列的解释）

SQL> desc v$logmnr_contents;
Name Type Nullable Default Comments
---------------- -------------- -------- ------- --------
SCN NUMBER Y
CSCN NUMBER Y
TIMESTAMP DATE Y
COMMIT_TIMESTAMP DATE Y
THREAD# NUMBER Y
LOG_ID NUMBER Y
XIDUSN NUMBER Y
XIDSLT NUMBER Y
XIDSQN NUMBER Y
PXIDUSN NUMBER Y
PXIDSLT NUMBER Y
PXIDSQN NUMBER Y
RBASQN NUMBER Y
RBABLK NUMBER Y
RBABYTE NUMBER Y
UBAFIL NUMBER Y
UBABLK NUMBER Y
UBAREC NUMBER Y
UBASQN NUMBER Y
ABS_FILE# NUMBER Y
REL_FILE# NUMBER Y
DATA_BLK# NUMBER Y
DATA_OBJ# NUMBER Y
DATA_OBJD# NUMBER Y
SEG_OWNER VARCHAR2(32) Y
SEG_NAME VARCHAR2(256) Y
TABLE_NAME VARCHAR2(32) Y
SEG_TYPE NUMBER Y
SEG_TYPE_NAME VARCHAR2(32) Y
TABLE_SPACE VARCHAR2(32) Y
ROW_ID VARCHAR2(18) Y
SESSION# NUMBER Y
SERIAL# NUMBER Y
USERNAME VARCHAR2(30) Y
SESSION_INFO VARCHAR2(4000) Y
TX_NAME VARCHAR2(256) Y
ROLLBACK NUMBER Y
OPERATION VARCHAR2(32) Y
OPERATION_CODE NUMBER Y
SQL_REDO VARCHAR2(4000) Y
SQL_UNDO VARCHAR2(4000) Y
RS_ID VARCHAR2(32) Y
SEQUENCE# NUMBER Y
SSN NUMBER Y
CSF NUMBER Y
INFO VARCHAR2(32) Y
STATUS NUMBER Y
REDO_VALUE NUMBER Y
UNDO_VALUE NUMBER Y
SQL_COLUMN_TYPE VARCHAR2(30) Y
SQL_COLUMN_NAME VARCHAR2(30) Y
REDO_LENGTH NUMBER Y
REDO_OFFSET NUMBER Y
UNDO_LENGTH NUMBER Y
UNDO_OFFSET NUMBER Y
DATA_OBJV# NUMBER Y
SAFE_RESUME_SCN NUMBER Y
XID RAW(8) Y
PXID RAW(8) Y
AUDIT_SESSIONID NUMBER Y
http://download.oracle.com/docs/cd/E11882_01/server.112/e25513/dynviews_2035.htm#REFRN30132