Saturday, April 23, 2016

Manually deploy EAR file

After a patch (such as JE patch 21950151) is applied or adadmin generates new JAR (Java Archive) file, the apps startup script adformsctl.sh may deploy forms EAR file. You may see a Warning (with status 0) in log $LOG_HOME/appl/admin/log/adstrtal.log.

Starting OPMN managed FORMS OC4J instance  ...
Calling txkChkFormsDeployment.pl to check whether latest FORMSAPP.EAR is deployed...
Program : $FND_TOP/patch/115/bin/txkChkFormsDeployment.pl started @ Mon Apr 18 1
6:58:03 2016
*** Log File = $LOG_HOME/appl/rgf/TXK/txkChkFormsDeployment_Mon_Apr_18_16_58_02_2016/txkChkFormsDeployment_Mon_Apr_18_16_58_02_2016.log

File "$IAS_ORACLE_HOME/j2ee/forms/applications/forms/formsweb/WEB-INF/lib/frmsrv.jar" exists. Proceeding to check the size...
=================================================
*** Latest formsapp.ear has NOT been deployed ***
Deploying the latest EAR file...
=================================================
Program : $FND_TOP/patch/115/bin/txkChkFormsDeployment.pl completed @ Mon Apr 18 16:59:04 2016
*****************************************
*             W A R N I N G             *
*****************************************
Error while executing the perl script txkChkFormsDeployment.pl

We have determined that you need to redeploy Forms (using txkChkFormsDeployment.pl).
But could not do it automatically for you, due to some issues.
For details refer the log files.
Follow Note: 397174.1 to redeploy Forms manually.
*****************************************
adformsctl.sh: exiting with status 0

I followed steps in  Doc ID 397174.1 to get over this. Without this steps, some forms may not open up. 
1)  back up file $INST_TOP/ora/10.1.3/j2ee/forms/config/system-jazn-data.xml (for deploying the formsapp.ear file). Note this file's timestamp always gets updated.
2)  when the oc4jadmin Password is unknown, search keyword oc4jadmin for pattern in file system-jazn-data.xml , and replace line
  <description>OC4J Administrator</description>
  <credentials>{903}9VrhYTuhd7DyBJf7J/4KwbSEIlETQJOO</credentials>
with
  <description>OC4J Administrator</description>
  <credentials>!welcome123</credentials>

Optional: after above change and bounce apps services, below line can verify the password is correct :
$ $AF_JRE_TOP/bin/java -jar $IAS_ORACLE_HOME/j2ee/home/admin_client.jar deployer:oc4j:opmn://hostname.domain.com:6530/forms oc4jadmin welcome123 -validateURI
URI deployer:oc4j:opmn://hostname.domain.com:6530/forms is valid and connected
-- where to get the port number
$ grep 6530 $ORA_CONFIG_HOME/10.1.3/opmn/conf/opmn.xml
<port local="6130" remote="6230" request="6530"/>
-- one more validation
$ cd $ORA_CONFIG_HOME/10.1.3/opmn/bin
$ ./opmnctl validate
opmnctl: opmn validation succeeded.

3)  stop apps services, and run the Perl script on each Forms tier to deploy the EAR file
$ $FND_TOP/bin/txkrun.pl -script=CfgOC4JApp
*** ALL THE FOLLOWING FILES ARE REQUIRED FOR RESOLVING RUNTIME ERRORS
*** Log File = $LOG_HOME/appl/rgf/TXK/txkCfgOC4JApp_Mon_Apr_18_17_17_52_2016.log
Program : $FND_TOP/patch/115/bin/txkCfgOC4JApp.pl started @ Mon Apr 18 17:17:52 2016

 *** Log File = $LOG_HOME/appl/rgf/TXK/txkCfgOC4JApp_Mon_Apr_18_17_17_52_2016.log

Enter Application name for re-deployment ? forms
Enter Oc4j Instance  password for re-deployment ? welcome123
Run Autoconfig <Yes/No> ? No

*****************************************************
Required values for starting OC4J instance "forms":
====================================================
s_formsstatus = enabled
s_forms_nprocs = 1 (value should be greater than 0)
Existing values from the context file:
======================================
s_formsstatus = enabled
s_forms_nprocs = 2
----------------------------------------------
*** Values for context variables are VALID ***
----------------------------------------------
*****************************************************
Stopping all OPMN processes.
OPMN stopped.
OPMN started.
Deplolying Application : "forms" onto OC4J instance: "forms"
Application deployed successfully.
Stopping and starting OC4J instances.
Started OC4J instances.
Binding webApp : "forms" with webmodule : "formsweb" for OC4J instance: "forms"
Web application bound successfully.
Stopping OPMN.
OPMN stopped.

Program : $FND_TOP/patch/115/bin/txkCfgOC4JApp.pl completed @ Mon Apr 18 17:42:21 2016
End of $FND_TOP/patch/115/bin/txkCfgOC4JApp.pl : No Errors encountered 

4)  restore the original system-jazn-data.xml file.
5)  run AutoConfig on each Forms server. (Without this step, the forms may not pop up after click due to failure in back-end authentication.)
$ADMIN_SCRIPTS_HOME/adautocfg.sh

Now, you shall see new timestamp on $IAS_ORACLE_HOME/j2ee/forms/applications/forms.ear file. And, adformsctl.sh will start forms successfully.

___________________________________________________________________
~~~~~~~ Additional notes (Real paths are replaced with env variables) ~~~~~~~
Manually deploying EAR file may not always work. Here is what I experienced when execution "$FND_TOP/bin/txkrun.pl -script=CfgOC4JApp" gets failure in instances where adstpall.sh (to stop apps) get Time out error from "adoafmctl.sh stop" and "adformsctl.sh stop".

1. I first saw error in startup log adstrtal.log in a situation when adstrtal.sh called the Perl script to deploy EAR file. 
Executing service control script:
$ADMIN_SCRIPTS_HOME/adformsctl.sh start
Timeout specified in context file: 100 second(s)

script returned:
****************************************************
ERROR : Timed out( 100000 ): Interrupted Exception

You are running adformsctl.sh  version 120.16.12010000.3

Starting OPMN managed FORMS OC4J instance  ...
Calling txkChkFormsDeployment.pl to check whether latest FORMSAPP.EAR is deployed...
Program : $FND_TOP/patch/115/bin/txkChkFormsDeployment.pl started @ Wed Jul  14 15:47:39 2016

*** Log File = $LOG_HOME/appl/rgf/TXK/
txkChkFormsDeployment_Thu_Jul_14_15_47_39_2016/txkChkFormsDeployment_Thu_Jul_14_15_47_39_2016.log

File "$IAS_ORACLE_HOME/j2ee/forms/applications/forms/formsweb/WEB-INF/lib/frmsrv.jar" exists. Proceeding to check the size...

=================================================
*** Latest formsapp.ear has NOT been deployed ***
Deploying the latest EAR file...
=================================================

*** ALL THE FOLLOWING FILES ARE REQUIRED FOR RESOLVING RUNTIME ERRORS
*** Log File = $LOG_HOME/appl/rgf/TXK/
txkChkFormsDeployment_Thu_Jul_14_15_47_39_2016/txkChkFormsDeployment_Thu_Jul_14_15_47_39_2016.log
****************************************************

2. Because of the error, adopmnctl.sh may show OC4J: forms is DOWN. When I looked into further, I saw errors in the EAR deployment log:

 $ more $LOG_HOME/appl/rgf/TXK/txkChkFormsDeployment_Thu_Jul_14_15_47_39_2016/txkChkFormsDeployment_Thu_Jul_14_15_47_39_2016.log

 - - -  - - -  - - -
Application deployed successfully.        <== this line (and above) show deployment went good 
===========================
Inside manageFormsOc4j()...
===========================

 *** OPERATION is stopproc ***
Execute SYSTEM command : $IAS_ORACLE_HOME/opmn/bin/opmnctl stopproc instancename=forms
opmnctl: stopping opmn managed processes...
opmn id=hostname.domin.com:6230
0 of 1 processes stopped. 
-------------------------------------------------------------------------------- 
ias-component/process-type/process-set:
    default_group/forms/default_group/

 Error
--> Process (index=1,uid=2087060759,pid=14495)
    time out while waiting for a managed process to stop
    Log:
   $LOG_HOME/ora/10.1.3/opmn/default_group~forms~default_group~1.log

 ===========================
Inside manageFormsOc4j()...
===========================
*** OPERATION is startproc ***
Execute SYSTEM command : $IAS_ORACLE_HOME/opmn/bin/opmnctl startproc instancename=forms
ERROR DESCRIPTION:
(*******FATAL ERROR*******
PROGRAM : $FND_TOP/patch/115/bin/txkChkFormsDeployment.pl($FND_TOP/bin/txkrun.pl)
TIME    : Thu Jul 14 15:50:07 2016
FUNCTION: TXK::Process::run [ Level 3 ]
MESSAGES:
Command error: <rc> = 38400, <command> = $IAS_ORACLE_HOME/opmn/bin/opmnctl startproc instancename=forms
Command error: <rc> = 52224, <command> = $IAS_ORACLE_HOME/opmn/bin/opmnctl stopproc instancename=forms
Command error: <rc> = 38400, <command> = $IAS_ORACLE_HOME/opmn/bin/opmnctl stopproc instancename=forms

 STACK TRACE
        TXK::Error::abort('TXK::Error','HASH(0x8942208)') called at $AU_TOP/perl/TXK/Common.pm line 299
        TXK::Common::doError('TXK::Process=HASH(0x89eec34)','Command error: <rc> = 38400, <command> = /path/to/EBSQA/...','undef')
 called at $AU_TOP/perl/TXK/Common.pm line 314
... ... ... ...

 3. Seems the errors are at the last stage of EAR deployment. When I shutdown apps service and started it again by adstrtal.sh, it did not try to deploy the EAR file again and just showed all in status 0. But, when I checked out the login and forms, I could not launch forms.

4. When I tried to deploy the EAR file manually by following the steps above, I got similar errors. Seems the error is from the last stage.

. . .  . . .  . . .
Stopping all OPMN processes.
OPMN stopped.
OPMN started.
Deplolying Application : "forms" onto OC4J instance: "forms"
Application deployed successfully.

Stopping and starting OC4J instances.
Errors encountered running $FND_TOP/patch/115/bin/txkCfgOC4JApp.pl
*******FATAL ERROR*******
PROGRAM : $FND_TOP/patch/115/bin/txkCfgOC4JApp.pl($FND_TOP/bin/txkrun.pl)
TIME    : Wed Jul  6 17:11:06 2016
FUNCTION: TXK::Process::run [ Level 3 ]
MESSAGES:
Command error: <rc> = 52224, <command> =$IAS_ORACLE_HOME/opmn/bin/opmnctl stopproc  instancename=forms

STACK TRACE
        TXK::Error::abort('TXK::Error','HASH(0x91118f4)') called at $AU_TOP/perl/TXK/Common.pm line 299
        TXK::Common::doError('TXK::Process=HASH(0x9a86c10)','Command error: <rc> = 52224, <command> =  /path/to/EBSQA/a...','undef') called at $AU_TOP/perl/TXK/Common.pm line 314
. . .  . . .  . . .

5. I did try things around and made forms work before I ran Step 6 below.

6. The deployment errors match the issue in Doc ID 1399491.1, which recommends patch 12965674 as the fix. After I applied it to 10.1.3.5 ORACLE_HOME (for Apache), it not only fixed the errors in EAR deployment but also fixed below Time out error in adstpall.log during apps services shutdown !

You are running adstpall.sh version 120.10.12010000.4

Executing service control script:
$ADMIN_SCRIPTS_HOME/adoafmctl.sh stop
Timeout specified in context file: 100 second(s)
script returned:
****************************************************
ERROR : Timed out( 100000 ): Interrupted Exception

You are running adoafmctl.sh version 120.8
Stopping OPMN managed OAFM OC4J instance ...

****************************************************
Executing service control script:
$ADMIN_SCRIPTS_HOME/adformsctl.sh stop
Timeout specified in context file: 100 second(s)
script returned:
****************************************************
ERROR : Timed out( 100000 ): Interrupted Exception

You are running adformsctl.sh  version 120.16.12010000.3
Stopping OPMN managed FORMS OC4J instance  ...

NOTES:  Oracle gives a document with a easier way to manually deploy R12 Ear file $ORACLE_HOME/forms/j2ee/formsapp.ear. See Doc ID 1961805.1
Posted by at No comments:
Labels:

Monday, April 4, 2016

Custom EBS password complexity

When I tried to create a new user in a non-prod R12.1 instance, I got below error:

Unable to load java class oracle.apps.custom.security.XXXXXPasswordValidation specified profile option SIGNON_PASSWORD_CUSTOM. Please verify that the class exists and that it implements the java interface oracle.apps.custom.security.XXXXXPasswordValidation

I checked profile option Signon Password Custom which had "oracle.apps.custom.security.XXXXXPasswordValidation" on site level. I ran below query and did not see the custom Java class in the database. So that error makes sense because it was not uploaded to the database.

SQL> SELECT dbms_java.longname(object_name), status
 FROM user_objects
 WHERE object_type = 'JAVA CLASS'
 AND dbms_java.longname(object_name) like '%PasswordValidation';

 DBMS_JAVA.LONGNAME(OBJECT_NAME)        STATUS
 oracle/apps/fnd/security/PasswordValidation           VALID
 oracle/apps/fnd/security/AppsPasswordValidation   VALID 

Fortunately I found the Java files are still saved in the file system. So I uploaded it to the database:

$ cd $JAVA_TOP/oracle/apps/custom/security
$ ls -al
-rwxrwxrwx 1 trainer users 1927 Nov  8  2013 XXXXXPasswordValidation.class
-rwxrwxrwx 1 trainer users 2629 Feb  6  2012 XXXXXPasswordValidation.java

$ loadjava -user apps/appsPWD -verbose -resolve -force XXXXXPasswordValidation.class
arguments: '-user' 'apps/appsPWD' '-verbose' '-resolve' '-force' 'XXXXXPasswordValidation.class'
creating : class oracle/apps/custom/security/XXXXXPasswordValidation
loading  : class oracle/apps/custom/security/XXXXXPasswordValidation
resolving: class oracle/apps/custom/security/XXXXXPasswordValidation

SQL> SELECT dbms_java.longname(object_name), status
 FROM user_objects
 WHERE object_type = 'JAVA CLASS'
 AND dbms_java.longname(object_name) like '%PasswordValidation';

DBMS_JAVA.LONGNAME(OBJECT_NAME)                STATUS
oracle/apps/custom/security/XXXXXPasswordValidation  VALID
oracle/apps/fnd/security/PasswordValidation                    VALID
oracle/apps/fnd/security/AppsPasswordValidation            VALID

After those steps, I was able to create EBS user if the password meets the complexity defined in the custom Java file.

Since oracle/apps/fnd/security/PasswordValidation exists in the database, I assumed it is an Oracle standard class and so I changed profile option Signon Password Custom from  "oracle.apps.custom.security.XXXXXPasswordValidation" to "oracle.apps.fnd.security.PasswordValidation".  But when I tried to create a user now , I got error:

Unable to load Java class oracle.apps.fnd.security.PasswordValidation specified in profile option SIGNON_PASSWORD_CUSTOM. Please verify that the class exists and that it implements the Java interface oracle.apps.fnd.security.PasswordValidation.

As an exercise, I re-uploaded the Java class to the database:

$ cd $JAVA_TOP/oracle/apps/fnd/security
$ ls -al PasswordValidation.class
-rwxrwxrwx 1 trainer users 673 Jan  3  2007 PasswordValidation.class

$ loadjava -user apps/appsPWD -verbose -resolve -force PasswordValidation.class
arguments: '-user' 'apps/appsPWD' '-verbose' '-resolve' '-force' 'PasswordValidation.class'
creating : class oracle/apps/fnd/security/PasswordValidation
loading  : class oracle/apps/fnd/security/PasswordValidation
resolving: class oracle/apps/fnd/security/PasswordValidation

Interestingly, that made it and other Java classes INVALID in the database.I had to run adadmin to compile APPS schema to make them VALID again. Seems that oracle.apps.fnd.security.PasswordValidation can not be used or it is a wrong value to put in profile option Signon Password Custom.

The custom Java code looks like: 
$ view XXXXXPasswordValidation.java

package oracle.apps.custom.security;

 import oracle.apps.fnd.common.VersionInfo;
import oracle.apps.fnd.security.*;
import java.*;

 // Referenced classes of package oracle.apps.fnd.security:
// PasswordValidation

 public class XXXXXPasswordValidation implements PasswordValidation
{

due to copy right, no detail here...

}

Updates:
Profile option "Signon Password Failure Limit" defines maximum number of tries allowed before user account becomes locked.
Profile option "Signon Password No Reuse" defines minimum number of days that a user must wait before being allowed to reuse a password.
Posted by at 4 comments:
Labels:

Monday, March 14, 2016

Enable Forms Runtime Diagnostics (FRD)

1. R12.1 Forms can be implemented in servlet mode or socket mode. Check file $FORMS_WEB_CONFIG_FILE (or $INST_TOP/ora/10.1.2/forms/server/appsweb.cfg) to find which mode forms runs:

In Servlet mode:
serverURL=/forms/lservlet
connectMode=servlet    

In Socket mode:
serverURL=(should be blank)
connectMode=Socket

This can be also verified by Profile option "ICX: Forms Launcher" value https://hostname.domain:port/forms/frmservlet on site level.

2. Doc ID 438652.1 ( R12: Forms Runtime Diagnostics (FRD), Tracing And Logging For Forms In Oracle Applications) gives ways to capture all events that occur in a form session. Unfortunately if the forms does launch, no trace log will be generated. I tested three ways in R12.1.

1) To enable FRD on Site level:
In appsweb.cfg, set "record=collect" as shown below (under ENVIRONMENT SPECIFIC PARAMETERS section)

# Sub argument for other params
record=collect

also can specify the log name
log=site1.log 

2) To enable FRD on User level:
Change Profile option "ICX: Forms Launcher" on user level to https://hostname.domain:port/forms/frmservlet?record=collect

Then, launch forms after logging onto EBS (usually without any services downtime, but may need to bounce Apache or clear cache). Forms shall popup a note "Forms Runtime Diagnostics is enabled, Please note this can affect performance." before forms shows up.

By default, trace file collect_<pid> gets written in folder $FORMS_TRACE_DIR, where <pid> is the process identifier.  "grep" the pid to find which os process created it.

Optionally, in appsweb.cfgs, specify the log name
log=user1.log
(Note: Log file site1.log or user1.log will be saved in folder $FORMS_TRACE_DIR. This folder may need manually cleaning from time to time.)

3) Enabling FRD (in R12.1) by URL https://hostname.domain:port/forms/frmservlet?record=collect ( or https://hostname.domain:port/forms/frmservlet?record=collect+log=user1.log )
Enter EBS userID/password to access forms directly. But it may give error:
APP-FND-01542: This Applications Server is not authorized to access this database.

To get this working, modify current context file by changing “s_appserverid_authentication” value from SECURE to OFF. Then shutdown apps and run Autoconfig.

3. When QA uses Vugen 12 for scripting and uses HP Performance Center (PC) 12 to run the scripts to test EBS R12.1.3 site performance, I was asked to change Profile option "ICX: Forms Launcher" on user level from https://hostname.domain:port/forms/frmservlet to
https://hostname.domain:port/forms/frmservlet?play=&record=names

I did not know what that profile value really does. But after the change, performance testing worked by PC 12 for our QA.

NOTES:  For JWS, do NOT use Method 2 of Option 1 in Doc ID 438652.1 or follow Doc ID 373548.1 (How To Collect And Use Forms Trace (FRD) in Oracle Applications Release 12) to enable FRD by setting up profile option Forms Runtime Parameters to "record=forms tracegroup=0-97". It will give error "FRM-90926: Duplicate Parameter on Command Line" without launching forms.
Posted by at No comments:
Labels:

Tuesday, March 8, 2016

Name a URL for EBS website (and F5)

After EBS is installed and configured on host webHost1d, the default URL is http://webHost1d.domain.com:s_webport. Most times, we want to replace it with more meaningful name, such as finance.domain.com. Or if multiple nodes for the web/form tier, a DNS name has to be used for the EBS site.

1. Request a DNS ip address for finance.domain.com

DNS ip address registration will map server webHost1d.domain.com (or additional server) to finance.domain.com. Before the mapping, you can not ping finance.domain.com or nslookup finance.domain.com.

$ nslookup finance.domain.com
** server can't find finance: SERVFAIL

$ ping finance.domain.com
ping: unknown host finance.domain.com

2. After the DNS ip is registered, nslookup will work like something below. Here, 123.45.67.987 is the ip address for finance.domain.com

$ nslookup finance.domain.com
Server:         123.45.67.89
Address:      123.45.67.89#53

finance.domain.com       canonical name = webHost1d.domain.com.
Name:   webHost1d.domain.com
Address: 123.45.67.987

If finance.domain.com is monitored by F5, its DNS ip address will tied to multiple pool members inside F5, depending on the number of EBS web/forms nodes. In this case, nslookup will return slightly different:

$ nslookup finance.domain.com
Server:         123.45.67.89
Address:      123.45.67.89#53

finance.domain.com        canonical name = finance.dev.vip.domain.com.
Name:   finance.dev.vip.domain.com
Address: 123.45.67.987

The DNS entry for finance.domain.com points to F5 CNAME (canonical name)  finance.dev.vip.domain.com

3. Also, ping on it shall also work

$ ping finance.domain.com
PING finance.domain.com (123.45.67.987) 56(84) bytes of data.
64 bytes from finance.domain.com (123.45.67.987): icmp_seq=1 ttl=63 time=0.280 ms
64 bytes from finance.domain.com (123.45.67.987): icmp_seq=2 ttl=63 time=0.559 ms

4. Make 3 changes in $CONTEXT_FILE to use finance.domain.com as the URL.

<externURL oa_var="s_external_url">https://webhost1d.domain.com:4453</externURL>  =>
<externURL oa_var="s_external_url">https://finance.domain.com:4453</externURL>

<webentryhost oa_var="s_webentryhost">webhost1d</webentryhost>  =>
<webentryhost oa_var="s_webentryhost">finance</webentryhost>

<login_page oa_var="s_login_page">https://webhost1d.domain.com:4453/OA_HTML/AppsLogin</login_page>   =>
<login_page oa_var="s_login_page">https://finance.domain.com:4453/OA_HTML/AppsLogin</login_page>

After AutoConfig, the EBS site can be accessed by https://finance.domain.com:4453

By the way, when 4453 is the ssl port number, it will be the value for three $CONTEXT_FILE variables (and also in $INST_TOP/ora/10.1.3/Apache/Apache/conf/ssl.conf file as the Listen port):

<httpslistenparameter oa_var="s_https_listen_parameter">4453</httpslistenparameter>
<web_ssl_port oa_var="s_webssl_port" oa_type="PORT" base="4443" step="1" range="-1" label="Web SSL Port">4453</web_ssl_port>
<activewebport oa_var="s_active_webport" oa_type="DUP_PORT" base="8000" step="1" range="-1" label="Active Web Port">4453</activewebport>

TROUBLESHHOTING

After an EBS R12.1.3 instance was cloned / moved to new nodes (webHost1d and webHost2d), EBS site page https://finance.domain.com:4453 did not work. 

Page using node name http://webHost1d.domain.com:s_webport worked but failed on re-directing to login page (Note: if company network does not allow http, this page may not load up in browser. Then, on OS level, try $ wget webHost1d.domain.com 8021 ). So, Apache services worked fine. Furthermore, TELNET to the node link works (if telnet is not available, try "wget"): 

 $ telnet webHost1d.domain.com 8021
Trying 177.99.88.66...
Connected to webHost1d.
Escape character is '^]'.
GET       <== type in/Enter
<!DOCTYPE HTML PUBLIC "-//IETF//DTD HTML 2.0//EN">
<HTML><HEAD>
<TITLE>400 Bad Request</TITLE>
</HEAD><BODY>
<H1>Bad Request</H1>
Your browser sent a request that this server could not understand.<P>
invalid request-URI <P>
</BODY></HTML>
Connection closed by foreign host.

While using NSLOOKUP to check the site, it returns two IPs:

$ nslookup finance.domain.com   <== It returns two entries
Server:         123.45.67.89
Address:      123.45.67.89#53

finance.domain.com        canonical name = finance.dev.vip.domain.com.
Name:   finance.dev.vip.domain.com
Address: 123.45.67.987
Name:   finance.dev.vip.domain.com
Address: 123.45.66.987

My understanding is that if it returns two IPs, either the web services are not fully working on web server or there is an issue in F5 / network. In above case, the problem was in F5. After F5 admin removed and re-entered two pool members' IPs, and also re-entered port number (s_webssl_port 4453) in F5 console, the problem got fixed. F5 admin said the problem was on the port number (s_webport 8021??). If s_webport is listened in F5, webpage https://finance.domain.com:4453 may give ERR_CONNECTION_RESET error

Also, need to confirm with Network team that finance.dev.vip.domain.com is the right cname (canonical name).

If 443 is used as s_active_webport (vs. 4453) in $CONTEXT_FILE, 443 shall be also entered in F5 to make site https://finance.domain.com work.

By the way, if EBS site https://finance.domain.com works, link to single node  https://webHost1d.domain.com:web_ssl_port shall say "This site is not secure" or "Your connection is not private" (or ERR_CERT_COMMON_NAME_INVALID, maybe because the ssl cert is not for webHost1d.domain.com).

In a VPN network, such as Zscaler, nslookup may show a different IP or show finance.domain.com is blocked on laptop, while both "nslookup finance.domain.com" and "wget https://finance.domain.com" works on web server (Linux level). That makes the website is not accessible on the laptop (with misleading error in browsers). The cause could be that Zscaler configured the site in a wrong segment.
C:\> nslookup finance.domain.com
... ...
*** Unknown can't find finance.domain.com: Non-existent domain

If reverse lookup is enabled, another common issue in network is IP address is not mapping to the DNS name, for example: 

$ nslookup  123.45.67.987 
** server can't find 987.67.45.123.in-addr.arpa: NXDOMAIN



Posted by at 1 comment:
Labels:

Thursday, March 3, 2016

Get active session info in EBS database

When an EBS job or session runs too long, everyone wants to know what it is doing in the database (11g & 12c) and how far away from its finish. Depending on the session stage, if the job is actively processing sql statements, two scripts here will provide similar Output:
... ... ...
Progress in DB (SID: 3258)
----------------------------------------------------------
START TIME ...... : 08-FEB-2016 15:00:54
Elapsed in min .... : 0:11
Remaining ......... : 0:3
Complete_pct .... : 79.59
SQL ID .......... : f8h3xq0c5sqb6
SQL hash value .. : 408705382
db Object ....... : AR.AR_PAYMENT_SCHEDULES_ALL
Message ......... : Index Fast Full Scan:  AR.AR_PAYMENT_SCHEDULES_ALL: 58776 out of 73853 Blocks done
----------------------------------------------------------
START TIME ...... : 08-FEB-2016 15:01:08
Elapsed in min .... : 69:35
Remaining ......... : 39:24
Complete_pct .... : 63.51
SQL ID .......... : f8h3xq0c5sqb6
SQL hash value .. : 408705382
db Object ....... : AR.AR_CASH_RECEIPTS_ALL
Message ......... : Table Scan:  AR.AR_CASH_RECEIPTS_ALL: 528906 out of 832791 Blocks done

  • Script finds database session info by entering a concurrent Request ID:
When the concurrent Request is still in Running status and if column ORACLE_SESSION_ID in table fnd_concurrent_requests is populated, below query shall return accurate information. Seem to me when the concurrent Request is near to finish, sometimes that column could get erased to NULL.
X~~~~~~~~~~~~~~~~~~~~~ enter EBS Request ID ~~~~~~~~~~~~~~~~~~~~~X
set echo off
set linesize 150
set verify off
set feedback off
set serveroutput on size 1000000

DECLARE
cursor sess_cursor is
select
        p.spid,
        s.process,
        s.sid,
        s.serial#,
        s.username,
        s.status,
        s.machine,
        s.terminal,
        s.program,
        s.module,
        s.action,
        s.sql_hash_value,
        s.sql_address,
        to_char(s.logon_time, 'DD-Mon-YYYY HH24:MI:SS') logontime,
        round((s.last_call_et/60),2) last_call_et,
        s.seconds_in_wait,
        s.client_identifier,
        s.sql_id,
        w.event,
        w.state
from
        v$session s, v$process p, v$session_wait w, fnd_concurrent_requests f
where
           s.paddr = p.addr
and     s.sid = w.sid
and     f.ORACLE_SESSION_ID = s.AUDSID
and     f.request_id='&Request_id';

begin
dbms_output.put_line('------------------------------------------------------------------------------');
dbms_output.put_line(' CM Request: database session details associated with Request id ');
dbms_output.put_line('------------------------------------------------------------------------------');

for x in sess_cursor
loop
        dbms_output.put_line('DB PID ......... : ' || x.spid);
        dbms_output.put_line('EBS PID ........ : ' || x.process);
        dbms_output.put_line('SID ............ : ' || x.sid);
        dbms_output.put_line('Serial# ........ : ' || x.serial#);
        dbms_output.put_line('Username ....... : ' || x.username);
        dbms_output.put_line('Status ......... : ' || x.status);
        dbms_output.put_line('Machine ........ : ' || x.machine);
        dbms_output.put_line('Terminal ....... : ' || x.terminal);
        dbms_output.put_line('Program ........ : ' || x.program);
        dbms_output.put_line('Module ......... : ' || x.module);
        dbms_output.put_line('Action ......... : ' || x.action);
        dbms_output.put_line('SQL Hash Value . : ' || x.sql_hash_value);
        dbms_output.put_line('Logon Time ..... : ' || x.logontime);
        dbms_output.put_line('Last Call Et ... : ' || x.last_call_et || ' ' || 'min');
        dbms_output.put_line('Seconds in Wait. : ' || x.seconds_in_wait);
        dbms_output.put_line('User ID ........ : ' || x.client_identifier);
        dbms_output.put_line('Session State .. : ' || x.state);
        dbms_output.put_line('Wait Event ..... : ' || x.event);
        dbms_output.put_line('SQL ID ... ..... : ' || x.sql_id);
        dbms_output.put_line('SQL Text ....... : ');

        for y in (
                select sql_text
                from v$sqltext v
                where
                v.hash_value = x.sql_hash_value
                and v.address = x.sql_address
                order by v.piece)
        loop
                dbms_output.put_line(' ' || y.sql_text);
        end loop;
        dbms_output.put_line('Progress in DB (SID: '|| x.sid ||')');
        for z in (
               SELECT
               ROUND(sl.elapsed_seconds/60) || ':' || MOD(sl.elapsed_seconds,60) elapsed,
               ROUND(sl.time_remaining/60) || ':' || MOD(sl.time_remaining,60) remaining,
               ROUND(sl.sofar/sl.totalwork*100, 2) progress_pct,
               sl.sql_id, sl.message, sl.sql_hash_value,
               opname operation,
               target object,
               to_char(start_time, 'DD-MON-YYYY HH24:MI:SS') start_time
               FROM   v$session_longops sl
               WHERE  sl.sid = x.sid AND sl.serial# = x.serial#
                      AND totalwork > 0
              order by progress_pct desc, start_time asc)
        loop
                 dbms_output.put_line('----------------------------------------------------------');
                 dbms_output.put_line('  START TIME ...... : ' || z.start_time);
                 dbms_output.put_line('  Elapsed in min .. : ' || z.elapsed);
                 dbms_output.put_line('  Remaining ....... : ' || z.remaining);
                 dbms_output.put_line('  Complete_pct .... : ' || z.progress_pct);
                 dbms_output.put_line('  SQL ID .......... : ' || z.sql_id);
                 dbms_output.put_line('  SQL hash value .. : ' || z.sql_hash_value);
                 dbms_output.put_line('  db Object ....... : ' || z.object);
                 dbms_output.put_line('  Message ......... : ' || z.message);
       end loop;

  dbms_output.put_line('--------------------------------------------------------------------');
end loop;
end;
/
  • Script finds database session info by entering OS process ID on Apps node 
X~~~~~~~~~~~~~~~~~~~~~ enter OS process ID ~~~~~~~~~~~~~~~~~~~~~~~X
set echo off
set linesize 150
set verify off
set feedback off
set serveroutput on size 1000000
DECLARE
v_sql_id  v$sql.sql_id%type;
cursor sess_cursor is
select
        p.spid,
        s.process,
        s.sid,
        s.serial#,
        s.username,
        s.status,
        s.machine,
        s.terminal,
        s.program,
        s.module,
        s.action,
        s.sql_hash_value,
        s.sql_address,
        to_char(s.logon_time, 'DD-Mon-YYYY HH24:MI:SS') logontime,
        round((s.last_call_et/60),2) last_call_et,
        s.seconds_in_wait,
        s.client_identifier,
        s.sql_id,
        w.event,
        w.state
from
        v$session s, v$process p, v$session_wait w
where
           s.paddr = p.addr
and     s.sid = w.sid
and     s.process = '&EBS_OS_pid';

begin
dbms_output.put_line('------------------------------------------------------------------------------');
dbms_output.put_line(' Database session details associated with Process id ');
dbms_output.put_line('------------------------------------------------------------------------------');

for x in sess_cursor
loop
        -- select distinct sql_id into v_sql_id from v$sqltext v
        --  where v.hash_value = x.sql_hash_value
        --   and v.address = x.sql_address;
        dbms_output.put_line('DB PID ......... : ' || x.spid);
        dbms_output.put_line('EBS PID ........ : ' || x.process);
        dbms_output.put_line('SID ............ : ' || x.sid);
        dbms_output.put_line('Serial# ........ : ' || x.serial#);
        dbms_output.put_line('Username ....... : ' || x.username);
        dbms_output.put_line('Status ......... : ' || x.status);
        dbms_output.put_line('Machine ........ : ' || x.machine);
        dbms_output.put_line('Terminal ....... : ' || x.terminal);
        dbms_output.put_line('Program ........ : ' || x.program);
        dbms_output.put_line('Module ......... : ' || x.module);
        dbms_output.put_line('Action ......... : ' || x.action);
        dbms_output.put_line('SQL Hash Value . : ' || x.sql_hash_value);
        dbms_output.put_line('Logon Time ..... : ' || x.logontime);
        dbms_output.put_line('Last Call Et ... : ' || x.last_call_et || ' ' || 'min');
        dbms_output.put_line('Seconds in Wait. : ' || x.seconds_in_wait);
        dbms_output.put_line('User ID ........ : ' || x.client_identifier);
        dbms_output.put_line('Session State .. : ' || x.state);
        dbms_output.put_line('Wait Event ..... : ' || x.event);
        dbms_output.put_line('SQL ID ... ..... : ' || x.sql_id);
        dbms_output.put_line('SQL Text ....... : ');

        for y in (
                select sql_text
                from v$sqltext v
                where
                v.hash_value = x.sql_hash_value
                and v.address = x.sql_address
                order by v.piece)
        loop
                dbms_output.put_line(' ' || y.sql_text);
        end loop;
        dbms_output.put_line('Progress in DB (SID: '|| x.sid ||')');
        for z in (
               SELECT
               ROUND(sl.elapsed_seconds/60) || ':' || MOD(sl.elapsed_seconds,60) elapsed,
               ROUND(sl.time_remaining/60) || ':' || MOD(sl.time_remaining,60) remaining,
               ROUND(sl.sofar/sl.totalwork*100, 2) progress_pct,
               sl.sql_id, sl.message, sl.sql_hash_value,
               opname operation,
               target object,
               to_char(start_time, 'DD-MON-YYYY HH24:MI:SS') start_time
               FROM   v$session_longops sl
               WHERE  sl.sid = x.sid AND sl.serial# = x.serial#
                      AND totalwork > 0
              order by progress_pct desc, start_time asc)
        loop
                 dbms_output.put_line('----------------------------------------------------------');
                 dbms_output.put_line('  START TIME ...... : ' || z.start_time);
                 dbms_output.put_line('  Elapsed in min .. : ' || z.elapsed);
                 dbms_output.put_line('  Remaining ....... : ' || z.remaining);
                 dbms_output.put_line('  Complete_pct .... : ' || z.progress_pct);
                 dbms_output.put_line('  SQL ID .......... : ' || z.sql_id);
                 dbms_output.put_line('  SQL hash value .. : ' || z.sql_hash_value);
                 dbms_output.put_line('  db Object ....... : ' || z.object);
                 dbms_output.put_line('  Message ......... : ' || z.message);
       end loop;
  dbms_output.put_line('--------------------------------------------------------------------');
end loop;
end;
/

Posted by at No comments:
Labels:
Subscribe to: Comments (Atom)