Build a Goldengate bidirectional replication environment from Oracle to Oracle

Build a Goldengate bidirectional replication environment from Oracle to Oracle

Objective: To build a Goldengate two-way replication environment (DDL + DML supported) from Oracle to Oracle ).

The database redo log is analyzed to apply the acquired data to the target end for data synchronization. Therefore, the source database must be in archive mode, and additional logs and forced logs must be enabled.

Archive mode, additional logs, and forced logs

-- View

SQL> select log_mode, supplemental_log_data_min, force_logging from v $ database;

LOG_MODE SUPPLEME

-----------------------

ARCHIVELOG NO

-- Modify

(1) archivelog

SQL> shutdown immediate

SQL> startup mount

SQL> alter database archivelog;

SQL> alter database open;

(2) force logging

SQL> alterdatabase force logging;

(3) supplemental log data

SQL> alterdatabase add supplemental log data;

(2) Disable Recycle Bin

If DDL support is enabled, you must disable recycle bin. The official website is explained as follows:

If the recyclebin is enabled, the Oracle GoldenGate DDL trigger session restarts es implicitrecycle bin DDL operations that cause the trigger to fail.

Oracle 11g:

SQL> alter system set recyclebin = offscope = spfile;

System altered.

If the database is 10 Gb, You need to disable recyclebin and restart it; or manually purge recyclebin.

(3) create a user to store DDL information and grant permissions

SQL> create user ggtest identified by oracle default tablespace users temporary tablespace temp;

User created.

SQL> grant connect, resource to ggtest;

Grant succeeded.

SQL> grant execute on utl_file to ggtest;

Grant succeeded.

Exit all sessions using Oracle, and then run the following script for users with SYSDBA permissions:

Gg1:/u01/ggate> echo $ ggate

/U01/ggate

-- Enter the GG directory and call the script:

Gg1:/home/oracle> cd $ ggate

Gg1:/u01/ggate> sqlplus/as sysdba;

SQL * Plus: Release 11.2.0.3.0 Production onTue Nov 8 19:41:58 2011

Copyright (c) 1982,201 1, Oracle. All rights reserved.

Connected:

Oracle Database 11g Enterprise EditionRelease 11.2.0.3.0-64bit Production

With the Partitioning, OLAP, Data Miningand Real Application Testing options

-- Script 1:

SQL> @ marker_setup. SQL;

Marker setup script

You will be prompted for the name of aschema for the GoldenGate database objects.

NOTE: The schema must be created prior torunning this script.

NOTE: Stop all DDL replication beforestarting this installation.

-- Enter the username we created earlier:

Enter GoldenGate schema name: ggtest

Marker setup table script complete, runningverification script...

Please enter the name of a schema for theGoldenGate database objects:

Setting schema name to ggtest

MARKER TABLE

-------------------------------

OK

MARKER SEQUENCE

-------------------------------

OK

Script complete.

-- Script 2:

SQL> @ ddl_setup. SQL;

GoldenGate DDL Replication setup script

Verifying that current user has privilegesto install DDL Replication...

You will be prompted for the name of aschema for the GoldenGate database objects.

NOTE: For an Oracle 10g source, the systemrecycle bin must be disabled. For Oracle 11g and later, it can be enabled.

-- Note that the recycle bin must be disabled in 10 Gb. In Versions later than 11 GB, you do not need to disable it.

NOTE: The schema must be created prior torunning this script.

NOTE: Stop all DDL replication beforestarting this installation.

-- Prompt the user who entered GG:

Enter GoldenGate schema name: ggtest

You will be prompted for the mode ofinstallation.

To install or reinstall DDL replication, enter INITIALSETUP

To upgrade DDL replication, enter NORMAL

-- Here Let's select the installation mode: install and reinstall select INITIALSETUP

Enter mode of installation: INITIALSETUP

Working, please wait...

Spooling to file ddl_setup_spool.txt

Checking for sessions that are holdinglocks on Oracle Golden Gate metadata tables...

Check complete.

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

Working, please wait...

DDL replication setup script complete, running verification script...

Please enter the name of a schema for theGoldenGate database objects:

Setting schema name to ggtest

DDLORA_GETTABLESPACESIZE STATUS:

......

STATUS OF DDL REPLICATION

Bytes -------------------------------------------------------------------------------------------------------

SUCCESSFUL installation of DDL Replicationsoftware components

Script complete.

-- Script 3:

SQL> @ role_setup. SQL;

GGS Role setup script

This script will drop and recreate the roleGGS_GGSUSER_ROLE

To use a different role name, quit thisscript and then edit the params. SQL script to change the gg_role parameter tothe preferred name. (Do not run the script .)

You will be prompted for the name of aschema for the GoldenGate database objects.

NOTE: The schema must be created prior torunning this script.

NOTE: Stop all DDL replication beforestarting this installation.

-- Enter the GG user name as follows:

Enter GoldenGate schema name: ggtest

Wrote file role_setup_set.txt

PL/SQL procedure successfully completed.

Role setup script complete

Grant this role to each user assigned tothe Extract, GGSCI, and Manager processes, by using the following SQL command:

-- We are prompted to grant permissions to relevant users:

GRANT GGS_GGSUSER_ROLE TO <loggedUser>

Where <loggedUser> is the userassigned to the GoldenGate processes.

-- Script 4: grant permissions

SQL> grant GGS_GGSUSER_ROLE to ggtest;

Grant succeeded.

-- Script 5:

SQL> @ ddl_enable. SQL;

Trigger altered.

Note that all the tables created by the script use the default names. You can also modify the default names of these tables.

Iii. Test

Note:

(1) The user name and Object Name of the target database can be different from that of the source database. The key is that the configuration file must be correctly matched.

(2) configure the source and target tnsnames to maintain interconnection. (Remember to start lsnrctl)

Gg1:

SQL> create user ssgg identified by oracle default tablespace users temporary tablespace temp;

User created.

SQL> grant connect, resource, dba to ssgg;

Grant succeeded.

Gg2:

SQL> create user ttgg identified by oracle default tablespace users temporary

Tablespace temp;

User created.

SQL> grant connect, resource, dba to ttgg;

Grant succeeded.

Parameter settings

Configure gg1 and add the checkpoint table:

GGSCI (gg1) 1> dblogin userid ggtest, password oracle

Successfully logged into database.

GGSCI (gg1) 2> view params./GLOBAL

Ggschema ggtest

Checkpointtable ggtest. checkpoint

GGSCI (gg1) 27> view params mgr

PORT 7809 -- specify the Management PORT

Dynamicportlist 7810-7900 -- Dynamic port list. When the specified port is unavailable, the management process automatically selects an available port. Up to 256 ports can be specified

Autostart er * -- automatically enable all extract and replicat process when mgr is enabled

Autorestart er *, retries 5, waitminutes 2 -- extract and replicat processes fail mgr automatically start

The parameter is restarted every 2 minutes.

1. t o c o n f I g u r e t h e p r I m a r y e x t r a c t g o u p

GGSCI (gg1) 10> add extract ext01, tranlog, begin now

EXTRACT added.

GGSCI (gg1) 13> add exttrail/u01/ggate/dirdat/sd, extract ext01

EXTTRAIL added.

GGSCI (gg1) 28> view params ext01

Extract ext01 -- extract the name of the Process Group

Userid ggtest, password oracle -- synchronize users

Exttrail/u01/ggate/dirdat/sd-- The Directory and identifier of the trail file (similar to the sd * file). The name must be 2 characters long.

Tranlogoptions excludeuser ggtest -- avoid the emergence of cyclic Replication

DDL INCLUDE ALL

DDLOPTIONS ADDTRANDATA

Table ssgg. *; -- synchronized table or all tables under the user

When you need to synchronize the DDL operations of all users outdoors except Goldengate and Oracle database, you can add the following content to the source Master extraction process.

Ddl include all

Then add the DDL error processing statement to the target Replicat process parameter file.

2. t o c o n f I g u r e t h e d a t a p u m p

GGSCI (gg1) 19> add extract p01, exttrailsource/u01/ggate/dirdat/sd, begin now

EXTRACT added.

GGSCI (gg1) 20> add rmttrail/u01/ggate/dirdat/pd, extract p01

RMTTRAIL added.

GGSCI (gg1) 29> view params p01

Extract p01

Userid ggtest, password oracle

Rmthost 192.168.50.231, mgrport 7809 -- specify the remote IP address and MGR Port

Rmttrail/u01/ggate/dirdat/pd

Table ssgg .*;

3. t o c o n f I g u r e t h e r e p l I c a t g r o u p

GGSCI (gg1) 3> add replicat r02, exttrail/u01/ggate/dirdat/dp, begin now

ERROR: No checkpoint table specified for add replicat.

GGSCI (gg1) 4> add replicat r02, exttrail/u01/ggate/dirdat/dp, checkpointtable ggtest. checkpoint

REPLICAT added.

GGSCI (gg1) 30> view params r02

Replicat r02

Assumetargetdefs -- defines the target table and the source table using the MAP syntax to have the same column structure. When a hotspot occurs, you can directly query the source definition file without viewing the source structure.

View

Userid ggtest, password oracle

DISCARDFILE/u01/ggate/dirdat/sdisc. dsc, append, megabytes 100

DDL INCLUDE MAPPED

DDLERROR DEFAULT IGNORE RETRYOP

Map ttgg. *, target ssgg .*;

Start all processes:

GGSCI (gg1) 3> start mgr

Manager started.

GGSCI (gg1) 4> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

Extract running EXT01 00:00:00 00:00:07

Extract running P01 00:00:00 00:00:01

Replicat running R02 00:00:00 00:00:08

Configure gg2 and add the checkpoint table:

GGSCI (gg2) 1> dblogin userid ggtest, password oracle

Successfully logged into database.

GGSCI (gg2) 25> view params./GLOBAL

Ggschema ggtest

Checkpointtable ggtest. checkpoint

GGSCI (gg2) 29> view params mgr

PORT 7809

Dynamicportlist 7810-7900

Autostart er *

Autorestart er *, retries 5, waitminutes 2

C o n f I g u r a t I o n f r o m s e c o u n d a r y s t e m t o p r I m r y s t e m

1. t o c o n f I g u r e t h e p r I m a r y e x t r a c t g r o u p

GGSCI (gg2) 12> add extract ext02, tranlog, begin now

EXTRACT added.

GGSCI (gg2) 13> add exttrail/u01/ggate/dirdat/td, extract ext02

EXTTRAIL added.

GGSCI (gg2) 26> view params ext02

Extract ext02

Userid ggtest, password oracle

Exttrail/u01/ggate/dirdat/td

Tranlogoptions excludeuser ggtest

DDL INCLUDE ALL

DDLOPTIONS ADDTRANDATA

Table ttgg .*;

2. t o c o n f I g u r e t h e p u m p

GGSCI (gg2) 17> add extract p02, exttrailsource/u01/ggate/dirdat/td, begin now

EXTRACT added.

GGSCI (gg2) 19> add rmttrail/u01/ggate/dirdat/dp, extract p02

RMTTRAIL added.

GGSCI (gg2) 27> view params p02

Extract p02

Userid ggtest, password oracle

Rmthost 192.168.50.230, mgrport 7809

Rmttrail/u01/ggate/dirdat/dp

Table ttgg .*;

3. t o c o n f I g u r e t h e r e p l I c a t g r o u p

GGSCI (gg2) 7> add replicat r01, exttrail/u01/ggate/dirdat/pd, begin now

ERROR: No checkpoint table specified for add replicat.

GGSCI (gg2) 8> add replicat r01, exttrail/u01/ggate/dirdat/pd, checkpointtable ggtest. checkpoint;

REPLICAT added.

GGSCI (gg2) 28> view params r01

Replicat r01

Assumetargetdefs

Userid ggtest, password oracle

DISCARDFILE/u01/ggate/dirdat/sdisc. dsc, append, megabytes 100

DISCARDFILE cannot be directly installed in the/u01/ggate/installation directory, and the process may not start.

DDL INCLUDE MAPPED

DDLERROR DEFAULT IGNORE RETRYOP

Map ssgg. *, target ttgg .*;

Start all processes:

GGSCI (gg2) 3> start mgr

Manager started.

GGSCI (gg2) 4> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

Extract running EXT02 00:00:00 00:00:03

Extract running P02 00:00:00 00:00:07

Replicat running R01 00:00:14

Data synchronization test:

Gg1:

SQL> conn ssgg/oracle

Connected.

SQL> select * from tab;

TNAME TABTYPE CLUSTERID

-----------------------------------------------

SSGG TABLE

TTGG TABLE

SQL> create table test as select * from ssgg;

Table created.

SQL> select * from test;

ID

----------

1

5

SQL> commit;

Commit complete.

Then check gg2 to see if the data is synchronized.

Gg2:

SQL> conn ttgg/oracle

Connected.

SQL> select * from tab;

TNAME TABTYPE CLUSTERID

-----------------------------------------------

SSGG TABLE

TTGG TABLE

SQL>/

TNAME TABTYPE CLUSTERID

-----------------------------------------------

SSGG TABLE

TEST TABLE

TTGG TABLE

SQL> insert into test values (10 );

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test;

ID

----------

1

5

10

Return gg1 to check whether the data is synchronized:

SQL> host hostname

Gg1

SQL> select * from test;

ID

----------

1

5

10

So far, two-way data synchronization from Oracle to Oracle is successful (DML + DDL ).

Bidirectional replication is generally used in applications in a dual-service center environment. Two-way replication (DDL + DML) faces many problems, including the following:

1. What if the two databases update the same record at the same time? Adjust Business Rules

2. What should I do if the network fails? Supports resumable upload

3. How can I fix the problem if the data is not synchronized? Export source and import destination

Summary:

Two-way replication is difficult to avoid data conflicts. To solve this problem, you must adjust the business:

A) only conduct business in one segment

B) different services are carried out on both ends, involving different datasets

C) the two ends carry out the same business, but the data is differentiated based on the region or other conditions. The two ends do not operate on the same data

Supplement:

You can use rowid in oracle to locate a record. However, the target database may be different from the source database. Therefore, you cannot use rowid to determine the logical changes of the source database, then the supplemental log is appended to the performance stage. After the additional log feature is enabled for the database, oracle will append the columns with unique identifiers to the redo log for the modification operation on the source end. In this way, the target database can know what specific changes have taken place on the source.

Create a checkpointtable on the target database. The checkpoint saved in this table indicates the current read/write position of the Replicat process. This is used to prevent data loss when the process needs to be restarted or when the server encounters any faults or when the network stops. Without this mechanism, data will be lost. Another benefit is that by using checkpoints, multiple Extract or Replicat processes can read data through the same set of clues.

Check points are not required for Extract and Replicat processes running in batch mode because such processes can always be restarted. However, checkpoints are required when the Extract and Replicat processes work continuously. Check Points are normally stored in files in the dirchk subdirectory, but for Replicat, you can choose to save checkpoints in the database checkpoint table.