Home » Infrastructure » Linux » Control not returning to UNIX when sqlplus is executed in while loop of shell script (Linux)
Control not returning to UNIX when sqlplus is executed in while loop of shell script [message #679922] Mon, 13 April 2020 23:05 Go to next message
preetham30
Messages: 6
Registered: April 2020
Location: Toronto
Junior Member
Hi All,

I have observed some of the shell scripts gets stuck because sqlplus session is not completing its session. This behavior is intermittent. I will give one of the scenario. Below is the code which executes one sql query.
 if [[ ${rcloop1} -eq 0 ]]; then
      sql_cnt_loop=0      
      # perform 2nd loop after 1 second to check if Indexing has completed
      sleep 1
      while : ; do
         exec_SQL "SELECT count(*)    \
                     FROM server_states  \
                    WHERE topic = 'WLM_FPFA_INDEXES'  \
                      AND current_state <> new_state \
                      AND server_name in ('${E_WLM_BATCH_SERVER_NAME_1}', '${E_WLM_BATCH_SERVER_NAME_2}')"
         rcloop2=$?

         if [[ $rcloop2 -eq 0 ]]; then
            sql_cnt_loop=$sql_cnt_loop+1
            if [[ ${E_WLM_SQL_RESULT} -eq 0 ]]; then
               Log "DJ Indexing has Completed..."
               rcloop2=0 # no rows - indexing has completed (not found in server_states table), return RC=0
               break     # exit from 2nd loop
            elif [[ ${E_WLM_SQL_RESULT} -gt 0 ]]; then
               sleep 1 # wait 1 sec then loop again
               continue  # record exists, continue monitoring indexing - loop again
            fi

         fi
      done

      rc=$rcloop2

   else
      rc=$rcloop1
   fi
below is the unix session.
pwlm 8297 8290 0 00:16 ? 00:00:03 /bin/ksh -a /opt/XWLMLL02
pwlm 29121 8297 0 00:32 ? 00:00:00 sqlplus -s

The script has run a process internally and above code part is to monitor whether the process is completed. Even though the process is completed, the script has not moved further from this point. Upon checking further, I have seen one active sqlplus session. I suspect that the sqlplus session is from the above code and it is somehow stuck in turn the script has stuck.

Below is the actual function exec_SQL.

exec_SQL()
{
   sql_Stmt=$1

   sql_Type=`echo $sql_Stmt | awk -F " " '{ printf toupper($1) }'`

   E_WLM_SQL_RESULT=`sqlplus -s /@${E_WLM_DB} <<!
      set pagesize 0 feedback off verify off heading off echo on
      whenever SQLERROR exit SQL.SQLCODE
      whenever OSERROR exit 9
      $sql_Stmt;
      commit;
   !`

   sql_RC=$?

   if [[ $sql_RC -eq 0 ]]; then

      str_Result=`echo $E_WLM_SQL_RESULT | grep SP2`  

      if [[ ${#str_Result} > 0 ]]; then
         sql_RC=57      # SQL Error
         sql_Code=0 
      else
         export E_WLM_SQL_RESULT
      fi
   else
      sql_Code=$sql_RC   # Store SQL.SQLCODE before overwriting

      if [[ $sql_RC -eq 9 ]]; then   # OSERROR
         sql_RC=91     
      else
         case $sql_Type in 
            INSERT) sql_RC=52 ;;
            UPDATE) sql_RC=53 ;;
            SELECT) sql_RC=54 ;;
            DELETE) sql_RC=56 ;;
            *) sql_RC=57 ;;
         esac
      fi
   fi

   if [[ $sql_RC -ne 0 ]]; then
      Log "Error in executing SQL Statement: $sql_Stmt "
      Log "SQL.SQLCODE=$sql_Code"
      Log "=====================================   ORACLE ERROR DESCRIPTION  ====================================="  
      Log "$E_WLM_SQL_RESULT"
      Log "======================================================================================================="
      Log "Returning with $sql_RC"

   fi

   return $sql_RC 
}
I have observed this similar issue 3 times till now in the past 7 days. But, it is for different scripts. The logic remains the same.

This script was working fine in our old infrastructure where there was AIX server. We have not at all faced this issue over there.

Anyone, could help me to resolve the issue?

Thanks in Advance.
Re: Control not returning to UNIX when sqlplus is executed in while loop of shell script [message #679923 is a reply to message #679922] Tue, 14 April 2020 00:24 Go to previous messageGo to next message
Michel Cadot
Messages: 67487
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Try with an "exit" statement at the end of your SQL*Plus session.

Re: Control not returning to UNIX when sqlplus is executed in while loop of shell script [message #679983 is a reply to message #679923] Wed, 15 April 2020 22:53 Go to previous messageGo to next message
preetham30
Messages: 6
Registered: April 2020
Location: Toronto
Junior Member
Thanks Michel. I have tried adding the exit statement. Still no luck. At one random query, the sqlplus session got stuck.

E_WLM_SQL_RESULT=`sqlplus -s /@${E_WLM_DB} <<!
set pagesize 0 feedback off verify off heading off echo on
whenever SQLERROR exit SQL.SQLCODE
whenever OSERROR exit 9
$sql_Stmt;
commit;
exit
!`
Re: Control not returning to UNIX when sqlplus is executed in while loop of shell script [message #679984 is a reply to message #679983] Thu, 16 April 2020 00:28 Go to previous messageGo to next message
Michel Cadot
Messages: 67487
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Insert the following statement at the top of your SQL*Plus script:
exec DBMS_SESSION.SESSION_TRACE_ENABLE;
Then each SQL*Plus session will create a trace file and you will know what happened in all but above all in the one that gets stuck.

But one question, are you sure what gets stuck is the query and not the loop that does not end because the outer process (the indexing) is long or stuck?

Re: Control not returning to UNIX when sqlplus is executed in while loop of shell script [message #679990 is a reply to message #679984] Thu, 16 April 2020 10:14 Go to previous messageGo to next message
preetham30
Messages: 6
Registered: April 2020
Location: Toronto
Junior Member
Thanks Michel. I will enable the trace. It is confirmed that the sqlplus getting stuck. Recently, I have faced this issue with one script which did not have any loop. As I told, we have 50+ scripts which calls this generic exec_sql function and this will be executed many thousands times daily. For our luck, today, all the scripts got completed without any sqlplus being stuck.
Re: Control not returning to UNIX when sqlplus is executed in while loop of shell script [message #679991 is a reply to message #679990] Thu, 16 April 2020 11:24 Go to previous messageGo to next message
Michel Cadot
Messages: 67487
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
It is confirmed that the sqlplus getting stuck
On what?
Post the associated trace file (or at least its bottom) it will ways on what the session is doing or waiting for.

Re: Control not returning to UNIX when sqlplus is executed in while loop of shell script [message #679995 is a reply to message #679991] Thu, 16 April 2020 13:38 Go to previous messageGo to next message
preetham30
Messages: 6
Registered: April 2020
Location: Toronto
Junior Member
Hi Michel,

I need to find out why it is getting stuck. Today only I have introduced the trace, and I will be running those scripts later this day. I told it is confirmed because I have added logging in the shell script before and after the sqlplus. The log after the sqlplus did not execute when I faced this issue last time.The tricky part is we are facing this issue intermittently and we are not sure when this will occur again. I will update this post when this issue re occurs.
Re: Control not returning to UNIX when sqlplus is executed in while loop of shell script [message #679997 is a reply to message #679995] Thu, 16 April 2020 13:50 Go to previous messageGo to next message
Michel Cadot
Messages: 67487
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
I need to find out why it is getting stuck.

The first thing to investigate is the trace file, it will give us clues to investigate further the problem, without it we can't help more.

A question: is the sqlplus session local to the database server or remote?

Re: Control not returning to UNIX when sqlplus is executed in while loop of shell script [message #680001 is a reply to message #679997] Thu, 16 April 2020 20:55 Go to previous messageGo to next message
preetham30
Messages: 6
Registered: April 2020
Location: Toronto
Junior Member
The sqlplus session is executed in application server.
Re: Control not returning to UNIX when sqlplus is executed in while loop of shell script [message #680003 is a reply to message #680001] Fri, 17 April 2020 00:26 Go to previous messageGo to next message
Michel Cadot
Messages: 67487
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

OK, waiting for the trace, check with your network admin there is no problem at network level when the sqlplus session starts to be stuck.
Can you post the sqlnet.ora file for both application and database servers.

Re: Control not returning to UNIX when sqlplus is executed in while loop of shell script [message #680016 is a reply to message #680003] Fri, 17 April 2020 10:56 Go to previous messageGo to next message
Michel Cadot
Messages: 67487
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Another member of this forum gave me an idea.
Can you remove the "-s" from the sqlplus command line.
Something like the following can happen: if you have an ampersand in your statement then sqlplus is waiting for you to enter a value, something you can't see with the "-s" option (to avoid this you can add the "define off" in your "set" command).
There are other cases like that where sqlplus may be waiting for an input.
Maybe not from the statement itself but from a glogin.sql or login.sql script...

Re: Control not returning to UNIX when sqlplus is executed in while loop of shell script [message #680240 is a reply to message #680016] Wed, 29 April 2020 17:11 Go to previous messageGo to next message
preetham30
Messages: 6
Registered: April 2020
Location: Toronto
Junior Member
Hi Michel,

After I enabled the trace logs, we got this issue yesterday and today. Almost after 2 weeks. In both cases, the trace log stuck at the below place.

(3460657536) [29-APR-2020 07:56:36:908] nsprecv: 00 0A 00 00 05 02 00 00 |........|
(3460657536) [29-APR-2020 07:56:36:908] nsprecv: 01 36 |.6 |
(3460657536) [29-APR-2020 07:56:36:908] nsprecv: normal exit
(3460657536) [29-APR-2020 07:56:36:908] nscon: got NSPTRD packet
(3460657536) [29-APR-2020 07:56:36:908] nscon: recving connect data
(3460657536) [29-APR-2020 07:56:36:908] nsdo: entry
(3460657536) [29-APR-2020 07:56:36:908] nsdo: cid=0, opcode=68, *bl=310, *what=1, uflgs=0x4001, cflgs=0x0
(3460657536) [29-APR-2020 07:56:36:908] nsdo: nsctx: state=2, flg=0x804005, mvd=0
(3460657536) [29-APR-2020 07:56:36:908] nsdo: gtn=10, gtc=10, ptn=10, ptc=8111
(3460657536) [29-APR-2020 07:56:36:908] nsrdr: entry
(3460657536) [29-APR-2020 07:56:36:908] nsrdr: recving a packet
(3460657536) [29-APR-2020 07:56:36:908] nsprecv: entry
(3460657536) [29-APR-2020 07:56:36:908] nsprecv: reading from transport...
(3460657536) [29-APR-2020 07:56:36:908] nttrd: entry

Thanks,
Preetham
Re: Control not returning to UNIX when sqlplus is executed in while loop of shell script [message #680241 is a reply to message #680240] Thu, 30 April 2020 00:17 Go to previous messageGo to next message
Michel Cadot
Messages: 67487
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I didn't ask for a Net trace but a SQL one in the session, see my post above how to do it.

Also did you do what I mentioned in my previous post? I think this is the most likely issue and way to get what happens.

Re: Control not returning to UNIX when sqlplus is executed in while loop of shell script [message #682166 is a reply to message #680240] Wed, 07 October 2020 03:59 Go to previous messageGo to next message
vikram.mahra@gmail.com
Messages: 1
Registered: October 2020
Location: India
Junior Member
Hello Preetham,
Any luck in finding the cause of the issue?
We are facing similar issue, when the sqlplus PID gets hung. We worked with DBA team also, and from Database side, the procedures are completing, but the control does not come back to our Unix server, and the shell script gets stuck at the same sqlplus PID.


Thanks!!
Re: Control not returning to UNIX when sqlplus is executed in while loop of shell script [message #682172 is a reply to message #682166] Wed, 07 October 2020 10:53 Go to previous messageGo to next message
Michel Cadot
Messages: 67487
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

So post the script!
Can you think we can debug code we don't see?
Can you do it?

Re: Control not returning to UNIX when sqlplus is executed in while loop of shell script [message #682194 is a reply to message #682166] Thu, 08 October 2020 07:35 Go to previous message
EdStevens
Messages: 1269
Registered: September 2013
Senior Member
As Michael Cadot said, no one can debug a script that they cannot see.
And you should start your own thread instead of hijacking someone else's.
Previous Topic: Best RHEL 64bit Version for Oracle 11g 11.2.0.10?
Next Topic: Oracle 11g XE installation in Debian
Goto Forum:
  


Current Time: Fri Oct 30 08:49:09 CDT 2020