Mani's Tech Blog: August 2012

Step by Step Upgrading Oracle 10g to Oracle 11g

Pre-Requisite:

You should have the Oracle database 10g, which you want to migerate.
Also here we are upgrading to Oracle Database 11g – Beta 6 (11.1.0.6)

Step 1) Installing Oracle 11g Home

We cannot upgrade the existing Oracle Home, since 11g is not a patchset. We have to install 11g oracle home as a seperate ORACLE_HOME in parallel to 10g Oracle Home.

Example my 10g Oracle Home is : /u01/app/oracle/oracle/product/10.2.0

then my 11g Oracel Home is : /u01/app/oracle/oracle/product/11.1.0

Just a parallel 11.1.0 directory can be created and we can install oracle home in this location.

Start the installation using the below command

./runInstaller -invPtrLoc /u01/app/oracle/oracle/product/11.1.0/oraInst

Screen 1 – Select Product Install
select “Oracle Database 11g”

Screen 2 – Select Installation Method
Choose “Advanced Installation”

Screen 3 – Specify Inventory directory and creditials
Note: We are providing local inventory here inside the corresponding ORACLE_HOME location.

Screen 4 – Select Installation Type
Choose “Enterprise Edition”

Screen 5 – Installation Location
Oracle Base as parent directory of ORACLE HOME

Screen 6 – Product Specific Pre-requisite Checks
It may gives below warning, we can ignore and proceed further

Screen 7 – Upgrade an Existsing Database
Choose “No”

Screen 8 – Select Configuration Option
Choose “Install Software Only”

Screen 9 – Privileged system groups

Based on the group of oracle user, this value has to be set.

Screen 10 – Summary
Click on “Install”

At the end of installation, installer will ask to run root.sh script. Do not press OK button.
Run root.sh as a root user and once done, press OK button. This will complete the software installation for Oracle Database 11g.

Step 2) Pre-Upgrade Utility

In 11g Home you installed, go to $ORACLE_HOME/rdbms/admin and copy the file utlu111i.sql to some temp location.

[oracle]$ cd $ORACLE_HOME
[oracle]$ cd rdbms/admin/
[oracle]$ pwd
/u01/app/oracle/oracle/product/product/11.1.0/db_1/rdbms/admin
[oracle]$ cp utlu111i.sql /tmp

The utility will give the output in the form of recommendations to be implemented before starting the upgrade. Unless these requirements are met, the upgrade will fail.

Most of the time issue use to come up with time zone….

Then login to the 10g oracle database and run the above sql you copied.

Oracle Database 11.1 Pre-Upgrade Information Tool 23-02-2011 01:34:07
.
**********************************************************************
Database:
**********************************************************************
–> name: ORCL
–> version: 10.2.0.1.0
–> compatible: 10.2.0.1.0
–> blocksize: 8192
–> platform: Linux IA (32-bit)
–> timezone file: V2
.
**********************************************************************
Tablespaces: [make adjustments in the current environment]
**********************************************************************
–> SYSTEM tablespace is adequate for the upgrade.
.
.
.
.
WARNING: –> Database contains schemas with objects dependent on network
packages.
…. Refer to the 11g Upgrade Guide for instructions to configure Network ACLs.
…. USER SYSMAN has dependent objects.
WARNING: –> EM Database Control Repository exists in the database.
…. Direct downgrade of EM Database Control is not supported. Refer to the
…. 11g Upgrade Guide for instructions to save the EM data prior to upgrade.
.

PL/SQL procedure successfully completed.

The utility will give the output in the form of recommendations to be implemented before starting the upgrade. Unless these requirements are met, the upgrade will fail.

Step 3) Executing the recommended steps

Following are the critical steps to be executed based on above warnings. These commands are to be executed while connecting to database from 10g Oracle Home

WARNING: –> Database is using an old timezone file version.
…. Patch the 10.2.0.1.0 database to timezone file version 4
…. BEFORE upgrading the database. Re-run utlu111i.sql after
…. patching the database to record the new timezone file version.

Finding the Version of existing timezone files:

SQL> select * from v$timezone_file;

FILENAME VERSION
———— ———-
timezlrg.dat 2

SQL> SELECT CASE COUNT(DISTINCT(tzname))
WHEN 183 then 1
WHEN 355 then 1
WHEN 347 then 1
WHEN 377 then 2
WHEN 186 then case COUNT(tzname) WHEN 636 then 2 WHEN 626 then 3 ELSE 0 end
WHEN 185 then 3
WHEN 386 then 3
WHEN 387 then case COUNT(tzname) WHEN 1438 then 3 ELSE 0 end
WHEN 391 then case COUNT(tzname) WHEN 1457 then 4 ELSE 0 end
WHEN 392 then case COUNT(tzname) WHEN 1458 then 4 ELSE 0 end
WHEN 188 then case COUNT(tzname) WHEN 637 then 4 ELSE 0 end
WHEN 189 then case COUNT(tzname) WHEN 638 then 4 ELSE 0 end
ELSE 0 end VERSION
FROM v$timezone_names;

VERSION
———-
2

If the Version of the existing timezone is less than 4, then apply the patch for Version 4 timezone files.

Check the database version

SQL> select banner from v$version;

BANNER
—————————————————————-
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Prod
PL/SQL Release 10.2.0.1.0 – Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 – Production
NLSRTL Version 10.2.0.1.0 – Production

For 10.2.0.1 check the metalink note ID 413671.1. We have a table which defines the patch to be applied.

Always try to use the official patch
The script (and on 10g also the csv file) are normally delivered through installation of a patch in the Oracle home. Please note that before using this note you are advised to double check that the time zone patches are not available for your patchset. Applying the “correct” patch through opatch is always preferable to the manual method described in this note.

If there is no official patchset for the version you are currently having then you can Identify the utltzuv2.sql & timezdif.csv combination patch for a different patchset, but same release.

For example if you run 10.2.0.1 and you are trying to find the utltzuv2.sql script & timezdif.csv file you can find the correct patch 5632264 for 10.2.0.2 and this will be applicable to 10.2.0.1 as well.

Please follow the metalink note ID 396387.1

Once you identify the correct patchset(5632264 for 10.2.X), download the same and unzip it.
[oracle]$ unzip p5632264_10202_LINUX.zip
[oracle]$ ls
etc files README.txt
[oracle]$ cd files/oracore/zoneinfo
[oracle]$ ls
readme.txt timezlrg.dat timezone.dat

Backup $ORACLE_HOME/oracore/zoneinfo directory

[oracle]$ cp -R $ORACLE_HOME/oracore/zoneinfo $ORACLE_HOME/oracore/zoneinfo_backup

Copy the .dat files

[oracle]$ cp timezone.dat timezlrg.dat $ORACLE_HOME/oracore/zoneinfo

Bounce the database and check the TIMEZONE version again

SQL> select * from v$timezone_file;

FILENAME VERSION
———— ———-
timezlrg.dat 4

VERSION
———-
4

WARNING: –> Database contains stale optimizer statistics.
…. Refer to the 11g Upgrade Guide for instructions to update
…. statistics prior to upgrading the database.
…. Component Schemas with stale statistics:
…. SYS
…. SYSMAN

Gather Dictionary stats:

Connect as sys user and gather statistics
SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS(‘SYS’);

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS(‘SYSMAN’);

PL/SQL procedure successfully completed.

Step 4) Run Pre-Upgrade Utility again

After executing the recommended steps, run the pre-upgrade utility once again to make sure, you don’t get any critical warnings.

Run the pre-upgrade utility script on 10g database while connecting from 10g oracle home.

If every thing looks fine, Shut down the database from 10g Oracle Home

This time make sure you dont have the critical warnings like the one with TIMEZONE version.

Step 5) Starting Upgrade

Source the following variables for 11g Oracle Home

[oracle]$ export ORACLE_HOME=/u01/app/oracle/oracle/product/product/11.1.0/db_1
[oracle]$ export PATH=$ORACLE_HOME/bin:$PATH
[oracle]$ export ORACLE_SID=orcl
[oracle]$ export TNS_ADMIN=$ORACLE_HOME/network/admin

connected to the database sys as sysdba

sqlplus “/ as sysdba” –> will be connected to idle instance

SQL> startup upgrade
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.

Total System Global Area 611000320 bytes
Fixed Size 1301588 bytes
Variable Size 201327532 bytes
Database Buffers 402653184 bytes
Redo Buffers 5718016 bytes
Database mounted.
Database opened.
SQL> SPOOL upgrade.log
SQL> @catupgrd.sql

Once the upgrades finishes. It will shut down the database automatically.
Login again as sysdba and startup in normal mode.

Check the dba_registry for the components and its status

Step 6) Post-Upgrade Steps

Once the upgrade completes, restart the instance to reinitialize the system parameters for normal operation.

SQL> STARTUP

Run utlu111s.sql to display the results of the upgrade:

SQL> @?/rdbms/admin/utlu111s.sql
.
Oracle Database 11.1 Post-Upgrade Status Tool 23-02-2011 05:22:40
.
Component Status Version HH:MM:SS
.
Oracle Server
. VALID 11.1.0.6.0 00:19:02
JServer JAVA Virtual Machine
. VALID 11.1.0.6.0 00:02:55
Oracle Workspace Manager
. VALID 11.1.0.6.0 00:00:54
OLAP Analytic Workspace
. VALID 11.1.0.6.0 00:00:26
OLAP Catalog
. VALID 11.1.0.6.0 00:00:58
Oracle OLAP API
. VALID 11.1.0.6.0 00:00:25
Oracle Enterprise Manager
. VALID 11.1.0.6.0 00:11:00
Oracle XDK
. VALID 11.1.0.6.0 00:00:53
Oracle Text
. VALID 11.1.0.6.0 00:00:50
Oracle XML Database
. VALID 11.1.0.6.0 00:03:52
Oracle Database Java Packages
. VALID 11.1.0.6.0 00:00:21
Oracle Multimedia
. VALID 11.1.0.6.0 00:04:25
Spatial
. VALID 11.1.0.6.0 00:05:18
Oracle Expression Filter
. VALID 11.1.0.6.0 00:00:13
Oracle Rules Manager
. VALID 11.1.0.6.0 00:00:12
Gathering Statistics
. 00:04:03
Total Upgrade Time: 00:55:57

PL/SQL procedure successfully completed.

Run catuppst.sql, located in the ORACLE_HOME/rdbms/admin directory, to perform upgrade actions that do not require the database to be in UPGRADE mode:

SQL> @?/rdbms/admin/catuppst.sql

Run utlrp.sql to recompile

SQL> select count(*) from dba_objects
2 where status = ‘INVALID’;

COUNT(*)
———-
1576

SQL> @?/rdbms/admin/utlrp.sql

SQL> select count(*) from dba_objects
2 where status = ‘INVALID’;

COUNT(*)
———-
0

Troubleshooting Workflow Notification Mailer Issues

Find Workflow Notification Mailer is up and Running?

SELECT component_name, component_status
FROM fnd_svc_components
WHERE component_type = 'WF_MAILER';

Workflow log’s: FNDCPGSC*.txt under $APPLCSF/$APPLOG directory

Find the Failed One’s?

Select NOTIFICATION_ID, MESSAGE_TYPE, MESSAGE_NAME, STATUS, MAIL_STATUS, FROM_USER, TO_USER from wf_notifications where MAIL_STATUS='FAILED';

Check pending e-mail notification that was pending for process.

Sql> SELECT COUNT(*), message_name FROM wf_notifications
WHERE STATUS='OPEN'
AND mail_status = 'MAIL'
GROUP BY message_name;

Sql> SELECT * FROM wf_notifications
WHERE STATUS='OPEN'
AND mail_status = 'SENT'
ORDER BY begin_date DESC

Check the Workflow notification has been sent or not?

select mail_status, status from wf_notifications where notification_id=

--If mail_status is MAIL, it means the email delivery is pending for workflow mailer to send the notification
--If mail_status is SENT, its means mailer has sent email
--If mail_status is Null & status is OPEN, its means that no need to send email as notification preference of user is "Don't send email"
--Notification preference of user can be set by user by logging in application + click on preference + the notification preference

1. Verify whether the message is processed in WF_DEFERRED queue

select * from applsys.aq$wf_deferred a where a.user_data.getEventKey()= ''
- notification id

2. If the message is processed successfully message will be enqueued to WF_NOTIFICATION_OUT queue, if it errored out it will be enqueued to WF_ERROR queue

select wf.user_data.event_name Event_Name, wf.user_data.event_key Event_Key,
wf.user_data.error_stack Error_Stack, wf.user_data.error_message Error_Msg
from wf_error wf where wf.user_data.event_key = '
To check what all mails have went and which all failed ?

Select from_user,to_user,notification_id, status, mail_status, begin_date
from WF_NOTIFICATIONS where status = 'OPEN';

Select from_user, to_user, notification_id, status, mail_status,begin_date,USER_KEY,ITEM_KEY,MESSAGE_TYPE,MESSAGE_NAME begin_date
from WF_NOTIFICATIONS where status = 'OPEN';

Users complain that notifications are stuck ?

Use the following query to check to see whatever the users are saying is correct

SQL> select message_type, count(1) from wf_notifications
where status='OPEN' and mail_status='MAIL' group by message_type;

E.g o/p of query -

MESSAGE_Type COUNT(1)
-------- ----------
POAPPRV 11 --- 11 mails of Po Approval not sent ---
INVTROAP 12
REQAPPRV 9
WFERROR 45 --- 45 mails have error

If Mail not received by User ?

select Name,DISPLAY_NAME,EMAIL_ADDRESS,NOTIFICATION_PREFERENCE,STATUS
from wf_users where DISPLAY_NAME=’xxx,yyy’ ;

Status – Active
Notification_preference-> Mailtext
Email Address should not be null

Notification not sent waiting to be mailed ?

SQL> select notification_id, status, mail_status, begin_date from WF_NOTIFICATIONS
where status = ‘OPEN’ and mail_status = ‘MAIL’;
To debug the notification id ?

$FND_TOP/sql
run wfmlrdbg.sql
******************************

Note: 1054215.1 - How to Check if the Workflow Mailer is Running
Note: 415516.1 - How to Check Whether Notification Mailer is Working or Not

Note: 831982.1 - 11i/R12 - A guide for troubleshoting Workflow Notification Emails - Inbound and Outbound
Note: 1012344.7 - Notifications Not Being Sent In Workflow
Note: 560472.1 - Workflow Mailers Not Sending Notifications

Please see (Note: 753845.1 - How to Perform a Meaningful SMTP Telnet Test to Troubleshoot Java Mailer Issues), the same error is reported in this doc.

Concurrent Manager : troubleshooting

Summary of Possible Reasons and Solutions for the Problem Where All Concurrent Requests Stuck in Pending Phase

Below are several different possible solutions to the problem where concurrent
requests are stuck in pending status:

1. When shutting down the concurrent manager are there any FNDLIBR processes still running at the OS level? If so, do a kill -9 on them. Then restart the concurrent manager.

2. Try Relinking $FND_TOP.

3. Rebuild the concurrent manager views. As applmgr run the following from
the OS:

This is non-destructive. Concurrent Manager views can be rebuild by running the following command at the command line:

Ensure that concurrent manager is shutdown.

FNDLIBR FND FNDCPBWV apps/apps SYSADMIN 'System Administrator' SYSADMIN

restart the concurrent mgr.

4. Another possibility is that the Profile Option "Concurrent: OPS Request Partitioning" is set to OFF (by default). Unless Concurrent OPS is being used, the setting for this profile option should be NULL.

5. Yet another possibility is that System Profile Option: Concurrent Active
Requests is set to 0.

1. Log into Oracle Applications as SYSADMIN.
2. Select System Administrator responsibility.
3. Navigate to PROFILE --> SYSTEM.
4. Query for %CONC%ACTIVE%.
5. Change the profile option for Concurrent: Active Request Limit to Null (blank).
6. Exit Oracle Applications and log in again for the change to take affect.
7. Run a new concurrent request.

6. Another possibility is that Concurrent managers were brought down, while an
outstanding request was still running in the background. In which case, Update
the FND_CONCURRENT_REQUESTS table as follows:
.
sql> update fnd_concurrent_requests
set status_code='X', phase_code='C'
where status_code='T';
sql> commit;

7. The control_code for concurrent_queue_name = 'FNDCRM' is 'N' in the FND_CONCURRENT_QUEUES table, which means 'Target node/queue unavailable'. This value should be NULL (CRM is running; target and actual process amount are the same), or 'A' ('Activate concurrent manager' control status).

Set the control_code to 'A' in fnd_concurrent_queues for the Conflict Resolution Manager:

1. Logon to Oracle Applications database server as 'applmgr'.
2. Verify the Applications environment is setup correctly ($ORACLE_HOME and $ORACLE_SID).

3. Logon to SQL*Plus as 'APPS' and run the following SQL statement:

update fnd_concurrent_queues
set control_code = 'A'
where concurrent_queue_name = 'FNDCRM';

commit;

4. Verify the status of the concurrent managers through the
Concurrent -> Manager -> Administer form.

If the CRM is still not active, bounce (deactivate, activate) the Internal Concurrent Manager. This is done through the Concurrent -> Manager ->

Administer form from the 'System Administrator' responsibility. It can also be done through the CONCSUB command at the command level.

Setting the control_code to 'A' in the fnd_concurrent_queues table for the Conflict Resolution Manager indicates that this concurrent manager is to be activated with the parameter values specified through this table for this manager (MAX_PROCESSES, CACHE_SIZE, etc).

8. What is the cache size? Try increasing it then bounce the concurrent manager.

If concurrent requests are rarely prioritized and there are managers that service short-running requests, consider setting the cache size to equal at least twice the number of target processes. This increases the throughput of the concurrent manaagers by attempting to avoid any sleep time. For example, if more than one manager or worker processes the same type of requests with only a small cache size, it may be unable to process any jobs in a single processing cycle, because other processes have already run the cached requests. When this happens, it is important to note that the manager will sleep before refreshign its cache. To increase manager throughput where there are sufficient requests of the required type in the queue, increase the cache size to improve the chance of the manager finding work to process and thus avoid having to enter a sleep phase.

TIP: Ensure that the system is not resource-constrained before attempting to increase the rate of concurrent processing in this way; otherwise, these changes may actually reduce concurrent processing throughput because jobs take longer to run..

Enter the number of requests your manager remembers each time it reads which requests to run. For example, if a manager's workshift has 1 target process and a cache value of 3, it will read three requests,, and will wait until these three requests have been run before reading new requests.

In reading requests, the manager will only put requests it is allowed to run into its cache. For example, if you have defined your manager to run only Order Entry reports then the manager will put only Order Entry requests into its cache.

If you enter 1, the concurrent manager must look at its requests list each time it is ready to process another request. By setting the cache size at a higher number, the concurrent manager does not have to read its requests list each time it runs a request. However, the manager does not recognizea nay priority changes you make for a particular request if it has already read that request into its cache. Further, even if you give a higher priority to a new request, that new request must wait until the buffer isempty and the manager returns to look at the requests list. That request may have to wait a long time if you set the buffer size to a high number.

You should use cache size to tune your concurrent managers to work most efficiently for you site's needs. If your organization tends to reprioritize jobs going to a certain manager, that manager should have its buffer size set fairly low.

*Suggestion: *Enter a value of 1 when defining a manager that runs long, time-consuming jobs, and a value of 3 or 4 for managers that run small, quick jobs.

9. Please check the sleep seconds set for the concurrent manager which runs the report that is taking long time for completion. Reducing the sleep time will improve performance.

1. Check for the value set for the sleep seconds for the concurrent manager using the following path: System Administrator Responsibilty -> Concurrent -> Manager -> Define.

2. Check the processes running for the concurent manager using the following command:

ps -ef | grep 'INVLIBR'

As all concurrent programs are dealt by INVLIBR, the number of processes returned here must tally with the value specified for the processes defined for the concurrent manager.

3. Also, check for the duration for which the processes are sleeping. Reducing the value assigned for it would improve performance.

As a workaround to improve performance, you could change the priority of concurent request.

Explanation
-----------
The sleep state would be assigned to some tasks when the program running it has to handle more tasks than it has been configured to process. For example, if the number of processes has been defined as say 10, then at any point of time there is a provision to run 10 tasks. Now say, 12 tasks arrive for the program, then time slots are alloted for 10 and the rest 2 are put to sleep. So, if the concurrent program running the problem report is handling larger number of processes the sleep and run behaviour can be observed.

Issue while starting WF Mailer in Apps 11i

Issue:
exception = java.lang.NoClassDefFoundError: javax/jms/Connection

Here is the full error message :
[Jul 21, 2011 2:23:11 PM EDT]:1311272591539:Thread[Task Request Thread,5,main]:0:-1:oraappsdba.amestruetemper.com:10.80.1.135:-1:-1:UNEXPECTED:[fnd.wf.bes.ConnectionManager$1]:Failed to establish Java Business Event System control connection: databaseId = oradbdba_dba, ownerName = APPLSYS, queueName = WF_CONTROL, subscriberName = WFBESCL_13825, selector = null, exception = java.lang.NoClassDefFoundError: javax/jms/Connection

Solution:
1. Please set $JAVA_TOP/appsborg2.zip in the AF_CLASSPATH in $APPL_TOP/admin/adovars.env file.

Example of AF_CLASSPATH setting:
/local/java/jdk1.3.1/lib/dt.jar:/local/java/jdk1.3.1/lib/tools.jar:/slot05/appmgr/atgwfqacomn/java/
ppsborg2.zip:/slot05/appmgr/atgwfqaora/8.0.6/forms60/java:/slot05/appmgr/atgwfqacomn/java

2. Shutdown and restart the Concurrent Manager.

Refernce : Java.Lang.Noclassdeffounderror: Javax/Jms/Connection When Starting Workflow Mailer [ID 342700.1]

Workflow Trouble Shooting

Oracle workflow notification mailer sql's
=========================================

1. Workflow: version
2. check workflow status.
3. check if workflow is used by only one instance
4. check if processor_read_timeout_close is set to 'Y'
5. check for bad e-mail address
6. How to know mail sent to a user with details:
7. How to know whether it is set to correct url from porfile options:
8. How to know reqid, process id, sid..
9. workflow patches
10. Workflow: To see failed, open notifications
11. To check if email address, notification preference, display_name
12. How to know workflow responsibility from backend:
13. Steps to drop and recreate WF_CONTROL queue:

=================
1. Workflow: version
=================
$FND_TOP/sql/wfver.sql

----------------------------------------------------
2. 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;

----------------------------------------------------
3. 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;

----------------------------------------------------
4. check if processor_read_timeout_close is set to 'Y'
----------------------------------------------------
set pagesize 100
set linesize 132
set feedback off
set verify off

col value format a35
col component_name format a30

select c.component_id, c.component_name, 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 = 'PROCESSOR_READ_TIMEOUT_CLOSE'
order by c.component_name,p.parameter_name;

----------------------------------------------------
5. check for bad e-mail address
----------------------------------------------------
If below SQL statement is returning rows you need to correct the email addresses for associated users:

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 '%%';

============================================
6. How to know mail sent to a user with details:
============================================
select name, display_name, notification_preference, email_address from wf_local_roles where name = '';

====================================================================
7. How to know whether it is set to correct url from porfile options:
====================================================================
set linesize 155;
set pagesize 200;
set verify off;
col Profile format a50;
col Value format a50;
select t.PROFILE_OPTION_ID ID, z.USER_PROFILE_OPTION_NAME Profile,
nvl(v.PROFILE_OPTION_VALUE,'Replace with non-virtual URL') Value
from apps.fnd_profile_options t, apps.fnd_profile_option_values v, apps.fnd_profile_options_tl z
where (v.PROFILE_OPTION_ID (+) = t.PROFILE_OPTION_ID)
and (z.PROFILE_OPTION_NAME = t.PROFILE_OPTION_NAME)
and (t.PROFILE_OPTION_NAME in ('WF_MAIL_WEB_AGENT'));

====================================
8. How to know reqid, process id, sid..
=====================================
select request_id,phase_code,status_code,hold_flag from fnd_concurrent_requests where REQUEST_ID=20422815;

select ORACLE_PROCESS_ID,ORACLE_SESSION_ID,OS_PROCESS_ID from fnd_concurrent_requests where REQUEST_ID=20422815;

select a.session_id,
b.owner,
b.object_type,
b.object_name,
a.oracle_username,
a.os_user_name,
a.process,
a.locked_mode
from v$locked_object a, dba_objects b
where b.object_id = a.object_id
and a.session_id='3383';

select name, display_name, notification_preference, email_address from wf_local_roles where name = 'SYSADMIN';

===================
9. workflow patches
===================
will provide us information as to your base line code level.
Many issues are only relevant to a certain code level so this information is essential:

set linesize 155;
set pagesize 200;
set verify off;
select b.bug_number bug, b.LAST_UPDATED_BY ldate, decode( bug_number, 2728236 , 'OWF.G INCLUDED IN 11.5.9',
3031977, 'POST OWF.G ROLLUP 1 - 11.5.9.1',
3061871, 'POST OWF.G ROLLUP 2 - 11.5.9.2',
3124460, 'POST OWF.G ROLLUP 3 - 11.5.9.3',
3316333, 'POST OWF.G ROLLUP 4 - 11.5.9.4.1',
3314376, 'POST OWF.G ROLLUP 5 - 11.5.9.5',
3409889, 'POST OWF.G ROLLUP 5 Consolidated Fixes For OWF.G RUP 5', 3492743, 'POST OWF.G ROLLUP 6 - 11.5.9.6',
3868138, 'POST OWF.G ROLLUP 7 - 11.5.9.7',
3262919, 'FMWK.H',
3262159, 'FND.H INCLUDE OWF.H',
3258819, 'OWF.H INCLUDED IN 11.5.10',
3438354, '11i.ATG_PF.H INCLUDE OWF.H',
3140000, 'ORACLE APPLICATIONS RELEASE 11.5.10 MAINTENANCE PACK',
3240000, '11.5.10 ORACLE E-BUSINESS SUITE CONSOLIDATED UPDATE 1',
3460000, '11.5.10 ORACLE E-BUSINESS SUITE CONSOLIDATED UPDATE 2',
3480000, 'ORACLE APPLICATIONS RELEASE 11.5.10.2 MAINTENANCE PACK',
4017300, 'ATG_PF:11.5.10 Consolidated Update (CU1) for ATG Product Family',
4125550, 'ATG_PF:11.5.10 Consolidated Update (CU2) for ATG Product Family',
4719658, 'ONE OFF PATCH FOR MISSING RESPONSIBILITIES - WFDS Fix',
5121512, 'AOL USER RESPONSIBILITY SECURITY FIXES VERSION 1',
6008417, 'AOL USER RESPONSIBILITY SECURITY FIXES 2b',
4676589, '11i.ATG_PF.H RUP4',
5473858, '11i.ATG_PF.H RUP5',
5903765, '11i.ATG_PF.H RUP6',
4334965, '11i.ATG_PF.H RUP3') patch
from apps.AD_BUGS b
where b. BUG_NUMBER in ('2728236','3031977','3061871','3124460','3316333','3314376','3409889','3492743','3262159','3262919','3868138','3258819','3438354','3240000','3460000','3140000','3480000','4017300','4125550','4719658','5121512','6008417','4676589','5473858','5903765','4334965')
order by patch;

===============================================
10. Workflow: 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;

====================================================================
11. To check if email address, notification preference, display_name
====================================================================

select DISPLAY_NAME, NOTIFICATION_PREFERENCE from wf_users where EMAIL_ADDRESS = 'h@h.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 = 'mmmmm@yahoo.com';

====================================================
12. How to know workflow responsibility from backend:
====================================================

select wes.status, wes.phase, wes.rule_function, wes.on_error_code from wf_events we, wf_event_subscriptions wes
where we.name='oracle.apps.fnd.wf.ds.userRole.updated' and we.guid=wes.event_filter_guid;

============================================
13. Steps to drop and recreate WF_CONTROL queue:
============================================

a. Shut down the concurrent managers.

b. Connect to sqlplus session as APPS user:
Execute: (For Workflow Embedded within Apps)

SQL>exec DBMS_AQADM.DROP_QUEUE_TABLE(queue_table => 'APPLSYS.WF_CONTROL', force =>TRUE);

SQL> commit;

Execute: (For Standalone Workflow)
sqlplus / @wfctlqec.sql

c. Execute wfjmsqc2.sql for creating all Advanced Queues with JMS Text structures.

Usage:
sqlplus / @wfjmsqc2.sql

Both of the script are under $FND_TOP/patch/115/sql or $FND_TOP/sql directories.

d.Run afwfqgnt.sql to recreate grants/synonyms for Workflow Advanced Queue tables

Usage:
sqlplus apps/apps @$FND_TOP/patch/115/sql/afwfqgnt.sql APPS APPLSYS
Additional Information: For Rebuilding all other WF Queue refer Note 754468.1 Title: How to Rebuild Workflow Queues ,based on your application version.