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.