Oracle GoldenGate (OGG) synchronizes data between the same DB on heterogeneous OS.

Oracle GoldenGate (OGG) synchronizes data between the same DB on heterogeneous OS.

The functions that OGG can achieve:

You can fine-tune to a single-table synchronization, a rows synchronization that meets a specific where condition, and specify the synchronization for column.

OGG's rivals: IBM Cdc;dell Shareplex;dsg;dds, etc.

Environment Description:
Source: Windows 2003x64bit, Oracle DB 11.2.0.3 X64bit
Destination: SUSE Linux, Oracle DB 11.2.0.3

Pre-prepared:
1.Microsoft Visual C + + 2005 SP1 Redistributable Package
Note: first: the C + + package distinguishes the number of bits and is sure to download the SP1 version of the package.
Second: As long as the use of Ogg on Windows, this C + + package must be installed,
Whether or not DB is Oracle, SQL Server, regardless of whether Ogg is used for the source database or the destination database.

2.ogg for Windows X64bit Oracle DB 11.2.0.3 x64bit media. --p18794252_1121023_mswin-x86-64.zip
Oracle DB 11.2.0.3 x64bit Media under 3.ogg for Linux x64bit. --p18794252_1121023_linux-x86-64.zip

  NOTE:
  First: Ogg media, in addition to the basic version (such as 11.2.1.0.1, the last of this. 1 is the base version) can be downloaded from www.oracle.com,
  Ogg's subsequent patches can only be downloaded from the MOS.
  Second: Ogg's media differentiates OS type, DB type, DB version, DB bit number.
  But for the following scenario, the same OGG media: The Oracle DB 11.2.0.3 x64bit is installed under SUSE11 and installed under SuSE 10.

Implementation process:
1. Upload media to source and destination.
2. Implementation process at Source:
2.1 Create goldengate System user and authorize
  sql> create tablespace ggtbs datafile ' d:\ related path \ggtbs01.dbf ' Size 1024M 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-the general production library is in archive mode.
2.3 Open additional logs for the source database
   sql>alter databse add supplemental log data;
    Toggle log to take effect on additional logs:
   sql>alter System archive log current;
2.4 Check additional logs from the source-side database
   sql>select supplemental_log_data_min from V$database;
  --Returns YES to indicate that additional logs are open.
2.5 Force logging to open the source database:
   sql> alter DB force logging;
2.6 Perform a check script on the main library to confirm that the following conditions exist: column definitions for indexed columns that identify a unique index are allowed to be 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= ' self-add user ' and DTC. Owner= ' Add Users yourself '
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

---> Self-add users, use the user that needs to be synced instead.
For a queried table and a unique index name, modify: either to become a non-unique index or to set the column definition to NOT NULL in the case of preserving a unique index.

2.7 Assume that the Ogg software is placed in the D:\ggs directory, the CMD interface into the D:\ggs
Ggsci>create subdirs-The command is built under D:\ggs to create subordinate directories such as DIRPRM, Dirdat, and Dirrpt.
2.8 Ggsci>edit params./globals
Add the following characters, and then save the exit
Mgrservname Ggsservice
2.9 Creating the Ogg Windows service
D:\ggs>install AddService autostart

2.10 Adding a supplemental log to a table
Ggsci>dblogin userid goldengate Password Oracle
ggsci> add Trandata user1.*---> Note: * Do not add a semicolon.
ggsci> add Trandata user2.*---> Note: * Do not add a semicolon.
ggsci> add Trandata user3.*---> Note: * Do not add a semicolon.

Confirm that the supplemental log for 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 Configuring DDL Replication
Sql> GRANT EXECUTE on Utl_file to Goldengate;
Ggsci>edit params./globals Add the following characters, and then save the exit
Ggschema goldengate

CMD under CD to D:\ggs directory
To exit all Oracle session
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 Configuring sequence Synchronization (if the standby is a report query request, ignore this step)
CMD under CD to D:\ggs directory
Sqlplus/as SYSDBA
sql> @sequence. sql
---> Source execution:sql> GRANT execute on goldengate.updatesequence to Goldengate;
Execute:sql> GRANT execute on goldengate.replicatesequence to goldengate---> destination;
2.13 Source-side Configuration parameter file
Ggsci > Edit Params Mgr
Add the following parameters, and then save the exit.
Port 7809
Autostart Extract *
AutoRestart extract *, waitminutes 1, retries, resetminutes 60
Purgeoldextracts d:\ggs\dirdat\st*, usecheckpoints, minkeephours 2

Ggsci> Add extract Extfull, Tranlog, Threads 2, Begin now
---> Above command is for RAC2 nodes of the main library, if standalone, ignore threads 2

Ggsci >edit params Extfull
Add the following parameters, and then save the exit
Extract Extfull
Setenv (Nls_lang = "American_america. ZHS16GBK ")
--tranlogoptions asmuser [email protected], Asmpassword Oracle
--threadoptions Maxcommitpropagationdelay 80000 iolatency 160000

Dboptions Allowunusedcolumn
UserID goldengate, password Oracle

DDL include mapped

Ddloptions addtrandata RETRYOP maxretries retrydelay, Report

Warnlongtrans 1h, Checkinterval 5m

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 trail file, file name D:\ggs\dirdat\st, is the extfull extraction process to write this trail file, the maximum size of each trail file is 50M
ggsci> Add Exttrail d:\ggs\dirdat\st, extract extfull, megabytes 50
---> Note:
Note: The trail file name of the source must be in a different name than the trail file name on the destination, for example:
Trail filename of Source/u02/ggs/dirdat/sd
Trail file name/u02/ggs/dirdat/td on the destination side
The reason: the deletion mechanism (checkpoint mechanism) of the Ogg trail file is judged by a set of replication environments (source and destination):
This has the potential to cause a consequence: The trail file number at source is 1000, the destination trail file number is 100, and the source and destination trail file name have the same name, because of the existence

100, so the trail file number that the source can delete is <100. This leads to source trail not being deleted in time, resulting in a file system utilization rate of $gg_home at source of 100%, which in turn creates

The trail file formed by the EXT process is not stored anywhere and causes the EXT process to abend.

2.15 Add the transport process Dpfull at the source, this transport process is the source-side d:\ggs\dirdat\st this trail file
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 transport process, you can also specify a size of 50M
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, and then save the exit
Extract Dpfull
PassThru
Rmthost Destination IP, mgrport 7809
Rmttrail/u02/ggs/dirdat/tt
Gettruncates
Table user1.*;
Table user2.*;
Table user3.*;

Sequence user1.*;
Sequence user2.*;
Sequence user3.*;

2.18 Source-side database query out an SCN number:
Select Current_scn from V$database;
--12754579013479

Please keep this SCN number in mind and this SCN number will be used for the first time the rep process is started on the destination side.

Source end starts exporting 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. The implementation process at the end of the destination:
3.1 Configure environment variable information:
When the standby repository is 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 Goldengate system users and authorize
sql> Create tablespace ggtbs datafile '/u02/related path/ggtbs01.dbf ' size 1024M auextend on;
sql> Create user Goldengate default tablespace ggtbs identified by Oracle; (The target database also creates this one Goldengate user)
Sql> Grant DBA to Goldengate;
3.3 Configuring the MGR Parameter file:
Ggsci > Edit Params Mgr
Port 7809
--autostart Replicat *
--autorestart Replicat *, waitminutes 1, retries, resetminutes 60
--purgeoldextracts/u02/ggs/dirdat/tt*, Usecheckpoints, minkeephours 2

3.4 Adding checkpointtable to the destination
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 is complete:

Note: Do not start the above repfull process, the repfull process starts after IMPDP import data.

Perform the data import in the destination library execution IMPDP, and then follow the steps below to complete the import.
Before starting the destination rep process, make sure that the following items have been completed:

First, there are parameters in the rep parameter file deferrefconst disable cascade Delete

Second, there is a parameter in the rep parameter file Suppresstriggers the trigger that suppresses the destination database 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, so if the destination database is 10.2.0.4, you also need to manually disable the trigger in the destination database (plsql Dev can be disabled)

Thirdly, IMPDP has been imported to complete, this is necessary, I do not have to explain more.

When you start the repfull process for the first time, use the following command to start:
Start Repfull, AFTERCSN 12754579013479

Resources:
Docs.oracle.com
Support.oracle.com