Prepare the Databases for Cache Operations

4 Prepare the Databases for Cache Operations

Before you can start cache operations, you must prepare both the Oracle database and TimesTen database for cache operations.

The following sections describe what you need to do in order to prepare both the Oracle and TimesTen databases for cache operations.

Prepare the Oracle Database for Cache Operations

As a prerequisite for setting up the TimesTen database as a cache for tables from an Oracle database, you need to configure the Oracle database for cache operations. In order to configure the Oracle database for cache operations, you need database administrator access.

Connect to the Oracle database as a database administrator to configure the Oracle database for cache operations. This example uses the SQL*Plus utility:

% sqlplus sys@tnsservicename as sysdba
Enter password: password

Note:

The use of the sys@tnsservicename as sysdba user in this example is applicable only for a test environment.

The following shows the steps necessary for preparing the Oracle database for cache operations.

Description of the illustration preporacle.eps

Task 1: Create the Tablespace for Cache Metadata and Management Objects

The Oracle cache administration user needs to have a default tablespace to store cache metadata and management objects. It is recommended to create a dedicated tablespace just for cache operations. This tablespace should not be shared with other applications.

The following example creates the cachetblsp tablespace in the Oracle database.

SQL> CREATE TABLESPACE cachetblsp DATAFILE 'cachetblsp_f1.dbf'
                SIZE 5G SEGMENT SPACE MANAGEMENT AUTO;
Tablespace created.

Task 2: Create the Cache Administration User on the Oracle Database

To be able to cache tables from an Oracle database to a TimesTen database, create a cache administration user on the Oracle database on both the Oracle and TimesTen databases.

The cache administration user on the Oracle database creates and maintains Oracle database objects that store the information used to manage the cache environment.

The following example creates the cacheadmin user (with password cacheadmpwd) in the Oracle database and specifies the default tablespace for cache management objects, cachetblsp.

SQL> CREATE USER cacheadmin IDENTIFIED BY cacheadmpwd
                DEFAULT TABLESPACE cachetblsp QUOTA UNLIMITED ON cachetblsp;
User created.

Later on, you will create a cache administration user on the TimesTen database with the same name to coordinate the caching of data between the Oracle and TimesTen databases.

Task 3: Identify Schema Users on the Oracle Database

To be able to cache tables from an Oracle database to a TimesTen database, you need to identify (or create) one or more schema users on the Oracle database.

These schema users own tables that will be cached in the TimesTen database.

Note:

The set of examples included in this guide assume that a schema user (with populated tables) named sales already exists in the Oracle database.

Later on, you will create one or more cache table users on the TimesTen database with the same name as the schema users to coordinate the caching of data between the Oracle and TimesTen databases.

Task 4: Grant SQL Privileges to the Cache Administration User on the Oracle Database

The cache administration user on the Oracle database requires a specific set of SQL privileges to perform cache operations in the Oracle database.

Run the grantCacheAdminPrivileges.sql script as the Oracle database administrator to grant to the Oracle cache administration user the minimum set of SQL privileges required to perform cache operations. The script is available at timesten_home/install/oraclescripts/grantCacheAdminPrivileges.sql of your TimesTen instance.

The following example grants the required SQL privileges to the cacheadmin user for cache operations in the Oracle database.

SQL> @grantCacheAdminPrivileges.sql cacheadmin

Please enter the administrator user id
The value chosen for administrator user id is cacheadmin

***************** Creation of TT_CACHE_ADMIN_ROLE starts ******************
0. Creating TT_CACHE_ADMIN_ROLE role
** Creation of TT_CACHE_ADMIN_ROLE done successfully **
***************** Initialization for cache admin begins ******************
0. Granting the CREATE SESSION privilege to CACHEADMIN
1. Granting the TT_CACHE_ADMIN_ROLE to CACHEADMIN
2. Granting the DBMS_LOCK package privilege to CACHEADMIN
3. Granting the DBMS_DDL package privilege to CACHEADMIN
4. Granting the DBMS_FLASHBACK package privilege to CACHEADMIN
5. Granting the CREATE SEQUENCE privilege to CACHEADMIN
6. Granting the CREATE CLUSTER privilege to CACHEADMIN
7. Granting the CREATE OPERATOR privilege to CACHEADMIN
8. Granting the CREATE INDEXTYPE privilege to CACHEADMIN
9. Granting the CREATE TABLE privilege to CACHEADMIN
10. Granting the CREATE PROCEDURE  privilege to CACHEADMIN
11. Granting the CREATE ANY TRIGGER  privilege to CACHEADMIN
12. Granting the GRANT UNLIMITED TABLESPACE privilege to CACHEADMIN
13. Granting the DBMS_LOB package privilege to CACHEADMIN
14. Granting the SELECT on SYS.ALL_OBJECTS privilege to CACHEADMIN
15. Granting the SELECT on SYS.ALL_SYNONYMS privilege to CACHEADMIN
16. Checking if the cache administrator user has permissions on the default
tablespace
     Permission exists
18. Granting the CREATE TYPE privilege to CACHEADMIN
19. Granting the SELECT on SYS.GV$LOCK privilege to CACHEADMIN
20. Granting the SELECT on SYS.GV$SESSION privilege  to CACHEADMIN
21. Granting the SELECT on SYS.DBA_DATA_FILES privilege  to CACHEADMIN
22. Granting the SELECT on SYS.USER_USERS privilege  to CACHEADMIN
23. Granting the SELECT on SYS.USER_FREE_SPACE privilege  to CACHEADMIN
24. Granting the SELECT on SYS.USER_TS_QUOTAS privilege  to CACHEADMIN
25. Granting the SELECT on SYS.USER_SYS_PRIVS privilege  to CACHEADMIN
26. Granting the SELECT on SYS.V$DATABASE privilege  to CACHEADMIN (optional)
27. Granting the SELECT on SYS.GV$PROCESS privilege  to CACHEADMIN (optional)
28. Granting the SELECT ANY TRANSACTION privilege to CACHEADMIN
29. Creating the TTCACHEADM.TT_07_ARDL_CG_COUNTER table
30. Granting SELECT privilege on TTCACHEADM.TT_07_ARDL_CG_COUNTER table to
PUBLIC
********* Initialization for cache admin user done successfully *********

The cache administration user on the Oracle database also needs specific privileges on each user table that is cached in TimesTen. The exact privileges depend on the type of cache groups being used, such as:

For read-only cache groups, grant the SELECT privilege on all the user tables that will be cached to the cache administration user.
```
SQL> GRANT SELECT ON sales.customers TO cacheadmin;
```
For read-write (AWT) cache groups, the cache administration user needs SELECT, INSERT, UPDATE, and DELETE privileges on all the user tables that will be cached,
```
SQL> GRANT SELECT, INSERT, UPDATE, DELETE ON sales.customers TO cacheadmin;
```

Later on when you are creating a DSN within TimesTen, you will need to know the Oracle database character set, which must match on both the Oracle and TimesTen databases.

SQL> SELECT value FROM nls_database_parameters WHERE parameter='NLS_CHARACTERSET';
VALUE
---------------------------------------------------------------------------
AL32UTF8

Prepare the TimesTen Database for Cache Operations

Likewise, you need to prepare the TimesTen database for cache operations.

You need to perform the following actions for a TimesTen database to be able to cache data from an Oracle database.

Description of the illustration prepttusers.eps

These steps are described in the following sections:

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

In order to connect to the Oracle database, an Oracle net service name needs to be added to the tnsnames.ora file.

You can either create a new timesten_home/conf/tnsnames.ora file or copy the sample tnsnames.ora file from the timesten_home/install/network/admin/samples/ directory to the timesten_home/conf directory.

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. In our example, the timesten_home variable points to /TimesTen directory. Specify the full path to the directory where the file is located, which in this case is /TimesTen/conf.
```
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/conf
```
Restart the main daemon to capture this setting.
```
ttDaemonAdmin -start
```
Within an editor, add the net service name for the Oracle Database to the tnsnames.ora file. The following is an example of defining orcl in a tnsnames.ora file. Save this name as you will set this net service name in the DSN.
```
orcl =  
 (DESCRIPTION =   
   (ADDRESS = (PROTOCOL = TCP)(HOST = myhost)
     (PORT = 1521))     
   (CONNECT_DATA =       
     (SERVICE_NAME = myhost.example.com)))
```

Task 2: Create a Data Source Name (DSN) for the TimesTen Database

A TimesTen database that caches data from an Oracle database can be referenced by either a system DSN or a user DSN.

The operating system user that installed and created the TimesTen instance is called the instance administrator. When this instance administrator connects using a DSN for the first time, a TimesTen database is implicitly created.

This example is going to reference the TimesTen database with a system DSN. 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 two 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). See Connecting to TimesTen With ODBC and JDBC drivers in the Oracle TimesTen In-Memory Database Operations Guide.

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 client DSN, cache1cs, defines client-server access for the local cache1 server DSN.

When creating a DSN for a TimesTen database that caches data from an Oracle database, the following connection attributes are important for your cache environment:

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';
VALUE
---------------------------------------------------------------------------
AL32UTF8
```
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 as was set in the tnsnames.ora file in step 1.
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.

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:

[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

You can use the default settings for all the other connection attributes.

Task 3: Create Users and Grant Privileges in the TimesTen Database

In addition to the Oracle database users, you must create the following TimesTen users before you can use cache.

Description of the illustration qusers.eps

A TimesTen cache administration user performs cache group operations. The TimesTen cache administration user must have the same name as the Oracle cache administration user that can access the cached Oracle Database tables. The password of the TimesTen cache administration user can be different than the password of the Oracle cache administration user with the same name.
One or more cache table owner users that own the cache tables. You must create a user that owns the TimesTen cache tables that has the same name as the schema owner that owns Oracle Database tables to be cached in the TimesTen database. The graphic shows one cache table owner called sales that has the same name as the sales schema owner in the Oracle database. The password of a cache table user can be different than the password of the Oracle Database schema owner with the same name.

Use the ttIsql utility on the TimesTen instance from an operating system shell or command prompt as the instance administrator, and connect to the cache1 DSN to create the TimesTen database that is to be used to cache data from an Oracle database:

% ttIsql cache1

Use ttIsql to create a cache administration user on TimesTen. Grant this user the minimum set of privileges required to create cache groups and to perform operations on the cache groups. Any cache administration user that creates, owns, and manages cache groups in the TimesTen database needs at least the CREATE SESSION, CACHE_MANAGER, and CREATE ANY TABLE privileges. In the following example, the cache administration user name is cacheadmin, which is the same name as the Oracle Database cache administration user that was created earlier:

Command> CREATE USER cacheadmin IDENTIFIED BY ttpwd;
User created.
Command> GRANT CREATE SESSION, CACHE_MANAGER, CREATE ANY TABLE TO cacheadmin;

Then, use ttIsql to create a cache table owner. In the following example, the cache table owner is sales, which is the same name as the schema owner that owns the Oracle database tables that are to be cached. Also, granting CREATE SESSION privilege so that sales can connect to the TimesTen database, if desired.

Command> CREATE USER sales IDENTIFIED BY ttpwd;
User created.
Command> GRANT CREATE SESSION to sales;
Command> exit;
Disconnecting...
Done.

Any additional privileges that the cache administration user requires depends on the types of cache groups you create and the operations that you perform on the cache groups. These required privileges are discussed when the cache groups are created.