Using Procedural Replication

About Procedural Replication

Procedural replication is available with Oracle database only. Oracle GoldenGate uses procedural replication to replicate Oracle Database supplied PL/SQL procedures avoiding the shipping and applying of 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 a 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 with Extract and Replicat.
System supplied packages are only working in combination with DML and DDL.

Procedural Replication Process Overview

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, Replicat can replay the entire PL/SQL procedure.

Determining Whether Procedural Replication Is On

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

If you want to use Oracle GoldenGate in an Oracle Database Vault environment with procedural replication, then you must set the appropriate privileges. See Oracle Database Vault Administrator’s Guide.

To enable procedural replication:

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

Example 11-18 Running 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;
/

Enabling and Disabling Supplemental Logging

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

To enable supplemental logging:

Connect to the source database as the Oracle GoldenGate administrator with DBLOGIN.

CONNECT https://localhost:9000 DEPLOYMENT demo AS ggadmin PASSWORD adminpw

DBLOGIN USERIDALIAS ggeast DOMAIN OracleGoldenGate

Add supplemental logging for procedural replication.

ADD PROCEDURETRANDATA

The output shows:

INFO OGG-13005 PROCEDURETRANDATA supplemental logging has been enabled.

Supplemental logging is enabled for procedure replication.

To disable supplemental logging:

Connect to the source database as the Oracle GoldenGate administrator with dblogin.

CONNECT https://localhost:9000 DEPLOYMENT demo AS ggadmin PASSWORD adminpw

DBLOGIN USERIDALIAS ggeast DOMAIN OracleGoldenGate

Remove supplemental logging for procedure replication.
```
DELETE PROCEDURETRANDATA
```

Supplemental logging is disabled for procedure replication.

To view information about supplemental logging:

Connect to the source database as the Oracle GoldenGate administrator with dblogin.

CONNECT https://localhost:9000 DEPLOYMENT demo AS ggadmin PASSWORD adminpw

DBLOGIN USERIDALIAS ggeast DOMAIN OracleGoldenGate

Display supplemental logging information for procedure replication.
```
INFO PROCEDURETRANDATA
```

Supplemental logging information for procedure replication is displayed.

Filtering Features for Procedural Replication

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.

Note:

When replicating Oracle Streams Advanced Queuing (AQ) procedures, you must use the RULE option in your parameter file as follows:

PROCEDURE INCLUDE FEATURE ALL_SUPPORTED

or

PROCEDURE INCLUDE FEATURE AQ, RULE

Do not use PROCEDURE INCLUDE FEATURE AQ without the RULE option.

Including all system supplied packages at Extract:

Connect to Extract in the source database.

EXTRACT edba

USERIDALIAS admin_dbA DOMAIN ORADEV
Create a new trail file.

EXTTRAIL ea
Enable procedure replication, if not already done.

TRANLOGOPTIONS INTEGRATEDPARAMS (ENABLE_PROCEDURAL_REPLICATION Y)
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:

Connect to Replicat in the target database.

REPLICAT rdba

USERIDALIAS admin_dbBDOMAIN ORADEV
Include filter for procedure replication.

PROCEDURE EXCLUDE FEATURE RLS

You have successfully excluded specific packages for procedure replication.

Handling Procedural Replication Errors

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:

Connect to Replicat in the target database.

REPLICAT rdba

USERIDALIAS admin_dbBDOMAIN ORADEV
Include filter for procedure replication.

PROCEDURE EXCLUDE FEATURE RLS
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.

Listing the Procedures Supported for Oracle GoldenGate Procedural Replication

The DBA_GG_SUPPORTED_PROCEDURES view displays information about the supported packages for Oracle GoldenGate procedural replication.

When a procedure is supported and Oracle GoldenGate procedural replication is on, calls to the procedure are replicated, unless the procedure is excluded specifically.

Connect to the database as sys (sqlplus, sqlcl, sqldeveloper) not as an Oracle GoldenGate administrator.
Query the DBA_GG_SUPPORTED_PROCEDURES view.

Example 11-19 Displaying Information About the Packages Supported for Oracle GoldenGate Procedural Replication

This query displays the following information about the packages:

The owner of each package
The name of each package
The name of each procedure
The minimum database release from which the procedure is supported
Whether there is an exclusion rule that prevents the procedure from being replicated for some database objects

COLUMN OWNER FORMAT A10
COLUMN PACKAGE_NAME FORMAT A15
COLUMN PROCEDURE_NAME FORMAT A15
COLUMN MIN_DB_VERSION FORMAT A14
COLUMN EXCLUSION_RULE_EXISTS FORMAT A14

SELECT OWNER,
       PACKAGE_NAME,
       PROCEDURE_NAME,
       MIN_DB_VERSION,
       EXCLUSION_RULE_EXISTS
  FROM DBA_GG_SUPPORTED_PROCEDURES;

Your output looks similar to the following:

OWNER      PACKAGE_NAME    PROCEDURE_NAME  MIN_DB_VERSION EXCLUSION_RULE
---------- --------------- --------------- -------------- --------------
XDB        DBMS_XDB_CONFIG ADDTRUSTMAPPING 12.2           NO
CTXSYS     CTX_DDL         ALTER_INDEX     12.2           NO
SYS        DBMS_FGA        DROP_POLICY     12.2           NO
SYS        XS_ACL          DELETE_ACL      12.2           NO
.
.
.

Monitoring Oracle GoldenGate Procedural Replication

A set of data dictionary views enable you to monitor Oracle GoldenGate procedural replication.

You can use the following views to monitor Oracle GoldenGate procedural replication:

View Description

View	Description
`DBA_GG_SUPPORTED_PACKAGES`	Provides details about supported packages for Oracle GoldenGate procedural replication. When a package is supported and Oracle GoldenGate procedural replication is on, calls to subprograms in the package are replicated.
`DBA_GG_SUPPORTED_PROCEDURES`	Provides details about the procedures that are supported for Oracle GoldenGate procedural replication.
`DBA_GG_PROC_OBJECT_EXCLUSION`	Provides details about all database objects that are on the exclusion list for Oracle GoldenGate procedural replication. A database object is added to the exclusion list using the `INSERT_PROCREP_EXCLUSION_OBJ` procedure in the `DBMS_GOLDENGATE_ADM` package. When a database object is on the exclusion list, execution of a subprogram n the package is not replicated if the subprogram operates on the excluded object.

DBA_GG_SUPPORTED_PACKAGES

Provides details about supported packages for Oracle GoldenGate procedural replication.

When a package is supported and Oracle GoldenGate procedural replication is on, calls to subprograms in the package are replicated.

DBA_GG_SUPPORTED_PROCEDURES

Provides details about the procedures that are supported for Oracle GoldenGate procedural replication.

DBA_GG_PROC_OBJECT_EXCLUSION

Provides details about all database objects that are on the exclusion list for Oracle GoldenGate procedural replication.

A database object is added to the exclusion list using the INSERT_PROCREP_EXCLUSION_OBJ procedure in the DBMS_GOLDENGATE_ADM package. When a database object is on the exclusion list, execution of a subprogram n the package is not replicated if the subprogram operates on the excluded object.

Connect to the database as sys (sqlplus, sqlcl, or sqldeveloper) not as an Oracle GoldenGate administrator.
Query the views related to Oracle GoldenGate procedural replication.