Configuration of Extract and Replicat Using Oracle GoldenGate 12c on Linux

Configuration of GoldenGate 12c on Oracle 11gR2 on Linux

PRIMARY SERVER	OGG1 – GGDB1
SECONDARY SERVER	OGG2 – GGDB2
ORACLE VERSION	Source – 11.2.0.3.0 Target – 11.2.0.4.0
ORACLE GOLDENGATE VERSION	12.1.2.0.0
OS VERSION	Oracle Linux 6 x86_64bit

Create a separate tablespace for the GoldenGate user.Before the Configuration of Oracle GoldenGate, create a user Oracle GoldenGate admin user. As we have SYS user in Oracle Database, we can have a GGADMIN user in Oracle GoldenGate to manage it.

SQL> set lines 200 pages 1000
SQL> col tablespace_name format a20
SQL> col file_name format a45
SQL> select tablespace_name,file_name from dba_data_files; TABLESPACE_NAME FILE_NAME
——————– ———————————————
USERS /vol1/ggdb1/users01.dbf
UNDOTBS1 /vol1/ggdb1/undotbs01.dbf
SYSAUX /vol1/ggdb1/sysaux01.dbf
SYSTEM /vol1/ggdb1/system01.dbf
EXAMPLE /vol2/ggdb1/example01.dbf

SQL> create tablespace tbsgg
2 datafile ‘/vol2/ggdb1/tbsgg01.dbf’ size 3G;Tablespace created.

Create the GoldenGate user ggadmin and make the tbsgg as a default tablespace to this ggadmin user.

SQL> create user ggadmin identified by oracle default tablespace tbsgg;User created.

SQL> alter user ggadmin quota unlimited on tbsgg;

User altered.

SQL> grant connect,resource,debug connect session, debug any procedure to ggadmin;Grant succeeded.

SQL> grant dba to ggadmin;

Grant succeeded.

Here I have granted the dba role to the GoldenGate Admin user ‘ggadmin’. To grant the exact role the ggadmin user please refer the below oracle documentation.,

http://docs.oracle.com/goldengate/1212/gg-winux/GIORA.pdf

NOTE: All these steps should be done on the TARGET system.

SOURCE

oracle@ogg1 ogg]$ ./ggsciOracle GoldenGate Command Interpreter for Oracle
Version 12.1.2.0.0 17185003 OGGCORE_12.1.2.0.0_PLATFORMS_130924.1316_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Sep 25 2013 00:31:13
Operating system character set identified as UTF-8.

GGSCI (ogg1.localdomain) 1> dblogin userid ggadmin, password oracle
Successfully logged into database.

During the Installation of Oracle GoldenGate 12c I haven’t checked (selected) the Start Manager option. So the Manager process seems to be stopped.,

TO CHECK STATUS OF MANAGER

INFO ALL – is a command to check the status of the Oracle GoldenGate Processes.

GSCI (ogg1.localdomain) 2> info allProgram Status Group Lag at Chkpt Time Since Chkpt

MANAGER STOPPED

MANAGER PROCESS:

Manager Process manages all the sub processes of Oracle GoldenGate. This process will be there in both the primary and secondary server.

GGSCI (ogg1.localdomain) 3> EDIT PARAMS MGR

PORT 7809

(You can specify any port number which is not used by other service)

TO START THE MANAGER

Once the Port is assigned to the MANAGER Process, we can start the manager process. Check the status of the Manager process too.

GGSCI (ogg1.localdomain) 5> start MGR
Manager started.GGSCI (ogg1.localdomain) 6> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

ON TARGET

The same steps which are done on the SOURCE should be done on the TARGET also.

[oracle@ogg2 ogg]$ ./ggsciOracle GoldenGate Command Interpreter for Oracle
Version 12.1.2.0.0 17185003 OGGCORE_12.1.2.0.0_PLATFORMS_130924.1316_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Sep 25 2013 00:31:13
Operating system character set identified as UTF-8.

GGSCI (ogg2.localdomain) 1> dblogin userid ggadmin, password oracle
Successfully logged into database.

GGSCI (ogg2.localdomain) 2> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER STOPPED

GGSCI (ogg2.localdomain) 3> edit params MGR

GGSCI (ogg2.localdomain) 4> view params MGR

PORT 7809

GGSCI (ogg2.localdomain) 5> start MGR
Manager started.

GGSCI (ogg2.localdomain) 6> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

Now We have configured the Manager Process on both sides Source and Target.

Further to this, we will continue to create and configure the EXTRACT, DATAPUMP and REPLICAT Processes.

Before Configuring the Extract Process, we should enable the Supplemental Logging on the Database level as well as GoldenGate level.,

ENABLE SUPPLEMENTAL LOGIN TO DATABASE

We should enable supplemental log to the database. Each and Every table in the database should have a primary key.

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;Database altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

ENABLE SUPPLEMENTAL LOGIN TO ALL TABLES

In Oracle GoldenGate level enable the supplemental login to all the tables or the schema.

In the earlier Oracle GoldenGate, we had option to enable supplemental login at table level. But now in the latest version, we can enable supplemental login at schema level.

Here in this example, we are going to configure DML Replication using Oracle GoldenGate for the Schema SCOTT.

To enable the supplemental login please issue the below command.,

GGSCI (ogg1.localdomain) 20> ADD SCHEMATRANDATA SCOTT2015-01-04 14:05:36 INFO OGG-01788 SCHEMATRANDATA has been added on schema SCOTT.

2015-01-04 14:05:36 INFO OGG-01976 SCHEMATRANDATA for scheduling columns has been added on schema SCOTT.

CREATING EXTRACT FOR LOCAL SERVER

Now on the Source Side, we need to configure the Extract Parameters and the Datapump Parameters.

GGSCI (ogg1.localdomain) 6> EDIT PARAMS EXT1–EXTRACT GROUPNAME
EXTRACT EXT1
–SOURCE DB GG USERNAME AND PASSWORD
USERID ggadmin, PASSWORD oracle
–SOURCE TRAIL FILE PATH AND PREFIX
EXTTRAIL /ogg/dirdat/et
–SOURCE TABLE NAMES THAT ARE TO BE REPLICATED
TABLE scott.*;

Note : Specify <local_path> for EXTTRAIL for EXT1

ADDING EXTRACT

Once the Parameters are set to the EXTRACT Process EXT1, we need to add this Extract to the GoldenGate.

GGSCI (ogg1.localdomain) 8> ADD EXTRACT EXT1, TRANLOG, BEGIN NOW
EXTRACT added.

TRANLOG – Tells the EXTRACT Process to capture the changes directly from the Database Online Redo Logfiles.

BEGIN NOW – To start the process Immediately

ADDING EXTRAIL

Trail files are created mapped / created to the EXTRACT Process EXT1. So the changed data/committed data are captured by EXTRACT Process EXT1 and written to the Trail Files. Each Trail Files are of size 5M.

To Add the trail file to the EXTRACT Process EXT1, Please issue the below command.,

GGSCI (ogg1.localdomain) 9> ADD EXTTRAIL /ogg/dirdat/et, EXTRACT EXT1, MEGABYTES 5
EXTTRAIL added.

CREATING DATAPUMP FOR REMOTE SERVER

GGSCI (ogg1.localdomain) 10> edit params DMP1–DATAPUMP PROCESS NAME
EXTRACT DMP1
–PROCESSING OPTION (PASSTHRU OR NOPASSTHRU)
PASSTHRU
–TARGET HOSTNAME,MANAGER PORT NUMBER
RMTHOST OGG2, MGRPORT 7809
–TARGET TRAIL FILE PATH AND PREFIX
RMTTRAIL /ogg/dirdat/et
–SOURCE TABLE NAME
TABLE scott.*;

Note : Specify <remote_path> for RMTTRAIL for DMP1

PASSTHRU – This Parameter ensures the data is propagated to the target host without any manipulation.

ADDING EXTRACT

GGSCI (ogg1.localdomain) 11> ADD EXTRACT DMP1, EXTTRAILSOURCE /ogg/dirdat/et
EXTRACT added.

ADDING EXTRAIL

GGSCI (ogg1.localdomain) 12> ADD RMTTRAIL /ogg/dirdat/et, EXTRACT DMP1, MEGABYTES 5
RMTTRAIL added.

BEFORE STARTING THE EXTRACT PROCESS CREATE THE REPLICAT PROCESSES IN TARGET

TARGET

CHECKPOINTTABLE

As we have controlfile in Oracle Database, Similarly we have CHECKPOINTTABLE table in Oracle GoldenGate. Each and every changes are written to this table.

In our configuration, we are creating a global checkpointtable. So to specify globally, Oracle GoldenGate has a file called GLOBALS. We have to edit this file.

GGSCI (ogg2.localdomain) 7> EDIT PARAMS ./GLOBALSCHECKPOINTTABLE ggadmin.ggschkpt

GGSCI (ogg2.localdomain) 8> ADD CHECKPOINTTABLE
ERROR: Missing checkpoint table specification.

You will get an error when adding Checkpoint table to the GoldenGate. So we should exit the GGSCI prompt and then relogin to it. Then issue the command and the checkpointtable will be added successfully.

GGSCI (ogg2.localdomain) 9> exit
[oracle@ogg2 ogg]$ ./ggsciOracle GoldenGate Command Interpreter for Oracle
Version 12.1.2.0.0 17185003 OGGCORE_12.1.2.0.0_PLATFORMS_130924.1316_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Sep 25 2013 00:31:13
Operating system character set identified as UTF-8.

GGSCI (ogg2.localdomain) 1> dblogin userid ggadmin, password oracle
Successfully logged into database.

GGSCI (ogg2.localdomain) 2> ADD CHECKPOINTTABLE

No checkpoint table specified. Using GLOBALS specification (ggadmin.ggschkpt)…

Successfully created checkpoint table ggadmin.ggschkpt.

CREATING REPLICAT

GGSCI (ogg2.localdomain) 4> EDIT PARAMS REP1–REPLICAT GROPUP NAME
REPLICAT REP1
–TARGET DB GG USERNAME AND PASSWORD
USERID ggadmin, PASSWORD oracle
–STRUCTURES OF PRIMARY AND SECONDARY ARE SAME
ASSUMETARGETDEFS
–THE DISCARDED DATA FILE
DISCARDFILE /ogg/dirrpt/REP1.DSC, PURGE
–MAPPING SOURCE AND TARGET TABLES
MAP scott.*, TARGET scott.*;

DISCARDFILE – Disacards data records that suffer an error during apply. It contains records that cannot be processed.

ADDING REPLICAT

Similar to EXTRACT Process, Trail files are created/added to REPLICAT Process also.

GGSCI (ogg2.localdomain) 4> ADD REPLICAT REP1, EXTTRAIL /ogg/dirdat/et
REPLICAT added.

Now You can Start the Extract Process(EXT1 and DMP1) in PRIMARY and followed by Replicat in SECONDARY.

SOURCE

GGSCI (ogg1.localdomain) 28> info allProgram Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING
EXTRACT STOPPED DMP1 00:00:00 00:25:57
EXTRACT STOPPED EXT1 00:00:00 00:00:02

GGSCI (ogg1.localdomain) 29> START EXTRACT EXT1

Sending START request to MANAGER …
EXTRACT EXT1 starting

GGSCI (ogg1.localdomain) 30> START EXTRACT DMP1

Sending START request to MANAGER …
EXTRACT DMP1 starting

GGSCI (ogg1.localdomain) 31> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING
EXTRACT RUNNING DMP1 00:00:00 00:26:22
EXTRACT RUNNING EXT1 00:00:38 00:00:08

TARGET

GGSCI (ogg2.localdomain) 5> info allProgram Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING
REPLICAT STOPPED REP1 00:00:00 00:18:17

GGSCI (ogg2.localdomain) 6> START REPLICAT REP1

Sending START request to MANAGER …
REPLICAT REP1 starting

GGSCI (ogg2.localdomain) 7> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING
REPLICAT RUNNING REP1 00:00:00 00:00:00

GoldenGate DML Configuration is completed successfully. Here we should consider something. A new parameter has been introduced from Oracle 11.2.0.4 specially for GoldenGate.

enable_goldengate_replication = FALSE

This parameter should be set to TRUE else, even though our processes are running fine, there will be no replication happening between the source and target databases. we will be getting the below error

ERROR OGG-02091 Operation not supported because enable_golde
ngate_replication is not set to true.

ERROR OGG-01668 PROCESS ABENDING.

In this article, our Target database is 11.2.0.4 version and we should enable this parameter.

SQL> show parameter enable_goldengate_replication

NAME TYPE VALUE
—————————————- ———– ———–
enable_goldengate_replication boolean FALSE

SQL> alter system set enable_goldengate_replication=TRUE scope=both;

System altered.

SQL> show parameter enable_goldengate_replication

NAME TYPE VALUE
—————————————- ———– ———–
enable_goldengate_replication boolean TRUE

Now let us check if the DML Operations which are done in Primary are getting replicated to Secondary or not.,

TEST CASE I – INSERT

I gonna Insert a row to the table DEPT and check if the same has been replicated to the Target Datbase.

SOURCESQL> conn scott/tiger@ggdb1
Connected.
SQL> select * from dept;

DEPTNO DNAME LOC
———- ————– ————-
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

SQL> insert into DEPT values(50,’IT’,’INDIA’);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from dept;

DEPTNO DNAME LOC
———- ————– ————-
50 IT INDIA
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

TARGET

SQL> conn scott/tiger@ggdb2
Connected.
SQL> select * from dept;

DEPTNO DNAME LOC
———- ————– ————-
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

SQL> select * from dept;

DEPTNO DNAME LOC
———- ————– ————-
50 IT INDIA
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

TEST CASE II – UPDATE

SOURCESQL> conn scott/tiger@ggdb1
Connected.
SQL> select * from dept;

DEPTNO DNAME LOC
———- ————– ————-
50 IT INDIA
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

SQL> update dept set DNAME=’RESEARCH’ where DEPTNO=50;

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from dept;

DEPTNO DNAME LOC
———- ————– ————-
50 RESEARCH INDIA
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

TARGET

SQL> conn scott/tiger@ggdb2
Connected.

Before Update

SQL> select * from dept;

DEPTNO DNAME LOC
———- ————– ————-
50 IT INDIA
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

After Update

SQL> select * from dept;

DEPTNO DNAME LOC
———- ————– ————-
50 RESEARCH INDIA
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

TEST CASE III – DELETE

SOURCESQL> conn scott/tiger@ggdb1
Connected.
SQL> select * from dept;

DEPTNO DNAME LOC
———- ————– ————-
50 RESEARCH INDIA
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

SQL> delete from dept where deptno=50;

1 row deleted.

SQL> commit;

Commit complete.

SQL> select * from dept;

DEPTNO DNAME LOC
———- ————– ————-
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

TARGET

SQL> conn scott/tiger@ggdb2
Connected.

Before Delete

SQL> select * from dept;

DEPTNO DNAME LOC
———- ————– ————-
50 RESEARCH INDIA
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

After Delete

SQL> select * from dept;

DEPTNO DNAME LOC
———- ————– ————-
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

2 Comments

arnab says:
January 25, 2024 at 2:19 pm
Thanks for the steps on configuring the various components.
Is there a parameter which allows us to club all changes to a table done within 1 DB transaction and apply on the last state to the target?
For e,g,.:
lets say Table dept has 3 updates on the same PK
update dept set DNAME=’RESEARCH’ where DEPTNO=50;
update dept set DNAME=’ENG’ where DEPTNO=50;
update dept set DNAME=’SALES’ where DEPTNO=50;
commit;
on the target side, instead of applying all the 3 updates, can it club the 3 statements and apply only update because they are part of the same database transaction?
- Veera says:
  February 25, 2024 at 11:46 am
  No, that cannot be done.

Configuration of Extract and Replicat Using Oracle GoldenGate 12c on Linux

Veera

Previous PostINSERTMISSINGUPDATES and NOINSERTMISSINGUPDATES

Next PostOGG-02024 Oracle GoldenGate Capture for Oracle, An attempt to gather information about the logmining server configuration from the Oracle database failed

2 Comments

Leave a Reply Cancel Reply

Polls

Calendar

Visitor’s Count

Subscribe For Latest Updates

Configuration of Extract and Replicat Using Oracle GoldenGate 12c on Linux

Veera

Previous PostINSERTMISSINGUPDATES and NOINSERTMISSINGUPDATES

Next PostOGG-02024 Oracle GoldenGate Capture for Oracle, An attempt to gather information about the logmining server configuration from the Oracle database failed

Related Posts

Integration of Oracle GoldenGate with Oracle Database 21c and OGG Enhancements

ADD or ALTER OGG Process with DESCRIPTION

Which PDBs are registered with Integrated Extract?

2 Comments

Leave a Reply Cancel Reply

Polls

Calendar

Visitor’s Count

Subscribe For Latest Updates