expdp fails with UDE-31623: operation generated ORACLE error 31623

 

Issue:

expdp fails with error 

UDE-31623: operation generated ORACLE error 31623

expdp \"/as sysdba\" tables=EMP directory=DATA_PUMP_DIR dumpfile=emp.dmp logfile=emp.log

 
Export: Release 19.0.0.0.0 - Production on Fri Nov 22 04:15:27 2024
Version 19.24.0.0.0
 
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
 
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
 
UDE-31623: operation generated ORACLE error 31623
ORA-31623: a job is not attached to this session via the specified handle
ORA-06512: at "SYS.DBMS_DATAPUMP", line 4747
ORA-06512: at "SYS.KUPV$FT_INT", line 2144
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.KUPV$FT_INT", line 2081
ORA-06512: at "SYS.DBMS_DATAPUMP", line 2263
ORA-06512: at "SYS.DBMS_DATAPUMP", line 4496
ORA-06512: at "SYS.DBMS_DATAPUMP", line 6127
ORA-06512: at line 1
 
Cause

ORA-4031 errors in alert log

ORA-04031: unable to allocate 56 bytes of shared memory ("streams pool","unknown object","streams pool","fixed allocation callback")

Solution

Check SGA parameters and increase as per requirement

In our case, 

SQL> show parameter sga_target value is less than sga_max_size.
Increased sga_target to fix the issue


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_max_size                         big integer 12G
sga_target                           big integer 10880M
SQL> alter system set sga_target=12G scope=both;

System altered.

Query to Update the Concurrent Request Status as Terminated from Backend

Below sql can be used for Concurrent Requests struck in running state without any database session ,and could not cancelled from Front end.

sqlplus apps

update fnd_concurrent_requests set status_code='X',phase_code='C' where request_id=&request_id;

commit;

Concurrent Request failing due to XML publisher directory not available

Issue:

Concurrent Request Failing during OutputPost Processing

Below is the error message from OutputPost Processor Logs:

 [UNEXPECTED]  java.io.FileNotFoundException: /usr/tmp/ERPTEST1/xdokjshyw821_1905677210.fo (No such file or directory)
        at java.io.FileOutputStream.open(Native Method)
        at java.io.FileOutputStream.<init>(FileOutputStream.java:221)
        at java.io.FileOutputStream.<init>(FileOutputStream.java:171)
        at oracle.apps.xdo.common.tmp.TmpFile.createTmpFileJDK118(Unknown Source)
        at oracle.apps.xdo.common.tmp.TmpFile.createTmpFile(Unknown Source)
        at oracle.apps.xdo.template.FOProcessor.getTmpFOFile(Unknown Source)
        at oracle.apps.xdo.template.FOProcessor.createFO(Unknown Source)
        at oracle.apps.xdo.template.FOProcessor.generate(Unknown Source)
        at oracle.apps.xdo.oa.schema.server.TemplateHelper.runProcessTemplate(TemplateHelper.java:5984)
        at oracle.apps.xdo.oa.schema.server.TemplateHelper.processTemplate(TemplateHelper.java:3475)
        at oracle.apps.xdo.oa.schema.server.TemplateHelper.processTemplate(TemplateHelper.java:3564)
        at oracle.apps.fnd.cp.opp.XMLPublisherProcessor.process(XMLPublisherProcessor.java:311)
        at oracle.apps.fnd.cp.opp.OPPRequestThread.run(OPPRequestThread.java:185)
Cause 

The Temporary directory for XML Publisher has not been modified after making the clone so it was pointing to non existing location and getting error out.

Solution

1. Create a new Temporary Directory on OS level for the cloned environment.
2. Make sure that the application owner (APPLMGR) has Read and Write permission this directory.

ADOP fs_clone failing with error : "Could not find patch context file from database"

 Issue:

FS_CLONE is failing with error :Could not find patch context file from database
Error details:
$adop phase=fs_clone
==============
Checking for existing adop sessions.
    No pending session exists.
    Starting new adop session.
===========================================================================
ADOP (C.Delta.12)
Session ID: 43
Node: test1.domain1.com
Phase: fs_clone
Log: /test/erp/apps/fs_ne/EBSapps/log/adop/32/20220121_145748/adop.log
===========================================================================
Verifying existence of context files in database.
    [UNEXPECTED] Could not find patch context file from database
    [UNEXPECTED]Patch edition context file not found in database for host test1.domain1.com

Summary report for current adop session:
    Node test1.domain1.com:
       - Fs_clone status:   Not Started
    For more details, run the command: adop -status -detail

[STATEMENT] Please run adopscanlog utility, using the command
"adopscanlog -latest=yes"
to get the list of the log files along with snippet of the error message corresponding to each log file.

adop exiting with status = 2 (Fail)

=========================
Cause:
===
Patch context file not found in fnd_oam_context_files tables.

Solution:
=======
1.Source the patch file system and run autoconfig on patch filesystem to update context file in database.
or
2. Run CtxSynchronizer to upload patch context file to database.

$ $ADJVAPRG oracle.apps.ad.autoconfig.oam.CtxSynchronizer action=upload contextfile=<patch context file name and location> logfile=/tmp/patchctxupload.log

Provide apps password when prompted.

Concurrent request Error: The executable file for this concurrent program cannot be executed.

 Issue: Concurrent request completed with below Error.

This request finished with an error and produced the following completion message: The executable file /test1/apps/fs2/EBSapps/appl/fnd/12.0.0/bin/XXCUST_PROG for this concurrent program cannot be executed.

ERROR:

The executable file /test1/apps/fs2/EBSapps/appl/fnd/12.0.0/bin/XXCUST_PROG for this concurrent program cannot be executed.

Contact your system administrator or support representative.

Cause:

Verify that the execution path to the executable file

Run the below command

$ls -lrt /test1/apps/fs2/EBSapps/appl/fnd/12.0.0/bin/XXCUST_PROG

Output of the above command shows executable is pointing to a wrong symbolic link

Solution:

1.Create a soft link with the program name as below:

$cd /test1/apps/fs2/EBSapps/appl/fnd/12.0.0/bin/

$ln -s -f $FND_TOP/bin/fndcpesr XXCUST_PROG

2. Re-run the concurrent program and test the issue.

EBS 12.2.X Patching: ADOP Cutover failed while running txkADOPCutOverPhaseCtrlScript.pl

Issue:

 During an adop online patching cycle, cutover phase failed while running the script txkADOPCutOverPhaseCtrlScript.pl

  [PROCEDURE]  Starting Middle Tier Services
    [UNEXPECTED]Error occurred running "perl /test/app/fs1/EBSapps/appl/ad/12.0.0/patch/115/bin/txkADOPCutOverPhaseCtrlScript.pl -contextfile=/test/app/fs1/inst/apps/EBSTEST_testebsmt111/appl/admin/EBSTEST_testebsmt111.xml -patchcontextfile=/test/app/fs1/2/inst/apps/EBSTEST_testebsmt111/appl/admin/EBSTEST_testebsmt111.xml -promptmsg=hide -console=off -sessionid=14 -timestamp=20201108_194155 -outdir=/test/app/fs_ne/EBSapps/log/adop/14/20201108_194155/cutover/testebsmt111 -action=forcestartup"
    [UNEXPECTED]Cutover phase has failed.

Cause: 

Review of all adop and cutover log files shows that Cutover phase failed after filesystem switch and ADMIN server startup,oacore services could not be started after cutover due to port conflict.

Resolution:

1. Check Status of adop session using below sql query:
set pagesize 200;
set linesize 160;
col PREPARE_STATUS format a15
col APPLY_STATUS format a15
col CUTOVER_STATUS format a15
col ABORT_STATUS format a15
col STATUS format a15
select NODE_NAME,ADOP_SESSION_ID, PREPARE_STATUS , APPLY_STATUS  ,CUTOVER_STATUS , CLEANUP_STATUS , ABORT_STATUS , STATUS
from AD_ADOP_SESSIONS
order by ADOP_SESSION_ID;
For my issue, cutover status was 5, which means ADMIN startup is completed.

2. Restart the cutover phase with mtrestart=no option

adop phase=cutover mtrestart=no

3. Fix the issues related to services startup on RUN filesystem (which is switched after cutover).

4. Startup the services

5. Perform cleanup phase and complete the adop cycle
adop phase=cleanup 

adpreclone.pl Error on Applications Tier

Issue:

adpreclone.pl appsTier on EBS 12.2.x environment fails with error

ERROR: The context variable s_apps_jdbc_connect_descriptor is null

Cause:

Value of parameter "s_apps_jdbc_connect_descriptor" is null in Applications Context file.

Solution:

1. Take Backup of context file

2. Update the value of parameter "s_apps_jdbc_connect_descriptor" with JDBC connect string to connect to the database.

3. Run Autoconfig on Applications node.

4. Restart adpreclone.pl appsTier

Autoconfig Error : ORA-01422: exact fetch returns more than requested number of rows

Issue: Autoconfig on EBS 12.2 environment fails while running script txkGenADOPWrapper.pl

Below is the error message  from logfile

=======================

Script Name    : txkGenADOPWrapper.pl

Script Version : 120.0.12020000.3

ERROR DESCRIPTION:

(*******FATAL ERROR*******

PROGRAM : (/test/app/fs1/inst/apps/test_mtnode1/admin/install/txkGenADOPWrapper.pl)

TIME    : Wed Nov 25 04:22:34 2020

FUNCTION: TXK::SQLPLUS::_doExecute [ Level 3 ]

MESSAGES:

SQLPLUS error: buffer=

 

SQL*Plus: Release 10.1.0.5.0 - Production on Wed Nov 25 04:22:33 2020

 

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

 

SQL> SQL> Connected.

SQL> SELECT ad_zd_adop.get_node_type('mtnode1') FROM DUAL

       *

ERROR at line 1:

ORA-01422: exact fetch returns more than requested number of rows

ORA-06512: at "APPS.AD_ZD_ADOP", line 3010

=======================

Cause:

FND_NODES table has incorrect node information.

Found an extra row in FND_NODES table where node_name is null after clone.

Solution:

1. Clean up FND_NODES table

Login to database as apps user

sqlplus apps/<password>

    

sql>exec FND_CONC_CLONE.SETUP_CLEAN;

2.Run Autoconfig on Database node(s).

cd $ORACLE_HOME/appsutil/bin

adconfig.sh

[Provide Database Context file  and apps Password]

3. Run Autoconfig on apps tier

Login to Application node 

Source the RUN filesystem environment file.

cd $AD_TOP/bin

adconfig.sh

[Provide Applictions Context file and apps Password]

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

Issue:

In a 12.2.6 EBS environment, Login fails with below error


Unable to create anonymous session. ICX_SESSION_CREATION_FAILED &#40;userid=6&#41; 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.


Analysis:

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> <test.domain.com> <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> <test.domain.com> <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> <test.domain.com> <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> <test.domain.com> <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.

Solution:

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.


(or)

Alternate Solution:

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

cd $ADMIN_SCRIPTS_HOME

admanagedsrvctl.sh stop oacore_server

admanagedsrvctl.sh start oacore_server

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.


TESTDB01=
       (DESCRIPTION=
               (ADDRESS=(PROTOCOL=tcp)(HOST=test001-vip1.domain.com)(PORT=1521))
               (ADDRESS=(PROTOCOL=tcp)(HOST=test002-vip1.domain.com)(PORT=1521))
               (ADDRESS=(PROTOCOL=tcp)(HOST=test003-vip1.domain.com)(PORT=1521))
           (CONNECT_DATA=
               (SERVICE_NAME=TESTDB01)
               (INSTANCE_NAME=TESTDB01)
           )
       )


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.

Analysis:

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

Solution:

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

ping  test001-vip1.domain.com
ping  test002-vip1.domain.com
ping  test003-vip1.domain.com

telnet test001-vip1.domain.com 1521
telnet test002-vip1.domain.com 1521
telnet test003-vip1.domain.com 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.

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.
http://www.appsdbadiaries.com/2016/01/concurrent-requests-on-hold.html




b)Bring Down Concurrent Manager using adcmctl.sh 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';

Commit;


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

d) Start Concurrent Manager using adcmctl.sh

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

 SQL>commit;

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

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

Pre-requisites

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
/ 

adcfgclone.pl script failed during instantiate OHS Stage

While cloning 12.2.6 environment, adcfgclone.pl 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.

Resolution:

$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.

Analysis: 

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 c.pid = 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 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

 

Solution

1.       Quick workaround for this issue is to clear the browser cache, restart the browser and launch the forms session again.

OR

2.       Check all the required lib files are installed or not and try relinking the forms executables to resolve the issue.

       OR

3.       This is a bug with Internet Explorer and R12.2.X. Contact Oracle support to provide a fix.

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.