Caching Using GoldenGate Example

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 cache1.

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 oratt with password ttpwd.

There is Oracle cache administration user cacheadmin with password ttpwd, which has SELECT privileges on the tables that are to be cached. The passwords for the application schema user and the cache administration user can be different.

The oratt 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)
);
Table created.
CREATE TABLE orders
(
    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)
);
Table created.
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)
);
Table created.
INSERT INTO customer VALUES('C000000001', 'Fred', 'Bloggs', 'Nice Villas, 
  Pleasant Town', '+16072321234');
1 row inserted.
INSERT INTO orders VALUES(123456, 'C000000001', '21/10/2021', 'N', 430.46);
1 row inserted.
INSERT INTO item VALUES(1, 123456, 'I000001725', 2, 15.25, 30.50);
1 row inserted.
INSERT INTO item VALUES(2, 123456, 'I000207351', 4, 99.99, 399.96);
1 row inserted.
COMMIT;

This example performs the following steps: