ORA-01502 Problem case study

This case study describes the complete root cause analysis and resolution of an Oracle 10gR2 database problem (ORA-01502) affecting a Weblogic Portal 10.0 Java EE application.

Environment specifications

-          Java EE server: Oracle Weblogic Portal 10.0 MP1

-          Database server: Oracle 10gR2

-          Middleware OS: Sun Solaris 5.10

-          Database server OS: IBM AIX 5.3 TL5

-          JDK: Sun Java HotSpot(TM) Server VM Version 1.5.0_11

-          Platform type: Internet facing Portal platform

Problem overview

·       Problem type: java.sql.SQLException: [BEA][Oracle JDBC Driver][Oracle]ORA-01502: index '<INDEX NAME>' or partition of such index is in unusable state

A SQLException error was observed from the Weblogic Portal server logs. This Exception was thrown from the remote Oracle database when trying to execute a SELECT SQL for one of our table.

The Weblogic JDBC Data Source associated with this table was also disabled by Weblogic: weblogic.common.resourcepool.ResourceDeadException: Pool <JDBC Pool Name> is disabled, cannot allocate resources to applications

Gathering and validation of facts

As usual, a Java EE problem investigation requires gathering of technical and non technical facts so we can either derived other facts and/or conclude on the root cause. Before applying a corrective measure, the facts below were verified in order to conclude on the root cause:

·       What is the client impact? HIGH (this Oracle table is part of our login process for our users)

·       Recent change of the affected platform? No

·       Any recent traffic increase to the affected platform? No

·       Since how long this problem has been observed?  This error suddenly appeared for the first time early in the morning

·       What is the health of the Oracle database server? Our Oracle DBA did confirm he was able to replicate the same SQL error by running a SQL directly from SQL*Plus. The overall health of the database appeared to be fine

·       Did a restart of the Weblogic Integration server resolve the problem? No

-          Conclusion #1: The problem seems to be isolated on the Oracle database server side and related to a corruption of one of the table index (ORA-01502).

ORA-01502: what is it?

Find below the Oracle notes on this error:

ORA-01502: index "string.string" or partition of such index is in unusable state

Cause: An attempt has been made to access an index or index partition that has been marked unusable by a direct load or by a DDL operation 

Action: DROP the specified index, or REBUILD the specified index, or REBUILD the unusable index partition

Weblogic log file analysis: ORA-01502!

A first analysis of the problem was done by reviewing the Weblogic portal managed server log errors.

java.sql.SQLException: [BEA][Oracle JDBC Driver][Oracle]ORA-01502: index '<Index Name>' or partition of such index is in unusable state

       at weblogic.jdbc.base.BaseExceptions.createException(Unknown Source)

       at weblogic.jdbc.base.BaseExceptions.getException(Unknown Source)

       at weblogic.jdbc.oracle.OracleImplStatement.execute(Unknown Source)

       at weblogic.jdbc.base.BaseStatement.commonExecute(Unknown Source)

       at weblogic.jdbc.base.BaseStatement.executeQueryInternal(Unknown Source)

       at weblogic.jdbc.base.BasePreparedStatement.executeQuery(Unknown Source)

       at weblogic.jdbc.wrapper.PreparedStatement.executeQuery(PreparedStatement.java:97)

       at org.<App Code>(AppDAOClass.java)

       at org.<App Code>(AppClass.java)

       at org.<App Code>.doFilter(AppFilterClass.java)

       at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:42)

       at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:42)

       at com.bea.portal.tools.servlet.http.HttpContextFilter.doFilter(HttpContextFilter.java:60)

       at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:42)

       at com.bea.p13n.servlets.PortalServletFilter.doFilter(PortalServletFilter.java:336)

       at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:42)

       at weblogic.servlet.internal.WebAppServletContext$ServletInvocationAction.run(WebAppServletContext.java:3393)

       at weblogic.security.acl.internal.AuthenticatedSubject.doAs(AuthenticatedSubject.java:321)

       at weblogic.security.service.SecurityManager.runAs(Unknown Source)

       at weblogic.servlet.internal.WebAppServletContext.securedExecute(WebAppServletContext.java:2140)

       at weblogic.servlet.internal.WebAppServletContext.execute(WebAppServletContext.java:2046)

       at weblogic.servlet.internal.ServletRequestImpl.run(Unknown Source)

       at weblogic.work.ExecuteThread.execute(ExecuteThread.java:200)

       at weblogic.work.ExecuteThread.run(ExecuteThread.java:172)

As you can see, the ORA-01502 error was thrown during the execution of our SELECT SQL from our application.

Root cause: duplicate primary key row data!

Investigation by our DBA team did confirm that our nightly data refresh process ended up injecting duplicate primary key row data to this table; causing the index of this table to go in unusable state.

Such bad INDEX state caused all our SQL executions fired from the Weblogic portal server to fail in a systematic manner with the ORA-01502 error.

Solution: a 4 steps resolution process!

#1 - First drop the unique constrain associated with the affected index

alter table <TABLE NAME> drop constraint <CONSTRAINT NAME>;

#2 - Now drops the affected index

drop index <INDEX NAME>;

#3 - Run a script to detect and remove duplicate rows as per the example below

select t.order_id, t.creation_date count(*) from affected_table t group by t.order_id, t.creation_date count(*) > 1

#4 – Finally, rebuild the index and its associated unique PK constraint

create index <INDEX NAME> on <TABLE NAME>(PK COLUMN NAME);

alter table <TABLE NAME> add primary key(PK COLUMN NAME) NOVALIDATE ;

Conclusion and recommendations

-          When facing ORA-01502 with Oracle, please ensure to do a fast and complete root cause analysis. If your problem is related to duplicate data, please ensure you also identify the trigger e.g. which update process that triggered the duplicate data itself in order to prevent the problem in the future

-          Avoid taking unnecessary and non efficient resolution steps such as an early Weblogic restart or Oracle database restart

Posted in: Oracle DB ORA Errors