网站导航
> 文档中心 > 基于OGG实现Oracle 11G双主同步

基于OGG实现Oracle 11G双主同步

网友: 文档中心 2022-10-13 07:47:34

📢📢📢📣📣📣
哈喽！大家好，我是【IT邦德】，江湖人称jeames007，10年DBA工作经验
一位上进心十足的【大数据领域博主】！😜😜😜
中国DBA联盟(ACDU)成员，目前从事DBA及程序编程
擅长主流数据Oracle、MySQL、PG 运维开发，备份恢复，安装迁移，性能优化、故障应急处理等。
✨ 如果有对【数据库】感兴趣的【小可爱】，欢迎关注【IT邦德】💞💞💞
❤️❤️❤️感谢各位大可爱小可爱！❤️❤️❤️

文章目录

  • 前言
    • 📣 1.双主环境
    • 📣 2.数据库配置
    • 📣 3.创建身份证明
    • 📣 4.源端添加SCHEMATRANDATA
    • 📣 5.源端和目标端创建脉动表
    • 📣 6.配置11G双向同步
      • ✨ ora11ga到ora11gb的同步
      • ✨ ora11gb到ora11ga的同步
    • 📣 7.双主数据对比

前言

最近客户应客户需求，基于OGG 21.3实现Oracle 11.2.0.4双主同步，分享给大家

📣 1.双主环境

1.OGG服务器[root@localhost ~]# cat /etc/redhat-releaseRed 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.serviceRemoved 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/sestatus2.源库11GR2ip:192.168.1.483.目标库11GR2ip:192.168.1.58

📣 2.数据库配置

## 以下操作在源库及目标库均操作1 开启数据库归档[oracle@rhel64 ~]$ sqlplus / as sysdbaSQL> !lsnrctl startSQL> alter system register;System altered.SQL> archive log listDatabase log modeArchive ModeAutomatic archival      EnabledArchive destination     /u01/app/oracle/product/11.2.0/dbhome_1/dbs/archOldest online log sequence     15Next log sequence to archive   17Current log sequence    17SYS@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--------- -------- --- ------------MESYES      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 force5 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 startlsnrctl status

📣 3.创建身份证明

1.登陆OGG[oracle@ogg21c ~]$ ggsciOracle GoldenGate Command Interpreter for OracleVersion 21.3.0.0.0 OGGCORE_21.3.0.0.0_PLATFORMS_210728.1047_FBOOracle Linux 7, x64, 64bit (optimized), Oracle Database 21c and lower supported versions on Jul 29 2021 03:59:23Operating 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 subdirsCreating subdirectories under current directory /oggParameter 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 credentialstoreCredential store created.GGSCI (ogg21c) 3> alter credentialstore add user ogg@192.168.1.48/MES, password jea alias ora11gaCredential store altered.GGSCI (ogg21c) 4> alter credentialstore add user ogg@192.168.1.58/MES, password jea alias ora11gbCredential store altered.GGSCI (ogg21c) 5> INFO CREDENTIALSTOREReading from credential store:Default domain: OracleGoldenGate  Alias: ora11ga  Userid: ogg@192.168.1.48/MES  Alias: ora11gb  Userid: ogg@192.168.1.58/MES5.测试登陆GGSCI (ogg21c) 6> dblogin useridalias ora11gaSuccessfully logged into database.GGSCI (ogg21c as ogg@MES) 7> dblogin useridalias ora11gbSuccessfully logged into database.以上测试均OK

📣 4.源端添加SCHEMATRANDATA

GGSCI (ogg21c as ogg@MES) 8> dblogin useridalias ora11gaSuccessfully logged into database.GGSCI (ogg21c as ogg@MES) 9> ADD SCHEMATRANDATA IT_MESGGSCI (ogg21c as ogg@MES) 10> INFO  SCHEMATRANDATA IT_MES2022-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 ora11gaSuccessfully logged into database.GGSCI (ogg21c as ogg@MES) 12> ADD HEARTBEATTABLE2022-10-12 23:08:14  INFO    OGG-14101  Successfully added heartbeat table.GGSCI (ogg21c as ogg@MES) 13> INFO HEARTBEATTABLEHEARTBEAT 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]$ ggsciADD EXTTRAIL ./dirdat/ea EXTRACT extadblogin useridalias ora11gaREGISTER EXTRACT exta DATABASEGGSCI (ogg21c as ogg@MES) 5> edit params extaEXTRACT extaUSERIDALIAS ora11gaDDL INCLUDE MAPPEDDDLOPTIONS REPORTTRANLOGOPTIONS EXCLUDETAG 99EXTTRAIL ./dirdat/eaTABLE IT_MES.*;

基于OGG实现Oracle 11G双主同步

2.创建replicate进程ADD REPLICAT repb INTEGRATED EXTTRAIL ./dirdat/eaGGSCI (ogg21c as ogg@MES) 11> edit params repbREPLICAT repbUSERIDALIAS ora11gbDBOPTIONS ENABLE_INSTANTIATION_FILTERINGDDL INCLUDE MAPPEDDDLOPTIONS REPORTDBOPTIONS SETTAG 99MAP IT_MES.*, TARGET IT_MES.*;注：配置完成后启动相关进程即可

基于OGG实现Oracle 11G双主同步

✨ ora11gb到ora11ga的同步

1.创建extract进程[oracle@ogg21c ~]$ ggsciADD EXTRACT extb INTEGRATED TRANLOG BEGIN NOWADD EXTTRAIL ./dirdat/eb EXTRACT extbdblogin useridalias ora11gbREGISTER EXTRACT extb DATABASEGGSCI (ogg21c as ogg@MES) 5> edit params extbEXTRACT extbUSERIDALIAS ora11gbDDL INCLUDE MAPPEDDDLOPTIONS REPORTTRANLOGOPTIONS EXCLUDETAG 99EXTTRAIL ./dirdat/ebTABLE IT_MES.*;2.创建replicate进程ADD REPLICAT repa INTEGRATED EXTTRAIL ./dirdat/ebGGSCI (ogg21c as ogg@MES) 9> edit params repaREPLICAT repaUSERIDALIAS ora11gaDDL INCLUDE MAPPEDDDLOPTIONS REPORTDBOPTIONS SETTAG 99MAP IT_MES.*, TARGET IT_MES.*;

📣 7.双主数据对比

测试DDL和DML同步在Oracle 11g 目标端操作,操作前启动进程repa、extbSQL> 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同步都已经完成！

基于OGG实现Oracle 11G双主同步
基于OGG实现Oracle 11G双主同步
功夫不负有心人，这样一套双主的11G双主实时同步就部署完成了

在这里插入图片描述

身份验证指纹锁商城

网络标签：

相关问题