文档说明：GoldenGate19.1 Oracle单向dml配置全过程，源端说19c数据库，asm磁盘组，目标端是11g，本地文件系统。
测试环境
IP地址 ogg版本 数据库版本 OS
源端 192.168.87.43 19.1 19.8 OLE 7.6
目标端 192.168. 17.194 19.1 11.2.0.4 OLE 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 10月 18 2019 install drwxrwxr-x 2 oracle oinstall 4096 10月 18 2019 response -rwxr-xr-x 1 oracle oinstall 918 10月 18 2019 runInstaller drwxr-xr-x 12 oracle oinstall 4096 10月 18 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配置
开启表级别日志，追加对象为用户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
