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---------------
Thursday, August 16, 2018
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.
Subscribe to:
Comments (Atom)