]$ sqlplus / as sysdbaSQL*Plus: Release 10.2.0.1.0 - Production on Sun Jul 24 20:01:10 2010Copyright (c) 1982, 2005, Oracle. All rights reserved.ERROR:ORA-01075: you are currently logged on2)即便登陆成功在执行SQL命令的时候一样会收到报错sys@secdb> select * from dual;select * from dual*ERROR at line 1:ORA-00604: error occurred at recursive SQL level 1ORA-00018: maximum number of sessions exceeded2.问题分析单纯从ORA-01075报错信息本身无法定位问题。从alert日志中发现“ORA-00018: maximum number of sessions exceeded”报错信息。细查之后发现此时后台出现大量的归档进程,而且数据库中的processes=50,是由于超出了session限制导致的这个报错。数据库后台的进程信息如下:[oracle@secdb1 ~]$ ps -ef | grep secdb | grep -v greporacle 10023 1 0 19:13 ? 00:00:00 ora_pmon_secdboracle 10025 1 0 19:13 ? 00:00:00 ora_psp0_secdboracle 10027 1 0 19:13 ? 00:00:00 ora_mman_secdboracle 10029 1 0 19:13 ? 00:00:00 ora_dbw0_secdboracle 10031 1 0 19:13 ? 00:00:00 ora_lgwr_secdboracle 10033 1 0 19:13 ? 00:00:00 ora_ckpt_secdboracle 10035 1 0 19:13 ? 00:00:00 ora_smon_secdboracle 10037 1 0 19:13 ? 00:00:00 ora_reco_secdboracle 10039 1 0 19:13 ? 00:00:00 ora_cjq0_secdboracle 10041 1 0 19:13 ? 00:00:00 ora_mmon_secdboracle 10043 1 0 19:13 ? 00:00:00 ora_mmnl_secdboracle 10063 1 0 19:13 ? 00:00:00 ora_p000_secdboracle 10065 1 0 19:13 ? 00:00:00 ora_p001_secdboracle 10067 1 0 19:13 ? 00:00:00 ora_p002_secdboracle 10069 1 0 19:13 ? 00:00:00 ora_p003_secdboracle 10071 1 0 19:13 ? 00:00:00 ora_p004_secdboracle 10077 1 0 19:13 ? 00:00:00 ora_arc0_secdboracle 10079 1 0 19:13 ? 00:00:00 ora_arc1_secdboracle 10081 1 0 19:13 ? 00:00:00 ora_arc2_secdboracle 10083 1 0 19:13 ? 00:00:00 ora_arc3_secdboracle 10085 1 0 19:13 ? 00:00:00 ora_arc4_secdboracle 10087 1 0 19:13 ? 00:00:00 ora_arc5_secdboracle 10089 1 0 19:13 ? 00:00:00 ora_arc6_secdboracle 10091 1 0 19:13 ? 00:00:00 ora_arc7_secdboracle 10093 1 0 19:13 ? 00:00:00 ora_arc8_secdboracle 10095 1 0 19:13 ? 00:00:00 ora_arc9_secdboracle 10100 1 0 19:13 ? 00:00:00 ora_arca_secdboracle 10103 1 0 19:13 ? 00:00:00 ora_arcb_secdboracle 10105 1 0 19:13 ? 00:00:00 ora_arcc_secdboracle 10107 1 0 19:13 ? 00:00:00 ora_arcd_secdboracle 10109 1 0 19:13 ? 00:00:00 ora_arce_secdboracle 10111 1 0 19:13 ? 00:00:00 ora_arcf_secdboracle 10113 1 0 19:13 ? 00:00:00 ora_arcg_secdboracle 10115 1 0 19:13 ? 00:00:00 ora_arch_secdboracle 10117 1 0 19:13 ? 00:00:00 ora_arci_secdboracle 10119 1 0 19:13 ? 00:00:00 ora_arcj_secdboracle 10121 1 0 19:13 ? 00:00:00 ora_arck_secdboracle 10123 1 0 19:13 ? 00:00:00 ora_arcl_secdboracle 10125 1 0 19:13 ? 00:00:00 ora_arcm_secdboracle 10127 1 0 19:13 ? 00:00:00 ora_arcn_secdboracle 10132 1 0 19:13 ? 00:00:00 ora_arco_secdboracle 10135 1 0 19:13 ? 00:00:00 ora_arcp_secdboracle 10137 1 0 19:13 ? 00:00:00 ora_arcq_secdboracle 10139 1 0 19:13 ? 00:00:00 ora_arcr_secdboracle 10141 1 0 19:13 ? 00:00:00 ora_arcs_secdboracle 10143 1 0 19:13 ? 00:00:00 ora_arct_secdboracle 10173 1 29 19:13 ? 00:00:26 ora_qmnc_secdboracle 10229 1 0 19:14 ? 00:00:00 ora_q000_secdboracle 10296 10294 0 19:14 ? 00:00:00 oraclesecdb (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))可见后台启动了非常多的归档进程,正是这些进程占满了所有50个session,导致系统无法登陆。3.问题处理1)停止数据库既然已经无法正常登陆到数据库,只能强制使用操作系统命令将其终止。$ ps -ef |grep $ORACLE_SID|grep -v grep|awk '{print $2}' | xargs kill -9$ ipcs -m | grep oracle | awk '{print $2}' | xargs ipcrm shm严重警告:以上命令严禁在任何数据库服务器上进行尝试!关于上面两条命令的阐述请参考《【Kill】两条Linux命令彻底杀死Oracle》(http://space.itpub.net/519536/viewspace-619787)2)修改系统参数processes为5003)重新启动数据库问题处理完毕。4.小结此处遭遇的这个问题比较巧合,后台正好超过了50个session,在我调整完processes为500之后,后台session数还是稳定在50左右。这个案例告诉我们:在项目实施过程之前一定要对每一个参数细细斟酌和考量,不要人为的给自己增加困难。Good luck.secooler10.07.24-- The End --
oracle ora 10031,【问题处理】偶遇ORA- 01075: you are currently logged on错误
最新推荐文章于 2024-07-07 23:27:24 发布