Auditing Past Transactions With Oracle LogMiner

INSERT INTO emp(name, salary) VALUES ('John Doe', 50000);

 LogMiner will display:

 insert into Object#2581(col#1, col#2) values (hextoraw('4a6f686e20446f65'), hextoraw
('c306'));"

The contents of a dictionary file looks like:

CREATE_TABLE DICTIONARY_TABLE ( DB_NAME VARCHAR2(9), DB_ID NUMBER(20), 
DB_CREATED
VARCHAR2(20), DB_DICT_CREATED VARCHAR2(20), DB_DICT_SCN NUMBER(22), 
DB_THREAD_MAP 
NUMBER(22), DB_RESETLOGS_CHANGE# NUMBER(22), DB_RESETLOGS_TIME VARCHAR2(20), 
DB_VERSION_TIME 
VARCHAR2(20), DB_REDO_TYPE_ID VARCHAR2(8), DB_REDO_RELEASE VARCHAR2(60), 
DB_CHARACTER_SET 
VARCHAR2(30), DB_VERSION VARCHAR2(64), DB_STATUS VARCHAR2(64), 
DB_DICT_MAXOBJECTS 
NUMBER(22), DB_DICT_OBJECTCOUNT NUMBER(22)); 

INSERT_INTO DICTIONARY_TABLE VALUES ('MHD1',41190674,'12/24/2002 
23:36:15','03/16/2003 
12:30:24',,,1,'12/24/2002 16:05:38','12/24/2002
23:36:15','REDODATA','8.0.5.0.0','WE8ISO8859P1','8.0.5.1.0','Production',2788,2697);

The dictionary file can be converted into a SQL script by replacing globally the underscores with spaces.

CREATE_TABLE —> CREATE TABLE; CREATE_INDEX —> CREATE INDEX; INSERT_INTO —> INSERT INTO; and so on (there are more details in the header comments in the dictionary file itself).

Also, see Oracle Note 77638.1 on how to build a package and a LogMiner “Place Holder Columns” file.

Running The Log Miner

We have used four scripts to demonstrate the concepts in this paper. They are in the file MHSYS-logminer.sql and the logs in MHSYS-logminer.log.

First, on the source database, we create some transactions like:

INSERT INTO table1 ( rec_id,  emp_last_name,  emp_first_name,  salary )
VALUES ( 03, 'LASTTHREE', 'FIRSTTHREE', 10000.10 );

Then we update one row:

UPDATE table1 SET salary = 20000.10 WHERE rec_id = 03;

Then we build the dictionary file:

execute dbms_logmnr_d.build(dictionary_filename => 'dictionary.920.ora', -
                                                  dictionary_location => 'C:\TEMP');

Now, we copy the dictionary file, the online and archived redo log files from the period of time that interests us to the analyzing database machine. The analyzing database does not have to be only mounted, it can be open, in which case I normally just copy the V$LOGMNR_CONTENTS into a regular table.

上一页 [1] [2] [3] [4] [5] [6] 下一页

复制本页网址和标题，发送给你QQ/Msn的好友一起分享

上一篇:SQL优化思考

下一篇:Oracle8i和MS SQL Server7.0比较