持续创作，加速成长！这是我参与「掘金日新计划 · 10 月更文挑战」的第11天，点击查看活动详情
📢📢📢📣📣📣 哈喽！大家好，我是【IT邦德】，江湖人称jeames007，10年DBA工作经验 一位上进心十足的【大数据领域博主】！😜😜😜 中国DBA联盟(ACDU)成员，目前从事DBA及程序编程 擅长主流数据Oracle、MySQL、PG 运维开发，备份恢复，安装迁移，性能优化、故障应急处理等。 ✨ 如果有对【数据库】感兴趣的【小可爱】，欢迎关注【IT邦德】💞💞💞 ❤️❤️❤️感谢各位大可爱小可爱！❤️❤️❤️
@TOC
前言
最近客户应客户需求，基于OGG 21.3实现Oracle 11.2.0.4双主同步，分享给大家📣 1.双主环境
1.OGG服务器
[root@localhost ~]# cat /etc/redhat-release
Red Hat Enterprise Linux Server release 7.4 (Maipo)
hostname修改
[root@localhost ~]# hostname
[root@localhost ~]# ogg21c
--关闭防火墙还有selinux
[root@localhost ~]# systemctl stop firewalld.service
[root@localhost ~]# systemctl disable firewalld.service
Removed symlink /etc/systemd/system/multi-user.target.wants/firewalld.service.
Removed symlink /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.
修改/etc/selinux/config文件中设置SELINUX=disabled ，然后重启服务器
查看状态为：/usr/sbin/sestatus
2.源库11GR2
ip:192.168.1.48
3.目标库11GR2
ip:192.168.1.58
📣 2.数据库配置
## 以下操作在源库及目标库均操作
1 开启数据库归档
[oracle@rhel64 ~]$ sqlplus / as sysdba
SQL> !lsnrctl start
SQL> alter system register;
System altered.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch
Oldest online log sequence 15
Next log sequence to archive 17
Current log sequence 17
SYS@PROD>shutdown immediate;
SYS@PROD>startup mount;
SYS@PROD>alter database archivelog;
SYS@PROD>alter database open;
SYS@PROD>archive log list;
2 开启数据库级别附加日志
SQL> select name,supplemental_log_data_min , force_logging, log_mode from v$database;
NAME SUPPLEME FOR LOG_MODE
--------- -------- --- ------------
MES YES YES ARCHIVELOG
开启方法如下：
alter database add supplemental log data;
alter database add supplemental log data (all) columns;
3 开启强制日志
alter database force logging;
4 参数为设置
SQL> show parameter enable_goldengate_replication;
SQL> alter system set enable_goldengate_replication=TRUE;
SQL> startup force
5 OGG管理用户
SQL> select username from dba_users where username='OGG';
CREATE USER ogg identified by jea;
grant SELECT ANY DICTIONARY to ogg;
GRANT EXECUTE ON SYS.DBMS_LOCK TO ogg;
grant select any transaction to ogg;
grant select any table to ogg;
grant flashback any table to ogg;
grant alter any table to ogg;
exec dbms_goldengate_auth.grant_admin_privilege('OGG','*',TRUE);
6 业务用户，源库操作
CREATE USER it_mes identified by it_mes;
GRANT DBA to it_mes ;
grant SELECT ANY DICTIONARY to it_mes;
GRANT EXECUTE ON SYS.DBMS_LOCK TO it_mes;
7 启动监听
lsnrctl start
lsnrctl status
📣 3.创建身份证明
1.登陆OGG
[oracle@ogg21c ~]$ ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 21.3.0.0.0 OGGCORE_21.3.0.0.0_PLATFORMS_210728.1047_FBO
Oracle Linux 7, x64, 64bit (optimized), Oracle Database 21c and lower supported versions on Jul 29 2021 03:59:23
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2021, Oracle and/or its affiliates. All rights reserved.
2.OGG初始化
GGSCI (ogg21c) 1> 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.
Master encryption key wallet files /ogg/dirwlt: created.
Dump files /ogg/dirdmp: created.
3.身份证明注册
GGSCI (ogg21c) 2> add credentialstore
Credential store created.
GGSCI (ogg21c) 3> alter credentialstore add user ogg@192.168.1.48/MES, password jea alias ora11ga
Credential store altered.
GGSCI (ogg21c) 4> alter credentialstore add user ogg@192.168.1.58/MES, password jea alias ora11gb
Credential store altered.
GGSCI (ogg21c) 5> INFO CREDENTIALSTORE
Reading from credential store:
Default domain: OracleGoldenGate
Alias: ora11ga
Userid: ogg@192.168.1.48/MES
Alias: ora11gb
Userid: ogg@192.168.1.58/MES
5.测试登陆
GGSCI (ogg21c) 6> dblogin useridalias ora11ga
Successfully logged into database.
GGSCI (ogg21c as ogg@MES) 7> dblogin useridalias ora11gb
Successfully logged into database.
以上测试均OK
📣 4.源端添加SCHEMATRANDATA
GGSCI (ogg21c as ogg@MES) 8> dblogin useridalias ora11ga
Successfully logged into database.
GGSCI (ogg21c as ogg@MES) 9> ADD SCHEMATRANDATA IT_MES
GGSCI (ogg21c as ogg@MES) 10> INFO SCHEMATRANDATA IT_MES
2022-10-12 23:04:21 INFO OGG-06480 Schema level supplemental logging, excluding non-validated keys, is enabled on schema "IT_MES".
2022-10-12 23:04:21 INFO OGG-01980 Schema level supplemental logging is enabled on schema "IT_MES" for all scheduling columns.
📣 5.源端和目标端创建脉动表
## 源端、目标端均操作
GGSCI (ogg21c as ogg@MES) 11> dblogin useridalias ora11ga
Successfully logged into database.
GGSCI (ogg21c as ogg@MES) 12> ADD HEARTBEATTABLE
2022-10-12 23:08:14 INFO OGG-14101 Successfully added heartbeat table.
GGSCI (ogg21c as ogg@MES) 13> INFO HEARTBEATTABLE
HEARTBEAT table ogg.gg_heartbeat exists.
HEARTBEAT table ogg.gg_heartbeat_seed exists.
HEARTBEAT table ogg.gg_heartbeat_history exists.
HEARTBEAT table ogg.gg_heartbeat supplemental logging ENABLED.
HEARTBEAT table ogg.gg_heartbeat_seed supplemental logging ENABLED.
HEARTBEAT table ogg.gg_heartbeat_history partitioning DISABLED.
Frequency interval: 60 seconds.
Purge frequency interval: 1 days.
Retention time: 30 days.
📣 6.配置11G双向同步
✨ ora11ga到ora11gb的同步
1.创建extract进程
[oracle@ogg21c ogg]$ ggsci
ADD EXTTRAIL ./dirdat/ea EXTRACT exta
dblogin useridalias ora11ga
REGISTER EXTRACT exta DATABASE
GGSCI (ogg21c as ogg@MES) 5> edit params exta
EXTRACT exta
USERIDALIAS ora11ga
DDL INCLUDE MAPPED
DDLOPTIONS REPORT
TRANLOGOPTIONS EXCLUDETAG 99
EXTTRAIL ./dirdat/ea
TABLE IT_MES.*;
2.创建replicate进程
ADD REPLICAT repb INTEGRATED EXTTRAIL ./dirdat/ea
GGSCI (ogg21c as ogg@MES) 11> edit params repb
REPLICAT repb
USERIDALIAS ora11gb
DBOPTIONS ENABLE_INSTANTIATION_FILTERING
DDL INCLUDE MAPPED
DDLOPTIONS REPORT
DBOPTIONS SETTAG 99
MAP IT_MES.*, TARGET IT_MES.*;
注：配置完成后启动相关进程即可
✨ ora11gb到ora11ga的同步
1.创建extract进程
[oracle@ogg21c ~]$ ggsci
ADD EXTRACT extb INTEGRATED TRANLOG BEGIN NOW
ADD EXTTRAIL ./dirdat/eb EXTRACT extb
dblogin useridalias ora11gb
REGISTER EXTRACT extb DATABASE
GGSCI (ogg21c as ogg@MES) 5> edit params extb
EXTRACT extb
USERIDALIAS ora11gb
DDL INCLUDE MAPPED
DDLOPTIONS REPORT
TRANLOGOPTIONS EXCLUDETAG 99
EXTTRAIL ./dirdat/eb
TABLE IT_MES.*;
2.创建replicate进程
ADD REPLICAT repa INTEGRATED EXTTRAIL ./dirdat/eb
GGSCI (ogg21c as ogg@MES) 9> edit params repa
REPLICAT repa
USERIDALIAS ora11ga
DDL INCLUDE MAPPED
DDLOPTIONS REPORT
DBOPTIONS SETTAG 99
MAP IT_MES.*, TARGET IT_MES.*;
📣 7.双主数据对比
测试DDL和DML同步
在Oracle 11g 目标端操作,操作前启动进程repa、extb
SQL> create table IT_MES.TEST (id number primary key,name varchar2(255));
SQL> insert into IT_MES.TEST select object_id,object_name from dba_objects where object_id<=20;
SQL> commit;
源端和目标端可以对比数据：
【源库】
SQL> select count(*) from IT_MES.TEST;
COUNT(*)
----------
19
【目标库】
SQL> select count(*) from IT_MES.TEST;
COUNT(*)
----------
19
可见，DDL和DML同步都已经完成！
功夫不负有心人，这样一套双主的11G双主实时同步就部署完成了
