Creating an In-Memory Cache Database on Windows

Purpose

This tutorial will go through the process of setting up Oracle In-Memory Database Cache, which is a TimesTen in-memory database used as a cache to an Oracle database. This tutorial only covers how to set up Oracle In-Memory Database Cache for Windows.

Time to Complete

Approximately 30 minutes.

Overview

Oracle In-Memory Database Cache is a TimesTen in-memory database used as a cache to an Oracle database. Oracle In-Memory Database Cache improves application response time by caching a performance-critical subset of tables and table fragments from an Oracle database into a TimesTen in-memory database running in the application tier.

Oracle TimesTen In-Memory Database (TimesTen) is a memory-optimized relational database that provides applications with the instant responsiveness and very high throughput required by today's real-time enterprises in a wide range of industries. A TimesTen database resides entirely in memory at run time and is persisted to disk storage for the ability to recover and restart. Applications access the TimesTen database using the JDBC, ODBC, OCI, ODP.NET and/or Pro*C/C++ interfaces. TimesTen is fully transactional, persistent, and highly available with transactional replication. TimesTen is typically deployed in the middle-tier with the applications; it can be run as a stand-alone database or as an in-memory cache database for an Oracle database. Using TimesTen as an in-memory cache database provides applications the power of SQL, the speed of RAM and the reliability of a proven product with automatic data synchronization between the in-memory cache tables and the backend Oracle database.

This tutorial will go through the process of creating an in-memory cache database on Windows.

Scenario

For this tutorial, we will create a TimesTen in-memory database used for a simple trading application. The database will be called ordermatching. We will first run a script on the backend Oracle database to set up the application schema, then we will set up Oracle In-Memory Database Cache to cache tables from the Oracle database into a TimesTen database.

Prerequisites

Before starting this tutorial, you should:

.

Have an installation of the Oracle Database 10g Release 2 or higher.

.

Have an installation of TimesTen 11.2.1
Click on this link for a demonstration of how to install TimesTen Database 11g on Windows.

Create Schema in the Oracle Database

In this section, we will create the database objects and PL/SQL procedures within the Oracle database. Later, we will cache some of the Oracle database tables into a TimesTen in-memory database.

.

Within the Oracle database, the database objects and PL/SQL procedures must be owned by a user within the database. We will first create this user and give it permissions to manipulate the Oracle database.

At the command prompt, log in as the sysdba user in the Oracle database. This is typically done by typing:

sqlplus sys/<your system password>@<Oracle TNS Net Service Name> as sysdba

Once logged in, type:

create user ttorauser identified by oracle
default tablespace users
quota unlimited on users
temporary tablespace temp;

Afterwards, type:

grant connect, resource to ttorauser;

 

.

Now, we will create and populate database tables owned by TTORAUSER, as well as create PL/SQL procedures. We will do this by running a SQL script within the Oracle database while logged in as TTORAUSER.

Download the following SQL script, which can be found here.

Connect to the Oracle database as the TTORAUSER user. This is done by typing the following in a Windows command prompt:

sqlplus ttorauser/oracle@<Oracle TNS Net Service Name>

 

.

Run the SQL script you just downloaded as the TTORAUSER user in the Oracle database by typing:

@<disk_location>/create_ttorauser_schema.sql;

 

Configure the Oracle Database

Before we can cache tables from an Oracle database into a TimesTen in-memory database, a cache administration user and TimesTen system tables must be created in the Oracle database. A cache administration user is an Oracle user that tracks the changes between the Oracle database and the in-memory cache database. This user must be granted sufficient privileges so that he can create triggers and have access to the Oracle data that are being cached. We will name this user CACHEADM. In addition, In-Memory Database Cache requires a TimesTen schema to be created in the Oracle database.

In this section, we will go through the process of configuring the Oracle database.

.

We will first create the TimesTen schema within the Oracle database.

Login as the sysdba user in the Oracle database and type the following in the SQL*Plus command line:

@<tt_installation_home>/oraclescripts/initCacheGlobalSchema.sql USERS

This creates the TIMESTEN user in the Oracle Database and associates it to the USERS tablespace. USERS is a default tablespace in a typical Oracle installation. For best practice, it is recommended that you associate the TIMESTEN user to a different tablespace. The TIMESTEN user manages the TimesTen schema metadata within the Oracle database.

 

.

Now, we will create an Oracle account for the cache administration user.

Still logged in as the sysdba user in the Oracle database, type:

create user cacheadm identfied by cacheadm
default tablespace users
quota unlimited on users
temporary tablespace temp;

Afterwards, type:

@<tt_installation_home>/oraclescripts/grantCacheAdminPrivileges.sql cacheadm

These commands create the CACHEADM user within the Oracle database and grant it the appropriate privileges to serve as the TimesTen cache administration user.

 

.

The cache administration user is responsible for monitoring, and applying the data changes between the cache database and the Oracle database. As such, the cache administration user needs to be granted access privileges to the Oracle tables being cached.

Log in as the TTORAUSER user in the Oracle database and type:

grant select on TTORAUSER.SYMBOLS to CACHEADM;
grant select, insert, update, delete on TTORAUSER.FILLED_ORDERS to CACHEADM;

These commands assume that the SYMBOLS table owned by the TTORAUSER user will be cached as a read-only table in TimesTen, while the FILLED_ORDERS table will be cached as an updatable table. Since the SYMBOLS table is a read-only cached table, for that table we only need to grant select privileges to the cache administration user. However, since the FILLED_ORDERS table is an updatable cached table, for that table we need to grant insert, update, and delete privileges in addition to select privileges to the cache administration user.

 

Set Up the Cache Database

After configuring the Oracle database, we need to create a TimesTen in-memory database to serve as our cache database. We create a cache database by first defining a Data Source Name (DSN) to uniquely identify the particular TimesTen database to which we want to use. Specifically, a DSN is a character-string name that identifies a TimesTen database and a collection of attributes that are to be used when creating and connecting to the database. On Windows, the DSN also specifies the ODBC driver to be used to access the database.

In this section, we will specify the parameters of our cache database.

.

Open the ODBC Data Source Administrator by clicking:
Start > Control Panel > Administrative Tools > Data Sources (ODBC)

 

.

Click on the System DSN tab and click on the Add button.

 

.

From the driver selection menu, select TimesTen Data Manager 11.2.1 and click Finish.

 

.

A "TimesTen ODBC Setup menu will then appear. Fill in the following database properties:

Data Source Name - This will be the name of the in-memory cache database.
In this example, we use Data Source Name*: ordermatching

Data Source Path + Name - This will be the disk location (which should already exist in the OS) and name of the database files for the cache database.
In this example, we set Data Source Path + Name*: E:\ordermatchingdb\ordermatching

Transaction Log Directory - This will be the disk location of where you want to store the transaction log files of the cache database. If this field is left blank, the transaction log files will be stored in the same location as the database files. For best practice, this directory should be set as a different directory than the database files.
In this example, we set Transaction Log Directory: C:\ordermatching_logs

Database Character Set* - This is the database character set of the Oracle database from which TimesTen will cache database tables from. This character set must be identical to the character set used by that Oracle database. If you don't know or are unsure of which database character set your Oracle database uses, login as any user in that Oracle database and execute the following SQL statement, which will return the Oracle database character set:

select value from nls_database_parameters where parameter = 'NLS_CHARACTERSET';

In this example, we set Database Character Set**: WE8MSWIN1252.

 

.

Now we will set the amount of space the cache database takes up in main memory. The size of a TimesTen database is the sum of the Permanent and Temporary size (plus a small fixed amount used internally by TimesTen).

Select the First Connection tab to fill in the database size properties. In this example, we used the following:

Permanent Data Size : 40
Temporary Data Size : 32

Note: The permanent and temporary data size are measured in MB.

 

.

Now we will specify which Oracle database the in-memory cache database should cache from.

Select the IMDB Cache tab, and fill in the following database properties:

Oracle Net Service Name - This is the TNS Net Service Name of the Oracle database that will be cached in TimesTen. Specifically, this is the entry in the TNSNAMES.ORA file pointing to the Oracle database.
In this example, we set Oracle Net Service Name: ttorcl.

 

.

Click the OK button. This defines the DSN for the TimesTen database on your Windows OS.

An entry with the name ordermatching should appear in the list of System DSNs.

 

.

Finally, we need to ensure that correct the TNSNAMES.ORA file is associated with the TimesTen installation. To do this, open a new Windows command prompt and type:

ttmodinstall -tns_admin

This will list the current TNS_ADMIN location for the TimesTen instance, and ask if you would like to change this directory. The TNS_ADMIN location should be the parent folder of the TNSNAMES.ORA file that contains the Net Service name of the Oracle database specified in a previous step.

If this command does not work, it may be necessary to set the TimesTen environment variables within the command prompt by running <tt_installation_home>/bin/ttenv.bat within the Windows command prompt first.

 

Create the TimesTen Database and Configure It to be an In-Memory Cache Database

The TimesTen database that will be used as an in-memory cache database must include a user with either ADMIN or CACHE_MANAGER privilege. This user is known as the cache manager user. In addition, the Oracle user that owns the tables to be cached (in our example, TTORAUSER) must also exist in the cache database. This user is known as the cache table user.

In this section, we will start the TimesTen main process, initialize the database and create the TTORAUSER user, who in our scenario will serve as both the cache table user and the cache manager user. Note that these two users do not necessarily have to be the same.

.

Ensure that the TimesTen main process (or daemon) has been started. This process needs to be started before any TimesTen database can be run. To do this, type the following in a command prompt:

ttdaemonadmin -start

It is possible that this process has already been started. If so, running this command will return that the TimesTen main daemon (TimesTen main process) has already been started.

 

.

Create the database and load it into memory for the first time by typing the following in the Windows command prompt you already have open:

ttisql ordermatching

Once this process is completed, ttIsql, a command line interface for managing the TimesTen (ordermatching) database will be shown. You can think of ttIsql as "SQL*Plus for the TimesTen database".

 

.

Within this ttIsql command line interface, we will now create the TTORAUSER user. To do this, type the following in the ttIsql command line:

create user ttorauser identified by timesten;

After this, we will grant ADMIN privileges to TTORAUSER so that it can serve as the cache manager user within the TimesTen database. To do this, type the following in the ttIsql command line:

grant admin to ttorauser;

 

.

Now we will test if our in-memory cache database can connect to the Oracle database.

Within the ttIsql prompt, type:

connect "dsn=ordermatching;uid=ttorauser;oraclepwd=oracle";

This will connect to the ordermatching database as the TTORAUSER user we just created, and also specify the password of the TTORAUSER to connect to the Oracle database, as indicated by the OraclePwd argument. Once the connection is established, type:

set passthrough 3;
select * from v$version;

The first argument specifies that all SQL statements are passed through from the TimesTen database to the Oracle database for execution. As such, the second command is run against the Oracle database, returning the version of the Oracle database. Successfully running these two commands show that the TimesTen database can connect to the Oracle database.

Finally, run these commands to reset the TimesTen database to its original passthrough setting, and set automatic commits in TimesTen to "on", in preparation for the next step.

set passthrough 0;
autocommit on;

 

.

Finally, we will associate the cache administration user (which we earlier created and named CACHEADM in the Oracle database) with the TimesTen in-memory cache database. We will run the built-in procedure ttCacheUidPwdSet to do this. Afterwards, we verify that the cache administration user has been set by calling the procedure ttCacheUidGet to return the cache administration user ID for the TimesTen in-memory cache database. To do this, type the following in the ttIsql command line that you already have open.

call ttcacheuidpwdset('cacheadm','cacheadm');
call ttcacheuidget;

 

Create a Cache Grid and Associate the Cache Database with the Cache Grid

Creating a cache grid is required only for the first cache database created. Subsequent cache databases created can then associate themselves to a previously created cache grid. Only the cache manager user can create and manage a cache grid. In addition, a cache database can only belong to one cache grid. For more information on the TimesTen cache grid, see the TimesTen documentation.

.

Still logged in as the cache manager user TTORAUSER, call the built-in procedure ttGridCreate to create the cache grid. Only the first grid member needs to perform this step. In this case, the MYCACHEGRID cache grid is created. Afterwards, verify that MYCACHEGRID has been created by calling ttGridInfo.

call ttgridcreate('mycachegrid');
call ttgridinfo;

 

.

To associate the ordermatching database with MYCACHEGRID, call the procedure ttGridNameSet.

call ttgridnameset('mycachegrid');

 

After running these steps, the TimesTen in-memory database (ordermatching) is now ready to be used as an in-memory cache database. We can now cache Oracle database tables or table fragments into the ordermatching database as either read-only or updatable cached tables. For more information, the Caching Oracle Database Tables Using In-Memory Database Cache tutorial goes through the process of creating and populating cache tables.

Summary

In this tutorial, you have learned how to:

 

Resources

Hardware and Software Engineered to Work Together Copyright © 2011, Oracle and/or its affiliates. All rights reserved