Using Oracle GoldenGate

Exclusive offer: get 50% off this eBook here
Instant Oracle GoldenGate [Instant]

Instant Oracle GoldenGate [Instant] — Save 50%

A concise and clear step-by-step guide to get you quickly started with Oracle GoldenGate with this book and ebook

£11.99    £6.00
by Tony Bruzzese | August 2013 | Enterprise Articles Oracle

In this article by Tony Bruzzese, author of Instant Oracle Goldengate, we will learn how to create a one-way replication in OGG (Oracle GoldenGate). OGG is a comprehensive software package for enabling the replication of data in heterogeneous data environments. The product set enables high availability solutions, real-time data integration, transactional change data capture, data replication, transformations, and verification between operational and analytical enterprise systems.

(For more resources related to this topic, see here.)

Creating one-way replication (Simple)

Here we'll be utilizing the demo scripts included in the OGG software distribution to implement a basic homogenous (Oracle-to-Oracle) replication.

Getting ready

You need to ensure your Oracle database is in archivelog mode. If your database is not in archivelog mode, you won't be able to recover your database due to media corruption or user errors.

How to do it...

The steps for creating one-way replication are as follows:

  1. Check whether supplemental logging is enabled on your source database using the following command:

    SQL> select supplemental_log_data_min from v$database;

    The output of the preceding command will be as follows:

    SUPPLEME
    -----------------
    NO

  2. Enable supplemental logging using the following command:

    SQL> alter database add supplemental log data;
    SQL> select supplemental_log_data_min from v$database;

    The output of the preceding command will be as follows:

    SUPPLEME
    -----------------
    YES

  3. Let's run the demo script to create a couple of tables in the scott schema. You need to know the scott schema password, which is tiger by default. We do it using following command:

    $ cd /u01/app/oracle/gg
    $ ./ggsci
    $ sqlpus scott
    Enter password:
    SQL> @demo_ora_create.sql

    The output of the preceding command will be as follows:

    DROP TABLE tcustmer
    *
    ERROR at line 1:
    ORA-00942: table or view does not exist
    Table created.
    DROP TABLE tcustord
    *
    ERROR at line 1:
    ORA-00942: table or view does not exist
    Table created.

  4. You must add the checkpoint table, do it as follows:

    $ cd /u01/app/oracle/gg
    $ vi GLOBALS

    Add the following entry to the file:

    CheckPointTable ogg.chkpt

    Save the file and exit.

  5. Next create the checkpoint table using the following command:

    $ ./ggsci
    GGSCI> add checkpointtable
    GGSCI> info checkpointtable

    The output of the preceding command will be as follows:

    No checkpoint table specified, using GLOBALS specification (ogg.
    chkpt)...
    Checkpoint table ogg.chkpt created 2012-10-31 12:39:38.

  6. Set up the MANAGER parameter file using the following command:

    $ cd /u01/app/oracle/gg/dirprm
    $ vi mgr.prm

    Add the following lines to the file:

    PORT 7809
    DYNAMICPORTLIST 7810-7849
    AUTORESTART er *, RETRIES 6, WAITMINUTES 1, RESETMINUTES 10
    PURGEOLDEXTRACTS /u01/app/oracle/gg/dirdat/*, USECHECKPOINTS,
    MINKEEPDAYS 2

    Save the file and exit.

  7. Start the manager using the following command:

    $ cd /u01/app/oracle/gg
    $ ggsci
    GGSCI> start mgr
    GGSCI> info mgr

    The output of the preceding command will be as follows:

    GGSCI> info all
    Program Status Group Lag at Chkpt Time Since Chkpt
    MANAGER RUNNING

  8. Create a TNS entry in the database home so that the extract can connect to the Automatic Storage Management (ASM) instance, using the following command:

    $ cd $ORACLE_HOME/network/admin
    $ vi tnsnames.ora

    Add the following TNS entry:

    ASMGG =
    (DESCRIPTION =
    (ADDRESS =
    (PROTOCOL = IPC)
    (key=EXTPROC1521)
    )
    (CONNECT_DATA=
    (SID=+ASM)
    )
    )

    Save the file and exit.

  9. Create a user asmgg with the sysdba role in the ASM instance. Connect to the ASM instance as sys user using the following command:

    $ sqlplus sys/<password>@asmgg as sysasm

    The output of the preceding command will be as follows:

    SQL*Plus: Release 11.2.0.3.0 Production on Thu Nov 15 14:24:20
    2012
    Copyright (c) 1982, 2011, Oracle. All rights reserved.
    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit
    Production
    With the Automatic Storage Management option

    The user is created using the following command:

    SQL> create user asmgg identified by asmgg ;

    We will get the following output message:

    User created.

    Provide the sysdba role to the user ASMGG using the following command:

    SQL> grant sysdba to asmgg ;

    We will get the following output message:

    Grant succeeded.

  10. Let's add supplemental logging to the source tables using the following commands:

    $ cd /u01/app/oracle/gg
    $ ./ggsci
    GGSCI> add trandata scott.tcustmer

    The output will be as follows:

    Logging of supplemental redo data enabled for table SCOTT.
    TCUSTMER.

    Then type the following command:

    GGSCI> add trandata scott.tcustord

    The output message will be as follows:

    Logging of supplemental redo data enabled for table SCOTT.
    TCUSTORD.

    The next command to be executed is:

    GGSCI> info trandata scott.tcustmer

    The output message will be as follows:

    Logging of supplemental redo log data is disabled for table OGG.
    TCUSTMER.

    The next command to be used is:

    GGSCI> info trandata scott.tcustord

    The output will be as follows:

    Logging of supplemental redo log data is disabled for table OGG.
    TCUSTORD.

  11. Create the extract parameter file for data capture using the following command:

    $ cd /u01/app/oracle/gg/dirprm
    $ vi ex01sand.prm

    Add the following lines to the file:

    EXTRACT ex01sand
    SETENV (ORACLE_SID="SRC100")
    SETENV (ORACLE_HOME="/u01/app/oracle/product/11.2.0/db_1")
    SETENV (NLS_LANG="AMERICAN_AMERICA.AL32UTF8")
    USERID ogg, PASSWORD ogg
    TRANLOGOPTIONS EXCLUDEUSER ogg
    TRANLOGOPTIONS ASMUSER asmgg@ASMGG ASMPASSWORD asmgg
    -- Trail File location locally
    EXTTRAIL /u01/app/oracle/gg/dirdat/pr
    DISCARDFILE /u01/app/oracle/gg/dirrpt/ex01sand.dsc, PURGE
    DISCARDROLLOVER AT 01:00 ON SUNDAY
    TABLE SCOTT.TCUSTMER ;
    TABLE SCOTT.TCUSTORD ;

    Save the file and exit.

  12. Let's add the Extract process and start it. We do it by using the following command:

    $ cd /u01/app/oracle/gg
    $ ./ggsci
    GGSCI> add extract ex01sand tranlog begin now

    The output of the preceding command will be as follows:

    EXTRACT added.

    The following command adds the location of the trail files and size for each trail created:

    GGSCI> add exttrail /u01/app/oracle/gg/dirdat/pr extract ex01sand
    megabytes 2

    The output of the preceding command will be as follows:

    EXTTRAIL added.
    GGSCI> start ex01sand
    Sending START request to MANAGER ...
    EXTRACT EX01SAND starting
    GGSCI> info all
    Program Status Group Lag at Chkpt Time Since Chkpt
    MANAGER RUNNING
    EXTRACT RUNNING EX01SAND 00:00:00 00:00:06

  13. Next we'll create the data pump parameter file using the following command:

    $ cd /u01/app/oracle/gg/dirprm
    $ vi pp01sand.prm

    Add the following lines to the file:

    EXTRACT pp01sand
    PASSTHRU
    RMTHOST hostb MGRPORT 7820
    RMTTRAIL /u01/app/oracle/goldengate/dirdat/rp
    DISCARDFILE /u01/app/oracle/gg/dirrpt/pp01sand.dsc, PURGE
    -- Tables for transport
    TABLE SCOTT.TCUSTMER ;
    TABLE SCOTT.TCUSTORD ;

    Save the file and exit.

  14. Add the data pump process and final configuration on the source side as follows:

    GGSCI> add extract pp01sand exttrailsource /u01/app/oracle/gg/
    dirdat/pr

    The output of the preceding command will be as follows:

    EXTRACT added.

    The following command points the pump to drop the trail files to the remote location:

    GGSCI> add rmttrail /u01/app/oracle/goldengate/dirdat/rp extract
    pp01sand megabytes 2

    The output of the preceding command will be as follows:

    RMTTRAIL added

  15. Then we execute the following command:

    GGSCI> info all

    The output of the preceding command will be as follows:

    Program Status Group Lag at Chkpt Time Since Chkpt
    MANAGER RUNNING
    EXTRACT RUNNING EXPR610 00:00:00 00:00:05
    EXTRACT STOPPED PP01SAND 00:00:00 00:00:55

We're not going to start the data pump (pump) at this point since the manager does not yet exist at the target site.

Perform the following actions on the target server.

We've now completed most of our steps on the source system. We'll have to come back to the source server to start the pump a little later. Now, we'll move on to our target server where we'll have to set up the Replicat process in order to receive and apply the changes received from the source database. Perform the following actions on the target database:

  1. Create tables on the target host using the following command:

    $ cd /u01/app/oracle/goldengate
    $ sqlplus scott/tiger
    SQL> @demo_ora_create.sql

    The output of the preceding command will be as follows:

    DROP TABLE tcustmer
    *
    ERROR at line 1:
    ORA-00942: table or view does not exist
    Table created.
    DROP TABLE tcustord
    *
    ERROR at line 1:
    ORA-00942: table or view does not exist
    Table created.

  2. Let's add the checkpoint table as a global parameter using the following command:

    $ cd /u01/app/oracle/goldengate
    $ vi GLOBALS

    Add the following line to the file:

    CheckPointTable ogg.chkpt

    Save the file and exit.

  3. Create the checkpoint table using the following command:

    $ cd ..
    $ ./ggsci
    GGSCI> dblogin userid ogg password ogg
    GGSCI> add checkpointtable

    Then execute the following command:

    $ cd /u01/app/oracle/goldengate/dirprm
    $ vi mgr.prm

    Add the following lines to the file:

    PORT 7820
    DYNAMICPORTLIST 7821-7849
    AUTORESTART er *, RETRIES 6, WAITMINUTES 1, RESETMINUTES 10
    PURGEOLDEXTRACTS /u01/app/oracle/goldengate/dirdat/*,
    USECHECKPOINTS, MINKEEPFILES 2

    Save the file and exit

  4. Start the manager using the following command:

    $ cd /u01/app/oracle/goldengate
    $ ./ggsci
    GGSCI> start mgr
    GGSCI> info mgr
    GGSCI> info all

    We will get the following output:

    Program Status Group Lag at Chkpt Time Since Chkpt
    MANAGER RUNNING

  5. Edit the parameter file using the following command, now we're ready to create the replicat parameter file:

    $ cd /u01/app/oracle/goldengate/dirprm
    $ vi re01sand.prm

    Add the following lines to the file:

    REPLICAT re01sand
    SETENV (ORACLE_SID="TRG101")
    SETENV (ORACLE_HOME="/u01/app/oracle/product/11.1.0/db_1")
    SETENV (NLS_LANG = "AMERICAN_AMERICA.AL32UTF8")
    USERID ogg PASSWORD ogg
    DISCARDFILE /u01/app/oracle/goldengate/dirrpt/re01sand.dsc, APPEND
    DISCARDROLLOVER at 01:00
    ReportCount Every 30 Minutes, Rate
    REPORTROLLOVER at 01:30
    DBOPTIONS DEFERREFCONST
    ASSUMETARGETDEFS
    MAP SCOTT.TCUSTMER , TARGET SCOTT.TCUSTMER ;
    MAP SCOTT

    Save the file and exit.

  6. We now add and start the Replicat process using the following commands:

    $ cd ..

    The following extrail location must match exactly as in the pump's rmttrail location on the source server:

    $ ./ggsci
    GGSCI> add replicat re01sand exttrail /u01/app/oracle/goldengate/
    dirdat/rp checkpointtable ogg.chkpt
    GGSCI> start re01sand

    The output of the preceding command will be as follows:

    Sending START request to MANAGER ...
    REPLICAT RE01SAND starting

    Then we execute the following command:

    GGSCI> info all

    The output of the preceding command will be as follows:`

    Program Status Group Lag at Chkpt Time Since Chkpt
    MANAGER RUNNING
    REPLICAT RUNNING RE01SAND 00:00:00 00:00:01

  7. Let's go back to the source host and start the pump using the following command:

    $ cd /u01/app/oracle/gg
    $ ./ggsci
    GGSCI> start pp01sand

    The output of the preceding command will be as follows:

    Sending START request to MANAGER ...
    EXTRACT PP01SAND starting

  8. Next we use the demo insert script to add rows to source tables that should replicate to the target tables. We can do it using the following commands:

    $ cd /u01/app/oracle/gg
    $ sqlplus scott/tiger
    SQL> @demo_ora_insert

    The output of the preceding command will be as follows:

    1 row created.
    1 row created.
    1 row created.
    1 row created.
    Commit complete.

  9. To verify that the 4 rows just created have been captured at the source use the following commands:

    $ ./ggsci
    GGSC>stats ex01sand totalsonly scott.*

    The output of the preceding command will be as follows:

    Sending STATS request to EXTRACT EX01SAND ...
    Start of Statistics at 2012-11-30 20:22:37.
    Output to /u01/app/oracle/gg/dirdat/pr:
    … truncated for brevity
    *** Latest statistics since 2012-11-30 20:17:38 ***
    Total inserts 4.00
    Total updates 0.00
    Total deletes 0.00
    Total discards 0.00
    Total operations 4.00

  10. To verify if the pump has shipped to the target server use the following command:

    GGSCI> stats pp01sand totalsonly scott.*

    The output of the preceding command will be as follows:

    Sending STATS request to EXTRACT PP01SAND ...
    Start of Statistics at 2012-11-30 20:24:56.
    Output to /u01/app/oracle/goldengate/dirdat/rp:
    Cumulative totals for specified table(s):
    … cut for brevity
    *** Latest statistics since 2012-11-30 20:18:14 ***
    Total inserts 4.00
    Total updates 0.00
    Total deletes 0.00
    Total discards 0.00
    Total operations 4.00
    End of Statistics.

  11. And finally if they have been applied at the target, the next command is performed at the target server as follows:

    $ ./ggsci
    GGSCI> stats re01sand totalsonly scott.*

    The output of the preceding command will be as follows:

    Sending STATS request to REPLICAT RE01SAND ...
    Start of Statistics at 2012-11-30 20:28:01.
    Cumulative totals for specified table(s):
    ...
    *** Latest statistics since 2012-11-30 20:18:20 ***
    Total inserts 4.00
    Total updates 0.00
    Total deletes 0.00
    Total discards 0.00
    Total operations 4.00
    End of Statistics.

How it works...

Supplemental logging must be turned on at the database level and subsequently at the table level as well, for those tables you would like to replicate. For a one-way replication, this is done at the source table. There isn't a need to turn on supplemental logging at the target site, if the target site in turn is not a source to other targets or to itself.

A database user ogg is created in order to administer the OGG schema. This user is solely used for the purpose of administering OGG in the database.

Checkpoints are needed by both the source and target servers; these are structures that persist to disk as a known position in the trail file. You would start from these after an expected or unexpected shutdown of the OGG process.

The PORT parameter in the mgr.prm file specifies the port to which the MGR should bind and start listening for connection requests. If the manager is down, then connections can't be established and you'll receive TCP connection errors. The only necessary parameter required is the port number itself. Also, the PURGEOLDEXTRACT parameter is a nice way to keep your trail files to a minimum size so that they don't store indefinitely and finally run out of space in your filesystem. In this example, we're asking the manager to purge trail files and keep the files from the last two days on disk.

If your Oracle database is using an ASM instance, then OGG needs to establish a connection to the ASM instance in order to read the online-redo logs. You must ensure that you either use the sys schema or create a user (such as asmgg) with SYSDBA privileges for authentication.

Since we need a supplemental log at the table level, add trandata does precisely this

Now we'll focus on some of the EXTRACT (ex01sand) data capture parameters. For one thing, you'll notice that we need to supply the extract with credentials to the database and the ASM instance in order to scan the online-redo logs for committed transactions. The following lines tell OGG to exclude the user ogg from capture. The second tranlogoptions is how the extract authenticates to the ASM instance.

USERID ogg, PASSWORD ogg
TRANLOGOPTIONS EXCLUDEUSER ogg
TRANLOGOPTIONS ASMUSER asmgg@ASMGG ASMPASSWORD asmgg

If you're using Oracle version 10gR2 and later versions of 10gR2, or Oracle 11.2.0.2 and later, you could use the newer ASM API tranlogoptions DBLOGREADER rather than the ASMUSER. The API uses the database connection rather than connecting to the ASM instance to read the online-redo logs.

The following two lines in the extract tell the extract where to place the trail files, with a prefix of pr followed by 6 digits that increment once each file rolls over to the next file generation. The DISCARDFILE by convention has the same name as the extract but with an extension .dsc for discard. If, for any reason, OGG can't capture a transaction, it will throw the text and SQL to this file for later investigation.

EXTTRAIL /u01/app/oracle/gg/dirdat/pr
DISCARDFILE /u01/app/oracle/gg/dirrpt/ex01sand.dsc, PURGE

Tables or schemas are captured with the following syntax in the extract file:

TABLE SCOTT.TCUSTMER ;
TABLE SCOTT.TCUSTORD ;

The specification can vary and use wildcards as well. Say you want to capture the entire schema, you could specify this as TABLE SCOTT.* ;.

In the following code the first command adds the extract with the option tranlog begin now telling OGG to start capturing changes using the online-redo logs as of now. The second command tells the extract where to store the trail files with a size not exceeding 2 MB.

GGSCI> add extract ex01sand tranlog begin now
GGSCI> add exttrail /u01/app/oracle/gg/dirdat/pr extract ex01sand
megabytes 2

Now, the PUMP (data pump; pp01sand) is an optional, but highly recommended extract whose sole purpose is to perform all of the TCP/IP activity; for example, transporting the trail files to the target site. This is beneficial because we alleviate the capture process from performing any of the TCP/IP activity.

The parameters in the following snippet tell the pump to send the data as is with the PASSTHRU parameter. This is the optimal and preferred method if there isn't any data transformation along the way. The RMTHOST parameter specifies the destination host and the port to which the remote manager is listening, for example, port 7820. If the manager port is not running at the target, the destination host will refuse the connection; that is why we did not start the pump early on during our work on the source host.

PASSTHRU
RMTHOST hostb MGRPORT 7820
RMTTRAIL /u01/app/oracle/goldengate/dirdat/rp

The RMTTRAIL specifies where the trail file will be stored at the remote host with a prefix of rp followed by a 6 digit number sequentially increasing as the files roll over after a specified size has reached.

Finally, at the destination host, hostb, the Replicat process (re01sand) is the applier where the SQL is replayed in the target database. The following two lines in the parameter file specify how the Replicat knows to map source and target data as it comes in by way of the trail files:

MAP SCOTT.TCUSTMER , TARGET SCOTT.TCUSTMER ;
MAP SCOTT.TCUSTORD , TARGET SCOTT.TCUSTORD ;

The target tables don't necessarily have to be of the same schema names as in the preceding example, but they could have been applied to a different schema altogether if that was the requirement

Summary

In this article we learned about the creation of one-way replication using Oracle GoldenGate.

Resources for Article :


Further resources on this subject:


Instant Oracle GoldenGate [Instant] A concise and clear step-by-step guide to get you quickly started with Oracle GoldenGate with this book and ebook
Published: July 2013
eBook Price: £11.99
See more
Select your format and quantity:

About the Author :


Tony Bruzzese

Tony Bruzzese is a seasoned Oracle and Unix practitioner with more than 20 years of experience in architecting, troubleshooting, optimizing, and maximizing large scale clusters, highly complex systems using Oracle, UNIX, and third party technologies. He has worked in the Telecom Industry at Bell Canada, moved on to a consulting firm at CGI, and more recently in the BlackBerry Manufacturing arm of BlackBerry. Now a freelance IT consultant, he specializes in helping customers to resolve Oracle performance problems, designing disaster recovery for Oracle databases, implementing high availability solutions, and general database architecting. Tony's consulting endeavors are usually brief because he diagnoses and fixes problems quickly, generally in a few days. Tony is also Oracle 11g certified (OCP) and has maintained his certifications since Oracle 8.0.

Books From Packt


 OCA Oracle Database 11g: SQL Fundamentals I: A Real World Certification Guide ( 1ZO-051 )
OCA Oracle Database 11g: SQL Fundamentals I: A Real World Certification Guide ( 1ZO-051 )

 Oracle Business Intelligence Enterprise Edition 11g: A Hands-On Tutorial
Oracle Business Intelligence Enterprise Edition 11g: A Hands-On Tutorial

Oracle Enterprise Manager Cloud Control 12c: Managing Data Center Chaos
Oracle Enterprise Manager Cloud Control 12c: Managing Data Center Chaos

 Oracle SOA Suite 11g R1 Developer's Guide
Oracle SOA Suite 11g R1 Developer's Guide

 Oracle 11g Anti-hacker's Cookbook
Oracle 11g Anti-hacker's Cookbook

 Oracle Information Integration, Migration, and Consolidation
Oracle Information Integration, Migration, and Consolidation

 Oracle GoldenGate 11g Implementer's guide
Oracle GoldenGate 11g Implementer's guide

Oracle Goldengate 11g Complete Cookbook
Oracle Goldengate 11g Complete Cookbook



Code Download and Errata
Packt Anytime, Anywhere
Register Books
Print Upgrades
eBook Downloads
Video Support
Contact Us
Awards Voting Nominations Previous Winners
Judges Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software
Resources
Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software