核心思想:利用数据泵导入导出功能初始化数据,然后通过 OGG 同步增量数据。
源 端:192.168.217.86 数据库为Oracle 11204 版本,端口1521 服务名:test
目标端:192.168.217.87 数据库为Oracle 19.3 版本,端口1521 服务名:testogg
问题1:19c采用 IPV6的形式 ,MGR自动将 7809端口在 IPV6地址进行监听 ,而11.2环境如何配置 dpe 传输进程 去连接 IPV6的MGR进程 ????
netstat -tunlp|grep 7809
tcp6 0:0:0:0:7809
●配置manager进程
● 指定本地和远程网络通信端口, 所有Oracle GoldenGate端口是可配置的1.185 USEIPV4 | USEIPV6
Valid For
GLOBALS
Description
Use the
USEIPV4
parameter to force the use of Internet Protocol version 4 (IPv4) by Oracle GoldenGate for TCP/IP connections. By default, Oracle GoldenGate uses IPv6 in dual-stack mode and this parameter forces the use of IPv4 only.When
USEIPV4
is used, the entire network in which Oracle GoldenGate operates must be IPv4 compatible.Use the
USEIPV6
parameter to force the use of Internet Protocol version 6 (IPv6) by Oracle GoldenGate for TCP/IP connections. By default, Oracle GoldenGate uses IPv6 in dual-stack mode but falls back to IPv4, and only then to IPv6.USEIPV6
eliminates the IPv4 fallback step. The order of socket selection becomes:
IPv6 dual-stack 双协议栈
IPv6
When
USEIPV6
is used, the entire network in which Oracle GoldenGate operates must be IPv6 compatible.Default
Disabled
Syntax
CopyUSEIPV4
CopyUSEIPV6
Manager进程概述
若要配置和运行Oracle GoldenGate,manager进程必须在所有Oracle GoldenGate源和目标系统以及在任何配置使用的中间系统上运行。
管理器进程执行以下功能:
● 启动Oracle GoldenGate 进程
● 启动动态进程
● 启动Collector进程
● 管理进程的端口号
● 执行队列管理
● 创建事件、错误和阈值报告
每个Oracle GoldenGate安装有一个manger。一个管理器可以支持多个Oracle GoldenGate抽取和复制进程。
为manager分配本地通信端口每个Oracle GoldenGate配置中的Manager进程都需要一个专用端口,用于自身与其他本地Oracle GoldenGate进程之间的通信。
要指定此端口,请使用管理器参数文件中的port参数。遵循以下准则:
● Manager的默认端口号为7809。必须指定默认端口号(推荐,如果有此端口)或者选择其他端口。
● 该端口必须是未保留且不受限制的
● 系统上的每个Manager实例必须使用不同的端口号
维护用于通过防火墙进行远程连接的端口
如果在Oracle GoldenGate目标位置使用防火墙,则需要在目标系统上附加端口才能从远程Oracle GoldenGate进程接收动态TCP/IP通信。这些端口是:
● 被本地manager启动的每个Collector进程的端口,用于从远程在线抽取进程接收传播的事务数据
● 被本地manager作为远程任务的一部分启动的复制进程的端口。远程任务用于初始加载,并指用RMTTASK参数。此端口用于
接收来自远程抽取进程的传入请求
● 一些额外的端口,以防需要它们来扩展本地Oracle GoldenGate配置
● 其他Oracle GoldenGate产品的端口(如果它们与本地Oracle GoldenGate实例交互),如这些产品的文档中所述。
要指定这些端口,请使用manger参数文件中的DYNAMICPORTLIST参数。
遵循以下准则:
● 您可以使用以下格式的任意组合指定多达5000个端口::
7830, 7833, 7835
7830-7835
7830-7835, 7839
● 端口必须是无保留和无限制的.
● 系统上的每个manager实例必须使用不同的端口列表
虽然不是必需的参数,但强烈建议使用DYNAMICPORTLIST以获得最佳性能。Collector进程负责查找并绑定到可用端口,并且拥有一个已知的
合格端口列表可加快此进程。在没有DYNAMICPORTLIST(或者如果没有为其指定足够的端口),收集器将尝试对远程请求使用端口7840。
如果7840不可用,Collector将递增1,直到找到可用端口。这可能会延迟远程请求的接受。如果Collector进程用完DYNAMICPORTLIST列表中的端口,
则会发生以下情况:
● Manager在其进程报告和Oracle GoldenGate ggserr日志中报告错误
● Collector据Oracle GoldenGate tcperrs文件中的规则重试。有关tcperrs文件的更多信息,请参阅第170页的“处理 TCP/IP错误”。
有关端口和动态端口列表的详细信息,请参阅《Oracle GoldenGate Windows和UNIX参考指南》。
选择internet协议默认情况下,Oracle GoldenGate按以下优先级顺序选择套接字,以确保连接成功的最佳机会:
● IPv6 dual-stack
● IPv4 if IPv6 dual-stack is not available 如果IPv6双堆栈不可用,则为IPv4
● IPv6
如果您的网络具有不支持双堆栈模式的IPv6网络设备,则可以使用USEIPV6参数强制Oracle GoldenGate对所有连接使用IPv6。
这是一个全局参数,适用于Oracle GoldenGate实例的所有进程。使用USEIPV6时,整个网络必须与IPv6兼容,以避免连接失败。
1、源库初始化数据,创建表空间
首先准备同步需要的测试数据(非必要),创建表空间,导出数据到源端 11g。
select file_id,file_name from dba_data_files;
FILE_ID FILE_NAME
---------- ------------------------------------------------------------------
4 /u01/app/oracle/oradata/test/users01.dbf
3 /u01/app/oracle/oradata/test/undotbs01.dbf
2 /u01/app/oracle/oradata/test/sysaux01.dbf
1 /u01/app/oracle/oradata/test/system01.dbf
Create tablespace PROD_TBS datafile '/u01/app/oracle/oradata/test/prod_tbs01.dbf' size 2g;
测试数据导出
nohup expdp \'/ as sysdba\' directory=PUBLIC_DUMP LOGFILE=test_prod_scfop.log dumpfile=expdp_prod-2021-07-14_%U.dmp SCHEMAS=prod COMPRESSION=ALL PARALLEL=2 CLUSTER=N
1.1.导入到源端11g
将上面的导出 dump scp 到源端 11g 的 /u01/backup
create directory expdp_dir as '/u01/backup/';
grant read,write on directory expdp_dir to public;
set linesize 9999
col OWNER for a10
col DIRECTORY_NAME for a30
col DIRECTORY_PATH for a80
select * from dba_directories;
建立表空间导入数据
Create tablespace PROD_TBS datafile '/u01/app/oracle/oradata/testogg/prod_tbs01.dbf' size 2g;
$ nohup impdp \'/ as sysdba\' directory=expdp_dir LOGFILE=imp_prod0714.log dumpfile=expdp_prod-2021-07-14_%U.dmp SCHEMAS=prod PARALLEL=4 &
2、安装 ogg 19.1
创建 ogg 安装目录,一般会使用 OS 用户 Oracle 作为 ogg 系统安装用户。
2.1.下载 ogg
目前 OGG 最新版本为 Oracle GoldenGate 21.3.0.0,我这里使用的是 Oracle GoldenGate 19.1 普通版(区别于微服务版),下载地址如下:
进入 Oracle 官方网址 www.oracle.com
http://www.oracle.com/technetwork/middleware/goldengate/downloads/index.html
官方指导手册:
https://docs.oracle.com/en/middleware/goldengate/core/19.1/index.html
https://docs.oracle.com/en/middleware/goldengate/core/19.1/oracle-db/index.html
参数说明: https://docs.oracle.com/en/middleware/goldengate/core/19.1/reference/index.html
使用 Oracle 用户创建软件安装目录
mkdir -p /ogg
chown -R oracle:oinstall /ogg
上传软件到 /ogg 并解压
191004_fbo_ggs_Linux_x64_shiphome.zip
unzip 191004_fbo_ggs_Linux_x64_shiphome.zip
ll --查看解压后文件
-rw-r--r-- 1 oracle oinstall 556240981 Jul 14 11:36 191004_fbo_ggs_Linux_x64_shiphome.zip
drwxr-xr-x 3 oracle oinstall 19 Oct 18 2019 fbo_ggs_Linux_x64_shiphome
-rw-r--r-- 1 oracle oinstall 1413 May 29 2019 OGG-19.1.0.0-README.txt
-rw-r--r-- 1 oracle oinstall 332523 Oct 21 2019 OGG_WinUnix_Rel_Notes_19.1.0.0.4.pdf
2.2.配置环境变量
环境变量如下:
vi .bash_profile
umask 022
export PS1="`whoami`@`hostname`:"'[$PWD]$'
export HOST=`hostname | cut -f1 -d"."`
export PS1='${HOST}:$PWD(${ORACLE_SID})$ '
export LANG=en_US
# +--------------------------+
# | SETUP ORACLE ENVIRONMENT |
# +--------------------------+
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1
export ORACLE_SID=test
export OGG_HOME=/ogg
export ORACLE_TERM=xterm;
export PATH=.:$PATH:$HOME/.local/bin:$HOME/bin:$ORACLE_HOME/OPatch:$ORACLE_HOME/bin:$OGG_HOME
export NLS_DATE_FORMAT="yyyy-mm-dd HH24:MI:SS"
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/lib32:$OGG_HOME:/lib/usr/lib:/usr/local/lib
export CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
alias sqlplus='rlwrap sqlplus'
alias rman='rlwrap rman'
alias lsnrctl='rlwrap lsnrctl'
alias asmcmd='rlwrap asmcmd'
alias adrci='rlwrap adrci'
alias ggsci='rlwrap ggsci'
alias sys='sqlplus / as sysdba'
2.3.静默安装
ogg 自12c 以后可以图形化安装,也可以命令行安装。下面修改响应文件采用命令行静默安装。
vi /ogg/fbo_ggs_Linux_x64_shiphome/Disk1/response/oggcore.rsp
只修改如下两个地方即可。
INSTALL_OPTION=ORA11g
SOFTWARE_LOCATION=/ogg
################################################################################
## ##
## Oracle GoldenGate installation option and details ##
##
################################################################################
#-------------------------------------------------------------------------------
# Specify the installation option.
# Specify ORA19c for installing Oracle GoldenGate for Oracle Database 19c or
# ORA18c for installing Oracle GoldenGate for Oracle Database 18c or
# ORA12c for installing Oracle GoldenGate for Oracle Database 12c or
# ORA11g for installing Oracle GoldenGate for Oracle Database 11g
#-------------------------------------------------------------------------------
INSTALL_OPTION=ORA19c
#-------------------------------------------------------------------------------
# Specify a location to install Oracle GoldenGate
#-------------------------------------------------------------------------------
SOFTWARE_LOCATION=/ogg/ggs
#-------------------------------------------------------------------------------
# Specify true to start the manager after installation.
#-------------------------------------------------------------------------------
START_MANAGER=
使用如下命令静默安装:
/ogg/fbo_ggs_Linux_x64_shiphome/Disk1/runInstaller -silent -responseFile /ogg/fbo_ggs_Linux_x64_shiphome/Disk1/response/oggcore.rsp
Starting Oracle Universal Installer...
Checking Temp space: must be greater than 120 MB. Actual 172958 MB Passed
Checking swap space: must be greater than 150 MB. Actual 8063 MB Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2021-07-14_02-10-23PM. Please wait ...Ops-11gOGG-86:/ogg/fbo_ggs_Linux_x64_shiphome/Disk1(test)$ [WARNING] [INS-75003] The specified directory /ogg is not empty.
CAUSE: The directory specified /ogg contains files.
ACTION: Clean up the specified directory or enter a new directory location.
You can find the log of this install session at:
/u01/app/oraInventory/logs/installActions2021-07-14_02-10-23PM.log
WARNING:OUI-10030:You have specified a non-empty directory to install this product. It is recommended to specify either an empty or a non-existent directory. You may, however, choose to ignore this message if the directory contains Operating System generated files or subdirectories like lost+found.
Do you want to proceed with installation in this Oracle Home?
Successfully Setup Software.
The installation of Oracle GoldenGate Core was successful.
Please check '/u01/app/oraInventory/logs/silentInstall2021-07-14_02-10-23PM.log' for more details.
cd /ogg
./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 19.1.0.0.4 OGGCORE_19.1.0.0.0_PLATFORMS_191017.1054_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Oct 17 2019 23:13:12
Operating system character set identified as US-ASCII.
Copyright (C) 1995, 2019, Oracle and/or its affiliates. All rights reserved.
GGSCI (Ops-11gOGG) 1>
GGSCI (eomsdb1) 8> CREATE SUBDIRS
安装成功!!!
目标端 19c 下安装:
191004_fbo_ggs_Linux_x64_shiphome.zip
目标端解压到 /u01/soft
unzip 191004_fbo_ggs_Linux_x64_shiphome.zip
编辑响应文件,也只需修改如下两行:
vi /u01/soft/fbo_ggs_Linux_x64_shiphome/Disk1/response/oggcore.rsp
INSTALL_OPTION=ORA19c
SOFTWARE_LOCATION=/ogg
静默安装
/u01/soft/fbo_ggs_Linux_x64_shiphome/Disk1/runInstaller -silent -responseFile /u01/soft/fbo_ggs_Linux_x64_shiphome/Disk1/response/oggcore.rsp
--等待一分钟即可安装成功。
Starting Oracle Universal Installer...
Checking Temp space: must be greater than 120 MB. Actual 171849 MB Passed
Checking swap space: must be greater than 150 MB. Actual 8062 MB Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2021-07-14_04-09-37PM. Please wait ...Ops-19cOGG-87:/u01/soft(testogg)$ You can find the log of this install session at:
/u01/app/oraInventory/logs/installActions2021-07-14_04-09-37PM.log
Successfully Setup Software.
The installation of Oracle GoldenGate Core was successful.
Please check '/u01/app/oraInventory/logs/silentInstall2021-07-14_04-09-37PM.log' for more details
$cd /ogg
$./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 19.1.0.0.4 OGGCORE_19.1.0.0.0_PLATFORMS_191017.1054_FBO
Linux, x64, 64bit (optimized), Oracle 19c on Oct 17 2019 21:16:29
Operating system character set identified as US-ASCII.
Copyright (C) 1995, 2019, Oracle and/or its affiliates. All rights reserved.
3、数据库源端(11g)相关设置(源端配置)
3.1.设置 enable_ogg_replication为true
SQL> show parameter enable_goldengate_replicationNAME TYPE VALUE------------------------------------ ----------- ------------------------------enable_goldengate_replication boolean FALSEalter system set enable_goldengate_replication=true scope=both sid='*';
SQL> alter system set enable_goldengate_replication=true scope=both sid='*';System altered.Elapsed: 00:00:00.00SQL> show parameter enable_goldengate_replicationNAME TYPE VALUE------------------------------------ ----------- ------------------------------enable_goldengate_replication boolean TRUE
———————————————
3.2.添加补充日志,打开数据库的最小附加日志(数据库级别)
SQL> select log_mode,force_logging,SUPPLEMENTAL_LOG_DATA_MIN,
SUPPLEMENTAL_LOG_DATA_PK,SUPPLEMENTAL_LOG_DATA_ALL from v$database;
LOG_MODE FOR SUPPLEME SUP SUP
------------ --- -------- --- ---
ARCHIVELOG NO NO NO NO
alter database add supplemental log data;
alter database force logging;
SQL> select log_mode,force_logging,SUPPLEMENTAL_LOG_DATA_MIN,SUPPLEMENTAL_LOG_DATA_PK,SUPPLEMENTAL_LOG_DATA_ALL from v$database;
LOG_MODE FOR SUPPLEME SUP SUP
------------ --- -------- --- ---
ARCHIVELOG YES YES NO NO
3.3.创建ogg表空间、用户并授权
create tablespace ogg_tbs datafile '+DATA' size 1G autoextend on maxsize 1G; --RAC
create tablespace ogg_tbs datafile '/u01/app/oracle/oradata/test/tbs_ogg01.dbf' size 1G autoextend on maxsize 30G;
create user ogg identified by ogg default tablespace ogg_tbs;
grant connect,resource,unlimited tablespace to ogg;
grant create session,alter session,create sequence,create table to ogg;
grant select any dictionary,select any table to ogg;
grant alter any table to ogg;
grant flashback any table to ogg;
grant execute on dbms_flashback to ogg;
grant execute on utl_file to ogg;
grant select any transaction to ogg;
grant become user to ogg;
exec dbms_streams_auth.grant_admin_privilege('ogg');
3.4.配置 DDL 同步
3.4.1早期版本11204 之前
cd $OGG_HOME
sqlplus / as sysdba
SQL> @marker_setup.sql;
SQL> @ddl_setup.sql;
DDL TRIGGER RUNNING STATUS
------------------------------------------------------------------------------------------------------------------------
ENABLED
STAYMETADATA IN TRIGGER
------------------------------------------------------------------------------------------------------------------------
OFF
DDL TRIGGER SQL TRACING
------------------------------------------------------------------------------------------------------------------------
0
DDL TRIGGER TRACE LEVEL
------------------------------------------------------------------------------------------------------------------------
NONE
LOCATION OF DDL TRACE FILE
------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/test/test/trace/ggs_ddl_trace.log
Analyzing installation status...
VERSION OF DDL REPLICATION
------------------------------------------------------------------------------------------------------------------------
OGGCORE_19.1.0.0.0_PLATFORMS_191017.1054
STATUS OF DDL REPLICATION
------------------------------------------------------------------------------------------------------------------------
SUCCESSFUL installation of DDL Replication software components
Script complete.
SQL> @role_setup.sql; --按提示输入ogg用户
GGS Role setup script
This script will drop and recreate the role GGS_GGSUSER_ROLE
To use a different role name, quit this script and then edit the params.sql script to change the gg_role parameter to the preferred name. (Do not run the script.)
You will be prompted for the name of a schema for the GoldenGate database objects.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.
Enter GoldenGate schema name:ogg
Wrote file role_setup_set.txt
PL/SQL procedure successfully completed.
Role setup script complete
Grant this role to each user assigned to the Extract, GGSCI, and Manager processes, by using the following SQL command:
GRANT GGS_GGSUSER_ROLE TO <loggedUser>
where <loggedUser> is the user assigned to the GoldenGate processes.
SQL> grant GGS_GGSUSER_ROLE to ogg;
SQL> @ddl_enable.sql
3.4.2 配置 DDL 捕获说明
Extract 可以通过使用特殊的 DDL 触发器或本地通过 Oracle 日志挖掘服务器从源 Oracle 数据库捕获 DDL 操作。
在集成捕获模式下支持 DDL 捕获
Extract 的集成捕获模式支持两种 DDL 捕获方式:
在集成捕获模式下支持 DDL 捕获
Oracle 11.2.0.4 或更高版本:数据库 COMPATIBLE 参数设置为 11.2.0.4 或更高版本的 Oracle 数据库支持通过数据库日志挖掘服务器进行 DDL 捕获。这种方法称为原生 DDL 捕获(也称为无触发 DDL 捕获)。不需要安装触发器支持对象。本机 DDL 捕获是从多租户容器数据库捕获 DDL 的唯一受支持方法。对于下游挖掘,源数据库还必须将数据库COMPATIBLE设置为 11.2.0.4 或更高版本,以支持通过数据库日志挖掘服务器进行 DDL 捕获。
早于 11.2.0.4 的版本:COMPATIBLE参数设置为早于 11.2.0.4 的Oracle 数据库需要使用 Oracle GoldenGate DDL 触发器。要使用基于触发器的 DDL 捕获,您必须先安装 DDL 触发器和支持的数据库对象,然后才能为 DDL 支持配置 Extract。
支持经典捕获模式下的 DDL 捕获
经典捕获模式需要使用 Oracle GoldenGate DDL 触发器从 Oracle 数据库捕获 DDL。经典捕获模式不支持原生 DDL 捕获。
经典捕获模式不支持从多租户容器数据库捕获 DDL。
当您使用经典捕获模式并 CREATE USER 使用 DDL 触发器复制时,触发器所有者和 Extract 登录用户必须匹配以避免尝试复制 CREATE USER 命令时出现权限错误。
要使用基于触发器的 DDL 捕获,您必须在为 DDL 支持配置 Extract 之前安装 DDL 触发器和支持的数据库对象。
3.4.3 Oracle GoldenGate DDL 支持的限制
1)、DDL 语句长度
2)、支持的拓扑
3)、过滤、映射和转换
4)、重命名
5)、从表中提取和 DDL 之间的交互
6)、SQL 中的注释
7)、编译错误
8)、间隔分区
9)、在 DDL 触发器内执行的 DML 或 DDL
10)、LogMiner 数据字典维护
DDL 语句长度
Oracle GoldenGate 以字节而不是字符来衡量 DDL 语句的长度。支持的长度约为 4 MB,允许一些内部开销的大小取决于受影响对象的名称及其 DDL 类型,以及其他特征。如果 DDL 长于支持的大小,Extract 将发出警告并忽略 DDL 操作。
如果 Extract 正在通过 DDL 触发器捕获 DDL,则忽略的 DDL 将保存在标记表中。您可以使用ddl_ddl2file.sql脚本捕获被忽略的 Oracle DDL 语句以及任何其他 Oracle DDL 语句,该脚本将 DDL 操作保存到 USER_DUMP_DEST Oracle 目录中的文本文件中。该脚本提示输入以下内容:
包含在 GLOBALS 文件中指定的 Oracle GoldenGate DDL 对象的模式的名称。
Oracle GoldenGate 标记序列号,当在 Extract 参数文件中使用 DDLOPTIONS 该REPORT 选项时,它会记录在 Extract 报告文件中。
输出文件的名称。
支持的拓扑
Oracle GoldenGate 仅在同类配置中支持 DDL 同步。源和目标对象定义必须相同。
DDL 复制仅支持 Oracle 到 Oracle 复制。它在不同的数据库之间不受支持,例如 Oracle 到 Teradata,或 SQL Server 到 Oracle。
Oracle GoldenGate 不支持备用数据库上的 DDL。
Oracle GoldenGate 在所有受支持的单向配置中以及在两个且仅两个系统之间的双向配置中支持 DDL 复制。
其余八项可查看官方文档,这里不在赘述。
3.5.配置 tns
注意要是多实例需要配置 tns 登录创建的 ogg 用户,19c PDB 也需要 tns 登录到相应的数据库。
vi /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
TEST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = Ops-11gOGG-86)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = test)
)
)
3.6.打开表级附加日志
cd $OGG_HOME
./ggsci
dblogin userid ogg,password ogg --注意要是多实例需要配置 tns 登录创建的 ogg 用户
例:dblogin userid ogg@tns配置名字 ,password ogg
ADD TRANDATA PROD_SCFOP.* --注意不要加分号
ADD TRANDATA scott.*
ADD TRANDATA EASYC_READ.*
--查看附加日志是否添加成功
info TRANDATA scott.*
info TRANDATA PROD.*
3.7 配置 MGR
创建 GG 工作目录
GGSCI (Ops-11gOGG as ogg@test) 6> create subdirs
Creating subdirectories under current directory /ogg
Parameter file /ogg/dirprm: created.
Report file /ogg/dirrpt: created.
Checkpoint file /ogg/dirchk: created.
Process status files /ogg/dirpcs: created.
SQL script files /ogg/dirsql: created.
Database definitions files /ogg/dirdef: created.
Extract data files /ogg/dirdat: created.
Temporary files /ogg/dirtmp: created.
Credential store files /ogg/dircrd: created.
Masterkey wallet files /ogg/dirwlt: created.
Dump files /ogg/dirdmp: created.
3.7.1编辑 mgr 并启动
edit param mgr
port 7809
DYNAMICPORTLIST 7810-7829
userid ogg@TEST, PASSWORD ogg
AUTOSTART EXTRACT *
AUTORESTART EXTRACT *
PURGEOLDEXTRACTS /ogg/dirdat/*,usecheckpoints, minkeepdays 8
LAGREPORTHOURS 1
LAGINFOMINUTES 30
LAGCRITICALMINUTES 45
ACCESSRULE, PROG *, IPADDR 12.*.*.*, PRI 1, ALLOW
PORT: 指定 Manager 使用的端口。
Dynamicportliist:配置了捕获和复制进程使用的端口范围。
AUTORESTART: 参数使抽取/复制进程失败后自动重启。
PURGEOLDEXTRACTS 参数指定:当根据 checkpoint 发现已经完成抽取和复制的 trail 文件将被自动删除,但保留最近 10 个。
Purgeddlhistory 和 purgemarkerhistory 分别删除历史 DDL 历史表和 marker 表中的过期数据,以控制他们呢不会过于庞大。
GGSCI (Ops-11gOGG as ogg@test) 8> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER STOPPED
GGSCI (Ops-11gOGG as ogg@test) 9>
GGSCI (Ops-11gOGG as ogg@test) 9> start mgr
Manager started.
GGSCI (Ops-11gOGG as ogg@test) 10> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
————————————————
3.8.配置 extract 抽取进程 extu1
GGSCI (Ops-11gOGG as ogg@test) 14> add extract extu1, TRANLOG, BEGIN NOW
--RAC配置启动会报错
add extract ext1, TRANLOG, BEGIN NOW, THREADS 4 --(主库有四组日志)
--add extract ext1, TRANLOG, BEGIN 2021-08-17 21:00:56, THREADS 4
select distinct thread# from v$log;
select to_char(scn_to_timestamp(9035744263), 'yyyy-mm-dd hh24:mi:ss') scndate from dual;
SELECT TO_CHAR(MIN(start_scn) ) AS "Please select the minimumSCN" FROM v$transaction
UNION ALL SELECT TO_CHAR(current_scn) FROM v$database;
GGSCI (Ops-11gOGG as ogg@test) 14> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED EXTU1 00:00:00 00:00:08
GGSCI (Ops-11gOGG as ogg@test) 15> add exttrail /ogg/dirdat/ss, extract extu1, megabytes 1024
EXTTRAIL added.
GGSCI (Ops-11gOGG as ogg@test) 16> edit param extu1
EXTRACT extu1
setenv (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
setenv (ORACLE_SID=test)
setenv (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
setenv (TNS_ADMIN=/u01/app/oracle/product/11.2.0/dbhome_1/network/admin)
userid ogg@TEST, password ogg
REPORTCOUNT EVERY 30 MINUTES, RATE
DISCARDFILE /ogg/dirrpt/extu_ss.dsc, APPEND, MEGABYTES 1024
DISCARDROLLOVER AT 9:00
EXTTRAIL /ogg/dirdat/ss
GETTRUNCATES
--DYNAMICRESOLUTION
TRANLOGOPTIONS EXCLUDEUSER ogg
NOCOMPRESSUPDATES
NOCOMPRESSDELETES
GETUPDATEBEFORES
STATOPTIONS REPORTFETCH
DDL INCLUDE ALL
DDLOPTIONS ADDTRANDATA
DDLOPTIONS REPORT
table PROD.*;
table SCOTT.*;
参数说明:GETUPDATEBEFORES:是否在队列中写入后镜像,也可进行维护事务历史表。默认是复制;
GETTRUNCATES:是否在队列中进行复制 truncate 操作, 默认是不复制;
BR BRINTERVAL:对于存在长事务恢复情况下,恢复到检查点时间界限;
CACHEMGR CACHESIZE:主要用于控制存放未提交事务的虚拟内存和文件缓存空间;
WARNLONGTRANS:长事务警告频率, 用于收集监控长事务情况;
CHECKINTERVAL:同样也是作为长事务 WARNLONGTRANS 监控频率;
#频率格式
S|SEC|SECS|SECOND|SECONDS
M|MIN|MINS|MINUTE|MINUTES
H|HOUR|HOURS
D|DAY|DAYS
NUMFILES: 控制用于存储Oracle GoldenGate有关需要处理和map 表的信息的内存的初始分配,类似于缓存数目;
EOFDELAYCSECS:控制extract进程在数据源检查新数据的频度;
TRANLOGOPTIONS DBLOGREADER:该参数用于直接访问重做日志和归档日志,不需要通过连接到ASM 实例访问;
TRANLOGOPTIONS MINEFROMACTIVEDG:该参数用于从ADG 中读取到日志信息;
TRANLOGOPTIONS PURGEORPHANEDTRANSACTIONS;清除孤立的事务。解释:通过将其启动时间与节点的启动时间进行比较,事务在清除之前被验证为孤立的; 如果交易开始较早,则会被清除;
TRANLOGOPTIONS _DISABLESTREAMLINEDDBLOGREADER:这个参数在11.2.1.0.26以后才有,用于DBLOGREADER模式下,处理一些异常丢失事务的情况;
DYNAMICRESOLUTION:extract 进程启动后, 快速处理指定的表和map(开启这个参数,遇到记录中的表是才加载表的源数据到内存中);
Discardfile:指定discard 目录, 用于记录无法处理的日志信息 。
LOGALLSUPCOLS:该参数为 extract capture 进程记录补充日志中 update 和 delete 操作前的镜像信息。
启动 extu1 捕获进程
start extu1
3.9.配置extract 传输进程 dpe1
GGSCI (Ops-11gOGG as ogg@test) 18> add extract dpe1, exttrailsource /ogg/dirdat/ss
EXTRACT added.
GGSCI (Ops-11gOGG as ogg@test) 19> add rmttrail /ogg/dirdat/ss, EXTRACT dpe1, MEGABYTES 1024
RMTTRAIL added.
GGSCI (Ops-11gOGG as ogg@test) 20> edit param dpe1
EXTRACT dpe1
DYNAMICRESOLUTION
PASSTHRU
RMTHOST 192.168.217.87, MGRPORT 7809, COMPRESS
RMTTRAIL /ogg/dirdat/ss
TABLE scott.*;
TABLE prod_scfop.*;
参数说明:PASSTHRU:不登录到数据库操作(数据投递不必登录数据库)
DYNAMICRESOLUTION:动态解析Rmthost:远端主机(IP 或者主机名解析)Rmttrail:目标端 trail 文件存储位置以及名称
启动投递进程 dpe1(目标端 MGR 需要先启动)
start dpe1
4、数据库目标端(19c)相关设置
4.1.创建表空间和用户,并授权
create tablespace tbs_ogg datafile '/u01/app/oracle/oradata/TESTOGG/tbs_ogg01.dbf' size 1G autoextend on maxsize 30G;
create user ogg identified by ogg default tablespace tbs_ogg;
grant connect,resource,unlimited tablespace to ogg;
grant create session,alter session to ogg;
grant select any dictionary,select any table to ogg;
grant alter any table to ogg;
grant flashback any table to ogg;
grant execute on dbms_flashback to ogg;
grant execute on utl_file to ogg;
grant dba to ogg;
grant select any transaction to ogg;
exec DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE('ogg');
4.2. 配置mgr进程
--登录数据库
GGSCI (Ops-19cOGG) 1> dblogin userid ogg@TESTOGG,password ogg
Successfully logged into database.
GGSCI (Ops-19cOGG as ogg@testogg) 2> create subdirs
Creating subdirectories under current directory /ogg
Parameter file /ogg/dirprm: created.
Report file /ogg/dirrpt: created.
Checkpoint file /ogg/dirchk: created.
Process status files /ogg/dirpcs: created.
SQL script files /ogg/dirsql: created.
Database definitions files /ogg/dirdef: created.
Extract data files /ogg/dirdat: created.
Temporary files /ogg/dirtmp: created.
Credential store files /ogg/dircrd: created.
Masterkey wallet files /ogg/dirwlt: created.
Dump files /ogg/dirdmp: created.
4.2.1.编辑 mgr 并启动
edit param mgr
port 7809 DYNAMICPORTLIST 7810-7829 userid ogg@TESTOGG, password ogg --AUTOSTART EXTRACT * AUTORESTART EXTRACT *,RETRIES 3,WAITMINUTES 7,RESETMINUTES 60 PURGEOLDEXTRACTS /ogg/dirdat/*,usecheckpoints, minkeepdays 8 PURGEDDLHISTORY MINKEEPDAYS 7, MAXKEEPDAYS 10 PURGEMARKERHISTORY MINKEEPDAYS 7, MAXKEEPDAYS 10 LAGREPORTHOURS 1 --LAGINFOMINUTES 30 --LAGCRITICALMINUTES 4
AUTORESTART 参数表示每7分钟尝试重新启动所有进程,共尝试三次。以后每60分钟清零,再按照每7分钟尝试一次共试三次。
start mgr
GGSCI (Ops-19cOGG as ogg@testogg) 7> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER STOPPED
GGSCI (Ops-19cOGG as ogg@testogg) 8>
GGSCI (Ops-19cOGG as ogg@testogg) 8> start mgr
Manager started.
GGSCI (Ops-19cOGG as ogg@testogg) 9> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
GGSCI (Ops-19cOGG as ogg@testogg) 10> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
4.3.配置replicat 进程 rep1
注意:先不启,初始化完成之后再起进程
GGSCI (Ops-19cOGG) 1> dblogin userid ogg@TESTOGG,password ogg
GGSCI (Ops-19cOGG as ogg@testogg) 4> add checkpointtable ogg.rep1_ckpt
Successfully created checkpoint table ogg.rep1_ckpt.
GGSCI (Ops-19cOGG as ogg@testogg) 5> add replicat rep1, exttrail /ogg/dirdat/ss, checkpointtable ogg.rep1_ckpt
REPLICAT added.
GGSCI (Ops-19cOGG as ogg@testogg) 6> edit param rep1
REPLICAT rep1
setenv (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
--setenv (ORACLE_SID=)
userid ogg@TESTOGG, password ogg
REPORT AT 08:59
REPORTCOUNT EVERY 30 MINUTES, RATE
CACHEMGR CACHESIZE 2048MB, CACHEDIRECTORY /ogg/dirtmp
REPERROR DEFAULT, ABEND
DISCARDFILE /ogg/dirrpt/rep1.dsc, APPEND, MEGABYTES 1024
DISCARDROLLOVER AT 3:00
GETTRUNCATES
ALLOWNOOPUPDATES
APPLYNOOPUPDATES
ASSUMETARGETDEFS
DBOPTIONS DEFERREFCONST
DBOPTIONS SUPPRESSTRIGGERS
DDL INCLUDE MAPPED
DDLOPTIONS REPORT
DDLERROR 942 IGNORE
MAP PROD.* TARGET PROD.*;
MAP SCOTT.* TARGET SCOTT.*;
参数说明:dbOptions IntegratedParams:设置并行度;
EOFDELAYCSECS:控制 replicat 进程检查新数据的频度;
Reportrollover:指定何时生成 report 文件;
Reperror:控制记录 MAP 发生错误时的信息,这里指定 default 和 abendDefault:设置对所有错误的响应记录Abend:回滚事务并终止处理异常。ABEND 是默认值
CACHEMGR CACHESIZE :可以来控制OGG进程的内存使用。
列映射转换:
5. 19c目标端初始化数据
5.1.创建 db_link
先要配置好 tns TEST_LINK
TEST_LINK =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 源端IP)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = test)
)
)
SQL> create public database link TEST_LINK connect to system identified by Oracle_11g using 'TEST_LINK'; --这里写源库用户和密码
--用完可删除此 dblink
DROP PUBLIC DATABASE LINK TEST_LINK;
--测试 db_link
SQL> select * from dual@TEST_LINK;
D
-
X
SQL> select instance_name from v$instance@TEST_LINK;
INSTANCE_NAME
----------------
test
5.2.查询源生产库 scn
SQL> SELECT TO_CHAR(MIN(start_scn) ) AS "Please select the minimum SCN" FROM v$transactionUNION ALLSELECT TO_CHAR(current_scn) FROM v$database;Please select the minimum SCN----------------------------------------2181214
5.3.数据泵导入数据
目标端创建表空间
Create tablespace PROD_SCFOP_TBS datafile '/u01/app/oracle/oradata/TESTOGG/prod_scfop_tbs01.dbf' size 2g;
目标创建 dump 数据目录
create directory expdp_dir as '/u01/backup/';
grant read,write on directory expdp_dir to public;
查看数据目录
set linesize 9999
col OWNER for a10
col DIRECTORY_NAME for a30
col DIRECTORY_PATH for a80
select * from dba_directories;
5.3.1.使用 dblink 初始化数据
nohup impdp system/Oracle_19C@TESTOGG directory=EXPDP_DIR version=11.2.0.4 NETWORK_LINK=TEST_LINK flashback_scn=2181214 exclude=statistics parallel=4 cluster=no schemas=PROD,SCOTT logfile=impdp_scott.log logtime=ALL TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y &
注意:Dblink+BLOB 大字段会特别慢有问题,先启动 extu1 捕获进程在初始化数据。
期间在源库启动 extract 进程并模拟插入一条数据。
GGSCI (Ops-11gOGG as ogg@test) 22> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED DPE1 00:00:00 01:48:58
EXTRACT STOPPED EXTU1 00:00:00 02:03:02
GGSCI (Ops-11gOGG as ogg@test) 23>
GGSCI (Ops-11gOGG as ogg@test) 23> start extu1
Sending START request to MANAGER ...
EXTRACT EXTU1 starting
GGSCI (Ops-11gOGG as ogg@test) 24>
GGSCI (Ops-11gOGG as ogg@test) 24> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED DPE1 00:00:00 01:49:13
EXTRACT RUNNING EXTU1 02:03:13 00:00:04
GGSCI (Ops-11gOGG as ogg@test) 25> start dpe1
Sending START request to MANAGER ...
EXTRACT DPE1 starting
启动报错,没有加入表的 map 信息。如上,在 dpe1 配置文件最后两行添加。然后在此启动,报错由于目标端 mgr 进程没有启动,目标端启动 mgr 进程,源端已自动启动 dpe1.
GGSCI (Ops-11gOGG as ogg@test) 35> start dpe1
Sending START request to MANAGER ...
EXTRACT DPE1 starting
GGSCI (Ops-11gOGG as ogg@test) 36> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT ABENDED DPE1 00:00:00 01:53:04
EXTRACT RUNNING EXTU1 00:00:00 00:00:02
GGSCI (Ops-11gOGG as ogg@test) 37> view report dpe1
2021-07-14 20:01:50 ERROR OGG-01224 TCP/IP error 111 (Connection refused), endpoint: 12.0.217.87:7809.
2021-07-14 20:01:50 ERROR OGG-01668 PROCESS ABENDING.
GGSCI (Ops-11gOGG as ogg@test) 38> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING DPE1 00:00:00 00:00:03
EXTRACT RUNNING EXTU1 00:00:00 00:00:08
SQL> conn scott/scott
Connected.
SQL>
SQL> insert into test values(3);
1 row created.
SQL>
SQL> commit;
Commit complete.
SQL> select * from test;
ID
----------
2
3
1
5.4.初始化数据完成后设置
5.4.1.编译无效对象(非必须)
@?/rdbms/admin/utlrp.sql
5.4.2.收集统计信息
exec dbms_stats.gather_database_stats(degree =>4);
5.4.3.启动replicat进程
start REP1, aftercsn 2181214
5.4.4.查看状态
GGSCI (Ops-19cOGG) 1> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING REP1 00:00:00 00:00:01
GGSCI (Ops-19cOGG) 2> SEND REPLICAT rep1 STATUS
Sending STATUS request to REPLICAT REP1 ...
Current status: At EOF
Sequence #: 1
RBA: 20,053
0 records in current transaction.
6、简单测试
6.1.DML 测试
源端插入一条语句提交;
SQL> select * from test; ID----------231Elapsed: 00:00:00.00SQL> insert into test values(4);1 row created.Elapsed: 00:00:00.00SQL>SQL> commit;Commit complete.--目标端查看SQL> select * from scott.test; ID NAME---------- ----------1234
6.2.DDL 测试
源端添加字段,修改字段长度
SQL> desc test
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER(38)
SQL>
SQL> alter table test add name varchar(10);
Table altered.
Elapsed: 00:00:00.14
SQL>
SQL> desc test
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER(38)
NAME VARCHAR2(10)
SQL> alter table test modify name varchar2(32);
--添加、删除列
alter table test add changeTime date default sysdate;
Alter table test drop column changeTime;
目标端查看结果
源端 OGG 正常
GGSCI (Ops-11gOGG as ogg@test) 41> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING DPE1 00:00:00 00:00:06
EXTRACT RUNNING EXTU1 00:00:00 00:00:05
目标 OGG 正常
GGSCI (Ops-19cOGG as ogg@testogg) 26> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING REP1 00:00:00 00:00:07
创建表也可以正常同步,下面是创建后获取的建表语句。
SQL> set long 9999 pages 456 line 456
SQL> select dbms_metadata.get_ddl('TABLE','T_OGG_TEST','SCOTT') from dual;
DBMS_METADATA.GET_DDL('TABLE','T_OGG_TEST','SCOTT')
--------------------------------------------------------------------------------
CREATE TABLE "SCOTT"."T_OGG_TEST"
("ID" NUMBER(10,0) NOT NULL ENABLE,
"T_CHAR" CHAR(100),
"T_VARCHAR" VARCHAR2(100),
"T_NUMBER" NUMBER(10,0),
"T_LONG" LONG,
"T_BLOB" BLOB,
"T_DATE" DATE DEFAULT SYSDATE,
"T_TIMESTAMP" TIMESTAMP (6),
PRIMARY KEY ("ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ENABLE,
SUPPLEMENTAL LOG GROUP "GGS_93612" ("ID") ALWAYS) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"
LOB ("T_BLOB") STORE AS BASICFILE (
TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION
NOCACHE LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT))