Skip to content

Posts tagged ‘ERROR’

How To Flush A Sql Statement From Shared Pool

Problem:

Multiple session was hanging on sqlid that was executing very well one day ago.

Solution:

Flush the single sql id rather than whole shared pool

  • Get the address and hash_value of the sql_id:
    select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_Id=’3jktm6bykn79w’;
  • Now purge the sql statement using sys
  • exec DBMS_SHARED_POOL.PURGE (‘ADDRESS,HASH_VALUE’,’C’);

exec DBMS_SHARED_POOL.PURGE (‘0700010156DED3F0,4247395644′,’C’);

PL/SQL procedure successfully completed.

select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_Id=’3jktm6bykn79w’;

no rows selected

ORA-16810: multiple errors or warnings detected for the member / ORA-16766: Redo Apply is stopped / ORA-16853: apply lag has exceeded specified threshold

Error:

ORA-16810: multiple errors or warnings detected for the member  / ORA-16766: Redo Apply is stopped / ORA-16853: apply lag has exceeded specified threshold

Cause: Redo apply stopped on standby

Solution: Disable and Enable standby in dgmgrl

Example:

DGMGRL> show configuration

Configuration – dg_prmy_config

Protection Mode: MaxPerformance
Members:
prmy – Primary database
stby – Physical standby database
Error: ORA-16810: multiple errors or warnings detected for the member

Fast-Start Failover: DISABLED

Configuration Status:
ERROR (status updated 51 seconds ago)

DGMGRL>
DGMGRL> show database stby

Database – stby

Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 1 second ago)
Apply Lag: 13 hours 58 minutes 18 seconds (computed 1 second ago)
Average Apply Rate: 1.06 MByte/s
Real Time Query: OFF
Instance(s):
stby_1 (apply instance)
stby_2

Database Error(s):
ORA-16766: Redo Apply is stopped

Database Warning(s):
ORA-16853: apply lag has exceeded specified threshold

Database Status:
ERROR

DGMGRL> disable database stby
Disabled.
DGMGRL>
DGMGRL> enable database stby
Enabled.
DGMGRL>
DGMGRL>
DGMGRL> show configuration;

Configuration – dg_prmy_config

Protection Mode: MaxPerformance
Members:
prmy – Primary database
stby – Physical standby database
Warning: ORA-16853: apply lag has exceeded specified threshold

Fast-Start Failover: DISABLED

Configuration Status:
WARNING (status updated 13 seconds ago)

DGMGRL>

DGMGRL>
DGMGRL> show database stby

Database – stby

Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: 1 second (computed 0 seconds ago)
Average Apply Rate: 24.06 MByte/s
Real Time Query: OFF
Instance(s):
stby_1 (apply instance)
stby_2

Database Status:
SUCCESS

DGMGRL>
DGMGRL>
DGMGRL> show configuration;

Configuration – dg_prmy_config

Protection Mode: MaxPerformance
Members:
prmy – Primary database
stby – Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS (status updated 30 seconds ago)

DGMGRL>

 

ORA-00023 ORA-00024 ORA-00025

ORA-00023: session references process private memory; cannot detach session

Cause: An attempt was made to detach the current session when it contains references to process private memory.

Action: A session may contain references to process memory (PGA) if it has an open network connection, a very large context area, or operating system privileges. To allow the detach, it may be necessary to close the session’s database links and/or cursors. Detaching a session with operating system privileges is always disallowed.

ORA-00024: logins from more than one process not allowed in single-process mode

Cause: Trying to login more than once from different processes for ORACLE started in single-process mode.

Action: Logoff from the other process.

ORA-00025: failed to allocate string

Cause: Out of memory.

Action: Restart with larger sga heap.

 

 

Cause: Out of memory.

Action: Restart with larger sga heap.

 

 

ORA-00021: session attached to some other process; cannot switch session

ORA-00021: session attached to some other process; cannot switch session

Cause: The user session is currently used by others.
Action: Do not switch to a session attached to some other process.

 

 

ORA-00020 reported in -MGMTDB alert log

SYMPTOMS

-MGMTDB is not releasing the database connection, causing it to run out of processes. -MGMTDB is the management database used for storing CHM (Cluster Health Monitor) data in 12c.

alert_-MGMTDB.log shows:

Sun Nov 09 22:55:03 2014
CJQ0 started with pid=33, OS id=36397
Completed: ALTER DATABASE OPEN /* db agent *//* {1:34217:2109} */

Sun Nov 09 17:03:59 2014
ORA-00020: maximum number of processes (300) 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 m000 submission failed with error = 20

ps -ef | grep MGMTDB shows many processes:

oracle 29338 1 0 20:12 ? 00:00:00 oracle-MGMTDB (LOCAL=NO)
oracle 29342 1 0 20:12 ? 00:00:00 oracle-MGMTDB (LOCAL=NO)
oracle 29542 1 0 20:13 ? 00:00:00 oracle-MGMTDB (LOCAL=NO)
oracle 29546 1 0 20:13 ? 00:00:00 oracle-MGMTDB (LOCAL=NO)
oracle 29757 1 0 20:14 ? 00:00:00 oracle-MGMTDB (LOCAL=NO)
oracle 29761 1 0 20:14 ? 00:00:00 oracle-MGMTDB (LOCAL=NO)
oracle 29978 1 0 20:15 ? 00:00:00 oracle-MGMTDB (LOCAL=NO)
oracle 29984 1 0 20:15 ? 00:00:00 oracle-MGMTDB (LOCAL=NO)
oracle 30272 1 0 20:16 ? 00:00:00 oracle-MGMTDB (LOCAL=NO)
oracle 30276 1 0 20:16 ? 00:00:00 oracle-MGMTDB (LOCAL=NO)
oracle 30494 1 0 20:17 ? 00:00:00 oracle-MGMTDB (LOCAL=NO)
oracle 30498 1 0 20:17 ? 00:00:00 oracle-MGMTDB (LOCAL=NO)
oracle 30691 1 0 20:18 ? 00:00:00 oracle-MGMTDB (LOCAL=NO)

Shutdown and restart the -MGMTDB does not help, it soon reports ORA-20 again.

CAUSE

In MGMTDB, the user CHM’s password is expired.

select username, account_status, profile from dba_users where username=’CHM’;
USERNAME              ACCOUNT_STATUS      PROFILE
——————–  ——————- ————
CHM                   EXPIRED(GRACE)      DEFAULT
select * from dba_profiles where profile=’DEFAULT’ and resource_name=’PASSWORD_LIFE_TIME’;

PROFILE        RESOURCE_NAME           RESOURCE      LIMIT     COM
————– ———————– ————- ——— —–
DEFAULT        PASSWORD_LIFE_TIME      PASSWORD      180       NO

The user CHM’s password is expired after 180 day from DB creation. This causes the user account is locked and connection can not be made through.

SOLUTION

Please use the following procedure to reset the CHM user password to its original value.

as grid user,

$ export ORACLE_HOME=<GRID_HOME>
$ export ORACLE_SID=-MGMTDB
$ sqlplus / as sysdba
SQL> select name, password from user$ where name=’CHM’;
SQL> alter user CHM identified by values ‘<the password returned from the above query>’;

SQL> select username, account_status from dba_users where username=’CHM’;

It should show account_status as OPEN now and ORA-20 error should be stopped.

How to Connect After ORA-00020 has Occured

SYMPTOMS

 

After an ORA-20 “maximum number of processes (%s) exceeded” occurs … no new connections (even SYSDBA or SYSOPER) may be made to the instance

CHANGES

 

CAUSE

An ORA-20 “maximum number of processes (%s) exceeded” occurs when the number of OS processes for the instance exceeds the PROCESSES parameter

This is related to unpublished Bug 8518197: LOGINS NOT POSSIBLE AFTER ORA-20
and unpublished Bug 8518126: LOGINS NOT POSSIBLE AFTER ORA-20

SOLUTION

There are a couple of ‘workarounds’ for this issue … but no real solution at this time (an enhancement reqeust has been filed)

WORKAROUNDS:

UNIX: kill -9 one or more of the client connections

WINDOWS : using ORAKILL … kill one or more of the client connections

* Use an existing connection with sufficient privileges (if one is logged on) to view V$SESSION / V$PROCESS and
alter system kill session ‘SID, SERAL#’;

CASE STUDY

— GET A STABLE NUMBER OF PROCESSES AGAINST WHICH TO RUN THE TEST

SQL*Plus: Release 11.1.0.6.0 – Production on Thu May 14 04:44:37 2009

Copyright (c) 1982, 2007, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> STARTUP
ORACLE instance started.

Total System Global Area 631914496 bytes
Fixed Size 1301756 bytes
Variable Size 331350788 bytes
Database Buffers 293601280 bytes
Redo Buffers 5660672 bytes
Database mounted.
Database opened.

SQL> show parameter processes

NAME TYPE VALUE
———————————— ———– ——————————
aq_tm_processes integer 0
db_writer_processes integer 1
gcs_server_processes integer 0
global_txn_processes integer 1
job_queue_processes integer 5
log_archive_max_processes integer 4
processes integer 150

SQL> select count(*) from v$process;

COUNT(*)
———-
24

— SET PROCESSES TO COUNT + 1
SQL> alter system set processes = 25 scope = spfile;

System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 631914496 bytes
Fixed Size 1301756 bytes
Variable Size 331350788 bytes
Database Buffers 293601280 bytes
Redo Buffers 5660672 bytes
Database mounted.
Database opened.

SQL> show parameter processes

NAME TYPE VALUE
———————————— ———– ——————————
aq_tm_processes integer 0
db_writer_processes integer 1
gcs_server_processes integer 0
global_txn_processes integer 1
job_queue_processes integer 5
log_archive_max_processes integer 4
processes integer 25

SQL> select count(*) from v$process;

COUNT(*)
———-
23

— CONNECT WITH A NEW SESSION

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select count(*) from v$process;

COUNT(*)
———-
24

— This brings our session count up to PROCESSES -1

 

— ATTEMPT TO CONNECT WITH A 3RD SESSION

$ sqlplus / as sysdba

SQL*Plus: Release 11.1.0.6.0 – Production on Thu May 14 04:50:48 2009

Copyright (c) 1982, 2007, Oracle. All rights reserved.

ERROR:
ORA-00020: maximum number of processes (%s) exceeded

$ sqlplus / as sysoper

SQL*Plus: Release 11.1.0.6.0 – Production on Thu May 14 04:51:43 2009

Copyright (c) 1982, 2007, Oracle. All rights reserved.

ERROR:
ORA-00020: maximum number of processes (%s) exceeded

[oracle@joshowar-lnx ~]$ sqlplus -prelim / as sysdba

SQL*Plus: Release 11.1.0.6.0 – Production on Thu May 14 04:55:26 2009

Copyright (c) 1982, 2007, Oracle. All rights reserved.

SQL> select count(*) from v$process;
select count(*) from v$process
*
ERROR at line 1:
ORA-01012: not logged on
Process ID: 0
Session ID: 0 Serial number: 0

SQL> EXIT

 

— RESOLVE THE CONDITION

$ ps -ef | grep KBCOOK

oracle 17555 1 0 04:48 ? 00:00:00 ora_pmon_KBCOOK
oracle 17557 1 0 04:48 ? 00:00:00 ora_vktm_KBCOOK
oracle 17561 1 0 04:48 ? 00:00:00 ora_diag_KBCOOK
oracle 17563 1 0 04:48 ? 00:00:00 ora_dbrm_KBCOOK
oracle 17565 1 0 04:48 ? 00:00:00 ora_psp0_KBCOOK
oracle 17569 1 0 04:48 ? 00:00:00 ora_dia0_KBCOOK
oracle 17571 1 0 04:48 ? 00:00:00 ora_mman_KBCOOK
oracle 17573 1 0 04:48 ? 00:00:00 ora_dbw0_KBCOOK
oracle 17575 1 0 04:48 ? 00:00:00 ora_lgwr_KBCOOK
oracle 17577 1 0 04:48 ? 00:00:00 ora_ckpt_KBCOOK
oracle 17579 1 0 04:48 ? 00:00:00 ora_smon_KBCOOK
oracle 17581 1 0 04:48 ? 00:00:00 ora_reco_KBCOOK
oracle 17583 1 0 04:48 ? 00:00:00 ora_mmon_KBCOOK
oracle 17585 1 0 04:48 ? 00:00:00 ora_mmnl_KBCOOK
oracle 17587 1 0 04:48 ? 00:00:00 ora_d000_KBCOOK
oracle 17589 1 0 04:48 ? 00:00:00 ora_s000_KBCOOK
oracle 17608 1 0 04:48 ? 00:00:00 ora_smco_KBCOOK
oracle 17610 1 0 04:48 ? 00:00:00 ora_fbda_KBCOOK
oracle 17612 1 0 04:48 ? 00:00:00 ora_qmnc_KBCOOK
oracle 17641 1 0 04:48 ? 00:00:00 ora_q000_KBCOOK
oracle 17671 1 0 04:49 ? 00:00:00 ora_q001_KBCOOK
oracle 17761 1 0 04:50 ? 00:00:00 oracleKBCOOK (LOCAL=NO)
oracle 18480 1 0 04:58 ? 00:00:00 ora_w000_KBCOOK
oracle 18608 17126 0 05:00 pts/1 00:00:00 grep KBCOOK

$ kill -9 17761

$ ps -ef | grep KBCOOK

oracle 17555 1 0 04:48 ? 00:00:00 ora_pmon_KBCOOK
oracle 17557 1 0 04:48 ? 00:00:00 ora_vktm_KBCOOK
oracle 17561 1 0 04:48 ? 00:00:00 ora_diag_KBCOOK
oracle 17563 1 0 04:48 ? 00:00:00 ora_dbrm_KBCOOK
oracle 17565 1 0 04:48 ? 00:00:00 ora_psp0_KBCOOK
oracle 17569 1 0 04:48 ? 00:00:00 ora_dia0_KBCOOK
oracle 17571 1 0 04:48 ? 00:00:00 ora_mman_KBCOOK
oracle 17573 1 0 04:48 ? 00:00:00 ora_dbw0_KBCOOK
oracle 17575 1 0 04:48 ? 00:00:00 ora_lgwr_KBCOOK
oracle 17577 1 0 04:48 ? 00:00:00 ora_ckpt_KBCOOK
oracle 17579 1 0 04:48 ? 00:00:00 ora_smon_KBCOOK
oracle 17581 1 0 04:48 ? 00:00:00 ora_reco_KBCOOK
oracle 17583 1 0 04:48 ? 00:00:00 ora_mmon_KBCOOK
oracle 17585 1 0 04:48 ? 00:00:00 ora_mmnl_KBCOOK
oracle 17587 1 0 04:48 ? 00:00:00 ora_d000_KBCOOK
oracle 17589 1 0 04:48 ? 00:00:00 ora_s000_KBCOOK
oracle 17608 1 0 04:48 ? 00:00:00 ora_smco_KBCOOK
oracle 17610 1 0 04:48 ? 00:00:00 ora_fbda_KBCOOK
oracle 17612 1 0 04:48 ? 00:00:00 ora_qmnc_KBCOOK
oracle 17641 1 0 04:48 ? 00:00:00 ora_q000_KBCOOK
oracle 17671 1 0 04:49 ? 00:00:00 ora_q001_KBCOOK
oracle 18480 1 0 04:58 ? 00:00:00 ora_w000_KBCOOK

$ sqlplus / as sysdba

SQL*Plus: Release 11.1.0.6.0 – Production on Thu May 14 05:01:48 2009

Copyright (c) 1982, 2007, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

ORA-00020 and ASM

SYMPTOMS

An ASM instance complains of

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

CAUSE

The Oracle parameter PROCESSES has been exceeded as a result of multiple database instances connecting to the ASM instance

The default value (often 40) is insufficient to support more than one database connecting to the ASM instance

SOLUTION

Increase the PROCESSES parameter in the ASM parameter file

Processes = 25 + 15n, where n is the number of instances on the box using ASM for their storage.

NOTE : this formula is for a basic instance and does not accommodate for

* Multiple ARCH processes
* Multiple LGWR processes

Should the ORA-20 occur even after implementing this formula … add additional for any multiples of these background processes

More details about this calculation are available in the NOTE 265633.1 “ASM Technical Best Practices”

For 11.2 set up, the formula is  PROCESSES = 50 + 50*n

where n is the number database instances connecting to the Oracle ASM instance.

This is in the standard document

https://docs.oracle.com/cd/E11882_01/server.112/e18951/asminst.htm#OSTMG94080

 

For  12c set up, the formulas are

For n < 10, PROCESSES = 50*n + 50

For n >= 10, PROCESSES = 10*n + 450

This is in the standard document

https://docs.oracle.com/database/121/OSTMG/GUID-3C096558-27B8-4EAF-A61C-892CBDD7E7F7.htm

 

ORA-00019: maximum number of session licenses exceeded

ORA-00019: maximum number of session licenses exceeded

Cause: All licenses are in use.
Action: Increase the value of the LICENSE MAX SESSIONS initialization parameter.

 

 

elated: 	<<View:V$LICENSE>>
                <<Parameter:LICENSE_SESSIONS_WARNING>>
                <<Parameter:LICENSE_MAX_USERS>>
        
  Users received ORA-19 if they are not allowed to connect.
  Eg:  ORA-19 "maximum number of session licenses exceeded"
        
  Messages are written to the alert log for each connect attempt made once 
  LICENSE_MAX_SESSIONS is reached.
  Eg: If the user has RESTRICTED SESSION privilege:
             "License maximum (10) exceeded, DBA logon allowed"

      If the user was refused from connecting:
             "Non-DBA logon denied; current logons equal maximum (10)"

 

Troubleshooting Guide – ORA-00018: Maximum Number Of Sessions (%S) Exceeded

SYMPTOMS

You receive the following errors when trying to make new connections:

ORA-00604: error occurred at recursive SQL level 1
Cause: An error occurred while processing a recursive SQL statement.
(a statement applying to internal dictionary tables).
Action: If the situation described in the next message on the stack can be corrected, do so; otherwise, contact customer support.

ORA-00018: maximum number of sessions exceeded
Cause: An operation requested a resource that was unavailable.
The maximum number of sessions is specified by the initialization parameter SESSIONS.
When this maximum is reached, no more requests are processed.
Action: Try the operation again in a few minutes.
If this message occurs often, shut down Oracle, increase the SESSIONS parameter in the initialization parameter file, and restart Oracle.

NOTE: No new connections are permitted to the database.

However when querying v$resource_limit it shows this is not true.

SQL> select * from v$resource_limit;

RESOURCE_NAME      CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_AL LIMIT_VALU
------------------ ------------------- --------------- ---------- ----------
processes                          538             596       1365       1365
sessions                           651             744       4096       4096

 

show parameter session;

NAME                              TYPE        VALUE
--------------------------------- --------  -------
java_max_sessionspace_size        integer         0
java_soft_sessionspace_limit      integer         0
license_max_sessions              integer         0
license_sessions_warning          integer         0
logmnr_max_persistent_sessions    integer         1
session_cached_cursors            integer        20
session_max_open_files            integer        10
sessions                          integer      4096
shared_server_sessions            integer       100

At the time of the error:

Select count(*) from v$session;

COUNT(*)
----------
587

select count(*) from x$ksuse where bitand(ksspaflg,1) !=0 ;

COUNT(*)
----------
618 

As seen the current number of running sessions is no where near the settings for the sessions parameter yet the following error is still reported:
ORA-18 MAXIMUM NUMBER OF SESSIONS EXCEEDED

CAUSE

Cause#1:
The maximum number of sessions in the database have been exceeded

 

Cause#2:
This issue has been addressed in the following bug that has been closed as not a bug:
Bug:11825730 – ORA-18 MAXIMUM NUMBER OF SESSIONS EXCEEDED

Development explained that the problem is due to shared server being enabled and the shared_server_sessions is set to a low value. Verified by the output from the event 18 trace file:

038001B3A4 kmmsgism = 4096 max instance sessions
038001B3A8 kmmsgmsm = 100 max shared server sessions
038001B3AC kmmsgnsm = 100 current # of shared server sessions

SOLUTION

Solution#1:

Increase the value of the SESSIONS parameter in the parameter file.

If using spfile, then do the following:

SQL> alter system set sessions=<new value> scope=spfile;
SQL> shutdown immediate
SQL> startup

-or-
Increase the value of PROCESSES since SESSIONS is derived off of the PROCESSES parameter.
SESSIONS:      derived (1.1 * PROCESSES) + 5 (versions prior to 11.2)
SESSIONS:      derived (1.8 * PROCESSES) + 22 (11.2+)

SESSIONS specifies the total number of user and system sessions. The default number is greater than PROCESSES to allow for recursive sessions. Recursive sessions are not reflected in the v$session view. Recommendation is to preserve 50% of the SESSIONS value for recursive sessions.

Solution#2:

Limit the number of sessions per user  using resource limit profiles or resource manager. Refer  Limit Maxmimum Concurrent Sessions on Database Excluding Background Processes

Solution#3:
1. Increase the value for the init parameter shared_server_sessions
– OR –
2. Disable shared server

By default when shared server is configured, the connection made to the DB will be shared connections unless explicitly specified in the tnsnames.ora file that the service is using dedicated connections. Therefore if you do not intend to use shared server connections please disable shared server to allow for dedicated connections as follows. You disable shared server by setting SHARED_SERVERS to 0. No new client can connect in shared mode. However, when you set SHARED_SERVERS to 0, Oracle Database retains some shared servers until all shared server connections are closed. The number of shared servers retained is either the number specified by the preceding setting of
SHARED_SERVERS or the value of the MAX_SHARED_SERVERS parameter, whichever is smaller. If both SHARED_SERVERS and MAX_SHARED_SERVERS are set to 0, then all shared servers will terminate and requests from remaining shared server clients will be queued until the value of SHARED_SERVERS or MAX_SHARED_SERVERS is raised again.

After you disable shared server, all connections will be dedicated by default.

Solution#4:
Check if there is any user or application creating excessive sessions. Contact the application vendor in order to check why there are so many opened sessions (possibly sessions leak at the application level).

ORA-00017: session requested to set trace event

ORA-00017: session requested to set trace event

Cause: The current session was requested to set a trace event by another session.
Action: This is used internally; no action is required.

 

 

%d bloggers like this: