GoldenGate19.1 Oracle单向dml配置全过程


文档说明:GoldenGate19.1 Oracle单向dml配置全过程,源端说19c数据库,asm磁盘组,目标端是11g,本地文件系统。
作者:鼎甲科技 姚远

参考文档:

https://docs.oracle.com/en/middleware/goldengate/core/19.1/index.html

测试环境

IP地址ogg版本数据库版本OS
源端192.168.87.4319.119.8OLE 7.6
目标端192.168. 17.19419.111.2.0.4OLE 6.7
VirtualBox-6.1-6.1.14_140239_el7-1.x86_64.rpm
[oracle@dell u01]$ ll /u02/install/191004_fbo_ggs_Linux_x64_shiphome.zip 
-rw-r--r--. 1 oracle oinstall 556240981 Sep 25 15:27 /u02/install/191004_fbo_ggs_Linux_x64_shiphome.zip
[oracle@dell u01]$ 

安装

mkdir /u01/app/oracle/ggs/19.1 -p
mv 191004_fbo_ggs_Linux_x64_shiphome.zip /u01/app/oracle/ggs/19.1/
unzip 191004_fbo_ggs_Linux_x64_shiphome.zip

[oracle@ogg19 ~]$ cd fbo_ggs_Linux_x64_shiphome/Disk1/
[oracle@ogg19 Disk1]$ ll
total 16
drwxr-xr-x  4 oracle oinstall 4096 1018 2019 install
drwxrwxr-x  2 oracle oinstall 4096 1018 2019 response
-rwxr-xr-x  1 oracle oinstall  918 1018 2019 runInstaller
drwxr-xr-x 12 oracle oinstall 4096 1018 2019 stage

[oracle@ogg19 Disk1]$ ./runInstaller 
Starting Oracle Universal Installer...

Checking Temp space: must be greater than 120 MB.   Actual 42866 MB    Passed
Checking swap space: must be greater than 150 MB.   Actual 3071 MB    Passed
Checking monitor: must be configured to display at least 256 colors.    Actual 16777216    Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2020-09-30_02-42-53PM. Please wait ...[oracle@ogg19 Disk1]$ You can find the log of this install session at:
 /u01/app/oraInventory/logs/installActions2020-09-30_02-42-53PM.log


在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在.bash_profile中增加下面的行

export GGATE=$ORACLE_BASE/ogg19
export PATH=$PATH:$GGATE
GGSCI (ogg19) 2> CREATE SUBDIRS

Creating subdirectories under current directory /u01/app/oracle/ogg19

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


GGSCI (ogg19) 3> exit
[oracle@ogg19 ogg19]$ pwd
/u01/app/oracle/ogg19
[oracle@ogg19 ogg19]$ 

创建测试用户及测试数据

SQL> create tablespace tb_ogg datafile size 10m ;
SQL> create user scutech identified by dingjia default tablespace tb_ogg;
User created.
SQL> grant dba to scutech;
Grant succeeded.
$ sqlplus scutech/dingjia
SQL> create table tb (id int not null, name char(10), primary key(id));
Table created.

创建 OGG 用户,并赋权(Source & Target 步骤一样)

SQL> create user ogg identified by dingjia default tablespace tb_ogg;  
SQL> grant dba to ogg;

配置数据库日志

SQL> alter database add supplemental log data;
Database altered.
SQL> alter database force logging; 
Database altered.
SQL> set linesize 200;
SQL> select log_mode,supplemental_log_data_min,force_logging from v$database;
LOG_MODE		 SUPPLEMENTAL_LOG FORCE_LOGGING
------------------------ ---------------- ------------------------------------------------------------------------------
ARCHIVELOG		 YES		  YES

SQL>     ALTER SYSTEM SWITCH LOGFILE;

System altered.

SQL> alter system set enable_goldengate_replication=true;
System altered.

Credential store配置

GGSCI (oledb.scutech) 18>  create wallet
Created wallet.
Opened wallet.
GGSCI (oledb.scutech) 19> ADD MASTERKEY
2020-10-14T06:15:17Z  INFO    OGG-06142  Created version 1 of master key 'OGG_DEFAULT_MASTERKEY' in Oracle Wallet.
GGSCI (oledb.scutech) 20>  INFO MASTERKEY
Masterkey Name: OGG_DEFAULT_MASTERKEY
Version         Creation Date                   Status
1               2020-10-14T14:15:17.000+08:00   Current
GGSCI (oledb.scutech) 21> INFO MASTERKEy version 1        
Masterkey Name: OGG_DEFAULT_MASTERKEY
Version         Creation Date                   Status
1               2020-10-14T14:15:17.000+08:00   Current
GGSCI (oledb.scutech) 22> 

开启表级别日志,追加对象为用户scutech下所有表

GGSCI (oledb.scutech) 54> DBLOGIN USERID ogg password dingjia;
Successfully logged into database.
GGSCI (oledb.scutech as ogg@dbogg) 55> ADD SCHEMATRANDATA scutech
2020-10-14 19:00:57  INFO    OGG-01788  SCHEMATRANDATA has been added on schema "scutech".
2020-10-14 19:00:57  INFO    OGG-01976  SCHEMATRANDATA for scheduling columns has been added on schema "scutech".
2020-10-14 19:00:57  INFO    OGG-10154  Schema level PREPARECSN set to mode NOWAIT on schema "scutech".
2020-10-14 19:01:00  INFO    OGG-10471  ***** Oracle Goldengate support information on table SCUTECH.TA ***** 
Oracle Goldengate support native capture on table SCUTECH.TA.
Oracle Goldengate marked following column as key columns on table SCUTECH.TA: A
No unique key is defined for table SCUTECH.TA.
GGSCI (oledb.scutech as ogg@dbogg) 56> info SCHEMATRANDATA scutech
2020-10-14 19:01:23  INFO    OGG-06480  Schema level supplemental logging, excluding non-validated keys, is enabled on schema "SCUTECH".
2020-10-14 19:01:23  INFO    OGG-01980  Schema level supplemental logging is enabled on schema "SCUTECH" for all scheduling columns.
2020-10-14 19:01:23  INFO    OGG-10462  Schema "SCUTECH" have 1 prepared tables for instantiation.
GGSCI (oledb.scutech as ogg@dbogg) 57> info trandata scutech.*
2020-10-14 19:01:43  INFO    OGG-06480  Schema level supplemental logging, excluding non-validated keys, is enabled on schema "SCUTECH".
2020-10-14 19:01:43  INFO    OGG-01980  Schema level supplemental logging is enabled on schema "SCUTECH" for all scheduling columns.
2020-10-14 19:01:43  INFO    OGG-10471  ***** Oracle Goldengate support information on table SCUTECH.TA ***** 
Oracle Goldengate support native capture on table SCUTECH.TA.
Oracle Goldengate marked following column as key columns on table SCUTECH.TA: A
No unique key is defined for table SCUTECH.TA.
Logging of supplemental redo log data is enabled for table SCUTECH.TA.
Columns supplementally logged for table SCUTECH.TA: "A".
Prepared CSN for table SCUTECH.TA: 1968044

配置 Source 端 MGR 进程

配置全局文件

GGSCI (oledb.scutech) 2> edit params ./GLOBALS 
ggschema ogg 

全局文件 GLOBALS 要大写,必须放在 Golden Gate 根目彔下,否则其会默认将配置文件创建在 Golden Gate 根目彔下的 dirprm 子目彔中,在全局文件中添加 GGSCHEMA参数,这个 ggschema 就是要抽取的数据库schema。

配置 MGR 参数文件

GGSCI  (oledb.scutech) 5> edit params mgr 
port 7809 
dynamicportlist 7800-8000 
autorestart extract *,retries 5,waitminutes 2,resetminutes 5 

参数说明: Port:指定 MGR 进程通信端口 Dynamicportlist:MGR 进程为 Source 和 Target 端劢态通信指定端口 Autorestart Extract:自劢重启 Extract 进程组,每两分钟尝试重启所有 Extract 进程,重试 5 次,每隔 5分钟清零一次
注意:参数配置完成后,需要重启 MGR 进程后生效

GGSCI (oledb.scutech) 4> info all
Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING   

配置源数据抽取进程(Extract)

编辑配置文件 edit params ext1

extract ext1 
SETENV(ORACLE_SID='dbogg')
userid ogg,password dingjia 
exttrail /u01/app/oracle/ogg19/dirdat/et 
table scutech.*;
 TranlogOptions DBLOGREADER

参数说明:
Extract Ext1:表示此为 Extract 进程,当前 Group 为 Ext1
Setenv:设置环境发量字符集
Userid和password:表示登陆数据库使用的账号和密码
Exttrail:指定本地 Trail 文件地址
Table:表示要同步的表,*代表全部
TranlogOptions DBLOGREADER 不用配置登陆ASM需要的TNS配置内容,并且也不在需要指定归档路径,其自动会找到归档日志的位置
OGG11g提供一个新的参数:DBLOGREADER使用该参数,我们就不需要配置登陆ASM所需要的TNS配置内容,而且在参数文件里面也不需要制定归档路径的位置,它会自动去寻找归档路径。但如果要使用该参数,对数据库的版本有一定的要求,以下是官方文档对该参数的解释

添加抽叏进程
add extract ext1 ,tranlog,begin now

添加一个 group 为 ext1 的 extract 进程,并且使用的 tranlog 捕获模式,开始使用时间是 now。

添加本地 Trail 文件
add exttrail /u01/app/oracle/ogg19/dirdat/et,extract ext1
为 ext1 进程添加一个 remote trail 的链接,表明将会把 trail 传到目标端的/u01/app/oracle/ogg/dirdat 目彔下,并以 et 作为一系列 trail 的前缀。
启动服务
start ext1
如果出现:

2020-10-14T17:36:02.068+0800  ERROR   OGG-00685  Oracle GoldenGate Capture for Oracle, ext1.prm:  begin time 2020 M10 14 11:19:57 prior to oldest log in log history. Last SQL executed <SELECT 1   FROM dual   WHERE TO_DATE(:1,'YYYY-MM-DD HH24:MI:SS') <    (SELECT MIN(first_time)       FROM v$log_history      WHERE thread# = :2)>.

修改抽取的时间

GGSCI (oledb.scutech) 31> alter ext1 begin now
EXTRACT altered.

查看状态

GGSCI (oledb.scutech) 19> info all
Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING                                           
EXTRACT     RUNNING     EXT1        00:00:00      05:06:20    

配置源数据Pump 进程

编辑配置文件
edit params pump1

extract pump1
passthru
rmthost 192.168.17.194,mgrport 7809,compress
rmttrail /u01/app/oracle/ogg19/dirdat/pt
table scutech.*;

passthru:绕过数据定义检测

添加 pump 进程
add extract pump1,exttrailsource /u01/app/oracle/ogg19/dirdat/et
EXTRACT added. exttrailsource:表示这是个 data pump extract 与本地 trail 作连接

添加进程 Trail 文件
add rmttrail /u01/app/oracle/ogg19/dirdat/pt,extract pump1
RMTTRAIL added. tmttrail:将 pump 与 remote trail 做链接
启劢服务
start pump1
查看状态
info all

  GGSCI (oledb.scutech) 37>  add extract pump1,exttrailsource /u01/app/oracle/ogg/dirdat/et
EXTRACT added.
GGSCI (oledb.scutech) 38>  add rmttrail /u01/app/oracle/ogg/dirdat/pt,extract pump1 
RMTTRAIL added.
GGSCI (oledb.scutech) 39>  start pump1 
Sending START request to MANAGER ...
EXTRACT PUMP1 starting

2020-10-14T15:57:55.238+0800 ERROR OGG-06220 Oracle GoldenGate
Capture for Oracle, ext1.prm: Classic Extract does not support
multitenant container databases.

配置 Target 端 MGR 进程

GGSCI (ogg19 as ogg@orcl) 48> view param mgr

PORT 7809
dynamicportlist 7800-8000 
autostart er * 
autorestart extract *, waitminutes 2, resetminutes 5 
lagreporthours 1 
laginfominutes 3 
lagcriticalminutes 5 
purgeoldextracts /u01/app/oracle/ogg19/dirdat/pt*, usecheckpoints, minkeepdays 3

配置 Target 端 Replicat 进程

配置 Replicat 进程参数

GGSCI (ogg19 as ogg@orcl) 47> view param rep1
replicat rep1 
userid ogg,password dingjia 
assumetargetdefs 
reperror default,discard 
discardfile /u01/app/oracle/ogg19/dirrpt/rep1.dsc,append,megabytes 50 
map scutech.*, target scutech.*;

创建 Checkpointtable

GGSCI (ogg19) 8> dblogin userid ogg password dingjia
Successfully logged into database.
GGSCI (ogg19 as ogg@orcl) 9> add checkpointtable ogg.checkpoint_table
Successfully created checkpoint table ogg.checkpoint_table.

添加复制进程

GGSCI (ogg19 as ogg@orcl) 47> view param rep1
replicat rep1 
userid ogg,password dingjia 
assumetargetdefs 
reperror default,discard 
discardfile /u01/app/oracle/ogg19/dirrpt/rep1.dsc,append,megabytes 50 
map scutech.*, target scutech.*;

add replicat rep1,exttrail /u01/app/oracle/ogg19/dirdat/pt, CHECKPOINTTABLE ogg.checkpoint_table
添加一个 replicat 进程,group 为 rep1 , 并 且 链 接 到 相 应 的 trail 文件,使用名为ogg.checkpoint_table 作为 checkpointtable。

GGSCI (ogg19 as ogg@orcl) 46> info rep1
REPLICAT   REP1      Last Started 2020-10-15 15:30   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:01 ago)
Process ID           23046
Log Read Checkpoint  File /u01/app/oracle/ogg19/dirdat/pt000000000
                     2020-10-15 15:17:45.757109  RBA 2187

测试

在源

SQL> insert into tb values(6,'kkk');
1 row created.
SQL> commit
  2  ;

在目标

SQL> select * from tb;

	ID NAME
---------- ----------
	 2 dd
	 3 aa
	 4 sdsds
	 6 kkk
姚远ACE CSDN认证博客专家 ACE 华为云 MVP
Oracle ACE,华为云 MVP,Oracle10g,12c OCM; MySQL 5.6,5.7,8.0 OCP;CCNA; EMC Certified; IBM P Certified; RHCE; SQLServer 764; DB2 Certified; TOEIC 890;获得过两次国家部级科技进步奖;发明过两项计算机专利。微信:yaoyuanace 邮箱:yaoyuanace(at)qq.com
已标记关键词 清除标记
©️2020 CSDN 皮肤主题: Age of Ai 设计师:meimeiellie 返回首页