Updates Are Not Sent and Accumulated in Replication Tables with Reported Error "ORA-12012, PLS-00904" in Oracle Alert Logs

(Doc ID 1390937.1)

Last updated on JULY 26, 2017

Applies to:

Oracle Communications Network Charging and Control - Version 3.1.0 and later
Information in this document applies to any platform.
***Checked for relevance on 25-Jul-2014***

Symptoms

Replication updates are accumulating in the replication tables on the SMS (Service Management System) and are not sent to the replication clients, the SLCs (Service Logic Controllers) and the VWSs (Voucher and Wallet Servers).

Things to check:

1- Replication Tables:

Table Description :

rep_ora_event (Stores the new incoming replication events , waiting to be processed by their results)
rep_pending_queue (Contains a reference to the last event_id sent to every replication node)
rep_ora_renumbered (Contains the renumbered replication updates to be sent to the replication nodes)

Node_ID : Represent the ID of the replication node like (SLC,VWS). Table rep_cfn_node contains details information about the replication client's nodes.
ROE_EVENTID : Represent the last event_id was sent successfully to the replication node.

For more information about "Replication Table", please refer to SMS Technical Guide / Chapter 2/Section: Replication Overview.  See <Document 1341844.1> for how to download documentation.


These commands should be run from the SMS. If the SMS is a cluster, run these commands from any of the nodes in the cluster:

# 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.

 

set lines 75 pages 1000

COL NodeDescr FORMAT a20
COL NodeRepID FORMAT 9999999999
COL PrimIP FORMAT a13
COL SecIP FORMAT a12
COL EventID FORMAT 99999999

SELECT
TRIM(rcn.description) as Node_Name,
rcn.node_number as Node_RepID,
rpq.roe_eventid as Event_ID
FROM
rep_cnf_node rcn,
rep_pending_queue rpq
WHERE
rcn.node_number = rpq.node_id(+);

Node_Name    Node_RepID  Event_ID
----------- ----------- ------------
SLC          301         1644572
VWS01        351         1644563
VWS02        352         1644563

 

Values of column "EVENT_ID" will remain the same with no change.

2- Oracle alert logs:

By Checking oracle alert logs, the following error will be generated :

on both SMS nodes:

su - oracle

bash-3.00$cd /u01/app/oracle/admin/SMF1/bdump/
bash-3.00$less alert_SMF1.log
----skipped------

Errors in file /u01/app/oracle/admin/SMF1/bdump/smf1_j000_27313.trc:
ORA-12012: error on auto execute of job 172
ORA-06550: line 1, column 100:
PLS-00904: insufficient privilege to access object SMF.REP_INTERNAL
ORA-06550: line 1, column 96:
PL/SQL: Statement ignored

Errors in file /u01/app/oracle/admin/SMF1/bdump/smf1_j000_27313.trc:
ORA-12012: error on auto execute of job 170
ORA-06550: line 1, column 100:
PLS-00904: insufficient privilege to access object SMF.REP_INTERNAL
ORA-06550: line 1, column 96:
PL/SQL: Statement ignored
Wed Dec 28 10:08:15 2011
Thread 1 advanced to log sequence 1417 (LGWR switch)

 

Please note in SMS Cluster environment , Oracle alert logs are written separately into 2 file (each per instance) and can be found under:

/u01/app/oracle/admin/SMF1/bdump/alert_SMF1.log
/u01/app/oracle/admin/SMF2/bdump/alert_SMF2.log

Changes

The following or similar changes may be the cause of this problem:

1-Any manual modification of the Oracle Database replication JOBs during the initial setup or during any maintenance or optimization.
2-The installation of any patch modifying these Oracle Database JOBs, or require to manually modify these JOBs as a post-installation task.

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