Blog dedicated to Oracle Applications (E-Business Suite) Technology; covers Apps Architecture, Administration and third party bolt-ons to Apps

Wednesday, October 31, 2007

ORA-06502: PL/SQL: numeric or value error: associative array shape is not consistent with session parameters

During peak usage today, in the newly upgraded 11.5.10.2/10.2.0.3 instance, users were unable to login to E-Business Suite and reported errors like Internal Server Error, or

You have encountered an unexpected error. Please contact the System Administrator for assistance

Following error was logged in jserv.log:

[Oct 31, 2007 10:19:12 AM EDT]:1193840352057:Thread[Thread-1045,10,main]:-1:-1:justanexample.com:192.168.4.1
:8000:16680:UNEXPECTED:[fnd.framework.OAException]:Application: FND, Message Name: FND_GENERIC_MESSAGE. Tokens: MESSAGE = java
.sql.SQLException: ORA-20001: Oracle error -6502: ORA-06502: PL/SQL: numeric or
value error: associative array shape is not consistent with session parameters
has been detected in fnd_global.put(PERMISSION_CODE,FND_PERMIT_0001).
ORA-06512: at "APPS.APP_EXCEPTION", line 72
ORA-06512: at "APPS.FND_GLOBAL", line 240
ORA-06512: at "APPS.FND_GLOBAL", line 428
ORA-06512: at "APPS.FND_GLOBAL", line 447
ORA-06512: at "APPS.FND_GLOBAL", line 2376
ORA-06512: at "APPS.FND_SESSION_MANAGEMENT", line 821
ORA-06512: at "APPS.FND_SESSION_MANAGEMENT", line 1127
ORA-06512: at "APPS.FND_AOLJ_UTIL", line 421
ORA-06512: at line 1

I logged SR 6604364.992 for a possible solution. They suggested a few things but we solved it on our own. Here's the content of the TAR towards the end:

Vikram:

We have not faced the problem again after applying the DB patch 5890966 mentioned in the begining of the TAR. I have also run autoconfig after ensuring the value of s_sesstimeout / 60*1000 does not result in a decimal number. Soft close this SR for 15 days, just in case we get the error again.

- Vikram



19-NOV-07 15:16:00 GMT

(Update for record id(s): 110662639,110663600)

CAUSE DETERMINATION
===================
Upgrade to 10.2.0.3 results in
ORA-20001: Oracle error -6502: ORA-06502: PL/SQL: numeric or
value error: associative array shape is not consistent with session parameters

during peak load

CAUSE JUSTIFICATION
===================
The following justifies how the issue is related to this specific customer:

This is explained in the following bug:
Bug 5890966 - INTERMITTENT ORA-06502 DURING PEAK LOADING - is the cause of this issue on RDBMS 10.2.0.3

Note 455882.1 Expenses Workflow Error: "ORA-06502: PL/SQL: numeric or
value error: associative array shape is not consistent with
session parameters has been detected in fnd_global.
put(CONC_LOGIN_ID,-1)"

Note 436084.1 ORA-6502 Associative Array Shape Is Not Consistent After
Applying Oracle Database Patch

ISSUE CLARIFICATION
===================
-- Problem Statement:
On 11.5.10.2 in Production:
When attempting to login to Apps 11i after upgrade to RUP5 at peak load,
the following error occurs:

ERROR
-----------------------
Internal Server error 500

-- Steps To Reproduce:
The issue can be reproduced at will with the following steps at peak load:
1. Login to Apps 11i after upgrade to RUP5

-- Business Impact:
The issue has the following business impact:
Due to this issue, users cannot login at peak load

19-NOV-07 15:19:04 GMT

(Update for record id(s): 110663613,110663621)

PROPOSED SOLUTION(S)
====================
Apply Patch 5890966 - INTERMITTENT ORA-06502 DURING PEAK LOADING

PROPOSED SOLUTION JUSTIFICATION(S)
==================================
Note 455882.1 Expenses Workflow Error: "ORA-06502: PL/SQL: numeric or
value error: associative array shape is not consistent with
session parameters has been detected in fnd_global.
put(CONC_LOGIN_ID,-1)"

Note 436084.1 ORA-6502 Associative Array Shape Is Not Consistent After
Applying Oracle Database Patch

SOLUTION / ACTION PLAN
======================
-- To implement the solution, please execute the following steps::
Apply Patch 5890966 - INTERMITTENT ORA-06502 DURING PEAK LOADING on top of RDBMS 10.2.0.3



19-NOV-07 15:41:03 GMT

(Update for record id(s): 110665240)

KNOWLEDGE CONTENT
=================
Created a new note: Note 467688.1 Associative Array Shape Is Not Consistent With Session Parameter at Peak Load

Drop invalid java classes after 11.5.10 upgrade

We got two invalid java classes after applying a few patches on top of a freshly upgraded 11.5.10.2 instance:

OBJECT_TYPE OBJECT_NAME
----------------------------------------------------
JAVA CLASS /ae799f28_DirectDepositConcurr
JAVA CLASS /e6bc455_DocumentGenerator

Metalink Note 301345.1 advises dropping them:

Symptoms

The following 2 java classes are invalid after an Oracle Applications 11.5.10 upgrade:
/e6bc455_DocumentGenerator
/ae799f28_DirectDepositConcurr

Cause

These are invalid because CP classes are not loaded into the database,
and concurrent programs that reference them should not be loaded either.

Fix

Drop the invalid java objects:
/e6bc455_DocumentGenerator, and
/ae799f28_DirectDepositConcurr

So issue the following commands and drop them:

SQL> drop java class apps."/ae799f28_DirectDepositConcurr";

Java dropped.

SQL> drop java class apps."/e6bc455_DocumentGenerator";

Java dropped.

Tuesday, October 30, 2007

Log file locations for middle tier in Apps 11i and R12

In Apps 11i, if you have to troubleshoot a problem with Apache, Jserv, JVM the directories to look for log files are:

$IAS_ORACLE_HOME/Apache/Apache/logs
$IAS_ORACLE_HOME/Apache/Jserv/logs
$IAS_ORACLE_HOME/Apache/Jserv/logs/jvm

In R12, the techstack has changed and you have Apache, OC4J and OPMN. These are the directories to look for log files:

$LOG_HOME/ora/10.1.3/Apache
$LOG_HOME/ora/10.1.3/j2ee
$LOG_HOME/ora/10.1.3/opmn

catdwgrd.sql DataBase downgrade from the current release to original release

$ORACLE_HOME/rdbms/admin/catdwgrd.sql is a script used for downgrading your database from the current release you have installed to the release from which you upgraded. If we open this file and go through its contents:

For 11g (11.1.0.6):

BEGIN
-- Get the previous version of the CATPROC component
SELECT prv_version INTO p_prv_version
FROM registry$ WHERE cid='CATPROC';

IF p_prv_version IS NULL THEN
RAISE_APPLICATION_ERROR(-20000,
'Downgrade not supported - database has not been upgraded');
END IF;

IF substr(p_prv_version, 1, 6) NOT IN ('10.1.0', '10.2.0','11.1.0') THEN
RAISE_APPLICATION_ERROR(-20000,
'Downgrade not supported to version ' || p_prv_version );
END IF;


For 10g (10.2.0.3):

BEGIN
-- Get the previous version of the CATPROC component
SELECT prv_version INTO p_prv_version
FROM registry$ WHERE cid='CATPROC';

IF substr(p_prv_version, 1, 5) = '9.2.0' THEN
RETURN '0902000';
ELSIF substr(p_prv_version, 1, 6) = '10.1.0' THEN
RETURN '1001000';
ELSIF substr(p_prv_version, 1, 6) = '10.2.0' THEN
RETURN '1002000';
ELSE
RAISE_APPLICATION_ERROR(-20000,
'Downgrade not supported to version ' || p_prv_version );
END IF;
END version_script;
/

So you can:

Downgrade from 11.1.0.x to 10.2.0 or 10.1.0 or 11.1.0.x
Downgrade from 10.2.0.x to 9.2.0 or 10.1.0 or 10.2.0.x

This file doesn't exist in 9.2.0.x ORACLE_HOME.

Monday, October 29, 2007

Stateful and Stateless connections

From whatis.com:

Stateful and stateless are adjectives that describe whether a computer or computer program is designed to note and remember one or more preceding events in a given sequence of interactions with a user, another computer or program, a device, or other outside element. Stateful means the computer or program keeps track of the state of interaction, usually by setting values in a storage field designated for that purpose. Stateless means there is no record of previous interactions and each interaction request has to be handled based entirely on information that comes with it. Stateful and stateless are derived from the usage of state as a set of conditions at a moment in time. (Computers are inherently stateful in operation, so these terms are used in the context of a particular set of interactions, not of how computers work in general.)

The Internet's basic protocol, the Internet Protocol (IP), is an example of a stateless interaction. Each packet travels entirely on its own without reference to any other packet. When you request a Web page from a Web site, the request travels in one or more packets, each independent of the other as far as the Internet Protocol program itself is concerned. (The upper layer Transmission Control Protocol - TCP - does relate packets to each other, but uses the information within the packet rather than some external information to do this.) The term connectionless is also used to describe communication in which a connection is made and terminated for each message that is sent. IP is connectionless as well as stateless.

The Web's Hypertext Transfer Protocol (HTTP), an application layer above TCP/IP, is also stateless. Each request from a user for a Web page or URL results in the requested pages being served, but without the Web (HTTP) server remembering the request later. In other words, there is no recorded continuity. Each communication is discrete and unrelated to those that precede or follow. In order to have stateful communication, a site developer must furnish a special program that the server can call that can record and retrieve state information. Web browsers such as Netscape Explorer and Microsoft Internet Explorer provide an area in their subdirectories where state information can be stored and accessed. The area and the information that Web browsers and server applications put in this area is called a cookie.

The Internet (including the World Wide Web) can be thought of as a stateless system or machine. Most computers, human beings, and elephants are stateful.

As per metalink note 186981.1: Oracle E-Business Suite Release 11i Self-Service Web Applications are generally stateful. There are a few exceptions in the Customer Relationship Management product family, but Release 11i HTML applications should be assumed to be stateful as the default.

Friday, October 26, 2007

Readme of Oct 2007 CPU DB patch 6395038 (for 9.2.0.8) has incorrect query

Today Sandeep pinged me asking for clarification about a DB patch I had recommended. Step 5 in section 3.3.2 of the readme of Oct2007 CPU DB (9.2.0.8) patch 6395038 reads like this:

This step is required only if your database contains an Identity Management Metadata Repository. If you are not sure whether you need to perform this step, you can you can enter the following query (a non-null result means you should perform the step):

SQL> select USERNAME from dba_users where upper(USERNAME) like '%ODS%';
This returns the result WEBMETHODS in our environment.

As we all know WEBMETHODS has nothing to do with OID or Identity Management Metadata. The Oracle Internet Directory runs on an Oracle database and creates two database users: ODS and ODSCOMMON. ODS is the schema owner that contains all of the database objects (tables, views, objects, etc.) used for OID functionality and directory storage. When the OID needs to login to the database, it uses the ODS database account which has a default password of ODS. You should secure this database user account before putting the LDAP directory into production.

So the correct query should be:

select username from dba_users where upper(username) in ('ODS','ODSCOMMON');

I logged an SR to get this corrected. Oracle was quick in response:

ISSUE CLARIFICATION
====================
Readme for PATCH 6395038 SECTION 3.3.2 POINT 5 includes a very unreliable
method for testing for the presence of OID. The test is to perform the query:

select USERNAME from dba_users where upper(USERNAME) like '%ODS%';

For this customer they have a schema named WEBMETHODS which causes the test
to incorrectly identify the instance as one that contains OID.


.
ISSUE VERIFICATION
===================
Verified

.
CAUSE DETERMINATION
====================
Unclear logic

CAUSE JUSTIFICATION
====================
Unclear logic

.
PROPOSED SOLUTION(S)
======================

Modify the query to be:

select USERNAME from dba_users where upper(USERNAME) like 'ODS';

PROPOSED SOLUTION JUSTIFICATION(S)
====================================
REASON: Any instance which contains OID will have the 'ODS' schema
specifically. Older versions of OID also included the ODS_COMMON schema,
however even in these cases the ODS schema was always present.

.
SOLUTION / ACTION PLAN
=======================
As we are specifically looking for the schema's ODS or ODS_COMMON schemas, we can ignore any result other than these.

.
KNOWLEDGE CONTENT
=================
Platform / Port Specific? = NO

Created a new note: TBC
Created Doc Bug : Bug: 6531776

26-OCT-07 23:50:15 GMT

Vikram,
Documentation bug 6531776 and Metalink Note: 464734.1 have been created for this issue. It may take a few days for the note to be reviewed and become
available.
For clarity, the workaround is to ignore any value returned other than the 2 specific values of 'ODS' and 'ODS_COMMON'.

Eddie

26-OCT-07 23:51:55 GMT

.
UPDATE
=======
Hi Vikram,

I am inactivating this Service Request, as I believe I have provided you with the solution to your issue. If this is not the case, please update the Service Request within the next two weeks and I will be glad
to assist you further. Otherwise, no update is necessary and after two weeks, th
e Service Request will automatically close.

Best Regards,
Eddie
Global Customer Services

27-OCT-07 00:49:02 GMT

Hi Eddie,

Thanks for the quick response. However I have two points:

1. The OID schemas are ODS and ODSCOMMON. You have mentioned the schema name as ODS_COMMON which is again incorrect.
2. Metalink Note 464734.1 which you mention is not visible to me. Maybe you have
classified as internal only.

- Vikram

29-OCT-07 15:55:04 GMT

Vikram,
ODS_COMMON is indeed correct, however it is a LEGACY OID schema and not present with the later versions of OID. As I mentioned the note will be review
ed before becoming available to you.
Eddie

29-OCT-07 15:55:19 GMT

I get zero hits when I query for ODS_COMMON on metalink or google. However I get a lot of hits when I query for ODSCOMMON. Was ODSCOMMON schema called ODS_COMMON in previous versions. If yes, then it is very strange that ODS_COMMON doesn't return anything.

- Vikram

Use FNDLOAD to upload and download apps setup data

Vasu asked me for a way to download a DFF (Descriptive Flex field) into an LDT file. Metalink note 274667.1 has the FNDLOAD Commands to Download Different Seed Data Types. Pasting the contents of the note, just in case it disappears from metalink :)

The Generic Loader (FNDLOAD) is a concurrent program that can transfer Oracle Application entity data between database and text file.The loader reads a configuration file to determine which entity to access.

1 - Printer Styles
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcppstl.lct file_name.ldt STYLE PRINTER_STYLE_NAME="printer style name"

2 - Lookups
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct file_name.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME="FND"
LOOKUP_TYPE="lookup name"

3 - Descriptive Flexfield with all of specific Contexts
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt DESC_FLEX P_LEVEL=’COL_ALL:REF_ALL:CTX_ONE:SEG_ALL’ APPLICATION_SHORT_NAME="FND" DESCRIPTIVE_FLEXFIELD_NAME="desc flex name" P_CONTEXT_CODE="context name"

Examples:
Note <274528.1> - Note How To Download Single Context Using FNDLOAD For Descriptive Flexfield.
Note <256573.1> - How To Download/Upload Descriptive Flexfield With Name $SRS$.

4 - Key Flexfield Structures
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt KEY_FLEX P_LEVEL=’COL_ALL:FQL_ALL:SQL_ALL:STR_ONE:WFP_ALL:SHA_ALL:CVR_ALL:SEG_ALL’ APPLICATION_SHORT_NAME="FND" ID_FLEX_CODE="key flex code" P_STRUCTURE_CODE="structure name"

5 - Concurrent Programs
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct file_name.ldt PROGRAM APPLICATION_SHORT_NAME="FND" CONCURRENT_PROGRAM_NAME="concurrent name"

6 - Value Sets
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt VALUE_SET_VALUE FLEX_VALUE_SET_NAME="value set name"

7 - Value Sets with values
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt VALUE_SET FLEX_VALUE_SET_NAME="value set name"

8 - Profile Options
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct file_name.ldt PROFILE PROFILE_NAME="profile option" APPLICATION_SHORT_NAME="FND"

8 - Request Groups
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcpreqg.lct file_name.ldt REQUEST_GROUP REQUEST_GROUP_NAME="request group" APPLICATION_SHORT_NAME="FND"

10 - Request Sets
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct file_name.ldt REQ_SET
APPLICATION_SHORT_NAME="FND" REQUEST_SET_NAME="request set"

Examples:
Note <247126.1> - How To Download Request Set Definition From One Instance And Upload On Another Instance

11 - Responsibilities
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct file_name.ldt FND_RESPONSIBILITY RESP_KEY="responsibility"

12 - Menus
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct file_name.ldt MENU MENU_NAME="menu_name"

Oracle Application System Administrator's Guide Appendix C has detail description on the usage of loaders.

Apps 11i Environment variables

Before starting services on an Apps 11i instance, a DBA would first source the environment file which is typically the $APPL_TOP/APPSORA.env file. Apart from the 250 product tops, we have some environment variables which are available but not used very often:

CONTEXT_FILE : Gives the complete path of context file. You could do vi $CONTEXT_FILE.

CONTEXT_NAME: SID_hostname. Instead of doing an ls you could go straight to cd $APPLCSF/scripts/$CONTEXT_NAME

FND_SECURE: You could cd directly to FND_SECURE instead of FND_TOP/secure

FORMS60_OUTPUT defaults to $COMMON_TOP/temp

Thursday, October 25, 2007

Oracle Database 11g now available for Windows

While browsing OTN (Oracle technology network), I found that Oracle Database 11g is now available for download. Usually, Oracle comes out with releases for those platforms which are in widespread use. Windows version is usually the last to come out. However, Oracle has made an exception this time and after Linux version, released the Windows version. Versions for the most popular platforms like Solaris, HP-UX, AIX are not yet released. Possibly Windows version has been released first to encourage DBAs to install it on their PCs and play with it. You can download 11g for Windows and Linux here.

Wednesday, October 24, 2007

Impact of version 4 timezone files on E-Business Suite

You all must be aware of version 4 of timezone files. Timezone files introduce new timezones in an Oracle Database and its clients. Recently Rick wanted to know how should we go about ensuring that we have version 4 of timezone files in all the places possible in an E-Business Suite configuration. For an 11i instance that would mean:

1. 9.2.0.x or 10.2.0.x Database ORACLE_HOME
2. 8.1.7 IAS_ORACLE_HOME
3. 8.0.6 Tools ORACLE_HOME
4. Any remote ORACLE_HOME which has a database link or other interface with E-Biz
5. Database clients connecting directly to E-biz database

In Metalink Note 413671.1, Oracle specifically mentions that " For 8.0 and 8.1 (8i) these fixes are not needed. So we can remove 2 and 3 from the above list. That leaves us with

1. 9.2.0.x or 10.2.0.x Database ORACLE_HOME
2. Any remote 9i or 10g ORACLE_HOME which has a database link or other interface with E-Biz
3. Database clients connecting directly to E-biz database.

In metalink notes 417277.1, 400010.1 and 409210.1, it is mentioned that: "future CPUs will contain V3 and V4 timezone files". Incidentally, CPUJan2007, includes US (V3) time zone changes.

That leaves us to ensure that all the bolt-ons which connect to ERP should have the v4 timezone files and if you allow connections from tools like Toad, sqlplus, sqlpal, Oracle SQL Developer, you have to ensure that those clients also install the v4 timezone files. That's a big deal.

Tuesday, October 23, 2007

Increase the display width of fields displayed in mobile devices

When you connect to mobile server through telnet from your PC or through an RF terminal or a mobile device, sometimes the data is not completely visible because the width of display fields can not contain the data. Metalink Note 266976.1: How To Expand Fields Displayed On The Radio Frequency (RF) Terminal / MSCA / Mobile provides a workaround and a solution:

Goal
You observe the display on the Mobile User Interface (UI) terminal only uses half of the screen. You have a 20-digit part number and only 10 characters are displayed on the screen.
Fix
A quick work around can be utilized by doing the following:

1. Using Ctrl-A on a particular field, you can see the complete information.
2. You can then scroll to the right and see the complete data.

A more permanent fix would be to give a larger proportion of the screen width to the field value than the field prompt. By default, the ratio is 1:1, meaning half of the screen width will be set aside for field prompt and the remaining half for the field value.

This can be customized by changing the device configuration file (mwa/11.5.0/secure/default_key.ini). In the device configuration file, there is a property called PROMPT_RATIO. For example, if you like 1/3 of width for the prompt and 2/3 for the value, then you can set the PROMPT_RATIO property to 1:2. For 1/4 of width for prompt and 3/4 for value, set it to 1:3.

Use the following settings in $MWA_TOP/secure/default_key.ini file to increase the default width:

DATASTREAMINDICATOR=28
DEFAULT_WIDTH=40
DEFAULT_HEIGHT=32
DEFAULT_TERM_TYPE=VT100
CHARACTER_SET=UTF8

FNDFS_ and FNDSM _ in tnsnames.ora of 806 home

The tnsnames.ora file of the 806 home has many entries starting with FNDFS_ and FNDSM_.

FNDFS or the Report Review Agent (RRA) is the default text viewer within Oracle Applications, which allows users to view report output and log files. Report Review Agent is also referred to by the executable FNDFS. The default viewer must be configured correctly before external editors or browsers are used for viewing requests. Check out metalink note 111383.1 which describes the Basics About Report Review Agent (FNDFS) on 11i.

FNDSM is the Service manager. Sometimes after cloning you may find that concurrent managers do not start up. Metalink Note 305986.1 describes the scenario:

Symptoms

You have followed instructions within Note 230672.1 - Cloning Oracle Applications Release 11i with Rapid Clone and completed the cloning successfully.

However, now the Concurrent Manager cannot be started. The following error is produced in the Internal Concurrent Manager log file:

Could not start Service Manager FNDSM_hostname.
The TNS alias could not be located, the listener process on hostname could not be contacted, or the listener failed to spawn the Service Manager process.

Cause

This is caused because FNDSM_hostname service is not present in the target instance.

FNDSM database trigger is used to create the FNDIM_ and FNDSM_ concurrent managers. This trigger is present on FND_NODE table. If triggers are disabled in source then issue may occur.
Solution

- Run this following select SQL statements on the target node only to check trigger status:
> SELECT trigger_name , status
FROM user_triggers
WHERE table_name = 'FND_NODES' ;

- If status of the triggers show as DISABLED, then enable these following triggers as follows:
> Connect apps/apps
> alter trigger UPNAME enable;
> alter trigger FNDSM enable;

- Take backup of FND_NODES table in Target.
> create table FND_NODE_bk as select * from FND_NODES ;

- Login into Oracle Application with System Administrator Responsibility.
- Delete the rows from FND_NODES table in target using install -> node -> Define form.
- Then re-Register TRAGET hostname.
- This should register FNDIM_ and FNDSM_ concurrent managers in FND_CONCURRENT_QUEUE Table.

For cluster configuration, when the cluster name is manually inserted in FND_NODES, you can do the following to ensure that FNDFS_ entries are created in tnsnames.ora :

From the Application tier:

1. Log in as applmgr
2. cd to $FND_TOP/patch/115/sql
3. Run the script: afdcm037.sql
4. This script will create libraries for FNDSM and create Managers for Preexisting Nodes.

Note: Service Manager "FNDSM" can not be created from form:
Concurrent> Manager> Define under Sysadmin Responsibility.

Reference Bug 6085070 FNDSM TRIGGER CAUSES SERVICE MANAGER NOT TO BE
CREATED AFTER CLONING SINGLE NODE

Difference between UTF8 and AL32UTF8

UTF8 and AL32UTF8 are encodings of the Unicode character set and include all the characters in all modern languages. UTF8 and AL32UTF8 allow Oracle Applications to be run from one database instance using any combination of supported languages. The advantage of AL32UTF8 over UTF8 is in the handling of supplementary characters, which are increasingly used in certain languages. AL16UTF16 is the current default database character set for Oracle databases 10g and 11g and Oracle E-Business Suite R12.

If you happen to create a fresh Apps Production instance, be sure to select the characterset as AL16UTF16, if all the clients and servers connecting to your instance are 9i and above. This is because 8i databases have trouble connecting to AL32UTF8 databases as per metalink note 237593.1

Caution:

AL32UTF8 is the Oracle Database character set that is appropriate for XMLType data. It is equivalent to the IANA registered standard UTF-8 encoding, which supports all valid XML characters.

Do not confuse Oracle Database database character set UTF8 (no hyphen) with database character set AL32UTF8 or with character encoding UTF-8. Database character set UTF8 has been superseded by AL32UTF8. Do not use UTF8 for XML data. UTF8 supports only Unicode version 3.1 and earlier; it does not support all valid XML characters. AL32UTF8 has no such limitation.

Using database character set UTF8 for XML data could cause a fatal error or affect security negatively. If a character that is not supported by the database character set appears in an input-document element name, a replacement character (usually a question mark) is substituted for it. This will terminate parsing and raise an exception.

Monday, October 22, 2007

Send email through batch file in Windows

Alok sent me a query today. He wanted to know how to send email through a batch file in a windows box. This was for sending an email automatically after the backup with the backup log as the attachment. There are two well known solutions for this. First is a utility called mapisend.exe which ships with Exchange Server Resource Kit. If you have a valid Exchange Server license, you can download it from here. The other option is to use blat which is an open source windows utility for sending mails through commandline available here. Msexchange.org has this great article which describes various ways to send email without a client on windows: by using pickup directory, by mapisend or blat, by using telnet and by using ASP and CDONTS.

Friday, October 19, 2007

How to add a printer in Solaris

Here's a good post on Luo Donghua's oracle and unix blog : How to graphically add a printer in Solaris 8


If you've got a network printer...

From your Solaris system, do this (assumes printer IP is 192.168.1.99):

# lpadmin -p hp3200m -s 192.168.1.99 -I any

Type the following to set the default printer (assuming the printer is hp3200m):

# lpadmin -d hp3200m

You need to be sure that print services are started (/usr/lib/lpsched or the equivalent command using Service Management Facility [SMF] in the Solaris 10 OS).


Thursday, October 18, 2007

ORA-27041 SVR4 Error: 24: Too many open files after 10.2.0.3 upgrade and patch 5257698

As already described in my previous post, after an 11i upgrade, we came across:

ORA-01116: error in opening database file 79

ORA-01110: data file 123:'/example/dbfs/exampledata.dbf'

ORA-27041: unable to open file

SVR4 Error: 24: Too many open files

Additional information: 3

These errors came on running concurrent requests, opening forms, compiling flexfields. You name it. We increased the file descriptors from 256 to 4096 and then to 8192. However the error kept coming. On researching the problem we found bug 5257698 which matched our problem. But we had already applied patch 5257698 as part of the post steps of the upgrade, why did the error still crop up then ? Further research revealed that the patch readme has special instructions of re-running cr9idata.pl if the instance uses 9i NLS data. When I had analyzed this patch, I assumed that since Apps 11i was certified with 10.2.0.3, it won't need any old 9i nls data. I was incorrect in this assumption. According to Metalink Note 376460.1 and the bug 5257698, the old 9i NLS data is needed by Apps 11i:

DIAGNOSTIC ANALYSIS:

Bug is raised with APPS team bug 5248754 who confirmed that reverting to 9i settings is legitimate and necessary since Apps C code will break if we try to use the 10gR2 NLS data.

So we ran the following as per readme:

1. Shutdown the database
2. Re-run the script '$ORACLE_HOME/nls/data/old/cr9idata.pl' as the owner of the ORACLE_HOME directory
3. Set the ORA_NLS10 environment variable to the newly created $ORACLE_HOME/nls/data/9idata directory
4. Restart the database

We are hoping that the issue will be solved after this. However, I have an SR logged with Oracle for this issue, just in case the problem persists. Will update this thread with results in the coming days.

Wednesday, October 17, 2007

Oracle RDBMS 10.2.0.4 patchset expected to be released in Q4 2007

Oracle RDBMS 10.2.0.4 patchset is expected to be released in Q4 2007. Only two months to go before Q4 2007 comes to an end.

Refer Metalink Note 401436.1 to find out what bug fixes are being included in 10.2.0.4

October 2007 CPU note 455294.1 Section 2 link on 11i patches points to July 2007 CPU note

October 2007 CPU patchlist is finally out. They always publish it in the evening of the pre-announced date. So it was during the evening of Oct 16, 2007 when it was released. The metalink note for E-Business Suite patches is 455294.1. I assume that they must be creating each CPU note by a copy paste from previous quarter's CPU note. Notice how the font is different between Section 1 (Arial 12) and Section 2, 3 and 4(Times New Roman 12):

Critical Patch Update Note

Releases 11i and 12

Released: October 16, 2007

Last updated: October 16, 2007

This document includes the following sections:

If you click on Section 2, 3 or 4, it will take you to Note 423882.1 which is the note for July 2007 CPU for E-Business Suite. I realized this when I was reading the note by clicking on Section 2, and I thought strange all the patches are same in July 2007 and Oct 2007. Then I rechecked and found that I was directed to July 2007 CPU note. This is laughable. If you do not use the links in the document and scroll down manually you'll find the correct patches in the note 455294.1, but if you use the hyperlinks, you'll be on the wrong note.

Oracle should correct this. I hope they don't expect someone to open an SR and tell them. :)

ORA-27041 SVR4 Error:24: Too many open files

Recently this error appeared in forms while compiling flexfields:


While compiling the flexfields, the procedure wf_event.raise failed with following parameters:

event_name = oracle.apps.fnd.flex.dff.compiled
event_key = ONT.OE_HEADER_ATTRIBUTES

The error raised is:

ORA-00604: error occurred at recursive SQL level 1
ORA-01116: error in opening database file 959
ORA-01110: data file 959: $ORACLE_HOME/data/exampledbf.dbf
ORA-27041: unable to open file
SVR4 Error: 24: Too many open files
Additional information: 3

This error occurs when the open file descriptors is set low. We can check it with this command:

ulimit -n
256

256 is the default value if nothing is set specifically in the kernel parameters.

To increase the value without rebooting you should do the following

Shutdown all app services and DB services.

Login as root
ulimit -n 8192
su - applmgr
Start all app services
exit
su - oracle
Start all DB services
exit

The following script measures open files at the OS-level :

lsof | perl -lane '$x{"$F[0]:$F[1]"}++;END { print "$x{$_}\t$_" for sort {$x{$a}<=>$x{$b}} keys %x}'

Make sure lsof is in your PATH
Perl can be used from opatch installation.

I am revisiting this article today on August 6, 2009 where this was reported again. We have since moved to Solaris 10 where lsof doesn't work the same way it did in Solaris 8. However this time it was not due to a low ulimit. Solaris 10 employs the mechanism of /etc/project file to control resource allocation based on user and group. You can verify if an entry for your oracle user exists correctly on your box by typing this command:

projects -l

# projects -l
system
projid : 0
comment: ""
users : (none)
groups : (none)
attribs:
user.root
projid : 1
comment: ""
users : (none)
groups : (none)
attribs:
noproject
projid : 2
comment: ""
users : (none)
groups : (none)
attribs:
default
projid : 3
comment: ""
users : (none)
groups : (none)
attribs:
group.staff
projid : 10
comment: ""
users : (none)
groups : (none)
attribs:

user.oracle
projid : 100
comment: "Oracle Database"
users : oracle
groups : (none)
attribs: process.max-file-descriptor=(basic,2048,deny)
project.max-sem-ids=(priv,4098,deny)
project.max-shm-ids=(priv,2048,deny)
project.max-shm-memory=(priv,51539607552,deny)

The error was coming again while compiling flexfields. I checked the project file and found that the entry read like this:

user.oracle11i
projid : 100
comment: "Oracle Database"
users : oracle11i
groups : (none)
attribs: process.max-file-descriptor=(basic,2048,deny)
project.max-sem-ids=(priv,4098,deny)
project.max-shm-ids=(priv,2048,deny)
project.max-shm-memory=(priv,51539607552,deny)

However we did not have a user called oracle11i. The oracle user was called oracle, but the instance name was oracle11i. I took help of Unix team to get the /etc/project file corrected.

The actual line in /etc/project looks very different than the clean output of projects -l command:

user.oracle:118:Oracle Database:oracle::process.max-file-descriptor=(basic,2048,deny);project.max-sem-ids=(priv,4098,deny);project.max-shm-ids=(priv,2048,deny);project.max-shm-memory=(priv,51539607552,deny)

Once it was corrected it looked like this in projects -l:

user.oracle
projid : 100
comment: "Oracle Database"
users : oracle
groups : (none)
attribs: process.max-file-descriptor=(basic,2048,deny)
project.max-sem-ids=(priv,4098,deny)
project.max-shm-ids=(priv,2048,deny)
project.max-shm-memory=(priv,51539607552,deny)

We bounced the database and all services of E-Business Suite. The users retested and confirmed that they were not facing the issue now.

Tuesday, October 16, 2007

ORA-19905: log_archive_format must contain %s, %t and %r

During a go-live when an 11.5.10.2 database upgraded from 9.2.0.7 to 10.2.0.3, as soon as the DBA's turned compatible=10.2.0, the database would not start up. It gave the following error:

ORA-32004: obsolete and/or deprecated parameter(s) specified
ORA-19905: log_archive_format must contain %s, %t and %r

A solution is given in metalink note 283507.1:

Cause

Could not startup the database in mount / nomount stage using Spfile as Spfile had wrong format
for archive log.

Fix

To implement the solution, please execute the following steps:

1. Create a copy of the init.ora file. ( oracle_home/admin/pfile --> init.ora )
2. Edit the above init.ora to reflect the log_archive_format = 'arch_%r_%t_%s.arc'
and log_archive_dest_1 = f:\archive
3. Shutdown immediate and Startup nomount with the above pfile.
SQL> shutdown immediate
SQL> startup nomount pfile = oracle_home/admin/pfile/init.ora

4. Create spfile from pfile
SQL> create spfile = 'oracle_home/database/spfile.ora' from pfile =
'oracle_home/admin/pfile/init.ora'

5. SQL> shutdown immediate
6. SQL> startup
7. SQL> archive log list --- verify that db is in arhivelog mode.

Requirement of this format is documented in:
Administrator's Guide
10g Release 1 (10.1)
Part No. B10739-01
Chapter7 :Managing Archived Redo Logs
---> Specifying Archive Destinations .
For details please refer the above manual.

What are message files in Apps ?

Ever wondered what actually happens when a DBA does Generate Message files through adadmin ? What exactly are message files ?

When you chose the option Generate message files in adadmin, it invokes Message Dictionary Generator program (FNDMDGEN) and generates language.msb files in Product_top/mesg directory.

FNDMDGEN apps/appspword 0 Y language application_short_name DB_TO_RUNTIME
For example:
$ FNDMDGEN apps/apps 0 Y US PER DB_TO_RUNTIME
where Language=US and Application Short Name=PER
The FNDMDGEN program will generate a message file named Language.msb in place it in the custom applications basepath mesg directory.

For custom programs, Message Dictionary lets you catalog messages for display from your application without hard coding them into your forms and programs.

Using Message Dictionary, you can:

  • Define standard messages you can use in all your applications
  • Provide a consistent look and feel for messages within and across all your applications
  • Define flexible messages that can include context–sensitive variable text
  • Change or translate the text of your messages without regenerating or recompiling your application code.
What DBAs do from backend through adadmin - Generate Message Files, can be done through
the front end by:

Responsibility: System Administrator
Submit the concurrent process : Generate Messages

Monday, October 15, 2007

rlogin, rsh and rcp

How do you set up two boxes, so that you can copy files through rcp command ?

Short answer:

0. Ensure that rsh daemon is running on OS level.
1. Ensure that server1 has an entry in /etc/hosts of server2 and vice versa.
2. Ensure that $HOME/.rhosts file of the unix user you are using to do rcp has an entry like this:

In server1

hostname_of_server2 unix_user_id_used_for_rcp

In server2

hostname_of_server1 unix_user_id_used_for_rcp

That's it. You are all set to do rcp, rlogin

Friday, October 12, 2007

Oracle Acquisitions

50 Acquisitions till date.  Primary information is from wikipedia's Oracle Corporation page.

Company  ↓Date  ↓Industry  ↓Valuation
millions USD  ↓
Thinking Machines CorporationJune 1999Darwin, Datamining technologyN/A
ToplinkJanuary 2002Object relation mapping technologyN/A
NetForceJanuary 2002Adverse Event Reporting SystemN/A
SteltorJune 2002Enterprise calendaring systemN/A
PhaosMay 2004Identity ManagementN/A
CollaxaJune 2004Business Process ManagementN/A
PeopleSoftJanuary 2005Enterprise Software$10,300
OblixMarch 2005Identity ManagementN/A
RetekApril 2005Retail Industry Solutions$630
TripleHopJune 2005Context-sensitive Enterprise SearchN/A
TimesTenJune 2005Real-time Enterprise SolutionsN/A
ProfitLogicJuly 2005Retail Industry SolutionsN/A
Context MediaJuly 2005Enterprise Content IntegrationN/A
i-flex (Oracle Financial Services)August 2005Banking Industry Solutions$900
G-LogSeptember 2005Transportation Management SolutionsN/A
InnobaseOctober 2005Discrete Transactional Open Source Database TechnologyN/A
Thor TechnologiesNovember 2005Enterprise-wide User Provisioning Solutions.N/A
OctetStringNovember 2005Virtual Directory SolutionsN/A
TemposoftDecember 2005Workforce Management ApplicationsN/A
360CommerceJanuary 2006Retail Industry SolutionsN/A
Siebel SystemsJanuary 2006Customer Relationship Management Solutions$5,850
SleepycatFebruary 2006Open Source Database Software for Embedded ApplicationsN/A
HotSipFebruary 2006Communications Infrastructure SolutionsN/A
Portal SoftwareApril 2006Communications Industry Software Suite$220
Net4CallApril 2006Communications Industry Service Delivery PlatformN/A
DemantraJune 2006Demand-driven Planning SolutionsN/A
Telephony@WorkJune 2006IP-based Contact Center TechnologyN/A
Sigma DynamicsAugust 2006Real-time Predictive Analytics SoftwareN/A
SunopsisOctober 2006Enterprise Integration SoftwareN/A
MetaSolv SoftwareOctober 2006Communications Service Providers Solutions$219
StellentNovember 2006Content Management Solutions$440
SPL WorldGroupNovember 3, 2006Revenue and Operations Management SoftwareN/A
Hyperion SolutionsMarch 1, 2007Enterprise Performance Management$3,300
AppForge
(intellectual assets only)
April 2007Cross-platform handheld developmentN/A
Agile Software CorporationMay 15, 2007Product Life Cycle Management Software$495
BharosaJuly 18, 2007Identify Theft$49.5
NetSure Telecom Ltd.September 2, 2007Network intelligence and optimization softwareUndisclosed
BridgestreamSeptember 5, 2007Enterprise Role ManagementN/A
LogicalAppsOctober 9, 2007Compliance softwareN/A
MoniforceDecember 6, 2007End-user experience management softwareN/A
BEA SystemsJanuary 16, 2008Middleware software Company$8,500
CaptovationJanuary 16, 2008Document capture softwareN/A
Empirix (Web)March 27, 2008Web application testing softwareN/A
LODESTAR CorporationApril 24, 2007Utility software solutionsN/A
AdminServerMay 13, 2008Insurance policy administration softwareN/A
Skywire SoftwareJune 23, 2008Insurance softwareN/A
Global Knowledge SoftwareJuly 31, 2008Technical Writing/Training Authoring softwareN/A
ClearAppSeptember 2, 2008Application management solutions for composite applications softwareN/A
Primavera SystemsOctober 9, 2008Project Portfolio Management solutions softwareN/A
Haley LimitedOctober 29, 2008Policy modeling and automation softwareN/A