Running log miner from python

Python is so flexible for quick and dirty toolsets, that I built one this morning that invokes the Oracle log miner software to read archived redo logs. It queries the logs for changes to a given table, and prints any records it finds. The version below needs to be enhanced to have search filters (add username, time, sid, etc.) However, this will get you started.

#!/home/oracle/local/bin/python
"""
------------------------------------------------------------------------
Author:         Steve Howard
Date:           December 10, 2010
Purpose:        Simple program to process redo log changes
------------------------------------------------------------------------
"""

import cx_Oracle, sys, string, thread, datetime
from threading import Lock
from threading import Thread

plock = thread.allocate_lock()

#set up our time window
startYear = int(string.split(sys.argv[1],'_')[0])
startMonth = int(string.split(sys.argv[1],'_')[1])
startDay = int(string.split(string.split(sys.argv[1],' ')[0],'_')[2])
startHour = int(string.split(string.split(sys.argv[1],' ')[1],':')[0])
startMinute = int(string.split(string.split(sys.argv[1],' ')[1],':')[1])
startTime=datetime.datetime(startYear, startMonth, startDay, startHour,startMinute, 0)

endYear = int(string.split(sys.argv[2],'_')[0])
endMonth = int(string.split(sys.argv[2],'_')[1])
endDay = int(string.split(string.split(sys.argv[2],' ')[0],'_')[2])
endHour = int(string.split(string.split(sys.argv[2],' ')[1],':')[0])
endMinute = int(string.split(string.split(sys.argv[2],' ')[1],':')[1])
endTime=datetime.datetime(endYear, endMonth, endDay, endHour, endMinute,0)

#-----------------------------------------------------------------------

class readRedoThread(Thread):
  def __init__ (self,threadNum):
    Thread.__init__(self)
    self.t = threadNum

  def run(self):

    conn = cx_Oracle.connect(mode = cx_Oracle.SYSDBA)
    cursor = conn.cursor()
    contents = conn.cursor()

    cursor.prepare("select name \
                      from v$archived_log \
                      where first_time between :1 and :2 + 60/1440 \
                        and thread# = :3 \
                        and deleted = 'NO' \
                        and name is not null \
                        and dest_id = 1")

    #...and loop until we are past the ending time in which we are interested...
    global startTime
    global endTime

    s = startTime
    e = endTime

    while s < e:
      cursor.execute("select name \
                        from v$archived_log \
                        where first_time between :1 and :2 + 60/1440 \
                          and thread# = :3 \
                          and deleted = 'NO' \
                          and name is not null \
                          and dest_id = 1",[s, s, self.t])
      for row in cursor:
        logAdd = conn.cursor()
        logAdd.execute("begin sys.dbms_logmnr.add_logfile(:1); end;",[row[0]])
        logStart = conn.cursor()

        #you may have to use an "offline" catalog if this is being run 
        #  against a standby database, or against a read-only database.
        #logStart.execute("begin sys.dbms_logmnr.start_logmnr(dictfilename => :1); end;",["/tmp/dictionary.ora"])
        #logStart.execute("begin sys.dbms_logmnr.start_logmnr(options => dbms_logmnr.dict_from_online_catalog \
                                                                       + dbms_logmnr.print_pretty_sql \
                                                                       + dbms_logmnr.no_rowid_in_stmt); end;")
        logStart.execute("begin sys.dbms_logmnr.start_logmnr(options => dbms_logmnr.dict_from_online_catalog + \
                                                                        dbms_logmnr.no_rowid_in_stmt); end;")

        #contents.execute("select sql_redo,thread#,session#,serial#,timestamp,username from v$logmnr_contents where table_name like '%XWCMD_HOLDING%' and thread# = :1",
        #contents.execute("select sql_redo,thread#,session#,serial#,timestamp,username from v$logmnr_contents where sql_redo like '%XWCMD_ORACLE_TEXT%' and thread# = :1",      
        contents.execute("select sql_redo,thread#,session#,serial#,timestamp,username from v$logmnr_contents where thread# = :1", [self.t])
        for change in contents:
          #print change[0],change[1],change[2],change[3],change[4],change[5]
          plock.acquire()
          print change[0]
          plock.release()
      minutes = datetime.timedelta(minutes=60)
      s = s + minutes

#-----------------------------------------------------------------------

def restoreLogs():
 #placeholder for future procedure to get any necessary archived redo
logs from RMAN
 pass

#-----------------------------------------------------------------------

threadList = []
threadNums = []

conn = cx_Oracle.connect(mode = cx_Oracle.SYSDBA)
cursor = conn.cursor()
cursor.execute("select distinct thread# from v$archived_log where first_time >= :1 and next_time <= :2",[startTime,endTime])

for row in cursor:
 threadNums.append(row[0])

conn.close()

for i in threadNums:
 thisOne = readRedoThread(i)
 threadList.append(thisOne)
 thisOne.start()

for j in threadList:
 j.join()