当数据库的连接数达到上限后,后续的登陆操作都会报ORA-00020错误,这里给出ORA-00020错误的模拟及处理方法。1.调整数据库的processes参数到251)由于processes参数是静态参数,调整时需要使用“scope=spfile”选项进行调整。sys@ora11g> alter system set processes=25 scope=spfile;System altered.2)重启数据库使参数调整生效sys@ora11g> shutdown immediate;Database closed.Database dismounted.ORACLE instance shut down.sys@ora11g> startup;ORACLE instance started.Total System Global Area 535662592 bytesFixed Size 1337720 bytesVariable Size 398460552 bytesDatabase Buffers 130023424 bytesRedo Buffers 5840896 bytesDatabase mounted.Database opened.3)确认调整结果sys@ora11g> show parameter processesNAMETYPE VALUE--------------------------------- -------------aq_tm_processesinteger 0db_writer_processesinteger 1gcs_server_processesinteger 0global_txn_processesinteger 1job_queue_processesinteger 1000log_archive_max_processesinteger 4processesinteger 25此时数据库的processes参数已经成功调整为25。2.查看当前数据库进程数sys@ora11g> select count(*) from v$process;COUNT(*)----------23此时进程数是23。25是进程数的上限,因此次数据库实例还可以支持一个进程连接。3.开启新窗口连接数据库实例ora11g@secdb /home/oracle$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.1.0 Production on Mon Dec 12 21:25:30 2011Copyright (c) 1982, 2009, Oracle. All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionssys@ora11g> select count(*) from v$process;COUNT(*)----------24此时显示成功连接到数据库实例,进程数显示为24。4.再次开启新窗口创建数据库连接ora11g@secdb /home/oracle$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.1.0 Production on Mon Dec 12 21:26:37 2011Copyright (c) 1982, 2009, Oracle. All rights reserved.ERROR:ORA-00020: maximum number of processes (25) exceededEnter user-name:ORA-00020错误已经成功模拟出来,原因是此时已经达到数据库进程数的上限25。注意25表示最大的进程数,即当进程数为25时即会报错,数据库实例最多支持24个有效的进程。5.处理ORA-00020错误既然是由于进程数过多到时的报错,因此我们最直接的处理方法便是手工杀掉无用的用户连接。1)查看数据库后台进程信息ora10g@secdb /home/oracle$ ps -ef | grep ora11goracle 22882 1 0 21:24 ? 00:00:00 ora_pmon_ora11goracle 22884 1 0 21:24 ? 00:00:00 ora_vktm_ora11goracle 22888 1 0 21:24 ? 00:00:00 ora_gen0_ora11goracle 22890 1 0 21:24 ? 00:00:00 ora_diag_ora11goracle 22892 1 0 21:24 ? 00:00:00 ora_dbrm_ora11goracle 22894 1 0 21:24 ? 00:00:00 ora_psp0_ora11goracle 22896 1 0 21:24 ? 00:00:00 ora_dia0_ora11goracle 22898 1 0 21:24 ? 00:00:00 ora_mman_ora11goracle 22900 1 0 21:24 ? 00:00:00 ora_dbw0_ora11goracle 22902 1 0 21:24 ? 00:00:00 ora_lgwr_ora11goracle 22904 1 0 21:24 ? 00:00:00 ora_ckpt_ora11goracle 22906 1 0 21:24 ? 00:00:00 ora_smon_ora11goracle 22908 1 0 21:24 ? 00:00:00 ora_reco_ora11goracle 22910 1 0 21:24 ? 00:00:00 ora_mmon_ora11goracle 22912 1 0 21:24 ? 00:00:00 ora_mmnl_ora11goracle 22914 1 0 21:24 ? 00:00:00 ora_d000_ora11goracle 22916 1 0 21:24 ? 00:00:00 ora_s000_ora11goracle 22945 22103 0 21:24 ? 00:00:00 oracleora11g (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))oracle 22947 1 0 21:24 ? 00:00:00 ora_qmnc_ora11goracle 22961 1 0 21:24 ? 00:00:00 ora_cjq0_ora11goracle 22972 1 0 21:25 ? 00:00:00 ora_q000_ora11goracle 22974 1 0 21:25 ? 00:00:00 ora_q001_ora11goracle 22993 22992 0 21:25 ? 00:00:00 oracleora11g (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))oracle 23106 23066 0 21:27 pts/6 00:00:00 grep --color ora11g这里显示出两类进程,一类是Oracle数据库的后台进程,另外一类是用户连接进程。我们可以考虑讲用户连接进程杀掉,注意Oracle数据库后台进程不可轻易手工杀掉。2)杀掉用户连接进程22945ora10g@secdb /home/oracle$ kill -9 229453)尝试重新连接数据库ora11g@secdb /home/oracle$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.1.0 Production on Mon Dec 12 21:28:56 2011Copyright (c) 1982, 2009, Oracle. All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionssys@ora11g>连接成功。到此ORA-00020错误便处理完毕。6.小结本文给出了ORA-00020错误的模拟以及简单的处理方法。对于生产环境最有效的避免发生ORA-00020错误的方法便是,上线前充分评估系统需要的最大进程数,一次性设置充分。这样便可以从根本上防止ORA-00020错误的发生。Good luck.secooler11.12.12-- The End --
-00020 ora_【CONNECT】ORA-00020错误模拟及处理方法
最新推荐文章于 2022-05-06 17:49:14 发布
当数据库连接数超过上限时,会出现ORA-00020错误。解决方法包括调整数据库的processes参数并重启,查看并杀死无用的用户连接。本文提供了详细的模拟和处理步骤。
摘要由CSDN通过智能技术生成