[20190530]sqlplus preliminary connection.txt

[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,可以发现这样连接方式不受限制.