Example of Caching Using GoldenGate

A complete end to end example is useful in demonstrating how to create a read-only cache using GoldenGate.

In this example, the FQDN of the system hosting the TimesTen database is tthost1.example.com.

The DSN for the TimesTen database is ecommerce.

The GoldenGate deployment mode for TimesTen is on-box using direct mode connectivity.

Prerequisites

A functioning Oracle database is a recent version.
A recent version of GoldenGate is installed on the Oracle database host.
A functioning TimesTen instance is running at least TimesTen 22.1.1.1.0.
GoldenGate 21.3 is installed on the TimesTen host.
The TimesTen cache database has been configured and created.
DatabaseCharacterSet and ConnectionCharacterSet correctly set in the TimesTen database.

Oracle User and Source Tables

The Oracle application schema owner is appuser with password OR-AbCD123-zqpx.

There is also an Oracle user named ggapply with password OR-zXy087-TvrQ, which has SELECT privileges on the tables that are to be cached.

The appuser user owns three tables that will be cached in TimesTen:

CREATE TABLE customer
(
    custid     VARCHAR2(10) NOT NULL,
    firstname  VARCHAR2(20) NOT NULL,
    lastname   VARCHAR2(20) NOT NULL,
    address    VARCHAR2(128) NOT NULL,
    phone      VARCHAR2(16) NOT NULL,
    PRIMARY KEY (custid)
);
CREATE TABLE order
(
    orderid    NUMBER(10,0) NOT NULL,
    custid     VARCHAR2(10) NOT NULL,
    orderdate  DATE NOT NULL,
    priority   CHAR(1),
    amount     NUMBER(12,2) NOT NULL,
    PRIMARY KEY (orderid),
    FOREIGN KEY (custid) REFERENCES customer(custid)
);
CREATE TABLE item
(
    itemno     NUMBER(4,0) NOT NULL,
    orderid    NUMBER(10,0) NOT NULL,
    itemcode   VARCHAR2(10) NOT NULL,
    quantity   NUMBER(4,0) NOT NULL,
    price      NUMBER(6,2) NOT NULL,
    totalvalue NUMBER(10,2) NOT NULL,
    PRIMARY KEY (orderid,itemno),
    FOREIGN KEY (orderid) REFERENCES order(orderid)
);
INSERT INTO customer VALUES('C000000001', 'Fred', 'Bloggs', 'Nice Villas, 
  Pleasant Town', '+16072321234');
INSERT INTO order VALUES(123456, 'C000000001', '21/10/2021', 'N', 430.46);
INSERT INTO item VALUES(1, 123456, 'I000001725', 2, 15.25, 30.50);
INSERT INTO item VALUES(2, 123456, 'I000207351', 4, 99.99, 399.96);
COMMIT;

This example performs the following steps:

Prepare TimesTen Users and Target Tables

Perform procedures to create users and the target tables that support GoldenGate replication.

The TimesTen application user is appuser with password TT-app123-XyZ.

The TimesTen GoldenGate apply user is ggapply with password GG-912-azq.

To create these users in your TimesTen database, connect, using the ttIsql utility, to the TimesTen database as the instance administrator user and execute:
```
CREATE USER appuser IDENTIFIED BY TT-app123-XyZ;
GRANT CREATE SESSION, CREATE SYNONYM TO appuser;
CREATE USER ggapply IDENTIFIED BY GG-912-azq;
GRANT CREATE SESSION, CREATE TABLE TO ggapply;
```
Create the target tables in the TimesTen database. Make the tables owned by the user ggapply, not the application user (appuser). Connect to the database, using ttIsql, as the user ggapply:
```
ttIsql -connStr "DSN=ecommerce;UID=ggapply;PWD=GG-912-azq"
```

Execute the following SQL statements to create the tables and grant the necessary permissions:

CREATE TABLE customer
(
    custid     VARCHAR2(10) NOT NULL,
    firstname  VARCHAR2(20) NOT NULL,
    lastname   VARCHAR2(20) NOT NULL,
    address    VARCHAR2(128) NOT NULL,
    phone      VARCHAR2(16) NOT NULL,
    PRIMARY KEY (custid)
);
CREATE TABLE order
(
    orderid    NUMBER(10,0) NOT NULL,
    custid     VARCHAR2(10) NOT NULL,
    orderdate  DATE NOT NULL,
    priority   CHAR(1),
    amount     NUMBER(12,2) NOT NULL,
    PRIMARY KEY (orderid),
    FOREIGN KEY (custid) REFERENCES customer(custid)
);
CREATE TABLE item
(
    itemno     NUMBER(4,0) NOT NULL,
    orderid    NUMBER(10,0) NOT NULL,
    itemcode   VARCHAR2(10) NOT NULL,
    quantity   NUMBER(4,0) NOT NULL,
    price      NUMBER(6,2) NOT NULL,
    totalvalue NUMBER(10,2) NOT NULL,
    PRIMARY KEY (orderid,itemno),
    FOREIGN KEY (orderid) REFERENCES order(orderid)
);
GRANT SELECT ON customer TO appuser;
GRANT SELECT ON order TO appuser;
GRANT SELECT ON item TO appuser;
quit;

Connect as the user appuser and create synonyms for the tables:

ttIsql -connStr "DSN=ecommerce;UID=appuser; TT-app123-XyZ "
CREATE SYNONYM customer FOR ggapply.customer;
CREATE SYNONYM order FOR ggapply.order;
CREATE SYNONYM item FOR ggapply.item;

Prepare Oracle Database for GoldenGate Replication

Perform a few procedures to prepare the Oracle database to use GoldenGate replication.

On the Oracle Database system, prepare the parameter file for the GoldenGate Extract process. Using a text editor, create the file gg_home/dirprm/tt.prm with the following contents:
```
EXTRACT tt
USERID appuser, PASSWORD OR-AbCD123-zqpx
RMTHOST tthost1.example.com, MGRPORT 7809
RMTTRAIL dirdat/tr
TABLE appuser.customer;
TABLE appuser.order;
TABLE appuser.item;
```
Start the GGSCI utility. Assuming that the GoldenGate home directory is in the $GG_HOME directory:
```
cd $GG_HOME
./ggsci
```
From here on all commands use GGSCI.
Start the GoldenGate Manager:
```
start manager
```

Configure Oracle for GoldenGate:

DBLOGIN USERID appuser, PASSWORD OR-AbCD123-zqpx
ADD SCHEMATRANDATA appuser
ADD EXTRACT tt, INTEGRATED TRANLOG, BEGIN NOW
REGISTER EXTRACT tt, DATABASE
ADD RMTTRAIL dirdat/tr, EXTRACT dirdat/tr

Start the GoldenGate Extract process using the file you configured above:
```
start tt
```

Prepare the TimesTen Database for GoldenGate Replication

There are a few procedures to perform when preparing the TimesTen database to receive GoldenGate replication.

On the TimesTen host, use a text editor to create the Replicat parameter file gg_home/dirprm/REP.prm with the following contents:
```
REPLICAT rep
TARGETDB ecommerce, USERID ggapply, PASSWORD GG-912-azq
BATCHSQL
APPLY_PARALLELISM 4
MAP appuser.*, TARGET ggapply.*;
```
Start the GGSCI utility. Assuming that the GoldenGate home directory is in the $GG_HOME directory:
```
cd $GG_HOME
./ggsci
```
From here on all commands use GGSCI.
Start the GoldenGate Manager:
```
start manager
```

DBLOGIN SOURCEDB ecommerce, USERID ggapply, PASSWORD GG-912-azq
ADD CHECKPOINTTABLE ggapply.gg_ckpt_table
ADD REPLICAT rep, EXTTRAIL dirdat/tr, PARALLEL, CHECKPOINTTABLE 
    ggapply.gg_ckpt_table

Perform the Initial Data Load

The first procedure to starting the cache operations is to perform an initial data load of what is currently in the tables that are to be cached.

On the host with the Oracle database, determine the current SCN value (using SQL*Plus):
```
SELECT CURRENT_SCN FROM V$DATABASE;
 
CURRENT_SCN
-----------
    2791297
```
On the host with the TimesTen database, connect to the TimesTen database as the ggapply user specifying both the TimesTen and Oracle database passwords for this user:
```
ttIsql -connStr "DSN=ecommerce;UID=ggaply;PWD=GG-912-azq;OraclePWD=OR-zXy087-TvrQ
```

Load the data for each of the tables based on the Oracle database SCN value determined above:

call ttLoadFromOracle('ggapply', 'customer', 
      'SELECT * FROM appuser.customer AS OF scn 2791297');
call ttLoadFromOracle('ggapply', 'order', 'SELECT * FROM appuser.order 
      AS OF SCN 2791297');
call ttLoadFromOracle('ggapply', item, 'SELECT * FROM appuser.item 
      AS OF SCN 2791297');

Update the optimizer statistics for the tables that you just loaded to ensure optimal query plans in TimesTen:
```
statsupdate customer;
statsupdate order;
statsupdate item;
quit;
```

Start Real-Time Replication

Using GGSCI, start a Replicat process beginning with the SCN value used for the data load.

Assuming that the GoldenGate home directory is in the $GG_HOME directory.

cd $GG_HOME
./ggsci
START REPLICAT rep, AFTERCSN 2791297

Verify That Replication is Working

Once you have replication set up, verify that replication is working.

On the Oracle database, insert, update, and/or delete rows to add new data into the replicated tables.

On the TimesTen database, select from the replicated tables and verify that the changes are being propagated from the Oracle database.

You can also check the status of a Replicat process using the GGSCI command:

INFO REPLICAT rep