Performing an initial setup required for GoldenGate DDL replication

When GoldenGate is used for DDL replication, the Extract process on the source system requiressome metadata tables. Hence, to enable the DDL replication in a source environment, you need to run some scripts to do an initial setup which, in turn, creates these metadata objects.

In this recipe, we will look at how to perform the initial setup required for enabling the DDL replication in a GoldenGate environment.

Getting ready

We will use the GGATE_ADMIN schema for this setup.

How to do it...

Run the following scripts in the source environment:

Change the directory to GoldenGate Home as follows:
```
cd /u01/app/ggate
```
Log in to sqlplus as sysdba as shown in the following command:
```
sqlplus sys/**** as sysdba
```
Run the marker_setup.sql script:
```
SQL> @marker_setup.sql
```
This script will prompt for GoldenGate Admin schema name, enter it when prompted:
```
Enter Oracle GoldenGate schema name:GGATE_ADMIN
```
Run the ddl_setup.sql script as shown in the following command:
```
SQL> @ddl_setup.sql
```
This script will prompt for the GoldenGate Admin schema name, enter it when prompted:
```
Enter Oracle GoldenGate schema name:GGATE_ADMIN
```
Run the role_setup.sql script:
```
SQL> @role_setup.sql
```
Enter the GoldenGate Admin schema name, when prompted:
```
Enter Oracle GoldenGate schema name:GGATE_ADMIN
```
Grant the GoldenGate DDL role to the GoldenGate Admin user as follows:
```
SQL> GRANT GGS_GGSUSER_ROLE TO GGATE_ADMIN;
```
Run the ddl_enable.sql script as shown in the following command:
```
SQL> @ddl_enable.sql
```

Create a Globals file as shown in the following command:

GGSCI (prim1-ol6-112.localdomain) 1> edit params ./GLOBALS
GGSCHEMA GGATE_ADMIN

Save and close the file.

How it works...

The initial setup required for replicating DDL changes in a GoldenGate configuration is done in the source environment. GoldenGate binaries include the scripts that are installed in the GoldenGate Homedirectory. The scripts are run as shown in the following command:

SQL> @marker_setup.sql
Marker setup script
You will be prompted for the name of a schema for the Oracle GoldenGate database objects.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.
Enter Oracle GoldenGate schema name:GGATE_ADMIN
Marker setup table script complete, running verification script...
Please enter the name of a schema for the GoldenGate database objects:
Setting schema name to GGATE_ADMIN
MARKER TABLE
-------------------------------
OK
MARKER SEQUENCE
-------------------------------
OK
Script complete.

SQL> @ddl_setup.sql 
GoldenGate DDL Replication setup script
Verifying that current user has privileges to install DDL Replication...
You will be prompted for the name of a schema for the Oracle GoldenGate database objects.
NOTE: For an Oracle 10g source, the system recycle bin must be disabled. For Oracle 11g and later, it can be enabled.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.

Enter Oracle GoldenGate schema name:GGATE_ADMIN
Working, please wait ...
Spooling to file ddl_setup_spool.txt
Checking for sessions that are holding locks on Oracle Golden Gate metadata tables ...
Check complete.
Using GGATE_ADMIN as an Oracle GoldenGate schema name.
Working, please wait ...
………………………………………
………………………………………
DDL replication setup script complete, running verification script...
Please enter the name of a schema for the GoldenGate database objects:
Setting schema name to GGATE_ADMIN
LOCATION OF DDL TRACE FILE
---------------------------------------------------------------------/u01/app/oracle/diag/rdbms/dboratest/dboratest/trace/ggs_ddl_trace.log
Analyzing installation status...
STATUS OF DDL REPLICATION
---------------------------------------------------------------------SUCCESSFUL installation of DDL Replication software components
Script complete.

SQL> @role_setup.sql
GGS Role setup script
This script will drop and recreate the role GGS_GGSUSER_ROLE
To use a different role name, quit this script and then edit the params.sql script to change the gg_role parameter to the preferred name. (Do not run the script.)
You will be prompted for the name of a schema for the GoldenGate database objects.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.
Enter GoldenGate schema name:GGATE_ADMIN
Wrote file role_setup_set.txt
PL/SQL procedure successfully completed.
Role setup script complete
Grant this role to each user assigned to the Extract, GGSCI, and Manager processes, by using the following SQL command:
GRANT GGS_GGSUSER_ROLE TO <loggedUser>
Where <loggedUser> is the user assigned to the GoldenGate processes.
SQL> @ddl_enable.sql
Trigger altered.

There's more...

The DDL setup scripts by default follow a fixed naming convention in which all objects names start withGGS_. If you want to change the names of the tables created as a part of this setup due to some organization standards or security reasons, you can do so by modifying the params.sql script which is supplied as a part of GoldenGate binaries. All the DDL initial setup scripts read this script for setting the values of the object names.

GOLDDEN GATEM

Wednesday, April 20, 2016