[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