Issues faced while upgrading Oracle Applications (E-Business Suite) from 11i to R12.2

Issue #1:

 

ATTENTION: All workers either have failed or are waiting:

 

           FAILED: file cskbcat.ldt  on worker  1.

 

ATTENTION: Please fix the above failed worker(s) so the manager can continue.

 

Worker log:

Time when worker restarted job: Sun Feb 19 2017 06:33:15

 

Loading data using  FNDLOAD function.

FNDLOAD APPS/***** 0 Y UPLOAD @CS:patch/115/import/cskbcat.lct @CS:patch/115/import/US/cskbcat.ldt -

 

Connecting to APPS......Connected successfully.

 

Calling FNDLOAD function.

 

Returned from FNDLOAD function.

 

Log file: /u01/oracle/TEST/fs1/EBSapps/appl/admin/TEST/log/US_cskbcat_ldt.log

Error calling FNDLOAD function.

 

 

Time when worker failed: Sun Feb 19 2017 06:33:16

 

Fndload log file:

Uploading from the data file /u01/oracle/TEST/fs1/EBSapps/appl/cs/12.0.0/patch/115/import/US/cskbcat.ldt

Altering database NLS_LANGUAGE environment to AMERICAN

Dumping from LCT/LDT files (/u01/oracle/TEST/fs1/EBSapps/appl/cs/12.0.0/patch/115/import/cskbcat.lct(120.1), /u01/oracle/TEST/fs1/EBSapps/appl/cs/12.0.0/patch/115/import/US/cskbcat.ldt) to staging tables

Dumping LCT file /u01/oracle/TEST/fs1/EBSapps/appl/cs/12.0.0/patch/115/import/cskbcat.lct(120.1) into FND_SEED_STAGE_CONFIG

Dumping LDT file /u01/oracle/TEST/fs1/EBSapps/appl/cs/12.0.0/patch/115/import/US/cskbcat.ldt into FND_SEED_STAGE_ENTITY

Dumped the batch (CS_KB_SOLN_CATEGORIES_VL 1 , CS_KB_SOLN_CATEGORIES_VL 1 ) into FND_SEED_STAGE_ENTITY

Uploading from staging tables

  Error loading seed data for CS_KB_SOLN_CATEGORIES_VL:  CATEGORY_ID = 1,  ORA-29877: failed in the execution of the ODCIINDEXUPDATE routine

ORA-20000: Oracle Text error:

DRG-50857: oracle error in textindexmethods.ODCIIndexUpdate

ORA-20000: Oracle Text error:

DRG-10607: index meta data is not ready yet for queuing DML

DRG-13201: KOREAN_LEXER is desupported

ORA-30576: ConText Option dictionary loading error

 

 

Concurrent request completed

Current system time is Sun Feb 19 06:33:16 2017

 

Solution:

As per Applying The Patch 6678700 Worker 1 Failed: File Cskbcat.Ldt. ERRORS: ORA-20000: Oracle Text error: DRG-50857: oracle error in textindexmethods.ODCIIndexUpdate, DRG-13201: KOREAN_LEXER is desupported (Doc ID 1333659.1)

 

{oratest@ruh01sm4k02: ~ $} sqlplus "/ as sysdba" @$ORACLE_HOME/ctx/sample/script/drkorean.sql

 

SQL*Plus: Release 11.2.0.4.0 Production on Sun Feb 19 06:37:29 2017

 

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, OLAP, Data Mining and Real Application Testing options

 

List of indexes that use KOREAN_LEXER as top level lexer:

List of indexes that use KOREAN_LEXER as a sub lexer:

"CS"."CS_FORUM_MESSAGES_TL_N4"

"CS"."CS_INCIDENTS_ALL_TL_N1"

"CS"."CS_KB_ELEMENTS_TL_N2"

"CS"."CS_KB_SETS_TL_N3"

"CS"."CS_KB_SOLN_CAT_TL_N1"

"ICX"."ICX_QUES_CTX"

Migrate KOREAN_LEXER to KOREAN_MORPH_LEXER

Rebuild all indexes that use korean lexer as top level lexer:

Reindex all documents that use KOREAN_LEXER as sub lexer

reindexing : "CS"."CS_FORUM_MESSAGES_TL" finished.

reindexing : "CS"."CS_INCIDENTS_ALL_TL" finished.

reindexing : "CS"."CS_KB_ELEMENTS_TL" finished.

reindexing : "CS"."CS_KB_SETS_TL" finished.

reindexing : "CS"."CS_KB_SOLN_CATEGORIES_TL" finished.

reindexing : "ICX"."ICX_QUESTIONS_TL" finished.

SQL>

 

a)       Restart failed worker

 

How to change the hostname in Oracle linux OS server

1- vi /etc/sysconfig/network
2- edit the valus of HOSTNAME in that file to the value you want.
3- Replace the old value with the new value in /etc/hosts
4- Reboot the server

Regards,

Mohamed

Creating Oracle DB Enterprise Manager repository

Note ID: 278100.1

- As the DB files owner run:

[ortest@erpapp02 ~]$ emca -config dbcontrol db -repos create

STARTED EMCA at Apr 3, 2011 9:51:31 AM
EM Configuration Assistant, Version 11.1.0.7.0 Production
Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Enter the following information:
Database SID: TEST
Listener port number: 1521
Password for SYS user: 
Password for SYSMAN user:  
Password for SYSMAN user: Email address for notifications (optional):
Outgoing Mail (SMTP) server for notifications (optional):
-----------------------------------------------------------------

You have specified the following settings

Database ORACLE_HOME ................ /Oracle/Test/db/tech_st/11.1.0

Local hostname ................ erpapp02.rsteel.com
Listener port number ................ 1521
Database SID ................ TEST
Email address for notifications ...............
Outgoing Mail (SMTP) server for notifications ...............

-----------------------------------------------------------------
Do you wish to continue? [yes(Y)/no(N)]: y
Apr 3, 2011 9:53:35 AM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /Oracle/Test/db/tech_st/11.1.0/cfgtoollogs/emca/TEST/emca_2011_04_03_09_51_31.log.
Apr 3, 2011 9:53:37 AM oracle.sysman.emcp.EMReposConfig createRepository
INFO: Creating the EM repository (this may take a while) ...
Apr 3, 2011 10:03:03 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully created
Apr 3, 2011 10:03:07 AM oracle.sysman.emcp.EMReposConfig uploadConfigDataToRepository
INFO: Uploading configuration data to EM repository (this may take a while) ...
Apr 3, 2011 10:04:01 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Uploaded configuration data successfully
Apr 3, 2011 10:04:05 AM oracle.sysman.emcp.util.DBControlUtil configureSoftwareLib
INFO: Software library configured successfully.
Apr 3, 2011 10:04:05 AM oracle.sysman.emcp.EMDBPostConfig configureSoftwareLibrary
INFO: Deploying Provisioning archives ...
Apr 3, 2011 10:04:16 AM oracle.sysman.emcp.EMDBPostConfig configureSoftwareLibrary
INFO: Provisioning archives deployed successfully.
Apr 3, 2011 10:04:16 AM oracle.sysman.emcp.util.DBControlUtil secureDBConsole
INFO: Securing Database Control (this may take a while) ...
Apr 3, 2011 10:04:39 AM oracle.sysman.emcp.util.DBControlUtil secureDBConsole
INFO: Database Control secured successfully.
Apr 3, 2011 10:04:39 AM oracle.sysman.emcp.util.DBControlUtil startOMS
INFO: Starting Database Control (this may take a while) ...
Apr 3, 2011 10:05:09 AM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: Database Control started successfully
Apr 3, 2011 10:05:09 AM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: >>>>>>>>>>> The Database Control URL is https://erpapp02.rsteel.com:5500/em <<<<<<<<<<<
Apr 3, 2011 10:05:13 AM oracle.sysman.emcp.EMDBPostConfig invoke
WARNING:
************************  WARNING  ************************

Management Repository has been placed in secure mode wherein Enterprise Manager data will be encrypted.  The encryption key has been placed in the file: /Oracle/Test/db/tech_st/11.1.0/erpapp02.alrajhisteel.com_TEST/sysman/config/emkey.ora.   Please ensure this file is backed up as the encrypted data will become unusable if this file is lost.

***********************************************************
Enterprise Manager configuration completed successfully
FINISHED EMCA at Apr 3, 2011 10:05:13 AM
[ortest@erpapp02 ~]$

Recreate DB Enterprise Manager Repository :

- This will first drop the existing repository first, ensure you deleted the old EM folder under the $ORACLE_HOME

emca -config dbcontrol db -repos recreate

Drop DB Enterprise Manager Repository :

emca -deconfig dbcontrol db -repos drop

IMPORTANT: Dropping or recreating the EM repository will put the DB in the quiesce mode.

Regards,

Mohamed

How to get the SID and OS process for a running CR

- Run the below SQL statement to get the SID and OS procedd ID for a running request.

- The statement will ask for the Request ID

set pages 1000 lin 300
SELECT a.request_id, d.sid, d.serial# ,d.osuser,d.process , c.SPID "OS Process", d.STATUS, d.username
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
v$process c,
v$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.request_id = &Request_ID
AND a.phase_code = 'R';

Regards,

Mohamed

Login Page is not accessible

Issue:

Getting the following error when trying to open the login page:

You have encountered an unexpected error. Please contact the System Administrator for assistance.

• All MT services are up and running normally!
• No errors in the log files!

Solution:

To get more info about the error do the following:

- Enable "FND:Diagnostics" profile option from backend

update fnd_profile_option_values set profile_option_value='YES' where level_id=10001 and profile_option_id = (select fpo.profile_option_id from fnd_profile_options fpo,fnd_profile_options_tl fpot where upper(fpot.user_profile_option_name) like upper('FND: Diagnostics') and fpo.profile_option_name=fpot.profile_option_name);

commit;

select fnd_profile.value('FND_DIAGNOSTICS') "FND: Diagnostics" from dual;

- Refresh the login page again, now the error is:

Exception Details. 
 oracle.apps.fnd.framework.OAException: Could not load application module 'oracle.apps.fnd.sso.login.server.MainLoginPageAM'.
    at oracle.apps.fnd.framework.webui.OAJSPApplicationRegistry.registerApplicationModule(OAJSPApplicationRegistry.java:279)
    at oracle.apps.fnd.framework.webui.OAJSPApplicationRegistry.registerApplicationModule(OAJSPApplicationRegistry.java:78)
    at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:1300)
    at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:543)
    at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:431)
    at _OA._jspService(_OA.java:212)
    at com.orionserver.http.OrionHttpJspPage.service(OrionHttpJspPage.java:59)
    at oracle.jsp.runtimev2.JspPageTable.service(JspPageTable.java:462)
    at oracle.jsp.runtimev2.JspServlet.internalService(JspServlet.java:594)
    at oracle.jsp.runtimev2.JspServlet.service(JspServlet.java:518)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:856)
    at com.evermind.server.http.ServletRequestDispatcher.invoke(ServletRequestDispatcher.java:713)
    at com.evermind.server.http.ServletRequestDispatcher.forwardInternal(ServletRequestDispatcher.java:370)
    at com.evermind.server.http.ServletRequestDispatcher.unprivileged_forward(ServletRequestDispatcher.java:259)
    at com.evermind.server.http.ServletRequestDispatcher.access$100(ServletRequestDispatcher.java:51)
    at com.evermind.server.http.ServletRequestDispatcher$2.oc4jRun(ServletRequestDispatcher.java:193)
    at oracle.oc4j.security.OC4JSecurity.doPrivileged(OC4JSecurity.java:284)
    at com.evermind.server.http.ServletRequestDispatcher.forward(ServletRequestDispatcher.java:198)
    at com.evermind.server.http.EvermindPageContext.forward(EvermindPageContext.java:395)
    at _RF._jspService(_RF.java:225)
    at com.orionserver.http.OrionHttpJspPage.service(OrionHttpJspPage.java:59)
    at oracle.jsp.runtimev2.JspPageTable.service(JspPageTable.java:462)
    at oracle.jsp.runtimev2.JspServlet.internalService(JspServlet.java:594)
    at oracle.jsp.runtimev2.JspServlet.service(JspServlet.java:518)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:856)
    at com.evermind.server.http.ResourceFilterChain.doFilter(ResourceFilterChain.java:64)
    at oracle.apps.jtf.base.session.ReleaseResFilter.doFilter(ReleaseResFilter.java:26)
    at com.evermind.server.http.EvermindFilterChain.doFilter(EvermindFilterChain.java:15)
    at oracle.apps.fnd.security.AppsServletFilter.doFilter(AppsServletFilter.java:318)
    at com.evermind.server.http.ServletRequestDispatcher.invoke(ServletRequestDispatcher.java:621)
    at com.evermind.server.http.ServletRequestDispatcher.forwardInternal(ServletRequestDispatcher.java:370)
    at com.evermind.server.http.HttpRequestHandler.doProcessRequest(HttpRequestHandler.java:871)
    at com.evermind.server.http.HttpRequestHandler.processRequest(HttpRequestHandler.java:453)
    at com.evermind.server.http.AJPRequestHandler.run(AJPRequestHandler.java:313)
    at com.evermind.server.http.AJPRequestHandler.run(AJPRequestHandler.java:199)
    at oracle.oc4j.network.ServerSocketReadHandler$SafeRunnable.run(ServerSocketReadHandler.java:260)
    at oracle.oc4j.network.ServerSocketAcceptHandler.procClientSocket(ServerSocketAcceptHandler.java:234)
    at oracle.oc4j.network.ServerSocketAcceptHandler.access$700(ServerSocketAcceptHandler.java:29)
    at oracle.oc4j.network.ServerSocketAcceptHandler$AcceptHandlerHorse.run(ServerSocketAcceptHandler.java:879)
    at com.evermind.util.ReleasableResourcePooledExecutor$MyWorker.run(ReleasableResourcePooledExecutor.java:303)
    at java.lang.Thread.run(Thread.java:619)
## Detail 0 ##
oracle.apps.fnd.framework.OAException: Application: FND, Message Name: FND_NO_TRANSACTION_ID.
    at oracle.apps.fnd.framework.webui.OAJSPHelper.getTransactionId(OAJSPHelper.java:352)
    at oracle.apps.fnd.framework.webui.OAJSPApplicationRegistry.registerApplicationModule(OAJSPApplicationRegistry.java:133)
    at oracle.apps.fnd.framework.webui.OAJSPApplicationRegistry.registerApplicationModule(OAJSPApplicationRegistry.java:78)
    at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:1300)
    at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:543)
    at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:431)
    at _OA._jspService(_OA.java:212)
    at com.orionserver.http.OrionHttpJspPage.service(OrionHttpJspPage.java:59)
    at oracle.jsp.runtimev2.JspPageTable.service(JspPageTable.java:462)
    at oracle.jsp.runtimev2.JspServlet.internalService(JspServlet.java:594)
    at oracle.jsp.runtimev2.JspServlet.service(JspServlet.java:518)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:856)
    at com.evermind.server.http.ServletRequestDispatcher.invoke(ServletRequestDispatcher.java:713)
    at com.evermind.server.http.ServletRequestDispatcher.forwardInternal(ServletRequestDispatcher.java:370)
    at com.evermind.server.http.ServletRequestDispatcher.unprivileged_forward(ServletRequestDispatcher.java:259)
    at com.evermind.server.http.ServletRequestDispatcher.access$100(ServletRequestDispatcher.java:51)
    at com.evermind.server.http.ServletRequestDispatcher$2.oc4jRun(ServletRequestDispatcher.java:193)
    at oracle.oc4j.security.OC4JSecurity.doPrivileged(OC4JSecurity.java:284)
    at com.evermind.server.http.ServletRequestDispatcher.forward(ServletRequestDispatcher.java:198)
    at com.evermind.server.http.EvermindPageContext.forward(EvermindPageContext.java:395)
    at _RF._jspService(_RF.java:225)
    at com.orionserver.http.OrionHttpJspPage.service(OrionHttpJspPage.java:59)
    at oracle.jsp.runtimev2.JspPageTable.service(JspPageTable.java:462)
    at oracle.jsp.runtimev2.JspServlet.internalService(JspServlet.java:594)
    at oracle.jsp.runtimev2.JspServlet.service(JspServlet.java:518)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:856)
    at com.evermind.server.http.ResourceFilterChain.doFilter(ResourceFilterChain.java:64)
    at oracle.apps.jtf.base.session.ReleaseResFilter.doFilter(ReleaseResFilter.java:26)
    at com.evermind.server.http.EvermindFilterChain.doFilter(EvermindFilterChain.java:15)
    at oracle.apps.fnd.security.AppsServletFilter.doFilter(AppsServletFilter.java:318)
    at com.evermind.server.http.ServletRequestDispatcher.invoke(ServletRequestDispatcher.java:621)
    at com.evermind.server.http.ServletRequestDispatcher.forwardInternal(ServletRequestDispatcher.java:370)
    at com.evermind.server.http.HttpRequestHandler.doProcessRequest(HttpRequestHandler.java:871)
    at com.evermind.server.http.HttpRequestHandler.processRequest(HttpRequestHandler.java:453)
    at com.evermind.server.http.AJPRequestHandler.run(AJPRequestHandler.java:313)
    at com.evermind.server.http.AJPRequestHandler.run(AJPRequestHandler.java:199)
    at oracle.oc4j.network.ServerSocketReadHandler$SafeRunnable.run(ServerSocketReadHandler.java:260)
    at oracle.oc4j.network.ServerSocketAcceptHandler.procClientSocket(ServerSocketAcceptHandler.java:234)
    at oracle.oc4j.network.ServerSocketAcceptHandler.access$700(ServerSocketAcceptHandler.java:29)
    at oracle.oc4j.network.ServerSocketAcceptHandler$AcceptHandlerHorse.run(ServerSocketAcceptHandler.java:879)
    at com.evermind.util.ReleasableResourcePooledExecutor$MyWorker.run(ReleasableResourcePooledExecutor.java:303)
    at java.lang.Thread.run(Thread.java:619)
oracle.apps.fnd.framework.OAException: Application: FND, Message Name: FND_NO_TRANSACTION_ID.
    at oracle.apps.fnd.framework.webui.OAJSPHelper.getTransactionId(OAJSPHelper.java:352)
    at oracle.apps.fnd.framework.webui.OAJSPApplicationRegistry.registerApplicationModule(OAJSPApplicationRegistry.java:133)
    at oracle.apps.fnd.framework.webui.OAJSPApplicationRegistry.registerApplicationModule(OAJSPApplicationRegistry.java:78)
    at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:1300)
    at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:543)
    at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:431)
    at _OA._jspService(_OA.java:212)
    at com.orionserver.http.OrionHttpJspPage.service(OrionHttpJspPage.java:59)
    at oracle.jsp.runtimev2.JspPageTable.service(JspPageTable.java:462)
    at oracle.jsp.runtimev2.JspServlet.internalService(JspServlet.java:594)
    at oracle.jsp.runtimev2.JspServlet.service(JspServlet.java:518)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:856)
    at com.evermind.server.http.ServletRequestDispatcher.invoke(ServletRequestDispatcher.java:713)
    at com.evermind.server.http.ServletRequestDispatcher.forwardInternal(ServletRequestDispatcher.java:370)
    at com.evermind.server.http.ServletRequestDispatcher.unprivileged_forward(ServletRequestDispatcher.java:259)
    at com.evermind.server.http.ServletRequestDispatcher.access$100(ServletRequestDispatcher.java:51)
    at com.evermind.server.http.ServletRequestDispatcher$2.oc4jRun(ServletRequestDispatcher.java:193)
    at oracle.oc4j.security.OC4JSecurity.doPrivileged(OC4JSecurity.java:284)
    at com.evermind.server.http.ServletRequestDispatcher.forward(ServletRequestDispatcher.java:198)
    at com.evermind.server.http.EvermindPageContext.forward(EvermindPageContext.java:395)
    at _RF._jspService(_RF.java:225)
    at com.orionserver.http.OrionHttpJspPage.service(OrionHttpJspPage.java:59)
    at oracle.jsp.runtimev2.JspPageTable.service(JspPageTable.java:462)
    at oracle.jsp.runtimev2.JspServlet.internalService(JspServlet.java:594)
    at oracle.jsp.runtimev2.JspServlet.service(JspServlet.java:518)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:856)
    at com.evermind.server.http.ResourceFilterChain.doFilter(ResourceFilterChain.java:64)
    at oracle.apps.jtf.base.session.ReleaseResFilter.doFilter(ReleaseResFilter.java:26)
    at com.evermind.server.http.EvermindFilterChain.doFilter(EvermindFilterChain.java:15)
    at oracle.apps.fnd.security.AppsServletFilter.doFilter(AppsServletFilter.java:318)
    at com.evermind.server.http.ServletRequestDispatcher.invoke(ServletRequestDispatcher.java:621)
    at com.evermind.server.http.ServletRequestDispatcher.forwardInternal(ServletRequestDispatcher.java:370)
    at com.evermind.server.http.HttpRequestHandler.doProcessRequest(HttpRequestHandler.java:871)
    at com.evermind.server.http.HttpRequestHandler.processRequest(HttpRequestHandler.java:453)
    at com.evermind.server.http.AJPRequestHandler.run(AJPRequestHandler.java:313)
    at com.evermind.server.http.AJPRequestHandler.run(AJPRequestHandler.java:199)
    at oracle.oc4j.network.ServerSocketReadHandler$SafeRunnable.run(ServerSocketReadHandler.java:260)
    at oracle.oc4j.network.ServerSocketAcceptHandler.procClientSocket(ServerSocketAcceptHandler.java:234)
    at oracle.oc4j.network.ServerSocketAcceptHandler.access$700(ServerSocketAcceptHandler.java:29)
    at oracle.oc4j.network.ServerSocketAcceptHandler$AcceptHandlerHorse.run(ServerSocketAcceptHandler.java:879)
    at com.evermind.util.ReleasableResourcePooledExecutor$MyWorker.run(ReleasableResourcePooledExecutor.java:303)
    at java.lang.Thread.run(Thread.java:619)

- Make sure you do not have Invalid objects, incase you have, run the following:

login to the DB node as sysdba

SQL> @$ORACLE_HOME/rdbms/admin/utlrcmp.sql

Package created.

No errors.
SQL> exec utl_recomp.recomp_parallel(6);

PL/SQL procedure successfully completed.

SQL>
 

- Then run "$FND_TOP/sql/FNDDLTMP.sql" to Purge Inactive Sessions:

SQL> @$FND_TOP/sql/FNDDLTMP.sql

PL/SQL procedure successfully completed.


70 rows deleted.


78 rows deleted.


265 rows deleted.


0 rows deleted.


0 rows deleted.


0 rows deleted.


0 rows deleted.


Commit complete.


PL/SQL procedure successfully completed.

Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[apfintest@erpmigrate02 ~l]$

 

- Refresh your login page (no need to restart any services).
- Disable "FND:Diagnostics" profile option

update fnd_profile_option_values set profile_option_value='NO' where level_id=10001 and profile_option_id = (select fpo.profile_option_id from fnd_profile_options fpo,fnd_profile_options_tl fpot where upper(fpot.user_profile_option_name) like upper('FND: Diagnostics') and fpo.profile_option_name=fpot.profile_option_name);

commit;

=> logout and relogin

select fnd_profile.value('FND_DIAGNOSTICS') "FND: Diagnostics" from dual;

Regards,

Mohamed

Unable to open Oracle Report Manager outputs using MS Excel

Issue:

The user comes with a complain that he is not able to view report output in Excel format, the below error appears on the User's browser:

 

Cause:

IE security rules prevent the creation of an object using ActiveX Component.

Solution:

1- Add your application URL to the trusted sites (Internet Explorer > Tools > Internet Options > Security tab > click on the trusted sites sign)

Click on Sites button:

Click "Add" then close.

2- Change the security options for the trusted sites by clicking on "Custom Level" button then change the value of "Initialize and script ActiveX controls not marked as safe for scripting" to "Enable":

3- Change the value of "Enable XSS filter" to "Disable":

4- Open MS Excel and do the following:

File > Options > Trust Center > Trust Center Settings > Macro Settings

Ensure that "Trust access to the VBA project object model" is checked.

How to add a link to Oracle applications 12.1 home page using personalization

To start with this activity you need to change profile option “Personalize Self-Service Defn” for the desired user to Yes.

Login to the application using the user who have the personalization enabled.

Click on "Personalize Page" link.

Change the Personalization Level to "Function: Applications Configurable Home Page" then click Go.

Select "Content" tab as shown in the below snap:

Select the region that you want to include the link inside it, the red arrow shows the region I have selected to put my Link:

The next step we will define the link details:

Item Style: Link

ID: link1

Destination URI: http://erpmigrate02.rsteel.com:8010/OA_HTML/HR_Policy.pdf

Text: *** HR Policy Manual ***

Click Apply button.

The newly added link will be placed by default at the bottom, we need to use the "reorder" link to put it in the requested place:

Click Apply button.

 At the end it will look like:

On the OS level, login as the MT owner, in my case "apfintest" and put the .pdf file in the correct location:

[apfintest@erpmigrate02 ~]$ cd $OA_HTML
[apfintest@erpmigrate02 html]$ pwd
/Test/FINTEST/apps/apps_st/comn/webapps/oacore/html
[apfintest@erpmigrate02 html]$ ls -l HR_Policy.pdf
-rw-r--r-- 1 apfintest oinstall 7096969 Jan 29  2013 HR_Policy.pdf
[apfintest@erpmigrate02 html]$

Click on the "home" link again, and you will see your link.

How to dorp/recreate your Undo Tablespaces

> It is better to put the database in the mount mode while doing these activities.
> If the database is up and running you may face "ORA-30013: undo tablespace '%s' is currently in use." error while dropping the old Undo tablespace, this error indicates that you must wait for the undo tablespace to become unavailable. In other words, you must wait for existing transactions to be committed or rolled back. Also be aware that on some platforms, disk space is not freed to the OS until the database is restarted.  The disk space will remain "allocated" from the OS perspective until the database restart.

1- Collect the required data about your Undo Tablespaces:

SQL> SELECT TABLESPACE_NAME FROM DBA_TABLESPACES WHERE CONTENTS='UNDO';

TABLESPACE_NAME
------------------------------
APPS_UNDOTS1

 SQL> sho parameter undo

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
undo_management              string     AUTO
undo_retention                 integer     900
undo_tablespace              string     APPS_UNDOTS1
SQL>

SQL> SELECT * FROM DBA_DATA_FILES WHERE TABLESPACE_NAME='APPS_UNDOTS1';

FILE_NAME                 FILE_ID TABLESPACE_NAME            BYTES      BLOCKS STATUS    RELATIVE_FNO AUT   MAXBYTES    MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS ONLINE_
---------------------------------------- ---------- ------------------------------ ---------- ---------- --------- ------------ --- ---------- ---------- ------------ ---------- ----------- -------
/oracle/PROD/db/apps_st/data/undo01.dbf    379 APPS_UNDOTS1           4194304000      512000 AVAILABLE        379 YES 3.4360E+10      4194302     12800 4194238464      511992 ONLINE
/oracle/PROD/db/apps_st/data/undo02.dbf     19 APPS_UNDOTS1           4404019200      537600 AVAILABLE         19 YES 3.4360E+10      4194302     12800 4402970624      537472 ONLINE

SQL>

- Total size of the Undo Tablespace:

SQL> SELECT sum(bytes)/1024/1024/1024 FROM DBA_DATA_FILES WHERE TABLESPACE_NAME='APPS_UNDOTS1';

SUM(BYTES)/1024/1024/1024
-------------------------
        8.0078125

SQL> !ls -ltrh /oracle/PROD/db/apps_st/data/undo0*
-rw-r----- 1 orstage dba 4.2G Nov 16 16:49 /oracle/PROD/db/apps_st/data/undo02.dbf
-rw-r----- 1 orstage dba 4.0G Nov 16 16:49 /oracle/PROD/db/apps_st/data/undo01.dbf

SQL>

2- Create a new undo tablespace:

SQL> create undo tablespace APPS_UNDOTS2 datafile '/oracle/PROD/db/apps_st/data/undoNew.dbf' size 1000M;

3- Set the new tablespace as the undo tablespace to be used:

SQL> alter system set undo_tablespace=APPS_UNDOTS2 scope=spfile;

SQL> sho parameter undo

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
undo_management              string     AUTO
undo_retention                 integer     900
undo_tablespace              string     APPS_UNDOTS2
SQL>   

4- Drop the old undo tablespace:

    SQL> drop tablespace APPS_UNDOTS1 including contents and datafiles;

5- Recreate the old undo tablespace with a smaller size:

    SQL> create undo tablespace APPS_UNDOTS1 datafile '/oracle/PROD/db/apps_st/data/undo01.dbf' size 10240M '/oracle/PROD/db/apps_st/data/undo02.dbf' size 10240M;

6- Set the old tablespace as the undo tablespace to be used:

    SQL> alter system set undo_tablespace=APPS_UNDOTS1 scope=spfile;

 7- Drop the temp undo tablespace:

    SQL> drop tablespace APPS_UNDOTS2 including contents and datafiles;

Regards,
Mohamed

Troubleshooting Concurrent Request ORA-20100 error

When running "GMFRLAYR module: GMF Recreate Batch / Period Layers" Concurrent request we got the below error:

ORACLE error 20100 in FDPSTP Cause: FDPSTP failed due to ORA-20100: Error: FND_FILE failure. Unable to create file, o1152922.tmp in the directory, /usr/tmp. You will find more information in the request log. ORA-06512: at "APPS.FND_FILE", line 417 

- All configurations seems to be OK:

As the DB owner:

SQL> sho parameter utl_file_dir

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
utl_file_dir                 string     /usr/tmp, /usr/tmp, /Test/FINT
                         EST/db/tech_st/11.1.0/appsutil
                         /outbound/FINTEST_erpmigrate02
                         , /usr/tmp
SQL> 

As the MT owner:

[apfintest@erpmigrate02 ~]$ echo $APPLPTMP
/usr/tmp
[apfintest@erpmigrate02 ~]$ ls -l /usr/|grep tmp
lrwxrwxrwx   1 root root     10 Nov  4 13:01 tmp -> ../var/tmp
[apfintest@erpmigrate02 ~]$ ls -l /var|grep tmp
drwxrwxrwx  3 root root  4096 Nov 13 11:31 tmp
[apfintest@erpmigrate02 ~]$ 

- We have enough space for new logs:

[apfintest@erpmigrate02 ~]$ df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/sda2             254G  5.7G  235G   3% /
/dev/sdb2             943G  348G  547G  39% /Oracle
/dev/sdb1             946G  348G  551G  39% /Test
/dev/sda1             289M   32M  243M  12% /boot
tmpfs                 7.9G   17M  7.9G   1% /dev/shm
[apfintest@erpmigrate02 ~]$

- Trying to use the package used by the application to write the file:

SQL> sho user
USER is "APPS"
SQL> exec FND_FILE.PUT_LINE(FND_FILE.LOG, 'testing /usr/tmp folder');

PL/SQL procedure successfully completed.

SQL> !ls -ltr|tail
-rwxrwxrwx 1 orscm     dba           0 Nov 13 11:40 o1152928.tmp
-rwxrwxrwx 1 orscm     dba           0 Nov 13 11:40 l1152928.tmp
-rwxrwxrwx 1 orscm     dba           0 Nov 13 11:41 o1152959.tmp
-rwxrwxrwx 1 orscm     dba           0 Nov 13 11:41 l1152959.tmp
-rwxrwxrwx 1 orscm     dba           0 Nov 13 11:41 o1152946.tmp
-rwxrwxrwx 1 orscm     dba           0 Nov 13 11:41 l1152946.tmp
-rwxrwxrwx 1 orscm     dba           0 Nov 13 11:41 o1152916.tmp
-rwxrwxrwx 1 orscm     dba           0 Nov 13 11:41 l1152916.tmp
-rw-r--r-- 1 orfintest oinstall      0 Nov 13 11:41 o1154916.tmp
-rw-r--r-- 1 orfintest oinstall     24 Nov 13 11:41 l1154916.tmp

SQL> !cat l1154916.tmp
testing /usr/tmp folder

SQL>

- At last I found that the /usr/tmp folder is shared between two instances! and the same file name got generated by both instances!

- At this point you have two options:

a)

1. Stop all MT services.
2. Change $APPLPTMP value to another directory "ensure it is the first value listed by utl_file_dir DB parameter"

3. Run autoconfig on the MT nodes

4. Restart all services
5. Re-test the failed process

b)

As a quick solution "should not be applied unless you really need an urgent solution"

[root@erpmigrate02 tmp]# chmod 777 /usr/tmp/*
[root@erpmigrate02 tmp]# 

re-run your CR again...

Regards,

Mohamed