Learning Oracle: All errors

DATABASE ERRORS

ORA-00257 archiver error. Connect internal only, until freed

Cause: The archiver process received an error while trying to archive a redolog.

If the problem is not resolved soon, the database will stop executing transactions.

The most likely cause of this message is the destination device is out of space

to store the redo log file.

Action: Check the archiver trace file for a detailed description of the problem.

Also, verify that the device specified in the initialization parameter ARCHIVE_LOG_DEST

is set up properly for archiving.

ORA-00257 comes under "Oracle Database Server Messages".

These messages are generated by the Oracle database server when running any Oracle program.

How to free archive destination device:

Most probably archive destination is full. We should backup archive logs, then remove them.

We can use following steps for this

1. find the location of Archive destination by

show parameter archive_dest

lets say it provide LOCATION=/u10/oradata/mydb/arch

2. move some files to some other location using os command

cd /u10/oradata/mydb/arch

mv /u10/oradata/mydb/arch/* /u15/oradata/mydb/arch-bkp/

Or it can be done using RMAN also

rman target /

RMAN> backup archive log all format '/u15/oradata/mydb/arch-bkp';

RMAN> delete archive until time 'trunc(sysdate)';

ORA-01555: snapshot too old

ORA-01555: snapshot too old: rollback segment number string with name "string" too small
Cause: rollback records needed by a reader for consistent read are overwritten by other writers
Action: If in Automatic Undo Management mode, increase undo_retention setting.
Otherwise, use larger rollback segments

Peek into the ORA-01555 reasons:
The ORA-01555 error can occur when a long read only transaction is run against database and there are many DML transactions being executed on database (on same data). The longer query runs, there are more chances of encountering ORA-01555 exception.

The ORA-01555 is caused by Oracle "Read Consistency Mechanism". Oracle provides read consistency by reading the "before image" of updated data from "Online UNDO Segments". If there are lots of updates, long running read-only SQL and a small UNDO, the ORA-01555 error may encounter.

ORA-01555 can be avoided by following precautions:
1. Database should be in Automatic Undo Management mode.
2. Use a large optimal value for undo_retention.
3. Use a large optimal size of rollback segment (undo) size.
4. Should avoid to fetch (select / cursors) between commits.
5. Should Commit less often at the time of long running query, to reduce Undo Segment slot reuse.
6. Try to run long running queries on off peak hours, when there is less DML transactions on database.

ORA-27101: shared memory realm does not exist

ORA-27101: shared memory realm does not exist
Cause: Unable to locate shared memory realm
Action: Verify that the realm is accessible

Reference: Oracle Documentation

There may be 2 scenarios, which may lead you to encounter ORA-27101
1) Invalid ORACLE_HOME and ORACLE_SID
2) Database is down

[oracle@192 ~]$ sqlplus scott/tiger

SQL*Plus: Release 10.2.0.1.0 - Production on Wed May 22 16:48:05 2013

ERROR:

ORA-01034: ORACLE not available

ORA-27101: shared memory realm does not exist

Linux Error: 2: No such file or directory

Scenario 1: Invalid ORACLE_HOME and ORACLE_SID:

ORA-27101 is usually due to the invalid ORACLE_HOME and ORACLE_SID parameters at OS level. You should investigate that ORACLE_HOME and ORACLE_SID are set correctly, and ORACLE_HOME should be without trailing slash.

[oracle@192 ~]$ echo $ORACLE_HOME

/u01/app/oracle/product/10.2.0/db_1

[oracle@192 ~]$ echo $ORACLE_SID

testdb

In our case ORACLE_SID was not correct, we need to export ORACLE_SID to the valid one which is ora10g

[oracle@192 ~]$ export ORACLE_SID=ora10g

[oracle@192 ~]$ sqlplus scott/tiger

SQL*Plus: Release 10.2.0.1.0 - Production on Wed May 22 16:52:11 2013

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

Scenario 2: Database is down:

You may also face it when the database is down, to solve ORA-27101 you may have to start the database only

[oracle@192 ~]$ sqlplus sys/sys as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Wed May 22 17:07:38 2013

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area 1224736768 bytes

Fixed Size 1219112 bytes

Variable Size 167773656 bytes

Database Buffers 1040187392 bytes

Redo Buffers 15556608 bytes

Database mounted.

Database opened.

ORA-00020 maximum number of processes exceeded

ORA-00020 maximum number of processes exceeded
Cause: All process state objects are in use.
Action: Increase the value of the PROCESSES initialization parameter.

ORA-00020 comes under "Oracle Database Server Messages". These messages are generated by the Oracle database server when running any Oracle program.

How to increase PROCESSES initialization parameter:

1.    Login as sysdba
    sqlplus / as sysdba

2. Check Current Setting of Parameters
    sql> show parameter sessions
    sql> show parameter processes
    sql> show parameter transactions

3.    If you are planning to increase "PROCESSES" parameter you should also plan to increase "sessions and "transactions" parameters
    A basic formula for determining these parameter values is as follows:

        processes=x
        sessions=x*1.1+5
        transactions=sessions*1.1

4.    These paramters can't be modified in memory. You have to modify the spfile only (scope=spfile) and bounce the instance.
    sql> alter system set processes=500 scope=spfile;
    sql> alter system set sessions=555 scope=spfile;
    sql> alter system set transactions=610 scope=spfile;
    sql> shutdown abort
    sql> startup

ORA-00600 internal error code

ORA-00600 internal error code, arguments: [string], [string], [string], [string], [string], [string], [string], [string]

Cause: This is the generic internal error number for Oracle program exceptions.
This indicates that a process has encountered a low-level, unexpected condition.
Causes of this message include:
- timeouts
- file corruption
- failed data checks in memory
- hardware, memory, or I/O errors
- incorrectly restored files

Action: Report as a bug - the first argument is the internal error number

Reference: Oracle Documentation

Description of ORA-00600:
A typical ORA-00600 error does not include descriptive text. The message text can be followed by up to six arguments, which indicate the origin and attributes of the error. The first argument is the internal error number. Other arguments are various numbers, names, and character strings. The numbers may change meanings between different versions of Oracle. Empty brackets may be ignored.

In addition to being returned to the user, internal errors are also written to the Alert file along with additional information about the event. The Alert file also lists any trace files that may have been generated because of an internal error.

If you receive an ORA-00600 message, report it to Oracle Support Services.

Troubleshooting ORA-00600:
Assume we encounter following ORA-00600 exception
- ORA-00600: internal error code, arguments: [17069],[18798616],[],[],[]

Metalink provides a very useful ORA-00600 Troubleshooter tool (Metalink Note:153788.1) to allow you to get the description for an ORA-00600 error. In this web tool, we can enter the 17069 (first argument) for the 17069 error:

Login into the Metalink account and search for doc id “153788.1? in the Quick find search bar and open the document.

Follow following steps to lookup by code:
a. Select the correct Error code ORA-00600
b. Provide first argument of ORA-00600
c. Select the appropriate database version
d. Click Lookup Error button

ORA-03135: connection lost contact

ORA-03135: connection lost contact
Cause: 1) Server unexpectedly terminated or was forced to terminate.
2) Server timed out the connection.
Action: 1) Check if the server session was terminated.
2) Check if the timeout parameters are set properly in sqlnet.ora.

ORA-03135 occurs when we try to connect oracle database and the connection gets time out. ORA-03135 can also be caused by the firewall when connecting remotely.

To resolve ORA-03135 exception we need to increase the value of the expire_time parameter in the SQLNET.ORA file, as mentioned in Action part. We can also set the parameters sqlnet.inbound_connect_timeout and inbound_connect_timeout_listenername to 0 can avoid the ORA-03135 error. SQLNET.ORA file is located under $ORACLE_HOME/network/admin/.

Following is the Oracle Documentation having lists and description of sqlnet.ora file parameters
- http://docs.oracle.com/cd/E11882_01/network.112/e10835/sqlnet.htm#NETRF182

We recently faced ORA-03135 on one of our testing environment after Server Admin team did a firewall upgrade. Users were able connect to the database, but if their session was idle for some time, the connection was getting terminated.

To resolve ORA-03135 we added following to SQLNET.ORA file

SQLNET.EXPIRE_TIME = 10

This keeps connections alive by send a check to verify that client/server connections are active in every 10 minutes. If the probe finds a terminated connection, or a connection that is no longer in use, then it returns an error, causing the server process to exit.

ORA-03113: end-of-file on communication channel

ORA-03113: end-of-file on communication channel
Cause: The connection between Client and Server process was broken.
Action: There was a communication error that requires further investigation. First, check for network problems and review the SQL*Net setup. Also, look in the alert.log file for any errors. Finally, test to see whether the server process is dead and whether a trace file was generated at failure time.

"ORA-03113: end-of-file on communication channel" is a generic exception, it say that an established connection has been lost. ORA-3113 means that the oracle server process has suddenly killed or died due to some reason. ORA-03113 is a "catch-all" exception and there are many possible reasons of ORA-03113.

Usually ORA-03113 is a stack of oracle error messages issued. The ORA-03113 exception may not itself indicate the actual error and is accompanied with other errors which will help us to debug the actual issue.

Common reason of ORA-03113:
- Session has been killed
- Oracle internal errors
- Process has been aborted
- Network related issues
- Connection Pool related problem
- Oracle Database is crashed
- Database Server Machine is crashed

To debug ORA-03113 we need to look at any other exception messages on error message stack accompanied with ORA-03113. Also we should look in alert log to determine event happened when ORA-03113 was raised by the database. We can also check trace files that may have been generated for ORA-03113 exception.

ORA-16000: database open for read-only access

Let's suppose we have two databases opened in Read Only mode.
1. DB1
2. DB2

and on DB1, we have a DBLINK on DB2 named DB2.

Now, if we try to execute following query on DB1

SELECT EMPNO, ENAME, DNAME, SAL
FROM scott.emp EMP, scott.DEPT@DB2 DEPT
WHERE EMP.DEPTNO=DEPT.DEPTNO

it will throw following error
ORA-16000: database open for read-only access

to avoid this we must set our transaction as read only using follwoing command

set transaction read only;

Example:

ORA-12560: TNS: protocol adapter error

ORA-12560: TNS: protocol adapter error
Cause: A generic protocol adapter error occurred.
Action: Check addresses used for proper protocol specification. Before reporting this error, look at the error stack and check for lower level transport errors. For further details, turn on tracing and re-execute the operation. Turn off tracing when the operation is complete.

ORA-12560 is defined as a generic protocol adapter exception and may have various reasons.

C:\>sqlplus scott/tiger

SQL*Plus: Release 11.2.0.3.0 Production on Wed Feb 20 18:44:04 2013

ERROR:

ORA-12560: TNS: protocol adapter error

ORA-12560 is common error on Windows that results from any number of different causes. ORA-12560 is also very common exception when we have two Oracle homes. Mostly ORA-12560 occurs when we have Oracle Client configuration related issues but some times it may be due to Server side and network related issues. To resolve ORA-12560 we need to ensure following points:

- Ensure Database Service is up.
- Ensure listener is up and pointing to desired database service.
- Ensure all oracle variables are configured correctly (ORACLE_BASE, ORACLE_HOME, ORACLE_SID, PATH, TNS_ADMIN).
- On Windows: Ensure Full Access is granted to ORACLE_HOME and its sub-directories.
- Also cross-check the firewall & Network configurations.

Oracle Database Connection Error

Cannot Connect to the Oracle Database:

1.Problem: The user cannot connect to the Oracle database, with either the OEM (Oracle Enterprise Manager) or SQL*PLUS. The user's database is in a hung state and he does not have any messages in the alert log.

2.Impact: Very high as the user's database is in a hung state and he cannot perform any functions with the Oracle Database.

3.Solution: The Oracle database can hang for a lot of reasons. Below are the solutions to the problem.

A. The user must check the alert logs and the log files of the listener.

B. Check the server side logs like /adm/syslog and /etc/syslog.

C. The lsnrctl utility can be used to check if the listener is running. If the listener is not up and running then the server cannot detect incoming connections.

D. The user can check the instance by running - ps -ef|grep ora|grep pmon if the database instance is unavailable due to a crash. This would check the process monitor because the user cannot see the alert log in this scenario.

E. In Oracle 11g, a new utility has been introduced in SQL*PLUS called - prelim. This can be used to run oradebug command and utilities which function without actually connecting to the database.

F. After running the oradebug command, the user can use the utility called hanganalyze to diagnose the problem in the hung database.

G. The user can also kill or bounce the instance.

H. The user should ensure that the Oracle environment variables are properly set by looking at the Oracle_SID and the PATH in $ORACLE_HOME.

I. It is possible that due to insufficient Random Access Memory(RAM), the users cannot perform another connection to Oracle.

J. The users might be trying to connect to the Oracle Database using Background and server processes which are being used by another user.

K. Another possibility is that Oracle cannot find tnsnames.ora file or the tnsnames.ora file does not point to a valid database.

L. So, ensure that the user's path contains the correct path to the tnsnames.ora file and that the file points to the user's database on the specific host machine.

ORA-00020 maximum number of processes exceeded

ORA-00600 internal error code

ORA-03135: connection lost contact

SQLNET.EXPIRE_TIME = 10

ORA-03113: end-of-file on communication channel

ORA-16000: database open for read-only access

ORA-12560: TNS: protocol adapter error

ORA-12560 is defined as a generic protocol adapter exception and may have various reasons.

C:\>sqlplus scott/tiger

SQL*Plus: Release 11.2.0.3.0 Production on Wed Feb 20 18:44:04 2013

ERROR:

ORA-12560: TNS: protocol adapter error

Oracle Database Connection Error

Cannot Connect to the Oracle Database:

A. The user must check the alert logs and the log files of the listener.

B. Check the server side logs like /adm/syslog and /etc/syslog.

C. The lsnrctl utility can be used to check if the listener is running. If the listener is not up and running then the server cannot detect incoming connections.

E. In Oracle 11g, a new utility has been introduced in SQL*PLUS called - prelim. This can be used to run oradebug command and utilities which function without actually connecting to the database.

F. After running the oradebug command, the user can use the utility called hanganalyze to diagnose the problem in the hung database.

G. The user can also kill or bounce the instance.

H. The user should ensure that the Oracle environment variables are properly set by looking at the Oracle_SID and the PATH in $ORACLE_HOME.

I. It is possible that due to insufficient Random Access Memory(RAM), the users cannot perform another connection to Oracle.

J. The users might be trying to connect to the Oracle Database using Background and server processes which are being used by another user.

K. Another possibility is that Oracle cannot find tnsnames.ora file or the tnsnames.ora file does not point to a valid database.

L. So, ensure that the user's path contains the correct path to the tnsnames.ora file and that the file points to the user's database on the specific host machine.

ORA-07445 Internal error ?

As we know, basic code of database is written in C and C++ Language. When a function in C call another function but got some unexpected results and which is not handled by Oracle Code, then DBA encounter ORA-00600 or ORA-07445 Internal error. On further discussion in this article, we will also see which function is failed and case these errors.

ORA-600 [kwqbdrcp101] after Drop or Recreate of Propagation

Applies to:

Oracle Server - EnterpriseEdition - Version: 10.2.0.4 and later [Release: 10.2 and later]
Information in this documentapplies to any platform.

Symptoms:

ORA-00600: internal error code,arguments: [kwqbdrcp101], [], [], [], [], [], [], []

Current SQL statement for this session:
DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate; broken BOOLEAN := FALSE; BEGIN next_date := sys.dbms_aqadm.aq$_propaq(job); :mydate := next_date; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;

Stack trace

ksedst<- ksedmp<- ksfdmp<- kgerinv<- kgeasnmierr
<- kwqbdrcp<- kwqbdfetchbuf<- kwqbdeq<- kwqidabm<- kwqididqx
<- kwqididq<- kwqpdeq<- kwqpdest<- kwqjswucb<- rpiswu2
<- kwqjpropwin<- kwqjaq<- spefcmpa<- spefmccallstd<- pextproc
<- peftrusted<- psdexsp<- rpiswu2 <- psdextp<- pefccal
<- pefcal<- pevm_FCAL<- pfrinstr_FCAL<- pfrrun_no_tool<- pfrrun
<- plsql_run<- peicnt<- kkxexe<- opiexe<- opiodr
<- rpidrus<- skgmstack<- rpidru<- rpiswu2 <- rpidrv
<- rpiexe<- kkjex1e <- kkjsexe<- kkjrdp<- opirip
<- opidrv<- sou2o <- opimai_real<- main <- $START$

Queue_to_queue is false.

Changes

Recreate the streams environment.
Drop/recreate the propagation.

Cause

Due to message existence in Queue.

Solution

1. Stop the Propagation.

exec DBMS_PROPAGATION_ADM.STOP_PROPAGATION('');

2. Drop the Propagation.

exec DBMS_PROPAGATION_ADM.DROP_PROPAGATION('');

3. Purge the Queue table.

DECLARE
options dbms_aqadm.aq$_purge_options_t;
BEGIN
options.delivery_mode:=DBMS_AQADM.BUFFERED;
DBMS_AQADM.PURGE_QUEUE_TABLE('',NULL,options);
END;
/

4. Create the Propagation.

BEGIN
DBMS_PROPAGATION_ADM.CREATE_PROPAGATION(
propagation_name => '',
source_queue => '.',
destination_queue => '.',
destination_dblink => '' ,
rule_set_name => '', -- specify rule_set name, if you want
propagation to use a rule_set, otherwise eliminate this parameter.
);
END;
/
This post is about Specific ORA-0600 error, I would recommend to see General approach for troubleshooting ORA-00600 and ORA-07445 Internal error which is applicable to all ORA-07445Internal error and ORA-00600 internal errors.

Reference Note: ORA-600 [kwqbdrcp101] after Drop or Recreate of Propagation [ID 1322207.1]

ORA-00600: internal error code, arguments: [17074]

Bug 9795214 - Library Cache Memory Corruption / OERI[17074] may result in Instance crash [ID 9795214.8]

I have Oracle 11.2.0.1 Enterprise Edition Installed on Red Hat Enterprise Linux Server release 5.4 (Tikanga) 64 bit machine. It's my production environment.

Suddenly Database goes down, In alert log files "ORA-00600: internal errorcode, arguments: [17074]" message was written.

Error message is like:

Errors in file /test01/diag/rdbms/test01/test01/trace/test01_mman_27018.trc (incident=211313):
ORA-00600: internal error code, arguments: [17074], [0x75F3F9FD0], [0x7AC0E9C80], [6], [], [], [], [], [], [], [], []
Incident details in: /test01/diag/rdbms/test01/test01/incident/incdir_211313/test01_mman_27018_i211313.trc
Errors in file /test01/diag/rdbms/test01/test01/trace/test01_mman_27018.trc:
ORA-00600: internal error code, arguments: [17074], [0x75F3F9FD0], [0x7AC0E9C80], [6], [], [], [], [], [], [], [], []
MMAN (ospid: 27018): terminating the instance due to error 822
Instance terminated by MMAN, pid = 27018

On investigation i found this bug ........

This note gives a brief overview of bug 9795214.

Affects:

Product (Component)	Oracle Server (Rdbms)
Range of versions believed to be affected	Versions >= 11.1 but BELOW 12.1
Versions confirmed as being affected	· 11.2.0.2 · 11.2.0.1 · 11.1.0.7
Platforms affected	Generic (all / most platforms affected)

Fixed:

This issue is fixed in	· 12.1 (Future Release) · 11.1.0.7.7 Patch Set Update · 11.2.0.1 Patch 8 on Windows Platforms · 11.1.0.7 Patch 38 on Windows Platforms
Symptoms:		Related To:
· Instance May Crash · Memory Corruption · Mutex Contention · ORA-822 · ORA-600 [17074] · ORA-600 [kglhpd-bad-free] · Dump in or under KGHISPIR		· (None Specified)

Description

ORA-600 [17074] may occur in some rare circumstances, potentially causing

an instance crash

From 11g onwards library cache latches are replaced by the mutex (short for

mutual exclusion) concurrent programming algorithm. This issue is a

mutex race condition where conflicting updates on a library cache memory

chunk result in ORA-600 [17074]. This issue is intermittent and rare due

to the precise timing required for the contending memory chunk access.

This error results in process failure. Where the process is a critical

background process this will result in an instance crash.

There is no persistent corruption associated with this failure

Error arguments:

1 - 17074 error number

2 - Pointer to the heap where the inconsistency was found

3 - Pointer to the free fn

4 - Data block number

This issue does not occur in 10.2 or earlier releases.

Rediscovery Notes:

If ORA-600 [17074] occurs due to kgldacnt being non-zero (note that

if this error is flagged for some other reason, then it may not be

this bug), it could indicate this bug, especially if the function

kghrecr_quiesce() is present in the call stack.

Workaround

None

Please note: The above is a summary description only. Actual symptoms can vary. Matching to any symptoms here does not confirm that you are encountering this problem. For questions about this bug please consult Oracle Support.

This post is about Specific ORA-0600 error, I would recommend to see General approach for troubleshooting ORA-00600 and ORA-07445 Internal error which is applicable to all ORA-07445Internal error and ORA-00600 internal errors.

References

Bug:9795214 (This link will only work for PUBLISHED bugs)
Note:245840.1 Information on the sections in this article

ORA-00600: internal error code, arguments: [kpobav-1]

I have Oracle 11.2.0.1 Enterprise Edition installed on my Solaris 10server. Suddenly i observed "ORA-00600: internal error code, arguments: [kpobav-1]" in my alert log file.

It's clear ORA-00600 is an Oracle internal exception. So we need to go tohttps://support.oracle.com/. On further investigate at Oracle support. I found it a bug.

Bug 9703463 ORA-3137 [12333] or ORA-600 [kpobav-1] When Using Bind Peeking

This note gives a brief overview bug 9703463.
The content was last updated on: 22-NOV-2010
Click here for details of each of the sections below.

Affects:

Product (Component)	Oracle Server (Rdbms)
Range of versions believed to be affected	Versions >= 11.1 but BELOW 12.1
Versions confirmed as being affected	· 11.2.0.1 · 11.1.0.7
Platforms affected	Generic (all / most platforms affected)

Fixed:

This issue is fixed in	· 12.1 (Future Release) · 11.2.0.2 (Server Patch Set)
Symptoms:		Related To:
· Error May Occur · Internal Error May Occur (ORA-600) · ORA-3137 · ORA-600 [kpobav-1] · Stack is likely to include kxsPeekBinds		· Optimizer · _optim_peek_user_binds

Description

Frequent errors like ORA-3137 [12333] are raised. If the patch for bug:9243912

has been applied, you will see ORA-600 [kpobav-1] errors.

Rediscovery Notes:

Optimizer bind peeking in use

The call stack includes "kpobavopibvgkxsPeekBindskkscsCompareBinds"

The KXSCFBPE flag is set, and the CTXFTCA flag is not set

See note:1243836.1 for a detailed description of the diagnostics

Workaround:

Disable bind peeking by setting:

SQL> alter system set "_optim_peek_user_binds"=false;

Please note: The above is a summary description only. Actual symptoms can vary. Matching to any symptoms here does not confirm that you are encountering this problem. Always consult with Oracle Support for advice.

References

Bug:9703463 (This link will only work for PUBLISHED bugs)
Note:245840.1 Information on the sections in this article

----------------------------------------------------------------------------------------------------------------------------------------------------
I apply this workaround on server but unfortunately it doesn't work. It might be possible it works for your case. So my next move is to upgrade my Oracle database s/w from Oracle 11.2.0.1 Enterprise Edition to Oracle 11.2.0.2 Enterprise Edition.

When moving towards Oracle 11.2.0.2 Enterprise Edition I observed some new changes introduced on it. Follow the link http://umeshsharmasblog.blogspot.com/2011/04/important-changes-to-oracle-database.html.

I install Oracle 11.2.0.2 Enterprise Edition according to the changes and recommendations. Next step is to upgrade database. After completing all these activities.

I observe alert log file then i don't find ORA-00600: internal error code, arguments: [kpobav-1] in my alert log file. The issue is resolved by Upgrading my Oracle s/w from Oracle 11.2.0.1 Enterprise Edition to Oracle 11.2.0.2 Enterprise Edition.

This post is about Specific ORA-0600 error, I would recommend to see General approach for troubleshooting ORA-00600 and ORA-07445 Internal error which is applicable to all ORA-07445Internal error and ORA-00600 internal errors.

ORA-07445: exception encountered: core dump [pi_jaro_winkler_int()+409]

ORA-07445: exception encountered: core dump [pi_jaro_winkler_int()+409]

we use PL/SQL (DBMS Packages) TL_MATCH.JARO_WINKLER_SIMILARITY to compare text values in my package. When we start using this package. I start getting ORA-07445: exception encountered: core dump [pi_jaro_winkler_int()+409] exception in alert_log file.

I have Oracle 10.2.0.4 64 Bit Enterprise Edition installed on my Linux 64 system with 15 interim patches. On investigation this issue i found it is an Oracle Bug

Bug 6338068 - Dump (pi_jaro_winkler_int) from UTL_MATCH.JARO_WINKLER_SIMILARITY [ID 6338068.8]

This note gives a brief overview bug 6338068.
The content was last updated on: 18-NOV-2010
Click here for details of each of the sections below.
Affects:

Product (Component) PL/SQL (Plsql)
Range of versions believed to be affected Versions BELOW 11.2
Versions confirmed as being affected

* 10.2.0.4
* 10.2.0.3

Platforms affected Generic (all / most platforms affected)

Fixed:

This issue is fixed in

* 11.2.0.1 (Base Release)
* 11.1.0.7 (Server Patch Set)
* 10.2.0.5 (Server Patch Set)
* 10.2.0.4 Patch 23 on Windows Platforms

Symptoms:

Related To:

* Process May Dump (ORA-7445) / Apend / Abort

* PL/SQL (DBMS Packages)
* UTL_MATCH

Description

UTL_MATCH.JARO_WINKLER_SIMILARITY can dump in pi_jaro_winkler_int.

eg:
declare
s1 char(15) := '01DB - Metravib';
s2 char(24) := 'INGETUDES-EKIP /LINEDATA';
r number;
begin
r := UTL_MATCH.JARO_WINKLER_SIMILARITY(s1, s2);
end;
/
^
Dumps

Please note: The above is a summary description only. Actual symptoms can vary. Matching to any symptoms here does not confirm that you are encountering this problem. Always consult with Oracle Support for advice.

References

Bug:6338068 (This link will only work for PUBLISHED bugs)
Note:245840.1 Information on the sections in this article

Then investigate for solution of this problem. Oracle offers a patch set for this (p6338068_10204_Linux-x86-64). This is Linux 64 bit patch set
same can be available for other environments.

when i download this patch set another twist came out. In the Readme following lines are written.

“# - Your system configuration (Oracle Server version and patch level, OS Version) must exactly match those in the bug database entry - You must have NO OTHER PATCHES installed on your Oracle Server since the latest patch set (or base release x.y.z if you have no patch sets installed).”

While my Oracle home has 15 interim patches installed. So this solution doesn't work for me.

Finally only following is the option for me:

This issue is fixed in following releases:
• 11.2.0.1 (Base Release)
• 11.1.0.7 (Server Patch Set)
• 10.2.0.5 (Server Patch Set)
• 10.2.0.4 Patch 23 on Windows Platforms

Now i am planning to install Oracle 10.2.0.5 on my system.

This post is about Specific ORA-07445 error, I would recommend to see General approach for troubleshooting ORA-00600 and ORA-07445 Internal error which is applicable to all ORA-07445Internal error and ORA-00600 internal errors.

ORA-600 Troubleshooting

QUICKLINK: Note 600.1 ORA-600/ORA-7445 Lookup tool
Note 1082674.1 : A Video To Demonstrate The Usage Of The ORA-600/ORA-7445 Lookup Tool [Video]

Have you observed an ORA-0600 error reported in your alert log?

The ORA-600 error is the generic internal error number for Oracle program exceptions. It indicates that a process has encountered a low-level, unexpected condition.

The ORA-600 error statement includes a list of arguments in square brackets:

ORA 600 "internal error code, arguments: [%s], [%s],[%s], [%s], [%s]"

The first argument is the internal message number or character string. This argument and the database version number are critical in identifying the root cause and the potential impact to your system. The remaining arguments in the ORA-600 error text are used to supply further information (e.g. values of internal variables etc).

The first argument may help to narrow your problem to known issues. However, this argument can point to functionality that is referenced by many areas in the Oracle source code. The more detailed call stack information reported in the ORA-600 error trace may be needed to find a solution.

Looking for the best way to diagnose?

Whenever an ORA-600 error is raised, a trace file is generated and an entry is written to the alert.log with details of the trace file location. As of Oracle 11g, the database includes an advanced fault diagnosability infrastructure to manage trace data. For more detail on this functionality see the FAQ and quick steps video.

Note 453125.1 11g Diagnosability Frequently Asked Questions

Note 443529.1 11g Quick Steps to Package and Send Critical Error Diagnostic Information to Support[Video]

1. Check the Alert Log

The alert log may indicate additional errors or other internal errors at the time of the problem. Focus your analysis of the problem on the first internal error in the sequence. There are some exceptions, but often additional internal errors are side-effects of the first error condition.

The associated trace file may be truncated if the MAX_DUMP_FILE_SIZE parameter is not setup high enough or to „unlimited‟. If you see a message at the end of the trace file

"MAX DUMP FILE SIZE EXCEEDED"

there could be vital diagnostic information missing in the file and finding the root issue may be very difficult. Set the MAX_DUMP_FILE_SIZE appropriately and regenerate the error for complete trace information.

2. Search 600/7445 Lookup Tool

Visit My Oracle Support to access the ORA-00600 Lookup tool (Note 600.1). The ORA-600/ORA-7445 Lookup tool may lead you to applicable content in My Oracle Support on the problem and can be used to investigate the problem with argument data from the error message or you can pull out key stack pointers from the associated trace file to match up against known bugs.

3. Investigate reference material on the error

In the search tool from above, choose Error Code ORA-600 and enter the first argument number or character string. Click on „Lookup error‟ button to review the reference note for your error. The reference note will provide a description of the error and may point to specific notes or bugs that possibly cause the error.

4. "Fine tune" searches in Knowledge Base

You can also click on the "Search Knowledge Base‟ button. This potentially returns a large result set. To narrow the hits, add symptom key words or phrases to your search in the Knowledge Base. You might also find more specific matches using the call stack functions listed in the trace file just after the first argument listed with the ORA-600 error text.

As an example, you receive the ORA-600 [kghalo] error. The initial search in My Oracle Support brings back hundreds of hits. The call stack from the trace file shows the following functions:

...

kghfnd

kghalo

kghgex

...

Adding 'kghgex' to the search keywords in the Knowledge Base narrows the hits to less than 30.

See the following note and video for more pointers on using the ORA-600/ORA-7445 Lookup Tool.

Note 600.1 ORA-600/ORA-7445 Lookup tool

Note 1082674.1 A Video To Demonstrate The Usage Of The ORA-600/ORA-7445 Lookup Tool [Video]

See detailed pointers on analysis of these errors in the following note

Note 390293.1 Introduction to 600/7445 Internal Error Analysis

5. If assistance is required from Oracle

If it becomes necessary to get assistance from Oracle Support on an ORA-600 problem, please provide at a minimum, the

o Alert log

o Associated tracefile(s) or incident package at 11g

o Patch level information

o Information about changes in configuration and/or application prior to issues

o RDA report or Oracle Configuration Manager information

o Oracle Configuration Manager Quick Start Guide

o Note 548815.1 My Oracle Support Configuration Management FAQ

o Note 414966.1 RDA Documentation Index

o If error is reproducible, a self-contained reproducible testcase: Note.232963.1 How to Build a Testcase for Oracle Data Server Support to Reproduce ORA-600 and ORA-7445 Errors

Troubleshooting the dreaded “ORA-07445″ and “ORA-00600″ errors

Troubleshooting the dreaded “ORA-07445″ and “ORA-00600″ errors

ORA-07445 and ORA-00600 core dump errors are very generic and it does not give users any specific error message. It becomes the task of a DBA to find out the cause and the solution of the error. The million dollar question is how to find the cause of the error? There are no direct messages printed on the screen to start with.

In such case, we start with alert_SID.log file. Every ORA-07445 and ORA-00600 errors will get recorded in the alert_SID.log file with proper time and related trace file information.

We will use the information in trace file to find out the actual cause of the error and the solution through metalink. Metalink provides a very useful “ORA-600/ORA-7445 Troubleshooter tool (Metalink Note:153788.1)”

Login into the Metalink account and search for doc id “153788.1” in the Quick find search bar and open the document. Now navigate to the error in alert_SID.log file and find the related trace file from UDUMP directory.

There are 2 ways to lookup the error:

1. Lookup by code

a. Select the correct Error code (ORA-00600 or ORA-07445)

b. Provide the first argument or failing function

c. Select the appropriate database version

d. Click Lookup Error button
2. Search Call Stack

a. Select the correct Error code (ORA-00600 or ORA-07445)

b. Select the appropriate database version

c. Enter the Call Stack info in the “Search Call Stack box”

d. Click Search Call Stack button
Metalink will search it’s database and will show all the possibilities that can cause the problem and also it’s solutions. In case the error is not in the metalink database, they make a note of this new error too.

This tool plays important role in identifying bugs in the database.

The ORA-00600 (ORA 600) error is a generic internal error from Oracle, and you must look-up the numeric arguments to understand the nature of the internal error.

A typical ORA-00600 error does not include descriptive text and might look like this, with various arguments following the message in the Oracle alert log::

ORA-00600 [723][51202][1][51200][][]

Top 40 Most Searched For Oracle Errors

Position	Error	Global Monthly Searches*
1	ORA-12154	101,500
2	ORA-00600	40,500
3	ORA-01722	27,100
4	ORA-12560	22,200
5	ORA-03113	18,100
6	ORA-00604	14,800
7	ORA-00936	12,100
8	ORA-01017	12,100
9	ORA-01555	12,100
10	ORA-04031	12,100
11	ORA-00257	12,100
12	ORA-27101	12,100
13	ORA-00911	12,100
14	ORA-00933	9,900
15	ORA-01403	9,900
16	ORA-01422	9,900
17	ORA-04030	9,900
18	ORA-00932	9,900
19	ORA-01031	8,100
20	ORA-20000	8,100
21	ORA-12560	8,100
22	ORA-06508	8,100
23	ORA-01000	6,600
24	ORA-12505	6,600
25	ORA-20001	6,600
26	ORA-12519	6,600
27	ORA-01008	6,600
28	ORA-00054	6,600
29	ORA-01830	6,600
30	ORA-00907	6,600
31	ORA-00984	6,600
32	ORA-01461	5,400
33	ORA-01110	5,400
34	ORA-00001	5,400
35	ORA-02010	5,400
36	ORA-12537	5,400
37	ORA-03135	5,400
38	ORA-01034	5,400
39	ORA-00918	5,400
40	ORA-04063	5,400

Fix Oracle 10g 11g Data Pump IMPDP Failed withORA-39002 ORA-06512 ORA-39070 ORA-29283: invalid file operation

Error Description:

Oracle datapump impdp fails with following error.

impdp dumpfile=exp_TAB_28122011.dmp directory=DATA_PUMP_DIR logfile=exp_TAB_28122011_imp.log job_name=EXP ignore=y

Import: Release 11.2.0.2.0 - Production on Wed Dec 28 06:19:40 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation

Solution Description:

This error is because the impdp can not write the log file to the currsponding log file location. You can fix this error by checking the following things.

Find out the curresponding DATA_PUMP_DIR from the dba_directories view and check whether the directory physically present in the file system
Check whether enough permissions (READ & WRITE) are granted to the user who is performing the import. Suppose if you are performing the import using Scott user, you can use this command to give the permissions.

GRANT READ, WRITE ON DATA_PUMP_DIR TO SCOTT;

OS level: Check the oracle user has the write, read access on the directory mentioned in the impdp. You can change the permission on OS level using chmod command.

Problem

When doing export form datapump

ORA-29283: invalid file operation
ORA-06512: at “SYS.UTL_FILE”, line 488

Solution

create the unix directory and the data pump directory in oracle

DROP DIRECTORY DATA_PUMP_DIR;

CREATE OR REPLACE DIRECTORY

DATA_PUMP_DIR AS

‘/u01/oracle/admin/msoa1_js/dpdump/’;

GRANT READ, WRITE ON DIRECTORY SYS.DATA_PUMP_DIR TO dp WITH GRANT OPTION;

GRANT READ, WRITE ON DIRECTORY SYS.DATA_PUMP_DIR TO EXP_FULL_DATABASE;

GRANT READ, WRITE ON DIRECTORY SYS.DATA_PUMP_DIR TO IMP_FULL_DATABASE;

Data Pump IMPDP Failed with ORA-39002 ORA-06512 ORA-39070 ORA-29283: invalid file operation

Errors :
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation

Steps to Troubleshoot/Solve:

From the error message it is clear that Oracle is unable to write the log file to the corresponding logfile location specified.

1. Check the DIRECTORY existence from data dictionary i.e. from dba_directories check whether the directory is physically present in the filesystem.

2. Check permissions for the user invoking the impdp else grant those permissions

GRANT READ,WRITE on DATA_PUMP_DIR to VIJAY;

3. Check OS level permissions for DB home owner e.g. oracle on physical directory. Else use chmod to correct permissions on filesystem.

Oracle Performance Issues - Basic Checks

What are the basic checks for a junior DBA when slowness reports in the system?

Taking user inputs:

This is the very first step for troubleshooting any performance issues, get user inputs. You may use the below tips for this.

· Is application is slow or any particular batch processing is slow?

· Slowness is observed throughout the system or only few or one user

· Is it happening in some particular timing ?

· Is it slow right now?

By collecting these information we will get an outline of what needs to be checked.

Now login to system and start investigation.

Check the resource utilization:

You can check the CPU,Load,Memory utilization, use top or topas command in unix.
Check any single process is holding the CPU for long time -- note the process ID.
Press 'c' in top command, it will give you the time and process which is consuming more CPU.

Check the alert log:

First check the alert log for any error and note the error or abnormalities if any. You can check how many log switches are happening in one hour. If you have more than 5 archives per hour we can say you may need to increase the redo log size. Troubleshoot the errors if it s critical or related to performance.

Check the Database:

Logging to database and see any lock contention in database. You can use the below query for this.

SQL> select count(*) from v$lock where block=1;

If count is greater than one, lock is there in database. Check with application team and release the blocking sessions (Refer my Blocking Sessions in Oracle post)

CPU Intensive Queries

You can find out the sql query details using the below by taking the process id from top command.

select sql_text,a.sid,a.serial# from v$sqlarea c,v$session a,v$process b where a.paddr=b.addr and a.sql_address=c.address and b.spid=&pid;

Check with application team whether these are ad-hock queries or regular, disconnect the high CPU queries if possible.

Send these query details to application team for tuning.

Below query is an another way to find out high CPU quries:

select ss.username,se.SID,VALUE/100 cpu_usage_seconds
from v$session ss, v$sesstat se,v$statname sn
where se.STATISTIC# = sn.STATISTIC#
and NAME like '%CPU used by this session%'
and se.SID = ss.SID and ss.status='ACTIVE'
and ss.username is not null
order by VALUE desc;

Resource intensive Queries

Find out the resource intensive queries and share the details with application team.

col usr for a10
set lines 500
select
s.username usr,s.module,logon_time,status,
m.session_id sid,
m.session_serial_num ssn,
round(m.cpu) cpu100, --- CPU usage 100th sec
m.physical_reads prds, --- Number of physical read
m.logical_reads, --- Number of logical reads
m.pga_memory, --- PGA size at the end of the intervel
m.physical_read_pct prp,
m.logical_read_pct lrp,
s.sql_id
from v$sessmetric m,v$session s
where (m.physical_reads >100
or m.cpu>100
or m.logical_reads>10000)
and m.session_id=s.sid
and m.session_serial_num=s.serial#
and s.type='USER'
order by m.physical_reads DESC,m.cpu desc,m.logical_reads desc;

Stale stats tables check

Check any critical table statistics became stale

select count(*),owner from dba_tab_statistics where stale_stats='YES';

Make sure that no highly accessed tables are in stale stats and gather the stats if any.

Sample script for stats gathering:

execute dbms_stats.gather_table_stats(ownname => '<OWNER>', tabname =>'<TABLE NAME>', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', degree => 2, cascade => TRUE);

Single user session or batch slowness:

Find out the sessions for the user and enable the trace

Enabling

alter session set tracefile_identifier=ARUN_NEW;

EXECUTE dbms_system.set_sql_trace_in_session (109,18512,TRUE);

This will generate the trace in udump

Disabling

EXECUTE dbms_system.set_sql_trace_in_session (707,49158,FALSE);

Go to trace location:(udump)

tkprof trace_file.ora trace_out.txt

Analyse the report or share with application team

Server side checks

Check for the memory,paging, IO utilization from server side.

Paging and memory can be checked by top command and iostat will do the io statistics.

Contact the concern team for any abnormality if you see in this.

Apache Common Issues

General Information to Troubleshoot Apache Issues.

1)If you mistype any directive in Apache (httpd.conf or any conf file), Apache will not start & it will not write even in error_log. In these case it will report any error message to file

$COMMON_TOP/admin/log/$CONTEXT_NAME/adapcctl.txt

So first check in the above file

2)Found *.pid file in Apache_Top/Apache/logs/ even after bounce of an Apache web server (When Apache starts it creates .pid file in logs directory and when you shutdown apache it cleans this .pid file). So if this file is already there then Apache will not start.

Fix: Simply move this file

3)Could not bind port (Port already in Use) or Sometimes apache and concurrent

Manager will interchange their port no.’s while starting while results error and both of them will not up

Fix: To overcome this up the services in this manner

1)Listener

2)Apache web server

3)Concurrent manager

4)adstrtal.sh

4)Error 500 - Internal server error

Fix: 1) Clear Cache (Application Server Cache)and start the services

5)If there are no error messages in error_log or error_log_pls

then there might be issues in starting Jserv component of web server (which caters Java requests by mod_jserv)

6)Mobile is enabled in zone.properties disable it if you are not using it.

7)If you don't know port number of your web server you can check it in file $IAS_ORACLE_HOME/Apache/Apache/conf/httpd.conf with directive Listen & Port

8)Once you are able to access http://hostname.domainname:port and if you are getting any error message that means your web server (Core Apache) is working but there are various components called as modules and there may be issues with those modules which we will check these modules . few modules of our interest are

-mod_jserv

-mod_pls

-mod_oprocmgr

-mod_ossl (If you have ssl enabled on web server)

-mod_osso (If single sign on server is enabled on web server)

9)Once in a while adapcctl.sh start will not work then ultimate you have to go and

invoke apachectl script file in Apache top/apache/bin to start Apache webserver.

10)If we remove _pages from COMMON_TOP it will be created for the next time only when we bounce and up the Apache web server.

11)Jsp pages are complied with the file ojspcompile.pl executable. It is located at $JSP_TOP/bin/ and in R12 it is there in

$FND_TOP/patch/115/bin/

12)Error : Your session is no longer valid while trying to loging to applications.

The issue is resolved by changing the profile option session_cookie_domain

to null from domain.com

13)HTTP-403 forbidden message after entering the username and password in the "Apps Logon Links" page. [Not authorised to view this page]

Check for Apps password in $APACHE_TOP/modplsql/cfg/wdbsvr.app ,Comment out custom_auth entry in $APACHE_TOP/modplsql/cfg/wdbsvr.app and bounce apache.

14)Error message: You have insufficient privileges for the current operation.

Verify and update SESSION_COOKIE_DOMAIN in ICX_parameters if it is wrong.

Example :

update icx_parameters set SESSION_COOKIE_DOMAIN='NEW_VALUE' where SESSION_COOKIE_DOMAIN='OLD_VALUE'

15)AppsLocalLogin?.jsp page error outs….

Edit the $IAS_ORACLE_HOME/Apache/Jserv/etc/jserv.properties

Set wrapper.bin.parameters=-DLONG_RUNNING_JVM=false

add the line wrapper.bin.parameters=-DCACHEMODE=LOCAL

Restarted Apache for these changes to take effect and retested the issue

16) Login to the applications URL fails with

500 Internal server Error

java.lang.NoClassDefFoundError at

oracle.apps.fnd.sso.AppsLoginRedirect.AppsSetting(AppsLoginRedirect.java:126) at

oracle.apps.fnd.sso.AppsLoginRedirect.init(AppsLoginRedirect.java:170) at com.evermind[Oracle

Containers for J2EE 10g (10.1.3.0.0)

].server.http.HttpApplication.loadServlet(HttpApplication.java:2231) at com.evermind[Oracle

Containers for J2EE 10g (10.1.3.0.0)

].server.http.HttpApplication.findServlet(HttpApplication.java:4617) at com.evermind[Oracle

Containers for J2EE 10g (10.1.3.0.0)

].server.http.HttpApplication.findServlet(HttpApplication.java:4541) at com.evermind[Oracle

Containers for J2EE 10g (10.1.3.0.0)

Performed the below steps, but it did not resolved the issue

1) Bounced Apache server, cleared cache and validated ports

2) Bounced all Middle tier services and killed defunct processes

3) Bounced Unix Server

Solution

=======

Java object Caching Configuration parameter was Enabled in oc4j.properties

$INST_TOP/ora/10.1.3/j2ee/oacore/config

Disabled LONG_RUNNING_JVM=false from True

Bounced Apache server and could login to the applications URL without issues

ISSUES AND SOLUTIONS

ISSUE 1:

Description

When Apache script adapcctl.sh is issued.It shows that Apache is started.On checking the status Apache is stopped

Cause

The error log file in the folder

cd $APACHE_TOP/Apache/logs

Showed

fopen: No such file or directory httpd: could not log pid to file /var/opt/_/Apache/logs/httpd_pls.pid

SOLUTION 1

Created the missing directories /_/Apache/logs under /var/opt folder

Bounced Apache

ISSUE 2:

Description

oacore was not comming up in the configurator node.

opmnctl status

ias-component process-type pid status

OC4J oacore N/A Down

Because of this configurator cannot be invoked.

Cause

Check the oacore error log.

Example:

Below Log file gives root cause. //mtlog/_/logs/ora/10.1.3/opmn/oacore_default_group_2/oacorestd.err

14:11:56 Error initializing server: //inst/apps/_/ora/10.1.3/j2ee/oacore/config/server.xml,

Fatal error at line 70 offset 1 in file://inst/apps/_/ora/10.1.3/j2ee/oacore/config/server.xml: .:

XML-20100: (Fatal Error) Expected 'EOF'.

SOLUTION 2

Edit the /sid/inst/apps/SID_hostname/ora/10.1.3/j2ee/oacore/configserver.xml file and remove the extra line in the file. Restart and check oacore will be starting. This sudden change in server.xml seems to be because of a bug.

Bug - 6702510

ISSUE 3

AppsLocalLogin?.jsp page was displaying errors

Cause

Extract from the Jserv log file =========================== [27/02/2008 16:57:14:447 CST] weboamLocal/oracle.apps.fnd.oam. servlet.ui.OAMServlet:

Sucessfully initialize oaosu.OAMServlet [27/02/2008 16:57:14:447 CST] Servlet Zone rootauohstrzt11 initia lization complete [27/02/2008 16:57:15:101 CST]

Creating noun for: oracle.jsp.JspServlet [27/02/2008 16:57:15:102 CST] Creating noun for: AppsLocalLogin?.jsp

[27/02/2008 16:57:15:120 CST] oracle.jsp.JspServlet: init [27/02/2008 16:57:28:066 CST] Creating noun for: oracle.jsp.JspServlet

[27/02/2008 16:57:28:067 CST] Creating noun for: AppsLocalLogin?.jsp [27/02/2008 16:57:28:078 CST] oracle.jsp.JspServlet: init

[27/02/2008 16:57:29:753 CST] JspServlet?: unable to dispatch to requested page: Exception:java.lang.NoClassDefFoundError

SOLUTION 3:

Edited the $IAS_ORACLE_HOME/Apache/Jserv/etc/jserv.properties

a) Set wrapper.bin.parameters=-DLONG_RUNNING_JVM=false

b) Add the line wrapper.bin.parameters=-DCACHEMODE=LOCAL

c) Restarted Apache for these changes to take effect and retested the issue

ISSUE 4

New Message when starting Apache using adapcctl.sh start

Cause

It takes 7/10 minutes to clear this cache directory if present

apache Web Server Listener is not running.

Starting Apache Web Server Listener (dedicated HTTP) ...

Removing gantt cache directory

Successfully removed the gantt cache directory:/tcge1i/applmgr/common/html/cabo/images/cache/gantt

Apache Web Server Listener (PLSQL) is not running.

Starting Apache Web Server Listener (dedicated PLSQL) ...

SOLUTION 4

Wait for atleast 7 to 10 minutes and this cache directory will be cleared and Apache will be started successfully

ISSUE 5

Apache server is up and running.

But adapcctl.sh status shows

adapcctl.sh version 115.54

Apache Web Server Listener is not running. Apache Web Server Listener (PLSQL) is not running.

adapcctl.sh: exiting with status 1

Cause

Incorrect Lock file path given in httpd.conf

LockFile? $APACHE_TOP/Apache/log/PUPONI_httpd.lock

and

Incorrect path given in adapcctl.sh script.

SOLUTION 5:

Modified the path of lock file in $APACHE_TOP/Apache/conf/httpd.conf file.

LockFile? /var/opt/SID_/httpd.lock

and

Modified the path of adapcctl.sh file by commenting the wrong path

ISSUE 6

After selecting the E-Business home page got internal server error Internal Server Error The server encountered an internal error or misconfiguration and was unable to complete your request. Please contact the server administrator, and inform them of the time the error occurred, and anything you might have done that may have caused the error.

More information about this error may be available in the server error log.

Cause

Found that all the properties files as below

jserv.properties jserv_restrict.properties zone.properties zone_restrict.properties

were pointing to a dbc file name _.dbc

which was not physically present at the path

/sid/applmgr/11510/fnd/11.5.0/secure/_

$ cd //applmgr/11510/fnd/11.5.0/secure/_

$ ls .dbc

SOLUTION 6:

copied the file

twwyai.dbc

which was physically present at path

//applmgr/11510/fnd/11.5.0/secure/_

to the dbc file name which was being pointed by all the properties files

$ cp .dbc _>sid>.dbc

$ ls

_>sid>.dbc .dbc

This resolved the issue.

ISSUE 7

Instance is very slow / inaccessible with a warning on login page:

Warning

Low-level logging is currently enabled. Your application will not perform as well while Low-level logging is on.

Cause

Logging was enabled through profile values:

FND: Debug Log Enabled -- Yes

FND: Debug Log Level -- Statement

FND: Debug Log Module -- %

SLA: Enable Diagnostics -- Yes

FND Validation Level -- Error

SOLUTION 7

1. Change these values to:

FND: Debug Log Enabled -- No

FND: Debug Log Level -- ( Blank )

FND: Debug Log Module -- ( Blank )

SLA: Enable Diagnostics -- No

FND Validation Level -- None

2. Bounce Apache on all the MTs and clear Apache cache.

ISSUE 8

500 Internal Server Error java.lang.NoClassDefFoundError at oracle.apps.fnd.sso.AppsLoginRedirect.AppsSetting(AppsLoginRedirect?.java:126) at oracle.apps.fnd.sso.AppsLoginRedirect.init(AppsLoginRedirect?.java:170) at com.evermind[Oracle Containers for J2EE? 10g (10.1.3.0.0) ].server.http.HttpApplication.loadServlet(HttpApplication?.java:2231) at com.evermind

Performed the below steps, but it did not resolved the issue

1) Bounced Apache server, cleared cache and validated ports

2) Bounced all Middle tier services and killed defunct processes

3) Bounced Unix Server

Cause

No Cause Entered

SOLUTION 8

Java object Caching Configuration parameter was Enabled in oc4j.properties $INST_TOP/ora/10.1.3/j2ee/oacore/config

Disabled LONG_RUNNING_JVM=false from True

Bounced Apache server and could login to the applications URL without issues

Java Caching is a mechanism used to improve performance by storing data in the Middle Tier JVM memory, instead of retrieving the data from the database every time it is requested.

ISSUE 9

When trying to access the URL (Either Big IP or the node level), it hangs for a long time and getting redirected to a different URL and gives a blank page. For example, When try to access the ipayments URL which is hosted on node2, it hangs for a long time and its getting redirected towards the Sales URL which is hosted on node3, and displays a blank page.

Cause

All the host reference and the URL directives are pointing towards node3 and sales URL respectively, in the below files. Hence every attempt tries to launch the sales URL and get fails as the apache configuration files and parameters are pointing towards the correct one.

jserv.conf forms.properties zone.properties jserv.properties

SOLUTION 9

Stop Apache. Edit the files jserv.conf, forms.properties, zone.properties, jserv.properties, jserv.conf to replace the host reference and the URL directives to the correct one (In our case, its node3 . Clear the cache and restart the Apache.

ISSUE 10

Apache login issues occurs in multi MT environment. Unable to login to oracle applications.

Cause

Issue due to distributed cache.

SOLUTION 10

Set is_distributed to false in javacache.xml Bounce Apache.

ISSUE 11

Apache server is down. Apache logfile did not provide useful information.

Started the Apache server, but the Apache Web listener did not started.

1) Verified no log files (Apache,Jserv) crossed 2GB limit. error_log, access.log, mod_jserv

2) Veified that the port is listening (netstat -an |grep ) Apache port(httpd.conf), pls listener(httpd_pls.conf)

3) Verified that the Apache and Jserv configuration files are not changed recently httpd.conf & Jserv.conf

4) Shutdown all the Middle tier services and check for ipcs (semaphores) did not resolve the outage.

$ ipcs on application middle tier as Application user ap

Cause

No Cause Entered

SOLUTION 11

Rebooted the server and started the services. All services came back normal.

ISSUE 12

Unable to login to the home page URL. Login fails with the error:

Error Page

You have encountered an unexpected error. Please contact the system administrator for assistance.

Click here for exception details

Checks:

Upon clicking the link, can observe the error:

oracle.apps.fnd.framework.OAException: Application: FND, Message Name: SQL_PL SQL_ERROR. Tokens: ROUTINE = createSession(int)(userId=6,sessionMode='null','2D8 FCCC57459B66EE040558CDBC6118E44964630721347149122084914143412?'); REASON = java.s ql.SQLException: ORA-01422: exact fetch returns more than requested number of ro ws ORA-06512: at "APPS.FND_SESSION_MANAGEMENT", line 295 ORA-06512: at "APPS.FND_SESSION_MANAGEMENT", line 455 ORA-06512: at line 1 ; ERRNO = 1422;

Cause

FND_NODES table was updated with the same server_id for all Nodes.

SOLUTION 12

Update the table FND_NODES with correct server_ids for all nodes as in $FND_TOP/secure/sid_host/sid.dbf

Bounced apache on all apache nodes.

and Run select node_name, server_id from fnd_nodes;

to confirm the values.

Below is the example for the solution:

Found three hosts using same server_id:

SQL> select node_name, server_id from fnd_nodes; NODE_NAME SERVER_ID

AUOHSACPS06 2D8FCCC57459B66EE040558CDBC6118E44964630721347149122084914143412

AUOHSACPS03 2D8FCCC57459B66EE040558CDBC6118E44964630721347149122084914143412

AUOHSACPS02 2D8FCCC57459B66EE040558CDBC6118E44964630721347149122084914143412

reset server_id with original server_id from each hosts .dbc file @ $FND_TOP/secure:

SQL> select node_name, server_id from fnd_nodes; NODE_NAME SERVER_ID

AUOHSACPS06 2D8FF21843830F82E040558CDBC6195C27690802237516122831445560236127

AUOHSACPS03 2D90005D5A13C774E040558CDBC61D9436546148971863330582284123371818

AUOHSACPS02 2D8FCCC57459B66EE040558CDBC6118E44964630721347149122084914143412

ISSUE 13

Users getting '404 page not found errors' when tyring to access the URL

(OR)

Getting Exception in thread "main" java.lang.ClassFormatError: Truncated class file

(OR)

adstrtal.sh or adstpall.sh is throwing below error message

You are running adstrtal.sh version 115.16

Exception in thread "main" java.lang.ClassFormatError: oracle/apps/ad/context/AppsContext (Truncated class file) at java.lang.ClassLoader.defineClass0(Native Method) at java.security.AccessController.doPrivileged(Native Method) at java.net.URLClassLoader.findClass(URLClassLoader?.java:187) at java.lang.ClassLoader.loadClass(ClassLoader?.java:289) at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:274) at java.lang.ClassLoader.loadClass(ClassLoader?.java:235) at java.lang.ClassLoader.loadClassInternal(ClassLoader?.java:302) at oracle.apps.ad.autoconfig.ServiceControl.(ServiceControl?.java:319) at oracle.apps.ad.autoconfig.ServiceControl.main(ServiceControl?.java:662) Check logfile //applmgr/common/admin/log/_/08061104.log for details

Exiting with status 1

(OR)

[06/08/2007 11:03:12:181] (ERROR) an error returned handling request via protocol "ajpv12" [06/08/2007 11:03:12:181] (EMERGENCY) ajp12: can not connect to host 140.85.188.36:20021 [06/08/2007 11:03:13:184] (EMERGENCY) ajp12: can not connect to host 140.85.188.36:20021 [06/08/2007 11:03:15:038] (EMERGENCY) ajp12: auth did not receive challenge size [06/08/2007 11:03:15:038] (EMERGENCY) ajp12: auth fail

Cause

Extract from the JVM log file: //product/iAS/Apache/Jserv/logs/jvm/OACoreGroup.0.stderr

Exception in thread "main" java.lang.ClassFormatError: Truncated class file at org.apache.jserv.JServServletManager.load_init(JServServletManager?.java:765) at org.apache.jserv.JServServletManager.loadServlet(JServServletManager?.java:659) at org.apache.jserv.JServServletManager.loadStartupServlets(JServServletManager?.java:789) at org.apache.jserv.JServServletManager.init(JServServletManager?.java:447) at org.apache.jserv.JServ.start(JServ.java:625)

Verified that there are no log files that exceed 2GB limit (OS limit).

Clearing the Apache and Jserv cache did not resolve the issue. Unmount / mount product specific file system did not resolve

SOLUTION 13:

Try each of these solutions until the problem is resolved, in order to have the least impact on the system.

1. If you can determine the specific class file from the log files, use the 'touch' command.

e.g. touch classfile

If necessary, restart Apache and clear server cache.

2. Shut down all MT services, exit the Apps owner and ask the System Admin to unmount and remount the /sid/applmgr file system (mount point above $JAVA_TOP). Ask the System Admin to kill any processes that may be holding the file system open. Then log in to the Apps owner and restart MT services.

3. Shutdown all the MT services and ask System Admin to reboot the server if the actions above don't resolve the outage.

ISSUE 14:

Got login error - Internal Server Error after bounced all services on . find following message from Jserv log file:

$ tail -8 mod_jserv.log

[07/05/2007 18:41:06:915] (ERROR) ajp12: Servlet Error: java.lang.NoClassDefFoundError: null

[07/05/2007 18:41:06:916] (ERROR) an error returned handling request via protocol "ajpv12"

[07/05/2007 18:41:06:916] (ERROR) balance: 11906 internal servlet error in server auohsnops03.oracleoutsourcing.com:20720

[07/05/2007 18:41:06:916] (ERROR) an error returned handling request via protocol "balance"

[07/05/2007 20:07:52:732] (ERROR) ajp12: Servlet Error: java.lang.NoClassDefFoundError: null

No runaway, no lock on port 20720.

SOLUTION 14:

Shutdown all services on MT. Removed all log file (standard error...) from Jserv/jvm.

Started All services with successful login.

ISSUE 15

Customer reported Not able to login with “Internal Server Error” on .

We could not start some of services during bounced All Services with following error message: adapcctl.sh version 115.47

Cannot complete applications logon. You may have entered an invalid applications password, or there may have been a database connect error.

Apache Web Server Listener is not running (dedicated HTTP) ...

Cannot complete applications logon. You may have entered an invalid applications password, or there may have been a database connect error.

Apache Web Server Listener is already stopped.

Cannot complete applications logon. You may have entered an invalid applications password, or there may have been a database connect error.

adapcctl.sh: exiting with status 2

Unable to login the application URL, facing the error while login:

Internal Server Error The server encountered an internal error or misconfiguration and was unable to complete your request. Please contact the server administrator, ap@domain.com and inform them of the time the error occurred, and anything you might have done that may have caused the error.

Cause:

SQL> select END_DATE from fnd_user where user_name='GUEST';

END_DATE

---------

05-OCT-10

SOLUTION 15

We found out the apps and applsys were not in sync.

Just manual change the apps password from sqlplus.

Restarted all services and login successfully.

Update the FND_USER table with END_DATE=null and bounced apache

SQL> update fnd_user set END_DATE=null where user_name='GUEST';

1 row updated.

SQL> commit;

Commit complete.

SQL> select END_DATE from fnd_user where user_name='GUEST';

END_DATE

-----------

ISSUE 16

Unable to login to oracle applications:

error message: You have insufficient privileges for the current operation.

Checks

Unable to login to oracle applications:

error message: You have insufficient privileges for the current operation.

SOLUTION 16

Verify and update SESSION_COOKIE_DOMAIN in ICX_parameters if it is wrong.

Example :

update icx_parameters set SESSION_COOKIE_DOMAIN='NEW_VALUE' where SESSION_COOKIE_DOMAIN='OLD_VALUE';

ISSUE 17

Apache home page is not coming up even after bounced

Found in jvm log asking for java version

SOLUTION 17

//product/iAS/Apache/Apache/bin/java.sh: /usr/java/jdk1.3.1_15/bin/java: not found

Check the version in /usr/java and update that version in java.sh file and Bounced Apache.

ISSUE 18

Unable to access applications

cause

All applications are up and running.

"Solaris Error: 12: Not enough space" errors are encounterd in database listener logfile.

SOLUTION 18:

Check if the OS is SUN Solaris.

Check if /tmp filesystem is 100% full.

Check Swap space utilization.

Check the application process that is hagging resources. (Check if any sql sessions are hagging resources)

Clear the process in issue after getting required approval.

NOTE:

The above is applicable only for environments on Solaris OS.

ISSUE 19

Not able to login to APPS. JSP Error:

Unable to create an user session or ORA-01403 : no data found has been detected in FND.SIGNON.AUDIT_USER

Checks

Check number of rows in dual table.

Select * from dual;

DUAL table should always contain only one row.

The table named DUAL is a small table in the data dictionary that Oracle and

user-written programs can reference to guarantee a known result.

This table has one column called DUMMY and one row containing the value X.

SOLUTION 19

DUAL table should always contain only one row and one column.

Insert a row with value X into dual table if the dual table contains 0 rows.

If the dual table contains more than one row then delete the rows by keeping one row.

The rows in dual table not equal to zero.

ISSUE 20

Not able to launch forms from PHP

Checks

Select system administrator responsibility and click on any menu option.

The page shows "done" without launching forms (jinit)

Check the apps version.

If the apps version is 11.5.10 then check the Check the file version of $FND_TOP/admin/template/appsweb.cfg

If the file version is 115.148, then verify that "!WindowsDPI= " exists in $FORMS60_WEB_CONFIG_FILE

The windowsDPI value should be 96. If the value for WindowsDPI is not set in the

$FORMS60_WEB_CONFIG_FILE then update PROFILE_OPTION_VALUE.

SOLUTION 20

select PROFILE_OPTION_VALUE from fnd_profile_option_values where PROFILE_OPTION_ID=3769;

Update the tar with the output of the above command.

UPDATE fnd_profile_option_values

set PROFILE_OPTION_VALUE='https://.domainname.com/dev60cgi/f60cgi?windowsDPI=96' where PROFILE_OPTION_ID=3769;

This should update only one row.

commit;

ISSUE 21

Signature for "https URL redirecting to http URL"

Issue:

https URL redirecting to http URL and the page cannot be displayed error occurs.

SOLUTION 21

Check the httpd.conf and url_fw.conf, in url_fw.conf the Rewrite rule was

RewriteRule ^/$/OA_HTML/AppsLocalLogin.jsp.

Changed into

RewriteRule https://: /OA_HTML/AppsLocalLogin.jsp.

Learning Oracle

Pages

Wednesday, November 11, 2015

All errors

ORA-27101: shared memory realm does not exist

ORA-00020 maximum number of processes exceeded

ORA-00600 internal error code

ORA-03135: connection lost contact

ORA-03113: end-of-file on communication channel

ORA-16000: database open for read-only access

ORA-12560: TNS: protocol adapter error

ORA-00020 maximum number of processes exceeded

ORA-00600 internal error code

ORA-03135: connection lost contact

ORA-03113: end-of-file on communication channel

ORA-16000: database open for read-only access

ORA-12560: TNS: protocol adapter error

ORA-00600: internal error code, arguments: [17074]

Affects:

Fixed:

Symptoms:

Related To:

Description

References

ORA-00600: internal error code, arguments: [kpobav-1]

ORA-07445: exception encountered: core dump [pi_jaro_winkler_int()+409]

ORA-600 Troubleshooting

Troubleshooting the dreaded “ORA-07445″ and “ORA-00600″ errors

Top 40 Most Searched For Oracle Errors

No comments:

Post a Comment

Translate

Followers