Goldengate synchronizes one-way DDL operations in Oracle for Oracle

Goldengate synchronizes one-way DDL operations in Oracle for Oracle
We will perform the following test on the source end to execute the table's truncate SQL> truncate Table tcustmer; table truncated. on the target end, check the table data SQL> select count (*) from tcustmer; COUNT (*) ---------- 2. How can we synchronize the data with Goldengate for DDL operations? To support DDL synchronization, you must make some settings on the source database to track DDL operations. It includes the following content: Trigger, marker, and history table, a user role, and various other database objects. First, you must configure the GLOBALS parameter to tell gg to use that schema to store DDL operations. GGSCI (localhost. localdomain) 19> edit params./GLOBALS -- GoldenGate GLOBALS parameter file -- ggschema ggddl 2. Use sqlplus to create a ggddl user and install ddl support

SQL> conn / as sysdbaConnected.SQL> create user ggddl identified by ggddl ;User created.SQL> grant connect,resource to ggddl;Grant succeeded.

 

Disable the data recycle bin Function

SQL> ALTER SYSTEM SET RECYCLEBIN = OFF scope=spfile;System altered.SQL> startup forceORACLE instance started.Total System Global Area  835104768 bytesFixed Size         2217952 bytesVariable Size        574621728 bytesDatabase Buffers     255852544 bytesRedo Buffers           2412544 bytesDatabase mounted.Database opened.

 

Run marker_setup. SQL (ogg installation directory, go to sqlplus)

[oracle@localhost ogg]$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.1.0 Production on Fri Sep 11 19:58:40 2015Copyright (c) 1982, 2009, Oracle.  All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> @marker_setup.sqlMarker setup scriptYou 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:GGDDLMarker setup table script complete, running verification script...Please enter the name of a schema for the GoldenGate database objects:Setting schema name to GGDDLMARKER TABLE-------------------------------OKMARKER SEQUENCE-------------------------------OKScript complete.

 

In the displayed dialog box, enter the GGSCHEMA name to run ddl_setup.sql1SQL> @ ddl_setup. SQL to run role_setup. SQL.

SQL> @role_setup.sqlGGS Role setup scriptThis script will drop and recreate the role GGS_GGSUSER_ROLETo 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:ggddlWrote file role_setup_set.txtPL/SQL procedure successfully completed.Role setup script completeGrant 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.

 

After creating a role, you do not need to assign this role to the ggddl user, but to the schema used by Extract, Replicat, and so on. We configured system user SQL> GRANT GGS_GGSUSER_ROLE TO system; Grant succeeded. to enable ddl, trigger enable SQL> @ ddl_enable. SQL Trigger altered. disable Manager, Extract, and Replicat source GGSCI (localhost. localdomain) 1> stop Extract eorakkGGSCI (localhost. localdomain) 5> stop mgr! Destination GGSCI (localhost. localdomain) 1> stop replicat roraksending STOP request to replicat rorakk... Request processed. GGSCI (localhost. localdomain) 2> stop manager edit Extract Parameter

GGSCI (localhost.localdomain) 5> edit params eorakk---- Change Capture parameter file to capture-- TCUSTMER and TCUSTORD Changes--EXTRACT EORAKKUSERID system, PASSWORD oracleRMTHOST 192.168.199.104, MGRPORT 7809EXTTRAIL ./dirdat/KKDDL INCLUDE ALLTABLE SCOTT.TCUSTMER;TABLE SCOTT.TCUSTORD;

 

Add a ddl include all line to edit the Replicat parameter.

GGSCI (localhost.localdomain) 4>  edit params rorakk---- Change Delivery parameter file to apply-- TCUSTMER and TCUSTORD Changes--REPLICAT RORAKKUSERID system, PASSWORD oracleHANDLECOLLISIONSASSUMETARGETDEFSDISCARDFILE ./dirrpt/RORAKK.DSC, PURGEDDL INCLUDE MAPPEDMAP scott.tcustmer, TARGET scott.tcustmer;MAP scott.tcustord, TARGET scott.tcustord;

 

Add a ddl include mapped start Manager, Extract, and Replicat source end GGSCI (localhost. localdomain) 6> start manager Manager started. GGSCI (localhost. localdomain) 7> start extract eorakk Sending START request to MANAGER... extract eorakk starting destination GGSCI (localhost. localdomain) 5> start mgr Manager started. GGSCI (localhost. localdomain) 16> start replicat rorakk Sending START request to MANAGER... replicat rorakk starting

SQL> truncate table tcustmer;Table truncated.SQL> desc tcustmer  Name                     Null?    Type ----------------------------------------- -------- ---------------------------- CUST_CODE                NOT NULL VARCHAR2(4) NAME                          VARCHAR2(30) CITY                          VARCHAR2(20) STATE                         CHAR(2)SQL> alter table tcustmer add state_desc varchar2(30);Table altered.

 

Destination

SQL> select * from tcustmer;no rows selectedSQL> desc tcustmer Name                     Null?    Type ----------------------------------------- -------- ---------------------------- CUST_CODE                NOT NULL VARCHAR2(4) NAME                          VARCHAR2(30) CITY                          VARCHAR2(20) STATE                         CHAR(2) STATE_DESC                        VARCHAR2(30)

 

The data has been synchronized.