Filed 05-SEP-2000 Updated 14-SEP-2000 Product Oracle Server - Enterprise Edition V7 Product Version 8.1.6.0 Platform IBM RS/6000 AIX Platform Version No Data RDBMS Version 8.1.6.0 Affects Platforms Generic Priority Severe Loss of Service Status Code Bug (Response/Resolution) Base Bug N/A Fixed in Product Version No Data Problem statement: SMON ORA-00600 [KCBGCUR_9] DROP LOCALLY MANAGED TABLESPACE WITH COMPATIBLE=8.1.6 -------------------------------------------------------------------------------- PROBLEM: 1. Clear description of the problem encountered: running a 8.1.6 with parameter "compatible=8.1.6 " , doing a drop tablespace of a read only locally managed tablespace SMON dead with error : ORA-00600: internal error code, arguments: [kcbgcur_9] stack : ksedmp ksfdmp kgeasnmierr kcbgcur ktecgsc ktecgetsh ktecgsh kteinicnt ktssdt_segs ktmmon ksbrdp opirip opidrv sou2o main . instance dead, and next database open fails with the same error in SMON , . 2. Pertinent configuration information (MTS/OPS/distributed/etc) 3. Indication of the frequency and predictability of the problem problem can be reproduced at will 4. Sequence of events leading to the problem make sure you have compatible = 8.1.6 in your init.ora , run this SQL script as SYS !rm /tmp/fichero1.dbf create tablespace metadata datafile '/tmp/fichero1.dbf' size 15M extent management local uniform ; . create table emp ( c1 varchar2(10)) tablespace metadata; . create rollback segment monster storage (initial 100 k next 100 k maxextents unlimited) tablespace metadata; alter rollback segment monster online; . alter rollback segment monster offline; alter tablespace metadata read only; . drop table emp; drop rollback segment monster; drop tablespace metadata ; . . SMON will die with ORA-00600: [kcbgcur_9] . 5. Technical impact on the customer. Include persistent after effects. ========================= DIAGNOSTIC ANALYSIS: . ktsagfr is not executed by smon nor shadow process running the script, so it seems it is not Bug:1259735 once the ora-600 is hit the first time, when you try to open the database SMON will hit again the same error , so it must be something in the code used by SMON ========================= WORKAROUND: "drop tablespace metadata including contents " instead of "drop tablespace metadata ;" . once the ora-600 is hit and you restart the data base SMON will always die with the very same error, workaround then is : startup mount alter database datafile '....' offline drop ; for all the metadata's files alter database open ; drop tablespace metadata including contents ; . ========================= RELATED BUGS: ========================= REPRODUCIBILITY: 1. State if the problem is reproducible; indicate where and predictability problem can be reproducet at will, see above 2. List the versions in which the problem has reproduced 8.1.6.0 in Linux 8.1.6.0 in AIX 3. List any versions in which the problem has not reproduced ========================= TESTCASE: see above it is also in bug$ tc.sql ======================== STACK TRACE: ksedmp ksfdmp kgeasnmierr kcbgcur ktecgsc ktecgetsh ktecgsh kteinicnt ktssdt_segs ktmmon ksbrdp opirip opidrv sou2o main ========================= SUPPORTING INFORMATION: testcase is tc.sql alert file is tc.log and smon's trace file are : smon_1338.trc and smon_1429.trc . ========================= 24 HOUR CONTACT INFORMATION FOR P1 BUGS: Jose Miguel Juarros please call : +34+609115813 or +34+916312137 or +34+918567537 or send a short message to 609115813correo.movistar.net or send email to JoseMiguel.Juarrosoracle.com , if I'm dealing with a P1 I will be checking email. ========================= DIAL-IN INFORMATION: ========================= IMPACT DATE: ============ *** JJUARROS 09/05/00 11:11 am *** (CHG: Sta->11) *** JJUARROS 09/05/00 11:11 am *** supporting information is in ess30.us.oracle.com /bug/bug1397075 -rw-r--r-- 1 500 501 7228 Sep 5 12:02 ct.log --> alert.log -rw-r--r-- 1 500 501 443495 Sep 5 12:03 smon_1338.trc -rw-r--r-- 1 500 501 438277 Sep 5 12:03 smon_1429.trc -rw-r--r-- 1 500 501 483 Sep 5 12:06 tc.sql --> TESTCASE . Bug screening completed. From the call stack, this seems to be a buffer cache problem (kcbgcur). Please reassign to rdbmsrep if it is not the case. Thanks I'll take this as it is more than likely a space bug (and EMEA at that). Spoken to Bernard - he has a telco customer with the same problem. Testcase above doesn't reproduce on 8.1.6 or 8.2 Solaris. You get the error "ORA-01549: tablespace not empty, use INCLUDING CONTENTS option". I also got ora-1549 in Linux and AIX, and shortly after that SMON dead with the ora-600. I need to be able to reproduce on Solaris. Please try and reproduce there and if successful, provide me with the init.ora etc. If not, try and find out if it is an instance configuration issue rather than architecture. Thanks. Jose, sorry to jump in onto your bug, but I may have pretty good info on this problem. I am working on the telco customer with Bernard on this. Postbank (ING) NV with TAR They are on AIX SP 81600. TESTCASE ======== I have got a reproducable testcase on a vanilla AIX 8161. I have not stripped it to be as small as possible; do as you please :-) . Looks like the problem won't repro on Solaris. I tried 8162 and 8161 w/o luck. I will try my repro on other ports too. . set echo on connect system/manager drop tablespace MM_TEMP including contents; drop tablespace MM_DATA including contents; drop tablespace MM_IDX including contents; . create temporary tablespace MM_TEMP tempfile '/server1/oradata/v816/mm_temp01.dbf' size 1M reuse extent management local uniform size 10k / create tablespace MM_DATA datafile '/server1/oradata/v816/mm_data01.dbf' size 1M reuse extent management local uniform size 10k / create tablespace MM_IDX datafile '/server1/oradata/v816/mm_idx01.dbf' size 1M reuse extent management local uniform size 10k / alter user scott default tablespace MM_DATA temporary tablespace MM_TEMP; -- connect scott/tiger drop table bla; create table bla (x number, str varchar2(2000)) tablespace MM_DATA; declare x integer; str varchar2(2000); begin str := 'qwertyuiopasdfghjklzxcvbnmqwertyuiopasdfghjklzxcvbnmqwertyuiopasdf ghjklzxcvbnmqwertyuiopasdfghjklzxcvbnmqwertyuiopasdfghjklzxcvbnmqwertyuiopasdfg h jklzxcvbnm'; FOR x IN 1..2048 LOOP insert into bla values (x, str); END LOOP; end; / commit; create index idx_bla on bla(x, str) tablespace MM_IDX; -- connect system/manager alter tablespace MM_DATA read only; alter tablespace MM_IDX read only; -- connect scott/tiger drop index idx_bla; create index idx_bla on bla(x, str) tablespace MM_DATA; -- connect system/manager alter tablespace MM_DATA read write; -- connect scott/tiger drop index idx_bla; create index idx_bla on bla(x, str) tablespace MM_DATA; create index idx_bla2 on bla(str, x) tablespace MM_DATA; -- DIAGNOSIS ========= The catch is: the last creation of idx_bla must succeed; but idx_bla2 must fail with ORA-1652. Then, it appears as if SMON tries to cleanup TEMPORARY segments from both MM_DATA (which is READ WRITE) and MM_IDX (which is READ ONLY). . WORKAROUND ========== A workaround to avoid this is to use dictionary managed tablespaces or to keep the tablespace READ WRITE. . When the database *has* crashed, you can startup with an event set in init.ora. event = "10061 trace name context forever, level 10" Use startup restrict, set the tablespace READ WRITE, shutdown and then startup again. During the recovery phase on startup, SMON will be able to do its cleanup job in the READ WRITE tablespace. will try my testcase in Solaris Ran my testcase successfully on V816 on rtcaix1.us. Had to change str to varchar2(200) to avoid ORA-01450: maximum key length (758) exceeded (This database has 2k blocks, while mine as 16k blocks.) My script is in ~usupport/mmeijn/repromm.sql Also tried to repro on: hostname OS version repro nldell02 Red Hat 2.2.14 v8160 Y nlhp2 HP-UX 11.0 v81610 N nldec02 OSF1 V4.0D v81610 N reproduced with my testcase in Solaris : NB: it reproduces if and only if COMPATIBLE= 8.1.6 as said before , you can see the Solaris trace files in tcsol1.us.oracle.com directory : /u02/home/usupport/txemy files : tc.sql -- to reproduce the problem repair.sql -- to repair the problem alert_V816.log -- alert file v816_smon_21783.trc -- SMON's trace file it must be something related with "Faster segment drop" , Fast Drop freelists reproduced also in 8.1.6.1.0 in Linux. OK, reproduced. The testcase you used differs from that in the bug description above - this is probably the reason why I couldn't reproduce. no, must be something else, the problem does reproduces with the testcase in the bug description above, see tc2.sql and repair.sql and /u02/app/oracle/admin/V816/bdump/v816_smon_25783.trc in tcsol1.us.oracle.com tc2.sql contains exactly the same steps described in "4. Sequence of events leading to the problem ", may be when you didnt reproduced you had compatible=8.1.0 and not compatible=8.1.6 as instructed Colleague also reproduced on NT. .. No, I had compatible set to 8.1.6. Another customer met this error as well.