Clean Concurrent Manager Tables

The Below Script "CMCLEAN.SQL For Cleaning ConCurrent Manager In Oracle EBS VERSION :  -10.7
-11.0
-11.5
-12.1.3
REM
REM FILENAME
REM cmclean.sql
REM DESCRIPTION
REM Clean out the concurrent manager tables
REM NOTES
REM Usage: sqlplus @cmclean
REM
REM
REM $Id: cmclean.sql,v 1.4 2001/04/07 15:55:07 pferguso Exp $
REM
REM
REM +======================================================================+

set verify off;
set head off;
set timing off
set pagesize 1000

column manager format a20 heading 'Manager short name'
column pid heading 'Process id'
column pscode format a12 heading 'Status code'
column ccode format a12 heading 'Control code'
column request heading 'Request ID'
column pcode format a6 heading 'Phase'
column scode format a6 heading 'Status'

WHENEVER SQLERROR EXIT ROLLBACK;

DOCUMENT

WARNING : Do not run this script without explicit instructions
from Oracle Support

*** Make sure that the managers are shut down ***
*** before running this script ***

*** If the concurrent managers are NOT shut down, ***
*** exit this script now !! ***

#

accept answer prompt 'If you wish to continue type the word ''dual'': '

set feed off
select null from &answer;
set feed on

REM Update process status codes to TERMINATED

prompt
prompt ------------------------------------------------------------------------

prompt -- Updating invalid process status codes in FND_CONCURRENT_PROCESSES
set feedback off
set head on
break on manager

SELECT concurrent_queue_name manager,
concurrent_process_id pid,
process_status_code pscode
FROM fnd_concurrent_queues fcq, fnd_concurrent_processes fcp
WHERE process_status_code not in ('K', 'S')
AND fcq.concurrent_queue_id = fcp.concurrent_queue_id
AND fcq.application_id = fcp.queue_application_id;

set head off
set feedback on
UPDATE fnd_concurrent_processes
SET process_status_code = 'K'
WHERE process_status_code not in ('K', 'S');

REM Set all managers to 0 processes

prompt
prompt ------------------------------------------------------------------------

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

UPDATE fnd_concurrent_queues
SET running_processes = 0, max_processes = 0;

REM Reset control codes

prompt
prompt ------------------------------------------------------------------------

prompt -- Updating invalid control_codes in FND_CONCURRENT_QUEUES
set feedback off
set head on
SELECT concurrent_queue_name manager,
control_code ccode
FROM fnd_concurrent_queues
WHERE control_code not in ('E', 'R', 'X')
AND control_code IS NOT NULL;

set feedback on
set head off
UPDATE fnd_concurrent_queues
SET control_code = NULL
WHERE control_code not in ('E', 'R', 'X')
AND control_code IS NOT NULL;

REM Also null out target_node for all managers
UPDATE fnd_concurrent_queues
SET target_node = null;

REM Set all 'Terminating' requests to Completed/Error
REM Also set Running requests to completed, since the managers are down

prompt
prompt ------------------------------------------------------------------------

prompt -- Updating any Running or Terminating requests to Completed/Error
set feedback off
set head on
SELECT request_id request,
phase_code pcode,
status_code scode
FROM fnd_concurrent_requests
WHERE status_code = 'T' OR phase_code = 'R'
ORDER BY request_id;

set feedback on
set head off
UPDATE fnd_concurrent_requests
SET phase_code = 'C', status_code = 'E'
WHERE status_code ='T' OR phase_code = 'R';

REM Set all Runalone flags to 'N'
REM This has to be done differently for Release 10

prompt
prompt ------------------------------------------------------------------------

prompt -- Updating any Runalone flags to 'N'
prompt
set serveroutput on
set feedback off
declare
c pls_integer := dbms_sql.open_cursor;
upd_rows pls_integer;
vers varchar2(50);
tbl varchar2(50);
col varchar2(50);
statement varchar2(255);
begin

select substr(release_name, 1, 2)
into vers
from fnd_product_groups;

if vers >= 11 then
tbl := 'fnd_conflicts_domain';
col := 'runalone_flag';
else
tbl := 'fnd_concurrent_conflict_sets';
col := 'run_alone_flag';
end if;

statement := 'update ' || tbl || ' set ' || col || '=''N'' where ' || col || ' = ''Y''';
dbms_sql.parse(c, statement, dbms_sql.native);
upd_rows := dbms_sql.execute(c);
dbms_sql.close_cursor(c);
dbms_output.put_line('Updated ' || upd_rows || ' rows of ' || col || ' in ' || tbl || ' to ''N''');
end;
/

prompt

prompt ------------------------------------------------------------------------

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

prompt

set feedback on

REM <= Last REM statment -----------------------------------------------------

Disclaimer
EXCEPT WHERE EXPRESSLY PROVIDED OTHERWISE, THE INFORMATION, SOFTWARE,
PROVIDED ON AN "AS IS" AND "AS AVAILABLE" BASIS. ORACLE EXPRESSLY DISCLAIMS
ALL WARRANTIES OF ANY KIND, WHETHER EXPRESS OR IMPLIED, INCLUDING, BUT NOT
LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR
PURPOSE AND NON-INFRINGEMENT. ORACLE MAKES NO WARRANTY THAT: (A) THE RESULTS
THAT MAY BE OBTAINED FROM THE USE OF THE SOFTWARE WILL BE ACCURATE OR
RELIABLE; OR (B) THE INFORMATION, OR OTHER MATERIAL OBTAINED WILL MEET YOUR
EXPECTATIONS. ANY CONTENT, MATERIALS, INFORMATION OR SOFTWARE DOWNLOADED OR
OTHERWISE OBTAINED IS DONE AT YOUR OWN DISCRETION AND RISK. ORACLE SHALL HAVE
NO RESPONSIBILITY FOR ANY DAMAGE TO YOUR COMPUTER SYSTEM OR LOSS OF DATA THAT
RESULTS FROM THE DOWNLOAD OF ANY CONTENT, MATERIALS, INFORMATION OR SOFTWARE.

ORACLE RESERVES THE RIGHT TO MAKE CHANGES OR UPDATES TO THE SOFTWARE AT ANY
TIME WITHOUT NOTICE.
Limitation of Liability
IN NO EVENT SHALL ORACLE BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
SPECIAL OR CONSEQUENTIAL DAMAGES, OR DAMAGES FOR LOSS OF PROFITS, REVENUE,
DATA OR USE, INCURRED BY YOU OR ANY THIRD PARTY, WHETHER IN AN ACTION IN
CONTRACT OR TORT, ARISING FROM YOUR ACCESS TO, OR USE OF, THE SOFTWARE.

SOME JURISDICTIONS DO NOT ALLOW THE LIMITATION OR EXCLUSION OF LIABILITY.
ACCORDINGLY, SOME OF THE ABOVE LIMITATIONS MAY NOT APPLY TO YOU.

Oracle Document ID : [ID 134007.1]
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.

LETS START :

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

Notes:
$COMMON_TOP: APPLICATION_BASE/apps/apps_st/comn
CONTEXT_NAME: SERVICE_NAME_HOSTNAME

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

  $FND_TOP/sql/wfver.sql

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(+)
order by COMPONENT_STATUS , STARTUP_MODE , COMPONENT_NAME;

check if workflow is used by only one instance
col value format a20
select p.parameter_id,
p.parameter_name,
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 DISPLAY_NAME, NOTIFICATION_PREFERENCE, EMAIL_ADDRESS from wf_users where NOTIFICATION_PREFERENCE=’MAILTEXT’ and STATUS = ‘ACTIVE’;
select distinct(count(*)) from wf_users where NOTIFICATION_PREFERENCE=’MAILTEXT’ and STATUS = ‘ACTIVE’ and EMAIL_ADDRESS = ‘HR@Domainname.com‘;

Thank you
Osama mustafa

What Is $ADMIN_SCRIPT_HOME ???

As We All Know if you need to start/shutdown Oracle Apps you need go $ADMIN_SCRIPT_HOME In R12 Or $CONTEXT_HOME in R11

This Article Explain What These Scripts Do : 

adstrtal.sh

Master script to start all components/services of middle tier or application tier. This script will use Service Control API to start all services which are enabled after checking them in context file (SID_HOSTNAME.xml or CONTEXT_NAME.xml)

adstpall.sh

Master script to stop all components/services of middle tier or application tier


adalnctl.sh

Script to start / stop apps listener (FNDFS and FNDFS). This listener will file will be in 10.1.2 ORACLE_HOME (i.e. Forms & Reports Home)
listener.ora file will be in $INST_TOP/apps/$CONTEXT_NAME/ora/10.1.2/network/admin directory
(Mostly similar to one in 11i with only change in ORACLE_HOME i.e. from 8.0.6 to 10.1.2 )

adapcctl.sh

Script to start/stop Web Server or Oracle HTTP Server. This script uses opmn (Oracle Process Manager and Notification Server) with syntax similar to opmnctl [startstop]proc ohslike opmnctl stopproc ohs .

adcmctl.sh

Script to start / stop concurrent manager,

adformsctl.sh

Script to start / stop Forms OC4J from 10.1.3 Oracle_Home. This script will also use opmnctl to start/stop Forms OC4J like
opmnctl stopproc type=oc4j instancename=forms

adformsrvctl.sh

This script is used only if you wish to start forms in socket mode. Default forms connect method in R12 is servlet.
If started this will start frmsrv executable from 10.1.2 Oracle_Home in Apps R12

adoacorectl.sh

This script will start/stop oacore OC4J in 10.1.3 Oracle_Home. This scripts will also use opmnctl (similar to adapcctl & adformsctl) to start oacore instance of OC4J like
opmnctl startproc type=oc4j instancename=oacore

adoafmctl.sh

 This script will start/stop oafm OC4J in 10.1.3 Oracle_Home. This scripts will also use opmnctl (similar to above) to start oacore instance of OC4J like
opmnctl startproc type=oc4j instancename=oafm

adopmnctl.sh

This script will start/stop opmn service in 10.1.3 Oracle_Home. opmn will control all services in 10.1.3 Oracle_Home like web server or various oc4j instances. If any services are stopped abnormally opmn will/should start them automatically.

Thank You 
Osama Mustafa 
     

ocfs2console: o2cb_ctl: Unable to access cluster service while creating node

For the first time, during configuring the nodes, ocfs2console fails with message:
“o2cb_ctl: Unable to access cluster service while creating node”
TODO:
– Quit ocfs2console
– Stop the service
– Remove file /etc/ocfs2/cluster.conf
– Restart ocfs2console
– Configure the nodes again

[root@rac1 ~]#  /etc/init.d/ocfs2 stop
Stopping Oracle Cluster File System (OCFS2) [ OK ]

[root@rac1 ~]# /etc/init.d/o2cb offline ocfs2

[root@rac1 ~]# /etc/init.d/o2cb unload
Unmounting ocfs2_dlmfs filesystem: OK
Unloading module "ocfs2_dlmfs": OK
Unmounting configfs filesystem: OK
Unloading module "configfs": OK

[root@rac1 ~]# rm -f /etc/ocfs2/cluster.conf
[root@rac1 ~]# /usr/sbin/ocfs2console &

compile invalid objects in an APPS (EBS)

You need to know Count of Invalid Object for your APPS :

SELECT COUNT(*)
FROM DBA_OBJECTS
WHERE STATUS = 'INVALID';

For a more detailed query, use the following script :

SELECT OWNER, OBJECT_TYPE, COUNT(*)
FROM DBA_OBJECTS
WHERE STATUS = 'INVALID'
GROUP BY OWNER, OBJECT_TYPE;

To recompile an individual object, connect to SQL*PLUS as the owner of the object (generally apps) and use one of the following depending on the object type :

alter package compile; (package specification)
alter package compile body; (package body)
alter view compile; (view)

If the object compiles with warnings, use either of the following to see the errors that caused the warnings :

show errors

OR

select * from user_errors where name = '';

Another way to correct invalid objects is to run the adadmin utility as follows:

1. Log in as APPS User : /

2. Start the adadmin-Utility from the Unix prompt with this command :

adadmin

The utility will then ask you a series of questions.

3. Under the Maintain Applications Database Objects Menu, select Compile APPS schema(s)

Other Way :
 
Also try running $ORACLE_HOME/rdbms/admin/utlrp.sql ( as sysdba )

Within Applications, there is a script to compile INVALID objects – called ADCOMPSC.pls

 Arguments for ADCOMPSC.pls :

1 – Schema to run in
2 – Password for schema
3 – Check errors for objects starting with #3

NOTE: The order in which to compile Invalid Objects in schemas is SYS, SYSTEM, APPS and then all others. APPS_DDL and APPS_ARRAY_DDL should exist in all schema’s. In case of an ORA-1555 error while running adcompsc.pls, restart the script.

The script can be run as followed :

cd $AD_TOP/sql
sqlplus @adcompsc.pls SCHEMA_NAME SCHEMA_PASSWORD %

Example : SQL> @adcompsc.pls apps apps %

After the script completes, check for invalid objects again. If the number has decreased, but invalid objects still exist, run adcompsc.pls again. Keep running adcompsc.pls until number of invalid objects stops decreasing.

If there are any objects still left INVALID, verify them by using the script ‘aderrchk.sql’ to record the remaining INVALID objects. ‘Aderrchk.sql’ uses the same syntax as ‘adcompsc.pls’. This script is also supplied with the Applications. Send the aderrchk.sql to a file using the spool command in sqlplus.

e.g. sqlplus x/y @aderrchk.sql SCHEMA_NAME SCHEMA_PASSWORD %

For objects which will not compile, try the following :

select text
from user_source
where name = 'OBJECTNAME'
and text like '%Header%';

This script will provide the script that creates the packages/recreates the packages.

SQL>@packageheader
SQL>@packagebody

If recreating the package does not make the package valid, analyze the user_errors table to determine the cause of the invalid package :

select text
from user_errors
where name = '';

Thank You
Osama Mustafa

Upgrade 11.1.0.6 to 11.1.0.7 – APPS DBA

On each application tier server node,
shut down all server processes or services.

On the database tier server node,
shut down the database listener in the old Oracle home.

On the database server node,
as the owner of the Oracle 11g file system and database instance, unzip and extract the 11.1.0.7 patch set file for your platform.

Use the runInstaller in the patchset to start the OUI.

Once the OUi starts from the LOV s in the Oracle_home list choose the appropriate one to be upgraded and the location also.

The present oracle_home and its location will appear if the environment is set correctly.

Proceed with the next steps on the OUI screen.

On next Screen it will Say Configuration Assistant has failed.Skip this Step since its not required for Oracle E-Business Suite .

MOST IMPORTANT STEP IN UPGRADE .
After Installation you need to Apply the RDBMS patches7486407 and 7684818

Copy $APPL_TOP/admin/adstats.sql from the administration server node to the database server node.

$ sqlplus / as sysdba
SQL> shutdown normal;
SQL> startup restrict;
SQL> @adstats.sql
SQL> shutdown normal;
SQL> startup;
SQL> exit;

Thank You
Osama mustafa