参数文件中的process

最新推荐文章于 2024-06-12 14:40:03 发布

cuishipin9918

最新推荐文章于 2024-06-12 14:40:03 发布

阅读量264

点赞数

文章标签：数据库

oracle有两种模式：专用服务器和共享服务器
专用模式下，一个会话对应一个server process，控制server process的参数为Process，于是想到做实验看看Process不够用时，oracle会怎么样，想连上来的用户又会怎么样。

--现在process=150
A>show parameter process

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes                      integer     0
cell_offload_processing              boolean     TRUE
db_writer_processes                  integer     1
gcs_server_processes                 integer     0
global_txn_processes                 integer     1
job_queue_processes                  integer     1000
log_archive_max_processes            integer     4
processes                            integer     150

--先改成2看看效果
A>alter system set processes=2 scope=spfile;

System altered.

A>conn /as sysdba
Connected.
SYS>shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS>startup
ORA-00444: background process "DIAG" failed while starting
ORA-00020: maximum number of processes () exceeded

--看来这个process不仅仅包含用户会话数量，还包括后台进程，我设得太小，把后台进程也影响到了

--不知道后台进程有多少，于是到alert日志数了下，有21个，于是process=21
SYS>startup pfile=/home/oracle/orcl.ora;
ORACLE instance started.

Total System Global Area 304807936 bytes
Fixed Size                  2212856 bytes
Variable Size             197135368 bytes
Database Buffers          100663296 bytes
Redo Buffers                4796416 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00450: background process 'QMNC' did not start           //还有进程没起来，看样还得再改大点
ORA-00444: background process "QMNC" failed while starting
ORA-00020: maximum number of processes () exceeded
Process ID: 14430
Session ID: 19 Serial number: 3

--改成25+1=26

SYS>startup pfile=/home/oracle/orcl.ora;
ORACLE instance started.

Total System Global Area 304807936 bytes
Fixed Size                  2212856 bytes
Variable Size             197135368 bytes
Database Buffers          100663296 bytes
Redo Buffers                4796416 bytes
ORA-00205: error in identifying control file, check alert log for more info

--调整process和控制文件有毛关系，于是关库再起，NND，又没事了

SYS>shut immediate
ORA-01507: database not mounted

ORACLE instance shut down.

SYS>startup pfile=/home/oracle/orcl.ora;
ORACLE instance started.

--不过诡异的事情出现了，当我退出重连时，却死活连不上去了，sysdba不行——原来也有sysdba做不到的事啊!

SYS>exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
rlwrap: read error on master pty: Input/output error
[oracle@win7 ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Tue May 27 14:40:15 2014

>conn a/a
ERROR:
ORA-00020: maximum number of processes (26) exceeded

>conn /as sysdba
ERROR:
ORA-00020: maximum number of processes (26) exceeded

--要是以前只能关机重启了，这次用得是ASM，看看连带效应
[grid@win7 ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Tue May 27 14:47:11 2014

SQL> conn /as sysasm
Connected.
SQL> shutdown immediate
ORA-15097: cannot SHUTDOWN ASM instance with connected client
SQL> shutdown abort
ASM instance shutdown

>conn /as sysdba
Connected to an idle instance.
SYS>startup pfile=/home/oracle/orcl.ora;
ORACLE instance started.

[oracle@win7 ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Tue May 27 15:37:55 2014

>conn a/a
ERROR:
ORA-00020: maximum number of processes (26) exceeded //其实我想要的只是这么个效果

==========================================================

最后附上alert日志中部分告警信息

Starting background process QMNC（见附一）
Tue May 27 14:50:29 2014
QMNC started with pid=25, OS id=15014
Completed: ALTER DATABASE OPEN
Tue May 27 14:50:37 2014
ORA-00020: No more process state objects available
ORA-20 errors will not be written to the alert log for
the next minute. Please look at trace files to see all
the ORA-20 errors.
Process m000 submission failed with error = 20
Process m001 submission failed with error = 20
Process m002 submission failed with error = 20
Process m003 submission failed with error = 20
Process m000 submission failed with error = 20
Process m001 submission failed with error = 20
Tue May 27 14:50:39 2014
Process q000 submission failed with error = 20
Tue May 27 14:50:40 2014
Starting background process CJQ0
Process CJQ0 submission failed with error = 20
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_pmon_14937.trc:
ORA-00444: background process "CJQ0" failed while starting
ORA-00020: maximum number of processes () exceeded
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_pmon_14937.trc:
ORA-00450: background process 'CJQ0' did not start
ORA-00444: background process "CJQ0" failed while starting
ORA-00020: maximum number of processes () exceeded
Restarting dead background process CJQ0
Process CJQ0 submission failed with error = 20
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_pmon_14937.trc:
ORA-00444: background process "CJQ0" failed while starting
ORA-00020: maximum number of processes () exceeded
Starting background process CJQ0
Process CJQ0 submission failed with error = 20
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_pmon_14937.trc:
ORA-00444: background process "CJQ0" failed while starting
ORA-00020: maximum number of processes () exceeded
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_pmon_14937.trc:
ORA-00450: background process 'CJQ0' did not start
ORA-00444: background process "CJQ0" failed while starting
ORA-00020: maximum number of processes () exceeded
Tue May 27 14:50:49 2014
Process q000 submission failed with error = 20

注：
QMNC AQ Coordinator Process
QMNC is responsible for facilitating various background activities required by AQ and Oracle Streams: time management of messages, management of nonpersistent queues, cleanup of resources, and so on. QMNC dynamically spawns Qnnn processes as needed for performing these tasks.
CJQ0 job coordinator process (CJQ0)
Mnnn MMON Slave Process
Mnnn performs manageability tasks dispatched to them by MMON. Tasks performed include taking Automatic Workload Repository snapshots and Automatic Database Diagnostic Monitor analysis.Database and ASM instances

附一
oracle官网上有这样一段话
Note that if the AQ_TM_PROCESSES initialization parameter is set to 0, this process will not start. The database writes the following message to the alert log: WARNING: AQ_TM_PROCESSES is set to 0. System might be adversely affected.

--可尽管我的机器上该值为0，可alert日志显示该服务还是照常启动了
SYS>show parameter aq

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes integer 0
SYS>

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29646619/viewspace-1171053/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29646619/viewspace-1171053/