Error ORA-00020 in alert log and trace file
* Get the complete information helps to analyse, here you go - One place report to start with the ORA-00020 investigation
===============================
Check in Database
===============================
SELECT *
FROM V$RESOURCE_LIMIT
WHERE resource_name in ('sessions','processes','transactions');
RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_ALLOCATION LIMIT_VALUE
-------------------- ------------------- --------------- -------------------- -----------
processes 289 296 300 300
sessions 286 319 335 335
transactions 4294965549 4294967295 368 UNLIMITED
COL MACHINE FOR A 30
SELECT MACHINE, USERNAME, PROGRAM, COUNT(*)
FROM V$SESSION
GROUP BY MACHINE, USERNAME, PROGRAM
HAVING count(*) > 5
ORDER BY COUNT(*) DESC;
----------------- ------------------- ---------------------------------------- ---------
MACHINE USERNAME PROGRAM COUNT(*)
----------------- ------------------- ---------------------------------------- ---------
akb-ext-ops-1 SRL_AKDAF_SPARX JDBC Thin Client 37
akb-sod-ops-1 AFF_CODAC_SPARX JDBC Thin Client 32
.
.
.
session detail :
select inst_id,sid,username,sql_id,to_char(logon_time,'dd/mm/yy hh24:mi:ss') as logon_time,program,machine,status from gv$session where username is not null and username !='SYS' order by 1;
select sesion.sid,
sesion.username,
optimizer_mode,
hash_value,
address,
cpu_time,
elapsed_time,
sql_text
from v$sqlarea sqlarea, v$session sesion
where sesion.sql_hash_value = sqlarea.hash_value
and sesion.sql_address = sqlarea.address
and sesion.username is not null
/
===============================
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.
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
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 AKB_TMOQQ_USERA 149
20170308 16:57:10 AKB_TMOBI_USERB 57
20170308 16:57:10 AKB_MAXMO_USERC 11
20170308 16:57:10 AKB_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 AKB_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 AKB_TMOQQ_SPARX 152
3 20170308 17:03:27 AKB_TMOBI_SHARB 58
3 20170308 17:03:27 AKB_MAXMO_IPNQQ 12
3 20170308 17:03:27 AKB_TMOBI_OVMDQ 10
3 20170308 17:03:27 SYSTEM 1
2 20170308 16:58:26 AKB_TMOQQ_SPARX 150
2 20170308 16:58:26 AKB_TMOBI_SHARB 57
2 20170308 16:58:26 AKB_MAXMO_IPNQQ 11
2 20170308 16:58:26 AKB_TMOBI_OVMDQ 10
2 20170308 16:58:26 SYSTEM 1
2 20170308 16:58:26 SYS 1
1 20170308 16:53:26 AKB_TMOQQ_SPARX 150
1 20170308 16:53:26 AKB_TMOBI_SHARB 57
1 20170308 16:53:26 AKB_MAXMO_IPNQQ 11
1 20170308 16:53:26 AKB_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
* Get the complete information helps to analyse, here you go - One place report to start with the ORA-00020 investigation
===============================
Check in Database
===============================
SELECT *
FROM V$RESOURCE_LIMIT
WHERE resource_name in ('sessions','processes','transactions');
RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_ALLOCATION LIMIT_VALUE
-------------------- ------------------- --------------- -------------------- -----------
processes 289 296 300 300
sessions 286 319 335 335
transactions 4294965549 4294967295 368 UNLIMITED
COL MACHINE FOR A 30
SELECT MACHINE, USERNAME, PROGRAM, COUNT(*)
FROM V$SESSION
GROUP BY MACHINE, USERNAME, PROGRAM
HAVING count(*) > 5
ORDER BY COUNT(*) DESC;
----------------- ------------------- ---------------------------------------- ---------
MACHINE USERNAME PROGRAM COUNT(*)
----------------- ------------------- ---------------------------------------- ---------
akb-ext-ops-1 SRL_AKDAF_SPARX JDBC Thin Client 37
akb-sod-ops-1 AFF_CODAC_SPARX JDBC Thin Client 32
.
.
.
session detail :
select inst_id,sid,username,sql_id,to_char(logon_time,'dd/mm/yy hh24:mi:ss') as logon_time,program,machine,status from gv$session where username is not null and username !='SYS' order by 1;
select sesion.sid,
sesion.username,
optimizer_mode,
hash_value,
address,
cpu_time,
elapsed_time,
sql_text
from v$sqlarea sqlarea, v$session sesion
where sesion.sql_hash_value = sqlarea.hash_value
and sesion.sql_address = sqlarea.address
and sesion.username is not null
/
===============================
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.
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
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 AKB_TMOQQ_USERA 149
20170308 16:57:10 AKB_TMOBI_USERB 57
20170308 16:57:10 AKB_MAXMO_USERC 11
20170308 16:57:10 AKB_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 AKB_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 AKB_TMOQQ_SPARX 152
3 20170308 17:03:27 AKB_TMOBI_SHARB 58
3 20170308 17:03:27 AKB_MAXMO_IPNQQ 12
3 20170308 17:03:27 AKB_TMOBI_OVMDQ 10
3 20170308 17:03:27 SYSTEM 1
2 20170308 16:58:26 AKB_TMOQQ_SPARX 150
2 20170308 16:58:26 AKB_TMOBI_SHARB 57
2 20170308 16:58:26 AKB_MAXMO_IPNQQ 11
2 20170308 16:58:26 AKB_TMOBI_OVMDQ 10
2 20170308 16:58:26 SYSTEM 1
2 20170308 16:58:26 SYS 1
1 20170308 16:53:26 AKB_TMOQQ_SPARX 150
1 20170308 16:53:26 AKB_TMOBI_SHARB 57
1 20170308 16:53:26 AKB_MAXMO_IPNQQ 11
1 20170308 16:53:26 AKB_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