又是信号量和共享内存没有释放的db待机

连接以前公司工作的一个生产库，出现了下列错误。

SQL> conn / as sysdba

Connected to an idle instance.

SQL> select * from dual;

select * from dual

*

ERROR at line 1:

ORA-01012: not logged on

SQL> conn / as sysdba

Connected to an idle instance.

以为是数据库内部hang住了，用hanganalyze工具查看下是否有直接在os可以kill的进程

SQL> oradebug setmypid

Statement processed.

SQL> oradebug hanganalyze 3

Hang Analysis in /usr/oracle/admin/jhql/udump/jhql_ora_24770.trc

SQL>

SQL> quit

Disconnected

警告日志并没有明显的错误

Sun Apr 15 12:12:44 2012

Job queue slave processes stopped

Waiting for shared server 'S000' to die

All dispatchers and shared servers shutdown

Sun Apr 15 12:14:45 2012

Starting ORACLE instance (normal)

[oracle@localhost bdump]$ less /usr/oracle/admin/jhql/udump/jhql_ora_24770.trc

==============

Open chains found:

Other chains found:

Chain 1 : :

<0/1649/1/0xbd1b5bc0/28452/No Wait>

Extra information that will be dumped at higher levels:

[level 5] : 1 node dumps -- [SINGLE_NODE] [SINGLE_NODE_NW] [IGN_DMP]

[level 10] : 7 node dumps -- [IGN]

State of nodes

([nodenum]/cnode/sid/sess_srno/session/ospid/state/start/finish/[adjlist]/predec

essor):

[1647]/0/1648/1/0xbdd41f98/28454/IGN/1/2//none

[1648]/0/1649/1/0xbdd43500/28452/SINGLE_NODE_NW/3/4//none

[1649]/0/1650/1/0xbdd44a68/28450/IGN/5/6//none

[1650]/0/1651/1/0xbdd45fd0/28448/IGN/7/8//none

[1651]/0/1652/1/0xbdd47538/28446/IGN/9/10//none

[1652]/0/1653/1/0xbdd48aa0/28444/IGN/11/12//none

[1653]/0/1654/1/0xbdd4a008/28442/IGN/13/14//none

[1654]/0/1655/1/0xbdd4b570/28440/IGN/15/16//none

====================

END OF HANG ANALYSIS

====================

查看hanganalyze的日志，并没有明显的等待事件，想起以前也碰见过db意外中断，是信号量和共享内存未正常分配释放导致

[oracle@localhost ~]$ ipcs -m

------ Shared Memory Segments --------

key shmid owner perms bytes nattch status

0x01b9ced8 2162689 oracle 640 1612709888 8

0x00000000 1802251 root 644 790528 2 dest

0x00000000 1835020 root 644 790528 2 dest

0x00000000 1867789 root 644 790528 2 dest

0x00000000 1900558 root 644 790528 2 dest

0x00000000 1933327 root 644 790528 2 dest

0x00000000 1966096 root 644 790528 2 dest

0x00000000 2064403 root 644 790528 2 dest

0x00000000 2097172 root 644 790528 2 dest

0x00000000 2129941 root 644 790528 2 dest

[oracle@localhost ~]$ ipcrm -m 2162689

[oracle@localhost ~]$ ipcs

------ Shared Memory Segments --------

key shmid owner perms bytes nattch status

0x00000000 2162689 oracle 640 1612709888 8 dest

0x00000000 1802251 root 644 790528 2 dest

0x00000000 1835020 root 644 790528 2 dest

0x00000000 1867789 root 644 790528 2 dest

0x00000000 1900558 root 644 790528 2 dest

0x00000000 1933327 root 644 790528 2 dest

0x00000000 1966096 root 644 790528 2 dest

0x00000000 2064403 root 644 790528 2 dest

0x00000000 2097172 root 644 790528 2 dest

0x00000000 2129941 root 644 790528 2 dest

------ Semaphore Arrays --------

key semid owner perms nsems

0x00000000 3080223 root 666 1

0x7697d424 3801120 oracle 640 1504

------ Message Queues --------

key msqid owner perms used-bytes messages

[oracle@localhost ~]$ ipcrm -s 3801120

[oracle@localhost ~]$ ipcs -a

------ Shared Memory Segments --------

key shmid owner perms bytes nattch status

0x00000000 1802251 root 644 790528 2 dest

0x00000000 1835020 root 644 790528 2 dest

0x00000000 1867789 root 644 790528 2 dest

0x00000000 1900558 root 644 790528 2 dest

0x00000000 1933327 root 644 790528 2 dest

0x00000000 1966096 root 644 790528 2 dest

0x00000000 2064403 root 644 790528 2 dest

0x00000000 2097172 root 644 790528 2 dest

0x00000000 2129941 root 644 790528 2 dest

------ Semaphore Arrays --------

key semid owner perms nsems

0x00000000 3080223 root 666 1

------ Message Queues --------

key msqid owner perms used-bytes messages

已经ipcrm手工释放了分配的内存和信号量

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Sun Apr 15 12:33:18 2012

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to an idle instance.

SQL> select * from dual;

select * from dual

*

ERROR at line 1:

ORA-01034: ORACLE not available

SQL> startup;

ORACLE instance started.

Total System Global Area 1610612736 bytes

Fixed Size 2084296 bytes

Variable Size 1056965176 bytes

Database Buffers 536870912 bytes

Redo Buffers 14692352 bytes

Database mounted.

Database opened.

启动后查看警告日志：

Sun Apr 15 12:32:31 2012

MMNL absent for 1201 secs; Foregrounds taking over

Sun Apr 15 12:32:32 2012

Errors in file /usr/oracle/admin/jhql/bdump/jhql_mman_28444.trc:

ORA-27157: Message 27157 not found; No message file for product=RDBMS, facility=ORA

ORA-27300: Message 27300 not found; No message file for product=RDBMS, facility=ORA; arguments: [semop] [43]

ORA-27301: Message 27301 not found; No message file for product=RDBMS, facility=ORA; arguments: [Identifier removed]

ORA-27302: Message 27302 not found; No message file for product=RDBMS, facility=ORA; arguments: [sskgpwwait1]

Sun Apr 15 12:32:32 2012

MMAN: terminating instance due to error 27157

Instance terminated by MMAN, pid = 28444

Sun Apr 15 12:32:33 2012

Errors in file /usr/oracle/admin/jhql/bdump/jhql_mman_28444.trc:

ORA-27300: Message 27300 not found; No message file for product=RDBMS, facility=ORA; arguments: [semctl] [22]

ORA-27301: Message 27301 not found; No message file for product=RDBMS, facility=ORA; arguments: [Invalid argument]

ORA-27302: Message 27302 not found; No message file for product=RDBMS, facility=ORA; arguments: [sskgpwrm1]

ORA-27157: Message 27157 not found; No message file for product=RDBMS, facility=ORA

ORA-27300: Message 27300 not found; No message file for product=RDBMS, facility=ORA; arguments: [semop] [43]

看来是后台进程mman mmnl关闭db时出现进程释放错误，直接用ipcs –m|-s|-q 查看os的共享内存，消息队列等是否分配释放完毕，再次告诉我们操作系统的诊断工具是多么重要。

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25362835/viewspace-1057907/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25362835/viewspace-1057907/