首页 » OGG, ORACLE 9i-23ai » ora-600 [kcbgcur_9] internal error when ALTER DATABASE ADD SUPPLEMENTAL LOG DATA
ora-600 [kcbgcur_9] internal error when ALTER DATABASE ADD SUPPLEMENTAL LOG DATA
今天在配置GOLDENGATE时在增加附加日志时遇到了ora-600 ,下面记录一下解决过程
[oracle@dev-db ogg11r2]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Oct 10 13:05:43 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [kcbgcur_9], [8388681], [25],
[100663296], [33554432], [], [], []
SQL> select SUPPLEMENTAL_LOG_DATA_MIN from v$database;
SUPPLEME
--------
NO
SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 57572
Current log sequence 57575
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [kcbgcur_9], [8388681], [25],
[100663296], [33554432], [], [], []
/oracle/admin/devdb/udump/devdb_ora_22143.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /oracle/product/10.2.0
System name: Linux
Node name: dev-db
Release: 2.6.9-67.ELsmp
Version: #1 SMP Fri Nov 16 12:49:06 EST 2007
Machine: x86_64
Instance name: devdb
Redo thread mounted by this instance: 1
Oracle process number: 56
Unix process pid: 22143, image: oracle@dev-db (TNS V1-V3)
*** SERVICE NAME:(SYS$USERS) 2012-10-10 13:05:46.872
*** SESSION ID:(1771.4915) 2012-10-10 13:05:46.872
*** 2012-10-10 13:05:46.872
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [kcbgcur_9], [8388681], [25], [100663296], [33554432], [], [], []
Current SQL statement for this session:
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA
----- Call Stack Trace -----
calling call entry argument values in hex
location type point (? means dubious value)
-------------------- -------- -------------------- ----------------------------
ksedst()+31 call ksedst1() 000000000 ? 000000001 ?
000000000 ? 000000000 ?
000000000 ? 000000001 ?
ksedmp()+610 call ksedst() 000000000 ? 000000001 ?
000000000 ? 000000000 ?
000000000 ? 000000001 ?
ksfdmp()+21 call ksedmp() 000000003 ? 000000001 ?
000000000 ? 000000000 ?
000000000 ? 000000001 ?
kgerinv()+161 call ksfdmp() 000000003 ? 000000001 ?
000000000 ? 000000000 ?
000000000 ? 000000001 ?
kgeasnmierr()+163 call kgerinv() 006469D40 ? 2A971F0040 ?
000000000 ? 000000000 ?
000000001 ? 000000001 ?
kcbgcur()+4762 call kgeasnmierr() 006469D40 ? 2A971F0040 ?
000000000 ? 000000000 ?
000000000 ? 000800049 ?
ktugusc()+699 call kcbgcur() 00528F7A0 ? 000000001 ?
000000000 ? 000000000 ?
000000000 ? 000800049 ?
ktucwt3()+267 call ktugusc() 7FBFFF3650 ? 017F7E490 ?
000000001 ? 000000000 ?
000000000 ? 000000000 ?
dbs_about_to_modify call ktucwt3() 7FBFFF3C90 ? 00147AE14 ?
_slog_data()+1929 000000001 ? 000000000 ?
000000000 ? 000000000 ?
dbsucf()+3272 call dbs_about_to_modify 000000041 ? 000000008 ?
_slog_data() 7FBFFF42DC ? 7FBFFF42E4 ?
646000000000001 ? 000000000 ?
adbdrv()+47228 call dbsucf() 000000041 ? 000000008 ?
21A55C430 ? 7FBFFF42E4 ?
从ora-600 [kcbgcur_9] 的第二个参数是DBA,根据DBA计算datafile number
SQL> select dbms_utility.data_block_address_file(8388681) from dual;
DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(8388681)
---------------------------------------------
2
SQL> select name from v$datafile where file#=2;
NAME
--------------------------------------------------------------------------------
/oradata/devdb/undotbs01.dbf
从上面的可以判断是个undo文件，查看该undo tablespace发现这个文件是它的first file，表空间的第一个文件不可以删除替换。所以我再新建undo一个undotbs2,替换原来的undotbs1
create undo tablespace undotbs2 datafile '/backup/oradata/undotbs201.dbf' size 1g;
alter tablespace undotbs2 add datafile '/oradata/devdb/undotbs202.dbf' size 1g;
SQL> alter system set undo_tablespace=undotbs2;
System altered.
SQL> SELECT a.name,b.status
2 FROM v$rollname a,v$rollstat b
3 WHERE a.usn = b.usn
4 AND a.name IN (
5 SELECT segment_name
6 FROM dba_segments
7 WHERE tablespace_name = 'UNDOTBS1'
8 );
NAME STATUS
------------------------------ ---------------
_SYSSMU5$ PENDING OFFLINE
SQL> SELECT a.name,b.status , d.username , d.sid , d.serial#
2 FROM v$rollname a,v$rollstat b, v$transaction c , v$session d
3 WHERE a.usn = b.usn
4 AND a.usn = c.xidusn
5 AND c.ses_addr = d.saddr
6 AND a.name IN (
7 SELECT segment_name
8 FROM dba_segments
9 WHERE tablespace_name = 'UNDOTBS1'
10 );
NAME STATUS USERNA SID SERIAL#
---------- --------------- ------ ---------- ----------
no rows selected
发现untotbs1还有个回滚段在使用状态是PENDING OFFLINE，但与此关连的transaction已不存在,那就没法简单kill session/rollback transaction,接一来dump undo header查看一下此undo segment事件信息
SQL> alter system dump undo header '_SYSSMU5$';
System altered.
SQL> oradebug setmypid;
Statement processed.
SQL> oradebug tracefile_name;
/oracle/admin/devdb/udump/devdb_ora_24351.trc
[oracle@dev-db ~]$ vi /oracle/admin/devdb/udump/devdb_ora_24351.trc
/oracle/admin/devdb/udump/devdb_ora_24351.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /oracle/product/10.2.0
System name: Linux
Node name: dev-db
Release: 2.6.9-67.ELsmp
Version: #1 SMP Fri Nov 16 12:49:06 EST 2007
Machine: x86_64
Instance name: devdb
Redo thread mounted by this instance: 1
Oracle process number: 101
Unix process pid: 24351, image: oracle@dev-db (TNS V1-V3)
*** 2012-10-10 14:36:16.762
*** SERVICE NAME:(SYS$USERS) 2012-10-10 14:36:16.761
*** SESSION ID:(2034.4056) 2012-10-10 14:36:16.761
********************************************************************************
Undo Segment: _SYSSMU5$ (5)
********************************************************************************
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 5 #blocks: 399
last map 0x00000000 #maps: 0 offset: 4080
Highwater:: 0x00800478 ext#: 2 blk#: 111 ext size: 128
#blocks in seg. hdr's freelists: 0
#blocks below: 0
mapblk 0x00000000 offset: 2
Unlocked
Map Header:: next 0x00000000 #extents: 5 obj#: 0 flag: 0x40000000
Extent Map
-----------------------------------------------------------------
0x0080004a length: 7
0x00800051 length: 8
0x00800409 length: 128
0x00800209 length: 128
0x00800309 length: 128
Retention Table
-----------------------------------------------------------
Extent Number:0 Commit Time: 1244535908
Extent Number:1 Commit Time: 1244529413
Extent Number:2 Commit Time: 0
Extent Number:3 Commit Time: 1244535908
Extent Number:4 Commit Time: 1244535908
TRN CTL:: seq: 0x0135 chd: 0x001e ctl: 0x0004 inc: 0x00000000 nfb: 0x0001
mgc: 0x8201 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe)
uba: 0x00800476.0135.12 scn: 0x0003.11a0950d
Version: 0x01
FREE BLOCK POOL::
uba: 0x00800478.0135.01 ext: 0x2 spc: 0x12dc
uba: 0x00000000.0135.0d ext: 0x2 spc: 0x17a8
uba: 0x00000000.0135.06 ext: 0x2 spc: 0xa96
uba: 0x00000000.0000.00 ext: 0x0 spc: 0x0
uba: 0x00000000.0000.00 ext: 0x0 spc: 0x0
TRN TBL::
index state cflags wrap# uel scn dba parent-xid nub stmt_num cmt
------------------------------------------------------------------------------------------------
0x00 9 0x00 0x0380 0x0020 0x0003.11a0a8a4 0x00800474 0x0000.000.00000000 0x00000002 0x00000000 1347443029
0x01 9 0x00 0x037e 0x0025 0x0003.11a09f15 0x00800472 0x0000.000.00000000 0x00000001 0x00000000 1347442905
0x02 9 0x00 0x037c 0x0012 0x0003.11a0a950 0x00800476 0x0000.000.00000000 0x00000001 0x00000000 1347443044
0x03 9 0x00 0x0381 0x0029 0x0003.11a095c6 0x0080046d 0x0000.000.00000000 0x00000001 0x00000000 1347442894
0x04 9 0x00 0x0381 0xffff 0x0003.11a0a959 0x00800478 0x0000.000.00000000 0x00000002 0x00000000 1347443044
0x00800051 length: 8
0x00800409 length: 128
0x00800209 length: 128
0x00800309 length: 128
Retention Table
-----------------------------------------------------------
Extent Number:0 Commit Time: 1244535908
Extent Number:1 Commit Time: 1244529413
Extent Number:2 Commit Time: 0
Extent Number:3 Commit Time: 1244535908
Extent Number:4 Commit Time: 1244535908
TRN CTL:: seq: 0x0135 chd: 0x001e ctl: 0x0004 inc: 0x00000000 nfb: 0x0001
mgc: 0x8201 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe)
uba: 0x00800476.0135.12 scn: 0x0003.11a0950d
Version: 0x01
FREE BLOCK POOL::
uba: 0x00800478.0135.01 ext: 0x2 spc: 0x12dc
uba: 0x00000000.0135.0d ext: 0x2 spc: 0x17a8
uba: 0x00000000.0135.06 ext: 0x2 spc: 0xa96
uba: 0x00000000.0000.00 ext: 0x0 spc: 0x0
uba: 0x00000000.0000.00 ext: 0x0 spc: 0x0
TRN TBL::
index state cflags wrap# uel scn dba parent-xid nub stmt_num cmt
------------------------------------------------------------------------------------------------
0x00 9 0x00 0x0380 0x0020 0x0003.11a0a8a4 0x00800474 0x0000.000.00000000 0x00000002 0x00000000 1347443029
0x01 9 0x00 0x037e 0x0025 0x0003.11a09f15 0x00800472 0x0000.000.00000000 0x00000001 0x00000000 1347442905
0x02 9 0x00 0x037c 0x0012 0x0003.11a0a950 0x00800476 0x0000.000.00000000 0x00000001 0x00000000 1347443044
...
0x08 9 0x00 0x037e 0x001d 0x0003.11a095ca 0x0080046e 0x0000.000.00000000 0x00000001 0x00000000 1347442894
0x09 9 0x00 0x0380 0x002f 0x0003.11a09512 0x0080046c 0x0000.000.00000000 0x00000001 0x00000000 1347442779
0x0a 9 0x00 0x0380 0x0003 0x0003.11a0954e 0x0080046d 0x0000.000.00000000 0x00000001 0x00000000 1347442794
0x0b 9 0x00 0x037e 0x001b 0x0003.11a0a90e 0x00800477 0x0000.000.00000000 0x00000001 0x00000000 1347443030
...
0x25 9 0x00 0x037e 0x0021 0x0003.11a09f1f 0x00800472 0x0000.000.00000000 0x00000001 0x00000000 1347442905
0x26 9 0x00 0x037e 0x002a 0x0003.11a09f41 0x00800472 0x0000.000.00000000 0x00000001 0x00000000 1347442919
0x27 9 0x00 0x037f 0x002b 0x0003.11a09543 0x0080046d 0x0000.000.00000000 0x00000001 0x00000000 1347442794
0x28 9 0x00 0x0380 0x0017 0x0003.11a0a920 0x00800477 0x0000.000.00000000 0x00000001 0x00000000 1347443035
0x29 9 0x00 0x037e 0x0008 0x0003.11a095c9 0x0080046e 0x0000.000.00000000 0x00000001 0x00000000 1347442894
0x2a 9 0x00 0x0380 0x0015 0x0003.11a09f47 0x00800472 0x0000.000.00000000 0x00000001 0x00000000 1347442919
0x2b 9 0x00 0x037f 0x000a 0x0003.11a0954c 0x0080046d 0x0000.000.00000000 0x00000001 0x00000000 1347442794
0x2c 9 0x00 0x037e 0x0014 0x0003.11a09eb8 0x0080046f 0x0000.000.00000000 0x00000001 0x00000000 1347442904
0x2d 2 0x97 0x0361 0x0003 0x0000.005f314e 0x00800422 0x0000.000.00000000 0x00000035 0x00800422 0
0x2e 9 0x00 0x037e 0x000b 0x0003.11a0a8fd 0x00800477 0x0000.000.00000000 0x00000001 0x00000000 1347443030
0x2f 9 0x00 0x037e 0x0024 0x0003.11a09537 0x0080046d 0x0000.000.00000000 0x00000001 0x00000000 1347442794
注意到倒数第三行有一个状态为2记录，state=9表示事务已结束，state=10表示没有commit或者rollback，states=2表示存在prepared的悬挂事务，那查询一下分布式事务
SQL> select local_tran_id,state,FAIL_TIME from dba_2pc_pending;
LOCAL_TRAN_ID STATE FAIL_TIME
---------------------- ---------------- -------------------
8.7.699 collecting 2009-06-02 17:40:11
5.45.865 prepared 2009-06-09 17:06:49
7.18.3979 collecting 2009-06-30 16:15:20
发现果然有3条记录，(注：之前有重启过实例并未自动清空)，用rollback force 回滚prepared的事务，collecting的事务会报错
SQL> rollback force '8.7.699';
rollback force '8.7.699'
*
ERROR at line 1:
ORA-02058: no prepared transaction found with ID 8.7.699
SQL> rollback force '5.45.865';
Rollback complete.
SQL> rollback force '7.18.3979';
rollback force '7.18.3979'
*
ERROR at line 1:
ORA-02058: no prepared transaction found with ID 7.18.3979
SQL> select local_tran_id,state,FAIL_TIME from dba_2pc_pending;
LOCAL_TRAN_ID STATE FAIL_TIME
---------------------- ---------------- -------------------
8.7.699 collecting 2009-06-02 17:40:11
5.45.865 forced rollback 2009-06-09 17:06:49
7.18.3979 collecting 2009-06-30 16:15:20
对于no prepared transaction我直接用DBMS_TRANSACTION package把transaction删除
SQL> execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('8.7.699');
PL/SQL procedure successfully completed.
SQL> execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('7.18.3979');
BEGIN DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('7.18.3979'); END;
*
ERROR at line 1:
ORA-01453: SET TRANSACTION must be first statement of transaction
ORA-06512: at "SYS.DBMS_TRANSACTION", line 65
ORA-06512: at "SYS.DBMS_TRANSACTION", line 85
ORA-06512: at line 1
SQL> select local_tran_id,state,FAIL_TIME from dba_2pc_pending;
LOCAL_TRAN_ID STATE FAIL_TIME
---------------------- ---------------- -------------------
5.45.865 forced rollback 2009-06-09 17:06:49
7.18.3979 collecting 2009-06-30 16:15:20
遇ORA-01453我再次要进行一次提交
SQL> commit;
Commit complete.
SQL> execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('7.18.3979');
PL/SQL procedure successfully completed.
SQL> select local_tran_id,state,FAIL_TIME from dba_2pc_pending;
LOCAL_TRAN_ID STATE FAIL_TIME
---------------------- ---------------- -------------------
5.45.865 forced rollback 2009-06-09 17:06:49
SQL> SELECT a.name,b.status
2 FROM v$rollname a,v$rollstat b
3 WHERE a.usn = b.usn
4 AND a.name IN (
5 SELECT segment_name
6 FROM dba_segments
7 WHERE tablespace_name = 'UNDOTBS1'
8 );
no rows selected
发现undotbs1的回滚段都已offline,删除原来的undotbs1
SQL> drop tablespace undotbs1 including contents and datafiles;
Tablespace dropped.
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
Database altered.
到这因数据库增加附加日志遭遇ora-600的问题已解决，原因在于undo中有挂起的事务。
微信扫一扫，打赏作者吧～
对不起，这篇文章暂时关闭评论。