今天在维护一套库时,用户反映说外面用户无法登录,用sys或system用户可以登录

第一反映查看数据库日志,日志正常

第二查看数据库等待事件分析

SQL> select event,count(*) from v$session_wait group by event;

EVENT                                                              COUNT(*)
---------------------------------------------------------------- ----------
Streams AQ: waiting for messages in the queue                             1
SQL*Net message to client                                                 1
gcs remote message                                                        6
SQL*Net message from client                                             127
smon timer                                                                1
pmon timer                                                                1
DIAG idle wait                                                            1
wait for possible quiesce finish                                          1
rdbms ipc message                                                        16
ges remote message                                                        1
Streams AQ: qmn slave idle wait                                           1

EVENT                                                              COUNT(*)
---------------------------------------------------------------- ----------
ASM background timer                                                      1
resmgr:become active                                                    197
Streams AQ: qmn coordinator idle wait                                     1
Streams AQ: waiting for time management or cleanup tasks                  1
class slave wait     

resmgr:become active   等待事件非常严重,查看metalink id340463.1

建议检查resouce_manger_plan设置和v$rsrc_plan

resouce_manger_plan和v$rsrc_plan的值为INTERNAL_QUIESCE,

起用了INTERNAL_QUIESCE后只允许SYS_GROUP中的user登陆 SQL> show parameter plan

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
resource_manager_plan                string      INTERNAL_QUIESCE

那解决方法就比较简单了

取消resouce_manger_plan的值即可

alter system SET resource_manager_plan = '';

resmgr:become active等待事件消失,数据库其他会话登录正常。

总结下解决问题的思路:

1.查数据库日志

2.查数据库等待事件，看是否有异常

3.看性能收集报告比如statspack,awr,addm等