Upgrade R12.1.1 To R12.1.3

To Upgrade Oracle EBS R12.1.1 To R12.1.3 Follow the Below Steps :

From https://support.oracle.com you have to download the following patch :

  • p9239090_R12_LINUX_1of6.zip
  • p9239090_R12_LINUX_2of6.zip
  • p9239090_R12_LINUX_3of6.zip
  • p9239090_R12_LINUX_4of6.zip
  • p9239090_R12_LINUX_5of6.zip
  • p9239090_R12_LINUX_6of6.zip
  • p9239095_R12_GENERIC.zip
  • p9822544_R12.MSC.B_R12_GENERIC.zip(Optional)
  • p10349415_R12.AD.B_R12_GENERIC.zip (Optional)
  • p9239089_R12.AD.B_R12_LINUX.zip
Before Start Working You have to shutdown all application Services using  ( adstpall.sh ) and enable maintenance mode using adadmin 
Run the Patch in Order like the following, before apply Any Patch you need to check README.txt for any Pre-Install Step/Post-Install Step 
1- Start with patch Number 9239089 , before do that on Readme.txt you have to do the below :
-Open Two Session ( Terminal ) Run Apps Env On Terminal #1, Terminal #2 run database Env.
-On DB Terminal Create under $ORACLE_HOME/Appsutil/admin 
-Copy adgrants.sql (Unix)  from patch folder to Above folder.
-From $ORACLE_HOME/appsutil/admin Run adgrants.sql
-sqlplus /nolog
-SQL> @$ORACLE_HOME/appsutil/admin/adgrants.sql
2-Apply Patch 10349415
3-Apply Patch 9239090
4-Apply Patch 9239095
5-Apply patch 9822544 
6-Apply Patch  9817770 
7-Apply Patch 9966055
After you Applied all the above patch Do the below steps :
On Application Tier :
  • Run Autoconfig ( Which is Located $ADMIN_SCRIPT_HOME )
  • Run the admkappsutil.pl ( Located in $AD_TOP/bin/admkappustil.pl ) Using the below command 

perl /bin/admkappsutil.pl

This will generate new appsutil.zip located in  $INST_TOP/admin/out

On Database Tier :

Copy appsutil from Application tier to $ORACLE_HOME,Unzip in under $ORACLE_HOME Like the below

  • unzip appsutil.zip
  • Run autoconfig for Database Tier located in ( $ORACLE_HOME/appsutil/admin) –> adauto.sh
Finally two more simple step, you need to run adpreclone on Database Tier & application Tier using the below command :
  • perl adpreclone.pl dbTier (located in $ORACLE_HOME/appsutil/admin)
  • perl adpreclone.pl appsTier (Located in $ADMIN_SCRIPT_HOME)
Disable Maintenance mode now. 

Sqlplus apps/***
SQL> select release_name from fnd_product_groups;

Start Application Services using ( adstrtall.sh )

Thank you
Osama mustafa

Which module You installed on Apps

Sometimes you need to check which module you installed in oracle E-business suite and to do that there’s more one way :

using Query / as Apps User :

SELECT a.application_name,a.product_code,
DECODE (b.status, ‘I’, ‘Installed’, ‘S’, ‘Shared’, ‘N/A’) status,
patch_levelFROM apps.fnd_application_vl a,
apps.fnd_product_installations b
WHERE a.application_id = b.application_id
and b.status=’I’ order by product_code asc;

Using adutconf.sql scripts which is located in $AD_TOP

This script include information about Product installation status, version and other information.

Using Oracle Application Manager ( OAM )

Site Map —> License Manager Which licensed word means installed.


Thank you
Osama Mustafa

Oracle APPS DBA vs Oracle DBA

Name Different In One Thing Apps , Both Are DBA for the same corporation but before going further you need to know what the definition for both of them what APPS DBA mean and What Oracle DBA mean ?

Oracle DBA ( Database Administrator ) is the person who responsible for administration , Monitor , tuning and recover database ( Failure cases ) For Oracle Database this Person Called Oracle DBA.

Lot of Jobs and Tasks for this Person

  • Installing and upgrading the Oracle server and application tools
  • Allocating system storage and planning future storage requirements for the database system
  • Creating primary database storage structures (tablespaces) after application developers have designed an application
  • Creating primary objects (tables, views, indexes) once application developers have designed an application
  • Modifying the database structure, as necessary, from information given by application developers
  • Enrolling users and maintaining system security
  • Ensuring compliance with your Oracle license agreement
  • Controlling and monitoring user access to the database
  • Monitoring and optimizing the performance of the database
  • Planning for backup and recovery of database information
  • Maintaining archived data on tape
  • Backing up and restoring the database
  • Contacting Oracle Corporation for technical support
    For More Information Read oracle Documentation Here.
    in he big company that required 24/24hr monitor for more than one database therefore you cannot assign everything to this person he will be killed. So two kind of DBA appeared to reduce Tasks 
    Development DBA   & Production DBA each one of them has it’s own job and Tasks.
    Development DBA: 
    well from the name you can see it’s person who works closely with developers team to make sure database design and performance are working find without any problems.
    Production DBA
    Person who responsible to ensure that Database for this company is healthy and running without any problem it’s not easy job since this type of Database is the main database for company so you have to ensure it’s working fine or you will be fired.
    I mention before of my blog how to become DBA and what you need to do that ? Good understanding of the Oracle database,  understanding of the underlying operating system ,  how Oracle acquires and manages resources, knowledge of both database and system performance tuning,communication skills , management , and ability to handle multiple projects and deadlines all this Hints to become DBA. Read my Post About “how to become DBA” Here 
    After you have small knowledge about DBA tasks and What he is doing ? Let’s talk about APPS DBA.
    Simply the equation :
    ORACLE APPS DBA = Application* + Database.
    *Which Application is Fusion Middle Ware Products from Oracle.
    If you are aware of all the previous tasks  then you are able to be Oracle APPS DBA, Oracle APPS DBA include all Oracle DBA tasks and it’s very huge subject to learn and explorer.
    So the Task for APPS DBA :
    • All Oracle DBA tasks.
    • Patching
    • Cloning 
    • Maintenance Oracle Application.
    • Installations Oracle EBS.

    So as conclusion Oracle APPS DBA is an Oracle DBA because of this he can switch easliy between this job Role, and don’t let this disappointed you also Oracle DBA can switch to APPS DBA but he must read LOT.

    Thank you 
    Osama Mustafa  

    Gather Schema Statistics How to Use it !!!!

    Gather Schema Statistics program generates statistics that quantify the data distribution and storage characteristics of tables, columns, indexes, and partitions.

    As a general rule, run Gather Schema Statistics under the following circumstances:
    1.  After there has been a significant change in data in either content or volume.
    2.  After importing data.
    3.  Any time end-users notice deterioration in performance in routine day-to-day business transactions or when running concurrent programs.

     4.  Run on a regular basis (weekly at a minimum) and anytime after application of patch, conversion, etc.

    Type :

    1. GATHER_INDEX_STATS, Index statistics
    2. GATHER_TABLE_STATS, Table, column, and index statistics
    3. GATHER_SCHEMA_STATS,Statistics for all objects in a schema
    4. GATHER_DICTIONARY_STATS,Statistics for all dictionary objects
    5. GATHER_DATABASE_STATS,Statistics for all objects in a database

    Example :

    (ownname => 'Scott',
    estimate_percent => dbms_stats.auto_sample_size E "sample_size",
    options => 'GATHER EMPTY'

    The above call starts the gather_schema_stats for a schema called SCOTT with estimate_percent at default sample size and options gather empty which gathers statistics on objects which currently have no statistics.

    You can disable automated statistics collection job using the code below :

    EXEC dbms_scheduler.disable(’GATHER_STATS_JOB’);
     To re-enable the job:
    EXEC dbms_scheduler.enable(’GATHER_STATS_JOB’);
     To check if it job running or not 
    Thank you 
    Osama mustafa 

    Speeding up Oracle Applications/Payroll processing

    % sqlplus apps/apps

    To retrieve the current value of ‘THREADS’ parameter:

    SQL> select parameter_value
    from pay_action_parameters
    where parameter_name = 'THREADS';
    To set the value for the number of payroll threads:

    SQL> update pay_action_parameters
    set parameter_value = <number_of_threads>
    where parameter_name = ‘THREADS’;

    SQL> commit;

    if you don't have thread value use the below sql :
    SQL> insert into PAY_ACTION_PARAMETERS values ('THREADS', 'XXXX');

    thank you
    Osama mustafa 

    Steps To Clone Oracle EBS R12

    There’s Two major Parts In This Topics :
    1- Pre Clone Steps .
    2 – Post Clone Steps.


    1- Pre Clone Steps : 

    On Application Tier : 

    1-    Execute Apps environment file
    2-    cd $ADMIN_SCRIPTS_HOME
    3-    ./adautocfg.sh
    4-    Perl adpreclone.pl appsTier

    Database Tier : 

    1-    Execute Database environment ( under ORACLE_HOME)
    2-    cd $ORACLE_HOME/appsutil/scripts/(CONTEXT_NAME)
    3-    ./adautocfg.sh
    4-    perl adpreclone.pl dbTier

    shutdown application and database Copy your virtual nodes on new servers .

    Post clone Steps:

    Database Server

    1-    cd $ORACLE_HOME/appsutil/clone/bin
    2-    perl adcfgclone.pl dbTier

    Application Server:

    1-    cd $COMMON_TOP/clone/bin
    2-    perl adcfgclone.pl appsTier

    $COMMON_TOP: APPLICATION_BASE/apps/apps_st/comn

    Thank you
    Osama mustafa

    Check RMAN Backup Status

    The Below Scripts To Check Backup Status Via Sqlplus , Maybe you will find Same Or Another Scripts In the Same way : 

    Sqlplus / as sysdba

    This script will report on all backups – full, incremental and archivelog backups –

    col STATUS format a9
    col hrs format 999.99
    to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,
    to_char(END_TIME,'mm/dd/yy hh24:mi') end_time,
    elapsed_seconds/3600 hrs
    order by session_key;

    This script will report all on full and incremental backups, not archivelog backups –

    col STATUS format a9
    col hrs format 999.99
    to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,
    to_char(END_TIME,'mm/dd/yy hh24:mi') end_time,
    elapsed_seconds/3600 hrs
    where input_type='DB INCR'
    order by session_key;

    I would Love to Thank gavin soorma – OCM For His Amazing Effort . Share it For Knowledge .

    Thank you
    Osama Mustafa

    All About Oracle WorkFlow Mailer

    In this Topic We will discuss the Workflow mailer In Oracle Apps ad how to check it Let Start :

    Workflow: version


    check workflow status.

    set linesize 120
    set pagesize 50
    column COMPONENT_NAME format a45
    column STARTUP_MODE format a15
    column COMPONENT_STATUS format a15

    select fsc.COMPONENT_NAME,fsc.STARTUP_MODE,fsc.COMPONENT_STATUS,fcq.last_update_date
    from APPS.FND_CONCURRENT_QUEUES_VL fcq, fnd_svc_components fsc
    where fsc.concurrent_queue_id = fcq.concurrent_queue_id(+)

    check if workflow is used by only one instance
    col value format a20
    select p.parameter_id,
    v.parameter_value value
    from apps.fnd_svc_comp_param_vals_v v,
    apps.fnd_svc_comp_params_b p,
    apps.fnd_svc_components c
    where c.component_type = ‘WF_MAILER’
    and v.component_id = c.component_id
    and v.parameter_id = p.parameter_id
    and p.parameter_name in ( ‘INBOUND_SERVER’,’ACCOUNT’, ‘REPLYTO’)

    order by p.parameter_name;

    check for bad e-mail address

    set linesize 170
    col name format a40
    col email_address format a80
    select name, email_address from apps.wf_local_roles where email_address like ‘% %’;

    select name, email_address from apps.wf_local_roles where email_address like ‘%%’;

     “If this retuning Row The there’s bad Email”

    How to know mail sent to a user with details:

    select name, display_name, notification_preference, email_address
     from wf_local_roles where name = ”;

    To see failed, open notifications

    SELECT message_type, COUNT(1)
    FROM apps.wf_notifications
    WHERE 1 = 1 AND mail_status = ‘FAILED’ AND status = ‘OPEN’
    GROUP BY message_type;

    To check if email address, notification preference, display_name

    select DISPLAY_NAME, NOTIFICATION_PREFERENCE from wf_users where EMAIL_ADDRESS = ‘HR@domainname.com‘ and STATUS = ‘ACTIVE’ and DISPLAY_NAME = ‘xxxxx’;
    select distinct(count(*)) from wf_users where NOTIFICATION_PREFERENCE=’MAILTEXT’ and STATUS = ‘ACTIVE’ and EMAIL_ADDRESS = ‘HR@Domainname.com‘;

    Thank you
    Osama mustafa

    500 Internal Server Error / Oracle Application Server

    There’s more than One Reason For this i will provide you with two solutions (Meta-link Note):

    Solution One : 

    1. Backup and edit the forms configuration file (Default= formsweb.cfg)
    2. Modify the HTMLdelimiter to have only one character as a value 


    3. Save and exit the file.

    Solution Two :

    Edit oracle_apache.conf and comment out the include related to the forms.conf  as the following :

    # Oracle Forms Developer
    #include “/oas/product/oas2/forms/server/forms.conf”

    Thank You
    osama mustafa