This week, come across a new ORA-00600. Search on metalink and google, none recored return.
So paste it here.
Fri Mar 25 02:10:48 2011
Errors in file /s611/dba01/oracle/dump01/cubes/udump/cubes_ora_23620.trc:
ORA-00600: internal error code, arguments: [ktspfmthwm], [28339], [28350], [], [], [], [], []
First argument is ktspfmthwm, kt means kernel transaction, sp means space(check), fmt means format. hwm means "High Water Mark".
cubes_ora_23620.trc：
Current SQL statement for this session:
INSERT /*+ SYS_DL_CURSOR */ INTO SSS_LOADER_STG.FND_DOCUMENTS_TL
("DOCUMENT_ID","CREATION_DATE","CREATED_BY","LAST_UPDATE_DATE","LAST_UPDATED_BY","LAST_UPDATE_LOGIN","LANGUAGE","DESCRIPTION","SHORT_TEXT","FILE_NAME","MEDIA_ID","REQUEST_ID","PROGRAM_APPLICATION_ID","PROGRAM_ID","PROGRAM_UPDATE_DATE","DOC_ATTRIBUTE_CATEGORY","DOC_ATTRIBUTE1","DOC_ATTRIBUTE2","DOC_ATTRIBUTE3","DOC_ATTRIBUTE4","DOC_ATTRIBUTE5","DOC_ATTRIBUTE6","DOC_ATTRIBUTE7","DOC_ATTRIBUTE8","DOC_ATTRIBUTE9","DOC_ATTRIBUTE10","DOC_ATTRIBUTE11","DOC_ATTRIBUTE12","DOC_ATTRIBUTE13","DOC_ATTRIBUTE14","DOC_ATTRIBUTE15","APP_SOURCE_VERSION","SOURCE_LANG"
(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL)；
Call Stack:
calling call entry argument values in hex
location type point (? means dubious value)
-------------------- -------- -------------------- ----------------------------
..........omit .........
kgeasnmierr()+163 call kgerinv() 0068A38A0 ? 007A4F420 ?
7FFF014969A0 ? 7FFF014968E0 ?
000000000 ? 000000000 ?
ktspfmthwm()+737 call kgeasnmierr() 0068A38A0 ? 007A4F420 ?
7FFF014969A0 ? 7FFF014968E0 ?
000000000 ? 000006EB3 ?
ktsmg_seg()+3500 call ktspfmthwm() 2B18AB3A4A08 ? 000000003 ?
000000000 ? 7FFF014968E0 ?
000000000 ? 000006EB3 ?
kdblfpl()+1544 call ktsmg_seg() 2B18AB3A4A08 ? 7FFF014983E0 ?
000000003 ? 000000000 ?
000000000 ? 800000000 ?
..........omit .........
The Hint /*+ SYS_DL_CURSOR */ is very similiar with /*+APPEND */，when use sqlldr to perform a direct path load, you could see it.
From ktspfmthwm()+737，we know the first argument ktspfmthwm is oracle's function name. And the second parameter 28339 is one of the function's parameter 000006EB3。
Now that the ORA600 looks somehow related with High Water Mark, let's make a dump to check.
Segment header dump：
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 28350 #blocks: 124812
last map 0x9401d550 #maps: 28 offset: 5452
Highwater:: 0xa181c085 ext#: 28339 blk#: 4 ext size: 4
#blocks in seg. hdr's freelists: 0
#blocks below: 124768
mapblk 0x9401d550 offset: 297
Unlocked
--------------------------------------------------------
Low HighWater Mark :
Highwater:: 0xa181c085 ext#: 28339 blk#: 4 ext size: 4
#blocks in seg. hdr's freelists: 0
#blocks below: 124768
mapblk 0x9401d550 offset: 297
Level 1 BMB for High HWM block: 0x9fc1d6c1
Level 1 BMB for Low HWM block: 0x9fc1d6c1
--------------------------------------------------------
Segment Type: 1 nl2: 1 blksz: 16384 fbsz: 0
L2 Array start offset: 0x00002994
First Level 3 BMB: 0x9f800217
L2 Hint for inserts: 0x944000b6
Last Level 1 BMB: 0x9fc1d6dd
Last Level II BMB: 0x9401d93e
Last Level III BMB: 0x9401d93f
Map Header:: next 0x9f8003b0 #extents: 647 obj#: 403905 flag: 0x10000000
Inc # 0
Now we know exact meaning of this ORA600, the second argument 28339 is High High Water Mark's Position.(Maybe low high water mark)
The High High Water Mark is on the 28339+1=28340th extent.
The third argument 28350 means this segment has 28350 extents.
Under low high water mark, all block are formated. Between low high and high high water mark, blocks could be either formated or not. Above high high water mark, all blocks are unformated.
Pay attention to "Last Level III BMB: 0x9401d93f", It is a level three bit map block
Check it through, none wired place found, so we dump the first level bit map block where exist the high high water mark and low high water mark：
Dump of First Level Bitmap Block
--------------------------------
nbits : 4 nranges: 3 parent dba: 0x9401d926 poffset: 2
unformatted: 0 total: 12 first useful block: 1
owning instance : 1
instance ownership changed at 03/25/2011 02:10:47
Last successful Search 03/25/2011 02:10:47
Freeness Status: nf1 0 nf2 0 nf3 0 nf4 2
Extent Map Block Offset: 4294967295
First free datablock : 10
Bitmap block lock opcode 0
Locker xid: : 0x0000.000.00000000
Inc #: 0 Objd: 403905
HWM Flag: HWM Set
Highwater:: 0xa181c085 ext#: 28339 blk#: 4 ext size: 4
#blocks in seg. hdr's freelists: 0
#blocks below: 124768
mapblk 0x9401d550 offset: 297
--------------------------------------------------------
DBA Ranges :
--------------------------------------------------------
0x9fc1d6c1 Length: 4 Offset: 0
0xa001e7e5 Length: 4 Offset: 4
0xa181c081 Length: 4 Offset: 8
0:Metadata 1:FULL 2:FULL 3:FULL
4:FULL 5:FULL 6:FULL 7:FULL
8:FULL 9:FULL 10:75-100% free 11:75-100% free
--------------------------------------------------------
End dump data blocks tsn: 55 file#: 639 minblk 120513 maxblk 120513
..........omit .........
More......
GE, AGFA. experienced in 9i/10g/11g/12c, AdvanceReplication, Stream, GoldenGate, RAC, DataGuard, ODA, EXADATA, GridControl. In my career I have always been assigned to handle most complicated and critical oracle issues without solution online, and I hope to record some of them via this blog.
March 26, 2011
ORA-00600: internal error code, arguments: [ktspfmthwm], [28339], [28350], [], [], [], [], []
March 1, 2011
Add Column Internal
Yesterday, got one ticket, add column to a table. In order to make sure it wouldn't effect the running application based on that table, I make a "Add Column" experiment to test its influence.
Conclusion is:
alter table xxx add column nullable is a very light operation. Oracle only update the dictionary table with out making a full table scan. So even a huge big table can finish immediately.
On the contrary,
alter table xxx add column not null, Oracle will scan the segment and check and update all the blocks. It may cost very long time if the table is big. And because it will hold exclusive TM lock, all other operation on that table will be blocked until the "Add Column" command finished.
Following is my experiment :
SQL> conn bbed/oracle
Connected.
SQL> create table xxx (c1 int,c2 char(2));
Table created.
SQL> insert into xxx values(1,'a');
1 row created.
SQL> commit;
Commit complete.
SQL> select dump(rowid,16) from xxx;
DUMP(ROWID,16)
--------------------------------------------------------------------------------
Typ=69 Len=10: 0,0,27,68,1,40,0,f,0,0
#From the rowid we know this row is in file 5 block 15.
SQL> alter system flush buffer_cache;
System altered.
#Check the row data in block. "alter system dump datafile 5 block 15" can't show you the real information.
BBED> set file 5 block 15
FILE# 5
BLOCK# 15
BBED> map
File: /duckdb/oracle/duck/data/bbed01.dbf (5)
Block: 15 Dba:0x0140000f
------------------------------------------------------------
KTB Data Block (Table/Cluster)
struct kcbh, 20 bytes @0
struct ktbbh, 72 bytes @20
struct kdbh, 14 bytes @100
struct kdbt[1], 4 bytes @114
sb2 kdbr[1] @118
ub1 freespace[8059] @120
ub1 rowdata[9] @8179
ub4 tailchk @8188
BBED> p *kdbr[0]
rowdata[0]
----------
ub1 rowdata[0] @8179 0x2c
BBED> set offset 8179
OFFSET 8179
BBED> dump
File: /duckdb/oracle/duck/data/bbed01.dbf (5)
Block: 15 Offsets: 8179 to 8191 Dba:0x0140000f
------------------------------------------------------------------------
2c010202 c1020261 20030641 a7
<32 bytes per line>
#Now we add a nullable column.
SQL> alter table xxx add (c3 varchar2(20));
Table altered.
SQL> alter system flush buffer_cache;
System altered.
#Check the row data in block again.
BBED> p *kdbr[0]
rowdata[0]
----------
ub1 rowdata[0] @8179 0x2c
BBED> set offset 8179
OFFSET 8179
BBED> dump
File: /duckdb/oracle/duck/data/bbed01.dbf (5)
Block: 15 Offsets: 8179 to 8191 Dba:0x0140000f
------------------------------------------------------------------------
2c010202 c1020261 20030641 a7
<32 bytes per line>
#We can see the row data is not changed.
#Let's continue to add a not null column.
SQL> alter table xxx add (c4 varchar2(20) default 'dba' not null);
Table altered.
SQL> alter system flush buffer_cache;
System altered.
#Check the row data in block again.
BBED> map
File: /duckdb/oracle/duck/data/bbed01.dbf (5)
Block: 15 Dba:0x0140000f
------------------------------------------------------------
KTB Data Block (Table/Cluster)
struct kcbh, 20 bytes @0
struct ktbbh, 72 bytes @20
struct kdbh, 14 bytes @100
struct kdbt[1], 4 bytes @114
sb2 kdbr[1] @118
ub1 freespace[8045] @120
ub1 rowdata[23] @8165
ub4 tailchk @8188
BBED> p *kdbr[0]
rowdata[0]
----------
ub1 rowdata[0] @8165 0x2c
BBED> set offset 8165
OFFSET 8165
BBED> dump
File: /duckdb/oracle/duck/data/bbed01.dbf (5)
Block: 15 Offsets: 8165 to 8191 Dba:0x0140000f
------------------------------------------------------------------------
2c020402 c1020261 20ff0364 62612c00 0202c102 02612001 06c4a8
<32 bytes per line>
#We can see the oracle had change the row data on the block from "2c00 0202c102 026120" to "2c020402 c1020261 20ff0364 6261".
Now we know, if we want to add a column in production database, we had better choose nullable column.
Some people may got a question. If oracle add nullable column without modify block, then what will happen when oracle want to read data from block?
Oracle will do in this way: when read row data from block and only find tow columns, meanwhile oracle know the table should have 4 columns from col$, then oracle will add value "null" to following tow columns.
More......