Error ORA-00020 in alert log
===============================
Mon Nov 09 23:22:03 2015
Archived Log entry 83763 added for thread 1 sequence 83774 ID 0x36162634 dest 1:
Mon Nov 09 23:42:04 2015
Thread 1 advanced to log sequence 83776 (LGWR switch)
Current log# 1 seq# 83776 mem# 0: /oracle_db/db1/db_igt/ora_redo_01_a.rdo
Mon Nov 09 23:42:05 2015
Archived Log entry 83764 added for thread 1 sequence 83775 ID 0x36162634 dest 1:
Mon Nov 09 23:50:04 2015
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 J002 submission failed with error = 20
kkjcre1p: unable to spawn jobq slave process
Errors in file /software/oracle/diag/rdbms/igt/igt/trace/igt_cjq0_21012.trc:
Process J002 submission failed with error = 20
kkjcre1p: unable to spawn jobq slave process
Errors in file /software/oracle/diag/rdbms/igt/igt/trace/igt_cjq0_21012.trc:
Mon Nov 09 23:51:13 2015
ORA-00020: maximum number of processes 0 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.
Mon Nov 09 23:51:56 2015
Shutting down instance (abort)
License high water mark = 270
USER (ospid: 5241): terminating the instance
Mon Nov 09 23:51:57 2015
ORA-1092 : opiodr aborting process unknown ospid (30859_47851384046032)
Mon Nov 09 23:51:57 2015
ORA-1092 : opitsk aborting process
Mon Nov 09 23:51:57 2015
ORA-1092 : opiodr aborting process unknown ospid (10833_47625099882960)
Instance terminated by USER, pid = 5241
Mon Nov 09 23:51:59 2015
Instance shutdown complete
===============================
Error ORA-00020 in trace file
===============================
Error ORA-00020 in trace file
===============================
/software/oracle/diag/rdbms/orainst/orainst/trace/orainst_cjq0_21012.trc:
Trace file /software/oracle/diag/rdbms/orainst/orainst/trace/orainst_cjq0_21012.trc
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
ORACLE_HOME = /software/oracle/111
System name: Linux
Node name: tha-ais-2-aps-5
Release: 2.6.18-308.24.1.el5
Version: #1 SMP Wed Nov 21 11:42:14 EST 2012
Machine: x86_64
Instance name: igt
Redo thread mounted by this instance: 1
Oracle process number: 274
Unix process pid: 21012, image: oracle@tha-ais-2-aps-5 (CJQ0)
*** 2015-11-09 23:50:04.616
*** SESSION ID:(83.28755) 2015-11-09 23:50:04.616
*** CLIENT ID:() 2015-11-09 23:50:04.616
*** SERVICE NAME:(SYS$BACKGROUND) 2015-11-09 23:50:04.616
*** MODULE NAME:() 2015-11-09 23:50:04.616
*** ACTION NAME:() 2015-11-09 23:50:04.616
ORA-00020: No more process state objects available
ORA-00020: No more process state objects available
ORA-00020: No more process state objects available
*** 2015-11-09 23:50:09.627
ORA-00020: No more process state objects available
===============================
Check in Database
===============================
===============================
COL RESOURCE_NAME FOR A20
COL LIMIT_VALUE FOR A20
SELECT resource_name, current_utilization, limit_value
FROM V$RESOURCE_LIMIT
WHERE resource_name in ('sessions','processes','transactions');
RESOURCE_NAME CURRENT_UTILIZATION LIMIT_VALUE
-------------------- ------------------- -----------
processes 1041 2000
sessions 1015 3072
transactions 7 UNLIMITED
SELECT *
FROM V$RESOURCE_LIMIT
FROM V$RESOURCE_LIMIT
WHERE resource_name in ('sessions','processes','transactions');
RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_ALLOCATION LIMIT_VALUE
-------------------- ------------------- --------------- -------------------- -----------
processes 288 296 300 300
sessions 296 319 335 335
transactions 4294965549 4294967295 368 UNLIMITED
SET LINESIZE 120
SET PAGESIZE 1000
COL MACHINE FOR A30
COL USERNAME FOR A30
COL PROGRAM FOR A30
SELECT MACHINE, USERNAME, PROGRAM, COUNT(*)
FROM V$SESSION
GROUP BY MACHINE, USERNAME, PROGRAM
HAVING count(*) > 5
ORDER BY COUNT(*) DESC;
MACHINE USERNAME PROGRAM COUNT(*)
----------------- ------------------- ---------------------------------------- ---------
esp-vod-9-aps-1 IRL_VODAF_SPARX JDBC Thin Client 37
esp-vod-9-aps-1 ZAF_VODAC_SPARX JDBC Thin Client 32
esp-vod-9-aps-1 NZL_VODAF_SPARX JDBC Thin Client 31
esp-vod-9-aps-1 NZL_VODAF_SFIQQ JDBC Thin Client 18
esp-vod-9-aps-1 IRL_VODAF_SFIQQ JDBC Thin Client 18
esp-vod-9-aps-1 ZAF_VODAC_SFIQQ JDBC Thin Client 18
esp-vod-9-aps-1 ROM_VODAF_SPARX JDBC Thin Client 16
esp-vod-9-aps-1 CZE_VODAF_SBQQQ proxy@esp-vod-9-aps-1 (TNS V1-V3) 12
jdbcclient MLT_VODAF_MOCOQ JDBC Thin Client 11
esp-vod-9-aps-1 IRL_VODAF_SPARX notification@esp-vod-9-aps-1 (TNS V1-V3) 6
esp-vod-9-aps-1 ZAF_VODAC_SPARX notification@esp-vod-9-aps-1 (TNS V1-V3) 6
Solution
===============================
The computed number for PROCESSES is derived form SESSIONS number.
In 10g, use following formula:
(1.1 * PROCESSES) + 5
In 11g R1 onward it changed to
(1.5 * PROCESSES) + 22
When increasing PROCESSES parameter, one should also increase SESSIONS and TRANSACTIONS parameters.
In 10g, use following formula:
(1.1 * PROCESSES) + 5
In 11g R1 onward it changed to
(1.5 * PROCESSES) + 22
When increasing PROCESSES parameter, one should also increase SESSIONS and TRANSACTIONS parameters.
For Oracle 10g: the default formula for determining these parameter values is as follows:
processes=x
sessions=x*1.1+5
transactions=sessions*1.1
for example:
300, 335, 370
500, 555, 610
600, 665, 730
900, 995,1095
1000,1105,1215
These parameters can't be modified in memory.
You have to modify the spfile only (scope=spfile) and bounce the instance.
ALTER SYSTEM SET PROCESSES=500 SCOPE=SPFILE;
ALTER SYSTEM SET SESSIONS=555 SCOPE=SPFILE;
ALTER SYSTEM SET TRANSACTIONS=610 SCOPE=SPFILE;
SHUTDOWN ABORT;
STARTUP;
===============================
Preliminary session option.
===============================
The -prelim option.
Sometimes, because the threashold of session is reached, a DBA cannot login to the database in order to perform a SHUTDOWN command.
In this case, the solution would be to use the -prelim option.
When opening a session in Preliminary mode, it will not try to create private session structures in the SGA.
This allows you to connect to perform debugging or shutdown operations.
For Example:
sqlplus /nolog
SQL*Plus: Release 11.2.0.4.0 Production on Fri Jun 6 09:07:23 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
@ SQL> connect /
ERROR:
ORA-00020: maximum number of processes (1000) exceeded
exit
sqlplus -prelim "/ as sysdba"
SQL*Plus: Release 11.2.0.4.0 Production on Fri Jun 6 09:09:15 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
SQL> shutdown abort
ORACLE instance shut down.
SQL> exit
Disconnected from ORACLE
===============================
Checking Current Situation
===============================
SET lines 140 pages 1000
COL RESOURCE_NAME FOR A20
SELECT *
FROM v$resource_limit
WHERE resource_name='processes';
RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_ALLOCATION LIMIT_VALUE
------------- ------------------- --------------- ------------------ --------------------
processes 249 255 300 300
===============================
Monitoring from Database
===============================
When answering "Why did ORA-00020 occur, first option would be to query historical data.
If that data is not available, or not meaningful, it is possible to log sessions count to a table every N minutes.
Following example used Oracle JOB mechanism to call every 5 minutes a procedure.
The Procedure would log data to a table
---------------------------------------
Create Tablespace
---------------------------------------
CREATE TABLESPACE MONITOR_TBS DATAFILE '/oracle_db/db1/db_igt/ora_monitor_table_01.dbf' SIZE 10M AUTOEXTEND ON NEXT 10M MAXSIZE 1000M EXTENT MANAGEMENT LOCAL;
---------------------------------------
Create Sequence
---------------------------------------
CREATE SEQUENCE MONITOR_SEQ INCREMENT BY 1 START WITH 1 NOCACHE NOCYCLE;
---------------------------------------
Create Table
---------------------------------------
-- Create table
create table MONITOR_SESSIONS
(
seq_id NUMBER,
run_date VARCHAR2(30),
user_name VARCHAR2(30),
sessions NUMBER
)
tablespace MONITOR_TBS;
---------------------------------------
Grants as sysdba
---------------------------------------
GRANT SELECT ON SYS.V_$SESSION TO AUT_TMOQQ_USERA;
---------------------------------------
Code
---------------------------------------
CREATE OR REPLACE PACKAGE BODY ADMIN_MONITOR IS
-----------------------------------------------------------
PROCEDURE write_sga_w_log(p_module_name IN VARCHAR, p_msg_text IN VARCHAR2) IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO SGA_W_LOG( procedure_name, data ,ts_last_modified)
VALUES ( p_module_name, p_msg_text, SYSDATE);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
-----------------------------------------------------------
PROCEDURE monitor_sessions IS
v_module_name VARCHAR2(30);
v_seq_id NUMBER;
BEGIN
v_module_name := 'monitor_sessions';
SELECT MONITOR_SEQ.nextval INTO v_seq_id FROM DUAL;
INSERT INTO MONITOR_SESSIONS (seq_id,run_date, user_name, sessions)
SELECT v_seq_id, run_date, user_name, sessions FROM (
SELECT TO_CHAR(SYSDATE, 'YYYYMMDD hh24:mi:ss') AS run_date,
username AS user_name,
count(*) AS sessions
FROM V$SESSION
WHERE username IS NOT NULL
GROUP BY username ) SESSIONS_COUNT;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
write_sga_w_log(v_module_name, 'Unexpected Error: '||SQLERRM);
END monitor_sessions;
END ADMIN_MONITOR;
-----------------------------------------------------------
CREATE OR REPLACE PACKAGE ADMIN_MONITOR IS
PROCEDURE write_sga_w_log(p_module_name IN VARCHAR, p_msg_text IN VARCHAR2);
PROCEDURE monitor_sessions;
END ADMIN_MONITOR;
---------------------------------------
Job - to run every 5 minutes
---------------------------------------
DECLARE
v_job_number NUMBER(10);
BEGIN
DBMS_JOB.SUBMIT (JOB => v_job_number,
WHAT => 'ADMIN_MONITOR.monitor_sessions;',
NEXT_DATE => SYSDATE + 5/1440,
INTERVAL => 'SYSDATE + 5/1440'
);
COMMIT;
END;
/
===============================
Preliminary session option.
===============================
The -prelim option.
Sometimes, because the threashold of session is reached, a DBA cannot login to the database in order to perform a SHUTDOWN command.
In this case, the solution would be to use the -prelim option.
When opening a session in Preliminary mode, it will not try to create private session structures in the SGA.
This allows you to connect to perform debugging or shutdown operations.
For Example:
sqlplus /nolog
SQL*Plus: Release 11.2.0.4.0 Production on Fri Jun 6 09:07:23 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
@ SQL> connect /
ERROR:
ORA-00020: maximum number of processes (1000) exceeded
exit
sqlplus -prelim "/ as sysdba"
SQL*Plus: Release 11.2.0.4.0 Production on Fri Jun 6 09:09:15 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
SQL> shutdown abort
ORACLE instance shut down.
SQL> exit
Disconnected from ORACLE
===============================
Checking Current Situation
===============================
COL RESOURCE_NAME FOR A20
SELECT *
FROM v$resource_limit
WHERE resource_name='processes';
RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_ALLOCATION LIMIT_VALUE
------------- ------------------- --------------- ------------------ --------------------
processes 249 255 300 300
SET linesize 140
COL run_date FOR A20
SELECT TO_CHAR(SYSDATE, 'YYYYMMDD hh24:mi:ss') AS run_date,
username AS user_name,
count(*) AS sessions
FROM V$SESSION
WHERE username IS NOT NULL
GROUP BY username
ORDER BY count(*) DESC;
RUN_DATE USER_NAME SESSIONS
-------------------- ------------------------------ ----------
20170308 16:57:10 AUT_TMOQQ_USERA 149
20170308 16:57:10 AUT_TMOBI_USERB 57
20170308 16:57:10 AUT_MAXMO_USERC 11
20170308 16:57:10 AUT_TMOBI_USERD 10
20170308 16:57:10 SYS 1
20170308 16:57:10 SYSTEM 1
===============================
Checking Historic Situation
===============================
===============================
SET lines 200 pages 1000
COL begin_interval_time FOR A20
SELECT HIST_SNAPSHOT.snap_id,
HIST_SNAPSHOT.begin_interval_time,
HIST_RESOURCE_LIMIT.current_utilization,
HIST_RESOURCE_LIMIT.max_utilization,
HIST_RESOURCE_LIMIT.initial_allocation
FROM DBA_HIST_RESOURCE_LIMIT HIST_RESOURCE_LIMIT,
SYS.DBA_HIST_SNAPSHOT HIST_SNAPSHOT
WHERE HIST_RESOURCE_LIMIT.resource_name='processes'
AND HIST_RESOURCE_LIMIT.snap_id=HIST_SNAPSHOT.snap_id
ORDER BY HIST_SNAPSHOT.snap_id DESC;
SELECT parsing_schema_name,COUNT(*)
FROM DBA_HIST_SQLSTAT
WHERE snap_id=51158
GROUP BY parsing_schema_name;
Monitoring from Database
===============================
When answering "Why did ORA-00020 occur, first option would be to query historical data.
If that data is not available, or not meaningful, it is possible to log sessions count to a table every N minutes.
Following example used Oracle JOB mechanism to call every 5 minutes a procedure.
The Procedure would log data to a table
---------------------------------------
Create Tablespace
---------------------------------------
CREATE TABLESPACE MONITOR_TBS DATAFILE '/oracle_db/db1/db_igt/ora_monitor_table_01.dbf' SIZE 10M AUTOEXTEND ON NEXT 10M MAXSIZE 1000M EXTENT MANAGEMENT LOCAL;
---------------------------------------
Create Sequence
---------------------------------------
CREATE SEQUENCE MONITOR_SEQ INCREMENT BY 1 START WITH 1 NOCACHE NOCYCLE;
---------------------------------------
Create Table
---------------------------------------
-- Create table
create table MONITOR_SESSIONS
(
seq_id NUMBER,
run_date VARCHAR2(30),
user_name VARCHAR2(30),
sessions NUMBER
)
tablespace MONITOR_TBS;
---------------------------------------
Grants as sysdba
---------------------------------------
GRANT SELECT ON SYS.V_$SESSION TO AUT_TMOQQ_USERA;
---------------------------------------
Code
---------------------------------------
CREATE OR REPLACE PACKAGE BODY ADMIN_MONITOR IS
-----------------------------------------------------------
PROCEDURE write_sga_w_log(p_module_name IN VARCHAR, p_msg_text IN VARCHAR2) IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO SGA_W_LOG( procedure_name, data ,ts_last_modified)
VALUES ( p_module_name, p_msg_text, SYSDATE);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
-----------------------------------------------------------
PROCEDURE monitor_sessions IS
v_module_name VARCHAR2(30);
v_seq_id NUMBER;
BEGIN
v_module_name := 'monitor_sessions';
SELECT MONITOR_SEQ.nextval INTO v_seq_id FROM DUAL;
INSERT INTO MONITOR_SESSIONS (seq_id,run_date, user_name, sessions)
SELECT v_seq_id, run_date, user_name, sessions FROM (
SELECT TO_CHAR(SYSDATE, 'YYYYMMDD hh24:mi:ss') AS run_date,
username AS user_name,
count(*) AS sessions
FROM V$SESSION
WHERE username IS NOT NULL
GROUP BY username ) SESSIONS_COUNT;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
write_sga_w_log(v_module_name, 'Unexpected Error: '||SQLERRM);
END monitor_sessions;
END ADMIN_MONITOR;
-----------------------------------------------------------
CREATE OR REPLACE PACKAGE ADMIN_MONITOR IS
PROCEDURE write_sga_w_log(p_module_name IN VARCHAR, p_msg_text IN VARCHAR2);
PROCEDURE monitor_sessions;
END ADMIN_MONITOR;
---------------------------------------
Job - to run every 5 minutes
---------------------------------------
DECLARE
v_job_number NUMBER(10);
BEGIN
DBMS_JOB.SUBMIT (JOB => v_job_number,
WHAT => 'ADMIN_MONITOR.monitor_sessions;',
NEXT_DATE => SYSDATE + 5/1440,
INTERVAL => 'SYSDATE + 5/1440'
);
COMMIT;
END;
/
Sample output from the monitoring table:
COL run_date FOR A30
COL user_name FOR A30
SET LINESIZE 120
SET PAGESIZE 400
SQL> select * FROM MONITOR_SESSIONS ORDER BY seq_id desc, sessions desc;
SEQ_ID RUN_DATE USER_NAME SESSIONS
---------- ------------------------------ ------------------------------ ----------
3 20170308 17:03:27 AUT_TMOQQ_SPARX 152
3 20170308 17:03:27 AUT_TMOBI_SHARB 58
2 20170308 16:58:26 AUT_TMOQQ_SPARX 150
2 20170308 16:58:26 AUT_TMOBI_SHARB 57
2 20170308 16:58:26 AUT_MAXMO_IPNQQ 11
2 20170308 16:58:26 AUT_TMOBI_OVMDQ 10
2 20170308 16:58:26 SYSTEM 1
2 20170308 16:58:26 SYS 1
1 20170308 16:53:26 AUT_TMOQQ_SPARX 150
1 20170308 16:53:26 AUT_TMOBI_SHARB 57
1 20170308 16:53:26 AUT_MAXMO_IPNQQ 11
1 20170308 16:53:26 AUT_TMOBI_OVMDQ 10
===============================
Monitoring from Linux
===============================
COL user_name FOR A30
SET LINESIZE 120
SET PAGESIZE 400
SQL> select * FROM MONITOR_SESSIONS ORDER BY seq_id desc, sessions desc;
SEQ_ID RUN_DATE USER_NAME SESSIONS
---------- ------------------------------ ------------------------------ ----------
3 20170308 17:03:27 AUT_TMOQQ_SPARX 152
3 20170308 17:03:27 AUT_TMOBI_SHARB 58
3 20170308 17:03:27 AUT_MAXMO_IPNQQ 12
3 20170308 17:03:27 AUT_TMOBI_OVMDQ 10
3 20170308 17:03:27 SYSTEM 12 20170308 16:58:26 AUT_TMOQQ_SPARX 150
2 20170308 16:58:26 AUT_TMOBI_SHARB 57
2 20170308 16:58:26 AUT_MAXMO_IPNQQ 11
2 20170308 16:58:26 AUT_TMOBI_OVMDQ 10
2 20170308 16:58:26 SYSTEM 1
2 20170308 16:58:26 SYS 1
1 20170308 16:53:26 AUT_TMOQQ_SPARX 150
1 20170308 16:53:26 AUT_TMOBI_SHARB 57
1 20170308 16:53:26 AUT_MAXMO_IPNQQ 11
1 20170308 16:53:26 AUT_TMOBI_OVMDQ 10
1 20170308 16:53:26 SYSTEM 1
===============================
Monitoring from Linux
===============================
#/bin/bash
. /etc/sh/orash/oracle_login.sh igt
sqlplus / << EOF
select username, count(username) from v\$session group by
username having count(username)>10 order by 2;
select count(*) from v\$session;
exit
EOF
No comments:
Post a Comment