Shulou(Shulou.com)06/01 Report--

1 brief introduction of software

When installing, you should choose the most stable installation version, which is now officially released as follows:

Oracle GoldenGate 11.2.1.0.1

There are different installation media corresponding to different databases and versions. The following are two installation media used according to the current network situation:

Ogg112101_fbo_ggs_Linux_x64_ora10g_64bit.zip

Ggs_Linux_x64_MySQL_64bit.tar

Oracle Golden Gate software is a kind of structured data replication and backup software based on logs. It obtains the incremental changes of the data by analyzing the online logs or archived logs of the source database, and then applies these changes to the target database, so as to realize the synchronization of the source database and the target database. Oracle Golden Gate can realize real-time replication of a large amount of data at subsecond level between heterogeneous IT infrastructure (including almost all common operating system platforms and database platforms), so it can be applied in emergency systems, online reports, real-time data warehouse supply, transaction tracking, data synchronization, centralization / distribution, disaster recovery, database upgrade and migration, dual business centers and other scenarios. At the same time, Oracle Golden Gate can achieve a variety of flexible topologies, such as one-to-one, broadcast (one-to-many), aggregation (many-to-one), bi-directional, point-to-point, cascade and so on.

2 overall structure

Like the traditional logical replication, the principle of Oracle GoldenGate is to extract the redo log or archive log from the source side, then deliver it to the target side through TCP/IP, and finally parse and restore it to the target side, so that the target side can synchronize the data at the same source side. The following is the technical architecture of OracleGoldenGate

Manager process

The Manager process is the control process of the GoldenGate, running on the source side and the target side. Its main functions are as follows: starting, monitoring, restarting other processes of Goldengate, reporting errors and events, allocating data storage space, issuing threshold reports and so on.

Extract process

Extract runs on the source side of the database and is responsible for capturing data from source-side tables or logs.

Pump process

The pump process runs on the database source side. Its purpose is that if the source side uses a local trail file, then the pump process will send the trail in blocks to the destination side through the TCP/IP protocol, which is usually the recommended way. The pump process is essentially a special form of the extract process. If the trails file is not used, the extract process delivers the data directly to the target side after the data is extracted.

Trail file

In order to deliver the database transaction information from the source side to the target side more effectively and safely. GoldenGate introduces the concept of trail files. It was mentioned earlier that after extract has extracted the data, Goldengate will convert the extracted transaction information into a file in GoldenGate proprietary format. Then pump is responsible for delivering the trail files from the source side to the destination side, so such files will exist on both the source and destination sides. The purpose of the trail file is to prevent a single point of failure, persist the transaction information, and use the checkpoint mechanism to record its read and write location. If a failure occurs, the data can be retransmitted according to the location recorded by the checkpoint.

Replicat process

The Replicat process, which we usually call the application process. Running on the target side, it is the last station of data transmission, which is responsible for reading the contents of the target trail file, parsing it into DML or DDL statements, and then applying it to the target database.

GGSCI

GGSCI is the abbreviation of GoldenGate Software Command Interface. It provides a wealth of commands to perform various operations on Goldengate, such as creating, modifying, monitoring GoldenGate processes, and so on.

Flexible topology:

2.1 GoldenGate support Matrix Databases

OS and Platforms

Capture:

Windows 2000,2003,xp

Oracle

Linux

DB2 UDB

Sun Slolaris

Microsoft SQL Server

HP NonStop

Sybase ASE

HP Ux

Teradata

HP TRU64

Ingres

IBM AIX

Enscribe

IBM z/OS

SQL/MP

SQL/MX

Delivery:

All listed above

Mysql,hp neoview,Netezza

And ODBC compatible databases

3 GoldenGate software installation 3.1 groups and user creation

The source side is the oracle production database, and oracle and dba already exist, and the user and group are reused.

The target side is mysql, mysql and dba already exist, and the user and group are reused.

3.2 GoldenGate installation directory check

Before installing Oracle GoldenGate software, you need to confirm in advance that the installation directory belongs to the correct sovereignty limit, and that the file system has enough space to store Oracle GoldenGate software and can support daily operation and maintenance. For storing dirdat data directories, at least more than 100GB space is required. If the source database is in oracle rac mode, the storage of the directory of ogghome must be set so that the two hosts of rac can recognize it. If the mount is on a node with a lighter task, if the node fails, it can be mounted to another node in time to increase high availability.

Check the space information of the file system where the installation directory is located, through the following command:

Df-g / home/ogghome

Confirm that the installation directory belongs to the primary permission information, which is achieved by the following command:

Ls-ld / home/ogghome

3.3 extract the installation file

Oracle GoldenGate software is installed by decompression.

Extract the GoldenGate installation file by using the following command:

Unzip ggs_*.zip

Tar-xvf ggs_*.tar

3.4 GoldenGate software startup test

The Oracle GoldenGate software starts the interactive interface through the ggsci command, and when ggsci starts successfully, the table name installation process is correct. Be sure to enter the installation directory of the Oracle GoldenGate software before starting ggsci.

Start the ggsci of Oracle GoldenGate with the following command:

Cd / home/oracle/ggs/goldengate

. / ggsci

The account of Oracle database must set the LD_LIBRARY_PATH environment variable, otherwise an error will be reported.

LD_LIBRARY_PATH=/oracle/product/10.2/db/lib

3.5.Create GoldenGate software subdirectory

Enter the installation path and create the relevant directory (executed on both the source side and the destination side). The source side uses oracle users and the destination side uses mysql users:

Cd / home/ogghome

. / ggsci

GGSCI > create subdirs

Here is the log:

Creating subdirectories under current directory / home/ogghome

Parameter files / home/ogghome/dirprm: already exists

Report files / home/ogghome/dirrpt: created

Checkpoint files / home/ogghome/dirchk: created

Process status files / home/ogghome/dirpcs: created

SQL script files / home/ogghome/dirsql: created

Database definitions files / home/ogghome/dirdef: created

Extract data files / home/ogghome/dirdat: created

Temporary files / home/ogghome/dirtmp: created

Stdout files / home/ogghome/dirout: created

4 GoldenGate deployment 4.1 initialize source database 4.1.1 enable archiving mode

GoldenGate is based on the capture of oracle log changes, so in order to fully capture the changes in the oracle database, it is necessary to turn on archive mode.

SQL > alter system set log_archive_dest_1='location=/arch' scope=both

SQL > shutdown immediate

SQL > startup mount

SQL > alter database archivelog

SQL > alter database open

SQL > archive log list

Note that since the above command involves stopping the database application, it should be operated according to the actual situation of the business.

4.1.2 enable additional logs

In oracle, we can locate a record through rowid, but the database on the destination side may be completely different from that on the source side, so it is impossible to determine the logical changes of the source database through rowid. At this time, the attachment log supplemental log takes the stage of the performance. After the additional log feature is enabled in the database, oracle will also append the column that can uniquely mark the record to redo log for the modification operation on the source side. In this way, the target-side database can know what specific changes have taken place on the source side.

SQL > alter database add supplemental log data

SQL > alter system switch logfile

SQL > select supplemental_log_data_min from v$database

4.1.3 enable mandatory logging mode

SQL > alter database force logging

SQL > SELECT FORCE_LOGGING FROM V$DATABASE

4.1.4 goldengate creates users and authorizes

Create an account in the source oracle database:

Create tablespace data_goldengate datafile'+ DG_DATA1' size 10240m autoextend on

Create user goldengate identified by password default tablespace data_goldengate temporary tablespace temp

Give goldengate relevant permissions (for simplicity, you can grant DBA permissions. If security issues are involved, dba permissions cannot be granted casually)

Grant CONNECT,RESOURCE to goldengate

Grant CREATE SESSION,ALTER SESSION to goldengate

Grant SELECT ANY DICTIONARY,SELECT ANY TABLE to goldengate

Grant CREATE TABLE,ALTER ANY TABLE to goldengate

Grant unlimited tablespace to goldengate

The destination mysql creates an account and grants dba permission:

Grant ALL PRIVILEGES on *. * to goldengate@'%'

Grant ALL PRIVILEGES on *. * to goldengate@'localhost'

Update mysql.user set password=password ('password') where user='goldengate'

4.1.5 confirm whether to enable table-level additional logs for tables that need synchronization

. / ggsci

GGSCI (localhost.localdomain) 1 > dblogin userid goldengate password password

Successfully logged into database.

GGSCI (localhost.localdomain) 2 > info trandata xianyezhao.CALENDAR_DETAIL

Logging of supplemental redo log data is disabled for table XIANYEZHAO.CALENDAR_DETAIL.

GGSCI (localhost.localdomain) 3 > add trandata xianyezhao.CALENDAR_DETAIL

Logging of supplemental redo data enabled for table XIANYEZHAO.CALENDAR_DETAIL.

GGSCI (localhost.localdomain) 4 > info trandata XIANYEZHAO.CALENDAR_DETAIL

Logging of supplemental redo log data is enabled for table XIANYEZHAO.CALENDAR_DETAIL.

Columns supplementally logged for table XIANYEZHAO.CALENDAR_DETAIL: SEQNO.

4.2 configure ASM access method

Because the oracle redo log files are on ASM storage, the goldengate software needs to access ASM

Configure the tns connection string for the asm instance. Since the listening status is BLOCKED, you must add (UR=A) to log in to the asm instance remotely through ogg. Here are the official details:

ASM instances appear with a blocked status from the 'lsnrctl services'command (see below). To remotely access a' blocked service' you need toadd (UR=A) to the tns connect string (plus you will need a password filefor the asm instance).

Tnsnames.ora entry:

The following is the monitoring status, which can be viewed using lsnrctl status:

Service "+ ASM" has 1 instance (s).

Instance "+ ASM2", status BLOCKED, has 1 handler (s) for this service...

Here is the specific tns connection string:

ASM =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.219.6) (PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = + ASM)

(INSTANCE_NAME = + ASM2)

(UR=A)

)

)

4.3 configure the NFS file system

Because archiving uses the file system, you need to use the nfs file mode to mount the archive from another node locally.

a. Enable the nfs service:

Service nfs start

View nfs status:

Service nfs status

b. Enable nfs permission:

Edit the permission list:

Vi / etc/exports

/ archivelog1 * (rw)

Update permissions:

Exportfs-a

c. Create / archivelog1, change permissions to oracle and dba

Chown oracle:dba / archivelog1

Use the root user mount:

Mount 192.168.219.3:/archivelog1 / archivelog1

4.4 configure MGR on the source side and destination side

Ggsci > EDIT PARAMS MGR

PORT 7809

PURGEOLDEXTRACTS. / dirdat,USECHECKPOINTS

Ggsci > START MGR

Ggsci > INFO MGR

4.5 create Extract: ext_cal4.5.1 add extract

Add extract ext_cal, tranlog, begin now, THREADS 2

EXTRACT added.

4.5.2 set exttrail

Add exttrail. / dirdat/ca, extract ext_cal, megabytes 100

EXTTRAIL added.

4.5.3 configuring extract

The following * replace with a password:

Edit params ext_cal

/ * /

EXTRACT ext_cal

SETENV (NLS_LANG= "AMERICAN_AMERICA.ZHS16GBK")

SETENV (ORACLE_SID= "g139bak2")

Userid goldengate, password password

TRANLOGOPTIONS ASMUSER sys@asm,ASMPASSWORD *

EXTTRAIL. / dirdat/ca

TRANLOGOPTIONS LOGRETENTION DISABLED

TRANLOGOPTIONS BUFSIZE 2048000

Tranlogoptions altarchivedlogformat threadid 1 g139bak_%S_%T_%R.log

Tranlogoptions altarchivedlogformat threadid 2 g139bak_%S_%T_%R.log

Tranlogoptions altarchivelogdest instance g139bak1 / archivelog1/g139bak_archivelog, altarchivelogdest instance g139bak2 / archivelog2/g139bak_archivelog

-- DDL INCLUDE mapped

-- DDLOPTIONS ADDTRANDATA

DISCARDFILE. / dirrpt/ext_cal.dsc,APPEND,MEGABYTES 1000

DISCARDROLLOVER AT 6:00

REPORTROLLOVER AT 6:00

REPORTCOUNT EVERY 1 HOURS,RATE

TRANLOGOPTIONS ALTARCHIVELOGDEST / archivelog2

FETCHOPTIONS MISSINGROW ABEND

STATOPTIONS REPORTFETCH

WARNLONGTRANS 1H,CHECKINTERVAL 10m

DYNAMICRESOLUTION

TABLE XIANYEZHAO.CALENDAR_DETAIL

TABLE XIANYEZHAO.CALENDAR_INFO

/ * /

Here is a look at the content:

GGSCI (gb139bak02) 5 > view report ext_cal

4.6.Create a delivery process: dp_cal4.6.1 adds extract

Add extract dp_cal, exttrailsource. / dirdat/ca

4.6.2 set extract delivery rmttrail

Add rmttrail. / dirdat/ca, extract dp_cal

4.6.3 configure extract

Edit params dp_cal

/ * /

EXTRACT dp_cal

Passthru

DYNAMICRESOLUTION

RMTHOST 192.168.219.35, MGRPORT 7809, COMPRESS

RMTTRAIL. / dirdat/ca

TABLE XIANYEZHAO.CALENDAR_DETAIL

TABLE XIANYEZHAO.CALENDAR_INFO

/ * /

Due to no configuration of the target side of the mgr process, 7809 did not open, the network reported an error, causing the process to hang.

GGSCI (localhost.localdomain) 48 > view report dp_cal

GGSCI (gb139bak02) 65 > view report dp_cal

4.7 create a definition file

Edit the defgen file on the source side

Su-oracle

Cd. / dirprm

$vi cal.prm

DEFSFILE / home/ogghome/dirdef/cal.def

USERID goldengate PASSWORD password

TABLE XIANYEZHAO.CALENDAR_DETAIL

TABLE XIANYEZHAO.CALENDAR_INFO

Generate a definition file by executing the defgen command

/ home/ogghome/defgen PARAMFILE / home/ogghome/dirprm/cal.prm

*

Oracle GoldenGate Table Definition Generator for Oracle

Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230

Linux, x64, 64bit (optimized), Oracle 10g on Apr 23 2012 05:09:39

Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.

Starting at 2014-06-09 15:24:08

*

Operating System Version:

Linux

Version # 1 SMP Thu Sep 5 21:21:44 EDT 2013, Release 2.6.18-371.el5

Node: gb139bak02

Machine: x86_64

Soft limit hard limit

Address Space Size: unlimited unlimited

Heap Size: unlimited unlimited

File Size: unlimited unlimited

CPU Time: unlimited unlimited

Process id: 2014

*

* * Running with the following parameters * *

*

DEFSFILE / home/ogghome/dirdef/cal.def

USERID goldengate PASSWORD *

TABLE XIANYEZHAO.CALENDAR_DETAIL

Retrieving definition for XIANYEZHAO.CALENDAR_DETAIL

TABLE XIANYEZHAO.CALENDAR_INFO

Retrieving definition for XIANYEZHAO.CALENDAR_INFO

Definitions generated for 2 tables in / home/ogghome/dirdef/cal.def

Transfer the generated def file to MySQL

[oracle@localhost dirdef] $sftp mysql@192.168.61.131

Connecting to 192.168.61.131...

Mysql@192.168.61.131's password:

Sftp > cd / home/ogghome/dirdef

Sftp >! ls

Sftp > put cal.def

Uploading cal.def to / home/ogghome/dirdef/cal.def

Cal.def 100% 1001 1.0KB/s 00:00

Modify the permissions of the definition file on MySQL side

Chown mysql:dba / home/ogghome/dirdef/cal.def

Chmod 755 / home/ogghome/dirdef/cal.def

4.8.1 Oracle to oracle export mode for initialization data

1. Check the system change number:

Col current_scn for 999999999999999999999999999999

Select current_scn from v$database

-- 1138506

2. Export import data:

Set linesize 133

Col directory_path format a50

Select * from dba_directories

SYS DATA_PUMP_DIR / opt/oracle/app/admin/test/dpdump/

Expdp\'/ as sysdba\ 'DIRECTORY=DATA_PUMP_DIR DUMPFILE=t3.dmp LOGFILE=t3.log TABLES=test.t3 FLASHBACK_SCN=1138506

Impdp\'/ as sysdba\ 'DIRECTORY=DATA_PUMP_DIR DUMPFILE=t3.dmp LOGFILE=t3_imp.log TABLES=test.t3 REMAP_TABLESPACE=DATA_TEST:DATA_TEST

-- REMAP_SCHEMA=TEST:TEST

3. When starting the application process, you need to specify the aftersn number. Here is a specific example:

Start Replicat with SCN

Start rep_cal, aftercsn 1138506

4.8.2 Oracle to mysql mode

How to use goldengatedirect load:

Source side:

ADD EXTRACT einixyz, SOURCEISTABLE

EXTRACT einixyz

Userid goldengate, password password

RMTHOST 192.168.219.35, MGRPORT 7809, COMPRESS

RMTTASK REPLICAT, GROUP RINIXYZ

TABLE XIANYEZHAO.T_INI

Create a definition file

Edit the defgen file on the source side

Su-oracle

Cd. / dirprm

$vi cal_xyz.prm

DEFSFILE / home/ogghome/dirdef/cal_xyz.def

USERID goldengate PASSWORD password

TABLE XIANYEZHAO.T_INI

Generate a definition file by executing the defgen command

/ home/ogghome/defgen PARAMFILE / home/ogghome/dirprm/cal_xyz.prm

Sftp is uploaded to the destination server / home/ogghome/dirdef/ directory.

Destination side:

ADD REPLICAT rinixyz, SPECIALRUN

Replicat RINIXYZ

Sourcedefs / home/ogghome/dirdef/cal_xyz.def

SETENV (MYSQL_UNIX_PORT=/mysql/dbdata/mysql.sock)

Sourcedb cal@localhost userid goldengate, password password

Reperror default, discard

Discardfile. / dirrpt/ext_in.dsc,APPEND,MEGABYTES 1000

MAP XIANYEZHAO.T_INI, TARGET CAL.T_INI

To start data initialization, you only need to start the extraction process on the source side:

Start einixyz

4.9 the target side creates Replicat4.9.1 to create databases and table statements manually

Create the database:

Create database cal

To create a table, you need to convert oracle format to mysql format. Here are some of the contents:

1.number needs to be converted to DECIMAL

2.varchar2 needs to be converted to varchar

3.date needs to be converted to datetime

4.mysql does not support the conversion of NUMBER (38J 0) to DECIMAL (38J 0), so you need to convert * to the maximum value of oracle, 38 bits long, DECIMAL (38J 0).

5. Since the mysql partition field must be of type int, the following HASH (UIN) cannot partition hash.

The 6.mysql index has no concept of concurrency and removes the parallel option.

CREATE TABLE CALENDAR_DETAIL

(SEQNO DECIMAL NOT NULL

DETAILSEQNO DECIMAL

LABELID DECIMAL

LABELTYPE DECIMAL (2Jing 0)

MESSTYPE DECIMAL (2Jing 0)

UIN VARCHAR (32)

SERVICEID DECIMAL (8 dint 0)

SPSID VARCHAR (32)

RECTARGET VARCHAR (1024)

STARTSENDTIME DATETIME

SENDMSG VARCHAR (2000)

CREATETIME DATETIME

MODIFYTIME DATETIME

TYPE DECIMAL (38pr 0) DEFAULT 0

);

CREATE INDEX CALENDAR_DETAIL_IDX3_NEW ON CALENDAR_DETAIL (DETAILSEQNO)

CREATE UNIQUE INDEX PK_CALENDAR_DETAIL ON CALENDAR_DETAIL (SEQNO)

CREATE INDEX CALENDAR_DETAIL_IDX1_NEW ON CALENDAR_DETAIL (STARTSENDTIME)

CREATE INDEX CALENDAR_DETAIL_IDX2_NEW ON CALENDAR_DETAIL (UIN)

ALTER TABLE CALENDAR_DETAIL ADD CONSTRAINT PK_CALENDAR_DETAIL PRIMARY KEY (SEQNO)

CREATE TABLE CALENDAR_INFO

(SEQNO DECIMAL NOT NULL

UIN VARCHAR (32)

GID VARCHAR (50)

CREATORUIN VARCHAR (32)

SERVICEID DECIMAL (10dint 0)

SPSID VARCHAR (32)

TITLE VARCHAR (600)

SITE VARCHAR (200)

ISATTACH DECIMAL (2Jing 0)

CONTENT VARCHAR (1500)

STARTDATE DATETIME

ENDDATE DATETIME

STARTTIME DECIMAL (5dint 0)

ENDTIME DECIMAL (5dint 0)

STATUS DECIMAL (2Jing 0)

DATEDESCRIPT VARCHAR (128)

ENABLE DECIMAL (2Jing 0)

AUTHSTATUS DECIMAL (2Jing 0)

LABELID DECIMAL

LABELTYPE DECIMAL (2Jing 0)

SPECIALAPPID DECIMAL

RECMYSMS DECIMAL (2Jing 0)

RECMYEMAIL DECIMAL (2Jing 0)

RECMOBILE VARCHAR (1024)

RECEMAIL VARCHAR (1024)

CREATETIME DATETIME

MODIFYTIME DATETIME

VERSION DECIMAL (8 dint 0)

EXPEND VARCHAR (50)

COMEFROM DECIMAL (5dint 0)

SPECIALTYPE DECIMAL (2Jing 0) DEFAULT 0

);

CREATE INDEX CALENDAR_INFO_IDX6_NEW ON CALENDAR_INFO (SPECIALAPPID)

CREATE INDEX CALENDAR_INFO_IDX1_NEW ON CALENDAR_INFO (UIN)

CREATE INDEX CALENDAR_INFO_IDX7_NEW ON CALENDAR_INFO (LABELID)

CREATE INDEX CALENDAR_INFO_IDX4_NEW ON CALENDAR_INFO (STARTTIME)

CREATE INDEX CALENDAR_INFO_IDX8_NEW ON CALENDAR_INFO (GID)

CREATE INDEX SPECIALTYPE_INDEX ON CALENDAR_INFO (SPECIALTYPE)

CREATE UNIQUE INDEX PK_CALENDAR_INFO ON CALENDAR_INFO (SEQNO)

CREATE INDEX CALENDAR_INFO_IDX5_NEW ON CALENDAR_INFO (ENDTIME)

CREATE INDEX CALENDAR_INFO_IDX3_NEW ON CALENDAR_INFO (ENDDATE)

CREATE INDEX CALENDAR_INFO_IDX2_NEW ON CALENDAR_INFO (STARTDATE)

ALTER TABLE CALENDAR_INFO ADD CONSTRAINT PK_CALENDAR_INFO PRIMARY KEY (SEQNO)

4.9.2 configure the replicat process on the target side

Ggsci > EDIT PARAMS. / GLOBALS

CHECKPOINTTABLE goldengate.ggschkpt

Ggsci > exit

Ggsci > dblogin sourcedb cal@192.168.219.35:3306,userid goldengate, password password

Ggsci > add checkpointtable cal.checkpt

Successfully created checkpoint table cal.checkpt.

4.9.3 create Replicat

Add replicat rep_cal, exttrail. / dirdat/ca, checkpointtable cal.checkpt

4.9.4 configure replicat

Edit params rep_cal

/ * /

REPLICAT rep_cal

SETENV (MYSQL_UNIX_PORT=/mysql/dbdata/mysql.sock)

Sourcedb cal@localhost userid goldengate, password password

SQLEXEC "select CURRENT_TIME ();" EVERY 10 MINUTES

REPORT AT 6:00

Gettruncates

STATOPTIONS RESETREPORTSTATS

Reperror (default,abend)

Discardrollover at 6:00

Numfiles 1000

MAXLONGLEN 8000

Dynamicresolution

Allownoopupdates

SOURCEDEFS. / dirdef/cal.def

GROUPTRANSOPS 100000

MAXTRANSOPS 100000

REPORTCOUNT EVERY 1 MINUTES RATE

Discardfile. / dirrpt/rep_cal.dsc, append, megabytes 1000

MAP XIANYEZHAO.CALENDAR_DETAIL, TARGET CAL.CALENDAR_DETAIL, KEYCOLS (SEQNO)

MAP XIANYEZHAO.CALENDAR_INFO, TARGET CAL.CALENDAR_INFO, KEYCOLS (SEQNO)

/ * /

If you are synchronizing from oracle to oracle, you can start Replica using SCN

Start rep_cal, aftercsn 1138506

If you are from oracle to mysql, start it directly:

Start rep_cal

View commands:

GGSCI (mysqldb1) 20 > info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

REPLICAT RUNNING REP_CAL 00:00:00 00:00:08

GGSCI (mysqldb1) 21 > view report rep_cal

5 Goldengate synchronization test 5.1 start the synchronization process

Start the related process on the source side

GGSCI > start mgr

GGSCI > start ext_cal

GGSCI > start dp_cal

The target side starts the relevant process.

GGSCI > start mgr

GGSCI > start rep_cal

5.2 heterogeneous does not support ddl synchronization

Synchronization from oracle to mysql belongs to heterogeneous architecture and does not support ddl synchronization, including adding and deleting fields, adding and deleting indexes, renaming tables, and table analysis statistics.

If source-side and destination-side ddl operations are involved, both source-side and destination-side operations need to be done manually.

5.3 DML testing

DML synchronization is supported from oracle synchronization to mysql.

5.3.1 Table synchronization without primary key

There are 647581 records in CALENDAR_20131213_LOTTERY. For a table without a primary key, each insert and delete is batch processed according to 100000 records, and the value is determined by the parameter.

The insertion starts at 15:18:43 and ends at 15:21:22 for a total of 159 seconds.

The deletion began at 15:25:07 and ended at 15:28:42 for a total of 215 seconds.

The DML generated during full synchronization is consistent after the start of increments. There is a partial delay in batch synchronization, with 4072 records per second for data insertion and 3012 records for deletion.

5.3.2 Table synchronization with primary key

There are 14358726 records in the CALENDAR_INVITE_INFO table, and the full synchronization starts at 16:36:12. At 16:56:40, the source side inserts 6645999 Magi MySQL target side inserts 3887999 records, there is a delay of 2758000 records. The source side has all completed the insertion of 14358726 and the target side of MySQL has been inserted 8545999. At 17:19:19 time, complete the full amount of data insertion, using 2587 seconds, insert about 5550 pieces per second. Compared with tables without primary keys, it is about 36% faster. Therefore, a primary key must be added to the table requiring synchronization.

Update operation, calendar_detail table has 12042 records, about 3 seconds to complete the synchronization target side, updated to 1. 5.

SQL > select count (1), type from calendar_detail group by type

COUNT (1) TYPE

--

1783 3

10259 0

Update calendar_detail set type=1

Mysql > select count (1), type from calendar_detail group by type

+-+ +

| | count (1) | type | |

+-+ +

| | 12042 | 1 | |

+-+ +

CALENDAR_INVITE_INFO uses the following synchronization script (stored procedure pendant 01):

Declare

Row_num number

Cursor get_data is

Select

SEQNO

CALSEQNO

UIN

INVITERUIN

INVITERALIAS

RECMYSMS

RECMYEMAIL

RECMOBILE

RECEMAIL

STATUS

REFUSERESION

INVITEAUTH

INVITEREMARK

INVITETIME

REMARKTIME

ENABLE

ISDELETED

BEFORETYPE

BEFORETIME

NOTIFYCONF

NEXTSENDDATE

From calendar01.CALENDAR_INVITE_INFO

Begin

Row_num: = 1

For rec in get_data loop

Insert into CALENDAR_INVITE_INFO (

SEQNO

CALSEQNO

UIN

INVITERUIN

INVITERALIAS

RECMYSMS

RECMYEMAIL

RECMOBILE

RECEMAIL

STATUS

REFUSERESION

INVITEAUTH

INVITEREMARK

INVITETIME

REMARKTIME

ENABLE

ISDELETED

BEFORETYPE

BEFORETIME

NOTIFYCONF

NEXTSENDDATE

)

Values (

Rec.SEQNO

Rec.CALSEQNO

Rec.UIN

Rec.INVITERUIN

Rec.INVITERALIAS

Rec.RECMYSMS

Rec.RECMYEMAIL

Rec.RECMOBILE

Rec.RECEMAIL

Rec.STATUS

Rec.REFUSERESION

Rec.INVITEAUTH

Rec.INVITEREMARK

Rec.INVITETIME

Rec.REMARKTIME

Rec.ENABLE

Rec.ISDELETED

Rec.BEFORETYPE

Rec.BEFORETIME

Rec.NOTIFYCONF

Rec.NEXTSENDDATE

);

Row_num: = row_num + 1

-submit every 2000 articles

If mod (row_num, 2000) = 0 then

Commit

End if

End loop

Commit

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.