【此为"一森咖记"公众号——第26篇文章】
【前言】
有时候,在oracle库上执行shutdown immediate命令后,很长一段时间后才能关闭数据库,尤其是在会话较多的时候。
原因为何,今天本文将探讨下。
首先,先介绍下oracle命令关闭instance的几种方法:
ORACLE shutdown 过程:
1. shutdown normal:阻止任何用户建立新的连接;等待当前所有正在连接的用户主动断开连接;当所有的用户都断开连接后,将立即关闭数据库;
2. shutdown transactional:阻止用户连接新的连接和开始新事务;等待所有活动事务提交后,再断开用户连接;当所有的活动实务提交完毕、所有的用户都断开连接后,将关闭数据库;
3. shutdown immediate:阻止用户连接新连接和开始新事务;将未提交的活动事务回退;关闭数据库;
4. shutdown abort:阻止用户建立新连接和开始新事务;取消未提交的活动事务且不执行回退;立即终止正在执行的任何SQL语句,立即关闭数据库。
小结:
shutdown abort是最开速关闭数据库的方式,但很可能会使数据库处于不一致状态,严重时可损坏数据库,导致数据库起不来,特别是生产环境,有一种赌的成分在。一般情况下,我们推荐使用shutdown immediate方式库。
但有时候,执行shutdown immediate命令时,还是会等待很长时间才能关库,这是啥个原因?
shutdown immediate时执行以下操作:
1. 阻止任何用户建立新连接,同时阻止当前连接的用户开始任何新事务;
2. 回退任何未提交的事物;
3. ORACLE不再等待用户主动断开连接,而是直接关闭进程、unmount库,终止instance。
由此可以推断出,影响快速关闭数据库几个因素:
1. 连接的客户端进程数量;
2. 未提交的事务占用的UNDO BLOCK数量;
3. 长时间运行的会话;
4. SMON进程清理临时段的速度。
分析上述原因,上述2和4是oracle的内部机制,我们不能干预;1和3还是可以搞一搞的,可以做下两项动作来加快关库速度。
1. 清理客户端会话;
2. 清理长时间运行的会话。
因为清理客户端会话包含了可以清理长时间运行的会话,所以下面的内容将重点讲述如何清理客户端会话。
考虑到有时候,大家会将会话(Session) 和连接(Connection)混为一谈,这里再多解释下:
会话(Session):是通信双方从开始通信到通信结束期间的一个上下文(Context)。这个上下文是一段位于服务器端的内存:记录了本次连接的客户端机器、通过哪个应用程序、哪个用户登录等信息.
连接(Connection):连接是从客户端到ORACLE实例的一条物理路径。连接可以在网络上建立,或者在本机通过IPC机制建立。通常会在客户端进程与一个专用服务器或一个调度器之间建立连接。
注意:
会话(Session) 是和连接(Connection)是同时建立的,两者是对同一件事情不同层次的描述。简单讲,连接(Connection)是物理上的客户端同服务器的通信链路,会话(Session)是逻辑上的用户同服务器的通信交互。
看英文表述更明确,连接(connection)、会话、进程定义:
A connection is a physical circuit between you and the database. A connection might be one of many types -- most popular begin DEDICATED server and SHARED server. Zero, one or more sessions may be established over a given connection to the database as show above with sqlplus. A process will be used by a session to execute statements. Sometimes there is a one to one relationship between CONNECTION->SESSION->PROCESS (eg: a normal dedicated server connection). Sometimes there is a one to many from connection to sessions (eg: like autotrace, one connection, two sessions, one process).
A process does not have to be dedicated to a specific connection or session however, for example when using shared server (MTS), your SESSION will grab a process from a pool of processes in order to execute a statement. When the call is over, that process is released back to the pool of processes.
接下来,文章重点开始:
如何清理客户端会话?
ORACLE清理会话进程有三种方式
方法1:OS层KILL -9 SPID (Linux)
通过下面命令批量Kill掉系统中(LOCAL=NO)的ORACLE进程
[oracle@ethanDB ~]$ ps -ef|grep ora|grep -v grep|grep -v ora_|grep LOCAL=NO|awk '{print $2}'|xargs kill -9
或
[oracle@ethanDB ~]$ ps aux |grep "LOCAL=NO" |awk '{printf "%s\n", $2}' |xargs kill -9
然后,执行关闭数据库流程:
1. 关监听
2. 执行上述命令断开外部会话session
3. shutdown immediate
方法2: ALTER SYSTEM KILL SESSION
如下官方文档描述所示,alter system kill session实际上不是真正的杀死会话,它只是将会话标记为终止,等待PMON进程来清除会话。
The KILL SESSION clause lets you mark a session as terminated, roll back ongoing transactions, release all session locks, and partially recover session resources. To use this clause, your instance must have the database open. Your session and the session to be terminated must be on the same instance unless you specify integer3.You must identify the session with the following values from the V$SESSION view:
For integer1, specify the value of the SID column.
For integer2, specify the value of the SERIAL# column.
For the optional integer3, specify the ID of the instance where the target session to be killed exists. You can find the instance ID by querying the GV$ tables.
If the session is performing some activity that must be completed, such as waiting for a reply from a remote database or rolling back a transaction, then Oracle Database waits for this activity to complete, marks the session as terminated, and then returns control to you. If the waiting lasts a minute, then Oracle Database marks the session to be terminated and returns control to you with a message that the session is marked to be terminated. The PMON background process then marks the session as terminated when the activity is complete.
Whether or not the session has an ongoing transaction, Oracle Database does not recover the entire session state until the session user issues a request to the session and receives a message that the session has been terminated.
可使用如下命令来快速回滚事物、释放会话的相关锁、立即返回当前会话的控制权。
Specify IMMEDIATE to instruct Oracle Database to roll back ongoing transactions, release all session locks, recover the entire session state, and return control to you immediately.
sql> ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;
方法3: ALTER SYSTEM DISCONNECT SESSION
ALTER SYSTEM DISCONNECT SESSION 杀掉专用服务器(DEDICATED SERVER)或共享服务器的连接会话,它等价于从操作系统杀掉进程。它有两个选项POST_TRANSACTION和IMMEDIATE, 其中POST_TRANSACTION表示等待事务完成后断开会话,IMMEDIATE表示中断会话,立即回滚事务。
SQL> ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' POST_TRANSACTION;
SQL> ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' IMMEDIATE;
针对方法2和3,具体删除会话如何操作?
命令如下:
这里模拟了一个用户TEST_LY以监听方式访问oracle
[oracle@ethanDB ~]$ sqlplus test_ly/ethan_yang@ethanDB
###---查看外部会话的语句如下:
SYS@ethanDB> SELECT s.inst_id,
2 s.sid,
3 s.serial#,
4 p.spid,
5 s.username,
6 s.program
7 FROM gv$session s
8 JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
9 WHERE s.type != 'BACKGROUND' and s.username not like '%SYS%';
INST_ID SID SERIAL# SPID USERNAME PROGRAM
---------- ---------- ---------- ---------- ---------- ---------------------------------------------
1 817 28719 20371 TEST_LY sqlplus@ethanDB (TNS V1-V3)
#oracle中批处理删除外部连接session
set linesize 999
col spid format A10
col username format A10
col program format A50
declare cursor del_cur is
SELECT s.inst_id,
s.sid,
s.serial#,
p.spid,
s.username,
s.program
FROM gv$session s
JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE s.type != 'BACKGROUND' and s.username not like '%SYS%';
begin
for cur in del_cur
loop
execute immediate ( 'alter system kill/DISCONNECT session '''||cur.sid || ','|| cur.SERIAL# ||''' ');
end loop;
end;
/
上述源代码可详见本人github,链接如下:
https://github.com/polestarYang/oracle-git/blob/master/del_external_sessions.sh
或
##根据计算机名批量删除会话,具体删除条件可以自行调整上面的查询语句
declare cursor del_cur is
select b.sid,b.serial# from v$session b where b.MACHINE = 'MACHINE_NAME' and b.STATUS = 'INACTIVE';
begin
for cur in del_cur
loop
execute immediate ( 'alter system kill session/DISCONNECT '''||cur.sid || ','|| cur.SERIAL# ||''' ');
end loop;
end;
/
【结语】
1. 本文讲述了oracle库上执行shutdown immediate命令时执行时间过长原因以及如何解决;
2. 实际生产中推荐两种方法批量删除会话:
方法1:ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' IMMEDIATE; ;
方法2:ps aux |grep "LOCAL=NO" |awk '{printf "%s\n", $2}' |xargs kill -9
【参考】
Troubleshooting Shutdown Immediate/Normal Hang Issues (文档 ID 1906014.1)
【参考】
https://www.cnblogs.com/kerrycode/p/3636992.html
【参考】
https://www.cnblogs.com/kerrycode/p/4100865.html
【参考】
https://www.eygle.com/faq/Kill_Session.htm
【参考】
https://www.cnblogs.com/kerrycode/p/3636992.html
【参考】
https://blog.csdn.net/u010632377/article/details/36009739
【参考】
https://blog.csdn.net/zhoushengtao12/article/details/87926000
如果大家觉得此文有帮助,欢迎关注个人微信公众号;
长按识别二维码或公众号搜索“一森咖记”