ogg for oracle 19c cdb安装配置
1.环境信息
源端 | 目标端 | 说明 | |
hostname | zyt001 | zyt004 | 主机名 |
ip | 192.168.163.101 | 192.168.163.104 | ip地址 |
db_name | orcl | orclcdb | 数据库名 |
db_unique_name | orcl | orclcdb | 数据库唯一名 |
数据库版本 | 19.3 | 19.3 | |
系统版本 | centos 7.6 | centos 7.6 | linux系统 |
goldengate版本 | 19.1.0.4 | 19.1.0.4 |
2.准备环境
两台linux Oracle 19c 单实例数据库server
3.操作步骤
注意：源端目标端安装ogg软件方法一样
3.1创建ogg软件安装目录
使用oracle用户
su - oracle
cd /u02
mkdir ogg_work
mkdir soft
3.2上传ogg软件包
用ftp工具或者rz命令上传安装包191004_fbo_ggs_Linux_x64_shiphome.zip到/u02/soft
解压：
unzip 191004_fbo_ggs_Linux_x64_shiphome.zip
3.3安装ogg软件（任选其一进行安装即可）
3.4.1图形安装
打开xmanager-passive,设置环境变量DISPLAY
[oracle@zyt002 soft]$ cd fbo_ggs_Linux_x64_shiphome/Disk1/
[oracle@zyt002 Disk1]$ export DISPLAY=192.168.163.1:0.0
[oracle@zyt002 Disk1]$ xhost +
access control disabled, clients can connect from any host
[oracle@zyt002 Disk1]$ ./runInstaller
调出图形界面：
1.选择19c数据库对应的选项
2.填写ogg软件目录/u02/ogg_work
3.点击install
4.点击close关闭，安装完成
创建目录
cd /u02/ogg_work
./ggsci
CREATE SUBDIRS
到此ogg软件安装完成。
3.4.2静默安装
[oracle@zyt002 soft]$ cd fbo_ggs_Linux_x64_shiphome/Disk1/
修改oggcore.rsp中的以下参数
vi response/oggcore.rsp
INSTALL_OPTION=ORA19c
SOFTWARE_LOCATION=/u02/ogg_work
INVENTORY_LOCATION=/u02/oraInventory
UNIX_GROUP_NAME=oinstall
--静默安装19c
./runInstaller -silent -nowait -responseFile /u02/soft/fbo_ggs_Linux_x64_shiphome/Disk1/response/oggcore.rsp
cd /u02/ogg_work
./ggsci
CREATE SUBDIRS
3.5打开归档
查看源端数据库是否开归档：
zyt001：
sqlplus / as sysdba
archive log list;
如果归档没有打开，则打开归档日志
--打开归档
shutdown immediate
startup mount
alter database archivelog；
alter database open；
--查看归档是否打开
archive log list;
3.6打开强记，附加日志（cdb级别）
alter database force logging; alter database add supplemental log data; ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION = TRUE scope=BOTH;（11g数据库以上需配置） alter system set streams_pool_size=1.25g scope=spfile;--重启生效，一般数据库都是自动分配asmm管理 |
3.7创建ogg管理用户
sqlplus / as sysdba alter session set container=PDB; create tablespace oggadmin datafile '/data/oradata/ORCLC/pdb/gg01.dbf' size 1g autoextend off; create user oggadmin identified by oggadmin account unlock; alter user oggadmin default tablespace oggadmin; grant dba to oggadmin; exec dbms_goldengate_auth.grant_admin_privilege('oggadmin',container=>'PDB'); exit sqlplus / as sysdba create user c##ggadmin identified by ggadmin; exec dbms_goldengate_auth.grant_admin_privilege('c##ggadmin',container=>'ALL'); grant dba to c##ggadmin container=all; |
3.8配置源端mgr进程
cd /u02/ogg_work ./ggsci edit params mgr PORT 7809 DYNAMICPORTLIST 7810-7820 PURGEOLDEXTRACTS ./dirdat/*, USECHECKPOINTS, MINKEEPDAYS 3 PURGEMARKERHISTORY MINKEEPDAYS 3, MAXKEEPDAYS 7 LAGREPORTHOURS 1 LAGINFOMINUTES 30 LAGCRITICALMINUTES 45 ACCESSRULE, PROG *, IPADDR *, ALLOW |
3.9配置源端抽取进程
edit params ext_test extract ext_test SETENV(ORACLE_SID="orcl") SETENV (ORACLE_HOME="/u01/app19c/oracle/product/19.3.0/db_1") SETENV(NLS_LANG=AMERICAN_AMERICA.ZHS16GBK) userid c##ggadmin,password ggadmin FETCHOPTIONS NOUSESNAPSHOT REPORTCOUNT EVERY 1 MINUTES,RATE WARNLONGTRANS 1h,CHECKINTERVAL 5m EXTTRAIL ./dirdat/e0 DISCARDFILE ./dirrpt/ext_test.dsc, APPEND,MEGABYTES 1000 SOURCECATALOG pdb table scott.emp; --添加抽取进程 add extract ext_test, integrated tranlog,begin now add exttrail ./dirdat/e0,extract ext_test MEGABYTES 100 --注册抽取进程 dblogin userid c##ggadmin,password ggadmin register extract ext_test database container(pdb) alter extract ext_test ,scn 34451733333 |
3.10配置源端投递进程
edit params dp_test EXTRACT dp_test RMTHOST 192.168.163.104, MGRPORT 7809, compress PASSTHRU RMTTRAIL ./dirdat/r0 DYNAMICRESOLUTION SOURCECATALOG pdb table table scott.emp --添加投递进程 add extract dp_test,exttrailsource ./dirdat/e0 add rmttrail ./dirdat/r0,extract dp_test |
3.11源端添加trandata
dblogin userid oggadmin@pdb,password oggadmin add trandata table scott.emp |
3.12目标端安装ogg软件（同源端略）
1）创建ogg用户
sqlplus / as sysdba alter session set container=ORCLPDB; create tablespace oggadmin datafile '/data/oradata/ORCLCDB/orclpdb/gg01.dbf' size 1g autoextend off; create user oggadmin identified by oggadmin account unlock; alter user oggadmin default tablespace oggadmin; grant dba to oggadmin; exec dbms_goldengate_auth.grant_admin_privilege('oggadmin',container=>'ORCLPDB'); exit sqlplus / as sysdba create user c##ggadmin identified by ggadmin; exec dbms_goldengate_auth.grant_admin_privilege('c##ggadmin',container=>'ALL'); grant dba to c##ggadmin container=all; |
3.13目标端配置mgr进程
cd /u02/ogg_work ./ggsci edit params mgr PORT 7809 DYNAMICPORTLIST 7810-7820 PURGEOLDEXTRACTS ./dirdat/*, USECHECKPOINTS, MINKEEPDAYS 3 PURGEMARKERHISTORY MINKEEPDAYS 3, MAXKEEPDAYS 7 LAGREPORTHOURS 1 LAGINFOMINUTES 30 LAGCRITICALMINUTES 45 ACCESSRULE, PROG *, IPADDR *, ALLOW |
3.14目标端配置复制进程
edit params rep_test
REPLICAT rep_test SETENV(ORACLE_SID="orclcdb") SETENV (ORACLE_HOME="/u01/app19c/oracle/product/19.3.0/db_1") setenv (NLS_LANG="AMERICAN_AMERICA. ZHS16GBK ") userid c##ggadmin,password ggadmin DISCARDFILE ./dirrpt/rep_test.dsc, APPEND, MEGABYTES 1000 REPORT AT 01:59 REPORTCOUNT EVERY 30 MINUTES, RATE REPERROR DEFAULT, ABEND GETTRUNCATES ALLOWNOOPUPDATES --HANDLECOLLISIONS assumetargetdefs SOURCECATALOG pdb --table map scott.emp, target orclpdb.scott.emp; --添加复制进程 dblogin userid c##ggadmin,password ggadmin add checkpointtable ogg.checkpointtab add replicat rep_test, INTEGRATED,exttrail ./dirdat/r0,checkpointtable ogg.checkpointtab |
4.初始化表数据
4.1 启动抽取进程和投递进程
4.2检查数据库中最老的数据库事务开始的时间
select min(START_TIME) from gv$transaction; |
如果获得的结果和当前时间相差很大，可以考虑让这个事务提交或是回滚，或是kill掉这个session.
4.3获得数据库当前的SCN数值
set num 16 select current_scn from v$database; 34451784376 |
4.4基于上一步获得的SCN进行数据的导出
expdp oggadmin/oggadmin directory=ogg_dump dumpfile=emp.dmp logfile =exp_emp.log\ tables=scott.emp statistics=none FLASHBACK_SCN=34451784376 |
4.5目标库数据库导入数据
impdp oggadmin /oggadmin directory=ogg_dump dumpfile=emp.dmp logfile =imp_emp.log |
4.6目标端启动rep_test进程的时候，需要使用如下的命令启动rep进程
start rep_test , aftercsn 34451784376 |
5.测试同步
源端执行insert delete update操作
目标端，查看对应数据变化
注：以上配置仅针对DML操作
官方文档参考地址
https://docs.oracle.com/en/middleware/goldengate/core/19.1/oracle-db/configuring-extract.html#GUID-74E23910-A441-4939-BDFD-FE0B2EE9783F
https://docs.oracle.com/en/middleware/goldengate/core/19.1/gclir/ggsci-command-line-interface-commands.html#GUID-810596D1-B088-413D-8918-A810ED891962
------Learning records ------the end------
