DDL Replication Configuration of Oracle GoldenGate 11g

DDL Replication Configuration of Oracle GoldenGate 11g

We have already seen in my previous post How to install Oracle GoldenGate 11g and configure DML in it. In this post, let us continue with the same set up by configuring the DDL replication with Oracle GoldenGate 11g.

The Details are given below.,


PRIMARY SERVER
OGG1 – GGDB1
SECONDARY SERVER OGG2 – GGDB2
ORACLE VERSION 11.2.0.2.0
ORACLE GOLDENGATE VERSION 11.2.1.0.1
OS VERSION IBM AIX6.1

 

PRIMARY SERVER –  RMDB SECONDARY SERVER – CLONEDB
MANAGER PROCESSPORT 7878 MANAGER PROCESSPORT 7879
EXTRACT PROCESS – EXT1EXTRACT EXT1

USERID ggadmin, PASSWORD oracle

DDL INCLUDE ALL;

DDLOPTIONS ADDTRANDATA, REPORT

EXTTRAIL /vol1/ggate/dirdat/et

TRANLOGOPTIONS ASMUSER SYS@+ASM, ASMPASSWORD oracle

TABLE scott.*;

REPLICAT PROCESS – REP1REPLICAT REP1

USERID ggadmin, PASSWORD oracle

DDL INCLUDE MAPPED

DDLOPTIONS REPORT

DDLERROR DEFAULT IGNORE RETRYOP

HANDLECOLLISIONS

ASSUMETARGETDEFS

DISCARDFILE /vol1/ggate/dirrpt/REP1.DSC, PURGE

MAP scott.*, TARGET scott.*;

 

DATAPUMP PROCESS – DPMP1

EXTRACT DPMP1

PASSTHRU

RMTHOST GGDB2, MGRPORT 7879

RMTTRAIL /vol1/ggate/dirdat/et

TABLE scott.*;

CHECKPOINTTABLE  – ./GLOBALS

GGSCHEMA ggadmin

CHECKPOINTTABLE  – ./GLOBALS

CHECKPOINTTABLE ggadmin.ggschkpt

GGSCHEMA ggadmin

 

 

If you are going to start a fresh installation, then check the

link http://www.oracle-scn.com/installation-and-dml-replication-configuration-of-oracle-goldengate-11g/

 

Let us continue from the Previous post. Now is the Step 5. Request to follow the below to configure the DDL Replication.

 

STEP 5 BEGIN —> PRIMARY

BEFORE FOLLOWING THE DDL STEPS PERFORM THE BELOW ONE.,

 

OGG1:/vol1/ggate /> ./ggsci

Oracle GoldenGate Command Interpreter for Oracle

Version 10.4.0.19 Build 002

Linux, x64, 64bit (optimized), Oracle 11 on Sep 17 2009 23:51:28

Copyright (C) 1995, 2009, Oracle and/or its affiliates.  All rights reserved.

 

GGSCI (OGG1) 2> DBLOGIN USERID ggadmin, PASSWORD oracle

Successfully logged into database.

 

GGSCI (OGG1) 3> EDIT PARAMS ./GLOBALS

GGSCHEMA ggadmin

 

GGSCI (OGG1) 4> exit

In the previous post for configuration of DML we created a CHECKPOINTTABLE and entered the details in ./GLOBALS. Now we have to enter the same in the source ./GLOBALS.

 

STOP MANAGER AND BOTH EXTRACT

OGG1:/vol1/ggate />./ggsciOracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230
AIX 5L, ppc, 64bit (optimized), Oracle 11g on Apr 23 2012 05:03:51

Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.

GGSCI (OGG1) 1> INFO ALL

Program       Status          Group      Lag        Time Since Chkpt

MANAGER   RUNNING
EXTRACT    RUNNING    EXT1       00:00:00    00:00:02
EXTRACT    RUNNING    DPMP1    00:00:00    00:00:00

GGSCI (OGG1) 2> STOP EXTRACT EXT1Sending STOP request to EXTRACT EXT1 …
Request processed.

GGSCI (OGG1) 4> STOP EXTRACT DPMP1Sending STOP request to EXTRACT DPMP1 …
Request processed.

GGSCI (OGG1) 6> STOP MGR
Manager process is required by other GGS processes.
Are you sure you want to stop it (y/n)? ySending STOP request to MANAGER …
Request processed.
Manager stopped.

GGSCI (OGG1) 7> INFO ALLProgram       Status          Group      Lag        Time Since Chkpt

MANAGER   RUNNING
EXTRACT    RUNNING    EXT1       00:00:00    00:00:41
EXTRACT    RUNNING    DPMP1    00:00:00    00:00:24

DOWN THE DATABASE

SQL> shut immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

START THE DATABASE

SQL> startup

ORACLE instance started.

 

Total System Global Area 1553305600 bytes

Fixed Size          2213656 bytes

Variable Size          989858024 bytes

Database Buffers       553648128 bytes

Redo Buffers             7585792 bytes

Database mounted.

Database opened.

SQL>

SQL> show parameter recyclebinNAME                  TYPE                VALUE
—————–     —————–    —————–
recyclebin           string                 OFF
SQL>

Start the Manager process alone in the Primary and Secondary servers.

NOTE : YOU HAVE TO START THE MANAGER IN NEW SESSION

You should open a new GGSCI prompt.

 

CHECK BOTH EXTRACT (EXT1 AND DPMP1) AND REPLICAT (REP1) ARE STOPED

 

GGSCI (OGG1) 1> START MGR
Manager started.GGSCI (OGG1) 2> INFO ALL

Program      Status           Group       Lag          Time Since Chkpt

MANAGER  RUNNING
EXTRACT   STOPPED     EXT1       00:00:00     00:04:06
EXTRACT   STOPPED     DPMP1    00:00:00     00:03:50

 

The below scripts are needed to be run to enable the DDL in Oracle GoldenGate.,

marker_setup.sql
ddl_setup.sql
role_setup.sql
ddl_enable.sql
ddl_pin.sql ggadmin

 

FIRST SCRIPT TO RUN

GO TO THE LOCATION IN WHICH YOU HAVE UNZIP THE SOFTWARE

OGG1:/usr2/oracle />cd /vol1/ggateOGG1:/vol1/ggate /> pwd
/vol1/ggate

From this location give.,

OGG1:/vol1/ggate />sqlplus /nolog

LOGIN AS THE USER WHICH HAVE SYSDBA PERMISSION

SYS (OR) GOLDENGATE USER AS SYSDBA

SQL> @marker_setup.sql
Marker setup scriptYou 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:- – – – – – > ggadmin

Marker setup table script complete, running verification script…
Please enter the name of a schema for the GoldenGate database objects:
Setting schema name to ggadmin

MARKER TABLE
——————————-
OK

MARKER SEQUENCE
——————————-
OK

Script complete.

 

SECOND SCRIPT TO RUN

SQL> @ddl_setup.sqlGoldenGate 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 GoldenGate database objects.
NOTE: The schema must be created prior to running this script.
NOTE: On Oracle 10g and up, system recycle bin must be disabled.
NOTE: Stop all DDL replication before starting this installation.

Enter GoldenGate schema name:ggadmin

You will be prompted for the mode of installation.
To install or reinstall DDL replication, enter INITIALSETUP
To upgrade DDL replication, enter NORMAL
Enter mode of installation:INITIALSETUP

Working, please wait …
Spooling to file ddl_setup_spool.txt

Using ggadmin as a GoldenGate schema name, INITIALSETUP as a mode of installation.

Working, please wait …

RECYCLEBIN must be empty.
This installation will purge RECYCLEBIN for all users.
To proceed, enter yes. To stop installation, enter no.

Enter yes or no:yes

DDL replication setup script complete, running verification script…
Please enter the name of a schema for the GoldenGate database objects:
Setting schema name to ggadmin

DDLORA_GETTABLESPACESIZE STATUS:

Line/pos Error
————————————————————-
No errors No errors

CLEAR_TRACE STATUS:

Line/pos Error
————————————————————–
No errors No errors

CREATE_TRACE STATUS:

Line/pos Error
————————————————————-
No errors No errors

TRACE_PUT_LINE STATUS:

Line/pos Error
————————————————————–
No errors No errors

INITIAL_SETUP STATUS:

Line/pos Error
———- —————————————————————–
No errors No errors

DDLVERSIONSPECIFIC PACKAGE STATUS:

Line/pos Error
———- —————————————————————–
No errors No errors

DDLREPLICATION PACKAGE STATUS:

Line/pos Error
———- —————————————————————–
No errors No errors

DDLREPLICATION PACKAGE BODY STATUS:

Line/pos Error
———- —————————————————————–
No errors No errors

DDL HISTORY TABLE
———————————–
OK

DDL HISTORY TABLE(1)
———————————–
OK

DDL DUMP TABLES
———————————–
OK

DDL DUMP COLUMNS
———————————–
OK

DDL DUMP LOG GROUPS
———————————–
OK

DDL DUMP PARTITIONS
———————————–
OK

DDL DUMP PRIMARY KEYS
———————————–
OK

DDL SEQUENCE
———————————–
OK

GGS_TEMP_COLS
———————————–
OK

GGS_TEMP_UK
———————————–
OK

DDL TRIGGER CODE STATUS:

Line/pos Error
———- —————————————————————–
No errors No errors

DDL TRIGGER INSTALL STATUS
———————————–
OK

DDL TRIGGER RUNNING STATUS
———————————–
ENABLED

STAYMETADATA IN TRIGGER
———————————–
OFF

DDL TRIGGER SQL TRACING
———————————–
0

DDL TRIGGER TRACE LEVEL
———————————–
0

LOCATION OF DDL TRACE FILE
——————————————————————————–
/oracle/diag/rdbms/GGDB1/GGDB1/trace/ggs_ddl_trace.log

Analyzing installation status…

STATUS OF DDL REPLICATION
——————————————————————————–
SUCCESSFUL installation of DDL Replication software components

Script complete.

THIRD SCRIPT TO RUN

SQL> @role_setup.sqlGGS 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:ggadmin
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> conn sys/oracle@GGDB1 as sysdba
Connected.
SQL> GRANT GGS_GGSUSER_ROLE TO GGADMIN;

Grant succeeded.

FOURTH & FIFTH SCRIPT TO RUN

SQL> @ddl_enable.sqlTrigger altered.

SQL> @ddl_pin.sql ggadmin;PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.

TO CHECK STATUS OF THE SCRIPT WE RUN BEFORE

To check the status, please run the below scripts.,

marker_status.sql

ddl_status.sql

 

SQL> @marker_status.sql
Please enter the name of a schema for the GoldenGate database objects:
ggadmin
Setting schema name to ggadminMARKER TABLE
——————————-
OK

MARKER SEQUENCE
——————————-
OK
SQL>

SQL> @ddl_status.sql
Please enter the name of a schema for the GoldenGate database objects:
ggadmin
Setting schema name to ggadminDDLORA_GETTABLESPACESIZE STATUS:

Line/pos Error
———- —————————————————————–
No errors No errors

CLEAR_TRACE STATUS:

Line/pos Error
———- —————————————————————–
No errors No errors

CREATE_TRACE STATUS:

Line/pos Error
———- —————————————————————–
No errors No errors

TRACE_PUT_LINE STATUS:

Line/pos Error
———- —————————————————————–
No errors No errors

INITIAL_SETUP STATUS:

Line/pos Error
———- —————————————————————–
No errors No errors

DDLVERSIONSPECIFIC PACKAGE STATUS:

Line/pos Error
———- —————————————————————–
No errors No errors

DDLREPLICATION PACKAGE STATUS:

Line/pos Error
———- —————————————————————–
No errors No errors

DDLREPLICATION PACKAGE BODY STATUS:

Line/pos Error
———- —————————————————————–
No errors No errors

DDL HISTORY TABLE
———————————–
OK

DDL HISTORY TABLE(1)
———————————–
OK

DDL DUMP TABLES
———————————–
OK

DDL DUMP COLUMNS
———————————–
OK

DDL DUMP LOG GROUPS
———————————–
OK

DDL DUMP PARTITIONS
———————————–
OK

DDL DUMP PRIMARY KEYS
———————————–
OK

DDL SEQUENCE
———————————–
OK

GGS_TEMP_COLS
———————————–
OK

GGS_TEMP_UK
———————————–
OK

DDL TRIGGER CODE STATUS:

Line/pos Error
———- —————————————————————–
No errors No errors

DDL TRIGGER INSTALL STATUS
———————————–
OK

DDL TRIGGER RUNNING STATUS
———————————–
ENABLED

STAYMETADATA IN TRIGGER
———————————–
OFF

DDL TRIGGER SQL TRACING
———————————–
0

DDL TRIGGER TRACE LEVEL
———————————–
0

LOCATION OF DDL TRACE FILE
——————————————————————————–
/oracle/diag/rdbms/GGDB1/GGDB1/trace/ggs_ddl_trace.log

Analyzing installation status…

STATUS OF DDL REPLICATION
——————————————————————————–
SUCCESSFUL installation of DDL Replication software components
SQL>

CHANGES NEED IN EXTRACT

There are certain parameters to be added to the EXTRACT Process. Adding these parameters to the EXTRACT Process starts / enables DDL Operations in GoldenGate.

 

Login to the ggsci prompt and make the necessary modifications.

OGG1:/vol1/ggate />./ggsciOracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230
AIX 5L, ppc, 64bit (optimized), Oracle 11g on Apr 23 2012 05:03:51

Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.

GGSCI (OGG1) 1> INFO ALL

Program          Status          Group      Lag          Time Since Chkpt

MANAGER      RUNNING
EXTRACT       STOPPED    EXT1      00:00:00    06:01:18
EXTRACT       STOPPED    DPMP1   00:00:00    06:01:01

GGSCI (OGG1) 2> EDIT PARAMS EXT1EXTRACT EXT1
USERID ggadmin, PASSWORD oracle
DDL INCLUDE ALL;
DDLOPTIONS ADDTRANDATA, REPORT
EXTTRAIL /vol1/ggate/dirdat/et
TRANLOGOPTIONS ASMUSER SYS@+ASM, ASMPASSWORD oracle
TABLE scott.*;

DDL INCLUDE ALL – Include DDL Operations for all objects.

DDLOPTIONS ADDTRANDATA, REPORT – Whenever a new table is created in the schema, automatically supplemental log data will enabled for that table.

BEFORE STARTING THE EXTRACT PROCESS EDIT THE REPLICAT PROCESSES IN SECONDARY AND

TAKE EXPORT OF THE TABLEs from ggadmin(Goldengate Schema) IN PRIMARY.

(These tables are created while running the 5 scripts)

 

RUN role_setup.sql in SECONDARY (GO TO STEP 6)

IMPORT ALL THE TABLES to GGADMIN(Goldengate Schema) TO SECONDARY.

GGSCI (OGG1) 3> START EXTRACT EXT1
Sending START request to MANAGER …
EXTRACT EXT1 startingGGSCI (OGG1) 4> START EXTRACT DPMP1
Sending START request to MANAGER …
EXTRACT DPMP1 starting

GGSCI (OGG1) 5> INFO ALL
Program       Status         Group     Lag           Time Since ChkptMANAGER    RUNNING
EXTRACT     RUNNING   EXT1      00:00:00    00:00:08
EXTRACT     RUNNING   DPMP1   00:00:00    06:08:50

 

STEP 5 END ––> PRIMARY

 

STEP 6 BEGIN —> SECONDARY

 

SCRIPT TO RUN —> THIS ALONE IS ENOUGH

 

@role_setup.sql

 

The above script needs to be run in the Secondary Server. This script assigns the ADMIN ROLE to the GoldenGate user ggadmin.

 

Connect as sysdba and run the script.,

SQL> conn sys/oracle@GGDB2 as sysdba

Connected.

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:ggadmin

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> conn sys/oracle@GGDB2 as sysdba
Connected.SQL> GRANT GGS_GGSUSER_ROLE TO GGADMIN;
Grant succeeded.

IMPORT ALL THE TABLES THAT STARTS WITH GGS_ GGADMIN(Goldengate Schema) TO SECONDARY.

 

TO PERFORM DDL OPERATION

CHANGES IN REPLICAT – Similar to the EXTRACT Process, REPLICAT process also required some changes (some parameters to be added for DDL Operations).

 

Now Login to the GGSCI prompt.,

GGDB2:/vol1/ggate />./ggsci

 

Oracle GoldenGate Command Interpreter for Oracle

Version 10.4.0.19 Build 002

Linux, x64, 64bit (optimized), Oracle 11 on Sep 17 2009 23:51:28

 

Copyright (C) 1995, 2009, Oracle and/or its affiliates.  All rights reserved.

GGSCI (GGDB2) 2> EDIT PARAMS REP1

 

REPLICAT REP1

USERID ggadmin, PASSWORD oracle

DDL INCLUDE MAPPED

DDLOPTIONS REPORT

DDLERROR DEFAULT IGNORE RETRYOP

HANDLECOLLISIONS

ASSUMETARGETDEFS

DISCARDFILE /vol1/ggate/dirrpt/REP1.DSC, PURGE

MAP scott.*, TARGET scott.*;

DDL INLCUDE MAPPED / ALL – All types of DDL operations are enabled for all objects.

For egs., we can enable only “create” or “alter” separately.

DDLOPTIONS REPORT – Creates and Manages REPORTS. When issuing VEIW REPORT <Group_Name> we can see the complete report of the DDL operations taken place.

DDLERROR DEFAULT IGNORE RETRYOP – When an error occurs in the DDL Operation, GoldenGate just IGNOREs it and retries the Operation. We can set the retry options in numbers like 3,4 and interval time in seconds.

GoldenGate process retries the operation for 3 times in a particular interval of time. If the operation is still not cleared the respective process will ABEND.

 

GGSCI (GGDB2) 3> INFO ALLProgram      Status        Group    Lag          Time Since Chkpt

MANAGER  RUNNING
REPLICAT  STOPPED  REP1    00:00:00   06:01:38

Now You can Start the Extract Processes EXT1 and DPMP1 in PRIMARY and followed by Replicat REP1 in SECONDARY

GGSCI (GGDB2) 4> START REPLICAT REP1Sending START request to MANAGER …
REPLICAT REP1 starting

GGSCI (GGDB2) 5> INFO ALLProgram        Status         Group   Lag       Time Since Chkpt

MANAGER    RUNNING
REPLICAT    RUNNING   REP1    01:08:    07 00:00:04

 

STEP 6 END —> SECONDARY

 

Now you can check if the DDL Replication is happening or not. Perform some DDL operations like create table, alter table etc in source and check if it is replicating it to the secondary database.

 

Hope you understood clearly on configuring the DDL Replication using Oracle GoldenGate between two databases.

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *