用户反映修改密码后程序明显变慢,查看AWR发现:
ASH信息如下:
进一步验证:
SQL>select event,p1 from v$session t where t.username is not null and t.status='ACTIVE';
EVENT P1
---------------------------------------------------------------- ----------
row cache lock 7
row cache lock 7
row cache lock 7
row cache lock 7
查询v$rowcache
SQL> select parameter from v$rowcache where cache#=7;
PARAMETER
--------------------------------
dc_users
dc_users
dc_users
经查阅资料,在10.2.0.5及以后版本,使用错误密码登陆尝试会导致很高的Library Cache Locks或row cache lock,可以设置Oracle的隐含参数28401来避免:
alter system set event='28401 trace name context forever,level 1' scope=spfile;
官方参考如下:
通过审计功能分析是哪台机器哪个用户的频繁登陆失败
select *
from (select os_username, userhost, terminal, username, count(*) cnt
from dba_audit_trail
where returncode = 1017
and timestamp >= date '2015-12-13'
group by os_username, userhost, username, terminal
order by 5 desc)
where rownum < 10;
有时我们一台机器上部署了多个应用(服务),需要确认是哪一个进程在使用错误的密码访问Oracle,这里我们可以借助Oracle的数据库触发器记录日志
create table tmp_logon_denied
(
id varchar2(36),
ip varchar2(50),
osuser varchar2(100),
loginuser varchar2(100),
machine varchar2(100),
program varchar2(100),
timestamp date,
primary key (id)
);
/ CREATE OR REPLACE TRIGGER logon_denied_to_alert
AFTER servererror ON DATABASE
DECLARE
message VARCHAR2(168);
ip VARCHAR2(15);
v_os_user VARCHAR2(80);
v_module VARCHAR2(50);
v_action VARCHAR2(50);
v_pid VARCHAR2(10);
v_sid NUMBER;
v_program VARCHAR2(48);
BEGIN
IF (ora_is_servererror(1017)) THEN -- get ip FOR remote connections :
IF upper(sys_context('userenv', 'network_protocol')) = 'TCP' THEN
ip := sys_context('userenv', 'ip_address');
END IF; SELECT sid INTO v_sid FROM sys.v_$mystat WHERE rownum < 2;
SELECT p.spid, v.program
INTO v_pid, v_program
FROM v$process p, v$session v
WHERE p.addr = v.paddr
AND v.sid = v_sid; v_os_user := sys_context('userenv', 'os_user');
dbms_application_info.read_module(v_module, v_action); message := to_char(SYSDATE, 'YYYYMMDD HH24MISS') ||
' logon denied from ' || nvl(ip, 'localhost') || ' ' ||
v_pid || ' ' || v_os_user || ' with ' || v_program || ' – ' ||
v_module || ' ' || v_action; --sys.dbms_system.ksdwrt(2, message); insert into tmp_logon_denied(id, ip, osuser, loginuser, machine, program, timestamp)
select sys_guid(), ip, t.OSUSER, t.USERNAME, t.MACHINE, t.PROGRAM, sysdate
from v$session t
where t.SID = v_sid; END IF;
END;
/