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>
目标库查看
人生就像一滴水，非要落下才后悔！
--kingle
