Monday, February 2, 2015

How To Clear Caches

Apache / iAS 
For 11i and earlier versions:
- shutdown iAS server
- go to $OA_HTML (for 11.5.9) or $COMMON_TOP (for 11.5.10.x) directory
- backup the directory _pages and delete its contents by running for instance:
  rm -rf $COMMON_TOP/_pages/*
- for modplsql caches remove contents of  $IAS_ORACLE_HOME/Apache/modplsql/cache directory
- restart iAS server

To clear middle tier cache in release 12:
- go to "Functional Administrator" responsibility
- select Core Services => Caching Framework => Global Configuration => Clear cache
you may need to clear the related directories after backup:
$OA_HTML/cabo/images/cache
$OA_HTML/cabo/styles/cache

Web Browser
for Internet Explorer:
- go to menu Tools => Internet Options,
- select 'General' tab,
- click on button 'Delete Files' in 'Temporary Internet files' area
  or
- click on button 'Delete...' then in 'Delete Browsing History' pop-up window click on 'Delete Files...'
- close all IE windows and restart new browser session.
for Mozilla Firefox:
- go to menu Tools
- select 'Clear Private Data...' or 'Clear Recent History...' then check 'Cache'
  or
- go to menu Edit or Tools
- select Preferences or Options.
- expand the 'Advance' options and choose 'Cache' or Privacy
- click the button called 'Clear Cache'.
for Safari:
- go to Safari menu
- select Empty Cache => Click 'Empty' in the dialogue box
- go to menu Edit => Preferences,
- choose 'Cache' in 'Advanced' category,
- click on buttons 'Clear Memory Cache' and 'Clear Disk Cache'.
- close all Netscape windows and restart new browser session.
Jinitiator
Two possibilities depending of the Jinitiator version:
for 1.1.8.x versions:
- delete all files in directory:
C:\Program Files\Oracle\Jinitiator <version>\jcache\
for 1.3.1.x versions:
- go to Start => Parameters => Control Panel
- double-click on "Jinitiator <version>" icon
- in the new pop-up window, click on "Cache" tab
- click on "Clear Jar Cache " button. On prompt, click Yes.
(you can also delete directly all files under directory:
C:\Documents and Settings\<your user name>\Oracle Jar Cache)

Java
- go to Start => Parameters => Control Panel
- double-click on 'Java' icon
- in the new pop-up window, click on 'General' tab
- click on 'Setting...' button in 'Temporary Internet files' area then click on 'Delete Files...' button
  (you can select Applets, Applications or other files)
JVM
- go to responsibility "Functional Administrator"
- click on "Core Services" tab then the "Caching Framework" sub-tab
- click on "Global Configuration" link then click on "Clear all cache" button to clear all of the Java Cache's

Portal
- go to the url: http://<host.domain>:<port>/pls/admin_/gateway.htm or cache.htm
- click on "Cache Settings" option and note the "Cache Directory"
- go to this directory and delete all the cache files in the directory and sub directories
See also modplsql caches in Apache/iAS section above.

WebADI / BNE cache

For instance when enabling BNE log
- go to url: http://<host.domain>:<port>/oa_servlets/oracle.apps.bne.framework.BneAdminServlet
- click on the "clear-cache" link
- at the bottom of the page you should see 'Cache Cleared'

In case you have login issue after accidentally cleared the _pages instead of using the method above for r12

Recompile jsp files as below.

Use the ojspcompile.pl perl script to perform a manual pre-compilation of the JSP pages. The following command will compile all the JSP pages and build up the JSP cache again.
Unix: # perl $FND_TOP/patch/115/bin/ojspCompile.pl --compile --flush -p 2
Windows: C:> perl -x <FND_TOP>\patch\115\bin\ojspCompile.pl -compile -flush


Cabo
Images and style sheets can be corrupted or out of sync in the cabo caches,
for Netscape:
Java/JRE plug-in (Windows)

Sunday, December 28, 2014

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.

Sunday, October 19, 2014

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

Friday, October 10, 2014

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

Thursday, October 9, 2014

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.

Tuesday, August 19, 2014

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]

Tuesday, July 22, 2014

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.