Script to check OpenDB

I am mostly working on automating all the steps of deployment and testing. Most of the time we face open DB issue. Somewhere in the server, there can be code issue which leads to holding database connection. This happens mainly with the old release codes. This needs to be constantly monitored across the system. The better approach would be poling DB to check no of database connection which is idle for more than specified time (say 1 hour). This is simple in case of Oracle, because this information can be derived from v$session table.

It has to be implemented with some scripting language (like PERL), but decided to use ant because this has been used before. Here is the sample target used,

<target name="db1" if="oracle1.url" >
 <!-- Connect to database to find idle connections -->
 <echo message="......................... ${db1.name} .........................."/>
 <sql
     driver="${oracle1.driver}"
     url="${oracle1.url}"
     userid="${oracle1.user}"
     password="${oracle1.password}"
     print="yes"
  showheaders="false"
  showtrailers="false"
  onerror="continue"
  output="${session.log}"
  keepformat="yes"
     >
 <classpath refid="common.classpath"/><![CDATA[
  select     rpad(username,15 ,' ')
      rpad(MACHINE, 30,' ')
   rpad(process,15, ' ')
   lpad(last_call_et,15, ' ') '   '
   rpad(osuser, 15,' ')
  from V$session where
   last_call_et > ${oracle1.min.idle.time}
   and last_call_et < ${oracle1.max.idle.time}
   and username = UPPER('${oracle1.user}')
   and (
    machine in ( ${oracle1.machine.name} ) OR
    osuser in ( ${oracle1.os.user} )
   );
  ]]>
 </sql> 
</target>

All the parameter can be configured and email the result file session.log

Now this can be set as crontab and run every one hour.

Miscellaneous Tasks - ant - SQL

I was going through misc tasks of ant while working on build script for one our release. I found SQL task and was wondering why do we need this?

But today i could use this for one of our daily verification script, used this to get failure count from one of the table. what a great usage :)

Link

Sample:

<target name="queue" if="oracle.url" >
<sql
driver="${oracle.driver}"
url="${oracle.url}"
userid="${oracle.user}"
password="${oracle.password}"
print="yes"
showheaders="false"
showtrailers="false"
onerror="continue"
>
<classpath refid="common.classpath"/><![CDATA[

select 'No of item to be processed : ' || count(sqno) 
from Queue where processedtime is null ;

select 'Seq number : ' || sqno || CHR(10) || 
'Total time in queue : ' || numtodsinterval(sysdate-queuecreated,'day') || CHR(10) ||
' (day hour:minute:seconds.milliseconds) ' || CHR(10) ||
'Total number of failure(s) : ' || FAILURECOUNT 
from QUEUE where SQNO =
( select min(SQNO) from Queue 
where processedtime is null );

]]>
</sql>
</target>

Reversion - steps to be considered

All enterprise applications should have reversion process before deploying them into production. This is followed by most of the online applications; to take care of addressing unavoidable issues (revert back to old release, if there is no workaround). Configuration changes are key in this process. Important things to take care,

* Don't depend on back up configuration files: Always change the parameters in current configuration files. This is because even though we take back up of configuration file before deploying current release, there is a possibility that it will be changed in production environment for some other components/reasons. When we revert the release, the changes done after the release will be gone if we use back up file.

* Don't overlook any configuration changes: Do not leave any configuration change assuming that it will not affect old release. If we overlook one parameter and that can even bring down entire environment. We had such a situation while testing in Dev, one parameter has been changed from milliseconds to minutes (frequency between run). So as part of release we changed from 60000 to 1. Everything went well. We have done reversion and missed this parameter, old application assumes this as 1 and runs. Because it is 1 millisecond and it went into loop. Ultimately it brought down entire database, oops. So learned from mistake

ebay buyers feel pride to win product

Past 3 weeks i am watching ebay bidding to buy zune 30GB (i blew black friday deal 76$ :( ). Used zune sold for more than 150$ with shipping around 20$. I am wondering, same model new zune is 159$ (even went down to 149$) in amazon.com. This offer comes with free shipping, why dont they buy this instead of ebay for more.

It may be because winning product gives more joy than buying a fixed price item or I see some customers are relisting item because some body just bid for fun :)

Anyway i may not buy this in ebay if it is more than 60$.

Verifying batch program failure

We have automated ant process which runs every day to check health check of particular component, this has been configured through crontab. It failed to complete the process and hung in between, so there is no mail generated. To close this hole, have written small script which will check the log file and look for 'BUILD SUCCESSFUL' if it is not found error mail will be generated. This is configured to run 1 hour after the verification script. This works well :)

Here is the sample script which i used

ErrorCheck.sh:

#!/bin/ksh
log_file=$1
subject=$2
shift
shift
mailaddrs=$*
## checks for (BUILD SUCCESSFUL). If not present, sends email.
grep_result=`grep -c "BUILD SUCCESSFUL" $log_file`
if [ "$grep_result" = "1" ]
then
echo "NO PROBLEM"
else
echo "PROBLEM"
mailx -s"$subject" $mailaddrs < $log_file
fi

How to Execute:

./ErrorCheck.sh {logfilename} {Email subject} {email addresses separated by space}

./ErrorCheck.sh junitresult.log "Dev Test failed" test@test.com test1@test.com