系列文章目录
第一章 ADG基本知识
第二章 oracle18C ADG部署流程
第三章 oracle18c OGG级联模式（ADG模式 级联 Downstream库+OGG18）
前言
oracle18c ADG downstream OGG
一、环境说明
oracle版本：Version 18.3.0.0.0
| 角色 | IP | 主机名 | sid | global_names | service_names | db_unique_name |
|---|---|---|---|---|---|---|
| 主库 | 192.168.103.31 | pri | orcl | pri | pri | pri |
| 备库 | 192.168.103.32 | adg | orcl | adg | adg | adg |
| downstream | 192.168.103.33 | ds | orcl | ds | ds | ds |
二、配置ORACLE
1.检查数据库各个参数
要保证每个实例的db_unique_name和global_name不同
SQL> show parameter db_unique_name;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string pri
SQL> select * from global_name;
GLOBAL_NAME
--------------------------------------------------------------------------------
pri
2.设置下游挖掘数据库（downstream 以下简称DS库）为归档模式
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 1577058176 bytes
Fixed Size 8896384 bytes
Variable Size 503316480 bytes
Database Buffers 1056964608 bytes
Redo Buffers 7880704 bytes
Database mounted.
SQL>
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.
3.在PRI库上开启强制日志及附加日志
SQL> alter database force logging;
Database altered.
SQL> alter database add supplemental log data;
Database altered.
SQL> alter database add supplemental log data (primary key,unique,foreign key) columns;
Database altered.
SQL> select log_mode,force_logging,supplemental_log_data_min, supplemental_log_data_pk,supplemental_log_data_ui from v$database;
LOG_MODE FORCE_LOGGING SUPPLEME SUP SUP
------------ --------------------------------------- -------- --- ---
ARCHIVELOG YES YES YES YES
4.配置级联的归档配置（三个库均要设置）
SQL> alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(pri,adg,ds)' scope=both sid='*';
System altered.
5.ADG库上配置日志发送
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_3='SERVICE=ds ASYNC OPTIONAL NOREGISTER VALID_FOR=(STANDBY_LOGFILES, STANDBY_ROLE) DB_UNIQUE_NAME=ds' scope=both;
System altered.
让设置生效
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_3=ENABLE;
System altered.
6.DS库上配置接收日志
SQL> host
[oracle@ds ~]$ mkdir /u01/app/oracle/oradata/orcl/ORCL/stdarch/ -p
[oracle@ds ~]$
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ONLINE_LOGFILE,PRIMARY_ROLE)' scope=both;
System altered.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='LOCATION=/u01/app/oracle/oradata/orcl/ORCL/stdarch VALID_FOR=(ONLINE_LOGFILE,PRIMARY_ROLE)' scope=both;
System altered.
让设置生效
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1=ENABLE;
System altered.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;
System altered.
查看配置
SQL> show parameter LOG_ARCHIVE_CONFIG
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_config string DG_CONFIG=(pri,adg,ds)
SQL> show parameter log_archive_dest_1
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1 string LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ONLINE_LOGFILE,PRIMARY_ROLE)
log_archive_dest_10 string
log_archive_dest_11 string
log_archive_dest_12 string
log_archive_dest_13 string
log_archive_dest_14 string
log_archive_dest_15 string
log_archive_dest_16 string
log_archive_dest_17 string
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_18 string
log_archive_dest_19 string
SQL> show parameter log_archive_dest_2
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2 string LOCATION=/u01/app/oracle/oradata/orcl/ORCL/stdarch VALID_FOR=(STANDBY_LOGFILE,PRIMARY_ROLE)
log_archive_dest_20 string
log_archive_dest_21 string
log_archive_dest_22 string
log_archive_dest_23 string
log_archive_dest_24 string
log_archive_dest_25 string
log_archive_dest_26 string
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_27 string
log_archive_dest_28 string
log_archive_dest_29 string
7.DS库添加备用日志文件组接收日志
（如果只用归档实时则不需要）
日志大小和数量参考标准,两个原则：
- 每个备用重做日志文件必须至少与重做源数据库的重做日志中最大的重做日志文件一样大。为便于管理，Oracle 建议重做源数据库的重做日志和重做传输目的地的备用重做日志中的所有重做日志文件大小相同。
- 对于重做源数据库上的每个重做线程，备用重做日志必须至少比重做源数据库上的重做日志多一个重做日志组。
确定日志组和大小：
SQL> SELECT GROUP#, BYTES FROM V$LOG;
GROUP# BYTES
---------- ----------
1 209715200
2 209715200
3 209715200
日志组数量为3，大小为200M，则备用redo日志应该至少为4个200M的日志文件
SQL> ALTER DATABASE ADD STANDBY LOGFILE SIZE 200M;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE SIZE 200M;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE SIZE 200M;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE SIZE 200M;
Database altered.
SQL> SELECT GROUP#, BYTES FROM V$STANDBY_LOG;
GROUP# BYTES
---------- ----------
4 209715200
5 209715200
6 209715200
7 209715200
8.需要主库发送密码文件到DS库
（因为 ADG库的密码文件==PRI库的密码文件，DS库的密码文件==ADG库的密码文件）
[oracle@pri dbs]$ cd $ORACLE_HOME/dbs/
[oracle@pri dbs]$ ls
hc_orcl.dat init.ora lkORCL lkPRI orapworcl spfileorcl.ora
[oracle@pri dbs]$ scp orapworcl 192.168.103.32:/u01/app/oracle/product/18.3.0/db_1/dbs
oracle@192.168.103.32's password:
orapworcl 100% 3584 2.2MB/s 00:00
9.创建OGG账号（PRI和DS操作）
SQL> create user c##ogg identified by "ogg@123" ;
User created.
SQL> grant connect,resource,alter system,select any dictionary to c##ogg container=all;
Grant succeeded.
SQL> ALTER USER c##ogg QUOTA UNLIMITED ON users;
User altered.
SQL> EXEC DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE(grantee => 'c##OGG', privilege_type => 'capture', grant_select_privileges=> true, do_grants => TRUE,container=>'all');
PL/SQL procedure successfully completed.
10.DS库添加ogg复制权限
SQL> show parameter ENABLE_GOLDENGATE_REPLICATION
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
enable_goldengate_replication boolean FALSE
SQL> ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION = true scope=both sid='*';
System altered.
SQL> show parameter ENABLE_GOLDENGATE_REPLICATION
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
enable_goldengate_replication boolean TRUE
三、配置OGG
3.1 DS库上安装OGG
3.1.1 上传安装包
上传ogg18.tar.gz到Oracle服务器
3.1.2 解压文件：
mkdir /tmp/ogginstall
tar xvf ogg18.tar.gz -C /tmp/ogginstall
3.1.3 修改配置文件
cd ogg_install/ogg18/Disk1/response
sed -i "s#^INSTALL_OPTION=#INSTALL_OPTION=ORA18c#g" oggcore.rsp
sed -i "s#^SOFTWARE_LOCATION=#SOFTWARE_LOCATION=/u01/app/ogg#g" oggcore.rsp
sed -i "s#^INVENTORY_LOCATION=#INVENTORY_LOCATION=/u01/app/ogg/oraInventory#g" oggcore.rsp
sed -i "s#^UNIX_GROUP_NAME=#UNIX_GROUP_NAME=oinstall#g" oggcore.rsp
主要修改参数为：
[oracle@ds response]$ cat oggcore.rsp |grep -v '^#'|grep -v "^$"
oracle.install.responseFileVersion=/oracle/install/rspfmt_ogginstall_response_schema_v12_1_2
INSTALL_OPTION=ORA18c
SOFTWARE_LOCATION=/u01/app/ogg
START_MANAGER=
MANAGER_PORT=
DATABASE_LOCATION=
INVENTORY_LOCATION=/u01/app/ogg/oraInventory
UNIX_GROUP_NAME=oinstall
3.1.4 开始安装
cd ..
chmod +x ./runInstaller
chmod -R +x install/
mkdir /u01/app/ogg -p
./runInstaller -silent -responseFile /tmp/ogginstall/ogg18/Disk1/response/oggcore.rsp
出现提示“Successfully Setup Software.”时，按一下回车完成OGG的安装。
3.1.5 初始化文件夹
[oracle@ds ogg]$ cd /u01/app/ogg/
[oracle@ds ogg]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 18.1.0.0.0 OGGCORE_18.1.0.0.0_PLATFORMS_180928.0432_FBO
Linux, x64, 64bit (optimized), Oracle 18c on Sep 29 2018 07:21:38
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2018, Oracle and/or its affiliates. All rights reserved.
GGSCI (ds) 1> create subdirs
Creating subdirectories under current directory /u01/app/ogg
Parameter file /u01/app/ogg/dirprm: created.
Report file /u01/app/ogg/dirrpt: created.
Checkpoint file /u01/app/ogg/dirchk: created.
Process status files /u01/app/ogg/dirpcs: created.
SQL script files /u01/app/ogg/dirsql: created.
Database definitions files /u01/app/ogg/dirdef: created.
Extract data files /u01/app/ogg/dirdat: created.
Temporary files /u01/app/ogg/dirtmp: created.
Credential store files /u01/app/ogg/dircrd: created.
Masterkey wallet files /u01/app/ogg/dirwlt: created.
Dump files /u01/app/ogg/dirdmp: created.
3.1.6 创建凭据
GGSCI (ds) 2> create wallet
Created wallet.
Opened wallet.
GGSCI (ds) 3> add credentialstore
Credential store created.
GGSCI (ds) 4> Alter credentialstore ADD USER c##ogg@pri PASSWORD ogg@123 alias pri
Credential store altered.
GGSCI (ds) 5> Alter credentialstore ADD USER c##ogg@adg PASSWORD ogg@123 alias adg
Credential store altered.
GGSCI (ds) 6> Alter credentialstore ADD USER c##ogg@ds PASSWORD ogg@123 alias ds
Credential store altered.
3.1.7 注册抽取进程
GGSCI (ds) 7> dblogin useridalias pri
Successfully logged into database CDB$ROOT.
GGSCI (ds as c##ogg@orcl/CDB$ROOT) 8> MININGDBLOGIN USERIDALIAS ds
Successfully logged into mining database.
GGSCI (ds as c##ogg@orcl/CDB$ROOT) 9> REGISTER EXTRACT extkafka DATABASE container (ORCLPDB)
2022-04-14 00:39:24 INFO OGG-02003 Extract EXTKAFKA successfully registered with database at SCN 4684714.
3.1.8 添加管理进程
GGSCI (ds as c##ogg@orcl/CDB$ROOT) 10> edit param mgr
GGSCI (ds as c##ogg@orcl/CDB$ROOT) 11> view param mgr
port 7809
LAGREPORTHOURS 5
LAGINFOMINUTES 5
LAGCRITICALMINUTES 15
--AUTOSTART extract e*
--AUTIRESTART extract p*
PURGEOLDEXTRACTS ./dirdat/*, usecheckpoints, minkeepdays 14
accessrule,prog *,ipaddr *,allow
3.1.9 添加抽取进程
GGSCI (ds as c##ogg@orcl/CDB$ROOT) 12> edit param extkafka
GGSCI (ds as c##ogg@orcl/CDB$ROOT) 13> view param extkafka
EXTRACT extkafka
nouserid
--USERIDALIAS pri
TRANLOGOPTIONS MININGUSERALIAS ds
TRANLOGOPTIONS INTEGRATEDPARAMS (downstream_real_time_mine, Y)
TRANLOGOPTIONS INTEGRATEDPARAMS (max_sga_size 700)
TRANLOGOPTIONS INTEGRATEDPARAMS (parallelism 3)
--trace /tmp/1.log
Setenv (NLS_LANG="AMERICAN_AMERICA.ZHS16GBK")
DISCARDFILE ./dirrpt/ext_kfk.dsc, APPEND, MEGABYTES 1024
LOGALLSUPCOLS
UPDATERECORDFORMAT FULL
DBOPTIONS ALLOWUNUSEDCOLUMN
REPORTCOUNT EVERY 1 MINUTES, RATE
WARNLONGTRANS 2h,CHECKINTERVAL 300
EXTTRAIL ./dirdat/so
SOURCECATALOG ORCLPDB
table OGG.test1;
GGSCI (ds as c##ogg@orcl/CDB$ROOT) 14> ADD EXTRACT extkafka, INTEGRATED TRANLOG, BEGIN NOW
EXTRACT (Integrated) added.
GGSCI (ds as c##ogg@orcl/CDB$ROOT) 15> ADD EXTTRAIL ./dirdat/so, extract extkafka, megabytes 500
EXTTRAIL added.
3.1.10 添加投递进程
GGSCI (ds as c##ogg@orcl/CDB$ROOT) 16> edit param pumkafka
GGSCI (ds as c##ogg@orcl/CDB$ROOT) 17> view param pumkafka
extract pumkafka
PASSTHRU
RMTHOST 192.168.101.31, MGRPORT 17809
RMTTRAIL ./dirdat/ta,format release 12.2
SOURCECATALOG ORCLPDB
table OGG.*;
GGSCI (ds as c##ogg@orcl/CDB$ROOT) 18> add extract pumkafka,exttrailsource ./dirdat/so
EXTRACT added.
GGSCI (ds as c##ogg@orcl/CDB$ROOT) 19> add rmttrail ./dirdat/ta,EXTRACT pumkafka,MEGABYTES 500
RMTTRAIL added.
3.1.11 查看并启动
GGSCI (ds as c##ogg@orcl/CDB$ROOT) 20> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER STOPPED
EXTRACT STOPPED EXTKAFKA 00:00:00 00:03:21
EXTRACT STOPPED PUMKAFKA 00:00:00 00:00:12
GGSCI (ds as c##ogg@orcl/CDB$ROOT) 21> start mgr
Manager started.
GGSCI (ds as c##ogg@orcl/CDB$ROOT) 22> start extkafka
Sending START request to MANAGER ...
EXTRACT EXTKAFKA starting
GGSCI (ds as c##ogg@orcl/CDB$ROOT) 23> start pumkafka
Sending START request to MANAGER ...
EXTRACT PUMKAFKA starting
GGSCI (ds as c##ogg@orcl/CDB$ROOT) 24> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXTKAFKA 00:03:38 00:00:04
EXTRACT RUNNING PUMKAFKA 00:00:00 00:00:33
3.2 OGG-bigdata安装与设置
使用已经做好的docker镜像（省略）
[root@jhmk01 ~]# docker exec -it oggforbigdata-11g bash
bash-4.2# source /etc/profile
bash-4.2# cd /u01/app/ogg/
bash-4.2# ./ggsci
Oracle GoldenGate for Big Data
Version 12.3.2.1.1 (Build 005)
Oracle GoldenGate Command Interpreter
Version 12.3.0.1.2 OGGCORE_OGGADP.12.3.0.1.2_PLATFORMS_180712.2305
Linux, x64, 64bit (optimized), Generic on Jul 13 2018 00:46:09
Operating system character set identified as US-ASCII.
Copyright (C) 1995, 2018, Oracle and/or its affiliates. All rights reserved.
GGSCI (jhmk01) 1> edit param mgr
GGSCI (jhmk01) 2> view param mgr
PORT 17809
DYNAMICPORTLIST 17830-17840
-- AUTORESTART REPLICAT *, RETRIES 5, WAITMINUTES 3, RESETMINUTES 60
PURGEOLDEXTRACTS ./dirdat/*, usecheckpoints, minkeepdays 1
LAGREPORTHOURS 1
LAGINFOMINUTES 30
LAGCRITICALMINUTES 45
GGSCI (jhmk01) 3> edit param rep1
GGSCI (jhmk01) 4> view param rep1
REPLICAT rep1
SOURCECATALOG ORCLPDB
MAP OGG.*,target ogg.*;
gettruncates
getupdates
setenv(NLS_LANG="AMERICAN_AMERICA.ZHS16GBK")
targetdb libfile libggjava.so set property=./dirprm/kafka.props
GGSCI (jhmk01) 5> ADD REPLICAT rep1 , exttrail ./dirdat/ta
REPLICAT added.
GGSCI (jhmk01) 6> start mgr
Manager started.
GGSCI (jhmk01) 7> start rep1
Sending START request to MANAGER ...
REPLICAT REP1 starting
GGSCI (jhmk01) 8> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING REP1 00:00:00 00:00:01
四、测试
再主库上做一些改动，观察是否能捕获到
GGSCI (ds) 1> stats extkafka
Sending STATS request to EXTRACT EXTKAFKA ...
Start of Statistics at 2022-04-14 02:19:42.
Output to ./dirdat/so:
Extracting from ORCLPDB.OGG.TEST1 to ORCLPDB.OGG.TEST1:
*** Total statistics since 2022-04-14 02:16:27 ***
Total inserts 1.00
Total updates 2.00
Total deletes 1.00
Total discards 0.00
Total operations 4.00
*** Daily statistics since 2022-04-14 02:16:27 ***
Total inserts 1.00
Total updates 2.00
Total deletes 1.00
Total discards 0.00
Total operations 4.00
*** Hourly statistics since 2022-04-14 02:16:27 ***
Total inserts 1.00
Total updates 2.00
Total deletes 1.00
Total discards 0.00
Total operations 4.00
*** Latest statistics since 2022-04-14 02:16:27 ***
Total inserts 1.00
Total updates 2.00
Total deletes 1.00
Total discards 0.00
Total operations 4.00
End of Statistics.
GGSCI (ds) 2> stats pumkafka
Sending STATS request to EXTRACT PUMKAFKA ...
Start of Statistics at 2022-04-14 02:19:47.
Output to ./dirdat/ta:
Extracting from ORCLPDB.OGG.TEST1 to ORCLPDB.OGG.TEST1:
*** Total statistics since 2022-04-14 02:16:27 ***
Total inserts 1.00
Total updates 2.00
Total deletes 1.00
Total discards 0.00
Total operations 4.00
*** Daily statistics since 2022-04-14 02:16:27 ***
Total inserts 1.00
Total updates 2.00
Total deletes 1.00
Total discards 0.00
Total operations 4.00
*** Hourly statistics since 2022-04-14 02:16:27 ***
Total inserts 1.00
Total updates 2.00
Total deletes 1.00
Total discards 0.00
Total operations 4.00
*** Latest statistics since 2022-04-14 02:16:27 ***
Total inserts 1.00
Total updates 2.00
Total deletes 1.00
Total discards 0.00
Total operations 4.00
End of Statistics.
GGSCI (jhmk01) 4> stats rep1
Sending STATS request to REPLICAT REP1 ...
Start of Statistics at 2022-04-13 18:18:46.
Replicating from ORCLPDB.OGG.TEST1 to ogg.TEST1:
*** Total statistics since 2022-04-13 18:15:23 ***
Total inserts 1.00
Total updates 2.00
Total deletes 1.00
Total discards 0.00
Total operations 4.00
*** Daily statistics since 2022-04-13 18:15:23 ***
Total inserts 1.00
Total updates 2.00
Total deletes 1.00
Total discards 0.00
Total operations 4.00
*** Hourly statistics since 2022-04-13 18:15:23 ***
Total inserts 1.00
Total updates 2.00
Total deletes 1.00
Total discards 0.00
Total operations 4.00
*** Latest statistics since 2022-04-13 18:15:23 ***
Total inserts 1.00
Total updates 2.00
Total deletes 1.00
Total discards 0.00
Total operations 4.00
End of Statistics.
1387
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
