1.整体数据库配置
三个源端和一个downstream下游抽取数据库配置：
alter system set enable_goldengate_replication=TRUE;
Alter database add supplemental log data;
Alter database add supplemental log data(primary key,unique index) columns;
Alter database add supplemental log data(all) columns;
Alter database force logging;
select SUPPLEMENTAL_LOG_DATA_MIN,SUPPLEMENTAL_LOG_DATA_PK,SUPPLEMENTAL_LOG_DATA_UI,SUPPLEMENTAL_LOG_DATA_FK,SUPPLEMENTAL_LOG_DATA_ALL,force_logging
from v$database;
2.SOURCE DB1、2、3端数据库参数调整和配置：
1.db1,db2,db3 三个库创建表空间和用户
create tablespace ogg datafile ‘ xxxxx.dbf ’ size 1g autoextend on next 1g;
alter tablespace ogg add datafile ‘ xxxx.dbf ’ size 1g autoextend on next 1g;
create user ogg account identified by oracle ;
2. 授权：
begin
dbms_goldengate_auth.grant_admin_privilege(grantee=>'OGG',
GRANT_SELECT_PRIVILEGES=>TRUE,
PRIVILEGE_TYPE=>'CAPTURE',
DO_GRANTS=>TRUE);
END;
/
EXEC DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE('OGG');
EXEC DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE('OGG');
GRANT SELECT ON V_$DATABASE TO OGG ;
GRANT EXECUTE ON OGG.UPDATESEQUENCE TO OGG;
GRANT EXECUTE ON OGG.REPLICATESEQUENCE TO OGG;
GRANT SELECT ON SYS.SEQ$ TO OGG;
GRANT SELECT ON SYS.USER$ TO OGG;
GRANT SELECT ON SYS.OBJ$ TO OGG;
GRANT SELECT ON DBA_SEQUENCES TO OGG;
GRANT CONNECT, RESOURCE 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 SELECT ON DBA_CLUSTERS TO OGG;
GRANT SELECT ANY TRANSACTION TO OGG;
一般我都是直接给：
grant connect,resource,dba to ogg;
3. 建表：
create table ogg.t1 as select OBJECT_ID,OWNER,OBJECT_NAME,CREATED from dba_segments ;
create table ogg.t2 (
pid number(20) primary key not null ,
product varchar2(40),
location varchar2(40),
produce varchar2(40)
);
-- 暂时不插入数据，后面 ogg 进程配置完毕后插入
begin
for v_count in 1..100000000 loop
insert into ogg.t2 values(v_count,'rose','hangzhou10','zhejiang');
commit;
end loop;
end;
/
4. 配置 tns 连接信息三个 sourceDB 和一个下游抽取 db-mid 都要配置
dba1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.147.249.104)(PORT = 21521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dba1)
)
)
dba2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.147.249.104)(PORT = 21521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dba2)
)
)
dba3 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.147.249.104)(PORT = 21521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dba3)
)
)
mid =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.147.249.105)(PORT = 21521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = mid)
)
)
5. 设置本地归档路径和配置日志传输参数
dba1:
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=/u01/arch1/ VALID_FOR=(ONLINE_LOGFILE, PRIMARY_ROLE)';
ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(dba1,mid)';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1=ENABLE ;
设置service 归档传到目标端路径位置 :
目标端创建3 个目录，分别存放 db1 db2 db3 的归档日志：
alter system set log_archive_dest_5='SERVICE=mid ASYNC OPTIONAL NOREGISTER TEMPLATE=/u01/arch_dba1/dba1_arch_%t_%s_%r.dbf VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) REOPEN=300 DB_UNIQUE_NAME=mid'
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_5=ENABLE;
dba2:
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=/u02/arch2/ VALID_FOR=(ONLINE_LOGFILE, PRIMARY_ROLE)';
ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(dba2,mid)';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1=ENABLE ;
设置service 目标端归档存放位置：
alter system set log_archive_dest_6='SERVICE=mid ASYNC OPTIONAL NOREGISTER TEMPLATE=/u01/arch_dba2/dba2_arch_%t_%s_%r.dbs VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) REOPEN=300 DB_UNIQUE_NAME=mid'
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_6=ENABLE;
dba3:
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=/ u03 / arch3 / VALID_FOR=(ONLINE_LOGFILE, PRIMARY_ROLE)';
ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(dba 3 ,mid)';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1=ENABLE ;
设置service 目标端归档日志存放位置：
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_7=ENABLE;
附录TNS 配置文件截图
：
3.downstream DB-mid端数据库参数调整和配置：
1.ogg 用户创建和授权完成；
2. 添加 standby_redo （ standby redo 配置和 DG (n+1)*(thread) 原则）
alter database add standby logfile group 11 '/u01/app/oracle/oradata/MID/standby_redo11.log' size 200m;
alter database add standby logfile group 12 '/u01/app/oracle/oradata/MID/standby_redo12.log' size 200m;
alter database add standby logfile group 13 '/u01/app/oracle/oradata/MID/standby_redo13.log' size 200m;
alter database add standby logfile group 14 '/u01/app/oracle/oradata/MID/standby_redo14.log' size 200m;
3. 配置 arch standby_redo 归档位置
配置本库归档日志即（mid-db ）
下游抽取库配置：
alter system set log_archive_config='DG_CONFIG=(dba1,dba2,dba3,mid)';
本身归档位置：
alter system set log_archive_dest_1='LOCATION=/u01/arch_mid/ VALID_FOR=(ONLINE_LOGFILE,PRIMARY_ROLE)' DB_UNIQUE_NAME=mid;
配置downstream_real_time_mine Y 的 standby_redo 日志即接收过来的归档日志
alter system set log_archive_dest_5='LOCATION=/u01/arch_dba1/ VALID_FOR=(STANDBY_LOGFILE,PRIMARY_ROLE)';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_5=ENABLE;
置于dba2 dba3 的日志位置为什么不用配置？因为在源端的时候我们已经指定了日志传输位置。
那么dba1 同时也指定了日志传输位置，为啥还要配置呢？ downstream_real_time_mine Y 可以实时解析 standby_redo ，也就是说 dba1 的 redo 会以 dg 的形式传输到 mid 下游库来解析的，所以 standby_redo 归档到 /u01/arch_dba1 目录下。
注意：三个源端--- 日志传输到 mid db 下游库做日志分析，是利用了 dg 的特性，
但是只能有一个downstream_real_time_mine Y
其他两个downstream_real_time_mine N 且必须置于 N
在这里我们让dba1 是 Y ， dba2 、 dba3 是 N 配置 OGG 进程。
4.downstream-架构的密码文件配置：
密码文件配置：
dba1 生成密码文件：
cd $ORACLE_HOME/dbs
orapwd file=orapwdba1 password=Golden_1230 force=y sys=y
cp orapwdba1 orapwdba2
cp orapwdba1 orapwdba3
cp orapwdba1 mid
密码文件保证这几个库密码一致，日志传输正常进行。--- 至此为止日志传输算是正常进行了！！！
5.downstream-架构-install goldengate software
OK goldengate software install finished !!!
6.downstream-架构-configre goldengate extract
6. 1编辑MGR参数文件
PORT 7809
DYNAMICPORTLIST 7810-7900
AUTORESTART EXTRACT * ,WAITMINUTES 2, RETRIES 10
LAGREPORTHOURS 1
LAGINFOMINUTES 30
LAGCRITICALMINUTES 45
PURGEOLDEXTRACTS /ogg/dirdat/*,USECHECKPOINTS, minkeephours 2
启动mgr 进程：
start mgr
6.2 编辑extract for dba1的抽取进程extdb1参数文件
-------- 配置集成抽取模式
1. 编辑extract 进程参数文件；
EXTRACT extdb1
USERID ogg@dba1, PASSWORD oracle
TRANLOGOPTIONS mininguser ogg@mid miningpassword oracle
TRANLOGOPTIONS INTEGRATEDPARAMS (downstream_real_time_mine Y)
TRANLOGOPTIONS integratedparams ( max_sga_size 2048 ) -- , parallelism 4)
cachemgr cachesize 2g
-- tranlogoptions _NOREADAHEAD ANY
getupdatebefores
GETTRUNCATES
REPORTCOUNT EVERY 15 MINUTES, RATE
numfiles 5000
DISCARDFILE ./dirrpt/extdb1.dsc,APPEND,MEGABYTES 1024
WARNLONGTRANS 2h,CHECKINTERVAL 10m
EXTTRAIL ./dirdat/a1
FETCHOPTIONS NOUSESNAPSHOT
DBOPTIONS ALLOWUNUSEDCOLUMN
DYNAMICRESOLUTION
TABLE OGG.T1;
TABLE OGG.T2;
2. 登录数据库和mid 下游抽取库；
dblogin USERID ogg@dba1, PASSWORD oracle
miningdblogin userid ogg@mid ,PASSWORD oracle
3. 在数据库中注册extract 抽取进程；
register extract extdb1 database
4. 添加集成抽取进程，tranlog begin now;
add extract extdb1 integrated tranlog begin now
5. 分配进程组使用的trail 文件；
add exttrail ./dirdat/a1 extract extdb1 megabytes 1024
6. 启动extdb1 抽取进程；
start extdb1
------- 非集成抽取模式 - 即经典抽取模式 classical 抽取模式
编辑参数文件ext11
EXTRACT ext11
USERID ogg@dba1, PASSWORD oracle
--TRANLOGOPTIONS mininguser ogg@mid miningpassword oracle
--TRANLOGOPTIONS INTEGRATEDPARAMS (downstream_real_time_mine Y)
--TRANLOGOPTIONS integratedparams ( max_sga_size 2048 ) -- , parallelism 4)
cachemgr cachesize 2g
-- tranlogoptions _NOREADAHEAD ANY
getupdatebefores
GETTRUNCATES
REPORTCOUNT EVERY 15 MINUTES, RATE
numfiles 5000
DISCARDFILE ./dirrpt/ext11.dsc,APPEND,MEGABYTES 1024
WARNLONGTRANS 2h,CHECKINTERVAL 10m
EXTTRAIL ./dirdat/zz
FETCHOPTIONS NOUSESNAPSHOT
DBOPTIONS ALLOWUNUSEDCOLUMN
--DYNAMICRESOLUTION
tranlogoptions dblogreader
TABLE OGG.T1;
TABLE OGG.T2;
添加ext11 抽取进程，并分配 ./dirdat/zz trail 文件给 ext11 进程组使用：
GGSCI (qhdb2.zpepc.com.cn as ogg@dba1) 160> add extract ext11 ,tranlog ,begin now
EXTRACT added.
GGSCI (qhdb2.zpepc.com.cn as ogg@dba1) 161> add exttrail ./dirdat/zz ,extract ext11 ,megabytes 2000
EXTTRAIL added.
GGSCI (qhdb2.zpepc.com.cn as ogg@dba1) 162> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT1 00:07:09 00:00:02
EXTRACT STOPPED EXT11 00:00:00 00:00:22
启动经典模式抽取进程：
start ext11
6.3 编辑extract for dba1的抽取进程ext2参数文件
编辑ext2 参数文件：
GGSCI (qhdb2.zpepc.com.cn as ogg@dba2) 13> view param ext2
EXTRACT ext2
USERID ogg@dba2, PASSWORD oracle
TRANLOGOPTIONS mininguser ogg@mid miningpassword oracle
TRANLOGOPTIONS INTEGRATEDPARAMS (downstream_real_time_mine N)
TRANLOGOPTIONS integratedparams ( max_sga_size 2048 ) -- , parallelism 4)
cachemgr cachesize 2g
-- tranlogoptions _NOREADAHEAD ANY
getupdatebefores
GETTRUNCATES
REPORTCOUNT EVERY 15 MINUTES, RATE
numfiles 5000
DISCARDFILE ./dirrpt/ext2.dsc,APPEND,MEGABYTES 1024
WARNLONGTRANS 2h,CHECKINTERVAL 10m
EXTTRAIL ./dirdat/t2
FETCHOPTIONS NOUSESNAPSHOT
DBOPTIONS ALLOWUNUSEDCOLUMN
--DYNAMICRESOLUTION
--tranlogoptions dblogreader
TABLE OGG.T1;
TABLE OGG.T2;
注册extract -ext2 进程到数据库：
GGSCI (qhdb2.zpepc.com.cn as ogg@dba2) 14> register extract ext2 database
2021-07-08 16:34:54 INFO OGG-15418 Usage of template clause for log archive destination '6' will cause redo transport to only send archived redo to downstream.
2021-07-08 16:35:06 INFO OGG-02003 Extract EXT2 successfully registered with database at SCN 139768734.
GGSCI (qhdb2.zpepc.com.cn as ogg@dba2) 15> add extract ext2 integrated tranlog begin now
EXTRACT (Integrated) added.
GGSCI (qhdb2.zpepc.com.cn as ogg@dba2) 16> add exttrail ./dirdat/t2 extract ext2 megabytes 2000
EXTTRAIL added.
6.2 编辑extract for dba1的抽取进程ext3参数文件
编辑ext3 参数文件
GGSCI (qhdb2.zpepc.com.cn) 1> dblogin USERID ogg@dba3, PASSWORD oracle
Successfully logged into database.
GGSCI (qhdb2.zpepc.com.cn as ogg@dba3) 2> miningdblogin userid ogg@mid ,PASSWORD oracle
Successfully logged into mining database.
EXTRACT ext3
USERID ogg@dba3, PASSWORD oracle
TRANLOGOPTIONS mininguser ogg@mid miningpassword oracle
TRANLOGOPTIONS INTEGRATEDPARAMS (downstream_real_time_mine N)
TRANLOGOPTIONS integratedparams ( max_sga_size 2048 ) -- , parallelism 4)
cachemgr cachesize 2g
-- tranlogoptions _NOREADAHEAD ANY
getupdatebefores
GETTRUNCATES
REPORTCOUNT EVERY 15 MINUTES, RATE
numfiles 5000
DISCARDFILE ./dirrpt/ext3.dsc,APPEND,MEGABYTES 1024
WARNLONGTRANS 2h,CHECKINTERVAL 10m
EXTTRAIL ./dirdat/t3
FETCHOPTIONS NOUSESNAPSHOT
DBOPTIONS ALLOWUNUSEDCOLUMN
--DYNAMICRESOLUTION
--tranlogoptions dblogreader
TABLE OGG.T1;
TABLE OGG.T2;
注册ext3 到数据库：
GGSCI (qhdb2.zpepc.com.cn as ogg@dba3) 5> register extract ext3 database
2021-07-08 16:40:09 INFO OGG-15418 Usage of template clause for log archive destination '7' will cause redo transport to only send archived redo to downstream.
2021-07-08 16:40:18 INFO OGG-02003 Extract EXT3 successfully registered with database at SCN 139427050.
GGSCI (qhdb2.zpepc.com.cn as ogg@dba3) 6> add extract ext3 integrated tranlog begin now
EXTRACT (Integrated) added.
GGSCI (qhdb2.zpepc.com.cn as ogg@dba3) 7> add exttrail ./dirdat/t3 ,extract ext3 megabytes 1000
EXTTRAIL added.
查看dirdat 目录下 trail 文件产生的情况：
7.downstream-架构-configre goldengate datapump
配置datapump投递进程
配置datapump 投递进程 for ext1
编辑参数文件dpet1
EXTRACT dpet1
PASSTHRU
RMTHOST 127.0.0.1, MGRPORT 7809, compress
RMTTRAIL ./dirdat/local/t1 -- , FORMAT RELEASE 12.2
TABLE OGG.T1;
TABLE OGG.T2;
添加datapump 投递进程：
添加dpet1 投递进程
GGSCI (qhdb2.zpepc.com.cn as ogg@dba1) 7>
add extract dpet1 ,exttrailsource ./dirdat/aa
EXTRACT added.
添加远端trail 文件（备注： goldengate 目标端的 trail 文件是在源端定义的，所以在源端添加目标端的 trail 文件）
GGSCI (qhdb2.zpepc.com.cn as ogg@dba1) 8>
add RMTTRAIL ./dirdat/local/t1 ,extract dpet1 ,megabytes 2000
RMTTRAIL added.
启动dpet1 投递进程：
./dirdat/local/t1xxxxx trail 文件生成！
8.downstream-架构-configre goldengate replicat
配置replicat复制进程
replicat 进程有两种模式：
1. 传统的replicat 复制；
编辑全局参数文件：
GGSCI (qhdb2.zpepc.com.cn) 2> edit param ./GLOBALS
GGSCHEMA ogg
checkpointtable ogg.ckpt
添加checkpointtable
GGSCI (qhdb2.zpepc.com.cn) 4> dblogin USERID ogg@tagdb,PASSWORD oracle
Successfully logged into database.
GGSCI (qhdb2.zpepc.com.cn as ogg@mid) 5> add checkpointtable ogg.ckpt
Successfully created checkpoint table ogg.ckpt.
添加replicat 进程 - rept1
REPLICAT rept1
--setenv (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
--setenv (ORACLE_SID='mid')
--setenv (ORACLE_HOME='/u01/app/oracle/product/19.3.0/db_home1')
--setenv (ORA_TZFILE='/u01/app/oracle/product/19.3.0/db_home1/oracore/zoneinfo/timezone_32.dat')
USERID ogg@tagdb, PASSWORD oracle
SQLEXEC 'ALTER SESSION SET CONSTRAINTS=DEFERRED'
REPORT AT 01:59
REPORTCOUNT EVERY 30 MINUTES, RATE
REPERROR DEFAULT, ABEND
--reperror -1 , DISCARD
--reperror 1403 , DISCARD
--ASSUMETARGETDEFS
MaxDiscardRecs 200
--HANDLECOLLISIONSM-#M-(
--batchsql BATCHESPERQUEUE 100, OPSPERBATCH 8000
--GROUPTRANSOPS 2000
--MAXTRANSOPS 500
assumetargetdefs
--sourcedefs ./dirdef/xxxx.def
DISCARDFILE ./dirrpt/rept1.dsc, purge, MEGABYTES 1024
DISCARDROLLOVER AT 02:30 on sunday
GETTRUNCATES
ALLOWNOOPUPDATES
--sourcecharset passthru
MAP OGG.t1 , target ogg.t1 ;
----filter ( @getenv('TRANSACTION','CSN') > 15756503460690 ) ;
添加rept1 进程：
add replicat rept1 ,exttrail ./dirdat/local/t1 checkpointtable ogg.ckpt
2. integrated replicat 复制； ( 不需要 checkpointtable)
add replicat rept1 integrated exttrail ./dirdat/local/t1
------- 添加完毕后， start * 启动进程即可。！！！
