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> 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
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> alter user ggadmin quota unlimited on tbsgg;
User altered.
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
Login to the GGSCI prompt
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.
Copyright (C) 1995, 2013, Oracle and/or its affiliates. All rights reserved.
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.
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.
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.
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.
Copyright (C) 1995, 2013, Oracle and/or its affiliates. All rights reserved.
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 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.,
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.
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.
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.,
EXTTRAIL added.
CREATING DATAPUMP FOR REMOTE SERVER
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
EXTRACT added.
ADDING EXTRAIL
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) 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.
[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.
Copyright (C) 1995, 2013, Oracle and/or its affiliates. All rights reserved.
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
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.
REPLICAT added.
Now You can Start the Extract Process(EXT1 and DMP1) in PRIMARY and followed by Replicat in SECONDARY.
SOURCE
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
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
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.
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.
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
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
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
Loading ...
Total Users : 1816419
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?
No, that cannot be done.