Oracle for Oracle One-way data synchronization using Goldengate

Experimental environment

Data source side: HOST1 IP 192.168.199.163

Data target side: HOST2 IP 192.168.199.104

Both machines are installed http://lqding.blog.51cto.com/9123978/1694971 article describes the installation configuration is good goldengate.

To achieve data synchronization, the Oracle source must meet the following settings

1. Oracle needs to run in archive mode
   

Sql> Startup Mount ORACLE instance started. Total System Global area 835104768 bytesfixed size 2217952 bytesvariable size 574621728 bytesdatabase buffers 255852 544 Bytesredo buffers 2412544 bytesdatabase mounted. sql> ALTER DATABASE Archivelog;database altered. sql> ALTER DATABASE Open;database altered.

2. Turn on log attached properties

sql> ALTER DATABASE ADD supplemental LOG data;database altered. sql> ALTER SYSTEM SWITCH LOGFILE; System altered.

The Ogg installation directory provides some demo SQL

[[email protected] ogg]$ ls demo_ora_*demo_ora_create.sql demo_ora_insert.sql demo_ora_lob_create.sql demo_ora_ Misc.sql Demo_ora_pk_befores_create.sql Demo_ora_pk_befores_insert.sql Demo_ora_pk_befores_updates.sql

We use Demo_ora_create.sql to create two tables on both the source and destination sides, using Demo_ora_insert.sql to insert the data at the source end.

Host1

Sql> alter user Scott identified by Tiger account unlock; User altered.  Sql> Grant resource to Scott; This permission is required to grant succeeded #ggsci log on to the database. Sql> Grant Select any dictionary to Scott; # Add Trandata requires this permission grant succeeded. Sql> Conn scott/tigerconnected.sql> @demo_ora_create .sqlsql> @demo_ora_insert. SQL

Host2

Sql> alter user Scott identified by Tiger account unlock; User altered.  Sql> Grant resource to Scott; This permission is required to grant succeeded #ggsci log on to the database. Sql> Grant Select any dictionary to Scott; # Add Trandata requires this permission sql> Conn scott/tigerconnected.sql> @demo_ora_create. SQL

Add additional logs for tables that need to be synchronized

Ggsci (localhost.localdomain) 1> dblogin userid Scott, password tigersuccessfully logged into database. Ggsci (localhost.localdomain) 2> add trandata scott.tcustmerlogging of supplemental Redo data enabled for table scott.t Custmer. Ggsci (localhost.localdomain) 3> add trandata scott.tcustordlogging of supplemental Redo data enabled for table scott.t CustOrd.

First, the initialization of loading data

Configure an initialization extract on the source side to synchronize existing data in the table

Ggsci (localhost.localdomain) 7> ADD EXTRACT einiload, Sourceistableextract added.

The add extract command adds a extract, einiload to the group name of extract. Sourceistable indicates that the data source is a table.

View Extract's information

Ggsci (localhost.localdomain) 9> INFO EXTRACT *, tasksextract einiload Initialized 2015-09-11 15:25 Status STOP Pedcheckpoint Lag not availablelog Read Checkpoint not Available first record record 0T Ask Sourceistable

Configuring the capture parameters for initialization loading

Ggsci (localhost.localdomain) 10> edit params einiload----GoldenGate Initial Data capture--for Tcustmer and tcustord- -extract Einiloaduserid System, PASSWORD "Oracle" Rmthost 192.168.199.104, Mgrport 7809RMTTASK Replicat, GROUP Riniloadtable SCOTT. Tcustmer; TABLE SCOTT. Tcustord;

On the target side, configure a Replicat

Ggsci (localhost.localdomain) 2> ADD replicat riniload, Specialrunreplicat added.

View Replicat Information

Ggsci (localhost.localdomain) 4> info replicat *, tasksreplicat riniload Initialized 2015-08-22 14:18 Status STO Ppedcheckpoint Lag 00:00:00 (updated 00:02:50 ago) Log Read Checkpoint not availabletask Specialrun

Configuring the Replicat parameter

Ggsci (localhost.localdomain) 5> edit params riniload----GoldenGate Initial Load delivery--replicat Riniloadassumetargetdefsuserid system, PASSWORD "Oracle" Discardfile./DIRRPT/RINILOAD.DSC, Purgemap scott.*, TARGET scott.*;

Start extract

Ggsci (Localhost.localdomain) 11> start extract einiloadsending start request to MANAGER ... EXTRACT Einiload Starting

View Logs

Ggsci (Localhost.localdomain) 21> View report Einiload

If there is an error, find the cause and resolve

Processing table scott. Tcustmerprocessing table scott. tcustord************************************************************************                    ** run time  Statistics **                          ******************************************** report at 2015-09-11 16:23:40  (activity since  2015-09-11 16:23:33) Output to riniload:from table scott. tcustmer:       #                    inserts:          2       #                   updates:          0       #                    deletes:          0       #                   discards:          0from table scott. tcustord:       #                    inserts:          2       #                    updates:         0       #                    deletes:          0       #                   discards:          0REDO Log Statistics  Bytes  parsed                     0  Bytes output                   574

The log shows that the data was synchronized successfully.

Verify on the destination library

Sql> Select COUNT (*) from Tcustmer;  COUNT (*)----------2sql> Select COUNT (*) from Tcustord; COUNT (*)----------2

Second, the configuration data real-time synchronization

SOURCE side, configure a real-time extract

Ggsci (localhost.localdomain) 22> ADD EXTRACT Eorakk, Tranlog, BEGIN now, THREADS 1EXTRACT added.

Edit the Extract parameter file

Ggsci (localhost.localdomain) 23> EDIT PARAMS eorakk----Change Capture parameter file to capture--Tcustmer and Tcusto RD changes--extract eorakkuserid System, PASSWORD oraclermthost 192.168.199.104, Mgrport 7809RMTTRAIL./dirdat/kktable SCOTT. Tcustmer; TABLE SCOTT. Tcustord;

Add the remote tail file for extract, which means the tail file is generated on the destination.

Ggsci (localhost.localdomain) 24> ADD rmttrail./dirdat/kk, EXTRACT Eorakk, megabytes 5RMTTRAIL added.

Validation results

Ggsci (localhost.localdomain) 28> INFO rmttrail * Extract Trail:./dirdat/kk Extract:eorakk seqno:0 rba:0 File size:5m

Start the Extract process

Ggsci (Localhost.localdomain) 29> start extract eorakksending start request to MANAGER ... EXTRACT Eorakk Starting

Validation results

ggsci  (Localhost.localdomain)  30> INFO EXTRACT EORAKK, DETAILEXTRACT     EORAKK    Last Started 2015-09-11 17:07    status runningcheckpoint lag       00:00:00  (updated  00:00:01 ago) log read checkpoint  oracle redo logs                       2015-09-11 17:07:47  thread 1, seqno 25, rba 36139008                       SCN 0.1174781  (1174781)   Target Extract Trails:  Remote  trail name                                 seqno         RBA     Max MB  ./dirdat/KK                                            0        1050          5   Extract Source                           Begin              End                /u01/app/oracle/oradata/orcl/redo01.log  2015-09-11  16:58  2015-09-11 17:07  Not Available                            *  initialized *   2015-09-11 16:58current directory    /u01/ app/oggreport file          /u01/app/ogg/dirrpt/ eorakk.rptparameter file       /u01/app/ogg/dirprm/ eorakk.prmcheckpoint file      /u01/app/ogg/dirchk/eorakk.cpeprocess  file         /u01/app/ogg/dirpcs/eorakk.pcestdout file           /u01/app/ogg/dirout/EORAKK.outError log             /u01/app/ogg/ggserr.log

Ggsci (Localhost.localdomain) 31> VIEW report Eorakk

Ggsci (localhost.localdomain) 32> INFO allprogram Status Group Lag at chkpt time Since Chkptmanager RUNNING EXTRACT RUNNING Eorakk 00:00:00 00:00:06

Target side, configuring Replicat

Installing the Checkpoint table

Configure the name of the checkpoint table

Ggsci (localhost.localdomain) 33> EDIT PARAMS./globalsheckpointtable system.ggschkpt

Generate Checkpoint Table

Ggsci (localhost.localdomain) 1> dblogin USERID system, PASSWORD oraclesuccessfully logged into database. Ggsci (localhost.localdomain) 2> ADD Checkpointtableno checkpoint table specified, using GLOBALS specification (System . ggschkpt) ... Successfully created Checkpoint table System.ggschkpt.GGSCI (Localhost.localdomain) 3>

Add Replicat

Ggsci (localhost.localdomain) 3> ADD replicat Rorakk, Exttrail./dirdat/kkreplicat added.

Create a replicat parameter file

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, Purgemap Scott.tcustmer, TARGET Scott.tcustmer; MAP Scott.tcustord, TARGET Scott.tcustord;

Start Replicat

Ggsci (Localhost.localdomain) 5> start Replicat rorakksending start request to MANAGER ... Replicat Rorakk Starting

Validation results

Ggsci (localhost.localdomain) 6> info replicat rorakkreplicat Rorakk last Started 2015-08-22 15:49 Status RUNNIN  Gcheckpoint Lag 00:00:00 (updated 00:00:08 ago) Log Read Checkpoint File./dirdat/kk000000 First Record RBA 1050

Verifying data synchronization

In the source database, execute the following script to insert, UPDATE, delete operations on two tables

[Email protected] ogg]$ sqlplus scott/tigersql*plus:release 11.2.0.1.0 Production on Fri Sep one 17:58:17 2015Copyright (  c) 1982, 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> @/u01/app/ogg/demo_ora_misc.sql

View data in a source table

Sql> Select COUNT (*) from Tcustmer;  COUNT (*)----------5sql> Select COUNT (*) from Tcustord; COUNT (*)----------3

View data for the destination table

Sql> Select COUNT (*) from Tcustmer;  COUNT (*)----------5sql> Select COUNT (*) from Tcustord; COUNT (*)----------3

Data is synchronized

This article is from the "Ding Dong" blog, please be sure to keep this source http://lqding.blog.51cto.com/9123978/1695162

Oracle for Oracle One-way data synchronization using Goldengate