2

Oracle19c单实例数据库配置OGG单用户数据同步测试 - 君子不怨天

 1 year ago
source link: https://www.cnblogs.com/junzibuyuantian/p/16254980.html
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.

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.

  1. 若数据全部同步没有条件、没有主键表、添加表级别附加日志
GGSCI > add trandata 用户名.表名

  2. 若数据同步有条件过滤并且表有主键，添加表级别附加日志
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 cs000000000

goldengate所有的进程(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不同的地方和个别参数限制，如下：

  1. 不能在添加抽取进程时指定线程（thread）参数（单实例）
  2. value "3 M" is out of legal range (300 s - 20000000 s) for [checkinterval].
  3. 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

report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK