Oracle TimesTen and other Oracle Technologies: февраля 2013

Oracle DB to Oracle TimesTen replication using Golden Gate

I wrote a post about Oracle - Oracle replication by using Golden Gate last year, therefore I decided to continue write about Golden Gate technology and at this time, I would like to describe Oracle to TimesTen replication.

Environment (two virtual machines):

host db (192.168.2.131), OEL 5.2. x86, Oracle DB EE 11.2.0.3.0, Golden Gate 11.2.1
host tt1 (192.168.2.156), OEL 6.3. x86_64, TimesTen 11.2.2.4.1, Golden Gate 11.1.1

In this example I use complicated example, because I'm setting a replication between different databases (Oracle DB and TimesTen), different platforms (x86 and x86_64) and different golden gate versions (11.2.1 and 11.1.1).

1. First of all, install Golden Gate software on each host. I've already written about it here.

2. Prepare the source database (host db) for replication.

Run the following commands as sysdba:

shutdown immediate;
startup mount;
alter database archivelog;
alter database open;
select log_mode from v$database;
alter database add supplemental log data;
alter system set recyclebin=off scope=spfile;
purge dba_recyclebin;

In this case I've enabled acrhivelog mode for database, added additional information into log files and turned off the recyclebin feature. Next, create a schema that will contain the Oracle GoldenGate DDL objects (this step is not necessary, because the DDL replication doesn't work in heterogenous environment).

create tablespace gg_tbls datafile '/u01/app/oracle/oradata/orcl/gg_tbls.dbf' size 100m reuse autoextend on;
create user ggate identified by oracle default tablespace gg_tbls quota unlimited on gg_tbls;
grant create session, connect, resource to ggate;
grant dba to ggate; -- just in case
grant execute on utl_file to ggate;

Next, run the following scripts:

SQL> @marker_setup.sql
SQL> @ddl_setup.sql
SQL> @role_setup.sql
SQL> grant GGS_GGSUSER_ROLE to ggate;
SQL> @ddl_enable.sql

3. Create the schemes and tables for replication on both hosts (db, tt1).

Create the scheme and the table for replication (db host):

create user source identified by oracle default tablespace users temporary tablespace temp;
grant connect,resource,unlimited tablespace to source;
connect source/oracle
create table t1 (id number primary key, name varchar2(50));

Next, prepare the target database (host tt1) for replication. Create a following DSN (Don't forget that database character set should be the same on both hosts):

[gg_rep]
Driver=/u01/app/oracle/product/11.2.2.4.1/TimesTen/tt1122/lib/libtten.so
DataStore=/u01/app/oracle/product/datastore/gg_rep
PermSize=80
TempSize=32
PLSQL=1
DatabaseCharacterSet=WE8MSWIN1252
...
[gg_rep_cl]
TTC_SERVER=tt1
TTC_SERVER_DSN=gg_rep

I've created the two DSN, because Golden Gate uses a client\server connection to TimesTen. Next, create a user and table for replication.

[oracle@tt1 gg]$ ttisql gg_rep

create user target identified by oracle;
grant connect, create table to target;
grant admin to target;
connect "DSN=gg_rep;UID=target;PWD=oracle";
create table t1 (id number primary key, name varchar2(50));

4. Golden Gate configuration

4.1. Source host configuration
Add information about your Oracle GoldenGate DDL scheme into file GLOBALS.

[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> EDIT PARAMS ./GLOBALS
GGSCHEMA ggate

GGSCI (db.us.oracle.com) 2>

Start manager process.

GGSCI (db.us.oracle.com) 2> edit params mgr
PORT 7809

GGSCI (db.us.oracle.com) 3> start manager

Manager started.

GGSCI (db.us.oracle.com) 4> info mgr

Manager is running (IP port db.us.oracle.com.7809).

GGSCI (db.us.oracle.com) 5>

dblogin userid ggate password oracle
add schematrandata source
add extract ext1, tranlog, begin now
add exttrail /u01/app/oracle/product/gg/dirdat/tr, extract ext1

Edit file ext1 like the following:

GGSCI (db.us.oracle.com) 6> edit params ext1
extract ext1
userid ggate, password oracle
exttrail /u01/app/oracle/product/gg/dirdat/tr, format release 11.1
table source.t1;

I added the parameter "format release 11.1", because I use 11.1.1 Golden Gate version on target. Then, add one more extract.

add extract pump1, exttrailsource /u01/app/oracle/product/gg/dirdat/tr , begin now
add rmttrail /u01/app/oracle/product/gg/dirdat/tr, extract pump1

Edit file pump1 like the following:

GGSCI (db.us.oracle.com) 7> edit params pump1
EXTRACT pump1
USERID ggate, PASSWORD oracle
RMTHOST tt1, MGRPORT 7809
RMTTRAIL /u01/app/oracle/product/gg/dirdat/tr, format release 11.1
PASSTHRU
table source.t1;

After that run all extracts.

start extract ext1
start extract pump1
info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     EXT1        00:00:00      00:00:28
EXTRACT     RUNNING     PUMP1       00:00:00      00:00:03

4.2. Creating DEF file on source host.
I have to create a DEF file for replicated object for avoiding the following error:

2013-02-20 12:27:14  ERROR   OGG-01163  Oracle GoldenGate Delivery for TimesTen, rep1.prm:  
Bad column length (8) specified for column ID in table SOURCE.T1, maximum allowable length is 3.
2013-02-20 12:27:14  ERROR   OGG-01668  Oracle GoldenGate Delivery for TimesTen, rep1.prm:  PROCESS ABENDING.

So, let's create DEF file. The parameter file is the following (./dirprm/defgen.prm):

defsfile ./dirdef/tt.defs
userid ggate password oracle
table source.t1;

Then, run defgen utility.

./defgen paramfile ./dirprm/defgen.prm

BUT, this method doesn't work in my case (I use different Golden Gate versions). Details in support.oracle.com (BUG:13999007 - SOURCE DEF FILE INCORRECT, CAUSE REPLICAT READ INCORRECT). Solution is - using NOEXTATTR parameter (Replicat abend with ERROR OGG-00303 Problem at line xx. Expecting file, table, or record definition. [ID 1455370.1]). In my case I have to create a DEF file by the following command:

./defgen paramfile ./dirprm/defgen.prm NOEXTATTR

In this case DEF file will have a suitable format for 11.1.1 version. Copy DEF file (/dirdef/tt.defs) to tt1 host.

4.3. Target host configuration

Change the GLOBAL file.

GGSCI (tt1) 1> EDIT PARAMS ./GLOBALS
CHECKPOINTTABLE target.ggchkpt

Start manager process.

GGSCI (tt1) 2> edit params mgr
PORT 7809

GGSCI (tt1) 3> start manager

Manager started.

GGSCI (tt1) 4> info mgr

Manager is running (IP port tt1.7809).

GGSCI (tt1) 5>

Create the checkpoint table and add replicat.

dblogin SOURCEDB gg_rep_cl userid target password oracle
add checkpointtable target.ggchkpt
add replicat rep1, exttrail /u01/app/oracle/product/gg/dirdat/tr, begin now

Edit file rep1 and start replicat.

GGSCI (tt1) 5> edit params rep1
REPLICAT rep1
SOURCEDEFS /u01/app/oracle/product/gg/dirdef/tt.defs
TARGETDB gg_rep_cl, USERID target, PASSWORD oracle
discardfile /u01/app/oracle/product/gg/discard/rep1_discard.txt, append, megabytes 10
map source.t1, target target.t1;

GGSCI (tt1) 6> start replicat rep1

Sending START request to MANAGER ...
REPLICAT REP1 starting

GGSCI (tt1) 7> info all

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING
REPLICAT    RUNNING     REP1        00:00:00      00:00:03

5. Checking replication
DB host.

[oracle@db ggate]$ sqlplus source/oracle

SQL*Plus: Release 11.2.0.3.0 Production on Mon Feb 25 15:25:38 2013

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 * from t1;

no rows selected

SQL>

TimesTen.

[oracle@tt1 gg]$ ttisql "DSN=gg_rep;UID=target;PWD=oracle"

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


connect "DSN=gg_rep;UID=target;PWD=oracle";
Connection successful: DSN=gg_rep;UID=target;DataStore=/u01/app/oracle/product/datastore/gg_rep;DatabaseCharacterSet=WE8MSWIN1252;ConnectionCharacterSet=US7ASCII;DRIVER=/u01/app/oracle/product/11.2.2.4.1/TimesTen/tt1122/lib/libtten.so;PermSize=80;TempSize=32;TypeMode=0;
(Default setting AutoCommit=1)
Command> select * from t1;
0 rows found.
Command>

Insert a row into t1 table.

SQL> insert into t1 values (1,'test');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t1;

        ID NAME
---------- --------------------------------------------------
         1 test

SQL>

Check TimesTen.

Command> select * from t1;
< 1, test >
1 row found.
Command>

Now, update the row.

SQL> update t1 set name='test3' where id = 1;

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from t1;

        ID NAME
---------- --------------------------------------------------
         1 test3

SQL>

Check TimesTen.

Command> select * from t1;
< 1, test3 >
1 row found.
Command>

Replication works fine.

Calling Java code from PLSQL

Create a simple java class.

[oracle@db ~]$ sqlplus oratt/oracle

SQL*Plus: Release 11.2.0.3.0 Production on Thu Feb 7 18:16:27 2013

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> ! cat Factorial.java
public class Factorial {
  public static int getFactorialValue(int a) {
    if (a == 1) return 1;
    else return a * getFactorialValue(a - 1);
  }
}

SQL>

There are a lot of options for loading the java class (I'll describe only two of them).

1. Load the file by using loadjava utility

SQL> ! loadjava -user oratt/oracle Factorial.java

SQL>

select object_name, object_type from user_objects where object_type like 'J%';

SQL> select object_name, object_type from user_objects where object_type like 'J%';

OBJECT_NAME                    OBJECT_TYPE
------------------------------ -------------------
Factorial                      JAVA SOURCE
Factorial                      JAVA CLASS

SQL>

2. Create java statement Before that let's delete Factorial class from database

SQL> !dropjava -user oratt/oracle Factorial.java

SQL> select object_name, object_type from user_objects where object_type like 'J%';

no rows selected

SQL> CREATE JAVA SOURCE NAMED "Factorial" AS
   2   public class Factorial {
   3     public static int getFactorialValue(int a) {
   4       if (a == 1) return 1;
   5       else return a * getFactorialValue(a - 1);
   6     }
   7   }
   8 /  

Java created.

SQL> select object_name, object_type from user_objects where object_type like 'J%';

OBJECT_NAME                    OBJECT_TYPE
------------------------------ -------------------
Factorial                      JAVA SOURCE
Factorial                      JAVA CLASS

SQL>

Now, create a package.

SQL> CREATE OR REPLACE PACKAGE java_pack AS
   2   function factorial_func(x in pls_integer) return pls_integer;
   3 END java_pack;
   4 /

Package created.

SQL> CREATE OR REPLACE PACKAGE BODY java_pack AS
   2
   3   function factorial_func(x in pls_integer)
   4    return  pls_integer
   5   as language java
   6   name 'Factorial.getFactorialValue (int) return int';
   7
   8 END java_pack;
   9 /

Package body created.

SQL>

SQL> exec dbms_output.put_line(java_pack.factorial_func(15));
2004310016

PL/SQL procedure successfully completed.

SQL> select java_pack.factorial_func(10) from dual;

JAVA_PACK.FACTORIAL_FUNC(10)
----------------------------
                     3628800

SQL>

Is you can see, its very easy to use Java code in PLSQL. There is only one thing you should remember - it is a Oracle Database JVM version (DB Version 11.2 - Java 1.5.0 (1.5.0_01)) I would recommend you to read the following notes:

What Version of Java is Compatible With The Database JVM? [ID 438294.1]
How to identify JDK version running in Oracle Database JVM [ID 331673.1]

Calling external C function from PLSQL

In this post I'll describe the process of calling external C function from PLSQL.

1. Create a simple C function. (in this example I use basic factorial function)

[oracle@db /]$ cd /u01/app/oracle/product/
[oracle@db product]$ ls
11.2.0
[oracle@db product]$ mkdir c_lib
[oracle@db product]$ ls
11.2.0  c_lib
[oracle@db product]$ cd c_lib
[oracle@db c_lib]$ touch factorial.c
[oracle@db c_lib]$ cat factorial.c
int getVal (int a){
  if(a!=1){
    return(a * getVal(a-1));
    }
  else return 1;
}
[oracle@db c_lib]$

1.1 Compile it and create a shared library.

[oracle@db c_lib]$ gcc -fPIC -c factorial.c
[oracle@db c_lib]$ gcc -shared -o factorial.so factorial.o
[oracle@db c_lib]$ ls
factorial.c  factorial.o  factorial.so
[oracle@db c_lib]$

Now we have a shared library. Lets set up the listener.
2. Modify the listener.ora.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = db)(PORT = 1521))
                   (ADDRESS = (PROTOCOL = IPC)(KEY = extproc)) 
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
     (SID_NAME = PLSExtProc)
     (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
     (PROGRAM = extproc)
     (ENVS = "EXTPROC_DLLS=/u01/app/oracle/product/c_lib/factorial.so")
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle

Restart listener.

[oracle@db admin]$ lsnrctl stop listener

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 05-FEB-2013 15:31:01

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=db)(PORT=1521)))
The command completed successfully
[oracle@db admin]$ lsnrctl start listener

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 05-FEB-2013 15:31:09

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

Starting /u01/app/oracle/product/11.2.0/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.3.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/db/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=extproc)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=db)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     listener
Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date                05-FEB-2013 15:31:09
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/db/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=extproc)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@db admin]$

Check the listener status:

[oracle@db admin]$ lsnrctl status listener

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 05-FEB-2013 15:32:13

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=db)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     listener
Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date                05-FEB-2013 15:31:09
Uptime                    0 days 0 hr. 1 min. 3 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/db/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=extproc)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "orcl" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully

3. Create a library (as sysdba)

[oracle@db c_lib]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Feb 4 18:01:02 2013

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> create or replace library c_factorial as '/u01/app/oracle/product/c_lib/factorial.so' 
   2 /

Library created.

SQL> select owner, library_name, file_spec, status from all_libraries where lower(library_name) = 'c_factorial';

OWNER     LIBRARY_NAME    FILE_SPEC                                  STATUS
--------- --------------- ------------------------------------------ -------
SYS       C_FACTORIAL     /u01/app/oracle/product/c_lib/factorial.so VALID

SQL> grant execute on c_factorial to oratt;

Grant succeeded.

SQL>

4. Create a package (oratt user).

SQL> CREATE OR REPLACE PACKAGE c_pack AS
   2   function factorial_func(x in integer) return pls_integer;
   3 END;
   4 / 
 
Package created.
 
SQL> CREATE OR REPLACE PACKAGE BODY c_pack AS 
   2   function factorial_func(x in integer)
   3     return  pls_integer
   4   as language c
   5   library sys.c_factorial
   6   name "getVal"; 
   7 END;
   8 / 
 
Package Body created.

5. Execute factorial_func function

SQL> set serveroutput on
SQL> begin
   2   dbms_output.put_line(c_pack.factorial_func(5));
   3 end;
   4 /
120

PL/SQL procedure successfully completed.

SQL> select c_pack.factorial_func(5) from dual;

C_PACK.FACTORIAL_FUNC(5)
------------------------
                     120

SQL>