由于篇幅問題，這里拆開來寫，上篇?OGG 基礎知識可點此查看，本篇正式介紹 OGG 部署以及數據遷移和同步。
核心思想：利用數據泵導入導出功能初始化數據，然后通過?OGG 同步增量數據。
源??端：192.168.217.86 ?數據庫為Oracle 11204 版本，端口1521 服務名：test
目標端：192.168.217.87 數據庫為Oracle 19.3 版本，端口1521 服務名：testogg
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.log dumpfile=expdp_prod-2021-07-14_%U.dmp SCHEMAS=prod COMPRESSION=ALL PARALLEL=4 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
但是，官方網站下架了這個安裝包，現在已經下載不到了，我這里提前下載了，有需要的可以點擊下載：
http://m.sunline.cc/doc/90345
官方指導手冊：
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
使用如下命令靜默安裝：
/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>
安裝成功！！！
目標端 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_replication
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
enable_goldengate_replication boolean FALSE
alter 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.00
SQL> show parameter enable_goldengate_replication
NAME 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 配置 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 觸發器和支持的數據庫對象即 跑腳本 marker_setup.sql 和 ddl_setup.sql 及 ddl_enable.sql 等，然后才能為 DDL 支持配置 Extract。
支持經典捕獲模式下的?DDL 捕獲
經典捕獲模式需要使用?Oracle GoldenGate DDL 觸發器從 Oracle 數據庫捕獲 DDL。經典捕獲模式不支持原生 DDL 捕獲。
經典捕獲模式不支持從多租戶容器數據庫捕獲?DDL。
當您使用經典捕獲模式并?CREATE USER?使用?DDL 觸發器復制時，觸發器所有者和 Extract 登錄用戶必須匹配以避免嘗試復制?CREATE USER?命令時出現權限錯誤。
要使用基于觸發器的?DDL 捕獲，您必須在為 DDL 支持配置 Extract 之前安裝 DDL 觸發器和支持的數據庫對象。
3.4.1早期版本 11204 之前
根據上一節說明 compatible 參數為 11.2.0.4 之前的版本還需要跑 SQL 腳本配置 DDL，19c 則已經不需要了。我們源端環境是 11.2.0.4 但 compatible 參數小于 11.2.0.4，故需要安裝 SQL 腳本支持 DDL。
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.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.* --注意不要加分號
ADD TRANDATA scott.*
ADD TRANDATA EASYC_READ.*
--查看附加日志是否添加成功
info TRANDATA scott.*
info TRANDATA PROD.*
info TRANDATA EASYC_READ.*
3.7 配置 MGR
create subdirs 創建?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 192.*.*.*, 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
3.10.配置支持序列
1）在配置支持 DDL 的前提下，需要對 OGG 給予 DBA 權限。
CREATE USER OGG IDENTIFIED BY password;
GRANT CONNECT, RESOURCE, DBA TO OGG;
2）然后 ggsci登錄 OGG
EDIT PARAMS ./GLOBALS
在文件中，輸入下列參數并指定您之前在此過程中創建的 DDL 用戶。
GGSCHEMA ogg
3）運行 sequence.sql
在源和目標兩個系統上的 SQL*Plus 中，從 Oracle GoldenGate 安裝目錄的根目錄運行腳本 sequence.sql。這個腳本創建了一些供 Oracle GoldenGate 進程使用的過程。（不要自己運行它們，系統將提示您輸入用戶信息，您在第一步中創建的用戶，我這里是 OGG）
SQL> @sequence.sql
Elapsed: 00:00:00.07
Please enter the name of a schema for the GoldenGate database objects:
OGG
Setting schema name to OGG
Elapsed: 00:00:00.07
Elapsed: 00:00:00.84
Elapsed: 00:00:00.08
Elapsed: 00:00:00.26
Elapsed: 00:00:00.09
UPDATE_SEQUENCE STATUS:
Line/pos Error
---------------------------------------- -----------------------------------------------------------------
No errors No errors
Elapsed: 00:00:00.11
GETSEQFLUSH
Line/pos Error
---------------------------------------- -----------------------------------------------------------------
No errors No errors
Elapsed: 00:00:00.04
SEQTRACE
Line/pos Error
---------------------------------------- -----------------------------------------------------------------
No errors No errors
Elapsed: 00:00:00.03
REPLICATE_SEQUENCE STATUS:
Line/pos Error
---------------------------------------- -----------------------------------------------------------------
No errors No errors
Elapsed: 00:00:00.04
STATUS OF SEQUENCE SUPPORT
--------------------------------------------------------------
SUCCESSFUL installation of Oracle Sequence Replication support
4）在源系統上的 SQL*Plus中，將該過程的權限授予可用于發出命令的數據庫用戶。記住或記錄這個用戶。在發出調用該過程的命令之前登錄到數據庫。
eg:GRANT EXECUTE on DDLuser.updateSequence TO DBLOGINuser;
GRANT EXECUTE on ogg.updateSequence TO ogg;
5）在目標系統上的 SQL*Plus 中，將該過程的權限授予 Replicat 數據庫用戶
eg:GRANT EXECUTE on DDLuser.replicateSequence TO Replicatuser;
GRANT EXECUTE on ogg.replicateSequence TO ogg;
6）在源系統的SQLPlus中，在SQLPlus中發出以下語句
ALTER TABLE sys.seq$ ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
注意：如果是多租戶容器環境，以上需要切到 PDB下執行，另外執行完 sequence.sql，還需要在 GGSCI 中 FLUSH SEQUENCE。
sqlplus / as sysdba
SQL> alter session set container=CERTMISSN;
SQL> @sequence
Flush Sequence
GGSCI> DBLOGIN USERIDALIAS GGADMIN DOMAIN GOLD_QC_CDB$ROOT
GGSCI> FLUSH SEQUENCE CERTMISSNPDB.SRCSCHEMA1. ##----PDB.schema
官方文檔參考鏈接：https://docs.oracle.com/en/middleware/goldengate/core/19.1/oracle-db/additional-oracle-goldengate-configuration-considerations.html#GUID-2FBECF4E-1D8D-42A7-B4B1-A1FDE56039D8
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 45
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 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$transaction
UNION ALL
SELECT 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
----------
2
3
1
Elapsed: 00:00:00.00
SQL> insert into test values(4);
1 row created.
Elapsed: 00:00:00.00
SQL>
SQL> commit;
Commit complete.
--目標端查看
SQL> select * from scott.test;
ID NAME
---------- ----------
1
2
3
4
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))
6.3.序列測試
源端創建表、序列、插入數據，檢查目標端是否正常同步。
CREATE TABLE "SCOTT"."TEST"
("ID" NUMBER,
"NAME" VARCHAR2(32),
"ADDR" VARCHAR2(50));
CREATE SEQUENCE "SCOTT"."SEQ_TEST" MINVALUE 1 MAXVALUE 99999999999 INCREMENT BY 1 START WITH 1 NOCACHE ORDER NOCYCLE;
INSERT INTO SCOTT.TEST VALUES(1,'JiekeXu','Beijing');
INSERT INTO SCOTT.TEST VALUES("SCOTT"."SEQ_TEST".nextval,'Jieke','Tianjing');
INSERT INTO SCOTT.TEST VALUES("SCOTT"."SEQ_TEST".nextval,'Jie','GSAU');
commit;
或者檢查原有序列當前值，下一個值是否和目標端一致，如果一致說明正常同步，如所有表都不一致，則需要單獨處理序列，一般情況下原端先導出序列，然后目標端刪除序列，再導入序列，然后進行業務數據比對測試等后續操作。
（未完待續）
以上資料來源于互聯網和 Oracle?GoldenGate?官方網站，由于本人技術能力有限，如有錯誤或不當之處，敬請諒解，也可添加我個人微信【JiekeXu_DBA】一起交流探討。
?? 歡迎關注我的公眾號，一起學習新知識！
————————————————————————————
公眾號：JiekeXu DBA之路
墨天輪：http://m.sunline.cc/u/4347
CSDN ：https://blog.csdn.net/JiekeXu
騰訊云：https://cloud.tencent.com/developer/user/5645107
————————————————————————————
