How to Setup Oracle GoldenGate - Oracle to Oracle Extract and Replicat (step by step Classic Extract & Replicat setup)

The purpose of this document is to provide a working example and show you how to Install and setup GoldenGate Replication between two Oracle databases on Unix platforms
This guide uses examples and assumes details that you may need to modify for your environment.

Contents
------------------------
Verify Database Configuration Requirements
Install & Setup GoldenGate Software and Administrator user
Configure the Manager process
Prepare Objects for Capture
Configure Extract for Change Capture and Extract Pump
Configure Initial Data Load
Configure Delivery
Test Replication

Verify Database Configuration Requirements
==============================
Ensure that the following environment variables are set.
ORACLE_HOME
ORACLE_SID
TNS_ADMIN

Verify archive log mode is setup correctly
As SYS user execute:
archive log list

If the source database is not in archivelog mode, enable it using below steps:
alter system set log_archive_dest='LOCATION=USE_DB_RECOVERY_FILE_DEST' scope=both sid='*';
shutdown immediate
startup mount
alter database archivelog;
alter database open;


Install & Setup GoldenGate Software and Administrator user:
==================================

Download software from http://edelivery.oracle.com/
Product Pack “Oracle Fusion Middleware” and the appropriate platform
Select Oracle GoldenGate on Oracle Media Pack for the appropriate Oracle version

Place the download file into the directory you want to designate as GoldenGate home.
unzip the file and inflate using tar command:
tar -xvof fbo_ggs_Linux_x64_ora11g_64bit.tar
Add the necessary variables to your environment file and execute
export PATH=$PATH:/u01/oracle/ggshome
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/u01/oracle/ggshome
export TNS_ADMIN=$ORACLE_HOME/network/admin
export ggh=/u01/oracle/ggshome
cd $ggh
execute the ggsci interface and create the subdirectories
GGSCI (celclnx14.oracle.com) 1> create subdirs

Example tnsnames.ora
TARBR =
(DESCRIPTION =    (ADDRESS_LIST =  (ADDRESS = (PROTOCOL = TCP)(HOST = celclnx14.us.oracle.com)(PORT = 45035)) )
    (CONNECT_DATA = (SERVICE_NAME = TARBR)     )  )

TARBR2 =
(DESCRIPTION =    (ADDRESS_LIST =  (ADDRESS = (PROTOCOL = TCP)(HOST = celclnx14.us.oracle.com)(PORT = 45035)) )
    (CONNECT_DATA = (SERVICE_NAME = TARBR2)     )  )

Example listener.ora
TARBR =
(ADDRESS_LIST =
        (ADDRESS=
                (PROTOCOL=TCP)
                (HOST=celclnx14.us.oracle.com)
                (PORT=45035)
        )
)
SID_LIST_TARBR=
(SID_LIST=
    (SID_DESC =
      (ORACLE_HOME = /bugmnt10/am/celclnx14/SR3.6008695737/app/oracle/product/11.2.0.2)
      (SID_NAME = TARBR)
    )
    (SID_DESC =
      (ORACLE_HOME = /bugmnt10/am/celclnx14/SR3.6008695737/app/oracle/product/11.2.0.2)
      (SID_NAME = TARBR2)
    )
)



Create a database user and tablespace that is dedicated to Oracle GoldenGate on each site (source and target).

create tablespace ggs_data datafile 'ggs_data01.dbf' size 10m autoextend on next 10m maxsize unlimited;


--Create GG admin user on Source and Target
create user ggadmin identified by ggadmin default tablespace ggs_data temporary tablespace temp;
grant connect,resource,create session, alter session to ggadmin;
grant select any dictionary, select any table,create table to ggadmin;
grant alter any table to ggadmin;
grant execute on utl_file to ggadmin;
grant flashback any table to ggadmin;
grant execute on dbms_flashback to ggadmin;
grant insert,update,delete on target.tcustmer to ggadmin;
grant insert,update,delete on target.tcustord to ggadmin;

--Create user schemas that will be replicated
--On source database
create user source identified by source default tablespace users temporary tablespace temp;
grant connect,resource,create session to source;
grant create table to source;

--On target database
create user target identified by target default tablespace users temporary tablespace temp;
grant connect,resource,create session to target;
grant create table to target;

--Enable minimum supplemental logging (required) (on Source if uni-directional or both Source and Target if bi-directional)
alter database add supplemental log data;


--Verify connectivity between databases using TNS aliases from tnsnames.ora
While logged into Source host, sqlplus into remote database
sqlplus target/target@TARBR2
SQL> select * from global_name;
-- Ensure that the output shows the name of the remote site.

--Verify GoldenGate admin can connect
cd $ggh
Shell> ggsci
GGSCI (celclnx14.oracle.com) 2> DBLOGIN USERID ggadmin, PASSWORD ggadmin
Successfully logged into database.


Configure the Manager process
=====================
On the source system, create the Manager parameter file and specify the port it should use.
Shell> cd $ggh
Shell> ggsci

-- edit the port, userid, password
GGSCI> EDIT PARAMS MGR
-- GoldenGate Manager param file
PORT  7809

-- Save the file and quit, then start manager
GGSCI> START MGR

Manager started.

--Verify the Manager process is started:
GGSCI> INFO MGR

Manager is running (IP port celclnx14.us.oracle.com.7809).

-- Create source tables and load initial data
Using SQL*Plus, create and populate the TCUSTMER and TCUSTORD tables by running the demo_ora_create.sql and demo_ora_insert.sql files found in the install directory.
Execute the following commands on the system.
Shell> cd $ggh
Shell> sqlplus source/source
SQL> @demo_ora_create
SQL> @demo_ora_insert

Verify the results:
SQL> select count(*) from tcustmer;
SQL> select count(*) from tcustord;
SQL> exit

Prepare Objects for Capture
==================
Use existing table or create demo data from demo_ora_create.sql and demo_ora_insert.sql from the GoldenGate install directory.
Add supplemental logging using GGSCI
Shell> ggsci
GGSCI> DBLOGIN USERID ggadmin, PASSWORD ggadmin
GGSCI> ADD TRANDATA SOURCE.TCUSTMER
GGSCI> ADD TRANDATA SOURCE.TCUSTORD
Verify that supplemental logging has been turned on for these tables.
GGSCI> INFO TRANDATA SOURCE.TCUST*


Prepare the target Oracle Database
=======================
On the target system, create the Manager parameter file and specify the port it should use.
Shell> cd $ggh
Shell> ggsci
GGSCI> EDIT PARAMS MGR
edit the port
Verify the Manager process is started:
GGSCI> INFO MGR


Configure Initial Data Load
==================
Synchronize or Copy data from Source to Target.
This can be accomplished by various methods. An example for this step can be found in:
GoldenGate’s initial load can be performed from an active source database. Users and applications can access and update data while the load is running. There are different approaches for using GoldenGate Initial Load and each approach is well documented in the GoldenGate for Windows and Unix Adminstrator’s Guide.
or
Doc ID 1276058.1 - Oracle GoldenGate Best Practices: Instantiation from an Oracle Source Database
Doc ID 1311707.1  -GoldenGate (OGG) Initial Load Techniques

Example 1
=======
This example shows CTAS as sync method:
Create database links between source and target"
On source: grant privilges if necessary
create database link TARBR2 connect to target identified by target using 'TARBR2';
On target: grant privilges if necessary
create database link TARBR connect to source identified by source using 'TARBR';

Login to target user on target database and execute:
SQL> create table tcustmer as (select * from tcustmer@TARBR);

Table created.

SQL> create table tcustord as (select * from tcustord@TARBR);

Table created.


Example 2
=======
This example uses schema based datapump export/import

Get the current SCN on the source database:
SQLPLUS> select current_scn from v$database ;

SQLPLUS> create directory dumpdir as '' ;

SHELL>expdp system/password directory=dumpdir full=y schemas= parallel=4 dumpfile=ora102_%u.dmp flashback_scn=&SCN_FROM_ABOVE



Configure Extract
============
The following section we will create the following:
Extract Group
ExtTrail File
Pump and RmtTrail Trail File

--Note:  best practice, do not name GoldenGate processes ending with numerals.

--  This will be changed in future version of this document.

Configure Change Capture
==================
1. Add the Extract group
Execute the following command on the system to add an Extract group named EORA.
Shell> cd $ggh
Shell> ggsci
GGSCI> ADD EXTRACT EORA01, TRANLOG, BEGIN NOW, THREADS 1
-- NOTE: THREADS value is the number of your RAC instances

Verify the results:
GGSCI> INFO EXTRACT *
Note: Record the two characters selected for your : ______. You will need this in the next step and when you set up the Replicat.

-- Create local trail file for extract to write and Pump will read
GGSCI> ADD EXTTRAIL ./dirdat/lt, EXTRACT EORA01

Verify the results:
GGSCI> INFO EXTRACT *

2. Create the Extract parameter file on the system.

GGSCI> EDIT PARAM EORA01

-- Change Capture parameter file to capture TCUSTMER and TCUSTORD Changes
-- and edit trail file parameters

EXTRACT EORA01
SETENV (ORACLE_SID="TARBR")
SETENV (ORACLE_HOME = "/bugmnt10/am/celclnx14/SR3.6008695737/app/oracle/product/11.2.0.2")
USERID ggadmin, PASSWORD ggadmin
EXTTRAIL ./dirdat/lt
DYNAMICRESOLUTION
-- DDL INCLUDE MAPPED OBJNAME SOURCE.*  -- Optional, you will need to enable DDL replication support otherwise you will get OGG-00529
TABLE SOURCE.TCUSTMER;
TABLE SOURCE.TCUSTORD;

Note: Refer to the "Installing Oracle GoldenGate DDL support" section in Oracle Installation and Setup Guide for DDL setup instructions.
         also reference. MOS article Doc ID 1468548.1 - How To Configure Oracle Goldengate DDL Replication In an Existing GoldenGate One-Way Replication

Note: When Oracle Automatic Storage Management (ASM) is in use, the TRANLOGOPTIONS ASMUSER and ASMPASSWORD must be set in the Extract parameter file. For more information refer to the GoldenGate for Windows & UNIX Administrator and Reference manuals.


3. Define the GoldenGate Pump process and remote trail
Execute the following command on the to add the local trail that will store the changes on the source for Pump process.

GGSCI> ADD EXTRACT GGPUMP, EXTTRAILSOURCE ./dirdat/lt

GGSCI> ADD RMTTRAIL ./dirdat/rt, EXTRACT GGPUMP

-- Create a parameter file for the data pump.

GGSCI> EDIT PARAMS GGPUMP
-- Add following lines to this parameter file:
-- Note: RMTHOST is the target host name or IP.  If you need to set up HA on the target system, specify the VIP for your target host as RMTHOST, otherwise use the IP address/hostname of the target system

EXTRACT GGPUMP
RMTHOST celclnx14.oracle.com, MGRPORT 7809
RMTTRAIL ./dirdat/rt
PASSTHRU
TABLE SOURCE.*;


5. Start the capture process

GGSCI> START EXTRACT EORA01
GGSCI> START EXTRACT GGPUMP
Verify the results:
GGSCI> INFO EXTRACT EORA01, DETAIL
GGSCI> VIEW REPORT EORA01

GGSCI> INFO EXTRACT GGPUMP, DETAIL
GGSCI> VIEW REPORT GGPUMP


Configure Change Delivery Replicat
=======================
1. Create a GLOBALS file on the target system
Execute the following commands on the system.
-- Create and edit the GLOBALS parameter file to add the checkpoint table.  For more info on checkpointtable, Refer to Doc ID 965698.1 - I Need Information About The Replicat Checkpoint Table
Shell> cd $ggh
Shell> ggsci
GGSCI> EDIT PARAMS ./GLOBALS
CHECKPOINTTABLE GGADMIN.checkpointtable
GGSCHEMA GGADMIN

Note: You could name the table anything you want, but for training purposes we are using checkpointtable.
 Verify that the GLOBALS file was created in the root GoldenGate directory, and remove any file extension that was added.


2. Activate the GLOBALS parameters
For the GLOBALS configuration to take effect, you must exit the session in which the changes were made. Execute the following command to exit
GGSCI> EXIT

3. Add a Replicat checkpoint table
On the system, execute the following commands in GGSCI:
Shell> cd $ggh
Shell> ggsci
GGSCI> DBLOGIN USERID ggadmin, PASSWORD ggadmin
GGSCI> ADD CHECKPOINTTABLE ggadmin.checkpointtable

4. Add the Replicat group
Execute the following command on the system to add a delivery group named RORA.
GGSCI> ADD REPLICAT RORA01, EXTTRAIL ./dirdat/rt,CHECKPOINTTABLE ggadmin.checkpointtable
Note: Refer to your Extract set up for the correct two-character .

5. Create Replicat parameter file
Execute the following commands on the system to bring up the parameter file in the editor.
GGSCI> EDIT PARAM RORA01

Type in the following parameters
--
-- Change Delivery parameter file to apply
-- TCUSTMER and TCUSTORD Changes
--
REPLICAT RORA01
SETENV (ORACLE_SID="TARBR2")
USERID ggadmin, PASSWORD ggadmin
HANDLECOLLISIONS
ASSUMETARGETDEFS
DISCARDFILE ./dirrpt/RORA01.DSC, PURGE
MAP SOURCE.tcustmer, TARGET TARGET.tcustmer;
MAP SOURCE.tcustord, TARGET TARGET.tcustord;

-- Error   OGG-00664  OCI Error beginning session (status = 1017-ORA-01017: invalid username/password; logon denied).
-- If the above error occurs, then check to make sure Replicat param file is set to target database SID using SETENV

ALTER REPLICAT RORA01, extrba 0

6. Start the Replicat process
GGSCI> START REPLICAT RORA01
Verify the results:
GGSCI> INFO REPLICAT RORA01
GGSCI> VIEW REPORT RORA01


Test Replication
===========

Execute test update,insert, and delete operations
shell> cd $ggh
shell> sqlplus source/source
SQL> @demo_ora_misc

-- On the Source database
select * from SOURCE.tcustmer;
select * from SOURCE.tcustord;
SQL> exit

shell> ggsci
GGSCI> SEND EXTRACT EORA01, REPORT
GGSCI> VIEW REPORT EORA01

-- On the Target database, ensure data was replicated
shell> cd $ggh
shell> sqlplus target/target
select * from TARGET.tcustmer;
select * from TARGET.tcustord;
SQL> exit

shell> ggsci
GGSCI>   
GGSCI> SEND REPLICAT RORA01, REPORT
GGSCI> VIEW REPORT RORA01