Setting Up the Oracle Database and TimesTen Scaleout Systems

Before you can create a cache group, you must first install TimesTen Scaleout and then configure both the Oracle database and TimesTen Scaleout systems.

See Prerequisites and Installation of TimesTen Scaleout.

Note:

It is best to have the TimesTen and Oracle databases on separate systems, to avoid resource contention between them. TimesTen, being an in-memory database, uses a significant amount of memory. It may also use a significant amount of CPU time and generate a significant amount of I/O, depending on the workload.

You inform TimesTen of the Oracle database with which to connect, which credentials to use when connecting to the Oracle database and which users own the tables in both TimesTen and Oracle databases.

Create Users and Tablespace in the Oracle Database

In the Oracle database, you must create a default tablespace to be used for storing cache management objects that should not be shared with other applications. We strongly recommend that this tablespace be used solely for cache management.

Create the following users on the Oracle database:

Note:

If you are using Oracle Autonomous Database Serverless for the Transaction Processing workload type, use the preconfigured databases services LOW or TP.
If you are using a multitenant container database (CDB) or pluggable database (PDB), note the specific instructions below on how to create the cache administration user and grant this user privileges in a CDB or PDB.

Identify existing schema users that own the Oracle database tables that you want to cache in a TimesTen database.
Create an Oracle cache administration user creates and maintains Oracle database objects that store information used to manage the cache environment and enforce predefined behaviors of particular cache group types.

The following demonstrates how to create the default tablespace, the schema user, and the cache administration user:

Start SQL*Plus on the Oracle database system from an operating system shell or command prompt. Connect to the Oracle database as an Oracle database user with the privileges needed to create required roles and grant the necessary privileges to the cache administration user on the Oracle database.
```
% cd timesten_home/install/oraclescripts
% sqlplus sys as sysdba
Enter password: password
```
This example uses the sys as sysdba user since the SYS user is able to grant the required privileges. For the Transaction Processing workload type, use the admin user instead. You can use any Oracle database user that has the appropriate privileges. See Required Privileges for Cache Administration User for Cache Operations in Oracle TimesTen In-Memory Database Cache Guide.
For the non-autonomous Oracle Database, use SQL*Plus to create a default tablespace. In the following example, the name of the default tablespace is cachetblsp:
```
SQL> CREATE TABLESPACE cachetblsp DATAFILE 'tt_cache.f' SIZE 5G 
 SEGMENT SPACE MANAGEMENT AUTO;

Tablespace created.
```
Skip this step for the Transaction Processing workload type. Oracle Autonomous Database automatically configures default data and temporary tablespaces for the database. Adding, removing, or modifying tablespaces is not allowed. Oracle Autonomous Database creates one or multiple tablespaces automatically depending on the storage size.
Identify one or more existing schemas (or create a new schema) with schema owners that own Oracle database tables that are to be cached in a TimesTen database. The tables to be cached may or may not already exist. Grant the schema owner the minimum set of privileges required to create tables in the Oracle database to be cached in a TimesTen database. This example will cache tables owned by the sales schema owner.

The following SQL*Plus example grants the necessary privileges required to the sales user.
```
SQL> GRANT CREATE SESSION, CREATE TABLE, CREATE CLUSTER, CREATE INDEXTYPE, CREATE OPERATOR
 TO sales;
```
Use SQL*Plus to create a cache administration user and grant privileges to this user.
Note:

If you are using a multitenant container database (CDB) or pluggable database (PDB), the cache administrator user can be one of the following:
- Local user: A local user is a database user that can operate only within a single PDB. You must assign cache privileges only within the PDB in which this user exists.
- Common user: A common user is a database user known in every container and has the same identity in the CBD root and in every existing and future PDB in the CDB. You must assign cache privileges within each PDB in the CDB in which you want to use cache.
1. Create a cache administration user and specify the default tablespace that you created for cache management objects.
  - For the non-autonomous Oracle Database, the following SQL*Plus example creates the cache administration user.
```
SQL> CREATE USER cacheadmin IDENTIFIED BY orapwd
    DEFAULT TABLESPACE cachetblsp QUOTA UNLIMITED ON cachetblsp;
```
  - For the Transaction Processing workload type, the following SQL*Plus example creates the cache administration user.
```
SQL> CREATE USER cacheadmin IDENTIFYED BY orapwd
    QUOTA UNLIMITED ON DATA;
```
2. Run the SQL*Plus script timesten_home/install/oraclescripts/grantCacheAdminPrivileges.sql to grant the cache administration user the minimum set of privileges required to perform cache group operations.
  Note:
  
  If you are using a multitenant container database (CDB) or pluggable database (PDB), run the grantCacheAdminPrivileges.sql script to assign cache privileges as follows:
  - If the cache administrator user is a local user: You must assign cache privileges only within the PDB in which this user exists. This is the preferred method.
  - If the cache administrator user is a common user: You must assign cache privileges within each PDB in the CDB in which you want to use cache. Do not run the SQL*Plus script to grant privileges to the common user in the CBD root.
  For a non-autonomous Oracle Database, the following example passes cacheadmin as the cache administration user name to the grantCacheAdminPrivileges.sql script:
  
  Note:
  
  See the comments in the timesten_home/install/oraclescripts/grantCacheAdminPrivileges.sql script for the required privileges by the user who runs this script and the privileges that this user grants to the cache administration user.
```
SQL> @grantCacheAdminPrivileges "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.V$SESSION privilege  to CACHEADMIN (optional)
28. Granting the SELECT on SYS.V$PROCESS privilege  to CACHEADMIN (optional)
29. Granting the SELECT ANY TRANSACTION privilege to CACHEADMIN
********* Initialization for cache admin user done successfully *********
SQL> exit
```
  For the Transaction Processing workload type, step 16 should show instead:
```
16. Checking if the cache administrator user has permissions on the default tablespace
No existing permission.
```
  Oracle Autonomous Database automatically configures tablespaces. Therefore, this permission is not necessary.

See Create the Oracle Database Users and Default Tablespace in Oracle TimesTen In-Memory Database Cache Guide.

Create a TimesTen Database

Create the database definition before creating and opening a TimesTen database.

Creating a TimesTen database includes the following tasks:

Create a Database Definition for the TimesTen Database

When creating a database definition file for a TimesTen database that caches data from an Oracle database, pay special attention to the settings of the connection attributes.

The OracleNetServiceName must be set to the net service name of the Oracle database instance.
PermSize specifies the allocated size of the database's permanent region in MB. Set PermSize to a size large enough to store all of the data in the cache groups, indexes, and so on. The PermSize value must be smaller than the physical RAM on the machine. The PermSize value could be from a few GB to several TB. The default is 32 MB.
DatabaseCharacterSet must match the Oracle database character set.

You can determine the Oracle database character set by executing the following query in SQL*Plus as any user:
```
SQL> SELECT value FROM nls_database_parameters 
       WHERE parameter='NLS_CHARACTERSET';
```
CacheAdminWallet when set to 1 specifies that credentials for the Oracle cache administration user that are registered with the ttGridAdmin dbCacheCredentialSet command are stored in an Oracle Wallet.

The following example shows the contents of a database definition file named database1.dbdef that defines:

The full path for the checkpoint files as /disk1/databases/database1
The directory for the log files as /disk2/logs
The database character set as AL32UTF8
32 GB for the permanent memory region of every element
4 GB for the temporary memory region of every element
1 GB for the internal transaction log buffer of every element
An upper limit of 3072 user-specified concurrent connections to the database
The OracleNetServiceName set to inst1.
CacheAdminWallet set to 1.

DataStore=/disk1/databases/database1
LogDir=/disk2/logs
DatabaseCharacterSet=AL32UTF8
PermSize=32768
TempSize=4096
LogBufMB=1024
Connections=3072
OracleNetServiceName=inst1
CacheAdminWallet=1

See Create a Database Definition.

Create and Open the TimesTen Database

Once the database definition is created to include connection attributes for cache, you can perform the rest of the tasks necessary to create and open the TimesTen database.

See Creating a Database in Oracle TimesTen In-Memory Database Scaleout User's Guide.

Use the ttGridAdmin dbdefCreate command to create a database definition based on a database definition file. TimesTen Scaleout uses the name of the database definition file to name the database definition.
Create the database1 database definition based on the database1.dbdef file.
```
% ttGridAdmin dbdefCreate /mydir/database1.dbdef
Database Definition database1 created.
```

Add the database1 database definition to the current version of the model.

% ttGridAdmin modelApply
...
Updating grid state...................................................OK
Pushing new configuration files to each instance......................OK
...
ttGridAdmin modelApply complete

Create a TimesTen database based on the attributes stored in a database definition.
The ttGridAdmin dbCreate command creates a database based on a database definition.

Create the database1 database based on the database1 database definition.
```
% ttGridAdmin dbCreate database1
Database database1 creation started
```

Wait until all data instances report that they have loaded their element of the database into memory before proceeding with the definition of the distribution map. You can verify the status of the database creation process with the ttGridAdmin dbStatus command.

% ttGridAdmin dbStatus database1 -element
Database database1 element level status as of Tue Dec 22 08:52:09 PST 2020
 
Host  Instance  Elem Status CA Status Date/Time of Event  Message 
----- --------- ---- ------ --------- ------------------- ------- 
host3 instance1  1   loaded  stopped   2020-12-22 08:52:00   
host4 instance1  2   loaded  stopped   2020-12-22 08:52:04   
host5 instance1  3   loaded  stopped   2020-12-22 08:51:47   
host6 instance1  4   loaded  stopped   2020-12-22 08:51:58 
host7 instance1  5   loaded  stopped   2020-12-22 08:52:04
host8 instance1  6   loaded  stopped   2020-12-22 08:52:04

Define the distribution map of the TimesTen database and add all the elements of the available data instances in the grid1 grid to the distribution map of the database1 database.
```
% ttGridAdmin dbDistribute database1 -add all -apply
Distribution map updated
```
Open the database1 database for user connections.
The ttGridAdmin dbOpen command opens a database for user connections.
```
% ttGridAdmin dbOpen database1
Database database1 open started
```

Verify that the database is open with the ttGridAdmin dbStatus command.

The example shows a status summary for the database1 database. Notice that the report shows all elements of the database as open.

% ttGridAdmin dbStatus database1 -element
Database database1 element level status as of Tue Dec 22 08:52:49 PST 2020
 
Host  Instance  Elem Status CA Status Date/Time of Event  Message 
----- --------- ---- ------ --------- ------------------- ------- 
host3 instance1    1 opened stopped 2020-12-22 08:52:44
host4 instance1    2 opened stopped 2020-12-22 08:52:43
host5 instance1    3 opened stopped 2020-12-22 08:52:43
host6 instance1    4 opened stopped 2020-12-22 08:52:44
host7 instance1    5 opened stopped 2020-12-22 08:52:44
host8 instance1    6 opened stopped 2020-12-22 08:52:44

Add the Oracle Database Net Service Name to the tnsnames.ora File

When you set up a cache environment in TimesTen Scaleout, all instances in a database must have access to the Oracle database.

For the Transaction Processing workload type, use the preconfigured databases services LOW or TP:

databasename_low
databasename_tp

The Oracle Database tnsnames.ora file defines Oracle Net Services to which applications connect. Use the ttGridAdmin TNSNamesImport command to import the tnsnames.ora on all instances in the TimesTen database.

Note:

If you need to add SQLNet parameters, create and import a sqlnet.ora file with the ttGridAdmin SQLNetImport command.

Add the Oracle Database net service name to a tnsnames.ora file.

For the non-autonomous Oracle Database, the following is an example of defining inst1 in a tnsnames.ora file:

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

For the Transaction Processing workload type, the following is an example of defining inst1_low in a tnsnames.ora file:

inst1_low =  
 (DESCRIPTION =   
   (ADDRESS = (PROTOCOL = TCP)(HOST = adb.us-phoenix-1.oraclecloud.com)
     (PORT = 1521))     
   (CONNECT_DATA =       
     (SERVICE_NAME = inst1_low.adb.oraclecloud.com)))

Import this file into the grid using the ttGridAdmin TNSNamesImport command.

% ttGridAdmin TNSNamesImport /tmp/tnsnames.ora
TNSNames configuration file /tmp/tnsnames.ora imported

Apply it to the model with the ttGridAdmin modelApply command. After the model apply completes, the tnsnames.ora file exists on all instances.

% ttGridAdmin modelApply
...
Updating grid state...................................................OK
Pushing new configuration files to each instance......................OK
...
ttGridAdmin modelApply complete

See Import TNS Names (TNSNamesImport) or Import a Sqlnet File (SQLNetImport) in Oracle TimesTen In-Memory Database Reference.

Create Users in the TimesTen Database

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

A TimesTen cache administration user performs cache group operations. The TimesTen cache administration user must have the same name as the Oracle Database cache administration user that can access the cached Oracle database tables. The password of the cache administration user can be different than the password of the companion Oracle database cache administration user. See Create the TimesTen Users in Oracle TimesTen In-Memory Database Cache Guide.
One or more cache users own the cache tables. You must create a TimesTen cache user with the same name as an Oracle database schema user for each schema user who owns or will own Oracle database tables to be cached in the TimesTen database. The password of a cache user can be different than the password of the Oracle database schema user with the same name.

The owner and name of a TimesTen cache table is the same as the owner and name of the corresponding cached Oracle database table.

On one of the data instances on the TimesTen database, connect using the direct connectable. Create a TimesTen cache administration user and grant this user the minimum set of privileges required to create cache groups and to perform operations on the cache groups. In the following example, the TimesTen cache administration user name is cacheadmin, which is the same name as the Oracle cache administration user that was created earlier:

ttisql "DSN=database1"

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

Then, create a cache user. In the following example, the cache user name is sales, which is the same name as the Oracle database schema user with the tables that we want to cache:

Command> CREATE USER sales IDENTIFIED BY ttpwd;

The privileges that the TimesTen cache administration user requires depend on the operations that you perform on the cache groups. See Create the TimesTen Users in Oracle TimesTen In-Memory Database Cache Guide.

See Managing TimesTen Users in Oracle TimesTen In-Memory Database Security Guide for more information about TimesTen users and privileges.

Create a Connectable for the TimesTen Database

When creating a connectable for a TimesTen database that caches data from an Oracle database, pay special attention to the settings of the connection attributes.

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

UID specifies the name of the TimesTen cache administration user, that has the same name as the Oracle database cache administration user who can access the cached Oracle database tables. The UID connection attribute can be specified in a direct connectable, a client/server connectable, or a connection string.
PwdWallet provides the location of the wallet in which credentials are stored for users. You can store the TimesTen user name and password in a wallet. You can also store the cache administrator users and respective passwords in a wallet, which are necessary when performing cache operations and connecting to the Oracle database. Providing credentials in a wallet is more secure than supplying a password in a client DSN or on the connection string.
If you are not using PwdWallet for supplying credentials, then you can use:
- PWD to specify the password of the TimesTen cache administration user specified in the UID connection attribute.
- OraclePWD to provide 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.
PassThrough can be set to control whether statements are to be run in the TimesTen database or passed through to be run in the Oracle database. See Using Passthrough.
LockLevel must be set to its default of 0 (row-level locking) because cache does not support database-level locking.

The following example shows how to create a connectable that uses cache.

Create a connectable file that sets the connection character set, OracleNetServiceName, cache administration user credentials saved in a wallet for the connection.

This connectable file is named database1CS.connect. This file sets AL32UTF8 as the connection character set, cache administration user name as cacheadmin, and the location of the wallet with the cache administration user credentials as /wallets/cacheadminwallet.
```
ConnectionCharacterSet=AL32UTF8
OracleNetServiceName=inst1
UID=cacheadmin
PwdWallet=/wallets/cacheadminwallet
```
Note:

Before you can provide a wallet, you must first create the wallet for the user credentials. See Providing the Cache Administration User Names and Passwords in an Oracle Wallet in Oracle TimesTen In-Memory Database Security Guide on how to add user credentials in an Oracle Wallet.

If you do provide a wallet, then the wallet must be located in the same path on every data element from which the user accesses the connectable.
The ttGridAdmin connectableCreate command creates a connectable based on a connectable file.

Create the database1CS connectable based on the database1CS.connect connectable file.
```
% ttGridAdmin connectableCreate -dbdef database1 -cs /mydir/database1CS.connect
Connectable database1CS created.
```

Apply the creation of the database1CS connectable to the current version of the model to make the connectable available for use.

% ttGridAdmin modelApply
...
Updating grid state...................................................OK
Pushing new configuration files to each instance......................OK
...
ttGridAdmin modelApply complete

See Providing the Cache Administration User Names and Passwords in an Oracle Wallet in Oracle TimesTen In-Memory Database Security Guide and Create a Connectable.

Register the Cache Administration User Name and Password in the TimesTen Database

TimesTen must know which credentials to use when connecting to the Oracle database. All instances in the same database must use the same Oracle cache administration user id and password when connecting to the Oracle database.

Use the ttGridAdmin dbCacheCredentialSet command on the active management instance to register the Oracle cache administration user name and password in the TimesTen database. When you set CacheAdminWallet=1, then cache administration user credentials are stored in an Oracle Wallet. Otherwise, the credentials are stored in memory.

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

When prompted, specify the cache administration user name as the Oracle database user id and the cache administration user password as the Oracle database password.

The Oracle cache administration user name and password need to be registered only once in a TimesTen database. See Set the Cache Administration User Name and Password in the TimesTen Database in Oracle TimesTen In-Memory Database Cache Guide.

You can also use the ttGridAdmin dbCacheCredentialSet command to change the Oracle cache administration user name and password. The password can be changed at any time. The cache administration user name can only be changed when there are no cache groups on the TimesTen database. See Changing Cache User Names and Passwords in Oracle TimesTen In-Memory Database Cache Guide.