Due to busy scheduled I could not post any update yesterday. Last year we implemented oracle MWA (Mobile) in our company. Oracle mobile application is being used in warehouse to keep track of goods. Following are steps I performed as oracle applications DBA.
Apply MWA patches
- Stop Oracle Applications Services (Apache, Forms, Concurrent Manager, Report Server).
[orajai1@jai0301e] /home/orajai1
Source Oracle apps environment
$ . $APPL_TOP/$CONTEXT_NAME.env
$cd $COMMON_TOP/admin/scripts/$CONTEXT_NAME
$ adstpall.sh stop apps/<password> (Only on concurrent manager node)
-
Take name and number of invalid objects belongs to apps, mwa, inv schema.
SQL> SELECT COUNT(1) FROM DBA_OBJECTS WHERE status='INVALID' AND OWNER='APPS'
Export Invalid objects name in excel sheet, to compare invalid objects after apply these patches in case of any issue.
SELECT object_name, object_type, owner, status FROM dba_objects WHERE status = 'INVALID' AND owner = 'APPS' ORDER BY 1, 2, 3
- Take backup of following directories on all applications tier (web/form as well concurrent manager).
$cp $FND_TOP/secure $FND_TOP/secure.<mmddyyyy>
$cp $TNS_ADMIN/$CONTEXT_NAME $TNS_ADMIN/$CONTEXT_NAME.<mmddyyyy>
$cp $IAS_ORACLE_HOME/network/admin/$CONTEXT_NAME $IAS_ORACLE_HOME/network/admin/$CONTEXT_NAME.<mmddyyyy>
- Apply following MWA Patches using /staging/scripts/ikn_autopatch.sh script.
Noteà
Sometime file version would be updated after running autoconfig. Autoconfig you need to run at end of installation of all patches. You need to make sure about file version using adident after running autoconfig.
# 4260471 Mobile Applications Telnet Screen is rendere
Duration : 30 Min
Check file version after patch installation
$ adident Header $MWA_TOP/bin/MWADIS
/opt01/app/jai1appl/mwa/11.5.0/bin/MWADIS:
$Header mwadis.oc 115.158.1159.5 2005/03/28 18:49:15 sthamman noship $
$Header mwacfg.lc 115.24.1159.2 2004/12/31 10:38:20 sthamman noship
$ adident Header $MWA_TOP/admin/template/mwactl.sh
/opt01/app/jai1appl/mwa/11.5.0/admin/template/mwactl.sh:
$Header mwactl.sh 115.53.1159.3 2004/11/17 10:03:19 sthamman ship
# 4577556 Mandatory Mobile Patch for 1159 Customer
Duration: 30
Check file version after patch installation
$cd $COMMON_TOP/java/oracle/apps/mwa/presentation/telnet
$ adident Header Options.class
Options.class:
$Header Options.java 115.17.1159.9 2005/09/30 04:12:05 ajohnson ship $
$ adident Header ProtocolHandler.class
ProtocolHandler.class:
$Header ProtocolHandler.java 115.133.1159.8 2005/05/18 13:32:12 ajohnson ship
# 4630366 Mandatory Mobile Patch for 1159 Customer
Duration 30 Min
$cd $COMMON_TOP/java/oracle/apps/mwa/presentation/telnet
$ adident Header Options.class
Options.class:
$Header Options.java 115.17.1159.9 2005/09/30 04:12:05 ajohnson ship
# 3869946 MOBILE PAGES FAILED TO OPEN
Duration 60 min
Check file version
$ adident Header $WMS_TOP/patch/115/import/US/wmsmenu.ldt
/opt01/app/jai1appl/wms/11.5.0/patch/115/import/US/wmsmenu.ldt:
$Header wmsmenu.ldt 115.29.11590.5 2004/09/16 19:13:01 htnguyen ship $
$ adident Header $FND_TOP/patch/115/sql/AFFMFUNB.pls
/opt01/app/jai1appl/fnd/11.5.0/patch/115/sql/AFFMFUNB.pls:
$Header AFFMFUNB.pls 115.36 2005/04/11 13:50:03 skghosh ship $
# 4345239 STOCK LOCATOR ISSUE IN THE SUB TRANSFER SCREEN
Duration 60 min
Check file version
$ adident Header $INV_TOP/patch/115/sql/INVITPSB.pls
/opt01/app/jai1appl/inv/11.5.0/patch/115/sql/INVITPSB.pls:
$Header INVITPSB.pls 115.108.11590.8 2005/05/27 16:07:52 mchemban ship $
$ adident Header $INV_TOP/patch/115/sql/INVLKFFB.pls
/opt01/app/jai1appl/inv/11.5.0/patch/115/sql/INVLKFFB.pls:
$Header INVLKFFB.pls 115.1.11590.3 2005/05/27 15:57:27 mchemban ship $
$ adident Header $INV_TOP/patch/115/sql/INVLKFFS.pls
/opt01/app/jai1appl/inv/11.5.0/patch/115/sql/INVLKFFS.pls:
$Header INVLKFFS.pls 115.1.11590.2 2005/05/27 15:46:14 mchemban ship $
$ adident Header $INV_TOP/patch/115/import/US/i4345239.ldt
/opt01/app/jai1appl/inv/11.5.0/patch/115/import/US/i4345239.ldt:
$Header i4345239.ldt 115.0 2005/05/27 15:33:22 mchemban noship $
- Compile all invalid objects and check if there is any new invalid objects.
- Check invalid object of INV and MWA schema and compile them manually if it is taking long
- We need to check MWA related configuration in auto context file on each middle tier , concurrent manager tier before running autoconfig.
-
Make following changes in applications context file on each middle and concurrent manager tier and run autoconfig.
<oa_mwa_server>
<hostname oa_var="s_mwahost">jai0301e</hostname>
<domain oa_var="s_mwadomain">orbit.com</domain>
<mwaLogLevel oa_var="s_mwaLogLevel">error</mwaLogLevel>
<mwaLogRotate oa_var="s_mwaLogRotate">Yes</mwaLogRotate>
<mwaLogFileSize oa_var="s_mwaLogFileSize">10000000</mwaLogFileSize>
<mwaDropConnectionTimeoutoa_var="s_mwaDropConnectionTimeout">5</mwaDropConnectionTimeout>
<mwaStaleSessionTimeoutoa_var="s_mwaStaleSessionTimeout">60</mwaStaleSessionTimeout>
<mwaDispatcherThreadCountoa_var="s_mwaDispatcherThreadCount">15</mwaDispatcherThreadCount>
<mwaDispatcherClientsPerWorkeroa_var="s_mwaDispatcherClientsPerWorker">10</mwaDispatcherClientsPerWorker>
<mwaJVMb oa_var="s_mwaJVMb">FALSE</mwaJVMb>
<mwaPortNo oa_var="s_mwaPortNo" oa_type="PORT">10210</mwaPortNo>
<mwaTelnetPortNo oa_var="s_mwaTelnetPortNo" oa_type="PORT">10200</mwaTelnetPortNo>
<mwaDispatcherPort oa_var="s_mwaDispatcherPort"oa_type="PORT">10310</mwaDispatcherPort>
<mwaActivateLOVByEnter oa_var="s_mwaActivateLOVByEnter">FALSE</mwaActivateLOVByEnter>
<mwaSubmenuChangeOrgResp oa_var="s_mwaSubmenuChangeOrgResp">FALSE</mwaSubmenuChangeOrgResp>
</oa_mwa_server>
Check value for following tag in the application context file $APPL_TOP/admin/<SID>_<HOSTNAME>.xml. Value for these port would be decided for production. We'll use last two digit of mwaPortNo same as last two digit of Apache Port.
e.g.
if Production port is 8010 then mwaPortNo would be 10210 and Dispatcher port 10310
<mwaPortNo oa_var="s_mwaPortNo" oa_type="PORT">10210</mwaPortNo>
mwa.Dispatcher=%s_hostname%.%s_domainname%:%s_mwaDispatcherPort%
mwa.TelnetServer=%s_hostname%.%s_domainname%:%s_mwaPortNo%
Make sure Dispatcher and Telnet Server hostname is as per production requirement.
- Run autoconfig on all web/concurrent manager tier.
$cd $SCRIPTS
$ pwd
/opt01/app/oracle/jai1comn/admin/scripts/JAI1_jai0301e
$adautocfg.sh
Enter apps password : *****
Check autoconfig log on each tier (middle tier, concurrent manager tier).
- Run /staging/scripts/ikn_post_autoconf.sh on each node.
Run ikn_post_autoconf.sh on both jai00108e and jai00208e.
$cd /staging/scripts
./ikn_post_autoconf.sh ERMR_jai0108e ERMR ERMR1PPSSYS eierm
- Copy back the listener.ora and tnsnames.ora files from backup in 806 home and IAS home.
-
Check the DBC files in $FND_TOP/secure and add given entry if not there.
BATCH_VALIDATE_USER=GUEST
BATCH_VALIDATE_PWD=ORACLE
Make sure DBC file exists.
-rwxr-xr-x 1 orajai1 dba 806 Feb 08 11:00 jai001_jai1.dbc
- Start applications services including concurrent manager.
- Email to Functional team to run given below "Concurrent Manager", which is part of post installation step of patch # 4345239 "Generate Stock Locator Flexfield definition for Mobile transactions"
- Compile invalid objects if there was any due to above concurrent program.
- Validate applications services
Configure MWA
We are going to use only MWA server for time being. Ensure mwa related
setting are part of configuration file. Please perform as given below.
- Make given changes in $MWA_TOP/secure/mwa.cfg file.
mwa.logdir= /log01/JAI1/mwa
Rest of given below mwa related settings are part of autocontext file. Please cross check values for given variables in the $MWA_TOP/secure/mwa.cfg file
File : $MWA_TOP/secure/mwa.cfg
Variable Values:
mwa.DbcFolder=
/opt01/app/jai1appl/fnd/11.5.0/secure/
mwa.DbcFile=
jai0301e_jai11
mwa.logdir= /log01/JAI1/mwa
mwa.TelnetPortNumber=10210
mwa.DropConnectionTimeout=5
mwa.StaleSessionTimeout=60
mwa.SystemLog=system.log
mwa.LogLevel=trace
Check Dispatcher setting
Dispatcher related setting variable's values are already part of autocontext file. Please check values for following dispatcher related variables in the $MWA_TOP/secure/mwa.cfg file.
[orajai1@jai0301e] /opt01/app/jai1appl/mwa/11.5.0/secure
$ grep -i Dispatcher mwa.cfg grep -v "#"
mwa.Dispatcher=jai0301e.orbit.com:10310
mwa.DispatcherWorkerThreadCount=15
mwa.DispatcherClientsPerWorker=10
Change Default_key Files
Please modify $MWA_TOP/default_key.ini file in JAI1 instance with following updates
The changes required to be done are in $MWA_TOP/default_key.ini
$ cp $MWA_TOP/default_key.ini $MWA_TOP/default_key.ini.mmddyyyy
1) Change DEFAULT_WIDTH = 27
2) Change DEFAULT_HEIGHT = 18
3) Change PROMPT_RATIO = 1:2
Performance Related Changes.
Execute following SQL using APPS schema
SQL> CREATE INDEX po.idx_rcv_tran_interface_prf1 ON po.rcv_transactions_interface
(shipment_header_id,transaction_status_code)
TABLESPACE pox COMPUTE STATISTICS ;
SQL> CREATE INDEX inv.idx_mtl_onhand_quan_det_prf1
ON inv.mtl_onhand_quantities_detail(lpn_id,organization_id)
TABLESPACE invx COMPUTE STATISTICS;
SQL> analyze table inv.MTL_SECONDARY_INVENTORIES delete statistics
SQL> CREATE INDEX wsh.idx_wsh_delivery_details_prf1 ON wsh.wsh_delivery_details
(source_header_number,released_status)
TABLESPACE wshx COMPUTE STATISTICS;
SQL> create index PO.idx_rcv_ship_headers_PRF1 ON PO.rcv_shipment_headers(EXPECTED_RECEIPT_DATE)
TABLESPACE POX COMPUTE STATISTICS;
Schedule Gather Schema Statistic for MWA
Login to Oracle Applications and select "SYSTEM ADMINISTRATOR" and Submit gather statistics
MWA Services Alert/Start/Stop Scripts
/home/orajai1/start_mwa
/home/orajai1/stop_mwa
/home/orajai1/stat_mwa
Start MWA Server
Ensure script mwa_services has been deployed in $HOME to start services.
Start mwa services using script
$. ./start_mwa
Validate MWA Configuration
After copying scripts run /home/orajai1/start_mwa script to start MWA services. Allocate following responsibility to any user and perform given steps to validate configurations.
"Materials & Mfg"
Start à Run à telnet jai0301e.orbit.com 10210
Noteà Only "Oracle Mobile Applications" repsonsbility would be visible in WMA telnet session.
Appendix
Start MWA services
$ cat start_mwa.sh
. $APPL_TOP/$CONTEXT_NAME.env
export WMATELPORT=10250 # WMA Tel Net Server Port
export WMATELPORT2=10260 # WMA Tel Net Server Port
ulimit -n 1024
echo "Starting the MWA Telnet Server ..."
cd $MWA_TOP/bin
nohup mwactl.sh -java_config "-mx128m -ms64m -Dmwa.cfg=$MWA_TOP/secure/$CONTEXT_NAME/mwa.cfg" start $WMATELPORT &
sleep 30
nohup mwactl.sh -java_config "-mx128m -ms64m -Dmwa.cfg=$MWA_TOP/secure/$CONTEXT_NAME/mwa.cfg" start $WMATELPORT2 &
sleep 30
if [ `ps -ef grep mwa grep -v grep wc -l` -eq 2 ]
then
echo "MWA Telnet Server is up now "
else
echo "WMA Telnet Server Did not start"
fi
cd
Stop MWA services
# You need instance sysadmin password to stop MWA server
. $APPL_TOP/$CONTEXT_NAME.env
export WMATELPORT=10250
export WMATELPORT2=10260
echo "Stoping the MWA Telnet Server ..."
cd $MWA_TOP/bin
mwactl.sh -java_config "-Dmwa.cfg=$MWA_TOP/secure/$CONTEXT_NAME/mwa.cfg" -mwatop $MWA_TOP -login SYSADMIN/<password> stop_force $WMATELPORT &
mwactl.sh -java_config "-Dmwa.cfg=$MWA_TOP/secure/$CONTEXT_NAME/mwa.cfg" -mwatop $MWA_TOP -login SYSADMIN/gone2acpt stop_force $WMATELPORT2 &
for i in `ps -ef grep mwa grep -v grep grep -v _mwa awk '{ print $2 }'`
do
kill -9 $i
done
echo "MWA Server and dispatcher is down ...."
cd
MWA Services availability Check
$ cat stat_mwa
# Description:This script will check mwa services and send notify DBAs if it was down.
# Author: Janmejai Singh
#
# History
##############################################
#! /bin/ksh
maillist="jsingh@orbit.com"
set -x
if [ -f /tmp/run_it.txt ]
then
exit
fi
if [ `ps -ef grep -i mwa grep -v grep grep -v stat_mwa wc -l` -ne 2 ]
then
echo "MWA Server is down on `hostname` ." mailx -s "Please start MWA Services using $HOME/start_mwa.sh script ..." $maillist
#echo "Please start MWA Services using `hostname` : $HOME/start_mwa script ..." mailx -s "MWA Server is down on `hostname` ." $maillist
fi
