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.
Overview
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. Multiple cache groups can be used to cache different sets of related tables in the Oracle Database. Updates to the cache tables in the cache database can be asynchronously or synchronously write-through to the Oracle database, depend upon the desired trade off between performance and consistency.
The focus of this tutorial is to illustrate how to create and manage an ASYNCHRONOUS WRITETHROUGH (AWT) cache group using SQL statements.
The ASYNCHRONOUS WRITETHROUGH (AWT) cache group that you create in this tutorial is based on the 'customers' and 'orders' tables in an Oracle database. This enables the processing of the customer orders to be performed in the In-memory cache database, hence increasing the response time and throughput of the order entry application.It also removes the workload from the backend Oracle Order & Billing systems.
Have access to or have installed either Oracle client (11g, 10g or 9iR2) Or the Oracle database on the same server as the Oracle In-Memory Database Cache 7.0
2.
Have access to or have installed Oracle In-Memory Database Cache 7.0 with access control enabled.
Check out the viewlets "How to install Oracle In-Memory Database Cache 7.0" Linux / Windows for more information.
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)
The tutorial assumes that the Oracle demo user BILL owns the CUSTOMERS and ORDERS tables, and the connect string to the remote Oracle database is ttorcl.
To create the Oracle demo schema, perform the following steps:
1.
Use SQL*Plus, log in as the system user and execute the following statements to create a user account for bill:
CREATE USER bill
IDENTIFIED BY bill
DEFAULT TABLESPACE users;
2.
Login in as the user Bill, create and populate the CUSTOMERS and ORDERS tables.
You can cut and paste the following CREATE TABLE and INSERT statements into SQL*PLUS, to create and populate the demo tables.
Alternatively you can download and run the create_obj.sql script manually to populate Bill's schema.
CREATE TABLE CUSTOMERS ( CUST_NUMBER NUMBER,
FIRST_NAME VARCHAR2(12) NOT NULL,
LAST_NAME VARCHAR2(12) NOT NULL,
ADDRESS VARCHAR2(100) NOT NULL,
CONSTRAINT CUSTOMERS_PK PRIMARY KEY (CUST_NUMBER));
insert into customers values (3700,'Peter','Burchard','882 Osborne Avenue, Boston, MA 02122');
insert into customers values (1121,'Saul','Mendoza','721 Stardust Street, Mountain View, CA 94043');
insert into customers values (1001,'Steven','McPhee','72 Vine Street, San Jose, CA 95125');
insert into customers values (1224,'Abdul','Aziz','6793 Bird Avenue, San Jose, CA 95126');
insert into customers values (3611,'Katherine','McKenzie','54 East 21st Avenue, New York, NY 10009');
CREATE TABLE ORDERS (
ORDER_NUMBER NUMBER NOT NULL,
CUST_NUMBER NUMBER NOT NULL,
PROD_NUMBER CHAR(10) NOT NULL,
ORDER_DATE DATE NOT NULL,
CONSTRAINT ORDERS_PK PRIMARY KEY (ORDER_NUMBER),
CONSTRAINT ORDERS_CUST_NUMBER_FK FOREIGN KEY (CUST_NUMBER) REFERENCES CUSTOMERS (CUST_NUMBER));
insert into ORDERS values (6853036,3700,'0028616731',to_date('2006-04-05','yyyy-mm-dd'));
insert into ORDERS values (6853041,3700,'0198612710',to_date('2006-01-12','yyyy-mm-dd'));
insert into ORDERS values (6853169,1121,'0003750299',to_date('2006-08-01','yyyy-mm-dd'));
insert into ORDERS values (6853174,1121,'0789428741',to_date('2006-02-02','yyyy-mm-dd'));
insert into ORDERS values (6853179,1121,'0198612583',to_date('2006-10-25','yyyy-mm-dd'));
insert into ORDERS values (6854765,1001,'0198612605',to_date('2006-11-25','yyyy-mm-dd'));
insert into ORDERS values (6854770,1001,'0198612710',to_date('2006-12-07','yyyy-mm-dd'));
insert into ORDERS values (6855297,1224,'0877799113',to_date('2007-01-03','yyyy-mm-dd'));
insert into ORDERS values (6855430,3611,'0877797099',to_date('2006-06-24','yyyy-mm-dd'));
insert into ORDERS values (6855435,3611,'0877799113',to_date('2006-12-05','yyyy-mm-dd'));
insert into ORDERS values (6855440,3611,'0198612710',to_date('2007-03-13','yyyy-mm-dd'));
insert into ORDERS values (6855445,3611,'0198612583',to_date('2006-11-11','yyyy-mm-dd'));
COMMIT;
3.
Execute SELECT COUNT(*) on the tables to make sure the objects have been created correctly.
Creating a Cache Administration user account in Oracle
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 that the cache database creates in the Oracle database in order to track changes to the data, the Cache Administration user is also responsible for applying the data changes from the cache database back to the Oracle database.
To create an account for the Cache Administration user, perform the following steps:
1.
Use SQL*Plus, log in as the system user and execute the following statements to create an account and grant unlimited tablespace to the user ttsys:
CREATE USER ttsys
IDENTIFIED BY ttsys
DEFAULT TABLESPACE cache_administration_user_tablespace;
GRANT UNLIMITED TABLESPACE TO 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 the objects that the 'In-Memory Database Cache" option requires.
2.
Grant CREATE TABLE privilege to ttsys, this allows the cache administration user to create a system table on Oracle to support AWT. This table is used to track the state and the last transaction that was applied to Oracle.
3.
Next, log in as the user Bill, and grant SELECT, INSERT, UPDATE, and DELETE on the CUSTOMERS and ORDERS tables 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 bill, 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 bill:
Next, you need to create and define a DSN (Data Source Name) named ttdemo2 for the cache database.
On Windows, set:
Data Source Name: ttdemo2
Data Store Path and Name: c:\temp\ttdemo2
Permanent Data Size: 20
User ID: bill
Oracle ID:ttorcl
Database Character Set: Must be the same database character set as the Oracle database
On UNIX / Linux platforms, add the following entry to the sys.odbc.ini file:
[ttdemo2]
Datastore=/tmp/ttdemo2
PermSize=20
UID=bill
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 executing 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, in ttIsql use the SQL CREATE CACHE GROUP statement to create an ASYNCHRONOUS WRITETHROUGH cache group.
To create an AWT cache group,
perform the following steps:
1.
Launch the ttIsql command line utility and connect to the ttdemo2 database, note that the user Bill's passwords for both the cache database and the Oracle database are submitted at connection time as part of the connect string. This will create and load the ttdemo2 database into memory.
2.
Call the built-in procedure ttCacheUidPwdSet to set the Oracle username and password for the cache administration user.
3.
Cut and paste the following CREATE CACHE GROUP statement into ttIsql, to create an AWT cache group named CG_AWT_CUS_ORDER, to cache the CUSTOMERS and ORDERS tables from the Oracle schema Bill.
create asynchronous writethrough cache group BILL.CG_AWT_CUS_ORDER
from BILL.CUSTOMERS (
CUST_NUMBER NUMBER NOT NULL,
FIRST_NAME VARCHAR2(12) NOT NULL,
LAST_NAME VARCHAR2(12) NOT NULL,
ADDRESS VARCHAR2(100) NOT NULL,
primary key (CUST_NUMBER)),
BILL.ORDERS (
ORDER_NUMBER NUMBER NOT NULL,
CUST_NUMBER NUMBER NOT NULL,
PROD_NUMBER CHAR(10) NOT NULL,
ORDER_DATE DATE NOT NULL,
primary key (ORDER_NUMBER),
foreign key (CUST_NUMBER)
references BILL.CUSTOMERS (CUST_NUMBER));
4.
Use the ttIsql command cachegroups to check the definition of the newly created cache group.
5.
Cache tables belonging to a Cache Group are similar to regular in-memory database tables. You can use the command desc to describe the tables, and perform SQL SELECT statements against them.
To enable the propagation of data between the in-memory cache database and Oracle for an AWT cache group, both the Cache and Replication
agents must be running. The cache agent is responsible for loading and refreshing the cache, whereas the replication agent manages the replication schemes that enables the cache database to communicate with Oracle. Use the ttIsql utility and start up both agents.
To start up the agents, perform the following steps:
1.
Call the built-in procedure ttCacheStart to start the Cache agent.
2.
Call the built-in procedure ttRepStart to start the Replication agent.
3.
Execute the ttstatus command line utility to confirm that the cache and replication agents are up and running. You can also launch ttstatus within the current ttIsql session via the command HOST.
From this point forward, all updates to the in-memory cache tables CUSTOMERS and ORDERS will be applied to their Oracle counterparts also.
The SQL DROP CACHE GROUP statement can be used to remove the cache group from the cache database.
Please note the Replication Agent must be stopped before dropping or creating an AWT Cache Group.
To drop an AWT cache group, perform the following steps:
Call the built-in procedure ttRepStop to stop the Replication agent, and then execute the DROP CACHE GROUP statement to drop the cache group.
Bookmark
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.)
