Question

How to access or view audit logs from oracle source

  • 25 February 2022
  • 3 replies
  • 109 views

we are using CDC(streamsets) for oracle source and , redo logs are enabled from source end(oracle). wanted to know how to view those redo logs so that we get an idea like which record is getting INSERTED/UPDATED/DELETED and for which table.


3 replies

while I'm querying like : select * from v$logmnr_contents  , I'm getting following error

dbms_logmnr.start_logmnr() must be invoked before selecting from v$logmnr_contents

Userlevel 2
Badge

Hi @harshith 

I am not sure how to check in the source side, but from the SDC side you can enable the DEBUG logging for package "com.streamsets.pipeline.stage.origin.jdbc.cdc”.

log4j.logger.com.streamsets.pipeline.stage.origin.jdbc.cdc=DEBUG
DEBUG	Num Active Txns = 0 Total Cached Records = 0 Commit SCN = , SCN = 11428550, Operation = 1, Txn Id = 9.13.3813, Timestamp = 2022-02-25T09:06:55, Row Id = AAAOyeAAEAAAACGAAL, Redo SQL = insert into "SDC"."RANJITH"("ID") values ('990')

Once you add the DEBUG config, you will see the above DEBUG logging in sdc.log file from which you can check which sql command is read by oracle origin.

Userlevel 2
Badge

Querying LogMiner is not just running some query using v$logmnr_contents. It needs planning (without a given concrete DB configuration you will not be able to see contents), and setup (you need to start a LogMiner session before starting actual querying). Please check Oracle’s official documentation for all the details: https://docs.oracle.com/en/database/oracle/oracle-database/21/sutil/oracle-logminer-utility.html#GUID-A5A1E94C-45AA-4B6A-B7B3-E1AD2F8675B5.  

Reply