1.环境
源库: 192.168.18.61/62 ORACLE19.29 RAC LINUX8.1
目标库 : 192.168.18.60 ORACLE19.29 FS LINUX8.1
2.安装OGG
安装OGG19.1.0.0.4 3. 源端CDB/PDB配置
(1).修改数据库配置
SQL> select supplemental_log_data_min from v$database;
SQL> alter database add supplemental log data;CDB/PDB
SQL> select FORCE_LOGGING from v$database;
SQL> alter database force logging;CDB
SQL> alter system switch logfile;
SQL> ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION=TRUE SCOPE=BOTH SID='*';CDB
(2).创建ogg工作空间
-- 创建表空间CDB
create tablespace ogg_tbs datafile '+DGDATA01' size 200m autoextend off;
-- 创建表空间PDB
alter session set container=itpuxpdb;
create tablespace ogg_tbs datafile '+DGDATA01' size 200m autoextend off;
alter session set container=fgedupdb;
create tablespace ogg_tbs datafile '+DGDATA01' size 200m autoextend off;
(3).创建 OGG 用户并授权
alter session set container=CDB$ROOT;
create user c##goldengate identified by goldengate default tablespace ogg_tbs quota unlimited on ogg_tbs;
grant connect,resource to c##goldengate container=all;
grant alter any table to c##goldengate container=all;
grant alter session to c##goldengate container=all;
grant create session to c##goldengate container=all;
grant flashback any table to c##goldengate container=all;
grant select any dictionary to c##goldengate container=all;
grant select any table to c##goldengate container=all;
grant execute any type to c##goldengate container=all;
grant select any transaction to c##goldengate container=all;
grant create any table,create any sequence to c##goldengate container=all;
grant insert any table to c##goldengate container=all;
grant update any table to c##goldengate container=all;
grant delete any table to c##goldengate container=all;
grant create any index to c##goldengate container=all;
grant unlimited tablespace to c##goldengate container=all;
grant execute on dbms_flashback to c##goldengate container=all;
grant comment any table to c##goldengate container=all;
grant dba to c##goldengate container=all;
exec dbms_goldengate_auth.grant_admin_privilege('c##goldengate','*',grant_optional_privileges=>'*',container=>'all');
alter system set streams_pool_size='768M' sid='*' scope=both;
alter session set container=itpuxpdb;
create user goldengate identified by goldengate default tablespace ogg_tbs quota unlimited on ogg_tbs;
grant connect,resource to goldengate;
grant alter any table to goldengate;
grant alter session to goldengate;
grant create session to goldengate;
grant flashback any table to goldengate;
grant select any dictionary to goldengate;
grant select any table to goldengate;
grant execute any type to goldengate;
grant select any transaction to goldengate;
grant create any table,create any sequence to goldengate;
grant insert any table to goldengate;
grant update any table to goldengate;
grant delete any table to goldengate;
grant create any index to goldengate;
grant unlimited tablespace to goldengate;
grant execute on dbms_flashback to goldengate;
grant comment any table to goldengate;
grant dba to goldengate;
exec dbms_goldengate_auth.grant_admin_privilege('goldengate');
alter session set container=fgedupdb;
create user goldengate identified by goldengate default tablespace ogg_tbs quota unlimited on ogg_tbs;
grant connect,resource to goldengate;
grant alter any table to goldengate;
grant alter session to goldengate;
grant create session to goldengate;
grant flashback any table to goldengate;
grant select any dictionary to goldengate;
grant select any table to goldengate;
grant execute any type to goldengate;
grant select any transaction to goldengate;
grant create any table,create any sequence to goldengate;
grant insert any table to goldengate;
grant update any table to goldengate;
grant delete any table to goldengate;
grant create any index to goldengate;
grant unlimited tablespace to goldengate;
grant execute on dbms_flashback to goldengate;
grant comment any table to goldengate;
grant dba to goldengate;
exec dbms_goldengate_auth.grant_admin_privilege('goldengate');
4. 目标端CDB/PDB配置
(1).修改数据库配置
SQL> select supplemental_log_data_min from v$database;
SQL> alter database add supplemental log data;CDB/PDB
SQL> select FORCE_LOGGING from v$database;
SQL> alter database force logging;CDB
SQL> alter system switch logfile;
SQL> ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION=TRUE SCOPE=BOTH;CDB
(2).创建ogg工作空间
-- 创建表空间CDB
create tablespace ogg_tbs datafile size 200m autoextend off;
-- 创建表空间PDB
alter session set container=itpuxpdb;
create tablespace ogg_tbs datafile size 200m autoextend off;
alter session set container=fgedupdb;
create tablespace ogg_tbs datafile size 200m autoextend off;
(3).创建 OGG 用户并授权
alter session set container=CDB$ROOT;
create user c##goldengate identified by goldengate default tablespace ogg_tbs quota unlimited on ogg_tbs;
grant connect,resource to c##goldengate container=all;
grant alter any table to c##goldengate container=all;
grant alter session to c##goldengate container=all;
grant create session to c##goldengate container=all;
grant flashback any table to c##goldengate container=all;
grant select any dictionary to c##goldengate container=all;
grant select any table to c##goldengate container=all;
grant execute any type to c##goldengate container=all;
grant select any transaction to c##goldengate container=all;
grant create any table,create any sequence to c##goldengate container=all;
grant insert any table to c##goldengate container=all;
grant update any table to c##goldengate container=all;
grant delete any table to c##goldengate container=all;
grant create any index to c##goldengate container=all;
grant unlimited tablespace to c##goldengate container=all;
grant execute on dbms_flashback to c##goldengate container=all;
grant comment any table to c##goldengate container=all;
grant dba to c##goldengate container=all;
exec dbms_goldengate_auth.grant_admin_privilege('c##goldengate','*',grant_optional_privileges=>'*',container=>'all');
alter system set streams_pool_size='768M' scope=both;
alter session set container=itpuxpdb;
create user goldengate identified by goldengate default tablespace ogg_tbs quota unlimited on ogg_tbs;
grant connect,resource to goldengate;
grant alter any table to goldengate;
grant alter session to goldengate;
grant create session to goldengate;
grant flashback any table to goldengate;
grant select any dictionary to goldengate;
grant select any table to goldengate;
grant execute any type to goldengate;
grant select any transaction to goldengate;
grant create any table,create any sequence to goldengate;
grant insert any table to goldengate;
grant update any table to goldengate;
grant delete any table to goldengate;
grant create any index to goldengate;
grant unlimited tablespace to goldengate;
grant execute on dbms_flashback to goldengate;
grant comment any table to goldengate;
grant dba to goldengate;
exec dbms_goldengate_auth.grant_admin_privilege('goldengate');
alter session set container=fgedupdb;
create user goldengate identified by goldengate default tablespace ogg_tbs quota unlimited on ogg_tbs;
grant connect,resource to goldengate;
grant alter any table to goldengate;
grant alter session to goldengate;
grant create session to goldengate;
grant flashback any table to goldengate;
grant select any dictionary to goldengate;
grant select any table to goldengate;
grant execute any type to goldengate;
grant select any transaction to goldengate;
grant create any table,create any sequence to goldengate;
grant insert any table to goldengate;
grant update any table to goldengate;
grant delete any table to goldengate;
grant create any index to goldengate;
grant unlimited tablespace to goldengate;
grant execute on dbms_flashback to goldengate;
grant comment any table to goldengate;
grant dba to goldengate;
exec dbms_goldengate_auth.grant_admin_privilege('goldengate');
5.配置源端管理进程
PORT 7809 DYNAMICPORTLIST 7810-7899 PURGEOLDEXTRACTS ./dirdat/*, usecheckpoints, minkeepdays 3 PURGEOLDEXTRACTS ./dirdat/EFG03/*, usecheckpoints, minkeepdays 3 PURGEOLDEXTRACTS ./dirdat/PFG03/*, usecheckpoints, minkeepdays 3 PURGEDDLHISTORY MINKEEPDAYS 7, MAXKEEPDAYS 10 PURGEMARKERHISTORY MINKEEPDAYS 7, MAXKEEPDAYS 10 accessrule, prog server, ipaddr *, pri 1, allow LAGREPORTHOURS 1 LAGINFOMINUTES 30 LAGCRITICALMINUTES 45 AUTOSTART ER * AUTORESTART EXTRACT *, RETRIES 5, WAITMINUTES 3
6.源端配置OGG认证
ggsci ADD CREDENTIALSTORE ALTER CREDENTIALSTORE ADD USER c##goldengate@itpuxdb alias itpuxdbcdb domain oracle info CREDENTIALSTORE domain oracle dblogin useridalias itpuxdbcdb domain oracle ALTER CREDENTIALSTORE ADD USER goldengate@itpuxpdb alias itpuxpdb domain oracle dblogin useridalias itpuxpdb domain oracle ALTER CREDENTIALSTORE ADD USER goldengate@fgedupdb alias fgedupdb domain oracle dblogin useridalias fgedupdb domain oracle
7.源端设置检查点和心跳
#设置检查点表 EDIT PARAMS ./GLOBALS GGSCHEMA goldengate CHECKPOINTTABLE goldengate.checkpoint #检查点和心跳表要设置在PDB级别 dblogin useridalias itpuxpdb domain oracle add checkpointtable goldengate.checkpoint ADD HEARTBEATTABLE INFO HEARTBEATTABLE dblogin useridalias fgedupdb domain oracle add checkpointtable goldengate.checkpoint ADD HEARTBEATTABLE INFO HEARTBEATTABLE
8.源端对同步对象添加补充日志
dblogin useridalias itpuxpdb domain oracle GGSCI> ADD SCHEMATRANDATA itpux01 GGSCI> ADD TRANDATA itpux02.ITPUX02_YG GGSCI> ADD TRANDATA itpux02.ITPUX02_YS GGSCI> ADD TRANDATA itpux03.itpux03_MEMBER
9.目标端配置mgr
PORT 7809 DYNAMICPORTLIST 7810-7899 PURGEOLDEXTRACTS ./dirdat/*, usecheckpoints, minkeepdays 3 PURGEOLDEXTRACTS ./dirdat/RFG03/*, usecheckpoints, minkeepdays 3 PURGEDDLHISTORY MINKEEPDAYS 7, MAXKEEPDAYS 10 PURGEMARKERHISTORY MINKEEPDAYS 7, MAXKEEPDAYS 10 accessrule, prog server, ipaddr *, pri 1, allow LAGREPORTHOURS 1 LAGINFOMINUTES 30 LAGCRITICALMINUTES 45 AUTORESTART REPLICAT *, RETRIES 5, WAITMINUTES 3
10.目标端配置OGG认证
ggsci ADD CREDENTIALSTORE ALTER CREDENTIALSTORE ADD USER goldengate@itpuxpdb alias itpuxpdb domain oracle info CREDENTIALSTORE domain oracle dblogin useridalias itpuxpdb domain oracle ALTER CREDENTIALSTORE ADD USER goldengate@fgedupdb alias fgedupdb domain oracle info CREDENTIALSTORE domain oracle dblogin useridalias fgedupdb domain oracle
11.目标端设置检查点和心跳
#设置检查点表 EDIT PARAMS ./GLOBALS GGSCHEMA goldengate CHECKPOINTTABLE goldengate.checkpoint dblogin useridalias itpuxpdb domain oracle add checkpointtable goldengate.checkpoint ADD HEARTBEATTABLE INFO HEARTBEATTABLE dblogin useridalias fgedupdb domain oracle add checkpointtable goldengate.checkpoint ADD HEARTBEATTABLE INFO HEARTBEATTABLE
12.配置抽取进程
cd /ogg mkdir -p ./dirdat/EFG03 mkdir -p ./dirrpt/EFG03 EXTRACT EFG03 SETENV (ORACLE_HOME ="/oracle/app/oracle/product/19c/db_1") SETENV (ORACLE_SID = "itpuxdb1") SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8) useridalias itpuxdbcdb domain oracle TRANLOGOPTIONS INTEGRATEDPARAMS(max_sga_size 256, parallelism 2) TRANLOGOPTIONS DBLOGREADER #集成模式不使用 DBOPTIONS ALLOWUNUSEDCOLUMN FETCHOPTIONS NOUSESNAPSHOT GETTRUNCATES WARNLONGTRANS 2h, CHECKINTERVAL 5m DISCARDFILE ./dirrpt/EFG03/EFG03.dsc, APPEND, MEGABYTES 1024 DISCARDROLLOVER AT 02:30 REPORTCOUNT EVERY 1 MINUTES, RATE EXTTRAIL ./dirdat/EFG03/ex TABLE itpuxpdb.itpux01.*; TABLE itpuxpdb.itpux02.ITPUX02_YG; TABLE itpuxpdb.itpux02.ITPUX02_YS; TABLE itpuxpdb.itpux03.ITPUX03_MEMBER; DBLOGIN USERIDALIAS itpuxdbcdb DOMAIN oracle REGISTER EXTRACT EFG03 DATABASE CONTAINER (itpuxpdb) ADD EXTRACT EFG03, INTEGRATED TRANLOG, BEGIN NOW (集成模式自动抽取RAC所有节点) add exttrail ./dirdat/EFG03/ex,extract EFG03,MEGABYTES 1024
13.配置pump进程
cd /ogg mkdir -p ./dirdat/PFG03 mkdir -p ./dirrpt/PFG03 EXTRACT PFG03 SETENV (NLS_LANG="AMERICAN_AMERICA.AL32UTF8") useridalias itpuxdbcdb domain oracle PASSTHRU RMTHOST 192.168.18.60, MGRPORT 7809 RMTTRAIL ./dirdat/RFG03/re DISCARDFILE ./dirrpt/PFG03/PFG03.dsc, APPEND, MEGABYTES 1024 DISCARDROLLOVER AT 02:30 TABLE itpuxpdb.itpux01.*; TABLE itpuxpdb.itpux02.ITPUX02_YG; TABLE itpuxpdb.itpux02.ITPUX02_YS; TABLE itpuxpdb.itpux03.ITPUX03_MEMBER; add extract PFG03,exttrailsource ./dirdat/EFG03/ex add rmttrail ./dirdat/RFG03/re,EXTRACT PFG03,MEGABYTES 1024
14.目标端应用进程配置
cd /ogg mkdir -p ./dirdat/RFG03 mkdir -p ./dirrpt/RFG03 edit params RFG03 REPLICAT RFG03 -- 环境变量 setenv (ORACLE_HOME ="/oracle/app/oracle/product/19c/db_1") setenv (ORACLE_SID = "itpuxdb") setenv (NLS_LANG=AMERICAN_AMERICA.AL32UTF8) setenv (TNS_ADMIN=/oracle/app/oracle/product/19c/db_1/network/admin) -- 数据库连接（使用加密密码，并指定PDB） USERIDALIAS itpuxpdb DOMAIN oracle -- 丢弃文件管理 DISCARDFILE ./dirrpt/RFG03/RFG03.dsc, APPEND, MEGABYTES 1024 DISCARDROLLOVER AT 02:30 -- 错误处理 REPERROR DEFAULT, ABEND REPORTCOUNT EVERY 30 MINUTES, RATE -- 数据应用选项 ALLOWNOOPUPDATES ASSUMETARGETDEFS -- 数据映射 MAP itpuxpdb.itpux01.*,TARGET itpuxpdb.itpux01.*; MAP itpuxpdb.itpux02.ITPUX02_YG,TARGET itpuxpdb.itpux02.ITPUX02_YG; MAP itpuxpdb.itpux02.ITPUX02_YS,TARGET itpuxpdb.itpux02.ITPUX02_YS; MAP itpuxpdb.itpux03.ITPUX03_MEMBER,TARGET itpuxpdb.itpux03.ITPUX03_MEMBER; 使用检查点表（推荐） add replicat RFG03, exttrail ./dirdat/RFG03/re, checkpointtable goldengate.checkpoint
15.为第二个PDB配置抽取进程
cd /ogg mkdir -p ./dirdat/EFG04 mkdir -p ./dirrpt/EFG04 EXTRACT EFG04 SETENV (ORACLE_HOME ="/oracle/app/oracle/product/19c/db_1") SETENV (ORACLE_SID = "itpuxdb1") SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8) useridalias itpuxdbcdb domain oracle TRANLOGOPTIONS INTEGRATEDPARAMS(max_sga_size 256, parallelism 2) DBOPTIONS ALLOWUNUSEDCOLUMN FETCHOPTIONS NOUSESNAPSHOT GETTRUNCATES WARNLONGTRANS 2h, CHECKINTERVAL 5m DISCARDFILE ./dirrpt/EFG04/EFG04.dsc, APPEND, MEGABYTES 1024 DISCARDROLLOVER AT 02:30 REPORTCOUNT EVERY 1 MINUTES, RATE EXTTRAIL ./dirdat/EFG04/ex TABLE fgedupdb.fgedu.*; #添加补充日志 dblogin useridalias fgedupdb domain oracle ADD SCHEMATRANDATA fgedu #创建抽取 DBLOGIN USERIDALIAS itpuxdbcdb DOMAIN oracle REGISTER EXTRACT EFG04 DATABASE CONTAINER(FGEDUPDB) ADD EXTRACT EFG04, INTEGRATED TRANLOG, BEGIN NOW (集成模式自动抽取RAC所有节点) add exttrail ./dirdat/EFG04/ex,extract EFG04,MEGABYTES 1024
16. 为第二个PDB配置pump进程
cd /ogg mkdir -p ./dirdat/PFG04 mkdir -p ./dirrpt/PFG04 EXTRACT PFG04 SETENV (NLS_LANG="AMERICAN_AMERICA.AL32UTF8") useridalias itpuxdbcdb domain oracle PASSTHRU RMTHOST 192.168.18.60, MGRPORT 7809 RMTTRAIL ./dirdat/RFG04/re DISCARDFILE ./dirrpt/PFG04/PFG04.dsc, APPEND, MEGABYTES 1024 DISCARDROLLOVER AT 02:30 TABLE fgedupdb.fgedu.*; add extract PFG04,exttrailsource ./dirdat/EFG04/ex add rmttrail ./dirdat/RFG04/re,EXTRACT PFG04,MEGABYTES 1024
17. 为第二个PDB 配置目标端应用进程
cd /ogg mkdir -p ./dirdat/RFG04 mkdir -p ./dirrpt/RFG04 edit params RFG04 REPLICAT RFG04 -- 环境变量 setenv (ORACLE_HOME ="/oracle/app/oracle/product/19c/db_1") setenv (ORACLE_SID = "itpuxdb") setenv (NLS_LANG=AMERICAN_AMERICA.AL32UTF8) setenv (TNS_ADMIN=/oracle/app/oracle/product/19c/db_1/network/admin) -- 数据库连接（使用加密密码，并指定PDB） USERIDALIAS fgedupdb DOMAIN oracle -- 丢弃文件管理 DISCARDFILE ./dirrpt/RFG04/RFG04.dsc, APPEND, MEGABYTES 1024 DISCARDROLLOVER AT 02:30 -- 错误处理 REPERROR DEFAULT, ABEND REPORTCOUNT EVERY 30 MINUTES, RATE -- 数据应用选项 DBOPTIONS ENABLE_INSTANTIATION_FILTERING ALLOWNOOPUPDATES ASSUMETARGETDEFS -- 并行设置 MAP_PARALLELISM 4 MIN_APPLY_PARALLELISM 2 MAX_APPLY_PARALLELISM 4 SPLIT_TRANS_RECS 4 -- 数据映射 MAP fgedupdb.fgedu.*,TARGET fgedupdb.fgedu.*; 使用检查点表（推荐） add replicat RFG04, PARALLEL，exttrail ./dirdat/RFG04/re, checkpointtable goldengate.checkpoint
