基于OGG实现Oracle实时同步MySQL的全过程

chown -r oracle:oinstall /oraogg
chmod 775 -r /oraogg

vi .bash_profile

export gg_home=/oraogg/goldengate
export path=$path:$home/bin:$oracle_home/bin:$gg_home
export ld_library_path=$oracle_home/lib:/lib:/usr/lib
alias ggsci='cd $gg_home;ggsci'

source .bash_profile

3.静默安装
cd /oraogg/fbo_ggs_linux_x64_oracle_shiphome/disk1/response
vi /oraogg/fbo_ggs_linux_x64_oracle_shiphome/disk1/response/oggcore.rsp

--只修改如下3个地方即可。
install_option=ora19c
software_location=/oraogg/goldengate
inventory_location=/u01/app/orainventory
/u01/app/oracle/product/19.3.0/db_1

/oraogg/fbo_ggs_linux_x64_oracle_shiphome/disk1/runinstaller -silent
-responsefile /oraogg/fbo_ggs_linux_x64_oracle_shiphome/disk1/response/oggcore.rsp

cd /ogg
./ggsci
create subdirs

yum install https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm
yum install mysql-community-server --nogpgcheck

groupadd -g 1005 ogg
useradd -g ogg -u 1005 -m oggm
mkdir -p /oggmysql
chown oggm:ogg /oggmysql

cd /oggmysql/
unizp 213000_ggs_linux_x64_mysql_64bit.zip
tar -xf ggs_linux_x64_mysql_64bit.tar

[root@ogg21all oggmysql]# ./ggsci -v
oracle goldengate command interpreter for mysql
version 21.3.0.0.0 oggcore_21.3.0.0.0_platforms_210728.1047
oracle linux 7, x64, 64bit (optimized), mysql on jul 28 2021 18:17:46
copyright © 1995, 2021, oracle and/or its affiliates. all rights reserved.

su - oggm
$ /oggmysql/ggsci
ggsci (ogg21all) 1> create subdirs

vi .bash_profile

export gg_home=/oggmysql
export path=$path:$home/bin:$gg_home
alias ggsci='cd $gg_home;ggsci'

source .bash_profile

alter database add supplemental log data;
alter database add supplemental log data (all) columns;
alter database force logging;
alter system set enable_goldengate_replication=true;

##修改归档路径
mkdir -p /home/oracle/arch
sys@oradb> alter system set log_archive_dest_1=‘location=/home/oracle/arch';
system altered.
sys@oradb> archive log list
database log mode archive mode
automatic archival enabled
archive destination /u01/app/oracle/arch
oldest online log sequence 3
next log sequence to archive 5
current log sequence 5

sys@oradb> select name,supplemental_log_data_min , force_logging, log_mode from v$database;
name               supplemental_log force_logging     log_mode
------------------ ---------------- ----------------- ------------------------
orclcdb            yes              yes               archivelog

##关闭回收站
sql> show parameter recyclebin;
sql> alter system set recyclebin = off scope = spfile;
sql> show recyclebin;
sql> purge recyclebin;

-- ogg管理用户
sys@oradb> alter session set container=orclpdb1;

create user ogg identified by oracle;
grant dba to ogg;
grant select any dictionary to ogg;
grant execute on sys.dbms_lock to ogg;
grant select any transaction to ogg;
grant select any table to ogg;
grant flashback any table to ogg;
grant alter any table to ogg;

exec dbms_goldengate_auth.grant_admin_privilege('ogg','*',true); 


-- 业务用户
create user rptuser identified by oracle;
grant dba to rptuser ;
grant select any dictionary to rptuser;
grant execute on sys.dbms_lock to rptuser;

1.生成mysql端ddl语句
可以使用navicat的数据传输功能或其它工具直接从oracle端生成mysql类型的建表语句如下：

mysql -uroot -proot
create database rptdb;

mysql -uroot -proot -h 172.18.12.91 -d rptdb -f < ddl.sql


2.ddl语句如下
set names utf8; 
set foreign_key_checks = 0; 

drop table if exists `addresses`; 
create table `addresses` 
( `address_id` decimal(12, 0) not null, 
`customer_id` decimal(12, 0) not null, 
`date_created` datetime not null, 
`house_no_or_name` varchar(60) null, 
`street_name` varchar(60) null, 
`town` varchar(60) null, 
`county` varchar(60) null, 
`country` varchar(60) null, 
`post_code` varchar(12) null, 
`zip_code` varchar(12) null, 
primary key (`address_id`), 
index `address_cust_ix`(`customer_id` asc) 
);

----- table structure for card_details ----
drop table if exists `card_details`; 
create table `card_details` 
( `card_id` decimal(12, 0) not null, 
`customer_id` decimal(12, 0) not null, 
`card_type` varchar(30) not null, 
`card_number` decimal(12, 0) not null, 
`expiry_date` datetime not null, 
`is_valid` varchar(1) not null, 
`security_code` decimal(6, 0) null, 
primary key (`card_id`), 
index `carddetails_cust_ix`(`customer_id` asc) 
);


---- table structure for customers ----
drop table if exists `customers`; 
create table `customers` 
( `customer_id` decimal(12, 0) not null, 
`cust_first_name` varchar(40) not null, 
`cust_last_name` varchar(40) not null, 
`nls_language` varchar(3) null, 
`nls_territory` varchar(30) null, 
`credit_limit` decimal(9, 2) null, 
`cust_email` varchar(100) null, 
`account_mgr_id` decimal(12, 0) null, 
`customer_since` datetime null, 
`customer_class` varchar(40) null, 
`suggestions` varchar(40) null, 
`dob` datetime null, `mailshot` varchar(1) null, 
`partner_mailshot` varchar(1) null, 
`preferred_address` decimal(12, 0) null, 
`preferred_card` decimal(12, 0) null, 
primary key (`customer_id`), 
index `cust_account_manager_ix`(`account_mgr_id` asc), 
index `cust_dob_ix`(`dob` asc), 
index `cust_email_ix`(`cust_email` asc) 
);

[root@ogg21all /]# su - oracle
[oracle@ogg21all ~]$ ggsci
ggsci (ogg21all) 2> edit params mgr
port 7809

add credentialstore
alter credentialstore add user ogg@172.18.12.90/oradb, password oracle alias ora19c
info credentialstore

ggsci (ogg21all) 7> info credentialstore
reading from credential store:
default domain: oraclegoldengate
alias: ora19c
userid: ogg@172.18.12.90/oradb

dblogin useridalias ora19c
add schematrandata rptuser
info schematrandata rptuser
list tables rptuser.*

ggsci (ogg21all) 1> edit params mgr
port 8809

ggsci (ogg21all) 2> start mgr
manager started.

ggsci (ogg21all) 3> info all
program status group lag at chkpt time since chkpt
manager running

oracle全量同步到mysql
注意：在此阶段，源端需要停业务，不能产生新数据。

-- oracle端
edit params ext0
extract ext0
useridalias ora19c
rmthost 127.0.0.1,mgrport 8809
rmttask replicat,group rep0
table rptuser.addresses;
table rptuser.card_details;
table rptuser.customers;

add extract ext0 ,sourceistable
delete extract ext0



-- mysql端
edit params rep0
replicat rep0
targetdb rptdb@172.18.12.91:3306 userid root password root
map rptuser.addresses, target rptdb.addresses;
map rptuser.card_details, target rptdb.card_details;
map rptuser.customers, target rptdb.customers;

add replicat rep0 ,specialrun
delete replicat rep0


-- 直接启动源端ext0即可，rep0不用启动,mgr会自动启动它，等同步结束，它会自动关闭
start ext0


-- 查看日志
info rep0,showch
view report rep0

--登录验证数据
mysql -uroot -proot -h 172.18.12.91 -d rptdb

mysql> select count(*) from addresses;
+----------+
| count(*) |
+----------+
|      150 |
+----------+
1 row in set (0.04 sec)

mysql> select count(*) from card_details;
+----------+
| count(*) |
+----------+
|      150 |
+----------+
1 row in set (0.05 sec)

mysql> select count(*) from customers;
+----------+
| count(*) |
+----------+
|      100 |
+----------+
1 row in set (0.04 sec)

ggsci (ogg21all as ogg@oradb) 21> info ext0

extract    ext0      last started 2023-11-25 18:58   status stopped
checkpoint lag       not available
log read checkpoint  table rptuser.customers
                     2023-11-25 18:58:46  record 100
task                 sourceistable

add extract exto integrated tranlog begin now
add exttrail ./dirdat/eo extract exto

dblogin useridalias ora19c
register extract exto database

edit params exto
extract exto
useridalias ora19c
tranlogoptions fetchpartiallob
exttrail ./dirdat/eo
table rptuser.addresses;
table rptuser.card_details;
table rptuser.customers;

start exto
ggsci (ogg21all as ogg@oradb) 12> info all
program status group lag at chkpt time since chkpt
manager running
extract running exto 00:02:19 00:00:08

edit params repm
replicat repm
targetdb rptdb@172.18.12.91:3306 userid root password root
map rptuser.addresses, target rptdb.addresses;
map rptuser.card_details, target rptdb.card_details;
map rptuser.customers, target rptdb.customers;

add rep repm, exttrail /oraogg/goldengate/dirdat/eo, nodbcheckpoint
delete rep repm

start repm

1.oracle端测试产生数据
sys@oradb> delete from rptuser.addresses where address_id=150;

ggsci (ogg21all as ogg@oradb) 21> stats exto,total

sending stats request to extract group exto ...

start of statistics at 2023-11-25 19:36:13.

output to ./dirdat/eo:

extracting from rptuser.addresses to rptuser.addresses:

*** total statistics since 2023-11-25 19:25:02 ***
    total inserts                              0.00
    total updates                              0.00
    total deletes                              1.00
    total upserts                              0.00
    total discards                             0.00
    total operations                           1.00

end of statistics.

2.mysql端插入数据
mysql -uroot -proot -h 172.18.12.91 -d rptdb
mysql> select count(*) from addresses;

ggsci (ogg21all) 18> stats repm,total

sending stats request to replicat group repm ...

start of statistics at 2023-11-25 19:56:10.

replicating from rptuser.addresses to rptdb.addresses:

*** total statistics since 2023-11-25 19:54:49 ***
    total inserts                              0.00
    total updates                              0.00
    total deletes                              1.00
    total upserts                              0.00
    total discards                             0.00
    total operations                           1.00

end of statistics.

mysql> select * from addresses where address_id=150;
empty set (0.00 sec)

mysql> select database();
+------------+
| database() |
+------------+
| rptdb      |
+------------+
1 row in set (0.00 sec)