You should upgrade your Weblogic version to at least 10.3.6.0.12 - It's simple

Upgrading your weblogic version can help you improve stability and performance on your weblogic instance and also help deal with leaked connections and other issues.  Also, as far as upgrades go, it really doesn't get much easier than upgrading your weblogic version if you stay within the same release.

For example, we just upgraded a demo instance from 10.3.0.6.4 to .12

In case you are wondering, 10.3.0.6.12 is the current minimum supported version of Weblogic for R12.2 EBS, and based on the amount of downloads for it and other things I've read, its considered a "favorite" and stable release for many people.

I would recommend by first downloading the latest ETCC patch and running the checkMTpatch.sh against your EBS.  This will give you a FULL report on what patches you should apply for all of your currently installed applications in the tech stack.

Ours recommended we upgrade to at least 10.3.0.6.12 and then rerun checkMTpatch.sh for a new set of patch recommendations afterwards.

We did ample research before upgrading and came across a note that was pretty helpful, stating that we'd most likely need to remove some patches before we could apply the new .12 version to upgrade, but I'll get to that in a minute.

Getting Conflict When Applying Weblogic 10.3.6 PSU Patch 20780171 (Doc ID 2048247.1)


We shut down all the weblogic servers and OHS, then removed the following patches below.  How'd we know which ones to remove - easy.  When you try to install the new .12 patch version it does a crosscheck of the incompatible patches you need to first remove before it can be applied.  

First things first:

Navigate to the $FMW_HOME/utils/bsu directory.

E-WL: Weblogic Smart Update (BSU) Hangs when Starting (Doc ID 2002949.1)

Modify memory arguments to 1GB inside bsu.sh.  The patch will fail with a java error if you do not!

MEM_ARGS="-Xms1024m -Xmx1024m"


=====================

Next:

Download and unzip the  20780171 - 10.3.6.0.12 patch in the recommended directory:

$FMW_HOME/utils/bsu/cache_dir

When you unzip the patch it comes out as a Alpha-Numeric jar file. 

Example: unzip p20780171_1036_Generic.zip
extracting: EJUW.jar

You'll notice you will use this 4 character name (EJUW) to apply the patch, minus the dot jar at the end, as seen below:

 20780171 - 10.3.6.0.12 patch

bsu.sh -install -patch_download_dir=$FMW_HOME/utils/bsu/cache_dir -patchlist=EJUW -prod_dir=$FMW_HOME/wlserver_10.3/

It came back with the list of incompatible patches:

D33T,LL4G,VFS8,Y5AP, and YIJF 

You may have to play with the order of removing as we tried all of them at first and got another dependency so that's why we pulled a particular one out and did it first.

>cd $FMW_HOME/utils/bsu/

bsu.sh -remove -patchlist=YIJF -prod_dir=$FMW_HOME/wlserver_10.3/

bsu.sh -remove -patchlist=D33T,LL4G,VFS8,Y5AP -prod_dir=$FMW_HOME/wlserver_10.3/

 20780171 - 10.3.6.0.12 patch

bsu.sh -install -patch_download_dir=$FMW_HOME/utils/bsu/cache_dir -patchlist=EJUW -prod_dir=$FMW_HOME/wlserver_10.3/

 13729611 - immediately applied after according to note above (Doc ID 2048247.1)

bsu.sh -install -patch_download_dir=$FMW_HOME/utils/bsu/cache_dir -patchlist=L34G -prod_dir=$FMW_HOME/wlserver_10.3/

Reran the checkMTpatch.sh script and it provided us with a new list of patches to apply:

 17319481, 19259028, 19687084, 20474010, 22128205, 22323006

bsu.sh -install -patch_download_dir=$FMW_HOME/utils/bsu/cache_dir -patchlist=N5FK,TYHS,8FBW,S8H6,GK5N,DI8E -prod_dir=$FMW_HOME/wlserver_10.3/

After applying these you should go ahead and run the checkMTpatch.sh again one more time to assure that there are no remaining patches to apply, we got the message that we were all up to date on our version of WLS afterwards.

At this point you can go ahead and start up your Admin Server and then all of your WLS managed servers, and lastly OHS.

Also if you are curious as to an easy way to find out what version you are using:

>cd $FMW_HOME/user_projects/domains/EBS_domain_<SID>/bin

>. setDomainEnv.sh     --make sure there is a space between to source

>java weblogic.version

WebLogic Server Temporary Patch for BUG22128205 Fri Nov 13 16:02:12 IST 2015
WebLogic Server Temporary Patch for BUG20474010 Sun Mar 01 17:22:18 IST 2015
WebLogic Server Temporary Patch for BUG19687084 Mon Jun 29 15:01:13 IST 2015
WebLogic Server Temporary Patch for BUG19259028 Wed Aug 19 14:57:09 IST 2015
WebLogic Server Temporary Patch for BUG17319481 Wed Jul 01 23:52:43 MDT 2015
WebLogic Server Temporary Patch for BUG13729611 Fri Jun 26 09:54:52 MDT 2015
WebLogic Server 10.3.6.0.12 PSU Patch for BUG20780171 THU JUN 18 15:54:42 IST 2015
WebLogic Server Temporary Patch for BUG17495356 Tue Sep 24 00:23:38 CDT 2013
WebLogic Server Temporary Patch for BUG17495356 Tue Sep 24 00:23:38 CDT 2013
WebLogic Server Temporary Patch for BUG17495356 Tue Sep 24 00:23:38 CDT 2013
WebLogic Server Temporary Patch for BUG17495356 Tue Sep 24 00:23:38 CDT 2013
WebLogic Server Temporary Patch for BUG17495356 Tue Sep 24 00:23:38 CDT 2013
WebLogic Server Temporary Patch for ${CRS} Mon Jul 30 16:45:20 EDT 2012
WebLogic Server Temporary Patch for ${CRS} Mon Jul 30 16:45:20 EDT 2012
WebLogic Server Temporary Patch for BUG13845626 Tue Mar 20 17:35:12 IST 2012
WebLogic Server 10.3.6.0  Tue Nov 15 08:52:36 PST 2011 1441050


Cheers!  If anyone has any additional questions please let me know.

Create idle MWA session logging table for sessions to be terminated

This script is intended to be used with the Post:

Automatically Terminate old MWA sessions using PL/SQL and DBMS scheduler


CREATE TABLE XXX.XXX_KILLED_MWA_USER_SESSIONS
(
  SCHEMANAME    VARCHAR2(30 BYTE),
  INST_ID       NUMBER,
  SID           NUMBER,
  SERIAL#       NUMBER,
  LOGON_TIME    DATE,
  LAST_CALL_ET  NUMBER,
  MODULE        VARCHAR2(64 BYTE),
  ACTION        VARCHAR2(64 BYTE),
  KILLED_TIME   DATE,
  SQL_ID        VARCHAR2(64 BYTE)
)

TABLESPACE XXX
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;


GRANT SELECT ON XXX.XXX_KILLED_MWA_USER_SESSIONS TO APPS;

MWA PL/SQL Procedure to kill idle sessions longer than 12 hours

--This script is to be referenced by the Post:

Automatically Terminate old MWA sessions using PL/SQL and DBMS scheduler

**Note: Special thanks to Justin Hartman.  He made this procedure for other issues, but let me use and alter it for our MWA purposes.


CREATE OR REPLACE PROCEDURE XXX.XXX_MWA_STALE_SESSION_KILL
AS
   V_DB       VARCHAR2 (10);
   V_SQLERR   VARCHAR2 (255);
BEGIN
   SELECT NAME INTO V_DB FROM V$DATABASE;

   DECLARE
      CURSOR GET_MWA_SESSIONS_CUR
      IS
           SELECT A.SCHEMANAME,
                  A.INST_ID,
                  A.SID,
                  A.SERIAL#,
                  A.LOGON_TIME,
                  ROUND (A.last_call_et / 3600) "HOURS_INACTIVE",
                  A.MODULE,
                  LTRIM (A.ACTION, 'IDLE_LAST') "ACTION",
                  A.SQL_ID,
                     'ALTER SYSTEM DISCONNECT SESSION '
                  || ''''
                  || A.SID
                  || ', '
                  || A.SERIAL#
                  || ',@'
                  || A.INST_ID
                  || ''''
                  || ' IMMEDIATE'
                     KILL
             FROM GV$SESSION A
            WHERE     status = 'INACTIVE'
                  AND module = 'MWAJDBC'
                  AND action NOT LIKE '%GENERIC%'
                  AND last_call_et > (3600 * 12) ----INACTIVE FOR GREATER THAN 24 HOURS
         ORDER BY last_call_et DESC;
   BEGIN
      FOR X IN GET_MWA_SESSIONS_CUR
      LOOP
         UTL_MAIL.SEND (
            SENDER       => 'ABC@XYZ.com',
            RECIPIENTS   => 'ABC@XYZ.com',
            SUBJECT      => 'Stale MWA session was killed on ' || V_DB,
            MESSAGE      =>    'The Following MWA session was killed due to being idle longer than 24 hours:'
                            || CHR (13)
                            || CHR (13)
                            || CHR (10)
                            || CHR (13)
                            || CHR (10)
                            || 'Logon Time:     '
                            || TO_CHAR (X.LOGON_TIME,
                                        'DD-MON-YYYY HH24:MI:SS')
                            || CHR (13)
                            || CHR (10)
                            || 'Module:           '
                            || X.MODULE
                            || CHR (13)
                            || CHR (10)
                            || 'REPORT:         '
                            || X.ACTION
                            || CHR (13)
                            || CHR (10)
                            || 'SQL ID:           '
                            || X.SQL_ID);


         DBMS_OUTPUT.PUT_LINE ('EMAIL SENT');
      END LOOP;

      BEGIN
         FOR I IN GET_MWA_SESSIONS_CUR
         LOOP
            INSERT INTO XXX.XXX_KILLED_MWA_USER_SESSIONS
                 VALUES (I.SCHEMANAME,
                         I.INST_ID,
                         I.SID,
                         I.SERIAL#,
                         I.LOGON_TIME,
                         I.HOURS_INACTIVE,
                         I.MODULE,
                         I.ACTION,
                         SYSDATE,
                         I.SQL_ID);

            COMMIT;

            EXECUTE IMMEDIATE I.KILL;

            DBMS_OUTPUT.put_line ('# of rows inserted: ' || SQL%ROWCOUNT);
         END LOOP;
      END;
   END;
EXCEPTION
   WHEN OTHERS
   THEN
      V_SQLERR := SQLERRM;

      BEGIN
         UTL_MAIL.SEND (SENDER       => 'ABC@XYZ.com',
                        RECIPIENTS   => 'ABC@XYZ.com',
                        SUBJECT      => 'MWA_USER check error ' || V_DB,
                        MESSAGE      => V_SQLERR);
      EXCEPTION
         WHEN OTHERS
         THEN
            RAISE;
      END;
END XXX_MWA_STALE_SESSION_KILL;
/

SHOW ERRORS;

Automatically Terminate old MWA sessions using PL/SQL and DBMS scheduler

We've been experiencing MWA servers freezing and not allowing new connections and causing anyone else connected to the particular server in question to get locked up.

I was doing some research on Oracle support, and found a note related to MWA troubleshooting for R12.

Doc ID 782162.1

What I found was that Oracle recommends that you terminate old idle sessions that have been connected for longer than 12 hours.  They actually provide you with a simple sql statement that dynamically generates the kill statements for those database sessions.

We've taken it a step further here in the sense that - our team altered the script a little bit for some more information, turned it into a PL/SQL procedure, logged the information to a custom table, and then scheduled a DBMS job to run the procedure hourly to terminate the idle sessions.  Lastly with the procedure, it will also notify you of the sessions it terminated via UTL_MAIL.

This may sound like it takes a lot of work, but its very simple.  Let's walk through our setup quickly, and then what all you will need to do to make it work for yourself.

We are currently using:

-R12.2.4
-a load balancer to round robin the connections, instead of the mwa dispatcher
-shared appl_top, with 3 application nodes
-2 to 4 MWA servers per application node


The first thing we did was alter the sql to our liking so that it provided the information we wanted to log as well as provide the dynamic kill script to run.  We wanted to log this information for reporting purposes and how patches or changes in configuration might change the behavior/performance of the MWA servers themselves.

Here is the script to create a logging table (replace XXX with your custom schema with DBA privs):

Create logging table script


After you create the backup table, go ahead and create the PL/SQL procedure that you can find here:

PL/SQL procedure

***Note this has calls in the procedure to the logging table, if you do not wish to log these, you can remove this and not create the logging table.



You can then schedule this to run as a job via OEM, at least that is how we did this.

Log into OEM, and navigate to your database or clustered database.  Go to the Administration drop down and Oracle Scheduler, then Jobs. Create a new job.

Name the job with something other than the name of your procedure, you can just add _JOB or whatever you want to name it.  Put in the schema you developed the procedure in, and fill out the description of the what the job is doing, such as "Terminating Idle MWA sessions."

In the PL/SQL block, all you will need to do is replace the "-- Insert PL/SQL code here" with the name of your PL/SQL procedure. 

Example:

begin

 XXX.MWA_STALE_SESSION_KILL --replace XXX with your schema, that's it!

end;

On the schedule tab, and options, you can choose to your liking.

After this is setup, schedule it to run and then check your logging table, if you chose to log it.  Please do this in a DEVELOPMENT environment first! After you have successfully tested it, you now have a working scheduled procedure (you could also use cron for this if you want) that will terminate MWA sessions that have been IDLE for longer than 12 hours.  Good luck and be careful!

Until next time!

-Lance

Installing REST services in Oracle R12.2.4 EBS

This week I have been playing around with getting REST services up and available to use for our developers.  We are soon going to be refreshing our development environment, so I figured this may be the perfect time to give them a week or so to go out and play around with REST services and see if they can work as business solution.

I will start by giving the some pertinent information to this install:

We are currently on or using:

R12.2.4 E-Business Suite
AD/TXK Delta 6 
wlserver_10.3


NOTE: To install REST, I was following the Oracle note ID: 1311068.1 , Installing Oracle E-Business Suite Integrated SOA Gateway, Release 12.2


Now, a couple things here before I walk you though what I did to get it working.  I want to be clear that I was following the portion of the above mentioned note to implement REST services only, as per our business requirements.  The REST solution has minimal requirements as opposed to implementing SOAP services, as they require a whole new architecture to get it to work, REST does not.  This document is lengthy, do not let it discourage you!  The REST portion is minimal here.

With that said, on with what we did!

STEPS: 

There are quite a few requirements to figure out what all you will need to have in place before you can implement REST services.  Please read the note in its entirety, but the information below is what I believe to be the most important.

The two MAIN requirements are to have EBS patch 22328483:R12.OWF.C, and Oracle Fusion Middleware patch 17495356 for Weblogic Server.

You can check for these patches in your environment by doing the following in your desired destination instance:



For the regular EBS patch that would be applied with ADOP method:

select * from apps.ad_bugs where bug_number in '22328483';
--or
select * from apps.ad_applied_patches where patch_name ='22328483';

We did not have this patch, and needed to install it.




For the FMW Weblogic patch, it is a bit more complicated:

On your application server go to your $FMW_HOME/utils/bsu/

./bsu.sh -prod_dir=$FMW_Home/wlserver_10.3 -status=applied -verbose -view > applied_patches.txt

This will create an output file you can search in for patch number 17495356.  For us, we already had this patch applied.

Alternatively you can also search for this patch in your AdminServer.log file located here:

$FMW_Home/user_projects/domains/EBS_domain_XXXX/servers/AdminServer/logs/AdminServer.log

Each time the server starts it will display a list of patches that it uses, and the patch in question was also in here for us.

Example line in AdminServer.log file:
<WebLogic Server "AdminServer" version:
WebLogic Server Temporary Patch for BUG17495356 Tue Sep 24 00:23:38 CDT 2013


You must apply the patches first if you do not have them. This next section will take us to the actual steps performed after the patches were applied.

INSTALL / SETUPS:

 1. Enable the ASADMIN user and give it the Integration Administrator role.  

This is pretty straight forward in the note, but this is what I did:

a. Login as SYSADMIN user
b. Go to User Management responsibility, drill into Users, and then User Maintenance
c. Pull up ASADMIN user, click Update, remove the "Active To" date field if there is one
d. Reset the password, in the same screen, go to Assign Roles.
e. Select search by "Code" in the drop down and use "UMX|FND_IREP_ADMIN" and select.
f. Apply it and now sysadmin has the role assigned.



2. Create a Temporary folder on your EBS that has write permissions. We created a folder here called "soa"

We created it here on the application node $NE_BASE/inst/SID/soa

You'll need to update some files with this new location here $INST_TOP/soa.

Update the isgagent.properties file, specifically the ISG_TEMP_LOCATION parameter with the folder you created above. NOTE: Make a backup of these files, and then modify on each application node you have, if you have more than one



3. The next step to run the actual txkISGConfigurator.xml utility that is supposed to stop and restart the weblogic servers:

ant -f $JAVA_TOP/oracle/apps/fnd/txk/util/txkISGConfigurator.xml ebsSetup -DforceStop=yes

Now, I could not for the life of me get this script to run, it would fail every time it tried to force stop weblogic, so I ended up stopping all my weblogic servers and AdminServer and removing the -DforceStop=yes, and this still did not work correctly.

Hmm, I couldn't figure out what was going on here initially.  After some searching I found an oracle community thread somewhere that mentioned for me to re-source the EBSapps.env file - located under /appl_top/%inst%/, AND to source your setDomainEnv.sh file located: $FMW_HOME/user_projects/domains/EBS_domain_$TWO_TASK/bin/setDomainEnv.sh

Make sure you use ". ./setDomainEnv.sh" since this is cosidered an environment file.

This got us past our Weblogic issue we were having, although I still left off the -DforceStop from my command as I wanted to make sure that wasn't the issue.


Something else interesting to note - In the documentation Oracle provides in this note, they say you should get prompted for APPS user/pw, ASADMIN user/pw.  I got prompted for APPS and WEBLOGIC, but the install went in fine!  We'll come back to this part later.


After your script finishes, you should get something similar to:

     [echo]  Mandatory Step : Restart all the Weblogic Servers NOW !!!

ebsSetup:

BUILD SUCCESSFUL
Total time: 3 minutes 7 seconds

As you can see it does not take very long to complete.

VERIFICATION and Troubleshooting:

First Verfication

Restart your Weblogic servers if you've left them down as I did.

Go into your Weblogic console and under your Domain Structure, expand your Environment and go into your Clusters.

After your clusters show up, go into oafm_cluster1, and go to the Services tab at the top.  You should now see your OAEADatasource.



Second Verification: (Deploy an actual REST Web Service)

a. Log into SYSADMIN, navigate to Integrated SOA Gateway responsibility, and drill down into Integration Repository, and hit Search on the Right Pane
b. Click the link "Show More Search Options"
c. Fill in Category with Interface Subtype, and Category Value with Java Bean Services, click Go
d. Click Rest Service Locator
e. If you wish you can click Grants tab to change some grants around to the service you are about to create, but it is just a test, so you can go directly to the REST Web Services tab if you want.
f. Fill our the mandatory Service Alias field with whatever you want to name it, I named it Test_Rest, and click Deploy at the bottom.

You are supposed to get a message that your service deployed fine, and I wish I could say that mine did, but it did not.  I ended up getting the message:

oracle.apps.fnd.framework.OAException: Password for user:ASADMIN is not configured.

DOH!  I was almost there. I went back and tried changing the password once again but that did not help.  I searched online and found very little, but did find some people having the exact same issue I did.  I saved opening an SR for last, as no one really wants to do that.  I bit the bullet, and opened an SR under the product Oracle E-Business Suite Integrated SOA Gateway.

I basically told them I followed the documentation to the T and what my issue I was having was.  Oracle came back and told me that if I had properly followed all documentation, then I could try using the below command to add ASADMIN to the FNDVault:

****************************************

ant -f $JAVA_TOP/oracle/apps/fnd/txk/util/txkSoaConfigUtility.xml addASADMINToFNDVault 

****************************************


This utility will ask for the APPS password, and the ASADMIN user/password.  I ran it and entered the password as I had set it.

I went back to the second validation section (steps a-f just above), and was able to successfully deploy my first REST Web Service!  You can Undeploy the service just as easy.  After it is deployed you can select it and hit the Undeploy button.




I suggest that you run txkSoaConfigUtility.xml utility if you :


1. Run the txkISGConfigurator.xml, and if it does not ask you for the ASADMIN credentials, run it after the setup.

2. You could wait until you try to deploy the service and get the same message as I did, or

3. You could run the utility directly after enabling and setting up the ASADMIN user to make sure the password gets accepted into the vault (make sure you login as ASADMIN first and change the password if you have it setup where it forces you to change your password upon logging into the EBS!)


As a side note, oracle did come back on the SR after some conversation and say that this document is configured with the latest AD/TXK Delta, which is update 7, while we are on Delta 6.


Cheers and good luck, I hope this helps someone else out there! Until next time. . . .

Lance