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 the EVENTACTIONS option of TABLE and MAP 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 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.
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.
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 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.
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.