In this blog post, we will show you how to set up Oracle Goldengate Procedural Replication for High Availability.
Please follow the below steps.

************************************************
Initial/ Basic Configuration steps – ON SOURCE
************************************************

1) On Source – Added minimum supplemental logging
2) On Source – Create user REMOTEUSER identified by REMOTEUSER ;
GRANT DBA , UNLIMITED TABLESPACE TO REMOTEUSER;
Create user GGS identified by GGS ;
GRANT DBA , UNLIMITED TABLESPACE TO GGS;
3) On Source – Enabled Archivelog mode
4) On Source – Set ENABLE_GOLDENGATE_REPLICATION=TRUE set it in spfile as well
SQL> alter system set enable_goldengate_replication=true scope=both;

System altered.

5) On Source – changed the parameters to below

SQL> alter system set SGA_TARGET=10G scope=spfile;
System altered.

SQL> alter system set streams_pool_size=2G scope=spfile;
System altered.

5.1) On Source – add schematrandata REMOTEUSER.*,ALLCOLS

GGSCI (rac2.1place1cloud.com) 8> add schematrandata REMOTEUSER,allcols
2018-07-02 18:11:28 INFO OGG-01788 SCHEMATRANDATA has been added on schema “REMOTEUSER”.
2018-07-02 18:11:28 INFO OGG-01976 SCHEMATRANDATA for scheduling columns has been added on schema “REMOTEUSER”.
2018-07-02 18:11:28 INFO OGG-01977 SCHEMATRANDATA for all columns has been added on schema “REMOTEUSER”.

6) On Source – dblogin userid *** , password ****
info procedure trandata
add procedure trandata

GGSCI (rac2.oracloudintegrators.com) 27> info proceduretrandata
2018-07-02 19:12:01 INFO OGG-13008 Procedure level supplemental logging is disabled.
GGSCI (rac2.oracloudintegrators.com) 28> ADD proceduretrandata
2018-07-02 19:14:39 INFO OGG-13005 PROCEDURETRANDATA supplemental logging has been enabled.

*******************************************
Check if Procedure replication is ON
*******************************************

SET SERVEROUTPUT ON
SQL> DECLARE
SQL> 2 on_or_off NUMBER;
3 BEGIN
4 on_or_off := DBMS_GOLDENGATE_ADM.GG_PROCEDURE_REPLICATION_ON;
5 IF on_or_off=1 THEN
6 DBMS_OUTPUT.PUT_LINE(‘Oracle GoldenGate procedural replication is ON.’);
7 ELSE
DBMS_OUTPUT.PUT_LINE(‘Oracle GoldenGate procedural replication is OFF.’);
8 9 END IF;
10 END;
11
12 /
Oracle GoldenGate procedural replication is ON.
PL/SQL procedure successfully completed.

7) On Source – Register Extract E6 database

GGSCI (rac2.oracloudintegrators.com) 3> register extract e7 database

2018-07-02 18:40:53 INFO OGG-02003 Extract E6 successfully registered with database at SCN 1435923.

—->
Wait for a few seconds

GGSCI (rac2.oracloudintegrators.com) 12> dblogin USERID ggs PASSWORD ggs
Successfully logged into database.

GGSCI (rac2.oracloudintegrators.com) 13> add extract e7, integrated tranlog , begin now
EXTRACT (Integrated) added.

GGSCI (rac2.oracloudintegrators.com) 14> add rmttrail ./dirdat/e7 , extract e7
RMTTRAIL added.

*****************************
Our extract parameter file looks like below
*****************************

GGSCI (rac2.oracloudintegrators.com) 46> view params e6

extract e6
USERID ggs PASSWORD ggs
rmthost rac2.oracloudintegrators.com , mgrport 7889
rmttrail ./dirdat/e6
DDL INCLUDE MAPPED
TRANLOGOPTIONS EXCLUDETAG 00 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< dblogin userid ggs, password ggs
Successfully logged into database.

GGSCI (rac1.oracloudintegrators.com) 2> add schematrandata remoteuser,allcols

2018-07-02 18:12:10 INFO OGG-01788 SCHEMATRANDATA has been added on schema “REMOTEUSER”.

2018-07-02 18:12:10 INFO OGG-01976 SCHEMATRANDATA for scheduling columns has been added on schema “REMOTEUSER”.

2018-07-02 18:12:10 INFO OGG-01977 SCHEMATRANDATA for all columns has been added on schema “REMOTEUSER”.

***************************
Added the Replicat
***************************

GGSCI (rac1.oracloudintegrators.com) 13> dblogin userid ggs, password ggs
Successfully logged into database.

GGSCI (rac1.oracloudintegrators.com) 14> add replicat r6 , integrated , exttrail ./dirdat/e6
REPLICAT (Integrated) added.

My replicat parameter file looked liked below

GGSCI (rac1.oracloudintegrators.com) 2> edit params r6

replicat r6
USERID ggs PASSWORD ggs
DDL INCLUDE ALL
MAP REMOTEUSER.*, target REMOTEUSER.*;

************************************************************************
End of Basic Configuration Completed on source and target
************************************************************************

Now on source
========================
SQL> conn REMOTEUSER/REMOTEUSER
Connected.

SQL> CREATE OR REPLACE TYPE REMOTEUSER.COLLEGE_TYPE as object(
subject varchar2(30),
text varchar2(80)); 2 3
4 /

Type created.

Create queue table first
===========
SQL> EXEC dbms_aqadm.create_queue_table(queue_table => ‘REMOTEUSER.COLLEGE_QUEUE_TABLE’, –
> queue_payload_type => ‘REMOTEUSER.COLLEGE_TYPE’, –
storage_clause => ‘PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 TABLESPACE USERS’, –
> > Sort_list => ‘ENQ_TIME’, –
> Multiple_consumers => TRUE, –
> Compatible => ‘10.0.0’, –
> replication_mode => DBMS_AQADM.REPLICATION_MODE);

PL/SQL procedure successfully completed.

Check if queue table has been created on the target.

SQL> select OWNER,QUEUE_TABLE,OBJECT_TYPE from dba_queue_tables where OWNER=’REMOTEUSER’;

OWNER QUEUE_TABLE OBJECT_TYPE
———————- ——————————— ———————————
REMOTEUSER COLLEGE_QUEUE_TABLE REMOTEUSER.COLLEGE_TYPE

Create queue
===========

SQL> EXEC DBMS_AQADM.CREATE_QUEUE( –
Queue_name => ‘REMOTEUSER.COLLEGE_QUEUE’, –
> > Queue_table => ‘REMOTEUSER.COLLEGE_QUEUE_TABLE’, –
> Queue_type => 0, –
> Max_retries => 5, –
> Retry_delay => 0, –
> dependency_tracking => FALSE);

PL/SQL procedure successfully completed.

Create Agents
================
SQL>
DECLARE
V_agent sys.aq$_agent;
BEGIN
V_agent:= sys.aq$_agent(‘Agent1′,NULL,NULL);
DBMS_AQADM.ADD_SUBSCRIBER(queue_name=>’REMOTEUSER.COLLEGE_QUEUE’
,subscriber=>v_agent);
END;SQL> 2 3 4 5 6 7
8
9 /

PL/SQL procedure successfully completed.

SQL> DECLARE
V_agent sys.aq$_agent;
BEGIN
V_agent:= sys.aq$_agent(‘Agent2′,NULL,NULL);
DBMS_AQADM.ADD_SUBSCRIBER(queue_name=>’REMOTEUSER.COLLEGE_QUEUE’
,subscriber=>v_agent);
END; 2 3 4 5 6 7
8 /

PL/SQL procedure successfully completed.

*************************
Start the queue on the source.
*************************

SQL> exec DBMS_AQADM.START_QUEUE(‘REMOTEUSER.COLLEGE_QUEUE’);

PL/SQL procedure successfully completed.

Enqueue messages on the source

SQL> DECLARE
v_enqueue_options dbms_aq.enqueue_options_t;
2 3 v_message_properties dbms_aq.message_properties_t;
4 v_message_handle raw(16);
5 v_message REMOTEUSER.COLLEGE_TYPE ;
6 BEGIN
7 FOR i in 1..2 LOOP
8 v_message := REMOTEUSER.COLLEGE_TYPE(i,i);
9 dbms_aq.enqueue(
10 queue_name => ‘REMOTEUSER.COLLEGE_QUEUE’,
11 enqueue_options => v_enqueue_options,
12 message_properties => v_message_properties,
13 payload => v_message,
14 msgid => v_message_handle);
15 commit;
16 END LOOP;
17 END;
18 /

PL/SQL procedure successfully completed.

We checked if all the messages have been queued on target and it was queued.

SQL> select Q_NAME,MSGID,STATE,EXPIRATION,ENQ_TIME,DEQ_TIME,ENQ_UID from REMOTEUSER.COLLEGE_QUEUE_TABLE;

Q_NAME MSGID STATE EXPIRATION ENQ_TIME
———————- ———————- ———- ———- —————————–
DEQ_TIME ENQ_UID
———————- ———————————
COLLEGE_QUEUE 700995B8CB096DBEE05355 0 04-JUL-18 08.18.42.366872 PM
08DC0A2780
REMOTEUSER

COLLEGE_QUEUE 700995B8CB086DBEE05355 0 04-JUL-18 08.18.42.363900 PM
08DC0A2780
REMOTEUSER

*************************************
Try the Dequeue now
***********************************

SQL> set serveroutput on size 99999
SQL>
SQL> DECLARE
2 dequeue_options dbms_aq.DEqueue_options_t;
3 v_message_properties dbms_aq.message_properties_t;
4 v_message_handle raw(16);
5 v_message REMOTEUSER.COLLEGE_TYPE ;
no_messages exception;
6 7 pragma exception_init (no_messages, -25228);
BEGIN
8 9 for agent_number in 1..2 loop
10 BEGIN
11 dequeue_options.wait := DBMS_AQ.NO_WAIT;
12 dequeue_options.consumer_name := ‘Agent’||agent_number;
dequeue_options.navigation := dbms_aq.FIRST_MESSAGE;
13 14 LOOP
15 dbms_aq.DEqueue(
queue_name => ‘REMOTEUSER.COLLEGE_QUEUE’,
16 17 DEqueue_options => dequeue_options,
18 message_properties => v_message_properties,
19 payload => v_message,
20 msgid => v_message_handle);
21 DBMS_OUTPUT.PUT_LINE (‘Message: ‘ || v_message.subject || ‘ … ‘ || v_message.text );
22 dequeue_options.navigation := dbms_aq.NEXT_MESSAGE;
23 END LOOP;
24 commit;
25 DBMS_OUTPUT.PUT_LINE (‘No more messages for Agent’||agent_number);
EXCEPTION
26 27 WHEN no_messages THEN
28 DBMS_OUTPUT.PUT_LINE (‘No more messages for Agents ‘);
29 COMMIT;
30 END;
31 END LOOP;
32 END;
/
33 Message: 1 … 1
Message: 2 … 2
No more messages for Agents
Message: 1 … 1
Message: 2 … 2
No more messages for Agents

PL/SQL procedure successfully completed.

SQL> select * from REMOTEUSER.COLLEGE_QUEUE_TABLE;

no rows selected