Skip to content

Posts from the ‘ERROR’ Category

ORA-16857: member disconnected from redo source for longer than specified threshold

Problem:

Data Guard configuration shows error at standby site

ORA-16857: member disconnected from redo source for longer than specified threshold

Solution:

Disable standby database in dgmgrl and Enable again. it solves problem after few minutes when log archive gap filled.

Demo:

-bash-4.2$ dgmgrl
DGMGRL> connect sys/a@db1
Connected to "db1"
Connected as SYSDBA.
DGMGRL> show configuration;

Configuration - dg_db1_config

  Protection Mode: MaxPerformance
  Members:
  db1    - Primary database
    Error: ORA-16778: redo transport error for one or more members

    stadby_db - Physical standby database
      Warning: ORA-16857: member disconnected from redo source for longer than specified threshold

Fast-Start Failover: DISABLED

Configuration Status:
ERROR   (status updated 2 seconds ago)

DGMGRL> show database db1

Database - db1

  Role:               PRIMARY
  Intended State:     TRANSPORT-ON
  Instance(s):
    db1_1
    db1_2
      Error: ORA-16737: the redo transport service for member "stadby_db" has an error

Database Status:
ERROR

DGMGRL> show database stadby_db

Database - stadby_db

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 175714 seconds ago)
  Apply Lag:          0 seconds (computed 175714 seconds ago)
  Average Apply Rate: 188.00 KByte/s
  Real Time Query:    OFF
  Instance(s):
    stadby_db_1 (apply instance)
    stadby_db_2

  Database Warning(s):
    ORA-16857: member disconnected from redo source for longer than specified threshold

Database Status:
WARNING

DGMGRL> disable database stadby_db;
Disabled.
DGMGRL> enable database stadby_db;
Enabled.
DGMGRL> show database stadby_db;

Database - stadby_db

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      1 day(s) 14 hours 47 minutes 10 seconds (computed 0 seconds ago)
  Apply Lag:          1 day(s) 18 hours 16 minutes 17 seconds (computed 0 seconds ago)
  Average Apply Rate: 189.00 KByte/s
  Real Time Query:    OFF
  Instance(s):
    stadby_db_1 (apply instance)
    stadby_db_2

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

Database Status:
WARNING


DGMGRL> show database stadby_db;

Database - stadby_db

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 0 seconds ago)
  Apply Lag:          0 seconds (computed 0 seconds ago)
  Average Apply Rate: 254.00 KByte/s
  Real Time Query:    OFF
  Instance(s):
    stadby_db_1 (apply instance)
    stadby_db_2

Database Status:
SUCCESS

DGMGRL> show configuration;

Configuration - dg_db1_config

  Protection Mode: MaxPerformance
  Members:
  db1    - Primary database
    stadby_db - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 52 seconds ago)

DGMGRL>

Oracle Database 19c Upgrade Error

Getting an error on Oracle Multimedia component after upgrade of database to 19c


Oracle Multimedia
ORA-04067: not executed, package body “MDSYS.MDPRVT_IDX” does not exist ORA-
06512: at line 206
ORA-06508: PL/SQL: could not find program unit being called:
“MDSYS.MDPRVT_IDX”

CAUSE

This behavior is the result of a software defect that Oracle Development has identified as Bug 29782284.
 

SOLUTION

Prior to the upgrade download and apply the patch for bug 29782284.

If the upgrade has been completed, run utlrp.sql and dba_registry will have Multimedia removed.


 

19c:SRVCTL status service fails with Java.lang.NullPointerException

Oracle Database – Enterprise Edition – Version 19.3.0.0.0 and later
Information in this document applies to any platform.

SYMPTOMS

The following errors occur while running srvctl status service command.

[oracle@<db_name> ~]$ srvctl status service -d <dbservice_name>
Exception in thread “main” java.lang.NullPointerException
at oracle.ops.opsctl.StatusAction.internalExecuteService(StatusAction.java:2038)
at oracle.ops.opsctl.StatusAction.executeSIHAService(StatusAction.java:1652)
at oracle.ops.opsctl.Action.executeSIHA(Action.java:443)
at oracle.ops.opsctl.OPSCTLDriver.execute(OPSCTLDriver.java:537)
at oracle.ops.opsctl.OPSCTLDriver.main(OPSCTLDriver.java:25) 

[oracle@<db_name> ~]$ srvctl status database -d <db_name>
Database is running.

[oracle@<db_name> ~]$ srvctl config database -d <db_name>
Database unique name: <db_name>
Database name: <db_name>
Oracle home: <rdbms_home>/product/12.2.0.3/db_1
Oracle user: oracle
Spfile: +<diskgroup_name>/<db_name>/spfile<dba_name>.ora
Password file:
Domain: <domain_name>
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Disk Groups: +<diskgroup_name1>,+<diskgroup_name2>
Services:<db_service>
OSDBA group:
OSOPER group:
Database instance: <db_name>

[oracle@<db_name> ~]$ srvctl config service -d <dbservice_name>
Service name:<dbservice_name>
Cardinality: SINGLETON
Service role: PRIMARY
Management policy: AUTOMATIC
DTP transaction: false
AQ HA notifications: false
Global: false
Commit Outcome: false
Failover type:
Failover method:
Failover retries:
Failover delay:
Failover restore: NONE
Connection Load Balancing Goal: LONG
Runtime Load Balancing Goal: NONE
TAF policy specification: NONE
Edition:
Pluggable database name:
Hub service:
Maximum lag time: ANY
SQL Translation Profile:
Retention: 86400 seconds
Replay Initiation Time: 300 seconds
Drain timeout:
Stop option:
Session State Consistency: DYNAMIC
GSM Flags: 0
Service is enabled
Service uses Java: false

 srvm.trc[main] [ 2019-05-22 11:17:58.926 EDT ] [EntityOperations.isEntityRegisteredCRSD:367] entity: ora.<db_name>.<service_name>.svc, type: 1, registered: true
[main] [ 2019-05-22 11:17:58.926 EDT ] [CRSCache.getAttributesFromCRS:155] CRS: name: ora.<db_name>.<service_name>.svc, type 1, node: null
[main] [ 2019-05-22 11:17:58.926 EDT ] [CRSCache.getAttributesFromCRS:156] attrs: [SERVER_POOLS, CARDINALITY, USR_ORA_DISCONNECT, ROLE, MANAGEMENT_POLICY, DTP, AQ_HA_NOTIFICATION, COMMIT_OUTCOME, FAILOVER_TYPE, FAILOVER_METHOD, FAILOVER_RETRIES, FAILOVER_RESTORE, TAF_FAILOVER_DELAY, CLB_GOAL, RLB_GOAL, GLOBAL, MAX_LAG_TIME, RETENTION, REPLAY_INITIATION_TIME, SESSION_STATE_CONSISTENCY, SERVICE_NAME_PQ, SERVICE_TYPE, GSM_FLAGS, TABLE_FAMILY_ID, DRAIN_TIMEOUT, STOP_OPTION, CSS_CRITICAL, TAF_POLICY, EDITION, PLUGGABLE_DATABASE, HUB_SERVICE, MAX_LAG_TIME, SQL_TRANSLATION_PROFILE, ACTIONS, ACTIVE_PLACEMENT]
[main] [ 2019-05-22 11:17:58.989 EDT ] [CRSCache.getAttributesFromCRS:163] CRS: [<ACTIONS:isJavaService,group:”dba”,user:”oracle” rf_action,group:”dba”,user:”oracle”>, <ACTIVE_PLACEMENT:0>, <AQ_HA_NOTIFICATION:0>, <CARDINALITY:1>, <CLB_GOAL:LONG>, <COMMIT_OUTCOME:0>, <CSS_CRITICAL:no>, <DRAIN_TIMEOUT:>, <DTP:0>, <EDITION:>, <FAILOVER_METHOD:>, <FAILOVER_RESTORE:NONE>, <FAILOVER_RETRIES:>, <FAILOVER_TYPE:>, <GLOBAL:false>, <GSM_FLAGS:0>, <HUB_SERVICE:>, <MANAGEMENT_POLICY:AUTOMATIC>, <MAX_LAG_TIME:ANY>, <PLUGGABLE_DATABASE:>, <REPLAY_INITIATION_TIME:300>, <RETENTION:86400>, <RLB_GOAL:NONE>, <ROLE:PRIMARY>, <SERVER_POOLS:>, <SERVICE_NAME_PQ:>, <SERVICE_TYPE:MAIN>, <SESSION_STATE_CONSISTENCY:DYNAMIC>, <SQL_TRANSLATION_PROFILE:>, <STOP_OPTION:>, <TABLE_FAMILY_ID:>, <TAF_FAILOVER_DELAY:>, <TAF_POLICY:NONE>, <USR_ORA_DISCONNECT:false>]
[main] [ 2019-05-22 11:17:58.990 EDT ] [ServiceArgs.setServiceType:230] set serviceType to MAIN
[main] [ 2019-05-22 11:17:58.990 EDT ] [ServiceArgs.setServiceType:230] set serviceType to MAIN
[main] [ 2019-05-22 11:17:58.990 EDT ] [CRSCache.getAttributesFromCRS:155] CRS: name: ora.<db_name>.<service_name>.svc, type 1, node: null
[main] [ 2019-05-22 11:17:58.990 EDT ] [CRSCache.getAttributesFromCRS:156] attrs: [SERVER_POOLS]
[main] [ 2019-05-22 11:17:59.007 EDT ] [CRSCache.getAttributesFromCRS:163] CRS: [<SERVER_POOLS:>]
[main] [ 2019-05-22 11:17:59.007 EDT ] [ServiceImpl.getServiceCardinality:3425] value of SERVER_POOLS attr :
[main] [ 2019-05-22 11:17:59.007 EDT ] [CRSCache.getAttributesFromCRS:155] CRS: name: ora.<db_name>.<service_name>.svc, type 1, node: null
[main] [ 2019-05-22 11:17:59.007 EDT ] [CRSCache.getAttributesFromCRS:156] attrs: [CARDINALITY]
[main] [ 2019-05-22 11:17:59.027 EDT ] [CRSCache.getAttributesFromCRS:163] CRS: [<CARDINALITY:1>]
[main] [ 2019-05-22 11:17:59.027 EDT ] [ServiceImpl.getServiceCardinality:3435] value of CARDINALITY attr : 1
[main] [ 2019-05-22 11:17:59.027 EDT ] [ServiceImpl.getServiceCardinality:3462] this is a SIHA service
[main] [ 2019-05-22 11:17:59.029 EDT ] [CRSCache.getAttributesFromCache:229] CRS cache: ora.<db_name>.db [<DATABASE_TYPE:SINGLE>]
[main] [ 2019-05-22 11:17:59.029 EDT ] [CRSCache.getAttributesFromCRS:155] CRS: name: ora.<db_name>.db, type 1, node: null
[main] [ 2019-05-22 11:17:59.029 EDT ] [CRSCache.getAttributesFromCRS:156] attrs: [HOSTING_MEMBERS]
[main] [ 2019-05-22 11:17:59.046 EDT ] [CRSCache.getAttributesFromCRS:163] CRS: [<HOSTING_MEMBERS:>]
[main] [ 2019-05-22 11:17:59.046 EDT ] [CRSCache.getAttributesFromCRS:155] CRS: name: ora.<db_name>.db, type 1, node: null
[main] [ 2019-05-22 11:17:59.046 EDT ] [CRSCache.getAttributesFromCRS:156] attrs: [SERVER_POOLS]
[main] [ 2019-05-22 11:17:59.068 EDT ] [CRSCache.getAttributesFromCRS:163] CRS: [<SERVER_POOLS:>]
Exception in thread “main”
java.lang.NullPointerException
at oracle.ops.opsctl.StatusAction.internalExecuteService(StatusAction.java:2038)
at oracle.ops.opsctl.StatusAction.executeSIHAService(StatusAction.java:1652)
at oracle.ops.opsctl.Action.executeSIHA(Action.java:443)
at oracle.ops.opsctl.OPSCTLDriver.execute(OPSCTLDriver.java:537)
at oracle.ops.opsctl.OPSCTLDriver.main(OPSCTLDriver.java:252)
[Thread-1] [ 2019-05-22 11:17:59.069 EDT ] [InnerInterruptHandler.run:116] Running the shutdown hook
[Thread-1] [ 2019-05-22 11:17:59.069 EDT ] [InnerInterruptHandler.run:120] Calling Interrupt handler….ctrlCHandler
[Thread-1] [ 2019-05-22 11:17:59.069 EDT ] [CommandLineParser.<init>:506] Is this a cluster? : false
[Thread-1] [ 2019-05-22 11:17:59.070 EDT ] [OPSCTLDriver.ctrlCHandler:306] Stopped srvctl execution
[Thread-1] [ 2019-05-22 11:17:59.070 EDT ] [OPSCTLDriver.ctrlCHandler:307] Invoking interrupt handler
[Thread-1] [ 2019-05-22 11:17:59.070 EDT ] [CmdOptVals.isOptionSet:1076] Calling isOptionSet for verbose
[Thread-1] [ 2019-05-22 11:17:59.070 EDT ] [OptVal.isSet:155] m_value is null
[Thread-1] [ 2019-05-22 11:17:59.070 EDT ] [SRVCTLHandler.getMethodName:122] getMethodName: Method Name = status_service_Handler
[Thread-1] [ 2019-05-22 11:17:59.070 EDT ] [SRVCTLHandler.invokeCtrlCHandler:230] Interrupt handler method status_service_Handler
[Thread-1] [ 2019-05-22 11:17:59.070 EDT ] [SRVCTLHandler.invokeCtrlCHandler:231] Interrupt handler class oracle.ops.opsctl.StatusAction
[Thread-1] [ 2019-05-22 11:17:59.071 EDT ] [SRVCTLHandler.invokeCtrlCHandler:244] oracle.ops.opsctl.StatusAction.status_service_Handler()
java.lang.Class.getMethod(Class.java:1786)
oracle.ops.opsctl.SRVCTLHandler.invokeCtrlCHandler(SRVCTLHandler.java:234)
oracle.ops.opsctl.OPSCTLDriver.ctrlCHandler(OPSCTLDriver.java:308)
sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
java.lang.reflect.Method.invoke(Method.java:498)
oracle.cluster.common.InnerInterruptHandler.run(InterruptHandler.java:123)
[Thread-1] [ 2019-05-22 11:17:59.086 EDT ] [OPSCTLDriver.ctrlCHandler:311] PRCG-1036 : Internal error.
oracle.ops.opsctl.SRVCTLHandler.invokeCtrlCHandler(SRVCTLHandler.java:245)
oracle.ops.opsctl.OPSCTLDriver.ctrlCHandler(OPSCTLDriver.java:308)
sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
java.lang.reflect.Method.invoke(Method.java:498)
oracle.cluster.common.InnerInterruptHandler.run(InterruptHandler.java:123)

CAUSE

Due to bug 29839229
 

SOLUTION

As workaround,

srvctl status service -d <dbname> -s <service_name>

Bug 29839229 fixed in a 20.1 version, request/apply patch 29839229 for your platform/version

Remote_listener Getting Reset To Blank After Cluster Startup

APPLIES TO:

Oracle Database – Enterprise Edition – Version 12.2.0.1 to 19.3.0.0.0 [Release 12.2 to 18]
Information in this document applies to any platform.

SYMPTOMS

users get error while connecting to database

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor


remote_listener value got reset to blank after db node patching on all databases of the cluster causing databases not able to connect from outside, and databases are not getting registered with scan_listeners.
crsd_oraagent_oracle.trc inside <oracle base>/diag/crs/<HOSTNAME>/crs/trace location indicate following messages2019-05-11 19:41:49.764 : USRTHRD:3059689216: {2:49138:41584} LsnrRegThd::setRemoteListener 391 tmpSet endp:<scan name>:<PORT>
2019-05-11 19:41:49.764 : USRTHRD:3059689216: {2:49138:41584} LsnrRegThd::setRemoteListener 440 skip remote listener registration m_runRegRLUpdate:0 isForceRemoteListenerSet:0 isRemoteListenerSet:1
2019-05-11 19:41:49.774 : USRTHRD:3059689216: {2:49138:41584} ORA-01405: fetched column value is NULL

CHANGES

CAUSE

This is due to unpublished Bug 29637215 – FAEOPS: SHELL DB DOESN’T HAVE REMOTE_LISTENER VALUE WITH GRID 19.1.0.3
 

SOLUTION

Bug 29637215 fixed in future release. Apply interim fix 29637215, if available for your platform and Oracle version.

If no patch exists for your version, please contact Oracle Support for a backport request

As a workaround,

Manually update the remote_listener values every time bounce the instances from this cluster.

alter system set remote_listener=’racscan.com:1521′ scope=both sid=’*’;


 

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor after cluster upgrade to 19c

Problem

users get error while connecting to database

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Cause:

Cluster upgrade from 12.2 to 19c

Solution:

set remote_listener parameter

alter system set remote_listener=’racscan.com:1521′ scope=both sid=’*’;

ORA-39123 ORA-29349 during Transportable Tablespace

Description:

Error occurred while doing import in transportable table space mode because tablespace was already exists from previous import

impdp system@pdbsvc full=y directory=example_dir logfile=transport_example.log network_link=example_dblink transport_datafiles=’+ORADATA1/pdbsvc/A380B9279457244CE0530589020A9EE4/DATAFILE/prod_space.6991.1041528629′

Import: Release 12.2.0.1.0 – Production on Fri May 29 13:22:27 2020

Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
Source time zone is -05:00 and target time zone is +00:00.
Estimate in progress using BLOCKS method…
Processing object type DATABASE_EXPORT/PLUGTS_FULL/FULL/PLUGTS_TABLESPACE
Processing object type DATABASE_EXPORT/PLUGTS_FULL/PLUGTS_BLK
ORA-39123: Data Pump transportable tablespace job aborted
ORA-29349: tablespace ‘prod_space’ already exists

Job “SYSTEM”.”SYS_IMPORT_FULL_01″ stopped due to fatal error at Fri May 29 13:24:07 2020 elapsed 0 00:01:33

Solution:

Drop Tablespace with content and datafile

restore and recover tablespace

import again

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>

%d bloggers like this: