Configuring GoldenGate to Provide Cache Refresh Functionality for TimesTen

You can set up a caching environment between GoldenGate and TimesTen.

The high-level steps for setting up a GoldenGate caching environment with TimesTen are as follows:

Install, configure and prepare the source database. In most scenarios, the source database already exists and contains the tables that you desire to cache.
Install GoldenGate at the source database and prepare the source database for use with GoldenGate.
Configure GoldenGate data capture for the source database tables that you wish to cache in TimesTen.
Decide if you will run the GoldenGate apply processes on the same host as the target TimesTen database (an on-box deployment) or on a different host to the target TimesTen database (an off-box deployment). See Choosing On-Box or Off-Box for Deployment of a GoldenGate Replicat Process.
Install, configure and prepare the TimesTen database that acts as a cache. In general, deploy the cache on a different host from the source database, which is the host where the application processes run. See Installing and Configuring TimesTen and the Target Database.
Create the necessary database users in TimesTen. Create the TimesTen tables that correspond to the tables that you wish to cache from the source database. Grant the necessary privileges and create synonyms (if desired). See Create TimesTen Database Users, Tables and Synonyms.
If you have chosen an off-box deployment, install a TimesTen client instance on the GoldenGate apply host and configure it to connect to the TimesTen database. See Installing and Configuring a TimesTen Client Instance (for Off-Box Deployments Only).
Configure the GoldenGate apply mechanism (Replicat process) for the TimesTen database tables that correspond to the source database tables. See Configure GoldenGate Data Apply.
Perform an initial data load to populate the TimesTen cache tables from the corresponding source database tables. This process usually involves some GoldenGate specific actions as well as the actual data loading. See Perform an Initial Load.
Activate GoldenGate continuous real-time replication to provide ongoing data change synchronization from the source database to TimesTen. See Start GoldenGate Continuous Real-Time Replication.

The remainder of this chapter assumes the following for simplicity:

The source database is an Oracle database running a recent release (18c or later).
The target database is TimesTen release 22.1.1.1.0 or later.
The GoldenGate release is 21.3 or later.
The actions required for steps (1) through (3) above have been correctly carried out. This chapter does not cover these steps since they are covered in detail in the GoldenGate documentation and by many other online sources and articles.
This chapter primarily focuses on steps (4) through (10).

Choosing On-Box or Off-Box for Deployment of a GoldenGate Replicat Process

When you deploy GoldenGate for TimesTen, you ultimately instantiate a set of processes that are responsible for receiving all replicated data from the GoldenGate source, storing it in a (local) trail file, reading the replicated data from the trail file and applying it to the target TimesTen database.

If you deploy GoldenGate for TimesTen in the same host, VM, container, or pod as the target TimesTen database, then you can use either direct mode or client-server connectivity. This is known as an on-box deployment in GoldenGate terms. Generally, direct mode connectivity is preferred and recommended for this scenario.
If you deploy GoldenGate for TimesTen in a different host, VM, container, or pod to the target TimesTen database, then you have to use client-server connectivity. In GoldenGate terms this is an off-box deployment.

The following sections describe the different connectivity options available to application processes, including GoldenGate processes:

Direct Mode Connectivity

TimesTen direct mode is a local only connectivity method that enables applications to interact with a local (same host) TimesTen database.

Direct mode connections use a highly efficient mechanism that eliminates inter-process communication, context switches and other overheads. Direct mode delivers the lowest possible data access latency together with high throughput. Use of direct mode is limited to application processes that are executing in one of the following environments:

In the same bare metal host as the TimesTen database.
In the same virtual machine as the TimesTen database.
In the same container as the TimesTen database or, for Kubernetes environments, in a container in the same pod as the TimesTen database container.

Direct mode connections offer better performance with less overhead. Using direct mode connections will significantly increase the complexity if you want high availability when using a combination of TimesTen and Goldengate configurations. For example, when you combine GoldenGate with either a TimesTen active-standby pair or TimesTen Scaleout, automated failover and recovery for GoldenGate is significantly more complex compared to an off-box configuration using client-server connections.

Host resources (CPU, memory, storage) must be sufficient to accommodate the TimesTen database instance, the TimesTen database, all GoldenGate processes, all associated processing plus any other local processing (such as applications).

Client-Server Connectivity

TimesTen client-server mode provides regular client-server connectivity through TCP/IP connections.

The applications can run anywhere that has suitable network connectivity to the host where TimesTen is running. Client-server potentially offers more flexibility than direct mode, but this flexibility comes at the cost of increased overhead and lower performance due to network latency, additional processing, and so on.

Installing and Configuring TimesTen and the Target Database

You can install and configure TimesTen and the target database for both on-box and off-box deployments.

Note:

If you already have a suitable TimesTen instance and database that is configured for connectivity to the source Oracle database, then you can skip this step.

Prepare an Oracle Database Net Services tnsnames.ora file with a suitable TNS entry to enable connectivity from the TimesTen host system to the source Oracle database. Save this file in a suitable directory.

The following example creates a TNS entry called myoradb:
```
MYORADB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oradb.example.net)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = myoradb)
    )
  )
```
Set the TNS_ADMIN location for the cache agent with the ttInstanceModify -tnsadmin option to set the path to the tnsnames.ora file. Specify the full path to the directory where the file is located.
```
ttInstanceModify -tnsadmin /TimesTen/conf
```
For cache in TimesTen Classic, set the TNS_ADMIN environment variable to indicate the full path to the directory where the tnsnames.ora file is located. Set this variable in the user's profile script so that it persists.
```
export TNS_ADMIN=/TimesTen/tnsadmin
```
Restart the main daemon to capture this setting.
```
ttDaemonAdmin -stop
ttDaemonAdmin -start
```
Prepare the host where both TimesTen and the target database will reside. Install TimesTen and create a TimesTen instance. When creating the instance, enable the instance to use the TNS_ADMIN value that it can detect from the environment. See Installation of TimesTen Classic on Linux or UNIX in the Oracle TimesTen In-Memory Database Installation, Migration, and Upgrade Guide.
Once you have a functional TimesTen instance, define a Data Source Name (DSN) in the instance sys.odbc.ini file. The DSN defines all of the parameters for the target database.

The following example shows a DSN, myappdb, for the TimesTen database. This type of DSN is known as a Server DSN as it defines all of the attributes for the database and defines an endpoint for direct mode connections. The value for the OracleNetServiceName attribute should be the name of the TNS entry (myoradb in this example) that was configured previously. The values specified for DatabaseCharacterSet and ConnectionCharacterSet must match the source Oracle Database character set.
```
[ODBC Data Sources]
myappdb=TimesTen 22.1 Driver
 
[myappdb]
DataStore=/disk1/db/ckpt/myappdb
LogDir=/disk2/db/log
PermSize=8192
TempSize=512
LogBufMB=1024
LogFileSize=1024
MemoryLock=4
DatabaseCharacterSet=AL32UTF8
ConnectionCharacterSet=AL32UTF8
OracleNetServiceName=myoradb
```
While logged in as the TimesTen instance administration user, set the environment for the TimesTen instance. Connect to the DSN using the ttIsql utility, which creates the target database.

Create TimesTen Database Users, Tables and Synonyms

Perform steps to create the TimesTen database users, tables and synonyms.

While connected to the TimesTen database as the TimesTen instance administrator user, create a dedicated GoldenGate apply database user for the GoldenGate apply processes. This user owns all of the cached tables in TimesTen. Make sure that the dedicated GoldenGate apply database user has all necessary privileges on the cached tables.

This example creates a dedicated GoldenGate apply database user called ggapply.
```
CREATE USER ggapply IDENTIFIED BY "mypwd";
GRANT CREATE SESSION, CREATE TABLE to ggapply;
```
Applications should connect to the database as different users from the GoldenGate apply database user. As always, application users should be granted the minimum set of privileges consistent with the operations needed to perform. This example creates two application users named appuser1 and appuser2:
```
CREATE USER appuser1 IDENTIFIED BY some_suitable_password;
GRANT CREATE SESSION to appuser1;
CREATE USER appuser2 IDENTIFIED BY some_suitable_password;
GRANT CREATE SESSION to appuser2;
```
GoldenGate for Oracle TimesTen supports delivery of data to user tables, instead of cache groups. Since GoldenGate uses regular tables instead of cache groups, create your cache tables in TimesTen as regular tables (using the CREATE TABLE statement) and not as cache groups (using the CREATE READONLY CACHE GROUP statement).

This step creates the required cache tables in TimesTen. Make sure that the table definitions are compatible with the corresponding tables in the source database. You should create the tables owned by the ggapply database user (you need to be connected either as ggapply or as some other user with ADMIN privileges):
```
CREATE TABLE ggapply.cachetab1 ( … );
CREATE TABLE ggapply.cachetab2 ( … );
…
```
When using GoldenGate as the cache refresh mechanism, any read-only cached tables in TimesTen are not truly read-only. Applications are not prevented from modifying data in the tables (provided that they have suitable access privileges on the tables). However, any such modifications can be overwritten if GoldenGate refreshes newly modified data to the table from the back-end database. You can mitigate this by ensuring that application users only have read access to the cache tables. These tables must be owned by a user other than the application users, such as the dedicated GoldenGate apply database user to ensure that the GoldenGate apply process to write to these same tables.

Grant SELECT (read) privileges on the cache tables to the application users:
```
GRANT SELECT ON ggapply.cachetab1 TO appuser1, appuser2, …;
GRANT SELECT ON ggapply.cachetab2 TO appuser1, appuser2, …;
```
Generally, for convenience and transparency, each application user may want to have a private synonym for the application tables that it needs to query to avoid having to always qualify the table name with the name of the dedicated GoldenGate apply database user ggapply user. For example:
```
CREATE SYNONYM appuser1.cachetab1 FOR ggapply.cachetab1;
CREATE SYNONYM appuser2.cachetab1 FOR ggapply.cachetab1;
CREATE SYNONYM appuser1.cachetab2 FOR ggapply.cachetab2;
CREATE SYNONYM appuser2.cachetab2 FOR ggapply.cachetab2;
```

Installing and Configuring a TimesTen Client Instance (for Off-Box Deployments Only)

When using an off-box deployment, you need to prepare the host where GoldenGate for TimesTen will be installed.

Create a TimesTen installation and from that a TimesTen client instance. Consult Installation of TimesTen Classic on Linux or UNIX in the Oracle TimesTen In-Memory Database Installation, Migration, and Upgrade Guide.

Add a suitable client DSN to the client instance sys.odbc.ini file to enable connections to the target TimesTen database that was configured in the Installing and Configuring TimesTen and the Target Database section.

In this example, the client DSN is named myappdbcs and the host name where the TimesTen database is running is myttserver.example.com. The TimesTen server is listening on port 6625 (the default). This hostname must be resolvable on the client system through DNS or /etc/hosts and regular TCP connectivity must be functional between the client and server systems. Port 6625 on the server must not be blocked by a firewall. Note the setting for ConnectionCharacterSet.

[ODBC Data Sources]
myappdbcs=TimesTen 22.1 Client Driver
 
[myappdbcs]
TTC_SERVER=myttserver.example.com/6625
TTC_SERVER_DSN=myappdb
ConnectionCharacterSet=AL32UTF8

Configure GoldenGate Data Apply

Ensure that your environment is set for your local TimesTen instance (server or client) and change your directory to the GoldenGate installation directory.

Start the GGSCI utility and use it to perform the following steps:
```
./ggsci
```
Start the manager process:
```
START MANAGER
```
Login to your TimesTen database using the DBLOGIN command. If you are using off-box deployment, use the client DSN; otherwise, use the server DSN. The following example uses the server DSN of myappdb.
```
DBLOGIN SOURCEDB myappdb, USERID ggapply, PASSWORD ggapply_users_password
```
Create the GoldenGate checkpoint table. This is required for using a GoldenGate Replicat process with TimesTen. Choose a table name so that it does not conflict with your application tables:
```
ADD CHECKPOINTTABLE ggapply.checkpoint_table_name
```
Create a parallel Replicat group, which maximizes throughput. In this example, this group is called rep:
```
ADD REPLICAT rep, EXTTRAIL trail_name, PARALLEL, CHECKPOINTTABLE ggapply.checkpoint_table_name
```
The trail_name is the name of the remote trail that you specified for either the data pump or Extract process on the source server.
Create a parameter file for the Replicat group. In our example, this file should be gg_home/dirprm/REP.prm:
```
REPLICAT rep
TARGETDB myappdb, USERID ggapply, PASSWORD ggapply_users_password
BATCHSQL
MAP oraowner.sourcetab1, TARGET ggapply.cachetab1;
MAP oraowner.sourcetab2, TARGET ggapply.cachetab2;
…
```
Here oraowner.sourcetab1 is the table owner and name on the source database, and ggapply.cachetab1 is the table owner and name in the TimesTen database. Usually the table name is the same in both the source and the target, but this is not a requirement. You can specify multiple MAP directives or use wildcards for multiple tables.

For off-box deployment, use the client DSN instead of the server DSN. You can also use a GoldenGate credential store and USERIDALIAS for password encryption. For more information, see Using Oracle GoldenGate with Oracle Database.

If you are using a parallel Replicat process include the following line before the MAP statements:
```
APPLY_PARALLELISM 4
```
You can experiment with the number of apply threads to see which value provides the most optimal throughput in your environment.

Add any other parameters in this file that apply to your database environment.

Note:

When using TimesTen native caching, one option you have is to cache a subset of a table (specific columns and/or rows). This is also possible with GoldenGate, but these details are not covered in this document.

Perform an Initial Load

GoldenGate only refreshes cache tables in TimesTen with modified data. Thus, before starting a GoldenGate Replicat process for continuous replication, you need to perform an initial load of data to populate the cached tables in the TimesTen database with the rows from the source database tables.

The initial table data load is used to establish data synchronization when instantiating GoldenGate replication. In general, there may be a workload running against the source database tables while you do this.

To perform the initial load (and the switch for continuous replication), perform the following:

Make sure that you have started the GoldenGate Extract process on the source Oracle database. It is vital that GoldenGate has started change data capture and propagation on the source database before proceeding to the next step.
On the source Oracle database, determine the current SCN value. For example, run the following SQL query through SQL*Plus:
```
SELECT CURRENT_SCN FROM V$DATABASE;
```
In this example, the SCN value returned by this query is 12345678.
Connect to the TimesTen database as a suitable database user using the TimesTen ttIsql utility. This user must meet the following criteria:
1. The user must exist in both the target TimesTen database and the source Oracle database.
2. You must know the password for that user for both TimesTen and Oracle databases. The passwords may differ on each of the databases.
3. In TimesTen, the user must have a minimum of the CREATE SESSION and INSERT privileges on all tables to be loaded.
4. In the Oracle database, the user must have sufficient privileges to execute the load query.
The following example connects to the TimesTen database with the ggapply user that was created earlier:
```
ttIsql -connStr "DSN=myappdb;UID=ggaply;PwdWallet=/wallets/cacheadminwallet"
```
The initial table data load is used to establish data synchronization when instantiating GoldenGate replication. To achieve the best performance for the initial table data load:
- Use the TimesTen ttLoadFromOracle built-in procedure for the initial table data load if the backend database is an Oracle database. See ttLoadFromOracle in the Oracle TimesTen In-Memory Database Reference.
- Use the TimesTen ttBulkCp utility for the initial table data load if the backend database is a non-Oracle database. Export the table data in CSV format and then load it into TimesTen using the ttBulkCP utility. See ttBulkCp in the Oracle TimesTen In-Memory Database Reference.
Load the data for each of the GoldenGate target tables using the TimesTen ttLoadFromOracle built-in procedure, specifying a flashback query targeting the SCN value determined in step (2) above. For example:
```
call ttLoadFromOracle('ggapply', 'cachetab1', 'SELECT * FROM 
   oraowner.sourcetab1 AS OF SCN 12345678');
call ttLoadFromOracle('ggapply', 'cachetab2', 'SELECT * FROM 
   oraowner.sourcetab2 AS OF SCN 12345678');
…
```

Note:

If there are no dependencies (such as foreign key constraints) between tables, then you can load them in parallel using separate ttIsql sessions. Provided that resources are not a constraint, this can reduce the time required for the initial data load.

You have now populated the TimesTen cache tables with data from the source Oracle database. For more information, see Loading Data From an Oracle Database Into a TimesTen Table in the Oracle TimesTen In-Memory Database Operations Guide.

Start GoldenGate Continuous Real-Time Replication

Start the GoldenGate Replicat process rep using GGSCI, specifying the SCN value from which to start.

START REPLICAT rep, AFTERCSN 12345678

Note:

GoldenGate refers to this value as a CSN (Commit Sequence Number) rather than an SCN, hence the parameter name AFTERCSN.

You can see the changes are replicated from source database to TimesTen. You can also check the status of a Replicat process using the following GGSCI command.

INFO REPLICAT rep

You now have a working setup that uses GoldenGate to replicate data changes from your source Oracle database to your TimesTen cache database.