Vishal desai’s Oracle Blog

January 10, 2014

Golden Gate conditional processing using UPDATEDELETES, TOKENS and FILTER

Filed under: Golden Gate, Replication — vishaldesai @ 4:01 pm

Client had two databases (source and target) and wanted to replicate data as follows:

All transactions generated by OLTP user needs to be replicated (as is) in near real time to target database. ARCHIVER user runs batch operations and deletes data from source database but on Target database it should convert ARCHIVER deletes into updates and there should be column indicator on target database to identify records that were deleted by ARCHIVER user on source database. Eventually these records will be deleted from Target database once its consumed by queue processor.

Test Case:

For demo purpose my Target database is same as Source database.

Source Database Target Database
APPSCHEMA – stores application tables APPSCHEMA – stores application tables
OLTP – web user  
ARCHIVER – user for archiving old data using batch operations  

Scripts to setup demo:

Database schema and table setup:
================================

DROP USER oltp cascade;
DROP USER archiver cascade;
DROP USER appschema cascade;
DROP TABLE appschema.test;
DROP TABLE appschema.test1;

CREATE USER oltp IDENTIFIED BY oltp DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp;
CREATE USER archiver IDENTIFIED BY archiver DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp;
CREATE USER appschema IDENTIFIED BY appschema DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp;
GRANT CREATE session, RESOURCE, DBA TO oltp,archiver,appschema;
  
CREATE TABLE appschema.test
(
   id      NUMBER PRIMARY KEY,
   name    VARCHAR2(20),
   company VARCHAR2(20)
);

  
CREATE TABLE appschema.test1
(
   id          NUMBER PRIMARY KEY,
   name        VARCHAR2(20),
   company     VARCHAR2(20),
   deleted_row VARCHAR(20)
);

Source:
=======

--Add Extract
dblogin  userid ggadmin@DBTNS, password pass1234
ADD EXTRACT e_test, TRANLOG, BEGIN NOW
ADD EXTTRAIL ./dirdat/et, EXTRACT e_test

--Extract parameter file
edit params e_test

EXTRACT e_test
SETENV (NLS_LANG="AMERICAN_AMERICA.AL32UTF8")
SETENV (ORACLE_HOME = "/opt/oracle/app/11.2.0.3")
SETENV (ORACLE_SID = "DBTNS")
DBOPTIONS
USERID ggadmin, PASSWORD pass1234
EXTTRAIL ./dirdat/et
TABLE appschema.test,TOKEN(TK_DBUSER = @GETENV ("TRANSACTION" , "USERNAME"));

--Generate definition file
edit params defgen1

defsfile /opt/oracle/app/gghome/dirdef/test.def
USERID ggadmin@DBTNS, PASSWORD pass1234
table appschema.test;

defgen paramfile /opt/oracle/app/gghome/dirprm/defgen1.prm

Copy /opt/oracle/app/gghome/dirdef/test.def to target server.

Target:
=======

--Add Replicat
dblogin  userid ggadmin@DBTNS, password pass1234
ADD CHECKPOINTTABLE ggadmin.CHKPTAB
ADD REPLICAT r_test, EXTTRAIL ./dirdat/et CHECKPOINTTABLE ggadmin.CHKPTAB

--Replicat parameter file
edit params r_test

REPLICAT r_test
SETENV (NLS_LANG="AMERICAN_AMERICA.AL32UTF8")
SETENV (ORACLE_HOME = "/opt/oracle/app/11.2.0.3")
SETENV (ORACLE_SID = "DBTNS")
SOURCEDEFS /opt/oracle/app/gghome/dirdef/test.def
USERID ggadmin, PASSWORD pass1234
ALLOWDUPTARGETMAP
GETINSERTS
GETUPDATES
GETDELETES
MAP appschema.test, TARGET appschema.test1, COLMAP (USEDEFAULTS, deleted_row = " "), FILTER ( @STRFIND(@TOKEN("TK_DBUSER"),"OLTP") > 0);
IGNOREINSERTS
IGNOREUPDATES
GETDELETES
UPDATEDELETES
MAP appschema.test, TARGET appschema.test1, COLMAP (USEDEFAULTS, deleted_row = "ARCHIVER"), FILTER ( @STRFIND(@TOKEN("TK_DBUSER"),"ARCHIVER") > 0);

Basically we are telling Golden Gate to replicate transactions as is when TOKEN value has OLTP stored in it and convert deletes into updates when TOKEN value has ARCHIVER stored in it.

Testing Transactions:

-- Populate Data and verify INSERTS are replicated to Target

SQL> conn oltp/oltp
Connected.
SQL> insert into appschema.test values (20,'VISHAL','ORA');

1 row created.

SQL> insert into appschema.test values (21,'ERIC','ORA');

1 row created.

SQL> insert into appschema.test values (22,'PHIL','ORA');

1 row created.

SQL> insert into appschema.test values (23,'GARY','ORA');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from appschema.test;

        ID NAME                 COMPANY
---------- -------------------- --------------------
        20 VISHAL               ORA
        21 ERIC                 ORA
        22 PHIL                 ORA
        23 GARY                 ORA

SQL> select * from appschema.test1;

        ID NAME                 COMPANY              DELETED_ROW
---------- -------------------- -------------------- --------------------
        20 VISHAL               ORA
        21 ERIC                 ORA
        22 PHIL                 ORA
        23 GARY                 ORA

-- Run DELETE as OLTP user

SQL> conn oltp/oltp
Connected.
SQL> delete from appschema.test where id=20;

1 row deleted.

SQL> commit;

Commit complete.

SQL> select * from appschema.test  order by id;

        ID NAME                 COMPANY
---------- -------------------- --------------------
        21 ERIC                 ORA
        22 PHIL                 ORA
        23 GARY                 ORA

SQL> select * from appschema.test1 order by id;

        ID NAME                 COMPANY              DELETED_ROW
---------- -------------------- -------------------- --------------------
        21 ERIC                 ORA
        22 PHIL                 ORA
        23 GARY                 ORA

-- Run DELETE as ARCHIVER user

SQL> conn archiver/archiver
Connected.
SQL> delete from appschema.test where id=22;

1 row deleted.

SQL> commit;

Commit complete.

SQL> select * from appschema.test order by id;

        ID NAME                 COMPANY
---------- -------------------- --------------------
        21 ERIC                 ORA
        23 GARY                 ORA

SQL> select * from appschema.test1 order by id;

        ID NAME                 COMPANY              DELETED_ROW
---------- -------------------- -------------------- --------------------
        21 ERIC                 ORA
        22 PHIL                 ORA                  ARCHIVER
        23 GARY                 ORA

That concludes the demo.

P.S. FILTER (@TOKEN("TK_DBUSER")="OLTP") did not work for some reason. Probably Token was storing blank padding’s. I will update this blog when I have time to verify that. :)

February 24, 2011

Golden Gate Bidirectional Replication Oracle to SQL Server example

Filed under: Golden Gate, Replication — vishaldesai @ 3:37 am

In this blog I will try to show how to setup golden gate heterogenous bidirectional replication (Oracle 11g <-> SQL Server 2008).

Software Setup

Virtual Box 1: OEL 6.0, Oracle 11g, Golden Gate 11, Oracle Instance oralin11g
Virtual Box 2: Windows 2008 Server, SQL Server 2008, Golden Gate 11, SQL Server Instance MSSQLSERVER

Pre-requisites

Install golden gate and create sub directories.
Create golden gate schema in Oracle Database and grant required permissions.
Turn on database into archive log mode and supplemental logging.

Note: Below configuration is not from production environment. In actual setup you have to consider lot of other things such as performance, conflict resolution, test insert/update/delete, testing actual application workflows etc.

Configuration

-SQL Server Table structure.

--Run following in SQL Server Management studio.

USE [HR]
GO

/****** Object:  Table [dbo].[emp1]    Script Date: 02/22/2011 16:27:05 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [hruser].[emp1](
    [id] [smallint] NOT NULL,
    [fname] varchar(50) NOT NULL,
    [lname] varchar(50) NOT NULL,
 CONSTRAINT [PK_emp1] PRIMARY KEY CLUSTERED
(
    [id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

-- Oracle Table structure

SQL> create table vdesai.emp1
(id number not null,
fname varchar2(50),
lname varchar2(50)) tablespace users;

Table created.

- Create ODBC connection as per documentation (Reference e17805 Page 23). I have created ODBC connection named ggsqlds. Verify ODBC connection using golden gate.

GGSCI (WIN-NE9G39A65OR) 2> dblogin sourcedb ggsqlds , userid sa , password vishal
Successfully logged into database.

GGSCI (WIN-NE9G39A65OR) 3> add trandata hruser.emp1

Logging of supplemental log data is enabled for table hruser.emp1

- Generate defination files for SQL Server 2008 & Oracle.

GGSCI (WIN-NE9G39A65OR) 8> edit params defgen

defsfile C:\app\Administrator\ggssql\dirdef\emp1.def
sourcedb ggsqlds, userid sa, password vishal
table hruser.emp1;

C:\app\Administrator\ggssql>defgen paramfile C:\app\Administrator\ggssql\dirprm\defgen.prm

C:\app\Administrator\ggssql>defgen paramfile C:\app\Administrator\ggssql\dirprm\defgen.prm

***********************************************************************
         Oracle GoldenGate Table Definition Generator for ODBC
                     Version 11.1.1.0.0 Build 078
 Windows x64 (optimized), Microsoft SQL Server on Jul 28 2010 15:01:58

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

                    Starting at 2011-02-22 16:44:51
***********************************************************************

Operating System Version:
Microsoft Windows Server 2008 R2 , on x64
Version 6.1 (Build 7600: )

Process id: 1284

***********************************************************************
**            Running with the following parameters                  **
***********************************************************************
defsfile C:\app\Administrator\ggssql\dirdef\emp1.def
sourcedb ggsqlds, userid sa, password *********
table hruser.emp1;
Retrieving definition for HRUSER.EMP1

Definitions generated for 1 tables in C:\app\Administrator\ggssql\dirdef\emp1.def

GGSCI (linux6) 9> edit params defgen

defsfile /app/oracle/ggs/dirdef/emp1.def
USERID ggs_owner, PASSWORD vishal
table vdesai.emp1;

$ defgen paramfile /app/oracle/ggs/dirprm/defgen.prm

***********************************************************************
        Oracle GoldenGate Table Definition Generator for Oracle
                     Version 11.1.1.0.0 Build 078
   Linux, x64, 64bit (optimized), Oracle 11 on Jul 28 2010 13:15:49

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

                    Starting at 2011-02-23 19:54:24
***********************************************************************

Operating System Version:
Linux
Version #1 SMP Wed Feb 2 18:40:23 EST 2011, Release 2.6.32-100.28.5.el6.x86_64
Node: linux6
Machine: x86_64
                         soft limit   hard limit
Address Space Size   :    unlimited    unlimited
Heap Size            :    unlimited    unlimited
File Size            :    unlimited    unlimited
CPU Time             :    unlimited    unlimited

Process id: 2927

***********************************************************************
**            Running with the following parameters                  **
***********************************************************************
defsfile /app/oracle/ggs/dirdef/emp1.def
USERID ggs_owner, PASSWORD ******
table vdesai.emp1;
Retrieving definition for VDESAI.EMP1

2011-02-23 19:54:31  WARNING OGG-00869  No unique key is defined for table EMP1. All viable columns will be used to represent the key, but may not guarantee uniqueness.  KEYCOLS may be used to define the key.

Definitions generated for 1 tables in /app/oracle/ggs/dirdef/emp1.def

- Copy linux emp1.def to windows c:\app\Administrator\ggssql\dirdef\emp1.def. Copy windows emp1.def to linux /app/oracle/ggs/dirdef/emp1.def. Make sure not to overwrite same file on source and target.

- For SQL Server 2008 set database option and take full backup

exec sp_dboption “HR”, “trunc. log on chkpt.”, “false”
Take full backup of database

- Setup Manager & Extract on Virtual box 2 (MSQSQLSERVER)

GGSCI (WIN-NE9G39A65OR) 3> edit param ./globals

MGRSERVNAME MGRSQLSERVER

GGSCI (WIN-NE9G39A65OR) 2> edit param mgr

PORT 7809

GGSCI (WIN-NE9G39A65OR) 9> ADD EXTRACT sqlext, TRANLOG, BEGIN NOW
EXTRACT added.

GGSCI (WIN-NE9G39A65OR) 10> ADD EXTTRAIL C:\app\Administrator\ggssql\dirdat\lt,EXTRACT sqlext
EXTTRAIL added.

GGSCI (WIN-NE9G39A65OR) 11> ADD EXTRACT sqldpump, EXTTRAILSOURCE C:\app\Administrator\ggssql\dirdat\lt
EXTRACT added.

GGSCI (WIN-NE9G39A65OR) 12> edit params sqlext

EXTRACT sqlext
sourcedb ggsqlds, userid sa, password vishal
TRANLOGOPTIONS MANAGESECONDARYTRUNCATIONPOINT
EXTTRAIL C:\app\Administrator\ggssql\dirdat\lt
TABLE hruser.emp1;

GGSCI (WIN-NE9G39A65OR) 13> ADD RMTTRAIL /app/oracle/ggs/dirdat/rt, EXTRACT sqldpump
RMTTRAIL added.

GGSCI (WIN-NE9G39A65OR) 14> EDIT PARAMS sqldpump

EXTRACT sqldpump
sourcedb ggsqlds, userid sa, password vishal
RMTHOST 192.168.56.101, MGRPORT 7809
RMTTRAIL /app/oracle/ggs/dirdat/rt
PASSTHRU
TABLE hruser.emp1;

- Setup Replicat on Virtual Box 1 (oralin11g)

GGSCI (linux6) 10> ADD REPLICAT orarep, EXTTRAIL /app/oracle/ggs/dirdat/rt
REPLICAT added.

GGSCI (linux6) 11> edit params orarep

REPLICAT orarep
sourcedefs /app/oracle/ggs/dirdef/emp1.def
USERID ggs_owner, PASSWORD vishal
MAP hruser.emp1, TARGET vdesai.emp1 ;

-Setup Extract on Virtual Box 1 (oralin11g)

GGSCI (linux6) 12> ADD EXTRACT oraext, TRANLOG, BEGIN NOW
EXTRACT added.

GGSCI (linux6) 13> ADD EXTTRAIL /app/oracle/ggs/dirdat/lt, EXTRACT oraext
EXTTRAIL added.

GGSCI (linux6) 14> ADD EXTRACT oradpump, EXTTRAILSOURCE /app/oracle/ggs/dirdat/ltEXTRACT added.

GGSCI (linux6) 15> edit params oraext

EXTRACT oraext
USERID ggs_owner, PASSWORD vishal
TRANLOGOPTIONS EXCLUDEUSER ggs_owner
EXTTRAIL /app/oracle/ggs/dirdat/lt
TABLE vdesai.emp1 KEYCOLS ID;

GGSCI (linux6) 16> ADD RMTTRAIL C:\app\Administrator\ggssql\dirdat\rt, EXTRACT oradpump
RMTTRAIL added.

GGSCI (linux6) 17> EDIT PARAMS oradpump

EXTRACT oradpump
userid ggs_owner, password vishal
RMTHOST 192.168.56.103, MGRPORT 7809
RMTTRAIL C:\app\Administrator\ggssql\dirdat\rt
PASSTHRU
TABLE vdesai.emp1;

- Create checkpoint table on Virtual Box 2 (MSSQLSERVER) and setup Replicat.

GGSCI (WIN-NE9G39A65OR) 10> dblogin sourcedb ggsqlds, userid sa, password vishal
Successfully logged into database.

GGSCI (WIN-NE9G39A65OR) 11> ADD CHECKPOINTTABLE hruser.chkptab

Successfully created checkpoint table HRUSER.CHKPTAB.

GGSCI (WIN-NE9G39A65OR) 15> ADD REPLICAT sqlrep, EXTTRAIL C:\app\Administrator\ggssql\dirdat\rt CHECKPOINTTABLE HRUSER.CHKPTAB
REPLICAT added.

GGSCI (WIN-NE9G39A65OR) 16> edit params sqlrep

REPLICAT sqlrep
sourcedefs C:\app\Administrator\ggssql\dirdef\emp1.def
sourcedb ggsqlds, userid sa, password vishal
MAP vdesai.emp1, TARGET hruser.emp1;

- Start Extract, pump and Replicat processes.

GGSCI (WIN-NE9G39A65OR) 17> info all

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING
EXTRACT     STOPPED     SQLDPUMP    00:00:00      00:06:38
EXTRACT     STOPPED     SQLEXT      00:00:00      00:07:10
REPLICAT    STOPPED     SQLREP      00:00:00      00:00:59

GGSCI (WIN-NE9G39A65OR) 18> start extract sqlext

Sending START request to MANAGER ('MGRSQLSERVER') ...
EXTRACT SQLEXT starting

GGSCI (WIN-NE9G39A65OR) 19> start extract sqldpump

Sending START request to MANAGER ('MGRSQLSERVER') ...
EXTRACT SQLDPUMP starting

GGSCI (WIN-NE9G39A65OR) 20> start replicat sqlrep

Sending START request to MANAGER ('MGRSQLSERVER') ...
REPLICAT SQLREP starting

GGSCI (WIN-NE9G39A65OR) 21> info all

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     SQLDPUMP    00:00:00      00:07:02
EXTRACT     RUNNING     SQLEXT      00:00:00      00:00:05
REPLICAT    RUNNING     SQLREP      00:00:00      00:01:24

GGSCI (linux6) 21> info all

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING
EXTRACT     STOPPED     ORADPUMP    00:00:00      00:03:59
EXTRACT     STOPPED     ORAEXT      00:00:00      00:04:22
REPLICAT    STOPPED     ORAREP      00:00:00      00:05:08    

GGSCI (linux6) 22> start extract oraext

Sending START request to MANAGER ...
EXTRACT ORAEXT starting

GGSCI (linux6) 23> start extract oradpump

Sending START request to MANAGER ...
EXTRACT ORADPUMP starting

GGSCI (linux6) 24> start replicat orarep

Sending START request to MANAGER ...
REPLICAT ORAREP starting

GGSCI (linux6) 25> info all

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     ORADPUMP    00:00:00      00:04:15
EXTRACT     RUNNING     ORAEXT      00:00:00      00:00:01
REPLICAT    RUNNING     ORAREP      00:00:00      00:05:24    

- Testing

-- SQL Server

begin tran
insert into hruser.emp1 values (1,'Vishal','Desai')
commit tran

-- Oracle

SQL> select * from vdesai.emp1;

    ID FNAME        LNAME
---------- -------------------- --------------------
     1 Vishal        Desai

SQL> insert into vdesai.emp1 values (11,'John','Mako');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from vdesai.emp1;

    ID FNAME        LNAME
---------- -------------------- --------------------
    11 John         Mako
     1 Vishal        Desai

- SQL Server from SQL Management Studio.

select * from hruser.emp1

id    fname    lname
1    Vishal    Desai
11    John    Mako

Golden Gate Bidirectional Replication Oracle to Oracle example

Filed under: Golden Gate, Replication — vishaldesai @ 1:28 am

In this blog I will try to show how to setup golden gate homogeneous bidirectional replication (Oracle <-> Oracle).

Software Setup:

Virtual Box 1: OEL 6.0, Oracle 11g, Golden Gate 11, Oracle Instance oralin11g
Virtual Box 2: Windows 2008 Server, Oracle 11g, Golden Gate 11, Oracle Instance orawin11g

Pre-requisites:

Install golden gate and create sub directories.
Create golden gate schema in Oracle Database and grant required permissions.
Turn on database into archive log mode and supplemental logging.

Note: Below configuration is not from production environement. In actual setup you have to consider lot of other things such as performance, conflict resolution, test insert/update/delete, testing actual application workflows etc.

Configuration:

Setup Extract and dump trail on Virtual Box 1 (oralin11g):

GGSCI (linux6) 1> ADD EXTRACT ext1, TRANLOG, BEGIN NOW
EXTRACT added.

GGSCI (linux6) 2> ADD EXTTRAIL /app/oracle/ggs/dirdat/lt, EXTRACT ext1
EXTTRAIL added.

GGSCI (linux6) 3> ADD EXTRACT dpump, EXTTRAILSOURCE /app/oracle/ggs/dirdat/lt
EXTRACT added.

GGSCI (linux6) 4> EDIT PARAMS ext1

EXTRACT ext1
USERID ggs_owner, PASSWORD vishal
TRANLOGOPTIONS EXCLUDEUSER ggs_owner
EXTTRAIL /app/oracle/ggs/dirdat/lt
TABLE vdesai.emp;

GGSCI (linux6) 5> ADD RMTTRAIL C:\app\oracle\ggsoracle\dirdat\rt, EXTRACT dpump
RMTTRAIL added.

GGSCI (linux6) 6> EDIT PARAMS dpump

EXTRACT dpump
USERID ggs_owner, PASSWORD vishal
RMTHOST 192.168.56.103, MGRPORT 7809
RMTTRAIL C:\app\oracle\ggsoracle\dirdat\rt
PASSTHRU
TABLE vdesai.emp;

Setup Replicat trail on Virtual Box 2 (orawin11g):

GGSCI (WIN-NE9G39A65OR) 1> ADD REPLICAT rep1, EXTTRAIL C:\app\oracle\ggsoracle\dirdat\rt CHECKPOINTTABLE GGS_OWNER.CHKPTAB
REPLICAT added.

GGSCI (WIN-NE9G39A65OR) 2> EDIT PARAMS rep1

REPLICAT rep1
ASSUMETARGETDEFS
USERID ggs_owner, PASSWORD vishal
MAP vdesai.emp, TARGET vdesai.emp;

Setup Extract and dump trail on Virtual Box 2 (orawin11g)

GGSCI (WIN-NE9G39A65OR) 3> ADD EXTRACT ext2, TRANLOG, BEGIN NOW
EXTRACT added.

GGSCI (WIN-NE9G39A65OR) 4> ADD EXTTRAIL C:\app\oracle\ggsoracle\dirdat\lt, EXTRACT ext2
EXTTRAIL added.

GGSCI (WIN-NE9G39A65OR) 5> ADD EXTRACT dpump, EXTTRAILSOURCE C:\app\oracle\ggsoracle\dirdat\lt
EXTRACT added.

GGSCI (WIN-NE9G39A65OR) 6> EDIT PARAMS ext2

EXTRACT ext2
USERID ggs_owner, PASSWORD vishal
TRANLOGOPTIONS EXCLUDEUSER ggs_owner
EXTTRAIL C:\app\oracle\ggsoracle\dirdat\lt
TABLE vdesai.emp;

GGSCI (WIN-NE9G39A65OR) 7> ADD RMTTRAIL /app/oracle/ggs/dirdat/rt, EXTRACT dpump

RMTTRAIL added.

GGSCI (WIN-NE9G39A65OR) 8> EDIT PARAMS dpump

EXTRACT dpump
USERID ggs_owner, PASSWORD vishal
RMTHOST 192.168.56.101, MGRPORT 7809
RMTTRAIL /app/oracle/ggs/dirdat/rt
PASSTHRU
TABLE vdesai.emp;

Setup Replicat trail on Virtual Box 1 (oralin11g):

GGSCI (linux6) 7> ADD REPLICAT rep2, EXTTRAIL /app/oracle/ggs/dirdat/rt CHECKPOINTTABLE GGS_OWNER.CHKPTAB
REPLICAT added.

GGSCI (linux6) 8> EDIT PARAMS rep2

REPLICAT rep2
ASSUMETARGETDEFS
USERID ggs_owner, PASSWORD vishal
MAP vdesai.emp, TARGET vdesai.emp;

Start Extract & Replicate processes on Virtual Box 1 (oralin11g):

GGSCI (linux6) 46> INFO ALL

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING
EXTRACT     STOPPED     DPUMP       00:00:00      00:00:47
EXTRACT     STOPPED     EXT1        00:00:00      00:00:49
REPLICAT    STOPPED     REP2        00:00:00      00:00:45    

GGSCI (linux6) 47> START EXTRACT ext1

Sending START request to MANAGER ...
EXTRACT EXT1 starting

GGSCI (linux6) 48> START EXTRACT dpump

Sending START request to MANAGER ...
EXTRACT DPUMP starting

GGSCI (linux6) 49> START REPLICAT rep2

Sending START request to MANAGER ...
REPLICAT REP2 starting

GGSCI (linux6) 50> INFO ALL

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     DPUMP       00:00:00      00:00:02
EXTRACT     RUNNING     EXT1        00:00:00      00:00:08
REPLICAT    RUNNING     REP2        00:00:00      00:00:03    

Start Extract and Replicat processes on Virtual Box 2 (orawin11g):

GGSCI (WIN-NE9G39A65OR) 34> INFO ALL

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING
EXTRACT     STOPPED     DPUMP       00:00:00      00:01:38
EXTRACT     STOPPED     EXT2        00:00:00      00:01:40
REPLICAT    STOPPED     REP1        00:00:00      00:01:41

GGSCI (WIN-NE9G39A65OR) 35> START EXTRACT EXT2

Sending START request to MANAGER ...
EXTRACT EXT2 starting

GGSCI (WIN-NE9G39A65OR) 36> START EXTRACT dpump

Sending START request to MANAGER ...
EXTRACT DPUMP starting

GGSCI (WIN-NE9G39A65OR) 37> START REPLICAT rep1

Sending START request to MANAGER ...
REPLICAT REP1 starting

GGSCI (WIN-NE9G39A65OR) 38> INFO ALL

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     DPUMP       00:00:00      00:00:04
EXTRACT     RUNNING     EXT2        00:00:00      00:00:07
REPLICAT    RUNNING     REP1        00:00:00      00:00:07

Testing:

-- Insert into orawin11g database

SQL> select * from vdesai.emp;

no rows selected

SQL> insert into vdesai.emp values (1,'Vishal','Desai');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from vdesai.emp;

    ID FNAME    LNAME
---------- ------------ ------------
     1 Vishal    Desai

-- Verify record on oralin11g database

SQL> select * from vdesai.emp;

        ID FNAME        LNAME
---------- ------------ ------------
         1 Vishal       Desai

-- Insert into oralin11g database

SQL> select * from vdesai.emp;

        ID FNAME        LNAME
---------- ------------ ------------
         1 Vishal       Desai

SQL>
SQL> insert into vdesai.emp values (2,'Bob','Peterson');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from vdesai.emp;

        ID FNAME        LNAME
---------- ------------ ------------
         2 Bob          Peterson
         1 Vishal       Desai

-- Verify record on orawin11g database

SQL> select * from vdesai.emp; 

    ID FNAME    LNAME
---------- ------------ ------------
     2 Bob        Peterson
     1 Vishal    Desai

Oracle Golden Gate

Filed under: Golden Gate, Replication — vishaldesai @ 12:44 am

Lot of people have already blogged about Oracle Golden Gate so in this blog I will just give pointers to those links.

High level overview of Oracle Golden Gate

http://sai-oracle.blogspot.com/2010/09/what-is-oracle-goldengate.html

Quick overview and demos to setup Golden Gate

http://gavinsoorma.com/2010/02/oracle-goldengate-an-introduction/ (To start with and there are various other tutorials on Gavin’s blog)

Troubleshooting

http://deciphercorp.wordpress.com/2010/08/05/using-oracle-goldengates-logdump-utility/

While testing golden gate I ran into lot of issues and logdump utility helped me a lot to read trail files and fix issues.

Performance & Exception Handler

http://www.oracle11ggotchas.com/articles/DefiningMultipleReplicatstoIncreaseGoldenGatePerformance.htm

http://www.oracle11ggotchas.com/articles/CreatingaGoldenGateExceptionHandlertotrapandlogOracleErrors.htm

Extract Internals

http://www.pythian.com/news/7225/oracle-goldengate-extract-internals-part-i/

http://www.pythian.com/news/7459/oracle-goldengate-extract-internals-part-ii/

http://www.pythian.com/news/7617/oracle-goldengate-extract-internals-part-iii/

Oracle Golden Gate by example

http://apex.oracle.com/pls/apex/f?p=44785:24:2096163966240098::::P24_CONTENT_ID,P24_PREV_PAGE:5153,24

Upcoming Golden Gate Books

https://www.packtpub.com/oracle-goldengate-11g-implementers-guide/book

http://apress.com/book/view/9781430235668

Important Metalink notes and Documentation

1194273.1 – How to de normalize data using Golden Gate

1099523.1 – TimesTen replication using Golden Gate

http://download.oracle.com/docs/cd/E18101_01/index.htm

The Silver is the New Black Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.