oracle提供了日志挖掘的功能,可以从日志文件中挖掘出数据库发生的所有变化。所提供的信息可以用于检查谁在什么时候修改了什么数据,用于事后审计;或用于做系统优化的参考数据(比如日志增长过快)。是DBA日常工作中常用到的一个工具。
这里只做一个简单的测试:
C:\Documents and Settings\yuechaotian>sqlplus "/ as sysdba"
SQL*Plus: Release 9.2.0.1.0 - Production on 星期三 11月 28 15:50:52 2007
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
连接到:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
SQL> show parameter utl_file_dir
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
utl_file_dir string
------------------------------------ ----------- ------------------------------
utl_file_dir string
SQL> alter system set utl_file_dir='d:\oracle\oradir' scope=spfile;
系统已更改。
SQL> startup force
ORACLE 例程已经启动。
ORACLE 例程已经启动。
Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
数据库装载完毕。
数据库已经打开。
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
数据库装载完毕。
数据库已经打开。
SQL> show parameter utl_file_dir
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
utl_file_dir string d:\oracle\oradir
------------------------------------ ----------- ------------------------------
utl_file_dir string d:\oracle\oradir
SQL> conn test/test
已连接。
SQL> select * from test1;
已连接。
SQL> select * from test1;
A B
---------- ----------
11 1
22 2
---------- ----------
11 1
22 2
SQL> select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') from dual;
TO_CHAR(SYSDATE,'YY
-------------------
2007-11-28 15:52:19
-------------------
2007-11-28 15:52:19
SQL> update test1 set a = 1 where b = 1;
已更新 1 行。
SQL> update test1 set a = 2 where b = 2;
已更新 1 行。
SQL> commit;
提交完成。
SQL> select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') from dual;
TO_CHAR(SYSDATE,'YY
-------------------
2007-11-28 15:52:42
-------------------
2007-11-28 15:52:42
SQL> exec sys.dbms_logmnr_d.build(dictionary_filename => 'dir1.txt',dictionary_location => 'd:\oracle\oradir' );
PL/SQL 过程已成功完成。
SQL> exec sys.dbms_logmnr.add_logfile(LogFileName => 'D:\oracle\oradata\ora\REDO04.LOG',Options => sys.dbms_logmnr.NEW);
PL/SQL 过程已成功完成。
SQL> exec sys.dbms_logmnr.add_logfile(LogFileName => 'D:\oracle\oradata\ora\REDO05.LOG',Options => sys.dbms_logmnr.ADDFILE);
PL/SQL 过程已成功完成。
SQL> exec sys.dbms_logmnr.start_logmnr(startTime => to_date('2007-11-28 15:52:19', 'yyyy-mm-dd hh24:mi:ss'),endTime => to_date('2007-11-28 15:52:42', 'yyyy-mm-dd hh24:mi:ss'),DictFileName => 'd:\oracle\oradir\dir1.txt');
PL/SQL 过程已成功完成。
SQL> set wrap off
SQL> set linesize 1000
SQL> set linesize 1000
SQL> select SQL_REDO from v$logmnr_contents where SEG_NAME = 'TEST1';
SQL_REDO
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
update "TEST"."TEST1" set "A" = '1' where "A" = '11' and ROWID = 'AAAHfOAALAAAAEyAAA';
update "TEST"."TEST1" set "A" = '2' where "A" = '22' and ROWID = 'AAAHfOAALAAAAEyAAB';
update "TEST"."TEST1" set "A" = '2' where "A" = '22' and ROWID = 'AAAHfOAALAAAAEyAAB';
SQL> select SQL_UNDO from v$logmnr_contents where SEG_NAME = 'TEST1';
SQL_UNDO
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
update "TEST"."TEST1" set "A" = '11' where "A" = '1' and ROWID = 'AAAHfOAALAAAAEyAAA';
update "TEST"."TEST1" set "A" = '22' where "A" = '2' and ROWID = 'AAAHfOAALAAAAEyAAB';
update "TEST"."TEST1" set "A" = '22' where "A" = '2' and ROWID = 'AAAHfOAALAAAAEyAAB';
SQL> exec sys.dbms_logmnr.end_logmnr;
PL/SQL 过程已成功完成。
可见对于test1表的变化,在日志中都有对应的反映。其实上面只查询了视图v$logmnr_contents中的部分内容,该视图还有很多内容可供使用:
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
SEG_TYPE NUMBER Y
SEG_TYPE_NAME VARCHAR2(32) Y
TABLE_SPACE VARCHAR2(32) Y
ROW_ID VARCHAR2(19) 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 RAW(4) Y
UNDO_VALUE RAW(4) Y
SQL_COLUMN_TYPE VARCHAR2(32) Y
SQL_COLUMN_NAME VARCHAR2(32) Y
REDO_LENGTH NUMBER Y
REDO_OFFSET NUMBER Y
UNDO_LENGTH NUMBER Y
UNDO_OFFSET NUMBER Y
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
SEG_TYPE NUMBER Y
SEG_TYPE_NAME VARCHAR2(32) Y
TABLE_SPACE VARCHAR2(32) Y
ROW_ID VARCHAR2(19) 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 RAW(4) Y
UNDO_VALUE RAW(4) Y
SQL_COLUMN_TYPE VARCHAR2(32) Y
SQL_COLUMN_NAME VARCHAR2(32) Y
REDO_LENGTH NUMBER Y
REDO_OFFSET NUMBER Y
UNDO_LENGTH NUMBER Y
UNDO_OFFSET NUMBER Y
SQL>
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7204674/viewspace-622216/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/7204674/viewspace-622216/