Oracle GoldenGate for Oracle 11g to PostgreSQL 9.2.4 Configuration(三)
t) 11> info mgr
Manager is running (IP port ggpgt.7809).
Demo table in Oracle and PostgreSQL
As mentioned, it is a basic set up without using initial load nor a data pump process. So we only create a simple table in Oracle and Postgres to replicate data. My Oracle database has a user called postgres and this user will now own a table ggtest
[oracle@ggos ~]$ sqlplus zwc
SQL*Plus: Release 11.2.0.4.0 Production on Wed Sep 4 21:40:03 2013
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create table ggtest(col1 number,col2 varchar2(50));
Table created.
SQL> alter table ggtest add primary key(col1);
Table altered.
Then you can connect with a Postgres utility to the Postgres database and create a similar table in the public schema
[postgres@ggpgt ~]$ psql -U zwc -d repdb -h ggpgt
Password for user zwc:
psql (9.2.4)
Type "help" for help.
repdb=> create table "public"."ggtest"
repdb-> (
repdb(> "col1" integer NOT NULL,
repdb(> "col2" varchar(20),
repdb(> CONSTRAINT "PK_Col111" PRIMARY KEY ("col1")
repdb(> );
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "PK_Col111" for table "ggtest"
CREATE TABLE
repdb-> \d+ ggtest
Table "public.ggtest"
Column | Type | Modifiers | Storage | Stats target | Description
--------+-----------------------+-----------+----------+--------------+-------------
col1 | integer | not null | plain | |
col2 | character varying(20) | | extended | |
Indexes:
"PK_Col111" PRIMARY KEY, btree (col1)
Has OIDs: no
Verify Oracle DB connection
To check the connection to the Oracle database we can use the GoldenGate command interface, log into the Oracle db, list the tables we can capture and check their data types
[oracle@ggos ggs]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.7 16934304 OGGCORE_11.2.1.0.7_PLATFORMS_130709.1600.1_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Jul 18 2013 07:04:28
Copyright (C) 1995, 2013, Oracle and/or its affiliates. All rights reserved.
GGSCI (ggos) 1> dblogin userid zwc, password zwc
Successfully logged into database.
GGSCI (ggos) 2> list tables *
ZWC.GGTEST
Found 1 tables matching list criteria.
GGSCI (ggos) 3> capture tabledef zwc.ggtest
Table definitions for ZWC.GGTEST:
COL1 NUMBER NOT NULL PK
COL2 VARCHAR (50)
Verify PostgreSQL ODBC connection
To check the ODBC connection to the Postgres database we also use the GoldenGate command line tool
[postgres@ggpgt ggs]$ ./ggsci
Oracle GoldenGate Command Interpreter
Version 11.2.1.0.2 OGGCORE_11.2.1.0.2T3_PLATFORMS_120724.2205
Linux, x64, 64bit (optimized), PostgreSQL on Jul 25 2012 00:32:24
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
GGSCI (ggpgt) 1> dblogin sourcedb gg_postgres userid zwc
Password:
2013-09-04 13:56:35 INFO OGG-0303