Saturday, May 30, 2015

Difference between Oracle EBS R12.1.3 and Oracle EBS R12.2.4

Hi,

It's been a long time since I wrote a blog post, I was busy working with Oracle EBS upgrades,content server setup, and Oracle SOA 11g upgrade projects. We have recently upgraded Oracle EBS 12.1.3 to Oracle EBS R12.2.4.

There are lot of difference.Below are the key ones, I will try to extend this post based on difference noticed by me as I work more rigorously.

1)In R12.2 we have to kind of filesystems fs1(run filesystem) and fs2(patch filesystem) where as In R12.1.3 we only deal with one application filesystem.

2)In R12.2 we have the Application servers replaced by Weblogic server to manage the technology statck.
The 10.1.3 Home is replaced by FMW  (Fusion Middleware Home) i.e $FMW_HOME

The major change in R12.2 is involvement of Weblogic server to manager all the forms,oacore servers
where as in R12.1.3 we had the Application server 10.1.3 to manage the web home or Java Home

3)The  adpatch(patching) in R 12.1.3 is replaced by adop(online patching) in R 12.2.4

adop(online patching) utility involves 5 phases to apply a standard patch in Oracle EBS R12.2.
adop involves 5 phases
1)prepare => prepare phase involves synchronization of the filesystems fs1 (run ,filesystem) and fs2(patch filesystem), filesystems are inter changeable.
2)apply=>In this phase we apply all the patches
Note: These patches need to be copied in fs_ne (non editioned filesystems)
3)finalize =>In this phase we are getting ready for cutover phase
4)cutover =>In cutover phase the filesystem switchover takes place. Previously the filesystem which was patch filesystem will now become run filesystem.)
5) cleanup =>In cleanup phase all the obsolute objects gets compiled .

adop online patching utility doesn't require downtime. It involves minimal downtime during cutover phase where switching of filesystems happens. We can apply lot of patches and do cutover any time to minimize downtime

where as in  adpatch we just apply patch most of the times by bringing down applications or in hot patch mode.

4)'Apps' user Password change:
The 'apps' user password change involve 3 steps in R12.2.4
1)Change the password with FNDCPASS
2)Change the password in EBS Datasource from Weblogic admin console
3)Run AutoconfigCheck below link from Bala for detail steps:

http://balaoracledba.com/2014/05/15/r12-2-changing-apps-or-applsys-password-on-r12-2-instance/

Where as in R 12.1.3 It only involves 2 steps:
1)Change the password with FNDCPASS
2)Run Autoconfig
5)Log files locations:
All the oacore,forms log files are placed in $EBS_DOMAIN_HOME in R12.2. I will explain this in details in my upcoming posts.
Where as in R12.1.3 all the logs for oacore,forms are under $LOG_HOME/ora/10.1.3

I will come up with lot of difference in this thread so that we all gets comfortable with Oracle EBS Release 12.2.4

Happy Oracle Apps DBA reading and learning EBS R12.2.4...



Monday, September 1, 2014

Compressing the Oracle EBS TOPs and Uncompressing using tar and gzip

 Hi Apps DBA,

We usually do cloning by copying the Application tops from Source(TEST1) to Target(TEST2).
First we compress the files in source and than we copy those file from source to target and finally we uncompress followed by renaming and further process .Below are the useful commands and safe one to do this task.

Step 1:Tarring and zipping:

nohup tar -cvpf - ./test1appl 2> /u01/app/test1/test1appl.log | gzip -c -> /u01/app/test1/test1appl.gz &

nohup tar -cvpf - ./test1comn 2> /u01/app/test1/test1comn.log | gzip -c -> /u01/app/test1/test1comn.gz &

nohup tar -cvpf - ./test1ora 2> /u01/app/test1/test1ora.log | gzip -c -> /u01/app/test1/test1ora.gz &

Step 2:scp from source  to target:
Assuming we are on target server,we copy files from source to target as below:

scp -rp apptest1@uslaxorcap19:/u01/app/test1/*.tar.gz .

passwd:apptest1

Step 3:Untarring and Unzipping:

nohup cat /u02/app/test2/test1appl.tar.gz|gunzip| tar xvf - >/u02/app/test2/test1appl.log &
nohup cat /u02/app/test2/test1comn.tar.gz|gunzip| tar xvf - >/u02/app/test2/test1comn.log &
nohup cat /u02/app/test2/test1ora.tar.gz|gunzip| tar xvf - >/u02/app/test2/test1ora_27Jun.log &

Note 1:Below command comes in single line in Unix shell prompt
Note 2:Advantage of above command is we can check logs and make sure the compressing and Uncompressing  is done successfully.



Enjoy doing Apps DBA tasks...


Best regards,

Rafi








Tuesday, August 19, 2014

Health check script for Oracle EBS Database and Application 11i/R12

Hi Apps DBA,

Lot of times we need to check  the Health of Oracle EBS Database and Application,The below script
can be very handy for it.

SQL>conn apps/***
SQL>spool health_check_apps_db.out

set pages 1000
set linesize 135
col PROPERTY_NAME for a25
col PROPERTY_VALUE for a15
col DESCRIPTION for a35
col DIRECTORY_PATH for a70
col directory_name for a25
col OWNER for a10
col DB_LINK for a40
col HOST for a20
col "User_Concurrent_Queue_Name" format a50 heading 'Manager'
col "Running_Processes" for 9999 heading 'Running'
set head off
set feedback off
set echo off

break on utl_file_dir

select '--------------------------------------------------------------------------------' from dual;
select '-----------------------     Database Checks    ---------------------------------' from dual;
select '--------------------------------------------------------------------------------' from dual;
Prompt
select '************************ Getting Database Information  *************' from dual ;

select 'Database Name..................... : '||name from v$database;
select 'Database Status................... : '||open_mode from v$database;
select 'Archiving Status.................. : '||log_mode  from v$database;
select 'Global Name....................... : '||global_name from global_name;
select 'Creation Date..................... : '||to_char(created,'DD-MON-YYYY HH24:MI:SS') from v$database;
select 'Checking For Missing File......... : '||count(*) from v$recover_file;
select 'Checking Missing File Name ....... : '||count(*) from v$datafile where name like '%MISS%';
select 'Total SGA ........................ : '||round(sum(value)/(1024*1024))||' MB' from v$sga ;
select 'Database Version.................. : '||version from v$instance;
select 'Temporary Tablespace.............. : '||property_value from database_properties
                                                where property_name like 'default_temp_tablespace';
select 'Apps Temp Tablespace.............. : '||temporary_tablespace from dba_users where username like '%APPS%';
select 'Temp Tablespace size.............. : '||sum(maxbytes/1024/1024/1024)||' GB' from dba_temp_files group by tablespace_name;
select 'No of Invalid Object ............. : '||count(*) from dba_objects where status = 'INVALID' ;
select 'service Name...................... : '||value from v$parameter2 where name='service_names';
select 'plsql code type................... : '||value from v$parameter2 where name='plsql_code_type';
select 'plsql subdir count................ : '||value from v$parameter2 where name='plsql_native_library_subdir_count';
select 'plsql native library dir.......... : '||value from v$parameter2 where name='plsql_native_library_dir';
select 'Shared Pool Size.........,........ : '||(value/1024/1024) ||' MB' from v$parameter where name='shared_pool_size';
select 'Log Buffer........................ : '||(value/1024/1024) ||' MB' from v$parameter where name='log_buffer';
select 'Buffer Cache...................... : '||(value/1024/1024) ||' MB' from v$parameter where name='db_cache_size';
select 'Large Pool Size................... : '||(value/1024/1024) ||' MB' from v$parameter where name='large_pool_size';
select 'Java Pool Size.................... : '||(value/1024/1024) ||' MB' from v$parameter where name='java_pool_size';
select 'utl_file_dir...................... : '||value from v$parameter2 where name='utl_file_dir';
select directory_name||'.................... : '||directory_path from all_directories where rownum  < 15 ;

select '************************ Getting Apps Information *****************' from dual ;

select 'Home URL.......................... : '||home_url from apps.icx_parameters ;
select 'Session Cookie.................... : '||session_cookie from apps.icx_parameters ;
select 'Applicaiton Database ID........... : '||fnd_profile.value('apps_database_id') from dual;
select 'GSM Enabled....................... : '||fnd_profile.value('conc_gsm_enabled') from dual;
select 'Maintainance Mode................. : '||fnd_profile.value('apps_maintenance_mode') from dual;
select 'Site Name......................... : '||fnd_profile.value('Sitename')from dual;
select 'Bug Number........................ : '||bug_number from ad_bugs where bug_number='2728236';

select '************************ Doing Workflow Checks ********************' from dual ;

select 'No Open Notifications............. : '||count(*) from wf_notifications where mail_status in('MAIL','INVALID','OPEN');
select 'Name(wf_systems).................. : '||name from wf_systems;
select 'Display Name(wf_systems).......... : '||display_name from wf_systems;
select 'Address........................... : '||address from wf_agents;
select 'Workflow Mailer Status............ : '||component_status from applsys.fnd_svc_components
                                                where component_name like 'Workflow Notification Mailer';
select 'Test Address...................... : '||b.parameter_value
                                                from fnd_svc_comp_param_vals_v a, fnd_svc_comp_param_vals b
                                                where a.parameter_id=b.parameter_id
                                                and a.parameter_name in ('TEST_ADDRESS');
select 'From Address...................... : '||b.parameter_value
                                                from fnd_svc_comp_param_vals_v a, fnd_svc_comp_param_vals b
                                                where a.parameter_id=b.parameter_id
                                                and a.parameter_name in ('FROM');
select 'WF Admin Role..................... : '||text from wf_resources where name = 'WF_ADMIN_ROLE' and  rownum =1;


Prompt
Prompt Getting Apps Node Info
Prompt ************************
select Node_Name,'........................ : '||server_id from fnd_nodes;
select server_type||'......................: '||name from fnd_app_servers, fnd_nodes
                                                where fnd_app_servers.node_id =fnd_nodes.node_id;

select '************************ Doing Conc Mgr Checks  ********************' from dual ;

Prompt Getting Con Mgr Status
Prompt ************************
Prompt
Prompt Manager Name                                                 Hostname          No of Proc Running
Prompt ~~~~~~~~~~~~                                                 ~~~~~~~~          ~~~~~~~~~~~~~~~~~~
set lines 145
Column Target_Node   Format A12
select User_Concurrent_Queue_Name,'....... : '||Target_Node||' ...... : '||Running_Processes
                                                from fnd_concurrent_queues_vl
                                                where Running_Processes = Max_Processes
                                                and Running_Processes > 0;

Prompt
Prompt Getting Pending Request
Prompt ***********************
--select user_concurrent_program_name||'........ : '||request_id
--                                                  from fnd_concurrent_requests r, fnd_concurrent_programs_vl p, fnd_lookups s, fnd_lookups ph
--                                                  where r.concurrent_program_id = p.concurrent_program_id
--                                                 and r.phase_code = ph.lookup_code
--                                                and ph.lookup_type = 'CP_PHASE_CODE'
--                                               and r.status_code = s.lookup_code
--                                                  and s.lookup_type = 'CP_STATUS_CODE'
--                                                  and ph.meaning ='Pending'
--                                                  and rownum < 10
--                                                  order by to_date(actual_start_date, 'dd-MON-yy hh24:mi');
--

Prompt
Prompt Getting Workflow Components Status
Prompt **********************************

set pagesize 1000
set linesize 125
col COMPONENT_STATUS for a20
col COMPONENT_NAME for a45
col STARTUP_MODE for a12

select fsc.COMPONENT_NAME,
fsc.STARTUP_MODE,
fsc.COMPONENT_STATUS,
fcq.MAX_PROCESSES TARGET,
fcq.RUNNING_PROCESSES ACTUAL
from APPS.FND_CONCURRENT_QUEUES_VL fcq, APPS.FND_CP_SERVICES fcs,
APPS.FND_CONCURRENT_PROCESSES fcp, fnd_svc_components fsc
where fcq.MANAGER_TYPE = fcs.SERVICE_ID
and fcs.SERVICE_HANDLE = 'FNDCPGSC'
and fsc.concurrent_queue_id = fcq.concurrent_queue_id(+)
and fcq.concurrent_queue_id = fcp.concurrent_queue_id(+)
and fcq.application_id = fcp.queue_application_id(+)
and fcp.process_status_code(+) = 'A'
order by fcp.OS_PROCESS_ID, fsc.STARTUP_MODE;

select '--------------------------------------------------------------------------------' from dual;
select '-----------------------     End Of Database Checks  ----------------------------' from dual;
select '--------------------------------------------------------------------------------' from dual;

SQL>spool off


Enjoy Oracle Apps DBA tasks..



Thanks,

Rafi








Thursday, July 24, 2014

crsctl command to start and stop crs in Oracle RAC Database


Hi DBAs,

We basically have the below command to stop/start crs in Oracle RAC Database.

crsctl command to stop and start the cluster on a specific node noderac1 & noderac2:


We need to login with 'root' or specific sudo user having permissions to run the crs commands.

/u01/app/11.2.0.3/grid/bin/crsctl stop crs
/u01/app/11.2.0.3/grid/bin/crsctl disable crs

/u01/app/11.2.0.3/grid/bin/crsctl enable crs
/u01/app/11.2.0.3/grid/bin/crsctl start crs

where /u01/app/11.2.0.3/grid =$GRID_HOME
 
crsctl command to stop and start on the Clusterware(noderac1 &noderac2) will be

/u01/app/11.2.0.3/grid/bin/crsctl stop cluster -all
 /u01/app/11.2.0.3/grid/bin/crsctl disable crs

/u01/app/11.2.0.3/grid/bin/crsctl enable crs
/u01/app/11.2.0.3/grid/bin/crsctl start resource -all

where /u01/app/11.2.0.3/grid =$GRID_HOME


Enjoy doing RAC tasks...

Thanks,

Rafi

How to check Pre-requistics Patches while applying Database Patch using opatch

Hi DBAs,

Lot of time we apply Database patches using the opatch,It is mandatory to know the Pre-requistics patches for applying the Patch.We can find it out with the below steps.


Step 1:Go to the Directory where patch is copied(PATCH TOP)
>cd /home/oracle/patch/18308717

Step 2:Execute below command
>$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph ./
Oracle Interim Patch Installer version 11.2.0.3.6
Copyright (c) 2013, Oracle Corporation.  All rights reserved.

PREREQ session

Oracle Home       : /u01/app/oracle/product/11.2.0.3/dbhome_1
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/11.2.0.3/dbhome_1/oraInst.loc
OPatch version    : 11.2.0.3.6
OUI version       : 11.2.0.3.0
Log file location : /u01/app/oracle/product/11.2.0.3/dbhome_1/cfgtoollogs/opatch/opatch2014-07-24_17-52-50PM_1.log

Invoking prereq "checkconflictagainstohwithdetail"

Prereq "checkConflictAgainstOHWithDetail" passed.

OPatch succeeded.
node1(TESTDB1)  /home/oracle/patch/18308717
>ls -altr
total 24
-rw-rw-r-- 1 oracle oinstall 5905 May 14 03:35 README.txt
drwxr-xr-x 3 oracle oinstall 4096 May 14 03:35 files
drwxr-xr-x 4 oracle oinstall 4096 May 14 03:35 etc
drwxrw-rw- 3 oracle oinstall 4096 Jul 24 11:30 ..
drwxrwxr-x 4 oracle oinstall 4096 Jul 24 11:30 .
node1(TESTDB1)  /home/oracle/patch/18308717


Step 3:Verify the Log

>vi /u01/app/oracle/product/11.2.0.3/dbhome_1/cfgtoollogs/opatch/opatch2014-07-24_17-52-50PM_1.log

[Jul 24, 2014 5:52:50 PM]    PREREQ session
[Jul 24, 2014 5:52:50 PM]    OPatch invoked as follows: 'prereq CheckConflictAgainstOHWithDetail -ph ./ -invPtrLoc /u01/app/oracle/product/11.2.0.3/dbhome_1/oraInst.loc '
[Jul 24, 2014 5:52:50 PM]    OUI-67077:
                             Oracle Home       : /u01/app/oracle/product/11.2.0.3/dbhome_1
                             Central Inventory : /u01/app/oraInventory
                                from           : /u01/app/oracle/product/11.2.0.3/dbhome_1/oraInst.loc
                             OPatch version    : 11.2.0.3.6
                             OUI version       : 11.2.0.3.0
                             OUI location      : /u01/app/oracle/product/11.2.0.3/dbhome_1/oui
                             Log file location : /u01/app/oracle/product/11.2.0.3/dbhome_1/cfgtoollogs/opatch/opatch2014-07-24_17-52-50PM_1.log
[Jul 24, 2014 5:52:50 PM]    Patch history file: /u01/app/oracle/product/11.2.0.3/dbhome_1/cfgtoollogs/opatch/opatch_history.txt
[Jul 24, 2014 5:52:50 PM]    Invoking prereq "checkconflictagainstohwithdetail"
[Jul 24, 2014 5:52:51 PM]    Patch 18308717 is not subset of any other patch processed till now
[Jul 24, 2014 5:52:51 PM]    Patch ID  18308717
[Jul 24, 2014 5:52:51 PM]    Patches that conflict: [  ]
[Jul 24, 2014 5:52:51 PM]    Checking conflicts for patch: 18308717
[Jul 24, 2014 5:52:51 PM]    Checking conflicts/supersets for patch: 18308717 with patch:18308717
[Jul 24, 2014 5:52:51 PM]    Checking conflicts/supersets for patch: 18308717 with patch:17076717
[Jul 24, 2014 5:52:51 PM]    Checking conflicts/supersets for patch: 18308717 with patch:17296419
[Jul 24, 2014 5:52:51 PM]    Checking conflicts/supersets for patch: 18308717 with patch:16475860
[Jul 24, 2014 5:52:51 PM]    Prereq "checkConflictAgainstOHWithDetail" passed.
[Jul 24, 2014 5:52:51 PM]    Finishing PrereqSession at Thu Jul 24 17:52:51 IST 2014 

Message:Patch 18308717 is not subset of any other patch processed till now 
From the above logs we can confirm that there are no-prerequistics patches to be applied before applying 18308717.


Enjoy doing DBA tasks...

Happy DBA tasks..

Thanks,

Rafi











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