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 https://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
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
Request processed.
Request processed.
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.
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>
—————– —————– —————–
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
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
/vol1/ggate
From this location give.,
LOGIN AS THE USER WHICH HAVE SYSDBA PERMISSION
SYS (OR) GOLDENGATE USER AS SYSDBA
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
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
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
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
Please enter the name of a schema for the GoldenGate database objects:
ggadmin
Setting schema name to ggadminMARKER TABLE
——————————-
OK
MARKER SEQUENCE
——————————-
OK
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.
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
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.
Sending START request to MANAGER …
EXTRACT EXT1 startingGGSCI (OGG1) 4> START EXTRACT DPMP1
Sending START request to MANAGER …
EXTRACT DPMP1 starting
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.
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.
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
REPLICAT REP1 starting
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.
Loading ...
Total Users : 1822657