Category: Troubleshooting

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-Forwarded-For: 10.11.32.120
Proxy-Client-IP: 10.11.32.120
X-WebLogic-KeepAliveSecs: 30
X-WebLogic-Force-JVMID: 222603901


]”, which is more than the configured time (StuckThreadMaxTime) of “600” seconds. Stack trace:
        java.net.SocketInputStream.socketRead0(Native Method)
        java.net.SocketInputStream.read(SocketInputStream.java:152)
        java.net.SocketInputStream.read(SocketInputStream.java:122)
        oracle.net.nt.MetricsEnabledInputStream.read(TcpNTAdapter.java:730)






Solution1
=====


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


Solution2
========


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 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)
PLAN_HASH_VALUE AVG_ET_SECS
————— ———–
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


coe_xfr_sql_profile_<sql_id>_<plan_hash_value>.sql


4. Run the script as sysdba


SQL>coe_xfr_sql_profile_0a3f7vuks8d7y_1484137450.sql 


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

How to change default start Page in Oracle EBS

Application start page can changed for a Oracle EBS user in two ways.

1. Change settings in Preferences:

We can set the Application Start Page in Preferences. Click on Preferences and set the start page to preferred responsibility/page. After setting this preference, user will be taken to the above set page on successful login.

To reset the Responsibility Start Page, we can modify using same navigation.

2.  Change Profile option “Application Start Page”
Login as SYSADMIN user . Navigate to system adminstrator’s reponsibility > Profile > System

QUery for Profile Option name ‘Application Start Page” and User, then change or remove the value at User Level.

HTTP 404 error after logging into Oracle EBS 12.2.6 url

Issue Description

We recently upgraded the Oracle EBS Version to 12.2.6 from 12.1.3. After Upgrade, one user reported he is getting erro HTTP 404 when he login to the application url.

Cause:

User has set his preferences to load a Custom Start Page after login. The custom html was not available after the upgrade.Hence he is getting error HTTP 404 as in screenshot. He is unable to reset the preferences as the page is not loading.

Solution:

In this case, only option is to modify the profile option ‘Application Start Page’ for the specific user  to a correct value.

Oracle EBS 12.2.x: ADOP error during Cutover phase

Background of the issue: I was applying hrblobal driver in a multi-node Production environment. Our Production application EBS has one Primary node and one DMZ Node.


After applying hrglobal driver using the online patching cycle, Cutover phase thrown below error message


==

Applying patch(es): <6390830> on node EBSDMZ failed.
If you choose to proceed with cutover, node EBSDMZ will be marked as abandoned.
Do you want adop to continue processing on completed nodes [y/n]? n
    As per user choice, adop will not proceed further with processing.


==


If we Provide input as ‘Y’ above, EBSDMZ node will be abandoned and we need to register the DMZ node again.




Solution:


1.Apply Patch on DMZ node using options=nodb and restart the cutover phase


On DMZ Node
=====
Source the patch filesystem
adop phase=apply patchtop=$PER_TOP/patch/115 patches=driver:hrglobal.drv allnodes=NO action=nodb options=forceapply


2. Restart Cutover phase on Master node


ORA-32771: Cannot Add File To Bigfile Tablespace

When trying to add a datafile to a tablespace , got the error – ORA-32771: cannot add file to bigfile tablespace.


SQL> ALTER TABLESPACE BIG_TBSP1 add datafile ‘+DATA/df01.dbf’ size 130G;
ALTER TABLESPACE BIG_TBSP1 add datafile ‘+DATA/df01.dbf’ size 130G;
*
ERROR at line 1:
ORA-32771: cannot add file to bigfile tablespace


SOLUTION:


A bigfile tablespace can contain only one datafile and no other datafile can be added to that.


SQL> select BIGFILE from dba_tablespaces where tablespace_name=’BIG_TBSP1’;


BIGFILE
———————– —
YES


How to increase space in Bigfile Tablespace?




BIGFILE tablespace stores its data in a single datafile with a much larger capacity.


We can resize the size of the datafile in BIGFILE tablespace using ALTER DATABASE Command




ALTER DATABASE DATAFILE ‘/+DATA/df0101.dbf’ RESIZE 180G;


Since BIGFILE Tablespace has only one datafile, there is no need to identify the datafile and increase its size.
We can use ALTER TABLESPACE command to resize at the tablespace level.


ALTER TABLESPACE BIG_TBSP1 RESIZE 180G;

Enable ArchiveLog Mode on Oracle RAC database fails with ORA-00265

On a test RAC environment, while archiving is being enabled, “ALTER DATABASE ARCHIVELOG” command errored with ORA-00265: instance recovery required, cannot set ARCHIVELOG mode


Below are the steps performed to enable Archivelog mode on a 3-node RAC database environment.


test0115:TEST1011 $ srvctl status database -d TEST101
Instance TEST1011 is running on node test0115
Instance TEST1012 is running on node test0116
Instance TEST1013 is running on node test0117
test0115:TEST1011 $
test0115:TEST1011 $ srvctl stop database -d TEST101




test0115:TEST1011 $ srvctl status database -d TEST101
Instance TEST1011 is not running on node test0115
Instance TEST1012 is not running on node test0116
Instance TEST1013 is not running on node test0117
test0115:TEST1011 $




test0115:TEST1011 $ srvctl start database -d TEST101 -o mount
test0115:TEST1011 $  srvctl status database -d TEST101
Instance TEST1011 is running on node test0115
Instance TEST1012 is running on node test0116
Instance TEST1013 is running on node test0117
test0115:TEST1011 $




test0115:TEST1011 $ sqlplus / as sysdba


SQL*Plus: Release 11.2.0.4.0 Production on Sat May 9 14:13:54 2020


Copyright (c) 1982, 2013, Oracle.  All rights reserved.




Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Partitioning, Real Application Clusters and Automatic Storage Management options


SQL> select name,open_mode from gv$database;


NAME      OPEN_MODE
——— ——————–
TEST101   MOUNTED
TEST101   MOUNTED
TEST101   MOUNTED


SQL>SQL> alter database archivelog;
alter database archivelog
*
ERROR at line 1:
ORA-00265: instance recovery required, cannot set ARCHIVELOG mode


Solution:
=========


1. Shutdown the database cleanly


SQL> shutdown immediate (on 3 RAC nodes)


2. Startup the database in mount stage


SQL> startup mount


3. Enable Archivelog mode


SQL> Alter database archivelog;


Database altered.


4. Open the database


SQL> Alter database open.


5. Verify archielog mode is enabled.


SQL> ARCHIVE LOG LIST;

Impdp fails with error ORA-31604

Issue:
When tried to Import a table from one Oracle database to another using IMPDP utility,encountered the error ORA-31604: invalid transform NAME parameter “MODIFY” for object type PROCACT_INSTANCE in function ADD_TRANSFORM
Impdp logfile has the below error:
Starting “<LOGIN_SCHEMA>”.”SYS_IMPORT_FULL_10″:  <LOGIN>/******** parfile=<PARFILE_NAME>.par logfile=<LOG_NAME>.log dumpfile=<DUMPFILE_NAME>.dmp parallel=1
Processing object type TABLE_EXPORT/TABLE/PROCACT_INSTANCE
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.LOAD_MD_TRANSFORMS []
ORA-31604: invalid transform NAME parameter “MODIFY” for object type PROCACT_INSTANCE in function ADD_TRANSFORM
ORA-06512: at “SYS.DBMS_SYS_ERROR”, line 86
ORA-06512: at “SYS.KUPW$WORKER”, line 8996
Cause: This is a bug in Oracle Database versio 11g, fixed in Oracle Database version 12c
Solution:
As a workaround, implement any of the below solutions:
1. Use an additional parameter that is exclude=PROCACT_INSTANCE during impdp
2. Redo the export with exclude=PROCACT_INSTANCE and perform import using new dumpfiles.
The use of the exclude=PROCACT_SYSTEM will exclude the resource manager objects such as resource plans and groups. We need to re-create resource plans and groups after impdp is successfully completed.