问题描述
在配置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中有挂起的事务。
「喜欢这篇文章,您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。