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.

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *