月归档：二月 2012

一、Oracle数据库创建相关用户和权限
1.创建timesten用户
store information about cache grids

SQL> CREATE TABLESPACE cachetblsp DATAFILE
  2  'E:\ORACLE\ORADATA\XFF\datfttuser.dbf'
  3  SIZE 10M autoextend on next 10m maxsize 30g;

表空间已创建。

SQL> @E:\oracle\timesten\oraclescripts\initCacheGlobalSchema "cachetblsp"

Please enter the tablespace where TIMESTEN user is to be created
The value chosen for tablespace is cachetblsp

******* Creation of TIMESTEN schema and TT_CACHE_ADMIN_ROLE starts *******
1. Creating TIMESTEN schema
2. Creating TIMESTEN.TT_GRIDID table
3. Creating TIMESTEN.TT_GRIDINFO table
4. Creating TT_CACHE_ADMIN_ROLE role
5. Granting privileges to TT_CACHE_ADMIN_ROLE
** Creation of TIMESTEN schema and TT_CACHE_ADMIN_ROLE done successfully **

PL/SQL 过程已成功完成。

2.创建测试用户
the Oracle tables to be cached in a TimesTen database

SQL> create user xff identified by xifenfei;

用户已创建。

SQL> grant create session,resource to xff;

授权成功。

3.创建cache管理用户
creates and maintains Oracle objects that store information used to manage cache grids and enforce predefined behaviors of particular cache group types.

SQL> CREATE USER cacheuser IDENTIFIED BY oracle
  2  DEFAULT TABLESPACE cachetblsp QUOTA UNLIMITED ON cachetblsp;

用户已创建。

SQL>  @grantCacheAdminPrivileges "cacheuser"

Please enter the administrator user id
The value chosen for administrator user id is cacheuser

***************** Initialization for cache admin begins ******************
0. Granting the CREATE SESSION privilege to CACHEUSER
1. Granting the TT_CACHE_ADMIN_ROLE to CACHEUSER
2. Granting the DBMS_LOCK package privilege to CACHEUSER
3. Granting the RESOURCE  privilege to CACHEUSER
4. Granting the CREATE PROCEDURE  privilege to CACHEUSER
5. Granting the CREATE ANY TRIGGER  privilege to CACHEUSER
6. Granting the DBMS_LOB package privilege to CACHEUSER
7. Granting the SELECT on SYS.ALL_OBJECTS privilege to CACHEUSER
8. Granting the SELECT on SYS.ALL_SYNONYMS privilege to CACHEUSER
9. Checking if the cache administrator user has permissions on the default
tablespace
     Permission exists
11. Granting the CREATE ANY TYPE privilege to CACHEUSER
********* Initialization for cache admin user done successfully *********

二、TimesTen创建相关用户
1.cache管理用户
A cache manager user performs cache grid and cache group operations. The TimesTen cache manager user must have the same name as an Oracle user that can access the cached Oracle tables.

Command>  CREATE USER cacheuser IDENTIFIED BY timesten;

User created.

Command> GRANT CREATE SESSION, CACHE_MANAGER, CREATE ANY TABLE TO cacheuser;

2.cache对应用户
You must create a TimesTen cache table user with the same name as an Oracle schema user for each schema user who owns or will own Oracle tables to be cached in the TimesTen database

Command> create user xff identified by timesten;

User created.

三、配置DSN

Data Store Path + Name: E:\oracle\timesten\mytt_db\data
Permanent Data Size: 64
Oracle Net Service Name: XFF
Database Character Set: ZHS16GBK

四、在TT中设置cache管理用户名和密码

ttIsql "DSN=my_ttdb;UID=cacheuser;PWD=timesten;OraclePWD=oracle"
Command> call ttCacheUidPwdSet('cacheuser','oracle');
Warning  5183: Function kollglid2 was not in the library. The function will not
be called.
Warning  5183: Function kollgsnp2 was not in the library. The function will not
be called.
Warning  5186: The OCI client library in use does not contain required routines
to support caching LOBS from Oracle.

--第一次调用相关函数因为没有编译出错，再次调用即可
Command> call ttCacheUidPwdSet('cacheuser','oracle');

五、创建测试表（Oracle db中）

SQL>  CREATE TABLE readtab (keyval NUMBER NOT NULL PRIMARY KEY, str VARCHAR2(32));

表已创建。

SQL> CREATE TABLE writetab (pk NUMBER NOT NULL PRIMARY KEY, attr VARCHAR2(40));

表已创建。

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

已创建 1 行。

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

已创建 1 行。

SQL> INSERT INTO writetab VALUES (100, 'XIFENFEI');

已创建 1 行。

SQL> INSERT INTO writetab VALUES (101, 'WWW.XIFENFEI.COM');

已创建 1 行。

SQL> COMMIT;

提交完成。

SQL> GRANT SELECT ON readtab TO cacheuser;

授权成功。

SQL> GRANT SELECT ON writetab TO cacheuser;

授权成功。

SQL> GRANT INSERT ON writetab TO cacheuser;

授权成功。

SQL> GRANT UPDATE ON writetab TO cacheuser;

授权成功。

SQL>  GRANT DELETE ON writetab TO cacheuser;

授权成功。

六、TimesTen相关配置
1.创建cache grid

Command> call ttGridCreate('myGrid');
Command>  call ttGridNameSet('myGrid');

2.Start the cache agent

Command> call ttCacheStart;

3.创建cache group

Command> call ttCacheStart;
Command> CREATE READONLY CACHE GROUP readcache
       > AUTOREFRESH INTERVAL 5 SECONDS
       > FROM XFF.readtab
       > (keyval NUMBER NOT NULL PRIMARY KEY, str VARCHAR2(32));
Command> CREATE DYNAMIC ASYNCHRONOUS WRITETHROUGH  CACHE GROUP writecache
       > FROM XFF.writetab
       > (pk NUMBER NOT NULL PRIMARY KEY, attr VARCHAR2(40));

Command> cachegroups;

Cache Group CACHEUSER.READCACHE:

  Cache Group Type: Read Only
  Autorefresh: Yes
  Autorefresh Mode: Incremental
  Autorefresh State: Paused
  Autorefresh Interval: 5 Seconds
  Autorefresh Status: ok
  Aging: No aging defined

  Root Table: XFF.READTAB
  Table Type: Read Only

Cache Group CACHEUSER.WRITECACHE:

  Cache Group Type: Asynchronous Writethrough (Dynamic)
  Autorefresh: No
  Aging: LRU on

  Root Table: XFF.WRITETAB
  Table Type: Propagate

2 cache groups found.

4.Start the replication agent for the AWT cache group

Command> call ttRepStart;

5.Manually load the cache group

Command>  LOAD CACHE GROUP readcache COMMIT EVERY 256 ROWS;
2 cache instances affected.
Command> LOAD CACHE GROUP writecache COMMIT EVERY 256 ROWS;
2 cache instances affected.

七、TT授权
主要是为了直接在cacheuser中操作方便，无其他应意义

ttisql my_ttdb
Command> GRANT SELECT ON xff.readtab TO cacheuser;
Command> GRANT SELECT ON xff.writetab TO cacheuser;
Command> GRANT UPDATE ON xff.writetab TO cacheuser;
Command> GRANT DELETE ON xff.writetab TO cacheuser;
Command> GRANT INSERT ON xff.writetab TO cacheuser;

八、相关测试
1.测试READTAB表

Command> SELECT * FROM XFF.READTAB;
< 1, Hello >
< 2, World >
2 rows found.

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

已创建 1 行。

SQL> commit;

提交完成。

Command> SELECT * FROM XFF.READTAB;
< 1, Hello >
< 2, World >
< 3, Welcome >
3 rows found.

SQL> update readtab set str='www.xifenfei';

已更新3行。

SQL> commit;

提交完成。

Command> SELECT * FROM XFF.READTAB;
< 1, www.xifenfei >
< 2, www.xifenfei >
< 3, www.xifenfei >
3 rows found.

SQL> delete from readtab where keyval=3;

已删除 1 行。

SQL> commit;

提交完成。

Command> SELECT * FROM XFF.READTAB;
< 1, www.xifenfei >
< 2, www.xifenfei >
2 rows found.

2.测试WRITETAB表

Command> SELECT * FROM XFF.writeTAB;
< 100, XIFENFEI >
< 101, WWW.XIFENFEI.COM >
2 rows found.
Command> update xff.writetab set attr='www.xifenfei.com' where pk=100;
1 row updated.
Command> commit;

SQL> select * from writetab;

        PK ATTR
---------- ----------------------------------------
       100 www.xifenfei.com
       101 WWW.XIFENFEI.COM

Command> insert into xff.writetab values(102,'xifenfei');
1 row inserted.
Command> commit;

SQL> select * from writetab;

        PK ATTR
---------- --------------------------------
       102 xifenfei
       100 www.xifenfei.com
       101 WWW.XIFENFEI.COM

相关文档

ttrestore使用说明

E:\>ttrestore -h
Usage:
  ttrestore [-h | -help | -?]
  ttrestore [-V | -version]
  ttrestore [-fname <filePrefix>] [-noconn] -dir <directory>
                 {<DSN> | [-connstr] <connStr>}
  ttrestore -i [-noconn] {<DSN> | [-connstr] <connStr>}

options:
  -h | -help | -?      Prints this message and exits.
  -V | -version        Prints the release number and exits.
  -fname <filePrefix>  The file prefix for the backup files in the backup
                       directory. Default is the base filename portion of the
                       DataStore parameter of the data store to be restored.
  -dir <directory>     The directory in which the backup files are stored.
  -noconn              Do not test-connect after restoring the data store.
  -i                   Read from standard input for stream data.
  <DSN>, <connStr>     The DSN or ODBC connection string of the data store
                       to be restored.

查看库中当前情况

Command> tables;
  XIFENFEI.REP_TABLE
  XIFENFEI.T1
  XIFENFEI.T2
  XIFENFEI.T3
  XIFENFEI.T4
  XIFENFEI.V4
  XIFENFEI.XFF
7 tables found.
Command> select * from t2;
< 3 >
< 5 >
< 3 >
< 5 >
< 3 >
< 5 >
< 3 >
< 5 >
< 3 >
< 5 >
< 3 >
< 5 >
12 rows found.

删除数据文件和日志文件

E:\oracle\timesten\mytt_db>dir
 驱动器 E 中的卷没有标签。
 卷的序列号是 38D0-2A35

 E:\oracle\timesten\mytt_db 的目录

2012/02/23  22:58    <DIR>          .
2012/02/23  22:58    <DIR>          ..
2012/02/11  19:06    <DIR>          data
2012/02/23  22:57    <DIR>          log
               0 个文件              0 字节
               4 个目录  9,478,365,184 可用字节

E:\>ttisql my_ttdb

Copyright (c) 1996-2011, Oracle.  All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.



connect "DSN=my_ttdb";
  821: No readable checkpoint files.  OS error: '系统找不到指定的文件。'.  Consi
der connecting with Overwrite=1 to create new data store
The command failed.
Done.

还原数据库

E:\>ttrestore -dir E:\oracle\timesten\tt_back\full   -fname xifenfei01_ my_ttdb
Restore started ...
Restore complete

E:\oracle\timesten\mytt_db>dir
 驱动器 E 中的卷没有标签。
 卷的序列号是 38D0-2A35

 E:\oracle\timesten\mytt_db 的目录

2012/02/23  23:01    <DIR>          .
2012/02/23  23:01    <DIR>          ..
2012/02/11  19:06    <DIR>          data
2012/02/23  23:01        21,119,936 data.ds0
2012/02/23  23:01        21,119,936 data.ds1
2012/02/23  23:01    <DIR>          log
               2 个文件     42,239,872 字节
               4 个目录  9,222,610,944 可用字节

测试还原结果

E:\>ttisql my_ttdb

Copyright (c) 1996-2011, Oracle.  All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.



connect "DSN=my_ttdb";
Connection successful: DSN=my_ttdb;UID=XIFENFEI;DataStore=E:\oracle\timesten\myt
t_db\data;DatabaseCharacterSet=ZHS16GBK;ConnectionCharacterSet=ZHS16GBK;DRIVER=E
:\oracle\timesten\bin\ttdv1122.dll;LogDir=E:\oracle\timesten\mytt_db\log;PermSiz
e=64;TempSize=32;RACCallback=0;TypeMode=0;OracleNetServiceName=XFF;
(Default setting AutoCommit=1)
Command> tables;
  XIFENFEI.REP_TABLE
  XIFENFEI.T1
  XIFENFEI.T2
  XIFENFEI.T3
  XIFENFEI.T4
  XIFENFEI.V4
  XIFENFEI.XFF
7 tables found.
Command> select * FROM T2;
< 3 >
< 5 >
< 3 >
< 5 >
< 3 >
< 5 >
< 3 >
< 5 >
< 3 >
< 5 >
< 3 >
< 5 >
12 rows found.

补充说明
1)ttRestore 除了可以作为数据库还原操作外，还可以用来做数据库复制

ttBackup -dir /users/rob/tmp -fname restored "dsn=origDSN"
ttRestore -dir /users/rob/tmp -fname restored "dsn=restoredDSN"

2)对于在Linux或者unix系统使用streamFull备份恢复方式

dd bs=64k if=/dev/rmt0 | ttRestore -i DSN=FastIns

3)该命令是用来数据库还原，那么对于备份之后到数据库异常这段时间的数据该如何处理，是否会丢失暂时还未知。