bug

CTAS导致严重BUG一则

September 25, 2013 Internals, oracle No comments

某国内巨头的一起CASE,在系统繁忙情况下大量的CTAS 导致数据字典表的不一致,由原来10.0.2.4版本升级到10.0.2.5之后问题依旧。
具体表现为:
1. 尝试drop这些不一致的tablespace

15:00:18 SQL> @dropts_2012.0319.1500.sql
15:00:18 SQL> drop tablespace ODSCP1003 including contents;
drop tablespace ODSCP1003 including contents
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [ktssdrp1], [11], [0], [0], [], [],
[], []

Elapsed: 00:01:52.44
15:02:11 SQL> drop tablespace ODSCP1004 including contents;
drop tablespace ODSCP1004 including contents
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [ktssdrp1], [12], [0], [0], [], [],
[], []
Elapsed: 00:00:03.51

2.TAB$,seg$数据的不一致 hcheck检查结果

3.无法shutdown database

BUG: Various ORA-600 errors / dictionary inconsistency from CTAS / DROP

目前处理方案为: 针对这个库的大小接近30TB,建议整理出问题的TS,将这些TS的数据整理到新的TS中,将没有问题的TS TTS到新的DB中,再将这个DB升级到11g,由于oracle已经对10g的DB不再提供support(需要购买extended 服务),建议对一些残留问题的10gDB尽快提上升级议程。另外针对本库的ODS应用,不建议采用32K blocksize,11gR2下的32k blocksize依然存在很多问题:

Bug 7605618
Bug 11799496
Bug 12680822

11.2.0.2 EXPDP 遭遇 ORA-4031

July 16, 2012 11g, maintain, oracle No comments

一套oracle 11.2.0.2 数据仓库系统使用expdp导出数据时 进程hang 死 使用kill -9命令杀掉之后 database一直没有释放latch 通过下面的日志可以发现 pmon已经发现 ospid=15038的process dead 但是无法clean 进程在为 “fixed allocation callback” 分配内存时 遭遇了ORA-4031,在这套库中stream_pool_size 并没有显式指定,而从11g开始oracle data pump开始使用Advanced Queue高级队列来控制其job作业,这也就是这个case的root cause。

我们可以通过显式指定stream_pool_size解决这个问题,注意指定stream_pool_size之后必须restart instance.

我们可以通过3个note完整的理解这个问题

DataPump Export (EXPDP) Fails With Errors UDE-31623 ORA-4031 [ID 1318528.1]
UDE-31623 Error With DataPump Export [ID 1080775.1]
Bug 9896536: MEMORY LEAKE OCCURS IN STREAM SPOOL WHEN EXPDP IS EXECUTED.

*** 2012-07-16 13:34:14.231
found process 0x1bd12952b0 pid=403 serial=16 ospid = 15038 dead

*** 2012-07-16 13:34:19.445
kssxdl: error deleting SO: 0x1bd98ba410 = transaction (55)
  ----------------------------------------
  SO: 0x1bd98ba410, type: 55, owner: 0x1b015a1100, flag: INIT/-/-/0x00 if: 0x3 c: 0x3
   proc=0x1bd12952b0, name=transaction, file=ktccts.h LINE:407, pg=0
ORA-04031: unable to allocate 56 bytes of shared memory ("streams pool","unknown object","streams pool","fixed allocation callback")

*** 2012-07-16 13:34:29.456
found process 0x1bd12952b0 pid=403 serial=16 ospid = 15038 dead

*** 2012-07-16 13:34:34.713
kssxdl: error deleting SO: 0x1bd98ba410 = transaction (55)
  ----------------------------------------
  SO: 0x1bd98ba410, type: 55, owner: 0x1b015a1100, flag: INIT/-/-/0x00 if: 0x3 c: 0x3
   proc=0x1bd12952b0, name=transaction, file=ktccts.h LINE:407, pg=0
ORA-04031: unable to allocate 56 bytes of shared memory ("streams pool","unknown object","streams pool","fixed allocation callback")

*** 2012-07-16 13:34:44.732
found process 0x1bd12952b0 pid=403 serial=16 ospid = 15038 dead
ORA-04031: unable to allocate 56 bytes of shared memory ("streams pool","unknown object","streams pool","fixed allocation callback")
Mon Jul 16 13:49:03 2012
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Errors in file /data/oracle/diag/rdbms/std/edw1/trace/edw1_pmon_14903.trc:
ORA-04031: unable to allocate 56 bytes of shared memory ("streams pool","unknown object","streams pool","fixed allocation callback")
Mon Jul 16 13:49:18 2012
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Errors in file /data/oracle/diag/rdbms/std/edw1/trace/edw1_pmon_14903.trc:
ORA-04031: unable to allocate 56 bytes of shared memory ("streams pool","unknown object","streams pool","fixed allocation callback")
Mon Jul 16 13:49:34 2012
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Errors in file /data/oracle/diag/rdbms/std/edw1/trace/edw1_pmon_14903.trc:
ORA-04031: unable to allocate 56 bytes of shared memory ("streams pool","unknown object","streams pool","fixed allocation callback")

ORA-16009 in alert log with standby and LGWR ASYNC

December 2, 2011 oracle, replication No comments

今天用10.2.0.1的一套物理DG 转 逻辑 standby 出现大量错误:

primary 端:

— Connected User is Valid
RFS[2]: Assigned to RFS process 30286
RFS[2]: Database mount ID mismatch [0x9a9ac2b3:0x9a9a1a16]
RFS[2]: Destination database ID mismatch [0x2593781616:0x2592975883]
RFS[2]: Not using real application clusters
Fri Dec 2 19:27:39 2011
Errors in file /u01/app/oracle/admin/liu/udump/liu_rfs_30286.trc:
ORA-16009: remote archive log destination must be a STANDBY database
Fri Dec 2 19:30:16 2011
Thread 1 advanced to log sequence 24
Current log# 2 seq# 24 mem# 0: /u01/app/oracle/oradata/liu/redo02.log
Fri Dec 2 19:30:17 2011
LNS: Standby redo logfile selected for thread 1 sequence 24 for destination LOG_ARCHIVE_DEST_2

standby 端:

0x0000.000a997d, PID 29187, oracle@yang (P005)
Fri Dec 2 19:11:29 2011
LOGMINER: Log Auto Delete – deleting: /u01/arch/1_21_767998669.dbf
Deleted file /u01/arch/1_21_767998669.dbf
LNS1 started with pid=33, OS id=30088
Fri Dec 2 19:28:22 2011
Thread 1 advanced to log sequence 4
Current log# 3 seq# 4 mem# 0: /u01/app/oracle/oradata/liu/redo03.log
Fri Dec 2 19:28:23 2011
Errors in file /u01/app/oracle/admin/liu/bdump/liu_lns1_30088.trc:
ORA-16009: remote archive log destination must be a STANDBY database
Fri Dec 2 19:28:23 2011
LGWR: Error 16009 creating archivelog file ‘liu’
LNS: Failed to archive log 3 thread 1 sequence 4 (16009)

但是日志apply 正常 查找metalink

Bug 4676659 – ORA-16009 in alert log with standby and LGWR ASYNC [ID 4676659.8]
Modified 24-SEP-2008 Type PATCH Status PUBLISHED
Bug 4676659 ORA-16009 in alert log with standby and LGWR ASYNC

This note gives a brief overview of bug 4676659.
The content was last updated on: 03-APR-2008
Click here for details of each of the sections below.
Affects:

Product (Component) Oracle Server (Rdbms)
Range of versions believed to be affected Versions >= 10.2 but < 11 Versions confirmed as being affected 10.2.0.2 Platforms affected Generic (all / most platforms affected) Fixed: This issue is fixed in 10.2.0.3 (Server Patch Set) 11.1.0.6 (Base Release)

解决方法:升级数据库到10.2.0.5