How To Find Inactive Database (文档 ID 1543066.1)
Oracle Database - Enterprise Edition - Version 11.2.0.3 and later
Information in this document applies to any platform.
***Checked for relevance on 15-Dec-2016***
问题
How to find out Inactive databases in the environment ?
检查有没有连接数据库的session,可以使用以下方法:
1)可以检查监听日志文件,
您可以使用audit trail信息来查看趋势和用户活动,audit trail将文本格式化为以下字段:
Timestamp * Connect Data [* Protocol Info] * Event [* SID | Service] * Return Code
下面显示成功连接数据库的监听日志事件
14-MAY-2009 15:28:58 * (connect_data=(service_name=sales.us.example.com)(cid=(program=)(host=sales-server)(user=jdoe)))
* (address=(protocol=tcp)(host=192.168.2.35)(port=41349)) * establish * sales.us.example.com * 0
下面显示没有成功连接数据库的监听日志事件
03-MAY-2009 16:42:35 * <unknown connect data> *
(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.2.35)(PORT=53208)) * establish * <unknown sid> * 12525
TNS-12525: TNS:listener has not received client's request in time allowed
TNS-12604: TNS: Application timeout occurred
监听日志文件可以通过以下方式定位
如果在listener.ora文件中设置了 DIAG_ADR_ENABLED_<listener_name>=off
监听器日志将默认位于 $ORACLE_HOME/network/log 目录中
由 LOG_DIRECTORY_<listener_name> 指定的路径,由listener.ora文件中的 LOG_FILE_<listener_name> 参数指定的文件名。
如果在listener.ora文件中设置了 DIAG_ADR_ENABLED_<listener_name>=on(默认值):
监听日志将默认位于 $ORACLE_BASE\diag\tnslsnr\hostname\<listener_name>\trace\<listener_name>.log
如果在listener.ora文件中定义了ADR_BASE_LISTENER参数
则Listener日志将位于 <ADR_BASE_LISTENER>\diag\tnslsnr\hostname\<listener_name>\trace\<listener_name>.log
2)您可以使用以下查询来检查当前登录到数据库的内容:
SQL> select SERVICE_NAME,STAT_NAME,VALUE from V$SERVICE_STATS where STAT_NAME='logons cumulative' and SERVICE_NAME<>'SYS$BACKGROUND';
SERVICE_NAME STAT_NAME VALUE
----------------- ----------------------------- ----------
SYS$USERS logons cumulative 37
orclXDB logons cumulative 0
orcl logons cumulative 4
SERVICE_NAME = SYS$USERS,表示本地连接,而其他SERVICE_NAME表示远程连接。
SQL> select Count(*),USERNAME,PROGRAM from V$SESSION where TYPE<>'BACKGROUND' group by USERNAME,PROGRAM;
COUNT(*) USERNAME PROGRAM
---------- --------------- --------------------------
3 SYS plsqldev.exe
3)您可以使用以下查询来检查数据库的登录历史:
SQL> select SNAP_ID,SERVICE_NAME,STAT_NAME,VALUE from DBA_HIST_SERVICE_STAT where STAT_NAME='logons cumulative' and SERVICE_NAME<>'SYS$BACKGROUND';
文章翻译完成,下面是知识延伸,kill 远程数据库连接会话
使用[G]V$SESSION 和 [G]V$PROCESS 视图查找远程数据库连接会话,而非本地连接会话,如果kill本地连接会话,可能会导致数据库宕机
SQL> SELECT s.inst_id,
2 s.sid,
3 s.serial#,
4 p.spid,
5 s.username,
6 s.program,
7 s.paddr,
8 s.STATUS
9 FROM gv$session s
10 JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
11 WHERE s.TYPE<>'BACKGROUND';
INST_ID SID SERIAL# SPID USERNAME PROGRAM PADDR STATUS
---------- ---------- ---------- ------------ ---------- ---------------- ---------------- --------
1 145 59 3004 SYS plsqldev.exe 000007FFC92FD9C8 ACTIVE
1 146 15 2804 SYS plsqldev.exe 000007FFC9300290 INACTIVE
1 144 11 2864 SYS plsqldev.exe 000007FFC93012E0 INACTIVE
ALTER SYSTEM KILL SESSION
SQL> ALTER SYSTEM KILL SESSION 'SID,SERIAL#';
SQL> SELECT 'ALTER SYSTEM KILL SESSION '''|| SID ||',' ||SERIAL# ||''''||';' FROM GV$SESSION
WHERE TYPE<>'BACKGROUND' ORDER BY INST_ID;
如果是RAC,可以选择kill session的实例
SQL> ALTER SYSTEM KILL SESSION 'SID,SERIAL#,@INST_ID';
KILL SESSION命令实际上不会杀死会话。在某些情况下,如等待远程数据库的回复或回滚事务,会话将不会立即自动停止,并等待当前操作完成。在这些情况下,会话状态会被标记为killed。
我们发现当一个session被kill掉以后,该session的paddr被修改,如果有多个session被kill,那么多个session的paddr都被更改为相同的进程地址:
在这种情况下,很多时候,资源是无法释放的,我们需要查询spid,在操作系统级来kill这些进程.
但是由于此时v$session.paddr已经改变,我们无法通过v$session和v$process关联来获得spid
可以通过下面的查询获得spid:
SQL> SELECT s.username,s.status,
2 x.ADDR,x.KSLLAPSC,x.KSLLAPSN,x.KSLLASPO,x.KSLLID1R,x.KSLLRTYP,
3 decode(bitand (x.ksuprflg,2),0,null,1)
4 FROM x$ksupr x,v$session s
5 WHERE s.paddr(+)=x.addr
6 and bitand(ksspaflg,1)!=0;
参考文章:http://www.eygle.com/faq/Kill_Session.htm
SQL> ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;
操作系统级来kill这些进程之前,值得检查,看它是否执行回滚。您可以通过运行此脚本(session_undo.sql)来执行此操作。
如果有问题的会话的USED_UREC值正在减少,您应该使其完成回滚,而不是在操作系统级别杀死会话。
操作系统级来kill进程
WINDOWS
C:\> orakill ORACLE_SID spid
会话线程应该立即被杀死,并释放所有资源。
UNIX
% kill -9 spid
警告:如果在Oracle 12c中使用多线程模型,则不应尝试杀死操作系统进程
Multi-Threaded Oracle Model : 12c New (文档 ID 1958348.1)
https://oracle-base.com/articles/12c/multithreaded-model-using-threaded_execution_12cr1#killing-sessions