配置ogg同步oracle 11g数据到oracle 19c

原创 Leo 2022-12-01

697

文档课题：配置ogg同步oracle 11g数据到oracle 19c.

1、环境介绍

2、软件介绍
GoldenGate提供一个单一的平台，其可以为任何企业环境实现秒级灾难备份.它是一种基于日志的结构化数据复制方式，通过解析源数据库在线日志或归档日志获得数据的增删改变化（数据量只有日志的四分之一左右），再将这些变化应用到目标数据库，实现源数据库与目标数据库同步、双活.
GoldenGate TDM的数据复制过程解析：
a、利用捕捉进程(Extract Process)在源端读取Online Redo Log或Archive Log，然后进行解析，只提取其中数据的变化如增、删、改操作，并将相关信息转换为GoldenGate TDM自定义的中间格式存放在队列文件(trail file)中.再利用传送进程将队列文件通过TCP/IP传送到目标数据库.捕捉进程在每次读完log中的数据变化并在数据传送到目标系统后，会写检查点，记录当前完成捕捉的log位置，检查点的存在可以使捕捉进程在中止结束开始恢复后可从检查点位置继续复制.
b、目标系统接受数据变化开缓存到GoldenGate TDM队列当中，队列为临时存储数据变化的文件，等待投递进程读取数据.
c、GoldenGate TDM投递过程(replicat process)从队列中读取数据变化并创建对应的SQL语句，通过数据库的本地接口执行，提交到数据库成功后更新自己的检查点，记录已经完成复制的位置，数据的复制过程最终完成.

由此可见，GoldenGate TDM是一种基于软件的数据复制方式，将数据变化转化为自己的格式，直接通过TCP/IP网络传输，无需依赖于数据库自身的传递方式，而且可以通过高达10:1的压缩率对数据进行压缩，可以大大降低带宽需求.在目标端GoldenGate TDM可以通过交易重组，分批加载等技术手段大大加快数据投递的速度和效率，降低目标系统的资源占用，可以在亚秒级实现大量数据的复制，并且目标端数据库是活动的.
3、安装ogg
3.1、创建目录
3.1.1、源端
[root@leo-11g-ogg:~]# mkdir -p /u01/app/ogg
[root@leo-11g-ogg:~]# chown -R oracle:oinstall /u01/app/ogg
[root@leo-11g-ogg:~]# mkdir -p /u01/setup/ogg
3.1.2、目标端
[root@leo-19c-ogg:~]# mkdir -p /u01/app/ogg
[root@leo-19c-ogg:~]# chown -R oracle:oinstall /u01/app/ogg
[root@leo-19c-ogg:~]# mkdir -p /u01/setup/ogg
3.2、安装ogg软件
3.2.1、源端
sftp> cd /u01/setup/ogg
sftp> lcd F:\installmedium\ogg
sftp> put 191004_fbo_ggs_Linux_x64_shiphome.zip
[root@leo-11g-ogg:~]# chown -R oracle:oinstall /u01/setup
[oracle@leo-11g-ogg ogg]$ pwd
/u01/setup/ogg
[oracle@leo-11g-ogg ogg]$ unzip -q 191004_fbo_ggs_Linux_x64_shiphome.zip
[oracle@leo-11g-ogg Disk1]$ pwd
/u01/setup/ogg/fbo_ggs_Linux_x64_shiphome/Disk1
[oracle@leo-11g-ogg Disk1]$ export DISPLAY=192.168.133.1:0.0

[oracle@leo-11g-ogg Disk1]$ ./runInstaller

3.2.2、目标端
sftp> cd /u01/setup/ogg
sftp> lcd F:\installmedium\ogg
sftp> put 191004_fbo_ggs_Linux_x64_shiphome.zip
[root@leo-19c-ogg:~]# chown -R oracle:oinstall /u01/setup
[oracle@leo-19c-ogg ogg]$ pwd
/u01/setup/ogg
[oracle@leo-19c-ogg ogg]$ unzip -q 191004_fbo_ggs_Linux_x64_shiphome.zip
[oracle@leo-19c-ogg Disk1]$ pwd
/u01/setup/ogg/fbo_ggs_Linux_x64_shiphome/Disk1
[oracle@leo-19c-ogg Disk1]$ export DISPLAY=192.168.133.1:0.0

[oracle@leo-19c-ogg Disk1]$ ./runInstaller

3.3、环境变量
3.3.1、源端
[oracle@leo-11g-ogg:~]$ vi .bash_profile
添加如下：
export GG_HOME=/u01/app/ogg
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'

[oracle@leo-11g-ogg:~]$ source .bash_profile
3.3.2、目标端
[oracle@leo-19c-ogg:~]$ vi .bash_profile
添加如下：
export GG_HOME=/u01/app/ogg
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'

[oracle@leo-19c-ogg ~]$ source .bash_profile
3.4、数据库配置
3.4.1、源端
3.4.1.1、强制生成日志
sys@ORCL 2022-12-01 09:54:51> alter database force logging;

Database altered.

sys@ORCL 2022-12-01 09:55:01> select force_logging from v$database;

FOR
---
YES
3.4.1.2、开启归档
[oracle@leo-11g-ogg:~]$ sqlplus / as sysdba
SQL> shutdown immediate
SQL> startup mount
SQL> alter database archivelog;
SQL> alter system set log_archive_dest_1='location=/u01/app/oracle/oradata/archivelog';
SQL> alter database open;
3.4.1.3、开启补充日志
SQL> select supplemental_log_data_min from v$database;

SUPPLEME
--------
NO
SQL> alter database add supplemental log data;
SQL> alter system switch logfile;
SQL> select supplemental_log_data_min from v$database;

SUPPLEME
--------
YES

说明：redo默认情况下只记录数据块改变的信息，其实它还可以记录更详细的信息，这些信息对第三方的容灾软件很重要，所以此处将supplemental log mode模式打开，oracle一般是用rowid来唯一标识一行记录，但goldengate需要主键等其他标识，所以要开附加日志.
3.4.1.4、开启enable_goldengate_replication
SQL> alter system set enable_goldengate_replication=true;
SQL> show parameter enable_goldengate

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
enable_goldengate_replication        boolean     TRUE
3.4.2、目标端
3.4.2.1、开启enable_goldengate_replication
SQL> alter system set enable_goldengate_replication=true;
SQL> show parameter enable_goldengate

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
enable_goldengate_replication        boolean     TRUE
3.5、创建用户、表空间及授权
创建goldengate数据库用户以及专属于ogg的表空间.
3.5.1、源端
[oracle@leo-11g-ogg:~]$ sqlplus / as sysdba
create tablespace oggtbs datafile '/u01/app/oracle/oradata/orcl/oggtbs01.dbf' size 50m autoextend on;
create user ogg identified by ogg default tablespace oggtbs;
grant resource to ogg;
grant create session, alter session to ogg;
grant select any dictionary to ogg;
grant flashback any table to ogg;
grant alter any table to ogg;
grant select any table to ogg;
grant execute on dbms_flashback to ogg;
grant all privileges to ogg;
grant dba to ogg;
3.5.2、目标端
[oracle@leo-19c-ogg ~]$ sqlplus / as sysdba
create tablespace oggtbs datafile '/u01/app/oracle/oradata/SIMDB/oggtbs01.dbf' size 50m autoextend on;
create user ogg identified by ogg default tablespace oggtbs;
grant resource to ogg;
grant create session, alter session to ogg;
grant select any dictionary to ogg;
grant flashback any table to ogg;
grant alter any table to ogg;
grant select any table to ogg;
grant execute on dbms_flashback to ogg;
grant insert any table to ogg;
grant update any table to ogg;
grant delete any table to ogg;
grant dba to ogg;
3.6、准备数据
3.6.1、源端
新建用户并授予权限.
create user leo identified by leo;
grant connect,resource to leo;
grant select on scott.emp to leo;
grant select on scott.dept to leo;
create user alina identified by alina;
grant connect,resource to alina;
grant select on scott.salgrade to alina;

leo用户创建测试表，并增加主键.
SQL> show user
USER is "LEO"
create table lemp as select * from scott.emp;
create table ldept as select * from scott.dept;
alter table ldept add primary key (deptno);
alter table lemp add primary key (empno);
SQL> show user
USER is "ALINA"
create table asalgrade as select * from scott.salgrade;
alter table asalgrade add primary key (grade);

导出数据，用于目标端.
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
exp leo/leo@orcl file=/home/oracle/leo.dmp tables=ldept,lemp rows=y log=/home/oracle/exp.log

将源端的dmp文件scp到目标端
scp leo.dmp oracle@192.168.133.109:/home/oracle/
3.6.2、目标端
创建用户并授予权限
create user leo identified by leo;
grant connect,resource,unlimited tablespace to leo;

导入测试数据
[oracle@leo-deng ~]$ imp leo/leo file=/home/oracle/leo.dmp full=y statistics=none
验证测试数据
SQL> conn leo/leo
Connected.
SQL> select * from tab;

TNAME           TABTYPE CLUSTERID
------------------------------ ------- ----------
LEMP            TABLE
LDEPT           TABLE

SQL> select count(*) from mydept;

COUNT(*)
----------
         4
SQL> select count(*) from myemp;

COUNT(*)
----------
        14
4、OGG配置
4.1、源端
说明：必须在ogg的home目录下启动ggsci，这样才能把子目录建在ogg的home目录下，如果不在相应位置建立子目录，在后面编辑参数文件时会报错.
[oracle@leo-11g-ogg ogg]$ cd $GG_HOME
[oracle@leo ogg]$ ./ggsci
4.1.1、创建目录
GGSCI (leo-11g-ogg) 1> create subdirs

Creating subdirectories under current directory /u01/app/ogg

Parameter file                 /u01/app/ogg/dirprm: created.
Report file                    /u01/app/ogg/dirrpt: created.
Checkpoint file                /u01/app/ogg/dirchk: created.
Process status files           /u01/app/ogg/dirpcs: created.
SQL script files               /u01/app/ogg/dirsql: created.
Database definitions files     /u01/app/ogg/dirdef: created.
Extract data files             /u01/app/ogg/dirdat: created.
Temporary files                /u01/app/ogg/dirtmp: created.
Credential store files         /u01/app/ogg/dircrd: created.
Masterkey wallet files         /u01/app/ogg/dirwlt: created.
Dump files                     /u01/app/ogg/dirdmp: created.

重要目录说明：
dirchk：存放检查点(checkpoint)文件
dirdat：存放trail文件
dirdef：存放通过defgen工具生成的源或目标端数据定义文件
dirpcs：存放进程状态文件
dirprm：存放配置参数文件
dirrpt：存放进程报告文件
dirsql：存放sql脚本文件
dirtmp：当事务所需要的内存超过已分配的内存时，默认存储在此目录
4.1.2、添加端口
4.1.2.1、源端配置
GGSCI (leo-11g-ogg) 2> edit param mgr
添加
port 7809

GGSCI (leo-11g-ogg) 3> start mgr
Manager started.

GGSCI (leo-11g-ogg) 4> info all

Program     Status      Group       Lag at Chkpt Time Since Chkpt

MANAGER     RUNNING

GGSCI (leo-11g-ogg) 2> dblogin userid ogg,password ogg
Successfully logged into database.

GGSCI (leo-11g-ogg as ogg@orcl) 3> add trandata leo.*

2022-12-01 12:35:24 INFO    OGG-15132 Logging of supplemental redo data enabled for table LEO.LDEPT.

2022-12-01 12:35:24 INFO    OGG-15133 TRANDATA for scheduling columns has been added on table LEO.LDEPT.

2022-12-01 12:35:24 INFO    OGG-15135 TRANDATA for instantiation CSN has been added on table LEO.LDEPT.

2022-12-01 12:35:24 INFO    OGG-15132 Logging of supplemental redo data enabled for table LEO.LEMP.

2022-12-01 12:35:24 INFO    OGG-15133 TRANDATA for scheduling columns has been added on table LEO.LEMP.

2022-12-01 12:35:24 INFO    OGG-15135 TRANDATA for instantiation CSN has been added on table LEO.LEMP.

说明：数据库附加日志打开后还需要对每张表执行add trandata吗？
答案是肯定的，如果不执行add trandata，insert同步没有问题（ORACLE数据库），但在同步update或delete时，就会因为丢失主键报同步错误.
不开启表级的最小附加日志，update的redo信息不记录没有进行更新的字段信息，如主键不更新的话主键不记录在redo中，所以会导致同步失败.
4.1.2.2、目标端配置
提前在目标端添加mgr端口.
GGSCI (leo-19c-ogg) 2> create subdirs

Creating subdirectories under current directory /u01/app/ogg

Parameter file                 /u01/app/ogg/dirprm: created.
Report file                    /u01/app/ogg/dirrpt: created.
Checkpoint file                /u01/app/ogg/dirchk: created.
Process status files           /u01/app/ogg/dirpcs: created.
SQL script files               /u01/app/ogg/dirsql: created.
Database definitions files     /u01/app/ogg/dirdef: created.
Extract data files             /u01/app/ogg/dirdat: created.
Temporary files                /u01/app/ogg/dirtmp: created.
Credential store files         /u01/app/ogg/dircrd: created.
Masterkey wallet files         /u01/app/ogg/dirwlt: created.
Dump files                     /u01/app/ogg/dirdmp: created.

GGSCI (leo-19c-ogg) 3> edit param mgr
添加以下：
port 7809

GGSCI (leo-19c-ogg) 4> start mgr
Manager started.

GGSCI (leo-19c-ogg) 5> info all

Program     Status      Group       Lag at Chkpt Time Since Chkpt

MANAGER     RUNNING
4.1.3、抓取进程
extract进程，负责从源端数据表或者日志中捕获数据.
GGSCI (leo-11g-ogg as ogg@orcl) 4> add extract exta,tranlog,begin now
EXTRACT added.

添加本地队列ra，exta进程捕捉到的有效数据将写入本地队列.
GGSCI (leo-11g-ogg as ogg@orcl) 5> add exttrail ./dirdat/ra,extract exta
EXTTRAIL added.

为exta进程配置参数
GGSCI (leo-11g-ogg as ogg@orcl) 6> edit param exta
添加如下内容：
extract exta
userid ogg,password ogg
setenv (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
exttrail ./dirdat/ra
dynamicresolution /* 优化参数，动态分析表结构
gettruncates   /*抓取truncate table操作
table leo.*;
4.1.4、投递进程
源端数据库配置投递过程.
GGSCI (leo-11g-ogg as ogg@orcl) 7> add extract dp1,exttrailsource ./dirdat/ra
EXTRACT added.

GGSCI (leo-11g-ogg as ogg@orcl) 8> edit param dp1
添加如下：
extract dp1
userid ogg,password ogg
setenv (NLS_LANG = AMERICAN_AMERICA.AL32UTF8)
passthru
rmthost 192.168.133.109,mgrport 7809
rmttrail ./dirdat/ra
table leo.*;
--添加远程trail文件.
GGSCI (leo-11g-ogg as ogg@orcl) 9> add rmttrail ./dirdat/ra,extract dp1
RMTTRAIL added.

GGSCI (leo-11g-ogg as ogg@orcl) 10> info all

Program     Status      Group       Lag at Chkpt Time Since Chkpt

MANAGER     RUNNING
EXTRACT     STOPPED     DP1         00:00:00      00:03:45
EXTRACT     STOPPED     EXTA        00:00:00      00:08:13

GGSCI (leo) 6> start dp1

Sending START request to MANAGER ...
EXTRACT DP1 starting

GGSCI (leo) 7> start exta

Sending START request to MANAGER ...
EXTRACT EXTA starting

GGSCI (leo) 8> info all

Program     Status      Group       Lag           Time Since Chkpt

MANAGER    RUNNING
EXTRACT     RUNNING     DP1         00:00:00      00:04:26
EXTRACT     RUNNING     EXTA        00:38:56      00:00:04
4.2、目标端
[oracle@leo-deng ~]$ cd $GG_HOME
[oracle@leo-deng ogg]$ ./ggsci
4.2.1、添加checkpointtable
GGSCI (leo-19c-ogg) 1> edit params ./globals
添加如下
checkpointtable ogg.checkpoint

GGSCI (leo-19c-ogg) 2> dblogin userid ogg,password ogg
Successfully logged into database.

GGSCI (leo-19c-ogg as ogg@simdb) 2> add checkpointtable ogg.checkpoint

Successfully created checkpoint table ogg.checkpoint.

4.2.2、复制进程
GGSCI (leo-19c-ogg as ogg@simdb) 3> add replicat repa,exttrail ./dirdat/ra,checkpointtable ogg.checkpoint
REPLICAT added.

GGSCI (leo-19c-ogg as ogg@simdb) 4> edit param repa
添加如下：
replicat repa
setenv (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
userid ogg,password ogg
numfiles 500
grouptransops 10000
handlecollisions
assumetargetdefs
allownoopupdates
dynamicresolution
discardfile ./dirrpt/repsa_discard.txt, append, megabytes 10
map leo.*, target leo.*;

说明：逗号与target之间需用空格隔开, ogg.checkpoint中的ogg为之前创建的用户.
GGSCI (leo-19c-ogg as ogg@simdb) 5> info all

Program     Status      Group       Lag at Chkpt Time Since Chkpt

MANAGER     RUNNING
REPLICAT    STOPPED     REPA        00:00:00      00:02:16

GGSCI (leo-19c-ogg as ogg@simdb) 8> start repa

Sending START request to MANAGER ...
REPLICAT REPA starting

GGSCI (leo-19c-ogg as ogg@simdb) 9> info all

Program     Status      Group       Lag at Chkpt Time Since Chkpt

MANAGER     RUNNING
REPLICAT    RUNNING     REPA        00:00:00      00:00:00
5、验证数据
--源端向ldept表中插入一条数据.
sys@ORCL 2022-12-01 15:29:30> conn leo/leo
Connected.
leo@ORCL 2022-12-01 15:29:35> select * from ldept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

leo@ORCL 2022-12-01 15:29:45> insert into ldept values (50,'IT','ChongQing');

1 row created.

leo@ORCL 2022-12-01 15:30:03> commit;

Commit complete.

--目标端验证.
SQL> select * from ldept

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES           CHICAGO
        40 OPERATIONS     BOSTON
        50 IT                ChongQing
ogg基本配置完成.

总结：配置过程包括操作系统层面前期准备、源端目标端数据库配置、源端中mgr/extract/pump进程配置、目标端中mgr/replicat进程配置.配置过程要保持数据库开启状态，否则ggsci中用户不能登录.

墨力计划

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

配置ogg同步oracle 11g数据到oracle 19c

1、环境介绍

2、软件介绍

3、安装ogg

3.1、创建目录

3.1.1、源端

3.1.2、目标端

3.2、安装ogg软件

3.2.1、源端

3.2.2、目标端

3.3、环境变量

3.3.1、源端

3.3.2、目标端

3.4、数据库配置

3.4.1、源端

3.4.1.1、强制生成日志

3.4.1.2、开启归档

3.4.1.3、开启补充日志

3.4.1.4、开启enable_goldengate_replication

3.4.2、目标端

3.4.2.1、开启enable_goldengate_replication

3.5、创建用户、表空间及授权

3.5.1、源端

3.5.2、目标端

3.6、准备数据

3.6.1、源端

3.6.2、目标端

4、OGG配置

4.1、源端

4.1.1、创建目录

4.1.2、添加端口

4.1.2.1、源端配置

4.1.2.2、目标端配置

4.1.3、抓取进程

4.1.4、投递进程

4.2、目标端

4.2.1、添加checkpointtable

4.2.2、复制进程

5、验证数据

评论