Friday, July 4, 2014

How to activate Periodic Alert Scheduler in Oracle EBS Application


Hi Apps DBAs,

 We can activate Periodic Alert Scheduler in Oracle EBS Application

Step 1:Login to ORACLE EBS Application HOME PAGE with Alert Manager Responsibility

We need to Login the Oracle EBS Application HOME PAGE with Alert Manager Responsibility or else assign the responsibility to the User

In the Database we can login with 'Apps' user and check the user exists

SQL> SELECT USER_NAME from fnd_user where  USER_NAME like '%RAFI%';

USER_NAME
--------------------------------------------------------------------------------
RAFI

How to check responsibilities assigned to a user:

SQL> SELECT B.RESPONSIBILITY_NAME
     FROM FND_USER_RESP_GROUPS A,
     FND_RESPONSIBILITY_VL B,
     FND_USER C
     WHERE A.responsibility_id = B.responsibility_id AND
     C.user_id = A.user_id AND
     (to_char(A.end_date) IS NULL
     OR A.end_date > sysdate)
     AND C.user_name like '%RAFI%';


RESPONSIBILITY_NAME
--------------------------------------------------------------------------------
TL System Administrator (Read Only)
TL System Administrator
Alert Manager

Step 2: We need to have alert Manager Responsibily to do this task.

We need to Navigate to Alert Manager Responsibility

Navigate to alert ->Define ;Go to Request->Schedule

there we can see periodic Alert Scheduler =>Active it (Click on Activate button)

Screenshot below for reference.





Enjoy Apps DBA tasks....


Thanks,

Rafi






















































Sunday, June 29, 2014

Killing multiple sessions in Oracle Database

Hi,

We often get task to kill sessions which are active,inactive and the session related with a particular program.When we have one or two sessions we can kill it easily,if we have multiple sessions we can do it as follows:


Step 1:Check the Name of the Database
sqlplus "/as sysdba"

SQL>Select name from v$database;

Step 2:How to kill all inactive session in Database

sqlplus "/as sysdba"

SQL>set heading off

SQL>spool kill12.sql

SELECT 'ALTER SYSTEM KILL SESSION '||''''||sid ||','|| serial#||''''||' immediate;'
 FROM v$session
 WHERE status ='INACTIVE' and type != 'BACKGROUND';

SQL>spool off

then execute the sql script

SQL> @kill12.sql

Note:For RAC Database where we have more than 1 instances,we need to follow step 2 on all the Oracle instances.

How to kill all active session in Database:
sqlplus "/as sysdba"

SQL>set heading off
SQL>spool kill_active.sql

SELECT 'ALTER SYSTEM KILL SESSION '||''''||sid ||','|| serial#||''''||' immediate;'
FROM v$session
WHERE status ='ACTIVE' and type != 'BACKGROUND';

SQL>spool off

Note:For RAC Database where we have more than 1 instances,we need to follow step 2 on all the Oracle instances.


How to kill all ODI sessions in Database:
sqlplus "/as sysdba"

SQL>set heading off
SQL>set lines 1000
SQL>set linesize 2000
SQL>spool kill_active.sql

SELECT 'ALTER SYSTEM KILL SESSION '||''''||sid ||','|| serial#||''''||' immediate;'
FROM v$session
WHERE status ='ACTIVE' AND USERNAME LIKE '%ODI%';

SQL>spool off


Note:For RAC Database where we have more than 1 instances,we need to follow step 2 on all the Oracle instances.


How to get the list of Users and Processes running ODI sessions:

SQL> SET LINESIZE 100
COLUMN spid FORMAT A10
COLUMN username FORMAT A10
COLUMN program FORMAT A45

SELECT s.inst_id,s.sid,s.serial#,p.spid, s.username, s.program
 FROM   gv$session s
 JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE  s.type != 'BACKGROUND' AND S.USERNAME LIKE '%ODI%'; 

 How to kill a particular object blocking session:

 1.Find the tables(objects) which are locked:

SELECT o.owner, o.object_name, o.object_type, o.last_ddl_time, o.status, l.session_id, l.oracle_username, l.locked_mode
FROM dba_objects o, gv$locked_object l
WHERE o.object_id = l.object_id and o.object_name='XX_OBJECT';

2.Killing the session holding the lock:

--Find the serial# for the sessions holding the lock:

SQL> select SERIAL# from v$session where SID=667;

SERIAL#
----------
21091

SQL> alter system kill session '667,21091';

Note:For RAC Database where we have more than 1 instances,we need to follow step 2 on all the Oracle instances.

Enjoy DBA tasks...

Happy DBA learning..


Best regards,

Rafi






Wednesday, June 18, 2014

Reorganization of table in Oracle Database

Hi All,


As we all know reorganization help in better disk space management and performance management in oracle Database,so this task is often done in companies for Tables,Tablespaces and Databases.It helps to reduce the fragmentation( which affects the performance of the Oracle).

Below are the Steps I follow for doing reorganization:

Step 1:Check the last analyzed, tables size before Reorganization:


-- Query 1:Check the last Analyzed and number of rows:
SQL> select name from v$database;

NAME
---------
TESTDB

SQL> SELECT  OWNER||' '||TABLE_NAME||' '||LAST_ANALYZED||' '|| PARTITIONED||' '||NUM_ROWS  FROM DBA_TABLES WHERE TABLE_NAME='ORDER_TAB';

OWNER||''||TABLE_NAME||''||LAST_ANALYZED||''||PARTITIONED||''||NUM_ROWS
--------------------------------------------------------------------------------
ORD_SCHEMA ORDER_TAB 06-JUN-14 NO 71374990


--Query 2 :Check the size of the table

SELECT OWNER||' '||SEGMENT_NAME||' '||BYTES/1024/1024||' '||BLOCKS FROM DBA_SEGMENTS WHERE SEGMENT_NAME='ORDER_TAB';

OWNER||''||SEGMENT_NAME||''||BYTES/1024/1024||''||BLOCKS
--------------------------------------------------------------------------------
ORD_SCHEMA ORDER_TAB 41643 5330304


Note: top -c enter 1 to get the list of cpus =>8 we can give parallel upto 16


ALTER INDEX APPS.XHL_ORDER_TAB_NU5 REBUILD NOLOGGING PARALLEL 12


Note:CUSTOM_SCHEMA,APPS and ORD_SCHEMA are the important schemas used.

Step 2: Get the list of all the INDEXES associated with the tables used for Reorganization.

set lines 2000
set linesize 2000
select 'alter index ORD_SCHEMA.'||index_name||' rebuild NOLOGGING parallel 12;' from dba_indexes where table_name='ORDER_TAB';


If db is up:
=================


select 'alter index ORD_SCHEMA.'||index_name||' rebuild online NOLOGGING parallel 12;' from dba_indexes where table_name='ORDER_TAB';


INDEXES NEEDS TO BE REBUILDED:
=================================


--alter index ORD_SCHEMA.ORDER_TAB_NU17 rebuild online parallel 12;
--alter index ORD_SCHEMA.ORDER_TAB_F2 rebuild online parallel 12;
--alter index ORD_SCHEMA.ORDER_TAB_N18 rebuild online parallel 12;
alter index ORD_SCHEMA.XHL_ORDER_TAB_NU3 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N10 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N11 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N12 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_U1 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N6 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_U2 rebuild parallel 12;
alter index ORD_SCHEMA.XHL_ORDER_TAB_NU1 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N9 rebuild parallel 12;
alter index ORD_SCHEMA.XHL_ORDER_TAB_NU4 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N1 rebuild parallel 12;
alter index ORD_SCHEMA.XHL_ORDER_TAB_NU2 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N7 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N8 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N2 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N3 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N4 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N5 rebuild parallel 12;
alter index ORD_SCHEMA.XHL_ORDER_TAB_NU5 rebuild parallel 12;
alter index ORD_SCHEMA.XHL_ORDER_TAB_R1 rebuild parallel 12;



Step 3:Make sure to make it noparallel and  logging after indexes are rebuild:

Make it no parallel to release the CPU channels

select 'alter index ORD_SCHEMA.'||index_name||' noparallel logging;' from dba_indexes where table_name='ORDER_TAB';


Step 3:Move the tables to reduce fragmentation.

SQL> spool move_table_10June2014.out
SQL> ALTER TABLE ORD_SCHEMA.ORDER_TAB MOVE;
SQL> spool off

Table altered.



Step 4: Rebuild indexes online using VNC session(vncserver)

We need to rebuild indexes using VNC session or run it in the form of shell scripts using nohup as this operation takes time.

spool rebuild_10Jun2014.out
alter index ORD_SCHEMA.ORDER_TAB_NU17 rebuild parallel 12;
alter index ORD_SCHEMA.ORDER_TAB_F2 rebuild parallel 12;
alter index ORD_SCHEMA.ORDER_TAB_N18 rebuild parallel 12;
alter index ORD_SCHEMA.XHL_ORDER_TAB_NU3 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N10 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N11 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N12 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_U1 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N6 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_U2 rebuild parallel 12;
alter index ORD_SCHEMA.XHL_ORDER_TAB_NU1 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N9 rebuild parallel 12;
alter index ORD_SCHEMA.XHL_ORDER_TAB_NU4 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N1 rebuild parallel 12;
alter index ORD_SCHEMA.XHL_ORDER_TAB_NU2 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N7 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N8 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N2 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N3 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N4 rebuild parallel 12;
alter index ORD_SCHEMA.OE_ORDER_HEADERS_N5 rebuild parallel 12;
alter index ORD_SCHEMA.XHL_ORDER_TAB_NU5 rebuild parallel 12;
alter index ORD_SCHEMA.XHL_ORDER_TAB_R1 rebuild parallel 12;
spool off


Step 5:Check index status again after rebuild

Check index status again after rebuild,if they are in unusable status for some reason,please rebuild them again and make sure
it comes to VALID Status.



SQL> select owner, index_name,STATUS from dba_indexes where status='UNUSABLE' and table_name='ORDER_TAB';

OWNER                          INDEX_NAME                     STATUS
------------------------------ ------------------------------ --------
APPS                           ORDER_TAB_NU17      UNUSABLE
ORD_SCHEMA                            ORDER_TAB_F2        UNUSABLE
ORD_SCHEMA                            XHL_ORDER_TAB_NU3   UNUSABLE
ORD_SCHEMA                            OE_ORDER_HEADERS_N10           UNUSABLE
ORD_SCHEMA                            OE_ORDER_HEADERS_N11           UNUSABLE
ORD_SCHEMA                            OE_ORDER_HEADERS_N12           UNUSABLE
ORD_SCHEMA                            OE_ORDER_HEADERS_U1            UNUSABLE
ORD_SCHEMA                            OE_ORDER_HEADERS_N6            UNUSABLE
ORD_SCHEMA                            OE_ORDER_HEADERS_U2            UNUSABLE
ORD_SCHEMA                            XHL_ORDER_TAB_NU1   UNUSABLE
ORD_SCHEMA                            OE_ORDER_HEADERS_N9            UNUSABLE

OWNER                          INDEX_NAME                     STATUS
------------------------------ ------------------------------ --------
CUSTOM_SCHEMA                           XHL_ORDER_TAB_NU4   UNUSABLE
ORD_SCHEMA                            OE_ORDER_HEADERS_N1            UNUSABLE
ORD_SCHEMA                            XHL_ORDER_TAB_NU2   UNUSABLE
ORD_SCHEMA                            OE_ORDER_HEADERS_N7            UNUSABLE
ORD_SCHEMA                            OE_ORDER_HEADERS_N8            UNUSABLE
ORD_SCHEMA                            OE_ORDER_HEADERS_N2            UNUSABLE
ORD_SCHEMA                            OE_ORDER_HEADERS_N3            UNUSABLE
ORD_SCHEMA                            OE_ORDER_HEADERS_N4            UNUSABLE
ORD_SCHEMA                            OE_ORDER_HEADERS_N5            UNUSABLE
APPS                           XHL_ORDER_TAB_NU5   UNUSABLE
ORD_SCHEMA                            XHL_ORDER_TAB_R1    UNUSABLE

22 rows selected.


To check unusable indexes for table:
==========================================


SQL> select owner, index_name,STATUS from dba_indexes where status='UNUSABLE' and table_name='ORDER_TAB';

no rows selected

SQL>




Step 6:Make it no parallel and release the channels:

select 'alter index ORD_SCHEMA.'||index_name||' noparallel logging;' from dba_indexes where table_name='ORDER_TAB';





Step 7:Check the status of INDEX after rebuild
Check the status of INDEX after rebuild,make sure it is in valid state or else rebuild the INDEX again.
SELECT INDEX_NAME||' '||INDEX_TYPE||' '||STATUS
FROM USER_INDEXES WHERE TABLE_NAME IN('PS_TEST_WL','PSWORKLIST','PS_TEST_USERINST','PS_TEST_STEPINST','PS_TEST_TAUTH_AW','PS_TEST_ADV_AW','PS_TEST_SHEET_AW');


Step 8:Run the gather stats for the tables in VNC session:

We run gather statistics to choose the best posible execution plan.

spool gather_stats_tables_TESTDB.out



sqlplus "/as sysdba"

SQL>

Begin DBMS_STATS.GATHER_TABLE_STATS(
ownname => 'ORD_SCHEMA',
tabname => 'ORDER_TAB',
degree => 10,
cascade => TRUE,
METHOD_OPT => 'FOR COLUMNS SIZE AUTO',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);
END;
/


Step 9:Verify the number of rows,statistics ,tables size after reorganization

SQL> select name from v$database;

NAME
---------
TESTDB

SQL> SELECT  OWNER||' '||TABLE_NAME||' '||LAST_ANALYZED||' '|| PARTITIONED||' '||NUM_ROWS  FROM DBA_TABLES WHERE TABLE_NAME='ORDER_TAB';

OWNER||''||TABLE_NAME||''||LAST_ANALYZED||''||PARTITIONED||''||NUM_ROWS
--------------------------------------------------------------------------------
ORD_SCHEMA ORDER_TAB 11-JUN-14 NO 71348700

SQL> SELECT OWNER||' '||SEGMENT_NAME||' '||BYTES/1024/1024||' '||BLOCKS FROM DBA_SEGMENTS WHERE SEGMENT_NAME='ORDER_TAB';

OWNER||''||SEGMENT_NAME||''||BYTES/1024/1024||''||BLOCKS
--------------------------------------------------------------------------------
ORD_SCHEMA ORDER_TAB 42534 5444352




Hope it helps.Enjoy DBA tasks...



Thanks,

Rafi

Thursday, February 13, 2014

Changing the look and feel of Oracle EBS environment



Changing the look and feel of Oracle EBS environment:

Changing the Oracle EBS look and feel help us to change the colour of the HTML and java based form page in Oracle EBS,the advantage is,it helps us differentiate between PRODUCTION and NON-PRODUCTION environment .

We can follow the below steps to achieve it:

Step 1:Login to Oracle EBS Home page with ‘sysadmin’ user.

Step 2:Navigate to  System Administrator Responsibility =>system profile form page

Step 3:Navigate to the System Profile option Name =>Java Look and Feel
And Site =>oracle   ,Screenshot below for reference.



Step 4: :Navigate to the System Profile option Name =>Java Colour scheme
And site =>teal
This choose the teal colour   ,Screenshot below for reference



Step 5: :Navigate to the System Profile option Name ,profile to change the =>Oracle Applications Look and Feel
And site =>Base Desktop look and feel.  ,Screenshot below for reference.



Step 6:Signout and login Oracle EBS application again to see the new look and feel it.

Enjoy Apps DBA tasks,Happy Apps DBA learning...


Best regards,

Rafi.



Sunday, February 2, 2014

Using Data mover psdmt utility to refresh table data or move data from one evironment to other

 Hi DBAs,

PSDMT (Data mover) utility is very useful to refresh table data from one environment to other.In the below example DEVDB(Source) and TESTDB(Target).



We can prefer this utility when we have huge amount of tables data to move from one environment to other.
.
Step 1:Go to Peoplesoft Datamover client utility psdmt.
We have to go to People Tools Home and navigate to psdmt.exe


Step 2:Login to Datamover with DEVDB(source) as below:
Login to DEVDB(Source):

user:ADM1
pass:Welcome123

Note:Remove files from temp directory  before performing below steps.
=====


Step 3: Copy the below script in Datamover

Set log c:\temp\log_exp.log;
Set output c:\temp\data.dat;
Export SGA_BVN_MAPPING;
Export SRVC_GROUP_AREA;


Step 4: Import into TESTDB database using datamover(psdmt)

Login to TESTDB(Target):

User:ADM1
pass:welcome123

Step 5:Copy the below script in Datamover

Set log c:\temp\log_imp.log;
Set input c:\temp\data.dat;
import SGA_BVN_MAPPING;
import SRVC_GROUP_AREA;

Step 6:Verify the COUNT OF ROWS in tables on source(DEVDB) and target(TESTDB):
SOURCE:DEVDB

CONN USER1 and give below:

SQL> select name from v$database;

NAME
---------
DEVDB

SQL> select count(*) from USER1.SGA_BVN_MAPPING;

  COUNT(*)
----------
    262742

SQL> select count(*) from USER1.SRVC_GROUP_AREA;

  COUNT(*)
----------
   9864049

TARGET:(TESTDB)

CONN USER1 and give below:

SQL> select name from v$database;

NAME
---------
TESTDB

SQL> select count(*) from USER1.SGA_BVN_MAPPING;

  COUNT(*)
----------
    262742

SQL> select count(*) from USER1.SRVC_GROUP_AREA;

  COUNT(*)
----------
   9864049

Step 7:Send mail
Completed refresh of tables SGA_BVN_MAPPING and SRVC_GROUP_AREA in TESTDB.


Enjoy PeopleSoft DBA tasks.



Best regards,
Rafi


Monday, January 6, 2014

Applying patch to Multi-node Oracle EBS R12 Applications with SHARED APPL_TOP and PCP setup

Hi DBAs,

For a multi-node system with a shared application tier file system patching time will be reduce,as we apply patch only once – on the primary node.This ensures minimal downtime as all adpatch actions are performed only once.
Below are the steps which I follow to apply patch to Oracle EBS R12 Applications for 2 nodes Applications where SHARED APPL_TOP is enabled
along with PCP setup.This is RAC Oracle 11g Database.

We need to find out the primary node in the PCP(Parallel concurrent Processing) Concurrent managers set up.
Below link can be used for it:

http://rafioracledba.blogspot.com/search?q=primary

Step 1: Before applying patch:(check invalid objects in DB):

SQL> select name from v$database;

NAME
---------
TESTDB

SQL> select owner,count(*) from dba_objects where status='INVALID' group by owner;

OWNER                            COUNT(*)
------------------------------ ----------
PUBLIC                                  1
APPS                                  158
SYS                                     3

Step 2:Enable Maintainance mode

 ebstest:applmgr::/home/applmgr>echo $TWO_TASK
TESTDB
Use 'adadmin' to enable maintainance mode before patching.

 5.    Change Maintenance Mode

Change Maintenance Mode
   ----------------------------------------

Maintenance Mode is currently: [Enabled].

Backing up restart files, if any......Done.

             Change Maintenance Mode
   ----------------------------------------

Maintenance Mode is currently: [Enabled].


Step 3:Applying patch using 'adpatch'(auto patch utility):Note:Review the readme file twice before proceeding with 'adpatch' session.
It is very important to review the readme.txt in unix vi editor before applying applying patch
and follow the instruction as given in the readme.txt file and apply any pre-requistics patch
 if required.autoconfig run also not required unless it is specified after patch installation in readme.txt file.

Go to the patch top directory,where all driver and required ldt files are present with the application
filesystem owner,makes sure all files have read,write and execute permission.
As described below:


ebstest1:applmgr::/stage/patches/8845580>ls -altr
total 12432
-rwxrwxrwx    1 applmgr  dba            2931 Oct 29 2007  u8845580.drv
-rwxrwxrwx    1 applmgr  dba            1173 Oct 29 2007  f8845580.ldt
-rwxrwxrwx    1 applmgr  dba         6323692 Oct 29 2007  b8845580.ldt
-rwxrwxrwx    1 applmgr  dba              37 Oct 29 2007  marker1.txt
-rwxrwxrwx    1 applmgr  dba            2852 Oct 29 2007  README.txt
-rwxrwxrwx    1 applmgr  dba            5502 Oct 29 2007  README.html
drwxrwxrwx    5 applmgr  dba             256 Feb 21 01:21 po
drwxrwxrwx   54 root     dba           12288 Feb 21 01:21 ..
drwxr-xr-x    3 applmgr  dba             256 Feb 21 01:21 .
ebstest:applmgr::/stage/patches/8845580>adpatch

Review the README for pre-requisite information.

Your default directory is '/d21/TESTDB/apps/appl'.
Is this the correct APPL_TOP [Yes] ?

Verify the log file after applying patch.Below is the 'adpatch' session.


ebstest1:/tmp/rafi/8845580>adpatch

                     Copyright (c) 2002 Oracle Corporation
                        Redwood Shores, California, USA

                         Oracle Applications AutoPatch

                                 Version 12.0.0

NOTE: You may not use this utility for custom development
      unless you have written permission from Oracle Corporation.


Attention: AutoPatch no longer checks for unapplied pre-requisite patches.
You must use OAM Patch Wizard for this feature. Alternatively, you can
review the README for pre-requisite information.


Your default directory is '/product/app/TESTDB/apps/apps_st/appl'.
Is this the correct APPL_TOP [Yes] ?

AutoPatch records your AutoPatch session in a text file
you specify.  Enter your AutoPatch log file name or press [Return]
to accept the default file name shown in brackets.

Filename [adpatch.log] : 8845580_05Jan2014.log

You can be notified by email if a failure occurs.
Do you wish to activate this feature [No] ?

Please enter the batchsize [1000] :


Please enter the name of the Oracle Applications System that this
APPL_TOP belongs to.

The Applications System name must be unique across all Oracle
Applications Systems at your site, must be from 1 to 30 characters
long, may only contain alphanumeric and underscore characters,
and must start with a letter.

Sample Applications System names are: "prod", "test", "demo" and
"Development_2".

Applications System Name [TESTDB] : TESTDB *


NOTE: If you do not currently have certain types of files installed
in this APPL_TOP, you may not be able to perform certain tasks.

Example 1: If you don't have files used for installing or upgrading
the database installed in this area, you cannot install or upgrade
the database from this APPL_TOP.

Example 2: If you don't have forms files installed in this area, you cannot
generate them or run them from this APPL_TOP.

Example 3: If you don't have concurrent program files installed in this area,
you cannot relink concurrent programs or generate reports from this APPL_TOP.


Do you currently have files used for installing or upgrading the database
installed in this APPL_TOP [YES] ? YES *


Do you currently have Java and HTML files for HTML-based functionality
installed in this APPL_TOP [YES] ? YES *


Do you currently have Oracle Applications forms files installed
in this APPL_TOP [YES] ? YES *


Do you currently have concurrent program files installed
in this APPL_TOP [YES] ? YES *


Please enter the name Oracle Applications will use to identify this APPL_TOP.

The APPL_TOP name you select must be unique within an Oracle Applications
System, must be from 1 to 30 characters long, may only contain
alphanumeric and underscore characters, and must start with a letter.

Sample APPL_TOP Names are: "prod_all", "demo3_forms2", and "forms1".

APPL_TOP Name [ebstest2] : ebstest2 *



You are about to apply a patch to the installation of Oracle Applications
in your ORACLE database 'TESTDB'
using ORACLE executables in '/product/app/TESTDB/apps/tech_st/10.1.2'.

Is this the correct database [Yes] ?

AutoPatch needs the password for your 'SYSTEM' ORACLE schema
in order to determine your installation configuration.

Enter the password for your 'SYSTEM' ORACLE schema:


The ORACLE username specified below for Application Object Library
uniquely identifies your existing product group: APPLSYS

Enter the ORACLE password of Application Object Library [APPS] :

AutoPatch is verifying your username/password.
The status of various features in this run of AutoPatch is:

                                           <-Feature version in->
Feature                          Active?   APPLTOP    Data model    Flags
------------------------------   -------   --------   -----------   -----------
CHECKFILE                        Yes       1          1             Y N N Y N Y
PREREQ                           Yes       6          6             Y N N Y N Y
CONCURRENT_SESSIONS              No        2          2             Y Y N Y Y N
PATCH_TIMING                     Yes       2          2             Y N N Y N Y
PATCH_HIST_IN_DB                 Yes       6          6             Y N N Y N Y
SCHEMA_SWAP                      Yes       1          1             Y N N Y Y Y
JAVA_WORKER                      Yes       1          1             Y N N Y N Y
CODELEVEL                        Yes       1          1             Y N N Y N Y



Identifier for the current session is 44869

Reading product information from file...

Reading language and territory information from file...

Reading language information from applUS.txt ...

AutoPatch warning:
 Product Data File
 /product/app/TESTDB/apps/apps_st/appl/admin/zfaprod.txt
 does not exist for product "zfa".
This product is registered in the database but the
above file does not exist in APPL_TOP.  The product
will be ignored without error.


AutoPatch warning:
 Product Data File
 /product/app/TESTDB/apps/apps_st/appl/admin/zsaprod.txt
 does not exist for product "zsa".
This product is registered in the database but the
above file does not exist in APPL_TOP.  The product
will be ignored without error.


AutoPatch warning:
 Product Data File
 /product/app/TESTDB/apps/apps_st/appl/admin/jtsprod.txt
 does not exist for product "jts".
This product is registered in the database but the
above file does not exist in APPL_TOP.  The product
will be ignored without error.


Reading database to see what industry is currently installed.

Reading FND_LANGUAGES to see what is currently installed.
Currently, the following language is installed:

Code   Language                                Status
----   --------------------------------------- ---------
US     American English                        Base

Your base language will be AMERICAN.

Setting up module information.
Reading database for information about the modules.
Saving module information.
Reading database for information about the products.
Reading database for information about how products depend on each other.
Reading topfile.txt ...

Saving product information.

AD code level : [B.3]



Trying to obtain a lock...


  Attempting to instantiate the current-view snapshot...

  No baseline bug-fixes info available. Will attempt next time.


     **************** S T A R T   O F   U P L O A D ****************

Start date: Sun Jan 05 2014 09:15:26



0 "left over" javaupdates.txt files uploaded to DB: Sun Jan 05 2014 09:15:26

0 patches uploaded from the ADPSV format patch history files: Sun Jan 05 2014 09:15:26


Uploading information about files copied during the previous runs ...

0 "left over" filescopied_<session_id>.txt files uploaded to DB: Sun Jan 05 2014 09:15:26

     ****************** E N D   O F   U P L O A D ******************




End date: Sun Jan 05 2014 09:15:26




Enter the directory where your Oracle Applications patch has been unloaded

The default directory is [/tmp/rafi/8845580] :

Please enter the name of your AutoPatch driver file : u8845580.drv


Done with Invoker's Rights processing.


Telling workers to quit...

1 worker has quit.  Waiting for 47 more.

All workers have quit.


Dropping FND_INSTALL_PROCESSES table...


FND_INSTALL_PROCESSES table dropped.


Dropping AD_DEFERRED_JOBS table...


AD_DEFERRED_JOBS table dropped.

  Updating the checkfile repository if necessary...
Processing the last batch...
  Done updating the checkfile repository...

Done running SQL scripts and EXEC commands.

Compiling invalid objects...


sqlplus -s APPS/***** @/product/app/TESTDB/apps/apps_st/appl/ad/12.0.0/sql/adutlrcmp.sql APPLSYS ***** APPS ***** ***** 48  0 NONE FALSE

Number of invalid objects: 141

Getting list of invalid objects in APPS schema.

Invalid object reports are recorded in
/product/app/TESTDB/apps/apps_st/appl/admin/TESTDB/log/44869_postenv.*

Skipping...
 Generate forms library files for Specified driver
 since no such action is present for this driver file

Skipping...
 Generate forms menu files for Specified driver
 since no such action is present for this driver file

Skipping...
 Generate forms for Specified driver
 since no such action is present for this driver file

Skipping...
 Generate reports libraries for Specified driver
 since no such action is present for this driver file

Skipping...
 Generate reports for Specified driver
 since no such action is present for this driver file

Skipping...
 Generate Messages for Specified driver
 since no such action is present for this driver file

Skipping...
 Generate Workflow resource files for Specified driver
 since no such action is present for this driver file

Skipping ...
 Running AutoConfig since none of its templates were
 patched during this run of adpatch.

Saving Patch History information to Database...

Trying to obtain a lock...

> Inserted 1 patch history records (total).

Gathering Statistics for AD_PATCH_HIST_TEMP

Done Gathering Statistics for AD_PATCH_HIST_TEMP

>>> Inserted 1 bug history records for this patch (total).
>>>>> Inserted 0 action history records for this bug (total).

Gathering Statistics for AD_PATCH_HIST_TEMP

Done Gathering Statistics for AD_PATCH_HIST_TEMP

>>> Inserted 1 bug history records for this patch (total).
>>>>> Inserted 0 action history records for this bug (total).



  Updating the current-view snapshot...

  Done saving Patch History information.


About to do IREP processing...

  Attempting to process IREP files ...

  Successfully processed IREP files.

Done IREP processing.


Copying applprod.tmp to applprod.txt (if needed)...

  Did not need to copy applprod.tmp to applprod.txt.

Copying admin/<sid>/applterr.txt to admin/applterr.txt (if needed)...

Did not need to copy admin/TESTDB/applterr.txt to admin/applterr.txt.

sqlplus -s APPS/***** @/product/app/TESTDB/apps/apps_st/appl/ad/12.0.0/sql/adtimrpt.sql 44869 adt44869

A job timing report has been generated for the current session.
You should check the file
    /product/app/TESTDB/apps/apps_st/appl/admin/TESTDB/out/adt44869.lst

for details.


Purging timing information for prior sessions.

sqlplus -s APPS/***** @/product/app/TESTDB/apps/apps_st/appl/ad/12.0.0/sql/adtpurge.sql 10 1000

Done purging timing information for prior sessions.

AutoPatch is complete.

AutoPatch may have written informational messages to the file
/product/app/TESTDB/apps/apps_st/appl/admin/TESTDB/log/8845580_05Jan2014.lgi

Errors and warnings are listed in the log file
/product/app/TESTDB/apps/apps_st/appl/admin/TESTDB/log/8845580_05Jan2014.log

and in other log files in the same directory.


Step 4: Disable Maintainance mode again by using 'adadmin' utility:
 AD Administration Main Menu
   --------------------------------------------------

   1.    Generate Applications Files menu

   2.    Maintain Applications Files menu

   3.    Compile/Reload Applications Database Entities menu

   4.    Maintain Applications Database Entities menu

   5.    Change Maintenance Mode

   6.    Exit AD Administration

Enter your choice [6] : 5

             Change Maintenance Mode
   ----------------------------------------

Maintenance Mode is currently: [Enabled].


Maintenance mode should normally be enabled when patching
Oracle Applications and disabled when users are logged on
to the system.  See the Oracle Applications Maintenance
Utilities manual for more information about maintenance mode.


Please select an option:

   1.    Enable Maintenance Mode

   2.    Disable Maintenance Mode

   3.    Return to Main Menu



Enter your choice [3] : 2

sqlplus -s &un_apps/***** @/product/app/TESTDB/apps/apps_st/appl/ad/12.0.0/patch/115/sql/adsetmmd.sql DISABLE

Successfully disabled Maintenance Mode.
sqlplus -s &un_apps/***** @/product/app/TESTDB/apps/apps_st/appl/ad/12.0.0/patch/115/sql/adsetmmd.sql DISABLE

Successfully disabled Maintenance Mode.

Step 5: Bounce application after applying patch :

Step 5 a:Stop the Concurrent Managers in PCP setup:

In the PCP set use the below steps to Stop the Concurrent Managers.
Identify the Primary node,In our case Nodeapp01 is the prmary node of PCP setup.


a) stop apps listener on node1(Nodeapp01)
=>APPS_TEST
Node1 /product/app/TEST/inst/apps/TESTX1_Nodeapp01/admin/scripts >./adalnctl.sh stop

adalnctl.sh version 120.3

Shutting down listener process APPS_TEST.

adalnctl.sh: exiting with status 0


adalnctl.sh: check the logfile /product/app/TEST/inst/apps/TESTX1_Nodeapp01/logs/appl/admin/log/adalnctl.txt for more information ...


b)Stop apps listener on node2(Nodeapp02)

Node2 /product/app/TEST/inst/apps/TESTX2_icmpapp02/admin/scripts >./adalnctl.sh stop

adalnctl.sh version 120.3

Shutting down listener process APPS_TEST.

adalnctl.sh: exiting with status 0


adalnctl.sh: check the logfile /product/app/TEST/inst/apps/TESTX2_icmpapp02/logs/appl/admin/log/adalnctl.txt for more information ...

c)Stop the apps concurrent managers from node1
===============================================

SinceTEST_R12is having PCP setup,stop the Concurrent managers from node1(Primary node)


Node1:Nodeapp01

/product/app/TEST/inst/apps/TESTX1_Nodeapp01/admin/scripts >./adcmctl.sh stop

You are running adcmctl.sh version 120.17.12010000.5


Enter the APPS username : APPS

Enter the APPS password :
Shutting down concurrent managers for TEST_CM2 ...
ORACLE Password:
Submitted request 2532852 for CONCURRENT FND SHUTDOWN


adcmctl.sh: exiting with status 0


adcmctl.sh: check the logfile /product/app/TEST/inst/apps/TESTX1_Nodeapp01/logs/appl/admin/log/adcmctl.txt for more information ...


step d): Check and Kill all processes by finding the concurrent processes:
On Node 1 and Node 2 make sure no concurrent manager process is running.

Node1: Nodeapp01

ps -ef|grep FNDLIBR|grep applmgr

ps -ef|grep FNDSM|grep applmgr

kill -9 pid

Node 2: icmpapp02

Node2: icmpapp02

ps -ef|grep FNDLIBR|grep applmgr

ps -ef|grep FNDSM|grep applmgr

kill -9 pid


Step e:Run cmclean.sql
Run cmclean.sql from application node by going to $ADMIN_SCRIPTS_HOME or $INST_TOP/admin/scripts
Note:Stop the Concurrent manager services on both the apps node and than run cmclean on any one apps node.
Note:If we don't have cmclean.sql script than we can download and copy it to any of the application node.

TESTEBS2:/product/app/TESTDB/inst/apps/TESTEBS2/admin/scripts>sqlplus

SQL*Plus: Release 10.1.0.5.0 - Production on Sun Jan 5 10:07:50 2014

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Enter user-name: apps
Enter password:

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

SQL> @cmclean.sql
DOC>
DOC>WARNING : Do not run this script without explicit instructions
DOC>from Oracle Support
DOC>
DOC>
DOC>*** Make sure that the managers are shut down ***
DOC>*** before running this script ***
DOC>
DOC>*** If the concurrent managers are NOT shut down, ***
DOC>*** exit this script now !! ***
DOC>
DOC>#
If you wish to continue type the word 'dual': dual



-----------------------------------------------------------------------
-- Updating invalid process status codes in FND_CONCURRENT_PROCESSES

Manager short name   Process id Status code
-------------------- ---------- ------------
FNDICM                   606912 A
FNDCPOPP                 606924 U
FNDOPP18288              606929 U
                         606930 U

4 rows updated.


-----------------------------------------------------------------------
-- Updating running processes in FND_CONCURRENT_QUEUES
-- Setting running_processes = 0 and max_processes = 0 for all managers

51 rows updated.


-----------------------------------------------------------------------
-- Updating invalid control_codes in FND_CONCURRENT_QUEUES

0 rows updated.


51 rows updated.


-----------------------------------------------------------------------
-- Updating any Running or Terminating requests to Completed/Error

0 rows updated.


-----------------------------------------------------------------------
-- Updating any Runalone flags to 'N'

Updated 0 rows of runalone_flag in fnd_conflicts_domain to 'N'

-----------------------------------------------------------------------
Updates complete.
Type commit now to commit these updates, or rollback to cancel.
-----------------------------------------------------------------------

SQL> commit;

Commit complete.



Step f:Stop the apps tier servies on node1 and node2:
ebstest:applmgr::/u01/TESTDB/apps/comn/admin/scripts/TESTDB_ebstest>./adstpall.sh

You are running adstpall.sh version 115.22


Enter the APPS username: apps

Step f:Make sure no 'applmgr' processes is running on node1 and node2

Make sure no 'applmgr' processes or else kill it with kill -9 pid.

On both the nodes check the FNDLIBR and FNDSM processes.

Node 1:Nodeapp01:
-----------------

applmgr@[Nodeapp01]-> ps -ef|grep appmgr

 applmgr  4794     1   0 20:16:05 ?           0:01 FNDLIBR FND Concurrent_Processor MANAGE OLOGIN="APPS/ZGB44E179A7BC94375178D975C
 applmgr  4784     1   0 20:16:04 ?           0:01 FNDLIBR FND Concurrent_Processor MANAGE OLOGIN="APPS/ZGB44E179A7BC94375178D975C
 applmgr  4782     1   0 20:16:04 ?           0:01 FNDLIBR FND Concurrent_Processor MANAGE OLOGIN="APPS/ZGB44E179A7BC94375178D975C
 applmgr  4789     1   0 20:16:05 ?           0:01 FNDLIBR FND Concurrent_Processor MANAGE OLOGIN="APPS/ZGB44E179A7BC94375178D975C
 applmgr  4792     1   0 20:16:05 ?           0:01 FNDLIBR FND Concurrent_Processor MANAGE OLOGIN="APPS/ZGB44E179A7BC94375178D975C
 applmgr  4786     1   0 20:16:04 ?           0:01 FNDLIBR FND Concurrent_Processor MANAGE OLOGIN="APPS/ZGB44E179A7BC94375178D975C

kill -9 4794 4784

applmgr@[Nodeapp01]-> ps -ef|grep applmgr




Node 2:Nodeapps02:
------------------

applmgr@[Nodeapp02]-> ps -ef|grep applmgr

 applmgr  4794     1   0 20:16:05 ?           0:01 FNDLIBR FND Concurrent_Processor MANAGE OLOGIN="APPS/ZGB44E179A7BC94375178D975C
 applmgr  4784     1   0 20:16:04 ?           0:01 FNDLIBR FND Concurrent_Processor MANAGE OLOGIN="APPS/ZGB44E179A7BC94375178D975C
 applmgr  4782     1   0 20:16:04 ?           0:01 FNDLIBR FND Concurrent_Processor MANAGE OLOGIN="APPS/ZGB44E179A7BC94375178D975C
 applmgr  4789     1   0 20:16:05 ?           0:01 FNDLIBR FND Concurrent_Processor MANAGE OLOGIN="APPS/ZGB44E179A7BC94375178D975C
 applmgr  4792     1   0 20:16:05 ?           0:01 FNDLIBR FND Concurrent_Processor MANAGE OLOGIN="APPS/ZGB44E179A7BC94375178D975C
 applmgr  4786     1   0 20:16:04 ?           0:01 FNDLIBR FND Concurrent_Processor MANAGE OLOGIN="APPS/ZGB44E179A7BC94375178D975C

kill -9 4794 4784

applmgr@[Nodeapp02]-> ps -ef|grep applmgr

Step g:Start apps listener on node1 and node2

Node 1:
-------------
 Node1 /product/app/TEST/inst/apps/TESTX1_Nodeapp01/admin/scripts >./adalnctl.sh start

adalnctl.sh version 120.3

Checking for FNDFS executable.
Starting listener process APPS_TEST.

adalnctl.sh: exiting with status 0


adalnctl.sh: check the logfile /product/app/TEST/inst/apps/TESTX1_Nodeapp01/logs/appl/admin/log/adalnctl.txt for more information ...

Node 2:
--------

Node2 /product/app/TEST/inst/apps/TESTX2_icmpapp02/admin/scripts >./adalnctl.sh start

adalnctl.sh version 120.3

Checking for FNDFS executable.
Starting listener process APPS_TEST.

adalnctl.sh: exiting with status 0


adalnctl.sh: check the logfile /product/app/TEST/inst/apps/TESTX2_icmpapp02/logs/appl/admin/log/adalnctl.txt for more information ...


Step h:Start the concurrent manager from node 1(Nodeapp01)

Since TEST_R12 is having PCP setup start the Concurrent managers from node1(Primary node)


Node1 /product/app/TEST/inst/apps/TESTX1_Nodeapp01/admin/scripts >./adcmctl.sh start

You are running adcmctl.sh version 120.17.12010000.5


Enter the APPS username : APPS

Enter the APPS password :
Starting concurrent manager for TEST_CM1 ...
Starting TEST_0216@TEST_CM1 Internal Concurrent Manager
Default printer is noprint

adcmctl.sh: exiting with status 0

Step i:Start applications using adstrtal.sh

ebstest:applmgr::/u01/TESTDB/apps/comn/admin/scripts/TESTDB_ebstest>./adstrtal.sh

You are running adstrtal.sh version 115.22

Step 6:After applying PATCH:

Step a:Verify the patch is applied successfully:

SQL> select name from v$database;

NAME
---------
TESTDB

SQL> select bug_number,creation_date from ad_bugs where bug_number='8845580';

BUG_NUMBER                     CREATION_DATE
------------------------------ ---------------
5549427                        12-FEB-13


Note 1:We run cmclean after clonning also to make sure the node name is updated in the FND_NODES Table correctly
Note 2:In Oracle 11i the cmclean scripts can be found in $COMMON_TOP/admin/scipts/TESTDB_ebstest


SQL> select owner,count(*) from dba_objects where status='INVALID' group by owner;

OWNER                            COUNT(*)
------------------------------ ----------
SYS                                     1
TEST_USER1                    2
TEST_USER2                    5
APPS                                 22

Step b:After finishing the installation of this patch, please perform the
normal post-install steps like :

          1.      (Re)compiling all the database objects
          2.      (Re)generating message files
          3.      (Re)generating forms etc.
          4.      Clear the middle tier cache for Oracle Incentive
                   Compensation.
          5.      Bouncing the middle tier by following steps mentioned in Step 5.

Note 1:We can use 'adadmin' utility for Completing Step 2 and Step 3.
Note 2:For clearing the Middle tier cache use the below linK:
http://rafioracledba.blogspot.com/2014/01/how-to-clear-cache-for-oracle-ebs-r12.html

Step c:Check the version of the file after it is applied by verifying the .lgi (log information file)

EBSTtest1:/product/app/TEST/inst/apps/TEST_EBSTtest1/admin/scripts>echo $CN_TOP

/product/app/TEST/apps/apps_st/appl/cn/12.0.0

EBSTtest1:/product/app/TEST/inst/apps/TEST_EBSTtest1/admin/scripts>grep '$Header' $CN_TOP/patch/115/sql/cnvrpgps.pls

/* $Header: cnvrpgps.pls 120.3.12010000.1 2008/07/24 11:06:30 appldev ship $ */

EBSTtest1:/product/app/TEST/inst/apps/TEST_EBSTtest1/admin/scripts>grep '$Header' $CN_TOP/patch/115/sql/cnvrpgpb.pls

/* $Header: cnvrpgpb.pls 120.11.12010000.3 2009/10/09 23:42:54 rnagired ship $ */


Step d:Do the Health Check of Oracle EBS Application
Login TESTDB application by using Home page URL

Note :We can get HOME page URL by using below query:
SQL>Select Home_URL from icx_parameter;

 http://ebstest.abccomp.com:8035    with sysadmin/welcome123

Health check completed successfully by submitting active user Concurrent request REQUEST ID 28758820.

Step e:Intimate end User 
Make sure you intimate the end User or release your application to the end User.

Enjoy Apps DBA tasks.


Best regards,

Rafi.






How to clear the cache for Oracle EBS R12 or Middle Tier Applications

We can clear the cache in Oracle EBS R12 or Middle Tier Applications using the below steps:


Note: Clearing the OA Framework cache in a PRODUCTION instance can cause data issues if multiple users are engaged and transacting data in the application at the time cache is cleared.  Please only utilize this in Production if advised by Oracle Support Services or Oracle Development.



Step1:Login and choose the Functional Administrator responsibility - then choose Home. As seen in below screenshot.



Step2:Choose the Core Services Tab - then the Caching Framework Sub-Menu (In the dark blue region).
 - Proceed to choose 'Global Configuration' from the left hand side menu that appears
 - In the far right choose 'Clear all Cache' button


Step3:A screen prompts and confirms that the action will clear all cache on the middle tier server - choose Yes.
Essentially, this just forces all user sessions to engage and validate - rather than using cached values.


Step4:A confirmation message is displayed, confirming that all cache has been cleared across middle tiers.

Step5:Proceed to test and confirm whatever change was made to the preference, profile, etc....
References:

How To Clear The Cache Using Functional Administrator? [ID 759038.1]

Enjoy  doing Oracle Apps   DBA tasks.


Best regards,

Rafi