The Oracle In-Memory Database Cache (IMDB Cache) is an Oracle Database product option ideal for caching performance-critical subsets of an Oracle database in the application tier. Using the IMDB Cache improves an application’s response time and throughput.
The IMDB Cache consists of three key technology components
· Oracle TimesTen In-Memory Database (TimesTen) for application-tier real-time data management;
· Caching technology to cache frequently –accessed tables from an Oracle Database server to the application tier and maintain consistency of cached data;
· Transactional data replication component to ensure cross-tier high availability
The IMDB Cache enables the caching of subsets of tables from an Oracle Database to the application tier. Cached tables are updatable and the IMDB Cache synchronizes data between Oracle Database and the cache.
This blog is an attempt to list down steps to create Oracle Times Ten 11.2.2 Cache database on Windows.
Steps to Create Times Ten 11.2.2 Cache Database are as follows
1. Configure network connection to the Oracle database
Go to Times Ten Installation directory and execute ttenv.bat to set environmental variable, one of the variable setting should be TNS_ADMIN, its value determines the location of
TNSNAMES.ORA file which Times Ten uses to connect to an Oracle Database.
Verify that TNS_ADMIN value is pointing towards the current directory. If TNS_ADMIN value is not defined or directory is not correct, use the utility ttmodinstall to set this parameter.
For the demo, plan is to cache tables from Oracle ‘BI’ schema and net service name to the oracle database is ‘orcl’, make sure that the you know the character set of oracle database. It is required while defining DSN to set up the cache in next step.
Test connection with Oracle Database by SQL + and also check the character set of oracle database with following command.
select * from nls_database_parameters where parameter='NLS_CHARACTERSET'
2. Define a Data Source Name ( DSN)
DSN – Defines the configuration of cache database, creation of DSN is required before creating cache database
Go to Control Panel -> Administrative Tools -> Data Sources (ODBC)
Choose Tab System DSN, Click on Add to Create a New Data Source, Select the Times Ten Data Manager 11.2.2 Driver and then fill in details for Times Ten ODBC setup
The Times Ten ODBC setup has following tabs
3. Data Store - Specify the data source name, the data store path/name, transaction log directory and database character set
4. First Connection – Specify following parameters
Permanent Data Size – is the size in MB for the permanent partition of the database (Set it to 40 MB)
Temporary Data Size – is the Size in MB for temporary partition of the database (Set it to 32 MB)
5. IMDB Cache –
Keep other setting as default and click ok to create DSN
6. Check Times Ten Database Status - The main database daemon process must be running before a database can be created.
Run ttenv.bat command to set environmental variables and then run ttstatus to check the database status
7. Connect to Database - Use command Line Utility - ttiSQL TT_BI_CACHE , where TT_BI_CACHE is DSN Name
At first connection, the database TT_BI_CACHE is being created and loaded into memory and ready to run commands
8. Times Ten Commands
a. dssize - to check the size of database
b. ttstatus ( via the host command ) – to view the database connection status for system processes and the database connections
c. Create a Cache Database User and grant CREATE SESSION and CREATE TABLE privileges
In order to cache Oracle tables, the user who owns the Oracle tables to be cached must also exists in the cache database
CREATE USER BI IDENTIFIED BY oracle99;
GRANT CREATE SESSION, CREATE TABLE TO BI;
d. Connect to TT_BI_CHACHE Database with User (BI)
CONNECT “dsn= TT_BI_CACHE;uid=BI”;
e. Create table
Can execute DDL and DML statements against the cache database just like a SQL relational database
f. Create Index
g. List Tables and Indexes
9. Verify the connection to the Oracle Database using Passthrough
Passthrough enables SQL statements to be executed to the Oracle database through a Times Ten database connection. Verify the connection to the Oracle database
passthrough 3; - to enable SQL access to Oracle database through a Times Ten
Select * from v$version; - it should execute this command against the oracle instance.
Use ‘Exit’ command to disconnect from the database.
10. Check the content of the DataStore and Transaction Log directories. The database check point and transaction log files should be created
a. TimesTen maintain two complete images of the contents of a database on disk. These images are called checkpoints files
b. Transaction logs are persisted to disk, They are used for database recovery and replication
