[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,可以發現這樣連線方式不受限制.