Steps to configure Oracle 11g OEM DBConsole manually for an existing database

Steps to configure Oracle 11g OEM DBConsole manually for an existing database

Step 1. Drop the existing configuration if it is having sysman user already present.
Connect to sqlplus with sys as sysdba and check SYSMAN is exist or not
SQL> Select username from dba_users where username='SYSMAN';

USERNAME
------------------------------
SYSMAN
     
Command to drop the existing configuration
$ emca -deconfig dbcontrol db -repos drop

STARTED EMCA at Jun 23, 2011 5:27:34 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: VIS
Listener port number: 1521
Password for SYS user:
Password for SYSMAN user:
Password for SYSMAN user:
Do you wish to continue? [yes(Y)/no(N)]: Y
Sun Dec 28 10:10:35 2014 oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /data/oracle/cfgtoollogs/emca/prod9/emca_2011_06_23_05_27_34.log.
Jun 23, 2011 5:27:49 AM oracle.sysman.emcp.EMDBPreConfig performDeconfiguration
WARNING: EM is not configured for this database. No EM-specific actions can be performed.
Jun 23, 2011 5:27:50 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Dropping the EM repository (this may take a while) ...
Jun 23, 2011 5:37:25 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully dropped
Enterprise Manager configuration completed successfully
FINISHED EMCA at Jun 23, 2011 5:37:26 AM

Step 2. Create the OEM GRID repository
$ emca -repos create

STARTED EMCA at Jun 23, 2011 6:41:59 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: VIS
Listener port number: 1521
Password for SYS user:
Password for SYSMAN user:
Do you wish to continue? [yes(Y)/no(N)]: Y
Jun 23, 2011 6:42:17 AM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /data/oracle/cfgtoollogs/emca/prod9/emca_2011_06_23_06_41_59.log.
Jun 23, 2011 6:42:18 AM oracle.sysman.emcp.EMReposConfig createRepository
INFO: Creating the EM repository (this may take a while) ...
Jun 23, 2011 7:16:31 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully created
Enterprise Manager configuration completed successfully
FINISHED EMCA at Jun 23, 2011 7:16:31 AM

Step 4. Confitgure EM Grid control



$ emca -config dbcontrol db

STARTED EMCA at Jun 23, 2011 8:58:47 PM
EM Configuration Assistant, Version 11.1.0.7.0 Production
Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Enter the following information:
Database SID: VIS
Database Control is already configured for the database VIS
You have chosen to configure Database Control for managing the database VIS
This will remove the existing configuration and the default settings and perform a fresh configuration
Do you wish to continue? [yes(Y)/no(N)]: Y
Listener port number: 1521
Password for SYS user:
Password for DBSNMP 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 ................ /data/oracle/product/11.1.0

Local hostname ................ localhost
Listener port number ................ 1521
Database SID ................ VIS
Email address for notifications ...............
Outgoing Mail (SMTP) server for notifications ...............

-----------------------------------------------------------------
Do you wish to continue? [yes(Y)/no(N)]: y
Jun 23, 2011 8:59:36 PM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /data/oracle/cfgtoollogs/emca/prod9/emca_2011_06_23_20_58_47.log.
Jun 23, 2011 8:59:41 PM oracle.sysman.emcp.util.DBControlUtil stopOMS
INFO: Stopping Database Control (this may take a while) ...
Jun 23, 2011 8:59:49 PM oracle.sysman.emcp.EMReposConfig uploadConfigDataToRepository
INFO: Uploading configuration data to EM repository (this may take a while) ...
Jun 23, 2011 9:09:13 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Uploaded configuration data successfully
Jun 23, 2011 9:09:36 PM oracle.sysman.emcp.util.DBControlUtil configureSoftwareLib
INFO: Software library configured successfully.
Jun 23, 2011 9:09:36 PM oracle.sysman.emcp.EMDBPostConfig configureSoftwareLibrary
INFO: Deploying Provisioning archives ...
Jun 23, 2011 9:10:15 PM oracle.sysman.emcp.EMDBPostConfig configureSoftwareLibrary
INFO: Provisioning archives deployed successfully.
Jun 23, 2011 9:10:16 PM oracle.sysman.emcp.util.DBControlUtil secureDBConsole
INFO: Securing Database Control (this may take a while) ...
Jun 23, 2011 9:11:01 PM oracle.sysman.emcp.util.DBControlUtil secureDBConsole
INFO: Database Control secured successfully.
Jun 23, 2011 9:11:01 PM oracle.sysman.emcp.util.DBControlUtil startOMS
INFO: Starting Database Control (this may take a while) ...
Jun 23, 2011 9:13:04 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: Database Control started successfully
Jun 23, 2011 9:13:04 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: >>>>>>>>>>> The Database Control URL is https://localhost:1158/em <<<<<<<
Jun 23, 2011 9:13:20 PM oracle.sysman.emcp.EMDBPostConfig invoke
WARNING:
************************  WARNING  ************************

Management Repository has been placed in secure mode wherein Enterprise Manager data will be encrypte                                                    ile: /VIS/db/tech_st/11.1.0/admin/localhost_VIS/sysman/config/emkey.ora.   Pleas                                                         d data will become unusable if this file is lost.
***********************************************************
Enterprise Manager configuration completed successfully
FINISHED EMCA at Jun 23, 2011 9:13:20 PM

Step 5. Verify the configuration by typing the address (https://localhost:1158/em) in the explorer.


How to check the status of EM Grid control
To check the status of grid control you have issue emctl status dbcontrol
$ emctl status dbconsole
Oracle Enterprise Manager 11g Database Control Release 11.1.0.7.0
Copyright (c) 1996, 2008 Oracle Corporation.  All rights reserved.
https://localhost:1158/em/console/aboutApplication
Oracle Enterprise Manager 11g is running.
------------------------------------------------------------------
Logs are generated in directory /data/oracle/product/11.1.0/localhost_VIS/sysman/log

How to start the EM Grid control
$ emctl start dbconsole
Oracle Enterprise Manager 11g Database Control Release 11.1.0.7.0
Copyright (c) 1996, 2008 Oracle Corporation.  All rights reserved.
https://localhost:1158/em/console/aboutApplication
Starting Oracle Enterprise Manager 11g Database Control ................ started.
------------------------------------------------------------------
Logs are generated in directory /data/oracle/product/11.1.0/localhost_prod9/sysman/log

How to stop the EM Grid Control?

$ emctl stop dbconsole
Oracle Enterprise Manager 11g Database Control Release 11.1.0.7.0
Copyright (c) 1996, 2008 Oracle Corporation.  All rights reserved.
https://localhost:1158/em/console/aboutApplication
Stopping Oracle Enterprise Manager 11g Database Control ...
 ...  Stopped.

How to drop DBConsole using EMCA or manually (with and without repository)

How to drop DBConsole configuration along with the repository using EMCA? 

To completely remove the DBConsole configuration files and repository run below command. 

<ORACLE_HOME>/bin/emca -deconfig dbcontrol db -repos drop

Enter the following information:
Database SID: orcl
Listener port number: 1521
Password for SYSMAN user:
Do you wish to continue? [yes(Y)/no(N)]: Y

This command will remove the DBConsole configuration files under <ORACLE_HOME>/<Hostname_SID>,
<ORACLE_HOME>/oc4j/j2ee/OC4J_DBConsole_<Hostname>_<SID>
as well as DBConsole repository from the Database.

How to drop DBConsole configuration files using EMCA (leave repository intact) ?

 To remove DBConsole configuration files (leaving repository intact) run following EMCA command.

<ORACLE_HOME>/bin/emca -deconfig dbcontrol db

Enter the following information:
Database SID: orcl
Do you wish to continue? [yes(Y)/no(N)]:

This command will remove only the DBConsole configuration files which are under <ORACLE_HOME>/<Hostname_SID> and <ORACLE_HOME>/oc4j/j2ee/OC4J_DBConsole_<Hostname>_<SID>

How to drop DBConsole repository objects manually ?

DBConsole repository can be dropped manually by executing following SQL statements.

Step 1: Drop AQ related objects in the SYSMAN schema
Logon SQLPLUS as user SYS
SQL> exec DBMS_AQADM.DROP_QUEUE_TABLE(queue_table=>'SYSMAN.MGMT_NOTIFY_QTABLE',force=>TRUE);

Step 2: Drop the DB Control Repository Objects
Logon SQLPLUS as user SYS or SYSTEM, and drop the sysman account and management objects:
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP RESTRICT;
SQL> EXEC sysman.emd_maintenance.remove_em_dbms_jobs;
SQL> EXEC sysman.setEMUserContext('',5);
SQL> REVOKE dba FROM sysman;
SQL> DECLARE
CURSOR c1 IS
SELECT owner, synonym_name name
FROM dba_synonyms
WHERE table_owner = 'SYSMAN';
BEGIN
FOR r1 IN c1 LOOP
IF r1.owner = 'PUBLIC' THEN
EXECUTE IMMEDIATE 'DROP PUBLIC SYNONYM '||r1.name;
ELSE
EXECUTE IMMEDIATE 'DROP SYNONYM '||r1.owner||'.'||r1.name;
END IF;
END LOOP;
END;
/
SQL> DROP USER mgmt_view CASCADE;
SQL> DROP ROLE mgmt_user;
SQL> DROP USER sysman CASCADE;
SQL> ALTER SYSTEM DISABLE RESTRICTED SESSION;

How to delete DBConsole configuration files manually?

To manually delete DBConsole configuration files, remove the following directories from the filesystem:

<ORACLE_HOME>/<hostname_sid>
<ORACLE_HOME>/oc4j/j2ee/OC4J_DBConsole_<hostname>_<sid>

If the dbcontrol is upgraded from lower version, for example, from 10.2.0.3 to 10.2.0.4, then the following directory also needs to be removed from the file system.
<ORACLE_HOME>/<hostname_sid>.upgrade
<ORACLE_HOME>/oc4j/j2ee/OC4J_DBConsole_<hostname>_<sid>.upgrade

NOTE : Be careful while removing these directories as one should NOT remove OC4J_DBConsole or any other directory by mistake, they are very important. You need to delete only those directories with <hostname>_<sid> where hostname is the hostname of the server and SID should be the database SID.

On Windows you also need to delete the DB Console service:
Using regedit
- run regedit
- navigate to HKEY_LOCAL_MACHINE/SYSTEM/CurrentControlSet/Services
- locate the OracleDBConsole<sid> entry and delete it
Or
On Windows XP and Windows Server 2003 you can run the following from the command line:
CMD> sc delete <service_name>
- where <service_name> is the DB Control service name (typically: OracleDBConsole<sid>)
Or
CMD> nmesrvops delete <servicename>
- where <service_name> is the DB Control service name (typically: OracleDBConsole<sid>)

How to Find a JAR File Which Contains a Particular JAVA CLASS Object?

How to find a jar file which contains a particular JAVA CLASS object
--------------------------------------------------------------------

1) Find invalid java classes:
   --------------------------
Lets assume that we have two JAVA CLASS invalids as returned by the
following query:

SQL>SELECT object_name,object_type,owner,status FROM dba_objects
WHERE object_type='JAVA CLASS' AND status='INVALID';
OBJECT_NAME                    OBJECT_TYPE OWNER   STATUS
------------------------------ ----------- ------- -------
/b0993e19_MyAppsContext        JAVA CLASS  APPS    INVALID
oracle/apps/fnd/common/FileLog JAVA CLASS  APPS    INVALID

2) Try to resolve invalids:
   ------------------------
First of all try to resolve these invalids using following statements.

SQL>ALTER JAVA CLASS "/b0993e19_MyAppsContext" RESOLVE;
SQL> ALTER JAVA CLASS "oracle/apps/fnd/common/FileLog" RESOLVE;
Note: If you don't include java class name in double quotes the statements will fail.

If these objects are still invalid proceed to next step

3) Find respective modules of java classes:
   ----------------------------------------
We need to check as to which module the JAVA CLASS belongs.

To check the module of first invalid run the following statement:
SQL>SELECT dbms_java.longname('/b0993e19_MyAppsContext') FROM dual;
DBMS_JAVA.LONGNAME('/B0993E19_MYAPPSCONTEXT')
---------------------------------------------------
oracle/apps/wms/cartonization/server/MyAppsContext

The result of this query shows that this java class is associated 
with WMS module as it is included in oracle/apps/wms package

From the name of second invalid above, FileLog, it is clear that it is
associated with FND module as it is included in oracle/apps/fnd package.

4) Find jar file which includes the java class:
   --------------------------------------------
Create a shell script, say, whichjar.sh with the following logic:

#!/usr/bin/ksh
for f in `ls $1/java/jar/*.jar`
do
        t=`strings $f | grep $2`
        if test ! -z "$t"
        then
                echo "$2 is found in $f"
        fi
done 

Now run the script with the following command:

$./whichjar.sh <$PRODUCT_TOP> <CLASSNAME>

$./whichjar.sh $WMS_TOP MyAppsContext
MyAppsContext is found in /u01/visappl/wms/11.5.0/java/jar/wmscrtzn.jar

$./whichjar.sh $FND_TOP FileLog
FileLog is found in /u01/visappl/fnd/11.5.0/java/jar/fndaolj.jar

Translation Synchronization Patches.

TSP is a process to synchronize US language and other installed languages which is a very efficient process.

It is based on ad_.. tables like for example AD_FILE_VERSIONS

and those tables are updated when you apply a patch or when you run adadmin / maintain snapshot.

When you generate the file manisfest using adgennls.pl script,

this script compares the files versions (from the AD_.. tables) between the US  and the other installed languages

and generates the adgennls.txt file.

So this means the same TSP can only be applied if your 2 instances are exactly similar (clone)

In case you applied different patches then you need to generate a new file manifest.

The TSP will provide a patch containing all the NLS objects that are in a lower

version that their corresponding US object, so only the objects that really need a translation will be provided by the TSP.

Scripts to check profile option at user level

Note: 470102.1 - How To Check If a Profile Option Is Set In Oracle Application
Note: 201945.1 - How to list E-Business Suite Profile Option values for all levels using SQLPlus
Note: 282382.1 - How to Search all of the Profile Options for a Specific Value
Note: 367926.1 - How To Find All Users With A Particular Profile Option Set?
Note: 282382.1 - How to Search all of the Profile Options for a Specific Value
How To Set A System Profile Value Without Logging In To The Applications [ID 364503.1]
How to Change Profile Option Value Without Forms [ID 943710.1]

How to backup the current autopatch session in R12

Issue:

Adpatch is running and fails on one of the workers.  To fix this worker and
continue with the patch installation, a new patch needs to be applied.
Considering that only 1 adpatch session can run on an instance at any given
time, how can a patch be applied when adpatch is already running?


1.  Using the adctrl utility, shutdown the workers.
    a. adctrl
    b. Select option 3 "Tell worker to shutdown/quit"
2.  Backup the FND_INSTALL_PROCESSES table which is owned by the APPLSYS schema
    a. sqlplus applsys/<password>
    b. create table fnd_Install_processes_back
       as select * from fnd_Install_processes;
    c. The 2 tables should have the same number of records.
       select count(*) from fnd_Install_processes_back;
       select count(*) from fnd_Install_processes;
3.  Backup the AD_DEFERRED_JOBS table.
    a. sqlplus applsys/<password>
    b. create table AD_DEFERRED_JOBS_back
       as select * from AD_DEFERRED_JOBS;
    c. The 2 tables should have the same number of records.
       select count(*) from AD_DEFERRED_JOBS_back;
       select count(*) from AD_DEFERRED_JOBS;
4.  Backup the .rf9 files located in $APPL_TOP/admin/<SID>/restart directory.
    At this point, the adpatch session should have ended and the cursor should 
    be back at the Unix prompt.
    a. cd $APPL_TOP/admin/<SID>
    b. mv restart restart_back
    c. mkdir restart
5.  Drop the FND_INSTALL_PROCESSES table and the AD_DEFERRED_JOBS table.
    a. sqlplus applsys/<password>
    b. drop table FND_INSTALL_PROCESSES;
    c. drop table AD_DEFERRED_JOBS;
6.  Apply the new patch.
7.  Restore the .rf9 files located in $APPL_TOP/admin/<SID>/restart_back
    directory.
    a. cd $APPL_TOP/admin/<SID>
    b. mv restart restart_<patchnumber>
    c. mv restart_back restart 
8. Restore the FND_INSTALL_PROCESSES table which is owned by the APPLSYS
    schema.
    a. sqlplus applsys/<password>
    b. create table fnd_Install_processes
       as select * from fnd_Install_processes_back;
    c. The 2 tables should have the same number of records.
       select count(*) from fnd_Install_processes;
       select count(*) from fnd_Install_processes_back;
9. Restore the AD_DEFERRED_JOBS table.
    a. sqlplus applsys/<password>
    b. create table AD_DEFERRED_JOBS
       as select * from AD_DEFERRED_JOBS_back;
    c. The 2 tables should have the same number of records.
       select count(*) from AD_DEFERRED_JOBS_back;
       select count(*) from AD_DEFERRED_JOBS;
10. Re-create synonyms
    a. sqlplus apps/apps
    b. create synonym AD_DEFERRED_JOBS for APPLSYS.AD_DEFERRED_JOBS;
    c. create synonym FND_INSTALL_PROCESSES FOR APPLSYS.FND_INSTALL_PROCESSES;
11. Start adpatch, it will resume where it stopped previously.

SQL Script to find the file version and Patch number

SELECT af.app_short_name "Application", af.filename "File Name",
afv.VERSION "File Version",
NVL (aap.patch_name, 'Default Installation') "Patch Number",
'$' || fa.basepath || '/' || af.subdir "Location"
FROM apps.ad_files af,
apps.ad_patch_run_bug_actions apa,
apps.ad_patch_run_bugs aprb,
apps.ad_patch_runs apr,
apps.ad_patch_drivers apd,
apps.ad_applied_patches aap,
apps.ad_file_versions afv,
apps.fnd_application fa
WHERE af.filename = 'IGSEN010.fmb'
AND af.file_id = apa.file_id(+)
AND apa.common_action_id(+) = 4042
AND apa.patch_run_bug_id = aprb.patch_run_bug_id(+)
AND aprb.patch_run_id = apr.patch_run_id(+)
AND apr.patch_driver_id = apd.patch_driver_id(+)
AND apd.applied_patch_id = aap.applied_patch_id(+)
AND af.file_id = afv.file_id
AND af.app_short_name = fa.application_short_name
GROUP BY aap.patch_name,
af.filename,
afv.VERSION,
af.app_short_name,
af.file_id,
af.subdir,
fa.basepath

JRIMETA.DAT Explanation

JRIMETA.DAT is an encrypted binary file maintained by the JRI tools which holds information about the all files located in the $JAVA_TOP.

The active copy of the file is located in the $JAVA_TOP/META-INF directory. There is also a copy in $JAVA_TOP/oracle/apps/media/META-INF and in $COMMON_TOP/html/META-INF.

When a patch containing a java class file is applied, adpatch looks in the JRIMETA.DAT and compares this with the version of the class file contained within the patch. If the patch file is newer then it replaces the actual class file and the JRIMETA.DAT file is updated. adpatch does not look at the actual class file within $JAVA_TOP for comparison with the corresponding file in the patch.

Sometimes the JRIMETA.DAT and the $JAVA_TOP get out of synchronization with each other. This can happen if replacement java class files are copied manually rather than correctly applied via adpatch. This might also happen if the $JAVA_TOP (or part thereof) is copied from one Applications system to another as an attempt perhaps to fix some perceived corruption somewhere in the $JAVA_TOP or other unspecified error. If the two environments do not have an identical build and patching history this is likely to cause an inconsistency between the $JAVA_TOP and JRIMETA.DAT.

Typically, concerns regarding the JRIMETA.DAT manifest themselves when it is noticed that a patch with newer java class files is applied, and the java class files in the $JAVA_TOP are not replaced by the newer files in the patch. It is likely JRIMETA.DAT has reported to adpatch that the files in the $JAVA_TOP are already at the same or a later version as those in the patch.

The following command uses the adjlist utility to report the contents of the JRIMETA.DAT file. The output is written to the adjlist.rpt file:-

$ adjava -mx512m oracle.apps.ad.jri.adjlist -archives $JAVA_TOP -reportfile adjlist.rpt -logfile adjlist.log

If you have AD I or later installed, the following command will produce a more detailed report

$ adjava -mx512m oracle.apps.ad.jri.adjlist -archives $JAVA_TOP -allinfo -reportfile adjlist.rpt -logfile adjlist.log


During patching, updates to the JRIMETA.DAT file are reported in the main adaptch log file as below. In the example shown, the RegUi.class contained in the j4676589_jtf.zip is upgraded from 115.22 to 115.27 after first checking the JRIMETA.DAT:-
------------------------------------------------------------------------------
About to Merge meta data of delta archive 11 and master archive : Thu Aug 30 2007 16:44:55

Loading metadata from archive /oracle/115102/patches/4676589/j4676589_jtf.zip

This delta archive replaces the following elements:

oracle/apps/jtf/um/ui/RegUi [115.22 -> 115.27] {oracle/apps/jtf/um/ui/RegUi.class }
------------------------------------------------------------------------------


If the JRIMETA.DAT reports that all files currently in the $JAVA_TOP are equal to or newer than files supplied in the patch, the main adpatch log will be updated as follows:-
------------------------------------------------------------------------------
About to Merge meta data of delta archive 11 and master archive : Fri Aug 24 2007 10:23:41
Loading metadata from archive /oracle/applprod/patches/ATG4_24082007/4676589/j4676589_jtf.zip
This delta archive does not add to or supersede any elements of the master archive
Done Merging meta data of delta archive 11 and master archive : Fri Aug 24 2007 10:23:43
------------------------------------------------------------------------------

If it is suspected there is a mismatch between the files in the $JAVA_TOP and the JRIMETA.DAT, the following command will report any  inconsistencies:-

$ adjava -mx512m oracle.apps.ad.jri.adjcopy -masterArchive $JAVA_TOP -sync -mode CHECK_ONLY

If this reports errors, the following command will update the JRIMETA.DAT to reflect the versions of files contained with the $JAVA_TOP

$ adjava -mx512m oracle.apps.ad.jri.adjcopy -masterArchive $JAVA_TOP -sync -mode APPLY

The above command should only be used if it is not possible to revert to a previous known, good environment. If it is suspected there is a mismatch between the JRIMETA.DAT and the $JAVA_TOP and this is discovered when patches are being tested (as part of an upgrade for example), it is preferable to return to a known, good JRIMETA.DAT and then  repeat the patches or maintenance and identify where the  inconsistency occurs. The above command only corrects the JRIMETA.DAT to reflect the current $JAVA_TOP - it is more important that the source of the problem is identified and how and why the problem initially occurred. It will usually only be possible to identify the source of the mismatch if there is a known, good situation that can be reverted to.

After the above command has been run, re-apply the patch that failed previously and check to see if the java class files are correctly copied from the patch.

It is not possible to create a new JRIMETA.DAT file from the $JAVA_TOP if the existing JRIMETA.DAT has been lost or irretrievably corrupted.

Pinning Objects to Improve Apps Performance

By avanish.srivatsav on May 29, 2007

Keeping database packages in the Oracle database's System Global Area (SGA) shared pool is a common practice.  This is also called pinning.  One common misconception is that pinning is not useful or needed in later Oracle database versions such as 10g and above, since those releases offer automatic shared memory management.  This isn't completely accurate for all cases, for reasons which I'll discuss below.


An Introduction to the System Global Area (SGA)
The Oracle database's System Global Area contains various pools of memory used to satisfy particular classes of memory allocation requests:

• Shared pool:  used for caching shared cursors, stored procedures, dictionary caches, etc.
• Buffer cache:  cache of disk data
• Large pool:  large allocations
• Java pool:  Java allocations and for caching java objects
• Log buffer:  in-memory buffer for redo generation
• Streams Pool:  new in 10g, for buffering inbound and outbound logical change records
• Fixed SGA:  bootstrap section of the SGA

SGA memory is allocated in units of contiguous memory chunks called granules.  The size of a granule depends on your operating system platform and the SGA's total size. On most platforms, if the estimated SGA size is:

• Less than or equal to 1GB, the granule size is 4 MB
• Greater than 1GB, the granule size is 16 MB

A Primer on Space Allocations in the Shared Pool

One of the important components of the SGA is the shared pool.  The
shared pool was introduced as a feature of the Oracle Database in
Version 7, primarily as a repository for shared SQL and PL/SQL.  The
shared pool has come a long way since its original release.


The Oracle database requires contiguous space.  For example, if a request for 4 K of memory is made, the database cannot allocate separate 3 K and 1 K chunks.  It must allocate a 4 K block of contiguous free memory to satisfy the request.  If there is no free memory, it will scan the Least Recently Used list to free some memory.  The heap manager will try to free as many objects as possible before giving up.  If the shared pool has no space to satisfy the request for memory, an ORA-4031 error is thrown.
With the advent of automatic shared memory management, we need not configure the size of the shared pool via the shared_pool_size parameter.  Automatic shared memory management requires one parameter to be set:  sga_target.  The Oracle database's automatic memory allocation is superior to manual memory management. This prevents ORA-4031 errors in most cases.

When Large Objects Jump in the Shared Pool

Imagine a large package (or any object) has to be loaded into the shared pool.  Large PL/SQL objects present particular challenges.  The database has to search for free space for the object.  If it cannot get enough contiguous space, it will free many small objects to satisfy the request.  If several large objects need to be loaded, the database has to throw out many small objects in the shared pool. 
Finding candidate objects and freeing memory is very costly.  These tasks will impact CPU resources.

One approach to avoiding performance overhead and memory allocation errors is to keep large PL/SQL objects in the shared pool at startup time.  This process is known as pinning.  This loads the objects into the shared pool and ensures that the objects are never aged out of the shared pool.  If the objects are never aged out, then that avoids problems with insufficient memory when trying to reload them.
What's in Your Shared Pool Now?
Objects are 'kept' in the shared pool using the dbms_shared_pool package that is defined in the dbmspool.sql file. 

For example:

execute dbms_shared_pool.keep('owner.object');

To view a list of all objects that are kept in the shared pool, one can query the v$db_object_cache:

select owner,name,type,sharable_mem from v$db_object_cache where kept='YES';

The SQL query above will list all of the objects that are 'kept' in the shared pool using dbms_shared_pool.keep.
Identifying Candidate Objects for Keeping in the Shared Pool

To identify candidates that should be kept in the shared pool, first run the following query:

select substr(owner,1,10)||'.'||substr(name,1,35) "ObjectName", type, sharable_mem,loads, executions, kept from v$db_object_cache where type in ('TRIGGER','PROCEDURE','PACKAGE BODY','PACKAGE') and executions >0 order by executions desc,loads desc,sharable_mem desc

The query above will return something like this:

Next, query the x$ksmlru table, using:

select * from x$ksmlru;

The x$ksmlru table keeps track of the current shared pool objects and the corresponding number of objects flushed out of the shared pool to allocate space for the load.  These objects are stored and flushed out based on the Least Recently Used (LRU) algorithm.  Here's what the table looks like:

describe x$ksmlru       

Table or View x$ksmlru         

     Name           Null?    Type                       
     -------------- -------- --------------             
     ADDR                    RAW(4)
     INDX                    NUMBER                      
     KSMLRCOM                VARCHAR2(20)                
     KSMLRSIZ                NUMBER                      
     KSMLRNUM                NUMBER  

• KSMLRNUM  shows the number of objects that were flushed to load the large object
• KSMLRISZ shows the size of the object that was loaded (contiguous memory allocated)

Note:  This is a fixed table: once you query the table, the database will
automatically reset the table.  Make sure that you spool the output to
a file so you can capture it for analysis.
Analyze the x$ksmlru output to determine if there are any large allocations that are flushing other objects.  If this is the case, analyze the v$db_object_cache to identify the objects with high loads or executions.  These should be kept in the shared pool.

Keeping Objects in Oracle Applications Databases

All E-Business Suite DBAs should do some analysis to assess whether pinning can improve the performance of your Apps environment.  Arriving at the objects to be pinned varies from setup to setup.  Objects have to be pinned after each instance startup, and ideally immediately after the startup.
The $AD_TOP/sql/ADXGNPIN.sql script is provided to pin the packages in Oracle Applications.   This script pins all Oracle Applications objects, which is generally not completely necessary or advisable.  You should modify this script based on your analyses of v$db_object_cache and x$ksmlru to identify the right objects to be pinned for your environment. 
For more information about this script (and the related ADXCKPIN.sql script, used for listing objects in the shared pool), see the appropriate manual for your Apps release:

• Oracle Applications Maintenance Procedures (Release 11i) (OTN, PDF, 2.5 MB)
• Oracle Applications Maintenance Procedures (Release 12) (OTN, PDF, MB)

Related

Diagnosing and Resolving Error ORA-04031 (Metalink Note 146599.1)

How to compile forms manually in 11i and R12

Regenerate the form manually:

GENERATION OF FORM
------------------
Oracle Forms executable used to generate forms depends of platform and Oracle Applications versions :

	UNIX	WINDOWS NT
11.5.X	f60gen	f60gen.exe
12.X	frmcmp	frmcmp.exe

Command line is :

<forms executable> module=<source form name> userid=APPS/<APPS password>
output_file=<executable form name> module_type=form batch=yes compile_all=special

1)  Regenerate forms in R11i
1. Set the environment on Application tier.
2. cd $AU_TOP/forms/US.
3. Execute the following command:
f60gen userid=apps/apps module=<FORM>.fmb output_file=$AU_TOP/forms/US/<FORM>.fmx
module_type=form batch=no compile_all=special
Examples :

WINDOWS:
=========
f60gen.exe module=PERWSEAD.fmb userid=apps/apps output_file=$AU_TOP/forms/US/PERWSEAD.fmx module_type=form batch=yes compile_all=special

UNIX:
====
f60gen module=PERWSEAD.fmb userid=apps/apps output_file=$AU_TOP/forms/US/PERWSEAD.fmx
module_type=form batch=yes compile_all=special

Note:
The .fmb files are stored in $AU_TOP, and when generated the executable (.fmx) is stored under the product.
2)  Regenerate forms in R12
1. Set the environment on Application tier.
2. cd $AU_TOP/forms/US.
3. Execute the following command:
frmcmp userid=apps/apps module=<FORM>.fmb output_file=$AU_TOP/forms/US/<FORM>.fmx
module_type=form batch=no compile_all=special
Examples:

WINDOWS:
=========
frmcmp.exe userid=apps/apps module=PERWSEAD.fmb output_file=$AU_TOP/forms/US/PERWSEAD.fmx
module_type=form batch=no compile_all=special

UNIX:
====
frmcmp userid=apps/apps module=PERWSEAD.fmb output_file=$AU_TOP/forms/US/PERWSEAD.fmx
module_type=form batch=no compile_all=special

Getting Yesterdays or Tomorrows Day With Bash Shell Date Command

GNU date syntax

The syntax is as follows:

 
date  --date="STRING"
date  --date="next Friday"
date  --date="2 days ago"

Getting Date In the Future

To get tomorrow and day after tomorrow (tomorrow+N) use day word to get date in the future as follows:
date --date='tomorrow'  
date --date='1 day'  
date --date='10 day'  
date --date='10 week'
date --date='10 month'  
date --date='10 year'

How Do I Assigned Yesterday To Shell Variable?

yest=$(date --date="2 days ago")
echo "$yest" yest=$(date --date="yesterday" +"%d/%m/%Y")  
echo "The backup was last verified on $yest"

SQL Queries for Oracle Apps DBA for Day to Day Activities

/* To find the latest application version */

select ARU_RELEASE_NAME||'.'||MINOR_VERSION||'.'||TAPE_VERSION version, START_DATE_ACTIVE updated,ROW_SOURCE_COMMENTS "how it is done",BASE_RELEASE_FLAG "Base version" FROM AD_RELEASES where END_DATE_ACTIVE IS NULL

/* Query to find out if any patch except localisation patch is applied or not, if applied, that what all drivers it contain and time of it's application*/

select A.APPLIED_PATCH_ID, A.PATCH_NAME, A.PATCH_TYPE, B.PATCH_DRVIER_ID, B.DRIVER_FILE_NAME, B.ORIG_PATCH_NAME, B.CREATION_DATE, B.PLATFORM, B.SOURCE_CODE, B.CREATIONG_DATE, B.FILE_SIZE, B.MERGED_DRIVER_FLAG, B.MERGE_DATE from AD_APPLIED_PATCHES A, AD_PATCH_DRIVERS B where A.APPLIED_PATCH_ID = B.APPLIED_PATCH_ID and A.PATCH_NAME = ''

/* To know that if the patch is applied successfully, applied on both node or not, start time of patch application and end time of patch application, patch top location , session id ... patch run id */

select D.PATCH_NAME, B.APPLICATIONS_SYSTEM_NAME, B.INSTANCE_NAME, B.NAME, C.DRIVER_FILE_NAME, A.PATCH_DRIVER_ID, A.PATCH_RUN_ID, A.SESSION_ID, A.PATCH_TOP, A.START_DATE, A.END_DATE, A.SUCCESS_FLAG, A.FAILURE_COMMENTS from AD_PATCH_RUNS A, AD_APPL_TOPS B, AD_PATCH_DRVIERS C, AD_APPLIED_PATCHES D where A.APPL_TOP_ID = B.APPL_TOP_ID AND A.PATCH_DRIVER_ID = C.PATCH_DRIVER_ID and C.APPLIED_PATCH_ID = D.APPLIED_PATCH_ID and A.PATCH_DRIVER_ID in (select PATCH_DRIVER_ID from AD_PATCH_DRIVERS where APPLIED_PATCH_ID in (select APPLIED_PATCH_ID from AD_APPLIED_PATCHES where PATCH_NAME = '')) ORDER BY 3;

/* to find the base application version */ 

select ARU_RELEASE_NAME||'.'||MINOR_VERSION||'.'||TAPE_VERSION version, START_DATE_ACTIVE when updated, ROW_SOURCE_COMMENTS "how it is done" from AD_RELEASES where BASE_RELEASE_FLAG = 'Y'


/* To find all available application version */

select ARU_RELEASE_NAME||'.'||MINOR_VERSION||'.'||TAPE_VERSION version, START_DATE_ACTIVE when updated, END_DATE_ACTIVE "when lasted", CASE WHEN BASE_RELEASE_FLAG = 'Y' Then 'BASE VERSION' ELSE 'Upgrade' END "BASE/UPGRADE", ROW_SOURCE_COMMENTS "how it is done" from AD_RELEASES


/* To get file version of any application file which is changed through patch application */

select A.FILE_ID, A.APP_SHORT_NAME, A.SUBDIR, A.FILENAME, max(B.VERSION) from AD_FILES A, AD_FILE_VERSIONS B where A.FILE_ID = B.FILE_ID and B.FILE_ID = 86291 group by A.FILE_ID, A.APP_SHORT_NAME, A.SUBDIR, A.FILENAME


/* To get information related to how many time driver file is applied for bugs */

select * from AD_PATCH_RUN_BUGS where BUG_ID in (select BUG_ID from AD_BUGS where BUG_NUMBER = ''

/* To find latest patchset level for module installed */

select APP_SHORT_NAME, max(PATCH_LEVEL) from AD_PATCH_DRIVER_MINIPKS GROUP BY APP_SHORT_NAME

/* To find what is being done by the patch */

select A.BUG_NUMBER "Patch Number", B. PATCh_RUN_BUG_ID "Run Id",D.APP_SHORT_NAME appl_top, D.SUBDIR, D.FILENAME, max(F.VERSION) latest, E.ACTION_CODE action from AD_BUGS A, AD_PATCH_RUN_BUGS B, AD_PATCH_RUN_BUG_ACTIONS C, AD_FILES D, AD_PATCH_COMMON_ACTIONS E, AD_FILE_VERSIONS F where A.BUG_ID = B.BUG_ID and B.PATCH_RUN_BUG_ID = C.PATCH_RUN_BUG_ID and C.FILE_ID = D.FILE_ID and E.COMMON_ACTION_ID = C.COMMON_ACTION_ID and D.FILE_ID = F.FILE_ID and A.BUG_NUMBER = '' and B.PATCH_RUN_BUG_ID = ' < > ' and C.EXECUTED_FLAG = 'Y' GROUP BY A.BUG_NUMBER, B.PATCH_RUN_BUG_ID, D. APP_SHORT_NAME, D>SUBDIR, D.FILENAME, E.ACTION_CODE


/* To find Merged patch Information from database in Oracle Applications */

select bug_number from ad_bugs where bug_id in ( select bug_id from ad_comprising_patches where patch_driver_id =(select patch_driver_id from ad_patch_drivers where applied_patch_id =&n) );

 Select J.PATCh_NAME, H.APPLICATIONS_SYSTEM_NAME Instance_Name, H.NAME, I.DRIVER_FILE_NAME, D.APP_SHORT_NAME appl_top,D.SUBDIR, D.FILENAME, max(F.VERSION) latest, E.ACTION_CODE action from AD_BUGS A, AD_PATCH_RUN_BUGS B,AD_PATCH_RUN_BUG_ACTIONS C, AD_FILES D, AD_PATCH_COMMON_ACTIONS E, AD_FILE_VERSIONS F, AD_PATCH_RUNS G,
AD_APPL_TOPS H, AD_PATCH_DRIVERS I, AD_APPLIED_PATCHES J where A.BUG_ID = B.BUG_ID and
B.PATCH_RUN_BUG_ID = C.PATCH_RUN_BUG_ID and C.FILE_ID = D.FILE_ID and E.COMMON_ACTION_ID = C.COMMON_ACTION_ID
and D.FILE_ID = F.FILE_ID and G.APPL_TOP_ID = H.APPL_TOP_ID and G.PATCH_DRIVER_ID = I.PATCH_DRIVER_ID and
I.APPLIED_PATCH_ID = J.APPLIED_PATCH_ID and B.PATCH_RUN_ID = G.PATCH_RUN_ID and C.EXECUTED_FLAG = 'Y' and
G.PATCH_DRIVER_ID in (select PATCH_DRIVER_ID from AD_PATCH_DRIVERS where APPLIED_PATCH_ID
in (select APPLIED_PATCH_ID from AD_APPLIED_PATCHES where PATCH_NAME = 'merged'))
GROUP BY J.PATCH_NAME, H.APPLICATIONS_SYSTEM_NAME, H.NAME, I.DRIVER_FILE_NAME, D.APP_SHORT_NAME, D.SUBDIR,
D.FILENAME, E.ACTION_CODE


/* Script to find out Patch level of mini Pack */

Select product_version,patch_level from FND_PROUDCT_INSTALLATIONS where patch_level like '%&shortname%';

Replace short name by name of Oracle Apps Minipack for which you want to find out Patch level . ex.
AD - for Applications DBA
GL - for General Ledger
PO - Purchase Order


Where is Concurrent Manager Node ? If you don't know where is your CM here are few ways to find your it .

SQLPLUS> select node_name from apps.fnd_nodes where support_cp='Y';

Profile Options Useful for Oracle Apps DBA

Here is the list of few profile options which Apps DBA use frequently. It is not necessary that you as Apps DBA must know all profile options, it depends on your implemnetation. I am going going to update more about Profile Options.

Applications Help Web Agent
Applications Servlet Agent
Applications Web Agent
Concurrent: Active Request Limit
Concurrent: Hold Requests
Concurrent: Multiple Time Zones
Concurrent: Report Access Level
Concurrent: Report Copies
Concurrent: Request priority
Database Instance
Enable Security Group
FND: Debug Log Filename
FND: Debug Log Level
Forms Runtime Parameters
Gateway User ID
ICX: Discoverer Launcher
ICX: Forms Launcher
ICX: Report Launcher
ICX: Limit Connect
ICX: Limit time
ICX: Session Timeout
MO Operating Unit
Node Trust Level
RRA: Delete Temporary Files
RRA: Enabled
RRA: Service Prefix
RRA: Maximum Transfer Size
Self Service Personal Home Page Mode
Sign-On: Audit Level
Signon Password Failure Limit
Signon Password Hard to Guess
Signon Password Length
Signon Password No Reuse
Site Name
Socket Listener Port
TCF: Host
TCF: Port
TWO TASK
Viewer: Text


Below you will find commonly asked questions about various component Version in Oracle Applications & way to find them which might be helpful in Apps DBA Job. Most of these are for Unix / Linux.


Q: How to find Oracle Applications Web Server Version ?
Q: How to find Version of Apache used with oracle apps 11i ?

Log to Application tier as Operating System Usually called as APPLMGR ; go to location $IAS_ORACLE_HOME/Apache/Apache/bin and run command
./httpd -version
You will see output like

Server version: Oracle HTTP Server Powered by Apache/1.3.19 (Unix)
Server built: Dec 6 2005 14:59:13 (iAS 1.0.2.2.2 rollup 5)
Which means you are on iAS Version 1.0.2.2.2 with patchset rollup 5 with Apache Version 1.3.19


Server version: Oracle-Application-Server-10g/10.1.2.0.2 Oracle-HTTP-Server

Above is output If you have installed 10g Application Server with 11i


Q: How to find Jinitiator Version ?

Check for file like appsweb_SID_HOSTNAME.cfg under $OA_HTML/bin defined by environment variable FORMS60_WEB_CONFIG_FILE & search for entry like jinit_ver_name , you will see entry like
jinit_ver_name=Version=1,3,1,23
which means Jinitiator version is 1.3.1.23 ; if your version is 1.3.1.18 you will see entry like 1,3,1,18


Q: How to find Forms Version in 11i ?

Login to forms from frontend , on top menu bar of forms click on "Help" & Select "About Oracle Applications" go to "Forms Server " section. You should see entry like below depending on your forms version
Oracle Forms Version : 6.0.8.26.0
Which mean you are on forms version 6.0.8.26 . If you want to know whats your forms patchset level then subtract 9 from fourth digit which means for above case form patchset 17 is applied.


Q: How to find Forms Version in Apps from command Line ?

Enter "f60gen" on Forms Server and check for first line in output like
Forms 6.0 (Form Compiler) Version 6.0.8.26.0 (Production)
This confirms that you are on forms server version 6.0.8.26.0 and patch set 17. ( Patch Set = Fourth Digit - 9)


Q: How to find Version of any file in Oracle Apps 11i ? or
Q: How to find any Reports Version 11i ? or

In Oracle Applications under ad utilities there is utility called as adident Used for Identification purpose or to find out file version use
adident Header
for ex. inorder to to find file version of one AR form i.e. ARXGLCOR.fmx
adident Header ARXGLCOR.fmx
You should see output like
$Header APPSTAND.fmb 115.33 2002/04/04 11:13:40 pkm ship
$ $Header ARXGLCOR.fmb 115.15 2005/01/31 13:48 mraymond ship
Which means above form executable consist of two forms whose version is 115.33 & 115.15 resp. Similarly you can use adident to find version of any report in 11i.


Q: How to find Operation System Version (Unix/Linux) ?

For solaris use command
uname -a
You will see output like
For Solaris SunOS servername 5.8 Generic_117350-23 sun4u sparc SUNW,Sun-Fire-V240
For RedHat Linux use command
cat /etc/*release*
You will see output like
Red Hat Enterprise Linux AS release 3 (Taroon Update 6)

Which means you are on Solaris 5.8 or Linux AS 3 resp.

Q: How to find if your Operating System is 32 bit or 64 Bit ?

For solaris use command
isainfo -v
If you see out put like
32-bit sparc applications
That means your O.S. is only 32 bit but if you see output like


64-bit sparcv9 applications
32-bit sparc applications
above means your o.s. is 64 bit & can support both 32 & 64 bit applications


Q: How to find Operating System version ?

For solaris use command
uname -a
You will see output like
For Solaris SunOS servername 5.8 Generic_117350-23 sun4u sparc SUNW,Sun-Fire-V240
For RedHat Linux use command
cat /etc/*release*
You will see output like
Red Hat Enterprise Linux AS release 3 (Taroon Update 6)

Which means you are on Solaris 5.8 or Linux AS 3 resp.

Q: How to find if your Operating System is 32 bit or 64 Bit ?
For solaris use command
isainfo -v
If you see out put like
32-bit sparc applications
That means your O.S. is only 32 bit but if you see output like


64-bit sparcv9 applications
32-bit sparc applications
above means your o.s. is 64 bit & can support both 32 & 64 bit applications



Q: Can I run 64 bit application on 32 bit Operating system ?

You can run 32 bit application (like oracle application server, web server, all oracle application server are 32 bit ) on both 32 /64 bit operating system but a 64 bit application like 64 bit database can run only on 64 bit operating system.

Q How to find if your database is 32 bit or 64 bit(Useful in applying Patches) ?


execute "file $ORACLE_HOME/bin/oracle" , you should see output like
/u01/db/bin/oracle: ELF 64-bit MSB executable SPARCV9 Version 1
which means you are on 64 bit oracle
If your oracle is 32 bit you should see output like
oracle: ELF 32-bit MSB executable SPARC Version 1


Q. How to find Version of Apps 11i ?

Run following SQL from apps user

SQL> select RELEASE_NAME from fnd_product_groups;
You should see output like
RELEASE_NAME
-----------------------
11.5.10.2
Which means you are on Apps Version 11.5.10.2

Q: Can I run 64 bit application on 32 bit Operating system ?

You can run 32 bit application (like oracle application server, web server, all oracle application server are 32 bit ) on both 32 /64 bit operating system but a 64 bit application like 64 bit database can run only on 64 bit operating system.


Q How to find if your database is 32 bit or 64 bit(Useful in applying Patches) ?

execute "file $ORACLE_HOME/bin/oracle" , you should see output like
/u01/db/bin/oracle: ELF 64-bit MSB executable SPARCV9 Version 1
which means you are on 64 bit oracle
If your oracle is 32 bit you should see output like
oracle: ELF 32-bit MSB executable SPARC Version 1
Now you know what should be bit of patch to download



Q How to Discoverer Version installed with Apps ?

Discoverer with Apps installed in ORACLE_HOME same as 806 is usually 3i or 4i. To find Version login to Application Tier & go to $ORACLE_HOME/discwb4/bin and execute
strings dis4ws | grep -i 'discoverer version'
You should see output like
Discoverer Version:Session 4.1.47.09.00

Q. How to find Workflow Version embedded in Apps 11i ?

Run following SQL from apps user ;
SQL>select TEXT from WF_RESOURCES where NAME='WF_VERSION';

You should see output like
TEXT
-----------------------
2.6.0
Which means you are on Workflow Version 2.6.0


You can also use script wfver.sql in FND_TOP/sql to find version of workflow in Apps.


Q: How to find version of JDK Installed on Apps ?

There might be multiple JDK installed on Operating System . Like JDK 1.3.1, 1.4.2 or 1.5 but in order to find which Version of JDK your Apps is using
Open your Context File $SID_$HOSTNAME.xml under $APPL_TOP/admin and look for variable
JDK_TOP oa_var="s_jdktop" what so ever value assigned against that parameter go to that directory & cd bin & execute command
./java -version so lets assume entry above is /usr/jdk then cd /usr/jdk/bin & ./java -version , you will see output like

java version "1.4.2_10"
Java(TM) 2 Runtime Environment, Standard Edition (build 1.4.2_10-b03)
Java HotSpot(TM) Client VM (build 1.4.2_10-b03, mixed mode)

Linux commnads while troubleshooting Oracle Apps

Enable Trace on any Executable to find out whats happening at O.S. Level

truss -eafl -o output.trc -o truss.txt
for example for Apache
truss -eafl -o output.trc -o truss.txt apachectl

This command will trace any system calls and will help you to find out errors.



How to find a "word" or pattern in all files in a directory & subdirectories

find . -name "*" -exec grep -l {} \; -print
for example I want to search for word oracle
find . -name "*" -exec grep -l oracle {} \; -print



How to delete files older than N number of days , Useful to delete old log files

find . -name '*.*' -mtime + -exec rm {} \;
for example if I want to delete all files older than 7 days
find . -name '*.*' -mtime +7 -exec rm {} \;
*Check carefully & run it first from logs or dump directory


How to find a class inside a set of Jar files


for i in 'find .-name *.jar'
do
if test 'jar -tvf $i|grep QualityObject.class'
then
ls $i
fi
done


How to find the symbolic links that point to the old path in your oracle_home and appl_top.

This command is useful in cloning after restore from source to target that symbolic link are not pointing to source.

ls -al `find . -type l` | grep $OLD_PATH



To find all the text files that contains the old path at the UNIX level.

find . -type f -print|grep -v ":"|xargs file|grep "text"|awk '{FS=":"}{print $1}'|xargs grep -il $OLD_PATH



How to Sort files based on Size of file in a Directory
Useful in finding out spaces issues

ls -l | sort -nrk 5 | more


How to check if a Port is listening for any Service

netstat -an | grep $PORTNO

How to schedule a Job in Unix

Use cronjob
crontab -l ( list current jobs in cron)
crontab -e ( edit current jobs in cron )
_1_ _2_ _3_ _4_ _5_ $Job_Name
1 - Minutes (0-59)
2 - Hours ( 0-24)
3 - day of month ( 1- 31 )
4 - Month ( 1-12)
5 - A day of week ( 0- 6 ) 0 -> sunday 1-> monday
e.g. 0 0 1 * 5 Means run job at Midnight on 1st of month & every friday

R12 Login page is not opening, a blank page is being displayed.

Below is the error in the oacore log

javax.servlet.ServletException: java.lang.RuntimeException: Guest user/pwd does not exist or match: GUEST/ORACLE


Running the below query, confirmed that GUEST user and password was not in sync

select fnd_web_sec.validate_password('GUEST','ORACLE') from dual;

FND_WEB_SEC.VALIDATE_PASSWORD('GUEST','ORACLE')
--------------------------------------------------------------------------------
N


Solution:
1. Shutdown the EBS services.
2. Use the below API to reset the GUEST user password:

java oracle.apps.fnd.security.AdminAppServer APPS/<APPS Password> UPDATE DBC=<Full path of EBS DBC File> GUEST_USER_PWD=GUEST/<Guest User Password>  DB_HOST= <Host_Name> DB_PORT=<PortNumber>
DB_NAME=<SID>

3. Run autoconfig on DB Node and then application node.

4. Execute the below sql again:

select fnd_web_sec.validate_password('GUEST','ORACLE') from dual;

Check whether query output is showing Y.
If no,  Please check whether the below error is seen on autoconfig log:

Unable to update GUEST_USER_PWD in database to GUEST/ORACLE - Password was not changed, this point to the DB parameter JAVA_JIT_ENABLED which is set as TRUE.

5. On 11g DB you need to have the below settings for EBS specifically, run the below sql

alter system set JAVA_JIT_ENABLED= FALSE scope = both;

6. Follow the steps 2 and 3 again.

7. Run the below command:

perl $FND_TOP/patch/115/bin/ojspCompile.pl --compile --flush -p 2

7. Check whether the compile completed successfully.

8. Restart the application services.