目錄
- 19c單實體配置GoldenGate 并進行用戶資料同步測驗
- 一、資料庫操作
- 1、開啟資料庫附加日志
- 2、開啟資料庫歸檔模式
- 3、開啟goldengate同步
- 4、創建goldengate管理用戶
- 5、集成捕獲所需權限授權
- 6、創建測驗用戶及測驗表
- 二、配置GOLDENGATE
- 1、配置MGR
- 2、重啟MGR
- 3、配置GLOBALS
- 4、創建checkpoint表
- 5、添加表級別的附加日志
- 6、查詢資料庫字符集
- 7、添加抽取行程
- 8、添加復制行程
- 9、啟動抽取行程與復制行程
- 三、進行資料同步測驗：
- 1、資料插入測驗
- （1）向SHUAIGE.T1表中插入一條資料
- （2）檢查行程同步狀態
- （3）驗證資料是否同步
- 2、資料更新測驗
- （1）在SHUAIGE.T1表中更新一條資料
- （2）檢查行程同步狀態
- （3）驗證資料是否同步
- 3、資料洗掉測驗
- （1）在SHUAIGE.T1表中洗掉一條資料
- （2）檢查行程同步狀態
- （3）驗證資料是否同步
- 1、資料插入測驗
- 一、資料庫操作
19c單實體配置GoldenGate 并進行用戶資料同步測驗
當前資料使用的是經典模式配置GoldenGate 進行資料同步，關于容器資料庫配置GoldenGate 請參閱：
Configuring Oracle GoldenGate in a Multitenant Container Database (Doc ID 2031069.1)
一、資料庫操作
1、開啟資料庫附加日志
開啟的最小附加日志和強制日志
SQL> alter database add supplemental log data;
SQL> select supplemental_log_data_min from v$database;
SQL> alter database force logging;
SQL> select force_logging from v$database;
2、開啟資料庫歸檔模式
RAC所有節點
SQL> shutdown immediate
實體1：
SQL> startup mount
SQL> alter database archivelog;
SQL> archive log list;
SQL> alter database open;
實體2 ：
直接執行
SQL> startup
如果需要修改歸檔日志路徑：
SQL> alter system set log_archive_dest_1= 'location=<歸檔路徑>' sid='*';
3、開啟goldengate同步
SQL> show parameter gol
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
enable_goldengate_replication boolean FALSE
SQL> alter system set ENABLE_GOLDENGATE_REPLICATION=true;
System altered.
SQL> show parameter gol
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
enable_goldengate_replication boolean TRUE
4、創建goldengate管理用戶
--1、create tablespace
create tablespace gguser_dat datafile '/u01/app/oracle/oradata/ORCL/gguser_dat_01.dbf' size 100m autoextend on next 100m maxsize unlimited;
--2、create the user
create user gguser IDENTIFIED BY gguser123
default tablespace gguser_dat
temporary tablespace TEMP
profile DEFAULT;
--3、Grant role privileges
grant connect to GGUSER;
grant resource to GGUSER;
--4、Grant system privileges
grant alter any index to GGUSER;
grant alter any table to GGUSER;
grant alter session to GGUSER;
grant alter system to GGUSER;
grant create any directory to GGUSER;
grant create any index to GGUSER;
grant create any table to GGUSER;
grant create session to GGUSER;
grant delete any table to GGUSER;
grant drop any directory to GGUSER;
grant drop any index to GGUSER;
grant drop any table to GGUSER;
grant flashback any table to GGUSER;
grant insert any table to GGUSER;
grant select any dictionary to GGUSER;
grant select any table to GGUSER;
grant select any transaction to GGUSER;
grant unlimited tablespace to GGUSER;
grant update any table to GGUSER;
5、集成捕獲所需權限授權
授予gguser用戶集成捕獲所需的權限
SQL> exec dbms_goldengate_auth.grant_admin_privilege('GGUSER',container=>'ALL');
請參閱：Integrated Capture Process Cannot Start with OGG-02061 at Multitenant Configuration (Doc ID 2748951.1)
6、創建測驗用戶及測驗表
--1、創建測驗用戶表空間（SHUAIGE_DAT）
create tablespace shuaige_dat datafile '/u01/app/oracle/oradata/ORCL/shuaige_dat_01.dbf' size 100M autoextend on next 100m maxsize unlimited;
--2、創建測驗用戶（SHUAIGE），密碼（shuaige123）
create user SHUAIGE IDENTIFIED BY shuaige123
default tablespace shuaige_dat
temporary tablespace TEMP
quota unlimited ON shuaige_dat
profile DEFAULT;
--3、授予用戶（SHUAIGE）用戶資料庫角色
grant connect to SHUAIGE;
grant resource to SHUAIGE;
--4、創建測驗表
--測驗表（T1）
CREATE TABLE SHUAIGE.T1 (
ID number(10),
NAME varchar2(30))
tablespace shuaige_dat;
--測驗表（T2）
CREATE TABLE SHUAIGE.T2 (
ID number(10),
NAME varchar2(30))
tablespace shuaige_dat;
--5、給測驗表創建主鍵約束
--創建表主鍵(T1)，主鍵名（T1_PK），主鍵列（ID）
alter table SHUAIGE.T1 add constraint T1_PK primary key(id)
using index
tablespace shuaige_dat;
--創建表主鍵(T2)，主鍵名（T2_PK），主鍵列（ID）
alter table SHUAIGE.T2 add constraint T2_PK primary key(id)
using index
tablespace shuaige_dat;
測驗目標即(SHUAIGE.T1)表資料使用Goldengate同步到(SHUAIGE.T2)表
二、配置GOLDENGATE
1、配置MGR
GGSCI > edit params mgr
添加以下內容：
PORT 7809
DYNAMICPORTLIST 7810-7899
USERID gguser, PASSWORD AACAAAAAAAAAAAJAACQGHDZHDDGFMEACUCUEPFYGGJBFJGIH BLOWFISH, ENCRYPTKEY DEFAULT
AUTORESTART ER *, RETRIES 5, WAITMINUTES 1, RESETMINUTES 10
PURGEOLDEXTRACTS ./dirdat/*, USECHECKPOINTS, MINKEEPDAYS 1
LAGINFOMINUTES 10
LAGCRITICALMINUTES 60
使用goldengate默認生成的key的加密方法：encrypt password <要進行加密的密碼>,ENCRYPTKEY default Using default key
示例：（用戶：gguser，密碼：gguser123），對該密碼進行加密
GGSCI 27> encrypt password gguser123,ENCRYPTKEY default Using default key Using Blowfish encryption with DEFAULT key. Encrypted password: AACAAAAAAAAAAAJAACQGHDZHDDGFMEACUCUEPFYGGJBFJGIH Algorithm used: BLOWFISH為了安全起見最好在goldengate組態檔中使用加密密碼進行配置，當然明文密碼也是可用的
2、重啟MGR
GGSCI > stop mgr
Manager process is required by other GGS processes.
Are you sure you want to stop it (y/n)?y
Sending STOP request to MANAGER ...
Request processed.
Manager stopped.
GGSCI > start mgr
Manager started.
3、配置GLOBALS
GGSCI > edit params ./GLOBALS
添加以下內容：
CHECKPOINTTABLE gguser.checkpoint
OUTPUTFILEUMASK 027
4、創建checkpoint表
GGSCI > DBLOGIN USERID gguser, PASSWORD AACAAAAAAAAAAAJAACQGHDZHDDGFMEACUCUEPFYGGJBFJGIH BLOWFISH, ENCRYPTKEY DEFAULT
Successfully logged into database.
GGSCI > ADD CHECKPOINTTABLE gguser.checkpoint
Successfully created checkpoint table gguser.checkpoint.
或使用明文密碼登錄資料庫：dblogin userid
, password ，示例如下： GGSCI > dblogin userid gguser, password gguser123 Successfully logged into database.
5、添加表級別的附加日志
ggsci命令列中登錄資料庫
GGSCI > DBLOGIN USERID gguser, PASSWORD AACAAAAAAAAAAAJAACQGHDZHDDGFMEACUCUEPFYGGJBFJGIH BLOWFISH, ENCRYPTKEY DEFAULT
Successfully logged into database.
-
若資料全部同步沒有條件、沒有主鍵表、添加表級別附加日志 GGSCI > add trandata 用戶名.表名 -
若資料同步有條件過濾并且表有主鍵，添加表級別附加日志 GGSCI > add trandata 用戶名.表名,clos(列名)
info trandata 用戶.表名查詢trandata是否添加成功（enable），未添加trandata為（disable）狀態
按用戶生成添加、查詢、洗掉表級別附加日志sql，示例：
#在資料庫中執行：
select 'add trandata '||owner||'.'||table_name from dba_tables where owner = 'SHUAIGE';
select 'info trandata '||owner||'.'||table_name from dba_tables where owner = 'SHUAIGE';
select 'delete trandata '||owner||'.'||table_name from dba_tables where owner = 'SHUAIGE';
#在GGSCI中執行：
add trandata SHUAIGE.T1
add trandata SHUAIGE.T2
info trandata SHUAIGE.T1
info trandata SHUAIGE.T2
添加成功輸出如下：
GGSCI > add trandata SHUAIGE.T1
2022-05-10 10:41:10 INFO OGG-15132 Logging of supplemental redo data enabled for table SHUAIGE.T1.
2022-05-10 10:41:10 INFO OGG-15133 TRANDATA for scheduling columns has been added on table SHUAIGE.T1.
2022-05-10 10:41:10 INFO OGG-15135 TRANDATA for instantiation CSN has been added on table SHUAIGE.T1.
2022-05-10 10:41:11 INFO OGG-10471 ***** Oracle Goldengate support information on table SHUAIGE.T1 *****
Oracle Goldengate support native capture on table SHUAIGE.T1.
Oracle Goldengate marked following column as key columns on table SHUAIGE.T1: ID.
GGSCI > add trandata SHUAIGE.T2
2022-05-10 10:41:11 INFO OGG-15132 Logging of supplemental redo data enabled for table SHUAIGE.T2.
2022-05-10 10:41:11 INFO OGG-15133 TRANDATA for scheduling columns has been added on table SHUAIGE.T2.
2022-05-10 10:41:11 INFO OGG-15135 TRANDATA for instantiation CSN has been added on table SHUAIGE.T2.
2022-05-10 10:41:11 INFO OGG-10471 ***** Oracle Goldengate support information on table SHUAIGE.T2 *****
Oracle Goldengate support native capture on table SHUAIGE.T2.
Oracle Goldengate marked following column as key columns on table SHUAIGE.T2: ID.
校驗trandata輸出如下：
GGSCI > info trandata SHUAIGE.T1
2022-05-10 10:43:26 INFO OGG-10471 ***** Oracle Goldengate support information on table SHUAIGE.T1 *****
Oracle Goldengate support native capture on table SHUAIGE.T1.
Oracle Goldengate marked following column as key columns on table SHUAIGE.T1: ID.
Logging of supplemental redo log data is enabled for table SHUAIGE.T1.
Columns supplementally logged for table SHUAIGE.T1: "ID".
Prepared CSN for table SHUAIGE.T1: 2175964
GGSCI > info trandata SHUAIGE.T2
2022-05-10 10:43:33 INFO OGG-10471 ***** Oracle Goldengate support information on table SHUAIGE.T2 *****
Oracle Goldengate support native capture on table SHUAIGE.T2.
Oracle Goldengate marked following column as key columns on table SHUAIGE.T2: ID.
Logging of supplemental redo log data is enabled for table SHUAIGE.T2.
Columns supplementally logged for table SHUAIGE.T2: "ID".
Prepared CSN for table SHUAIGE.T2: 2176044
GGSCI (ocp19c as gguser@orcl) 14>
6、查詢資料庫字符集
當前資料庫session的環境字符集：
SQL> select userenv('language') from dual;
AMERICAN_AMERICA.AL32UTF8
7、添加抽取行程
GGSCI > add extract ef_test1,TRANLOG,BEGIN NOW
GGSCI > add exttrail ./dirdat/cs,extract ef_test1
GGSCI > edit params ef_test1
（./dirdat/cs）表示extract行程生成exttrail檔案的位置和檔案名開頭為cs，示例檔案：
-rw-r----- 1 ggate ggate 1823 May 10 11:24 cs000000000goldengate所有的行程(extract、replicat)的名字都不能超過八個字符，在添加行程和設定組態檔的時候最好起一些有含義的名字
這個示例（ef_test1）ef代表捕獲行程也叫抽取行程，test1就代表第一個測驗
添加以下內容：
EXTRACT ef_test1
SETENV (ORACLE_SID="orcl")
SETENV(NLS_LANG="AMERICAN_AMERICA.AL32UTF8")
USERID gguser, PASSWORD AACAAAAAAAAAAAJAACQGHDZHDDGFMEACUCUEPFYGGJBFJGIH BLOWFISH, ENCRYPTKEY DEFAULT
NUMFILES 3000
EOFDELAYCSECS 10
EXTTRAIL ./dirdat/cs
TRANLOGOPTIONS LOGRETENTION DISABLED
TRANLOGOPTIONS DBLOGREADER
TRANLOGOPTIONS DBLOGREADERBUFSIZE 1048576
TRANLOGOPTIONS _NOREADAHEAD ANY
WARNLONGTRANS 4H, CHECKINTERVAL 300
GETTRUNCATES
GETUPDATEBEFORES
NOCOMPRESSDELETES
CACHEMGR, CACHESIZE 128MB
-- TABLES
TABLE shuaige.t1;
注意：行程中所有的引數都可以按照實際的生產或測驗需求添加，以上引數是我比較常用的
我發現在19c 配置goldengate與11g配置goldengate不同的地方和個別引數限制，如下：
- 不能在添加抽取行程時指定執行緒（thread）引數（單實體）
- value "3 M" is out of legal range (300 s - 20000000 s) for [checkinterval].
- CACHESIZE. Range values between: min 128 - max 2097152 MB.
在GGSCI命令列輸入help查詢更多幫助資訊
8、添加復制行程
GGSCI > add replicat rf_test1,exttrail ./dirdat/cs, checkpointtable gguser.checkpoint
GGSCI > edit params rf_test1
這里的（./dirdat/cs）表示要指定replicat行程讀取的exttrail檔案
在（3、配置GLOBALS）中已經配置了全域GLOBALS，添加復制行程時會自動添加到gguser.checkpoint，若添加時手動指定checkpoint表，則以手動指定的為準
添加以下內容：
REPLICAT rf_test1
SETENV(NLS_LANG="AMERICAN_AMERICA.AL32UTF8")
USERID gguser, PASSWORD AACAAAAAAAAAAAJAACQGHDZHDDGFMEACUCUEPFYGGJBFJGIH BLOWFISH, ENCRYPTKEY DEFAULT
SQLEXEC "ALTER SESSION SET COMMIT_LOGGING=IMMEDIATE"
SQLEXEC "ALTER SESSION SET COMMIT_WAIT=NOWAIT"
NUMFILES 3000
EOFDELAYCSECS 10
DISCARDFILE ./dirdsc/rf_test1.dsc, APPEND, MEGABYTES 512
DISCARDROLLOVER AT 00:00
ASSUMETARGETDEFS
ALLOWNOOPUPDATES
GETTRUNCATES
GETUPDATEBEFORES
--TABLES
MAP shuaige.t1, TARGET shuaige.t2;
注意：行程中所有的引數都可以按照實際的生產或測驗需求添加，以上引數是我比較常用的
這個示例（rf_test1）rf代表復制行程也叫應用行程，所有rf_test1就代表第一個測驗的復制行程
使用ggate用戶手動創建dirdsc目錄，因為我在這個復制行程中配置了DISCARDFILE引數，會在指定目錄下生成.dsc檔案
mkdir -p /ggate/dirdsc
9、啟動抽取行程與復制行程
GGSCI > start ef_test1
GGSCI > start rf_test1
查看行程狀態
GGSCI > info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EF_TEST1 00:00:00 00:00:06
REPLICAT RUNNING RF_TEST1 00:00:00 00:00:07
三、進行資料同步測驗：
1、資料插入測驗
（1）向SHUAIGE.T1表中插入一條資料
SYS@orcl> insert into shuaige.T1 values(1,'李清照');
1 row created.
SYS@orcl> commit;
Commit complete.
（2）檢查行程同步狀態
GGSCI > stats ef_test1,total
Sending STATS request to EXTRACT EF_TEST1 ...
Start of Statistics at 2022-05-10 11:25:49.
Output to ./dirdat/cs:
Extracting from SHUAIGE.T1 to SHUAIGE.T1:
*** Total statistics since 2022-05-10 11:24:10 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total upserts 0.00
Total discards 0.00
Total operations 1.00
End of Statistics.
GGSCI > stats rf_test1,total
Sending STATS request to REPLICAT RF_TEST1 ...
Start of Statistics at 2022-05-10 11:26:41.
Replicating from SHUAIGE.T1 to SHUAIGE.T2:
*** Total statistics since 2022-05-10 11:24:11 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total upserts 0.00
Total discards 0.00
Total operations 1.00
End of Statistics.
可以看到插入的資料已經成功的捕獲和應用了
常用檢查行程狀態命令stats，EXTRACT與REPLICAT行程都適用
GGSCI > stats EXTRACT,total --查看當前行程所有的操作狀態
GGSCI > stats EXTRACT,hourly --查看當前小時整點到當前時間所有的操作狀態
GGSCI > stats EXTRACT,daily --查看今日凌晨0點到當前時間所有的操作狀態
詳情查看（help stats EXTRACT）（help stats REPLICAT）：
（3）驗證資料是否同步
SYS@orcl> select * from shuaige.t1;
ID NAME
---------- ------------------------------
1 李清照
SYS@orcl> select * from shuaige.t2;
ID NAME
---------- ------------------------------
1 李清照
2、資料更新測驗
（1）在SHUAIGE.T1表中更新一條資料
SYS@orcl> update shuaige.t1 set name='辛棄疾' where id = '1';
1 row updated.
SYS@orcl> commit;
Commit complete.
（2）檢查行程同步狀態
GGSCI (ocp19c) 17> stats ef_test1,total
Sending STATS request to EXTRACT EF_TEST1 ...
Start of Statistics at 2022-05-10 15:49:43.
Output to ./dirdat/cs:
Extracting from SHUAIGE.T1 to SHUAIGE.T1:
*** Total statistics since 2022-05-10 11:24:10 ***
Total inserts 1.00
Total updates 1.00
Total befores 1.00
Total deletes 0.00
Total upserts 0.00
Total discards 0.00
Total operations 2.00
End of Statistics.
GGSCI (ocp19c) 18> stats rf_test1,total
Sending STATS request to REPLICAT RF_TEST1 ...
Start of Statistics at 2022-05-10 15:50:07.
Replicating from SHUAIGE.T1 to SHUAIGE.T2:
*** Total statistics since 2022-05-10 11:24:11 ***
Total inserts 1.00
Total updates 1.00
Total befores 1.00
Total deletes 0.00
Total upserts 0.00
Total discards 0.00
Total operations 2.00
End of Statistics.
可以看到更新的資料已經成功的捕獲和應用了
（3）驗證資料是否同步
SYS@orcl> select * from shuaige.t1;
ID NAME
---------- ------------------------------
1 辛棄疾
SYS@orcl> select * from shuaige.t2;
ID NAME
---------- ------------------------------
1 辛棄疾
3、資料洗掉測驗
（1）在SHUAIGE.T1表中洗掉一條資料
SYS@orcl> delete from shuaige.t1 where id = 1;
1 row deleted.
SYS@orcl> commit;
Commit complete.
（2）檢查行程同步狀態
GGSCI (ocp19c) 19> stats ef_test1,total
Sending STATS request to EXTRACT EF_TEST1 ...
Start of Statistics at 2022-05-10 15:52:48.
Output to ./dirdat/cs:
Extracting from SHUAIGE.T1 to SHUAIGE.T1:
*** Total statistics since 2022-05-10 11:24:10 ***
Total inserts 1.00
Total updates 1.00
Total befores 1.00
Total deletes 1.00
Total upserts 0.00
Total discards 0.00
Total operations 3.00
End of Statistics.
GGSCI (ocp19c) 20> stats rf_test1,total
Sending STATS request to REPLICAT RF_TEST1 ...
Start of Statistics at 2022-05-10 15:52:56.
Replicating from SHUAIGE.T1 to SHUAIGE.T2:
*** Total statistics since 2022-05-10 11:24:11 ***
Total inserts 1.00
Total updates 1.00
Total befores 1.00
Total deletes 1.00
Total upserts 0.00
Total discards 0.00
Total operations 3.00
End of Statistics.
可以看到洗掉的資料已經成功的捕獲和應用了
（3）驗證資料是否同步
SYS@orcl> select * from shuaige.t1;
no rows selected
SYS@orcl> select * from shuaige.t2;
no rows selected
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/472325.html
標籤：其他
上一篇：MySQL 筆記
下一篇：一文讀懂資料庫發展史