ora-600 [kcbgcur_9] internal error when ALTER DATABASE ADD SUPPLEMENTAL LOG DATA

张维照 2019-05-31
11
0 0
摘要:因数据库增加附加日志遭遇ora-600的问题已解决,原因在于undo中有挂起的事务。

问题描述

在配置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中有挂起的事务。


「喜欢文章,快来给作者赞赏墨值吧」

评论

0
0
Oracle
订阅
欢迎订阅Oracle频道,订阅之后可以获取最新资讯和更新通知。
墨值排行
今日本周综合
热门文章
近期活动
全部
相关课程
全部