[20190530]sqlplus preliminary connection.txt
--//前几天遇到的问题,别人系统看提示明显大量用户登录导致的问题.
--//ORA-00020: maximum number of processes (300) exceeded
--//不过让我吃惊的是使用sqlplus preliminary依旧可以登录,在我的理解sqlplus preliminary方式仅仅没有执行一些sql语句.
--//还是通过测试加强理解.
1.环境:
SCOTT@book> @ ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
SCOTT@book> show parameter processes
NAME TYPE VALUE
------------------------- ------- -----
aq_tm_processes integer 0
db_writer_processes integer 3
gcs_server_processes integer 0
global_txn_processes integer 1
job_queue_processes integer 200
log_archive_max_processes integer 2
processes integer 200
--//我的测试环境processes = 200.
2.测试:
$ cat a.sql
host sleep &&1
quit
$ seq 200 | xargs -P 200 -I{} sqlplus -s -l scott/book @ a.sql 300
$ rlsql scott/book
SQL*Plus: Release 11.2.0.4.0 Production on Thu May 30 09:52:19 2019
Copyright (c) 1982, 2013, Oracle. All rights reserved.
ERROR:
ORA-00020: maximum number of processes (200) exceeded
$ sqlplus -prelim / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu May 30 09:52:34 2019
Copyright (c) 1982, 2013, Oracle. All rights reserved.
SYS@book> show sga
ORA-01012: not logged on
Process ID: 0
Session ID: 0 Serial number: 0
SYS@book> @ spid
select s.sid,s.serial# ,s.process,s.server,p.spid,p.pid,p.serial# p_serial#,'alter system kill session '''||s.sid||','||s.serial#||''''||' immediate;' c50 from v$session s,v$process p where s.sid in (select sid from v$mystat where rownum=1) and s.paddr=p.addr
*
ERROR at line 1:
ORA-01012: not logged on
Process ID: 0
Session ID: 0 Serial number: 0
--//实际上这个时候不能执行任何sql语句,仅仅通过调用oradebug
SYS@book> oradebug setmypid
Statement processed.
SYS@book> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/book/book/trace/book_ora_35095.trc
--//oradebug hanganalyze 3
$ ps -ef | grep preli[m]
oracle 35094 51302 0 09:54 pts/2 00:00:00 sqlplus -prelim as sysdba
$ pstree -p | grep 35094
| |-bash(51302)---sqlplus(35094)---oracle(35095)
$ ps -ef | grep 3509[5]
oracle 35095 35094 0 09:54 ? 00:00:00 oraclebook (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
--//也就是实际上这个时候并不会全部用完全部processes进程.
3.继续测试:
$ seq 200 | xargs -P 200 -I{} sqlplus -s -l scott/book @ a.sql 300
$ ipcs -m
------ Shared Memory Segments --------
key shmid owner perms bytes nattch status
0x00000000 364773385 oracle 640 12582912 198
0x00000000 364806154 oracle 640 633339904 198
0xe8a8ec10 364838923 oracle 640 2097152 198
--//实际上nattch,也就是还可以有2个进程可以连接,测试看看.连续执行3次sqlplus -prelim / as sysdba.
$ ipcs -m
------ Shared Memory Segments --------
key shmid owner perms bytes nattch status
0x00000000 364773385 oracle 640 12582912 201
0x00000000 364806154 oracle 640 633339904 201
0xe8a8ec10 364838923 oracle 640 2097152 201
--//nattch=201,可以发现这样连接方式不受限制.
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-2646153/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/267265/viewspace-2646153/