Data synchronization between Oracle GoldenGate databases on Heterogeneous OS

Data synchronization between Oracle GoldenGate databases on Heterogeneous OS

Oracle GoldenGate (hereinafter referred to as OGG) synchronizes data between databases of the same type on Heterogeneous OS.

Functions implemented by ogg:
 
It can be refined to single-Table synchronization, rows synchronization that meets specific where conditions, and column synchronization.
 
Competitors of ogg: ibm cdc, dell shareplex, dsg, and dds
 
Environment Description:
Source: windows 2003x64bit, oracle db 11.2.0.3 x64bit
Destination: SUSE linux, oracle db 11.2.0.3
 
Prerequisites:
1. Microsoft Visual C ++ 2005 SP1 Redistributable Package
Note: first, the c ++ package distinguishes the number of BITs and must download the sp1 package.
Second, as long as ogg is used on windows, the c ++ package must be installed,
Whether the database is oracle or sqlserver, whether ogg is used for the source database or the target database.
 
2. oracle db 11.2.0.3 x64bit media in ogg for windows x64bit. --P18794252_1121023_MSWIN-x86-64.zip
3. oracle db 11.2.0.3 x64bit media in ogg for linux x64bit. --P18794252_1121023_Linux-x86-64.zip
 
Note:
First, the ogg media can be downloaded from www.oracle.com except for the basic version (for example, 11.2.1.0.1, and the final. 1 is the basic version,
Subsequent ogg patches can only be downloaded from mos.
Second, the ogg media is divided into OS type, db type, db version, and db bit number.
However, the same ogg media is used in the following scenarios: oracle db 11.2.0.3 x64bit is installed under suse11 and suse 10.

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

Installing Oracle 12C in Linux-6-64

Install Oracle 11gR2 (x64) in CentOS 6.4)

Steps for installing Oracle 11gR2 in vmwarevm

Install Oracle 11g XE R2 In Debian

--------------------------------------------------------------------------------
 
Implementation process:
1. Upload the media to the source and destination.
2. implementation process at the source:
2.1 create and authorize a goldengate system user
SQL> create tablespace GGTBS datafile 'd: \ related path \ ggtbs01.dbf' size 1024 M auextend on;
SQL> Create user Goldengate default tablespace ggtbs identified by oracle; (the target database also creates this goldengate user)
SQL> grant dba to goldengate;
2.2 set the database to archive mode-normal production databases are in archive mode.
2.3 additional logs of open-source databases
SQL> alter databse add supplemental log data;
Switch logs to make the additional logs take effect:
SQL> alter system archive log current;
2.4 check additional logs of the source database
SQL> select supplemental_log_data_min from v $ database;
-- If yes is returned, the additional log is enabled.
2.5 open the force logging of the source database:
SQL> alter database force logging;
2.6 execute the check script on the master database to check whether the following conditions exist: Find the column definition of the unique index column that allows null
 
Select dic. table_owner, dic. table_name, dic. index_name, di. uniqueness, dic. column_name
From dba_ind_columns dic, dba_indexes di, dba_tab_columns dtc
Where dic. table_owner = 'add user' and dtc. OWNER = 'add user'
AND dic. table_owner = di. table_owner and dic. TABLE_NAME = di. table_name
And dic. index_name = di. index_name
And di. uniqueness = 'unique' and dtc. owner = di. table_owner
And dtc. TABLE_NAME = di. table_name and dic. column_name = dtc. COLUMN_NAME and dtc. nullable = 'y'
And dic. TABLE_NAME = dtc. TABLE_NAME
 
---> To add users manually, use the user to be synchronized instead.
Modify the name of the queried table and unique index: either change to a non-unique index, or set the column definition to not null when the unique index is retained.
 
2.7 assume that the ogg software is placed in the d: \ ggs directory, and enter d: \ ggs on the cmd interface.
Ggsci> create subdirs --> this command is used to create sub-directories such as dirprm, dirdat, and dirrpt under d: \ ggs.
2.8 ggsci> edit params./GLOBALS
Add the following characters, save and exit
MGRSERVNAME GGSSERVICE
2.9 create a windows service for ogg
D: \ ggs> install addservice autostart
 

2.10 add supplemental logs to the table
Ggsci> dblogin userid goldengate password oracle
Ggsci> add trandata USER1. * ---> Note: * do not add extra points.
Ggsci> add trandata USER2. * ---> Note: * do not add extra points.
Ggsci> add trandata USER3. * ---> Note: * do not add extra points.

Check whether the Supplemental log of the table has been added:
Select table_name from all_tables
Where owner = 'user1' and table_name not in (select distinct table_name from dba_log_groups where owner = 'user1 ');
 
2.11 configure DDL Replication
SQL> GRANT EXECUTE ON UTL_FILE TO goldengate;
Ggsci> edit params./GLOBALS Add the following characters, save and exit
GGSCHEMA goldengate
 
Cd to d: \ ggs directory under cmd
Exit all oracle sessions
Sqlplus/as sysdba
SQL> @ marker_setup. SQL
SQL> @ ddl_setup. SQL
SQL> @ role_setup. SQL
SQL> grant ggs_ggsuser_role to goldengate;
SQL> @ ddl_enable. SQL
 
2.12 configure sequence synchronization (skip this step if the standby database is a REPORT query requirement)
Cd to d: \ ggs directory under cmd
Sqlplus/as sysdba
SQL> @ sequence. SQL
---> Source execution: SQL> GRANT EXECUTE on goldengate. updateSequence TO goldengate;
---> Target execution: SQL> GRANT EXECUTE on goldengate. replicateSequence TO goldengate;
2.13 source configuration parameter file
Ggsci> edit params mgr
Add the following parameters, save and exit.
Port 7809
Autostart extract *
Autorestart extract *, waitminutes 1, retries 60, RESETMINUTES 60
PURGEOLDEXTRACTS d: \ ggs \ dirdat \ st *, USECHECKPOINTS, MINKEEPHOURS 2
 

Ggsci> add extract extfull, tranlog, Threads 2, begin now
---> The preceding commands are applicable to the scenario where the master database is a rac2 node. If the master database is a single machine, ignore Threads 2.
 
Ggsci> edit params extfull
Add the following parameters, save and exit
Extract extfull
Setenv (NLS_LANG = "AMERICAN_AMERICA.ZHS16GBK ")
-- Tranlogoptions asmuser sys @ ASM1, ASMPASSWORD oracle
-- Threadoptions maxcommitpropagationdelay 80000 IOLATENCY 160000
 
DBOPTIONS ALLOWUNUSEDCOLUMN
Userid goldengate, password oracle
 
Ddl include mapped
 
Ddloptions addtrandata retryop maxretries 1000 RETRYDELAY 10, REPORT
 
WARNLONGTRANS 1 h, CHECKINTERVAL 5 m
 
Exttrail d: \ ggs \ dirdat \ st
Gettruncates
Dynamicresolution
 
Tableexclude 'user. cncdata ';
Tableexclude 'user. sb_czm_pyDD ';
 
Table USER1 .*;
Table USER2 .*;
Table USER3 .*;
 
Sequence USER1 .*;
Sequence USER2 .*;
Sequence USER3 .*;
 
2.14 Add a trail file named d: \ ggs \ dirdat \ st. The trail file is written by the extfull extraction process. The maximum size of each trail file is 50 MB.
Ggsci> add exttrail d: \ ggs \ dirdat \ st, extract extfull, MEGABYTES 50
---> Note:
Note: The Source trail file name must be different from the target trail file name, for example:
Source trail file name/u02/ggs/dirdat/sd
Target trail file name/u02/ggs/dirdat/td
The reason is that the deletion mechanism (checkpoint mechanism) of ogg trail files is determined based on a complete replication environment (source and target:
This may cause the following consequence: the source's trail file number reaches 1000, the target trail file number reaches 100, and the source and the target trail file name have the same name.
 
100. Therefore, the trail file number that can be deleted from the source is <100. In this way, the source trail cannot be deleted in time, resulting in the file system usage of the source $ GG_HOME is 100%
 
The trail files extracted from the ext process are stored nowhere, resulting in the ext process abend.
 
2.15 Add a transport process dpfull to the source. The transport process transmits the d: \ ggs \ dirdat \ st file from the source.
Ggsci> add extract dpfull exttrailsource d: \ ggs \ dirdat \ st
 
2.16 create a Remote Queue file (File Name:/u02/ggs/dirdat/tt) and assign it to the transmission process. You can also specify the file size as 50 MB.
Ggsci> add rmttrail/u02/ggs/dirdat/tt, extract dpfull, MEGABYTES 50
 
2.17 edit the parameters of the dpfull process:
Ggsci> edit params dpfull
Add the following parameters, save and exit
Extract dpfull
Passthru
Destination IP address of rmthost, mgrport 7809
Rmttrail/u02/ggs/dirdat/tt
Gettruncates
Table USER1 .*;
Table USER2 .*;
Table USER3 .*;
 
Sequence USER1 .*;
Sequence USER2 .*;
Sequence USER3 .*;
 

2.18 The source database queries an scn Number:
Select current_scn from v $ database;
-- 12754579013479
 
Remember this scn number. This scn number will be used when the target end starts the rep process for the first time.
 
The source end starts to export data according to this scn number.
Expdp sys/****** directory = exp dumpfile = trff_app % U. dmp logfile = trff_app_exp.log schemas = trff_app parallel = 4
 
Flashback_scn = 12754579013479
 
 
 
3. implementation process at the target end:
3.1 configure environment variables:
When the slave database is a Linux OS:
Vi. bash_profile
Export PATH = $ PATH: $ ORACLE_HOME/bin:/u02/ggs
Export LD_LIBRARY_PATH =/u02/ggs: $ ORACLE_HOME/lib

3.2 Create and authorize a goldengate system user
SQL> create tablespace GGTBS datafile '/u02/related path/ggtbs01.dbf' size 1024 M auextend on;
SQL> Create user Goldengate default tablespace ggtbs identified by oracle; (the target database also creates this goldengate user)
SQL> grant dba to goldengate;
3.3 configure the mgr parameter file:
Ggsci> edit params mgr
Port 7809
-- Autostart replicat *
-- Autorestart replicat *, waitminutes 1, retries 60, RESETMINUTES 60
-- PURGEOLDEXTRACTS/u02/ggs/dirdat/tt *, USECHECKPOINTS, MINKEEPHOURS 2
 
3.4 add checkpointtable to the target
Ggsci> dblogin userid goldengate, password oracleoracle
Ggsci> add checkpointtable goldengate. ckptfull
Ggsci> add replicat repfull, exttrail/u02/ggs/dirdat/tt, CHECKPOINTTABLE goldengate. ckptfull
Ggsci> edit params repfull
Replicat repfull
Setenv (NLS_LANG = "AMERICAN_AMERICA.ZHS16GBK ")
 
Assumetargetdefs
 
Userid goldengate, password oracle
 
Dboptions deferrefconst, SUPPRESSTRIGGERS
Gettruncates
ALLOWNOOPUPDATES
Ddl include mapped
 
Discardfile./dirrpt/repfull. dsc, append, megabytes 5000
 
Map USER1. *, target USER1 .*;
Map USER2. *, target USER2 .*;
Map USER3. *, target USER3 .*;
 
Configuration completed:
 
Note: Do not start the above repfull process. The repfull process starts after impdp imports data.
 
Execute impdp in the target database to import data. After the import is completed, execute the following steps.
Before starting the target rep process, make sure that the following items have been completed:
 
First, DEFERREFCONST is disabled in the rep parameter file.
 
Second, in the rep parameter file, the parameter SUPPRESSTRIGGERS is used to suppress the target database trigger when the rep process is running.
Note: SUPPRESSTRIGGERS this parameter is only valid for 10.2.0.5 and later, 11.2.0.2 and later oracle database versions. Therefore, if the target database is 10.2.0.4, you also need to manually disable the trigger in the target database (you can disable it with plsql dev)
 
Third, Impdp has been imported, which is required. I don't need to explain it any more.
 
When starting the repfull process for the first time, use the following command to start it:
Start repfull, aftercsn 12754579013479