Sunday, December 15, 2013

ORA errors & solutions


Purpose

Troubleshooting Steps
 Scenario 1: Oracle V$PARAMETER Open Cursors Issue with EnterpriseOne
 Scenario 2: "OCI0000375 - Error - ORA-12154" When Starting EnterpriseOne Service After Changing Database Version to Oracle 11g
 Scenario 3: Case Study on ORA-01722 Wrong Bind Variables Cause Transaction Failure
 Scenario 4: ORA-01536 Error When Trying to Run a UBE
 Scenario 5: Installation Workbench Fails With Error "OCI0000367 - Unable to connect to Oracle ORA-12154: TNS:could not resolve the connect identifier specified"
 Scenario 6: ORA-04030 Out of Process Memory Error Message for Oracle 10GR2
 Scenario 7: ORA-00942 Error Running an EnterpriseOne Application
 Scenario 8: Intermittent Error "ORA-28547: Connection to server failed" in the Enterprise Server Logs
 Scenario 9: Passwords Expires by Default In Oracle 11g
 Scenario 10: Unable to connect to Oracle ORA-12705: invalid or unknown NLS parameter value specified
 Scenario 11: ORA-01033 ORACLE initialization or shutdown in progress

References

Applies to:

JD Edwards EnterpriseOne Tools - Version S24 to 9.1 [Release S24 to 9.1]
Information in this document applies to any platform.

Purpose

The purpose of this document is to provide troubleshooting tips when encountering ORA-NNNN errors with EnterpriseOne.  The following ORA errors are covered in this document:
  • ORA-00942: table or view does not exist
  • ORA-12154: TNS:could not resolve the connect identifier specified
  • ORA-01722: invalid number
  • ORA-01536: space quota exceeded for tablespace
  • ORA-12154: TNS:could not resolve the connect identifier specified
  • ORA-04030: out of process memory when trying to allocate xxxxx bytes
  • ORA-00942: table or view does not exist
  • ORA-28547: connection to server failed, probable Oracle Net admin error
  • ORA-12705: Unable to connect to Oracle ORA-12705: invalid or unknown NLS parameter value specified
  • ORA-01033: ORACLE initialization or shutdown in progress

Troubleshooting Steps

Scenario 1: Oracle V$PARAMETER Open Cursors Issue with EnterpriseOne

You may receive two messages when logging into EnterpriseOne/OneWorld that does not have an apparent effect on the actual ability to login.  The messages are:

Mar 10 10:26:29.104 - 1752/1340 OCI0000339 - Unable to retrieve the open_cursors setting
Mar 10 10:26:29.114 - 1752/1340 OCI0000340 - Error - ORA-00942: table or view does not exist

Solution
Oracle has internal views called V$* which provide information about the database itself.

Currently SELECT permissions are required for V$PARAMETER and V$SESSION.  We specify "select any dictionary" to support these two and any others that may be required in the future.

V$PARAMETER is queried to determine the number of open cursors each connection supports.  If the EnterpriseOne driver cannot query that view, it defaults to 255.  If customers choose to forbid access to the view, they must make sure the database is set up for at least 255 open cursors, otherwise they may get an error about exceeding the number of cursors.

Scenario 2: "OCI0000375 - Error - ORA-12154" When Starting EnterpriseOne Service After Changing Database Version to Oracle 11g

The database is upgraded from Oracle 10g to Oracle 11g (64bit) while remaining on the same machine.  Both databases are installed in different ORACLE homes; where JDETRNG is the connect string for the Oracle 10g Instance and JDETRGDB is the connect string for the Oracle 11g instance.

The Enterprise server and database are running on the same machine and the platform for the server is Red Hat Enterprise Linux 5 Update 5 64 bit.  Oracle 11g 32-bit Client is installed for the 32 bit libraries required by EnterpriseOne to connect to the 64-bit database.

When starting EnterpriseOne service against the new database instance, the system still tries to connect to the previous Oracle 10g instance.  The following messages appear in the Server Security Kernel log:
8429/-156543280 MAIN_THREAD Wed Mar 21 17:43:42.997158 jdeksec.c762
INITIALIZING SECURITY SERVER KERNEL

8429/-156543280 MAIN_THREAD Wed Mar 21 17:43:43.148446 dbinitcn.c745
OCI0000374 - Failed to connect to the database instance

8429/-156543280 MAIN_THREAD Wed Mar 21 17:43:43.148535 dbinitcn.c750
OCI0000375 - Error - ORA-12154: TNS:could not resolve the connect identifier specified

8429/-156543280 MAIN_THREAD Wed Mar 21 17:43:43.148575 dbinitcn.c427
OCI0000064 - Unable to create database server connection

8429/-156543280 MAIN_THREAD Wed Mar 21 17:43:43.148640 dbinitcn.c520
OCI0000367 - Unable to connect to Oracle ORA-12154: TNS:could not resolve the connect identifier specified

8429/-156543280 MAIN_THREAD Wed Mar 21 17:43:43.148705 jdb_drvm.c815
JDB9900164 - Failed to connect to JDETRNG

8429/-156543280 MAIN_THREAD Wed Mar 21 17:43:43.148744 jtp_cm.c282
JDB9909003 - Could not init connect.

The Security Kernel debug log shows the following messages:
Mar 21 17:43:43.147311 dbsql_s.c1825 - 8429/-156543280 MAIN_THREAD SELECT * FROM SVM900.F98611 WHERE ( OMDATP = 'System - 900 ' ) ORDER BY OMDATP ASC
Mar 21 17:43:43.147555 jdb_exef.c2521 - 8429/-156543280 MAIN_THREAD Exiting JDB_SelectKeyed with Success
Mar 21 17:43:43.147582 dbfetch.c107 - 8429/-156543280 MAIN_THREAD ORACLE DBFetch conn=09dfe628 requ=09e32008 maxrows=86
Mar 21 17:43:43.147646 jdb_exef.c4707 - 8429/-156543280 MAIN_THREAD Fetched the record
Mar 21 17:43:43.147665 jdb_exef.c4639 - 8429/-156543280 MAIN_THREAD No More Data found
Mar 21 17:43:43.147673 jdb_rq1.c5380 - 8429/-156543280 MAIN_THREAD Entering JDB_CloseTable (hRequest 09e2dc00)
Mar 21 17:43:43.147682 jdb_rq1.c5495 - 8429/-156543280 MAIN_THREAD Entering JDB_CloseTable(Table = F98611)
Mar 21 17:43:43.147691 jdb_rqst.c1224 - 8429/-156543280 MAIN_THREAD Entering JDB_ClearSequencing (hRequest 09e2dc00)
Mar 21 17:43:43.147699 jdb_rqst.c1317 - 8429/-156543280 MAIN_THREAD Exiting JDB_ClearSequencing with Success
Mar 21 17:43:43.147752 jdb_rqst.c2854 - 8429/-156543280 MAIN_THREAD Entering JDB_ClearGroupBy (hRequest 09e2dc00)
Mar 21 17:43:43.147760 jdb_rqst.c3012 - 8429/-156543280 MAIN_THREAD Exiting JDB_ClearGroupBy with Success
Mar 21 17:43:43.147771 dbfreerq.c97 - 8429/-156543280 MAIN_THREAD ORACLE DBFreeReq conn=09dfe628 requ=09e32008 CLOSE
Mar 21 17:43:43.147780 jdb_exet.c9031 - 8429/-156543280 MAIN_THREAD Entering JDB_ClearBuffers (hRequest 09e2dc00)

........

Mar 21 17:43:43.147877 jdb_rqst.c2414 - 8429/-156543280 MAIN_THREAD Entering JDB_ClearAggregate (hRequest 09d9d970)
Mar 21 17:43:43.147936 jdb_rq1.c14590 - 8429/-156543280 MAIN_THREAD Decremented table handle count to 0
Mar 21 17:43:43.147946 jdb_rq1.c5831 - 8429/-156543280 MAIN_THREAD Exiting JDB_CloseTable(Table = F986101) with Success
Mar 21 17:43:43.147954 jdb_rq1.c5833 - 8429/-156543280 MAIN_THREAD Exiting JDB_CloseTable with Success
Mar 21 17:43:43.148028 jdb_rq1.c1158 - 8429/-156543280 MAIN_THREAD Entering JDB_OpenTable(Table = F95921)
Mar 21 17:43:43.148088 jdecsec.c362 - 8429/-156543280 MAIN_THREAD Try to fetch proxy info from cache
Mar 21 17:43:43.148418 dbinitcn.c745 - 8429/-156543280 MAIN_THREAD OCI0000374 - Failed to connect to the database instance
Mar 21 17:43:43.148516 dbinitcn.c750 - 8429/-156543280 MAIN_THREAD OCI0000375 - Error - ORA-12154: TNS:could not resolve the connect identifier specified
Mar 21 17:43:43.148566 dbinitcn.c427 - 8429/-156543280 MAIN_THREAD OCI0000064 - Unable to create database server connection
Mar 21 17:43:43.148617 dbinitcn.c502 - 8429/-156543280 MAIN_THREAD ORACLE DBInitCon envr=09d2b478 conn=09e69430 01hw308984 A (JDE@JDETRNG) V0 Unicode=Y (srvrusers=0) 2
Mar 21 17:43:43.148633 dbinitcn.c520 - 8429/-156543280 MAIN_THREAD OCI0000367 - Unable to connect to Oracle ORA-12154: TNS:could not resolve the connect identifier specified
Mar 21 17:43:43.148694 jdb_drvm.c815 - 8429/-156543280 MAIN_THREAD JDB9900164 - Failed to connect to JDETRNG
Mar 21 17:43:43.148735 jtp_cm.c282 - 8429/-156543280 MAIN_THREAD JDB9909003 - Could not init connect.
Cause
When analyzing the debug log, the system is attempting to connect to the Server Map's datasource (SVM900.F98611) but fails:
Mar 21 17:43:43.147311 dbsql_s.c1825 - 8429/-156543280 MAIN_THREAD SELECT * FROM SVM900.F98611 WHERE ( OMDATP = 'System - 900 ' )
Found that the Server Map data source is still pointing to Oracle 10g instance.
Solution
To resolve, update all the database datasources for the Server Map datasource (SVM900.F98611) with the Oracle connect string that points to the 11g instance.

When updating/changing the database instance, it is necessary to verify the Connect String in the following locations:
  1. Enterprise Server JDE.INI:
    Verify the parameter "Database" in the [DB SYSTEM SETTINGS] section. This needs to point to correct Oracle Connect String for your configuration.  This including case-sensitivity.

    [DB SYSTEM SETTINGS]
    Database=
  2. The database instance tnsnames.ora file and 32-bit Client:
    Verify the tnsnames.ora for the database instance and make sure the Connect String is defined correctly.
    Configure the tnsnames.ora for the 32-bit Oracle Client Installed for communication between EnterpriseOne and the database, with correct Connect String. To avoid any issue's with tnsnames.ora, copy the file from the database instance to the Oracle 32-bit client location.
  3. Database Datasource's for System Datasource (SY900.F98611):
    Update all the database datasources for System datasource with the new Oracle connect string.  Again, including case-sensitivity.
  4. Specific to LINUX/UNIX platform:
    Verify the Environment Variable "ORACLE_SID" in the enterpriseone.sh file. This needs to point to the correct Oracle Connect String for your configuration.

Scenario 3: Case Study on ORA-01722 Wrong Bind Variables Cause Transaction Failure

This document is a case study for error code: ORA-01722 in an Oracle Database.  When running R42565 (Print Invoice), the following error occurs:
UPDATE PPDTA.F47046 SET SYPNID='XXXXXX',SYVR02=' ',SYVR03=' ' WHERE ( SYEDOC = 99999.000000 AND SYEDCT = 'SO' AND SYEKCO = '00001' )
OCI0000178 - Unable to execute - UPDATE PRODDTA.F47046 SET SYPNID=:BND1,SYVR02=:BND2,SYVR03=:BND3 WHERE ( SYEDOC = :KEY1 AND SYEDCT = :KEY2 AND SYEKCO = :KEY3 )
OCI0000179 - Error - ORA-01722: invalid number
JDB9900401 - Failed to execute db request
JDB3600011 - Failed to perform Update for F47046
 

Solution
Fix third party interface(s) if any.  In this case study, the error is caused by 3rd party software which returns different data type than the data type supported by EnterpriseOne.
To troubleshoot this issue:

  1. Verify whether the standard (EnterpriseOne) routine issues the same query
  2. If the query is different from the standard routine, check your customized code
  3. Delete TAMSpec DDDICT and GLBLTBL files so they JITI from Central Objects (this process can correct a data type issue if they are corrupt)
From the Update statement above, the columns PNID (TradingPartnerId), VR02 (Reference2Vendor) and VR03 (ReferenceUCISNo) are updated.
The standard routine from E1 should write the following Update statement.  (Notice that it should only update one column "SYEDDL"):
sql> UPDATE PPDTA.F47046 SET SYEDDL = 1234 WHERE ( SYEDOC = 99999.000000 AND SYEDCT = 'SO' AND SYEKCO = '00001' );
(In this case "1234" is total number of lines written to detail file F47047 (EDI Invoice Detail (Sales) - Outbound)
This indicates a customization has been done to the JDE standard routine.
Below is some additional information that can help to further understand this error:
ORA-01722: invalid number
Cause: The attempted conversion of a character string to a number failed because the character string was not a valid numeric literal. Only numeric fields or character fields containing numeric data may be used in arithmetic functions or expressions. Only numeric fields may be added to or subtracted from dates.
Action: Check the character strings in the function or expression. Check that they contain only numbers, a sign, a decimal point, and the character "E" or "e" and retry the operation

Considerations: ORA-01722 error may exist until Oracle Database shared pool is flushed.

Bind Variable:
Each time the query is submitted, Oracle (RDBMS) first checks in the shared pool to see whether this statement has been submitted before. If any, the execution plan that this statement previously used is retrieved, and the SQL is executed. If the statement cannot be found in the shared pool, Oracle has to go through the process of parsing the statement (translate statement which Database can understand), working out the various execution paths and coming up with an optimal access plan before it can be executed. This process is known as a hard parse and for OLTP (On-line Transaction Processing) applications can actually take longer to carry out that the DML instruction itself.
If Oracle Database fails to find exactly matching query, the sql statement has to be hard parsed. Hard parsing is very CPU intensive, and involves obtaining latches on key shared memory areas.
Oracle Database will reuse the execution plans for these SQL statements using bind variables. Bind variables are substitution variables that are used in place of literals and that have the effect of sending exactly the same SQL statement to Oracle Database every time the query is executed.
The benefit is that any routine would be able to reuse the execution plan every time, reducing the latch activity in the SGA (so it requires less system resources).
   
Notes:
    • Any RDBMS platform supports bind variable. To get detailed information on this, please contact your vendor for your database system.
    • In this case JDE is not responsible for binding (or bind variables) because JDE connects to the database through 3rd party APIs (OCI, ODBC, JDBC and so on). The API itself passes the bind variable value to Oracle (or SQL) at runtime based on SQL statement constructed. In other words, JDE never passes bind variables directly to the DB. So this type of error is very unlikely to be caused by JDE code.
    • Note that this document is not to elaborate what bind variables are and what equivalent terms in other RDBMS platform (or database servers).

Example of Trace File: (The following is a sample tace file)
=====================
PARSING IN CURSOR #82 len=128 dep=0 uid=59 oct=6 lid=59 tim=3504794486531 hv=2282568797 ad='965ab548'
UPDATE PRODDTA.F47046  SET SYPNID=:BND1,SYVR02=:BND2,SYVR03=:BND3 WHERE  ( SYEDOC = :KEY1 AND SYEDCT = :KEY2 AND SYEKCO = :KEY3 )
END OF STMT
PARSE #82:c=0,e=601,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=3504794486528
=====================
BINDS #82:
kkscoacd
Bind#0
  oacdty=96 mxl=32(32) mxlc=00 mal=00 scl=00 pre=00
  oacflg=01 fl2=1000010 frm=02 csi=2000 siz=376 off=0
  kxsbbbfp=9fffffffbf272628  bln=32  avl=02  flg=05
  value=""
Bind#1
  oacdty=96 mxl=128(52) mxlc=00 mal=00 scl=00 pre=00
  oacflg=01 fl2=1000010 frm=02 csi=2000 siz=0 off=32
  kxsbbbfp=9fffffffbf272648  bln=128  avl=02  flg=01
  value=""
Bind#2
  oacdty=96 mxl=128(52) mxlc=00 mal=00 scl=00 pre=00
  oacflg=01 fl2=1000010 frm=02 csi=2000 siz=0 off=160
  kxsbbbfp=9fffffffbf2726c8  bln=128  avl=02  flg=01
  value=""
Bind#3
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=01 fl2=1000000 frm=00 csi=00 siz=0 off=288
  kxsbbbfp=9fffffffbf272748  bln=22  avl=04  flg=01
  value=99999
Bind#4
  oacdty=96 mxl=32(06) mxlc=00 mal=00 scl=00 pre=00
  oacflg=01 fl2=1000010 frm=02 csi=2000 siz=0 off=312
  kxsbbbfp=9fffffffbf272760  bln=32  avl=04  flg=01
  value=""
Bind#5
  oacdty=96 mxl=32(12) mxlc=00 mal=00 scl=00 pre=00
  oacflg=01 fl2=1000010 frm=02 csi=2000 siz=0 off=344
  kxsbbbfp=9fffffffbf272780  bln=32  avl=10  flg=01
  value=""
EXEC #82:c=30000,e=35167,p=10,cr=111,cu=3,mis=1,r=0,dep=0,og=1,tim=3504794521792
ERROR #82:err=1722 tim=358890959
Note: The above trace file reads that database failed to parse input string value (only numeric value was successfully parsed). It is not able to recognize string values as the above shows that it is not able to return the value for Bind#0,  Bind#1, Bind#2, Bind#4 and Bind#5. Only numeric value '99999' is return for Bind#3.

Scenario 4: ORA-01536 Error When Trying to Run a UBE

EnterpriseOne 9.0 with Tools Release 8.98.3.1 runs on an AIX 5.3 Enterprise Server with an Oracle 10g database.  The JAS Server has Oracle Application Server 10.1.3.4 installed on an AIX Server.  Users can login to E1, but they cannot run UBES from an HTML/web or FAT client in any environment, including production.

E1 services have been bounced, a new full package built and deployed for production, but the issue persists.  PORTTEST runs with no issue for the production environment.

When a UBE is submitted, E1 tries to update table F986110 (Job Control Master) but an ORA- 01536 error appears in Queue Kernel log.  The error does not allow the update to complete and the UBE fails to run:


544812/1 MAIN_THREAD Mon Nov 8 12:16:51.778631 queueknl.c518
INITIALIZING QUEUE KERNEL

544812/1 MAIN_THREAD Mon Nov 8 12:16:52.731696 dbperfrq.c471
OCI0000178 - Unable to execute - UPDATE SVM900A.F986110 SET JCJOBQUE=:BND1,JCJOBPTY=:BND2,JCJOBSTS=:BND3,JCENHV=:BND4,JCUSER=:BND5,JCPSWD=:BND6,JCJOBTYPE=:BND7,JCSBMDATE=:BND8,JCSBMTIME=
:BND9,JCJOBNBR=:BND10,JCEXEHOST=:BND11,JCORGHOST=
:BND12,JCPROCESSID=:BND13,JCACTDATE=:BND14,JCACTTIME=:BND15,JCSRVBLOBA=:BND16,
JCFUNO=:BND17,JCPRTQ=:BND18,JCFNDFUF1=:BND19,JCFNDFUF2=:BND20 WHERE ( JCEXEHOST = :KEY1 AND JCJOBNBR = :KEY2 )

544812/1 MAIN_THREAD Mon Nov 8 12:16:52.731773 dbperfrq.c477
OCI0000179 - Error - ORA-01536: space quota exceeded for tablespace 'SVM900T'

544812/1 MAIN_THREAD Mon Nov 8 12:16:52.731819 jdb_drvm.c1128
JDB9900401 - Failed to execute db request

544812/1 MAIN_THREAD Mon Nov 8 12:16:52.731876 jdb_exet.c3215
JDB3600011 - Failed to perform Update for F986110

544812/1 MAIN_THREAD Mon Nov 8 12:16:52.732081 queueknl.c1015
QUEUE00600060-Failed to update job R0006P_XJDE0003_34_PDF to S status. Closing F986110 handle.

544812/1 MAIN_THREAD Mon Nov 8 12:16:52.732385 jdb_exef.c4880
JDB3300006 - Failed to validate request handle

544812/1 MAIN_THREAD Mon Nov 8 12:16:52.732441 queueknl.c987
QUEUE00600072-Job hq520j2 _6015 _QBATCH _R0006P_XJDE0003_34_PDF not found in F986110 - removing from Waitlist.

544812/1 MAIN_THREAD Mon Nov 8 12:21:55.433335 dbperfrq.c471
OCI0000178 - Unable to execute - UPDATE SVM900A.F986110 SET JCJOBQUE=:BND1,JCJOBPTY=:BND2,JCJOBSTS=:BND3,JCENHV=:BND4,JCUSER=:BND5,JCPSWD=:BND6,JCJOBTYPE=:BND7,JCSBMDATE=:BND8,JCSBMTIME=
:BND9,JCJOBNBR=:BND10,JCEXEHOST=:BND11,JCORGHOST=
:BND12,JCPROCESSID=:BND13,JCACTDATE=:BND14,JCACTTIME=:BND15,JCSRVBLOBA=:BND16,
JCFUNO=:BND17,JCPRTQ=:BND18,JCFNDFUF1=:BND19,JCFNDFUF2=:BND20 WHERE ( JCEXEHOST = :KEY1 AND JCJOBNBR = :KEY2 )


Using Oracle Enterprise Manager it shows information for the SVM900T tablespace:
Allocated Size (MB) = 9,000.0
Space Used (MB) = 3.6
Allocated Free Space: 8,996.4
Segment Management = AUTO
Block Size (B) = 8192
The above indicates no issue with the tablespace. 

Solution
Issue the ALTER command with a privileged user to extend the tablespace quota to unlimited for the user that owns the table:


alter user <USER> quota unlimited on <TABLESPACE_NAME>

In this case, SVM900A is the owner of table F986110 so the command will look like:

alter user SVM900A quota unlimited on SVM900T;

Scenario 5: Installation Workbench Fails With Error "OCI0000367 - Unable to connect to Oracle ORA-12154: TNS:could not resolve the connect identifier specified"

The Installation Workbench fails with error:
3428/3472 MAIN_THREAD Fri Mar 25 13:42:27.699000 Jdb_ctl.c4050
Starting OneWorld

3428/3464 WRK:Starting jdeCallObject Fri Mar 25 13:43:10.699001 dbinitcn.c745
OCI0000374 - Failed to connect to the database instance

3428/3464 WRK:Starting jdeCallObject Fri Mar 25 13:43:10.699002 dbinitcn.c750
OCI0000375 - Error - ORA-12154: TNS:could not resolve the connect identifier specified

3428/3464 WRK:Starting jdeCallObject Fri Mar 25 13:43:10.699003 dbinitcn.c427
OCI0000064 - Unable to create database server connection

3428/3464 WRK:Starting jdeCallObject Fri Mar 25 13:43:10.699004 dbinitcn.c520
OCI0000367 - Unable to connect to Oracle ORA-12154: TNS:could not resolve the connect identifier specified

3428/3464 WRK:Starting jdeCallObject Fri Mar 25 13:43:10.699005 Jdb_drvm.c936
JDB9900164 - Failed to connect to orcl

Where "orcl" is the name of the backend database.
Cause
 The tnsnames.ora file does not have an entry with a connect descriptor of "orcl".

This system has an Oracle backend database.  There is an Oracle Home installed on the Deployment server that is used to connect to the Oracle backend database using Oracle's TNS.  The connect information is in the tnsnames.ora file that is located under $ORACLE_HOME\network\admin.  This file contains entries for each database you will connect to using the SQLPlus install under this Oracle home.  The tnsnames.ora file does not have an entry with connect descriptor named "orcl".
Solution
Copy the "orcl" entry out of the tnsnames.ora file on the backend database and paste it in to the tnsnames.ora file for the Oracle home on the Deployment server.

Nothing needs to be restarted. Just paste in the entry and then save changes.  This is a dynamic change which is immediately active.

Scenario 6: ORA-04030 Out of Process Memory Error Message for Oracle 10GR2

Users are experiencing Oracle 10GR2 memory issues on a Windows 2003 Enterprise Server.  The errors are found in Oracle logs.

ERRORS:

Oracle Alert Log:

Fri Dec 14 09:26:20 2007
Errors in file d:\oracle\admin\e1db\udump\e1db_ora_5908.trc:
ORA-00600: internal error code, arguments: [12761], [], [], [], [], [], [], []
ORA-00604: error occurred at recursive SQL level 2
ORA-04030: out of process memory when trying to allocate 123404 bytes (QERHJ hash-joi,kllcqas:kllsltba)

Fri Dec 14 09:26:20 2007
Errors in file d:\oracle\admin\e1db\udump\e1db_ora_5908.trc:
ORA-04030: out of process memory when trying to allocate 82444 bytes (pga heap,control file i/o buffer)
ORA-00600: internal error code, arguments: [12761], [], [], [], [], [], [], []
ORA-00604: error occurred at recursive SQL level 2
ORA-04030: out of process memory when trying to allocate 123404 bytes (QERHJ hash-joi,kllcqas:kllsltba)

Fri Dec 14 09:30:14 2007
Errors in file d:\oracle\admin\e1db\udump\e1db_ora_7492.trc:
ORA-00600: internal error code, arguments: [12761], [], [], [], [], [], [], []
ORA-00604: error occurred at recursive SQL level 2
ORA-04030: out of process memory when trying to allocate 123404 bytes (QERHJ hash-joi,kllcqas:kllsltba)
 

Connection intermittently fails with error: 

TNS-12518: TNS: listener could not hand off client connection

Max Utilization of processes parameter is not being hit by connections and database background processes.
Solution
Review Oracle Products document on My Oracle Support Document 371983.1.  The document subject: 10gR2 Dedicated Connections Intermittently Fail with TNS-12518 

SPECIFIC TO:
Oracle Net Services - Version: 10.2.0.1.0 to 10.2.0.3.0
Microsoft Windows 2000
Microsoft Windows Server 2003
Microsoft Windows (32-bit)
Connections to 10gR2 database


Other Oracle Products reference documents:


Additional Notes:

ALERT.LOG
Oracle database provides a log file called the ALERT.LOG to record any DDL (Data Definition Language) commands such as STARTUP, SHUTDOWN, CREATE TABLESPACE, ADD DATAFILE, ALTER DATABASE, DROP ROLLBACK SEGMENT, etc. that are executed in the database. This file also logs many ORA errors that users might encounter.  This is very useful in diagnosing problems in the database. Oracle writes to the alert log every time one of these events occurs.  The alert log should be monitored frequently to detect any kind of ORA errors.

Scenario 7: ORA-00942 Error Running an EnterpriseOne Application

The following Oracle errors are being logged in the Enterprise Server JDE.LOG:
180/2812 MAIN_THREAD Wed Jul 22 19:23:30.412156 dbcolind.c141
OCI0000017 - Unable to execute statement for describe - SELECT * FROM PDCURYDTA.F75CDXM WHERE ( DXC75PID = :KEY1 ) ORDER BY DXC75PID ASC,DXC75CID ASC

180/2812 MAIN_THREAD Wed Jul 22 19:23:30.427001 dbcolind.c148
OCI0000018 - Error - ORA-00942: table or view does not exist

180/2812 MAIN_THREAD Wed Jul 22 19:23:30.427003 dbinitrq.c981
OCI0000143 - Failed to determine column order - SELECT * FROM PDCURYDTA.F75CDXM WHERE ( DXC75PID = :KEY1 ) ORDER BY DXC75PID ASC,DXC75CID ASC

180/2812 MAIN_THREAD Wed Jul 22 19:23:30.427005 dbinitrq.c988
OCI0000144 - Error - ORA-00942: table or view does not exist

180/2812 MAIN_THREAD Wed Jul 22 19:23:30.427007 Jdb_drvm.c1026
JDB9900168 - Failed to initialize db request

180/2812 MAIN_THREAD Wed Jul 22 19:23:30.427009 Jtp_cm.c1015
JDB9909007 - Unable to obtain driver request handle
Solution
The request for a certain application is done through EnterpriseOne but the response from Oracle Database is that there no such a table:
OCI0000144 - Error - ORA-00942: table or view does not exist

Explanation of:  ORA-00942 table or view does not exist

Cause:
The table or view entered does not exist, a synonym that is not allowed here was used, or a view was referenced where a table is required. Existing user tables and views can be listed by querying the data dictionary. Certain privileges may be required to access the table. If an application returned this message, the table the application tried to access does not exist in the database, or the application does not have access to it.

Action:
Check each of the following:
  • The spelling of the table or view name
  • That a view is not specified where a table is required.
  • That an existing table or view name exists.
Contact your database administrator if the table needs to be created or if user or application privileges are required to access the table.

Also, if attempting to access a table or view in another schema, make certain the correct schema is referenced and that access to the object is granted

This error message may occur when the metadata between JDE and Oracle DB are different.
Table specification for JDE is stored in tables F98710, F98711, F98712 and F98713 based on this RDB data it will create F75CDXM.h, F75CDXM.c and F75CDXM.obj as C components and GLBTBL.xdb and GLBTBL.ddb as Specification.
These two components (Specification and C Components) make a table as an object and Oracle needs to have metadata for a certain table object.
The Oracle Database error cannot find metadata information in the database so it gives error.
In general, this type of error message occurs:
  • When there is any change in database
  • Or after applying a JDE ESU(s). If there is any change in the table structure the special instructions for the ESU you applied will specify the need to generate the table through OMW.
In case the issue is caused by any other reason, then follow the steps below:
  1. Log on to FAT (WebDev) client
  2. Look for the table which has problem in OMW
  3. Go to Table Operation and click 'Generate Table', this action will correct both JDE and Oracle DB

    • JDE: It will correct F75CDXM.h, F75CDXM.c in your Deployment Server and it will update GLBTBL.xdb and GLBTBL.ddb on the machine where you are working on. In case you have to update spec in your server, build and deploy a server package against this table to correct it.
    • Oracle DB: it will drop table (if any) and create/truncate table based on definition in F75CDXM.h file
       
  4. (If you want to apply it globally in JDE) build/deploy package

Scenario 8: Intermittent Error "ORA-28547: Connection to server failed" in the Enterprise Server Logs

There are random error messages in the Enterprise Server JDE.LOG that seem to be causing performance issues and/or JAS errors. The errors are as follows:

5496/4336 WRK:Init Remote Env Token Fri Feb 20 14:56:46.513000 dbinitcn.c897
OCI0000065 - Unable to create user session to database server
5496/4336 WRK:Init Remote Env Token Fri Feb 20 14:56:46.513001 dbinitcn.c902
OCI0000141 - Error - ORA-28547: connection to server failed, probable Oracle Net admin error
5496/4336 WRK:Init Remote Env Token Fri Feb 20 14:56:46.513002 dbinitcn.c520
OCI0000367 - Unable to connect to Oracle ORA-28547: connection to server failed, probable Oracle Net admin error
5496/4336 WRK:Init Remote Env Token Fri Feb 20 14:56:46.513003 Jdb_drvm.c909
JDB9900164 - Failed to connect to ORCL

5496/4336 WRK:Init Remote Env Token Fri Feb 20 14:56:46.513004 Jtp_cm.c273
JDB9909003 - Could not init connect.

This is a new installation, and the error occurs at random, but happens about 3 to 5 times everyday affecting different users.
Cause
A failure occurred during initialization of a network connection from a client process to the Oracle server.  The connection was completed but a disconnect occurred while trying to perform protocol-specific initialization, usually due to use of different network protocols by opposite sides of the connection.
This usually is caused by incorrect Oracle Net administrative setup for database links or external procedure calls. The most frequent specific causes are:
  • The connection uses a connect string which refers to a Heterogeneous Services agent instead of an Oracle server.
  • The connection uses a connect string which includes an (HS=) specification.
The Oracle services are starting as LOCAL SYSTEM not as a LOCAL SERVER DOMAIN Account.
ORA-28547 can occur on Oracle Net Services; version 10.1.0 to 10.2.x  when "Oracle Native Authentication Services" is failing.
Solution
Change the database service to run as a user who is a member of the Domain Admin group. Oracle Log On user must be a privileged user to allow it to correctly authenticate the client user for Native Authentication. To change the user:
  1. Edit the properties of the database service, select the Log On tab
  2. Select "This Account" and add you domain admin account details and click OK
  3. Shutdown the database
  4. Stop and restart the service
  5. If you still get the ORA-28547 error after making the above change, do the following:
Review the Oracle user dump file (_ora_.trc in \user_dump_dest directory on the Oracle server box)  and you may find an error stating it has exceeded the maximum number of processes.

If that is the case, open the init.ora and check what the process parameter is set to there and increase it.  For example: if you find it currently set to 50, increase it to 100, bounce the Oracle instance server and E1 services.

WORKAROUND:
As a workaround disable Oracle Native Authentication Services.  Change SQLNET.AUTHENTICATION_SERVICES from (NTS) to (NONE) in the server sqlnet.ora .  The sqlnet.ora file is usually found in the $ORACLE_HOME/NETWORK/ADMIN directory.  A sample sqlnet.ora file:
TRACE_LEVEL_CLIENT = OFF
SQLNET.AUTHENTICATION_SERVICES = (NONE)
NAMES.DIRECTORY_PATH = (TNSNAMES)
AUTOMATIC_IPC = OFF
 

Scenario 9: Passwords Expires by Default In Oracle 11g

Users are unable to login to EnterpriseOne.  The following steps have been attempted:
  1. Stop and Start E1 service.  Service fails to start.
  2. Reboot Server.  Start E1 service and it still fails to start.
  3. Database and listeners startup OK. 
  4. Run the E1 script:  RunOneWorld.sh to start service and it fails with the following memory errors and the following:
$ RunOneWorld.sh

system returns:
$ *** glibc detected *** jdenet_k: double free or corruption (!prev): 0x08faf7f0 ***

Solution
Found that in Oracle 11g Enterprise Manager the SYS password has expired. Other passwords in EM were found to be expiring soon.  The goal is to change the default setting for the Proxy User Password to "never expire". The script below shows how to change the passwords from expiring:

This script is run on the database and should be performed by a DBA.

ALTER PROFILE DEFAULT LIMIT
FAILED_LOGIN_ATTEMPTS UNLIMITED
PASSWORD_LIFE_TIME UNLIMITED; 
 
When specified with a password parameter, UNLIMITED indicates that no limit has been set for the parameter.

After running the script the system user or Proxy User password should no longer expire.  After making this change the database, listener and E1 service are all started with no issue.

Additional information on the changes with Oracle 11g, review Document 454635.1.

Scenario 10: Unable to connect to Oracle ORA-12705: invalid or unknown NLS parameter value specified

Any E1 kernel (Security, Call Object) that connects to an Oracle database fails with the following errors:
OCI0000065 - Unable to create user session to database server
OCI0000141 - Error - ORA-12705: invalid or unknown NLS parameter value specified
OCI0000367 - Unable to connect to Oracle ORA-12705: invalid or unknown NLS parameter value specified
Cause
There are several issues that cause the ORA-12705 invalid or unknown NLS parameter value specified error message:
  1. NLS_LANG mismatch between the client (Enterprise server) and the Database server.
  2. NLS_LANG that doesn't match with the language or territory.
  3. Release mismatch between the database client and the server.
  4. NLS_LANG not defined in uppercase in UNIX Platforms.
  5. Inappropriate permissions set on the NLS data files.
  6. NLS_LANG not defined but on UNIX Platforms, if the shell is changed, it may add a variable such as: LANG=UTF-8 which can cause the above issue.
Solution
  1. Verify the code page setting of the Oracle database by executing the following SQL queries from
    SQL Plus:

    SELECT VALUE FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER = 'NLS_CHARACTERSET';

    SELECT VALUE FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER = 'NLS_NCHAR_CHARACTERSET';

    Based on the result you may set the NLS_LANG in the following format:
    LANGUAGE_TERRITORY.CHARACTERSET
  2. The Oracle character set setting (according to the any of the EnterpriseOne Installation Guides) for the English language is WE8MSWIN1252. Refer to the installation guide (based on the application release and platform type) for the correct value required by your language and territory.
  3. Refer to Oracle Products Document 207303.1 Client / Server / Interoperability Support Matrix For Different Oracle Versions.
  4. The NLS_LANG value should be defined in uppercase on UNIX Servers for the Environment Variable.
  5. The NLS loadable files are usually located in:

    $ORACLE_HOME/ocommon/nls/admin/data         >>> for UNIX platforms
    $ORACLE_HOME\ocommon\nls\admin\data         >>> for Windows servers

    On UNIX for example; the rights should be as follows:

    drwxr-xr-x 2 oracle dba 9728 Aug 3 15:17 data/
    -rw-r--r-- 1   oracle dba 714 Mar 312000 *.nlb

    To change the protection issue:

    Directory: chmod 755
    Files: chmod 644 *.nlb
     
  6. If the shell on a UNIX server has been changed; for example: from bsh to .bashrc for the E1 user (JDE900), check to see what LANG or NLS system variables may have been added/modified.  Depending on the shell, different config files may be invoked at startup causing system variables to be set before EnterpriseOne is started.

Scenario 11: ORA-01033 ORACLE initialization or shutdown in progress

When attempting to start E1 services after rebooting or bouncing the services, only the Network service starts, the Queue service does not.  The following error messages appear in the server JDE.LOG:
2936 Wed Sep 3 15:49:39 2003 dbinitcn.c653
OCI0000065 - Unable to create user session to database server

2936 Wed Sep 3 15:49:39 2003 dbinitcn.c658
OCI0000065 - Error - ORA-01033: ORACLE initialization or shutdown in progress

2936 Wed Sep 3 15:49:39 2003 jdb_drvm.c680
JDB9900164 - Failed to connect to owpr
Solution
Review database datasources for possible issue with the database name, etc.  During troubleshooting, found that the TNSNAMES.ORA is pointing to the wrong database (owpr instead of xetest).  Update the TNSNAMES.ORA with correct connection information to the database.

Posted by at
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)