Configuration of Oracle Golden Gate 12c for Change Replication – Delta Load

Introduction

Never test or try new features on production environments.

In the blog, Configuration of Oracle Golden Gate for Initial Load, I depicted a simple example of configuring OGG for initial extract/replicat processes.

A table was created and populated on the source database and OGG was used to extract all the records from the table and replicat (load) all the records onto the target table that has the same structure as the source table.

In this blog I will cover the replication of any DML changes in the source table.

This means that if we update, insert or delete any row on the source table then the same changes will be applied in real time onto the target table.

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.

The table we are going to examine is: myuser.ogg_table

This table should exist on both source and target databases and it contains identical data.

If the table does not exist on your source or target databases then create one simple table or run the following code on source database to create the table:

Log on the source database as myuser to create the table ogg_table.

CREATE TABLE ogg_table 
(id  NUMBER
,str VARCHAR2(20)
,
);
ALTER TABLE ogg_table ADD CONSTRAINT t_pk PRIMARY KEY(id);

Run the following code to insert data on the table:

BEGIN
  FOR i IN 1..1000 LOOP
    INSERT INTO ogg_table (id,str)
    VALUES (i, DBMS_RANDOM.STRING('U',10));
    IF MOD(i, 1000) = 0 THEN
      COMMIT;
    END IF;
  END LOOP;
END;
/

Then wait for the table to be created on target database.

As we perform the final test then the source table will be altered to check the changes on target database.

B.- Configuring Oracle Golden Gate

1.- On target server we create a checkpoint table.  A checkpoint table is strongly recommended for the replicat (load) process.  The replicat uses this table in order to maintain a record of its read position in the trail thus ensuring data integrity and proper recovery.

Log on target server as oracle and start the OGG interface and create a global parameter file which will be accessed by any replicat process on target server:

$ ./ggsci

ggsci > info mgr

Manager is running (IP port tgtdb.localdomain.7809, Process ID 3846).

ggsci > edit params ./GLOBALS

ggschema ogg_user

checkpointtable ogg_user.chkptab

ggsci > dblogin userid ogg_user, password oracle

Successfully logged into database.

ggsci > add checkpointtable ogg_user.chkptab

Successfully created checkpoint table ogg_user.chkptab.

Log on the target database as ogg_user to verify the checkpoint table.

2.- On source server we create the online change extract process.

Log on souce server as oracle and start the OGG interface:

ggsci > add extract ext1, tranlog, begin now

EXTRACT added.

Now we add the trail that will be used by the replicat process on target server.  The file path below to the target server.

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

RMTTRAIL added.

Now we create the parameter file for the extract process ext1:

ggsci > edit params ext1

extract  ext1
userid   ogg_user, password oracle
rmthost  tgtdb, mgrport 7809
rmttrail /db0/oracle/product/12.1.2.1/ogg_1/dirdat/rt
table    myuser.ogg_table;

ggsci > dblogin userid ogg_user password oracle

ggsci > add trandata myuser.ogg_table allcols

We enable table supplemental logging to log primary key columns.

3.- On target server we create the online change replicat process and the parameter file.

Log on target server as oracle and start the OGG interface:

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

REPLICAT added.

ggsci > edit params rep1

replicat rep1
userid ogg_user, password oracle
assumetargetdefs
ddlerror default discard
discardfile ./rep1_discards.dsc
map myuser.ogg_table, target myuser.ogg_table, keycols(id);

4.- On source server we start the online change extract process ext1.

Log on source server as oracle and start the OGG interface:

ggsci > start extract ext1

Sending START request to MANAGER …

EXTRACT EXT1 starting

ggsci > status extract ext1

EXTRACT EXT1: RUNNING

ggsci > info extract ext1

EXTRACT    EXT1      Last Started 2018-09-03 12:32   Status RUNNING

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

Process ID           7202

Log Read Checkpoint  Oracle Redo Logs

                     2018-09-03 12:35:07  Seqno 9, RBA 9985536

                     SCN 0.2382956 (2382956)

5.- On target server we start the online change replicat process rep1.

Log on target server as oracle and start the OGG interface:

ggsci > start replicat rep1

Sending START request to MANAGER …

REPLICAT REP1 starting

ggsci > info replicat rep1

REPLICAT   REP1      Initialized   2018-09-03 12:26   Status STOPPED

Checkpoint Lag       00:00:00 (updated 00:13:22 ago)

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

                     First Record  RBA 0

Here we see status STOPPED which is not a good sign.  After checking out the file: ggserr.log I see this:

ORA-01950: no privileges on tablespace ‘OGG_DATA’…….

The I logged on target database as sys and run:

alter user ogg_user quota unlimited on ogg_data;

Then:

ggsci > info replicat rep1

REPLICAT   REP1      Last Started 2018-09-03 13:19   Status RUNNING

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

Process ID           8190

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

                     First Record  RBA 1456

6.- At this point we have the online change processes extract ext1 on source server and replicat rep1 on target server up and running.  Remember that the table myuser.ogg_table on both source and target databases are similar.

Now we test the data extract/replicat by doing some changes on the source database:

Log on source database as user myuser and perform some changes on the table ogg_table:

INSERT INTO ogg_table (id,str) VALUES (1001,'Hello');
COMMIT;

SELECT * FROM ogg_table WHERE id = 998;
result: 998 TFSSUGZDDB

DELETE FROM ogg_table WHERE id = 998;
COMMIT;

SELECT * FROM ogg_table WHERE id = 1;
result: 1  YZXSLEPLAZ

UPDATE ogg_table SET str = 'NumberOne' WHERE id = 1;
COMMIT;

I noticed that the replicat process rep1 had status ABENDED which again is not a good sign. 

I checked out the error log and I found the following:

2018-09-03 13:37:20  QL error 1403 mapping MYUSER.OGG_TABLE to MYUSER.OGG_TABLE OCI Error ORA-01403: no data found,

Then I added the parameter handlecollisions to the parameter file rep1.

Then I altered the start time of the replicat process:

ggsci > alter replicat rep1, begin 2018-09-03 13:19:40

Then I have to wait for the replicat process to catch up the delay.

Then I remove the handlecollisions parameter from parameter file.

Then log on target database as myuser and checked the results:

SELECT * FROM ogg_table WHERE id = 1001;
id 	str
1001	Hello

SELECT * FROM ogg_table WHERE id = 998;
No rows selected

SELECT * FROM ogg_table WHERE id = 1;
result: 1  NumberOne

Feel free to try on your own tables.

Legg igjen en kommentar

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