SMS Screens Doesn't Use Bind Variables when Querying Subscriber EDRs and Database Shared Pool is Overflow with Error "Ora-04031: Unable to Allocate Xxx Bytes of Shared Memory" (Doc ID 1440442.1)

Last updated on SEPTEMBER 14, 2016

Applies to:

Oracle Communications Network Charging and Control - Version 4.4.0 and later
Information in this document applies to any platform.
***Checked for relevance on 27-Oct-2013***

Symptoms

  1. PImanager, ccsProfileDaemon, smsMaster and ccsPeriodicCharge ...etc.
  2. Replication events were accumulated and not sent to the replication client nodes.
  3. Replication Jobs execution failed and were marked as broken.

On the impacted SMS node (These commands should be run from the SMS. If the SMS is a cluster, run these commands from only impacted node):

su - oracle
oracle@SMS>cd /u01/app/oracle/admin/SMF/bdump/
oracle@SMS>more alert_SMF.log

..........skipped.....................
Sun Mar 11 05:34:54 2012
Errors in file /u01/app/oracle/admin/SMF1/bdump/smf1_j001_16605.trc:
ORA-12012: error on auto execute of job 1081
ORA-04031: unable to allocate 576 bytes of shared memory ("shared pool","select t.ts#,t.file#,t.block...","sql area","ckydef : kkdlcky")
ORA-06512: at "SMF.REP_CHECK", line 28
ORA-06512: at line 1
Sun Mar 11 05:34:59 2012
Errors in file /u01/app/oracle/admin/SMF1/bdump/smf1_j000_16540.trc:
ORA-12012: error on auto execute of job 2736
ORA-04031: unable to allocate 40 bytes of shared memory ("shared pool","select t.ts#,t.file#,t.block...","Typecheck","chedef : qcuatc")
ORA-20011: RENUMBER_EVENTS: Error renumbering :-
ORA-06512: at "SMF.REP_INTERNAL", line 329
ORA-06512: at line 1

..........skipped.....................

su - oracle
oracle@SMS>sqlplus " / as sysdba"

SQL>col WHAT format a44;
SQL>col LOG_USER format a10
SQL>col interval format a40

SQL>select job,log_user,last_date, next_date,what,FAILURES,instance,broken from dba_jobs where WHAT like '%SMF.%';

JOB LOG_USER      NEXT_DATE                      WHAT                   FAILURES  INSTANCE BROKEN
---- ---------- ------------- ------------------------------------------ ------- ---------- ------
172 SYSTEM      40000101000000 SMF.REP_INTERNAL.FLUSH_REP_ORA_RENUMBERED; 17         1        Y
175 SYSTEM      40000101000000 SMF.REP_INTERNAL.RENUMBER_EVENTS;          17         2        Y
169 SYSTEM      40000101000000 SMF.REP_INTERNAL.FLUSH_REP_ORA_RENUMBERED; 17         2        Y
196 SYSTEM      40000101000000 SMF.JOBSTATUSCHECK;                        17         0        N
170 SYSTEM      40000101000000 SMF.REP_INTERNAL.RENUMBER_EVENTS;          17         1        Y
# su - smf_oper
# sqlplus smf/(password)
SQL> select count(*) from rep_ora_event;
COUNT(*)
--------
xxxx

SQL> select count(*) from rep_ora_renumbered;
COUNT(*)
--------
xxxx

 

Value of "xxxx" will keep increasing as the replication events are not sent to replication clients.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms