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

denglj · 发表于 2022-12-1 19:30:42

文档课题：配置ogg同步oracle11g数据到oracle19c.

1、环境介绍

登录/注册后可看大图

9358763888fd82d752.png (19.04 KB, 下载次数: 289)

下载附件

2022-12-1 19:28 上传

2、软件介绍

GoldenGate提供一个单一的平台，其可以为任何企业环境实现秒级灾难备份.它是一种基于日志的结构化数据复制方式，通过解析源数据库在线日志或归档日志获得数据的增删改变化（数据量只有日志的四分之一左右），再将这些变化应用到目标数据库，实现源数据库与目标数据库同步、双活.

GoldenGateTDM的数据复制过程解析：

a、利用捕捉进程(ExtractProcess)在源端读取Online Redo Log或ArchiveLog，然后进行解析，只提取其中数据的变化如增、删、改操作，并将相关信息转换为GoldenGateTDM自定义的中间格式存放在队列文件(trailfile)中.再利用传送进程将队列文件通过TCP/IP传送到目标数据库.捕捉进程在每次读完log中的数据变化并在数据传送到目标系统后，会写检查点，记录当前完成捕捉的log位置，检查点的存在可以使捕捉进程在中止结束开始恢复后可从检查点位置继续复制.

b、目标系统接受数据变化开缓存到GoldenGateTDM队列当中，队列为临时存储数据变化的文件，等待投递进程读取数据.

c、GoldenGateTDM投递过程(replicatprocess)从队列中读取数据变化并创建对应的SQL语句，通过数据库的本地接口执行，提交到数据库成功后更新自己的检查点，记录已经完成复制的位置，数据的复制过程最终完成.

由此可见，GoldenGateTDM是一种基于软件的数据复制方式，将数据变化转化为自己的格式，直接通过TCP/IP网络传输，无需依赖于数据库自身的传递方式，而且可以通过高达10:1的压缩率对数据进行压缩，可以大大降低带宽需求.在目标端GoldenGateTDM可以通过交易重组，分批加载等技术手段大大加快数据投递的速度和效率，降低目标系统的资源占用，可以在亚秒级实现大量数据的复制，并且目标端数据库是活动的.

3、安装ogg3.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-oggogg]$ pwd

/u01/setup/ogg

[oracle@leo-11g-oggogg]$ unzip -q 191004_fbo_ggs_Linux_x64_shiphome.zip

[oracle@leo-11g-oggDisk1]$ pwd

/u01/setup/ogg/fbo_ggs_Linux_x64_shiphome/Disk1

[oracle@leo-11g-oggDisk1]$ export DISPLAY=192.168.133.1:0.0

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

登录/注册后可看大图

3178063888ffcaf845.png (106.34 KB, 下载次数: 302)

下载附件

2022-12-1 19:29 上传

登录/注册后可看大图

61794638890031cc4e.png (126.71 KB, 下载次数: 296)

下载附件

2022-12-1 19:29 上传

登录/注册后可看大图

362336388900a82097.png (96.5 KB, 下载次数: 301)

下载附件

2022-12-1 19:29 上传

登录/注册后可看大图

29325638890117dbf6.png (90.88 KB, 下载次数: 295)

下载附件

2022-12-1 19:29 上传

登录/注册后可看大图

746796388901838b6e.png (60.51 KB, 下载次数: 294)

下载附件

2022-12-1 19:29 上传

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-oggogg]$ pwd

/u01/setup/ogg

[oracle@leo-19c-oggogg]$ unzip -q 191004_fbo_ggs_Linux_x64_shiphome.zip

[oracle@leo-19c-oggDisk1]$ pwd

/u01/setup/ogg/fbo_ggs_Linux_x64_shiphome/Disk1

[oracle@leo-19c-oggDisk1]$ export DISPLAY=192.168.133.1:0.0

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

登录/注册后可看大图

2071638890285e755.png (103.98 KB, 下载次数: 296)

下载附件

2022-12-1 19:29 上传

登录/注册后可看大图

784876388902e32893.png (126.4 KB, 下载次数: 297)

下载附件

2022-12-1 19:29 上传

登录/注册后可看大图

35751638890359c480.png (95.6 KB, 下载次数: 299)

下载附件

2022-12-1 19:29 上传

登录/注册后可看大图

956416388903cadb5c.png (90.42 KB, 下载次数: 288)

下载附件

2022-12-1 19:30 上传

登录/注册后可看大图

56585638890439936d.png (61.62 KB, 下载次数: 294)

下载附件

2022-12-1 19:30 上传

3.3、环境变量3.3.1、源端

[oracle@leo-11g-ogg:~]$vi .bash_profile

添加如下：

exportGG_HOME=/u01/app/ogg

export PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin:$GG_HOME

exportLD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib

aliasggsci='cd $GG_HOME;ggsci'

[oracle@leo-11g-ogg:~]$source .bash_profile

3.3.2、目标端

[oracle@leo-19c-ogg:~]$vi .bash_profile

添加如下：

exportGG_HOME=/u01/app/ogg

export PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin:$GG_HOME

exportLD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib

aliasggsci='cd $GG_HOME;ggsci'

[oracle@leo-19c-ogg~]$ source .bash_profile

3.4、数据库配置3.4.1、源端3.4.1.1、强制生成日志

sys@ORCL2022-12-01 09:54:51> alter database force logging;

Databasealtered.

sys@ORCL2022-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默认情况下只记录数据块改变的信息，其实它还可以记录更详细的信息，这些信息对第三方的容灾软件很重要，所以此处将supplementallog mode模式打开，oracle一般是用rowid来唯一标识一行记录，但goldengate需要主键等其他标识，所以要开附加日志.

3.4.1.4、开启enable_goldengate_replication

SQL> altersystem 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> altersystem 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

createtablespace oggtbs datafile '/u01/app/oracle/oradata/orcl/oggtbs01.dbf' size 50mautoextend on;

createuser ogg identified by ogg default tablespace oggtbs;

grantresource to ogg;

grantcreate session, alter session to ogg;

grantselect any dictionary to ogg;

grantflashback any table to ogg;

grantalter any table to ogg;

grantselect any table to ogg;

grantexecute on dbms_flashback to ogg;

grant allprivileges to ogg;

grant dbato ogg;

3.5.2、目标端

[oracle@leo-19c-ogg~]$ sqlplus / as sysdba

createtablespace oggtbs datafile '/u01/app/oracle/oradata/SIMDB/oggtbs01.dbf' size50m autoextend on;

createuser ogg identified by ogg default tablespace oggtbs;

grantresource to ogg;

grantcreate session, alter session to ogg;

grantselect any dictionary to ogg;

grantflashback any table to ogg;

grantalter any table to ogg;

grantselect any table to ogg;

grantexecute on dbms_flashback to ogg;

grantinsert any table to ogg;

grantupdate any table to ogg;

grantdelete any table to ogg;

grant dbato ogg;

3.6、准备数据3.6.1、源端

新建用户并授予权限.

createuser leo identified by leo;

grantconnect,resource to leo;

grantselect on scott.emp to leo;

grantselect on scott.dept to leo;

createuser alina identified by alina;

grantconnect,resource to alina;

grantselect on scott.salgrade to alina;

leo用户创建测试表，并增加主键.

SQL>show user

USER is "LEO"

createtable lemp as select * from scott.emp;

createtable ldept as select * from scott.dept;

altertable ldept add primary key (deptno);

altertable lemp add primary key (empno);

SQL>show user

USER is "ALINA"

createtable asalgrade as select * from scott.salgrade;

altertable asalgrade add primary key (grade);

导出数据，用于目标端.

exportNLS_LANG=AMERICAN_AMERICA.AL32UTF8

expleo/leo@orcl file=/home/oracle/leo.dmp tables=ldept,lemp rows=ylog=/home/oracle/exp.log

将源端的dmp文件scp到目标端

scpleo.dmp oracle@192.168.133.109:/home/oracle/

3.6.2、目标端

创建用户并授予权限

createuser leo identified by leo;

grantconnect,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-oggogg]$ cd $GG_HOME

[oracle@leoogg]$ ./ggsci

4.1.1、创建目录

GGSCI(leo-11g-ogg) 1> create subdirs

Creatingsubdirectories under current directory /u01/app/ogg

Parameterfile /u01/app/ogg/dirprm:created.

Reportfile /u01/app/ogg/dirrpt: created.

Checkpointfile /u01/app/ogg/dirchk:created.

Processstatus files /u01/app/ogg/dirpcs: created.

SQLscript files /u01/app/ogg/dirsql: created.

Databasedefinitions files /u01/app/ogg/dirdef: created.

Extractdata files /u01/app/ogg/dirdat: created.

Temporaryfiles /u01/app/ogg/dirtmp:created.

Credentialstore files /u01/app/ogg/dircrd:created.

Masterkeywallet files /u01/app/ogg/dirwlt:created.

Dumpfiles /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

Managerstarted.

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

Successfullylogged into database.

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

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

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

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

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

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

2022-12-0112: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

Creatingsubdirectories under current directory /u01/app/ogg

Parameterfile /u01/app/ogg/dirprm:created.

Reportfile /u01/app/ogg/dirrpt: created.

Checkpointfile /u01/app/ogg/dirchk:created.

Processstatus files /u01/app/ogg/dirpcs: created.

SQLscript files /u01/app/ogg/dirsql: created.

Databasedefinitions files /u01/app/ogg/dirdef: created.

Extractdata files /u01/app/ogg/dirdat: created.

Temporaryfiles /u01/app/ogg/dirtmp:created.

Credentialstore files /u01/app/ogg/dircrd:created.

Masterkeywallet files /u01/app/ogg/dirwlt:created.

Dumpfiles /u01/app/ogg/dirdmp: created.

GGSCI(leo-19c-ogg) 3> edit param mgr

添加以下：

port 7809

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

Managerstarted.

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

EXTRACTadded.

添加本地队列ra，exta进程捕捉到的有效数据将写入本地队列.

GGSCI(leo-11g-ogg as ogg@orcl) 5> add exttrail ./dirdat/ra,extract exta

EXTTRAILadded.

为exta进程配置参数

GGSCI(leo-11g-ogg as ogg@orcl) 6> edit param exta

添加如下内容：

extractexta

useridogg,password ogg

setenv(NLS_LANG=AMERICAN_AMERICA.AL32UTF8)

exttrail./dirdat/ra

dynamicresolution /* 优化参数，动态分析表结构

gettruncates /*抓取truncatetable操作

table leo.*;

4.1.4、投递进程

源端数据库配置投递过程.

GGSCI(leo-11g-ogg as ogg@orcl) 7> add extract dp1,exttrailsource ./dirdat/ra

EXTRACTadded.

GGSCI(leo-11g-ogg as ogg@orcl) 8> edit param dp1

添加如下：

extractdp1

useridogg,password ogg

setenv(NLS_LANG = AMERICAN_AMERICA.AL32UTF8)

passthru

rmthost192.168.133.109,mgrport 7809

rmttrail./dirdat/ra

tableleo.*;

--添加远程trail文件.

GGSCI(leo-11g-ogg as ogg@orcl) 9> add rmttrail ./dirdat/ra,extract dp1

RMTTRAILadded.

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

SendingSTART request to MANAGER ...

EXTRACTDP1 starting

GGSCI(leo) 7> start exta

Sending STARTrequest to MANAGER ...

EXTRACTEXTA 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-dengogg]$ ./ggsci

4.2.1、添加checkpointtable

GGSCI(leo-19c-ogg) 1> edit params ./globals

添加如下

checkpointtableogg.checkpoint

GGSCI(leo-19c-ogg) 2> dblogin userid ogg,password ogg

Successfullylogged into database.

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

Successfullycreated checkpoint table ogg.checkpoint.

4.2.2、复制进程

GGSCI(leo-19c-ogg as ogg@simdb) 3> add replicat repa,exttrail./dirdat/ra,checkpointtable ogg.checkpoint

REPLICATadded.

GGSCI(leo-19c-ogg as ogg@simdb) 4> edit param repa

添加如下：

replicatrepa

setenv(NLS_LANG=AMERICAN_AMERICA.AL32UTF8)

useridogg,password ogg

numfiles500

grouptransops10000

handlecollisions

assumetargetdefs

allownoopupdates

dynamicresolution

discardfile./dirrpt/repsa_discard.txt, append, megabytes 10

mapleo.*, 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

SendingSTART request to MANAGER ...

REPLICATREPA 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@ORCL2022-12-01 15:29:30> conn leo/leo

Connected.

leo@ORCL2022-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@ORCL2022-12-01 15:29:45> insert into ldept values (50,'IT','ChongQing');

1 rowcreated.

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

Commitcomplete.

--目标端验证.

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中用户不能登录.

帐号		自动登录	找回密码
密码			注册

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