Saturday, June 8, 2013

GoldenGate 11G Install on Linux x86

Today I will show you how to install GoldenGate under Linux 5.5 following the recipe of my teammate Gleb.

Prerequisites:

·          Oracle Linux 5.5 installed in 2 VM using VirtualBox, I have used one pre-built VM from Oracle (http://www.oracle.com/technetwork/database/enterprise-edition/databaseappdev-vm-161299.html)
·          Download Oracle GoldenGate software from (http://www.oracle.com/technetwork/middleware/goldengate/downloads/index.html)
·          Start both VM machines and change the host name to source_host & target_host respectively
·          Test ping connectivity from source_host to target_host

Installation

·          Create /01 directory and change the owner to oracle:dba on source_host:

[oracle@source_host Desktop]$ su -
Password:
[root@source_host ~]# cd /
[root@source_host /]# df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/hda1              11G  2.7G  6.9G  28% /
/dev/hdb1              12G   11G  683M  94% /home
tmpfs                1014M  264M  750M  27% /dev/shm
[root@source_host /]# mkdir /u01
[root@source_host /]# chown oracle:dba /u01
[root@source_host /]# exit
Logout

·          Copy GoldenGate binaries to /u01 and unzip the file, then create goldengate directory under /u01 and untar the file:
[oracle@source_host Desktop]$ mv ogg112101_fbo_ggs_Linux_x86_ora11g_32bit.zip /u01/

[oracle@source_host Desktop]$ cd /u01
[oracle@source_host u01]$ ls
ogg112101_fbo_ggs_Linux_x86_ora11g_32bit.zip
[oracle@source_host u01]$ unzip ogg112101_fbo_ggs_Linux_x86_ora11g_32bit.zip
Archive:  ogg112101_fbo_ggs_Linux_x86_ora11g_32bit.zip
  inflating: fbo_ggs_Linux_x86_ora11g_32bit.tar 
  inflating: OGG_WinUnix_Rel_Notes_11.2.1.0.1.pdf 
  inflating: Oracle GoldenGate 11.2.1.0.1 README.txt 
  inflating: Oracle GoldenGate 11.2.1.0.1 README.doc 
[oracle@source_host u01]$ ls
fbo_ggs_Linux_x86_ora11g_32bit.tar
ogg112101_fbo_ggs_Linux_x86_ora11g_32bit.zip
OGG_WinUnix_Rel_Notes_11.2.1.0.1.pdf
Oracle GoldenGate 11.2.1.0.1 README.doc
Oracle GoldenGate 11.2.1.0.1 README.txt

[oracle@source_host u01]$ mkdir goldengate
[oracle@source_host u01]$ mv fbo_ggs_Linux_x86_ora11g_32bit.tar goldengate/

[oracle@source_host u01]$ cd goldengate/
[oracle@source_host goldengate]$ ls
fbo_ggs_Linux_x86_ora11g_32bit.tar
[oracle@source_host goldengate]$ tar -xvf fbo_ggs_Linux_x86_ora11g_32bit.tar

UserExitExamples/
UserExitExamples/ExitDemo_passthru/
UserExitExamples/ExitDemo_passthru/Makefile_passthru.AIX
UserExitExamples/ExitDemo_passthru/exitdemo_passthru.c
UserExitExamples/ExitDemo_passthru/Makefile_passthru.LINUX
UserExitExamples/ExitDemo_passthru/Makefile_passthru.HP_OSS
UserExitExamples/ExitDemo_passthru/exitdemopassthru.vcproj
UserExitExamples/ExitDemo_passthru/readme.txt
UserExitExamples/ExitDemo_passthru/Makefile_passthru.SOLARIS
UserExitExamples/ExitDemo_passthru/Makefile_passthru.HPUX
UserExitExamples/ExitDemo_lobs/
UserExitExamples/ExitDemo_lobs/exitdemo_lob.vcproj
UserExitExamples/ExitDemo_lobs/Makefile_lob.HPUX
UserExitExamples/ExitDemo_lobs/exitdemo_lob.c
UserExitExamples/ExitDemo_lobs/Makefile_lob.AIX
UserExitExamples/ExitDemo_lobs/Makefile_lob.SOLARIS
UserExitExamples/ExitDemo_lobs/Makefile_lob.LINUX
UserExitExamples/ExitDemo_lobs/readme.txt
UserExitExamples/ExitDemo/
UserExitExamples/ExitDemo/exitdemo.vcproj
UserExitExamples/ExitDemo/Makefile_exit_demo.SOLARIS
UserExitExamples/ExitDemo/exitdemo_utf16.c
UserExitExamples/ExitDemo/readme.txt
UserExitExamples/ExitDemo/exitdemo.c
UserExitExamples/ExitDemo/Makefile_exit_demo.HPUX
UserExitExamples/ExitDemo/Makefile_exit_demo.AIX
UserExitExamples/ExitDemo/Makefile_exit_demo.LINUX
UserExitExamples/ExitDemo/Makefile_exit_demo.HP_OSS
UserExitExamples/ExitDemo_pk_befores/
UserExitExamples/ExitDemo_pk_befores/exitdemo_pk_befores.c
UserExitExamples/ExitDemo_pk_befores/Makefile_pk_befores.SOLARIS
UserExitExamples/ExitDemo_pk_befores/exitdemo_pk_befores.vcproj
UserExitExamples/ExitDemo_pk_befores/readme.txt
UserExitExamples/ExitDemo_pk_befores/Makefile_pk_befores.AIX
UserExitExamples/ExitDemo_pk_befores/Makefile_pk_befores.LINUX
UserExitExamples/ExitDemo_pk_befores/Makefile_pk_befores.HPUX
UserExitExamples/ExitDemo_more_recs/
UserExitExamples/ExitDemo_more_recs/Makefile_more_recs.HPUX
UserExitExamples/ExitDemo_more_recs/exitdemo_more_recs.vcproj
UserExitExamples/ExitDemo_more_recs/Makefile_more_recs.AIX
UserExitExamples/ExitDemo_more_recs/readme.txt
UserExitExamples/ExitDemo_more_recs/exitdemo_more_recs.c
UserExitExamples/ExitDemo_more_recs/Makefile_more_recs.LINUX
UserExitExamples/ExitDemo_more_recs/Makefile_more_recs.SOLARIS
bcpfmt.tpl
bcrypt.txt
cfg/
cfg/ProfileConfig.xml
cfg/jps-config-jse.xml
cfg/password.properties
cfg/MPMetadataSchema.xsd
cfg/mpmetadata.xml
cfg/Config.properties
chkpt_ora_create.sql
cobgen
convchk
db2cntl.tpl
ddl_cleartrace.sql
ddl_ddl2file.sql
ddl_disable.sql
ddl_enable.sql
ddl_filter.sql
ddl_nopurgeRecyclebin.sql
ddl_ora10.sql
ddl_ora10upCommon.sql
ddl_ora11.sql
ddl_ora9.sql
ddl_pin.sql
ddl_purgeRecyclebin.sql
ddl_remove.sql
ddl_session.sql
ddl_session1.sql
ddl_setup.sql
ddl_status.sql
ddl_staymetadata_off.sql
ddl_staymetadata_on.sql
ddl_trace_off.sql
ddl_trace_on.sql
ddl_tracelevel.sql
ddlcob
defgen
demo_more_ora_create.sql
demo_more_ora_insert.sql
demo_ora_create.sql
demo_ora_insert.sql
demo_ora_lob_create.sql
demo_ora_misc.sql
demo_ora_pk_befores_create.sql
demo_ora_pk_befores_insert.sql
demo_ora_pk_befores_updates.sql
dirjar/
dirjar/xmlparserv2.jar
dirjar/spring-security-cas-client-3.0.1.RELEASE.jar
dirjar/spring-security-taglibs-3.0.1.RELEASE.jar
dirjar/org.springframework.transaction-3.0.0.RELEASE.jar
dirjar/identityutils.jar
dirjar/xpp3_min-1.1.4c.jar
dirjar/jps-api.jar
dirjar/jacc-spi.jar
dirjar/org.springframework.jdbc-3.0.0.RELEASE.jar
dirjar/org.springframework.aspects-3.0.0.RELEASE.jar
dirjar/identitystore.jar
dirjar/jps-mbeans.jar
dirjar/fmw_audit.jar
dirjar/commons-codec-1.3.jar
dirjar/jmxremote_optional-1.0-b02.jar
dirjar/spring-security-core-3.0.1.RELEASE.jar
dirjar/log4j-1.2.15.jar
dirjar/commons-logging-1.0.4.jar
dirjar/org.springframework.context-3.0.0.RELEASE.jar
dirjar/org.springframework.asm-3.0.0.RELEASE.jar
dirjar/org.springframework.expression-3.0.0.RELEASE.jar
dirjar/org.springframework.instrument-3.0.0.RELEASE.jar
dirjar/jps-wls.jar
dirjar/jps-upgrade.jar
dirjar/jdmkrt-1.0-b02.jar
dirjar/jps-ee.jar
dirjar/jps-common.jar
dirjar/org.springframework.beans-3.0.0.RELEASE.jar
dirjar/jps-manifest.jar
dirjar/oraclepki.jar
dirjar/spring-security-acl-3.0.1.RELEASE.jar
dirjar/jagent.jar
dirjar/osdt_xmlsec.jar
dirjar/jps-patching.jar
dirjar/org.springframework.context.support-3.0.0.RELEASE.jar
dirjar/jps-unsupported-api.jar
dirjar/monitor-common.jar
dirjar/osdt_cert.jar
dirjar/spring-security-web-3.0.1.RELEASE.jar
dirjar/spring-security-config-3.0.1.RELEASE.jar
dirjar/org.springframework.aop-3.0.0.RELEASE.jar
dirjar/xstream-1.3.jar
dirjar/jps-internal.jar
dirjar/osdt_core.jar
dirjar/org.springframework.core-3.0.0.RELEASE.jar
dirjar/slf4j-api-1.4.3.jar
dirjar/ldapjclnt11.jar
dirjar/org.springframework.test-3.0.0.RELEASE.jar
dirjar/org.springframework.orm-3.0.0.RELEASE.jar
dirjar/org.springframework.web-3.0.0.RELEASE.jar
dirjar/slf4j-log4j12-1.4.3.jar
dirjar/jsr250-api-1.0.jar
dirprm/
dirprm/jagent.prm
emsclnt
extract
freeBSD.txt
ggMessage.dat
ggcmd
ggsci
help.txt
jagent.sh
keygen
libantlr3c.so
libdb-5.2.so
libgglog.so
libggrepo.so
libicudata.so.38
libicui18n.so.38
libicuuc.so.38
libxerces-c.so.28
libxml2.txt
logdump
marker_remove.sql
marker_setup.sql
marker_status.sql
mgr
notices.txt
oggerr
params.sql
prvtclkm.plb
pw_agent_util.sh
remove_seq.sql
replicat
retrace
reverse
role_setup.sql
sequence.sql
server
sqlldr.tpl
tcperrs
ucharset.h
ulg.sql
usrdecs.h
zlib.txt
[oracle@source_host goldengate]$

·          Start GG command line utility and create necessary working directories for GoldenGate:

[oracle@source_host goldengate]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO
Linux, x86, 32bit (optimized), Oracle 11g on Apr 23 2012 08:09:25

Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.



GGSCI (source_host.localdomain) 1> create subdirs

Creating subdirectories under current directory /u01/goldengate

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


GGSCI (source_host.localdomain) 2>
GGSCI (source_host.localdomain) 2> exit
[oracle@source_host goldengate]$ mkdir discard
[oracle@source_host goldengate]$

·          Connect to Source DB and run required configuration and scripts:

[oracle@source_host goldengate]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Sat Jun 8 11:55:56 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter database add supplemental log data;

Database altered.

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

System altered.

SQL> create user ggate identified by ggate default tablespace users temporary tablespace temp;

User created.

SQL> grant connect,resource to ggate;

Grant succeeded.

SQL> grant execute on utl_file to ggate;

Grant succeeded.

SQL>

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


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

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

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

Script complete.
SQL>

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

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

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
------------------------------------------------------------------------------------------------------------------------
/home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/ggs_ddl_trace.log

Analyzing installation status...


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

Script complete.
SQL>

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

SQL> grant GGS_GGSUSER_ROLE to ggate;

Grant succeeded.

SQL> @ddl_enable.sql

Trigger altered.

SQL>

SQL> create user sender identified by qwerty default tablespace users temporary tablespace temp;

User created.

SQL> grant connect,resource,unlimited tablespace to sender;

Grant succeeded.

SQL>


·          The installation is done in the Source host, now do the same on the Target host

·          Connect to the Target DB and create a receiver user which will be synced from Source DB


SQL> create user receiver identified by qwerty default tablespace users temporary tablespace temp;

User created.

SQL> grant connect,resource,unlimited tablespace to receiver;

Grant succeeded.

SQL>

·          In the Source host configure the MANAGER and the Extract processes

[oracle@source_host goldengate]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO
Linux, x86, 32bit (optimized), Oracle 11g on Apr 23 2012 08:09:25

Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.



GGSCI (source_host.localdomain) 1> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     STOPPED                                          


GGSCI (source_host.localdomain) 2> edit params mgr

--Add this line--
PORT 7809

GGSCI (source_host.localdomain) 3> start manager

Manager started.


GGSCI (source_host.localdomain) 7> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                          


GGSCI (source_host.localdomain) 8> add extract ext1, tranlog, begin now
EXTRACT added.


GGSCI (source_host.localdomain) 9> add exttrail /u01/goldengate/dirdat/lt, extract ext1
EXTTRAIL added.


GGSCI (source_host.localdomain) 10> edit params ext1

--Add these lines--
--extract group--
extract ext1
--connection to database--
userid ggate, password ggate
--hostname and port for trail--
rmthost target_host.localdomain, mgrport 7809
--path and name for trail--
rmttrail /u01/goldengate/dirdat/lt
--DDL support
ddl include mapped objname sender.*;
--DML
table sender.*;

GGSCI (source_host.localdomain) 12>

·          Now configure the MANAGER and Replicat processes in Target host

[oracle@target_host goldengate]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO
Linux, x86, 32bit (optimized), Oracle 11g on Apr 23 2012 08:09:25

Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.



GGSCI (target_host.localdomain) 1> edit params ./GLOBAL


GGSCI (target_host.localdomain) 4> dblogin userid ggate
Password:
Successfully logged into database.

GGSCI (target_host.localdomain) 5> add checkpointtable ggate.checkpoint

Successfully created checkpoint table ggate.checkpoint.

GGSCI (target_host.localdomain) 6> add replicat rep1, exttrail /u01/goldengate/dirdat/lt,checkpointtable ggate.checkpoint
REPLICAT added.


GGSCI (target_host.localdomain) 7> edit params rep1

--Add these lines--
--Replicat group --
replicat rep1
--source and target definitions
ASSUMiETARGETDEFS
--target database login --
userid ggate, password ggate
--file for dicarded transaction --
discardfile /u01/goldengate/discard/rep1_discard.txt, append, megabytes 10
--ddl support
DDL
--Specify table mapping ---
map sender.*, target receiver.*;



GGSCI (target_host.localdomain) 11> edit params mgr

--Add this line--
PORT 7809

GGSCI (target_host.localdomain) 12> start manager

Manager started.


GGSCI (target_host.localdomain) 13> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                          
REPLICAT    STOPPED     REP1        00:00:00      00:03:31   


·          Now on the Source host start the Extract process


GGSCI (source_host.localdomain) 3> start extract ext1

Sending START request to MANAGER ...
EXTRACT EXT1 starting


GGSCI (source_host.localdomain) 4> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                          
EXTRACT     RUNNING     EXT1        00:00:00      00:22:34   


GGSCI (source_host.localdomain) 5>

·          Now on the Target host start the Replicat process

GGSCI (target_host.localdomain) 14> start replicat rep1

Sending START request to MANAGER ...
REPLICAT REP1 starting


GGSCI (target_host.localdomain) 16> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                          
REPLICAT    RUNNING     REP1        00:00:00      00:00:05  

·          At this point GoldenGate is installed and configured in both Source & Target hosts, Extract/Replicat processes are running, now the funny part begins. Let’s test our replication environment!

·          In the Source DB run the below command:

SQL> create table sender.test_tab_1 (id number,rnd_str varchar2(12));
SQL> insert into sender.test_tab_1 values (1,'test_1');
SQL> commit;

·                     ·          Now check the Target DB:

SQL> select * from receiver.test_tab_1;

ID         RND_STR
---------- ------------
1          test_1


Our GoldenGate DDL and DML replication is now working. The table was created on the Target DB and data were replicated.

Thanks,
Alfredo

No comments:

Post a Comment