通过OGG实现异构数据库迁移（ORACLE TO MYSQL）

su - oracle
$vi .bash_pfofile

export OGG_HOME=/ogg/oinstall
export LD_LIBRARY_PATH=$JAVA_HOME/jre/lib/amd64:$JAVA_HOME/jre/lib/amd64/server:$JAVA_HOME/jre/lib/amd64/libjsig.so:$JAVA_HOME/jre/lib/amd64/server/libjvm.so:$OGG_HOME/lib:$LD_LIBRARY_PATH:$ORACLE_HOME/lib:/usr/lib
export PATH=$OGG_HOME:$PATH:$ORACLE_HOME/bin

su - root
#useradd ogg -g oinstall
#passwd ogg
#mkdir /ogg
#chown -R oracle:oinstall /ogg/

$unzip -d ~ 213000_fbo_ggs_Linux_x64_Oracle_shiphome.zip
$cd fbo_ggs_Linux_x64_Oracle_shiphome/Disk1/response/
$vi oggcore.rsp
INSTALL_OPTION=ORA19C
SOFTWARE_LOCATION=/ogg/oinstall

./runInstaller -silent -nowait -responseFile /ogg/fbo_ggs_Linux_x64_Oracle_shiphome/Disk1/response/oggcore.rsp

cd $OGG_HOME
create subdirs

SQL>select name,open_mode,force_logging,supplemental_log_data_min from v$database;
SQL>archive log list;
SQL>alter database force logging;
SQL>alter database add supplemental log data;

SQL>create tablespace ogg_data datafile /oradata/oggtbs01.dbf size 1024M autoextend on;
SQL>create user c##ogg identified by ogg default tablespace ogg_data;
SQL>grant dba ,connect, resource, unlimited tablespace to c##ogg;
SQL>exec dbms_goldengate_auth.grant_admin_privilege(c##ogg,*,TRUE);

SQL>create tablespace ogg_data datafile
/oradata/ORCL2/D418DD8AF0E48322E0538289A8C07AB4/datafile/ogg.dbf size 1024M autoextend on;
SQL>create user ogg identified by ogg default tablespace ogg_data;
SQL>grant dba ,connect, resource, unlimited tablespace to ogg;
SQL>exec dbms_goldengate_auth.grant_admin_privilege(ogg,*,TRUE);

SQL>create user test identified by test;
SQL>grant dba ,connect, resource, unlimited tablespace to test;

SQL>create table test_ogg_tab(id int,name varchar(20),primary key(id));

ggsci>dblogin userid ogg@pdb19,password ogg
ggsci>add schematrandata pdb19.test
ggsci>add trandata test.*

edit param ./globals
oggschema ogg

PORT 7809
DYNAMICPORTLIST 7810-7899
AUTORESTART EXTRACT *, RETRIES 5, WAITMINUTES 3, RESETMINUTES 60

GGSCI > start mgr
GGSCI > info all
$ netstat -ntpl |grep 7809

GGSCI > edit param ext_mq
extract ext_mq
DDL INCLUDE ALL
SETENV (ORACLE_HOME = "/u01/app/oracle/product/19.3.0/db")
userid c##ogg@orcl2, password ogg
exttrail /ogg/oinstall/dirdat/to
table PDB19.test.*;

GGSCI >add extract ext_mq, TRANLOG,BEGIN NOW
GGSCI >add exttrail /ogg/oinstall/dirdat/to, extract ext_mq
GGSCI >register extract ext_mq database container(PDB19)

GGSCI >edit param pump_mq
extract pump_mq
userid c##ogg@orcl2 , password ogg
rmthost 192.168.10.180,mgrport 7809
rmttrail /ogg/oinstall/dirdat/rt
table PDB19.test_.*;

GGSCI >add extract pump_mq, exttrailsource /ogg/oinstall/dirdat/to
GGSCI >add rmttrail /ogg/dirdat/rt, extract pump_mq, megabytes 100

edit param tab1
dsdffile /ogg/oinstall/dirdef/ogg_test.ogg_test
userid test@PDB19, password test
table test.test_ogg_tab;
./defgen paramfile dirprm/tab1.prm

#useradd oggmq
#passwd oggmq
#unzip -d ~ ./213000_ggs_Linux_x64_MySQL_64bit.zip
#mkdir -p /ogg/oinstall

su - oggmq
tar -xvf ggs_Linux_x64_MySQL_64bit.tar -C /ogg/oinstall

cd $OGG_HOME
ggsci
create subdirs

create user ogg@% identified by ogg;
grant all on *.* to ogg@%;
create database ogg;
use ogg;
create table test_ogg_tab(id int,name varchar(20),primary key(id));

edit param mgr
PORT 7809

edit param ./GLOBALS
CHECKPOINTTABLE ogg.checkpoint

dblogin sourcedb ogg@127.0.0.1:3306 ,userid ogg, password ogg
add checkpointtable ogg.ggs_checkpoint

ggsci>edit params r_tab1
replicat r_tab1
targetdb ogg@127.0.0.1:3306,userid ogg,password ogg
sourcedefs /ogg/oinstall/dirdef/ogg_test.ogg_test
HANDLECOLLISIONS
MAP test.test_ogg_tab,target ogg.test_ogg;

dblogin sourcedb ogg@127.0.0.1:3306 ,userid ogg, password ogg
add replicat r_tab1,exttrail /ogg/dirdat/rt,checkpointtable ogg.ggs_checkpoint

insert into test_ogg_tab values(1,d);
insert into test_ogg_tab values(2,b);
insert into test_ogg_tab values(3,ae);
commit;

select * from test_ogg_tab;