本章包含有关在使用Oracle GoldenGate DDL触发器支持DDL复制时更改数据库环境或Oracle GoldenGate环境的说明。有关DDL对象的更多信息,请参见安装基于触发器的DDL捕获。
For instructions on configuring Oracle GoldenGate DDL support, see Configuring DDL Support.
Note:
本章仅适用于经典捕获模式或集成捕获模式,其中使用了基于触发器的DDL捕获。
Topics:
- Disabling DDL Processing Temporarily如果有指示,在执行实例化或其他任务之前,必须禁用DDL活动。
- Enabling and Disabling the DDL Trigger您可以启用和禁用捕获DDL操作的触发器,而无需在Oracle GoldenGate中进行任何配置更改。
- Maintaining the DDL Marker Table您可以随时从标记表中清除行。它没有保存DDL历史。
- Deleting the DDL Marker Table不要删除DDL标记表,除非您想停止同步DDL。
- Maintaining the DDL History Table您可以清除DDL历史表来控制它的大小,但是要小心操作。
- Deleting the DDL History Table历史表和DDL触发器是相互依赖的。如果启用DDL触发器,则删除历史表的尝试将失败。这是一个安全措施,以防止触发器变得无效和缺少DDL操作。
- Purging the DDL Trace File为了防止DDL跟踪文件消耗过多的磁盘空间,请定期运行ddl_cleartrace脚本。
- Applying Database Patches and Upgrades when DDL Support is Enabled数据库补丁和升级通常会使Oracle GoldenGate DDL触发器和其他Oracle GoldenGate DDL对象失效。
- Apply Oracle GoldenGate Patches and Upgrades when DDL support is Enabled使用以下步骤对DDL对象应用补丁或升级。
- Restoring an Existing DDL Environment to a Clean State按照以下步骤完全删除并重新安装Oracle GoldenGate DDL对象。
- Removing the DDL Objects from the System此过程删除DDL环境并删除保持源和目标DDL操作之间连续性的历史记录。
15.1 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.
- Disable user DDL operations on the source database.
- 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.
- 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. - Disable the Oracle GoldenGate DDL trigger, if one is in use. See Enabling and Disabling the DDL Trigger.
Parent topic: Managing the DDL Replication Environment
15.2 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.
Parent topic: Managing the DDL Replication Environment
15.3 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:
-
The name given with the
MARKERTABLE
parameter in theGLOBALS
file, if specified. -
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. For more information, see Reference for Oracle GoldenGate.
Parent topic: Managing the DDL Replication Environment
15.4 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:
Parent topic: Managing the DDL Replication Environment
15.5 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.
-
To prevent any possibility of DDL history loss, make regular full backups of the history table.
-
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.
-
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. For more information, see Reference for Oracle GoldenGate.
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.
Parent topic: Managing the DDL Replication Environment
15.6 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:
Parent topic: Managing the DDL Replication Environment
15.7 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.
Parent topic: Managing the DDL Replication Environment
15.8 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.
- Log in to SQL*Plus as a user that has
SYSDBA
privileges. - Disable the Oracle GoldenGate DDL trigger by running the
ddl_disable
script in SQL*Plus. - Apply the patch.
- 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.
Parent topic: Managing the DDL Replication Environment
15.9 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.
- Run GGSCI. Keep the session open for the duration of this procedure.
- Stop Extract to stop DDL capture.
STOP EXTRACT group
- Stop Replicat to stop DDL replication.
STOP REPLICAT group
- Download or extract the patch or upgrade files according to the instructions provided by Oracle GoldenGate.
- Change directories to the Oracle GoldenGate installation directory.
- Log in to SQL*Plus as a user that has
SYSDBA
privileges. - 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.
- Run the
ddl_disable
script to disable the DDL trigger. - 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. - Run the
ddl_enable.sql
script to enable the DDL trigger. - In GGSCI, start Extract to resume DDL capture.
START EXTRACT group
- Start Replicat to start DDL replication.
START REPLICAT group
Parent topic: Managing the DDL Replication Environment
15.10 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.
- 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.
- (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 theEVENTACTIONS
option ofTABLE
andMAP
to stop the Extract and Replicat processes after the DDL and DML has been processed. - Run GGSCI.
- Stop Extract to stop DDL capture.
STOP EXTRACT group
- Stop Replicat to stop DDL replication.
STOP REPLICAT group
- Change directories to the Oracle GoldenGate installation directory.
- Log in to SQL*Plus as a user that has
SYSDBA
privileges. - 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.
- Run the
ddl_disable
script to disable the DDL trigger. - 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 addl_remove_spool.txt
file that logs the script output and addl_remove_set.txt
file that logs environment settings in case they are needed for debugging. - Run the
marker_remove
script to remove the Oracle GoldenGate marker support system. This script produces amarker_remove_spool.txt
file that logs the script output and amarker_remove_set.txt
file that logs environment settings in case they are needed for debugging. - 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;
- 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 toON
for this tablespace, and the tablespace must be sized to accommodate the growth of theGGS_DDL_HIST
andGGS_MARKER
tables. TheGGS_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 theddl_fire_error_in_trigger
parameter toTRUE
. 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 setAUTOEXTEND
toON
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. - 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
- Run the
marker_setup
script to reinstall the Oracle GoldenGate marker support system. You are prompted for the name of the Oracle GoldenGate schema. - Run the
ddl_setup
script. You are prompted for the name of the Oracle GoldenGate DDL schema. - Run the
role_setup
script to recreate the Oracle GoldenGate DDL role. - 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.
- Run the
ddl_enable.sql
script to enable the DDL trigger.
Parent topic: Managing the DDL Replication Environment
15.11 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.
- Run GGSCI.
- Stop Extract to stop DDL capture.
STOP EXTRACT group
- Stop Replicat to stop DDL replication.
STOP REPLICAT group
- Change directories to the Oracle GoldenGate installation directory.
- Run SQL*Plus and log in as a user that has
SYSDBA
privileges. - 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.
- Run the
ddl_disable
script to disable the DDL trigger. - 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 addl_remove_spool.txt
file that logs the script output and addl_remove_set.txt
file that logs current user environment settings in case they are needed for debugging. - Run the
marker_remove
script to remove the Oracle GoldenGate marker support system. This script produces amarker_remove_spool.txt
file that logs the script output and amarker_remove_set.txt
file that logs environment settings in case they are needed for debugging.
Parent topic: Managing the DDL Replication Environment