Kevin Kempf's Blog

October 23, 2014

Running a shell script via concurrent request

Filed under: 11i, Linux, Mobile Supply Chain, R12.2 — kkempf @ 8:55 am

shell

The Problem

Mobile Supply Chain (MSCA) in 11i and R12 is a rather unreliable product as I discussed here.  It tends to cease working for no reason; I’ve blogged about this before so I won’t dwell on how ridiculous that is.  While I have Linux/OS cron jobs to bounce the service entirely once per day, and rotate the ports twice per day, it’s still not enough.  Invariably, the dispatcher will stop fielding connections at 2am on Sunday (2 hours after the bounce), when I get a phone call and I have to go VPN in and unscrew it.  It is in my own self-interests, then, that I set out to create a concurrent request which would cleanly kill/restart MSCA from inside the Ebusiness Suite, and the reason behind this post.  Incidentally, one added benefit of using a concurrent process is that I can leverage Oracle responsibilities and security, ensuring not just anybody can bounce the MSCA telnet server.

Nothing New Here

I suppose I’ve always known there was a host option when you defined a concurrent program, I just never knew if it worked.  I’m in a weird sort of down-time before our R12.2 go-live and find myself without much to work on for a few days, so I’m “playing” in R12.2.  There’s nothing revolutionary about this post, it’s just my goal to walk from beginning to end through the process of running a host script as a concurrent process, and provide some in depth/meaningful context as to why you’d like to do that.  In fact I’d like to acknowledge the Oracle Ebizsuite blog here which helped me understand how to go about setting this up, as well as Doc ID 1594853.1.

From the Apps to the OS

I’ll walk the setup starting within the apps.  This bit hasn’t changed any from 11i to R12.

Concurrent Program Executable

As sysadmin, define the Executable via Concurrent->Program->Executable

CP Executable

Incidentally, you need to define an application in the 3rd line; we used our custom schema.

Concurrent Program Definition

Now define the concurrent program via Concurrent->Program->Define

Incidentally, you need to define an application in the 3rd line; we used our custom schema.  The Executable in the 5th field is the one you defined in the prior step.  Note I have no parameters, but if you wanted to you could add them here.  We’ll go over how the shell sees these in a minute.

CP Define

Assign a Request Group

Go to Security->Responsibility->Request and figure out what group can run this concurrent request.  Or create a group, I suppose.

Sec Respons Request

Write Your Script

Things to know about the script

  • appsuser is the OS user who started MSCA out of $ADMIN_SCRIPTS_HOME via mwactrlwrpr.sh or the like
  • kkempf@myemail.com references: I want to know when someone runs this request, or when the dispatcher port isn’t free for some reason to restart the dispatcher (in essence, meaning the script failed)
  • System vs. User parameters: I didn’t see any way to make it cleaner than was done over here, so I pulled it in so you can see the parms
    • $0: The shell script being run
    • $1: apps/{your apps password in plain text!!!}  HINT: Don’t put this in the concurrent request, or the person who just ran this CR can see the apps password
    • $2: unsure; possibly OS PID?
    • $3: Apps userid who ran the job
    • $4: Concurrent Request ID
    • $5: First User parameter (in my case, there are none)
    • $6-$n: Additional User parameters
  • 11i vs. R12 parms: I had two versions because they moved where the port number of the individual telnet servers were between 11i and 12 so awk changed slightly to get the right spot
    •     #DTL=`ps -fu appsuser|grep telnet|grep -v "grep"|awk '{print $15}'`   # 11i
          DTL=`ps -fu appsuser|grep telnet|grep -v "grep"|awk '{print $17}'`   #  R12.2
  • Seems like it need the .prog extension to be called e.g. scriptname.prog
  • If you try to use this in 11i you will need to define $ADMIN_SCRIPTS_HOME variable via .bash_profile or something as it’s a new one in R12.  Or you can just hardcode the path to the mwactlwrpr.sh script
  • I put the script in $CUSTOM_TOP/bin
  • Script walk through
    • It first echoes back your parameters
    • Next it issues the command mwactlwrpr.sh stop to attempt to gracefully shut down the MSCA telnet server.  This will fail to stop the dispatcher.
    • It emails me that the concurrent request was run, so I can be aware of how often this is needed, and who is doing it
    • Sleep 30 seconds so the mwactlwrpr.sh command can try to do it’s job.  Then figure out the PID for MWADIS and kill it
    • Next figure out the remaining telnet processes and kill them.  This doesn’t normally execute
    • Sleep 30 seconds and then issue mwactlwrpr.sh start.  Tee the output of this command to a temp file and scan for the words “is not free to start the dispatcher”
      • tee /tmp/mwa_alert.txt
    • If you see that it’s not free to start the dispatcher, email me again, this means there’s a bigger problem which needs my attention

bounce_mwa.prog

#!/bin/bash
# mwa_bounce.sh   06/24/13 kmk

echo "Following are System Parameters"
echo "~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~"
p0=$0
p1=$1
p2=$2
p3=$3
p4=$4
echo "1st System Parameter    :"$p0
#echo "2nd System Parameter    :"$p1
echo "3rd System Parameter    :"$p2
echo "4th System Parameter    :"$p3
echo "5th System Parameter    :"$p4
echo "~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~"
echo "~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~"
echo "Following are User Parameters  "
echo "~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~"
#u1=$5
#echo "1st User Parameter    :"$u1
echo "~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~"


echo "Stopping MWA Server via "$p0
echo
nohup $ADMIN_SCRIPTS_HOME/mwactlwrpr.sh stop $p1
echo $ADMIN_SCRIPTS_HOME/mwactlwrpr.sh stop APPS/password

mail -s "Concurrent Request "$p4" Submitted to Bounce MWA by "$p3 kkempf@myemail.com < /dev/null

sleep 30

# Kill MWADIS

for pids in `ps -fu appsuser|grep MWADIS|grep -v "grep"|awk '{print $2}'`
do
    echo "forcing kill on MWADIS PID ${pids}"
    kill -9 ${pids}
done

# Kill telnet
for pids in `ps -fu appsuser|grep telnet|grep -v "grep"|awk '{print $2}'`
do
    #DTL=`ps -fu appsuser|grep telnet|grep -v "grep"|awk '{print $15}'`   # 11i
    DTL=`ps -fu appsuser|grep telnet|grep -v "grep"|awk '{print $17}'`   #  R12.2
    echo "forcing kill on PID ${pids} for ${DTL}"
    kill -9 ${pids}
done

sleep 30

echo "Starting MWA Server via "$p0
echo $ADMIN_SCRIPTS_HOME/mwactlwrpr.sh start APPS/password
echo
rm -rf /tmp/mwa_alert.txt
touch /tmp/mwa_alert.txt
nohup $ADMIN_SCRIPTS_HOME/mwactlwrpr.sh start $p1 |tee /tmp/mwa_alert.txt
  
# if the dispatcher isn't free, email me

MWAERROR=`cat /tmp/mwa_alert.txt | grep "is not free to start the dispatcher"`

if [ ! -z "$MWAERROR" ]
then
  echo $MWAERROR | mail -s "MWA Error" kkempf@myemail.com
fi

Why Didn’t I Just Use Oracle’s Vanilla Scripts?

In short, because they don’t work.  They start MSCA fine, but in 12.2 (at least as of the AD delta 5) they don’t actually stop the processes correctly.  It stops the individual ports, but not the dispatcher.  The dispatcher is like some bad villain in an action movie that just refuses to die:

$ ps -ef|grep oratest
...                                                                                                                                                                                                                                    
oratest  29516     1  0 Oct21 ?        00:01:26 /u02/apptest/fs2/EBSapps/comn/util/jdk32/bin/java -Dxdo.xliff.source=EBS -DCLIENT_PROCESSID=29516 -Doracle.apps.mwa=/u02/apptest/fs2/EBSapps/appl/mwa/12.0.0 -Doracle.apps.jrad.mmd=/u02/apptest/fs2/FMW_Home/Oracle_EBS-app1/applications/oacore/html/jrad -Doracle.apps.inst=/u02/apptest/fs2/inst/apps/TEST_host -mx512m -ms128m oracle.apps.mwa.presentation.telnet.Listener 10260
oratest  29540     1  0 Oct21 ?        00:01:24 /u02/apptest/fs2/EBSapps/comn/util/jdk32/bin/java -Dxdo.xliff.source=EBS -DCLIENT_PROCESSID=29540 -Doracle.apps.mwa=/u02/apptest/fs2/EBSapps/appl/mwa/12.0.0 -Doracle.apps.jrad.mmd=/u02/apptest/fs2/FMW_Home/Oracle_EBS-app1/applications/oacore/html/jrad -Doracle.apps.inst=/u02/apptest/fs2/inst/apps/TEST_host -mx512m -ms128m oracle.apps.mwa.presentation.telnet.Listener 10262
oratest  29567     1  0 Oct21 ?        00:01:25 /u02/apptest/fs2/EBSapps/comn/util/jdk32/bin/java -Dxdo.xliff.source=EBS -DCLIENT_PROCESSID=29567 -Doracle.apps.mwa=/u02/apptest/fs2/EBSapps/appl/mwa/12.0.0 -Doracle.apps.jrad.mmd=/u02/apptest/fs2/FMW_Home/Oracle_EBS-app1/applications/oacore/html/jrad -Doracle.apps.inst=/u02/apptest/fs2/inst/apps/TEST_host -mx512m -ms128m oracle.apps.mwa.presentation.telnet.Listener 10264
oratest  29584     1  0 Oct21 ?        00:00:00 /bin/sh -f /u02/apptest/fs2/inst/apps/TEST_host/admin/scripts/mwactl.sh start_dispatcher
oratest  29589 29584  0 Oct21 ?        00:00:01 /u02/apptest/fs2/EBSapps/appl/mwa/12.0.0/bin/MWADIS
$ mwactlwrpr.sh stop apps/apps

Stopping MWA Servers and the dispatcher ....
Stopping MWA Server on Port number: 10260
Stopping MWA Server on Port number: 10262
Stopping MWA Server on Port number: 10264
Stopping MWA Dispatcher on Port number: 10830
$ ps -ef|grep oratest
...                                                                                                                                                                                
oratest  29584     1  0 Oct21 ?        00:00:00 /bin/sh -f /u02/apptest/fs2/inst/apps/TEST_host/admin/scripts/mwactl.sh start_dispatcher
oratest  29589 29584  0 Oct21 ?        00:00:01 /u02/apptest/fs2/EBSapps/appl/mwa/12.0.0/bin/MWADIS

$  mwactl.sh stop_dispatcher apps/apps
MWA Telnet Server Release: 1.0.8.4 [December 12th 2002]
Telnet dispatcher shut down successfully.
$ ps -ef|grep oratest
...                                                                                                                                                                                        
oratest  29584     1  0 Oct21 ?        00:00:00 /bin/sh -f /u02/apptest/fs2/inst/apps/TEST_host/admin/scripts/mwactl.sh start_dispatcher
oratest  29589 29584  0 Oct21 ?        00:00:01 /u02/apptest/fs2/EBSapps/appl/mwa/12.0.0/bin/MWADIS

Final Steps

Go to $CUSTOM_TOP/bin or wherever you have your shell script

Sym link fndcpesr to the name of the shell script minus the .prog extension:

ln

Confession: I have no idea how you’re supposed to do this in R12 and what I could find on My Oracle Support only covered through R12.1 (where there is only one filesystem).  I don’t know how you’re supposed to handle the dual filesystems, and you can clearly see my sym link is hard coded to fs2.  There has to be a more elegant way to do this, just not sure what it is at the moment.

If I do an ls -ltr on my directory, here’s what it looks like:

ls ltr

Concurrent Request

Here’s the log (there is no output) from my concurrent request.

CR Log

Soapbox

I found this to be a fun diversion, but I like writing shell scripts as much as SQL, and I like figuring out ways to do things which are more “correct”.  All that said, shame on the Oracle MSCA team for delivering such a half-baked product.  The R12.2 version of MSCA is plagued by a host of problems, most of which I have active SR’s open for:

  • Constant issues with tabbing not working in the MSCA GUI
  • Really, your stop script doesn’t stop your process?  Even when I specify stop_dispatcher?
  • How is it that adstrtal.sh doesn’t start MSCA, but it used to in 11i, and R12 has been out for some time?  I could say the same for adstpall.sh…
  • Major, quirky Java compatibility issues with the MSCA GUI
  • For the love of Pete, please don’t tell me that the tabbing issues are known to be resolved in JRE 1.7.23.  While your MSCA might work with 7.23, that’s not a real-world option as everyone else at Oracle is telling me to be on the latest version of Java and this PC also has to connect to Oracle forms.

October 17, 2014

Querying your 11i/R12 Context File from Oracle SQL*Plus

Filed under: 11i, R12.2 — kkempf @ 8:53 am

 

contextfile

Peeling back the layers of the context file, precipitated by MSCA

We all know that the context file holds information about virtually everything on the applications tier, and how important it is.  As a result of our upgrade effort from 11i to R12.2, I started getting incessant calls wanting to know that current dispatcher port for MSCA. While this post will be specific to MSCA as the reason for having to figure out how to do this query, the principles here can be used to obtain any value from the context file via SQL*Plus, for any reason.

Background

I should note a few things about the MSCA product for those (presumably the majority of you) who are unfamiliar with it.  The MSCA product is essentially a telnet front end, which runs on the application server on a specific port.  Users can either use telnet to connect to it, or the MSCA GUI client.  Either one requires a hostname and port.  MSCA actually runs on many ports, managed by a dispatcher to reduce the load on any one port.  So ideally, users connect to the dispatcher.  Thus one would think that when I created a new environment, it would be easy to just inform everyone of the port to connect to and the calls would end.  In my case, there’s two problems with that.  First, there’s a known R12.2 bug which switches the dispatcher port during an online patch session.  So if you’re running on filesystem1, it might be 10300, but if you’re on filesystem2, 10302.  Second, MSCA doesn’t like to stay running.  So it’s helpful for a user to confirm they’re trying to connect to the correct port before calling.

The good part

The bottom line is the query to accomplish this is as follows; the table fnd_oam_context_values stores entire context files as clobs in a field called text.

select
extractvalue( xmltype( text ),’//mwaDispatcherPort‘ )
from
fnd_oam_context_files
where
status = ‘S’
and name = ‘SID_hostname
and last_synchronized =
(select
max( last_synchronized )
from
fnd_oam_context_files
where
status = ‘S’
and name = ‘SID_hostname
) ;

Obviously, when you do it, you’re going to change a few things:

  • mwaDispatcherPort is the tag from the $CONTEXT_FILE you’re looking for the value of.  Examples include oa_context_name, jinit_ver_dot, platform, or whatever you’re looking for.
  • SID_hostname is the database SID followed by an underscore and the applications tier hostname.

Why the subquery?  There are many context files stored in this field as clobs.  You want the most recent one (presumably, tweak as desired, based on last_synched) and you want one which has a value of S (synched? not 100% sure about this column, just figured it out through trial and error)

Incidentally, there is a table called fnd_env_context which holds some, but not all of the values contained in the context file.

End Result

We’re gunning for a centralized APEX report which can query the databases and return the relevant port numbers for users, as appropriate, depending on their apps responsibility.  So they can stop calling me so I can blog more.

October 16, 2014

R12.2 Apps Password Changing

Filed under: Linux, R12.2 — kkempf @ 10:02 am

cloning

Staring down the barrel of an R12.2 upgrade

So we’re getting really, really close to pulling the trigger on this release.  I’m tying up loose ends from an admin perspective, and one of them was confirming I could clone under 12.2.

R12.2 Cloning

In general, I’d say cloning is easier than 11i.  That said, I currently have an SR open because after completing the clone in accordance with 1383621.1 I can’t run a patch cycle because of an issue with appltop_id.  I’m really hoping I missed something simple and it will be resolved soon.  Other than that, my clone worked great, I could log in and things were running right.  Although the cloning document isn’t what I would call a “roadmap” to a clone, it’s enough that if you used to know how to clone you can figure it out.  My favorite part is for the poor people running R12.2 on Windows (hopefully, nobody) where in order to prepare the source system, you have to shut down production to clone.

Password Changing

There’s a lot of moving parts in the E-Business suite, and Oracle did us a favor by providing the FNDCPASS utility again in R12.2 to facilitate apps password changes.  Well really any schema changes.  I grabbed Doc 1674462.1 and I have to say it’s well written and accurate!  In the old days of 11i, you had to run autoconfig to propogate the apps password change to a human-readable text file under the Apache install (among other reasons, I’m sure).  No, really, it’s out there source an 11i environment and type this:

cat ($IAS_HOME)/Apache/modplsql/cfg/wdbsvr.app|grep -m1 password|sed -re ‘s/(^.+= )//’

Well now that bit is buried under Weblogic, so it takes longer in that you have to log in to the admin server and use the web-based GUI to make the JDBC connection change.

My Gift to you

Although I feel the document is accurate, I don’t want to have to go dig it out every time I do a clone (or rely on Metalink being up/accessible) and need to change my non-production system password to something different than production.  Besides, a working shell script means I don’t have to worry about standardizing my process.  In my cloning notes I simply say “change apps password with passwordchanger12.sh”.

So here’s my shell scripted version; I could have automated it more but I feel this script allows for you to walk through the steps without making it a complete “black box” in case it changes in the future.  I think the only caveat is that the directory you run it in needs to be writable.  You can chmod +x the script and just run it without arguments to get the “help” information.

########################
# apps password change #
########################

if [ $# -ne 6 ]
then
  echo "Usage:  passwordchanger12.sh"            
  echo "        -appspass    password             Current apps Password"
  echo "        -syspass     password             Current system Password"
  echo "        -newpass     password             New apps, apps schema's, non-apps and non-apps schema's Password"
  echo
  echo "example:  passwordchange.sh -appspass apps -syspass manager -newpass tayl0r"
  echo
  echo "General order of password changes IAW DOC ID 1674462.1:"
  echo "0) export fnd_users"
  echo "1) Stop everything with adsptall.sh and ensure it's down"
  echo "2) Run this script"
  echo "3) Start only the admin server with adadminsrvctl.sh"
  echo "4) Change the apps password in WLS Datasource" 
  echo "   a. Log in to WLS Administration Console"
  echo "   b. Click Lock & Edit in Change Center"
  echo "   c. In the Domain Structure tree, expand Services, then select Data Sources"
  echo "   d. On the Summary of JDBC Data Sources page, select EBSDataSource"
  echo "   e. On the Settings for EBSDataSource page, select the Connection Pool tab"
  echo "   f. Enter the new password in the Password field"
  echo "   g. Enter the new password in the Confirm Password field"
  echo "   h. Click Save"
  echo "   i. Click Activate Changes in Change Center"
  echo "5) Start everything with adstrtal.sh"
  echo "6) Check in the apps and WLS"
  echo "   a. Log in to WLS Administration Console"
  echo "   b. In the Domain Structure tree, expand Services, then select Data Sources"
  echo "   c. On the Summary of JDBC Data Sources page, select EBSDataSource"
  echo "   d. On the Settings for EBSDataSource page, select Monitoring > Testing"
  echo "   e. Select oacore_server1"
  echo "   f. Click Test DataSource"
  echo "   g. Look for the message Test of EBSDataSource on server oacore_server1 was successful."
  echo 
  exit 1
fi

for parm in `echo $*`
do
  if [ $1 = '-appspass' ]
  then
    shift
    APPSPASS=$1
  elif [ $1 = '-syspass' ]
  then
    shift
    SYSPASS=$1
  elif [ $1 = '-newpass' ]
  then
    shift
    NEWPASS=$1
  fi 

  if [ $2 ]
  then
    shift
  fi
done

echo "changing non apps passwords..."
$FND_TOP/bin/FNDCPASS apps/${APPSPASS} 0 Y SYSTEM/${SYSPASS} ALLORACLE ${NEWPASS}

echo "changing apps password..."
$FND_TOP/bin/FNDCPASS apps/${APPSPASS} 0 Y SYSTEM/${SYSPASS} SYSTEM APPLSYS ${NEWPASS}
  echo "Remaining Steps:"
  echo "3) Start only the admin server with adadminsrvctl.sh"
  echo "4) Change the apps password in WLS Datasource" 
  echo "   a. Log in to WLS Administration Console"
  echo "   b. Click Lock & Edit in Change Center"
  echo "   c. In the Domain Structure tree, expand Services, then select Data Sources"
  echo "   d. On the Summary of JDBC Data Sources page, select EBSDataSource"
  echo "   e. On the Settings for EBSDataSource page, select the Connection Pool tab"
  echo "   f. Enter the new password in the Password field"
  echo "   g. Enter the new password in the Confirm Password field"
  echo "   h. Click Save"
  echo "   i. Click Activate Changes in Change Center"
  echo "5) Start everything with adstrtal.sh"
  echo "6) Check in the apps and WLS"
  echo "   a. Log in to WLS Administration Console"
  echo "   b. In the Domain Structure tree, expand Services, then select Data Sources"
  echo "   c. On the Summary of JDBC Data Sources page, select EBSDataSource"
  echo "   d. On the Settings for EBSDataSource page, select Monitoring > Testing"
  echo "   e. Select oacore_server1"
  echo "   f. Click Test DataSource"
  echo "   g. Look for the message Test of EBSDataSource on server oacore_server1 was successful."

exit

Create a free website or blog at WordPress.com.