Configuration of Oracle Golden Gate 12c with Data Pump

Introduction

Never test or try new features on production environments.

In this blog I will cover another method of extract/replicat, Oracle Golden Gate Data Pump.

This method of data replication has nothing to do with the Oracle Database Data Pump: expdp/impdp.

I think it’s worth discussing the OGG data pump replication method which is highly recommended and it is a best practice.

The advantage of using OGG data pump is that the extract process write data to a local trail file and then the information is sent over to the target server on another trail file using TCP/IP.  Then in case of network failure the data reside on both the local and target trail files.

The figure below illustrates the OGG Data Pump process:

A.- Topology

  • Oracle VirtualBox 5.0.2
  • Oracle Linux 6.7
  • Oracle Database 12c – 12.1.0.2
  • Oracle Golden Gate 12c – 12.1.2.1

The source database is srcdb and the target database is stgdb.

B.- Creating an example table on source and target databases

Log on source and target databases as user myuser and create the following table:

CREATE TABLE land (land_id    VARCHAR2(10)
                  ,land_name  VARCHAR2(100)
                  ,CONSTRAINT land_pk PRIMARY KEY(land_id));

Then insert data onto table mysuer.land on source database only:

BEGIN
  FOR i IN 1..10000 LOOP
    INSERT INTO land (land_id, land_name) 
    VALUES (DBMS_RANDOM.STRING('L',7), DBMS_RANDOM.STRING('L', 15));
    COMMIT;
  END LOOP;
END; 
/

C.- Configuring Oracle Golden Gate for Data Pump Replication

1.- On source server we create the extract process.  Log on source server as oracle and start the OGG interface from OGG home.

[oracle@srcdb ogg_1]$ ./ggsci

ggsci > add extract extdp1, tranlog, begin now

EXTRACT added.

2.- On source server create a local trail file.  This trail will be used by the extract process to write data to and then it will be read by the data pump process.

ggsci > add exttrail /db0/oracle/product/12.1.2.1/ogg_1/dirdat/lt, extract extdp1

EXTTRAIL added.

3.- On source server we create the data pump group and assign to it the trail created on step 2.

ggsci > add extract dp1, exttrailsource /db0/oracle/product/12.1.2.1/ogg_1/dirdat/lt

EXTRACT added.

4.- On source server we create a parameter file for the extract process extdp1.

ggsci > edit params extdp1

extract extdp1

userid ogg_user, password oracle

exttrail /db0/oracle/product/12.1.2.1/ogg_1/dirdat/lt

table mysuer.land;

5.- On source server we add the location of the trail file on the target server and assign it to the data pump process dp1.

ggsci > add rmttrail /db0/oracle/product/12.1.2.1/ogg_1/dirdat/rt, extract dp1

RMTTRAIL added.

6.- On source server we create a parameter file for the data pump process dp1.

ggsci > edit params dp1

extract dp1

userid ogg_user, password oracle

rmthost tgtdb, mgrport 7809

rmttrail /db0/oracle/product/12.1.2.1/ogg_1/dirdat/rt

passthru

table mysuer.land;

Note the parameter passthru.  It means that the structure of database objects are identical on source and target servers and no column mapping will be done.

7.- On target server we create the replicat process.  Log on target server as oracle and start the OGG interface.

[oracle@tgtdb ogg_1]$ ./ggsci

ggsci > add replicat repdp1, exttrail /db0/oracle/product/12.1.2.1/ogg_1/dirdat/rt

REPLICAT added.

8.- On target server we create a parameter file for repdp1.

ggsci > edit params repdp1

replicat repdp1

assumetargetdefs

userid ogg_user, password oracle

map mysuer.land, target mysuer.land;

9.- At this point we have the following processes on source server:

ggsci > info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                          

EXTRACT     STOPPED     DP1          00:00:00     01:08:19   

EXTRACT     RUNNING    EXT1        00:00:00      00:00:04   

EXTRACT     STOPPED     EXTDP1   00:00:00      01:21:42

and on target server:

ggsci > info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                          

REPLICAT    RUNNING     REP1        00:00:00      00:00:01   

REPLICAT    STOPPED     REPDP1    00:00:00      00:08:02

Now we start the following processes.

On source server:

ggsci > start extract extdp1

Sending START request to MANAGER …

EXTRACT EXTDP1 starting

ggsci > start extract dp1

Sending START request to MANAGER …

EXTRACT DP1 starting

ggsci > info extract extdp1

EXTRACT    EXTDP1    Last Started 2018-09-04 14:18   Status RUNNING

Checkpoint Lag       00:00:00 (updated 00:00:07 ago)

Process ID           13305

Log Read Checkpoint  Oracle Redo Logs

                     2018-09-04 14:21:18  Seqno 10, RBA 39731712

                     SCN 0.2536350 (2536350)

ggsci >info extract dp1

EXTRACT    DP1       Last Started 2018-09-04 14:51   Status RUNNING

Checkpoint Lag       00:00:00 (updated 00:00:05 ago)

Process ID           13984

Log Read Checkpoint  File /db0/oracle/product/12.1.2.1/ogg_1/dirdat/lt000000

                     2018-09-04 13:31:12.000000  RBA 1519522

The data pump process dp1 is reading from trail file:

/db0/oracle/product/12.1.2.1/ogg_1/dirdat/lt000000

On target server:

ggsci > start replicat repdp1

Sending START request to MANAGER …

REPLICAT REPDP1 starting

ggsci > info replicat repdp1

REPLICAT   REPDP1    Last Started 2018-09-04 14:38   Status RUNNING

Checkpoint Lag       00:00:00 (updated 00:00:03 ago)

Process ID           13737

Log Read Checkpoint  File /db0/oracle/product/12.1.2.1/ogg_1/dirdat/rt000002

                     2018-09-04 10:36:25.999722  RBA 2089

Then compare the contents of table mysuer.land on both source and target servers.

Legg igjen en kommentar

Din e-postadresse vil ikke bli publisert. Obligatoriske felt er merket med *