ocp数据库进程数超1000的问题解决.docx

2014-05-1423:16:51【吐槽】xsnail2014-05-1423:16:51不是NULL2014-05-159:28:02【话唠】田新营2014-05-159:28:02是a.【话唠】田新营2014-05-159:28:39prod_unit_of_measureisnull才是我们期望的【话唠】田新营2014-05-159:29:50这个语句不该用like,会全表扫描或全索引快速扫描,性能低下2014-05-1510:02:27【吐槽】小番茄2014-05-1510:02:27明白了，谢谢田老师！2014-05-1622:38:07【活跃】穆翥考完，Mark一个2014-05-1712:34:52【吐槽】xsnail2014-05-1712:34:52分享一个考51比较好的资料/hobbypei/article/details/174212792014-05-1812:18:46【吐槽】xsnail2014-05-1812:18:46我问下，OCP的051，052，053，多少分通过啊2014-05-1817:34:00【冒泡】OCP新手上路2014-05-1817:34:0068分左右2014-05-1818:14:57【吐槽】xsnail2014-05-1818:14:57谢谢2014-05-1913:22:43【吐槽】海波2014-05-1913:22:43准确分数是：05160分，052、05366分【吐槽】xsnail2014-05-1913:23:31好没难度啊。2014-05-1917:39:07【吐槽】xsnail2014-05-1917:39:07各位同学，大家谁有题库的解析啊9:23:12【吐槽】COCO2014-05-229:23:12田老师，请教一个问题，我有一个DG备库，只是同步主库用了，没有应用在上面跑，可是今天居然发现连接数超标ERROR:ORA-00020:maximumnumberofprocesses(1000)exceeded现在我想想个办法登陆数据库，然后查询一下到底有哪些连接在上面？【吐槽】COCO2014-05-229:23:41可是现在不知用什么办法能连的上去？不知哪位同学遇到过这类问题没有？9:54:18【话唠】田新营2014-05-229:54:18sysdba(用户sys)不受限制,dba(system)不去顶9:55:54【吐槽】COCO2014-05-229:55:54oraclectosb01:/home/oraclesqlplus/assysdbaSQL*Plus:Release.0ProductiononThuMay2209:54:192014Copyright(c)1982,2011,Oracle.Allrightsreserved.ERROR:ORA-00020:maximumnumberofprocesses(1000)exceeded【吐槽】COCO2014-05-229:56:41我是用SYS登陆的，也登陆不进去啊。我就是想登陆进去看看哪些连接用户连接上来了，因为按道理应该没有什么用户连进来才对的。9:59:21【活跃】穆翥kill-9先？【吐槽】COCO2014-05-2210:00:24我用这个命令看了，出现了很多GRID用户进程，超级多。不知为何？ps-ef|greporacle【吐槽】COCO2014-05-2210:00:36现在是不知道该KILL那个进程啊？【吐槽】COCO2014-05-2210:00:58grid509870841001:25:27-0:00oraclectospdg(LOCAL=NO)grid510527281001:26:58-0:00oraclectospdg(LOCAL=NO)grid511181161001:25:26-0:00oraclectospdg(LOCAL=NO)grid511836481001:24:35-0:00oraclectospdg(LOCAL=NO)grid512491941001:25:26-0:00oraclectospdg(LOCAL=NO)grid513147401001:26:26-0:00oraclectospdg(LOCAL=NO)grid513802601001:25:35-0:00oraclectospdg(LOCAL=NO)grid514458321001:26:35-0:00oraclectospdg(LOCAL=NO)grid515113361001:26:00-0:00oraclectospdg(LOCAL=NO)grid515769201001:26:57-0:00oraclectospdg(LOCAL=NO)grid516424401001:27:58-0:00oraclectospdg(LOCAL=NO)grid517079881001:27:28-0:00oraclectospdg(LOCAL=NO)grid517735021001:27:00-0:00oraclectospdg(LOCAL=NO)grid518390521001:32:36-0:00oraclectospdg(LOCAL=NO)。10:02:06【活跃】淋雨的蚊子2014-05-2210:02:06是不是你的gd会话把所有的会话都占用完了【吐槽】COCO2014-05-2210:02:52不就不知道这些GRID进程是怎么来的？一般来说不会有这么多GRID进程的啊？【吐槽】COCO2014-05-2210:03:51而现在我是否能随便KILL掉这些GRID进程？这些GRID进程是否就是连接到我的数据库CTOSPDG的呢？10:05:04【话唠】田新营2014-05-2210:05:04echo$ORACLE_SID10:05:33【吐槽】COCO2014-05-2210:05:33oraclectosb01:/oracle/product/diag/rdbms/ctospdg/ctospdg/traceecho$ORACLE_SIDctospdg【话唠】田新营2014-05-2210:06:18ALERT_SID.LOG【话唠】田新营2014-05-2210:06:49服务器上跑了几个库?【吐槽】COCO2014-05-2210:07:14就一个数据库ThuMay2202:40:182014MediaRecoveryDelayedfor120minute(s)(thread1sequence134529)ThuMay2202:41:062014*FatalNIconnecterror12170.VERSIONINFORMATION:TNSforIBM/AIXRISCSystem/6000:Version.0-ProductionTCP/IPNTProtocolAdapterforIBM/AIXRISCSystem/6000:Version.0-ProductionOracleBequeathNTProtocolAdapterforIBM/AIXRISCSystem/6000:Version.0-ProductionTime:22-MAY-201402:41:06Tracingnotturnedon.Tnserrorstruct:nsmainerrcode:12535TNS-12535:TNS:operationtimedoutnssecondaryerrcode:12606ntmainerrcode:0ntsecondaryerrcode:0ntOSerrcode:0Clientaddress:(ADDRESS=(PROTOCOL=tcp)(HOST=71)(PORT=3077)*FatalNIconnecterror12170.VERSIONINFORMATION:TNSforIBM/AIXRISCSystem/6000:Version.0-ProductionTCP/IPNTProtocolAdapterforIBM/AIXRISCSystem/6000:Version.0-ProductionOracleBequeathNTProtocolAdapterforIBM/AIXRISCSystem/6000:Version.0-ProductionTime:22-MAY-201402:41:06Tracingnotturnedon.Tnserrorstruct:nsmainerrcode:12535TNS-12535:TNS:operationtimedoutnssecondaryerrcode:12606ntmainerrcode:0ntsecondaryerrcode:0ntOSerrcode:0Clientaddress:(ADDRESS=(PROTOCOL=tcp)(HOST=71)(PORT=3077)ThuMay2202:43:392014ORA-00020:maximumnumberofprocesses(1000)exceededORA-20errorswillnotbewrittentothealertlogforthenextminute.PleaselookattracefilestoseealltheORA-20errors.10:09:15【吐槽】COCO2014-05-2210:09:15正常情况下LOG是这样的（延时2小时应用主库LOG）：ThuMay2200:07:582014MediaRecoveryLog+DATA/ctospdg/archivelog/2014_05_21/thread_1_seq_134512.1380.848182077MediaRecoveryLog+DATA/ctospdg/archivelog/2014_05_21/thread_2_seq_132073.1175.848181957ThuMay2200:08:262014MediaRecoveryLog+DATA/ctospdg/archivelog/2014_05_21/thread_2_seq_132074.2491.848182075ThuMay2200:08:492014MediaRecoveryDelayedfor120minute(s)(thread2sequence132075)ThuMay2200:10:062014RFS45:Selectedlog12forthread2sequence132088dbid-1631430854branch771064765ThuMay2200:10:072014RFS54:Selectedlog10forthread1sequence134526dbid-1631430854branch771064765ThuMay2200:10:082014ArchivedLogentry252633addedforthread2sequence132087ID0xffffffff9ed0ea29dest1:ARC0:Archivelogthread2sequence132087availablein120minute(s)ThuMay2200:10:092014ArchivedLogentry252634addedforthread1sequence134525ID0xffffffff9ed0ea29dest1:ARC2:Archivelogthread1sequence134525availablein120minute(s)ThuMay2200:17:572014MediaRecoveryLog+DATA/ctospdg/archivelog/2014_05_21/thread_2_seq_132075.712.848182673MediaRecoveryLog+DATA/ctospdg/archivelog/2014_05_21/thread_1_seq_134513.1494.848182675MediaRecoveryDelayedfor120minute(s)(thread2sequence132076)ThuMay2200:20:052014RFS45:Selectedlog11forthread2sequence132089dbid-1631430854branch771064765ThuMay2200:20:052014RFS54:Selectedlog9forthread1sequence134527dbid-1631430854branch771064765ThuMay2200:20:072014ArchivedLogentry252635addedforthread1sequence134526ID0xffffffff9ed0ea29dest1:ARC4:Archivelogthread1sequence134526availablein120minute(s)ThuMay2200:20:082014ArchivedLogentry252636addedforthread2sequence132088ID0xffffffff9ed0ea29dest1:ARC3:Archivelogthread2sequence132088availablein120minute(s)10:11:07【话唠】田新营2014-05-2210:11:07local=no的是用户进程,可以杀掉,看看或直接重启服务器【吐槽】COCO2014-05-2210:11:48为什么会有这么多GRID的用户进程呢？【话唠】田新营2014-05-2210:12:07正常情况下,sys是永远可以登入的【话唠】田新营2014-05-2210:12:21你用了asm吗?【吐槽】COCO2014-05-2210:12:37是用了ASM的【吐槽】COCO2014-05-2210:12:57正常情况下不会有这么多GRID进程的10:13:34【话唠】田新营2014-05-2210:13:34gird用户下,asm库被db进程连接【话唠】田新营2014-05-2210:13:57db开多了后台进程连asm【话唠】田新营2014-05-2210:14:15你的db是安装在oracle用户下吗?【吐槽】COCO2014-05-2210:14:47我的DB是装在ORACLE用户的啊，按道理GRID应该不会有那么多进程访问我的DB的吧，【话唠】田新营2014-05-2210:15:18asm库的日志?10:15:31【吐槽】COCO2014-05-2210:15:31=【话唠】田新营2014-05-2210:16:19lsnrctlstatus【吐槽】COCO2014-05-2210:17:11gridcctd01:/home/gridlsnrctlstatusLSNRCTLforIBM/AIXRISCSystem/6000:Version.0-Productionon22-MAY-201410:15:36Copyright(c)1991,2011,Oracle.Allrightsreserved.Connectingto(DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)STATUSoftheLISTENER-AliasLISTENERVersionTNSLSNRforIBM/AIXRISCSystem/6000:Version.0-ProductionStartDate12-DEC-201314:41:44Uptime160days19hr.33min.51secTraceLeveloffSecurityON:LocalOSAuthenticationSNMPONListenerParameterFile/oracle/grid/network/adminstener.oraListenerLogFile/oracle/base/diag/tnslsnr/cctd01stener/alert/log.xmlListeningEndpointsSummary.(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=2)(PORT=1521)(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=3)(PORT=1521)ServicesSummary.Service+ASMhas1instance(s).Instance+ASM1,statusREADY,has1handler(s)forthisservice.Servicecctdhas1instance(s).Instancecctd1,statusREADY,has1handler(s)forthisservice.ServicecctdXDBhas1instance(s).Instancecctd1,statusREADY,has1handler(s)forthisservice.Thecommandcompletedsuccessfully【吐槽】COCO2014-05-2210:17:31ASM的日志在哪个目录啊？10:17:56【话唠】田新营2014-05-2210:17:56登入asm库,showparameterback【话唠】田新营2014-05-2210:18:52监听中根本看不到你的dg库【吐槽】COCO2014-05-2210:19:32刚才错了,是这个：oraclectosb01:/oracle/base/diag/tnslsnr/ctosb01stener/tracelsnrctlstatusLSNRCTLforIBM/AIXRISCSystem/6000:Version.0-Productionon22-MAY-201410:17:44Copyright(c)1991,2011,Oracle.Allrightsreserved.Connectingto(ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521)STATUSoftheLISTENER-AliasLISTENERVersionTNSLSNRforIBM/AIXRISCSystem/6000:Version.0-ProductionStartDate15-MAY-201313:29:26Uptime371days20hr.48min.17secTraceLeveloffSecurityON:LocalOSAuthenticationSNMPONListenerParameterFile/oracle/grid/network/adminstener.oraListenerLogFile/oracle/base/diag/tnslsnr/ctosb01stener/alert/log.xmlListeningEndpointsSummary.(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ctosb01)(PORT=1521)ServicesSummary.Service+ASMhas1instance(s).Instance+ASM,statusREADY,has1handler(s)forthisservice.Servicectosihas1instance(s).Instancectosi,statusREADY,has1handler(s)forthisservice.ServicectosiXDBhas1instance(s).Instancectosi,statusREADY,has1handler(s)forthisservice.ServicectospXDBhas1instance(s).Instancectospdg,statusREADY,has1handler(s)forthisservice.Servicectospdghas2instance(s).Instancectospdg,statusUNKNOWN,has1handler(s)forthisservice.Instancectospdg,statusREADY,has1handler(s)forthisservice.Thecommandcompletedsuccessfully【话唠】田新营2014-05-2210:19:33数据库rac,灾备是单实例?10:21:58【话唠】田新营2014-05-2210:21:58上面有2个库,难道是超出了os的资源限制?【话唠】田新营2014-05-2210:22:34sys登录ctosi看看【吐槽】COCO2014-05-2210:22:50数据库是单实例，是DG的备库【吐槽】COCO2014-05-2210:23:21是有2个数据库，压力都很小的，在CTOSI库看什么呢？【话唠】田新营2014-05-2210:23:56可不可以用sys连接【吐槽】COCO2014-05-2210:24:19CTOSI这个库没有问题：oraclectosb01:/oracle/product/diag/rdbms/ctospdg/ctospdg/traceexportORACLE_SID=ctosioraclectosb01:/oracle/product/diag/rdbms/ctospdg/ctospdg/tracesqlplus/assysdbaSQL*Plus:Release.0ProductiononThuMay2210:22:172014Copyright(c)1982,2011,Oracle.Allrightsreserved.Connectedto:OracleDatabase11gEnterpriseEditionRelease.0-64bitProductionWiththePartitioning,AutomaticStorageManagement,OracleLabelSecurity,OLAP,DataMiningandRealApplicationTestingoptionsSQLshowparameterinstNAMETYPEVALUE-active_instance_countintegercluster_database_instancesinteger1instance_groupsstringinstance_namestringctosiinstance_numberinteger0instance_typestringRDBMSopen_links_per_instanceinteger8parallel_instance_groupstringparallel_server_instancesinteger110:25:45【话唠】田新营2014-05-2210:25:45ps-ef|greporacle现在是不知道该KILL那个进程啊？grid509870841001:25:27-0:00oraclectospdg(LOCAL=NO)grid510527281001:26:58-0:00oraclectospdg(LOCAL=NO)grid511181161001:25:26-0:00oraclectospdg(LOCAL=NO)grid511836481001:24:35-0:00oraclectospdg(LOCAL=NO)grid512491941001:25:26-0:00oraclectospdg(LOCAL=NO)grid513147401001:26:26-0:00oraclectospdg(LOCAL=NO)grid513802601001:25:35-0:00oraclectospdg(LOCAL=NO)grid514458321001:26:35-0:00oraclectospdg(LOCAL=NO)grid515113361001:26:00-0:00oraclectospdg(LOCAL=NO)grid515769201001:26:57-0:00oraclectospdg(LOCAL=NO)grid516424401001:27:58-0:00oraclectospdg(LOCAL=NO)grid517079881001:27:28-0:00oraclectospdg(LOCAL=NO)grid517735021001:27:00-0:00oraclectospdg(LOCAL=NO)grid518390521001:32:36-0:00oraclectospdg(LOCAL=NO)【话唠】田新营2014-05-2210:26:12杀掉local=no的部门进程,再用sys连接dg【吐槽】COCO2014-05-2210:27:06哦，好，我试试【话唠】田新营2014-05-2210:27:34连接后,showparameterprocesses10:28:25【话唠】田新营2014-05-2210:28:25SELECTusername,MACHINE,count(1)fromgv$sessiongroupbyusername,MACHINE;10:31:34【吐槽】COCO2014-05-2210:31:34要KILL多少才行啊？KILL了好几个还是不行啊？10:34:22【活