2011-09-07

Oracle Goldengate SQLEXEC output parameters

Environment
AIX 5.3.0.0
Oracle database 10.2.0.5
Goldengate 11.1.1.1

Problem
Replicat failed and the report file contains:
ERROR OGG-00353 Could not find stored procedure param (parameter does not exist in sp (seqnumout))

Solution
When using SQLEXEC with SELECT statement, make sure that alias in SELECT statement is the same as output parameter in GETVAL. It is seqnumout in the example below.

MAP HOU.HOUSEHOLD_PERS1, TARGET STG.HOUSEHOLD_PERS1,
SQLEXEC (ID seqnum, QUERY "select STG.HOUSEHOLD_PERS1_SEQ.nextval seqnumout from dual", NOPARAMS),
COLMAP (USEDEFAULTS,
GG_OPERATION = @GETENV ("GGHEADER","OPTYPE"),
GG_BEFORE_AFTER = @GETENV ("GGHEADER","BEFOREAFTERINDICATOR"),
GG_COMMITTIME = @GETENV ("GGHEADER","COMMITTIMESTAMP"),
SEQ# = @GETVAL(seqnum.seqnumout),
TKN_SCN = @TOKEN ("TKN_SCN"),
TKN_RSN = @TOKEN ("TKN_RSN")
);

2011-09-05

Oracle Goldengate installation on AIX

Environment
AIX 5.3.0.0 (command oslevel)
Oracle database 10.2.0.5
GoldenGate 11.1.1.1

Version 11.1.1.1 OGGCORE_11.1.1_PLATFORMS_110421.2040
AIX 5L, ppc, 64bit (optimized), Oracle 10.2 on Apr 22 2011 02:17:48

Problem
After GoldenGate installation, ggsci cannot start.

ggsci
exec(): 0509-036 Cannot load program ggsci because of the following errors:
        0509-150   Dependent module libclntsh.a(shr.o) could not be loaded.
        0509-022 Cannot load module libclntsh.a(shr.o).
        0509-026 System error: A file or directory in the path name does not exist.
 
pwd
/oracle/goldengate/11.1.1
ldd ggsci
ggsci needs:
         /usr/lib/libc.a(shr_64.o)
         /usr/lib/libC.a(shr_64.o)
         /usr/lib/libC.a(shr2_64.o)
         /usr/lib/libC.a(ansi_64.o)
         /usr/lib/libpthreads.a(shr_xpg5_64.o)
         /oracle/goldengate/11.1.1/libicuuc38.a(libicuuc38.1.so)
         /oracle/goldengate/11.1.1/libicui18n38.a(libicui18n38.1.so)
Cannot find      /oracle/goldengate/11.1.1/libxerces-c.a
         /unix
         /usr/lib/libcrypt.a(shr_64.o)
         /usr/lib/libC.a(ansicore_64.o)
         /usr/lib/libC.a(shrcore_64.o)
         /usr/lib/libC.a(shr3_64.o)
         /oracle/goldengate/11.1.1/libicudata38.a(libicudata38.1.so)
         /usr/lib/libpthread.a(shr_xpg5_64.o)
         /usr/lib/libc_r.a(shr_64.o)
 
ls -l *a
-r-xr-xr-x    1 orax122  oinstall   11360230 Mar 29 10:42 libicudata38.a
-r-xr-xr-x    1 orax122  oinstall    9302329 Mar 29 10:42 libicui18n38.a
-r-xr-xr-x    1 orax122  oinstall    6302602 Mar 29 10:42 libicuuc38.a
-r-xr-xr-x    1 orax122  oinstall    9209080 Mar 29 10:42 libxerces-c.a
 
Solution
echo $LIBPATH
/oracle/goldengate/11.1.1

$LIBPATH must include $ORACLE_HOME/lib

export LIBPATH=${OGG_HOME}:$ORACLE_HOME/lib
echo $LIBPATH
/oracle/goldengate/11.1.1:/oracle/rdbms/10.2.0/orax122/lib