Normal 0 7.8 磅 0 2

1 測試使用Cache Connect to Oracle

1.1 Setting up TimesTen and Oracle

1.1.1 設定TimesTen的環境變數

[root@server1 ~]# su - tt

[tt@server1 ~]$ cat .bash_profile

# .bash_profile

# Get the aliases and functions

if [ -f ~/.bashrc ]; then

. ~/.bashrc

fi

# User specific environment and startup programs

export TimesTen=/oracle/timesten/TimesTen/tt70

export ORACLE_HOME=/oracle/app/oracle/product/10.2.0/db_1

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:ORACLE_HOME/network/lib:ORACLE_HOME/oracm/lib:/lib:/usr/lib:/usr/local/lib

PATH=$PATH:$HOME/bin:/oracle/timesten/TimesTen/tt70/bin:$ORACLE_HOME/bin

export PATH

unset USERNAME

在這裡要注意要設定$ORACLE_HOME,確保timesten使用者能連線資料庫

[tt@server1 ~]$ sqlplus

SQL*Plus: Release 10.2.0.3.0 - Production on Tue Feb 10 07:50:00 2009

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production

With the Partitioning, Real Application Clusters and Data Mining options

SQL>

1.1.2 建立一個DSN

在這裡一定要注意字符集的設定

#####################################################################

# Test Cache Oracle Data

#####################################################################

[CacheOracleData]

Driver=/oracle/timesten/TimesTen/tt70/lib/libtten.so

DataStore=/oracle/timesten/peyton/TempDs

AutoCreate=1

Logging=1

LogPurge=1

PermSize=16

TempSize=16

UID=scott

OracleId=orcl1

OraclePwd=tiger

DatabaseCharacterSet=ZHS16GBK

ConnectionCharacterSet=ZHS16GBK

1.1.3 建立一個TimesTen使用者

ttIsql CacheOracleData

Command>CREATE USER scott IDENTIFIED BY 'tiger';

Command>GRANT ADMIN, DDL TO scott;

1.2 Creating a READONLY cache group

1.2.1 Create an Oracle table

sqlplus scottt/tiger@orcl1

SQL> CREATE TABLE readtab (a NUMBER(10,0) NOT NULL PRIMARY KEY,b CHAR(31));

SQL> INSERT INTO readtab VALUES (1, 'hello');

SQL> INSERT INTO readtab VALUES (2, 'world');

SQL> COMMIT;

1.2.2 Create a READONLY cache group

[tt@server1 ~]$ ttIsql CacheOracleData

Copyright (c) 1996-2008, Oracle. All rights reserved.

Type ? or "help" for help, type "exit" to quit ttIsql.

All commands must end with a semicolon character.

connect "DSN=CacheOracleData";

Enter password for 'scott':

Connection successful: DSN=CacheOracleData;UID=scott;DataStore=/oracle/timesten/peyton/TempDs;DatabaseCharacterSet=ZHS16GBK;ConnectionCharacterSet=US7ASCII;DRIVER=/oracle/timesten/TimesTen/tt70/lib/libtten.so;OracleId=orcl1;PermSize=16;TempSize=16;TypeMode=0;

(Default setting AutoCommit=1)

Command> call ttCacheUidPwdSet('scott','tiger');

Command> call ttCacheStart();

Command> CREATE READONLY CACHE GROUP readcache AUTOREFRESH INTERVAL 5 SECONDS FROM readtab (a INT NOT NULL PRIMARY KEY, b CHAR(31));

5062: gv$session could not be found on Oracle. Check that scott has SELECT privileges on gv$session

The command failed.

在這裡報了一個錯，由於我是rac環境，這裡單獨在SQL裡又設定

SQL> grant select any dictionary to scott;

Command> CREATE READONLY CACHE GROUP readcache AUTOREFRESH INTERVAL 5 SECONDS FROM readtab (a INT NOT NULL PRIMARY KEY, b CHAR(31));

1.2.3 Load the cache group

Command> LOAD CACHE GROUP readcache COMMIT EVERY 30 ROWS;

Command> cachegroups;

Cache Group SCOTT.READCACHE:

Cache Group Type: Read Only

Autorefresh: Yes

Autorefresh Mode: Incremental

Autorefresh State: On

Autorefresh Interval: 5 Seconds

Root Table: SCOTT.READTAB

Table Type: Read Only

1 cache group found.

1.2.4 Update the Oracle table

SQL> connect scott/tiger@orcl1

SQL> INSERT INTO readtab VALUES (3, 'Hello');

SQL> INSERT INTO readtab VALUES (4, 'Again');

SQL> commit;

Oracle. Check the contents of the READTAB table in ttIsql:

Command> SELECT * FROM readtab;

< 1, hello >

< 2, world >

< 3, Hello >

< 4, Again >

4 rows found.

Command> insert into readtab values (5,'bb');

8225: Table READTAB is read only

The command failed.

Attempts to update a cached table in a READONLY cache group result in

TimesTen error 8225 “Table is read only”. However, if the PassThrough

attribute is set to either 2 or 3, DML statements may be passed through the cache

to Oracle and then propagated by AUTOREFRESH back into the cache group

from Oracle.

Command> autocommit 0;

Command> set passthrough 2;

Command> delete from readtab where a = 5;

1 row deleted.

Command> commit;

SQL> select * from readtab;

A B

---------- -------------------------------

1 hello

2 world

3 Hello

4 Again

1.2.5 Stop the cache agent

Command> call ttCacheStop();

[@more@]