Oracle Database Administration: Install and Configure GoldenGate

Synopsis

Article illustrates step by step procedure to install and configure GoldenGate. We have classified the activity in four different sections

Installation of Oracle GoldenGate Software on Linux
Prepare and Configure the Oracle Database for GoldenGate
Add and Configure Extract on Source
Add and Configure Replicat on Target
Testing for GoldenGate Replication

Installation of GoldenGate Software (Perform on both source and target servers)

Step 1:- Unzip the GoldenGate software and runinstaller from Linux prompt. To install GoldenGate for Oracle 11g database, select the 11g option and Click Next

Setp 2:- Specify GoldenGate software location where GoldenGate has to be installed, Check start Manager, Provide Oracle Database Home location and Manager port. Then Click Next

Step 3:- Verify installation details and Click on the Install

Installation progresses, keep monitoring until installation completes

Step 4:- Click Finish. Now GoldenGate software has been successfully installed

Step 5:- Go to the location where GoldenGate has been installed and go to GGSCI prompt. Run command create subdirs from GGSCI prompt

$./ggsci
GGSCI (prim.localdomain) 1> create subdirs

Creating subdirectories under current directory /u01/app/oracle/product/11.2.0/goldengate

Parameter files /u01/app/oracle/product/11.2.0/goldengate/dirprm: already exists
Report files /u01/app/oracle/product/11.2.0/goldengate/dirrpt: already exists
Checkpoint files /u01/app/oracle/product/11.2.0/goldengate/dirchk: already exists
Process status files /u01/app/oracle/product/11.2.0/goldengate/dirpcs: already exists
SQL script files /u01/app/oracle/product/11.2.0/goldengate/dirsql: already exists
Database definitions files /u01/app/oracle/product/11.2.0/goldengate/dirdef: already exists
Extract data files /u01/app/oracle/product/11.2.0/goldengate/dirdat: already exists
Temporary files /u01/app/oracle/product/11.2.0/goldengate/dirtmp: already exists
Credential store files /u01/app/oracle/product/11.2.0/goldengate/dircrd: already exists
Masterkey wallet files /u01/app/oracle/product/11.2.0/goldengate/dirwlt: already exists
Dump files /u01/app/oracle/product/11.2.0/goldengate/dirdmp: already exists

Prepare and Configure the Oracle Database for GoldenGate

Step 1:- Create tablespace for storing the GoldenGate data

SQL> create tablespace GGDATA datafile '/u01/app/oracle/oradata/GGTGT/GGDATA01.dbf' size 500m autoextend on maxsize 5g;

Tablespace created.

Step 2:- Create user, this user will be GoldenGate owner schema and make GGDATA tablespace as default for this user

SQL> create user GGOWNER identified by ggowner default tablespace GGDATA account unlock;

User created.

Step 3:- To make things simple, we are granting DBA role to GoldenGate owner user

SQL> grant dba to ggowner;

Grant succeeded.

Step 4:- To configure GoldenGate, turn off the recycle bin and restart the database. Bring it to mount state

SQL> alter system set recyclebin=off scope=spfile;

System altered.

SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.

Total System Global Area 839282688 bytes
Fixed Size 2217992 bytes
Variable Size 499124216 bytes
Database Buffers 335544320 bytes
Redo Buffers 2396160 bytes
Database mounted.

Step 5:- Enable archivelog mode as its mandatory requirement to configure GoldenGate

SQL> alter database archivelog;

Database altered.

Step 6:- Enable supplemental Logging and open the database

SQL> alter database add supplemental log data;

Database altered.

SQL> alter database open;

Database altered.

Step 7:- Run maker setup.sql scripts from GoldenGate software directory and provide GoldeGate owner schema

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:GGOWNER

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

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

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

Script complete.

Step 8:- Run DDL setup script from GoldenGate home and provide GoldenGate owner schema

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:GGOWNER

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.

Using GGOWNER as a Oracle GoldenGate schema name.

Working, please wait ...

DDL replication setup script complete, running verification script...
Please enter the name of a schema for the GoldenGate database objects:
Setting schema name to GGOWNER

CLEAR_TRACE STATUS:

Line/pos Error
---------- -----------------------------------------------------------------
No errors No errors

CREATE_TRACE STATUS:

Line/pos Error
---------- -----------------------------------------------------------------
No errors No errors

TRACE_PUT_LINE STATUS:

Line/pos Error
---------- -----------------------------------------------------------------
No errors No errors

INITIAL_SETUP STATUS:

Line/pos Error
---------- -----------------------------------------------------------------
No errors No errors

DDLVERSIONSPECIFIC PACKAGE STATUS:

Line/pos Error
---------- -----------------------------------------------------------------
No errors No errors

DDLREPLICATION PACKAGE STATUS:

Line/pos Error
---------- -----------------------------------------------------------------
No errors No errors

DDLREPLICATION PACKAGE BODY STATUS:

Line/pos Error
---------- -----------------------------------------------------------------
No errors No errors

DDL IGNORE TABLE
-----------------------------------
OK

DDL IGNORE LOG TABLE
-----------------------------------
OK

DDLAUX PACKAGE STATUS:

Line/pos Error
---------- -----------------------------------------------------------------
No errors No errors

DDLAUX PACKAGE BODY STATUS:

Line/pos Error
---------- -----------------------------------------------------------------
No errors No errors

SYS.DDLCTXINFO PACKAGE STATUS:

Line/pos Error
---------- -----------------------------------------------------------------
No errors No errors

SYS.DDLCTXINFO PACKAGE BODY STATUS:

Line/pos Error
---------- -----------------------------------------------------------------
No errors No errors

DDL HISTORY TABLE
-----------------------------------
OK

DDL HISTORY TABLE(1)
-----------------------------------
OK

DDL DUMP TABLES
-----------------------------------
OK

DDL DUMP COLUMNS
-----------------------------------
OK

DDL DUMP LOG GROUPS
-----------------------------------
OK

DDL DUMP PARTITIONS
-----------------------------------
OK

DDL DUMP PRIMARY KEYS
-----------------------------------
OK

DDL SEQUENCE
-----------------------------------
OK

GGS_TEMP_COLS
-----------------------------------
OK

GGS_TEMP_UK
-----------------------------------
OK

DDL TRIGGER CODE STATUS:

Line/pos Error
---------- -----------------------------------------------------------------
No errors No errors

DDL TRIGGER INSTALL STATUS
-----------------------------------
OK

DDL TRIGGER RUNNING STATUS
-----------------------------------
ENABLED

STAYMETADATA IN TRIGGER
-----------------------------------
OFF

DDL TRIGGER SQL TRACING
-----------------------------------
0

DDL TRIGGER TRACE LEVEL
-----------------------------------
NONE

LOCATION OF DDL TRACE FILE
------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/ggdb01/GGDB01/trace/ggs_ddl_trace.log

Analyzing installation status...

VERSION OF DDL REPLICATION
------------------------------------------------------------------------------------------------------------------------
OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401

STATUS OF DDL REPLICATION
------------------------------------------------------------------------------------------------------------------------
SUCCESSFUL installation of DDL Replication software components

Script complete.

Step 9:- Perform Role Setup from GoldenGate home, using script role_setup.sql and provide GoldenGate owner name

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:GGOWNER
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 GGOWNER;

Grant succeeded.

Add and Configure Extract Process on Source

Step 1:- Add the extract, trail files will be written to local

GGSCI (prim.localdomain) 1> add extract GGEXT01, tranlog begin now
EXTRACT added.

GGSCI (prim.localdomain) 2>add exttrail /u01/app/oracle/product/11.2.0/goldengate/dirdat/lt, extract GGEXT01
EXTTRAIL added.

Step 2:- Add extract parameter file and start the extract process

GGSCI (prim.localdomain) 3> edit params GGEXT01

EXTRACT GGEXT01
USERID GGOWNER, PASSWORD ggowner
DISCARDFILE /u01/app/oracle/product/11.2.0/goldengate/dirrpt/ggext01.dsc
EXTTRAIL /u01/app/oracle/product/11.2.0/goldengate/dirdat/lt
TABLE GGOWNER.EMPLOYEE;

GSCI (prim.localdomain) 4> start extract GGEXT01

Sending START request to MANAGER ...
EXTRACT GGEXT01 starting

GGSCI (prim.localdomain) 5> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING
EXTRACT RUNNING GGEXT01 00:00:00 00:00:00

Step 3:- Add Pump process

GGSCI (prim.localdomain) 6> add extract GGPUMP01, exttrailsource /u01/app/oracle/product/11.2.0/goldengate/dirdat/lt
EXTRACT added.

GGSCI (prim.localdomain) 7> add rmttrail /u01/app/oracle/product/11.2.0/goldengate/dirdat/rt, extract GGPUMP01
RMTTRAIL added.

Step 4:- Edit pump parameter file and start the pump process

GGSCI (prim.localdomain) 8> edit params GGPUMP01

EXTRACT GGPUMP01
PASSTHRU
RMTHOST 192.168.10.2, MGRPORT 7809
RMTTRAIL /u01/app/oracle/product/11.2.0/goldengate/dirdat/rt
TABLE GGOWNER.*;

GGSCI (prim.localdomain) 9> start extract GGPUMP01

Sending START request to MANAGER ...
EXTRACT GGPUMP01 starting

GGSCI (prim.localdomain) 10> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING
EXTRACT RUNNING GGEXT01 00:00:00 00:00:01
EXTRACT RUNNING GGPUMP01 00:00:00 00:00:02

Add and Configure Replicat on Target

Step 1:- Login to the database from GGSCI prompt and add checkpoint table

GGSCI (stby.localdomain) 1> dblogin userid ggowner, password ggowner
Successfully logged into database.

GGSCI (stby.localdomain as ggowner@GGTGT) 2> add checkpointtable ggowner.checkpoint

Successfully created checkpoint table ggowner.checkpoint.

Step 2:- Add replicat process

GGSCI (stby.localdomain) 3> add replicat GGREPL01, EXTTRAIL /u01/app/oracle/product/11.2.0/goldengate/dirdat/rt, checkpointtable ggowner.checkpoint
REPLICAT added.

Step 3:- Update the replicat parameter file

GGSCI (stby.localdomain) 4> edit params GGREPL01

REPLICAT GGREP01
ASSUMETARGETDEFS
USERID ggowner, PASSWORD ggowner
DISCARDFILE /u01/app/oracle/product/11.2.0/goldengate/dirrpt/ggrepl01.dsc,append,MEGABYTES 500
MAP GGOWNER.EMPLOYEE, TARGET GGOWNER.EMPLOYEE;

Step 4:- Start replicat process and verify

GGSCI (stby.localdomain) 5> start replicat GGREPL01

Sending START request to MANAGER ...
REPLICAT GGREPL01 starting

GGSCI (stby.localdomain) 6> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING
REPLICAT RUNNING GGREPL01 00:00:00 00:00:01

Oracle Database Administration

Monday 14 August 2017

Install and Configure GoldenGate