19 min read

(For more resources related to this topic, see here.)

Upgrading Oracle GoldenGate binaries

In this recipe you will learn how to upgrade GoldenGate binaries. You will also learn about GoldenGate patches and how to apply them.

Getting ready

For this recipe, we will upgrade the GoldenGate binaries from version 11.2.1.0.1 to 11.2.1.0.3 on the source system, that is prim1-ol6-112 in our case. Both of these binaries are available from the Oracle Edelivery website under the part number V32400-01 and V34339-01 respectively. 11.2.1.0.1 binaries are installed under /u01/app/ggate/112101.

How to do it…

The steps to upgrade the Oracle GoldenGate binaries are:

  1. Make a new directory for 11.2.1.0.3 binaries: 
    mkdir /u01/app/ggate/112103
  2. Copy the binaries ZIP file to the server in the new directory.
  3. Unzip the binaries file: 
    [ggate@prim1-ol6-112 112103]$ cd /u01/app/ggate/112103
[ggate@prim1-ol6-112 112103]$ unzip V34339-01.zip 
Archive:  V34339-01.zip
inflating: fbo_ggs_Linux_x64_ora11g_64bit.tar  
inflating: Oracle_GoldenGate_11.2.1.0.3_README.doc  
inflating: Oracle GoldenGate_11.2.1.0.3_README.txt  
inflating: OGG_WinUnix_Rel_Notes_11.2.1.0.3.pdf
  4. Install the new binaries in /u01/app/ggate/112103: 
    [ggate@prim1-ol6-112 112103]$ tar -pxvf fbo_ggs_Linux_x64_ora11g_64bit.tar
  5. Stop the processes in the existing installation: 
    [ggate@prim1-ol6-112 112103]$ cd /u01/app/ggate/112101
[ggate@prim1-ol6-112 112101]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Apr 23 2012 08:32:14
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights 
reserved.
GGSCI (prim1-ol6-112.localdomain) 1> stop *
Sending STOP request to EXTRACT EGGTEST1 ...
Request processed.
Sending STOP request to EXTRACT PGGTEST1 ...
Request processed.
  6. Stop the manager process: 
    GGSCI (prim1-ol6-112.localdomain) 2> STOP MGR
    Manager process is required by other GGS processes.
    Are you sure you want to stop it (y/n)? y
    Sending STOP request to MANAGER ...
    Request processed.
    Manager stopped.
  7. Copy the subdirectories to the new binaries: 
    [ggate@prim1-ol6-112 112101]$ cp -R dirprm /u01/app/ggate/112103/
    [ggate@prim1-ol6-112 112101]$ cp -R dirrpt /u01/app/ggate/112103/
    [ggate@prim1-ol6-112 112101]$ cp -R dirchk /u01/app/ggate/112103/
    [ggate@prim1-ol6-112 112101]$ cp -R BR /u01/app/ggate/112103/
    [ggate@prim1-ol6-112 112101]$ cp -R dirpcs /u01/app/ggate/112103/
     [ggate@prim1-ol6-112 112101]$ cp -R dirdef /u01/app/ggate/112103/
    [ggate@prim1-ol6-112 112101]$ cp -R dirout /u01/app/ggate/112103/
    [ggate@prim1-ol6-112 112101]$ cp -R dirdat /u01/app/ggate/112103/
    [ggate@prim1-ol6-112 112101]$ cp -R dirtmp /u01/app/ggate/112103/
  8. Modify any parameter files under dirprm if you have hardcoded old binaries path in them.
  9. Edit the ggate user profile and update the value of the GoldenGate binaries home: 
    vi .profile
export GG_HOME=/u01/app/ggate/112103
  10. Start the manager process from the new binaries: 
    [ggate@prim1-ol6-112 ~]$ cd /u01/app/ggate/112103/
[ggate@prim1-ol6-112 112103]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.3 14400833 OGGCORE_11.2.1.0.3_PLATFORMS_120823.1258_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Aug 23 2012 20:20:21
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
GGSCI (prim1-ol6-112.localdomain) 1> START MGR
Manager started.
  11. Start the processes: 
    GGSCI (prim1-ol6-112.localdomain) 18> START EXTRACT *
Sending START request to MANAGER ...
EXTRACT EGGTEST1 starting
Sending START request to MANAGER ...
EXTRACT PGGTEST1 starting

How it works…

The method to upgrade the GoldenGate binaries is quite straightforward. As seen in the preceding section, you need to download and install the binaries on the server in a new directory. After this, you would stop the all GoldenGate processes that are running from the existing binaries. Then you would copy all the important GoldenGate directories with parameter files, trail files, report files, checkpoint files, and recovery files to the new binaries. If your trail files are kept on a separate filesystem which is linked to the dirdat directory using a softlink, then you would just need to create a new softlink under the new GoldenGate binaries home. Once all the files are copied, you would need to modify the parameter files if you have the path of the existing binaries hardcoded in them. The same would also need to be done in the OS profile of the ggate user. After this, you just start the manager process and rest of the processes from the new home.

GoldenGate patches are all delivered as full binaries sets. This makes the procedure to patch the binaries exactly the same as performing major release upgrades.

Table structure changes in GoldenGate environments with similar table definitions

Almost all of the applications systems in IT undergo some change over a period of time. This change might include a fix of an identified bug, an enhancement or some configuration change required due to change in any other part of the system. The data that you would replicate using GoldenGate will most likely be part of some application schema. These schemas, just like the application software, sometimes require some changes which are driven by the application vendor. If you are replicating DDL along with DML in your environment then these schema changes will most likely be replicated by GoldenGate itself. However, if you are only replicating only DML and there are any DDL changes in the schema particularly around the tables that you are replicating, then these will affect the replication and might even break it.

In this recipe, you will learn how to update the GoldenGate configuration to accommodate the schema changes that are done to the source system. This recipe assumes that the definitions of the tables that are replicated are similar in both the source and target databases.

Getting ready

For this recipe we are making the following assumptions:

  1. GoldenGate is set up to replicate only DML changes between the source and target environments.
  2. The application will be stopped for making schema changes in the source environment.
  3. The table structures in the source and target database are similar.
  4. The replication is configured for all objects owned by a SCOTT user using a SCOTT.* clause.
  5. The GoldenGate Admin user has been granted SELECT ANY TABLE in the source database and INSERT ANY TABLE, DELETE ANY TABLE, UPDATE ANY TABLE, SELECT ANY TABLE in the target database.

The schema changes performed in this recipe are as follows:

  1. Add a new column called DOB (DATE) to the EMP table.
  2. Modify the DNAME column in the DEPT table to VARCHAR(20).
  3. Add a new table called ITEMS to the SCOTT schema: 
    ITEMS

ITEMNO    NUMBER(5)    PRIMARY KEY
NAME     VARCHAR(20)
  4. Add a new table called SALES to the SCOTT schema: 
    SALES

INVOICENO    NUMBER(9)	    PRIMARY KEY
ITEMNO       NUMBER(5)	    FOREIGN KEY      ITEMS(ITEMNO)
EMPNO        NUMBER(4)	    FOREIGN KEY      EMP(EMPNO)
  5. Load the values for the DOB column in the EMP table.
  6. Load a few records in the ITEMS table.

How to do it…

Here are the steps that you can follow to implement the preceding schema changes in the source environment:

  1. Ensure that the application accessing the source database is stopped. There should not be any process modifying the data in the database.
  2. Once you have stopped the application, wait for 2 to 3 minutes so that all pending redo is processed by the GoldenGate extract.
  3. Check the latest timestamp read by the Extract and Datapump processes and ensure it is the current timestamp: 
    GGSCI (prim1-ol6-112.localdomain) 9> INFO EXTRACT EGGTEST1 
GGSCI (prim1-ol6-112.localdomain) 10> INFO EXTRACT *
EXTRACT    EGGTEST1  Last Started 2013-03-25 22:24   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:07 ago)
Log Read Checkpoint  Oracle Redo Logs
         2013-03-25 22:35:06  Seqno 350, RBA 11778560
         SCN 0.11806849 (11806849)
EXTRACT    PGGTEST1  Last Started 2013-03-25 22:24   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:04 ago)
Log Read Checkpoint  File /u01/app/ggate/dirdat/st000010
          2013-03-25 22:35:05.000000  RBA 7631
  4. Stop the Extract and Datapump processes in the source environment: 
    GGSCI (prim1-ol6-112.localdomain) 1> STOP EXTRACT *
Sending STOP request to EXTRACT EGGTEST1 ...
Request processed.
Sending STOP request to EXTRACT PGGTEST1 ...
Request processed.
  5. Check the status of the Replicat process in the target environment and ensure that it has processed the timestamp noted in step 3: 
    GGSCI (stdby1-ol6-112.localdomain) 54> INFO REPLICAT *
REPLICAT   RGGTEST1  Last Started 2013-03-25 22:25   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:04 ago)
Log Read Checkpoint  File ./dirdat/rt000061
           2013-03-25 22:37:04.950188  RBA 10039
  6. Stop the Replicat process in the target environment: 
    GGSCI (stdby1-ol6-112.localdomain) 48> STOP REPLICAT *
Sending STOP request to REPLICAT RGGTEST1 ...
Request processed.
  7. Apply the schema changes to the source database: 
    SQL> ALTER TABLE SCOTT.EMP ADD DOB DATE;
Table altered.

SQL> ALTER TABLE SCOTT.DEPT MODIFY DNAME VARCHAR(20);        
Table altered.

SQL> CREATE TABLE SCOTT.ITEMS (
ITEMNO NUMBER(5) PRIMARY KEY,
NAME VARCHAR(20));
Table created.
SQL> CREATE TABLE SCOTT.SALES (
INVOICENO NUMBER(9) PRIMARY KEY,
ITEMNO NUMBER(5) REFERENCES SCOTT.ITEMS(ITEMNO),
EMPNO NUMBER(4) REFERENCES SCOTT.EMP(EMPNO));
Table created.


SQL> UPDATE SCOTT.EMP SET DOB=TO_DATE('01-01-1980','DD-MM-YYYY');
14 rows updated.

SQL> INSERT INTO SCOTT.ITEMS VALUES (1,'IRON');
1 row created.

SQL> INSERT INTO SCOTT.ITEMS VALUES (2,'COPPER');
1 row created.

SQL> INSERT INTO SCOTT.ITEMS VALUES (3,'GOLD');
1 row created.

SQL> INSERT INTO SCOTT.ITEMS VALUES (4,'SILVER');
1 row created.

SQL> COMMIT;
Commit complete.
  8. Apply the schema changes to the target database: 
    SQL> ALTER TABLE SCOTT.EMP ADD DOB DATE;
Table altered.

SQL> ALTER TABLE SCOTT.DEPT MODIFY DNAME VARCHAR(20);        
Table altered.

SQL> CREATE TABLE SCOTT.ITEMS (
ITEMNO NUMBER(5) PRIMARY KEY,
NAME VARCHAR(20));
Table created.
SQL> CREATE TABLE SCOTT.SALES (
INVOICENO NUMBER(9) PRIMARY KEY,
ITEMNO NUMBER(5) REFERENCES SCOTT.ITEMS(ITEMNO),
EMPNO NUMBER(4) REFERENCES SCOTT.EMP(EMPNO));
Table created.


SQL> UPDATE SCOTT.EMP SET DOB=TO_DATE('01-01-1980','DD-MM-YYYY');
14 rows updated.

SQL> INSERT INTO SCOTT.ITEMS VALUES (1,'IRON');
1 row created.

SQL> INSERT INTO SCOTT.ITEMS VALUES (2,'COPPER');
1 row created.

SQL> INSERT INTO SCOTT.ITEMS VALUES (3,'GOLD');
1 row created.

SQL> INSERT INTO SCOTT.ITEMS VALUES (4,'SILVER');
1 row created.

SQL> COMMIT;
Commit complete.
  9. Add supplemental logging for the newly added tables: 
    GGSCI (prim1-ol6-112.localdomain) 4> DBLOGIN USERID GGATE_ADMIN@
DBORATEST
Password: 
Successfully logged into database.

GGSCI (prim1-ol6-112.localdomain) 5> ADD TRANDATA SCOTT.ITEMS

Logging of supplemental redo data enabled for table SCOTT.ITEMS.

GGSCI (prim1-ol6-112.localdomain) 6> ADD TRANDATA SCOTT.SALES  

Logging of supplemental redo data enabled for table SCOTT.SALES.
  10. Alter the Extract and Datapump processes to skip the changes generated by the Application Schema Patch: 
    GGSCI (prim1-ol6-112.localdomain) 7> ALTER EXTRACT EGGTEST1 BEGIN 
NOW
EXTRACT altered.

GGSCI (prim1-ol6-112.localdomain) 8> ALTER EXTRACT PGGTEST1 BEGIN 
NOW
EXTRACT altered.
  11. Start the Extract and Datapump in the source environment: 
    GGSCI (prim1-ol6-112.localdomain) 9> START EXTRACT *

Sending START request to MANAGER ...
EXTRACT EGGTEST1 starting

Sending START request to MANAGER ...
EXTRACT PGGTEST1 starting
  12. Start the Replicat process in the target environment: 
    GGSCI (stdby1-ol6-112.localdomain) 56> START REPLICAT RGGTEST1

Sending START request to MANAGER ...
REPLICAT RGGTEST1 starting

How it works…

The preceding steps cover a high level procedure that you can follow to modify the structure of the replicated tables in your GoldenGate configuration. Before you start to alter any processes or parameter file, you need to ensure that the applications are stopped and no user sessions in the database are modifying the data in the tables that you are replicating. Once the application is stopped, we check that all the redo data has been processed by GoldenGate processes and then stop. At this point we run the scripts that need to be run to make DDL changes to the database. This step needs to be run on both the source and target database as we will not be replicating these changes using GoldenGate. Once this is done, we alter the GoldenGate processes to start from the current time and start them.

There’s more…

Some of the assumptions made in the earlier procedure might not hold true for all environments. Let’s see what needs to be done in such cases where the environment does not satisfy these conditions:

Specific tables defined in GoldenGate parameter files

Unlike the earlier example, where the tables are defined in the parameter files using a schema qualifier for example SCOTT.*, if you have individual tables defined in the GoldenGateparameterfiles, you would need to modify the GoldenGate parameter files to add these newly created tables to include them in replication.

Individual table permissions granted to the GoldenGate Admin user

If you have granted table-specific permissions to the GoldenGate Admin user in the source and target environments, you would need to grant them on the newly added tables to allow the GoldenGate user to read their data in the source environment and also to apply the changes to these tables in the target environment.

Supplemental logging for modified tables without any keys

If you are adding or deleting any columns from the tables in the source database which do not have any primary/unique keys, you would then need to drop the existing supplemental log group and read them. This is because when there are no primary/unique keys in a table, GoldenGate adds all columns to the supplemental log group. This supplemental log group will have to be modified when the structure of the underlying table is modified.

Supplemental log groups with all columns for modified tables

In some cases, you would need to enable supplemental logging on all columns of the source tables that you are replicating. This is mostly applicable for consolidation replication topologies where all changes are captured and converted into INSERTs in the target environment, which usually is a Data warehouse. In such cases, you need to drop and read the supplemental logging on the tables in which you are adding or removing any columns.

Table structure changes in GoldenGate environments with different table definitions

In this recipe you will learn how to perform table structure changes in a replication environment where the table structures in the source and target environments are not similar.

Getting ready

For this recipe we are making the following assumptions:

  1. GoldenGate is set up to replicate only DML changes between the source and target environments.
  2. The application will be stopped for making schema changes in the source environment.
  3. The table structures in the source and target databases are not similar.
  4. The GoldenGate Admin user has been granted SELECT ANY TABLE in the source database and INSERT ANY TABLE, DELETE ANY TABLE, UPDATE ANY TABLE, SELECT ANY TABLE in the target database.
  5. The definition file was generated for the source schema and is configured in the replicat parameter file.

The schema changes performed in this recipe are as follows:

  1. Add a new column called DOB (DATE) to the EMP table.
  2. Modify the DNAME column in the DEPT table to VARCHAR(20).
  3. Add a new table called ITEMS to the SCOTT schema: 
    ITEMS

ITEMNO    NUMBER(5)        PRIMARY KEY
NAME     VARCHAR(20)
  4. Add a new table called SALES to the SCOTT schema: 
    SALES
INVOICENO    NUMBER(9)    PRIMARY KEY
ITEMNO       NUMBER(5)    FOREIGN KEY      ITEMS(ITEMNO)
EMPNO        NUMBER(4)    FOREIGN KEY      EMP(EMPNO)
  5. Load the values for the DOB column in the EMP table.
  6. Load a few records in the ITEMS table.

How to do it…

Here are the steps that you can follow to implement the previous schema changes in the source environment:

  1. Ensure that the application accessing the source database is stopped. There should not be any process modifying the data in the database.
  2. Once you have stopped the application, wait for 2 to 3 minutes so that all pending redo is processed by the GoldenGate extract.
  3. Check the latest timestamp read by the Extract and Datapump process, and ensure it is the current timestamp: 
    GGSCI (prim1-ol6-112.localdomain) 9> INFO EXTRACT EGGTEST1 

GGSCI (prim1-ol6-112.localdomain) 10> INFO EXTRACT *

EXTRACT    EGGTEST1  Last Started 2013-03-28 10:12  Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:07 ago)
Log Read Checkpoint  Oracle Redo Logs
         2013-03-28 10:16:06  Seqno 352, RBA 12574320
         SCN 0.11973456 (11973456)

EXTRACT    PGGTEST1  Last Started 2013-03-28 10:12   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:04 ago)
Log Read Checkpoint  File /u01/app/ggate/dirdat/st000010
          2013-03-28 10:15:43.000000  RBA 8450
  4. Stop the Extract and Datapump processes in the source environment: 
    GGSCI (prim1-ol6-112.localdomain) 1> STOP EXTRACT *

Sending STOP request to EXTRACT EGGTEST1 ...
Request processed.

Sending STOP request to EXTRACT PGGTEST1 ...
Request processed.
  5. Check the status of the Replicat process in the target environment and ensure that it has processed the timestamp noted in step 3: 
    GGSCI (stdby1-ol6-112.localdomain) 54> INFO REPLICAT *

REPLICAT   RGGTEST1  Last Started 2013-03-28 10:15   Status 
RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:04 ago)
Log Read Checkpoint  File ./dirdat/rt000062
           2013-03-28 10:15:04.950188  RBA 10039
  6. Stop the Replicat process in the target environment: 
    GGSCI (stdby1-ol6-112.localdomain) 48> STOP REPLICAT *

Sending STOP request to REPLICAT RGGTEST1 ...
Request processed.
  7. Apply the schema changes to the source database: 
    SQL> ALTER TABLE SCOTT.EMP ADD DOB DATE;
Table altered.

SQL> ALTER TABLE SCOTT.DEPT MODIFY DNAME VARCHAR(20);        
Table altered.

SQL> CREATE TABLE SCOTT.ITEMS (
ITEMNO NUMBER(5) PRIMARY KEY,
NAME VARCHAR(20));
Table created.

SQL> CREATE TABLE SCOTT.SALES (
INVOICENO NUMBER(9) PRIMARY KEY,
ITEMNO NUMBER(5) REFERENCES SCOTT.ITEMS(ITEMNO),
EMPNO NUMBER(4) REFERENCES SCOTT.EMP(EMPNO));
Table created.

SQL> UPDATE SCOTT.EMP SET DOB=TO_DATE('01-01-1980','DD-MM-YYYY');
14 rows updated.

SQL> INSERT INTO SCOTT.ITEMS VALUES (1,'IRON');
1 row created.

SQL> INSERT INTO SCOTT.ITEMS VALUES (2,'COPPER');
1 row created.

SQL> INSERT INTO SCOTT.ITEMS VALUES (3,'GOLD');
1 row created.

SQL> INSERT INTO SCOTT.ITEMS VALUES (4,'SILVER');
1 row created.

SQL> COMMIT;
Commit complete.
  8. Apply the schema changes to the target database: 
    SQL> ALTER TABLE SCOTT.EMP ADD DOB DATE;
Table altered.

SQL> ALTER TABLE SCOTT.DEPT MODIFY DNAME VARCHAR(20);
Table altered.

SQL> CREATE TABLE SCOTT.ITEMS (
ITEMNO NUMBER(5) PRIMARY KEY,
NAME VARCHAR(20));
Table created.


SQL> CREATE TABLE SCOTT.SALES (
INVOICENO NUMBER(9) PRIMARY KEY,
ITEMNO NUMBER(5) REFERENCES SCOTT.ITEMS(ITEMNO),
EMPNO NUMBER(4) REFERENCES SCOTT.EMP(EMPNO));
Table created.

SQL> UPDATE SCOTT.EMP SET DOB=TO_DATE('01-01-1980','DD-MM-YYYY');
14 rows updated.

SQL> INSERT INTO SCOTT.ITEMS VALUES (1,'IRON');
1 row created.

SQL> INSERT INTO SCOTT.ITEMS VALUES (2,'COPPER');
1 row created.

SQL> INSERT INTO SCOTT.ITEMS VALUES (3,'GOLD');
1 row created.

SQL> INSERT INTO SCOTT.ITEMS VALUES (4,'SILVER');
1 row created.

SQL> COMMIT;
Commit complete.
  9. Add supplemental logging for the newly added tables: 
    GGSCI (prim1-ol6-112.localdomain) 4> DBLOGIN USERID GGATE_ADMIN@DBORATEST
Password: 
Successfully logged into database.

GGSCI (prim1-ol6-112.localdomain) 5> ADD TRANDATA SCOTT.ITEMS

Logging of supplemental redo data enabled for table SCOTT.ITEMS.

GGSCI (prim1-ol6-112.localdomain) 6> ADD TRANDATA SCOTT.SALES  

Logging of supplemental redo data enabled for table SCOTT.SALES.
  10. Update the parameter file for generating definitions as follows: 
    vi $GG_HOME/dirprm/defs.prm

DEFSFILE ./dirdef/defs.def
USERID ggate_admin@dboratest, PASSWORD XXXX
TABLE SCOTT.EMP;
TABLE SCOTT.DEPT;
TABLE SCOTT.BONUS;
TABLE SCOTT.DUMMY;
TABLE SCOTT.SALGRADE;
TABLE SCOTT.ITEMS;
TABLE SCOTT.SALES;
  11. Generate the definitions in the source environment: 
    ./defgen paramfile ./dirprm/defs.prm
  12. Push the definitions file to the target server using scp: 
    scp ./dirdef/defs.def stdby1-ol6-112:/u01/app/ggate/dirdef/
  13. Edit the Extract and Datapump process parameter to include the newly created tables if you have specified individual table names in them.
  14. Alter the Extract and Datapump processes to skip the changes generated by the Application Schema Patch: 
    GGSCI (prim1-ol6-112.localdomain) 7> ALTER EXTRACT EGGTEST1 BEGIN
NOW
EXTRACT altered.

GGSCI (prim1-ol6-112.localdomain) 8> ALTER EXTRACT PGGTEST1 BEGIN 
NOW
EXTRACT altered.
  15. Start the Extract and Datapump in the source environment: 
    GGSCI (prim1-ol6-112.localdomain) 9> START EXTRACT *

Sending START request to MANAGER ...
EXTRACT EGGTEST1 starting

Sending START request to MANAGER ...
EXTRACT PGGTEST1 starting
  16. Edit the Replicat process parameter file to include the tables: 
    ./ggsci
EDIT PARAMS RGGTEST1
REPLICAT RGGTEST1
USERID GGATE_ADMIN@TGORTEST, PASSWORD GGATE_ADMIN
DISCARDFILE /u01/app/ggate/dirrpt/RGGTEST1.dsc,append,MEGABYTES 
500
SOURCEDEFS ./dirdef/defs.def
MAP SCOTT.BONUS, TARGET SCOTT.BONUS;
MAP SCOTT.SALGRADE, TARGET SCOTT.SALGRADE;
MAP SCOTT.DEPT, TARGET SCOTT.DEPT;
MAP SCOTT.DUMMY, TARGET SCOTT.DUMMY;
MAP SCOTT.EMP, TARGET SCOTT.EMP;
MAP SCOTT.EMP,TARGET SCOTT.EMP_DIFFCOL_ORDER;
MAP SCOTT.EMP, TARGET SCOTT.EMP_EXTRACOL, COLMAP(USEDEFAULTS,
     LAST_UPDATE_TIME = @DATENOW ());    
MAP SCOTT.SALES, TARGET SCOTT.SALES;
MAP SCOTT.ITEMS, TARGET SCOTT.ITEMS;
  17. Start the Replicat process in the target environment: 
    GGSCI (stdby1-ol6-112.localdomain) 56> START REPLICAT RGGTEST1

Sending START request to MANAGER ...
REPLICAT RGGTEST1 starting

How it works…

You can follow the previously mentioned procedure to apply any DDL changes to the tables in the source database. This procedure is valid for environments where existing table structures between the source and the target databases are not similar.

The key things to note in this method are:

  1. The changes should only be made when all the changes extracted by GoldenGate are applied to the target database, and the replication processes are stopped.
  2. Once the DDL changes have been performed in the source database, the definitions file needs to be regenerated.
  3. The changes that you are making to the table structures needs to be performed on both sides.

There’s more…

Some of the assumptions made in the earlier procedure might not hold true for all environments. Let’s see what needs to be done in cases where the environment does not satisfy these conditions:

Individual table permissions granted to the GoldenGate Admin user

If you have granted table-specific permissions to the GoldenGate Admin user in the source and target environments, you would need to grant them on the newly added tables to allow the GoldenGate user to read their data in the source environment and also to apply the changes to these tables in the target environment.

Supplemental logging for modified tables without any keys

If you are adding or deleting any columns from the tables in the source database which do not have any primary/unique keys, you would then need to drop the existing supplemental log group and read them. This is because when there are no primary/unique keys in a table, GoldenGate adds all columns to the supplemental log group. This supplemental log group will need to be modified when the structure of the underlying table is modified.

Supplemental log groups with all columns for modified tables

In some cases, you would need to enable supplemental logging on all columns of the source tables that you are replicating. This is mostly applicable for consolidation replication topologies where all changes are captured and converted into INSERTs in the target environment, which usually is a Data warehouse. In such cases, you need to drop and read the supplemental logging on the tables in which you are adding or removing any columns.

Packt

LEAVE A REPLY

Please enter your comment!
Please enter your name here