【概要】
由于公众号单篇文章的限制,上一篇文章只讲OGG Microservices for oracle数据库安装与配置,对于如何通过WEB端配置数据同步、如何通过命令行查看与管理服务、进程以及简单故障处理都没有讲解,所以本篇主要讲解如何通过WEB端来配置数据同步(与GGSCI命令行创建与管理抽取、传输以及应用进程类似)以及如何通过adminclient来直接查看与管理服务以及进程。
本文主要介绍OGG 19微服务架构安装、配置以及简单数据同步案例演示.
OS Version | Oracle Linux 7.5 X86-64 |
---|---|
DB Version | Oracle 11.2.0.4 X86-64 |
RAC & ASM | 2个节点的RAC,采用ASM作为数据库存储空间 |
通过学习本篇文章,你将快速掌握如下内容:
备注:如你对于安装与配置微服务还不熟悉,请通过上一篇文章来学习如何配置.
【检查与修改数据库配置FOR OGG】
1.开启数据库归档--如果没有开启
2.开启数据库级别附加日志--如果没有开始最小附加日志
3.开启强制日志--如果没有开启强制日志
4.设置ENABLE_GOLDENGATE_REPLICAT参数为TRUE
5.创建OGG用户包括包括源端用户、目标端用户以及OGG抽取用户
备注:1,2,3项目都不满足,都需要调整.
select name,supplemental_log_data_min , force_logging, log_mode from v$database;
NAME SUPPLEME FOR LOG_MODE
--------- -------- --- ------------
XIAOXU NO NO NOARCHIVELOG
shutdown immediate(单实例) or srvctl stop database -d xiaoxu(rac建议)
startup mount
alter system set log_archive_dest_1='location=+data';
alter database archivelog;
alter database add supplemental log data;
alter database force logging;
alter system set enable_goldengate_replication=TRUE;
alter database open;
select name,supplemental_log_data_min , force_logging, log_mode from v$database;
NAME SUPPLEME FOR LOG_MODE
--------- -------- --- ------------
XIAOXU YES YES ARCHIVELOG
show parameter goldengate
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
enable_goldengate_replication boolean TRUE
CREATE USER ggadmin identified by ggadmin;
GRANT DBA to ggadmin;
Create user source identified by source;
Grant connect, resource, create session, alter session to source;
Grant unlimited tablespace to source;
Create user target identified by target;
Grant connect, resource, create session, alter session to target;
Grant unlimited tablespace to target;
【总结】至此完成oracle database配置以及用户创建,为后续OGG同步数据做准备.
【配置OGG的经典抽取(classic extract)进程】
⏰如果采用classic extract同步ddl,需要通过OGG_HOME的bin路径下adminclient来配置,WEB暂无接口,如果需要DDL同步建议采用集成模式
备注:1-11步通过web完成extract创建以及DML配置,12步通过adminclient来配置DDL
备注:如果采用日志在asm上,增加tranlogoptions dblogreader参数
--备注:12.3版本名字是:Trandata,19版本变成Transaction information
【总结】至此完成oracle goldengate Microservices的classic extract的DML和DDL配置.
【配置OGG的传输进程】
备注:微服务版本中传输进程用Distribution Server替换经典架构中PUMP进程
【总结】至此完成oracle goldengate Microservices的Distribution Server配置。
【配置OGG的经典应用进程】
⏰配置应用进程之前先配置checkpointtable表存放应用进程的消息,包括seqno、rba、time以及事务信息 .
【总结】至此完成oracle goldengate Microservices的经典同步数据过程,包括extract、Distribution Server、replicat配置。
【数据同步测试】
SQL> create table source.xiaoxu_new(id int not null primary key,name varchar2(50),address varchar2(100));
Table created.
SQL> conn source
Enter password:
Connected.
SQL> insert into xiaoxu_new values(1,'xiaoxu','shanghai');
1 row created.
SQL> insert into xiaoxu_new values(2,'xiaoxing','shanghai');
1 row created.
SQL> insert into xiaoxu_new values(3,'xiaoyaba','shanghai');
1 row created.
SQL> commit;
Commit complete.
【总结】至此完成oracle goldengate Microservices的DDL+DML数据同步过程。
【通过adminclient命令行来管理】
⏰经典架构下通过ggsci命令下使用dblogin登陆数据库以及各种管理操作,微服务架构在adminclient命令行先连接到Service Manager,然后才能使用dblogin访问数据库和各种管理操作.
⏰进入OGG_HOME/bin目录,调用adminclient命令进入命令行模式,也是调用service manager的REST API接口来操作(类似登陆WEB).然后dblogin来访问数据库
具体命令(如果不先connect登陆,什么命令不好使)
cd $OGG_HOME/bin
./adminclient
访问service manager api:connect http://192.168.124.97:7809 deployment deploymentname as oggadmin password oggadmin
登陆database:dblogin useridalias ggadmin domain oggadmin
2.adminclient与ggsci命令基本相同
⏰如何创建、启动、删除,有些命令,web功能虽然好用,有些功能不支持,例如配置ddl、修改指定thread的scn、time、seqno、rba不支持
OGG (http://192.168.124.97:7809 deploy11g as ggadmin@XIAOXU) 5> info ext01
EXTRACT EXT01 Last Started 2019-07-30 23:01 Status RUNNING
Checkpoint Lag 00:00:03 (updated 00:00:09 ago)
Process ID 1529
Log Read Checkpoint Oracle Redo Logs
2019-07-31 00:14:38 Thread 1, Seqno 12, RBA 8719360
SCN 0.1157041 (1157041)
Log Read Checkpoint Oracle Redo Logs
2019-07-30 16:14:34 Thread 2, Seqno 5, RBA 8593408
SCN 0.1157040 (1157040)
Encryption Profile LocalWallet
No REPLICAT groups found, but some coordinated threads may have been excluded
OGG (http://192.168.124.97:7809 deploy11g as ggadmin@XIAOXU) 6> info rep
No EXTRACT groups found, but some coordinated threads may have been excluded
REPLICAT REP Last Started 2019-07-30 23:09 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:06 ago)
Process ID 13392
Log Read Checkpoint File /u01/app/ogg/db11.2/ogg191_deploy/var/lib/data/rt000000000
2019-07-30 23:30:09.997803 RBA 4948
Settings Profile Default
Encryption Profile LocalWallet
[CLASSIC抽取无法抽取到数据以及问题排查】
在Administration Server里面选择点击抽取进程的action的detail
--发现rac 2个节点信息,只发现一个无具体信息.有没有办法类似经典架构ggsci命令直接查看进程信息,现在名字叫adminclient
2.进入OGG_HOME/bin目录,调用adminclient命令进入命令行模式
OGG (not connected) 2> connect http://192.168.124.97:7809 deployment deploy11g as oggadmin password oggadmin
OGG (http://192.168.124.97:7809 deploy11g) 3> info all
Program Status Group Type Lag at Chkpt Time Since Chkpt
ADMINSRVR RUNNING
DISTSRVR RUNNING
PMSRVR RUNNING
RECVSRVR RUNNING
EXTRACT RUNNING EXT01 CLASSIC 00:00:00 00:00:01
EXTRACT RUNNING EXT02 CLASSIC 00:00:03 00:00:03
OGG (http://192.168.124.97:7809 deploy11g) 4> info EXT02
EXTRACT EXT02 Last Started 2019-07-30 11:29 Status RUNNING
Checkpoint Lag 00:00:02 (updated 00:00:07 ago)
Process ID 7058
Log Read Checkpoint Oracle Redo Logs
2019-07-30 11:45:12 Thread 1, Seqno 9, RBA 18807808
SCN 0.1054997 (1054997)
Log Read Checkpoint Oracle Redo Logs
2019-07-30 11:26:09 Thread 2, Seqno 0, RBA 0
SCN 0.0 (0)
Settings Profile Default
Encryption Profile LocalWallet
No REPLICAT groups found, but some coordinated threads may have been excluded
OGG (http://192.168.124.97:7809 deploy11g) 6> alter extract ext02,extseqno 1,extrba 0,thread 2
OGG (http://192.168.124.97:7809 deploy11g) 7> start ext02
2019-07-30T03:51:11Z INFO OGG-00975 EXTRACT EXT02 starting
2019-07-30T03:51:11Z INFO OGG-15426 EXTRACT EXT02 started
OGG (http://192.168.124.97:7809 deploy11g) 11> info ext02
EXTRACT EXT02 Last Started 2019-07-30 11:51 Status RUNNING
Checkpoint Lag 00:05:02 (updated 00:00:10 ago)
Process ID 20190
Log Read Checkpoint Oracle Redo Logs
2019-07-30 11:46:05 Thread 1, Seqno 9, RBA 18834448
SCN 0.1055111 (1055111)
Log Read Checkpoint Oracle Redo Logs
2019-07-30 11:26:09 Thread 2, Seqno 1, RBA 0
SCN 0.0 (0)
Settings Profile Default
Encryption Profile LocalWallet
No REPLICAT groups found, but some coordinated threads may have been excluded
OGG (http://192.168.124.97:7809 deploy11g) 12> info ext02
EXTRACT EXT02 Last Started 2019-07-30 11:51 Status ABENDED
Checkpoint Lag 00:05:02 (updated 00:00:11 ago)
Log Read Checkpoint Oracle Redo Logs
2019-07-30 11:46:05 Thread 1, Seqno 9, RBA 18834448
SCN 0.1055111 (1055111)
Log Read Checkpoint Oracle Redo Logs
2019-07-30 11:26:09 Thread 2, Seqno 1, RBA 0
SCN 0.0 (0)
Settings Profile Default
Encryption Profile LocalWallet
No REPLICAT groups found, but some coordinated threads may have been excluded
错误日志如下--此时发现附加日志没有开启,其实已开启.对比2个节点时间不一致.
2019-07-30 11:51:17 ERROR OGG-00730 No minimum supplemental logging is enabled.
OGG (http://192.168.124.97:7809 deploy11g) 43> alter extract ext02,extseqno 4,extrba 0,thread 2
OGG (http://192.168.124.97:7809 deploy11g) 43>start ext02
OGG (http://192.168.124.97:7809 deploy11g) 42> info ext02
EXTRACT EXT02 Last Started 2019-07-30 12:03 Status RUNNING
Checkpoint Lag 00:00:02 (updated 00:00:07 ago)
Process ID 23973
Log Read Checkpoint Oracle Redo Logs
2019-07-30 12:03:12 Thread 1, Seqno 9, RBA 21518336
SCN 0.1057982 (1057982)
Log Read Checkpoint Oracle Redo Logs
2019-07-30 04:03:11 Thread 2, Seqno 4, RBA 2557440
SCN 0.1057981 (1057981)
Settings Profile Default
Encryption Profile LocalWallet
No REPLICAT groups found, but some coordinated threads may have been excluded
OGG (http://192.168.124.97:7809 deploy11g) 43>
【总结】oracle goldengate 19 Microservices安装、配置以及数据同步实战到此结束。