更多动态

数据库运维案例-oracle数据库归档区爆满引起DOWN机

数据库维护事件-杭州软件公司监控系统异常,车辆无法监控

数据库维护案例-发票数据库性能耗尽,发票业务瘫痪

数据库维护-河北某集团数据库缺乏检查制度,导致数据库崩溃

数据库维护案例-广州电商网站数据库误操作引起性能耗尽

Goldengate同步方案_SQL SERVER到Oracle11g的同步【完整排错版亲测有效】

与北京OFO续签数据库巡检运维服务合同

广州鑫霸-dataguard broker+Goldengate双主热备方案

深圳解放路SQL Server2016 Always on集群部署维护项目

上海尤恩-Oracle数据库优化紧急救援项目

海南中诚实业-数据库月度巡检维保服务

杭州图软:Oracle active dataguard数据高可用部署运维项目

武汉网易通达-Oracle高可用集成维护服务

广州卓勤信息技术有限公司-Oracle数据库优化项目处理

2019年6月MYSQL数据库性能优化项目

北京新网医讯-2016年7月数据库优化项目

展开更多 arrow03

oracle通过OGG实现到sql server同步

2020-04-21 20:09
 
二维码
50

OGG同步ORACLE至SQLSERVER(转)

系统环境

源库:ORACLE 11.2.0.4+RedHat 6.5
目标库:SQLSERVER 2014+windows
ogg for oracle
链接: https://pan.baidu.com/s/1kWuhBsN 密码: h5q1
ogg for mssql
链接: https://pan.baidu.com/s/1gg3Jmcr 密码: nc93

OGG下载地址:https://www.oracle.com/technetwork/middleware/goldengate/downloads/index.html

源库配置

数据库设置为归档模式

ALTER DATASBASE ARCHIVELOG;

开启强制日志

ALTER DATABASE FORCE LOGGING;

开启附加日志

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

修改参数

ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION=TRUE;

创建用户

  1. --创建用户表空间
  2. create tablespace ogg datafile '/u01/app/oracle/oradata/ogg.dbf' size 2G autoextend off;
  3. --创建用户
  4. create user goldengate identified by goldengate;
  5. --添加权限
  6. grant dba to goldengate;

    grant connect,resource,create session,alter session,select any dictionary,select any table,flashback any table,alter any table,insert any table,update any table,delete any table,select any transaction,execute on DBMS_CAPTURE_ADM to ogg;

静默安装:

goldengate12c版本开始也开始采用oui方式进行安装,但是有很多情况下操作系统没有图形化,这种情况下可以采用静默方式安装,具体步骤如下:
1.上传ogg 12.1.2.1.0介质
2.解压介质
unzip V46695-01.zip
3.配置自动应答文件
静默安装需要配置一个自动应答文件,位置在刚才解压出来的介质目录中,具体如下:
[oracle@rac1 software]$ cd fbo_ggs_Linux_x64_shiphome/Disk1/response/
[oracle@rac1 response]$ ls
oggcore.rsp
这个oggcore.rsp就是静默安装的配置模板,照着这个模板修改即可。
[oracle@rac1 response]$ cat oggcore.rsp
oracle.install.responseFileVersion=/oracle/install/rspfmt_ogginstall_response_schema_v12_1_2
不要修改这个值
################################################################################
##                                                                            ##
## Oracle GoldenGate installation option and details                          ##
##                                                                            ##
################################################################################
#-------------------------------------------------------------------------------
# Specify the installation option.
# Specify ORA12c for installing Oracle GoldenGate for Oracle Database 12c and
#         ORA11g for installing Oracle GoldenGate for Oracle Database 11g
#-------------------------------------------------------------------------------
INSTALL_OPTION=ORA11g
根据系统是12c还是11g选择ORA12c或者ORA11g
#-------------------------------------------------------------------------------
# Specify a location to install Oracle GoldenGate
#-------------------------------------------------------------------------------
SOFTWARE_LOCATION=/goldengate
写上goldengate的安装目录
#-------------------------------------------------------------------------------
# Specify true to start the manager after installation.
#-------------------------------------------------------------------------------
START_MANAGER=false
是否在配置完成后自动启动mgr进程,是就选true,否就选false
#-------------------------------------------------------------------------------
# Specify a free port within the valid range for the manager process.
# Required only if START_MANAGER is true.
#-------------------------------------------------------------------------------
MANAGER_PORT=
在start_managertrue时添加,选择mgr启动端口号
#-------------------------------------------------------------------------------
# Specify the location of the Oracle Database.
# Required only if START_MANAGER is true.
#-------------------------------------------------------------------------------
DATABASE_LOCATION=
在start_managertrue时添加,写上$ORACLE_HOME的值
################################################################################
##                                                                            ##
## Specify details to Create inventory for Oracle installs                    ##
## Required only for the first Oracle product install on a system.            ##
##                                                                            ##
################################################################################
如果在安装goldengate前没有安装过其他Oracle产品再填写以下两个参数
#-------------------------------------------------------------------------------
# Specify the location which holds the install inventory files.
# This is an optional parameter if installing on
# Windows based Operating System.
#-------------------------------------------------------------------------------
INVENTORY_LOCATION=
指定inventory目录的位置,在使用windows操作系统时是一个可选参数。
#-------------------------------------------------------------------------------
# Unix group to be set for the inventory directory.  
# This parameter is not applicable if installing on
# Windows based Operating System.
#-------------------------------------------------------------------------------
UNIX_GROUP_NAME=
指定一个组,windows系统时不需要写
4.启动静默安装
./runInstaller -silent -responseFile /ogg/fbo_ggs_Linux_x64_shiphome/Disk1/response/oggcore.rsp
之后开始安装
这时候会提示一个安装日志,
You can find the log of this install session at:
/oracle/app/oraInventory/logs/installActions2016-07-08_11-26-16AM.log
可以使用 tail -100f   /oracle/app/oraInventory/logs/installActions2016-07-08_11-26-16AM.log来查看安装情况
安装完成后,安装命令执行页面会输出以下内容:
The installation of Oracle GoldenGate Core was successful.
Please check '/oracle/app/oraInventory/logs/silentInstall2016-07-08_11-26-16AM.log' for more details.
Successfully Setup Software.
5.进入到goldengate目录创建子目录
GGSCI (rac1) 2> create subdirs
Creating subdirectories under current directory /goldengate
Parameter files                /goldengate/dirprm: already exists
Report files                   /goldengate/dirrpt: created
Checkpoint files               /goldengate/dirchk: created
Process status files           /goldengate/dirpcs: created
SQL script files               /goldengate/dirsql: created
Database definitions files     /goldengate/dirdef: created
Extract data files             /goldengate/dirdat: created
Temporary files                /goldengate/dirtmp: created
Credential store files         /goldengate/dircrd: created
Masterkey wallet files         /goldengate/dirwlt: created
Dump files                     /goldengate/dirdmp: created
至此goldengate12c静默安装完成
附排错:
版权声明:本文为博主原创文章,未经博主允许不得转载。
Checking Temp space: must be greater than 120 MB.   Actual 30353 MB    PassedChecking swap space: must be greater than 150 MB.   Actual 1008 MB    PassedPreparing to launch Oracle Universal Installer from /tmp/OraInstall2017-09-06_10-54-47AM. Please wait ...[FATAL] [INS-75012] Software Location specified is already an existing Oracle home and does not contain the selected Oracle Golden Gate install type.   CAUSE: Software Location specified is found registered in the central inventory already and does not contain the selected Oracle Golden Gate install type.   ACTION: Specify an empty directory or an Oracle home containing the selected Oracle Golden Gate install type.
是由于前面安装修改了目录清单引起的。可以通过修改oracle下的清单中的xml文件解决
vi /u01/app/oraInventory/ContentsXML/inventory.xml
将有冲突的清单删除,如删除这里的LOC=”/u01/ogg”这行
<?xml version="1.0" standalone="yes" ?><!-- Copyright (c) 1999, 2011, Oracle. All rights reserved. --><!-- Do not modify the contents of this file by hand. --><INVENTORY><VERSION_INFO><SAVED_WITH>11.2.0.3.0</SAVED_WITH><MINIMUM_VER>2.1.0.6.0</MINIMUM_VER></VERSION_INFO><HOME_LIST><HOMENAME="OraDb11g_home1"LOC="/u01/app/oracle/product/11.2.0/db_1"TYPE="O"IDX="1"/><HOMENAME="OraHome1"LOC="/u01/ogg"TYPE="O"IDX="2"/></HOME_LIST><COMPOSITEHOME_LIST></COMPOSITEHOME_LIST></INVENTORY>


图形界面安装

software location为软件安装路径,database location为ORACLE_HOMEinstall安装选择数据库版本
配置MGR进程

  1. GGSCI>edit params mgr
    port 7809
    DYNAMICPORTLIST 7840-7850
    PURGEOLDEXTRACTS /ogg/software/dirdat/e_tab/*,usecheckpoints,minkeepdays 7

创建defgen文件

  1. a.编辑defgen参数

    GGSCI> edit params defgen

    defsfile /ogg/software/dirdef/tab.def

    userid goldengate,password goldengate

    table EAS2.CT_DSY_REPORTSYN001;

    table EAS2.CT_DSY_REPORTSYN002;

    table EAS2.CT_DSY_REPORTSYN003;

    table EAS2.CT_DSY_REPORTSYN004;

    table EAS2.CT_DSY_REPORTSYN005;

    table EAS2.CT_DSY_REPORTSYN006;

    table EAS2.CT_DSY_REPORTSYN007;

    table EAS2.CT_DSY_REPORTSYN008;

    table EAS2.CT_DSY_REPORTSYN009;

    table EAS2.CT_DSY_REPORTSYN010;

    table EAS2.CT_DSY_REPORTSYN011;

    table EAS2.CT_DSY_REPORTSYN012;

    table EAS2.CT_DSY_REPORTSYN013;

    table EAS2.CT_DSY_REPORTSYN014;

    table EAS2.CT_DSY_REPORTSYN015;

    table EAS2.CT_DSY_REPORTSYN016;

    table EAS2.CT_DSY_REPORTSYN017;

    table EAS2.CT_DSY_REPORTSYN018;

    table EAS2.CT_DSY_REPORTSYN019;

    table EAS2.CT_DSY_REPORTSYN020;

    table EAS2.CT_DSY_REPORTSYN020S1;

    table EAS2.CT_DSY_REPORTSYN021;

    table EAS2.CT_DSY_REPORTSYN021S1;

    table EAS2.CT_DSY_REPORTSYN022;

    table EAS2.CT_DSY_REPORTSYN023;

    table EAS2.CT_DSY_REPORTSYN024;

    table EAS2.CT_DSY_REPORTSYN025;

    table EAS2.CT_DSY_REPORTSYN026;

    table EAS2.CT_DSY_REPORTSYN027;

    table EAS2.CT_DSY_REPORTSYN028;

    table EAS2.CT_DSY_REPORTSYN029;

    table EAS2.CT_DSY_REPORTSYN030;

    table EAS2.CT_DSY_REPORTSYN031;

    table EAS2.CT_DSY_REPORTSYN032;

    table EAS2.CT_DSY_REPORTSYN033;

    table EAS2.CT_DSY_REPORTSYN034;

    table EAS2.CT_DSY_REPORTSYN035;

    table EAS2.CT_DSY_REPORTSYN036;



    b.生成defgen文件
    ./defgen paramfile /ogg/software/dirprm/defgen.prm
    c.将tab.def复制到目标端dirdef目录下

添加表附加日志

GGSCI>dblogin userid goldengate, password goldengate

add trandata   EAS2.CT_DSY_REPORTSYN001

add trandata   EAS2.CT_DSY_REPORTSYN002

add trandata   EAS2.CT_DSY_REPORTSYN003

add trandata   EAS2.CT_DSY_REPORTSYN004

add trandata   EAS2.CT_DSY_REPORTSYN005

add trandata   EAS2.CT_DSY_REPORTSYN006

add trandata   EAS2.CT_DSY_REPORTSYN007

add trandata   EAS2.CT_DSY_REPORTSYN008

add trandata   EAS2.CT_DSY_REPORTSYN009

add trandata   EAS2.CT_DSY_REPORTSYN010

add trandata   EAS2.CT_DSY_REPORTSYN011

add trandata   EAS2.CT_DSY_REPORTSYN012

add trandata   EAS2.CT_DSY_REPORTSYN013

add trandata   EAS2.CT_DSY_REPORTSYN014

add trandata   EAS2.CT_DSY_REPORTSYN015

add trandata   EAS2.CT_DSY_REPORTSYN016

add trandata   EAS2.CT_DSY_REPORTSYN017

add trandata   EAS2.CT_DSY_REPORTSYN018

add trandata   EAS2.CT_DSY_REPORTSYN019

add trandata   EAS2.CT_DSY_REPORTSYN020

add trandata   EAS2.CT_DSY_REPORTSYN020S1

add trandata   EAS2.CT_DSY_REPORTSYN021

add trandata   EAS2.CT_DSY_REPORTSYN021S1

add trandata   EAS2.CT_DSY_REPORTSYN022

add trandata   EAS2.CT_DSY_REPORTSYN023

add trandata   EAS2.CT_DSY_REPORTSYN024

add trandata   EAS2.CT_DSY_REPORTSYN025

add trandata   EAS2.CT_DSY_REPORTSYN026

add trandata   EAS2.CT_DSY_REPORTSYN027

add trandata   EAS2.CT_DSY_REPORTSYN028

add trandata   EAS2.CT_DSY_REPORTSYN029

add trandata   EAS2.CT_DSY_REPORTSYN030

add trandata   EAS2.CT_DSY_REPORTSYN031

add trandata   EAS2.CT_DSY_REPORTSYN032

add trandata   EAS2.CT_DSY_REPORTSYN033

add trandata   EAS2.CT_DSY_REPORTSYN034

add trandata   EAS2.CT_DSY_REPORTSYN035

add trandata   EAS2.CT_DSY_REPORTSYN036




配置抽取进程

  1. a.创建trail文件目录
  2. mkdir -p /u01/ogg/dirdat/EXMSSQL
  3. b.添加抽取进程
  4. GGSCI>add extract e_tab,tranlog,threads 1,begin now

    GGSCI>add exttrail /ogg/software/dirdat/e_tab/ex,extract e_tab,megabytes 200

    GGSCI>edit param e_tab


    extract e_tab

    SETENV (ORACLE_HOME = "/data/oracle/product/11.2.0/db_1")

    SETENV (ORACLE_SID = "eastest")

    SETENV (NLS_LANG = "AMERICAN_AMERICA.AL32UTF8")

    userid goldengate,password goldengate

    exttrail /ogg/software/dirdat/e_tab/ex

    TRANLOGOPTIONS DBLOGREADER

    EOFDELAYCSECS 50

    FLUSHCSECS 50

    dynamicresolution

    defsfile /ogg/software/dirdef/tab.def

    userid goldengate,password goldengate

    table EAS2.CT_DSY_REPORTSYN001;

    table EAS2.CT_DSY_REPORTSYN002;

    table EAS2.CT_DSY_REPORTSYN003;

    table EAS2.CT_DSY_REPORTSYN004;

    table EAS2.CT_DSY_REPORTSYN005;

    table EAS2.CT_DSY_REPORTSYN006;

    table EAS2.CT_DSY_REPORTSYN007;

    table EAS2.CT_DSY_REPORTSYN008;

    table EAS2.CT_DSY_REPORTSYN009;

    table EAS2.CT_DSY_REPORTSYN010;

    table EAS2.CT_DSY_REPORTSYN011;

    table EAS2.CT_DSY_REPORTSYN012;

    table EAS2.CT_DSY_REPORTSYN013;

    table EAS2.CT_DSY_REPORTSYN014;

    table EAS2.CT_DSY_REPORTSYN015;

    table EAS2.CT_DSY_REPORTSYN016;

    table EAS2.CT_DSY_REPORTSYN017;

    table EAS2.CT_DSY_REPORTSYN018;

    table EAS2.CT_DSY_REPORTSYN019;

    table EAS2.CT_DSY_REPORTSYN020;

    table EAS2.CT_DSY_REPORTSYN020S1;

    table EAS2.CT_DSY_REPORTSYN021;

    table EAS2.CT_DSY_REPORTSYN021S1;

    table EAS2.CT_DSY_REPORTSYN022;

    table EAS2.CT_DSY_REPORTSYN023;

    table EAS2.CT_DSY_REPORTSYN024;

    table EAS2.CT_DSY_REPORTSYN025;

    table EAS2.CT_DSY_REPORTSYN026;

    table EAS2.CT_DSY_REPORTSYN027;

    table EAS2.CT_DSY_REPORTSYN028;

    table EAS2.CT_DSY_REPORTSYN029;

    table EAS2.CT_DSY_REPORTSYN030;

    table EAS2.CT_DSY_REPORTSYN031;

    table EAS2.CT_DSY_REPORTSYN032;

    table EAS2.CT_DSY_REPORTSYN033;

    table EAS2.CT_DSY_REPORTSYN034;

    table EAS2.CT_DSY_REPORTSYN035;

    table EAS2.CT_DSY_REPORTSYN036;






配置投递进程

  1. GGSCI >add extract p_tab,EXTTRAILSOURCE /ogg/software/dirdat/e_tab/ex
  2. GGSCI >add rmttrail E:\ogg\software\dirdat\r_tab\re,ext p_tab,megabytes 200
  3. GGSCI >edit param p_tab

    extract p_tab

    rmthost 196.1.121.73,mgrport 7809

    passthru

    FLUSHCSECS 40

    EOFDELAYCSECS 10

    rmttrail E:\ogg\software\dirdat\r_tab\re

    table EAS2.*;

目标库配置

配置ODBC接口

新建SQL SERVER Native Client系统DSN

创建服务

>INSTALL ADDSERVICE 

编辑GLOBALS

  1. GGSCI >edit param ./GLOBALS

    checkpointtable dbo.ckpttab

创建checkpoint表

  1. GGSCI >dblogin sourcedb   ogg_odbc userid goldengate password goldengate

    GGSCI >add checkpointtable dbo.ckpttab

配置MGR进程

  1. GGSCI >edit params mgr

    port 7809

    DYNAMICPORTLIST 7840-7850

    ACCESSRULE, PROG SERVER, ALLOW

    autorestart er *, retries 5, waitminutes 3

    purgeoldextracts F:\OGG\dirdat\*,usecheckpoints, minkeepdays 7

配置复制进程

  1. a. 创建trail存放目录

  2. F:\ogg\dirdat\REMSSQL
  3. b.添加复制进程
  4. GGSCI >add replicat r_tab,exttrail F:\OGG\dirdat\REMSSQL\RE,begin now,checkpointtable dbo.ckpttab
    GGSCI >edit param r_tab

    replicat r_tab

    sourcedefs F:\OGG\dirdef\sync.def

    targetdb lu userid goldengate , password goldengate

    reperror default,discard

    discardfile F:\OGG\dirrpt\REMSSQL.dsc,append,megabytes 100
    gettruncates

    map DC_DC.DC_RA_MER_BASE,target dbo.DC_RA_MER_BASE;

初始化数据

  1. a.在SQLSERVER创建相应表
  2. b.源端导出相应表初始化数据
  3. GGSCI> edit param exinit
    SOURCEISTABLE

    userid goldengate , password goldengate

    rmthost 182.168.8.1, mgrport 7809
    RMTFILE F:\OGG\dirdat\REMSSQL\EX,MAXFILES 1024, MEGABYTES 1024

    table test.dc_ra_mer_base;

  4. --OGG INSTALL DIRECTORY
  5. $ ./extract paramfile ./dirprm/exinit.prm reportfile ./dirrpt/exinit.rpt
  6. c.启动源端进程
  7. GGSCI>start *
  8. d.目标段导入相应初始化数据并启动进程
  9. replicat paramfile ./dirprm/reinit.prm
  10. GGSCI>start *


昵称:
内容:
验证码:
提交评论