Step by Step Oracle 12c Golden Gate Installation and Configuration(Replication)

This document describes how to install Oracle 12c Golden Gate  and how to configure the replication.

Prerequisites:

1. Install Linux servers (Both source and target)
2. Install 12.1.0.2 database in  both source and target servers
3. Switch the database from noarchivelog mode to archivelog mode.
4. Download 12C Golden Gate software
5. Install Goldengate software
6. Configure Goldengate Replication.

1. Install Linux servers (Both source and target): Please Click Here to find how to install Linux 6 servers  in Virtual Box.

2. Install 12.1.0.2 database in  both source and target servers: Please Click Here to find how to install 12C database.

3. Switch the database from noarchivelog mode to archivelog mode: Please Click Here to find how to change database mode from Noarchivelog to Archivelog.

4. Download 12C Golden Gate software: Please Click Here to download 12C Golden Gate software.

Source and Target databases and servers info:

Source Server and Database:

Server: Oracle Linux Server release 6.9  - 64 Bit
Database: 12.1.0.2 - Enterprise Edition
Database Name: GOLD

Target Server and Database:

Server: Oracle Linux Server release 6.9  - 64 Bit
Database: 12.1.0.2 - Enterprise Edition
Database Name: SILVER



5. Installation of Golden Gate in Source Database: 12C Golden Gate is GUI based installation and needs to be run runInstaller upon unzipping the software.

Assumptions:

1. Installed 12.1.0.2 database in both source and target servers. Oracle home for database is /u01/app/oracle/product/12.1.0/dbhome_1

2. Create directory structure for Golden Gate home(/u01/app/oracle/product/ggate_home1)

Login as oracle user and run runInstaller from downloaded software.

[oracle@server1 Disk1]$ ./runInstaller
Starting Oracle Universal Installer...

Checking Temp space: must be greater than 120 MB.   Actual 5449 MB    Passed
Checking swap space: must be greater than 150 MB.   Actual 6099 MB    Passed
Checking monitor: must be configured to display at least 256 colors.    Actual 16777216    Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2018-06-30_03-15-21PM. Please wait ...

Select "Oracle GoldenGate for Oracle database 12C(1009.0 MB) and click on "Next".

Select location which we have created for GG home.

Select database home location, port number for manager and click on "Next".

Click on "Install"

Click on "Close"

Install the same way in target server as well.

6. Golden Gate Configuration:

Agenda: Create table(Test) in source database(GOLD) and replicate it in target database(SILVER).

Step1: Create schema and create one table under the schema.

SQL> create user ggtest identified by ggtest default tablespace ggtest_data;

User created.

SQL> grant connect,resource,create  table to ggtest;

Grant succeeded.

SQL> alter user ggtest quota unlimited on ggtest_data;

User altered.

SQL> create table ggtest.test(
VAL NUMBER NOT NULL,
DESCR VARCHAR2(20),
CONSTRAINT TEST1_PK PRIMARY KEY (VAL) ENABLE);  2    3    4 

Table created.

SQL> insert into ggtest.test values(1,'Spring');

1 row created.

SQL> commit;

Commit complete.

Step 2: Create goldengate user (gguser) in both source and target databases. Create separate tablespace and assign that tablespace as default tablespace to goldengate user. Grant required privileges to capture data.

Source(GOLD):

SQL> show parameter db_name

NAME      TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name      string GOLD

SQL> create tablespace ggate_data datafile '/u02/oradata/GOLD/ggate_01.dbf' size 50m autoextend on maxsize 10G;

Tablespace created.
SQL> create  user gguser identified by gguser default tablespace ggate_data temporary tablespace temp;

User created.

SQL> alter user gguser quota unlimited on ggate_data;

User altered.

SQL> GRANT CREATE SESSION, CONNECT, RESOURCE, ALTER SYSTEM TO GGUSER;

Grant succeeded.

SQL> EXEC DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE(grantee=>'gguser', privilege_type=>'CAPTURE', grant_optional_privileges=>'*');

PL/SQL procedure successfully completed.

SQL>

Target(SILVER):

SQL> show parameter db_name

NAME      TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name      string SILVER

SQL> create tablespace ggate_data datafile '/u02/oradata/SILVER/ggate_01.dbf' size 50m autoextend on maxsize 10G;

Tablespace created.

SQL> create  user gguser identified by gguser default tablespace ggate_data temporary tablespace temp;

User created.

SQL> alter user gguser quota unlimited on ggate_data;

User altered.

SQL> GRANT CREATE SESSION, CONNECT, RESOURCE, CREATE TABLE, dba, LOCK ANY TABLE TO GGUSER;

Grant succeeded.

SQL> GRANT INSERT ANY TABLE, UPDATE ANY TABLE, DELETE ANY TABLE TO GGUSER;

Grant succeeded.

SQL> EXEC DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE(grantee=>'gguser', privilege_type=>'APPLY', grant_optional_privileges=>'*');

PL/SQL procedure successfully completed.

SQL> 

STEP 3:  Run DDL support scripts in source database(GOLD).

Go to Goldengate home location and execute below scripts.

script 1:

SQL> @marker_setup.sql

Marker setup script

You will be prompted for the name of a schema for the Oracle GoldenGate database objects.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.

Enter Oracle GoldenGate schema name:GGUSER

Marker setup table script complete, running verification script...
Please enter the name of a schema for the GoldenGate database objects:
Setting schema name to GGUSER

MARKER TABLE
-------------------------------
OK

MARKER SEQUENCE
-------------------------------
OK

Script complete.
SQL>

script 2:

SQL> @ddl_setup.sql

Oracle GoldenGate DDL Replication setup script

Verifying that current user has privileges to install DDL Replication...

You will be prompted for the name of a schema for the Oracle GoldenGate database objects.
NOTE: For an Oracle 10g source, the system recycle bin must be disabled. For Oracle 11g and later, it can be enabled.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.

Enter Oracle GoldenGate schema name:GGUSER

Working, please wait ...
Spooling to file ddl_setup_spool.txt

Checking for sessions that are holding locks on Oracle Golden Gate metadata tables ...

Check complete.

script 3:

SQL> @role_setup.sql

GGS Role setup script

This script will drop and recreate the role GGS_GGSUSER_ROLE
To use a different role name, quit this script and then edit the params.sql script to change the gg_role parameter to the preferred name. (Do not run the script.)

You will be prompted for the name of a schema for the GoldenGate database objects.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.

Enter GoldenGate schema name:GGUSER
Wrote file role_setup_set.txt

PL/SQL procedure successfully completed.


Role setup script complete

Grant this role to each user assigned to the Extract, GGSCI, and Manager processes, by using the following SQL command:

GRANT GGS_GGSUSER_ROLE TO <loggedUser>

where <loggedUser> is the user assigned to the GoldenGate processes.

SQL> GRANT GGS_GGSUSER_ROLE TO GGUSER;

Grant succeeded.

script 4:

SQL> @ddl_enable.sql

Trigger altered.


SQL>

STEP 4: Enable supplemental logging in source database(GOLD).

SQL> SELECT supplemental_log_data_min, force_logging FROM v$database;

SUPPLEME FORCE_LOGGING
-------- ---------------------------------------
NO                    NO

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

Database altered.

SQL> ALTER DATABASE FORCE LOGGING;

Database altered.

SQL> ALTER SYSTEM SWITCH LOGFILE;

System altered.

SQL> SELECT supplemental_log_data_min, force_logging FROM v$database;

SUPPLEME FORCE_LOGGING
--------         ---------------------------------------
YES             YES

STEP 5: Enable  Goldengate replication in both source and target databases.

Source(GOLD):

SQL> show parameter db_name

NAME      TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name      string GOLD
SQL> ALTER SYSTEM SET enable_goldengate_replication=TRUE;

System altered.

SQL>

Target(SILVER):

SQL> show parameter db_name

NAME      TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name      string SILVER

SQL> ALTER SYSTEM SET enable_goldengate_replication=TRUE;

System altered.

STEP 6: Add GGUSER entry in GLOBALS file.

GGSCI (server1.apps.com) 1> edit params ./GLOBALS

(It opens vi editor and add below entry and save it.)

GGSCHEMA GGUSER

STEP 7: Add entries in manager parameter file and restart manager.

GGSCI (server1.apps.com) 2> edit params mgr

(It opens vi editor and add below entries and save it.)

PORT 7809
AUTOSTART ER *
AUTORESTART ER *, RETRIES 6, WAITMINUTES 2, RESETMINUTES 30
PURGEOLDEXTRACTS ./dirdat/*, USECHECKPOINTS, MINKEEPHOURS 2

stop/start manager:

GGSCI (server1.apps.com) 4> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           

GGSCI (server1.apps.com) 5> stop mgr

Manager process is required by other GGS processes.

Are you sure you want to stop it (y/n)?y

Sending STOP request to MANAGER ...

Request processed.

Manager stopped.

GGSCI (server1.apps.com) 6> start mgr

Manager started.

GGSCI (server1.apps.com) 7> info mgr

Manager is running (IP port server1.apps.com.7809, Process ID 9308).

GGSCI (server1.apps.com) 8> 

STEP8: Create credential store in source and target databases to GGUSER to connect database with alias. 

Source:

GGSCI (server1.apps.com) 1> dblogin, userid gguser@GOLD, password gguser

Successfully logged into database.

GGSCI (server1.apps.com as gguser@GOLD) 2> add credentialstore

Credential store created in ./dircrd/.

GGSCI (server1.apps.com as gguser@GOLD) 3> alter credentialstore add user gguser@GOLD alias gguser

Password: 

Credential store in ./dircrd/ altered.

GGSCI (server1.apps.com as gguser@GOLD) 5> info credentialstore

Reading from ./dircrd/:

Default domain: OracleGoldenGate

  Alias: gguser

  Userid: gguser@GOLD

GGSCI (server1.apps.com as gguser@GOLD) 6> 

Target:

GGSCI (server2.apps.com) 1> dblogin userid gguser@SILVER, password gguser

Successfully logged into database.

GGSCI (server2.apps.com as gguser@SILVER) 2> add credentialstore

Credential store created in ./dircrd/.

GGSCI (server2.apps.com as gguser@SILVER) 3> alter credentialstore add user gguser@SILVER alias gguser

Password: 

Credential store in ./dircrd/ altered.

GGSCI (server2.apps.com as gguser@SILVER) 4> info credentialstore

Reading from ./dircrd/:

Default domain: OracleGoldenGate

  Alias: gguser

  Userid: gguser@SILVER

GGSCI (server2.apps.com as gguser@SILVER) 5>

STEP 9: Enable trandata in source database(GOLD).

GGSCI (server1.apps.com as gguser@GOLD) 6> add trandata ggtest.TEST

Logging of supplemental redo data enabled for table GGTEST.TEST.

TRANDATA for scheduling columns has been added on table 'GGTEST.TEST'.

TRANDATA for instantiation CSN has been added on table 'GGTEST.TEST'.

GGSCI (server1.apps.com as gguser@GOLD) 7> 

STEP 10: Edit the  extract(CAPTURE) parameter file in source database and add below parameters.

GGSCI (server1.apps.com as gguser@GOLD) 7> edit params CAPTURE

(It opens vi editor and add below parameters and save it.)

EXTRACT CAPTURE

SETENV (ORACLE_SID='GOLD')

SETENV (ORACLE_HOME="/u01/app/oracle/product/12.1.0/dbhome_1")

USERIDALIAS gguser

EXTTRAIL ./dirdat/st

LOGALLSUPCOLS

UPDATERECORDFORMAT COMPACT

DDL INCLUDE MAPPED OBJNAME GGTEST.TEST;

TABLE GGTEST.TEST;

STEP 11: Add extract and trail to source database.

GGSCI (server1.apps.com as gguser@GOLD) 8> add extract CAPTURE integrated tranlog ,begin now

EXTRACT (Integrated) added.

GGSCI (server1.apps.com as gguser@GOLD) 9> ADD EXTTRAIL ./dirdat/st EXTRACT CAPTURE

EXTTRAIL added.

GGSCI (server1.apps.com as gguser@GOLD) 10> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           

EXTRACT     STOPPED     CAPTURE     00:00:00      00:00:46    

STEP 12: Edit pump(PUMP) parameter file and add below entries.

(It opens vi editor, add below entries and save it.)

EXTRACT PUMP

USERIDALIAS gguser

RMTHOST 192.168.1.120 MGRPORT 7809

RMTTRAIL ./dirdat/rt

TABLE GGTEST.TEST;

STEP 13: Add extract PUMP to source database.

GGSCI (server1.apps.com as gguser@GOLD) 12> ADD EXTRACT PUMP, EXTTRAILSOURCE ./dirdat/st

EXTRACT added.

GGSCI (server1.apps.com as gguser@GOLD) 13> ADD RMTTRAIL ./dirdat/rt EXTRACT PUMP MEGABYTES 50

RMTTRAIL added.

STEP 14: Register extracts CAPTURE in source database and start CAPTURE and PUMP.

GGSCI (server1.apps.com as gguser@GOLD) 14> register extract CAPTURE database

2018-07-23 15:15:58  INFO    OGG-02003  Extract CAPTURE successfully registered with database at SCN 1708881.

GGSCI (server1.apps.com as gguser@GOLD) 15> info all             

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           

EXTRACT     STOPPED     CAPTURE     00:00:00      00:10:52    

EXTRACT     STOPPED     PUMP        00:00:00      00:04:29    

GGSCI (server1.apps.com as gguser@GOLD) 16> start extract CAPTURE

Sending START request to MANAGER ...

EXTRACT CAPTURE starting

GGSCI (server1.apps.com as gguser@GOLD) 17> start extract pump

Sending START request to MANAGER ...

EXTRACT PUMP starting

GGSCI (server1.apps.com as gguser@GOLD) 18> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           

EXTRACT     RUNNING     CAPTURE     00:00:03      00:00:05    

EXTRACT     RUNNING     PUMP        00:00:00      00:00:03  

STEP 15: Export TEST table from source database and import in target database.

Export in source:

[oracle@server1 u02]$ expdp "'/as sysdba'" tables=GGTEST.TEST directory=DUMP dumpfile=TEST.dmp logfile=expdpTEST.log

Export: Release 12.1.0.2.0 - Production on Mon Jul 23 15:22:33 2018

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

FLASHBACK automatically enabled to preserve database integrity.

Starting "SYS"."SYS_EXPORT_TABLE_01":  "/******** AS SYSDBA" tables=GGTEST.TEST directory=DUMP dumpfile=TEST.dmp logfile=expdpTEST.log 

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 64 KB

Processing object type TABLE_EXPORT/TABLE/PROCACT_INSTANCE

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX

Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER

. . exported "GGTEST"."TEST"                             5.484 KB       1 rows

Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SYS.SYS_EXPORT_TABLE_01 is:

  /u02/DUMP/TEST.dmp

Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Mon Jul 23 15:23:10 2018 elapsed 0 00:00:30

Import: Import table in target database.

[oracle@server2 DUMP]$ impdp "'/as sysdba'" tables=GGTEST.TEST directory=DUMP dumpfile=TEST.dmp logfile=impdpTEST.log

Import: Release 12.1.0.2.0 - Production on Mon Jul 23 15:34:36 2018

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

Master table "SYS"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded

Starting "SYS"."SYS_IMPORT_TABLE_01":  "/******** AS SYSDBA" tables=GGTEST.TEST directory=DUMP dumpfile=TEST.dmp logfile=impdpTEST.log 

Processing object type TABLE_EXPORT/TABLE/PROCACT_INSTANCE

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

. . imported "GGTEST"."TEST"                             5.484 KB       1 rows

Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX

Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER

Job "SYS"."SYS_IMPORT_TABLE_01" successfully completed at Mon Jul 23 15:34:55 2018 elapsed 0 00:00:17

STEP 16: Edit manager parameter file in target database(SILVER) and add below parameters.

GGSCI (server2.apps.com as gguser@SILVER) 6> edit params mgr

(It opens vi editor, add below parameters and save it.)

PORT 7809

AUTOSTART ER *

AUTORESTART ER *, RETRIES 6, WAITMINUTES 2, RESETMINUTES 30

PURGEOLDEXTRACTS ./dirdat/*, USECHECKPOINTS, MINKEEPHOURS 2

stop/start the manager.

GGSCI (server2.apps.com as gguser@SILVER) 7> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           

GGSCI (server2.apps.com as gguser@SILVER) 8> stop mgr

Manager process is required by other GGS processes.

Are you sure you want to stop it (y/n)?y

Sending STOP request to MANAGER ...

Request processed.

Manager stopped.

GGSCI (server2.apps.com as gguser@SILVER) 10> start mgr

Manager started.

                                       

GGSCI (server2.apps.com as gguser@SILVER) 12> info mgr

Manager is running (IP port server2.apps.com.7809, Process ID 10633).

STEP 17: Create globals file and add checkpoint file in target database.

GGSCI (server2.apps.com as gguser@SILVER) 13> EDIT PARAMS ./GLOBALS

(It opens vi editor, add below entry and save it.)

GGSCHEMA GGUSER

Add checkpoint table in target database.

GGSCI (server2.apps.com as gguser@SILVER) 16> ADD CHECKPOINTTABLE GGUSER.CHECKPOINTS

Successfully created checkpoint table GGUSER.CHECKPOINTS.

STEP 18: Edit replicat parameter file(APPLY) and add below entries.

GGSCI (server2.apps.com as gguser@SILVER) 17> edit params APPLY

(It opens vi editor, add below entries and save it.)

REPLICAT APPLY

SETENV (ORACLE_SID="SILVER")

SETENV (ORACLE_HOME="/u01/app/oracle/product/12.1.0/dbhome_1")

USERIDALIAS gguser

APPLYNOOPUPDATES

DBOPTIONS INTEGRATEDPARAMS(parallelism 2)

ASSUMETARGETDEFS

DISCARDFILE ./dirrpt/rsubsera.dsc, append, megabytes 500

MAP GGTEST.TEST, TARGET GGTEST.TEST ;

STEP 19: Add replicat(APPLY) to target database and start it.

GGSCI (server2.apps.com as gguser@SILVER) 18> ADD REPLICAT APPLY, integrated, EXTTRAIL ./dirdat/rt

REPLICAT (Integrated) added.

GGSCI (server2.apps.com as gguser@SILVER) 19> info all        

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           

REPLICAT    STOPPED     APPLY       00:00:00      00:00:39    

GGSCI (server2.apps.com as gguser@SILVER) 20> start replicat apply

Sending START request to MANAGER ...

REPLICAT APPLY starting

GGSCI (server2.apps.com as gguser@SILVER) 21> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           

REPLICAT    RUNNING     APPLY       00:00:00      00:00:03    

STEP 20: Insert record in test table for source database and verify table replication in target database.

Before insert data:

Source(GOLD):

SQL> show parameter db_name

NAME      TYPE VALUE

------------------------------------ ----------- ------------------------------

db_name      string GOLD

SQL> select count(*) from ggtest.test;

  COUNT(*)

----------

1

SQL> select * from ggtest.test;

       VAL    DESCR

----------     --------------------

1        Spring

Target(SILVER):

SQL> show parameter db_name

NAME      TYPE VALUE

------------------------------------ ----------- ------------------------------

db_name      string SILVER

SQL> select count(*) from ggtest.test;

  COUNT(*)

----------

1

SQL> select * from ggtest.test;

       VAL DESCR

---------- --------------------

1      Spring

After insert the record:

Source(GOLD):

SQL> insert into ggtest.test values(2,'WINTER');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from ggtest.test;

       VAL DESCR

---------- --------------------

1 Spring

2 WINTER

Verification in Target(SILVER):

SQL> show parameter db_name

NAME      TYPE VALUE

------------------------------------ ----------- ------------------------------

db_name      string SILVER

SQL> select * from ggtest.test;

       VAL DESCR

---------- --------------------

1 Spring

2 WINTER

Thanks for your patience to review the post..............