David Marcos' Blog

ORA-00600: internal error code, arguments: [kkpo_rcinfo_defstg:objnotfound], [xxx] after upgrade to 11.2.0.2 18 January 2011

First of all, I would like to say thanks to Coskan (Josh) Gundogar for his invaluable assistance with resolving this problem. You can find more about Coskan here.

On Saturday January 15th we had to upgrade one of our busiest and biggest databases to 11.2.0.2 to fix several critical bugs on 11.2.0.1 that affected our systems.

After upgrading the database and initial test, sign off was given for the upgrade.

On Sunday January 16th (only one day after) I had several emails and couple of phone calls to check the database as we were having ORA-00600.

Please, be aware that ORA-00600 should be raised with Oracle. The steps below worked for us, but you will need to confirm with Oracle, your business and your Developers.

The problem:

The command generating the error was a very simple one:

ERROR at line 1:
ORA-00600: internal error code, arguments: [kkpo_rcinfo_defstg:objnotfound], [1002287], [], [], [], [], [], [], [], [], [], []
ORA-06512: at "xxx.DELETE_SOURCE", line 54
ORA-06512: at line 1

The command at line 54 is:

DELETE FROM xxx.SOURCE WHERE SOURCEID = v_source;

I could find very little information on Oracle Support regarding this error.

The solution:

First thing we did was to raise a Sev. 1 with Oracle. Their suggestion was to restore the database from backup, meaning we had to downgrade primary database plus 2 standby databases.

After successfully reproducing the error I tried a different SQL, to find out if it was something wrong with the table.

I did try to execute select, insert and update, and all three worked fine:

SQL> select *  FROM xxx.SOURCE WHERE SOURCEID = 49;

SOURCEID SOURCENAME  ABBREV
---------- ----------- ----------------
49 xxxxx yyyyy

SQL> select count(*) from xxx.source where sourceid  = 90000;

COUNT(*)
----------
0

SQL> delete from  xxx.source where sourceid  = 90000;

0 rows deleted.

SQL> rollback;

SQL> update xxx.source set sourcename = 'pr newswire' where sourceid = 49;

1 row updated.

SQL> rollback;

Rollback complete.

But the delete for an existing row failed:

SQL>   DELETE FROM xxx.SOURCE WHERE SOURCEID = 49;
DELETE FROM xxx.SOURCE WHERE SOURCEID = 49
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [kkpo_rcinfo_defstg:objnotfound],
[1002300], [], [], [], [], [], [], [], [], [], []

After some trial and error and different not very successful solutions we run a trace on the SQL (something to remember for next time, one of the first things to do and well worth to spend time reading).

The trace file showed the following:

=====================
PARSING IN CURSOR #47580064284056 len=283 dep=2 uid=0 oct=3 lid=0 tim=1295189617113218 hv=1612870922 ad='a06c7d880' sqlid='gnkrt49h24x8a'
select pctfree_stg, pctused_stg, size_stg,initial_stg, next_stg, minext_stg, maxext_stg, maxsiz_stg, lobret_stg,mintim_stg, pctinc_stg, initra_stg, maxtra_stg, optimal_stg, maxins_stg,frlins_stg, flags_stg, bfp_stg, enc_stg, cmpflag_stg, cmplvl_stg from deferred_stg$  where obj# =:1
END OF STMT
PARSE #47580064284056:c=1000,e=602,p=0,cr=0,cu=0,mis=1,r=0,dep=2,og=4,plh=0,tim=1295189617113216
BINDS #47580064284056:
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=2b46190f4a70  bln=22  avl=04  flg=05
value=1002300
EXEC #47580064284056:c=1000,e=1571,p=0,cr=0,cu=0,mis=1,r=0,dep=2,og=4,plh=866645418,tim=1295189617114927
FETCH #47580064284056:c=0,e=34,p=0,cr=1,cu=0,mis=0,r=0,dep=2,og=4,plh=866645418,tim=1295189617114999
STAT #47580064284056 id=1 cnt=0 pid=0 pos=1 obj=1062363 op='TABLE ACCESS BY INDEX ROWID DEFERRED_STG$ (cr=1 pr=0 pw=0 time=49 us cost=1 size=31 card=1)'
STAT #47580064284056 id=2 cnt=0 pid=1 pos=1 obj=1062364 op='INDEX UNIQUE SCAN I_DEFERRED_STG1 (cr=1 pr=0 pw=0 time=35 us cost=0 size=0 card=1)'
CLOSE #47580064284056:c=0,e=6,dep=2,type=3,tim=1295189617115210

*** 2011-01-16 14:53:37.357
<strong>DDE: Problem Key 'ORA 600 [kkpo_rcinfo_defstg:objnotfound]' was flood controlled (0x6) (incident: 812751)</strong>
<strong>ORA-00600: internal error code, arguments: [kkpo_rcinfo_defstg:objnotfound], [1002300], [], [], [], [], [], [], [], [], [], []</strong>
=====================
PARSE ERROR#47580064285064:len=92 dep=1 uid=0 oct=3 lid=0 tim=1295189617357412 err=600
select /*+ all_rows */ count(1) from "xxx"."INSTRUMENT_INSTRUMENTTAG" where "SOURCEID" = :1
EXEC #47580064671128:c=4059383,e=4121486,p=5,cr=331742,cu=129,mis=0,r=0,dep=0,og=1,plh=3685465056,tim=1295189617357720
ERROR #14:err=600 tim=1295189617357745
STAT #47580064671128 id=1 cnt=0 pid=0 pos=1 obj=0 op='DELETE  SOURCE (cr=0 pr=0 pw=0 time=17 us)'
STAT #47580064671128 id=2 cnt=1 pid=1 pos=1 obj=1063845 op='INDEX UNIQUE SCAN PK_SOURCE (cr=1 pr=0 pw=0 time=39 us cost=0 size=12 card=1)'
WAIT #47580064671128: nam='SQL*Net break/reset to client' ela= 20 driver id=1413697536 break?=1 p3=0 obj#=-1 tim=1295189617358676
WAIT #47580064671128: nam='SQL*Net break/reset to client' ela= 9860 driver id=1413697536 break?=0 p3=0 obj#=-1 tim=1295189617368572
WAIT #47580064671128: nam='SQL*Net message to client' ela= 4 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1295189617368700

*** 2011-01-16 14:53:48.940
WAIT #47580064671128: nam='SQL*Net message from client' ela= 11571422 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1295189628940169
CLOSE #47580064285064:c=0,e=19,dep=1,type=0,tim=1295189628940357
CLOSE #47580064671128:c=0,e=96,dep=0,type=0,tim=1295189628940417
=====================

Note.- The above is just an extract, the trace file identified a few more tables with the same problem.

The error was occurring on a different table. This was happening as SOURCE is one of our key tables with many references to it.

After performing a select on the failing table we had the following:

select * from xxx.instrumenttag
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [kkpo_rcinfo_defstg:objnotfound], [1002298], [], [], [], [], [], [], [], [], [], []

After a conversation with the business, we confirmed those tables were created on production couple of weekends before the upgrade and they were not being used.

As we only had integrity reference and were not being used, we decided to recreate them using the DDL from QA.

Once all tables (40+) and associate indexes were created successfully, the code worked:

SQL> begin
 2   xxx.DELETE_SOURCE(49);
 3  end;
 4  /

PL/SQL procedure successfully completed.

SQL>

Thoughts:

1.- This problem was a tough one. Ora-00600 are never good, but this was one of the hardest on production, in my humble opinion, to troubleshoot without restoring and downgrading a primary and 2 standby databases.

2.- It is my believe the second parameter on the error identifies the missing object (I cannot test it now):

ORA-00600: internal error code, arguments: [kkpo_rcinfo_defstg:objnotfound], [1002287], [], [], [], [], [], [], [], [], [], []

If my assumption is right, this would have helped while troubleshooting.

3.- Oracle has a specific statement that catupgrd.sql can be rerun (Note.- ID 1100828.1), I am not so sure…

catupgrd.sql is re-runnable and the bug states the errors can be ignored

4.- We did not have the same problem on QA as those empty objects were created after we did the upgrade. This proves how difficult testing can be.

5.- With 11.2 we have something new called “Deferred Segment Creation”. It is my believe that new empty objects without segments might have been the root cause of the error during the upgrade.

This is the reason behind this new feature (From Oracle):

“When you create a non-partitioned heap table, the table segment creation is deferred to the first row insert. This functionality is enabled by default with the initialization parameter DEFERRED_SEGMENT_CREATION  set to TRUE.”

The default value for this parameter is true (find more here.

The failing command was:

PARSING IN CURSOR #47580064284056 len=283 dep=2 uid=0 oct=3 lid=0 tim=1295189617113218 hv=1612870922 ad='a06c7d880' sqlid='gnkrt49h24x8a'
select pctfree_stg, pctused_stg, size_stg,initial_stg, next_stg, minext_stg, maxext_stg, maxsiz_stg, lobret_stg,mintim_stg, pctinc_stg, initra_stg, maxtra_stg, optimal_stg, maxins_stg,frlins_stg, flags_stg, bfp_stg, enc_stg, cmpflag_stg, cmplvl_stg from deferred_stg$  where obj# =:1
END OF STMT

After recreating the object, the same query worked:

SYS@xxx> select object_id from dba_objects where object_name = 'INSTRUMENT_INSTRUMENTTAG';

 OBJECT_ID
----------
 1063905

Elapsed: 00:00:00.00
SYS@MFE> select pctfree_stg, pctused_stg, size_stg,initial_stg, next_stg, minext_stg, maxext_stg, maxsiz_stg, lobret_stg,mintim_stg, pctinc_stg, initra_stg, maxtra_stg, optimal_stg, maxins_stg,frlins_stg, flags_stg, bfp_stg, enc_stg, cmpflag_stg, cmplvl_stg
 2  from sys.deferred_stg$
 3  where obj# =1063905;

PCTFREE_STG PCTUSED_STG   SIZE_STG INITIAL_STG   NEXT_STG MINEXT_STG MAXEXT_STG MAXSIZ_STG LOBRET_STG MINTIM_STG PCTINC_STG INITRA_STG MAXTRA_STG OPTIMAL_STG MAXINS_STG FRLINS_STG
----------- ----------- ---------- ----------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------- ---------- ----------
 FLAGS_STG    BFP_STG    ENC_STG CMPFLAG_STG CMPLVL_STG
---------- ---------- ---------- ----------- ----------
 10           0                                                                                                   0          1        255
 8          0                      0          0

Elapsed: 00:00:00.02
SYS@xxx>

I am  not entirely convince catupgrd.sql will perform the link between segments (tables, indexes and LOBs) and the associate  entry on sys.deferred_stg$. Something to test on QA.

6.- Query to find tables without segments:

6.1.- Coskan’s version used while troubleshooting:

SELECT 'select count(*) from ' || owner || '.' || table_name || ' ;'
 FROM dba_tables
 WHERE segment_created = 'NO';

6.2.- My version to verify objects (valid for INDEX PARTITION, TABLE PARTITION, TABLE, INDEX although the version below only checks tables):

SELECT owner, object_name, object_type
 FROM sys.deferred_stg$, dba_objects
 WHERE obj# = object_id
 AND object_type = 'TABLE';

As always, comments are welcome.

ORA-01548: active rollback segment ‘_SYSSMU1_xxxx’ found, terminate dropping tablespace while deleting UNDOTBS 9 November 2010

Last week I had to solve a strange error on one of our production databases. Luckily for us, it was the RMAN catalog.

I said luckily for us because this database is internal to us. The same problem on a production database  serving our clients could have been disastrous as we have tight deadlines.

The problem:

The UNDOTBS for our RMAN Catalog database was dropped. Any attempts to switch to a newly created UNDOTBS or removing the old UNDOTBS failed with the following error:

SQL> drop tablespace UNDOTBS01 including contents;
drop tablespace UNDOTBS01 including contents
*
ERROR at line 1:
ORA-01548: active rollback segment '_SYSSMU1_1255349037$' found, terminate dropping tablespace

The solution:

Our RMAN Catalog has a standby database for protection. The first thing I did was to stop the standby database from applying more archivelogs as the UNDOTBS01 existed. This would have allowed me to perform a failover if necessary.

As I mentioned before, I created a new UNDOTBS3 to check if I could modify the undo_tablespace parameter, but it did not work:

SQL> ALTER SYSTEM SET UNDO_TABLESPACE = undotbs3;
System altered.

SQL> drop tablespace UNDOTBS01 including contents;
drop tablespace UNDOTBS01 including contents
*
ERROR at line 1:
ORA-01548: active rollback segment '_SYSSMU1_1255349037$' found, terminate dropping tablespace

Tried to delete the rollback segment, but it did not work:

SQL> drop rollback segment '_SYSSMU1_1255349037$';
drop rollback segment '_SYSSMU1_1255349037$'
*
ERROR at line 1:
ORA-02175: invalid rollback segment name

In fact, it did not even exist:

SQL> select * from v$rollname ;
USN NAME
---------- ------------------------------
0 SYSTEM
18 _SYSSMU18_650722988$
19 _SYSSMU19_176786070$
20 _SYSSMU20_2971238361$
21 _SYSSMU21_1337078425$
22 _SYSSMU22_378998127$
23 _SYSSMU23_2828850578$
24 _SYSSMU24_3342375805$
25 _SYSSMU25_1071547613$
26 _SYSSMU26_357778769$
27 _SYSSMU27_3256159541$

So, how to fix it?

I did execute the following steps:

1.- Create a copy of the spfile:

create pfile from spfile;

So I could rename the spfile and work with this init.ora file until I fixed the problem.

2.- Stop the database:

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit

3.- Amended the init.ora file with the following:

*.undo_management='MANUAL'
*.undo_retention=1800
*.undo_tablespace='UNDOTBS3'
*._offline_rollback_segments=_SYSSMU1_1255349037$

4.- Start database up and try to delete the UNDOTBS01>

SQL> drop tablespace undotbs01 including contents and datafiles;
drop tablespace undotbs01 including contents and datafiles
*
ERROR at line 1:
ORA-01548: active rollback segment '_SYSSMU3_1255349037$' found, terminate dropping tablespace

As you can see, the rollback segment is different, it changed from _SYSSMU1_1255349037$ to _SYSSMU3_1255349037$ (the difference is the digit after SYSSMU).

After several tries, I realised I had to add as many entries to _offline_rollback_segments as entries I had on v$rollname where NAME LIKE ‘%SYSSMU%’ (10 in my case).

so I stopped the database again and made the necessary changes:

5.- Edit init.ora file and add as many entries as rows has V$rollname where name like ‘%SYSSMU%’:

*._offline_rollback_segments=(_SYSSMU1_1255349037$,_SYSSMU2_1255349037$,
_SYSSMU3_1255349037$,_SYSSMU4_1255349037$,
_SYSSMU5_1255349037$,_SYSSMU6_1255349037$,
_SYSSMU7_1255349037$,_SYSSMU8_1255349037$,
_SYSSMU9_1255349037$,_SYSSMU10_1255349037$)

6.- Start up the database and drop UNDOTBS01

SQL> drop tablespace undotbs01 including contents and datafiles;
Tablespace dropped.

7.- and create UNDOTBS01:

SQL> CREATE UNDO TABLESPACE "UNDOTBS01" DATAFILE '/u01/oradata/CATALOG/undo01.dbf' SIZE 1024M;
Tablespace created.

7.- Shutdown the database, rename (or delete) the init.ora file and start up the database using the spfile.

8.- Set UNDOTBS01 as the UNDO tablespace:

SQL> alter system set undo_tablespace=undotbs01 scope=both;

9.- Check parameters:

SQL> show parameter undo

NAME                                                   TYPE              VALUE
———————————— ———– ——————————
undo_management                      string            AUTO
undo_retention                             integer         1800
undo_tablespace                          string             UNDOTBS01

10.- and drop UNDOTBS3:

SQL> drop tablespace undotbs3 including contents and datafiles;
Tablespace dropped.

As soon as the database was running as normal I executed a backup to ensure all was ok.

After the verification, I enabled the recovery to keep the standby database in synch with the primary.

Note.- It is not necessary to add the full name _SYSSMU1_1255349037$ to _offline_rollback_segments. Only the first part (SYSSMU1) would have been enough.

_offline_rollback_segments=(_SYSSMU1_1255349037$,_SYSSMU2_1255349037$) is the same than

_offline_rollback_segments=(_SYSSMU1,_SYSSMU2)

As always, comments are welcome.