Shulou(Shulou.com)06/01 Report--

In the http://lqding.blog.51cto.com/9123978/1695162 article, we implemented the DML operation synchronization of the table.

We do the following tests

Execute the truncate of the table on the source side

SQL > truncate table tcustmer;Table truncated.

On the destination side, view the table data

SQL > select count (*) from tcustmer; COUNT (*)-2

The data is not synchronized.

How do we get Goldengate to synchronize DDL operations?

To support DDL synchronization, you need to make some settings on the source database so that you can track DDL operations. Includes the following content: Trigger, marker, and history table, a user role, and a variety of other database objects.

The first step is to configure the GLOBALS parameter to tell gg which schema to use to store DDL operations.

GGSCI (localhost.localdomain) 19 > edit params. / GLOBALS-- GoldenGate GLOBALS parameter file--GGSCHEMA GGDDL

two。 Use sqlplus to create ggddl users 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.

Turn off 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 (the installation directory of ogg, enter 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.

Enter the name of GGSCHEMA in the pop-up dialog box

Run ddl_setup.sql

SQL > @ ddl_setup.sql

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. 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 where is the user assigned to the GoldenGate processes.

Once the role is created, it is not meant to be assigned to the ggddl user, but to the schema used by Extract,Replicat and so on.

We configured system users before.

SQL > GRANT GGS_GGSUSER_ROLE TO system;Grant succeeded.

To enable ddl is to make trigger enable

SQL > @ ddl_enable.sqlTrigger altered.

Close Manager,Extract,Replicat

Source end

GGSCI (localhost.localdomain) 1 > stop Extract eorakkGGSCI (localhost.localdomain) 5 > stop mgr!

Destination end

GGSCI (localhost.localdomain) 1 > stop replicat rorakkSending STOP request to REPLICAT RORAKK... Request processed.GGSCI (localhost.localdomain) 2 > stop manager

Edit Extract parameters

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 line of DDL INCLUDE ALL based on the original

Edit Replicat parameters

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 line of DDL INCLUDE MAPPED based on the original

Start Manager, Extract, Replicat

Source end

GGSCI (localhost.localdomain) 6 > start managerManager started.GGSCI (localhost.localdomain) 7 > start extract eorakkSending START request to MANAGER... EXTRACT EORAKK starting

Destination end

GGSCI (localhost.localdomain) 5 > start mgrManager started.GGSCI (localhost.localdomain) 16 > start replicat rorakkSending START request to MANAGER... REPLICAT RORAKK starting

Validate data

Source end

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 end

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.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.