大家好，我是考100分的小小码 ，祝大家学习进步，加薪顺利呀。今天说一说登录sqlplus用户名和口令_sqlplus nolog登录,希望您对编程的造诣更进一步.
![sqlplus登录用户hang住[数据库教程]](https://www.yht7.com/upload/image/images/imgsql/21.jpg "登录sqlplus用户名和口令_sqlplus nolog登录插图")
环境oracle 11.2.0.4, linux redhat 6.9 RAC2个实例
1.问题现象
sqlplus登录用户hang住
[email protected] ~]$ sqlplus wa/Sx SQL*Plus: Release 11.2.0.4.0 Production on Thu Jul 23 09:47:40 2020 Copyright (c) 1982, 2013, Oracle. All rights reserved. ERROR: ORA-01017: invalid username/password; logon denied
代码100分
2.排查异常情况
代码100分PROGRAM USERNAME OSUSER SQL_ID MACHINE STATUS S_TIME EVENT ------------------------- ----- ------- -------------- -------- -------------- --------- JDBC Thin Client admin osx-scp001xx ACTIVE library cache lock JDBC Thin Client admin osx-scp001xx ACTIVE library cache lock JDBC Thin Client admin osx-scp001xx ACTIVE library cache lock [email protected] (TNS V root localhost ACTIVE library cache lock [email protected] (TNS V root localhost ACTIVE library cache lock [email protected] (TNS V root localhost ACTIVE library cache lock [email protected] (TNS V root localhost ACTIVE library cache lock [email protected] (TNS V root localhost ACTIVE library cache lock [email protected] (TNS V root localhost ACTIVE library cache lock [email protected] (TNS V root localhost ACTIVE library cache lock
检查RAC 2个节点，ACTIVE的Session都是啥event,可以发现是library cache lock
1. 会话等待事件是library cache lock
2. ???USERNAME 都是null?
3. 为啥没有library cache pin ? or 其它的等待事件！！！
3.观察AWR报告
SQL> begin 2 dbms_workload_repository.create_snapshot(); 3 end; 4 / PL/SQL procedure successfully completed.
代码100分观察 connect xxx 99%!!!
–关闭错误输入密码时的密码延迟验证特性，避免用户持续输入错误密码时产生大量的library cache lock等待，严重时使数据库完全不能登录
alter system set event=‘28401 trace name context forever,level 1‘,‘10949 trace name context forever,level 1‘ scope=spfile;
In 11g there is an intentional delay between allowing failed logon
attempts to retry. For some specific application types this can cause
a problem as the row cache entry is locked for the duration of the
delay . This can lead to excessive row cache lock waits for DC_USERS
for specific users / schemas .
This "fix" allows the logon delay to be disabled in 11.2.0.1 onwards
by setting event 28401 in the init.ora.
eg:
event="28401 trace name context forever, level 1" # disable logon delay.
This "event" will disable the logon sleep delay system-wide,
ie. it will affect all user accounts, system-wide, and so should be used
with extreme caution.
观察上述信息，可以了解到问题的原因，需要确认！
1.关闭监听，组织新的会话连接；
$ srvctl stop listener
2.杀掉已连接的会话
select ‘alter system kill session ‘‘‘||sid||‘,‘||serial#||‘‘‘ immediate;‘ as “sql_text” from v$session where event=‘library cache lock‘;
3.测试确认是密码错误【已掌握密码】
会话均不存在后，测试登录是等待一段时间可以出现报错信息，而非Hang住
ERROR:
ORA-01017: invalid username/password; logon denied
4.修改密码即可！
SQL> alter user wxx identified by “Sxx1”;
5.启动监听
$ srvctl start listener
！如果想后续再次出现同样的问题，可以设置上述Event，本次由于是稳定的生产环境，极少出现，因此并未设置event.
sqlplus登录用户hang住
原文地址：https://www.cnblogs.com/lvcha001/p/13381682.html
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。
转载请注明出处: https://daima100.com/7063.html
