GG12.2 Oracle to MySQL 简明操作命令

-- 参考文档
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)
);

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!