问题描述
刚刚领导打电话让赶紧停下手里的任务，远程分析XX省的核心库，该库正处于hang死的状态，业务卡顿的无法操作。由于现场服务器连接复杂，现场同事五六分钟没连上，时间紧，就先通过数据库连接工具分析排查。
分析过程
服务器运行情况
由于连不上服务器，业务急需恢复，该步骤暂时跳过。
nmon -->io情况 cpu情况
-
cpu高 一般为逻辑读高，不排除有些异常的逻辑读sql
工具 描述
uptime 平均负载
vmstat 包括系统范围的cpu平均负载
mpstat 查看所有cpu核信息
top 监控每个进程cpu用量
sar -u 查看cpu信息
pidstat 每个进程cpu用量分解
perf cpu剖析和跟踪，性能计数分析 需安装
-
I/O高 一般为物理(磁盘)读高，可能有全表扫等
工具 描述
iostat 磁盘详细统计信息
iotop 按进程查看磁盘IO的使用情况 需安装
pidstat 按进程查看磁盘IO的使用情况
perf 动态跟踪工具
-
内存高 sga(buffer cache、share pool等) ，pga分配内存问题等
工具 描述
free 缓存容量统计信息
vmstat 虚拟内存统计信息
top 监视每个进程的内存使用情况
pidstat 显示活动进程的内存使用统计
pmap 查看进程的内存映像信息
sar -r 查看内存
dtrace 动态跟踪
valgrind 分析程序性能及程序中的内存泄露错误
查看进程和会话参数设置
经查询，进程数和会话数均未超过参数设置值，排除进程和会话数过多的原因。
锁表也有可能是连接数不够。
--查看当前的数据库连接数
select count(*) from v$process;
返回187
--查看数据库允许的最大连接数
select value from v$parameter where name ='processes';
返回3000
--查看当前的session连接数
select count(*) from v$session;
返回168
--查看当前并发连接数
select count(*) from v$session where status='ACTIVE';
返回120
--查看数据库允许的最大会话数
select value from v$parameter where name ='sessions';
返回4884
假设超过参数设置值，进行以下修改：
--修改数据最大连接数
alter system set processes = 500 scope = spfile;
--重启关闭数据库
shutdown immediate;
startup;
拿到锁表sql
从sql方面进行排查，如果是select 的锁可以释放，delete需和业务确认后后再决定释放与否。
查看是否有锁表
该查询花费了将近5分钟，输出9781条记录.
set lines 999 pages 999
col object_name for a30
col machine for a30
SELECT object_name, machine, s.sid, s.serial#
FROM gv$locked_object b, dba_objects o, gv$session s WHERE b.object_id=o.object_id
AND b.session_id = s.sid;
返回结果中有大量object_name为AUD$的记录。和现场确认说是现场有三级等保要求必须开启审计功能。
查看具体的锁表sql
由于查询时间长暂时放弃查询锁表语句对应 的sql
select l.session_id sid,
s.serial#,
l.locked_mode,
l.oracle_username,
s.user#,
l.os_user_name,
s.machine,
s.terminal,
a.sql_text,
a.action
from v$sqlarea a, v$session s, v$locked_object l
where l.session_id = s.sid
and s.prev_sql_addr = a.address
order by sid, s.serial#;
或
SELECT O.OBJECT_NAME,
S.SID,
S.SERIAL#,
s.LOGON_TIME,
'alter system kill session ' || '''' || S.SID || ',' || S.SERIAL# || ''';'
FROM V$LOCKED_OBJECT T, ALL_OBJECTS O, V$SESSION S
WHERE T.OBJECT_ID = O.OBJECT_ID
AND T.SESSION_ID = S.SID;
释放数据表锁
本想杀掉审计的会话，但是雨哥说开启审计功能的场景下，即使批量杀掉审计的会话还是不断的会有新的审计会话生成。因此放弃释放数据表锁，采用后面的查询审计表大小并清空审计表的解决办法。
// 释放SESSION SQL:
alter system kill session 'sid, serial#';
ALTER system kill session '23, 1647';
//如果上述语句杀不掉提示会话不存在，尝试该语句
alter system disconnect SESSION '399, 14608' immediate;
//
遇到杀不掉的进程，可以根据SID，查找系统中对应的spid，然后kill -9 spid杀掉
select p.spid, s.osuser, s.program
from v$session s,v$process p
where s.paddr=p.addr and s.sid=12345
操作系统上操作 （慎重）
ps -ef | grep 上面的spid
kill -9 上面的spid
查询审计表大小
--查总记录数
select count(*) from sys.aud$
count
-------
53005448
--查看物理大小
select round(sum(bytes) / 1024 / 1024 / 1024, 2) || 'G'
from dba_segments
where owner ='SYS' and SEGMENT_NAME='AUD$'
查看集群状态
现场同事终于连上了服务器，在查看审计表大小等待的过程中，由于时间长，等待过程中查了下集群状态，3个节点只剩了1个节点，慌的一批。
su - grid
crsctl stat res -t
可能原因：节点1负载高，节点2和节点3依然活着，但是节点间状态探测失败。
解决办法
终止查看物理大小会话
关闭数据库连接工具的查询会话
清空审计表
非常幸运的是现场同事终于连上了服务器
查看物理大小 步骤跳过，执行了将近10分钟未出结果，恢复业务第一，中断查询物理大小直接进行清空审计表操作。
--清空审计表
sqlplus / as sysdba
TRUNCATE TABLE SYS.AUD$;
另新打开一创建再次执行清空审计表操作
--清空审计表
sqlplus / as sysdba
TRUNCATE TABLE SYS.AUD$;
所幸清空审计表操作成功。联系现场测业务，业务恢复正常。
补充：
如果清空审计表依然报错，就再开个窗口，一个truncate,另一个kill aud$相关的session（别kill truncate语句的session）
查看集群状态
集群恢复正常。
su - grid
crsctl stat res -t
总结
和现场建议配置审计定期清理，但是沟通后领导不愿意，定时任务暂时搁浅，将步骤发给现场，定期手动执行清空审计表操作。
附录
创建定时任务，定期清理AUD$表
创建定时任务，定期清理AUD$表 sys用户procedure
--sys用户登录 创建存储过程
export ORACLE_SID=orcl
sqlplus / as sysdba
create or replace procedure sp_trunc_audit_log is
begin
execute immediate
'truncate table aud$';
end;
/
授权system用户执行该存储过程的权限
grant execute on sp_trunc_audit_log to system;
--system用户创建procedure
切换到system用户
conn system/oracle
create or replace procedure sp_job_trunc_audit_log is
begin
sys.sp_trunc_audit_log;
end;
/
--system用户 自动调度job
sqlplus / as sysdba
切换到system用户
conn system/oracle
BEGIN
DBMS_SCHEDULER.CREATE_JOB
(
job_name => 'day_trunc_audit_log',
job_type => 'STORED_PROCEDURE',
job_action => 'SP_JOB_TRUNC_AUDIT_LOG',
start_date => sysdate,
repeat_interval => 'FREQ=DAILY; BYHOUR=04; BYMINUTE=05;INTERVAL=1',
enabled => true,
comments => 'every day truncate table audit log'
);
END;
/
说明：
FREQ=DAILY指定按日重复，byhour指定几点，byminute指定几分，bysecond指定几秒， INTERVAL=1 间隔1天
