Setting up a GoldenGate DDL replication and verifying the changes

In a default GoldenGate setup, all DMLs are captured in the source environment and all DMLs and DDLs are applied in the target environment. However, unless you start extracting the DDL statements in source, there would not be any DDL statements to apply at the target environment by replicat. Extracting the DDLs in source requires some extra configuration. In this recipe we will look into the additional parameters/setup required for performing the DDL replication.

Getting ready

For this recipe the following setup must be in place:

Source and target databases with the SCOTT sample schema.
DDL replication initial setup as explained in the previous recipe, Performing an initial setup required for GoldenGate DDL replication.

How to do it...

Following are the steps to configure DDL synchronization between the source and target environments.

Perform the following steps in the source environment:

Start the GoldenGate command line interface by using the following code:
```
./ggsci
```
Stop the Extract and Datapump processes if they are running as shown in the following command:
```
GGSCI> STOP EXTRACT EGGTEST1
GGSCI> STOP EXTRACT PGGTEST1
```

Edit the extract parameter file as follows:

./ggsci
EDIT PARAMS EGGTEST1
EXTRACT EGGTEST1
USERID GGATE_ADMIN@DBORATEST, PASSWORD ******
EXTTRAIL /u01/app/ggate/dirdat/st
DDL INCLUDE MAPPED OBJNAME SCOTT.*
TABLE SCOTT.*;

Start the Extract process as shown in the following command:
```
GGSCI> START EGGTEST1
```
Start the Datapump process as shown in the following command:
```
GGSCI> START PGGTEST1
```

Perform the following steps in the target environment:

Start the GoldenGate command line interface by using the following command:
```
./ggsci
```
Stop the Replicat process as shown in the following command:
```
GGSCI> STOP REPLICAT RGGTEST1
```
Grant the necessary privileges to the GoldenGate Admin user to enable it to replicate DDL changes to the target environment as shown in the following command:
```
SQL> GRANT ALTER on SCOTT.EMP to GGATE_ADMIN;
```
Start the Replicat process as shown in the following command:
```
GGSCI> START REPLICAT RGGTEST1
```

Check the structure of the EMP table in the target environment as shown in the following command:

SQL> DESC SCOTT.EMP

Name                Null?            Type
----------------------------------------- -------- -----
EMPNO              NOT NULL         NUMBER(4)
ENAME                               VARCHAR2(10)
JOB                                 VARCHAR2(9)
MGR                                 NUMBER(4)
HIREDATE                            DATE
SAL                                 NUMBER(7,2)
COMM                                NUMBER(7,2)
DEPTNO                              NUMBER(2)

After you complete the preceding steps, perform the following steps in the source environment:

Check the structure of the EMP table in the source environment as shown in the following command:

SQL> DESC SCOTT.EMP
Name                Null?            Type
----------------------------------------- -------- -----
EMPNO               NOT NULL         NUMBER(4)
ENAME                                VARCHAR2(10)
JOB                                  VARCHAR2(9)
MGR                                  NUMBER(4)
HIREDATE                             DATE
SAL                                  NUMBER(7,2)
COMM                                 NUMBER(7,2)
DEPTNO                               NUMBER(2)

Perform a DDL operation on the EMP table as shown in the following command:
```
SQL> ALTER TABLE EMP ADD CITY VARCHAR(25);
```

Verify the modified structure in the source environment as shown in the following command:

SQL> DESC SCOTT.EMP
Name                Null?            Type
----------------------------------------- -------- -----
EMPNO               NOT NULL         NUMBER(4)
ENAME                                VARCHAR2(10)
JOB                                  VARCHAR2(9)
MGR                                  NUMBER(4)
HIREDATE                             DATE
SAL                                  NUMBER(7,2)
COMM                                 NUMBER(7,2)
DEPTNO                               NUMBER(2)
CITY                                 VARCHAR2(25)

Perform the following steps in the target environment:

Verify the modified structure in the target environment as shown in the following command:

SQL> DESC SCOTT.EMP
Name                Null?            Type
----------------------------------------- -------- -----
EMPNO               NOT NULL         NUMBER(4)
ENAME                                VARCHAR2(10)
JOB                                  VARCHAR2(9)
MGR                                  NUMBER(4)
IREDATE                              DATE
SAL                                  NUMBER(7,2)
COMM                                 NUMBER(7,2)
DEPTNO                               NUMBER(2)
CITY                                 VARCHAR2(25)

How it works...

The DDL INCLUDE parameter enables the Extract process to extract any structural changes for allSCOTT objects. When the ALTER TABLE statement is run to add a column to the EMP table, this DDLchange is extracted by the EGGTEST1 Extract process and then transferred to the target site by thePGGTEST1 Datapump process. The GoldenGate Admin user needs to be granted some extra privileges to allow it to apply the DDL changes to the target database objects. In this example, we have only granted it the ALTER privileges on the EMP table. Then the Replicat process picks up the DDLchange from the trail file and applies it to the target database.

TIP

In this example we haven't modified the replicat parameter file to include any DDL parameter. This is because DDL replication is enabled by default at the replicat level. However, you might need to include some of the additional DDLOPTIONS parameters based on your requirements for which you might need to add it to the replicat parameter file.

There's more...

When you set up a GoldenGate DDL replication, there are some additional aspects that you should consider. Some of these are as follows:

EXTRA PRIVILEGES THAT ARE REQUIRED FOR THE GOLDENGATE ADMIN USER

When you replicate DDL using GoldenGate, you can synchronize the changes to existing objects as well as to the new objects that get created in the source environment. For the changes to the existing objects, the GoldenGate Admin user will need the appropriate privilege on the object whose structure is modified in the source environment, for example, ALTER on the EMP table as in this example.

For creating new objects in various schemas, you need to grant the CREATE ANY TABLE privilege to the GoldenGate Admin user.

FILTERING AND MAPPING

There are many options in GoldenGate for mapping and filtering various DDL changes to different objects/schemas. The filtering is mainly done at the extract level by specifying various options to theDDL INCLUDE and DDL EXCLUDE parameter. You can filter on the basis of object name, type, DDL type, or you can even search for a particular string in the DDL statement. Once the filtered changes are captured by the Extract process and sent to the target environment, the replicat then applies them on the basis of the mapping rules defined in its parameter file. You only define the mapping for the source and target objects in the replicat parameter file once. GoldenGate applies both DML and DDL changes based on these mapping rules to the target objects. Following is an example of how these parameters can be used:

DDL INCLUDE OBJNAME "SCOTT.*"
DDL EXCLUDE OBJNAME "SCOTT.*"

You can specify the INCLUDE clause on its own, but if you want to use an EXCLUDE parameter you must specify at least one INCLUDE parameter.

ADDITIONAL DDLOPTIONS

The following are some of the additional important DDLOPTIONS:

DDLOPTIONS ADDTRANDATA: If you have configured DDL replication, GoldenGate will create the new objects in the target environment. However, these objects will not have Supplemental Logging groups created by default due to which it will not be able to extract any DML changes that occur on these objects. Using the DDLOPTIONS ADDTRANDATA option ensures Supplemental Logging is enabled on the newly created objects that GoldenGate is replicating. It also updates the supplemental log groups when ALTER TABLE is run.
DDLOPTIONS REPORT: This option can be used to write extra information about DDL changes processed by a GoldenGate process to its report file.

GOLDDEN GATEM

Wednesday, April 20, 2016