Replication Packages/Triggers Missing at new site : ORA-04067: stored procedure OBJECT_NAME$RP does not exist

I built a 6 node cluster using notes at http://www.oracledbasupport.co.uk/adding-a-new-master-node-in-an-existing-multi-master-cluster/

Everything was going OK but then noticed some tables are returning errors like

ORA-04067: not executed, stored procedure “SYSTEM.VOUCHER_NO$RP” does not exist
ORA-01085: preceding errors in deferred rpc to “SYSTEM.VOUCHER_NO$RP.REP_INSERT”
ORA-02063: preceding 2 lines from REPAA

Solution : Run following SQL to locate which tables are missing the much needed replication packages which should be generated as a part of GENERATE_REPLICATION_SUPPORT API. A replication support was pushed by Master Defn site when we add a new master/new objects into existing replication group.

— List if replication packages are available at Master as well as remote node
COLUMN group HEADING ‘Replication_Group’ FORMAT A20;
COLUMN count(*) HEADING ‘NUMBER_OF_TABLES’ FORMAT 9999999;
select gname Replication_Group,count(*) NUMBER_OF_TABLES
from REPCAT$_REPOBJECT where type= 2 group by gname order by 1;

REPLICATION_GROUP NUMBER_OF_TABLES
—————————— —————-
O1_A-B_ALL 14< BR> O1_ERROR_A-B 7

Above SQL will return how many objects have been replicated. Let’s make sure this is consistent with the remote site.

repA> select object_name from dba_objects where object_name like ‘%$R%’
minus
select object_name from dba_objects@repAA where object_name like ‘%$R%’;

OBJECT_NAME
——————————————————————————–
ACCOUNT$RP
OFFER$RP
OFFER$RP
OFFER$RP
SERVICE$RP
SUBSCRIBE$RP
SUBSCRIBE$RP
TRANSACTION$RP

8 rows selected, so 8 objects are missing replication support.

I can also use “select sname,oname,type,STATUS from DBA_REPOBJECT minus
select sname,oname,type,STATUS from DBA_REPOBJECT<at>remote_db;”

What does $RP package contain?
This package have following procedures
– rep_delete
– rep_insert
– rep_update

Let’s generate the packages missing at remote repAA site

At Master Defn Site > BEGIN
Master> DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY (gname => ‘O1_BROKER_A-B’);
END;

Master> BEGIN
DBMS_REPCAT.generate_replication_package (sname => ‘BROKER_SYSTEM’,
oname =>’&1′);
END;

This went fine and did generate required packages. Above SQL returned no differences this time, still replication status was in “ERROR” at EM console

Next I tried generating a complete replication support as below.

Master/Remote DB> analyze table broker_system.account_status validate structure;

Table analyzed.
Master> BEGIN
dbms_repcat.generate_replication_support (
sname => ‘BROKER_SYSTEM’,
oname => ‘ACCOUNT_STATUS’,
type => ‘TABLE’,
distributed => TRUE);
END;
/
BEGIN
*
ERROR at line 1:
ORA-23308: object BROKER_SYSTEM.ACCOUNT_STATUS does not exist or is invalid
ORA-06512: at “SYS.DBMS_SYS_ERROR”, line 95
ORA-06512: at “SYS.DBMS_REPCAT_UTL”, line 2874

I also followed oracle Note:176913.1 and compared every stats on these tables. These databases were copied as offline backup and had exact mirror image of each other. I am using 3 sites A,AA,AAA and above error was reported from remote system AAA. A and AA were running OK.

At last I used following procedure to get them VALID

Master repA> BEGIN
DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY (gname => ‘O1_BROKER_A-B’);
END;

Master repA> EXECUTE Dbms_Repcat.Drop_Master_Repobject(‘BROKER_SYSTEM’, ‘OFFER_CATEGORY’,’TABLE’);
Master repA> EXECUTE Dbms_Repcat.Drop_Master_Repobject(‘BROKER_SYSTEM’, ‘OFFER_STATUS’, ‘TABLE’);
Master repA> EXECUTE Dbms_Repcat.Drop_Master_Repobject(‘BROKER_SYSTEM’, ‘OFFER_SUB_CATEGORY’, ‘TABLE’);
Master repA> EXECUTE Dbms_Repcat.Drop_Master_Repobject(‘BROKER_SYSTEM’, ‘SERVICE_PROVIDER’, ‘TABLE’);
Master repA> EXECUTE Dbms_Repcat.Drop_Master_Repobject(‘BROKER_SYSTEM’, ‘SUBSCRIBER_OFFER’, ‘TABLE’);
Master repA> EXECUTE Dbms_Repcat.Drop_Master_Repobject(‘BROKER_SYSTEM’, ‘SUBSCRIBER_PLAN’, ‘TABLE’);
Master repA> EXECUTE Dbms_Repcat.Drop_Master_Repobject(‘BROKER_SYSTEM’, ‘TRANSACTION_TYPE’, ‘TABLE’);

Disable all Foreign key constraints accessing these tables else truncate would fail with error “ORA-02266: unique/primary keys in table referenced by enabled foreign key”.

repAA/repAAA>truncate table OFFER_CATEGORY;
repAA/repAAA>truncate table OFFER_STATUS ;
repAA/repAAA>truncate table OFFER_SUB_CATEGORY ;
repAA/repAAA>truncate table SERVICE_PROVIDER ;
repAA/repAAA>truncate table SUBSCRIBER_OFFER ;
repAA/repAAA>truncate table SUBSCRIBER_PLAN ;
repAA/repAAA>truncate table TRANSACTION_TYPE ;

Master repA> BEGIN
DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
gname => ‘O1_BROKER_A-B’,
type => ‘TABLE’,
oname => ‘&1’,
sname => ‘BROKER_SYSTEM’,
use_existing_object => TRUE,
copy_rows => TRUE);
END;
/
Enter value for 1: OFFER_CATEGORY
old 4: oname => ‘&1’,
new 4: oname => ‘OFFER_CATEGORY’,
Run above API for all tables

BEGIN
DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
sname => ‘BROKER_SYSTEM’,
oname => ‘&1’,
type => ‘TABLE’,
min_communication => TRUE);
END;
/
Enter value for 1: OFFER_CATEGORY
old 4: oname => ‘&1’,
new 4: oname => ‘OFFER_CATEGORY’,
Run above API for all tables

Run DBMS_JOB dbms_repcat.do_deferred_repcat_admin(‘”O1_BROKER_A-B”‘, FALSE); to propogate the ADMINISTRATIVE requests. I often found Oracle EM doesn’t work well so run this job manually at all sites.