D Installing Trigger-Based DDL Capture

This appendix contains instructions for installing the objects that support the trigger-based method of Oracle GoldenGate DDL support.

This appendix includes the following sections:

To configure Oracle GoldenGate to capture and replicate DDL, see Chapter 13, "Configuring DDL Support."

Note:

DDL support for sequences (CREATE, ALTER, DROP, RENAME) is compatible with, but not required for, replicating the sequence values themselves. To replicate just sequence values, you do not need to install the Oracle GoldenGate DDL support environment. You can just use the SEQUENCE parameter in the Extract configuration.

D.1 When to Use Trigger-based DDL Capture

You must use trigger-based DDL capture when Extract will operate in the following configurations:

  • Extract operates in classic capture mode against any version of Oracle database.

  • Extract operates in integrated mode against an Oracle database version 11.2.0.3 or earlier.

If Extract will run in integrated mode against a version 11.2.0.4 or later Oracle database, the DDL trigger is not required. By default, DDL capture is handled transparently through the database logmining server.

If Extract will capture from a multitenant container database, integrated capture mode must be used with the native DDL capture method.

See Chapter 5, "Choosing Capture and Apply Modes" for more information about capture modes.

See Chapter 13, "Configuring DDL Support" for more information about configuring DDL support.

D.2 Overview of the Objects that Support Trigger-based DDL Capture

To install the Oracle GoldenGate trigger-based DDL environment, you will be installing the database objects shown in Table D-1.

Table D-1 DDL Synchronization Objects

Object Purpose Default name

DDL marker table

Stores DDL information. This table only receives inserts.

GGS_MARKER

Sequence on marker table

Used for a column in the marker table.

GGS_DDL_SEQ

DDL history table

Stores object metadata history. This table receives inserts, updates, deletes.

GGS_DDL_HIST

Object ID history table

Contains object IDs of configured objects.

GGS_DDL_HIST_ALT

DDL trigger

Fires on DDL operations. Writes information about the operation to the marker and history tables. Installed with the trigger are some packages.

GGS_DDL_TRIGGER_BEFORE

DDL schema

Contains the DDL synchronization objects.

None; must be specified during installation and in the GLOBALS file.

User role

Establishes the role needed to execute DDL operations.

GGS_GGSUSER_ROLE

Internal setup table

Database table for internal use only.

GGS_SETUP

ddl_pin

Pins DDL tracing, the DDL package, and the DDL trigger for performance improvements.

ddl_pin

ddl_cleartrace.sql

Removes the DDL trace file.

ddl_cleartrace.sql

ddl_status.sql

Verifies that the Oracle GoldenGate DDL objects are installed

ddl_status.sql

marker_status.sql

Verifies that the marker table is installed.

marker_status.sql

ddl_tracelevel.sql

Sets the level for DDL tracing.

ddl_tracelevel.sql


D.3 Installing the DDL Objects

Follow these steps to install the database objects that support Oracle GoldenGate DDL capture.

Note:

When using Extract in classic mode to capture in an Active Data Guard environment, the DDL objects must be installed on the source database, not the standby.
  1. Choose a schema that can contain the Oracle GoldenGate DDL objects. This schema cannot be case-sensitive.

  2. Grant the following permission to the Oracle GoldenGate schema.

    GRANT EXECUTE ON utl_file TO schema;
    
  3. Create a default tablespace for the Oracle GoldenGate DDL schema. This tablespace must be dedicated to the DDL schema; do not allow any other schema to share it.

  4. Set AUTOEXTEND to ON for the DDL tablespace, and size it to accommodate the growth of the GGS_DDL_HIST and GGS_MARKER tables. The GGS_DDL_HIST table, in particular, will grow in proportion to overall DDL activity.

  5. (Optional) To cause user DDL activity to fail when the DDL tablespace fills up, edit the params.sql script and set the ddl_fire_error_in_trigger parameter to TRUE. Extract cannot capture DDL if the tablespace fills up, so stopping the DDL gives you time to extend the tablespace size and prevent the loss of DDL capture. Managing tablespace sizing this way, however, requires frequent monitoring of the business applications and Extract to avoid business disruptions. As a best practice, make certain to size the tablespace appropriately in the first place, and set AUTOEXTEND to ON so that the tablespace does not fill up.

    CAUTION:

    Make a backup of the params.sql script before you edit it to preserve its original state.

  6. Create a GLOBALS file (or edit it, if one exists).

    EDIT PARAMS ./GLOBALS
    

    Note:

    EDIT PARAMS creates a simple text file. When you save the file after EDIT PARAMS, it is saved with the name GLOBALS in upper case, without a file extension, at the root of the Oracle GoldenGate directory. Do not alter the file name or location.
  7. In the GLOBALS file, specify the name of the DDL schema by adding the following parameter to the GLOBALS file.

    GGSCHEMA schema_name
    
  8. (Optional) To change the names of other objects listed in Table D-1, the changes must be made now, before proceeding with the rest of the installation. Otherwise, you will need to stop Oracle GoldenGate DDL processing and reinstall the DDL objects. It is recommended that you accept the default names of the database objects. To change any name in Table D-1 (except the schema), do one or both of the following:

    • Record all name changes in the params.sql script. Edit this script and change the appropriate parameters. Do not run this script.

    • List the names shown in Table D-2 in the GLOBALS file. The correct parameters to use are listed in the Parameter column of the table.

      Table D-2 GLOBALS Parameters for Changing DDL Object Names

      Object Parameter

      Marker table

      MARKERTABLE new_table_nameFoot 1 

      History table

      DDLTABLE new_table_name


      Footnote 1 Do not qualify the name of any of these tables. The schema name for these table must be either the one that is specified with GGSCHEMA or the schema of the current user, if GGSCHEMA is not specified in GLOBALS.

  9. To enable trigger-based DDL replication to recognize Oracle invisible indexes as unique identifiers, set the following parameter to TRUE in the params.sql script:

    define allow_invisible_index_keys = 'TRUE' 
    
  10. Save and close the GLOBALS file and the params.sql file.

  11. Change directories to the Oracle GoldenGate installation directory.

  12. Exit all Oracle sessions, including those of SQL*Plus, those of business applications, those of the Oracle GoldenGate processes, and those of any other software that uses Oracle. Prevent the start of any new sessions.

  13. Run SQL*Plus and log in as a user that has SYSDBA privilege. This privilege is required to install the DDL trigger in the SYS schema, which is required by Oracle. All other DDL objects are installed in the schema that you created in .

  14. Run the marker_setup.sql script. Supply the name of the Oracle GoldenGate schema when prompted, and then press Enter to execute the script. The script installs support for the Oracle GoldenGate DDL marker system.

    @marker_setup.sql
    
  15. Run the ddl_setup.sql script. You are prompted to specify the name of the DDL schema that you configured in . (Note: ddl_setup.sql will fail if the tablespace for this schema is shared by any other users. It will not fail, however, if the default tablespace does not have AUTOEXTEND set to ON, the recommended setting.)

    @ddl_setup.sql
    
  16. Run the role_setup.sql script. At the prompt, supply the DDL schema name. The script drops and creates the role that is needed for DDL synchronization, and it grants DML permissions on the Oracle GoldenGate DDL objects.

    @role_setup.sql
    
  17. Grant the role that was created (default name is GGS_GGSUSER_ROLE) to all Oracle GoldenGate Extract users. You may need to make multiple grants if the processes have different user names.

    GRANT role TO user;
    
  18. Run the ddl_enable.sql script to enable the DDL trigger.

    @ddl_enable.sql
    

To Install and Use the Optional Performance Tool

To improve the performance of the DDL trigger, make the ddl_pin script part of the database startup. It must be invoked with the Oracle GoldenGate DDL user name, as in:

@ddl_pin DDL_user

This script pins the PL/SQL package that is used by the trigger into memory. If executing this script from SQL*Plus, connect as SYSDBA from the Oracle GoldenGate installation directory. This script relies on the Oracle dmbs_shared_pool system package, so install that package before using ddl_pin.