Home Data Tutorials Using Oracle GoldenGate

Using Oracle GoldenGate

August 2, 2013 - 12:00 am

2679

14 min read

(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:

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

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

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.

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.

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.

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.

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

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.

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.

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.

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.

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

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.

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
```

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:

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.

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.

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

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

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.

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
```
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
```
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.
```

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

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.

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:

Oracle GoldenGate 11g: Configuration for High Availability [Article]
Getting Started with Oracle GoldenGate [Article]
Oracle GoldenGate: Considerations for Designing a Solution [Article]

Top 6 Cybersecurity Books from Packt to Accelerate Your Career

Your Quick Introduction to Extended Events in Analysis Services from Blog…

Logging the history of my past SQL Saturday presentations from Blog…

Storage savings with Table Compression from Blog Posts – SQLServerCentral

Daily Coping 31 Dec 2020 from Blog Posts – SQLServerCentral

Learning Essential Linux Commands for Navigating the Shell Effectively

Exploring the Strategy Behavioral Design Pattern in Node.js

How to integrate a Medium editor in Angular 8

Implementing memory management with Golang’s garbage collector

How to create sales analysis app in Qlik Sense using DAR…

Using Oracle GoldenGate

Creating one-way replication (Simple)

Getting ready

How to do it…

How it works…

Summary

Resources for Article :

LEAVE A REPLY Cancel reply

MobilePro

datapro

Programming

Subscribe to our newsletter