Oracle EBS Concurrent Manager Troubleshooting

In Oracle EBS version 12.2.6, Internal concurrent manager had an unexpected Outage.
ICM logfile has the error message  ORA-04068: existing state of packages has been discarded
====================

Shutting down Internal Concurrent Manager : 30-JAN-2018 12:09:54

List of errors encountered:
.............................................................................

_ 1 _
Error in DBMS lock request by handle. (ROUTINE=AFPGMG) (REASON=ORACLE
error 4068 in afpdlrh

Cause: afpdlrh failed due to ORA-04068: existing state of packages has
been discarded
ORA-04061: existing state of package body "APPS.FND_DCP" has been
invalidate


==========================================
Issue Resolution

1. Shut down the Concurrent manager completely using adcmctl.sh

cd $ADMIN_SCRIPTS_HOME

adcmctl.sh stop apps/<appspassword>

2. Compile the invalids in database

SQL>alter package FND_DCP compile body;

OR

Run the utlrp.sql script to recompile all the invalid objects in the database.

3. Startup Concurrent manager using adcmctl.sh

cd $ADMIN_SCRIPTS_HOME

adcmctl.sh start apps/<appspassword>

4. Verify Oracle EBS Concurrent manager is running fine.

Command to check LUN details on a Linux server

Logical Unit Number (LUN) is part of Storage Management in Oracle Automated Storage Management (ASM).

Below command is useful to check the available LUN's on a linux server.

As root user,

Run the command ls -la /dev/disk/by-id/  


Sample Output below:

[root@erptestdb ~]# ls -la /dev/disk/by-id/
total 0
drwxr-xr-x 2 root root 240 Jan 29 16:17 .
drwxr-xr-x 6 root root 120 Jan 29  2018 ..
lrwxrwxrwx 1 root root   9 Jan 29  2018 scsi-3******************************** -> ../../sdd
lrwxrwxrwx 1 root root  10 Jan 29 15:40 scsi-3********************************-part1 -> ../../sdd1
lrwxrwxrwx 1 root root   9 Jan 29  2018 scsi-3******************************** -> ../../sdf
lrwxrwxrwx 1 root root  10 Jan 29 16:17 scsi-3********************************-part1 -> ../../sdf1
lrwxrwxrwx 1 root root   9 Jan 29  2018 scsi-3******************************** -> ../../sdc
lrwxrwxrwx 1 root root  10 Jan 29 15:40 scsi-3********************************-part1 -> ../../sdc1
lrwxrwxrwx 1 root root   9 Jan 29  2018 scsi-3********************************-> ../../sdb
lrwxrwxrwx 1 root root  10 Jan 29 15:40 scsi-3********************************-part1 -> ../../sdb1
lrwxrwxrwx 1 root root   9 Jan 29  2018 scsi-3******************************** -> ../../sde
lrwxrwxrwx 1 root root  10 Jan 29 15:40 scsi-3********************************-part1 -> ../../sde1
[root@erptestdb ~]#

Oracle Applications Patching : ADOP Prepare phase failed due to missing information in FND_NODES.

Applying Patches to Oracle EBS and Oracle Database is one of the main activities for an Oracle database administration.We Often get errors during Oracle patching, which could be due to environment issue or a configuration issue or an Oracle Bug.

Checking Patch logfiles and Oracle Patching worker logfiles is very important to identify and troubleshoot the patching error.

Recently, while i am applying patch on a Production environment, Prepare phase in Oracle EBS online patching failed due to below  error

Error Details:Information missing in FND_NODES table for one or more application tier nodes.


=================================================
ADOP (C.Delta.9)
Session ID: 19
Node: prodapp01
Phase: prepare
Log: /applmgr/Oracle/PRODAPP/fs_ne/EBSapps/log/adop/19/20171230_095636/adop.log
===========================================================================

Validating configuration on node: [prodapp01].
    Log: /applmgr/Oracle/PRODAPP/fs_ne/EBSapps/log/adop/19/20171230_095636/prepare/validate/rwerpprodapp01
[ERROR]: Information missing in FND_NODES table for one or more application tier nodes. For details, refer to log file on the relevant node.
[WARNING]: There could be issues while validating the ports used for E-Business Suite instance against ports used in /etc/services. Refer the log file for more details.
[WARNING]: Either some of the required entries in /etc/hosts file might be missing (e.g. localhost or hostname) OR the file /etc/hosts could not be read.
[WARNING]: Found invalid cross references in FS config files.
    [UNEXPECTED]Error occurred running "perl /applmgr/Oracle/PRODAPP/fs2/EBSapps/appl/ad/12.0.0/patch/115/bin/txkADOPValidations.pl  -contextfile=/applmgr/Oracle/PRODAPP/fs2/inst/apps/PRODAPP_prodapp01/appl/admin/PRODAPP_prodapp01.xml -patchctxfile=/applmgr/Oracle/PRODAPP/fs1/inst/apps/PRODAPP_prodapp01/appl/admin/PRODAPP_prodapp01.xml -phase=prepare -logloc=/applmgr/Oracle/PRODAPP/fs_ne/EBSapps/log/adop/19/20171230_095636/prepare/validate/prodapp01 -promptmsg=hide"
    [UNEXPECTED]Error 1 occurred while Executing txkADOPValidation script on prodapp01


[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 = 1 (Fail)
=================================================


Analysis
The environment on which issue is reported has two middle Tier nodes. Primary node is the Master node and DMZ tier is the slave node.
Due to autoconfig failure on Oracle EBS Slave node, Support_Web  column is not updated in FND_NODES after autoconfig execution.
SUPPORT_WEB column for DMZ node is null in FND_NODES


Solution

1. Bring down Middle Tier Services
2. Run autoconfig on all the Middle Tier nodes
3. Startup the Middle Tier Services
4. Verify that FND_NODES table has correct information
5. Restart adop Prepare Phase

How to Retreive password of a front end user in Oracle EBS?

Sometimes, an Oracle database administrator requires to login as the user to reproduce the issue and troubleshoot.It is easy to recover the password of front end users in Oracle EBS by creating the Decrypt package.

Please be cautious and do not misuse this script. This should be used in test environments only.


Create package and Package body

-- Package
CREATE OR REPLACE PACKAGE get_pwd
AS
   FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2)
      RETURN VARCHAR2;
END get_pwd;
/
--Package Body
CREATE OR REPLACE PACKAGE BODY get_pwd
AS
   FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2)
      RETURN VARCHAR2
   AS
      LANGUAGE JAVA
      NAME 'oracle.apps.fnd.security.WebSessionManagerProc.decrypt(java.lang.String,java.lang.String) return java.lang.String';
END get_pwd;
/



Execute the below  Query to retrieve the password

SELECT usr.user_name,
       get_pwd.decrypt
          ((SELECT (SELECT get_pwd.decrypt
                              (fnd_web_sec.get_guest_username_pwd,
                               usertable.encrypted_foundation_password
                              )
                      FROM DUAL) AS apps_password
              FROM fnd_user usertable
             WHERE usertable.user_name =
                      (SELECT SUBSTR
                                  (fnd_web_sec.get_guest_username_pwd,
                                   1,
                                     INSTR
                                          (fnd_web_sec.get_guest_username_pwd,
                                           '/'
                                          )
                                   - 1
                                  )
                         FROM DUAL)),
           usr.encrypted_user_password
          ) PASSWORD
  FROM fnd_user usr
 WHERE usr.user_name = '&USER_NAME';

What is Edition Based Redefinition (EBR) in Oracle?

Edition Based Redefinition (EBR) is a new feature introduced from Oracle Software Version 11g R2.Edition-based redefinition allows multiple versions of PL/SQL objects, views and synonyms in a single schema.This feature facilitates Online patching of database objects in R12.2 online Patching.

Editionable and Non-editionable Objects

Below schema object types are editionable in the database:

SYNONYM

VIEW

SQL translation profile

All PL/SQL object types:

FUNCTION

LIBRARY

PACKAGE and PACKAGE BODY

PROCEDURE

TRIGGER

TYPE and TYPE BODY

If a schema object type is editionable in the database, then it can be editionable in schemas.All other schema object types are noneditionable in the database and in every schema, and objects of that type are always noneditioned. Tables are always noneditioned objects.

To view the editions related information in the database, use the below datadictionary tables.

DBA_EDITIONS: SELECT * FROM dba_editions;

DBA_OBJECTS_AE: DBA_OBJECTS_AE view shows all objects, of all editions.

What is the use of MMON background process in Oracle Database?

MMON (Manageability Monitor)

MMON (Manageability Monitor) is a background process introduced in Oracle 10g. It gathers memory statistics (snapshots) and stores this information in the AWR (automatic workload repository). MMON is also responsible for issuing alerts for metrics that exceed their thresholds.

How to Compile Forms in Oracle Applications Release 11i and Release 12?

Steps to compile forms  in  Oracle Applications Release 12.1.x and 12.2.x

Login to Application Tier node as applmgr user

Change to $AU_TOP/forms/US

Execute the Below command to generate form files.

frmcmp_batch userid=apps/<password> module=formname.fmb
output_file=$PROD_TOP/forms/US/<name>.fmx module_type=form batch=no compile_all=special

After execution of the above command, verify there are no compilation errors in output displayed and the fmx is created succesfully.


Steps to compile forms  in  Oracle Applications Release 11i

Login to Application Tier node as applmgr user

Change to $AU_TOP/forms/US

Execute the Below command to generate form files.

f60gen module=$AU_TOP/forms/US/<custom_form>.fmb  userid=apps/<passwd> output_file=$PRODUCT_TOP/forms/<customformname>.fmx module_type=form compile_all=yes


Tips:

Use command "f60gen help=y" or "frmcmp_batch.sh help=y" to find the optional parameters.

Using Compile_all= yes changes cached version in the source file in addition to compiling pl/sql in the utput fmx file.