Shulou(Shulou.com)06/01 Report--

Summary: the number of parallel processes is too large to connect to the database

Read the original text to improve the reading experience: https://www.modb.pro/db/22961?cyn

# Database parallel process exception

# # problem description

Sudden host ora_p processes suddenly increase, resulting in process full, resulting in sqlplus unable to log in

ERROR:

ORA-00020: maximum number of processes (2000) exceeded

# # View the process:

-Foreign connections:

Ps-ef | grep LOCAL=NO | grep-v grep | wc-l

-Local connection

Ps-ef | grep ora | grep-v grep | wc-l

2026

Ps-ef | grep ora_p | grep-v grep | wc-l

PS:ora_p148_XXXXXdb1

# # troubleshooting logs

Fri Mar 20 15:14:12 2020

ORA-00020: maximum number of processes (2000) exceeded

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 PA47 submission failed with error = 20

# # the reason why there are so many ora processes is that there are more than 2000 concurrent processes such as ora_p.

The basic positioning problem is caused by the parallelism of sql.

Solution:

1. Since sqlplus can no longer log in, to kill the process, log in and have a look.

Because it is caused by parallelism, the number of non-connections is too large, so you don't have to turn off listening.

In order to prevent killing the database process itself (ora_pmon, etc.), you need to filter the kill process

Ps-ef | grep ora_p1 | grep-v grep | awk'{print $2}'| xargs kill-9

2. After the process is down, log in to sqlplus and find the problem SQL

INST_ID USERNAME OSUSER MACHINE MODULE SQL_ID CNT

1 NF_XXXX Administrator WorkGroup\ ECW42E3712K11RR PL/SQL Developer 9y8pxh9ax0nyz 687

3. After viewing the execution plan, determine that it is caused by parallelism

4. If you want to get back to normal immediately, kill the sql session

SELECT a. "INST_ID", a. "SQL_ID", 'kill-9' | | b. "SPID" pid_kill,'alter system kill session''| | A.SID | |','| A.SERIAL# | |''; 'sid_kill FROM gv g vprocess b WHERE a. "PADDR" = b. "ADDR" AND a.sqlroomidcards

5. Track the cause of the source, check the monitoring log, and locate the source.

Since it is parallel, there are only one or two connections. You can locate the host IP by time or host name.

Vim / u01/app/grid/diag/tnslsnr/dzswjnfdb1/listener/alert/log.xml

Search: ECW42E3712K11RR

20-MAR-2020 15:11:12 * (CONNECT_DATA= (SERVICE_NAME=sngsnfdb) (CID= (PROGRAM=C:\ Program?Files??x86?\ PLSQL?Developer\ plsqldev.exe) (HOST=ECW42E3712K11RR)

(USER=Administrator) * (ADDRESS= (PROTOCOL=tcp) (HOST=10.10.85.178) (PORT=63350)) * establish * sngsnfdb * 0

20-MAR-2020 15:11:12 * (CONNECT_DATA= (SERVICE_NAME=sngsnfdb) (CID= (PROGRAM=C:\ Program?Files??x86?\ PLSQL?Developer\ plsqldev.exe) (HOST=ECW42E3712K11RR)

(USER=Administrator) * (ADDRESS= (PROTOCOL=tcp) (HOST=10.10.85.178) (PORT=63351)) * establish * sngsnfdb * 0

More database related articles: https://www.modb.pro/db?cyn

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.