Oracle | DBMS_GOLDENGATE

Oracle DBMS_GOLDENGATE_ADM Version 21c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

Purpose Provides interfaces to configure automatic conflict detection and resolution in an Oracle GoldenGate configuration that replicates tables between Oracle databases. When more than one replica of a table allows changes to the table, a conflict can occur when a change is made to the same row in two different databases at nearly the same time. Oracle GoldenGate replicates changes using row logical change records (LCRs). It detects a conflict by comparing the old values in the row LCR with the current values of the corresponding table row identified by the key columns. If any column value does not match, then there is a conflict. After a conflict is detected, Oracle GoldenGate can resolve the conflict by overwriting values in the row with some values from the row LCR, ignoring the values in the row LCR, or computing a delta to update the row values.

AUTHID CURRENT_USER

Constants

Name	Data Type	Value
use_custom_handlers_none	BINARY_INTEGER	0
use_custom_handlers_all	BINARY_INTEGER	1

Dependencies

ALL_GG_AUTO_CDR_TABLES	DBMS_GOLDENGATE_ADM_INT_IVOK	DBMS_SYS_ERROR
ALL_TAB_IDENTITY_COLS	DBMS_ISCHED	DBMS_UTILITY
DBMS_APPLY_ADM	DBMS_LOGREP_UTIL	V$DATABASE
DBMS_ASSERT	DBMS_STANDARD	V$PARAMETER
DBMS_GOLDENGATE_ADM_INTERNAL

Documented Yes: Packages and Types Reference

First Available 12.2

Security Model Owned by SYS with EXECUTE granted to the EXECUTE_CATALOG_ROLE role and GGSYS.

Security on this package can be controlled either by granting EXECUTE on this package to selected users or roles or by granting EXECUTE_CATALOG_ROLE to selected users or roles.

If subprograms in the package are run from within a stored procedure, then the user who runs the subprograms must be granted EXECUTE privilege on the package directly. It cannot be granted through a role. An Oracle GoldenGate administrator must be configured at each Oracle database in the table�s replication environment, and Oracle GoldenGate must be configured to replicate the table at each Oracle database. You can configure an Oracle GoldenGate administrator using the GRANT_ADMIN_PRIVILEGE procedure in the DBMS_GOLDENGATE_ADM package.

Source {ORACLE_HOME}/rdbms/admin/dbmsgg.sql

Subprograms

ADD_AUTO_CDR	DELETE_PROCREP_EXCLUSION_OBJ	REMOVE_AUTO_CDR
ADD_AUTO_CDR_COLUMN_GROUP	GG_PROCEDURE_REPLICATION_ON	REMOVE_AUTO_CDR_COLUMN_GROUP
ADD_AUTO_CDR_DELTA_RES	INSERT_PROCREP_EXCLUSION_OBJ	REMOVE_AUTO_CDR_DELTA_RES
ALTER_AUTO_CDR	PURGE_TOMBSTONES	UPDATE_IDENTITY_COLUMN_HWM
ALTER_AUTO_CDR_COLUMN_GROUP

ADD_AUTO_CDR

Configure GoldenGate automatic conflict detection and resolution at the table level dbms_goldengate_adm.add_auto_cdr( schema_name IN VARCHAR2, table_name IN VARCHAR2, resolution_granularity IN VARCHAR2 DEFAULT 'ROW', -- alt. COLUMN existing_data_timestamp IN TIMESTAMP WITH TIME ZONE DEFAULT NULL, tombstone_deletes IN BOOLEAN DEFAULT TRUE, fetchcols IN BOOLEAN DEFAULT TRUE, record_conflicts IN BOOLEAN DEFAULT FALSE, use_custom_handlers IN BINARY_INTEGER DEFAULT 0);

exec dbms_goldengate_adm.alter_auto_cdr_column_group('UWCLASS', 'SERVERS', 'COLUMN', NULL, TRUE, TRUE, TRUE);

ADD_AUTO_CDR_COLUMN_GROUP

Adds a column group and configures GoldenGate automatic conflict detection and resolution for the column group dbms_goldengate_adm.add_auto_cdr_column_group( schema_name IN VARCHAR2, table_name IN VARCHAR2, column_list IN VARCHAR2, column_group_name IN VARCHAR2 DEFAULT NULL, existing_data_timestamp IN TIMESTAMP WITH TIME ZONE DEFAULT NULL);

exec dbms_goldengate_adm.alter_auto_cdr_column_group('UWCLASS', 'SERVERS', 'LATITUDE,LONGITUDE', 'POSITION_COLGRP');

ADD_AUTO_CDR_DELTA_RES

Configures CDR and delta resolution for the column dbms_goldengate_adm.add_auto_cdr_delta_res( schema_name IN VARCHAR2, table_name IN VARCHAR2, column_name IN VARCHAR2);

exec dbms_goldengate_adm.alter_auto_cdr_delta_res('UWCLASS', 'SERVERS', 'STATUS');

ALTER_AUTO_CDR

Alter automatic conflict detection and resolution at the table level dbms_goldengate_adm.alter_auto_cdr( schema_name IN VARCHAR2, table_name IN VARCHAR2, tombstone_deletes IN BOOLEAN DEFAULT NULL, fetchcols IN BOOLEAN DEFAULT NULL, record_conflicts IN BOOLEAN DEFAULT NULL, use_custom_handlers IN BINARY_INTEGER DEFAULT NULL);

exec dbms_goldengate_adm.alter_auto_cdr('UWCLASS', 'SERVERS', tombstone_deletes=>TRUE);

ALTER_AUTO_CDR_COLUMN_GROUP

Alters a column group for automatic conflict detection and resolution dbms_goldengate_adm.alter_auto_cdr_column_group( schema_name IN VARCHAR2, table_name IN VARCHAR2, column_group_name IN VARCHAR2, add_column_list IN VARCHAR2, remove_column_list IN VARCHAR2);

exec dbms_goldengate_adm.alter_auto_cdr_column_group('UWCLASS', 'SERVERS', 'COORDINATES', 'LATITUDE,LONGITUDE', '');

DELETE_PROCREP_EXCLUSION_OBJ

Deletes a database object from the exclusion list for GoldenGate procedural replication dbms_goldengate_adm.DELETE_PROCREP_EXCLUSION_OBJ( package_owner IN VARCHAR2 DEFAULT NULL, package_name IN VARCHAR2 DEFAULT NULL, object_owner IN VARCHAR2 DEFAULT NULL, object_name IN VARCHAR2 DEFAULT NULL);

exec dbms_goldengate_adm.delete_procrep_exclusion_obj('UWCLASS', 'TESTPKG');

GG_PROCEDURE_REPLICATION_ON

Returns 1 if GoldenGate procedural replication is enabled: 0 if disabled dbms_goldengate_adm.gg_procedure_replication_on RETURN NUMBER;

BEGIN

        IF dbms_goldengate_adm.gg_procedure_replication_on = 1 THEN

          dbms_output.put_line('Replication Is On');

        ELSE

          dbms_output.put_line('Replication Is Off');

        END IF;

      END;

      /

INSERT_PROCREP_EXCLUSION_OBJ

Inserts a database object into the exclusion list for procedural replication dbms_goldengate_adm.insert_procrep_exclusion_obj( package_owner IN VARCHAR2 DEFAULT NULL, package_name IN VARCHAR2 DEFAULT NULL, object_owner IN VARCHAR2 DEFAULT NULL, object_name IN VARCHAR2 DEFAULT NULL);

-- Oracle advises that this procedure only be run under the direction of Oracle Support

      

      exec dbms_goldengate_adm.insert_procrep_exclusion_obj('SYS',  'DBMS_REDEFINITION', 'UWCLASS', 'SERVERS');

PURGE_TOMBSTONES

Purges rows that were deleted before the specified timestamp from the tombstone table dbms_goldengate_adm.purge_tombstones(purge_timestamp IN TIMESTAMP WITH TIME ZONE);

exec dbms_goldengate_adm.purge_tombstones(STANDARD.SYS_AT_TIME_ZONE(SYSTIMESTAMP, 'US/Pacific')-1);

REMOVE_AUTO_CDR

Remove automatic CDR configuration dbms_goldengate_adm.remove_auto_cdr( schema_name IN VARCHAR2, table_name IN VARCHAR2);

exec dbms_goldengate_adm.remove_auto_cdr('UWCLASS', 'SERVERS');

REMOVE_AUTO_CDR_COLUMN_GROUP

Removes a column group configured for automatic conflict detection and resolution dbms_goldengate_adm.remove_auto_cdr_column_group( schema_name IN VARCHAR2, table_name IN VARCHAR2, column_group_name IN VARCHAR2);

exec dbms_goldengate_adm.remove_auto_cdr_column_group('UWCLASS', 'SERVERS', 'POSITION_COLGRP');

REMOVE_AUTO_CDR_DELTA_RES

Remove automatic CDR delta resolution for a column dbms_goldengate_adm.remove_auto_cdr_delta_res( schema_name IN VARCHAR2, table_name IN VARCHAR2, column_name IN VARCHAR2);

exec dbms_goldengate_adm.remove_auto_cdr_delta_res('UWCLASS', 'SERVERS', 'STATUS');

UPDATE_IDENTITY_COLUMN_HWM

Updates the HighWaterMark for all instance Identity column sequence objects dbms_goldengate_adm.update_identity_column_hwm;

exec dbms_goldengate_adm.update_identity_column_hwm;