18 Using Procedural Replication

Oracle GoldenGate procedural replication allows you to replicate Oracle Database supplied PL/SQL procedures avoiding the shipping and applying or high volume records usually generated by these operations.

Procedural replication implements dictionary changes that control user and session behavior and the swapping of objects in dictionary.

Procedural replication is not related to the replication of the CREATE, ALTER, and DROP statements (or DDL), rather it is the replication of the procedure call like:

CALL procedure_name(arg1, arg2, ...);

As opposed to:

exec procedure_name(arg1, arg2, ...)

After you enable procedural replication, calls to procedures in Oracle Database supplied packages at one database are replicated to one or more other databases and then executed at those databases. For example, a call to subprograms in the DBMS_REDEFINITION package can perform an online redefinition of a table. If the table is replicated at several databases, and if you want the same online redefinition to be performed on the table at each database, then you can make the calls to the subprograms in the DBMS_REDEFINITION package at one database, and Oracle GoldenGate can replicate those calls to the other databases.

To support procedural replication, your Oracle Database should be configured to identify procedures that are enabled for this optimization.

To use procedural replication, the following prerequisites must be met:

• Oracle GoldenGate 12.3 with integrated Extract and Replicat.

• System supplied packages are only working in combination with DML and DDL.

Procedural replication uses a trail record to ensure that sufficient information is encapsulated with the record.

To use Oracle GoldenGate procedural replication, you need to enable it. Your Oracle Database must have a built in mechanism to identify the procedures that are enabled for this optimization.

PL/SQL pragmas are used to indicate which procedures can be replicated. When the pragma is specified, a callback is made to Logminer on entry and exit from the routine. The callback provides the name of the procedure call and arguments and indicates if the procedure exited successfully or with an error. Logminer augments the redo stream with the information from the callbacks. For supported procedures, the normal redo generated by the procedure is suppressed, and only the procedure call is replicated.

A new trail record is generated to identify procedural replication. This trail record leverages existing trail column data format for arguments passed to PL/SQL procedures. For LOBs, data is passed in chunks similar to existing trail format for LOBs. This trail record has sufficient information to replay the procedure as-is on the target.

When you enable procedural replication, it prevents writing of individual records impacted by the procedure to the trail file.

If an error is encountered when applying a PL/SQL procedure, the Replicat can replay the entire PL/SQL procedure.

Procedural replication is disabled by default. You can enable it by setting the TRANLOGOPTIONS option, ENABLE_PROCEDURAL_REPLICATION, to yes.

1. Ensure that you are in triggerless mode, see Prerequisites for Configuring DDL.
2. Connect to the source database as an Oracle GoldenGate administrator with dblogin.
3. Set the TRANLOGOPTIONS parameter option to yes.

   TRANLOGOPTIONS INTEGRATEDPARAMS (ENABLE_PROCEDURAL_REPLICATION Y)

Use the GG_PROCEDURE_REPLICATION_ON function in the DBMS_GOLDENGATE_ADM package to determine whether Oracle GoldenGate procedural replication is on or off.

1. Connect to the database as sys (sqlplus, sqlcl, sqldeveloper) not as an Oracle GoldenGate administrator.
2. Run the GG_PROCEDURE_REPLICATION_ON function.

SET SERVEROUTPUT ON
DECLARE
  on_or_off   NUMBER;
BEGIN
  on_or_off := DBMS_GOLDENGATE_ADM.GG_PROCEDURE_REPLICATION_ON;
  IF on_or_off=1 THEN
    DBMS_OUTPUT.PUT_LINE('Oracle GoldenGate procedural replication is ON.');
  ELSE
    DBMS_OUTPUT.PUT_LINE('Oracle GoldenGate procedural replication is OFF.');
  END IF;
END;
/

Oracle GoldenGate provides GGSCI commands to allow you to enable or disable procedural supplemental logging.

You can specify which procedures and packages you want to include or exclude for procedure replication.

You group supported packages and procedures using feature groups. You use the procedure parameter with the INCLUDE or EXCLUDE keyword to filter features for procedure replication.

In the procedure parameter, INCLUDE or EXCLUDE specify the beginning of a filtering clause. They specify the procedures to replicate (INCLUDE) or filter out (EXCLUDE). The filtering clause must consist of the INCLUDE ALL_SUPPORTED or EXCLUDE ALL_SUPPORTED keyword followed by any valid combination of the other filtering options of the procedure parameter. The EXCLUDE filter takes precedence over any INCLUDE filters that contain the same criteria.

Including all system supplied packages at Extract:

1. Connect to Extract in the source database.

   EXTRACT edba

   USERIDALIAS admin_dbA DOMAIN ORADEV

2. Create a new trail file.

   EXTTRAIL ea

3. Enable procedure replication, if not already done.

   TRANLOGOPTIONS INTEGRATEDPARAMS (ENABLE_PROCEDURAL_REPLICATION Y)

4. Include filter for procedure replication.

   PROCEDURE INCLUDE FEATURE ALL_SUPPORTED

You have successfully included all system supplied packages for procedure replication.

Excluding specific packages at Replicat:

1. Connect to Replicat in the target database.

   REPLICAT rdba

   USERIDALIAS admin_dbBDOMAIN ORADEV

2. Include filter for procedure replication.

   PROCEDURE EXCLUDE FEATURE RLS

You have successfully excluded specific packages for procedure replication.

Procedural replication uses REPERROR parameter to configure the behavior of Replicat when an procedural error occurs.

By default, Replicat will abend when a procedural replication occurs so using the following steps sets up error handling:

1. Connect to Replicat in the target database.

   REPLICAT rdba

   USERIDALIAS admin_dbBDOMAIN ORADEV

2. Include filter for procedure replication.

   PROCEDURE EXCLUDE FEATURE RLS

3. Specify error handling parameter, see REPERROR in Reference for Oracle GoldenGate for other options.

   REPERROR (PROCEDURE, DISCARD)

You have successfully handled errors for procedural replication.

There are four pragma options for procedures: AUTO, MANUAL, UNSUPPORTED, and NONE.

PL/SQL enter and exit markers are logged for procedures with pragmas AUTO, MANUAL, and UNSUPPORTED. The redo logs generated between the enter and exit markers are grouped and discarded.

Following is a list of the packages and procedures that are pragma constructs for replication. Any package or procedure not in this list is not considered a pragma construct for PL/SQL replication and is equivalent to pragma NONE.