Objective: Use oracle logminer to audit DML operations that happened in the db
url: https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sutil/oracle-logminer-utility.html#GUID-143798E2-B0E0-4E9D-A70C-08593A117027
scenario we are testing here: 22.13.3.2 Scenario 2: Using LogMiner to Calculate Table Access Statistics
Log miner doesnt store information, instead it retrieves information from redologfiles relative to the requested setting in dbms_logmnr.start_logmnr
logminer needs:
1. min. supplemental logging enabled at db level
2. archive log mode enabled
3. Dictionary build into REDO or Flat file for usage in mining db
4. mining db, in case we wanted to mine outside the db
5. TDE key to be loaded to mining db
6. source system accessibility in case we dont want use mining db
The mining db can be
1. same as source of redo or archive
2. different db with access to dictionary through redo or flat file
option 1 is preferred and easy.
If TDE is used, the mining db should have access to the TDE keys in the mining db wallet.
options to try:
1. mine redo
2. mined archived redo
3. without supplemental logging (update statement) -- minimal supplemental logging
4. with supplemental logging (update statement) -- minimal supplemental logging
Notes:
enable/disable supplemental logging (min):
rollforward: ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
rollback: ALTER DATABASE DROP SUPPLEMENTAL LOG DATA;
-- but we will need "Database-Level Identification Key Logging" to get additional column logged in redo
More granular supplemental logging:
1. Key based supplemental logging (primary key & unique key)
2. procedural supplemental logging which is needed for some replication and golden gate solution
Key based supplemental logging is furher controlled at
1. db level
2. table level
3. column group level
Examining supplemental logging setting:
V$DATABASE (SUPPLEMENTAL_LOG_DATA_MIN), DBA_LOG_GROUPS,DBA_LOG_GROUP_COLUMNS
Views to use:
V$LOGMNR_LOGS - to find out list of logs that db automatically picked as per the dbms_logmnr.start_logmnr operation
v$logmnr_contents - helps to generate sql_redo and sql_undo & perform audit operations
Test Tx routine:
exec dbms_workload_repository.create_snapshot;
1) commited Tx
conn userapp01/userapp01
desc USERAPP01.TBLAPP01_06
select * from USERAPP01.TBLAPP01_06 where id=196;
update USERAPP01.TBLAPP01_06 set idstr='A' where id=196;
select * from USERAPP01.TBLAPP01_06 where id=196;
commit;
select * from USERAPP01.TBLAPP01_06 where id=196;
2) rolled back
-- as sys
desc USERAPP01.TBLAPP01_06
select * from USERAPP01.TBLAPP01_06 where id=196;
update USERAPP01.TBLAPP01_06 set idstr='Z' where id=196;
select * from USERAPP01.TBLAPP01_06 where id=196;
rollback;
select * from USERAPP01.TBLAPP01_06 where id=196;
--- now examine the logfiles to see if the audit shows redo tx.
exec dbms_workload_repository.create_snapshot;
Audit start: Oct 12 03:33:56 2025
Audit End: 03:38:15
our logfile: /oradata/GGSRC05T/onlinelog/o1_mf_3_ll2oq43o_.log [sequence#69]
LogMining Routine:
step 1) add supplemental logging
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
03:52:55 SQL> select log_mode,sUPPLEMENTAL_LOG_DATA_MIN from v$database;
LOG_MODE ,SUPPLEME ------------,-------- ARCHIVELOG ,YES
|
step 2) set session format for nls_date_format
ALTER SESSION SET NLS_DATE_FORMAT = 'dd-mon-yyyy hh24:mi:ss';
step 3) Determine the redolog needed
Ex.:
SELECT NAME FROM V$ARCHIVED_LOG
WHERE FIRST_TIME = (SELECT MAX(FIRST_TIME) FROM V$ARCHIVED_LOG);
select * from v$log;
Actual:
03:39:40 SQL> select * from v$log;
GROUP#, THREAD#, SEQUENCE#, BYTES, BLOCKSIZE, MEMBERS,ARC,STATUS ,FIRST_CHANGE#,FIRST_TIME ,NEXT_CHANGE#,NEXT_TIME , CON_ID ----------,----------,----------,----------,----------,----------,---,----------------,-------------,--------------------,------------,--------------------,---------- 1, 1, 68, 52428800, 512, 1,YES,INACTIVE , 1681150,03-jun-2024 15:02:33, 1686513,12-oct-2025 03:08:15, 0 2, 1, 67, 52428800, 512, 1,YES,INACTIVE , 1544512,04-jan-2024 22:00:32, 1681150,03-jun-2024 15:02:33, 0 3, 1, 69, 52428800, 512, 1,NO ,CURRENT , 1686513,12-oct-2025 03:08:15, 1.8447E+19, , 0
Elapsed: 00:00:00.01 03:40:03 SQL> select * from v$logfile;
GROUP#,STATUS ,TYPE ,MEMBER ,IS_, CON_ID ----------,-------,-------,---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------,---,---------- 1, ,ONLINE ,/oradata/GGSRC05T/onlinelog/o1_mf_1_ll2oq3gn_.log ,NO , 0 2, ,ONLINE ,/oradata/GGSRC05T/onlinelog/o1_mf_2_ll2oq3rv_.log ,NO , 0 3, ,ONLINE ,/oradata/GGSRC05T/onlinelog/o1_mf_3_ll2oq43o_.log ,NO , 0
Elapsed: 00:00:00.01 03:40:30 SQL>
-- switched to generate archive:
04:01:58 SQL> select name from v$archived_log where sequence#=69;
NAME --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- /oraarch/GGSRC05T/archivelog/2025_10_12/o1_mf_1_69_ngp6hknh_.arc
Elapsed: 00:00:00.00 04:02:20 SQL> select first_time,next_time,completion_time from v$archived_log where sequence#=69;
FIRST_TIME ,NEXT_TIME ,COMPLETION_TIME --------------------,--------------------,-------------------- 12-oct-2025 03:08:15,12-oct-2025 04:01:05,12-oct-2025 04:01:05
|
Step 4) Add the redologs needed for analyse
EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -
LOGFILENAME => '/usr/oracle/data/db1arch_1_16_482701534.dbf', -
OPTIONS => DBMS_LOGMNR.NEW);
further files doesnt need the "options" specified in add_logfile
-- to remove a file
EXECUTE DBMS_LOGMNR.REMOVE_LOGFILE( -
LOGFILENAME => '/oracle/logs/log2.f');
Actual:
04:02:55 SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/oraarch/GGSRC05T/archivelog/2025_10_12/o1_mf_1_69_ngp6hknh_.arc',OPTIONS => DBMS_LOGMNR.NEW);
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00 04:03:49 SQL> select * from V$LOGMNR_LOGS;
LOG_ID,FILENAME ,LOW_TIME ,HIGH_TIME , DB_ID,DB_NAME , RESET_SCN,RESET_SCN_TIME ,COMPATIBLE , THREAD_ID,THREAD_SQN, LOW_SCN, NEXT_SCN,DIC,DIC,TYPE , BLOCKSIZE, FILESIZE,INFO , STATUS, CON_ID ----------,--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------,--------------------,--------------------,----------,--------,----------,--------------------,-----------------,----------,----------,----------,----------,---,---,-------,----------,----------,--------------------------------,----------,---------- 69,/oraarch/GGSRC05T/archivelog/2025_10_12/o1_mf_1_69_ngp6hknh_.arc ,12-oct-2025 03:08:15,12-oct-2025 04:01:05,4102825842,GGSRC05T, 1,07-oct-2023 14:02:26,12.2.0.0.0 , 1, 69, 1686513, 1693275,NO ,NO ,ARCHIVE, 512, 10562560, , 0, 0
Elapsed: 00:00:00.00 04:03:56 SQL>
|
Step 5) Start LogMiner and specify the dictionary to use & choice of tx status
EXECUTE DBMS_LOGMNR.START_LOGMNR( -
OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.COMMITTED_DATA_ONLY + DBMS_LOGMNR.PRINT_PRETTY_SQL);
---try more restrictive permission:
EXECUTE DBMS_LOGMNR.START_LOGMNR( -
STARTTIME => TO_DATE('01-Jan-1998 08:30:00','DD-MON-YYYY HH:MI:SS'), -
ENDTIME => TO_DATE('01-Jan-1998 08:45:00', 'DD-MON-YYYY HH:MI:SS'));
Actual:
04:03:56 SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR( - OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.COMMITTED_DATA_ONLY + DBMS_LOGMNR.PRINT_PRETTY_SQL);04:04:21 >
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01 04:04:21 SQL>
|
Step 6) Query v$logmnr_contents to display the REDO statement alone, in case your purpose of this operation is audit
SELECT username AS USR, (XIDUSN || '.' || XIDSLT || '.' || XIDSQN) AS XID, SQL_REDO
FROM V$LOGMNR_CONTENTS;
-- so much content
SELECT username AS USR, (XIDUSN || '.' || XIDSLT || '.' || XIDSQN) AS XID, SQL_REDO
FROM V$LOGMNR_CONTENTS
where seg_owner='?';
Actual:
only committed data:
04:05:10 SQL> SELECT username AS USR, (XIDUSN || '.' || XIDSLT || '.' || XIDSQN) AS XID, SQL_REDO FROM V$LOGMNR_CONTENTS where seg_owner='USERAPP01';04:07:23 2 04:07:23 3
USR ,XID ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------,-------------------------------------------------------------------------------------------------------------------------- SQL_REDO ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ USERAPP01 ,3.19.1027 update "USERAPP01"."TBLAPP01_06" set "IDSTR" = 'A' where "ID" = 196 and "IDSTR" = 'Y' and ROWID = 'AAAFfaAAHAAAACLAAA';
Elapsed: 00:00:01.85
Activate rollback data as well:
04:07:25 SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR( - OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG +04:07:44 > DBMS_LOGMNR.PRINT_PRETTY_SQL);
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01 04:07:53 SQL>
04:08:02 SQL> SELECT username AS USR, (XIDUSN || '.' || XIDSLT || '.' || XIDSQN) AS XID, SQL_REDO,timestamp FROM V$LOGMNR_CONTENTS where seg_owner='USERAPP01';04:08:39 2
USR ,XID ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------,-------------------------------------------------------------------------------------------------------------------------- SQL_REDO ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ TIMESTAMP -------------------- UNKNOWN ,7.31.4294967295 Unsupported 12-oct-2025 03:36:54
UNKNOWN ,4.18.4294967295 Unsupported 12-oct-2025 03:37:57
USERAPP01 ,3.19.1027 update "USERAPP01"."TBLAPP01_06" set "IDSTR" = 'A' where "ID" = 196 and "IDSTR" = 'Y' and ROWID = 'AAAFfaAAHAAAACLAAA'; 12-oct-2025 03:53:42
SYS ,8.29.859 update "USERAPP01"."TBLAPP01_06" set "IDSTR" = 'Z' where "ID" = 196 and "IDSTR" = 'A' and ROWID = 'AAAFfaAAHAAAACLAAA'; 12-oct-2025 03:54:48
SYS ,8.29.859 update "USERAPP01"."TBLAPP01_06" set "IDSTR" = 'A' where ROWID = 'AAAFfaAAHAAAACLAAA'; 12-oct-2025 03:54:50
Elapsed: 00:00:01.21 04:08:42 SQL>
1* select username,operation,seg_owner,seg_name,count(1) from v$logmnr_contents group by username,operation,seg_owner,seg_name order by 1,2,3 SQL> /
USERNAME ,OPERATION ,SEG_OWNER ,SEG_NAME , COUNT(1) ----------,--------------------------------,----------,--------------------,---------- GGADMIN ,COMMIT , , , 35 GGADMIN ,START , , , 35
... UNKNOWN ,UPDATE ,SYS ,WRM$_SNAPSHOT , 2 UNKNOWN ,UPDATE ,SYS ,WRM$_WR_CONTROL , 4 USERAPP01 ,COMMIT , , , 2 USERAPP01 ,START , , , 2 USERAPP01 ,UPDATE ,SYS ,USER$ , 1 USERAPP01 ,UPDATE ,USERAPP01 ,TBLAPP01_06 , 1
|
Step 7) close logmnr session, in case needed you can try different setting in start_logmnr without calling the end_logmnr procedure.
EXECUTE DBMS_LOGMNR.END_LOGMNR();
Actual:
SQL> EXECUTE DBMS_LOGMNR.END_LOGMNR();
PL/SQL procedure successfully completed.
|
Alert log should print something like below:
2025-10-12 04:18:28.119000 +01:00 LOGMINER: Begin mining logfile for session -2147479551 thread 1 sequence 69, /oraarch/GGSRC05T/archivelog/2025_10_12/o1_mf_1_69_ngp6hknh_.arc LOGMINER: skipped redo. Thread 1, RBA 0x000045.000035dc.0068, nCV 17 LOGMINER: session# 2147487745 op 4.1 (Transaction Block) LOGMINER: End mining logfile for session -2147479551 thread 1 sequence 69, /oraarch/GGSRC05T/archivelog/2025_10_12/o1_mf_1_69_ngp6hknh_.arc |
Foot notes:
Until 12.2, you had continous_mine option, where you dont need to supply the redologfiles, it is auto populated (doc says db needs to stay in mount and arch log mode for this to work!!)
EXECUTE DBMS_LOGMNR.START_LOGMNR(-
STARTSCN => 56453576, -
ENDSCN => 56454208, -
OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + -
DBMS_LOGMNR.COMMITTED_DATA_ONLY + -
DBMS_LOGMNR.PRINT_PRETTY_SQL + -
DBMS_LOGMNR.CONTINUOUS_MINE);
-- routine 1 failed without supplemental logging
-- routine 2 with supplemental logging
No comments:
Post a Comment