"ORA-04062: signature of package "APPS.WF_EVENT" has been changed" error (+ solution )

Symptoms

When saving a change in the Expense Report Templates form, the following error is received:

"ORA-04062: signature of package "APPS.WF_EVENT" has been changed"

Cause

The Form was not recompiled after the package signature changed.

If other product teams change their package signature, then all the dependent objects need to be
re-compiled.

Solution

To implement the solution, please execute the following steps:

1. Recompile the Expense Report Templates form APXXXDER using adamin.

Option #2

 Temporarily resetting FORMS60_PATH to:
"/app/D2CRP/d2crpappl/au/11.5.0/resource:/app/D2CRP/d2crpappl/au/11.5.0/forms/US:\
/app/D2CRP/d2crpappl/au/11.5.0/resource/US"

Generating form "forms/US/APXXXDER.fmx" with command:

/app/D2CRP/d2crpora/8.0.6/bin/f60gen module=/app/D2CRP/d2crpappl/au/11.5.0/forms/US/APXXXDER.fmb userid=APPS/***** output_file=/app/D2CRP/d2crpappl/ap/11.5.0/forms/US/APXXXDER.fmx module_type=form batch=yes compile_all=special

Successfully generated form "forms/US/APXXXDER.fmx".

ORA-24026: operation failed, queue APPLSYS.WF_CONTROL error in WF Notification Mailer (+ solution)

Symptoms

The orkflow mailer does not run because of errors in the log file

ORA-20002: 3825: Error '-24026 - ORA-24026: operation failed, queue APPLSYS.WF_CONTROL has
errors' encountered during execution of 'WF_RULE.ERROR_RULE' for event
'oracle.apps.fnd.cp.gsc.SvcComponent.start'.
FUNCTION_NAME=WF_EVENT_OJMSTEXT_QH.enqueue()

Cause

Either the queue WF_CONTROL or its data is corrupted, probably due to the change of the IMAP/SMTPserver settings.

Since the WF_CONTROL queue is used by the notification mailer to hold runtime data, and it is not valid, the mailer will not be able to start up

Solution

To solve this problem please proceed as follows:

1. Stop Workflow Agent listeners

2. Run the following:
cd $FND_TOP/patch/115/sql
sqlplus APPS/ @wfctqrec.sql APPLSYS

3. Grant required privileges:
sqlplus apps/passwd @afwfqgnt.sql APPS APPLSYS

Note: APPLSYS password is the same as APPS'.

4. Start Workflow Agent listeners.

The Workflow mailer should start now

Hint: to make sure the WF_CONTROL queue was created successfully, the Concurrent request 'Workflow Control Queue Cleanup' should run without errors.

Oracle CRS with Trunked public interface problem (+ workaround)

I've encountered a problem installing Oracle CRS on a server with trunked public 'ce' interfaces.

Hardware: v890
OS : Solaris 10 update 3
Oracle Version: 10.2.0.3
Trunking : 1.3

The 'vipca' script fails, and when you look at the logfile $CRS_HOME/log//racg/ora..vip.log it's packed full of usage messages from nettr (see extract from log below). Oracle has a bug logged with similar symptoms 5876195 but this is marked as not a bug and they have not published a workaround.

Looking at the script $CRS_HOME/bin/racgvip, it calls nettr -stats with incorrect options if you have a 'ce' interface.

line 305 # _O1=`$NETTR -stats ${_TRUNKHEAD} device=${_DEVICE} | $AWK '/'$_DEVICE'/ { _S += $2 } END { print _S }'`

If you alter this line to read

_O1=`$NETTR -stats ${_TRUNKHEAD} | $AWK '/'$_DEVICE'/ { _S += $2 } END { print _S }'`

Then re-run vipca manually, your vip will create successfully.

Hope this helps stop someone having the same headaches I did,

mel


------ Extract from log starts here

Oracle Database 10g CRS Release 10.2.0.1.0 Production Copyright 1996, 2005 Oracle. All rights reserved.
2007-03-08 13:48:30.063: [ RACG][1] [5638][1][ora.bombay.vip]: Usage: /etc/opt/SUNWconn/bin/nettr -setup device= members=
[ { device= members= }... ]
[ policy= ]
[ mac= ]
[ lacp= [ ptimer=<0|1> ] ]
/etc/opt/SUNWconn/bin/nettr
2007-03-08 13:48:30.070: [ RACG][1] [5638][1][ora.bombay.vip]: -setup device= members=
[ policy= ]

/etc/opt/SUNWconn/bin/nettr -stats
[ type=<1|2> ] [ interval= ]
/etc/opt/SUNWconn/bin/nettr -stats device=
[ type=<1|2> ] [ in
2007-03-08 13:48:30.070: [ RACG][1] [5638][1][ora.bombay.vip]: terval= ]

/etc/opt/SUNWconn/bin/nettr -release
/etc/opt/SUNWconn/bin/nettr -release device=

/etc/opt/SUNWconn/bin/nettr -add
device= members=
[ { device=dev2> members= }.
2007-03-08 13:48:30.070: [ RACG][1] [5638][1][ora.bombay.vip]: .. ]

/etc/opt/SUNWconn/bin/nettr -rem
device= members=
[ { device= members= }... ]

/etc/opt/SUNWconn/bin/nettr -mod
[ policy= ]
[ lacp= ] [ ptimer=<0|1> ]

/etc/opt/S

ORACLE APP : IMP of Operation Unit System profile

On typical Friday I got a Multiple request that one of our critical environment is down. During investigation I found someone remove Site level profile for operating unit. No user was able to login to application. Anyone who has access to the System Profile Values form will be able to remove the value and thus break the environment for everyone else.

The statement mentioned above applies to any 11.5.10 or higher environment. Previous releases of Oracle Applications did allow the System Administrator to login to the application using the Forms Login (known as dev60cgi) and correct the problem.

1. Login into Oracle Applications
2. Select the System Administrator Responsibility
3. Navigate to Profile --> System
4. Query up "MO: Operating Unit" profile
5. In the site level column there should be a default organization.
6. If this field is blank, select a default organization from the LOV

As of Oracle Applications 11.5.10 and due to some code changes it is no longer possible for the System Administrator to correct the problem via the application itself.

• Identify if a site level row exists for the profile option:
  

SQL> select application_id, profile_option_id,
site_enabled_flag, site_update_allowed_flag
from fnd_profile_options where PROFILE_OPTION_NAME = 'ORG_ID'

• Identify if a site level row exists for the profile option:

SQL> select * from fnd_profile_option_values
where profile_option_id = &option_id
and level_id = 10001;

• If site_enabled_flag is not equal 'Y'

SQL> update fnd_profile_options set site_enabled_flag = 'Y' 
where profile_option_name = 'ORG_ID';
commit;

• Find the valid organization id

  SQL> SELECT ORGANIZATION_ID, NAME FROM HR_OPERATING_UNITS;

Pick one of the organization_id values and use it with this insert statement:

• If a row does exist for the site level in table fnd_profile_option_values;

        SQL> Update FND_PROFILE_OPTION_VALUES
      set PROFILE_OPTION_VALUES = ''
      where PROFILE_OPTION_ID = 1991
      and APPLICATION_ID =0
      and level_id = 10001;

• If a row does not exist for the site level in table fnd_profile_option_values;

     SQL> insert into fnd_profile_option_values
  (values 0, profile_option_id_from_query1, 10001, 0, sysdate, 0,
   sysdate, -1, 0, "valid_organization_id", null, null);
   commit;

 ** Where "valid_organization_id" is replaced by an actual organization_id value in quotes. 

  OR you can use API 

 SQL> DECLARE 
stat boolean; 
BEGIN 
dbms_output.disable; 
dbms_output.enable(100000); 
stat := FND_PROFILE.SAVE('ORG_ID', valid_organization_id, 'SITE'); 
IF stat THEN                       
dbms_output.put_line( 'Stat = TRUE - profile updated' ); 
ELSE                 
dbms_output.put_line( 'Stat = FALSE - profile NOT updated' ); 
END IF;
commit; 

• validate if a site level row exists for the profile option:

        SQL> select * from fnd_profile_option_values
          where profile_option_id = &option_id and level_id = 10001;

• Compile pkg FND_GLOBA (optinal):

   SQL> alter package FND_GLOBAL compile;

  alter package FND_GLOBAL compile body;

• Restart all nodes. 

ORACLE APPS: How To Update and Move Workflow From One Instance to Another Instance?

How To Update and Move Workflow From One Instance to Another Instance ?

Let say test is Source and PROD is target instance

1. Login to test - set the environment -

Run the following commands -

WFLOAD apps/apps 0 Y DOWNLOAD reqappv_final.wft REQAPPRV WFLOAD apps/apps 0 Y DOWNLOAD poapprv_final.wft POAPPRV

This will pull the workflow definition file from the database - customizations - etc...

2. Next, ftp/scp the files created - reqapprv_final.wft, etc.. to the production database server.

3. Now, login to the database server where the files now reside. Set the environment now for the production instance.

Execute the following to backup original on production: WFLOAD apps/apps 0 Y DOWNLOAD reqappv_prod.wft REQAPPRV WFLOAD apps/apps 0 Y DOWNLOAD poapprv_prod.wft POAPPRV

WFLOAD apps/apps 0 Y UPGRADE reqappv_final.wft WFLOAD apps/apps 0 Y UPGRADE poapprv_final.wft

This will upload the wft file - into the database - for the new instance.

4. Test the results.

Another way is to use the workflow builder. 1. Pull up the workflow from test in the workflow builder. 2. Then choose File/Save As - and enter the production database information and save. 3. You have now saved the workflow into the production database.

How to find shortname for workflows based on description?

Workflow information can be found in the wf_item_types_tl table. It will supply the long name and the short name for all workflows. That being said, the PO Create Documents workflow is CREATEPO.wft

ORACLE APPS: How to implementing user preferred time zone

How to implementing user preferred time zone?

1. Technical Implementation

This section provides the technical considerations for implementing user preferred time zone.

1.1 Technology stack requirements

For time zone support to be activated, the system administrator must ensure that each of the following is true:

• Oracle E-Business Suite version 11i10 or higher

• Oracle RDBMS 9i or higher

• The database must be configured to use the time zone file 'timezlrg.dat' rather than the 'timezone.dat' file

• The database must be started in the standard corporate time zone

• Profile 'Server Timezone' (SERVER_TIMEZONE_ID) must be set at the Site level, and must be set to the same standard corporate time zone as the database

• Profile 'Client Timezone' (CLIENT_TIMEZONE_ID) must be set at the user level

• Profile 'Enable Timezone Conversions' (ENABLE_TIMEZONE_CONVERSIONS) must be set to 'Yes' ('Y') at the Site level.

• Profile 'Concurrent: Multiple Time Zones' (CONC_MULTI_TZ) should be set to 'No' (N) at the Site level

• Environment variable FORMS60_APPSLIBS must be set in the Forms tier (this has always been a requirement for 11i)

• Forms must be launched through the Personal Home Page or Navigate portlet

These requirements are discussed in more detail below.

1.1.1 Database: Oracle 9i and Time Zone File 'timezlrg.dat'

The 9i database is required because it has built-in time zone support, including time zone definitions and rules for daylight savings time. The E-Business Suite makes use of this capability for certain server-side conversions. Note that new datatypes, such as TIMESTAMP WITH LOCAL TIME ZONE, are not currently used but may be in the future.

The database must be started using file 'timezlrg.dat', which contains the time zone definitions that are used within the E-Business Suite.

To set this in a UNIX environment, issue the command

setenv ORA_TZFILE $ORACLE_HOME/oracore/zoneinfo/timezlrg.dat

before starting the database.

The database must also be started in the standard corporate time zone.

To set this in a Unix environment, issue the command

setenv TZ <Timezone Code> (For example ‘America/Los_Angeles’)

You can verify your setup by running the following in Sqlplus:

select to_char(SYSDATE, ‘DD-MON-RRRR HH24:MI:SS’)

from dual

to ensure the date with time returned are correct for the corporate time zone.

1.1.2 Applications Profiles

The profile option Server Timezone (SERVER_TIMEZONE_ID) should be set at the site level to the standard corporate time zone (the time zone in which the server has been set to run). This profile option should not be changed once set, as existing data will not be updated.

Users may specify their preferred time zone by setting the profile called Client Timezone (CLIENT_TIMEZONE_ID) at the user level. This profile appears on the self-service Preferences screen, as well as in the Forms-based Profiles screen. This time zone preference may be changed as often as needed. As with most profiles, you will need to login to the system again for the change to take effect.

The profile option Enable Timezone Conversions (ENABLE_TIMEZONE_CONVERSIONS) is seeded with a default value of No at the site level. This will cause the applications to continue showing all dates in the corporate time zone. Setting this value to Yes will enable the automatic conversion of all date with time fields to the user preferred time zone. Exercise caution when changing this value; unless users are notified, users may assume that they are still operating in the corporate time rather than local time or visa versa, and could enter or interpret data erroneously.

Please note the behavior of existing profile Concurrent: Multiple Time Zones (CONC_MULTI_TZ). This was an older feature to handle batch processing. Setting this profile to Yes alters the default value that appears for the Scheduled Start Date in the Submit Requests screen to SYSDATE-1.

With the new user preferred time zone feature enabled, this profile is no longer needed and should typically have a value of No (N).

1.1.3 Environment variable FORMS60_APPSLIBS

This environment variable controls multiple aspects of Oracle Forms in the E-Business Suite environment and must be left unchanged from the installed setting.

1.1.4 Launching of Forms

The time zone feature is only available in the Oracle Forms based user interfaces within the E-Business Suite when the user logs in through the Personal Home Page or the Navigate portlet. Direct launching of Forms, for example by typing a URL into the browser address line, is supported only for bootstrap purposes, and will not enable the time zone feature or other features such as language settings and date formats.

Ref : CU2_TimeZone_Guide.pdf

ORACLE APPS: How to add Remote database using CC JOB

I need to add a remote server in Database using CC JOB

First check what are already define as Remote Server

CC JOB Program NAME View Servers under Configurator Application Name.

Now add your new remote Server with following CC JOB

CC JOB Program NAME Define Remote Server under Configurator Application Name.

Server Name=

Host Name=

Port=<Database Listener Port>

Instance Name=

Server Db Version=

FND Name= 

Global Name= 

Notes=

      <> 

FND Link Name=_LINK

Import Enabled=N  

NOTE : Before enable the Remote server please make sure from the CC server you are able to login to remote server using apps username and password combination while your TNSNAME.ora file have right entry for remote server.

Failure to perform above test put me in following situation:

CC job fail with following

**Starts**10-OCT-2006 14:04:21

**Ends**10-OCT-2006 14:04:21

Remote server database version not found: ORA-12537: TNS:connection closed.

Solution : Update sqlnet.ora on remote server to comment out the tcp.validanode_checking=yes and tcp.invited_nodes=(LIST OF NODE NAMES)

Reload the listener

% lsnrctl reload

If try again the same CC JOB again to enable the remote server, It will Fail as during first run Database link is created but post activity such as updating Server Db version was not finished. In second run it again try to create the database link with same database link which will fail.

Solution: Connect to Database as apps account, Find out all Database links and drop the database link created for new Remote server you are working on.

Now enable the new Remote Database using CC job

CC JOB Program NAME Enable Remote Server under Configurator Application Name.

This CC will require you to pass passwd for apps account as your argument.

File : Remote_database_add_using_CC_problem_and_solution.doc

ORACLE APPS: ORA 01031 during clone build.

Today I was working on building new clone .

System admin give me the system which was not build as per deployment Doc and having loose ends all over the places. Some example

DNS was not push out.

Unix account created but no group added

/var/opt/oracle was not created.

I continue with my build.

After I run clone for oracle account which exit with status code 0.

I was trying to build database from Source backup.

Every time I try to connect as sysdba to ideal database. I have all required environment variable required for ORACLE database.

$ sqlplus 

SQL*Plus: Release 9.2.0.6.0 - Production on Fri Oct 6 18:34:00 2006

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Enter user-name: / as sysdba

ERROR:

ORA-01031: insufficient privileges

Enter user-name: 

ERROR:

ORA-01017: invalid username/password; logon denied

Enter user-name: 

ERROR:

ORA-01017: invalid username/password; logon denied

SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus

Here is what I had

$ id

uid=70002(oracle) gid=70035

This is Solaris 10 and database inside Zone

I did a quick search on Metalink but No direct hit.

Confirm the Oracle is re-compile and have permission setup correctly. oracle binary have permission "rwsr-s--x" (6751)

Confirm no issue with ENV

Only suspicious object was my gid was not listed in /etc/group.

Talk to SA ask him to add entry in /etc/group for dba with GID =70035 and add oracle and applmgr to dba group.

After SA make modification to /etc/group file, I have

$ id

uid=70002(oracle) gid=70035(dba)

 
But again when I try to connect SQLPLUS as sysdba I
have same error message. Time is ticking, I rethink what I
did so for, The oracle code was relink using the oracle apps
utility rapid clone. The linking and all modification was
done when group was not setup. 

Ok time for redo the work. 

I re-run the clone process for oracle account.
It finish again with exit code 0. So for so good. 

Now I try sqlplus “/ as sysdba”

addlnctl.sh: exiting with status 0

$ sqlplus 

SQL*Plus: Release 9.2.0.6.0 - Production on Fri Oct 6 18:50:18 2006

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Enter user-name: / as sysdba

Connected to an idle instance.

SQL> exit

CONCUSION:
The apps clone take the group name for the user provided in interactive
question set. 

LESSION LEARN: Make sure you have uid and gid with there corresponding
username and groupname before staring cloning.

NEXT : Find if there are txt files which have Group name.

ORACLE APPS: Why doesn't the "Launch Diagnostic" button work in 11.5.10 (OAM 2.3.1)?

Why doesn't the "Launch Diagnostic" button (on the Diagnostics page of the Applications Dashboard of Applications Manager) work in 11.5.10 (OAM 2.3.1)?


Make sure that the DISPLAY is set in jserv.properties to a valid and running X-Server. Apache needs to be restarted after changing the configuration (e.g. modifying jserv.properties, starting X-Server, using "xhost +" etc.). If the X-Server works properly then the buttons will appear as generated images instead of gray squares.

ORACLE APPS: How to add Read only account for OAM?

How to add Read only account for OAM?

Verify you have OAM Patch 2.1.1 or higher

select * from ad_bugs where bug_number in ('2600516')


These Oracle Applications responsibilities for OAM are:

System Administrator - this responsibility has access to all functionality in OAM.

OAM Read Only Mode - this responsibility provides administrators with view-only access to most of the data in OAM. This

responsibility does not provide access to critical actions such as Stop Services and Edit Configuration Parameters.

OAM Patch History - administrators with this responsibility can view the Applications Dashboard and Patch History pages.

Note: when an administrator is assigned more than one of the above responsibilities, the access mode is assigned according

to the highest access, as listed above. For example, if an administrator is assigned the System Administrator responsibility

and either OAM Read Only Mode or OAM Patch History, that administrator has access to all functionality in OAM. If an

administrator is assigned the responsibilities OAM Read Only Mode and OAM Patch History, the OAM Read Only Mode

responsibility takes precedence

Create a application user and assigned it OAM Read only mode responsibility.

ORACLE APPS: how to Determining the Current Version of Oracle HTTP Server

Determining the Current Version of Oracle HTTP Server

To determine the version of the Oracle HTTP Server, either consult the Oracle Installer inventory or run the following commands:

On Unix, before iAS 1.0.2.2:
% cd /Apache/Apache/bin
% ./httpds -v
Server version: Apache/1.3.9 (Unix)
OR
Server version: Apache/1.3.12 (Unix)

On Unix, with iAS 1.0.2.2:
% cd /Apache/Apache/bin
% ./httpd -v
Server version: Oracle HTTP Server Powered by Apache/1.3.19 (Unix)

ORACLE APPS : How To Determine The Version Of OAM (Oracle Application Manager)?

How To Determine The Version Of OAM (Oracle Application Manager)?

1. Login to OAM
2. Navigate to Site Map > Others > Applications Manager Log
3. Find the version in the upper left corner


If the above navigation path is not available in OAM or you are unable to
login to OAM, use the following steps to check the version of OAM:


Unix
----
$ cd $JAVA_TOP

$ ident $JAVA_TOP/fndoam.zip | grep Header

OR

$ strings -a fndoam.zip | grep Header


fndoam.zip 115.26 and below -- OAM 2.0
fndoam.zip 115.27 -- OAM 2.1
fndoam.zip 115.34 -- OAM 2.1.1
fndoam.zip 115.75 -- OAM 2.2
fndoam.zip 115.75.x.x -- OAM 2.3
fndoam.zip 115.76 -- OAM 2.3.1


The last entry should have something like ...
$Header: fndoam.zip 115.27 2002/04/18 09:41:37 pkm ship

Here is an example

ceapiapp$
ceapiapp$
ceapiapp$ cd $JAVA_TOP
ceapiapp$ strings -a fndoam.zip |grep Header
$Header: fndoam.zip 115.76 2003/11/13 16:57 ppradhan noship $
ceapiapp$

Ref : metalink Doc ID 214962.1