Shulou(Shulou.com)06/01 Report--

1. Platform environment

Oracle:rhel6.7+Oracle11.2.0.4 ip:192.168.56.2

PostgreSQL:rhel7.2+Pg9.6.1 ip:192.168.56.25

Goldengate:Goldengate12.2.0.1 for oracle and Goldengate12.2.0.1 for PostgreSQL

2. Ogg configuration

Oracle side:

Install goldengate for oracle 11g directly

Configure environment variables

[oracle@rhel6 ogg] $vi ~ / .bash_profile# add export LD_LIBRARY_PATH=/ogg/lib:$LD_LIBRARY_PATHexport PATH=/ogg:$PATH

Configure the Oracle database

# enable Archive sys@ORCL > alter database archivelog;#Forcing loggingsys@ORCL > alter database force logging;# add minimum additional Log sys@ORCL > alter database add supplemental log data;# View result sys@ORCL > select LOG_MODE,FORCE_LOGGING,SUPPLEMENTAL_LOG_DATA_MIN from v$database LOG_MODE FORCE_LOG SUPPLEMENTAL_LOG_DATA_MI---ARCHIVELOG YES YES# create goldengate user sys@ORCL > create user goldengate identified by goldengate Sys@ORCL > grant dba to goldengate;sys@ORCL > create user zhaoxu identified by zhaoxu;sys@ORCL > grant dba to zhaoxu;# create test table zhaoxu@ORCL > create table ggtest (col1 number, col2 varchar2 (20)); Table created.zhaoxu@ORCL > alter table ggtest add constraint pk_ggtest primary key (col1); Table altered.

Configure the ogg parameter file

# configure mgrGGSCI (rhel6) 2 > edit params mgrPORT 7809AUTOSTART ER * AUTORESTART EXTRACT *, RETRIES 100WAITMINUTES 2PURGEOLDEXTRACTS. / dirdat/*,USECHECKPOINTS,MINKEEPDAYS 3LAGREPORTHOURS 1LAGINFOMINUTES 30LAGCRITICALMINUTES 45SYSLOG ERROR WARN# starts mgrGGSCI (rhel6) 3 > start mgrGGSCI (rhel6) 4 > info all Program Status Group Lag at Chkpt Time Since ChkptMANAGER RUNNING # configure extraction process parameter GGSCI (rhel6) 5 > edit params ext_empEXTRACT EXT_EMPDYNAMICRESOLUTIONSETENV (NLS_LANG= "AMERICAN_AMERICA.UTF8") SETENV (ORACLE_HOME= "/ u02/app/oracle/product/11.2.4/db1") SETENV (ORACLE_SID= "orcl") USERID goldengate,PASSWORD goldengateDISCARDFILE. / dirrpt/ext_emp.dsc,APPEND,MEGABYTES 1024EXTTRAIL. / dirdat/zxtable zhaoxu.ggtest # configure delivery process parameters GGSCI (rhel6) 6 > edit params dp_tabEXTRACT DP_TABPASSTHRURMTHOST 192.168.56.25, MGRPORT 7809, COMPRESSRMTTRAIL. / dirdat/zxtable zhaoxu.ggtest;# configuration generation definition file parameters GGSCI (rhel6) 7 > edit params defgendefsfile. / dirdef/defgen.defuserid goldengate, password goldengatetable zhaoxu.ggtest # add extraction process and transfer process GGSCI (rhel6) 8 > add extract ext_emp,tranlog,begin nowGGSCI (rhel6) 9 > add exttrail. / dirdat/zx, extract ext_emp, megabytes 200GGSCI (rhel6) 10 > add extract dp_tab, exttrailsource. / dirdat/zxGGSCI (rhel6) 11 > add rmttrail. / dirdat/zx, extract dp_tab Megabytes 200GGSCI (rhel6) 12 > info allProgram Status Group Lag at Chkpt Time Since ChkptMANAGER RUNNING EXTRACT STOPPED DP_TAB 00:00:00 00:01:43 EXTRACT STOPPED EXT_EMP 00:00:00 add log GGSCI (rhel6) 13 > dblogin userid goldengate password goldengateSuccessfully logged into database.GGSCI (rhel6) of the add table As goldengate@orcl) 14 > add trandata zhaoxu.ggtestLogging of supplemental redo data enabled for table ZHAOXU.GGTEST.TRANDATA for scheduling columns has been added on table 'ZHAOXU.GGTEST'.TRANDATA for instantiation CSN has been added on table' ZHAOXU.GGTEST'.# generate definition file [oracle@rhel6 ogg] $. / defgen paramfile. / dirprm/defgen.prm * * * * Oracle GoldenGate Table Definition Generator for Oracle Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401 Linux X64, 64bit (optimized), Oracle 11g on Dec 11 2015 21:37:21 Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved. Starting at 2016-12-08 13:45:00****Operating System Version:LinuxVersion # 1 SMP Wed Jul 1 18:23:37 EDT 2015 Release 2.6.32-573.el6.x86_64Node: rhel6Machine: x86: 64 soft limit hard limitAddress Space Size: unlimited unlimitedHeap Size: unlimited unlimitedFile Size: unlimited unlimitedCPU Time: unlimited unlimitedProcess id: 3669 * * Running with the following parameters * * * defsfile. / dirdef/defgen.defuserid goldengate Password * table zhaoxu.ggtest Retrieving definition for ZHAOXU.GGTEST.Definitions generated for 1 table in. / dirdef/defgen.def. [oracle@rhel6 ogg] $cat. / dirdef/defgen.def * +-Defgen version 5.0 Encoding UTF-8** Definitions created/modified 2016-12-08 13 Name* 45 * Field descriptions for each column entry:** 1 Name* 2 Data Type* 3 External Length* 4 Fetch Offset* 5 Scale* 6 Level* 7 Null* 8 Bump if Odd* 9 Internal Length* 10 Binary Length* 11 Table Length* 12 Most Significant DT* 13 Least Significant DT* 14 High Precision* 15 Low Precision* 16 Elementary Item* 17 Occurs* 18 Key Column* 19 Sub Data Type* 20 Native Data Type* 21 Character Set* 22 Character Length* 23 LOB Type* 24 Partial Type*Database type: ORACLECharacter set ID: UTF-8National character set ID: UTF-16Locale: neutralCase sensitivity: 14 14 14 11 14 14 14TimeZone: GMT*Definition for Table ZHAOXU.GGTESTRecord length: 82Syskey: 0Columns: 2COL1 64 50 000 1 0 22-100 0COL2 64 20 56 0 0 10 0 20 20 000 0 0 0 01-1 000 0End of definition

PostgreSQL side:

Create databases, users, and Schema for synchronization, and create test tables

Postgres=# create database zhaoxu;postgres=# create user zhaoxu superuser password 'zhaoxu';postgres=#\ c zhaoxu zhaoxuzhaoxu=# create schema zhaoxu;CREATE SCHEMAzhaoxu=#\ dn List of schemas Name | Owner-+-public | pguser zhaoxu | zhaoxuzhaoxu=# CREATE TABLE ggtestzhaoxu-# (zhaoxu (# col1 integer NOT NULL,zhaoxu (# col2 varchar (20), zhaoxu (# CONSTRAINT pk_ggtest PRIMARY KEY (col1) zhaoxu) CREATE TABLEzhaoxu=#\ d List of relations Schema | Name | Type | Owner-+-zhaoxu | ggtest | table | zhaoxu

Decompress ogg

[pguser@rhel7 ogg] $tar-xvf ggs_Linux_x64_PostgreSQL_64bit.tar

Configure odbc data sources, and goldengate uses ODBC to connect to Postgres Database

[pguser@rhel7 ogg] $pwd/ogg [pguser@rhel7 ogg] $cat odbc.ini [ODBC Data Sources] GG_Postgres=DataDirect 9.6 PostgreSQL Wire Protocol [ODBC] IANAAppCodePage=106InstallDir=/ Ogg [GG _ Postgres] Driver=/ogg/lib/GGpsql25.soDescription=DataDirect 9.6 PostgreSQL Wire ProtocolDatabase=zhaoxuHostName=127.0.0.1PortNumber=5432 LogonID=zhaoxuPassword=zhaoxu

The alias of the ODBC is configured in [ODBC Data Sources], and the targetdb in the configuration file after GG_Postgres in this file needs to correspond to this.

[ODBC]:

IANAAppCodePage refers to the setting of the character set. if it is 4, it is ISO-8859-1. Note that this should always be the same as the character set setting of postgres. The corresponding values for different character sets can be found in the attachment.

InstallDir corresponds to the installation directory of ogg

[GG_Postgres]: the name here corresponds to the alias of the above ODBC

Driver here points to lib/GGpsql25.so under the ogg installation directory.

Description is the description

Database fill in the database name

HostName fill in the local hostname, which can be parsed.

PosrNumber is the listening port for postgres.

LogonID fill in the user name of postgres

Password fill in the password of postgres

Configure environment variables

Export LD_LIBRARY_PATH=/ogg/lib:$LD_LIBRARY_PATHexport PATH=$PATH:/oggexport ODBCINI=/ogg/odbc.ini

Configure Ogg

[pguser@rhel7 ogg] $. / ggsciOracle GoldenGate Command InterpreterVersion 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401Linux, x64, 64bit (optimized), PostgreSQL on Dec 11 2015 16:22:42Operating system character set identified as UTF-8.Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.# create directory GGSCI (rhel7) 1 > create subdirsCreating subdirectories under current directory / oggParameter files / ogg/dirprm: already existsReport files / ogg/dirrpt: createdCheckpoint files / ogg/dirchk: createdProcess status files / ogg/dirpcs: createdSQL script files / ogg/dirsql: createdDatabase definitions files / ogg/dirdef: createdExtract data files / ogg/dirdat: createdTemporary files / ogg/dirtmp: createdStdout files / ogg/dirout: created# configuration mgr process PORT 780 launch mgr process GGSCI (rhel7) 3 > start mgrManager started.GGSCI (rhel7) 4 > info allProgram Status Group Lag at Chkpt Time Since ChkptMANAGER RUNNING # fetch the definition file generated from the source side to the destination side [pguser@rhel7 ogg] $scp oracle@192.168.56.2:/ogg/dirdef/ Defgen.def / ogg/dirdef# configure replication process parameters GGSCI (rhel7) 5 > edit params rep1REPLICAT rep1SOURCEDEFS. / dirdef/defgen.defSETENV (PGCLIENTENCODING = "UTF8") SETENV (ODBCINI= "/ ogg/odbc.ini") SETENV (NLS_LANG= "AMERICAN_AMERICA.AL32UTF8") TARGETDB GG_Postgres Userid zhaoxu, password zhaoxuDISCARDFILE. / dirrpt/rep1.dscmap zhaoxu.ggtest, target zhaoxu.ggtest # add replication process GGSCI (rhel7) 6 > add replicat rep1, exttrail. / dirdat/zx NodbcheckpointGGSCI (rhel7) 7 > info allProgram Status Group Lag at Chkpt Time Since ChkptMANAGER RUNNING REPLICAT STOPPED REP1 00:00:00 00:02:29 # Test connecting to PostgreSQL database GGSCI (rhel7) 8 > dblogin sourcedb gg_postgres userid zhaoxuPassword: 2016-12-08 13:27:34 INFO OGG-03036 Database character set identified as UTF-8. Locale: en_US.2016-12-08 13:27:34 INFO OGG-03037 Session character set identified as UTF-8.Successfully logged into database.GGSCI (rhel7 as zhaoxu@gg_postgres) 9 > # if the connection is not successful, check the pg_hba.conf configuration file

3. Start the process on the source side and the target side

# GGSCI (rhel6) 16 > start * Sending START request to MANAGER... EXTRACT DP_TAB startingSending START request to MANAGER... EXTRACT EXT_EMP startingGGSCI (rhel6) 18 > info allProgram Status Group Lag at Chkpt Time Since ChkptMANAGER RUNNING EXTRACT RUNNING DP_TAB 00:00:00 00:00:12 EXTRACT RUNNING EXT_EMP 00:00:00 00:00:01 # PostgreSQL GGSCI (rhel7) 8 > start * Sending START request to MANAGER... REPLICAT REP1 startingGGSCI (rhel7) 9 > info allProgram Status Group Lag at Chkpt Time Since ChkptMANAGER RUNNING REPLICAT RUNNING REP1 00:00:00 00:00:00

4. Test data synchronization

Test insert

# zhaoxu@ORCL > insert into ggtest values (1 Magneto Zhaoxu'); 1 row created.zhaoxu@ORCL > insert into ggtest values (2 Magi Zhaoxi'); 1 row created.zhaoxu@ORCL > insert into ggtest values (3 menagerie Sanqi'); 1 row created.zhaoxu@ORCL > commit;Commit complete.# PostgreSQL end zhaoxu=# select * from ggtest; col1 | col2-+-1 | zhaoxu 2 | luoxi 3 | sanqi (3 SQL)

Test delete

# zhaoxu@ORCL > delete from ggtest where col1=3;1 row deleted.zhaoxu@ORCL > commit;Commit complete.zhaoxu@ORCL > select * from ggtest; COL1 COL2--1 zhaoxu 2 luoxi# PostgreSQL zhaoxu=# select * from ggtest Col1 | col2-+-1 | zhaoxu 2 | luoxi (2 rows)

Test update

# zhaoxu@ORCL > update ggtest set col2 = 'sanqi' where col1=1;1 row updated.zhaoxu@ORCL > commit;Commit complete.zhaoxu@ORCL > select * from ggtest; COL1 COL2--1 sanqi 2 luoxi# PostgreSQL zhaoxu=# select * from ggtest Col1 | col2-+-2 | luoxi 1 | sanqi (2 rows)

Reference documentation:

Http://blog.csdn.net/badly9/article/details/50372003

Http://blog.csdn.net/staricqxyz/article/details/11096203

Official documents:

Http://docs.oracle.com/goldengate/c1221/gg-winux/GIPSQ/sysreq.htm#GIPSQ107

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.