Category Archives: ORACLE

所有Oracle技术文章

非空字段空值的产生

上一篇讨论了非空字段中如果存在空值对于查询的影响,这里描述一下导致问题的原因。 非空字段空值对查询的影响:http://yangtingkun.net/?p=1481 书接上文,其实CBO的判断本身是没有问题的,问题在于,为什么一个空值会存在非空约束的字段中。 重新看一下问题: SQL> select * from t_def; ID NAME TYPE ———- —————————— ——– 1 a SQL> select * from t_def where type is null; no rows selected SQL> select * from t_def where type is not null; ID … Continue reading

Posted in BUG, ORACLE | Tagged , , | Leave a comment

非空字段空值对查询的影响

BLOG有三年没有更新了,其实这篇文档也是三年前写的,不过当时没有贴出来,今天看到有人问起,于是分享出来。 这是客户数据库中碰到的一个有意思的问题。 数据库版本为11.2.0.3,客户的DSG同步软件在同步某张表时出现了错误,报错无非插入空值ORA-1400,本来这是一个很简单的问题,但是诊断发现问题比较有意思: SQL> select * from t_def; ID NAME TYPE ———- —————————— ——– 1 a SQL> select * from t_def where type is null; no rows selected SQL> select * from t_def where type is not null; ID NAME … Continue reading

Posted in BUG, ORACLE | Tagged , , , | 2 Comments

密码延迟验证导致的系统HANG住

又是一个11g新特性导致的问题。 这个新特性很早之前就研究过,也在其他客户处碰到过类似的问题。从11g开始,如果一个用户使用不正确的密码尝试登录数据库,那么随着登录失败次数的增加,每次登录验证前延迟等待的时间也会增加: SQL> SET TIME ON 18:30:54 SQL> 18:30:58 SQL> conn test/test Connected. 18:31:25 SQL> 18:31:25 SQL> conn test/a conn test/a conn test/a conn test/a conn test/a conn test/a conn test/a conn test/test conn test/a ERROR: ORA-01017: invalid username/password; logon … Continue reading

Posted in ORACLE | Tagged , , , , | Leave a comment

IP地址被清空导致实例重启

客户10.2.0.4 RAC for Solaris 10环境突然出现了实例重启的现象。 数据库正常运行到下午3点左右,随后两个节点分别重启,其中一个节点上的实例无法自动启动。检查两个实例的告警日志发现,在节点重启前,两个节点都出现了明显的ORA-27504错误: Wed Apr 10 15:00:05 2013 Errors IN file /oracle/admin/orcl/udump/orcl1_ora_10997.trc: ORA-00603: ORACLE server SESSION TERMINATED BY fatal error ORA-27504: IPC error creating OSD context ORA-27300: OS system dependent operation:if_not_found failed WITH STATUS: 0 ORA-27301: OS … Continue reading

Posted in ORACLE | Tagged , , , , , , , , , , , | Leave a comment

ORA-7445(kjbcrcomplete)错误

客户10.2.0.5 RAC在验证表的逻辑结构是出现ORA-7445错误。 错误信息如下: Sun Mar 31 03:45:16 EAT 2013 Errors IN file /oracle/app/admin/orcl/udump/orcl2_ora_4039.trc: ORA-07445: 出现异常错误: 核心转储 [kjbcrcomplete()+5521] [SIGSEGV] [Invalid permissions FOR mapped object] [0x00000002A] [] [] 详细TRACE信息为: Ioctl ASYNC_CONFIG error, errno = 1 *** 2013-03-31 02:20:45.846 *** ACTION NAME:() 2013-03-31 … Continue reading

Posted in BUG | Tagged , , , | Leave a comment

Streams AQ: qmn coordinator waiting for slave to start等待

一个客户的10.2.0.5数据库的TOP 5等待出现了这个等待事件。 其实导致这个Streams AQ: qmn coordinator waiting for slave to start等待出现的原因除了参数设置不合理外,最主要的原因还是数据库太闲了: Event Waits Time(s) Avg Wait(ms) % Total Call   Time Wait Class CPU time 372 59.0 Streams AQ: qmn   coordinator waiting for slave to start 6 34 5,667 … Continue reading

Posted in ORACLE | Tagged , , | Leave a comment

ORA-600(13310)错误

客户10.2.0.4 RAC环境出现ORA-600[13310]错误。 错误信息如下: Sun Aug 1 04:08:24 2010 Errors IN file /oracle/admin/ORCL/udump/orcl1_ora_14964.trc: ORA-00603: ORACLE server SESSION TERMINATED BY fatal error ORA-27544: Failed TO map memory region FOR export ORA-27300: OS system dependent operation:socket failed WITH STATUS: 23 ORA-27301: OS failure … Continue reading

Posted in BUG | Tagged , , , , , , , , , , , , , | Leave a comment

ORA-600(17147)和ORA-7445(__lwp_kill)错误

客户10.2.0.4 RAC环境出现ORA-600[17147]和ORA-7445[__lwp_kill]错误。 错误信息为: Fri DEC 14 16:05:56 2012 Errors IN file /oraclelog/admin/orcl/bdump/orcl2_diag_27263.trc: ORA-07445: exception encountered: core dump [__lwp_kill()+48] [SIGIOT] [UNKNOWN code] [0x000006A7F] [] [] ORA-00600: internal error code, arguments: [17147], [0x9FFFFFFFFD3E6BB8], [], [], [], [], [], [] Fri DEC 14 … Continue reading

Posted in BUG | Tagged , , , , , , | Leave a comment

ORA-600(17059)错误

客户10.2.0.4 RAC出现大量的ORA-600[17059]错误。 错误信息如下: Tue May 21 09:55:30 2013 Errors IN file /oraclelog/admin/orcl/bdump/orcl1_j000_307.trc: ORA-00600: 内部错误代码, 参数: [17059], [0xC000001346657EB8], [], [], [], [], [], [] Tue May 21 09:55:32 2013 Errors IN file /oraclelog/admin/orcl/bdump/orcl1_j000_307.trc: ORA-00600: 内部错误代码, 参数: [17059], [0xC000001346657EB8], [], [], [], … Continue reading

Posted in BUG | Tagged , , , , , | Leave a comment

系统存在严重的latch: undo global data等待

客户10.2.0.5 RAC环境出现了严重的latch: undo global data等待。 问题时刻AWR的TOP如下: Event Waits Time(s) Avg Wait(ms) % Total Call   Time Wait Class latch: undo   global data 6,245,400 1,372,583 220 22.0 Other gc buffer busy 114,190,782 1,329,749 12 21.3 Cluster enq: TX – row … Continue reading

Posted in BUG | Tagged , , , | Leave a comment