Oracle migration to MySQL solution--ogg (goldengate)

Source-side Oracle 11.2.0.4 ogg12c
ip:10.200.3.193 System CentOS 7.1

1. Create a source table and some initialization data
Su-oracle
Sqlplus/as SYSDBA
Sql>alter database add supplemental log data;
Sql>select supplemental_log_data_min from V$database;

Sql>alter database forcelogging;

Sql> select Supplemental_log_data_min from V$database;
NO

sql> ALTER DATABASE add supplemental log data;

Database altered.
sql> alter system switch logfile;

System altered.

Sql> select Supplemental_log_data_min from V$database;
YES
sql> Create tablespace GGs;
Tablespace created.
Sql> create user GGs identified by GGs default Tablespace GGs;
User created.
Sql> Grant CONNECT,RESOURCE,DBA to GGs;
Sql> alter system set undo_retention=86400;
System altered.

Sql> Grant Flashback any table to GGs;

Configure the parameters for silent installation---refer to the installation of the centos7.1 goldengate12c

2. Create the manager parameter file on source side and specify the port and some required parameter options:

Cd/u01/app/ggs

./ggsci
Create a related directory: Ggsci (testtech3-193) 1> Create Subdirs

Configure Mgr
View params Mgr

Port 7809
Dynamicportlist 7810-7820,7830

Starting Start Mgr

Ggsci (testtech3-193) 1> dblogin userid ggs Password GGs
Successfully logged into database.
Ggsci (testtech3-193) 1> REGISTER EXTRACT ext1 DATABASE
Ggsci (testtech3-193) 2> add Trandata workflow.act_ru_execution

3. Edit the Defgen file
Vi/u01/app/ggs/dirprm/defgen.prm

Defsfile/u01/app/ggs/dirdef/table_del,purge
UserID GGs, Password GGs
Table workflow.act_ru_execution;

Cd/u01/app/ggs

Defgen PARAMFILE/U01/APP/GGS/DIRPRM/DEFGEN.PRM

cd/app/ggs/dirdef/
Copy the files to the/app/ggs/dirdef/on the target side

4. Execute the following command on source to add a extract process called Init for data initialization:

Cd/u01/app/ggs
Ggsci (testtech3-193) >./ggsci
Ggsci (testtech3-193) >add extract init,sourceistable
Ggsci (testtech3-193) >info extract *, Task
Ggsci (testtech3-193) >edit params init

Extract Init
UserID GGs Password GGs
Rmthost 10.200.3.178,mgrport 8809
Rmttask Replicat,group Repinit
Table workflow.act_ru_execution;

Destination Target mysql5.6 Ogg 12c

ip:10.200.3.178 System CentOS 7.1

1. Create target targets database and corresponding tables
Mysql-uroot-p
Create a database for OGG use login, for example to store checkpoint table

mysql> CREATE DATABASE Ogg;
Query OK, 1 row Affected (0.00 sec)

Mysql> grant all in to [e - Mail protected] identified by ' GGs ';
Query OK, 0 rows Affected (0.00 sec)

mysql> flush Privileges;
Query OK, 0 rows affected (0.02 sec)
2. Create the target-end target app process
Ggsci (techtest3-178) >dblogin sourcedb [email protected]:3306 userid ogg password GGs
Ggsci (techtest3-178) >add checkpointtable ogg.ck_table

Ggsci (techtest3-178) >add replicat repdel,exttrail/u01/app/ggs/dirdat/dl,checkpointtable ogg.ck_table
Ggsci (techtest3-178) >edit params Repdel

Replicat Repdel
targetdb [email protected]:3306 userid ogg password GGs
Handlecollisions
Sourcecharset ZHS16GBK----The source-side character set, be sure to setup, especially if the source and target side character sets are inconsistent, this parameter must be set.
Sourcedefs/u01/app/ggs/dirdef/table_def
Discardfile/u01/app/ggs/dirrpt/repdel.dsc,purge
Map workflow.act_ru_execution, Target activiti.act_ru_execution;

Because it is a heterogeneous environment, to specify the Sourcedefs parameter, use the data definition file of the source database.
The Handlecollisions parameter handles the conflict between the results of the initialization load and the real-time changes to the captured data synchronously.

Target side: View params Mgr

Port 8809
Dynamicportlist 8840-8850
Accexxxule, PROG replicat, ipaddr 10.200.3.139 allow

3. Target end target Create initialization load delivery parameter file

Ggsci (techtest3-178) >add Replicat Repinit,specialrun

Ggsci (techtest3-178) >edit params repinit

Replicat Repinit

targetdb [email protected]:3306 userid ogg password GGs

Sourcedefs/u01/app/ggs/dirdef/table_def

Discardfile/u01/app/ggs/dirrpt/repinit.dsc,purge

SqlExec "SET foreign_key_checks=0"

Map workflow.act_ru_execution, Target activiti.act_ru_execution;

4. Add a extract group on the source side of a source
Ggsci (testtech3-193) > ADD EXTRACT extdel,tranlog, BEGIN now
Ggsci (testtech3-193) >edit params Extdel
EXTRACT Extdel
UserID GGs, Password GGs
Gettruncates
Reportcount every 1 MINUTES, rate
Numfiles 5000
Discardfile./DIRRPT/EXTDEL.DSC, APPEND, megabytes 1000
Warnlongtrans 2h, Checkinterval 3m
Exttrail./dirdat/dl
Tranlogoptions Convertucs2clobs
Threadoptions Maxcommitpropagationdelay 60000
Dboptions Allowunusedcolumn
Table workflow.act_ru_execution;
Ggsci (testtech3-193) >add exttrail./dirdat/dl, extract Extdel, megabytes 500

Ggsci (testtech3-193) >add EXTRACT Dpedel, Exttrailsource./dirdat/dl

Ggsci (testtech3-193) >edit params Dpedel

EXTRACT Dpedel
Rmthost 10.200.3.178, Mgrport 8809, compress
PASSTHRU
Numfiles 50000
Rmttrail./dirdat/dl
Dynamicresolution
Table workflow.act_ru_execution;

Ggsci (testtech3-193) >add rmttrail./dirdat/dl, EXTRACT Dpedel, megabytes 500

The following is the start of the extraction and delivery process, because Oracle to MySQL initialization does not do the same as Oracle, such as the use of SCN.
The general idea is that the process extracts the data and then initializes the operation. When the initialization is over, the application process will conflict, handle the conflict, and resolve the problem. Finally do the data comparison.
Ggsci (testtech3-193) >start Extdel
Ggsci (testtech3-193) >start Dpedel

5. Source-side Source initiates initialization of loading data
Initialize Load Extraction task init configured before source side start:
Ggsci (testtech3-193) >start Init

On the target end, look at the report of the initialization data load replication process to confirm the load success:

Ggsci (techtest3-178) > View report repinit

The process restarts automatically after Init initialization is complete

6. End Target Start Repdel

Confirm that the handlecollisions to change the synchronization Replicat process Repdel is closed after the initialization data load is complete.

Ggsci (techtest3-178) 1> send Repdel,nohandlecollisions

The handlecollisions parameter in the Repdel parameter file is removed to prevent handlecollisions from being enabled again Repdel the next time it starts.

Final validation of data: initialization data, change data, etc.

Oracle migration to MySQL solution--ogg (goldengate)