Configuring a TimesTen Database to Cache Oracle Database Data

Certain operations must be performed on the TimesTen database by the instance administrator or the TimesTen cache administration user.

Specify Database Connection Definition for Cache

You can modify certain cache-related connection attributes to define connection attributes.

Set the Net Service Name for the Oracle Database in the tnsnames.ora File

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. This is for access to Oracle Database data.

Ensure that the main daemon is stopped before you modify the tnsnames.ora file.
```
ttDaemonAdmin -stop
```
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 will persist.
```
export TNS_ADMIN=/TimesTen/conf
```
Restart the main daemon to capture this setting.
```
ttDaemonAdmin -start
```

Add the net service name for the Oracle database into the tnsnames.ora file. The following is an example of defining orcl in a tnsnames.ora file:

orcl =  
 (DESCRIPTION =   
   (ADDRESS = (PROTOCOL = TCP)(HOST = myhost)
     (PORT = 1521))     
   (CONNECT_DATA =       
     (SERVICE_NAME = myhost.example.com)))

For TimesTen Scaleout, use ttGridAdmin commands to import or export tnsnames.ora or sqlnet.ora configuration for connecting to an Oracle database. See Add the Oracle Database Net Service Name to the tnsnames.ora File in the Oracle TimesTen In-Memory Database Scaleout User's Guide.

Define a DSN for the TimesTen Classic Database

A TimesTen database that caches data from an Oracle database can be referenced by either a system DSN or a user DSN. A TimesTen database is implicitly created the first time the instance administrator user connects to it using a DSN. When creating a DSN for a TimesTen database that caches data from an Oracle database, pay special attention to the settings of the connection attributes.

See Managing TimesTen Databases in Oracle TimesTen In-Memory Database Operations Guide.

On UNIX or Linux, the system DSN is located in the timesten_home/conf/sys.odbc.ini file. As described in Connecting to a TimesTen Database, the sys.odbc.ini file contains the DSN definitions.

This example defines cache1 and cache1cs ODBC Data Source Names (DSNs).

Note:

ODBC is TimesTen’s native API, though TimesTen also provides, or supports, many other commonly used database APIs such as JDBC, Oracle Call Interface, ODP.NET, cx_Oracle (for Python) and node-oracledb (for Node.js).

Direct connection: The cache1 DSN is a direct mode, or server DSN. It uses the TimesTen 22.1 Driver. It defines the parameters and connectivity for a database hosted by this TimesTen instance. Tools, utilities, and applications running on this host (myhost) can connect through this DSN using TimesTen’s low latency ‘direct mode’ connectivity mechanism.
Client-server connection: This database is also accessible remotely using TimesTen’s client-server connectivity. The cache1cs DSN is a client DSN and uses the TimesTen 22.1 Client Driver. It defines connectivity parameters for a server DSN that tools, utilities, and applications can connect to using TimesTen’s client-server connectivity mechanism. In this example, the DSN defines client-server access for the local cache1 server DSN.

All of these connection attributes can be set in a direct DSN or a connection string, unless otherwise stated.

DataStore specifies the fully qualified directory path name of the database and the file name prefix. This name is not a file name. In this example, DataStore is set to /disk1/databases/database1.
PermSize specifies the allocated size of the database's permanent region in MB. The PermSize value must be smaller than the physical RAM on the machine. Set this to a value that enables you to store all of your data. The PermSize value could be from a few GB to several TB. This example sets the permanent region to 1024 MB.
TempSize indicates the total amount of memory in MB allocated to the temporary region for the database. This example sets the temporary region to 256 MB.
LogBufMB specifies the size of the internal transaction log buffer for the database. This example sets the transaction log buffer to 256 MB.
LogFileSize specifies the maximum size of transaction log files in megabytes. This example sets the maximum size of transaction log files to 256 MB.
DatabaseCharacterSet must match the Oracle database character set. In this example, the database character set is AL32UTF8.
Note:

You can determine the Oracle database character set by running the following query in SQL*Plus as any user:
```
SQL> SELECT value FROM nls_database_parameters 
       WHERE parameter='NLS_CHARACTERSET';
```
ConnectionCharacterSet specifies the character encoding for the connection. Generally, you should choose a connection character set that matches your terminal settings or data source. In this example, the connection character set is AL32UTF8.
OracleNetServiceName must be set to the net service name of the Oracle database instance. This example sets this to orcl. This is the same name that was set in the tnsnames.ora file.

For Microsoft Windows systems, the net service name of the Oracle database instance must be specified in the Oracle Net Service Name field of the TimesTen Cache tab within the TimesTen ODBC Setup dialog box.
CacheAdminWallet=1 specifies that credentials for the Oracle cache administration user that are registered with the ttCacheUidPwdSet built-in procedure are stored in an Oracle Wallet, rather than in memory.
UID specifies the name of the TimesTen cache administration user. The UID connection attribute can be specified in a direct DSN, a client DSN, or a connection string.
PwdWallet specifies the wallet in which credentials are stored for users. You can provide the TimesTen user name and password within a wallet. You can also provide the cache administrator users and respective passwords in a wallet. The cache administration user credentials are necessary when performing cache operations and connecting to the Oracle database.
If you are not using PwdWallet to specify a wallet, then use PWD to specify the password of the TimesTen cache administration user specified in the UID connection attribute. The PWD connection attribute can be specified in a Direct DSN, a Client DSN, or a connection string.
If you are not using PwdWallet to specify a wallet, then use OraclePWD to specify the password of the Oracle Database cache administration user that has the same name as the TimesTen cache administration user specified in the UID connection attribute.

Note:

See Create the TimesTen Users.
PassThrough can be set to control whether statements are to be run in the TimesTen database or passed through to be processed in the Oracle database. See Setting a Passthrough Level.
LockLevel must be set to its default of 0 (row-level locking) because cache does not support database-level locking.
ReplicationApplyOrdering and CacheAWTParallelism control parallel propagation of changes to TimesTen cache tables in an AWT cache group to the corresponding Oracle Database tables. See Improving AWT Throughput With Parallel Propagation to the Oracle Database.

Then, there is an entry for the client DSN. The client DSN specifies the location of the TimesTen database with the following attributes:

The TTC_Server_DSN attribute specifies the server DSN of the intended database.
The TTC_Server attribute specifies the server (and the port number if you do not want to use the default port number) for the database.

In the sys.odbc.ini file, create a TimesTen DSN cache1 and set the following connection attributes. The cache1 DSN specifies a TimesTen database that caches data from an Oracle database.

[ODBC Data Sources]
cache1=TimesTen 22.1 Driver
cache1cs=TimesTen 22.1 Client Driver

[cache1]
DataStore=/disk1/databases/database1
PermSize=1024
TempSize=256
LogBufMB=256
LogFileSize=256
DatabaseCharacterSet=AL32UTF8
ConnectionCharacterSet=AL32UTF8
OracleNetServiceName=orcl
CacheAdminWallet=1

[cache1cs]
TTC_SERVER_DSN=CACHE1
TTC_SERVER=myhost/6625
ConnectionCharacterSet=AL32UTF8

Define Database Definition and Connectable in TimesTen Scaleout

In TimesTen Scaleout, a database definition contains the description of a database. It defines the database name, as well as the attributes of the database. A database definition can be used to create a database.

Each database has one or more connectables associated with it. Connectables specify how applications connect to the database. A connectable defines a name that applications can use to connect to a database.

See Create a Database Definition for the TimesTen Database and Create a Connectable for the TimesTen Database in the Oracle TimesTen In-Memory Database Scaleout User's Guide for connection attributes that relate to cache.

Create the TimesTen Users

First, you must create a user who performs cache group operations on the TimesTen database. We refer to this user as the TimesTen database cache administration user.

The TimesTen cache administration user must have the same name as the Oracle cache administration user that accesses the cached Oracle Database tables. The password of the TimesTen database cache administration user can be different than the password of the Oracle cache administration user.

Note:

You can create multiple cache administration users on a TimesTen database, such as one for each TimesTen DBA. However, you can only define a single cache administration user on the Oracle database for this particular TimesTen database. (You can use the same Oracle cache administration user for all TimesTen databases that connect to the Oracle database or define a separate cache administration user for each TimesTen database.) If you create multiple TimesTen cache administration users, one or more of these users can use the same Oracle cache administration user.

The TimesTen cache administration user must be assigned privileges to perform cache operations. The TimesTen cache administration user creates the cache groups. It may perform operations such as loading or refreshing a cache group (although these operations can be performed by any TimesTen user that has sufficient privileges). The TimesTen cache administration user can also monitor various aspects of the caching environment, such as asynchronous operations that are performed on cache groups such as autorefresh.

The second user that you must create is a cache table user that owns the cache tables on TimesTen and has the same name as the Oracle Database schema owner who owns Oracle Database tables to be cached in the TimesTen database. We refer to these users as cache table users, because the TimesTen cache tables are to be owned by these users. Therefore, the owner and name of a TimesTen cache table is the same as the schema owner and name of the corresponding cached Oracle Database table. The password of a cache table user can be different than the password of the Oracle Database schema owner with the same name.

The following example creates the TimesTen users. It uses the ttIsql utility to connect to the cache1 DSN as the instance administrator. One of the most frequently used TimesTen utilities is the ttIsql utility. This is an interactive SQL utility that serves the same purpose for TimesTen as SQL*Plus does for Oracle Database.

Creates the TimesTen database cache administration user cacheadmin whose name (in this example) is the same as the Oracle cache administration user.
Creates a cache table user sales whose name is the same as the Oracle Database schema owner of the Oracle Database tables to be cached in the TimesTen database.

% ttIsql cache1
Command> CREATE USER cacheadmin IDENTIFIED BY ttpwd;
Command> CREATE USER sales IDENTIFIED BY ttpwd;

Grant Privileges to the TimesTen Users

The privileges that the TimesTen users require depend on the types of cache groups you create and the operations that you perform on the cache groups.

All of the privileges required for the TimesTen cache administration user for each cache operation are listed in Required Privileges for Cache Administration User for Cache Operations.

You must grant required privileges to the cache administration user. This example grants the TimesTen cache administration user cacheadmin the following required privileges to perform the noted operations:

Set the cache administration user and password (CACHE_MANAGER).
Start or stop the cache agent and replication agent processes on the TimesTen database (CACHE_MANAGER).
Set a cache agent start policy (CACHE_MANAGER).
Set a replication agent start policy (ADMIN)
Create cache groups to be owned by the TimesTen cache administration user (CREATE [ANY] CACHE GROUP, inherited by the CACHE_MANAGER privilege; CREATE [ANY] TABLE to create the underlying cache tables which are to be owned by the cache table user).
Alter, load, refresh, flush, unload or drop a cache group requires the appropriate privilege:
- ALTER ANY CACHE GROUP
- LOAD {ANY CACHE GROUP | ON cache_group_name
- REFRESH {ANY CACHE GROUP | ON cache_group_name
- FLUSH {ANY CACHE GROUP | ON cache_group_name
- UNLOAD {ANY CACHE GROUP | ON cache_group_name
- DROP ANY CACHE GROUP and DROP ANY TABLE
Required privileges for other cache operations, such as for read-only cache groups, dynamic load operations, incremental autorefresh, full autorefresh and asynchronous writethrough, are listed in Required Privileges for Cache Administration User for Cache Operations.

As the instance administrator, use the ttIsql utility to grant the cacheadmin cache administration user the required privileges:

Command> GRANT CREATE SESSION, CACHE_MANAGER, CREATE ANY TABLE TO cacheadmin;
Command> exit

Providing Cache Administration User Credentials

If you are running a request that does not require access to the Oracle database, you can proceed without needing to provide credentials for the Oracle database. That is, you can connect with only the user name and password for connecting to the TimesTen database. However, when you want to perform cache operations that require connecting to the Oracle database, then you must provide the appropriate credentials to be able to connect to both the TimesTen and Oracle databases.

Once the cache administration users are created with their respective passwords, these credentials need to be provided in two places for cache operations to proceed.

Provided on the connection string: When you are connecting to the TimesTen database and are planning on performing cache operations that require TimesTen to connect to the Oracle database, the cache administration users and respective passwords are required. You can provide these either with the cache administration user and passwords saved in an Oracle Wallet (preferred) pointed to by the PwdWallet connection attribute or specified distinctly within the UID, PWD, PWDCrypt, and OraclePWD connection attributes. Providing credentials in a wallet is more secure than supplying a password on the connect string with the PWD or PWDCrypt connection attributes.

See Providing Cache Administration User Credentials When Connecting.
Registered internally within TimesTen: There are cache operations that TimesTen performs for you. In order for TimesTen to connect to the Oracle database successfully to perform these cache operations, TimesTen needs to have the Oracle cache administration user and password credentials registered internally. In TimesTen Classic, the internal registration is accomplished when you run the ttCacheUidPwdSet built-in procedure. You can specify that the results of the ttCacheUidPwdSet built in procedure are saved in an Oracle Wallet (preferred) or in memory. The credentials are saved within an Oracle Wallet when you set the CacheAdminWallet=1 in the DSN as a first connection attribute (which is best set in the DSN).

See Registering the Cache Administration User Name and Password.

Providing Cache Administration User Credentials When Connecting

When you are connecting to the TimesTen database with the intent on performing cache operations that require TimesTen to connect to the Oracle database, then the cache administration users and their respective passwords are required.

Supply the cache administration user credentials in the connection string either by:

You can provide the Oracle and TimesTen cache administration users credentials within an Oracle Wallet. This method requires you to first save the cache administration user credentials in an identifiable Oracle Wallet with the ttUser utility. After creating the wallet, the particular wallet is identified by UID and PwdWallet connection attributes on the connection string. This is the preferred method as it is more secure. See Connect Using an Oracle Wallet with Credentials.

Note:

Most sections in this book provide security credentials for both cache administration users with an Oracle Wallet.
You can provide the cache administration user name and passwords on the connection string. Specify the cache administration user name in the UID connection attribute. Specify the TimesTen cache administration user password in the PWD or PWDCrypt connection attribute and the Oracle cache administration user password in the OraclePWD connection attribute. See Connect Using Connection Attributes for Credentials.

Connect Using an Oracle Wallet with Credentials

You can provide credentials for cache administration users by saving them in an Oracle Wallet, which then can be used for connecting to both the TimesTen and Oracle databases.

Use the ttUser -setPwd command to store the TimesTen cache administration user and password in a wallet. Use the ttUser -setOraclePwd command to store the Oracle cache administration user and password in a wallet.

This section describes the process to add cache administration user passwords to an Oracle Wallet.

The following example shows how to use the ttUser utility to add both cache administration users to an Oracle Wallet in the /wallets/cacheadminwallet directory.

If it does not already exist, make a directory for your wallet. This example users /wallets as the directory for the wallet.
```
% mkdir /wallets
```
Run the ttUser -setPwd command to store the TimesTen cache administration user credentials. The ttUser utility requires that you provide a subdirectory name that identifies the wallet (since you cannot change the name of an Oracle Wallet). This example provides cacheadminwallet as the subdirectory name for the wallet. If cacheadminwallet directory does not exist, then the ttUser utility creates the cacheadminwallet subdirectory and then creates the Oracle Wallet in the /wallets/cacheadminwallet directory. The ttUser utility prompts for the password for the TimesTen cache administration user cacheadmin, which is added to the wallet.
```
% ttUser -setPwd -wallet /wallets/cacheadminwallet -uid cacheadmin
Enter password: 
```
Run the ttUser -setOraclePwd command to store the Oracle cache administration user credentials. The ttUser utility prompts for the password for the Oracle cache administration user cacheadmin, which is added to the wallet in /wallets/cacheadminwallet.
```
% ttUser -setOraclePwd -wallet /wallets/cacheadminwallet -uid cacheadmin
Enter password: 
```

After the credentials are stored within an Oracle Wallet, provide the user name and location of the wallet on the connection string. The PwdWallet connection attribute identifies the wallet. The UID connection attribute identifies which credentials to locate within the wallet.

connect “dsn=cache1;uid=cacheadmin;PwdWallet=/wallets/cacheadminwallet”;

For client/server connections, the wallet must exist on the client.

See Providing Cache Administration User Names and Passwords in an Oracle Wallet in the Oracle TimesTen In-Memory Database Security Guide for full details on how to store credentials in an Oracle Wallet. See PwdWallet and ttUser in the Oracle TimesTen In-Memory Database Reference.

Connect Using Connection Attributes for Credentials

You can provide credentials for cache administration users using connection attributes when connecting to the Oracle database.

In the connection string, specify the cache administration user name in the UID connection attribute. Specify the cache administration user credentials that are saved in the Oracle Wallet identified in the PwdWallet connection attribute.

% ttIsql "DSN=cache1;UID=cacheadmin;PwdWallet=/wallets/cacheadminwallet"

See Specifying Both Cache Administration Users and Passwords in the Oracle TimesTen In-Memory Database Security Guide for more information on providing cache credentials.

Registering the Cache Administration User Name and Password

You must register the Oracle database cache administration user name and password internally in the TimesTen database before any cache group operation can be issued.

The TimesTen database and some TimesTen utilities and built-in procedures perform cache operations on your behalf. In order to connect to the Oracle database, TimesTen must have the credentials of the Oracle cache administraiton user and password registered internally.

The cache agent connects to the Oracle database as the Oracle cache administration user to create and maintain Oracle Database objects that store information used to enforce predefined behaviors of particular cache group types. In addition, both the cache and replication agents connect to the Oracle database with the internally registered Oracle cache administration user credentials to manage Oracle database operations.

The Oracle database cache administration user name and password need to be registered only once in each TimesTen database that caches Oracle Database data unless the cache administration user name or its password is changed. For example, if you modify the password of the cache administration user, if the TimesTen database is destroyed and re-created, or if the Oracle cache administration user name is dropped and re-created in the Oracle database, the Oracle cache administration user name and password must be registered again.

The Oracle cache administration user name cannot be changed if there are cache groups in the database. The cache groups must be dropped before you can drop and recreate the cache administration user. See Changing Cache User Names and Passwords.

The following sections detail the different tools provided in TimesTen Classic and TimesTen Scaleout:

Registering the Cache Administration User Name and Password in TimesTen Classic

In TimesTen Classic, you can register the Oracle cache administration user name and password by calling the ttCacheUidPwdSet built-in procedure after connecting as the Timesten cache administration user.

Before you register the Oracle cache administration user and password internally within the TimesTen database, you must decide if you want to save these credentials in an Oracle Wallet (recommended) or within memory (the default). To save the Oracle cache administration user credentials within an Oracle Wallet, ensure that the CacheAdminWallet connection attribute is set to 1 (best set in your DSN).

This example connects as the cacheadmin cache administration user providing credentials in a wallet. After connection, the example calls ttCacheUidPwdSet providing the Oracle cache administration user name and password, which registers the Oracle cache administration user name and password within TimesTen.

% ttIsql "DSN=cache1;UID=cacheadmin;PwdWallet=/wallets/cacheadminwallet"
Command> call ttCacheUidPwdSet('cacheadmin','orapwd');

The credentials can also be registered from a command line by running a ttAdmin -cacheUidPwdSet utility command as a TimesTen external user with the CACHE_MANAGER privilege. The ttAdmin utility prompts for the password.

% ttAdmin -cacheUidPwdSet -cacheUid cacheadmin orapwd cache1
Enter password:

See ttCacheUidPwdSet and ttAdmin in Oracle TimesTen In-Memory Database Reference.

Registering the Cache Administration User Name and Password in TimesTen Scaleout

Before you register the Oracle cache administration user and password internally within the TimesTen database, you must decide if you want to save these credentials in an Oracle Wallet (recommended) or within memory (the default). To store the Oracle cache administration user credentials within an Oracle Wallet, ensure that the CacheAdminWallet connection attribute is set to 1 in your DSN.

In TimesTen Scaleout, use the ttGridAdmin dbCacheCredentialSet command to register the Oracle cache administration user name and password.

% ttGridAdmin dbCacheCredentialSet database1
Enter your Oracle user id: cacheadmin 
Enter Oracle password: 
Password accepted 
Configuring cache.....................................................OK

See Set the Cache Administration User Name and Password in the TimesTen Database in the Oracle TimesTen In-Memory Database Scaleout User's Guide.

Cache Group Requirements for Credentials

Because of the synchronous or asynchronous nature of some cache groups, TimesTen uses credentials set as follows:

When you connect to the TimesTen database to work with AWT or read-only cache groups, TimesTen Classic uses the credentials that are registered with the ttCacheUidPwdSet built-in procedure when connecting to the Oracle database on behalf of these cache groups.
When you connect to the TimesTen database to work with SWT or user managed cache groups or passthrough operations, TimesTen Classic connects to the Oracle database using the current user's credentials provided in the connection string. This can be either the credentials stored in a wallet designated by the UID and PwdWallet connection attributes or the UID, PWD, and OraclePwd connection attributes.
When you are using dynamic load, the credentials used depend on if you are using connection pooling or not.
- When UseCacheConnPool= 0 (the default), connection pooling is disabled. In this case, TimesTen Classic connects to the Oracle database using the current user's credentials provided in the connection string when performing a dynamic load request.
- When UseCacheConnPool= 2, connection pooling is enabled. In this case, TimesTen Classic connects to the Oracle database using the credentials that have been registered with the ttCacheUidPwdSet built-in procedure when performing a dynamic load request.