GoldenGate for Oracle to MySQL
1. Environments;
1. Source Database:
1. Oracle:11g R2;
2. Ip Address:192.168.10.11;
3. Hostname:primary.snda.com;
4. Oracle SID:primary;
2. Target Database:
1. Mysql:5.5.21;
2. Ip Address:192.168.10.55;
3. Hostname:master.snda.com;
2. Oracle to Mysql configuration;
3. Prepare the Environment;
1. Description:
1. The GoldenGate software must be installed on both the source and target systems;
2. The installation includes a sample database and scripts to generate initial data as well as subsequent update operations;
3. The source and target tables are created and loaded with initial data;
4. the GoldenGate Manager processes are also started so that other processes may be configured and started;
2. Prepare the Oracle source environment:
1. 在源数据库创建gg用户,并赋予一定的权限:create user gg identified by gg default tablespace example;grant connect, resource to gg;(一定要保证表/表空间是logging的,select table_name, tablespace_name, logging, owner from dba_tables where owner=’GG’;)
2. 在源数据库的gg用户下创建测试使用的表(脚本是GoldenGate安装目录下的demo_ora_create.sql文件);
3. 在源数据库的测试表中添加测试数据(脚本是GoldenGate目录下的demo_ora_insert.sql文件);
4. Add supplemental logging;
5. Configure the Manager process on the source;
3. Prepare the Mysql target environment:
1. 准备数据;
2. Configure the Manager process;
3. create a user;
4. Configure Initial Data Load using Direct Load;
1. Initial Load Methods:
1. Oracle GoldenGate Methods;
2. Database-specific Methods:
1. Backup/Restore;
2. Export/Import;
3. SQL scripts;
4. Break mirror;
5. Transportable tablespaces;
6. Note:
1. Run a test initial load early on for timing and sizing;
2. Run the actual initial load after starting change capture on the source;
3. Current use;
2. Initial data capture on source:
1. add the initial load capture batch task group:GGSCI> add extract eint, sourceistable;(sourceistable 参数代表这是initial data);
2. configure the initial load capture parameter file:GGSCI> edit params eint;
3. Initial data delivery on target:
1. add the initial load delivery batch task:GGSCI>add replicat rini, specialrun;(specialrun:Create a Replicat special run as a task)
2. configure the initial load delivery parameter file;(assumetargetdefs:假定目标数据库和源数据库表结构一致;discardfile:失败的记录记录的文件;)
4. Define the source.def:
1. 定义defgen参数文件;
2. 执行定义参数文件:dengen paramfile ./dirprm/defgen.prm,会生成表结构定义文件./dirdef/source.def;
3. 拷贝到目标数据库相应的目录下;
5. Execute the initial load process:
1. start extract process:start extract eini;
2. view the results on the target system:view report rini;
3. 在目标数据库中查看数据;
5. Configure Change Capture(online mode);
1. Extract can be configured to:
1. Capture changed data from database logs;
2. Capture data directly from source tables for initial data load;
3. Write the data to a local or remote trail or file;
2. add the extract group:add extract emsq, tranlog, begin now, threads ;
3. create the extract parameter file;
4. define the GoldenGate trail:add rmttrail ./dirdat/, extract emsq, megabytes 5;(default 10M size)
5. start the capture process:start extract eora;
6. 可以通过view report eorasd查看日志;
6. Configure Change Delivery;
1. set up the checkpoint table;
1. create a GLOBALS file on the target system;(exit and save)
2. activate the GLOBALS parameters;
2. Configure Change Delivery;
1. add the replicat group;
2. create replicat parameter file;
3. start the replicat process;
7. Generate Activity and Verify the Results;
1. exceute miscellaneous update, insert and delete operations on source system;
2. verify results on the source system;
3. verify results on the target system;
4. turn off error handling;
8. Parameter Files Knowlege:
1. Editing Parameter Files:
1. Edit parameter files to configure GoldenGate processes;
2. The GLOBALS parameter file is identified by its file path:GGSCI>edit params ./GLOBALS;
3. Manager and utility parameter files are identified by keywords:GGSCI>edit params mgr/defgen;
4. Extract and Replicat parameter files are identified by the process groiup name:GGSCI>edit params ;
2. GLOBALS Versus Process Parameters:
1. GLOBALS parameter apply to all processes:
1. set when Manager starts;
2. reside in /GOLBALS;
2. Process parameters apply to a specific process(Manager, Extract, Server Collector, Replicat, Utilities):
1. set when the process starts;
2. override GOLBALS settings;
3. reside by defaults in the dirprm directory in files named .prm;
4. most apply to all tables processed but some can be specified at the table level;
3.
GLOBALS Parameters:
1. Control things common to all processes in a GoldenGate instance;
2. Can be overridden by parameters at the process level;
3. Must be created before any processes are started;
4. Must exit GGSCI to save;
5. Once set, rarely changed;
6. MGRSERVNAME:defines a unique Manager service name;
7. CHECKPOINTTABLE:Defines the table name used for Replicat’s checkpoint table;