11g - alfredokriegdba.com

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

(http://www.pythian.com/blog/oracle-goldengate-installation-part-1/)

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

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

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

——————————-

MARKER SEQUENCE

——————————-

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

———————————–

DDL IGNORE LOG TABLE

———————————–

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

———————————–

DDL HISTORY TABLE(1)

———————————–

DDL DUMP TABLES

———————————–

DDL DUMP COLUMNS

———————————–

DDL DUMP LOG GROUPS

———————————–

DDL DUMP PARTITIONS

———————————–

DDL DUMP PRIMARY KEYS

———————————–

DDL SEQUENCE

———————————–

GGS_TEMP_COLS

———————————–

GGS_TEMP_UK

———————————–

DDL TRIGGER CODE STATUS:

Line/pos Error

—————————————- —————————————————————–

No errors No errors

DDL TRIGGER INSTALL STATUS

———————————–

DDL TRIGGER RUNNING STATUS

————————————————————————————————————————

ENABLED

STAYMETADATA IN TRIGGER

————————————————————————————————————————

OFF

DDL TRIGGER SQL TRACING

————————————————————————————————————————

DDL TRIGGER TRACE LEVEL

————————————————————————————————————————

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

where 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

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

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

Tag: 11g

GoldenGate 11G Install on Linux x86

Oracle Clusterware 11gR2 Basics