13 Managing the DDL Replication Environment

This chapter contains instructions for making changes to the database environment or the Oracle GoldenGate environment when the Oracle GoldenGate DDL trigger is being used to support DDL replication. See Installing Trigger-Based DDL Capture for more information about the DDL objects.

For instructions on configuring Oracle GoldenGate DDL support, see Configuring DDL Support.

Note:

This chapter is only relevant for classic capture mode or integrated capture mode in which trigger-based DDL capture is being used.

Topics:

• Disabling DDL Processing Temporarily
  You must disable DDL activities before performing an instantiation or other tasks, if directed.
• Enabling and Disabling the DDL Trigger
  You can enable and disable the trigger that captures DDL operations without making any configuration changes within Oracle GoldenGate.
• Maintaining the DDL Marker Table
  You can purge rows from the marker table at any time. It does not keep DDL history.
• Deleting the DDL Marker Table
  Do not delete the DDL marker table unless you want to discontinue synchronizing DDL.
• Maintaining the DDL History Table
  You can purge the DDL history table to control its size, but do so carefully.
• Deleting the DDL History Table
  The history table and the DDL trigger are interdependent. An attempt to drop the history table fails if the DDL trigger is enabled. This is a safety measure to prevent the trigger from becoming invalid and missing DDL operations.
• Purging the DDL Trace File
  To prevent the DDL trace file from consuming excessive disk space, run the ddl_cleartrace script on a regular basis.
• Applying Database Patches and Upgrades when DDL Support is Enabled
  Database patches and upgrades usually invalidate the Oracle GoldenGate DDL trigger and other Oracle GoldenGate DDL objects.
• Apply Oracle GoldenGate Patches and Upgrades when DDL support is Enabled
  Use the following steps to apply a patch or upgrade to the DDL objects.
• Restoring an Existing DDL Environment to a Clean State
  Follow these steps to completely remove, and then reinstall, the Oracle GoldenGate DDL objects.
• Removing the DDL Objects from the System
  This procedure removes the DDL environment and removes the history that maintains continuity between source and target DDL operations.

Disabling DDL Processing Temporarily

You must disable DDL activities before performing an instantiation or other tasks, if directed.

You can resume DDL processing after the task is finished.

1. Disable user DDL operations on the source database.
2. If there are previous DDL replication processes that are still active, make certain that the last executed DDL operation was applied to the target before stopping those processes, so that the load data is applied to objects that have the correct metadata.
3. Comment out the DDL parameter in the Extract and Replicat parameter files that you configured for the new Oracle GoldenGate environment. Comment out any other parameters that support DDL.
4. Disable the Oracle GoldenGate DDL trigger, if one is in use. See Enabling and Disabling the DDL Trigger.

Enabling and Disabling the DDL Trigger

You can enable and disable the trigger that captures DDL operations without making any configuration changes within Oracle GoldenGate.

The following scripts control the DDL trigger.

• ddl_disable: Disables the trigger. No further DDL operations are captured or replicated after you disable the trigger.

• ddl_enable: Enables the trigger. When you enable the trigger, Oracle GoldenGate starts capturing current DDL changes, but does not capture DDL that was generated while the trigger was disabled.

Before running these scripts, disable all sessions that ever issued DDL, including those of the Oracle GoldenGate processes, SQL*Plus, business applications, and any other software that uses Oracle. Otherwise the database might generate an ORA-04021 error. Do not use these scripts if you intend to maintain consistent DDL on the source and target systems.

Maintaining the DDL Marker Table

You can purge rows from the marker table at any time. It does not keep DDL history.

To purge the marker table, use the Manager parameter PURGEMARKERHISTORY. Manager gets the name of the marker table from one of the following:

1. The name given with the MARKERTABLE parameter in the GLOBALS file, if specified.

2. The default name of GGS_MARKER.

PURGEMARKERHISTORY provides options to specify maximum and minimum lengths of time to keep a row, based on the last modification date.

Deleting the DDL Marker Table

Do not delete the DDL marker table unless you want to discontinue synchronizing DDL.

The marker table and the DDL trigger are interdependent. An attempt to drop the marker table fails if the DDL trigger is enabled. This is a safety measure to prevent the trigger from becoming invalid and missing DDL operations. If you remove the marker table, the following error is generated:

ORA-04098: trigger 'SYS.GGS_DDL_TRIGGER_BEFORE' is invalid and failed re-validation

The proper way to remove an Oracle GoldenGate DDL object depends on your plans for the rest of the DDL environment. To choose the correct procedure, see one of the following:

• Restoring an Existing DDL Environment to a Clean State

• Removing the DDL Objects from the System

Maintaining the DDL History Table

You can purge the DDL history table to control its size, but do so carefully.

The DDL history table maintains the integrity of the DDL synchronization environment. Purges to this table cannot be recovered through the Oracle GoldenGate interface.

1. To prevent any possibility of DDL history loss, make regular full backups of the history table.

2. To ensure that purged DDL can be recovered, enable Oracle Flashback for the history table. Set the flashback retention time well past the point where it could be needed. For example, if your full backups are at most one week old, retain two weeks of flashback. Oracle GoldenGate can be positioned backward into the flashback for reprocessing.

3. If possible, purge the DDL history table manually to ensure that essential rows are not purged accidentally. If you require an automated purging mechanism, use the PURGEDDLHISTORY parameter in the Manager parameter file. You can specify maximum and minimum lengths of time to keep a row.

Note:

Temporary tables created by Oracle GoldenGate to increase performance might be purged at the same time as the DDL history table, according to the same rules. The names of these tables are derived from the name of the history table, and their purging is reported in the Manager report file. This is normal behavior.

Deleting the DDL History Table

The history table and the DDL trigger are interdependent. An attempt to drop the history table fails if the DDL trigger is enabled. This is a safety measure to prevent the trigger from becoming invalid and missing DDL operations.

Do not delete the DDL history table unless you want to discontinue synchronizing DDL. The history table contains a record of DDL operations that were issued. Once an Extract switches from using the DDL trigger to not using the trigger, as when source database redo compatibility is advanced to 11.2.0.4 or greater, these objects can be deleted though not immediately. It is imperative that all mining of the redo generated before the compatibility change be complete and that this redo not need to be mined again.

If you remove the history table, the following error is generated:

ORA-04098: trigger 'SYS.GGS_DDL_TRIGGER_BEFORE' is invalid and failed re-validation

The proper way to remove an Oracle GoldenGate DDL object depends on your plans for the rest of the DDL environment. To choose the correct procedure, see one of the following:

• Restoring an Existing DDL Environment to a Clean State

• Removing the DDL Objects from the System

Purging the DDL Trace File

To prevent the DDL trace file from consuming excessive disk space, run the ddl_cleartrace script on a regular basis.

This script deletes the trace file, but Oracle GoldenGate will create it again.

The default name of the DDL trace file is ggs_ddl_trace.log. It is in the USER_DUMP_DEST directory of Oracle. The ddl_cleartrace script is in the Oracle GoldenGate directory.

Applying Database Patches and Upgrades when DDL Support is Enabled

Database patches and upgrades usually invalidate the Oracle GoldenGate DDL trigger and other Oracle GoldenGate DDL objects.

Before applying a database patch, do the following.

1. Log in to SQL*Plus as a user that has SYSDBA privileges.
2. Disable the Oracle GoldenGate DDL trigger by running the ddl_disable script in SQL*Plus.
3. Apply the patch.
4. Enable the DDL trigger by running the ddl_enable script in SQL*Plus.

Note:

Database upgrades and patches generally operate on Oracle objects. Because Oracle GoldenGate filters out those objects automatically, DDL from those procedures is not replicated when replication starts again.

To avoid recompile errors after the patch or upgrade, which are caused if the trigger is not disabled before the procedure, consider adding calls to @ddl_disable and @ddl_enable at the appropriate locations within your scripts.

Apply Oracle GoldenGate Patches and Upgrades when DDL support is Enabled

Use the following steps to apply a patch or upgrade to the DDL objects.

This section explains how to apply Oracle GoldenGate patches and upgrades when DDL support is enabled.

Note:

If the release notes or upgrade documentation for your Oracle GoldenGate release contain instructions similar to those provided in this section, follow those instructions instead the ones in this section. Do not use this procedure for an upgrade from an Oracle GoldenGate version that does not support DDL statements that are larger than 30K (pre-version 10.4). To upgrade in that case, follow the instructions in Restoring an Existing DDL Environment to a Clean State.

This procedure may or may not preserve the current DDL synchronization configuration, depending on whether the new build requires a clean installation.

1. Run GGSCI. Keep the session open for the duration of this procedure.
2. Stop Extract to stop DDL capture.

   STOP EXTRACT group
   

3. Stop Replicat to stop DDL replication.

   STOP REPLICAT group
   

4. Download or extract the patch or upgrade files according to the instructions provided by Oracle GoldenGate.
5. Change directories to the Oracle GoldenGate installation directory.
6. Log in to SQL*Plus as a user that has SYSDBA privileges.
7. Disconnect all sessions that ever issued DDL, including those of Oracle GoldenGate processes, SQL*Plus, business applications, and any other software that uses Oracle. Otherwise the database might generate an ORA-04021 error.
8. Run the ddl_disable script to disable the DDL trigger.
9. Run the ddl_setup script. You are prompted for the name of the Oracle GoldenGate DDL schema. If you changed the schema name, use the new one.
10. Run the ddl_enable.sql script to enable the DDL trigger.
11. In GGSCI, start Extract to resume DDL capture.

    START EXTRACT group
    

12. Start Replicat to start DDL replication.

    START REPLICAT group

Restoring an Existing DDL Environment to a Clean State

Follow these steps to completely remove, and then reinstall, the Oracle GoldenGate DDL objects.

This procedure creates a new DDL environment and removes any current DDL history.

Note:

Due to object interdependencies, all objects must be removed and reinstalled in this procedure.

1. If you are performing this procedure in conjunction with the installation of a new Oracle GoldenGate version, download and install the Oracle GoldenGate files, and create or update process groups and parameter files as necessary.
2. (Optional) To preserve the continuity of source and target structures, stop DDL activities and then make certain that Replicat finished processing all of the DDL and DML data in the trail. To determine when Replicat is finished, issue the following command until you see a message that there is no more data to process.

   INFO REPLICAT group

   Note:

   Instead of using INFO REPLICAT, you can use the EVENTACTIONS option of TABLE and MAP to stop the Extract and Replicat processes after the DDL and DML has been processed.

3. Run GGSCI.
4. Stop Extract to stop DDL capture.

   STOP EXTRACT group
   

5. Stop Replicat to stop DDL replication.

   STOP REPLICAT group
   

6. Change directories to the Oracle GoldenGate installation directory.
7. Log in to SQL*Plus as a user that has SYSDBA privileges.
8. Disconnect all sessions that ever issued DDL, including those of Oracle GoldenGate processes, SQL*Plus, business applications, and any other software that uses Oracle. Otherwise the database might generate an ORA-04021 error.
9. Run the ddl_disable script to disable the DDL trigger.
10. Run the ddl_remove script to remove the Oracle GoldenGate DDL trigger, the DDL history and marker tables, and other associated objects. This script produces a ddl_remove_spool.txt file that logs the script output and a ddl_remove_set.txt file that logs environment settings in case they are needed for debugging.
11. Run the marker_remove script to remove the Oracle GoldenGate marker support system. This script produces a marker_remove_spool.txt file that logs the script output and a marker_remove_set.txt file that logs environment settings in case they are needed for debugging.
12. If you are changing the DDL schema for this installation, grant the following permission to the Oracle GoldenGate schema.

    GRANT EXECUTE ON utl_file TO schema;
    

13. If you are changing the DDL schema for this installation, the schema's default tablespace must be dedicated to that schema; do not allow any other schema to share it. AUTOEXTEND must be set to ON for this tablespace, and the tablespace must be sized 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.

    Note:

    If the DDL tablespace fills up, Extract stops capturing DDL. To cause user DDL activity to fail when that happens, edit the params.sql script and set the ddl_fire_error_in_trigger parameter to TRUE. Stopping user 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. Instead, Oracle recommends that you size the tablespace appropriately and set AUTOEXTEND to ON so that the tablespace does not fill up.

    WARNING:

    Do not edit any other parameters in params.sql except if you need to follow documented instructions to change certain object names.

14. If you are changing the DDL schema for this installation, edit the GLOBALS file and specify the new schema name with the following parameter.

    GGSCHEMA schema_name
    

15. Run the marker_setup script to reinstall the Oracle GoldenGate marker support system. You are prompted for the name of the Oracle GoldenGate schema.
16. Run the ddl_setup script. You are prompted for the name of the Oracle GoldenGate DDL schema.
17. Run the role_setup script to recreate the Oracle GoldenGate DDL role.
18. Grant the role to all Oracle GoldenGate users under which the following Oracle GoldenGate processes run: Extract, Replicat, GGSCI, and Manager. You might need to make multiple grants if the processes have different user names.
19. Run the ddl_enable.sql script to enable the DDL trigger.

Removing the DDL Objects from the System

This procedure removes the DDL environment and removes the history that maintains continuity between source and target DDL operations.

Note:

Due to object interdependencies, all objects must be removed.

1. Run GGSCI.
2. Stop Extract to stop DDL capture.

   STOP EXTRACT group
   

3. Stop Replicat to stop DDL replication.

   STOP REPLICAT group
   

4. Change directories to the Oracle GoldenGate installation directory.
5. Run SQL*Plus and log in as a user that has SYSDBA privileges.
6. Disconnect all sessions that ever issued DDL, including those of Oracle GoldenGate processes, SQL*Plus, business applications, and any other software that uses Oracle. Otherwise the database might generate an ORA-04021 error.
7. Run the ddl_disable script to disable the DDL trigger.
8. Run the ddl_remove script to remove the Oracle GoldenGate DDL trigger, the DDL history and marker tables, and the associated objects. This script produces a ddl_remove_spool.txt file that logs the script output and a ddl_remove_set.txt file that logs current user environment settings in case they are needed for debugging.
9. Run the marker_remove script to remove the Oracle GoldenGate marker support system. This script produces a marker_remove_spool.txt file that logs the script output and a marker_remove_set.txt file that logs environment settings in case they are needed for debugging.