Sunday, 14 June 2020

Oracle EBS 12.2.X Login fails with "Unable to create anonymous session. ICX_SESSION_CREATION_FAILED"


In a 12.2.6 EBS environment, Login fails with below error

Unable to create anonymous session. ICX_SESSION_CREATION_FAILED (userid=6) exception oracle.apps.fnd.common.PoolException: Exception creating new Poolable object. Encountered a java exception with the message Exception creating new Poolable object. Cause:java.lang.RuntimeException: java.lang.RuntimeException: java.sql.SQLException: java.lang.reflect.InvocationTargetException.


The error indicates that there are not enough resources to create a new JDBC session. oacore_server.log has the below error messages

####<Aug 08, 2017 3:43:05 PM EST> <Info> <Common> <> <oacore_server1> <[ACTIVE] ExecuteThread: '5' for queue: 'weblogic.kernel.Default (self-tuning)'> <<anonymous>> <> <005dy^V9G5e9xW6RJMU4TB0000xv001lSB> <1591994585553> <BEA-000627> <Reached maximum capacity of pool "EBSDataSource", making "0" new resource instances instead of "1".>
####<Aug 08, 2017 3:43:07 PM EST> <Info> <Common> <> <oacore_server1> <pool-2-thread-1> <<anonymous>> <> <*******************> <887561> <BEA-000627> <Reached maximum capacity of pool "EBSDataSource", making "0" new resource instances instead of "1".>
####<Aug 08, 2017 3:43:15 PM EST> <Info> <Common> <> <oacore_server1> <[ACTIVE] ExecuteThread: '5' for queue: 'weblogic.kernel.Default (self-tuning)'> <<anonymous>> <> <005dy^V9G5e9xW6RJMU4TB0000xv001lSB> <1591994595562> <BEA-000627> <Reached maximum capacity of pool "EBSDataSource", making "0" new resource instances instead of "1".>
####<Aug 08, 2017 3:43:15 PM EST> <Info> <Common> <> <oacore_server1> <pool-2-thread-1> <<anonymous>> <> <*******************> <955123> <BEA-000627> <Reached maximum capacity of pool "EBSDataSource", making "0" new resource instances instead of "1".>

Number of processes capacity for the Datasource "EBSDatasource" is exhausted.


1. Increase the number of sessions in EBSDatasource

Login to weblogic Console, Navigate to Datasource, click on EBSDatasource.

Click on Lock and Edit, Go to Connection Pool Tab, Increase the value in Number of sessions field.

Click on Activate Changes.

This change does not require any restart of services.


Alternate Solution:

2. Bounce oacore_server to release any inactive sessions and free up the resources

cd $ADMIN_SCRIPTS_HOME stop oacore_server start oacore_server

Query to check installed modules in Oracle EBS

The below query query gives the list of Installed/Not Installed/Shared Product Modules in Oracle E-Business Suite (EBS) application

set pages 20000;
col application_id for 9999;
col application_name for A50;
col status for A1;
col application_short_name for A10;
select fa.application_id,
fnd_product_installations fpi,
fnd_application fa,
fnd_application_tl fatl
fa.application_id = fpi.application_id and
fa.application_id = fatl.application_id and
fatl.language = 'US'
order by fa.application_short_name;

Check for values in Status column of the output, usually either I or N or S.

I - Installed
S - Shared
N - Not Licensed

Query to check AD and TXK Code level in a Oracle E-Business Suite environment

While preparing patch analysis on EBS environments, some times we find in README file that AD/ TXK level should be at a certain code level to apply the patch.We can use the below query to check AD and TXK Code level.

select  ABBREVIATION, NAME, codelevel FROM AD_TRACKABLE_ENTITIES where abbreviation in ('txk','ad');

Sample Output

$sqlplus apps

SQL>  select  ABBREVIATION, NAME, codelevel FROM AD_TRACKABLE_ENTITIES where abbreviation in ('txk','ad');

ABBREVIATION                   NAME                                     CODELEVEL
------------------------------ ---------------------------------------- ----------
ad                             Applications DBA                                     C.11
txk                            Oracle Applications Technology Stack     C.11

Thursday, 11 June 2020

Connecting to a Database from SQLDeveloper fails with IO Error: The Network Adapter Could Not Establish The Connection

Issue: New Users were trying to access a 3-node RAC Oracle Database system using SQLDeveloper

Below is the connection string provided to user for Oracle Database Connection. RAC Virtual IP is provided to connect to Database.


User tried to connect using one of the VIP and got the below error in SQLDeveloper.
Status : Failure -Test failed: IO Error: The Network Adapter Could Not Establish The Connection.


Verified the Oracle Database Services and Listener Services are running fine.
Other Users were able to connect to SQLDeveloper using the same Connection String.


User does not have network communication open to port 1521 on Oracle RAC Virtual IP.


telnet 1521
telnet 1521
telnet 1521

Ping to VIP address works fine but Telnet to port 1521 on the VIP address is giving "Connect Failed" Error.

When Network communication is opened on port 1521 on all the Oracle RAC Virtual IP servers/Addresses, User can connect to Oracle Database using SQLDeveloper.

Thursday, 4 June 2020

Troubleshooting concurrent requests struck at Post processing Phase

Sometimes , Eventhough the Database sessions related to the concurrent Programs are in INACTIVE State , we could not terminate the Concurrent Request with error "Could not Lock Request" , Issue could be Requests are struck at Post Processing Phase and OutPut Post Processor is locking the Concurrent Requests.

Need to follow the below steps to perform clean shutdown of Oracle Concurrent Manager.

a) Put Pending Concurrent Requests on hold using the below sql queries.

+Create table apps.conc_req_on_hold as select * from fnd_Concurrent_requests where PHASE_CODE='P' and hold_flag='N';
+select count(*) from apps.conc_req_on_hold
+ update fnd_Concurrent_requests set hold_flag='Y' where PHASE_CODE='P' and hold_flag='N' and request_id in (select request_id from apps.conc_req_on_hold);

NOTE: You have to commit if select & update are same number of records. Otherwise rollback and try again till the numbers are same

+ Commit;

You can find more details about putting Pending Concurrent Jobs on Hold here.

b)Bring Down Concurrent Manager using stop

c) Update the status of Struck Concurrent Requests to "Terminated" 

SQL> update fnd_concurrent_requests set status_code='X',phase_code='C' where status_code='R' and phase_code='R';


Use Concurrent Manager Recovery Wizard from OAM to clear the database sessions associated with cancelled Requests.

d) Start Concurrent Manager using

e) Remove Hold on Concurrent Requests

SQL>update fnd_Concurrent_requests set hold_flag='N' where request_id in (select request_id from apps.conc_req_on_hold);

Commit the changes


Steps to create Database link from Oracle Database to Microsoft SQLServer Database:

Steps to create Database link from Oracle Database to Microsoft SQLServer Database:


1. Check Network communication is Opened from Oracle Database Server to MSSQL Database Server using telnet
2. Download the Oracle Gateway Server Software same as your Oracle Database version.

Installation of Oracle Gateway Software

Hope you have downloaded the Dg4msql software for sql server 

1> Create a directory as gateway under $ORACLE_BASE directory. 

2) Invoke the GUI , Select Oracle Gateway for MYSQL and enter the sql server details at the time of installation 

3) Before completing the installation, OUI invokes the NETCA and create a new listener for gateway with new port (ex:1526) 

Note; In case if you have defined any bash profile for TNS_ADMIN, then please comment it 

4. Follow the below note for pre-req for gateway listener 

How to Configure DG4MSQL (Oracle Database Gateway for MS SQL Server) 64bit Unix OS (Linux, Solaris, AIX,HP-UX) post install ( Doc ID 562509.1 ) 

edit your gateway listener as recommended in the above note. 

5. create a tnsentry in $ORACLE_HOME/network/admin 

6. export TNS_ADMIN=$gateway home/network/admin 

lsnrctl stop <gateway listener> 

lsnrctl start <gateway listener> 

tnsping <gateway alias> 

conn / as sysdba 

create public database link <test> connect to "<sql server user>" identified by "<password>" using '<tnsnames.ora>' 

select * from dual@test; 

Script to check free space and used space in Oracle Database tablespaces

select a.tablespace_name,
       a.bytes_alloc/(1024*1024) "TOTAL ALLOC (MB)",
 a.physical_bytes/(1024*1024) "TOTAL PHYS ALLOC (MB)",
       nvl(b.tot_used,0)/(1024*1024) "USED (MB)",
       (nvl(b.tot_used,0)/a.bytes_alloc)*100 "% USED"
from ( select tablespace_name,
       sum(bytes) physical_bytes,
sum(decode(autoextensible,'NO',bytes,'YES',maxbytes)) bytes_alloc
       from dba_data_files
       group by tablespace_name ) a,
     ( select tablespace_name, sum(bytes) tot_used
       from dba_segments
group by tablespace_name ) b
where a.tablespace_name = b.tablespace_name (+)
--and   (nvl(b.tot_used,0)/a.bytes_alloc)*100 > 10
and   a.tablespace_name not in (select distinct tablespace_name from dba_temp_files)
and   a.tablespace_name not like 'UNDO%'
order by 1
 --order by 5

RMAN-03009: failure of allocate command on ORA_AUX_SBT_TAPE_1 channel

This is an error during Database Clone using RMAN. I was trying to restore database using RMAN Backup 

RMAN-00571: ===========================================================

RMAN-00569: ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of Duplicate Db command
RMAN-05501: aborting duplication of target database

RMAN-03009: failure of allocate command on ORA_AUX_SBT_TAPE_1 channel 
ORA-19554: error allocating device, device type: SBT_TAPE, device name:

ORA-27211: Failed to load Media Management Library

Additional information: 

Cause: Backup Configuration was modified.

Use the below command to Resolve the error and continue with Database Restore

rman auxiliary / 
allocate auxiliary channel c1 type disk;
allocate auxiliary channel c2 type disk;
duplicate database to 'DEV' backup location '/Daily_Backup/Backup_16042018';
EOF script failed during instantiate OHS Stage

While cloning 12.2.6 environment, script failed during instantiate OHS Stage.

Issue is because opmn components were unable to start up with below error messages.

globalInitNLS: NLS boot file not found or invalid
 -- default linked-in boot block used
XML parser init: error 201.
globalInitNLS: NLS boot file not found or invalid
 -- default linked-in boot block used

Further analysis on the clone logfiles for OHS creation shown that ORA_NLS10 parameter is unset before cloning.


$unset ORA_NLS10

+Restart the failed script

Concurrent Manager Troubleshooting : Multiple Concurrent Requests Struck without Processing

Scenario: Concurrent Requests are in Running Normal state for longer time than normal duration of the Program.


Step 1: Navigate to System Administrator Responsibility : Concurrent Manager --> Administer

Identify the Concurrent Requests which are in Running Status.

Get the Database Session Details of the currently running concurrent requests using the below Query:

SELECT DISTINCT  a.request_id,C.INST_ID, d.sid, d.serial# ,d.osuser,d.process , c.SPID ,d.inst_id
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
gv$process c,
gv$session d
WHERE a.controlling_manager = b.concurrent_process_id
AND = b.oracle_process_id
AND b.session_id=d.audsid
AND a.phase_code = 'R' and a.status_coDe='R';

Step 2: Verify if the database sessions are active/Inactive at the database level using the below query.

select inst_id,sid,serial#,program,module,status,last_call_et,sql_id from gv$session where sid=&sid;

-- sid value to be taken from output of Sql Query in Step 1

If Database session is INACTIVE And Running no sql for more than an Hour, we can Terminate the Concurrent Requests.
If the Database session is ACTIVE and has an SQL_ID attached with it, Need to check on tuning the sql being run by the database session.

I will cover more details about SQL Tuning in another post.

How to add space in ACFS filesystem?

The below post explains the step-by-step process to add storage to a mountpoint in ACFS filesystem

1.Before extending the mountpoint storage we need to add an LVM of required Storage Capacity on the server.

For checking the LUN's available on the server, click here

For now, assume the LUN added is /dev/sdf1

2. As root User, check the available disks by running the below command

$ /usr/sbin/oracleasm listdisks

3. Create a new disk using the below command

/usr/sbin/oracleasm createdisk DATA02 /dev/sdf1 

Where DATA02 is the name of new disk added
/dev/sdf1 is the LUN added for storage extension.

4. Verify the disk is added by running the below command.

 $/etc/init.d/oracleasm  listdisks

5. Navigate to the other cluster nodes and run the scandisks command to reflect the newly added disk on all the nodes of cluster.

$/etc/init.d/oracleasm  scandisks
Scanning the system for Oracle ASMLib disks:               [  OK  ]

 $/etc/init.d/oracleasm  listdisks

6. Now login to the database server as GRID user (Where ASM instance is running) to add space to ACFS.

Set the environment as required

export ORACLE_HOME=/u01/app/12.1.0/grid

$sqlplus / as sysasm

SQL> alter diskgroup DATA add disk '/dev/oracleasm/disks/DATA02';

Diskgroup altered.

7. Increase the space on filesystem

As root user,

$acfsutil size +1G /oradata
acfsutil size: new file system size: 591833071616 (564416MB)

How to Resolve FRM-92101 error while launching forms in Oracle EBS 12.2.x versions?

In EBS 12.2.6 Environment, Launching Forms fails with Error FRM-92101 intermittently.

FRM-92101: There was a failure in the Forms Server during startup. This could happen due to invalid configuration. Please look into the web-server log file for details


1.       Quick workaround for this issue is to clear the browser cache, restart the browser and launch the forms session again.
2.       Check all the required lib files are installed or not and try relinking the forms executables to resolve the issue.
3.       This is a bug with Internet Explorer and R12.2.X. Contact Oracle support to provide a fix.

How to Enable Database Vault in Oracle database Version 11gR1?

Steps to enable Database Vault in Oracle Database 11gR1:

1. In the command prompt, use DVCA utility to enable Oracle Database Vault.


dvca -action enable 
  -oh /u01/app/oracle/product/11.1.0/db_1
  -service conn_alias 
  -instance orcl 
  -dbname orcl 
  -owner_account lbrown_dvowner 
  -logfile dvcalog.txt 

Enter SYS password: sys_password
Enter owner password: owner_password

Parameter Description:

-service is the name of the database specifier. The specifier can be a connect descriptor or net service name. 

-instance is the name of the database instance.

-dbname is the database name.

-sys_passwd is the SYS password. If you use a cleartext password on the command line, you must include the nodecrypt option. If you omit the password, DVCA prompts you for it. Preferably, omit the password and then enter it interactively when prompted.

-owner_account is the Oracle Database Vault Owner account name.

-owner_passwd is the Oracle Database Vault Owner account password. If you use a cleartext password on the command line, you must include the nodecrypt option. If you omit the password, DVCA prompts you for it. Preferably, omit the password and then enter it interactively when prompted.

-logfile is an optional flag to specify a log file name and location. You can enter an absolute path, or enter a path that is relative to the location of the $ORACLE_HOME/bin directory.

-silent is the option to run in command line mode. This option is required if you are not running DVCA in an xterm window.

-nodecrypt is the option to read plaintext passwords.

-lockout is the flag to use to disable SYSDBA operating system authentication.

2. Stop the database, Database Control console process, and listener.

Enable the Oracle Database Vault option as follows:
 The make command enables both Oracle Database Vault (dv_on) and Oracle Label Security (lbac_on).
 You must enable Oracle Label Security before you can use Database Vault.

cd $ORACLE_HOME/rdbms/lib
make -f dv_on lbac_on

relink all

3.Restart the database, Database Control console process, and listener.

4. Verify Oracle Database Vault and Oracle Label Security are enabled.



EBS 12.2.6 login Page is taking long time to load

EBS Login Performance Issues:

After providing username and password in the login page of EBS 12.2.6, loading home page is taking very long time to load.

weblogic console url shows that one of the oacore servers, oacore_server2 is in WARNING state due to Max number of Stuck Threads reached.

Error Message from oacore log

<Error> <WebLogicServer> <BEA-000337> <[STUCK] ExecuteThread: '16' for queue: 'weblogic.kernel.Default (self-tuning)' has been busy for "605" seconds working on the request "Workmanager: default, Version: 0, Scheduled=true, Started=true, Started time: 605929 ms
GET /OA_HTML/OA.jsp?page=/oracle/apps/per/perimage/webui/PerImgViewPG&transactionid=82152123&language_code=US&pCalledFrom=PERWSIMG&OAMC=N&oas=Wnrjc-sK-Ns6nz5o7H7HIg..&pPersonId=16520&payLegislationCode=AE&CallFromForm='Y' HTTP/1.1
Accept: */*
Accept-Language: en-US
User-Agent: Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 10.0; WOW64; Trident/7.0; .NET4.0C; .NET4.0E)
Accept-Encoding: gzip, deflate
Cookie: oracle.uix=0^^GMT+4:00^p; treemenu1=0; JSESSIONID=tg0hURm_tL89fEex9hDrVrvC1rLBjpTn3fc2pZ89o7Hpb7jR7gVi!222603901; JsessionIDForms=Lh0hURqNRRpfmiTouQ_0kF2MiYYn5x8j8cW5AhjSBMEyOEfvp1oa!-851768; DPRPROD=l6hjdVQuX3p1xvWrRtPjEW8SAj
ECID-Context: 1.005NsDH9rp8FCC0_zxo2yW0002km000083;kXjE
Connection: Keep-Alive
X-WebLogic-KeepAliveSecs: 30
X-WebLogic-Force-JVMID: 222603901

]", which is more than the configured time (StuckThreadMaxTime) of "600" seconds. Stack trace: Method)


Temporary workaround is to Restart the oacore servers to release the stuck threads


1. Increase the value of paramter "StuckThreadMaxTime" TO 3600 Seconds from the weblogic Console.

Log in to WebLogic console 

Click on the Admin server >> Click on the Configuration tab >> Tuning tab and set the below values 

How to create a Database link in GL to transfer FSG reports?

Some times, to migrate FSG reports from one environment to the other, its easy to create a dblink between the source and target environemnts and transfer the reports.

STep-by-Step Process to create database link using General Ledger Responbility.

1. Ensure that network connectivity is open between the Source and Target environments.

2. Login to database node of source instance and copy the contents of tnsnames.ora

Extract from tnsnames.ora


4. Add the above tns entry in the tnsnames.ora file on the application server node of Target instance (Where db link need to be created)

5. Format the tns entry into a single line as below


6. Navigate to General Ledger Superuser Reponsibility.

Click on SEtup->>System--> Database links

A form will popup --> Click on "New Database Link"

Input the below values

Database Name: TEST1
Description :Database link for FSG reports
Connect String:
Apps Username: APPS
APPS Password: <Password>

7. Verify that db link is created

select * from dba_db_links;

How to create directories in Oracle Database?

Database Directories are required to read/write to a filesystem location from plsql code.

Below is the syntax to create database directory

SQL> create directory PLSQL_DIR AS '/oratmp/code';

Directory created.

Grant Read/write privileges to Required Schema (e.g . APPS)


Grant succeeded.

How to send mail from Linux system

Use the below command to send mail from a Linux server to a user email-id:

# echo "this is the body of the email" | mailx -s "test mail" -r "From" -S smtp="your-smtp"

-s =         Specifies the subject.
-r =         Email sent from.
-S =        Specifies the smtp server.

We can also add the configuration in mailx config file  /etc/mail.rc

set smtp=your.smtp.server

set from="from email address"

Then run the mailx command and then enter the body of the email, hit enter, and finally press Ctrl+D to deliver it.

# mailx -vvv -s "email subject"  external-email-address

<Body of the email>

Ctrl D to deliver the message

How to use coe_xfr_sql_profile.sql to Improve performance of a sql query?

Oracle SQL Performance tuning is one of the frequently reported issues in a Oracle Database administrator's daily job.
Very often we notice that sql queries or concurrent Programs which were running within few minutes earlier are now taking hours to be completed.
Underlying reason is due to the plan_hash_value used by the query has been changed.
coe_xfr_sql_profile.sql is the script developed by Oracle Support which helps us to identify the plan_hash_value which will resolve the performance issue.

Steps to use the script coe_xfr_sql_profile.sql

1. Download the script to a temporary directory on the database server.
Script is also available in Oracle Metalink.

2. Before Running the coe_xfr_sql_profile.sql, we need to know the sql_id of the sql query which is taking longer time to complete than usual.

To find the sql_id use below query

2.1 If you know the database SID of the long running query.
select inst_id,sid,serial#,status,last_Call_et,sql_id from gv$session where sid= '&sessionid';

2.2 If you know the query test, Use gv$sql and gv$sqlarea views to get the sql_id.

3. Login to sqlplus as sysdba and run coe_xfr_sql_profile.sql at the SQL prompt.

$ sqlplus “/as sysdba”
SQL> @coe_xfr_sql_profile.sql
Parameter 1:
SQL_ID (required)

Enter value for 1: 0a3f7vuks8d7y   (--this is the sql_id of long running sql)
————— ———–
1484137450 15.6
3622468234 4560.76

[Output shows the list of available plan hash values, which can be forced on to the sql query. We need to select the plan_hash_value with low ETA to resolve the performance issue]

Parameter 2:
PLAN_HASH_VALUE (required)

Enter value for 2: 1484137450 

++Based on the inputs provided, it generates a sql script with naming convention coe_xfr_sql_profile_0a3f7vuks8d7y_1484137450.sql as output


4. Run the script as sysdba


5. Verify that Performance issue is resolved by Re-running the sql.