After server rebooted unexpectedly, adoacorectl.sh failed to start OACORE OC4J due to opmn issue and made R12.1.3 initial page unable to re-direct to login page. The error is
adoacorectl.sh: exiting with status 204
File $INST_TOP/logs/appl/admin/log/adoacorectl.txt shows more detail:
10/09/18-11:37:25 :: adoacorectl.sh version 120.13
10/09/18-11:37:25 :: adoacorectl.sh: starting OPMN if it is not running
opmnctl: opmn is already running.
10/09/18-11:37:25 :: adoacorectl.sh: Starting OPMN managed OACORE OC4J instance
opmnctl: starting opmn managed processes...
============================================
opmn id=hostname.domian.com:6240
0 of 1 processes started.
ias-instance id=EBSDEV_hostname.hostname.domian.com
++++++++++++++++++++++++++++++++++++++++++++
ias-component/process-type/process-set:
default_group/oacore/default_group/
Error
--> Process (index=1,uid=1230729787,pid=17205)
failed to start a managed process after the maximum retry limit
Log:
$INST_TOP/logs/ora/10.1.3/opmn/default_group~oacore~default_group~1.log
10/09/18-11:37:37 :: adoacorectl.sh: exiting with status 204
File $INST_TOP/logs/ora/10.1.3/opmn/default_group~oacore~default_group~1.log has below warning which was not new and existed before:
--------
18/10/09 11:37:25 Start process
--------
18/10/09 11:37:26 WARNING: ApplicationLogManager is not installed, may result in loader leaks. Set -Djava.util.logging.manager=oracle.classloader.util.ApplicationLogManager
I did not make any changes in EBS in past months. Seems the problem was with opmn, but I do not know what it is. So I just ran autocofig. After that, "adoacorectl.sh start" worked surprisingly and login page showed up. The warning message with ApplicationLogManager still stays in the opmn log file.
The problem could be some session lock files got inconsistent by the server crash.
BTW, to turn trace (increase logging) on OC4J oacore by editing two files:
Edit j2ee-logging.xml adjust the following in file:
$ORA_CONFIG_HOME/10.1.3/j2ee/oacore/config/j2ee-logging.xml
<logger name=’oracle’ level='TRACE:32′ useParentHandlers=’false’>
Edit orion-web.xml adjust the following in file:
$ORA_CONFIG_HOME/10.1.3/j2ee/oacore/application-deployments/oacore/html/orion-web.xml
<param-name>debug_mode</param-name>
<param-value>true</param-value>
But it is difficult to read and understand the log log.xml under $LOG_HOME/ora/10.1.3/j2ee/oacore/oacore_default_group_1
Wednesday, October 10, 2018
Wednesday, September 19, 2018
Find the NLS settings on a connection
When making a connection to the database, below query will tell its process IDs on both client and db server:
SQL> select b.sid, b.serial#, a.spid processid, b.process clientpid
from v$process a, v$session b
where a.addr = b.paddr
and b.audsid = userenv('sessionid');
SID SERIAL# PROCESSID CLIENTPID
------------ ------------ ---------------- -----------------
180 42146 48824530 26093
26093 is the process ID for this sqlplus session on client/Apps side.
48824530 is the process ID on database server for this session.
In general,
V$SESSION.SID and V$SESSION.SERIAL# – process ID in database
V$PROCESS.SPID – Shadow process ID on the database server
V$SESSION.PROCESS – Client process ID
If start a new session on the same client where sqlplus is still running, you can verify it is running:
$ ps -ef | grep 26093
ebsdev 26093 25836 0 15:09 pts/1 00:00:00 sqlplus
To find what NLS settings are used to support the connection, run below lines:
$ ps ewww 26093 | tr ' ' '\n' | grep NLS
-DNLS_ASIA
FORMS_OVERRIDE_ENV=NLS_LANG,NLS_NUMERIC_CHARACTERS,NLS_SORT,NLS_DATE_LANGUAGE,NLS_DATE_FORMAT,FORMS_USER_DATE_FORMAT,FORMS_USER_DATETIME_FORMAT,FORMS_OUTPUT_DATE_FORMAT,FORMS_OUTPUT_DATETIME_FORMAT,FORMS_ERROR_DATE_FORMAT,FORMS_ERROR_DATETIME_FORMAT,FORMS_TZFILE,FORMS_DATETIME_SERVER_TZ,FORMS_DATETIME_LOCAL_TZ,FORMS_USER_CALENDAR
NLS_DATE_FORMAT=DD-MON-RR
NLS_DATE_LANGUAGE=
NLS_LANG=American_America.WE8ISO8859P1
NLS_NUMERIC_CHARACTERS=.,
NLS_SORT=BINARY
ORA_NLS10=$ORACLE_HOME/nls/data/9idata
Do the same for other sessions. For example, if you are interested on the listener session, find its process ID first and then see its NLS settings by "ps ewww".
$ ps -ef|grep tnslsnr
ebsdev 23845 1 0 Aug01 ? 00:00:00 $ORACLE_HOME/bin/tnslsnr APPS_EBSDEV -inherit
Similarly, you can do the same with 48824530 on database server to find its NLS settings.
$ ps ewww 48824530 | tr ' ' '\n' | grep NLS
NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1
NLSPATH=/usr/lib/nls/msg/%L/%N:/usr/lib/nls/msg/%L/%N.cat
ORA_NLS10=$ORACLE_HOME/nls/data/9idata
Below query can help to find parameters for database characterset. Ideally all of three sides shall be consistent.
SQL> SELECT db.parameter as parameter, db.value as database_value,
s.value as session_value, i.value as instance_value
FROM
nls_database_parameters db
LEFT JOIN
nls_session_parameters s
ON s.parameter = db.parameter
LEFT JOIN
nls_instance_parameters i
ON i.parameter = db.parameter
ORDER BY parameter;
PARAMETER DATABASE_VALUE SESSION_VALUE INSTANCE_VALUE
------------------ ------------------------- ------------------------- -------------------
NLS_CALENDAR GREGORIAN GREGORIAN
NLS_CHARACTERSET WE8ISO8859P1
NLS_COMP BINARY BINARY binary
NLS_CURRENCY $ $
NLS_DATE_FORMAT DD-MON-RR DD-MON-RR DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN AMERICAN
NLS_DUAL_CURRENCY $ $
NLS_ISO_CURRENCY AMERICA AMERICA
NLS_LANGUAGE AMERICAN AMERICAN AMERICAN
NLS_LENGTH_SEMANTICS BYTE BYTE BYTE
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_NCHAR_CONV_EXCP FALSE FALSE FALSE
NLS_NUMERIC_CHARACTERS ., ., .,
NLS_RDBMS_VERSION 12.1.0.2.0
NLS_SORT BINARY BINARY binary
NLS_TERRITORY AMERICA AMERICA america
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM DD-MON-RR HH.MI.SSXFF AM
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM DD-MON-RR HH.MI.SSXFF AM TZR TZR
NLS_TIME_FORMAT HH.MI.SSXFF AM HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR HH.MI.SSXFF AM TZR
When NLS_LANG is unset, it defaults to US7ASCII.
Without proper setting on ORA_NLS10, package UTL_FILE may not work correctly. See https://erpondb.blogspot.com/2016/10/troubleshhot-utlfile-error.html
SQL> select b.sid, b.serial#, a.spid processid, b.process clientpid
from v$process a, v$session b
where a.addr = b.paddr
and b.audsid = userenv('sessionid');
SID SERIAL# PROCESSID CLIENTPID
------------ ------------ ---------------- -----------------
180 42146 48824530 26093
26093 is the process ID for this sqlplus session on client/Apps side.
48824530 is the process ID on database server for this session.
In general,
V$SESSION.SID and V$SESSION.SERIAL# – process ID in database
V$PROCESS.SPID – Shadow process ID on the database server
V$SESSION.PROCESS – Client process ID
If start a new session on the same client where sqlplus is still running, you can verify it is running:
$ ps -ef | grep 26093
ebsdev 26093 25836 0 15:09 pts/1 00:00:00 sqlplus
To find what NLS settings are used to support the connection, run below lines:
$ ps ewww 26093 | tr ' ' '\n' | grep NLS
-DNLS_ASIA
FORMS_OVERRIDE_ENV=NLS_LANG,NLS_NUMERIC_CHARACTERS,NLS_SORT,NLS_DATE_LANGUAGE,NLS_DATE_FORMAT,FORMS_USER_DATE_FORMAT,FORMS_USER_DATETIME_FORMAT,FORMS_OUTPUT_DATE_FORMAT,FORMS_OUTPUT_DATETIME_FORMAT,FORMS_ERROR_DATE_FORMAT,FORMS_ERROR_DATETIME_FORMAT,FORMS_TZFILE,FORMS_DATETIME_SERVER_TZ,FORMS_DATETIME_LOCAL_TZ,FORMS_USER_CALENDAR
NLS_DATE_FORMAT=DD-MON-RR
NLS_DATE_LANGUAGE=
NLS_LANG=American_America.WE8ISO8859P1
NLS_NUMERIC_CHARACTERS=.,
NLS_SORT=BINARY
ORA_NLS10=$ORACLE_HOME/nls/data/9idata
Do the same for other sessions. For example, if you are interested on the listener session, find its process ID first and then see its NLS settings by "ps ewww".
$ ps -ef|grep tnslsnr
ebsdev 23845 1 0 Aug01 ? 00:00:00 $ORACLE_HOME/bin/tnslsnr APPS_EBSDEV -inherit
Similarly, you can do the same with 48824530 on database server to find its NLS settings.
$ ps ewww 48824530 | tr ' ' '\n' | grep NLS
NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1
NLSPATH=/usr/lib/nls/msg/%L/%N:/usr/lib/nls/msg/%L/%N.cat
ORA_NLS10=$ORACLE_HOME/nls/data/9idata
Below query can help to find parameters for database characterset. Ideally all of three sides shall be consistent.
SQL> SELECT db.parameter as parameter, db.value as database_value,
s.value as session_value, i.value as instance_value
FROM
nls_database_parameters db
LEFT JOIN
nls_session_parameters s
ON s.parameter = db.parameter
LEFT JOIN
nls_instance_parameters i
ON i.parameter = db.parameter
ORDER BY parameter;
PARAMETER DATABASE_VALUE SESSION_VALUE INSTANCE_VALUE
------------------ ------------------------- ------------------------- -------------------
NLS_CALENDAR GREGORIAN GREGORIAN
NLS_CHARACTERSET WE8ISO8859P1
NLS_COMP BINARY BINARY binary
NLS_CURRENCY $ $
NLS_DATE_FORMAT DD-MON-RR DD-MON-RR DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN AMERICAN
NLS_DUAL_CURRENCY $ $
NLS_ISO_CURRENCY AMERICA AMERICA
NLS_LANGUAGE AMERICAN AMERICAN AMERICAN
NLS_LENGTH_SEMANTICS BYTE BYTE BYTE
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_NCHAR_CONV_EXCP FALSE FALSE FALSE
NLS_NUMERIC_CHARACTERS ., ., .,
NLS_RDBMS_VERSION 12.1.0.2.0
NLS_SORT BINARY BINARY binary
NLS_TERRITORY AMERICA AMERICA america
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM DD-MON-RR HH.MI.SSXFF AM
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM DD-MON-RR HH.MI.SSXFF AM TZR TZR
NLS_TIME_FORMAT HH.MI.SSXFF AM HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR HH.MI.SSXFF AM TZR
When NLS_LANG is unset, it defaults to US7ASCII.
Without proper setting on ORA_NLS10, package UTL_FILE may not work correctly. See https://erpondb.blogspot.com/2016/10/troubleshhot-utlfile-error.html
Saturday, September 15, 2018
"deleted" files still take disk space
We received alerts on disk space used by EBS is 97% full. When I use "du" to check the space, it shows only 60% of the space is used. But "df -h" shows it is 97% full. The problem is that some files deleted by EBS process will not really release the space back yet until the process is stopped. See https://access.redhat.com/solutions/2316
Below line will find if there are "deleted" files that still hold space, assuming the partition is /u02/app:
$ lsof | grep /u02/ | grep app | grep delete | more
rwrun 18759 applMgr 13u REG 253,3 3140558848 3082366 $INST_TOP/temp/dat000418759 (deleted)
rwrun 18759 applMgr 15u REG 253,3 745730048 3082368 $INST_TOP/temp/idx000518759 (deleted)
rwrun 18759 applMgr 17u REG 253,3 0 3082375 $INST_TOP/temp/000718759 (deleted)
3140558848 is the size by the file. You may see the size is increasing if process 18759 keeps running. But you will not see the file by "ls" command.
$ ps -ef | grep 18759
$ORACLE_HOME/bin/rwrun mode=character ARRAYSIZE=5 P_CONC_REQUEST_ID=21334937 P_MAIL_INDICATOR='ALL' report=$APPL_TOP/aear/reports/US/XXXXRREG.rdf userid=APPS batch=yes destype=file desname=$APPLCSF/out/o21334937.out desformat=$FND_TOP/reports/PD pagesize=2050x66
We found concurrent report "XXXX Receipt Register" in request ID 21334937 ran for 15 hours. After it is cancelled, the "hidden" space is released back.
Below line will find if there are "deleted" files that still hold space, assuming the partition is /u02/app:
$ lsof | grep /u02/ | grep app | grep delete | more
rwrun 18759 applMgr 13u REG 253,3 3140558848 3082366 $INST_TOP/temp/dat000418759 (deleted)
rwrun 18759 applMgr 15u REG 253,3 745730048 3082368 $INST_TOP/temp/idx000518759 (deleted)
rwrun 18759 applMgr 17u REG 253,3 0 3082375 $INST_TOP/temp/000718759 (deleted)
3140558848 is the size by the file. You may see the size is increasing if process 18759 keeps running. But you will not see the file by "ls" command.
$ ps -ef | grep 18759
$ORACLE_HOME/bin/rwrun mode=character ARRAYSIZE=5 P_CONC_REQUEST_ID=21334937 P_MAIL_INDICATOR='ALL' report=$APPL_TOP/aear/reports/US/XXXXRREG.rdf userid=APPS batch=yes destype=file desname=$APPLCSF/out/o21334937.out desformat=$FND_TOP/reports/PD pagesize=2050x66
We found concurrent report "XXXX Receipt Register" in request ID 21334937 ran for 15 hours. After it is cancelled, the "hidden" space is released back.
The same problem happens to /tmp file partition, when parameter forms_tmpdir points to /tmp.
$ grep forms_tmpdir $CONTEXT_FILE
<forms_tmpdir oa_var="s_forms_tmpdir" osd="UNIX">/tmp</forms_tmpdir>
$ lsof | grep /tmp | grep delete <== it takes longer
$ lsof /tmp | grep delete <== it runs quick
frmweb 398 applMgr 86u REG 253,5 5303826 365 /tmp/filer6nHW3.TMP (deleted)
frmweb 570 s044150 86u REG 253,5 41307666 144 /tmp/file4FWO2C.TMP (deleted)
$ ps -ef | grep 570
applMgr 570 31251 0 Mar15 ? 00:07:39 frmweb server webfile=HTTP-0,0,1,default
It is better to change forms_tmpdir to $APPLTMP, $INST_TOP/temp or $NE_BASE/EBSapps/log. /tmp is for server logs and if it is full, the server may crash and go down.
Tuesday, September 11, 2018
FND: Diagnostics to find the true error
After log onto EBS R12.1.3 homepage, click on Preferences and get a generic error:
You have encountered an unexpected error. Please contact the System Administrator for assistance.
The error seems just on one page, not on all pages. I had the same error message before when the database having issue, such as invalid package. But the error does not tell what is the real cause.
The key is to enable Profile option "FND: Diagnostics". After re-login, the error message will come with a link "Click here for exception details"
That leads to find the true error is an ORA-00600 error on SQL statement:
SELECT * FROM (select t.territory_short_name territory_name, t.nls_territory
from fnd_territories_vl t, v$nls_valid_values v
where t.nls_territory = v.value
and v.parameter = 'TERRITORY') QRSLT ORDER BY territory_name
;
ORA-00600: internal error code, arguments: [1350], [3], [23], [60], [AMERICAN], [0], [], [], [], [], [], []
You have encountered an unexpected error. Please contact the System Administrator for assistance.
The error seems just on one page, not on all pages. I had the same error message before when the database having issue, such as invalid package. But the error does not tell what is the real cause.
The key is to enable Profile option "FND: Diagnostics". After re-login, the error message will come with a link "Click here for exception details"
That leads to find the true error is an ORA-00600 error on SQL statement:
SELECT * FROM (select t.territory_short_name territory_name, t.nls_territory
from fnd_territories_vl t, v$nls_valid_values v
where t.nls_territory = v.value
and v.parameter = 'TERRITORY') QRSLT ORDER BY territory_name
;
ORA-00600: internal error code, arguments: [1350], [3], [23], [60], [AMERICAN], [0], [], [], [], [], [], []
Thursday, August 16, 2018
EBS hits ORA-28040: No matching authentication protocol
After database was upgraded to 12.1.0.2, clone script to refresh a R12.1.3 instance got a error:
$ perl adcfgclone.pl appsTier
... ...
Target System Port Pool [0-99] : 27
Checking the port pool 27
done: Port Pool 27 is free
Report file located at $INST_TOP/admin/out/portpool.lst
Complete port information available at $INST_TOP/admin/out/portpool.lst
RC-40201: Unable to connect to Database EBSQA.
The detail error messages in log file are
---------------------------------------------------------------
ADX Database Utility
---------------------------------------------------------------
getConnectionUsingAppsJDBCConnector() -->
APPS_JDBC_URL=''
Trying to get connection using SID based connect descriptor
getConnection() -->
sDbHost : ebsdb1q
sDbDomain : domain.com
sDbPort : 1527
sDbSid : EBSQA
sDbUser : APPS
Trying to connect using SID...
getConnectionUsingSID() -->
JDBC URL: jdbc:oracle:thin:@ebsdb1q.domain.com:1527:EBSQA
Exception occurred: java.sql.SQLException: ORA-28040: No matching authentication protocol
Trying to connect using SID as ServiceName
getConnectionUsingServiceName() -->
JDBC URL: jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ebsdb1q.domain.com)(PORT=1527))(CONNECT_DATA=(SERVICE_NAME=EBSQA)))
Exception occurred: java.sql.SQLException: ORA-28040: No matching authentication protocol
Trying to connect using SID as ServiceName.DomainName
getConnectionUsingServiceName() -->
JDBC URL: jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ebsdb1q.domain.com)(PORT=1527))(CONNECT_DATA=(SERVICE_NAME=EBSQA.domain.com)))
Exception occurred: java.sql.SQLException: Listener refused the connection with the following error:
ORA-12514, TNS:listener does not currently know of service requested in connect descriptor
The Connection descriptor used by the client was:
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ebsdb1q.domain.com)(PORT=1527))(CONNECT_DATA=(SERVICE_NAME=EBSQA.domain.com)))
Connection could not be obtained; returning null
-------------------ADX Database Utility Finished---------------
Exception occurred while preseeding variables in the context file: java.sql.SQLException: Could not get connection to the database
StackTrace:
java.sql.SQLException: Could not get connection to the database
Because of the error, it failed to generate TNS files:
##########################################################################
Generate Tns Names
##########################################################################
Logfile: $INST_TOP/admin/log/NetServiceHandler.log
Classpath: $COMMON_TOP/java/lib/appsborg2.zip:$COMMON_TOP/java/classes
Updating s_tnsmode to 'generateTNS'
UpdateContext exited with status: 0
AC-50480: Internal error occurred: java.lang.Exception: Error while generating listener.ora.
Error generating tnsnames.ora from the database, temporary tnsnames.ora will be generated using templates
Instantiating Tools tnsnames.ora
Tools tnsnames.ora instantiated
Web tnsnames.ora instantiated
adgentns.pl exiting with status 2
ERRORCODE = 2 ERRORCODE_END
.end std out.
.end err out.
Result : FAILED
It seems next steps in the clone script use Sqlplus to make database connection and make this error ignorable. So, even with the error, the cloned instance worked fine.
To avoid this JDBC error, before running the clone script, add two lines to file $TNS_ADMIN/sqlnet.ora on database server and then bounce database listener (Doc. 2125856.1):
SQLNET.ALLOWED_LOGON_VERSION_CLIENT=8
SQLNET.ALLOWED_LOGON_VERSION_SERVER=8
The setting 8 (or 10, 11) has nothing to do with the Oracle database version, it is authentication level used by the JDBC. But, it is a temporary solution, because it leads to use old and unsupported Oracle drivers. The best solution though is to upgrade the Oracle driver on client side.
One solution after the clone worked is to customize file $ORA_CONFIG_HOME/10.1.3/j2ee/forms/config/server.xml with using two .jar files in $COMMON_TOP/java/lib.
I did not test the solution by Doc. 2228803.1 which will modify Perl script to use two newer .jar files.
If clone script does not get error, the log should like this:
---------------------------------------------------------------
ADX Database Utility
---------------------------------------------------------------
getConnection() -->
sDbHost : ebsdb1q
sDbDomain : domain.com
sDbPort : 1547
sDbSid : EBSQA
sDbUser : APPS
Trying to connect using SID...
getConnectionUsingSID() -->
JDBC URL: jdbc:oracle:thin:@ebsdb1q.domain.com:1547:EBSQA
Connection obtained
-------------------ADX Database Utility Finished---------------
$ perl adcfgclone.pl appsTier
... ...
Target System Port Pool [0-99] : 27
Checking the port pool 27
done: Port Pool 27 is free
Report file located at $INST_TOP/admin/out/portpool.lst
Complete port information available at $INST_TOP/admin/out/portpool.lst
RC-40201: Unable to connect to Database EBSQA.
The detail error messages in log file are
---------------------------------------------------------------
ADX Database Utility
---------------------------------------------------------------
getConnectionUsingAppsJDBCConnector() -->
APPS_JDBC_URL=''
Trying to get connection using SID based connect descriptor
getConnection() -->
sDbHost : ebsdb1q
sDbDomain : domain.com
sDbPort : 1527
sDbSid : EBSQA
sDbUser : APPS
Trying to connect using SID...
getConnectionUsingSID() -->
JDBC URL: jdbc:oracle:thin:@ebsdb1q.domain.com:1527:EBSQA
Exception occurred: java.sql.SQLException: ORA-28040: No matching authentication protocol
Trying to connect using SID as ServiceName
getConnectionUsingServiceName() -->
JDBC URL: jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ebsdb1q.domain.com)(PORT=1527))(CONNECT_DATA=(SERVICE_NAME=EBSQA)))
Exception occurred: java.sql.SQLException: ORA-28040: No matching authentication protocol
Trying to connect using SID as ServiceName.DomainName
getConnectionUsingServiceName() -->
JDBC URL: jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ebsdb1q.domain.com)(PORT=1527))(CONNECT_DATA=(SERVICE_NAME=EBSQA.domain.com)))
Exception occurred: java.sql.SQLException: Listener refused the connection with the following error:
ORA-12514, TNS:listener does not currently know of service requested in connect descriptor
The Connection descriptor used by the client was:
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ebsdb1q.domain.com)(PORT=1527))(CONNECT_DATA=(SERVICE_NAME=EBSQA.domain.com)))
Connection could not be obtained; returning null
-------------------ADX Database Utility Finished---------------
Exception occurred while preseeding variables in the context file: java.sql.SQLException: Could not get connection to the database
StackTrace:
java.sql.SQLException: Could not get connection to the database
Because of the error, it failed to generate TNS files:
##########################################################################
Generate Tns Names
##########################################################################
Logfile: $INST_TOP/admin/log/NetServiceHandler.log
Classpath: $COMMON_TOP/java/lib/appsborg2.zip:$COMMON_TOP/java/classes
Updating s_tnsmode to 'generateTNS'
UpdateContext exited with status: 0
AC-50480: Internal error occurred: java.lang.Exception: Error while generating listener.ora.
Error generating tnsnames.ora from the database, temporary tnsnames.ora will be generated using templates
Instantiating Tools tnsnames.ora
Tools tnsnames.ora instantiated
Web tnsnames.ora instantiated
adgentns.pl exiting with status 2
ERRORCODE = 2 ERRORCODE_END
.end std out.
.end err out.
Result : FAILED
It seems next steps in the clone script use Sqlplus to make database connection and make this error ignorable. So, even with the error, the cloned instance worked fine.
To avoid this JDBC error, before running the clone script, add two lines to file $TNS_ADMIN/sqlnet.ora on database server and then bounce database listener (Doc. 2125856.1):
SQLNET.ALLOWED_LOGON_VERSION_CLIENT=8
SQLNET.ALLOWED_LOGON_VERSION_SERVER=8
The setting 8 (or 10, 11) has nothing to do with the Oracle database version, it is authentication level used by the JDBC. But, it is a temporary solution, because it leads to use old and unsupported Oracle drivers. The best solution though is to upgrade the Oracle driver on client side.
One solution after the clone worked is to customize file $ORA_CONFIG_HOME/10.1.3/j2ee/forms/config/server.xml with using two .jar files in $COMMON_TOP/java/lib.
I did not test the solution by Doc. 2228803.1 which will modify Perl script to use two newer .jar files.
If clone script does not get error, the log should like this:
---------------------------------------------------------------
ADX Database Utility
---------------------------------------------------------------
getConnection() -->
sDbHost : ebsdb1q
sDbDomain : domain.com
sDbPort : 1547
sDbSid : EBSQA
sDbUser : APPS
Trying to connect using SID...
getConnectionUsingSID() -->
JDBC URL: jdbc:oracle:thin:@ebsdb1q.domain.com:1547:EBSQA
Connection obtained
-------------------ADX Database Utility Finished---------------
Tuesday, August 14, 2018
Every EBS Forms got a popup FRM-40735 error
After I apply January 2018 CPU patch to R12.1.3, every single forms gives a popup window with error:
ORA-01403: no data found
FRM-40735 WHEN-NEW-ITEM-INSTANCE trigger raised unhandled exception ORA-06502
I compiled below items using adadmin. But the problem is still there.
Forms PL/SQL library (.pll)
Forms menu
Generate message files
Generate product JAR files
Forms executable
I also tried "Help > Diagnostics > Trace > Trace with Binds and Waits" to enable forms trace, but did not find any ORA- error in the trace log.
The problem was that one file CUSTOM.pll in $AU_TOP/resource was not owned by appl_mgr, but was owned by a developer who copied it to the server. Because of that, adpatch did not compile it during patching and later adadmin refused to compile it also when I ran adadmin to compile all library files.
After I manually compile it, the popup window does not show up on any forms!
$ export FORMS_PATH=$FORMS_PATH:$AU_TOP/forms/US:$AU_TOP/resource
$ cd $AU_TOP/resource
$ frmcmp_batch.sh module=CUSTOM.pll userid=apps/appsPWD module_type=library compile_all=special
ORA-01403: no data found
FRM-40735 WHEN-NEW-ITEM-INSTANCE trigger raised unhandled exception ORA-06502
I compiled below items using adadmin. But the problem is still there.
Forms PL/SQL library (.pll)
Forms menu
Generate message files
Generate product JAR files
Forms executable
I also tried "Help > Diagnostics > Trace > Trace with Binds and Waits" to enable forms trace, but did not find any ORA- error in the trace log.
The problem was that one file CUSTOM.pll in $AU_TOP/resource was not owned by appl_mgr, but was owned by a developer who copied it to the server. Because of that, adpatch did not compile it during patching and later adadmin refused to compile it also when I ran adadmin to compile all library files.
After I manually compile it, the popup window does not show up on any forms!
$ export FORMS_PATH=$FORMS_PATH:$AU_TOP/forms/US:$AU_TOP/resource
$ cd $AU_TOP/resource
$ frmcmp_batch.sh module=CUSTOM.pll userid=apps/appsPWD module_type=library compile_all=special
CUSTOM.pll timestamp will change and its file size may increase after compilation. Use command to check and compare file contents:
$ adident Header $AU_TOP/resource/CUSTOM.pll
Tuesday, July 31, 2018
adpatch: Drop FND_INSTALL_PROCESSES table [No] ?
When using adpatch to apply a patch, it reports an issue and I had to make the choice:
Creating the AD_UTIL_PARAMS table...
Table AD_UTIL_PARAMS already exists, so dropping the table and recreating it.
Creating FND_INSTALL_PROCESSES table...
The table FND_INSTALL_PROCESSES created by AD Administration already exists.
If you are sure you do not want to keep the information from the failed
AD Administration session,you may drop FND_INSTALL_PROCESSES table and
continue with AutoPatch.
If you choose not to drop FND_INSTALL_PROCESSES table, AutoPatch
cannot continue successfully.
Drop FND_INSTALL_PROCESSES table [No] ?
The problem is an error from a previous session of adadmin. But the error was ignored.
After I answered Yes in above adpatch session, the session completed with the real error.
AutoPatch error:
The following ORACLE error:
ORA-04045: errors during recompilation/revalidation of SYSTEM.LOGIN_AUDIT
ORA-01031: insufficient privileges
ORA-01434: private synonym to be dropped does not exist
occurred while executing the SQL statement:
DROP SYNONYM AD_DEFERRED_JOBS
AutoPatch error:
Error dropping FND_INSTALL_PROCESSES table
You should check the file
$APPL_TOP/admin/<SID>/log/xxxxx.log
for errors.
I checked back adadmin log and saw adadmin had the same error which is caused by custom trigger SYSTEM.LOGIN_AUDIT in the database. If the problem is not fixed, AD utilities (adadmin, adpatch, etc) will keep getting the error. After I dropped the trigger from database, the problem does not show up again.
The lesson is I should check adadmin log carefully even for a simple run.
Creating the AD_UTIL_PARAMS table...
Table AD_UTIL_PARAMS already exists, so dropping the table and recreating it.
Creating FND_INSTALL_PROCESSES table...
The table FND_INSTALL_PROCESSES created by AD Administration already exists.
If you are sure you do not want to keep the information from the failed
AD Administration session,you may drop FND_INSTALL_PROCESSES table and
continue with AutoPatch.
If you choose not to drop FND_INSTALL_PROCESSES table, AutoPatch
cannot continue successfully.
Drop FND_INSTALL_PROCESSES table [No] ?
The problem is an error from a previous session of adadmin. But the error was ignored.
After I answered Yes in above adpatch session, the session completed with the real error.
AutoPatch error:
The following ORACLE error:
ORA-04045: errors during recompilation/revalidation of SYSTEM.LOGIN_AUDIT
ORA-01031: insufficient privileges
ORA-01434: private synonym to be dropped does not exist
occurred while executing the SQL statement:
DROP SYNONYM AD_DEFERRED_JOBS
AutoPatch error:
Error dropping FND_INSTALL_PROCESSES table
You should check the file
$APPL_TOP/admin/<SID>/log/xxxxx.log
for errors.
I checked back adadmin log and saw adadmin had the same error which is caused by custom trigger SYSTEM.LOGIN_AUDIT in the database. If the problem is not fixed, AD utilities (adadmin, adpatch, etc) will keep getting the error. After I dropped the trigger from database, the problem does not show up again.
The lesson is I should check adadmin log carefully even for a simple run.
Friday, July 13, 2018
SQL scripts for checking database tablespce
When a database tablespace is full, EBS will not work correctly. For example, I got below warnings from adpatch:
AutoPatch warning:
Error while Inserting into temp table (AD_PATCH_HIST_TEMP)
AutoPatch warning:
ORA-01653: unable to extend table APPLSYS.AD_PATCH_HIST_TEMP by 16 in tablespace APPS_TS_INTERFACE
Unable to Create Patch Objects
Error calling adphistStoreCurPatchHistInDB2
That came from last stage of applying a patch, and seems it is ignorable. But, it is good idea to check tablespace availability before applying apps patch.
Here are scripts check if free space (in 11g and 12c) is available:
SQL> SELECT ddf.tablespace_name,
SUM( distinct ddf.ddfbytes )/1048576 avail_MB,
SUM( NVL( ds.bytes , 0 ) / 1048576 ) used_MB
FROM
( SELECT tablespace_name, SUM( bytes ) ddfbytes
FROM dba_data_files
GROUP BY tablespace_name ) ddf,
dba_segments ds
WHERE ddf.tablespace_name = ds.tablespace_name (+)
GROUP BY ddf.tablespace_name
;
SQL> select * from dba_data_files;
SQL> select * from dba_temp_files;
SQL> select tablespace_name,
(free_blocks*8)/1024/1024 FreeSpaceGB,
(used_blocks*8)/1024/1024 UsedSpaceGB,
(total_blocks*8)/1024/1024 TotalSpaceGB,
i.instance_name, i.host_name
from v$sort_segment ss, v$instance i
where ss.tablespace_name in
(select tablespace_name from dba_tablespaces where contents='TEMPORARY')
;
Besides full tablespace, disk space for database archive logs could be full. In that case, the EBS login page may give error:
Unable to generate forwarding URL. Exception: oracle.apps.fnd.common.AppsException: oracle.apps.fnd.common.PoolException: Exception creating new Poolable object.
And Sqlplus login may fail with
ERROR:
ORA-00257: archiver error. Connect internal only, until freed.
When there is a tablespace or database problem, the EBS login page may hang and then give error in IE:
Internal Server Error
The server encountered an internal error or misconfiguration and was unable to complete your request.
Please contact the server administrator, appl_mgr@xxxx.com and inform them of the time the error occurred, and anything you might have done that may have caused the error.
More information about this error may be available in the server error log.
AutoPatch warning:
Error while Inserting into temp table (AD_PATCH_HIST_TEMP)
AutoPatch warning:
ORA-01653: unable to extend table APPLSYS.AD_PATCH_HIST_TEMP by 16 in tablespace APPS_TS_INTERFACE
Unable to Create Patch Objects
Error calling adphistStoreCurPatchHistInDB2
That came from last stage of applying a patch, and seems it is ignorable. But, it is good idea to check tablespace availability before applying apps patch.
Here are scripts check if free space (in 11g and 12c) is available:
SQL> SELECT ddf.tablespace_name,
SUM( distinct ddf.ddfbytes )/1048576 avail_MB,
SUM( NVL( ds.bytes , 0 ) / 1048576 ) used_MB
FROM
( SELECT tablespace_name, SUM( bytes ) ddfbytes
FROM dba_data_files
GROUP BY tablespace_name ) ddf,
dba_segments ds
WHERE ddf.tablespace_name = ds.tablespace_name (+)
GROUP BY ddf.tablespace_name
;
SQL> select * from dba_data_files;
SQL> select * from dba_temp_files;
SQL> select tablespace_name,
(free_blocks*8)/1024/1024 FreeSpaceGB,
(used_blocks*8)/1024/1024 UsedSpaceGB,
(total_blocks*8)/1024/1024 TotalSpaceGB,
i.instance_name, i.host_name
from v$sort_segment ss, v$instance i
where ss.tablespace_name in
(select tablespace_name from dba_tablespaces where contents='TEMPORARY')
;
Besides full tablespace, disk space for database archive logs could be full. In that case, the EBS login page may give error:
Unable to generate forwarding URL. Exception: oracle.apps.fnd.common.AppsException: oracle.apps.fnd.common.PoolException: Exception creating new Poolable object.
And Sqlplus login may fail with
ERROR:
ORA-00257: archiver error. Connect internal only, until freed.
When there is a tablespace or database problem, the EBS login page may hang and then give error in IE:
Internal Server Error
The server encountered an internal error or misconfiguration and was unable to complete your request.
Please contact the server administrator, appl_mgr@xxxx.com and inform them of the time the error occurred, and anything you might have done that may have caused the error.
More information about this error may be available in the server error log.
Tuesday, May 15, 2018
Run CONCSUB from command line
Assume Oracle EBS was installed on server node1q under /u06/app/
$ echo $APPL_TOP
/u06/app/EBSQA/apps/apps_st/appl
$ echo $ORACLE_HOME
/u06/app/EBSQA/apps/tech_st/10.1.2
$ echo $TWO_TASK
EBSQA
$ cd $FND_TOP/bin
$ ls -al CONCSUB
-rwxr-xr-x 1 app ora 1033192 Aug 28 2011 CONCSUB
Now, business users want to schedule shell scripts to submit concurrent jobs and then use their output files to complete other tasks. To avoid sharing the Applmgr account with business users, a separate account is used to run concurrent jobs on the EBS concurrent node.
1. Create OS user batchuser on the CM host node1q
2. Install Oracle client on node1q but under a different directory
$ whoami
batchuser
$ echo $ORACLE_HOME
/batchu01/app/product/11.2.0/EBIZ
3. Setup env variables (or put all lines into file setenv.ebsqa)
$ more setenv.ebsqa
. /u06/app/EBSQA/apps/apps_st/appl/EBSQA_node1q.env
# Notes: above file does not include below variables
# Don't run /u06/app/EBSQA/apps/apps_st/appl/APPSEBSQA_node1q.env
export PATH=$PATH:/batchu01/app/product/11.2.0/EBIZ/bin
export ORACLE_HOME=/batchu01/app/product/11.2.0/EBIZ
export TNS_ADMIN=/batchu01/app/product/11.2.0/EBIZ/network/admin
export ORACLE_SID=EBSQA
export TWO_TASK=EBSQA
export LD_LIBRARY_PATH=/batchu01/app/product/11.2.0/EBIZ/lib:$LD_LIBRARY_PATH
echo `which CONCSUB`
echo `which sqlldr`
echo $TWO_TASK
echo $ORACLE_HOME
$ . setenv.ebsqa
/u06/app/EBSQA/apps/apps_st/appl/fnd/12.0.0/bin/CONCSUB
/batchu01/app/product/11.2.0/EBIZ/bin/sqlldr
EBSQA
/batchu01/app/product/11.2.0/EBIZ
4. Make sure entry EBSQA is defined in file $TNS_ADMIN/tnsnames.ora
5. Copy file libclntsh.so.10.1 from EBS installation ORACLE_HOME/lib (i.e. /u06/app/EBSQA/apps/tech_st/10.1.2/lib) to
/batchu01/app/product/11.2.0/EBIZ/lib
$ cd /batchu01/app/product/11.2.0/EBIZ/lib
$ ls -al libclntsh.so.10.1
-rwxr-xr-x 1 batchuser batch 13696149 Aug 4 2015 libclntsh.so.10.1
Notes: this step will avoid below error:
CONCSUB: error while loading shared libraries: libclntsh.so.10.1: cannot open shared object file: No such file or directory
It may hit below error if the file version is wrong:
CONCSUB: error while loading shared libraries: libclntsh.so.10.1: wrong ELF class: ELFCLASS64
6. Submit the concurrent job as Linux user batchuser
$ whoami
batchuser
$ CONCSUB APPS/appsPWD SYSADMIN "System Administrator" u34567 WAIT=Y CONCURRENT FND FNDSCURS PROGRAM_NAME='"Active Users"'
Submitted request 33514235 for CONCURRENT FND FNDSCURS PROGRAM_NAME="Active Users"
Normal completion
7. Submit the concurrent job by putting one line to a shell script. This can be done by saving appsPWD to a flat file.
$ more /home/batchuser/pwd/apps.pwd
appsPWD
$ submission=$(CONCSUB APPS SYSADMIN "System Administrator" u34567 WAIT=Y CONCURRENT FND FNDSCURS PROGRAM_NAME='"Active Users"' < /home/batchuser/pwd/apps.pwd)
$ echo $submission
ORACLE Password: Submitted request 33514294 for CONCURRENT FND FNDSCURS PROGRAM_NAME="Active Users" Normal completion
$ reqno=$( echo ${submission#*Submitted request}|awk {'print $1'} )
$ echo $reqno
33514294
$ ls -al $APPLCSF/out/*33514294*.*
-rw-r--r-- 1 apps ora 25613 May 15 23:57 /path/to/conc/out/o33514294.out
Notes: It may cause a security issue from saving appsPWD on a flat file. Do not forget to secure it.
$ ls -al /home/batchuser/pwd/apps.pwd
-rw------- 1 batchuser batch 12 Aug 17 2016 apps.pwd
If multiple users need to run concurrent jobs on command line, you may create and assign them to a new OS group, and give the rw- permission on file apps.pwd to that group as well.
Concurrent job can also be submitted from SQL statement by calling FND_REQUEST.SUBMIT_REQUEST.
$ echo $APPL_TOP
/u06/app/EBSQA/apps/apps_st/appl
$ echo $ORACLE_HOME
/u06/app/EBSQA/apps/tech_st/10.1.2
$ echo $TWO_TASK
EBSQA
$ cd $FND_TOP/bin
$ ls -al CONCSUB
-rwxr-xr-x 1 app ora 1033192 Aug 28 2011 CONCSUB
Now, business users want to schedule shell scripts to submit concurrent jobs and then use their output files to complete other tasks. To avoid sharing the Applmgr account with business users, a separate account is used to run concurrent jobs on the EBS concurrent node.
1. Create OS user batchuser on the CM host node1q
2. Install Oracle client on node1q but under a different directory
$ whoami
batchuser
$ echo $ORACLE_HOME
/batchu01/app/product/11.2.0/EBIZ
3. Setup env variables (or put all lines into file setenv.ebsqa)
$ more setenv.ebsqa
. /u06/app/EBSQA/apps/apps_st/appl/EBSQA_node1q.env
# Notes: above file does not include below variables
# Don't run /u06/app/EBSQA/apps/apps_st/appl/APPSEBSQA_node1q.env
export PATH=$PATH:/batchu01/app/product/11.2.0/EBIZ/bin
export ORACLE_HOME=/batchu01/app/product/11.2.0/EBIZ
export TNS_ADMIN=/batchu01/app/product/11.2.0/EBIZ/network/admin
export ORACLE_SID=EBSQA
export TWO_TASK=EBSQA
export LD_LIBRARY_PATH=/batchu01/app/product/11.2.0/EBIZ/lib:$LD_LIBRARY_PATH
echo `which CONCSUB`
echo `which sqlldr`
echo $TWO_TASK
echo $ORACLE_HOME
$ . setenv.ebsqa
/u06/app/EBSQA/apps/apps_st/appl/fnd/12.0.0/bin/CONCSUB
/batchu01/app/product/11.2.0/EBIZ/bin/sqlldr
EBSQA
/batchu01/app/product/11.2.0/EBIZ
4. Make sure entry EBSQA is defined in file $TNS_ADMIN/tnsnames.ora
5. Copy file libclntsh.so.10.1 from EBS installation ORACLE_HOME/lib (i.e. /u06/app/EBSQA/apps/tech_st/10.1.2/lib) to
/batchu01/app/product/11.2.0/EBIZ/lib
$ cd /batchu01/app/product/11.2.0/EBIZ/lib
$ ls -al libclntsh.so.10.1
-rwxr-xr-x 1 batchuser batch 13696149 Aug 4 2015 libclntsh.so.10.1
Notes: this step will avoid below error:
CONCSUB: error while loading shared libraries: libclntsh.so.10.1: cannot open shared object file: No such file or directory
It may hit below error if the file version is wrong:
CONCSUB: error while loading shared libraries: libclntsh.so.10.1: wrong ELF class: ELFCLASS64
6. Submit the concurrent job as Linux user batchuser
$ whoami
batchuser
$ CONCSUB APPS/appsPWD SYSADMIN "System Administrator" u34567 WAIT=Y CONCURRENT FND FNDSCURS PROGRAM_NAME='"Active Users"'
Submitted request 33514235 for CONCURRENT FND FNDSCURS PROGRAM_NAME="Active Users"
Normal completion
7. Submit the concurrent job by putting one line to a shell script. This can be done by saving appsPWD to a flat file.
$ more /home/batchuser/pwd/apps.pwd
appsPWD
$ submission=$(CONCSUB APPS SYSADMIN "System Administrator" u34567 WAIT=Y CONCURRENT FND FNDSCURS PROGRAM_NAME='"Active Users"' < /home/batchuser/pwd/apps.pwd)
$ echo $submission
ORACLE Password: Submitted request 33514294 for CONCURRENT FND FNDSCURS PROGRAM_NAME="Active Users" Normal completion
$ reqno=$( echo ${submission#*Submitted request}|awk {'print $1'} )
$ echo $reqno
33514294
$ ls -al $APPLCSF/out/*33514294*.*
-rw-r--r-- 1 apps ora 25613 May 15 23:57 /path/to/conc/out/o33514294.out
Notes: It may cause a security issue from saving appsPWD on a flat file. Do not forget to secure it.
$ ls -al /home/batchuser/pwd/apps.pwd
-rw------- 1 batchuser batch 12 Aug 17 2016 apps.pwd
If multiple users need to run concurrent jobs on command line, you may create and assign them to a new OS group, and give the rw- permission on file apps.pwd to that group as well.
Concurrent job can also be submitted from SQL statement by calling FND_REQUEST.SUBMIT_REQUEST.
Friday, April 27, 2018
Enable BNE / WebADI debug trace
Instructions for BNE debug (in R12.1.3):
1) Login to System Administrator Responsibility.
2) Open the System -> Profiles form.
3) Search for %BNE%LOG% in the profiles.
This will return three profiles:
BNE Debug Log Level -- This should be set to "TRACE" (default "ERROR". all caps)
BNE Debug Log Filename -- The log file name should be specified here (eg: "bne2018.log")
BNE Debug Log Directory - -The log file directory should be specified here.
NOTE: The log file should exist, even if empty. Application will not create a new file. Create an empty log file at the location specified in BNE Debug Log Directory like (eg: "bne2018.log"). A services recycle may be needed to make the changes effective.
Once login as System Administrator, paste below URL to same browser window to view details, including the log:
https://hostname.domain:portnumber/OA_HTML/BneAdminServlet
https://hostname.domain:portnumber/OA_HTML/BneAdminServlet?bne:action=log-first&bne:lines=10
https://hostname.domain:portnumber/OA_HTML/BneAdminServlet?bne:action=log-last&bne:lines=10
To reproduce an issue:
(a). Click the "clear-cache" link.
(b). For the attribute "log-set-level" (Current JVM only) , click on TRACE
(c). Run through reproducible case
(d). examine/upload the log
All BNE profile settings in my instance:
1) Login to System Administrator Responsibility.
2) Open the System -> Profiles form.
3) Search for %BNE%LOG% in the profiles.
This will return three profiles:
BNE Debug Log Level -- This should be set to "TRACE" (default "ERROR". all caps)
BNE Debug Log Filename -- The log file name should be specified here (eg: "bne2018.log")
BNE Debug Log Directory - -The log file directory should be specified here.
NOTE: The log file should exist, even if empty. Application will not create a new file. Create an empty log file at the location specified in BNE Debug Log Directory like (eg: "bne2018.log"). A services recycle may be needed to make the changes effective.
Once login as System Administrator, paste below URL to same browser window to view details, including the log:
https://hostname.domain:portnumber/OA_HTML/BneAdminServlet
https://hostname.domain:portnumber/OA_HTML/BneAdminServlet?bne:action=log-first&bne:lines=10
https://hostname.domain:portnumber/OA_HTML/BneAdminServlet?bne:action=log-last&bne:lines=10
To reproduce an issue:
(a). Click the "clear-cache" link.
(b). For the attribute "log-set-level" (Current JVM only) , click on TRACE
(c). Run through reproducible case
(d). examine/upload the log
All BNE profile settings in my instance:
NAME
|
PROF_LEVEL
|
PROFILE_OPTION_VALUE
|
DESCRIPTION
|
BNE: ALLOW MAX DELIMITER
|
SITE
|
Y
|
To Allow maximum delimiter
character to import text files in the integrator.
|
BNE Allow No Security Rule
|
SITE
|
N
|
Allow applications that have not
defined a security rule to function
|
BNE Allow Set Parameters
|
SITE
|
Y
|
Controls access to the Upload
Parameters window
|
BNE Allow Set Parameters
|
App:IGS
|
Y
|
Controls access to the Upload
Parameters window
|
BNE Disable
|
NOT SET
|
NOT SET
|
Disables all Web ADI functionality
|
BNE Viewer Displays Image
|
SITE
|
Y
|
Excel Viewer does display default
FND display image in WebADI documents created
|
BNE Document Lifetime
|
NOT SET
|
NOT SET
|
How long do documents live before
they are automatically disabled.
|
BNE Enable Digital Signature
|
SITE
|
N
|
|
BNE Enforce Parameter Values
|
NOT SET
|
NOT SET
|
Sets whether a user can make an
alternate value selection where the request parameter is not valid
|
BNE Offline List of Values Limit
|
NOT SET
|
NOT SET
|
Limit on the number of values to
be downloaded for an offline List of Values
|
BNE Redirect Portal URL
|
NOT SET
|
NOT SET
|
URL of location Web ADI will
redirect to.
|
BNE Debug Log Filename
|
SITE
|
bns2018.log
|
Name of the Web ADI log file. If not set, file name is bne.log
|
BNE Debug Log Level
|
SITE
|
TRACE
|
Level of logging recorded in the
Web ADI log file
|
BNE Debug Log Directory
|
SITE
|
/path/to/utl_dir
|
Directory for Web ADI log file on
application server
|
BNE UIX Base Path
|
NOT SET
|
NOT SET
|
Web server path to the uix/cabo
base.
|
BNE UIX Physical Directory
|
NOT SET
|
NOT SET
|
Physical directory that is mapped
to BNE_UIX_BASE_PATH.
|
BNE Upload Batch Size
|
SITE
|
200
|
Number of uploaded rows in each
batch sent from the middle tier to the database
|
BNE: Enable Upload Compression
|
NOT SET
|
NOT SET
|
Enable Upload Compression for the
Instance
|
BNE Upload Staging Directory
|
SITE
|
/path/to/bne/upload
|
Physical file system directory for
upload files
|
BNE Upload Text Directory
|
NOT SET
|
NOT SET
|
Physical file system directory for
imported text files.
|
BNE Excel Worksheet Maximum
|
NOT SET
|
NOT SET
|
Allows users to set the maximum
number of worksheets for a workbook
|
BNE XML Response Compression
|
NOT SET
|
NOT SET
|
Allows compression XML data
|
Tuesday, April 24, 2018
Patching to enable Java Web Start (JWS) for EBS R12.1.3
The purpose is to make EBS forms work with browsers on Windows 10 (win10). Before this task, my EBS instances work with Microsoft IE11 and JRE 1.8 only on Windows 7 client. The key Oracle document to follow is Using Java Web Start with Oracle E-Business Suite (Doc ID 2188898.1).
If s_forms_launch_method does exist in $CONTEXT_FILE, that means JWS is not enabled in the instance. Otherwise, it shall have:
$ grep s_forms_launch_method $CONTEXT_FILE
<config_option type="techstack" oa_var="s_forms_launch_method">jws</config_option>
First of all, run query to see how many pre-patches are needed. Make sure first 6 patches were applied. If not, apply them first. After all steps, all below 12 patches shall be installed.
select * from ad_bugs where bug_number in (
'9239090', -- 12.1.3 Release Update Pack (applied)
'22284589', -- R12.FWK.B.delta.5 (applied with OCT17 CPU)
'17932167', -- R12.TXK.B.delta.3 (applied with OCT17 CPU)
'24693065', -- R12.AD.B (Doc ID 2166227.1) (applied with OCT17 CPU)
'8919491', -- R12.ATG_PF.B.delta.3 (applied)
'7461070', -- R12.AD.B.1 (pre-requisite for patch 23569686) (applied)
'23569686', -- R12.AD.B.delta.8 (Doc ID 2166227.1)
'24498616', -- R12.AD.B AD: Add Java Web Start support to EBS
'24319156', -- R12.TXK.B TXK: Add Java Web Start support to EBS
'25380324', -- R12.FND.B EBS Java Applets launching with Java Web Start
'24709902', -- R12.OWF.B Workflow Java Applets launching with Java Web Start
'25140916' -- R12.GL.B GL Account Hierarchy Manager
) order by creation_date desc
;
1. Backup file system, and copy/download patch .zip files to a same folder
2. Verify patch 26825525 was applied to 10.1.2 ORACLE_HOME. If it not, apply it.
Notes: (a) after 26825525 was applied, patch 25441839 is not needed (and actually opatch will roll back 26825525 if you try to apply 25441839).
(b) It is part of October 2017 CPU and also part of January 2018 CPU patch.
(c) Its prerequisite patch: 14825718 ORACLE FORMS BUNDLE PATCH 10.1.2.3.2
$ echo $ORACLE_HOME
/path/to/apps/tech_st/10.1.2
$ vi /etc/oraInst.loc
to make sure it points to the right location
$ opatch lsinventory | grep 26825525
1) Patch 26825525 applied on Mon Dec 11 12:59:06 EST 2017
3. Apply AD patch 23569686 R12.AD.B.delta.8
Confirmed with DBA that database patch 19393542 is not needed after OCT2018 PSU patch was applied.
$ adadmin (to turn on Maintenance Mode)
$ unzip p23569686_R12.AD.B_R12_LINUX.zip
$ cd 23569686
$ ls
$ adpatch
Post Step: patch 23569686 has a post step (Doc ID 2166227.1)
$ adadmin => 3. Compile/Reload Applications Database Entities menu => 1. Compile APPS schema.
Type "No" when prompted for:
Run Invoker Rights processing in incremental mode [No]
4. Apply AD patch 24498616 R12.AD.B
$ unzip p24498616_R12.AD.B_R12_GENERIC.zip
$ cd 24498616
$ ls
$ adpatch (it runs very quick)
5. Merged 4 patches and then apply it (see Step 12 also)
24319156 R12.TXK.B
25380324 R12.FND.B
24709902 R12.OWF.B
25140916 R12.GL.B
Did NOT need below step in 12.1.0.2 database after OCT2017 PSU patch was applied:
Additionally the following parameter need to be set to "true" before applying this patch
"_plsql_conditional_compilation"=true ( refer note 338821.1)
(From README of patch 25380324, 24709902, 25140916)
$ unzip win10_merge.zip
$ cd win10_merge
$ ls
$ adpatch
6. Apply JRE patch (to web tier).
Follow ID 393931.1 "Deploying JRE (Native Plug-in) for Windows Clients in Oracle E-Business Suite Release 12" to find the latest release. Download the 32-bit for MS Windows patch.
Patch 27638638 for 1.8.0_162. Or
Patch 26595878 for 1.8.0_152 (then, run below lines)
$ cp -p jre-8u152-windows-i586.exe $COMMON_TOP/webapps/oacore/util/jinitiator/j2se18152.exe
$ $FND_TOP/bin/txkSetPlugin.sh 18152
7. Enable JWS (Java Web Start)
$ vi $CONTEXT_FILE
from <config_option type="techstack" oa_var="s_forms_launch_method">browser</config_option>
to <config_option type="techstack" oa_var="s_forms_launch_method">jws</config_option>
8. Fix bug 27138793 : UNABLE TO LAUNCH FORMS AFTER APLYING JWS RELATED PATCHES (to avoid ORA-28040: No matching authentication protocol from launching forms session)
Add following 4 lines to file $ORA_CONFIG_HOME/10.1.3/j2ee/forms/config/server.xml
<shared-library name="oracle.jdbc" version="10.2.0.2" library-compatible="true">
<code-source path="$COMMON_TOP/java/lib/jdbc6.zip"/>
<code-source path="$COMMON_TOP/java/lib/orai18n.zip"/>
</shared-library>
above the last line and replace $COMMON_TOP with real path. If it hits error, check file $LOG_HOME/ora/10.1.3/j2ee/forms/forms_default_group_1/server.log
To preserve that customization from overwritten by autoconfig:
(a). Backup current server.xml file.
(b). Create a folder called "custom" under $FND_TOP/admin/template where forms_server_xml_1013.tmp exists.
(c). Copy forms_server_xml_1013.tmp to the custom folder. (copy and do not move)
(d). Add 4 lines of code to file custom/forms_server_xml_1013.tmp
9. Run autoconfig (and verify customization was saved in server.xml).
10. Start services
$ adadmin (to disable maintenance mode)
$ ./adstrtal.sh apps/XXXXXX
11. EBS works in browsers (Google Chrome 63.0.3239.xxxx, Firefox 57.0.2, and Microsoft Edge 38.14393.xxx) on Windows 10, and also IE 11 on Windows 7 (with JRE 1.8.0_162 pre-installed) !
It matches results in Doc ID 2188898.1 Section 6: Browser User Experience and Configuration.
Troubleshooting:
- If it gives white forms with message Oracle Corporation Oracle Forms Java Webstart Oracle Forms Applet in popup window, one possible cause could be that frmsvr.jar file got corrupted and EAR file did not get deployed successfully. Check $LOG_HOME/appl/admin/log/adstrtal.log carefully.
- Some custom group policy in Windows may block downloading JWS frmservlet.jnlp.
- On a Windows 7 VM machine with JRE 1.6 installed, IE 11 failed to launch EBS forms. It gives a popup: Unable to launch the application
Error: The application has requested a version of the JRE (version 1.8+) that currently is not locally installed. Java Web Start is unable to automatically download and install the requested version.
JRE 1.8 must be installed on the machine before JWS forms fire up.
12. Depending on EBS modules used, more patches may be required in Step 5. I did not apply below patches.
SQL> select * from ad_bugs where bug_number in (
-- OPM
'22598196', -- R12.WMS.B.delta.14
'23256716', -- R12.OPM_PF.B.Delta.7 (Oracle Process Manufacturing)
'24833534', -- OPM: Bug fix. It needs above two as pre-requisites
--
'23280764', -- IMC: Add JWS Support to Visualization UI Java applet
'23475123', -- IES: Add JWS Support to Scripting Author Java applet
'23575933', -- PON: Add JWS Support to Graph Price Java applet
'25075128', -- CSI: Add JWS Support to Visualizer Java applet
'25106959' -- MSC: Add Java Web Start Support to PS/SNO 12.1.3
);
NOTES: Doc ID 389422.1 also lists all necessary patches for browsers. 10 patches exist in my instances:
select to_char(last_update_date,'dd-Mon-yyyy hh:mi:ss PM'), A.* from ad_bugs A where bug_number in (
'9239090',
'17932167',
'18936791',
'21565052',
'20986782', -- No patch 20986782. It supersedes the previously recommended patch 19659562.
'19659562', -- Patch 19659562 was applied in 2015 for IE11. Seems both are not necessary if CRM is not used.
'19486870',
'21482112',
'19273341',
'19559960',
'22284589'
)
order by creation_date desc;
NOTES:
Some users (not all users) could not save/download frmservlet.jnlp file to their PC and so could not open forms. We found that was due to corporate group policy settings on them and was not an EBS issue.
After EBS Forms worked with JWS for a couple of weeks, Forms popup window did not launch and gave error message in Chrome:
URL validation failed. The error could have been caused through the use of the browser's navigation buttons ( the browser Back button or refresh, for example). If the error persists, Please contact system administrator.
I see below message in $LOG_HOME/ora/10.1.3/j2ee/forms/forms_default_group_1/application.log, but I do not know what it means:
formsweb: oracle.apps.fnd.security.AppsFrmservletFilter.validateExtraParams:Ticket validation failed stored=null URLqs=-1631732614
After EBS services were bounced, this intermittent Forms problem did not show up. Oracle Support did not find what was the cause. I believe the cause could be unstable/interrupted database connection.
Patches applied for JWS will make EBS R12.1.3 not support multiple forms sessions from same instance on one desktop. Oracle says this is to address security vulnerability.
After JRE1.8 was installed, other applications may use it as well. For example, after JRE was upgraded from 1.8.0_241 to 1.8.0_261, Oracle SQL Developer got error Unable to launch the Java Virtual Machine Located at path: C:\Program Files(x86)\Java\jdk18\jre\bin\msvcr100.dll. The reason is JRE 1.8.0_261 does not ship msvcr100.dll due to change in Java Product strategy. The workaround is to copy msvcr100.dll from C:\Windows\SysWOW64 to C:\Program Files (x86)\Java\jdk18\jre\bin folder.
If s_forms_launch_method does exist in $CONTEXT_FILE, that means JWS is not enabled in the instance. Otherwise, it shall have:
$ grep s_forms_launch_method $CONTEXT_FILE
<config_option type="techstack" oa_var="s_forms_launch_method">jws</config_option>
First of all, run query to see how many pre-patches are needed. Make sure first 6 patches were applied. If not, apply them first. After all steps, all below 12 patches shall be installed.
select * from ad_bugs where bug_number in (
'9239090', -- 12.1.3 Release Update Pack (applied)
'22284589', -- R12.FWK.B.delta.5 (applied with OCT17 CPU)
'17932167', -- R12.TXK.B.delta.3 (applied with OCT17 CPU)
'24693065', -- R12.AD.B (Doc ID 2166227.1) (applied with OCT17 CPU)
'8919491', -- R12.ATG_PF.B.delta.3 (applied)
'7461070', -- R12.AD.B.1 (pre-requisite for patch 23569686) (applied)
'23569686', -- R12.AD.B.delta.8 (Doc ID 2166227.1)
'24498616', -- R12.AD.B AD: Add Java Web Start support to EBS
'24319156', -- R12.TXK.B TXK: Add Java Web Start support to EBS
'25380324', -- R12.FND.B EBS Java Applets launching with Java Web Start
'24709902', -- R12.OWF.B Workflow Java Applets launching with Java Web Start
'25140916' -- R12.GL.B GL Account Hierarchy Manager
) order by creation_date desc
;
1. Backup file system, and copy/download patch .zip files to a same folder
2. Verify patch 26825525 was applied to 10.1.2 ORACLE_HOME. If it not, apply it.
Notes: (a) after 26825525 was applied, patch 25441839 is not needed (and actually opatch will roll back 26825525 if you try to apply 25441839).
(b) It is part of October 2017 CPU and also part of January 2018 CPU patch.
(c) Its prerequisite patch: 14825718 ORACLE FORMS BUNDLE PATCH 10.1.2.3.2
$ echo $ORACLE_HOME
/path/to/apps/tech_st/10.1.2
$ vi /etc/oraInst.loc
to make sure it points to the right location
$ opatch lsinventory | grep 26825525
1) Patch 26825525 applied on Mon Dec 11 12:59:06 EST 2017
3. Apply AD patch 23569686 R12.AD.B.delta.8
Confirmed with DBA that database patch 19393542 is not needed after OCT2018 PSU patch was applied.
$ adadmin (to turn on Maintenance Mode)
$ unzip p23569686_R12.AD.B_R12_LINUX.zip
$ cd 23569686
$ ls
$ adpatch
Post Step: patch 23569686 has a post step (Doc ID 2166227.1)
$ adadmin => 3. Compile/Reload Applications Database Entities menu => 1. Compile APPS schema.
Type "No" when prompted for:
Run Invoker Rights processing in incremental mode [No]
4. Apply AD patch 24498616 R12.AD.B
$ unzip p24498616_R12.AD.B_R12_GENERIC.zip
$ cd 24498616
$ ls
$ adpatch (it runs very quick)
5. Merged 4 patches and then apply it (see Step 12 also)
24319156 R12.TXK.B
25380324 R12.FND.B
24709902 R12.OWF.B
25140916 R12.GL.B
Did NOT need below step in 12.1.0.2 database after OCT2017 PSU patch was applied:
Additionally the following parameter need to be set to "true" before applying this patch
"_plsql_conditional_compilation"=true ( refer note 338821.1)
(From README of patch 25380324, 24709902, 25140916)
$ unzip win10_merge.zip
$ cd win10_merge
$ ls
$ adpatch
6. Apply JRE patch (to web tier).
Follow ID 393931.1 "Deploying JRE (Native Plug-in) for Windows Clients in Oracle E-Business Suite Release 12" to find the latest release. Download the 32-bit for MS Windows patch.
Patch 27638638 for 1.8.0_162. Or
Patch 26595878 for 1.8.0_152 (then, run below lines)
$ cp -p jre-8u152-windows-i586.exe $COMMON_TOP/webapps/oacore/util/jinitiator/j2se18152.exe
$ $FND_TOP/bin/txkSetPlugin.sh 18152
7. Enable JWS (Java Web Start)
$ vi $CONTEXT_FILE
from <config_option type="techstack" oa_var="s_forms_launch_method">browser</config_option>
to <config_option type="techstack" oa_var="s_forms_launch_method">jws</config_option>
8. Fix bug 27138793 : UNABLE TO LAUNCH FORMS AFTER APLYING JWS RELATED PATCHES (to avoid ORA-28040: No matching authentication protocol from launching forms session)
Add following 4 lines to file $ORA_CONFIG_HOME/10.1.3/j2ee/forms/config/server.xml
<shared-library name="oracle.jdbc" version="10.2.0.2" library-compatible="true">
<code-source path="$COMMON_TOP/java/lib/jdbc6.zip"/>
<code-source path="$COMMON_TOP/java/lib/orai18n.zip"/>
</shared-library>
above the last line and replace $COMMON_TOP with real path. If it hits error, check file $LOG_HOME/ora/10.1.3/j2ee/forms/forms_default_group_1/server.log
To preserve that customization from overwritten by autoconfig:
(a). Backup current server.xml file.
(b). Create a folder called "custom" under $FND_TOP/admin/template where forms_server_xml_1013.tmp exists.
(c). Copy forms_server_xml_1013.tmp to the custom folder. (copy and do not move)
(d). Add 4 lines of code to file custom/forms_server_xml_1013.tmp
9. Run autoconfig (and verify customization was saved in server.xml).
10. Start services
$ adadmin (to disable maintenance mode)
$ ./adstrtal.sh apps/XXXXXX
11. EBS works in browsers (Google Chrome 63.0.3239.xxxx, Firefox 57.0.2, and Microsoft Edge 38.14393.xxx) on Windows 10, and also IE 11 on Windows 7 (with JRE 1.8.0_162 pre-installed) !
It matches results in Doc ID 2188898.1 Section 6: Browser User Experience and Configuration.
Troubleshooting:
- If it gives white forms with message Oracle Corporation Oracle Forms Java Webstart Oracle Forms Applet in popup window, one possible cause could be that frmsvr.jar file got corrupted and EAR file did not get deployed successfully. Check $LOG_HOME/appl/admin/log/adstrtal.log carefully.
- Some custom group policy in Windows may block downloading JWS frmservlet.jnlp.
- On a Windows 7 VM machine with JRE 1.6 installed, IE 11 failed to launch EBS forms. It gives a popup: Unable to launch the application
Error: The application has requested a version of the JRE (version 1.8+) that currently is not locally installed. Java Web Start is unable to automatically download and install the requested version.
JRE 1.8 must be installed on the machine before JWS forms fire up.
12. Depending on EBS modules used, more patches may be required in Step 5. I did not apply below patches.
SQL> select * from ad_bugs where bug_number in (
-- OPM
'22598196', -- R12.WMS.B.delta.14
'23256716', -- R12.OPM_PF.B.Delta.7 (Oracle Process Manufacturing)
'24833534', -- OPM: Bug fix. It needs above two as pre-requisites
--
'23280764', -- IMC: Add JWS Support to Visualization UI Java applet
'23475123', -- IES: Add JWS Support to Scripting Author Java applet
'23575933', -- PON: Add JWS Support to Graph Price Java applet
'25075128', -- CSI: Add JWS Support to Visualizer Java applet
'25106959' -- MSC: Add Java Web Start Support to PS/SNO 12.1.3
);
NOTES: Doc ID 389422.1 also lists all necessary patches for browsers. 10 patches exist in my instances:
select to_char(last_update_date,'dd-Mon-yyyy hh:mi:ss PM'), A.* from ad_bugs A where bug_number in (
'9239090',
'17932167',
'18936791',
'21565052',
'20986782', -- No patch 20986782. It supersedes the previously recommended patch 19659562.
'19659562', -- Patch 19659562 was applied in 2015 for IE11. Seems both are not necessary if CRM is not used.
'19486870',
'21482112',
'19273341',
'19559960',
'22284589'
)
order by creation_date desc;
NOTES:
Some users (not all users) could not save/download frmservlet.jnlp file to their PC and so could not open forms. We found that was due to corporate group policy settings on them and was not an EBS issue.
After EBS Forms worked with JWS for a couple of weeks, Forms popup window did not launch and gave error message in Chrome:
URL validation failed. The error could have been caused through the use of the browser's navigation buttons ( the browser Back button or refresh, for example). If the error persists, Please contact system administrator.
I see below message in $LOG_HOME/ora/10.1.3/j2ee/forms/forms_default_group_1/application.log, but I do not know what it means:
formsweb: oracle.apps.fnd.security.AppsFrmservletFilter.validateExtraParams:Ticket validation failed stored=null URLqs=-1631732614
After EBS services were bounced, this intermittent Forms problem did not show up. Oracle Support did not find what was the cause. I believe the cause could be unstable/interrupted database connection.
Patches applied for JWS will make EBS R12.1.3 not support multiple forms sessions from same instance on one desktop. Oracle says this is to address security vulnerability.
After JRE1.8 was installed, other applications may use it as well. For example, after JRE was upgraded from 1.8.0_241 to 1.8.0_261, Oracle SQL Developer got error Unable to launch the Java Virtual Machine Located at path: C:\Program Files(x86)\Java\jdk18\jre\bin\msvcr100.dll. The reason is JRE 1.8.0_261 does not ship msvcr100.dll due to change in Java Product strategy. The workaround is to copy msvcr100.dll from C:\Windows\SysWOW64 to C:\Program Files (x86)\Java\jdk18\jre\bin folder.
Monday, January 22, 2018
Unable to load login page after clone
After I cloned a R12.1.3 instance on an 11g database, all EBS services started fine. But, It failed to re-direct to the login page with posting a generic error message in browsers. File
ORA-26040: Data block was loaded using the NOLOGGING option
The message is kind of misleading. I followed Doc ID 418130.1 (Unable To Login After Clone) to fix the problem. After I ran below PL/SQL procedure, the login page worked.
SQL> select count(1) from WF_LOCAL_USER_ROLES;
COUNT(1)
----------
2050
SQL> begin
apps.wf_local_synch.BULKSYNCHRONIZATION(P_ORIG_SYSTEM=>'ALL',
P_PARALLEL_PROCESSES=>2,
P_LOGGING=>'LOGGING',
P_RAISEERRORS=>TRUE,
P_TEMPTABLESPACE=>'APPS_TS_TX_DATA');
end;
/
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
SQL> exit
$LOG_HOME/ora/10.1.3/j2ee/oacore/oacore_default_group_1/application.log shows the true error:
Caused by: oracle.apps.jtf.base.resources.FrameworkException: ORA-01578: ORACLE data block corrupted (file # 45, block # 863844)
ORA-01110: data file 45:
'/path/to/a_txn_data05.dbf'Caused by: oracle.apps.jtf.base.resources.FrameworkException: ORA-01578: ORACLE data block corrupted (file # 45, block # 863844)
ORA-26040: Data block was loaded using the NOLOGGING option
The message is kind of misleading. I followed Doc ID 418130.1 (Unable To Login After Clone) to fix the problem. After I ran below PL/SQL procedure, the login page worked.
SQL> select count(1) from WF_LOCAL_USER_ROLES;
COUNT(1)
----------
2050
SQL> begin
apps.wf_local_synch.BULKSYNCHRONIZATION(P_ORIG_SYSTEM=>'ALL',
P_PARALLEL_PROCESSES=>2,
P_LOGGING=>'LOGGING',
P_RAISEERRORS=>TRUE,
P_TEMPTABLESPACE=>'APPS_TS_TX_DATA');
end;
/
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
SQL> exit
Subscribe to:
Posts (Atom)