How to Install Oracle GoldenGate

This topic discusses how to install and configure Oracle GoldenGate version 11.2.0

Installing GoldenGate version 11.2.0 involves downloading the software in zipped form from OTN and extracting in a directory. This directory will be called the GoldenGate Home.

As GoldenGate processes need to connect to Oracle Database, a GoldenGate administrator user is also created in the database. This user requires elevated privileges and should be granted SYSDBA among others.

Let us start by creating the administrator user called ggadmin and grant necessary privileges.

create user ggadmin identified by ggadmin;

Grant necessary privileges.

grant connect to ggadmin;
grant alter any table to ggadmin;
exec dbms_goldengate_auth.grant_admin_privilege('GGADMIN');
grant create session to ggadmin;
grant alter session to ggadmin;
grant resource to ggadmin;
grant connect to ggadmin;
grant select any dictionary to ggadmin;
grant flashback any table to ggadmin;
grant select any table to ggadmin;
grant select on dba_clusters to ggadmin;
grant execute on dbms_flashback to ggadmin;

In addition to above, the below permissions are also granted if the GoldenGate is configured with REPLICAT process on the server. If you are in doubt, go ahead and grant the below.

grant insert, delete, update on scott.emp to ggadmin;
grant create table to ggadmin;
grant lock any table to ggadmin;

Create tablespace GGTBS which will act as the default tablespace for GGADMIN.

create tablespace ggtbs datafile '/u02/voice/df/ggtbs01.dbf' size 10m autoextend on next 10m maxsize 100m;

Alter GGADMIN to make GGTBS its default tablespace also grant UNLIMITED QUOTA.

alter user ggadmin default tablespace ggtbs;
alter user ggadmin quota unlimited on ggtbs;

While configuring GoldenGate, changes to database is restricted. To achive this, database has to be started in RESTRICTED ACCESS mode. Hence, GGADMIN should be granted RESTRICTED SESSION privilege.

grant restricted session to ggadmin;

Shut down the database.

shutdown immediate

Change to directory where GoldenGate software is extracted and find marker_setup.sql

cd <where goldengate s/w is extracted>

[oracle@voice oracle_goldengate]$ ls -l marker_setup.sql
-rw-r----- 1 oracle oinstall 3824 Aug  9  2011 marker_setup.sql

Startup the database in RESTRICT mode.

SQL> startup restrict;

Connect as SYSDBA and execute marker_setup.sql.

SQL> connect / as sysdba
	
SQL> @marker_setup.sql

This script will prompt for GoldenGate administrator user for which we will supply ggadmin.

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


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

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

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

Script complete.

Next, execute ddl_setup.sql

SQL> !ls -l ddl_setup*
-rw-r----- 1 oracle oinstall 296951 Apr  2  2012 ddl_setup.sql
SQL> @ddl_setup.sql

ddl_setup.sql also prompts for GoldenGate administrator user for which we will supply ggadmin

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

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 GGADMIN 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 GGADMIN

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
-----------------------------------
0

LOCATION OF DDL TRACE FILE
------------------------------------------------------------------------------------------------------------------------
/u02/voice/diag/diag/rdbms/voice/voice/trace/ggs_ddl_trace.log

Analyzing installation status...


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

Script complete.
SQL>

Next, execute role_setup.sql


SQL> !ls -l role*
-rw-r----- 1 oracle oinstall 4375 Apr  8  2011 role_setup.sql
SQL> @role_setup.sql

role_setup.sql will also prompt for GoldenGate administrator user

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

Next, grant GGS_GGSUSER_ROLE to GoldenGate administrator.

SQL> grant ggs_ggsuser_role to ggadmin;

Next, execute ddl_enable.sql to enable replication of DDLs

SQL> !ls -l ddl_enable*
-rw-r----- 1 oracle oinstall 692 Oct 15  2010 ddl_enable.sql
SQL> @ddl_enable.sql

Trigger altered.

Execute sequence.sql, next

SQL> !ls -l sequence*
-rw-r----- 1 oracle oinstall 34564 Nov 16  2011 sequence.sql
SQL> @sequence.sql

sequence.sql also prompts for GoldenGate administrator.

Please enter the name of a schema for the GoldenGate database objects:
ggadmin
Setting schema name to GGADMIN

UPDATE_SEQUENCE STATUS:

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

GETSEQFLUSH

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

SEQTRACE

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

REPLICATE_SEQUENCE STATUS:

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

STATUS OF SEQUENCE SUPPORT
--------------------------------------------------------------
SUCCESSFUL installation of Oracle Sequence Replication support
SQL>

Create subdirectories where GoldenGate will store various files. Login to ggsci and execute CREATE SUBDIRS

./ggsci

GGSCI (voice) 1> create subdirs

Creating subdirectories under current directory /home/oracle/oracle_goldengate

Parameter files                /home/oracle/oracle_goldengate/dirprm: already exists
Report files                   /home/oracle/oracle_goldengate/dirrpt: created
Checkpoint files               /home/oracle/oracle_goldengate/dirchk: created
Process status files           /home/oracle/oracle_goldengate/dirpcs: created
SQL script files               /home/oracle/oracle_goldengate/dirsql: created
Database definitions files     /home/oracle/oracle_goldengate/dirdef: created
Extract data files             /home/oracle/oracle_goldengate/dirdat: created
Temporary files                /home/oracle/oracle_goldengate/dirtmp: created
Stdout files                   /home/oracle/oracle_goldengate/dirout: created


GGSCI (voice) 2>

Installation of GoldenGate is complete now. After completion, the below is the list of files in GoldenGate Home.

[oracle@voice oracle_goldengate]$ ls -ltr
total 475504
-rw-r----- 1 oracle oinstall      1476 Oct 15  2010 zlib.txt
-rw-r----- 1 oracle oinstall       759 Oct 15  2010 tcperrs
-rw-r----- 1 oracle oinstall       248 Oct 15  2010 sqlldr.tpl
-rw-r----- 1 oracle oinstall      1668 Oct 15  2010 libxml2.txt
-rw-r----- 1 oracle oinstall      1968 Oct 15  2010 freeBSD.txt
-rw-r----- 1 oracle oinstall       159 Oct 15  2010 db2cntl.tpl
-rw-r----- 1 oracle oinstall      1725 Oct 15  2010 bcrypt.txt
-rw-r----- 1 oracle oinstall       426 Oct 15  2010 bcpfmt.tpl
-rw-r----- 1 oracle oinstall      1715 Oct 15  2010 marker_status.sql
-rw-r----- 1 oracle oinstall      2275 Oct 15  2010 demo_ora_misc.sql
-rw-r----- 1 oracle oinstall      4015 Oct 15  2010 demo_ora_lob_create.sql
-rw-r----- 1 oracle oinstall       821 Oct 15  2010 demo_ora_insert.sql
-rw-r----- 1 oracle oinstall       883 Oct 15  2010 demo_ora_create.sql
-rw-r----- 1 oracle oinstall      2862 Oct 15  2010 ddl_trace_on.sql
-rw-r----- 1 oracle oinstall      2955 Oct 15  2010 ddl_tracelevel.sql
-rw-r----- 1 oracle oinstall      2501 Oct 15  2010 ddl_staymetadata_on.sql
-rw-r----- 1 oracle oinstall      2506 Oct 15  2010 ddl_staymetadata_off.sql
-rw-r----- 1 oracle oinstall      1227 Oct 15  2010 ddl_purgeRecyclebin.sql
-rw-r----- 1 oracle oinstall      1026 Oct 15  2010 ddl_pin.sql
-rw-r----- 1 oracle oinstall       388 Oct 15  2010 ddl_nopurgeRecyclebin.sql
-rw-r----- 1 oracle oinstall       692 Oct 15  2010 ddl_enable.sql
-rw-r----- 1 oracle oinstall       746 Oct 15  2010 ddl_disable.sql
-rw-r----- 1 oracle oinstall      4189 Oct 15  2010 ddl_ddl2file.sql
-rw-r----- 1 oracle oinstall      1059 Oct 15  2010 ddl_cleartrace.sql
-rw-r----- 1 oracle oinstall      2520 Oct 15  2010 demo_ora_pk_befores_updates.sql
-rw-r----- 1 oracle oinstall      1227 Oct 15  2010 demo_ora_pk_befores_insert.sql
-rw-r----- 1 oracle oinstall      1269 Oct 15  2010 demo_ora_pk_befores_create.sql
-rw-r----- 1 oracle oinstall       967 Oct 15  2010 demo_more_ora_insert.sql
-rw-r----- 1 oracle oinstall      1217 Oct 15  2010 demo_more_ora_create.sql
-rw-r----- 1 oracle oinstall      1053 Oct 15  2010 ddl_session.sql
-rw-r----- 1 oracle oinstall       425 Oct 15  2010 ddl_session1.sql
-rw-r----- 1 oracle oinstall      1272 Dec 28  2010 prvtclkm.plb
-rw-r----- 1 oracle oinstall      2721 Dec 28  2010 ddl_trace_off.sql
-rw-r----- 1 oracle oinstall      4375 Apr  8  2011 role_setup.sql
-rw-r----- 1 oracle oinstall      2393 Apr  8  2011 remove_seq.sql
-rw-r----- 1 oracle oinstall      3824 Aug  9  2011 marker_setup.sql
-rw-r----- 1 oracle oinstall      2278 Aug  9  2011 marker_remove.sql
-rw-r----- 1 oracle oinstall      4138 Aug  9  2011 ddl_remove.sql
-rw-r----- 1 oracle oinstall      6747 Aug 25  2011 ulg.sql
-rw-r----- 1 oracle oinstall      3167 Aug 29  2011 ddl_ora10upCommon.sql
-rw-r----- 1 oracle oinstall      3518 Oct 14  2011 ddl_filter.sql
-rw-r----- 1 oracle oinstall     34564 Nov 16  2011 sequence.sql
-rw-r----- 1 oracle oinstall     14051 Dec 15  2011 ddl_ora9.sql
-rw-r----- 1 oracle oinstall     14358 Dec 15  2011 ddl_ora11.sql
-rw-r----- 1 oracle oinstall     13689 Dec 15  2011 ddl_ora10.sql
-rw-r----- 1 oracle oinstall     28041 Dec 29  2011 usrdecs.h
-rw-r----- 1 oracle oinstall    213535 Jan 19  2012 notices.txt
-rw-r----- 1 oracle oinstall    169753 Feb  1  2012 help.txt
-rw-r----- 1 oracle oinstall     10506 Feb 27  2012 ddl_status.sql
-rw-r----- 1 oracle oinstall      1285 Feb 29  2012 chkpt_ora_create.sql
-rw-r----- 1 oracle oinstall     16763 Mar  3  2012 ucharset.h
-rw-r----- 1 oracle oinstall      5364 Mar 22  2012 params.sql
-rw-r----- 1 oracle oinstall    296951 Apr  2  2012 ddl_setup.sql
-rwxr-x--- 1 oracle oinstall   4508019 Apr 19  2012 libicui18n.so.38
-rwxr-x--- 1 oracle oinstall   4803823 Apr 19  2012 libxerces-c.so.28
-rwxr-x--- 1 oracle oinstall   3349821 Apr 19  2012 libicuuc.so.38
-rwxr-x--- 1 oracle oinstall     83229 Apr 19  2012 libantlr3c.so
-rwxr-x--- 1 oracle oinstall   1740066 Apr 19  2012 libdb-5.2.so
-rwxr-x--- 1 oracle oinstall  15581116 Apr 19  2012 libicudata.so.38
drwxr-x--- 7 oracle oinstall      4096 Apr 23  2012 UserExitExamples
-rw-r----- 1 oracle oinstall   1257280 Apr 23  2012 ggMessage.dat
-rwxr-x--- 1 oracle oinstall   9250398 Apr 23  2012 mgr
-rwxr-x--- 1 oracle oinstall   5825921 Apr 23  2012 ggcmd
drwxr-x--- 2 oracle oinstall      4096 Apr 23  2012 cfg
-rwxr-x--- 1 oracle oinstall      1015 Apr 23  2012 pw_agent_util.sh
-rwxr-x--- 1 oracle oinstall       406 Apr 23  2012 jagent.sh
drwxr-x--- 2 oracle oinstall      4096 Apr 23  2012 dirprm
drwxr-x--- 2 oracle oinstall      4096 Apr 23  2012 dirjar
-rwxr-x--- 1 oracle oinstall   5307711 Apr 23  2012 cobgen
-rwxr-x--- 1 oracle oinstall   5584197 Apr 23  2012 ddlcob
-rwxr-x--- 1 oracle oinstall   5901919 Apr 23  2012 emsclnt
-rwxr-x--- 1 oracle oinstall    316003 Apr 23  2012 keygen
-rwxr-x--- 1 oracle oinstall   6441773 Apr 23  2012 logdump
-rwxr-x--- 1 oracle oinstall   4622859 Apr 23  2012 retrace
-rwxr-x--- 1 oracle oinstall   4600385 Apr 23  2012 oggerr
-rwxr-x--- 1 oracle oinstall    654448 Apr 23  2012 reverse
-rwxr-x--- 1 oracle oinstall  11984721 Apr 23  2012 server
-rwxr-x--- 1 oracle oinstall   5054612 Apr 23  2012 convchk
-rwxr-x--- 1 oracle oinstall   8317872 Apr 23  2012 defgen
-rwxr-x--- 1 oracle oinstall  12266773 Apr 23  2012 ggsci
-rwxr-x--- 1 oracle oinstall  34210648 Apr 23  2012 extract
-rwxr-x--- 1 oracle oinstall   6569009 Apr 23  2012 libgglog.so
-rwxr-x--- 1 oracle oinstall   1893448 Apr 23  2012 libggrepo.so
-rwxr-x--- 1 oracle oinstall  27303957 Apr 23  2012 replicat
-rw-rw-r-- 1 oracle oinstall 209367040 Apr 23  2012 fbo_ggs_Linux_x86_ora11g_32bit.tar
-rwxrwxrwx 1 oracle oinstall    220546 May  2  2012 OGG_WinUnix_Rel_Notes_11.2.1.0.1.pdf
-rwxrwxrwx 1 oracle oinstall     93696 May  2  2012 Oracle GoldenGate 11.2.1.0.1 README.doc
-rwxrwxrwx 1 oracle oinstall     24390 May  2  2012 Oracle GoldenGate 11.2.1.0.1 README.txt
-rw-r--r-- 1 oracle oinstall  87966150 Nov 16 19:37 ogg112101_fbo_ggs_Linux_x86_ora11g_32bit.zip
-rw-rw-rw- 1 oracle oinstall       138 Nov 27 22:01 ggserr.log
-rw-rw-rw- 1 oracle oinstall        17 Nov 27 22:02 GLOBALS
-rw-r--r-- 1 oracle oinstall       571 Nov 27 22:19 marker_setup_spool.txt
-rw-r--r-- 1 oracle oinstall      2128 Nov 27 22:19 marker_setup_set.txt
-rw-r--r-- 1 oracle oinstall      2130 Nov 27 22:22 ddl_setup_set.txt
-rw-r--r-- 1 oracle oinstall       823 Nov 27 22:22 ddl_setup_spool.txt
-rw-r--r-- 1 oracle oinstall      2127 Nov 27 22:23 role_setup_set.txt
-rw-r--r-- 1 oracle oinstall      1528 Nov 27 22:23 role_setup_spool.txt
-rw-r--r-- 1 oracle oinstall         0 Nov 28 19:34 sequence_spool.txt
-rw-r--r-- 1 oracle oinstall      2128 Nov 28 19:34 sequence_set.txt
drwxrwxr-x 2 oracle oinstall      4096 Nov 28 19:44 dirtmp
drwxrwxr-x 2 oracle oinstall      4096 Nov 28 19:44 dirsql
drwxrwxr-x 2 oracle oinstall      4096 Nov 28 19:44 dirrpt
drwxrwxr-x 2 oracle oinstall      4096 Nov 28 19:44 dirpcs
drwxrwxr-x 2 oracle oinstall      4096 Nov 28 19:44 dirout
drwxrwxr-x 2 oracle oinstall      4096 Nov 28 19:44 dirdef
drwxrwxr-x 2 oracle oinstall      4096 Nov 28 19:44 dirdat
drwxrwxr-x 2 oracle oinstall      4096 Nov 28 19:44 dirchk

As seen above, it will create below subdirectories.

dirtmp
For storing temp files
dirsql
For storing SQLs
dirrpt
For storing report files
dirpcs
For storing
dirout
For storing output files
dirdef
For storing
dirdat
For storing trail files
dirchk
For storing checkpoint files

See the next part on how to configure GoldenGate here.

Hope this helps ...

Comments