insert_icm_record failed due to ORA-00001: unique constraint

Early morning today, I was called for investigating an issue in a Production instance which was not starting concurrent managers after a power outage. The SAN had gone down because of this power outage. The DB had come up without errors. Here's how the error looks:

APP-FND-01564: ORACLE error 1 in insert_icm_record

Cause: insert_icm_record failed due to ORA-00001: unique constraint (APPLSYS.FND_CONCURRENT_PROCESSES_U1) violated.

The SQL statement being executed at the time of the error was: INSERT INTO FND_CONCURRENT_PROCESSES (CONCURRENT_PROCESS_ID,
LAST_UPDATE_DATE, CREATION_DATE, CREATED_BY, LAST_UPDATED_BY, ORACLE_PROCESS_ID, QUEUE_APPLICATIO
N_ID, CONCURRENT_QUEUE_ID, OS_PROCESS_ID, SESSION_ID, PROCESS_START_DATE, PROCESS_STATUS_CODE, MANA
GER_TYPE, NODE_NAME, DB_NAME, DB_DOMAIN, SQLNET_STRING, DB_INSTANCE, Instance_Nu
mber) VALUES (:cpid, SYSDATE, SYSDATE, :mgrusrid, :mgrusrid, :opid,
:qappid, :qid, :ospid, :osid, SYSDATE, 'A', 0,
:node, :dbname, :dbdomain, :dbinst, (Select instance_name from v$instance), (Select instance_
number from v$instance)) and was executed from the file &ERRFILE.

List of errors encountered:
.............................................................................

_ 1 _
Routine AFPCAL received failure code while parsing or running your
concurrent program CPMGR

Since the error is due to unique index, I checked for the table_name and column_name of the index by firing this query:

1 select table_name,column_name from dba_ind_columns
2* where INDEX_NAME='FND_CONCURRENT_PROCESSES_U1'
SQL> /

TABLE_NAME
------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
FND_CONCURRENT_PROCESSES
CONCURRENT_PROCESS_ID

Next I looked for all the tables which had a column called CONCURRENT_PROCESS_ID:

SQL> select table_name from dba_tab_columns
2 where column_name='CONCURRENT_PROCESS_ID';

TABLE_NAME
------------------------------
FND_CONCURRENT_PROCESSES
FND_ENV_CONTEXT
CS_CONT_BILL_IFACE_ALL
CS_ORDERS_INTERFACE_ALL
CS_TERMINATION_INTERFACE_ALL
CS_TERMINATION_INTERFACE
CS_ORDERS_INTERFACE
CS_CONT_BILL_IFACE

Assuming that it is inserting a duplicate vale for concurrent_process_id, I checked for duplicates first by this query:

SQL> select concurrent_process_id,count(*)
2 from apps.fnd_concurrent_processes
3 group by concurrent_process_id
4 having count(*)>1;

no rows selected

So no existing duplicates. That means a new record which is being inserted is generating a duplicate concurrent_process_id.

SQL> select max(concurrent_process_id)
2 from apps.fnd_concurrent_processes
3 /

MAX(CONCURRENT_PROCESS_ID)
--------------------------
3483

Now we have to look for this value in all the tables which had this column:

SQL> select CONCURRENT_PROCESS_ID
2 from fnd_env_context
3 where CONCURRENT_PROCESS_ID=3483;
from fnd_env_context
*
ERROR at line 2:
ORA-01410: invalid ROWID

1 select concurrent_process_id
2 from apps.CS_CONT_BILL_IFACE_ALL
3* where concurrent_process_id=3483
SQL> /

no rows selected

SQL> select concurrent_process_id
2 from apps.CS_ORDERS_INTERFACE_ALL
3 where concurrent_process_id=3483
4 /

no rows selected

SQL> select concurrent_process_id
2 from apps.CS_TERMINATION_INTERFACE_ALL
3 where concurrent_process_id=3483
4 /

no rows selected

SQL> select concurrent_process_id
2 from apps.CS_TERMINATION_INTERFACE_ALL
3 where concurrent_process_id=3483
4 /

no rows selected

SQL> select concurrent_process_id
2 from apps.CS_TERMINATION_INTERFACE
3 where concurrent_process_id=3483
4 /

no rows selected

SQL> select concurrent_process_id
2 from apps.CS_ORDERS_INTERFACE
3 where concurrent_process_id=3483
4 /

no rows selected

SQL> select concurrent_process_id
2 from apps.CS_CONT_BILL_IFACE
3 where concurrent_process_id=3483
4 /

no rows selected

Something wrong with FND_CONTEXT_ENV. FND_ENV_CONTEXT table is populated with environmental data when a concurrent process starts.

I did 3 things:

1.
SQL>select CONCURRENT_PROCESS_ID from FND_ENV_CONTEXT;

CONCURRENT_PROCESS_ID
---------------------
3462
3462
3462
3462
3462
3462
3462
3462
3462
3462
3462
ERROR:
ORA-08103: object no longer exists

2.
SQL> create table apps.fnd_env_context_bak as select * from apps.fnd_env_context;
create table apps.fnd_env_context_bak as select * from apps.fnd_env_context
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [kcbgtcr_12], [1], [], [], [], [],
[], []

3. Bimal tried export and got this error:
. . exporting table FND_ENV_CONTEXT
EXP-00056: ORACLE error 8103 encountered
ORA-08103: object no longer exists
Export terminated successfully with warnings.

A lookup on ORA-00600 thru ORA-600 lookup tool on metalink note: 153788.1 reveals this:

ERROR:
ORA-600 [kcbgtcr_12] [a]

VERSIONS:
Version 9.0 to 10.01

DESCRIPTION:

ORA-600 [kcbgtcr_12] is raised when Oracle is trying to create a current
buffer after reading the block from disk and fails since there already is
a current buffer for this block in the cache.

ARGUMENTS:
Arg [a] The buffer class

FUNCTIONALITY:
Kernel Cache Buffer management Implementation

IMPACT:
NON CORRUPTIVE - No underlying data corruption.

SUGGESTIONS:

Known issues:
Bug# 5523799 + See Note 5523799.8
Various OERI (eg kcbgtcr_12) using ASSM managed segments
Fixed:

Bug# 4996133 See Note 4996133.8
OERI[kcbgtcr_5] updating an IOT in RAC environment
Fixed: 10.2.0.3, 11

Bug# 4464828 See Note 4464828.8
OERI[kcbgtcr_12] using ANALYZE on ASSM segment
Fixed: 9.2.0.8

Bug# 2928757 See Note 2928757.8
OERI[kcbgcur_6] / OERI:[kcbgtcr_12] can occur for files converted to locally managed
Fixed: 9.2.0.5, 10.1.0.2

Bug# 2619867 See Note 2619867.8
OERI:[KCBGTCR_12] / ORA-8103 / ORA-1410 SELECTing from bitmap managed segment
Fixed: 9.2.0.3, 10.1.0.2

Bug# 2122159 See Note 2122159.8
OERI:KCBGTCR_12 possible in RAC (eg during parallel load)
Fixed: 9.0.1.4, 9.2.0.1

Bug# 1935292 See Note 1935292.8
OERI:KCBGTCR_12 etc.. doing STAR QUERY with TEMP TABLE TRANSFORMATION in IPQ
Fixed: 9.0.1.2, 9.2.0.1

Bug# 1777175 See Note 1777175.8
OERI:kcbgcur_12 / OERI:kcbgcur_6 / OERI:kclchkinteg_1 accessing a table which is being DROPPED
Fixed: 9.0.1.2, 9.2.0.1

We can try to truncate the table or drop / recreate it. However this being an Oracle seeded table and this being a Production instance, it is best to log an SR and let Oracle guide us.

While Bimal was working with Oracle to fix the FND_ENV_CONTEXT table, Subu gave a workaround for the issue:

The concurrent_process_id in the table fnd_concurrent_processes is populated by the sequence FND_CONCURRENT_PROCESSES_S. The abonormal shutdown of the server during the power outage may have prevented the sequence from incrementing. If you manually do a nextval query on the sequence, it increments the value of the sequence:

SQL> select apps.FND_CONCURRENT_PROCESSES_S.currval
2 from dual;

CURRVAL
----------
3483


SQL> select apps.FND_CONCURRENT_PROCESSES_S.nextval
2 from dual;

NEXTVAL
----------
3484

SQL> select apps.FND_CONCURRENT_PROCESSES_S.currval
2 from dual;

CURRVAL
----------
3484

After doing this query, the concurrent managers log showed a different error for each manager:

Starting SYSADMIN Concurrent Manager : 21-JUL-2007 09:05:07

ORACLE error 8102 in insert_fcp

Cause: insert_fcp failed due to ORA-08102: index key not found, obj# 40781, dba 121643011 (2)
ORA-06512: at "APPS.FND_CP_FNDSM", line 121
ORA-06512: at line 1.

The SQL statement being executed at the time
Could not initialize the Service Manager FNDSM_TSGP1402_gpsgnep1. Verify that TSGP1402 has been registered for concurrent pro
cessing.
ORACLE error 8102 in cleanup_node

Cause: cleanup_node failed due to ORA-08102: index key not found, obj# 40781, dba 121643011 (2)
ORA-06512: at "APPS.FND_CP_FNDSM", line 29
ORA-06512: at line 1.

Looks like object# 40781 is corrupt too:

SQL> select object_name
2 from dba_objects
3 where object_id=40781;

FND_CONCURRENT_PROCESSES_N1

The index was dropped and recreated and the concurrent managers started up.

Meanwhile for the corrupt FND_ENV_CONTEXT, Oracle gave this solution:

1. Make sure that concurrent managers are not running and there are no FNDLIBR processes.

2. drop table FND_ENV_CONTEXT as APPLSYS user.

3. Open telnet window and source apps environment.

4. Execute the following:

adodfcmp userid=applsys/apps mode=tables odffile=/patch/115/odf/afcmgr.odf
touser=apps/ priv_schema=system/ logfile="adodfcmp.log"