• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 753
  • Last Modified:

Shell Script using Oracle

I am suppose to run this script to see if there is connectivity between Oracle and Informatica.
They mentioned that if the script hangs then the connectivity is lost.
How can I control the script if it hangs.  

sqlplus abcdef/gefhr@oraserver << EOSQL
alter session set nls_date_format = 'MM/DD/YY HH24:MI:SS';
set timing on
select max(session_timestamp) from infaprpc.rep_sess_log;
exit;
EOSQL
0
rvaede
Asked:
rvaede
  • 4
  • 3
1 Solution
 
amit_gCommented:
Try something like this ...

sqlplus abcdef/gefhr@oraserver << EOSQL &
alter session set nls_date_format = 'MM/DD/YY HH24:MI:SS';
set timing on
select max(session_timestamp) from infaprpc.rep_sess_log;
exit;
EOSQL

PID=$!

TimeToWait=300

sleep $TimeToWait

if ps | egrep "^ *$PID" > /dev/null
then
    echo "Script ended. Connectivity is fine."
else
    echo "Script still running. Connectivity is not fine."
    kill -9 $PID
fi
0
 
rvaedeAuthor Commented:
I ran the script and noticed that the PID remains defunct until the end of the script, so therefore it fails
with no connection.  Which is wrong:

dr30304 20994 20993   0        - ?           0:00 <defunct>
0
 
amit_gCommented:
If the process is defunct, most likely the connection did not happen properly but if you want to ignore it, you can do

if ps | egrep "^ *$PID" | grep -v "<defunct>" > /dev/null
0
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 
rvaedeAuthor Commented:
The connection in oracle was fine.  So if the Oracle connection fails, what would happen?
The PID might dissapear from ps therefore that would be wrong because the connection failed.
0
 
amit_gCommented:
That error could be trapped using 2> redirect. What error message do you get in that case?
0
 
rvaedeAuthor Commented:
It never lost connection.  I am not sure what would happen if it did.  Can I force it to lose connection?
0
 
amit_gCommented:
It could be any sqlplus error. You could give incorrect username/password to simulate that error.
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now