Hi,

Am not sure if this has anything to do with perl programming but would like to seek your advice.

I have 2 separate servers each with oracle database installed.I am trying to pull oracle logs from these 2 databases to my main linux server using perl DBI. On this linux server I have 1 dbipoll.pl script and 2 wrapper scripts that pass in parameters to dbipoll.pl.

The scripts I've used are found here: http://www.james.labocki.com/?p=153

Here is the sample wrapper script that I've used to call dbipoll.pl(separate script with different parameters for each databases used): 

#!/bin/sh
#
export ORACLE_HOME=/ora-main/app/oracle/product/11.1.0.6/db
export ORACLE_SID=rmdev1
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME
#
/usr/bin/dbipoll.pl -dbtype=oracle -server=servername1 -dbport=1521 -d
+b=development -user=myuser -xpassword=/etc/dbipasswd -table=dba_audit
+_trail -columns="OS_PROCESS,OS_USERNAME,USERNAME,USERHOST,ACTION,ACTI
+ON_NAME,TRANSACTIONID,SCN,SQL_BIND,SQL_TEXT,TO_CHAR(EXTENDED_TIMESTAM
+P,'MM/DD/YYYY HH24:MI:SS'),EXTENDED_TIMESTAMP" -countfile="/app/oracl
+e/product/monitor/countfile1" -countkey="EXTENDED_TIMESTAMP" -o="/opt
+/oracledb1.log"

[download]

As you can see,the script should retrieve logs and write to oracledb1.log and update the countfile1 with the latest timestamp.The wrapper scripts are scheduled to run at 5mins interval.

The first wrapper script,which I've named as call_dbipoll1.sh runs fine. I am able to retrieve events and both oracledb1.log and countfile1 are successfully updated. However,the second wrapper script,call_dbipoll2.sh is successful only for the first run. For subsequent runs,I've noticed that countfile2 does not get updated with the latest timestamp(ie. the file returns to blank) and oracledb2.log returns to blank as well. Therefore,am unable to pull new events then. Unable to determine what went wrong here..

Here is sample of my second wrapper script that tries to retrieve events from the 2nd database: 
#!/bin/sh
#
export ORACLE_HOME=/ora-main/app/oracle/product/11.1.0.6/db
export ORACLE_SID=rmdev2
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME
#
/usr/bin/dbipoll.pl -dbtype=oracle -server=servername2 -dbport=1521 -d
+b=development -user=myuser -xpassword=/etc/dbipasswd -table=dba_audit
+_trail -columns="OS_PROCESS,OS_USERNAME,USERNAME,USERHOST,ACTION,ACTI
+ON_NAME,TRANSACTIONID,SCN,SQL_BIND,SQL_TEXT,TO_CHAR(EXTENDED_TIMESTAM
+P,'MM/DD/YYYY HH24:MI:SS'),EXTENDED_TIMESTAMP" -countfile="/app/oracl
+e/product/monitor/countfile2" -countkey="EXTENDED_TIMESTAMP" -o="/opt
+/oracledb2.log"

[download]
Is there anything wrong with it?Anyone can advice on the problem or has any solutions/workarounds to solve it? Thanks in advance.

In reply to Need help on how to use Perl DBI script to poll logs from oracle databases? by remy06

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.