Monday 14 July 2014

Upgradation Of GoldenGate Version

From version     : 11.2.1.0.3
To Version          : 11.2.1.0.8
OS version          : Linux_x64

(Download the GoldenGate Patch from support.oracle.com)

Step 1: Go to Goldengate Home Directory

[oracle:sukumar:/app/oracle]$ cd /app/oracle/product/ggate

Step 2: Unzip the patch file, which populates the tar file.

 [oracle:sukumar:/app/oracle/product/ggate]$ ls -lrt *.zip
-rwxr-xr-x 1 oracle oinstall 88475139 Jul 13 06:47 p17011917_112108_Linux-x86-64.zip

[oracle:sukumar:/app/oracle/product/ggate]$ unzip p17011917_112108_Linux-x86-64.zip
Archive:  p17011917_112108_Linux-x86-64.zip
  inflating: fbo_ggs_Linux_x64_ora11g_64bit.tar
  inflating: Oracle-GoldenGate-11.2.1.0.8-README.txt
  inflating: OGG_WinUnix_Rel_Notes_11.2.1.0.8.pdf

[oracle:sukumar:/app/oracle/product/ggate]$ ls -lrt *.tar
-rw-rw-r-- 1 oracle oinstall 227624960 Jul 18  2013 fbo_ggs_Linux_x64_ora11g_64bit.tar

Step 3 : Make sure to stop all the Goldengate process.

[oracle:sukumar:/app/oracle/product/ggate]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.3 14400833 OGGCORE_11.2.1.0.3_PLATFORMS_120823.1258_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Aug 23 2012 20:20:21

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



GGSCI (sukumar) 1> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     EXT1      00:00:00      00:00:06
EXTRACT     RUNNING     EXT2      00:00:00      00:00:07
EXTRACT     RUNNING     PUMP1     00:00:00      00:00:07
EXTRACT     RUNNING     PUMP2     00:00:00      00:00:02

GGSCI (sukumar) 2> stop *

Sending STOP request to EXTRACT EXT1 ...
Request processed.

Sending STOP request to EXTRACT EXT2 ...
Request processed.

Sending STOP request to EXTRACT PUMP1 ...
Request processed.

Sending STOP request to EXTRACT PUMP2 ...
Request processed.



GGSCI (sukumar) 3> stop manager
Manager process is required by other GGS processes.
Are you sure you want to stop it (y/n)? y

Sending STOP request to MANAGER ...
Request processed.
Manager stopped.

Step 4: Un Tar the TAR file under Goldengate home directory

[oracle:sukumar:/app/oracle/product/ggate]$ ls -lrt *.tar
-rw-rw-r-- 1 oracle oinstall 227624960 Jul 18  2013 fbo_ggs_Linux_x64_ora11g_64bit.tar

[oracle:sukumar:/app/oracle/product/ggate]$ tar -xvf fbo_ggs_Linux_x64_ora11g_64bit.tar
UserExitExamples/
UserExitExamples/ExitDemo_more_recs/
UserExitExamples/ExitDemo_more_recs/Makefile_more_recs.LINUX
UserExitExamples/ExitDemo_more_recs/Makefile_more_recs.SOLARIS
UserExitExamples/ExitDemo_more_recs/Makefile_more_recs.HPUX
UserExitExamples/ExitDemo_more_recs/readme.txt
UserExitExamples/ExitDemo_more_recs/exitdemo_more_recs.c
UserExitExamples/ExitDemo_more_recs/exitdemo_more_recs.vcproj
UserExitExamples/ExitDemo_more_recs/Makefile_more_recs.AIX
UserExitExamples/ExitDemo_pk_befores/
UserExitExamples/ExitDemo_pk_befores/exitdemo_pk_befores.c
UserExitExamples/ExitDemo_pk_befores/Makefile_pk_befores.SOLARIS
UserExitExamples/ExitDemo_pk_befores/Makefile_pk_befores.AIX
UserExitExamples/ExitDemo_pk_befores/readme.txt
UserExitExamples/ExitDemo_pk_befores/Makefile_pk_befores.HPUX
UserExitExamples/ExitDemo_pk_befores/exitdemo_pk_befores.vcproj
UserExitExamples/ExitDemo_pk_befores/Makefile_pk_befores.LINUX
UserExitExamples/ExitDemo_lobs/
UserExitExamples/ExitDemo_lobs/Makefile_lob.SOLARIS
UserExitExamples/ExitDemo_lobs/exitdemo_lob.c
UserExitExamples/ExitDemo_lobs/readme.txt
UserExitExamples/ExitDemo_lobs/Makefile_lob.HPUX
UserExitExamples/ExitDemo_lobs/Makefile_lob.LINUX
UserExitExamples/ExitDemo_lobs/Makefile_lob.AIX
UserExitExamples/ExitDemo_lobs/exitdemo_lob.vcproj
UserExitExamples/ExitDemo/
UserExitExamples/ExitDemo/exitdemo.vcproj
UserExitExamples/ExitDemo/Makefile_exit_demo.HP_OSS
UserExitExamples/ExitDemo/Makefile_exit_demo.HPUX
UserExitExamples/ExitDemo/exitdemo_utf16.c
UserExitExamples/ExitDemo/exitdemo.c
UserExitExamples/ExitDemo/readme.txt
UserExitExamples/ExitDemo/Makefile_exit_demo.LINUX
UserExitExamples/ExitDemo/Makefile_exit_demo.SOLARIS
UserExitExamples/ExitDemo/Makefile_exit_demo.AIX
UserExitExamples/ExitDemo_passthru/
UserExitExamples/ExitDemo_passthru/Makefile_passthru.HPUX
UserExitExamples/ExitDemo_passthru/Makefile_passthru.AIX
UserExitExamples/ExitDemo_passthru/Makefile_passthru.LINUX
UserExitExamples/ExitDemo_passthru/readme.txt
UserExitExamples/ExitDemo_passthru/Makefile_passthru.SOLARIS
UserExitExamples/ExitDemo_passthru/Makefile_passthru.HP_OSS
UserExitExamples/ExitDemo_passthru/exitdemo_passthru.c
UserExitExamples/ExitDemo_passthru/exitdemopassthru.vcproj
bcpfmt.tpl
bcrypt.txt
cfg/
cfg/Config.properties
cfg/mpmetadata.xml
cfg/MPMetadataSchema.xsd
cfg/password.properties
cfg/jps-config-jse.xml
cfg/ProfileConfig.xml
chkpt_ora_create.sql
cobgen
convchk
db2cntl.tpl
ddl_cleartrace.sql
ddl_create.sql
ddl_ddl2file.sql
ddl_disable.sql
ddl_enable.sql
ddl_filter.sql
ddl_ora10.sql
ddl_ora10upCommon.sql
ddl_ora11.sql
ddl_ora9.sql
ddl_pin.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/spring-security-core-3.0.1.RELEASE.jar
dirjar/jmxremote_optional-1.0-b02.jar
dirjar/org.springframework.core-3.0.0.RELEASE.jar
dirjar/org.springframework.aspects-3.0.0.RELEASE.jar
dirjar/xmlparserv2.jar
dirjar/org.springframework.context-3.0.0.RELEASE.jar
dirjar/jps-patching.jar
dirjar/monitor-common.jar
dirjar/osdt_cert.jar
dirjar/org.springframework.context.support-3.0.0.RELEASE.jar
dirjar/log4j-1.2.15.jar
dirjar/org.springframework.web-3.0.0.RELEASE.jar
dirjar/commons-logging-1.0.4.jar
dirjar/osdt_xmlsec.jar
dirjar/org.springframework.jdbc-3.0.0.RELEASE.jar
dirjar/jps-mbeans.jar
dirjar/jagent.jar
dirjar/org.springframework.asm-3.0.0.RELEASE.jar
dirjar/osdt_core.jar
dirjar/jps-manifest.jar
dirjar/identitystore.jar
dirjar/jsr250-api-1.0.jar
dirjar/jps-common.jar
dirjar/jps-upgrade.jar
dirjar/xpp3_min-1.1.4c.jar
dirjar/org.springframework.transaction-3.0.0.RELEASE.jar
dirjar/org.springframework.instrument-3.0.0.RELEASE.jar
dirjar/jps-unsupported-api.jar
dirjar/fmw_audit.jar
dirjar/org.springframework.beans-3.0.0.RELEASE.jar
dirjar/spring-security-web-3.0.1.RELEASE.jar
dirjar/jps-api.jar
dirjar/jps-internal.jar
dirjar/spring-security-taglibs-3.0.1.RELEASE.jar
dirjar/slf4j-api-1.4.3.jar
dirjar/spring-security-config-3.0.1.RELEASE.jar
dirjar/ldapjclnt11.jar
dirjar/jps-ee.jar
dirjar/xstream-1.3.jar
dirjar/spring-security-acl-3.0.1.RELEASE.jar
dirjar/commons-codec-1.3.jar
dirjar/spring-security-cas-client-3.0.1.RELEASE.jar
dirjar/jps-wls.jar
dirjar/jacc-spi.jar
dirjar/org.springframework.orm-3.0.0.RELEASE.jar
dirjar/org.springframework.expression-3.0.0.RELEASE.jar
dirjar/oraclepki.jar
dirjar/identityutils.jar
dirjar/org.springframework.test-3.0.0.RELEASE.jar
dirjar/slf4j-log4j12-1.4.3.jar
dirjar/jdmkrt-1.0-b02.jar
dirjar/org.springframework.aop-3.0.0.RELEASE.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


Step 5: We are done with the version upgrade of Goldengate, connect and check the version.

[oracle:sukumar:/app/oracle/product/ggate]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.8 17044551 OGGCORE_11.2.1.0.0OGGBP_PLATFORMS_130718.0526_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Jul 18 2013 10:34:27

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



GGSCI (sukumar) 1> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     STOPPED
EXTRACT     STOPPED     EXT1       00:00:00      00:01:41
EXTRACT     STOPPED     EXT2       00:00:00      00:01:41
EXTRACT     STOPPED     PUMP1      00:00:00      00:01:40
EXTRACT     STOPPED     PUMP2      00:00:00      00:01:39


Step 6: Start the manager process followed by all the extract process.

GGSCI (sukumar) 2> start manager

Manager started.


GGSCI (sukumar) 3> start *

Sending START request to MANAGER ...
EXTRACT EXT1 starting

Sending START request to MANAGER ...
EXTRACT EXT2 starting

Sending START request to MANAGER ...
EXTRACT PUMP1 starting

Sending START request to MANAGER ...
EXTRACT PUMP2 starting


GGSCI (sukumar) 4> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     EXT1        00:00:00      00:01:56
EXTRACT     RUNNING     EXT2        00:00:00      00:00:00
EXTRACT     RUNNING     PUMP1       00:00:00      00:01:55
EXTRACT     RUNNING     PUMP2       00:03:19      00:00:00




Thursday 3 July 2014

Oracle GoldenGate DDL Replication



1.       Make sure to have a Non-Default tablespace for GoldenGate User (not users tablespace)

SQL> select username, default_tablespace from dba_users where username='GGS_DR_OWNER';

USERNAME                       DEFAULT_TABLESPACE
------------------------------ ------------------------------
GGS_DR_OWNER                   GGS_DATA

2.       Turn off Recycle bin for the Source database.

SQL> alter session set recyclebin=OFF scope=BOTH;

Session altered.


3.       You will find the below execution scripts under GoldenGate Software Home Directory.

SQL> @marker_setup

Marker setup 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:GGS_DR_OWNER


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

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

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

Script complete.



SQL> @ddl_setup

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 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 GoldenGate schema name:GGS_DR_OWNER

You will be prompted for the mode of installation.
To install or reinstall DDL replication, enter INITIALSETUP
To upgrade DDL replication, enter NORMAL
Enter mode of installation:INITIALSETUP

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 GGS_DR_OWNER as a GoldenGate schema name, INITIALSETUP as a mode of installation.

Working, please wait ...

RECYCLEBIN must be empty.
This installation will purge RECYCLEBIN for all users.
To proceed, enter yes. To stop installation, enter no.

Enter yes or no:yes


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

DDLORA_GETTABLESPACESIZE STATUS:

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

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 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
------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/admin/TXNQA/udump/ggs_ddl_trace.log

Analyzing installation status...


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

Script complete.



SQL> @role_setup

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

Grant succeeded.


SQL> @ddl_enable

Trigger altered.

SQL> @ddl_pin GGS_DR_OWNER

PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


GGSCI 1> DBLOGIN USERID GGS_DR_OWNER, PASSWORD GGS_DR_OWNER

GGSCI 2> ADD TRANDATA SUKUMAR.emp

EXTRACT ddlext
SETENV (NLS_LANG=AMERICAN_AMERICA.UTF8)
SETENV ORACLE_SID=sukumar
USERID GGS_DR_OWNER@DB_TNS, PASSWORD GGS_DR_OWNER
RMTHOST OGG01.sukumar.com, MGRPORT 7809
RMTTRAIL /u01/GG/source/dirdat/dd
TRANLOGOPTIONS ASMUSER sys@ASM_TNS, ASMPASSWORD asm123
DDL INCLUDE MAPPED                        ----------------- Mandatory Parameter for DDL replication
TABLE SUKUMAR.emp;


After completion of DDL Setup, If you don’t want to replicat the DDL’s, we have an option to disable it.

SQL> @ddl_disable

Trigger altered.


Tuesday 1 July 2014

OGG-00664 OCI Error beginning session

GGSCI (pkdb4) 1> info all

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     EXTTR       09:56:11      00:00:18
EXTRACT     STOPPED     EXTTRP      00:00:00      20:36:52


GGSCI (pkdb4) 2> start exttrp

Sending START request to MANAGER ...
EXTRACT EXTTRP starting


GGSCI (pkdb4) 3> info all

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     EXTTR       09:56:11      00:00:23
EXTRACT     STOPPED     EXTTRP      00:00:00      20:36:57


GGSCI (pkdb4) 4> view report exttrp


***********************************************************************
                 Oracle GoldenGate Capture for Oracle
                     Version 11.1.1.0.0 Build 078
   HP/UX, IA64, 64bit (optimized), Oracle 10 on Jul 28 2010 15:49:30

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


                    Starting at 2014-07-01 07:29:55
***********************************************************************

Operating System Version:
HP-UX
Version U, Release B.11.31
Node: pkdb4
Machine: ia64
                         soft limit   hard limit
Address Space Size   :    unlimited    unlimited
Heap Size            :   4294967296   4294967296
File Size            :    unlimited    unlimited
CPU Time             :    unlimited    unlimited

Process id: 1468

Description:

***********************************************************************
**            Running with the following parameters                  **
***********************************************************************
EXTRACT exttrp
USERID ggs_dr_owner, PASSWORD ************

Source Context :
  SourceModule            : [ggdb.ora.sess]
  SourceID                : [/home/ecloud/workspace/Build_OpenSys_r11.1.1.0.0_078_[34100]/perforce/src/gglib/ggdbora/ocisess.c]
  SourceFunction          : [OCISESS_try]
  SourceLine              : [498]

2014-07-01 07:29:55  ERROR   OGG-00664  OCI Error beginning session (status = 1034-ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
HPUX-ia64 Error: 2: No such file or directory).

2014-07-01 07:29:55  ERROR   OGG-01668  PROCESS ABENDING.


Reason :

If we have multiple instances running on same host, We need to mention to which instance extract has to connect.

FIX : 

Option 1: Use SETENV ORACLE_SID and ORACLE_HOME values on param file

SETENV (ORACLE_SID=TXNTR1)
SETENV (ORACLE_HOME=/u01/app/oracle/product/10.2.0/db)
USERID ggs_owner, PASSWORD ggs_owner

Option 2: Use TNS entry (if multiple DB instances are running on same node)

USERID ggs_owner@tns_entry, PASSWORD ggs_owner

----------------------------------------------------------
GGSCI (pkdb4) 5> start exttrp

Sending START request to MANAGER ...
EXTRACT EXTTRP starting


GGSCI (pkdb4) 6> info all

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     EXTTR       09:56:11      00:00:23
EXTRACT     RUNNING     EXTTRP      20:36:22      00:00:14