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.<\/p>\n
\r\n#!\/home\/oracle\/local\/bin\/python\r\n\"\"\"\r\n------------------------------------------------------------------------\r\nAuthor: Steve Howard\r\nDate: December 10, 2010\r\nPurpose: Simple program to process redo log changes\r\n------------------------------------------------------------------------\r\n\"\"\"\r\n\r\nimport cx_Oracle, sys, string, thread, datetime\r\nfrom threading import Lock\r\nfrom threading import Thread\r\n\r\nplock = thread.allocate_lock()\r\n\r\n#set up our time window\r\nstartYear = int(string.split(sys.argv[1],'_')[0])\r\nstartMonth = int(string.split(sys.argv[1],'_')[1])\r\nstartDay = int(string.split(string.split(sys.argv[1],' ')[0],'_')[2])\r\nstartHour = int(string.split(string.split(sys.argv[1],' ')[1],':')[0])\r\nstartMinute = int(string.split(string.split(sys.argv[1],' ')[1],':')[1])\r\nstartTime=datetime.datetime(startYear, startMonth, startDay, startHour,startMinute, 0)\r\n\r\nendYear = int(string.split(sys.argv[2],'_')[0])\r\nendMonth = int(string.split(sys.argv[2],'_')[1])\r\nendDay = int(string.split(string.split(sys.argv[2],' ')[0],'_')[2])\r\nendHour = int(string.split(string.split(sys.argv[2],' ')[1],':')[0])\r\nendMinute = int(string.split(string.split(sys.argv[2],' ')[1],':')[1])\r\nendTime=datetime.datetime(endYear, endMonth, endDay, endHour, endMinute,0)\r\n\r\n#-----------------------------------------------------------------------\r\n\r\nclass readRedoThread(Thread):\r\n def __init__ (self,threadNum):\r\n Thread.__init__(self)\r\n self.t = threadNum\r\n\r\n def run(self):\r\n\r\n conn = cx_Oracle.connect(mode = cx_Oracle.SYSDBA)\r\n cursor = conn.cursor()\r\n contents = conn.cursor()\r\n\r\n cursor.prepare(\"select name \\\r\n from v$archived_log \\\r\n where first_time between :1 and :2 + 60\/1440 \\\r\n and thread# = :3 \\\r\n and deleted = 'NO' \\\r\n and name is not null \\\r\n and dest_id = 1\")\r\n\r\n #...and loop until we are past the ending time in which we are interested...\r\n global startTime\r\n global endTime\r\n\r\n s = startTime\r\n e = endTime\r\n\r\n while s < e:\r\n cursor.execute(\"select name \\\r\n from v$archived_log \\\r\n where first_time between :1 and :2 + 60\/1440 \\\r\n and thread# = :3 \\\r\n and deleted = 'NO' \\\r\n and name is not null \\\r\n and dest_id = 1\",[s, s, self.t])\r\n for row in cursor:\r\n logAdd = conn.cursor()\r\n logAdd.execute(\"begin sys.dbms_logmnr.add_logfile(:1); end;\",[row[0]])\r\n logStart = conn.cursor()\r\n\r\n #you may have to use an \"offline\" catalog if this is being run \r\n # against a standby database, or against a read-only database.\r\n #logStart.execute(\"begin sys.dbms_logmnr.start_logmnr(dictfilename => :1); end;\",[\"\/tmp\/dictionary.ora\"])\r\n #logStart.execute(\"begin sys.dbms_logmnr.start_logmnr(options => dbms_logmnr.dict_from_online_catalog \\\r\n + dbms_logmnr.print_pretty_sql \\\r\n + dbms_logmnr.no_rowid_in_stmt); end;\")\r\n logStart.execute(\"begin sys.dbms_logmnr.start_logmnr(options => dbms_logmnr.dict_from_online_catalog + \\\r\n dbms_logmnr.no_rowid_in_stmt); end;\")\r\n\r\n #contents.execute(\"select sql_redo,thread#,session#,serial#,timestamp,username from v$logmnr_contents where table_name like '%XWCMD_HOLDING%' and thread# = :1\",\r\n #contents.execute(\"select sql_redo,thread#,session#,serial#,timestamp,username from v$logmnr_contents where sql_redo like '%XWCMD_ORACLE_TEXT%' and thread# = :1\", \r\n contents.execute(\"select sql_redo,thread#,session#,serial#,timestamp,username from v$logmnr_contents where thread# = :1\", [self.t])\r\n for change in contents:\r\n #print change[0],change[1],change[2],change[3],change[4],change[5]\r\n plock.acquire()\r\n print change[0]\r\n plock.release()\r\n minutes = datetime.timedelta(minutes=60)\r\n s = s + minutes\r\n\r\n#-----------------------------------------------------------------------\r\n\r\ndef restoreLogs():\r\n #placeholder for future procedure to get any necessary archived redo\r\nlogs from RMAN\r\n pass\r\n\r\n#-----------------------------------------------------------------------\r\n\r\nthreadList = []\r\nthreadNums = []\r\n\r\nconn = cx_Oracle.connect(mode = cx_Oracle.SYSDBA)\r\ncursor = conn.cursor()\r\ncursor.execute(\"select distinct thread# from v$archived_log where first_time >= :1 and next_time <= :2\",[startTime,endTime])\r\n\r\nfor row in cursor:\r\n threadNums.append(row[0])\r\n\r\nconn.close()\r\n\r\nfor i in threadNums:\r\n thisOne = readRedoThread(i)\r\n threadList.append(thisOne)\r\n thisOne.start()\r\n\r\nfor j in threadList:\r\n j.join()\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"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…<\/p>\n