You have encountered an unexpected PLSQL Error, Please contact System Administrator

You have encountered an unexpected PLSQL Error, Please contact System Administrator

Enable FND Debug using followin profile options:

FND: Debug Log Enabled Yes
FND: Debug Log Filename <empty>
FND: Debug Log Level STATEMENT
FND: Debug Log Mode Asynchronous with Cross-Tier Sequencing
FND: Debug Log Module %

2. Run the following SQL and and write down this number
SQL> select max(log_sequence) from fnd_log_messages;

3. Reproduce the issue and run the following SQL again to get the relevant information:
SQL> select * from fnd_log_messages where log_sequence > NUMBER_IDENTIFIED_BEFORE_IN_SQL_STATEMENT_AT_STEP_2 order by log_sequence;

Verify in the fnd_log_messages, you can see the following:

106500495|fnd.plsql.oid.fnd_ldap_wrapper.create_user: |ORA-31202: DBMS_LDAP: LDAP client/server error: Invalid credentials|
106500496|fnd.plsql.oid.fnd_ldap_wrapper.create_user: |l_err_code :FND_SSO_UNEXP_ERROR, l_tmp_str :ORA-31202: DBMS_LDAP: LDAP client/server error: Invalid credentials|
106500497|fnd.plsql.APP_EXCEPTION.RAISE_EXCEPTION.dict_auto_log|Unabled to call fnd_ldap_wrapper.create_user due to the following reason:
An unexpected error occurred. Please contact your System Administrator. (USER_NAME=SHARMAJ1)|

If this is the case, then during cloing, DBAs must have screwed up OID registration from your live system.

Do a fresh registration using txkrun.pl:
SQL> delete from fnd_user_preferences where user_name='#INTERNAL'
$FND_TOP/bin/txkrun.pl -script=SetSSOReg -registerinstance=yes
$FND_TOP/bin/txkrun.pl -script=SetSSOReg -registeroid=yes -provisiontype=3

Assigned Responsibilities are not visible to User

Assigned Responsibilities are not visible to User

Following shortcut solution worked many times in 11i and R12 as well

1. Run the following requests in the order below:
a. "Create FND_RESP WF Roles"
b. "Sync responsibility role data into the WF table.(obsolete in R12)"
c. "Synchronize WF LOCAL tables"
d. "Workflow Directory Services User/Role Validation"

2. End date and associated userid, un-end date it, and change the password.

3. Retest the issue.

There is an exhaustive troubleshooting steps for this issue, but above solution has worked in most of the cases for me, except very few, where I had to walk that extra mile.

Make sure Workflow Background Process is properly Scheduled. Follow this for best practice on this:
http://mkkoracleapps.blogspot.com/2012/12/workflow-related-scheduled-concurrent.html

eBusiness Suite Data Masking/Data Scrambling/Data Anonymization

eBusiness Suite Data Masking/Data Scrambling/Data Anonymization

Recently I got a chance to work on a eBusiness Data Masking requirement. Generally, few members try to differentiate between Data Masking, Data Scrambling and Data Anonymization, unfortunately that is bit misconception.

Definition what Oracle Provides:
Data masking, also known as data scrambling or data anonymization, is the process of obscuring sensitive information copied from a production database with realistic, scrubbed data based on masking rules, to a test or non-production database

I would like to share some learnings from this masking exercise:

1. FND_USER_MASKING_EXEMPTIONS/Exempt Users From Masking

By default seeded users(like SYSADMIN, GUEST) whose USER_ID is less than 1000 are exempted from masking. But if all other usernames are masked then testing users would be impacted, so prepare the list, who need access in the eBusiness and put them in FND_USER_MASKING_EXEMPTIONS.

Data Masking Pack Provides two SQLs
fndusmaexcr.sql - Creates the FND_USER_MASKING_EXEMPTIONS table.
fndusmaexpo.sql - Provides examples of how to populate the table.

FND_USER_MASKING_EXEMPTIONS >>> Definition >>> USER_ID, SOURCE, CURRENT_NAME, NEW_NAME, NEW_PASSWD
The CURRENT_NAME column contains the name of the user to exempt from standard masking.

2. What template needs to be used, JE(Standard) or JG, is decided by the table JE_ES_MODELO_190_ALL.
select OWNER  from all_tables  where TABLE_NAME = 'JE_ES_MODELO_190_ALL';

Use the standard version of the template if your production database includes the table JE_ES_MODELO_190_ALL in the JE schema. Use the JG version of the template if your production database includes the table JE_ES_MODELO_190_ALL in the JG schema.

High Level Steps for Data Masking is as follows

1. Compile all the objects before you start Generate Mask.
2. Generate Masks Include, Patching Activity for Data Masking Template Patch, Steps for Exempt Users, Create Tables using Pre Generate SQL, Edit Masking Definition, Remove Errors/Warnings.
3. If Environment is SSO Enabled, remove SSO using txkrun.pl deregister OR Delete Directly from FND_USER_PREFERENCES. If any LDAP operation is running like DIP(like OID to eBusiness synch), then there can be deadlock in the database and ORA-00060: deadlock detected while waiting for resource would be reported.
4. Execute the Mask.
5. Save the masking script and template, so that you can run on all instances where masking is required, and after each clone.

*** If You Forget to Provide Required Grant
Provide DBMS_CRYPTO/EXEMPT ACCESS POLICY Grant to SYSTEM, missing this become problemetic, as you need to recreate various triggers, taking the code from some other instance. Otherwise masking would not happen.
GRANT EXECUTE ON DBMS_CRYPTO TO SYSTEM;
GRANT EXEMPT ACCESS POLICY TO SYSTEM;

*** Stop the application, and start the Concurrent Managers Only
Note that several concurrent requests are started during the mask (Employee Update Program and DQM Staging Program). Verify that these programs complete successfully after bringing up the Concurrent Manager. Note that the Employee Update Program may log errors of "Update of Name was not done as the update would result in a duplicate supplier record".

In the Masking execution spool, you would see like following:
Completed Data Masking. Starting cleanup phase.                                 
Starting cleanup of generated procedures                                        
Completed cleanup of generated procedures                                       
Script execution complete                                                       
Program: fndusmaexpr                                                            
Process FND_USER_MASKING_EXEMPTIONS                                             
======================================                                          
... setup context.                                                              
... process records.                                                            
======================================                                          
End program - successful                                                        
request_id = 1152548                                                            
request_id = 1152549                                                            
request_id = 1152550                                                            
EBS drop table - map_scl_dk                                                     
EBS drop table - map_scl_nl_1                                                   

Make sure all these requests are completed successfully.

*** Customisation On Data Masking Template
For Custom columns you can add in this template rule, You can remove if you do not need the seeded template to mask some columns.

*** Add Datafile to SYSTEM Tablespace
ALTER TABLESPACE APPS_TS_TX_IDX ADD DATAFILE '+DATA01' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 4096M;
ALTER TABLESPACE APPS_TS_TX_IDX ADD DATAFILE '+DATA01' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 4096M;

*** Add Tempfile to Temp Tablespace
ALTER TABLESPACE TEMP11 add tempfile '+DATA01' SIZE 1024M;
ALTER TABLESPACE TEMP11 add tempfile '+DATA01' SIZE 1024M;

**** Rebuild all INVALID/UNUABLE Indexes
ALTER INDEX <OWNER>.<INDEX_NAME> REBUILD;

**** After Masking None of the Business Users are able to Access the System
If I remove the employee from the user and reset the password then that was able to access the system. You can reassign back the employee to the user. This is not a feasible solution if NONLIVE instances are accessed by many users.

"Workflow Directory Services User/Role Validation" Parameters : 100000, Yes, No, No
"Workflow Directory Services User/Role Validation" Parameters : 100000, No, Yes, No   

1. Run the following requests in the order below: 
a. "Create FND_RESP WF Roles"
b. "Sync responsibility role data into the WF table.(obsolete in R12)"
c. "Synchronize WF LOCAL tables"
d. "Workflow Directory Services User/Role Validation"

Make sure Workflow Background Process is properly Scheduled. Follow this for best practice on this:
http://mkkoracleapps.blogspot.com/2012/12/workflow-related-scheduled-concurrent.html

SQLPLUS APPS Connection is intermittently failing

SQLPLUS APPS Connection is intermittently failing

Reason for this Issue: Invalid TNS address supplied or destination is not listening. This error can also occur because of underlying network transport problems.

For my case it was wrong gateway configuration.

Successful:

mkkebstestu1@mkktestserver1:/home/mkkebstestu1>sqlplus apps/*************

SQL*Plus: Release 8.0.6.0.0 - Production on Mon Oct 29 15:42:35 2012

(c) Copyright 1999 Oracle Corporation.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> 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

Unsuccessful Attempts:

mkkebstestu1@mkktestserver1:/home/mkkebstestu1>sqlplus apps/************

SQL*Plus: Release 8.0.6.0.0 - Production on Mon Oct 29 15:46:49 2012

(c) Copyright 1999 Oracle Corporation.  All rights reserved.

ERROR:
ORA-12203: TNS:unable to connect to destination


Enter user-name: 

mkkebstestu1@mkktestserver1:/home/mkkebstestu1>netstat -rn
Routing tables
Destination        Gateway           Flags   Refs     Use  If   Exp  Groups

Route Tree for Protocol Family 2 (Internet):
default            172.30.0.1        UG        1     34949 en2      -      -   =>
default            172.30.0.10       UG        7    795361 en2      -      -
60.60.60.1         127.0.0.1         UGHS      0      1733 lo0      -      -   =>
60.60.60.1         60.60.60.1        UHSb      0      1733 en2      -      -   =>
60.60.60.1/24      60.60.60.1        U         0         0 en2      -      -
60.60.60.255       60.60.60.1        UHSb      0         4 en2      -      -
127/8              127.0.0.1         U        14    781913 lo0      -      -
172.30.0.0         172.30.0.92       UHSb      0         0 en2      -      -   =>
172.30/24          172.30.0.92       U         2   6576453 en2      -      -
172.30.0.92        127.0.0.1         UGHS      1       458 lo0      -      -
172.30.0.255       172.30.0.92       UHSb      0         4 en2      -      -

Route Tree for Protocol Family 24 (Internet v6):
::1                ::1               UH        0        92 lo0      -      -
mkkebstestu1@mkktestserver1:/home/mkkebstestu1>

>>>> If you notice the netstat spool there are two gatways are defined. This is not a correct configuration. Highlighted in RED.


Solution: Flush the Gateway from smitty

mkktestserver1[/] # smitty route

Press Enter AFTER making all desired changes.

                                                        [Entry Fields]
  Flush Routing Table in the Current Running System   yes                                                                          +
  Flush IPV4 Routes in the Configuration Data Base    yes                                                                          +
  Flush IPV6 Routes in the Configuration Data Base    no                                                                           +
   (effective in the next system restart)

Significance of "Sign-On:Audit Level" Profile Option

Significance of "Sign-On:Audit Level" Profile Option

Sign-On: Audit Level can be set at 4 different levels.  The level you specify will determine which Change Tracking information Oracle eBusiness would Capture

The four levels (from lowest to highest level of audit) and the information that they capture are:

1. When Site Level Value is None
Tracks no additional info.

2. When Site Level Value is User
Tracks: who signs on to Oracle Apps, the time users log on and off, the terminals in use

3. When Site Level Value is Responsibility
Tracks: User Info, the responsibilities user choose, how much time users spend using each responsibility

4. When Site Level Value is Form
Tracks: User Info, Responsibility Info, the forms users choose, how long users spend using each form

Based on the level chosen, the information captured gets stored in the following tables:
FND_LOGINS 
FND_LOGIN_RESPONSIBILITIES 
FND_LOGIN_RESP_FORMS 

Truncate Login Related Tables in NONLIVE Instance/Cloned Instance

Truncate Login Related Tables in NONLIVE Instance/Cloned Instance

This I generally do a post clone steps to purge the login related information.

$ADMIN_SCRIPTS_HOME/adopmnctl.sh stopall

truncate table APPLSYS.FND_LOGINS;
truncate table APPLSYS.FND_LOGIN_RESP_FORMS;
truncate table APPLSYS.FND_LOGIN_RESPONSIBILITIES;
truncate table APPLSYS.FND_UNSUCCESSFUL_LOGINS;
truncate table ICX.ICX_SESSIONS;
truncate table ICX.ICX_FAILURES;
truncate table ICX.ICX_SESSION_ATTRIBUTES;
truncate table ICX.ICX_TEXT;
truncate table ICX.ICX_CONTEXT_RESULTS_TEMP;
truncate table ICX.ICX_TRANSACTIONS;
truncate table APPLSYS.FND_SESSION_VALUES;

$ADMIN_SCRIPTS_HOME/adopmnctl.sh startall

Also if you are not able to execute purge signon conc prog, due to huge volume of data in the underlying tables then truncate is an option that you can pursue. OR you have to delete in multiple iterations.

Relevant Concurrent Program to Purge: Purge Signon Audit Data

Purge Inactive Sessions E-Business suite R12: $FND_TOP/sql/FNDDLTMP.sql

You have encountered an unexpected PLSQL Error, Please contact System Administrator

You have encountered an unexpected PLSQL Error, Please contact System Administrator

Justification: Cloning has de-registered the EBS instance from OID, and that is the reason, Autolink of user from EBStoOID is not happening.

If you manually update the user with the GUID information, single sign-on is working fine.

When enabled FND Debug, in fnd_log_messages I noticed the following:

106500486|fnd.plsql.oid.fnd_ldap_util.get_oid_session: |ORA-31202: DBMS_LDAP: LDAP client/server error: Invalid credentials|

106500487|fnd.plsql.oid.fnd_ldap_util.c_get_oid_session: |ORA-31202: DBMS_LDAP: LDAP client/server error: Invalid credentials|

106500488|fnd.plsql.oid.fnd_oid_plug.get_realm_dn: |END ->ORA-31202: DBMS_LDAP: LDAP client/server error: Invalid credentials|

106500489|fnd.plsql.oid.fnd_ldap_wrapper.create_user: |ORA-31202: DBMS_LDAP: LDAP client/server error: Invalid credentials|

**** This happened during one of my GOLIVE and during the cutover we window we had the cloning of production is also involved...

**** Solution is straight forward... OID component has been deregistered during the cloning process...

Register using following command:

$FND_TOP/bin/txkrun.pl -script=SetSSOReg -registeroid=yes -provisiontype=4

mod_oc4j: Failed to find a failover oc4j process for session request for destination: application://oacore (no island or jgroup).

mod_oc4j: Failed to find a failover oc4j process for session request for destination: application://oacore (no island or jgroup).

<<<<<<<<<<<<<<<Explanation>>>>>>>>>>>>>>>

Islands are no longer used within iAS version 10.1.3 in EBS R12, the new term is "cluster". A cluster is stated as "two or more OC4J server nodes hosting the same set of applications". A standard instance of Applications Release12 has some aspects of clustering enabled by default. There are many components to a cluster and all of them have not been configured in Oracle E-Business suite R12. So the error may change as following :

mod_oc4j: Failed to find a failover oc4j process for session request for destination: application://form (no cluster or jgroup).

<<<<<<<<<<<<<<<Reason>>>>>>>>>>>>>>>

??? Load: e.g. Too many clients connecting to the application, The number of requests are high enough that the process cannot respond to new requests prior to reaching the timeout period.

??? Performance: Heavily loaded JVM or Lack of enough memory for JVM

??? Code: Internal to Application Server, one of the components are not operating correctly or efficiently. External to ApplicationServer, E-Business code (seeded or custom code) causing memory leaks.

??? Lack of free ports for AJP communication

<<<<<<<<<<<<<<<Solution Approach#1: Resize oc4j oacore jvm heap size as well as number of oacore processes in context file>>>>>>>>>>>>>>>


The number of jvms (oc4j in R12) is configured by the autoconfig variables s_oacore_nprocs, s_disco_nprocs, s_forms_servlet_nprocs (11i), s_forms_nprocs (R12) and s_xmlsvcs_nprocs.

In 11i, those changes are made in the $IAS_ORACLE_HOME/Apache/Jserv/etc/jserv.conf file, while in R12 in the $ORA_CONFIG_HOME/10.1.3/opmn/conf/opmn.xml

Thumb Rule: 1 active JVM/OC4J instance per CPU core.  So if you have 8 CUPs of dual core, then you can configure upto 16 JVM. 1 JVM handles 100 Users, so based on the user load configure JVMs accordingly.


<<<<<Solution Approach#2: Proper Heap Configuration >>>>>

The heap is configured by s_oacore_jvm_start_option, s_forms_jvm_start_options in R12. For 12.1 and higher start with the following and increase as per the user load:
-Xmx1024M -Xms512M -XX:MaxPermSize=256M
      -XX:NewRatio=2 -XX:+PrintGCTimeStamps

Also, Add the following parameter to the DBC file:
JDBC\:oracle.jdbc.maxCachedBufferSize=262144

In JDK 1.6, the JVM detects that you have a server class machine (2 or more CPUs with 2GB or more memory), and will automatically enable Parallel Throughput Garbage Collector. The number of GC threads defaults to the number of CPUs on the machine. If you are running multiple JVMs on the same machine, or if your machine has more than 2 CPUs, to avoid the GC threads to be overly active you should reduce the number of GC threads by using:

-XX:+UseParallelGC -XX:ParallelGCThreads=2

Note: Although the setup of those parameters can be temporarily done manually changing (jserv.conf and jserv.properties in 11i; opmn.xml in R12), the correct way to update those parameters is using the context editor, update the variables s_oacore_nprocs and s_forms_servlet_nprocs, and then run autoconfig.

<<<<<Solution Approach#3: Long Running JVM >>>>>

Make sure to have the following setting in the jserv.properties file:
wrapper.bin.parameters=-DLONG_RUNNING_JVM=true

<<<<< Solution Approach#4: Java Cache Port Value >>>>>

The port value for java cache (s_java_object_cache_port) in context file should match profile option value JTF_DIST_CACHE value. 

 + The value for java cache port can be check in Context File -> parameter "s_java_object_cache_port" 
 + The value of the profile option JTF_DIST_CACHE can be found using the sql : 
      select fnd_profile.value('JTF_DIST_CACHE_PORT') from dual

<<<<< Solution Approach#5: Same Server Multiple EBS Instances >>>>>>

If the E-Business suite environment has multiple application servers then ensure that the following Autoconfig variables point to a "local disk". Hence this must be checked and changed on each application tier context file. 
     s_lock_pid_dir 
     s_pids_dir 
     s_web_pid_file

If the above variables are reset then run autoconfig for the changes to be effective.

<<<<< Solution Approach#6: EBS Instances with Load Balancer >>>>>>

Refer Oracle Support Note: Using Load-Balancers with Oracle E-Business Suite Release 12 (Doc ID 380489.1)

1. Make sure that the load balancer always implements Session Stickiness (also named Session Binding). This ensures that every time a request with an existing session is received it will be sent to the HTTP Server which created the session and therefore the routingID will always be able to be correctly decoded.

<<<<<<<<<<<<<<<Solution Approach#7: AJP Ports>>>>>>>>>>>>>>>

Ensure the port range used by AJP protocol by various middle tier components are not occupied by any other service. Run the following command to find the AJP port range.

$grep -i ajp $CONTEXT_FILE 

<ajp_protocol oa_var="s_ajp_protocol">ajp</ajp_protocol> 
<oacore_ajp_portrange oa_var="s_oacore_ajp_portrange" oa_type="PORT" base="21500" step="5" range="5" 
label="OC4J AJP Port Range for Oacore">21500-21504</oacore_ajp_portrange> 
<forms_ajp_portrange oa_var="s_forms_ajp_portrange" oa_type="PORT" base="22000" step="5" range="5" 
label="OC4J AJP Port Range for Forms">22000-22004</forms_ajp_portrange> 
<oafm_ajp_portrange oa_var="s_oafm_ajp_portrange" oa_type="PORT" base="25000" step="5" range="5" 
label="OC4J AJP Port Range for Oafm">25000-25004</oafm_ajp_portrange>

<<<<< Solution Approach#8: Firewall Between EBS Application Servers >>>>>

If any firewall exists between application server (Like firewall between forms/web tier & Concurrent tier etc) then refer the following workaround.

To improve performance, the mod_oc4j component in Oracle HTTP Server(OHS) process maintains open TCP connections to the AJP port within each OC4J components. In situations where a firewall exists between OHS and OC4J, packages sent via AJP are rejected if the connections can be idle for periods in excess of the inactivity timeout of stateful firewalls. However, the AJP socket is not closed as long as the socket remains open, the worker thread is tied to it and is never returned to the thread pool. OC4J will continue to create more threads, and will eventually exhaust system resources. 

Set the following parameters in the mod_oc4j.conf (Present in the directory $IAS_ORACLE_HOME/Apache/Apache/conf) configuration file. The value of Oc4jConnTimeout sets the length of inactivity in seconds, before the session is considered inactive. 

Oc4jUserKeepalive on 
Oc4jConnTimeout 12000 (or a similar value)

Also read, how to monitor JDBC Connections in my blog.
http://mkkoracleapps.blogspot.co.uk/2013/07/how-to-monitor-jdbc-and-jvm-in.html

Cheers!!!
Malay Khawas
Oracle Apps/Fusion DBA

Pin Sequence: Automatic Asset Numbering

There are two methods of doing Automatic Asser Numbering:

Method#1: Make the Sequence No Cache

1. The first and most obvious option would be not to cache the sequence values at all.  But the result of this could be a moderate to severe degradation of the application's performance.
SQL> alter sequence fa_additions_s nocache;

Method#2: Pin the Sequence:

2. A better solution is to prevent sequences from ageing out of the library cache by pinning them using dbms_shared_pool.keep().  Pinning the sequence will prevent the sequence values from being aged out of the cache.  Pinning the sequence is achieved by invoking the rdbms package dbms_shared_pool.keep() as follows:
SQL> exec dbms_shared_pool.keep('APPS.FA_ADDITIONS_S','Q')
 When you shut down a database normally, either "SHUTDOWN NORMAL" or "SHUTDOWN IMMEDIATE", the database takes care of making sure all sequences are "in sequence".  All cached sequence values are verified, and the last_number column in dba_sequences is updated accordingly.  Thus, under normal circumstances and assuming that your sequence has been pinned, you shouldn't lose cached sequence values during a normal shutdown.
 If the database experiences instance failure or a "SHUTDOWN ABORT" statement is issued, you lose any unused cached sequence values.  Oracle may also skip sequence values after  an export and Import and rollbacks.

To achive this either you have to modify the $ORACLE_HOME/bin/dbstart script and incorporate the pinning or you can create after database startup trigger.

Create a Startup Trigger:
create table AGCST.AG_FA_DBSTRT_PIN (
execution_date date
);
CREATE OR REPLACE TRIGGER DB_STRT_FA_PINING AFTER STARTUP ON DATABASE BEGIN
dbms_shared_pool.keep('APPS.FA_ADDITIONS_S','Q');
INSERT INTO AGCST.AG_FA_DBSTRT_PIN (execution_date) select sysdate from dual;
END;
/

This is the trigger I have used for Pinning the object while doing Automatic Gapless Asset Numbering Implementation. Table is created to know that the trigger has fired after every startup

Profile option FND_OBIEE_URL is null

Issue:

Profile option FND_OBIEE_URL is null. Please contact your System Administrator
java.lang.RuntimeException: Profile Option FND_OBIEE_URL is nullat _oa__html._OracleOasis._jspService(_OracleOasis.java:1487)
at oracle.jsp.runtime.HttpJsp.service(HttpJsp.java)
at oracle.jsp.app.JspApplication.dispatchRequest(JspApplication.java(Compiled Code))
at oracle.jsp.JspServlet.doDispatch(JspServlet.java(Compiled Code))
at oracle.jsp.JspServlet.internalService(JspServlet.java(Compiled Code))
at oracle.jsp.JspServlet.service(JspServlet.java)

Solution:

Set the e-Business Suite profile option "FND: Oracle Business Intelligence Suite Enterprise Edition base URL Value" (short name = FND_OBIEE_URL) to the location of the Siebel Analytics Web file 'saw.dll' , which is required for the integration of e-Business Suite with OBIEE.

URL Should be someting like in this in the Profile Option Value:http://<hostname>:9704/analytics/saw.dll?Dashboard

DROP function was hanging for ever, even if you try to recompile the objects it throws ORA-04021

ORA-04021 timeout occurred while waiting to lock object... Same is applicable if the package status is getting invalidated.

drop function AGCST.AG_ASG_CHG_MAIL_PROG;

The background behind it was, this function is used in workflow and the initiated workflow was still accesing the objects.

Get the SID Information which are accessing this object and kill the same:
select * from v$access where owner='AGCST';

select 'ALTER SYSTEM KILL SESSION '''||SID||','||SERIAL#||''';' from gv$session where INST_ID=2 and SID IN(select SID from gv$access
where object like '%AR%CASH%REC%' and type='TABLE'
and INST_ID=1);

Once you complete killing all the SIDs, it will allow you to drop the object.

Removing OAF personalization document in the database using JDR_UTILS package

This method only requires access to the database and must be used with great care to ensure the correct document is deleted. For this method you need to know the document name of the OAF page with the personalization

Run the following commands to show the available personalization documents:

set serverout on;
exec jdr_utils.listcustomizations('/oracle/apps/<product>/<subcategories>/<pagename>');
exec jdr_utils.listcustomizations('/oracle/apps/fnd/framework/navigate/webui/HomePG');
exec jdr_utils.listcustomizations('/oracle/apps/eam/workorder/webui/CreateUpdateWOPG');

This will print the details:
exec jdr_utils.printdocument('/oracle/apps/eam/workorder/lov/server/customizations/site/0/MaterialsVO');
This returns the documentnames of the personalization document for this OAF page.

For example for SITE level on Homepage
/oracle/apps/fnd/framework/navigate/webui/customizations/site/0/HomePG
SQL> exec jdr_utils.listcustomizations('/oracle/apps/eam/workorder/webui/CreateUpdateWOPG');
/oracle/apps/eam/workorder/webui/customizations/site/0/CreateUpdateWOPG
PL/SQL procedure successfully completed.

Backup the personalization document by exporting it to a file

adjava -mx128m -nojit oracle.jrad.tools.xml.exporter.XMLExporter /oracle/apps/<product>/<subcategories>/customizations/<personalizationlevel>/<personalizationid>/ /<pagename> \
-username apps -password <appspwd> -dbconnection "(description=(address_list=(ADDRESS=(PROTOCOL=TCP)(HOST=<myHost>)(PORT=<port>)))(CONNECT_DATA=(SID=<SID>)))" \
-rootdir /tmp
This create an XML document in the /tmp directory, like
/tmp/oracle/apps/<product>/<subcategories>/<personalizationlevel>/<personalizationid>/<pagename>.xml

Delete the personalization document

SQL> exec jdr_utils.deletedocument('/oracle/apps/<product>/<subcategories>/customizations/<personalizationlevel>/<personalizationid>/<pagename>');
For the home page example this is:
SQL> exec jdr_utils.deletedocument('/oracle/apps/fnd/framework/navigate/webui/customizations/site/0/HomePG');
Restart Apache to ensure that Java caching is cleared
To restore the personalization document deleted, use the command (optional)
-adjava -mx128m -nojit oracle.jrad.tools.xml.importer.XMLImporter \
/tmp/oracle/apps/<product>/<subcategories>/<personalizationlevel>/<personalizationid>/<pagename>.xml \
-username apps -password apps -dbconnection "(description=(address_list=(ADDRESS=(PROTOCOL=TCP)(HOST=<myHost>)(PORT=<port>)))(CONNECT_DATA=(SID=<SID>)))" -rootdir /tmp

OAF Personalizations Issue - Temporary disable all Personalizations

In case it's not possible to determine the exact document name of the OAF page failing the following method can be used. The caveat of this method however is that during the change of profile option mentioned this makes *all* personalizations will be disabled so will also affect other pages. So only to be used on TEST environment or during maintenance window for PROD environment.

Update the Profile Option: "Disable Self-Service Personal" = YES
This can be set at SITE and APPLICATION level

Navigate to the OAF page personalized(may be done using another user). It should now open since personalizations are not taken into account. Depending on the situation you have following options:

Cause of the failure is known (e.g. last change made)
Revert the personalization step causing the problem
Apply the changes

Cause of the failure is unknown
Click pencil "Manage Personalizations"
Now Deactivate or Delete the personalization document

The difference between Deactivate and Delete is that in the case of Deactivating the personalization document in the MDS repository is not removed, while this is the case for Delete. Especially when there are multiple personalization documents, but it's not clear which causes the problem the 'Deactivate' allows these to be tested individually without the need to reload or recreate the personalization documents. 

After fixing the problem:  "Disable Self-Service Personal" = NO (for the level it was set to YES)

OAF Page Migration - adjava XMLImporter

1. SCP the Directory Structure from Source(DEV) to Target(CRP2).

As an example we are migrating cwsa OAF Pages, then SCP the ${JAVA_TOP}/xxcto/oracle/apps/eam/cwsa directory from DEV to CRP2.
Use the following command using CRP2 applmgr user:
$ scp -r appldev@mkkapplserver1:${xx_srcjavatopxx}/xxcto/oracle/apps/eam/cwsa/* ${JAVA_TOP}/xxcto/oracle/apps/eam/cwsa/

2. Execute XMLImporter Command

cd ${JAVA_TOP}/xxcto/oracle/apps/eam/cwsa/
find . -name "*.xml" -print | grep webui | wc -l

This would give you the name of all the XMLs required to be imported, likes LOVs..... DO NOT miss any LOVs......

Go to the Directory where .xml file is copied inside the webui directory.
adjava oracle.jrad.tools.xml.importer.XMLImporter XXCTOCreatePropVehiclePG.xml -username apps -password ${xx_appspassword} -rootdir ${JAVA_TOP} -dbconnection "(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (COMMUNITY = tcp.world)(PROTOCOL = TCP) (Host = mkkapplserver1.mkkdomain.local)(Port = 1525)))(CONNECT_DATA = (SID = CRP2)))"
Make sure ADJAVA Import completed Successfully. This would load the OAF Pages to the MDS Repository.

3. Restart OACORE Services

. ${HOME}/db_apps_env.sh
. ${xx_scripts_top}/db_apps_env.sh
${ADMIN_SCRIPTS_HOME}/adoacorectl.sh stop
${ADMIN_SCRIPTS_HOME}/adoacorectl.sh start

After Cloning Managers are not coming up

Method#1:

● Clean the FND_NODE Info:
EXEC FND_CONC_CLONE.SETUP_CLEAN;
COMMIT;
EXIT;
●● Run Autoconfig on DB node and then Apps Node
●●● Run $FND_TOP/patch/115/sql/afdcm037.sql script
●●●● Start Services (adstrtal.sh)


Method#2:

● Ensure Concurrent :GSM Enabled profile is set to ‘Y’
●● Run $FND_TOP/patch/115/sql/afdcm037.sql

●●● Do Relinking of FND Conc Libraries:
cd $FND_TOP/bin
adrelink.sh force=y "fnd FNDLIBR"
adrelink.sh force=y "fnd FNDSM"
adrelink.sh force=y "fnd FNDFS"
adrelink.sh force=y "fnd FNDCRM"

Before bringing the managers up, execute CMCLEAN….. In most of the cases it helped….

FNDLOAD Utility - Some Ready Reference

CUSTOM_MODE=FORCE in Upload

FNDLOAD apps/${xx_appspassword} O Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct AG_HR_EMP_RETIRE_PROG.ldt CUSTOM_MODE=FORCE


MENU

FNDLOAD apps/${xx_appspassword} O Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct <Menu_Name>.ldt MENU MENU_NAME="<Menu_Name>"
FNDLOAD apps/${xx_appspassword} O Y UPLOAD $FND_TOP/patch/115/import/afsload.lct <Menu_Name>.ldt


CONCURRENT PROGRAM

FNDLOAD apps/${xx_appspassword} O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct <ccm_shrt_nm>.ldt PROGRAM APPLICATION_SHORT_NAME="ROI" CONCURRENT_PROGRAM_NAME="<ccm_shrt_nm>"
FNDLOAD apps/${xx_appspassword}  O Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct <ccm_shrt_nm>.ldt


REQUEST_SET

FNDLOAD apps/${xx_appspassword}  O Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct <req_set>.ldt REQ_SET_LINKS REQUEST_SET_NAME="<req_set>"
FNDLOAD apps/${xx_appspassword}  0 Y UPLOAD @FND:patch/115/import/afcprset.lct <req_set>.ldt REQ_SET_LINKS



RESPONSIBILITIES

FNDLOAD apps/${xx_appspassword} O Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct <Resp_Key>.ldt FND_RESPONSIBILITY RESP_KEY="<Resp_Key>"
FNDLOAD apps/${xx_appspassword} O Y UPLOAD $FND_TOP/patch/115/import/afscursp.lct <Resp_Key>.ldt FND_RESPONSIBILITY




VALUE_SET

FNDLOAD apps/${xx_appspassword} 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct <val_set>.ldt VALUE_SET FLEX_VALUE_SET_NAME=<val_set>
FNDLOAD apps/${xx_appspassword} O Y UPLOAD $FND_TOP/patch/115/import/afffload.lct <val_set>.ldt




REQUEST GROUP

FNDLOAD apps/${xx_appspassword} O Y DOWNLOAD $FND_TOP/patch/115/import/afcpreqg.lct <req_grp>.ldt REQUEST_GROUP REQUEST_GROUP_NAME="<req_grp>" APPLICATION_SHORT_NAME="ROI"
FNDLOAD apps/${xx_appspassword} O Y UPLOAD $FND_TOP/patch/115/import/afcpreqg.lct <req_grp>.ldt




PRINTER STYLES

FNDLOAD apps/${xx_appspassword} O Y DOWNLOAD $FND_TOP/patch/115/import/afcppstl.lct <prntr_styl>.ldt STYLE PRINTER_STYLE_NAME="<prntr_styl>"
FNDLOAD apps/${xx_appspassword} O Y UPLOAD $FND_TOP/patch/115/import/afcppstl.lct <prntr_styl>.ldt




ALERT

FNDLOAD apps/${xx_appspassword} O Y DOWNLOAD $ALR_TOP/patch/115/import/alr.lct <alert>.ldt ALR_ALERTS APPLICATION_SHORT_NAME="FND" ALERT_NAME="<alert>"
FNDLOAD apps/${xx_appspassword} O Y UPLOAD $ALR_TOP/patch/115/import/alr.lct <alert>.ldt




USER

FNDLOAD apps/${xx_appspassword} 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct <user_name>.ldt FND_USER USER_NAME='<user_name>'
FNDLOAD apps/${xx_appspassword} 0 Y UPLOAD $FND_TOP/patch/115/import/afscursp.lct <user_name>.ldt

Notes for using FNDLOAD against FND_USER:-
1. After uploading using FNDLOAD, user will be promoted to change their password again during their next signon attempt.
2. All the responsibilities will be extracted by FNDLOAD alongwith User Definition in FND_USER 
3. In the Target Environment , make sure that you have done FNDLOAD for new responsibilities prior to running FNDLOAD on users. 




LOOKUP

FNDLOAD apps/${xx_appspassword} 0 Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct <lookup_type>.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME ='XXCTO' LOOKUP_TYPE="<lookup_type"
FNDLOAD apps/${xx_appspassword} 0 Y UPLOAD $FND_TOP/patch/115/import/aflvmlu.lct <lookup_type>.ldt



FUNCTION

FNDLOAD apps/${xx_appspassword} 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct XXCTO_VEH.ldt FUNCTION FUNCTION_NAME="XXCTO_VEH%"
FNDLOAD apps/${xx_appspassword} 0 Y UPLOAD $FND_TOP/patch/115/import/afsload.lct XXCTO_VEH.ldt

If SSO Integration in Place you cannot change the password from System Administrator, User Management, FND_USER_PKG

ORA-20001: APP-FND-02602: Unabled to change password for user [username] for this following reason:
This password is not managed by Oracle Applications so it can not be changed here..
ORA-06512: "APPS.APP_EXCEPTION", at line 72
ORA-06512: "APPS.FND_USER_PKG", at line 322
ORA-06512: "APPS.FND_USER_PKG", at line 1147
ORA-06512: at line 1

This error occurs if you have both SSO-enabled E Business Suite (EBS) and are trying to use the OIM E Business Suite User Management connector to manage passwords in EBS. If you SSO-enable an EBS user, then their password is no longer stored in EBS, rather, it is stored in OID / SSO. As a result, EBS refuses to allow OIM to update the password.

You cannot use the OIM E Business Suite User Management connector to manage passwords for SSO-enabled EBS users. You need to choose one of the following:
1. Do not use the Update Password feature of OIM EBS User Management connector, or
2. not to SSO enable E Business Suite 

Change the %Application%SSO% Profile Options:
Application SSO LDAP Synchronization
Application SSO Login Types

Assign Responsibility From Backend

Find the RESPONSIBILITY_ID from RESPONSIBILITY_NAME:

select RESPONSIBILITY_ID,RESPONSIBILITY_NAME from FND_RESPONSIBILITY_VL where RESPONSIBILITY_NAME like 'Service%Contra%AMERICAS%CORE';

Verify whether the User is already having the responsibility assigned:

select USER_NAME,USER_ID from fnd_user where USER_NAME = 'MKHAWAS';
select USER_ID,RESPONSIBILITY_ID from FND_USER_RESP_GROUPS_DIRECT where USER_ID=29273 and RESPONSIBILITY_ID=53545;

Assign the Responsibility Using the Following SQL:

DECLARE

  v_user_name  VARCHAR2(30) := '&Enter_User_Name';
  Enter_Resp_Id  VARCHAR2(100) ;
  v_resp_appl  VARCHAR2(100);
  v_resp_key   VARCHAR2(100);
  v_appl_id    VARCHAR2(30);

BEGIN
select APPLICATION_ID ,RESPONSIBILITY_KEY 
  into v_appl_id ,v_resp_key 
  from FND_RESPONSIBILITY_VL 
where RESPONSIBILITY_ID=('&Enter_Resp_Id');

select APPLICATION_SHORT_NAME 
  into v_resp_appl 
  from FND_APPLICATION_VL   
where upper(APPLICATION_ID) = v_appl_id;

  fnd_user_pkg.addresp(username       => v_user_name
                      ,resp_app       => v_resp_appl
                      ,resp_key       => v_resp_key
                      ,security_group => 'STANDARD'
                      ,description    => 'Auto Assignment'
                      ,start_date     => SYSDATE
                      ,end_date       => SYSDATE + 1000);
END;
commit;
/

Options with adcfgclone.pl

Application Node: appsTier | appltop  | atTechStack

Database Node: dbTier   | database | dbconfig | dbTechStack | addracnode

Context File: Target Context File
If no context is specified, adcfgclone.pl will prompt for the cloned system target values to create the new context file. This script also prompts for the APPS password. Additionally, if the target Application context file is not specified the APPS password will be prompted for a second time in order to create the new context file.

For more information: perl adcfgclone.pl help

for usage examples: perl adcfgclone.pl examples

ORA-01166 in adcfgclone.pl dbTier | Issues with adcfgclone dbTier

ORA-01503: CREATE CONTROLFILE failed
ORA-01166: file number 601 is larger than MAXDATAFILES (600)
ORA-01110: data file 601: '/d10/oracle/mkkdbdata/hrx35.dbf'

Solution:

1. Correct s_dbfiles in source context file with your maximum number of database files. 
In order to find the maximum file id use:
select max(FILE_ID) from DBA_DATA_FILES;

2. Run autoconfig on DB tier on source in order to propagate the changes.

3. Redo all cloning steps.