-- 参考文档 How to Setup Oracle GoldenGate - Oracle to Oracle Extract and Replicat (step by step Classic Extract & Replicat setup) and Tutorials for Heterogeneous Databases (文档 ID 1484793.1) How to Replicate Data Between Oracle and MysqL Database? (文档 ID 1605674.1) 注意： 本案例中使用的脚本在$GGS_HOME底下都可以找到 /***** 环境预准备 **********************/ ==== 创建用户 useradd -u 2300 -g MysqL -d /home/oggs -m oggs echo -n Oracle_123 |passwd --stdin oggs su - oggs vi .bash_profile GGS_HOME=/u01/app/oggs; export GGS_HOME PATH=${PATH}:$GGS_HOME:/usr/local/MysqL/bin LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:/lib:/usr/lib:/usr/local/lib:$GGS_HOME export LD_LIBRARY_PATH ==== 创建目录 mkdir -p /u01/app/oggs chown -R oggs:MysqL /u01/app/oggs === 配置SHELL Limits 限制 1. Create a file labeled 99-grid-oracle-limits.conf within /etc/security/limits.d/ as follows (1). 创建个空文件 touch /etc/security/limits.d/99-oracle-grid-limits.conf (2). 文件中输入以下内容 cat >> /etc/security/limits.d/99-oracle-grid-limits.conf <<EOF oggs soft nproc 16384 oggs hard nproc 16384 oggs soft nofile 1024 oggs hard nofile 65536 oggs soft stack 10240 oggs hard stack 32768 EOF 2. create a shell script labeled oracle-grid.sh within /etc/profile.d/ to create the ulimits for the oracle and grid user. (1). 创建个空文件 touch /etc/profile.d/oracle-grid.sh (2). 编辑文件 vi /etc/profile.d/oracle-grid.sh #Setting the appropriate ulimits for oracle and grid user if [ $USER = "oracle" ] || [ $USER = "grid" ] || [ $USER = "oggs" ]; then if [ $SHELL = "/bin/ksh" ]; then ulimit -p 16384 ulimit -n 65536 else ulimit -u 16384 -n 65536 fi fi /***** 安装 GG ***********/ su - oggs cd /orasoft/ora11g unzip ggs_Linux_x64_MysqL_64bit.zip cd /u01/app/oggs tar -xvf /orasoft/ora11g/ggs_Linux_x64_MysqL_64bit.tar /**** 模拟插入部分测试数据 *************/ --- 源库 1. 创建测试用户 create user demo identified by demo; grant connect,resource to demo; conn demo/demo; 2. 创建测试数据 (1). 创建表（demo_ora_create.sql） sql> @demo_ora_create.sql (2). 导入测试数据 sql> @demo_ora_insert.sql /********* GG 环境初始化 ******************/ -- 源库（oracle） (1). 创建子目录 cd $GGS_HOME ggsci create subdirs (2). 确认gg可以正常工作 cd $GGS_HOME ggsci dblogin userid system,password Oracle_123 info mgr stop mgr edit param mgr PORT 7809 DYNAMICPORTLIST 7819-7839 PURGEOLDEXTRACTS /u01/app/oggs/dirdat start mgr info mgr -- 目标库(MysqL) (1). 创建子目录 cd $GGS_HOME ggsci create subdirs (2). 确认gg可以正常工作 cd $GGS_HOME ggsci dblogin sourcedb MysqL@localhost,userid root,password Oracle_123 info mgr edit param mgr PORT 7809 DYNAMICPORTLIST 7819-7839 PURGEOLDEXTRACTS /u01/app/oggs/dirdat ACCESSRULE,PROG *,IPADDR *,ALLOW ## 此项12.2之后需要设置，用于数据initialization,否则会出现access denied报错. start mgr info mgr 3. 源库配置 (1) 数据库级附加日志 alter database archivelog; alter database force logging; ## 非必须，强制日志是针对直接路径数据加载的 select supplemental_log_data_min from v$database; alter database add supplemental log data; (2) 启用ENABLE_GOLDENGATE_REPLICATION ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION = TRUE SCOPE=BOTH; (3) 创建用于数据同步的用户 create user ggs identified by ggs; grant dba to ggs; ## 这里为了简单给了DBA权限，并未考虑单独创建表空间，实际应该创建单独的表空间. (如果考虑到权限控制可以不授予DBA，授予以下权限即可,一般其直接给予DBA权限即可，这里作为备注 create user ggs identified by ggs default tablespace users temporary tablespace temp; grant connect,resource,create session,alter session to ggs; grant select any dictionary,select any table,create table to ggs; grant alter any table to ggs; grant execute on utl_file to ggs; grant flashback any table to ggs; grant execute on dbms_flashback to ggs; grant insert,update,delete on target.tcustmer to ggs; grant insert,delete on target.tcustord to ggs; ) (4) 启用表级附加日志 cd $GGS_HOME ggsci dblogin userid ggs,password ggs list tables * add trandata demo.tcustomer add trandata demo.tcustord 注意： ( set linesize 300 col owner format a10 col log_group_name format a15 col table_name format a25 col LOG_GROUP_TYPE format a30 select owner,log_group_name,table_name,log_group_type,always from dba_log_groups where owner='DEMO'; ##等同于 alter table demos.test_users add supplemental log group <log_group_name> (user_id) always; ) (5) 确认表是否添加附件日志 info trandata demo.* 4. 目标库配置 (1) 创建用于数据同步的用户 MysqL -uroot -pOracle_123 create database ggs; grant all on *.* to ggs@'localhost' identified by 'ggs'; flush privileges; (2) 创建用于存储数据的用户 create database gateway; (3) 导入数据并验证 cd $GGS_HOME MysqL gateway -uggs -pggs < demo_MysqL_create.sql describe TCUSTMER; describe TCUSTORD; /******** Initial Data Load using Direct Load Method *******/ -- 源库(oracle) 1. Configure the initial load capture parameter file edit params lxini -- -- GoldenGate Initial Data Capture -- for TCUSTMER and TCUSTORD -- EXTRACT lxini SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK) USERID ggs,PASSWORD ggs RMTHOST 192.168.30.110,MGRPORT 7809 RMTTASK REPLICAT,GROUP rini TABLE demo.TCUSTMER; TABLE demo.TCUSTORD; 2. Add the initial load capture batch task group cd $GGS_HOME ggsci add extract lxini,SOURCEISTABLE info extract *,TASKS 3. 准备 SOURCEDEFS 文件 (1) DEFGEN parameter file edit params defgen DEFSFILE ./dirdef/oracle.def USERID ggs,password ggs TABLE demo.TCUSTMER; TABLE demo.TCUSTORD; (2) Defgen command ./defgen paramfile ./dirprm/defgen.prm (3)文件拷贝到目标库 scp ./dirdef/oracle.def 192.168.30.110:/u01/app/oggs/dirdef -- 目标库(MysqL) 4. Configure the initial load delivery parameter file edit params rini -- -- Change Delivery parameter file for -- TCUSTMER and TCUSTORD changes -- REPLICAT rini TARGETDB gateway@localhost:3306,USERID ggs,PASSWORD ggs SOURCEDEFS ./dirdef/oracle.def DISCARDFILE ./dirrpt/rini.dsc,PURGE MAP demo.TCUSTMER,TARGET gateway.TCUSTMER; MAP demo.TCUSTORD,TARGET gateway.TCUSTORD; 5. Add the initial load delivery batch task group ADD REPLICAT rini,SPECIALRUN INFO REPLICAT *,TASKS -- 源库（oracle） 6. Execute the initial load process (源库) START EXTRACT lxini -- 目标库（MysqL） 7. Verify the results (目标库) view REPORT rini MysqL gateway -u ggs -pggs select * from TCUSTMER; /******** Configure Change Capture *******/ 以下操作是在源库（oracle）进行 1. Create the Extract parameter file edit params lxora -- -- Change Capture parameter file to capture -- TCUSTMER and TCUSTORD Changes -- EXTRACT lxora USERID ggs,MGRPORT 7809 RMTTRAIL ./dirdat/om TABLE demo.TCUSTMER; TABLE demo.TCUSTORD; 2. Add the Extract group ADD EXTRACT lxora,TRANLOG,BEGIN NOW,THREADS 1 3. Define the GoldenGate trail (add the trail that will store the changes on the target.) ADD RMTTRAIL ./dirdat/om,EXTRACT lxora,MEGABYTES 5 4. Start the capture process START EXTRACT lxora 5. verify the results INFO EXTRACT lxora INFO EXTRACT lxora,DETAIL STATS EXTRACT lxora VIEW REPORT lxora /********** Configure Change Delivery ***************/ --- Set up the checkpoint table 以下操作需要在目标库（MysqL）进行 1. Create a GLOBALS file on the target system edit params ./GLOBALS CHECKPOINTTABLE ggs.ggschkpt 2. Activate the GLOBALS parameters 注意：you must exit the session in which the changes were made. Execute the following command to exit GGSCI. exit 3. Add a Replicat checkpoint table DBLOGIN SOURCEDB ggs@localhost:3306 USERID ggs,PASSWORD ggs ADD CHECKPOINTTABLE -- Configure Change Delivery 4. Create Replicat parameter file edit params rmsq -- -- Change Delivery parameter file to apply -- TCUSTMER and TCUSTORD Changes -- REPLICAT rmsq TARGETDB gateway@localhost:3306,PASSWORD ggs HANDLECOLLISIONS SOURCEDEFS ./dirdef/oracle.def DISCARDFILE ./dirrpt/rmsq.dsc,TARGET gateway.TCUSTORD; 5. Add the Replicat group ADD REPLICAT rmsq,EXTTRAIL ./dirdat/om ## 另外一种是 add replicat rmsq,NODBCHECKPOINT,exttrail ./dirdat/om 6. Start the Replicat process START REPLICAT rmsq 7. verify the results INFO REPLICAT * info all /********* Generate Activity and Verify Results **********************/ 1. Execute miscellaneous update,insert,and delete operations(源库) cd $GGS_HOME sqlplus demo/demo @demo_ora_misc 2. Verify results on the source system (源库) sql> select * from tcustmer; sql> select * from tcustord; sql> exit Shell> ggsci GGSCI> SEND EXTRACT lxora,REPORT GGSCI> VIEW REPORT lxora 3. Verify your results on the target system Shell> cd $GGS_HOME Shell> MysqL gateway -u ggs -pggs MysqL> select * from TCUSTMER; MysqL> select * from TCUSTORD; MysqL> exit Shell> ggsci GGSCI> SEND REPLICAT rmsq,REPORT GGSCI> VIEW REPORT rmsq 4. Turn off initial load error handling for the running delivery process (需要执行，这个只是为初始同步设置的，一旦数据同步完成，正常运行中就不应该再使用这个参数以掩盖可能的错误) SEND REPLICAT rmsq,NOHANDLECOLLISIONS 5. Remove initial load error handling from the parameter file EDIT PARAMS rmsq 后记：注意一点，这个例子中Oracle to MysqL 只使用extract,replicat进程，并未使用data pump进程，这个没有问题，后续调试。 ============================================================================================================================== 附记：下列问题是Oracle to Oracle 的，MysqL也需要留意或者测试，留在这个文档中，作为之后的参考 1. 目标库 级联更新带来的问题 (触发器或者级联操作(外键)) 两种解决办法： > 数据库内完成，要同步的表使用 alter table ... disable constraint ... alter trigger ... disable > 如果目标数据库是Oracle，并且Oracle版本是10.2.0.5或者11.2.0.2之后，使用的优势GG11g，还可以使用GG11所提供的suppresstriggers选项 DBOPTIONS suppresstriggers 处于通用性考虑，推荐使用第一种方式 2. checkpoint table 两种方式: > 所有replicat 使用同一个checkpoint table 这种方式会用到global文件，不同于extract,replicat（放置在$GGS_HOME/dirprm）,GLOBALS文件是放置在$GGS_HOME底下的 Step： 1). $GGS_HOME下创建文件GLOBALS cd $GGS_HOME vi GLOBALS ## edit ./GLOBALS CheckPointTable ggt.chkpt GGSCHEMA GGT 2). 创建表ggt.chkpt cd $GGS_HOME ggsci dblogin userid ggt,password ggt add checkpointtable ggt.chkpt 3). 确认下 info checkpointtable ggt.chkpt 注意：实际创建了两张表 ggt.chkpt & ggt.chkpt_lox > Replicat 级别的checkpointtable 可以在add replicat 语句中直接加入 checkpointtable,相当于为每个Replicat进程定义一个专属Checkpoint Table ggsci> add replicat rb,exttrail dirdata/rp,checkpointtable ggt.rbckt 但是注意：这个命令并没有自动创建这个检查点表，而且没有任何提示，add replicat rb时不会报错，但delete replicat rb时，会提示找不到表,正确的做法如下： 1). create table ggt.rbckt as select * from ggt.chkpt where 0=1; 2). create table ggt.rbckt_lox as select * from ggt.chkpt_lox where 0=1 3). add replicat rb,checkpointtable ggt.rbckt ## 待验证这种方式 3. 使用的脚本内容 -- 源库 (1) 创建测试表：@demo_ora_create.sql DROP TABLE tcustmer; CREATE TABLE tcustmer ( cust_code VARCHAR2(4),name VARCHAR2(30),city VARCHAR2(20),state CHAR(2),PRIMARY KEY (cust_code) USING INDEX ); DROP TABLE tcustord; CREATE TABLE tcustord ( cust_code VARCHAR2(4),order_date DATE,product_code VARCHAR2(8),order_id NUMBER,product_price NUMBER(8,2),product_amount NUMBER(6),transaction_id NUMBER,PRIMARY KEY (cust_code,order_date,product_code,order_id) USING INDEX ); (2). 导入数据(demo_ora_insert.sql) INSERT INTO tcustmer VALUES ( 'WILL','BG SOFTWARE CO.','SEATTLE','WA' ); INSERT INTO tcustmer VALUES ( 'JANE','ROCKY FLYER INC.','DENVER','CO' ); INSERT INTO tcustord VALUES ( 'WILL',TO_DATE ('1994-09-30 15:33:00','YYYY-MM-DD HH24:MI:SS'),'CAR',144,17520,3,100 ); INSERT INTO tcustord VALUES ( 'JANE',TO_DATE ('1995-11-11 13:52:00','PLANE',256,133300,1,100 ); COMMIT; (3). 最后模拟测试数据变化时使用(demo_ora_misc.sql) INSERT INTO tcustmer VALUES ( 'DAVE','DAVE''S PLANES INC.','TALLAHASSEE','FL' ); INSERT INTO tcustmer VALUES ( 'BILL','BILL''S USED CARS','CO' ); INSERT INTO tcustmer VALUES ( 'ANN','ANN''S BOATS','WA' ); COMMIT; INSERT INTO tcustord VALUES ( 'BILL',TO_DATE ('1995-12-31 15:00:00',765,15000,100 ); INSERT INTO tcustord VALUES ( 'BILL',TO_DATE ('1996-01-01 00:00:00','TRUCK',333,26000,15,100 ); INSERT INTO tcustord VALUES ( 'DAVE',TO_DATE ('1993-11-03 07:51:35',600,135000,2,200 ); COMMIT; UPDATE tcustord SET product_price = 14000.00 WHERE cust_code = 'BILL' AND order_date = TO_DATE ('1995-12-31 15:00:00','YYYY-MM-DD HH24:MI:SS') AND product_code = 'CAR' AND order_id = 765; UPDATE tcustord SET product_price = 25000.00 WHERE cust_code = 'BILL' AND order_date = TO_DATE ('1996-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS') AND product_code = 'TRUCK' AND order_id = 333; UPDATE tcustord SET product_price = 16520.00 WHERE cust_code = 'WILL' AND order_date = TO_DATE ('1994-09-30 15:33:00','YYYY-MM-DD HH24:MI:SS') AND product_code = 'CAR' AND order_id = 144; UPDATE tcustmer SET city = 'NEW YORK',state = 'NY' WHERE cust_code = 'ANN'; COMMIT; DELETE FROM tcustord WHERE cust_code = 'DAVE' AND order_date = TO_DATE ('1993-11-03 07:51:35','YYYY-MM-DD HH24:MI:SS') AND product_code = 'PLANE' AND order_id = 600; DELETE from tcustord WHERE cust_code = 'JANE' AND order_date = TO_DATE ('1995-11-11 13:52:00','YYYY-MM-DD HH24:MI:SS') AND product_code = 'PLANE' AND order_id = 256; COMMIT; DELETE FROM tcustord; ROLLBACK; --- 目标库 (4). MysqL库创建表定义，demo_MysqL_create.sql CREATE TABLE TCUSTMER ( CUST_CODE VARCHAR(4) NOT NULL,NAME VARCHAR(30),CITY VARCHAR(20),STATE CHAR(2),PRIMARY KEY (CUST_CODE) ); -- DROP TABLE TCUSTORD; CREATE TABLE TCUSTORD ( CUST_CODE VARCHAR(4) NOT NULL,ORDER_DATE DATETIME NOT NULL,PRODUCT_CODE VARCHAR(8) NOT NULL,ORDER_ID INTEGER NOT NULL,PRODUCT_PRICE DECIMAL(8,PRODUCT_AMOUNT INTEGER,TRANSACTION_ID FLOAT,PRIMARY KEY (CUST_CODE,ORDER_DATE,PRODUCT_CODE,ORDER_ID) );
