5
Oracle19c单实例数据库配置OGG单用户数据同步测试 - 君子不怨天
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.
-
若数据全部同步没有条件、没有主键表、添加表级别附加日志 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
Recommend
-
51
Emergency模式（centos6叫单用户模式）：当忘记了root用户密码的时候，可以进入此模式进行修改root密码操作步骤：重启CentOS系统：init 6在开机界面时，按【↓】方向键，目的是为了停留在开机界面；选择第一项，同时按下e键，进入GRUB界面在GRUB界面，移动光标，找...
-
31
本文主要介绍如何使用阿里云Kubernetes，并结合存储卷功能完成对数据的持久化存储，以MySQL-5.7单实例为例讲解
-
49
作者：田逸（[email protected]）基本条件准备 准备虚拟机，基本配置为：16G内存、4core cpu、4个磁盘（1个系统盘、一个/tmp分区、一个数据分区、一个交换分区swap）。 准备操作系统，并使磁盘可用。 建议正式环境为用户数据存储提供独立的磁盘或者分区。 从oracle官...
-
8
【优质信源】计划09 — 浅谈无线局域网下的单用户MAC层接入协议（基于CSMA/CA框架）Wi-Fi话题下的优秀回答者我们先大体总结下目前802.11协议这一块的科研现状，也就是Wi-Fi科研这一...
-
11
Linux的单用户模式对于一个系统管理员来说，是必需要掌握的系统恢复模式。比如系统引导文件出错，忘记管理员Root密码，进行文档修复都是很必要的修复模式。对于不同的开机引导模式lilo或者Grub也好，进入单用户模式的方法大同小异。 ◆1. 若是使...
-
7
TcaplusDB API 的单实例内存和 CPU 消耗？
-
16
ZKSwap发布以太坊首个Layer2 NFT协议，单用户可免费创建65536个NFT • 2021-05-21 ...
-
5
一，问题背景 很多时候，我们需要确保进程只有一个实例运行。 有几种方法： http://stackoverflow.com/questi...
-
6
作者：@pdai本文为作者原创，转载请注明出处：https://www.cnblogs.com/pengdai/p/16538853.htm...
-
4
一、环境准备 1.1 软件下载 下载地址：https://www.oracle.com/database/technologies/oracle19c-lin...
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK