TimesTen In-Memory Database
for Extreme Performance
-
HowTo,
TimesTen Talk
March 18, 2020
Creating an Oracle TimesTen Database on Oracle Database Appliance
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:
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:
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
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 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:
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
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
See the following blogs for more configuration options:
- Oracle Database Appliance for TimesTen
- Installing Oracle TimesTen on an Oracle Database Appliance
- Configuring Oracle Database Appliance X8-2-HA for TimesTen Replication with Clusterware and ACFS
- Optimizing TimesTen Synchronous Replication on Oracle Database Appliance
- Configuring Oracle Database Appliance X8-2-HA for TimesTen Cache and Replication with ACFS
- Configuring Oracle Database Appliance X8-2-HA for TimesTen TLS 1.2 Replication with ACFS
- Configuring Oracle Database Appliance X8-2-HA for TimesTen Replication with ACFS Encryption
- Configuring Oracle Database Appliance X8-2-HA for TimesTen Replication with Clusterware Managed Applications
- Configuring Oracle Database Appliance X8-2-M for TimesTen Scaleout
Disclaimer: These are my personal thoughts and do not represent Oracle's official viewpoint in any way, shape, or form.