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
Wednesday, October 31, 2007
ORA-06502: PL/SQL: numeric or value error: associative array shape is not consistent with session parameters
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:
/e6bc455_DocumentGenerator
/ae799f28_DirectDepositConcurr
and concurrent programs that reference them should not be loaded either.
/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.
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
$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.
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.
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:
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
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.
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
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.
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
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
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
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:
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.
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: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
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 database2. 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
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):
Oracle should correct this. I hope they don't expect someone to open an SR and tell them. :)
Critical Patch Update Note
Releases 11i and 12
Released: October 16, 2007
This document includes the following sections:
- Section 1, "Oracle E-Business Suite Release 12"
- Section 2, "Oracle E-Business Suite Release 11i"
- Section 3, "Document Modification History"
- Section 4, "Documentation Accessibility"
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.
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:
for archive log.
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.
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 formatfor 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
'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.
the front end by:
Responsibility: System Administrator
Submit the concurrent process : Generate Messages
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.
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
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 Corporation June 1999 Darwin, Datamining technology N/A Toplink January 2002 Object relation mapping technology N/A NetForce January 2002 Adverse Event Reporting System N/A Steltor June 2002 Enterprise calendaring system N/A Phaos May 2004 Identity Management N/A Collaxa June 2004 Business Process Management N/A PeopleSoft January 2005 Enterprise Software $10,300 Oblix March 2005 Identity Management N/A Retek April 2005 Retail Industry Solutions $630 TripleHop June 2005 Context-sensitive Enterprise Search N/A TimesTen June 2005 Real-time Enterprise Solutions N/A ProfitLogic July 2005 Retail Industry Solutions N/A Context Media July 2005 Enterprise Content Integration N/A i-flex (Oracle Financial Services) August 2005 Banking Industry Solutions $900 G-Log September 2005 Transportation Management Solutions N/A Innobase October 2005 Discrete Transactional Open Source Database Technology N/A Thor Technologies November 2005 Enterprise-wide User Provisioning Solutions. N/A OctetString November 2005 Virtual Directory Solutions N/A Temposoft December 2005 Workforce Management Applications N/A 360Commerce January 2006 Retail Industry Solutions N/A Siebel Systems January 2006 Customer Relationship Management Solutions $5,850 Sleepycat February 2006 Open Source Database Software for Embedded Applications N/A HotSip February 2006 Communications Infrastructure Solutions N/A Portal Software April 2006 Communications Industry Software Suite $220 Net4Call April 2006 Communications Industry Service Delivery Platform N/A Demantra June 2006 Demand-driven Planning Solutions N/A Telephony@Work June 2006 IP-based Contact Center Technology N/A Sigma Dynamics August 2006 Real-time Predictive Analytics Software N/A Sunopsis October 2006 Enterprise Integration Software N/A MetaSolv Software October 2006 Communications Service Providers Solutions $219 Stellent November 2006 Content Management Solutions $440 SPL WorldGroup November 3, 2006 Revenue and Operations Management Software N/A Hyperion Solutions March 1, 2007 Enterprise Performance Management $3,300 AppForge
(intellectual assets only)April 2007 Cross-platform handheld development N/A Agile Software Corporation May 15, 2007 Product Life Cycle Management Software $495 Bharosa July 18, 2007 Identify Theft $49.5 NetSure Telecom Ltd. September 2, 2007 Network intelligence and optimization software Undisclosed Bridgestream September 5, 2007 Enterprise Role Management N/A LogicalApps October 9, 2007 Compliance software N/A Moniforce December 6, 2007 End-user experience management software N/A BEA Systems January 16, 2008 Middleware software Company $8,500 Captovation January 16, 2008 Document capture software N/A Empirix (Web) March 27, 2008 Web application testing software N/A LODESTAR Corporation April 24, 2007 Utility software solutions N/A AdminServer May 13, 2008 Insurance policy administration software N/A Skywire Software June 23, 2008 Insurance software N/A Global Knowledge Software July 31, 2008 Technical Writing/Training Authoring software N/A ClearApp September 2, 2008 Application management solutions for composite applications software N/A Primavera Systems October 9, 2008 Project Portfolio Management solutions software N/A Haley Limited October 29, 2008 Policy modeling and automation software N/A
Subscribe to:
Posts (Atom)
Posts
