OGG 实用案例（三）-linux oracle 同步 windwos oracle

2021/8/4 19:08:23

编程Tag： SQL test oracle linux 00 GGSCI OGG windwos hso32

本文主要是介绍OGG 实用案例（三）-linux oracle 同步 windwos oracle，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

承接上文

01、目标库配置安装ogg

解压 191004_fbo_ggs_Windows_x64_shiphome.zip 然后点击setup.exe 操作步骤见ogg 安装文档，linux/windwos安装选项一致

02、源库配置抽取进程文件

GGSCI (hso32-db-test as ogg@hso32) 86> edit param EXTORA

EXTRACT extora

SETENV (ORACLE_SID="hso32")

SETENV (NLS_LANG="AMERICAN_AMERICA.ZHS16GBK")

userid ogg,password ogg

REPORTCOUNT EVERY 1 MINUTES, RATE

DISCARDFILE ./dirrpt/extya.dsc, APPEND, MEGABYTES 1000

WARNLONGTRANS 2h, CHECKINTERVAL 30m;

EXTTRAIL ./dirdat/oo;

DBOPTIONS  ALLOWUNUSEDCOLUMN;

FETCHOPTIONS NOUSESNAPSHOT;

DDL INCLUDE OBJTYPE 'TABLE',INCLUDE OPTYPE 'ALTER';

DDLOPTIONS  addtrandata REPORT

table test_kingle.oggtest;

table test_ogg.test_ogg;

 

03、源库配置发送进程文件

GGSCI (hso32-db-test as ogg@hso32) 96> edit param PUORACLE

EXTRACT puoracle

passthru

dynamicresolution

userid ogg,password ogg

RMTHOST 10.118.193.39, MGRPORT 7809

numfiles 5000

RMTTRAIL ./dirdat/oo

table test_kingle.oggtest;

table test_ogg.test_ogg;

 

04、源库配置进程

GGSCI (hso32-db-test as ogg@hso32) 97> add extract extora,tranlog,begin now

EXTRACT added.

GGSCI (hso32-db-test as ogg@hso32) 98> add exttrail ./dirdat/oo,extract extora

EXTTRAIL added.

GGSCI(hso32-db-testasogg@hso32) 99> add extract puoracle,exttrailsource ./dirdat/oo

EXTRACT added.

GGSCI (hso32-db-test as ogg@hso32) 100> add rmttrail ./dirdat/oo,extract puoracle

RMTTRAIL added.

 

05、目标库windwos 初始化

01）、操作前

单实例初始化建议: 在CMD 界面sqlplus / as sysdba 测试登入，看是否成功，成功则CMD 页面进入到ogg的安装页面然后./ggsci 执行登入

       多实例初始化：多实例情况下保证CMD使用的环境变量是当前的环境变量，如果不是建议通过windwos SET 配置需要的环境变量，包括PATH 制定的sqlplus 登入命令位置；ORACLE_SID 实例名；ORACLE_HOME 数据库家目录位置。

        综上建议CMD 界面 echo %PATH%、echo %ORACLE_SID% 、echo%ORACLE_HOME%

查看环境是否正确，不正确使用set 调整

 

02）、配置oracle基础环境

C:\Users\Administrator>set ORACLE_SID=ORCL

C:\Users\Administrator>set ORACLE_HOME=D:\app\Administrator\product\11.2.0\dbhome_1\

06、配置ogg 操作用户

D:\app\Administrator\product\11.2.0\dbhome_1\BIN>sqlplus.exe / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on 星期五 7月 30 13:28:19 2021

Copyright (c) 1982, 2010, Oracle.  All rights reserved

连接到:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing option

SQL> show parameter name

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_file_name_convert                 string

db_name                              string      orcl

db_unique_name                       string      orcl

global_names                         boolean     FALSE

instance_name                        string      orcl

lock_name_space                      string

log_file_name_convert                string

service_names                        string      orcl

SQL> create tablespace ogg datafile 'D:\ogg\data\ogg.dbf' size 5000M

 

表空间已创建。

SQL> create user ogg identified by "ogg" default tablespace ogg temporary tables

pace TEMP;

用户已创建。

S

QL> GRANT CONNECT TO ogg

授权成功。

SQL> GRANT ALTER ANY TABLE TO ogg;

授权成功。

 

SQL> GRANT ALTER SESSION TO ogg;

授权成功。

SQL> GRANT CREATE SESSION TO ogg;

授权成功。

SQL> GRANT FLASHBACK ANY TABLE TO ogg;

授权成功。

SQL> GRANT SELECT ANY DICTIONARY TO ogg;

授权成功。

SQL> GRANT SELECT ANY TABLE TO ogg;

授权成功。

SQL> GRANT RESOURCE TO ogg;

授权成功。

SQL> GRANT SELECT ANY TRANSACTION TO ogg;

授权成功。

SQL> conn ogg

输入口令:

已连接。

SQL> exit

07、初始化ogg

D:\ogg>ggsci.exe

Oracle GoldenGate Command Interpreter for Oracle

Version 19.1.0.0.4 OGGCORE_19.1.0.0.0_PLATFORMS_191017.1054_FBO

Windows x64 (optimized), Oracle 11g on Oct 19 2019 11:09:03

Operating system character set identified as GBK.

Copyright (C) 1995, 2019, Oracle and/or its affiliates. All rights reserved.

GGSCI (yycs) 1> create create subdirs

08、创建源库一致的表结构

 当前为只需要增量数据，所以只需要表结构就行了，如果需要全量数据，建议使用导表结构全量导入。当前就一张表，我就手工创建。

SQL> create user test_ogg identified by test_ogg default tablespace users;

用户已创建。

SQL> grant dba to test_ogg;

授权成功。

SQL> conn test_ogg;

输入口令:

已连接。

SQL> create table test_ogg(id int,name varchar(20),primary key(id));

表已创建。

09、创建MGR进程配置文件

GGSCI (yycs as ogg@orcl) 3> dblogin userid ogg,password ogg

Successfully logged into database.

GGSCI (yycs as ogg@orcl) 12> edit param mgr

PORT 7809

DYNAMICPORTLIST 7800-7850

AUTORESTART EXTRACT *,RETRIES 5,WAITMINUTES 3

--PURGEOLDEXTRACTS ./dirdat/*,usecheckpoints,minkeepdays 3

LAGREPORTHOURS 1

LAGINFOMINUTES 30

LAGCRITICALMINUTES 45

10、创建应用进程配置文件

GGSCI (yycs as ogg@orcl) 22> add checkpointtable test_ogg.checkpoint

Successfully created checkpoint table test_ogg. checkpoint.

GGSCI (yycs as ogg@orcl) 32> edit param ./GLOBALS

checkpointtable test_ogg.checkpoint

GGSCI (yycs as ogg@orcl) 18> edit param reoracle

REPLICAT reoracle

SETENV (ORACLE_SID="ORCL")

setenv (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)

USERID ogg,PASSWORD ogg

REPORT AT 01:59

REPORTCOUNT EVERY 30 MINUTES, RATE

REPERROR DEFAULT, ABEND

DBOPTIONS DEFERREFCONST

assumetargetdefs

DISCARDFILE ./dirrpt/repyaa.dsc, APPEND, MEGABYTES 1000

DISCARDROLLOVER AT 02:30

GETTRUNCATES

ALLOWNOOPUPDATES

MAP test_ogg.test_ogg,TARGET test_ogg.test_ogg;

11、创建应用进程

GGSCI (yycs as ogg@orcl) 29> add replicat reoracle exttrail ./dirdat/oo,checkpoi

nttable test_ogg.checkpoint

REPLICAT added.

12、启动进程

  

首先启动源库进程ext、pu，然后启动目标库进程re

GSCI (hso32-db-test as ogg@hso32) 89> start EXTORA

Sending START request to MANAGER ...

EXTRACT EXTORA starting

GGSCI (hso32-db-test as ogg@hso32) 90> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           

EXTRACT     RUNNING     EXTKAFKA    00:00:00      00:00:00   

EXTRACT     RUNNING     EXTORA      68:09:11      00:00:07   

EXTRACT     RUNNING     PUKAFKA     00:00:00      00:00:00   

EXTRACT     STOPPED     PUORACLE    00:00:00      67:57:50

GGSCI (hso32-db-test as ogg@hso32) 97> start PUORACLE

Sending START request to MANAGER ...

EXTRACT PUORACLE starting

GGSCI (hso32-db-test as ogg@hso32) 98> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                          

EXTRACT     RUNNING     EXTKAFKA    00:00:00      00:00:06   

EXTRACT     RUNNING     EXTORA      00:00:00      00:00:03   

EXTRACT     RUNNING     PUKAFKA     00:00:00      00:00:03   

EXTRACT     RUNNING     PUORACLE    00:00:00      68:00:43   

GGSCI (hso32-db-test as ogg@hso32) 99>

13、测试同步性

测试：源库写入数据/目标库查看

 

SQL> insert into test_ogg values (23122,22);

 

1 row created.

 

SQL> commit;

 

Commit complete.

 

SQL>

 目标库查看 

 



这篇关于OGG 实用案例（三）-linux oracle 同步 windwos oracle的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！


相关编程文章

更多>
本站官方QQ
官方QQ:
23002807

沪ICP备2023033072号-1

     

扫一扫关注最新编程教程