X

TimesTen In-Memory Database
for Extreme Performance

Creating an Oracle TimesTen Database on Oracle Database Appliance

Doug Hood
Evangelist for Oracle TimesTen, Oracle In-Memory and Oracle NoSQL

Review

Review these blogs before creating a database on an ODA:

Introduction

Once you have installed TimesTen on an ODA and created a TimesTen instance, the next step is to create a TimesTen Database. Unlike the TimesTen installation and instance, the TimesTen database checkpoint and transaction log files should reside on the Oracle ACFS file systems. The placement and size of these TimesTen files is critical for performance and capacity planning.

The correct configuration of Oracle ACFS is a pre-requisite for using TimesTen on ACFS. Therefore the ACFS mount points for the DATA and REDO file system should already exist. The ACFS can easily be seen via the Linux 'df -h' command:

ACFS mount points

The TimesTen checkpoint file will be in a sub-directory of the DATA file system. The TimesTen Transaction Log Files will be in a sub-directory of the REDO file system.

On an ODA HA machine, ACFS is a clustered/shared file system. ie each node can see the same files on the mounted ACFS file systems. TimesTen does not need or expect a shared file system for Active Standby Pairs. To make sure that the TimesTen files do not clash, they should use their hostname as a 'namespace'. ie each node has its own subdirectory for its TimesTen checkpoint and transaction log files.

ACFS File System Example Linux directory Purpose
DATA /u02/app/oracle/oradata/testdb2/hostname/sampledb TimesTen Checkpoint Files
REDO /u03/app/oracle/redo/hostname/sampledb TimesTen Transaction Log Files

Oracle databases tend to dynamically grow their file system storage by adding data files as needed. For optimal TimesTen performance, the maximum TimesTen file storage should be allocated as ACFS volumes before the TimesTen database is created.

This calculation is fairly straight forwards as there is much more disk space available on ODA than RAM and the entire TimesTen database needs to reside in RAM.

Component Space Needed Rationale
Checkpoint files  At least 3x RAM

You need two checkpoint files and extra space for head room

Transaction Log files  At least 1x RAM The number of Transaction log files can grow if the Oracle Database or other TimesTen replication master database is down.

For example, if you are using an X8-2-HA ODA and want a 500 GB TimesTen database, then your ACFS configuration should be:

  • At least 3*500 = 1500 GB for the DATA file system
  • At least 1*500 = 500 GB for the REDO file system

 

Configure the TimesTen sys.odbc.ini config file

The TimesTen sys.odbc.ini config file contains metadata about a TimesTen database. The sys.odbc.ini files contains ODBC DSNs [Data Source Names] which consist of a set of name/value pairs which defines each TimesTen database.

The following example sys.odbc.ini assumes that a 100 GB database called sampledb will be created on an ODA with hostname odoemoda0:

TimesTen SYS.ODBC.INI

Configure the TimesTen sys.odbc.ini config file

The sys.ttconnect.ini file is used for the 'server side' configuration for TimesTen Client/Server. You need to define the server hostname. eg

TimesTen Client/Server configuration

Configure the TimesTen tnsnames.ora config file

If TimesTen will use OCI based client applications [eg OCI, Pro*C, Node.js, Python etc] then you should update the tnsnames.ora file [pointed to by $TNS_ADMIN]. The tnsnames.ora file will define the mappings between Oracle Net Service Name and TimesTen DSNs. eg

tnsnames.ora

tnsnames.ora file can have both regular Net Service Names that point to Oracle Databases and TimesTen DSNs.

 

Create the TimesTen Database, User and Grants

When a valid TimesTen DSN is used for the first time, it has the side-effect of creating a TimesTen database. A TimesTen Database consists of the checkpoint and transaction log files and a sub-daemon process used to manage that database.

You must use the [direct linked] ttIsql utility on the same host as the TimesTen instance to create the database. The first time that you connect to a TimesTen database may take awhile as it need to create two checkpint files and a bynch of transaction log files. Subsequent connections to a TimesTen database should be sub-second as the file will already exist. eg:

Create a database, user and grants

The above example is called connecting as the TimesTen Instance Administrator [ie the OS user who installed TimesTen]. Some operations require instance administrator connections, eg creating databases, creating users and destroying databases.

You should not create tables as the instance administrator. You should create tables as database users [eg scott].

 

Connecting as a database user

Now that the database user scott has been created, you can connect to the TimesTen database as that user. eg

Connect as user scott

If you do not specify the pwd=yourSecretPassword attribute in the connect string then ttIsql will prompt for the password.

 

Connection over a TCP Socket using the ttIsqlCS utility

Once a database user has been created, you can connect using the Client/Server utility ttIsqlCS. eg

TimesTen Client/Server connections with ttIsqlCS

See the following blogs for more configuration options:

Disclaimer: These are my personal thoughts and do not represent Oracle's official viewpoint in any way, shape, or form.

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.