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:

When to Use Trigger-based DDL Capture
Overview of the Objects that Support Trigger-based DDL Capture
Installing the DDL Objects

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.

Choose a schema that can contain the Oracle GoldenGate DDL objects. This schema cannot be case-sensitive.
Grant the following permission to the Oracle GoldenGate schema.
```
GRANT EXECUTE ON utl_file TO schema;
```
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.
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.
(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.
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.
In the GLOBALS file, specify the name of the DDL schema by adding the following parameter to the GLOBALS file.
```
GGSCHEMA schema_name
```
(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_name^Foot 1
  
  History table
  
  DDLTABLE new_table_name
  
  ^Footnote 1Do 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.
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' 
```
Save and close the GLOBALS file and the params.sql file.
Change directories to the Oracle GoldenGate installation directory.
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.
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 .
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
```
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
```
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
```
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;
```
Run the ddl_enable.sql script to enable the DDL trigger.
```
@ddl_enable.sql
```

Object	Parameter
Marker table	`MARKERTABLE` `new_table_name`^Foot 1
History table	`DDLTABLE` `new_table_name`

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.