Home > DBA > Oracle GoldenGate 11g direction

Oracle GoldenGate 11g direction

Oracle GoldenGate is great for heterogeneous environments by replicating and integrating data across different systems.

This post is about Oracle GoldenGate 11g and implementing a simple replication method. Installing and configuring GoldenGate is quite straightforward. I will not cover this just mention that the source database should be in archivelog mode, have supplemental logging enabled, another thing is to create golden gate user gg_owner. This post will show simple example how to replicate source schema to target, both located on the same Oracle database 11.2.0.2.

1. Users

SQL> CREATE USER SOURCEDB
IDENTIFIED BY …
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP;

User created.

SQL> GRANT CONNECT, RESOURCE TO SOURCEDB;

Grant succeeded.

SQL> CREATE USER TARGETDB
IDENTIFIED BY 12345678
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP;

User created.

SQL> GRANT CONNECT, RESOURCE TO TARGETDB;

Grant succeeded.

The purpose is to create replication between the two schemas. Golden Gate is installed in /oracle/gg11

2. Check existing configuration

[oracle@orclbox ~]$ cd /oracle/gg11
[oracle@orclbox gg11]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.1.1.1 OGGCORE_11.1.1_PLATFORMS_110421.2040
Linux, x64, 64bit (optimized), Oracle 11g on Apr 21 2011 22:42:14

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

GGSCI (orclbox) 1> info all

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING

GGSCI (orclbox) 2> stop manager
Manager process is required by other GGS processes.
Are you sure you want to stop it (y/n)? y

Sending STOP request to MANAGER …
Request processed.
Manager stopped.

GGSCI (orclbox) 3> info all

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     STOPPED

GGSCI (orclbox) 4> start manager

Manager started.

GGSCI (orclbox) 5> info all

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING

GGSCI (orclbox) 6> edit params mgr
PORT 7810
~
“/oracle/gg11/dirprm/mgr.prm” 1L, 10C

Manager is configured on default port 7809. In this case I prefer not to use the default one and configure manager on 7810.

3. Create extract s_extr on the source

GGSCI (orclbox) 7> add extract s_extr, tranlog, begin now

2011-06-13 WARNING OGG-01752  Cannot register EXTRACT S_EXTR with database because no database login was provided. You can manually register this group later with the REGISTER EXTRACT <GROUP NAME> LOGRETENTION command. Issue DBLOGIN first.
EXTRACT added.

GGSCI (orclbox) 8> add exttrail /oracle/gg11/dirdat/lt, extract s_extr
EXTTRAIL added.

GGSCI (orclbox) 9> edit params s_extr

extract s_extr
–connection to database
userid gg_owner, password
TRANLOGOPTIONS DBLOGREADER
rmthost orclbox, mgrport 7810
rmttrail /oracle/gg11/dirdat/lt
ddl include mapped objname sourcedb.*;
table sourcedb.*;
~
“/oracle/gg11/dirprm/s_extr.prm”

GGSCI (orclbox)>add exttrail /oracle/gg11/dirdat/lt, extract s_extr

GGSCI (orclbox) 10> info all

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING
EXTRACT     STOPPED     S_EXTR      00:00:00      00:10:38

4. First add checkpoint to the destination and then create the replicat on the destination (in this case in the same database, Oracle GoldenGate supports this)

GGSCI (orclbox) 11> edit params ./GLOBAL

GGSCHEMA gg_owner
CHECKPOINTTABLE gg_owner.checkpoint

GGSCI (orclbox) 12>dblogin userid gg_owner

Password:
Successfully logged into database.

GGSCI (orclbox) 13>add checkpointtable gg_owner.checkpoint

GGSCI (orclbox) 14> add replicat t_rep, exttrail /oracle/gg11/dirdat/lt,checkpointtable gg_owner.checkpoint
REPLICAT added.

GGSCI (orclbox) 15>edit params t_rep

replicat t_rep
ASSUMETARGETDEFS
–database login
userid gg_owner, password …
discardfile /oracle/gg11/discard/t_rep_disc.dat, append, megabytes 10
–ddl support
DDL
–table mapping
map sourcedb.*, target targetdb.*;

5. Time to test extraction and replication

GGSCI (orclbox) 16> info all

Program     Status      Group       Lag

MANAGER     RUNNING
EXTRACT     STOPPED     S_EXTR      00:00:00
REPLICAT    STOPPED     T_REP       00:00:00

In case you need to investigate if something is not working, you can use ‘info PAR s_extr , detail’ where PAR can be manager, extract, replicat.

GGSCI (orclbox) 17> start extract s_extr

Sending START request to MANAGER …
EXTRACT S_EXTR starting

GGSCI (orclbox) 18> start replicat t_rep

Sending START request to MANAGER …
REPLICAT T_REP starting

GGSCI (orclbox) 19> info all

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     S_EXTR      00:01:15      00:00:07
REPLICAT    RUNNING     T_REP       00:00:00      00:00:04

6. Now let’s start some activity on the source and see the replication in action

In the source schema

SQL> create table sourcedb.sync_table( sync_id number, sync_desc varchar2(50));

Table created.

– check the target schema and see it is already there

SQL> select * from targetdb.sync_table;

no rows selected

SQL> desc targetdb.sync_table;
Name                                      Null?    Type
—————————————– ——– —————————-
SYNC_ID                                            NUMBER
SYNC_DESC                                          VARCHAR2(50)

Insert a test row in the source table and ensure it populates to the target

SQL> insert into sourcedb.sync_table( sync_id , sync_desc ) values ( 1, ‘Description1′);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from targetdb.sync_table;

SYNC_ID SYNC_DESC
———- ————————————————–
1 Description1

– add new clob column to the source table and check the replicat

SQL> alter table sourcedb.sync_table add ( sync_detail clob);

Table altered.

SQL> desc targetdb.sync_table;
Name                                          Null?    Type
———————————————————————
SYNC_ID                                                NUMBER
SYNC_DESC                                              VARCHAR2(50)
SYNC_DETAIL                                            CLOB

SQL>

– update value for clob sync_detail column

SQL> update sourcedb.sync_table set sync_detail = ‘ DETAIL1′ where sync_id = 1;

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from targetdb.sync_table;

SYNC_ID SYNC_DESC                                          SYNC_DETAIL
———- ————————————————– ————-
1 Description1                                        DETAIL1

SQL>

Great synchronization is successful and clob calue is replicated.

Now let’s go on and add a primary key on the source table and see what’s going on:

SQL> alter table sourcedb.sync_table add constraint SYNC_TABLE_PK primary key (SYNC_ID);

Table altered.

SQL> desc targetdb.sync_table;
Name                                                                             Null?    Type
——————————————————————————————————–
SYNC_ID                                                                          NOT NULL NUMBER
SYNC_DESC                                                                                 VARCHAR2(50)
SYNC_DETAIL                                                                               CLOB

SQL> select index_name from all_indexes where owner = ‘TARGETDB’ and table_name = ‘SYNC_TABLE’;

INDEX_NAME
——————————
SYNC_TABLE_PK

We can see the primary key is created in the target table as well. We see both DDL and DML effect of the replication with Oracle GoldenGate.

Cheers,

Maria

About these ads
Categories: DBA
  1. October 26, 2011 at 4:31 am | #1

    I have a question about Flashback Data Archive tables in Goldengate replication. Are these tables usually excluded in an extract like “TABLEEXCLUDE .SYS_FBA_*”?
    What is the approach for replicating and initial load of Flash Back Data Archive tables?
    Here is my problem. I exported a user schema using Oracle DataPump, imported into destination database. GoldenGate abended saying some tables dont exist on dest. I checked tables and there were about 200 tables that were not exported because they are FBDA tables and DataPump just ignores them. So, I recreated them on source with scripts.
    So, how is this done? Source and destination have their own FBDA and these tables should not be neither recreated with scripts on dest or replicated? Or they have to be replicated with contents?

    • October 26, 2011 at 10:20 am | #2

      Hi,

      Good question.
      In my opinion for such history-tracked tables there is no need to use Oracle GoldenGate. Flashback Data Archive does this and stores the transactional changes to a table over its lifetime retention period. So why to build change data capture process for this with Oracle GoldenGate. If you have FBDA configured on the source and you replicate the tables with GoldenGate and on the other hand you have FBDA configured on the target I think that should be enough.

      It would be interesting to share later how you managed to do this.

      Regards,
      Maria

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: