查看oracle未释放sql,记录一次数据库异常导致ipc未释放案例 – 提供7*24专业数据库(Oracle,SQL Server,MySQL等)恢复和Oracle技术服务@Tel:+86 1342...

很多时候,linux/unix中的ipc未正常释放,表现的现象是再次启动实例的时候表现出来内存不足等类似现象,但是今天在数据库启动过程中因为数据库后台报ora-600[6002]错误,导致数据库启动失败,但是后续的一些列奇怪现象让我吃惊:

1.启动数据库异常的session一直处于卡主状态,任何办法无法终止

2.开打新会话,登录数据库提示进行操作提示not logging,但是abort会出现和1中一样的hang住

数据库open过程卡主 会话无法退出,crtl+c依然无效

SQL> startup

ORACLE instance started.

Total System Global Area 175775744 bytes

Fixed Size 1343668 bytes

Variable Size 117444428 bytes

Database Buffers 50331648 bytes

Redo Buffers 6656000 bytes

Database mounted.

^C

^C^C^C^C^C

alert报错

Tue Aug 27 20:33:57 2013

SMON: enabling cache recovery

Archived Log entry 29 added for thread 1 sequence 29 ID 0xfb920b77 dest 1:

ARC3: Archival started

ARC0: STARTING ARCH PROCESSES COMPLETE

Errors in file /u01/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_4992.trc (incident=201905):

ORA-00600: internal error code, arguments: [6002], [6], [6], [1], [0], [], [], [], [], [], [], []

Incident details in: /u01/oracle/diag/rdbms/ora11g/ora11g/incident/incdir_201905/ora11g_ora_4992_i201905.trc

Use ADRCI or Support Workbench to package the incident.

See Note 411.1 at My Oracle Support for error and packaging details.

Undo initialization errored: err:600 serial:0 start:1524754 end:1528104 diff:3350 (33 seconds)

Errors in file /u01/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_4992.trc:

ORA-00600: internal error code, arguments: [6002], [6], [6], [1], [0], [], [], [], [], [], [], []

Errors in file /u01/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_4992.trc:

ORA-00600: internal error code, arguments: [6002], [6], [6], [1], [0], [], [], [], [], [], [], []

Error 600 happened during db open, shutting down database

USER (ospid: 4992): terminating the instance due to error 600

Instance terminated by USER, pid = 4992

ORA-1092 signalled during: alter database Open...

新开启会话登录

[oracle@xifenfei ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue Aug 27 20:27:38 2013

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected.

SQL> startup mount

ORA-01012: not logged on

SQL> conn / as sysdba

Connected to an idle instance.

SQL> startup mount

ORA-01012: not logged on

alert日志提示

Tue Aug 27 20:53:18 2013

ORA-1092 : opitsk aborting process

Tue Aug 27 20:53:44 2013

ORA-1092 : opitsk aborting process

Tue Aug 27 20:54:21 2013

shutdown abort 依然卡主

SQL> shutdown abort

^C

^C^C

查看ipc

根据alert日志提示,数据库已经异常关闭了，但是sqlplus中显示异常，怀疑是ipc未被正常释放

[oracle@xifenfei ~]$ ipcs -m

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

key shmid owner perms bytes nattch status

0x00000000 2228224 oracle 640 8388608 1

0x00000000 2260993 oracle 640 167772160 1

0xee5c29c4 2293762 oracle 640 2097152 1

[oracle@xifenfei ~]$ ps -ef|grep pmon

oracle 4843 4785 0 20:29 pts/3 00:00:00 grep pmon

释放ipc

[oracle@xifenfei ~]$ ipcrm -m 2260993

[oracle@xifenfei ~]$ ipcs -m

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

key shmid owner perms bytes nattch status

0x00000000 2228224 oracle 640 8388608 1

0x00000000 2260993 oracle 640 167772160 1 dest

0xee5c29c4 2293762 oracle 640 2097152 1

[oracle@xifenfei ~]$ ipcrm -m 2228224

[oracle@xifenfei ~]$ ipcs -m

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

key shmid owner perms bytes nattch status

0x00000000 2228224 oracle 640 8388608 1 dest

0x00000000 2260993 oracle 640 167772160 1 dest

0xee5c29c4 2293762 oracle 640 2097152 1

[oracle@xifenfei ~]$ ipcrm -m 2293762

[oracle@xifenfei ~]$ ipcs -m

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

key shmid owner perms bytes nattch status

0x00000000 2228224 oracle 640 8388608 1 dest

0x00000000 2260993 oracle 640 167772160 1 dest

0x00000000 2293762 oracle 640 2097152 1 dest

重新启动数据库

[oracle@xifenfei ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue Aug 27 20:33:35 2013

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 175775744 bytes

Fixed Size 1343668 bytes

Variable Size 117444428 bytes

Database Buffers 50331648 bytes

Redo Buffers 6656000 bytes

Database mounted.

再次查看ipc

[oracle@xifenfei ~]$ ipcs -m

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

key shmid owner perms bytes nattch status

0x00000000 2228224 oracle 640 8388608 1 dest

0x00000000 2260993 oracle 640 167772160 1 dest

0x00000000 2293762 oracle 640 2097152 1 dest

0x00000000 2490374 oracle 640 8388608 29

0x00000000 2523143 oracle 640 167772160 29

0xee5c29c4 2555912 oracle 640 2097152 29