This tutorial shows you how to create and manage a simple READONLY cache group in Oracle In-Memory Database Cache 7.0
Approximately 30 minutes
This tutorial covers the following topics:
Place the cursor over this icon to load and view all the screenshots for this tutorial.
(Caution: This action loads all screenshots simultaneously, so response
time may be slow depending on your Internet connection.)
Note: Alternatively, you can place the cursor over each individual icon in the following steps to load and view only the screenshot associated with that step.
The Oracle Database option "In-Memory Database Cache" allows you to cache Oracle tables inside a TimesTen In-Memory database. In-Memory Database Cache uses the concept of a 'Cache group', which describes a collection of in-memory database tables that map to all or a subset of the tables in an Oracle Database. A cache group can consist of all or a subset of the rows and columns in these tables. Cache groups can be read-only or updatable, In-Memory Database Cache provides automatic synchronization between the Oracle database and In-Memory cache database.
The focus of this tutorial is to illustrate how the create and manage a READONLY cache group using SQL statements.
The READONLY cache group that you create in this tutorial is based on the tables 'departments' and 'employees' in the Oracle HR sample schema. This will enable an employee look up application to be built on top of the in-memory cache database; this configuration improves the performance of the application and removes the workload from the main Oracle Human Resource database.
Before starting this tutorial, you should:
| 1. | Have access to or have installed either Oracle client (11g, 10g or 9iR2) or the Oracle database on the same server as In-Memory Database Cache 7.0 |
| 2. | Have access to or have installed Oracle In-Memory Database Cache 7.0 with access control enabled. |
| Note | If the Oracle Client software was installed after In-Memory Database Cache, you must either reboot your system (Windows) or restart the cache database daemon (UNIX) |
| 3. | Have access to or have installed the Oracle HR sample schema in your Oracle database. |
Before you can use In-Memory Database Cache, you need to create an account for the Cache Administration user in the Oracle database. This user owns the system tables and triggers that the in-memory cache database creates in the Oracle database in order to track changes to data, so that they can be used to refresh the cached data in the in-memory cache database.
The rest of the tutorial assumes that the Oracle user hr has the password hr, and the Oracle database connect string is ttorcl.
To create an account for the Cache Administration user, perform the following steps:
1. |
Logging in as the system user, use SQL*Plus and execute the following statement to create an account for the user ttsys: CREATE USER ttsys
IDENTIFIED BY ttsys
Note: It is recommended to create a separate tablespace for the cache
administration user. This tablespace is used as the cache administration user’s
default tablespace. The tablespace contains autorefresh triggers for each Oracle
table, change log tables for each Oracle table cached, and other objects that the In-Memory Database Cache option requires. |
2. |
Grant CREATE ANY TRIGGER privilege to ttsys, this allows the cache administration user to create triggers on tables owned by other Oracle database users (such as the HR tables)
|
| 3. | Log in as the user hr, and grant SELECT on the tables DEPARTMENTS and EMPLOYEES to the cache administration user ttsys:
|
In order to cache tables from the Oracle database, the same Oracle database user must also exist in the cache database. Using the ttIsql utility to connect to the instance datastore, tt_tt70 and create a user, named hr, with ADMIN and DDL privileges.
To create an account in the cache database, perform the following steps:
1. |
Log in as the instance administrator (the OS user who installed the In-Memory Database Cache software), use ttIsql to connect to the instance datastore and execute the following statement to create an account for the user hr: CREATE USER hr IDENTIFIED BY 'hr';
|
2. |
Grant ADMIN and DDL privileges to hr
|
Next, you need to create a DSN (Data Source Name) named ttdemo1 for the cache database.
On Windows, set:
On UNIX / Linux platforms, add the following entry to the sys.odbc.ini file:
[ttdemo1]
Datastore=/tmp/ttdemo1
PermSize=20
UID=hr
OracleId=ttorcl
DatabaseCharacterSet=Must be the same database character set as the Oracle database
Use defaults for all the other settings.
The Oracle database character set can be retrieved by entering the following SQL against the Oracle database.
SELECT value FROM nls_database_parameters
WHERE parameter = 'NLS_CHARACTERSET';
If you need assistance on creating a DSN, check out the viewlets "How to create an in-memory cache database" Linux / Windows for more information.
Now, use the ttIsql utility and start up the in-memory database cache agent. The cache agent is responsible for retrieving data from the Oracle database and caching the data in the cache database.
To start up the cache agent, perform the following steps:
| 1. | Launch ttIsql and connect to the ttdemo1 database. The user hr's passwords for both the cache database and the Oracle database are submitted at connection time. This will create and load the ttdemo1 database into memory.
|
| 2. | Call the built-in procedure ttCacheUidPwdSet( ) to set the Oracle username and password for the cache administration user. Then call the procedure ttCacheStart( ) to start the cache agent.
|
| 3. | Execute the command line utility ttstatus to confirm that the cache agent is up and running.
|
Next, in ttIsql, use the SQL CREATE CACHE GROUP statement to create a READONLY cache group and then populate the cache with the current data from the Oracle database.
To create and populate a readonly cache group, perform the following steps:
| 1. | Cut and Paste the following CREATE CACHE GROUP statement into ttIsql to create a READONLY cache group, named CG_READONLY_DEPT_EMP, to cache the DEPARTMENTS and EMPLOYEES tables from the HR sample schema. create readonly cache group HR.CG_READONLY_DEPT_EMP
|
| 2. | Use the ttIsql cachegroups command to check the definition of the newly created cache group.
|
| 3. | Load the content of the Oracle tables into the READONLY cache group. load cache group cg_readonly_dept_emp commit every 256 rows;
After loading the data, the autorefresh state for the cache group will automatically be changed to ON. Changes to the data in the Oracle DEPARTMENTS and EMPLOYEES tables will now be refreshed in the cache database.
|
Use SQL*Plus, to insert a new employee into the EMPLOYEES table and commit the record. Verify the changes in the cache database.
| 1. | Insert a new employee into the Oracle HR.EMPLOYEES table. insert into employees |
| 2. | After 1 second (this is the refresh interval defined in the CREATE CACHE GROUP statement), the in-memory database cache will automatically refresh its cached data from Oracle. Check for the new data using ttIsql:
|
The SQL DROP CACHE GROUP statement can be used to remove the cache group from the in-memory cache database.
|
|
| Call the procedure ttCacheStop to stop the cache agent. Use ttstatus to confirm that the cache agent has been shut down.
|
In this lesson, you learned how to:
|
Create and Drop a READONLY Cache Group | |
|
Create a Cache Administration user in the Oracle database | |
|
Create a user account in the Cache database | |
|
Load data into a READONLY Cache Group | |
|
Start up and shut down the cache database Cache Agent | |
|
Use ttstatus to monitor the status of the in-memory cache database |
|
To learn more about the Oracle database option "In-Memory Database Cache" you can refer to:
|
Getting Started: In-Memory Database Cache Quick Start | |
|
Oracle In-Memory Database Cache Product Center on OTN | |
Place the cursor over this icon to hide all screenshots.