Golden Gate installation on Oracle DB

I have already written here about using Golden Gate for transferring the changes from Oracle DB to TimesTen. This combination works very well for heavy loaded system when the regular TimesTen cache mechanism doesn't work properly. In the next posts I am going to write about how to set up this replication.
Let's start from Golden Gate installation. I am going to install Golden Gate on the following system: OEL 5.2 x86 + Oracle DB EE 11.2.0.3.

First of all, find the correct Golden Gate software for your system on edelivery.oracle.com . In this case, I install the "Oracle GoldenGate V11.2.1.0.1 for Oracle 11g on Linux x86" package.

You can use existing OS user (like I do) or create a new one. If you create a new user, that user must be a member of the group that owns the Oracle instance. In this example I use the oracle user.
Create the Golden Gate directory, copy the archive, unzip and tar the archive.

[oracle@db odb_11g]$ mkdir /u01/app/oracle/product/gg
[oracle@db odb_11g]$ cp V32409-01.zip /u01/app/oracle/product/gg/
[oracle@db odb_11g]$ cd /u01/app/oracle/product/gg/
[oracle@db gg]$ ls
V32409-01.zip
[oracle@db gg]$ unzip V32409-01.zip
Archive:  V32409-01.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@db gg]$
[oracle@db gg]$ ls
fbo_ggs_Linux_x86_ora11g_32bit.tar    Oracle GoldenGate 11.2.1.0.1 README.doc  V32409-01.zip
OGG_WinUnix_Rel_Notes_11.2.1.0.1.pdf  Oracle GoldenGate 11.2.1.0.1 README.txt
[oracle@db gg]$ tar -xf fbo_ggs_Linux_x86_ora11g_32bit.tar
[oracle@db gg]$ ls
bcpfmt.tpl                 defgen                              libxml2.txt
bcrypt.txt                 demo_more_ora_create.sql            logdump
cfg                        demo_more_ora_insert.sql            marker_remove.sql
chkpt_ora_create.sql       demo_ora_create.sql                 marker_setup.sql
cobgen                     demo_ora_insert.sql                 marker_status.sql
convchk                    demo_ora_lob_create.sql             mgr
db2cntl.tpl                demo_ora_misc.sql                   notices.txt
ddl_cleartrace.sql         demo_ora_pk_befores_create.sql      oggerr
ddlcob                     demo_ora_pk_befores_insert.sql      OGG_WinUnix_Rel_Notes_11.2.1.0.1.pdf
ddl_ddl2file.sql           demo_ora_pk_befores_updates.sql     Oracle GoldenGate 11.2.1.0.1 README.doc
ddl_disable.sql            dirjar                              Oracle GoldenGate 11.2.1.0.1 README.txt
ddl_enable.sql             dirprm                              params.sql
ddl_filter.sql             emsclnt                             prvtclkm.plb
ddl_nopurgeRecyclebin.sql  extract                             pw_agent_util.sh
ddl_ora10.sql              fbo_ggs_Linux_x86_ora11g_32bit.tar  remove_seq.sql
ddl_ora10upCommon.sql      freeBSD.txt                         replicat
ddl_ora11.sql              ggcmd                               retrace
ddl_ora9.sql               ggMessage.dat                       reverse
ddl_pin.sql                ggsci                               role_setup.sql
ddl_purgeRecyclebin.sql    help.txt                            sequence.sql
ddl_remove.sql             jagent.sh                           server
ddl_session1.sql           keygen                              sqlldr.tpl
ddl_session.sql            libantlr3c.so                       tcperrs
ddl_setup.sql              libdb-5.2.so                        ucharset.h
ddl_status.sql             libgglog.so                         ulg.sql
ddl_staymetadata_off.sql   libggrepo.so                        UserExitExamples
ddl_staymetadata_on.sql    libicudata.so.38                    usrdecs.h
ddl_tracelevel.sql         libicui18n.so.38                    V32409-01.zip
ddl_trace_off.sql          libicuuc.so.38                      zlib.txt
ddl_trace_on.sql           libxerces-c.so.28
[oracle@db gg]$

Setting up the LD_LIBRARY_PATH.

[oracle@db gg]$ export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/u01/app/oracle/product/gg

Run ggsci and create the subdirs

[oracle@db gg]$ ./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 (db.us.oracle.com) 1> create subdirs

Creating subdirectories under current directory /u01/app/oracle/product/gg

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


GGSCI (db.us.oracle.com) 2> exit
[oracle@db gg]$

On this step the Golden Gate installation is completed. As you can see, the installation is very easy and takes a couple of minutes.

One more TimesTen video

One more TimesTen video (swingbench performance test) (author: Svetoslav Gyurov).

New type of data loading into TimesTen

Let's have a look on new feature which was introduced in 11.2.2.4 TimesTen version. I mean the opportunity to load data into TimesTen table based on the result of a query executed into Oracle database. If you don't want to use the cache group and cache grid features, if you don't want to create special users and etc., but  at the same time, you would like to cache some information into TimesTen, please use this new feature.

Before using this feature you should make some preparation:

• You should create user (or use the existing user) on Oracle DB site (plus grants). 
• DSN should have the same national database character set.
• specify the DSN attributes which are needed for connection to Oracle DB.

For example, let's imagine that I've got one user in my Oracle DB (oratt user) and I would like to cache some sql query. I will use oratt user for connection and for execution the SQL query into Oracle DB. It means that the oratt user should have the grants for all objects which you would like to use in your query.

[oracle@db ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Wed Nov 7 18:55:28 2012

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning and Real Application Testing options

SQL> select d.username from dba_users d where user in ('TIMESTEN', 'CACHEADMIN');

USERNAME
------------------------------
SYS
SYSTEM
ORATT
...

31 rows selected.

SQL> select d.username from dba_users d where user in ('TIMESTEN', 'CACHEADMIN');

no rows selected

SQL>

As you can see, there are no any specific TimesTen users as well. In TimesTen, we need to specify some DSN attributes (OracleNetServiceName,DatabaseCharacterSet,OraclePWD). In this case I use the following DSN:

[ds_test]
Driver=/u01/app/oracle/product/11.2.2.4/TimesTen/tt11224/lib/libtten.so
DataStore=/u01/app/oracle/product/datastore/ds_test
LogDir=/u01/app/oracle/product/datastore/ds_test/log
PermSize=1024
TempSize=128
PLSQL=1
NLS_LENGTH_SEMANTICS=BYTE
DatabaseCharacterSet=WE8MSWIN1252
PLSQL_TIMEOUT=1000
OracleNetServiceName=orcl
OraclePWD=oracle

I should create the oratt user as well.

Command> CREATE USER oratt IDENTIFIED BY oracle;

User created.

Command> grant create session to oratt;

That is all. I don't need to do anything else for loading data. Now I can load data. There are two methods of loading information into TimesTen:

• using createandloadfromoraquery command
• using  ttTableSchemaFromOraQueryGet and ttLoadFromOracle  procedures

I will describe both of them. Let's start with the first one. Documentation says:
"The ttIsql utility provides the createandloadfromoraquery command that, once provided the TimesTen table name and the SELECT statement, will automatically create the TimesTen table, execute the SELECT statement on Oracle, and load the result set into the TimesTen table."
For example:

[oracle@nodett1 ~]$ ttisql "DSN=ds_test;UID=oratt;PWD=oracle"

Copyright (c) 1996-2011, Oracle.  All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.



connect "DSN=ds_test;UID=oratt;PWD=oracle";
Connection successful: DSN=ds_test;UID=oratt;DataStore=/u01/app/oracle/product/datastore/ds_test;DatabaseCharacterSet=WE8MSWIN1252;ConnectionCharacterSet=US7ASCII;DRIVER=/u01/app/oracle/product/11.2.2.4/TimesTen/tt11224/lib/libtten.so;LogDir=/u01/app/oracle/product/datastore/ds_test/log;PermSize=1024;TempSize=128;TypeMode=0;PLSQL_TIMEOUT=1000;CacheGridEnable=0;OracleNetServiceName=orcl;
(Default setting AutoCommit=1)
Command> tables;
0 tables found.
Command>
Command> createandloadfromoraquery t_sql_load select level id,
       >                                             level val_1,
       >                                             level val_2,
       >                                             level val_3
       >                                        from dual
       >                                     connect by level <= 100;
Mapping query to this table:
    CREATE TABLE "ORATT"."T_SQL_LOAD" (
    "ID" number,
    "VAL_1" number,
    "VAL_2" number,
    "VAL_3" number
     )

Table t_sql_load created
100 rows loaded from oracle.
Command> tables;
  ORATT.T_SQL_LOAD
1 table found.
Command> select count(*) from t_sql_load;
< 100 >
1 row found.
Command>

As you can see, I loaded the result of the sql query into t_sql_load table. In this case TimesTen created the t_sql_load table, but I can also load the data into existing table.

Command> truncate table t_sql_load;
Command> select count(*) from t_sql_load;
< 0 >
1 row found.
Command> createandloadfromoraquery t_sql_load select level id,
       >                                             level val_1,
       >                                             level val_2,
       >                                             level val_3
       >                                        from dual
       >                                     connect by level <= 100;
Warning  2207: Table ORATT.T_SQL_LOAD already exists
100 rows loaded from oracle.
Command> select count(*) from t_sql_load;                                                                                                    < 100 >
1 row found.
Command>

In this case I've received the 2207 warning. It is very simple :) let's have a look on the second option. Documentation says: "The ttTableSchemaFromOraQueryGet built-in procedure evaluates the user-provided SELECT statement to generate a CREATE TABLE statement that can be executed to create a table on TimesTen, which would be appropriate to receive the result set from the SELECT statement. The ttLoadFromOracle built-in procedure executes the SELECT statement on Oracle and loads the result set into the TimesTen table." For example;

Command> drop table t_sql_load;
Command> tables;
0 tables found.
Command>
Command> call ttTableSchemaFromOraQueryGet('oratt','t_sql_load','select level id,level val_1, level val_2,level val_3 from dual connect by level <= 100');
< CREATE TABLE "ORATT"."T_SQL_LOAD" (
"ID" number,
"VAL_1" number,
"VAL_2" number,
"VAL_3" number
 ) >
1 row found.
Command>

ttTableSchemaFromOraQueryGet procedure is telling us which create table statement we should manually execute. Ok, execute the following statement.

Command> CREATE TABLE "ORATT"."T_SQL_LOAD" (
       > "ID" number,
       > "VAL_1" number,
       > "VAL_2" number,
       > "VAL_3" number
       >  );
Command> tables;
  ORATT.T_SQL_LOAD
1 table found.
Command> call ttloadfromoracle ('oratt','t_sql_load','select level id,level val_1, level val_2,level val_3 from dual connect by level <= 100');
< 100 >
1 row found.
Command> select count(*) from t_sql_load;
< 100 >
1 row found.
Command>

Now, we loaded all necessary information into TimesTen. It is also very easy. The second method is more flexible I think. So, now you can see how easy to load data from Oracle DB into TimesTen using this sql query feature. One more thing. As you now, cache groups in TimesTen have some naming restrictions. For example, you can't create a cache group on a table in Oracle DB which contains symbol "#". This feature helps in this situation as well. For example:

SQL> show user
USER is "ORATT"
SQL> create table tab# as
  2  select level id,
  3         level val_1,
  4         level val_2,
  5         level val_3
  6    from dual
  7  connect by level <= 100;

Table created.

SQL>

In TimesTen:

Command> drop table t_sql_load;
Command> tables;
0 tables found.
Command> createandloadfromoraquery "oratt.tab#" select * from tab#;
Mapping query to this table:
    CREATE TABLE "ORATT"."TAB#" (
    "ID" number,
    "VAL_1" number,
    "VAL_2" number,
    "VAL_3" number
     )

Table tab# created
100 rows loaded from oracle.
Command> tables;
  ORATT.TAB#
1 table found.
Command> select count(*) from tab#;
< 100 >
1 row found.
Command>