Oracle Apps: Check current product installation and version

Use this query:

SELECT a.application_name,
DECODE (b.status, 'I', 'Installed', 'S', 'Shared', 'N/A') status,
patch_level
FROM apps.fnd_application_vl a, apps.fnd_product_installations b
WHERE a.application_id = b.application_id
order by 1

Upgrading to Oracle Database 11g

 

Upgrade Path

You can directly upgrade to oracle 11g, if your curent database is 9.2.0.4 or newer.

It supports direct upgrades to versions 9.2.0.4, 10.1 and 10.2

7.3.3 -> 7.3.4 -> 9.2.0.8 -> 11.1

8.0.5 -> 8.0.6 -> 9.2.0.8 -> 11.1

8.1.7 -> 8.1.7.4 -> 9.2.0.8 -> 11.1

9.0.1.3-> 9.0.1.4 -> 9.2.0.8 -> 11.1

9.2.0.3 (or lower) -> 9.2.0.8 -> 11.1

Oracle 11g client can access Oracle databases of versions 8i, 9i and 10g.

COMPATIBLE parameter

The default compatibilty value for Oracle 11g is 11.1 , You can , however upgrade to 11g with a minimum value of the COMPATIBLE parameter of 10.0.0 , but only a small number of features will be available if you keep the parameter to 10.0.0

Manual Upgrade Procedure

1.Invoke the Pre-Upgrade Information Tool in the database to upgrade.

This tool is simply the script $ORACLE_HOME/rdbms/admin/utlu111i.sql. So, you have to copy this script altogether with the scripts: utlu111s.sql and utlu111x.sql to a staging directory in the database to upgrade.

As with previous versions, this tool will examine the target database and display the warnings and recommendations that you should consider before you start the upgrade process such as removing obsolete initialization parameters, setting proper values to some parameters and adding space to key tablespaces.

Spool the output of running the script for later review.

SQL>spool upgrade11g.log

SQL>@utlu111i.sql

…

SQL>spool off

2.Backup the database.

3.Set the COMPATIBLE parameter to 11.1.0. You can do so by issuing the following command:

ALTER SYSTEM SET COMPATIBLE=’11.1.0′ SCOPE=SPFILE;

4.Modify the values of the initialization parameters and remove the obsolete parameters as recommended by the Pre-upgrade tool in the current initialization parameter file.

5.Copy the initialization parameter file to the new Oracle 11g home.

6.Shutdown cleanly the database.

7.If the database is installed in a Windows system, perform the following steps:

a)Stop the Oracle database service. Usually its name has the format OracleService.

b)Delete the service. You can use the oradim utility for this purpose.

oradim -delete –SID

c)Use oradim utility in the Oracle 11g home to create a new Oracle Database 11g release instance. Of course, it should use the same SID.

oradim -NEW –SID

8.If the database to upgrade is using a password file, move it to the new Oracle 11g home.

9.Change the environment variables ORACLE_HOME, PATH, and LD_LIBRARY_PATH so that they point to the new Oracle Database 11g directories.

10.In the Oracle 11g home, change to directory $ORACLE_HOME/rdbms/admin and then start the SQL*Plus

11.Start the database in upgrade mode and then run the upgrade script (it takes long time). When working in upgrade mode, Oracle allows only SYSDBA connections and sets some initialization parameters to specific values that are required to run the upgrade script.

SQL>login sys/password as sysdba

SQL>startup upgrade pfile=$ORACLE_HOME/dbs/initorcl.ora

SQL>spool upgrade11g.log

SQL>@ catupgrd.sql

…

SQL>spool off

12.After the upgrade script finishes, make sure no error occurred during the upgrade. Usually errors are raised

because of lack of shared memory or tablespace size. If there is any error, fix its cause and restart the upgrade script.

13.When the upgrade script successfully finishes, restart the database in OPEN mode.

14.Rum utlu111s.sql script (referred to as Post-Upgrade Status tool) to view the results of the upgrade. This tool will view the installed components and their status. If you see a component with invalid status, usually running the script in the next step will set it to valid.

15.Execute the script utlrp.sql to in parallel recompile any invalid PL/SQL program unit and Java code.

SQL>@utlrp.sql

SQL>SELECT COUNT(*) FROM DBA_INVALID_OBJECTS;

16.Because the upgraded database disables all tablespace alerts by setting the threshold to null, set the thresholds for tablespace alert to the values you desire.

If the upgrade process fails, restore the database from the backup.

Oracle database: Reducing datafile size to recover free space

The following are queries you may use:
select 'alter table '||owner||'.'||table_name||' move tablespace STG_TS1 ;'
from dba_tables
where tablespace_name = 'STG_TS'
order by 1;

select 'alter index '||owner||'.'||index_name||' rebuild tablespace STG_TS1 ;'
from dba_indexes
where tablespace_name = 'STG_TS'
and index_type != 'LOB'
order by 1;


select 'alter table '||TABLE_OWNER||'.'||table_name||' move partition '||PARTITION_NAME||' tablespace STG_TS1 ;'
from dba_tab_partitions
where tablespace_name = 'STG_TS'
order by 1;

select 'alter table '||OWNER||'.'||table_name||' move lob('||SEGMENT_NAME||') store as (TABLESPACE STG_TS1) ;'
from dba_lobs
where tablespace_name = 'STG_TS'
order by 1;

select 'alter table ' ||owner || '.' || table_name||' move lob ('||column_name||') store as (tablespace STG_TS1);'
from dba_lobs where SEGMENT_NAME in ( select segment_name from dba_segments where tablespace_name ='STG_TS' and segment_type like '%LOB%');

Oracle workflow notification mailer status - sql

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.


3. Restart the services.

Oracle EBS: Discoverer Version is Installed on Linux/Unix Servers

For Discoverer 4i, run commands:

$cd $ORACLE_HOME/8.0.6/discwb4/bin
$strings -a dis4ws | grep -i 'Discoverer version' 

 

For Discoverer 9i (9.0.2 and 9.0.4), run commands from your ORACLE middle tier home:

$cd $ORACLE_MID_HOME/bin
$strings -a dis51ws | grep -i 'Discoverer version'

For Discoverer 10.1.2, run commands from your ORACLE middle tier home:
$cd $ORACLE_MID_HOME/bin 
$strings -a dis51ws | grep -i 'Discoverer version'

Oracle Tablespace size monitoring

COLUMN pct_free FORMAT 999.99 HEADING "% Free"
COLUMN name FORMAT A16 HEADING "Tablespace Name"
COLUMN mbytes FORMAT 99,999,999 HEADING "Total GBytes"
COLUMN used FORMAT 99,999,999 HEADING "Used Gbytes"
COLUMN free FORMAT 99,999,999 HEADING "Free Gbytes"

SELECT  fs.tablespace_name name,
        round((df.totalspace/1024),2) mbytes,
        round((df.totalspace - fs.freespace)/1024,2) used,
        round(fs.freespace/1024,2) free,
        round(100 * (fs.freespace / df.totalspace),2) pct_free
FROM    (SELECT
         tablespace_name,
    ROUND(SUM(bytes) / 1048576) TotalSpace
    FROM
    dba_data_files
    GROUP BY
    tablespace_name
    ) df,
    (SELECT
    tablespace_name,
    ROUND(SUM(bytes) / 1048576) FreeSpace
    FROM
    dba_free_space
    GROUP BY  tablespace_name
    ) fs
WHERE   df.tablespace_name = fs.tablespace_name(+)
order by 2 desc;