[20210722]数据库异常关闭的处理.txt

[20210722]数据库异常关闭的处理.txt --//昨天的测试,本来想输入show recyclebin,没想到手快使用命令补全输入shutdown immediate;然后马上按ctrl+c. --//我还输入一些命令还能正常使用.但是退出再次连接出现: $ rlsql SQL*Plus: Release 11.2.0.4.0 Production on Wed Jul 21 10:25:47 2021 Copyright (c) 1982, 2013, Oracle.  All rights reserved. Connected. SYS@book> show sga ORA-01012: not logged on Process ID: 0 Session ID: 0 Serial number: 0 --//已经无法使用,所以讲做运维一定要小心.如果关闭生产库麻烦就大了. --//检查alert发现: Wed Jul 21 10:21:24 2021 Shutting down instance (immediate) Shutting down instance: further logons disabled ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Stopping background process MMNL Wed Jul 21 10:21:24 2021 Stopping background process CJQ0 Stopping background process MMON --//仅仅停止了3个进程. --//以下我通过测试演示数据库异常关闭的处理. 1.环境: SYS@book> @ ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- -------------------------------------------------------------------------------- x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production 2.测试: SYS@book> shutdown immediate ORA-01013: user requested cancel of current operation --//执行后马上按ctrl+c. --//alert记录如下: Thu Jul 22 09:18:13 2021 Shutting down instance (immediate) Shutting down instance: further logons disabled Stopping background process MMNL Thu Jul 22 09:18:13 2021 Stopping background process CJQ0 Stopping background process MMON --//不退出会话,还可以执行命令. SYS@book> show sga Total System Global Area  643084288 bytes Fixed Size                  2255872 bytes Variable Size             205521920 bytes Database Buffers          427819008 bytes Redo Buffers                7487488 bytes --//退出会话,然后打开两个session,以sys用户登录,注其它用户已经无法登录了. --//在其中一个会话上执行shutdown abort,注shutdown immediate无法正常执行. --//session 1,sesson 2不要退出. --//session 1: SYS@book> shutdown abort ORACLE instance shut down. $ ipcs ------ Shared Memory Segments -------- key        shmid      owner      perms      bytes      nattch     status 0x00000000 33456130   oracle    640        12582912   1          dest 0x00000000 33488899   oracle    640        633339904  1          dest 0x00000000 33521668   oracle    640        2097152    1          dest ------ Semaphore Arrays -------- key        semid      owner      perms      nsems ------ Message Queues -------- key        msqid      owner      perms      used-bytes   messages --//你可以发现还有1个连接attach共享内存段. $ free -m              total       used       free     shared    buffers     cached Mem:        129161      39955      89206          0        628      35884 -/+ buffers/cache:       3441     125719 Swap:        30718          0      30718 --//session 1: SYS@book> startup ORACLE instance started. Total System Global Area  643084288 bytes Fixed Size                  2255872 bytes Variable Size             205521920 bytes Database Buffers          427819008 bytes Redo Buffers                7487488 bytes Database mounted. Database opened. $ free -m              total       used       free     shared    buffers     cached Mem:        129161      40466      88695          0        628      35885 -/+ buffers/cache:       3951     125209 Swap:        30718          0      30718 --//使用增加 40466-39955 = 511 $ ipcs ------ Shared Memory Segments -------- key        shmid      owner      perms      bytes      nattch     status 0x00000000 33456130   oracle    640        12582912   1          dest 0x00000000 33488899   oracle    640        633339904  1          dest 0x00000000 33521668   oracle    640        2097152    1          dest 0x00000000 33718277   oracle    640        12582912   23 0x00000000 33751046   oracle    640        633339904  23 0xe8a8ec10 33783815   oracle    640        2097152    23 ------ Semaphore Arrays -------- key        semid      owner      perms      nsems 0x6aa88594 23920640   oracle    640        204 ------ Message Queues -------- key        msqid      owner      perms      used-bytes   messages --//可以发现原来的共享内存段占用的内存没有释放.出现这种情况有一些数据库甚至无法启动的情况.感觉11g以后不会出现这种情况,具体看后面的测试. --//必须释放前面实例占用的共享内存段,当然我这里如果session 2退出,就可以释放. $ ipcs -t ------ Shared Memory Attach/Detach/Change Times -------- shmid      owner      attached             detached             changed 33456130   oracle      Jul 22 09:22:36      Jul 22 09:22:36      Jul 22 09:15:43 33488899   oracle      Jul 22 09:22:36      Jul 22 09:22:36      Jul 22 09:15:43 33521668   oracle      Jul 22 09:22:36      Jul 22 09:22:36      Jul 22 09:15:43 33718277   oracle      Jul 22 09:40:28      Jul 22 09:40:28      Jul 22 09:25:23 33751046   oracle      Jul 22 09:40:28      Jul 22 09:40:28      Jul 22 09:25:23 33783815   oracle      Jul 22 09:40:28      Jul 22 09:40:28      Jul 22 09:25:23 ------ Semaphore Operation/Change Times -------- semid    owner      last-op                    last-changed 23920640 oracle      Thu Jul 22 09:41:59 2021   Thu Jul 22 09:41:59 2021 ------ Message Queues Send/Recv/Change Times -------- msqid    owner      send                 recv                 change --//有点奇怪的是这两个使用的共享内存段的地址是一样的. $ pstree -p | grep sqlplus         |             |-bash(40500)---rlwrap(18044)---sqlplus(18045)---oracle(18047)         |             `-bash(61211)---rlwrap(18052)---sqlplus(18053)---oracle(18229) $ cat /proc/18047/maps  | grep SYSV 60000000-60c00000 rw-s 00000000 00:0b 33456130                           /SYSV00000000 (deleted) 60c00000-86800000 rw-s 00000000 00:0b 33488899                           /SYSV00000000 (deleted) 86800000-86a00000 rw-s 00000000 00:0b 33521668                           /SYSVe8a8ec10 (deleted) $ cat /proc/18229/maps  | grep SYSV 60000000-60c00000 rw-s 00000000 00:0b 33718277                           /SYSV00000000 (deleted) 60c00000-86800000 rw-s 00000000 00:0b 33751046                           /SYSV00000000 (deleted) 86800000-86a00000 rw-s 00000000 00:0b 33783815                           /SYSVe8a8ec10 (deleted) --//使用ipcrm删除. $ ipcs -a | grep "dest *$" | awk '{print $2}' |  xargs -IQ echo ipcrm -m Q ipcrm -m 33456130 ipcrm -m 33488899 ipcrm -m 33521668 $ ipcs -a | grep "dest *$" | awk '{print $2}' |  xargs -IQ  ipcrm -m Q $ ipcs ------ Shared Memory Segments -------- key        shmid      owner      perms      bytes      nattch     status 0x00000000 33456130   oracle    640        12582912   1          dest 0x00000000 33488899   oracle    640        633339904  1          dest 0x00000000 33521668   oracle    640        2097152    1          dest 0x00000000 33718277   oracle    640        12582912   25 0x00000000 33751046   oracle    640        633339904  25 0xe8a8ec10 33783815   oracle    640        2097152    25 ------ Semaphore Arrays -------- key        semid      owner      perms      nsems 0x6aa88594 23920640   oracle    640        204 ------ Message Queues -------- key        msqid      owner      perms      used-bytes   messages --//你可以发现竟然无法删除对应共享内存段.这也是我昨天遇到的情况. --//也就是你必须找到shmid= 33456130  33488899 33521668 的进程,kill掉才可以释放. # grep "SYSV" /proc/*/maps |  egrep  "33456130|33488899|33521668" /proc/18047/maps:60000000-60c00000 rw-s 00000000 00:0b 33456130                           /SYSV00000000 (deleted) /proc/18047/maps:60c00000-86800000 rw-s 00000000 00:0b 33488899                           /SYSV00000000 (deleted) /proc/18047/maps:86800000-86a00000 rw-s 00000000 00:0b 33521668                           /SYSVe8a8ec10 (deleted) */ $ ps -fp 18047 UID        PID  PPID  C STIME TTY          TIME CMD oracle   18047 18045  0 09:20 ?        00:00:00 oraclebook (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq))) $ kill -9 18047 $ ipcs -a ------ Shared Memory Segments -------- key        shmid      owner      perms      bytes      nattch     status 0x00000000 33718277   oracle    640        12582912   25 0x00000000 33751046   oracle    640        633339904  25 0xe8a8ec10 33783815   oracle    640        2097152    25 ------ Semaphore Arrays -------- key        semid      owner      perms      nsems 0x6aa88594 23920640   oracle    640        204 ------ Message Queues -------- key        msqid      owner      perms      used-bytes   messages --//OK. $ free -m              total       used       free     shared    buffers     cached Mem:        129161      40390      88770          0        628      36101 -/+ buffers/cache:       3660     125501 Swap:        30718          0      30718