TimesTen 应用层数据库缓存学习:3. 环境准备 - DB 12cR1版本

本文主要是介绍TimesTen 应用层数据库缓存学习:3. 环境准备 - DB 12cR1版本,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

本文通过一个例子讲述在建立Cache Group之前的准备工作。
准备工作包含4个部分。
1. 在Oracle数据库中创建用户
2. 为TimesTen数据库创建DSN
3. 在TimesTen 数据库中建立用户
4. 在TimesTen数据库中设置cache administration用户名和口令

本例演示环境包括一个Oracle数据库TTORCL(对应于12c的可插拔数据库pdborcl),版本为12.1.0.2.0;一个TimesTen数据库cachedb1_1122作为其缓存,版本为11.2.2.8.11 (64 bit Linux/x86_64)。Oracle中需要缓存的数据为用户tthr拥有。
所有在Oracle中新建的用户口令均为oracle, 在TimesTen中建立的用户口令均为timesten。TimesTen和Oracle中的缓存管理用户名皆为: cacheadm
Oracle和TimesTen共用一个tnsnames.ora,内容如下:

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.TTORCL =(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = pdborcl)))PDBORCL =(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = pdborcl)))
  • 使用以下的语句连接到Oracle数据库
    DBA:$ sqlplus sys/oracle@ttorcl as sysdba
    应用用户:$ sqlplus tthr/oracle@ttorcl
  • 使用以下的语句连接到TimesTen数据库
    DBA: $ ttisql cachedb1_1122
    Cache Admin:$ ttisql 'dsn=cachedb1_1122;uid=cacheadm;pwd=timesten;oraclepwd=oracle'
    应用用户:$ ttisql 'dsn=cachedb1_1122;uid=tthr;pwd=timesten

在Oracle数据库中创建用户

这一部分我们需要在Oracle可插拔数据库中提供三个用户,一个名为TIMESTEN的用户,一个schema 用户,一个cache admin user。
除TIMESTEN用户名不可改变外,一般schema用户都已经存在,在我们后续的例子中,我们设定schema用户为tthr,cache admin用户为cacheadm
每一个TimesTen数据库只能对应一个cache admin user,但一个cache admin user可以管理多个TimesTen数据库

TIMESTEN用户

$ cd $TT_HOME/oraclescripts/
# 此目录下包含许多在Oracle中执行,设置TimesTen Cache Group的脚本
$ ls
cacheCleanUp.sql  cacheInfo.sql  grantCacheAdminPrivileges.sql  initCacheAdminSchema.sql  initCacheGlobalSchema.sql  initCacheGridSchema.sql  README.TXT# 下面的语句中,ttorcl为Oracle数据库的实例名,在tnsnames.ora中设置$ sqlplus sys/oracle@ttorcl as sysdbaSQL*Plus: Release 12.1.0.2.0 Production on Sat Apr 23 13:57:21 2016Copyright (c) 1982, 2014, Oracle.  All rights reserved.Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing optionsSQL> CREATE TABLESPACE cachetblsp DATAFILE 'cachetblsp.dbf' SIZE 100M;Tablespace created.SQL> @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 procedure successfully completed.

应用Schema用户

应用Schema用户通常已经存在,后续建立的cache group就是要cache这个schema user的表,作为示例,这里还是演示其过程,本例中,Schema用户为tthr,
我们使用TimesTen安装中自带的HR Schema

$ cd $TT_HOME/quickstart/sample_scripts/hrschema/
$ ls
hr_cre_tt.sql  hr_idx_tt.sql  hr_popul_tt.sql  README.TXT
$ sqlplus sys/oracle@ttorcl as sysdbaSQL*Plus: Release 12.1.0.2.0 Production on Sat Apr 23 13:59:39 2016Copyright (c) 1982, 2014, Oracle.  All rights reserved.Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing optionsSQL> create user tthr identified by oracle;User created.SQL> GRANT CREATE SESSION, RESOURCE, CREATE VIEW TO tthr;      Grant succeeded.SQL> alter user tthr quota unlimited on users;
User altered.SQL> connect sys as sysdba
Enter password: 
Connected.
SQL> select * from dba_sys_privs where grantee='RESOURCE';GRANTEE  PRIVILEGE                ADM COM
-------- ---------------------------------------- --- ---
RESOURCE CREATE TABLE                 NO  YES
RESOURCE CREATE OPERATOR              NO  YES
RESOURCE CREATE TYPE                  NO  YES
RESOURCE CREATE CLUSTER               NO  YES
RESOURCE CREATE TRIGGER               NO  YES
RESOURCE CREATE INDEXTYPE             NO  YES
RESOURCE CREATE PROCEDURE             NO  YES
RESOURCE CREATE SEQUENCE              NO  YES8 rows selected.SQL> connect tthr/oracle@ttorcl
Connected.SQL> @hr_cre_tt.sql <-建表
SQL> @hr_idx_tt.sql <-建索引
SQL> @hr_popul_tt.sql <-加载数据

Cache 管理用户

$ cd $TT_HOME/oraclescripts/
$ sqlplus sys/oracle@ttorcl as sysdbaSQL*Plus: Release 12.1.0.2.0 Production on Sat Apr 23 14:32:06 2016Copyright (c) 1982, 2014, Oracle.  All rights reserved.Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing optionsSQL> CREATE USER cacheadm IDENTIFIED BY oracle
DEFAULT TABLESPACE cachetblsp QUOTA UNLIMITED ON cachetblsp;   2  User created.SQL> @grantCacheAdminPrivileges "cacheadm"Please enter the administrator user id
The value chosen for administrator user id is cacheadm***************** Initialization for cache admin begins ******************
0. Granting the CREATE SESSION privilege to CACHEADM
1. Granting the TT_CACHE_ADMIN_ROLE to CACHEADM
2. Granting the DBMS_LOCK package privilege to CACHEADM
3. Granting the CREATE SEQUENCE privilege to CACHEADM
4. Granting the CREATE CLUSTER privilege to CACHEADM
5. Granting the CREATE OPERATOR privilege to CACHEADM
6. Granting the CREATE INDEXTYPE privilege to CACHEADM
7. Granting the CREATE TABLE privilege to CACHEADM
8. Granting the CREATE PROCEDURE  privilege to CACHEADM
9. Granting the CREATE ANY TRIGGER  privilege to CACHEADM
10. Granting the GRANT UNLIMITED TABLESPACE privilege to CACHEADM
11. Granting the DBMS_LOB package privilege to CACHEADM
12. Granting the SELECT on SYS.ALL_OBJECTS privilege to CACHEADM
13. Granting the SELECT on SYS.ALL_SYNONYMS privilege to CACHEADM
14. Checking if the cache administrator user has permissions on the default
tablespacePermission exists
16. Granting the CREATE TYPE privilege to CACHEADM
17. Granting the SELECT on SYS.GV$LOCK privilege to CACHEADM (optional)
18. Granting the SELECT on SYS.GV$SESSION privilege  to CACHEADM (optional)
19. Granting the SELECT on SYS.DBA_DATA_FILES privilege  to CACHEADM (optional)
20. Granting the SELECT on SYS.USER_USERS privilege  to CACHEADM (optional)
21. Granting the SELECT on SYS.USER_FREE_SPACE privilege  to CACHEADM (optional)
22. Granting the SELECT on SYS.USER_TS_QUOTAS privilege  to CACHEADM (optional)
23. Granting the SELECT on SYS.USER_SYS_PRIVS privilege  to CACHEADM (optional)
********* Initialization for cache admin user done successfully **********

为TimesTen数据库创建DSN

此处建立的DSN是作为Oracle数据库的缓存,最关键的是其字符集属性必须与Oracle一致
从Oracle中,查询到字符集为AL32UTF8

$ sqlplus -S sys/oracle@ttorcl as sysdba
SELECT value FROM nls_database_parameters WHERE parameter='NLS_CHARACTERSET';VALUE
--------------------------------------------------------------------------------
AL32UTF8

使用TimesTen安装自带的样例DSN: cachedb1_1122,修改字符集和Oracle服务属性

[cachedb1_1122]
Driver=/home/oracle/TimesTen/tt1122/lib/libtten.so
DataStore=/home/oracle/TimesTen/tt1122/info/DemoDataStore/cachedb1_1122
PermSize=40
TempSize=32
PLSQL=1
DatabaseCharacterSet=AL32UTF8
OracleNetServiceName=ttorcl

然后在TimesTen中确定字符集属性与Oracle一致


Command> call ttconfiguration('DataBaseCharacterSet');
< DataBaseCharacterSet, AL32UTF8 >

在TimesTen 数据库中建立用户

在TimesTen中需要建立两个用户
1. Cache Manager
Cache管理用户,执行各种Cache Group和Cache grid操作,通常与Oracle中的Cache Admin用户名一致,本例为cacheadmin
2. Schema用户
对应Oracle中的Schema用户,用户名必须一致,本例为tthr

Then, you must create a user with the same name as an Oracle Database schema user for each schema user who owns or will own Oracle Database tables to be cached in the TimesTen database. We refer to these users as cache table users, because the TimesTen cache tables are to be owned by these users. Therefore, the owner and name of a TimesTen cache table is the same as the owner and name of the corresponding cached Oracle Database table. The password of a cache table user can be different than the password of the Oracle Database schema user with the same name.

$ ttisql -v1 cachedb1_1122
CREATE USER cacheadm IDENTIFIED BY timesten;
GRANT CREATE SESSION, CACHE_MANAGER, CREATE ANY TABLE, DROP ANY TABLE TO cacheadm;
CREATE USER tthr IDENTIFIED BY timesten;
GRANT CREATE SESSION, CREATE ANY TABLE to tthr;

这里需要指出的是对于Cache管理用户的授权,之所以赋予DROP ANY TABLE是为了cacheuser可以DROP CACHE GROUP,因为DROP CACHE GROUP需要删除相关的cache table
之后,还需要根据Cache Group的类型对Cache管理员授权,对于Read Only Cache Group,需要有对标的SELECT权限;对于AWT Group,除SELECT外,还需要有UPDATE, DELETE, INSERT权限

在TimesTen数据库中设置cache administration用户名和口令

其实就是在TT中缓存了访问Oracle的用户名和口令,下面这段话说明为何需要设置此用户名和口令

You must set the cache administration user name and password in the TimesTen database before any cache grid or cache group operation can be issued with the ttCacheUidPwdSet built-in procedure. The cache agent connects to the Oracle database as this user to create and maintain Oracle Database objects that store information used to manage a cache grid and enforce predefined behaviors of particular cache group types. In addition, both the cache and replication agents connect to the Oracle database with the credentials set with the ttCacheUidPwdSet built-in procedure to manage Oracle database operations.

并非所有的操作都会使用ttCacheUidPwdSet 设置的用户名和口令,一些操作如passthrough会使用连接属性中的OraclePwd属性

When you connect to the TimesTen database to work with AWT or read-only cache groups, TimesTen uses the credentials set with the ttCacheUidPwdSet built-in procedure when connecting to the Oracle database on behalf of these cache groups.

When you connect to the TimesTen database to work with SWT or user managed cache groups or passthrough operations, TimesTen connects to the Oracle database using the current user’s credentials as the user name and the OraclePwd connection attribute as the Oracle password. Thus, the correct user name and Oracle database password that should be used for connecting to the Oracle database must be set correctly in the connection string or with the connection attributes.

$ ttAdmin -cacheUidPwdSet -cacheUid cacheadm -cachePwd oracle cachedb1_1122
$ ttAdmin -cacheUidGet cachedb1_1122
Cache User Id                   : CACHEADM或者
$ ttIsql "DSN=cachedb1_1122;UID=cacheadm;PWD=timesten"
Command> call ttCacheUidPwdSet('cacheadm','oracle');
Command> call ttCacheUidGet();
< CACHEADM >

最后,创建一个cache grid,这时一个固定套路,接下来皆可以创建缓存组了

$ ttisql -v1 -connstr "dsn=cachedb1_1122;uid=cacheadm;pwd=timesten;oraclepwd=oracle"
Command> call ttGridCreate ('samplegrid');
Command> call ttGridInfo;
< SAMPLEGRID, CACHEADM, Linux Intel x86, 32-bit, 11, 2, 2 >
Command> call ttGridNameSet ('samplegrid');
Command> call ttGridNameGet;
< SAMPLEGRID >

参考

  • Oracle® TimesTen Application-Tier Database Cache User’s Guide 11g Release 2 (11.2.2)| 2 Getting Started
  • Oracle® TimesTen Application-Tier Database Cache User’s Guide 11g Release 2 (11.2.2)| 3 Setting Up a Caching Infrastructure

这篇关于TimesTen 应用层数据库缓存学习:3. 环境准备 - DB 12cR1版本的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/677717

相关文章

MySQL分表自动化创建的实现方案

《MySQL分表自动化创建的实现方案》在数据库应用场景中,随着数据量的不断增长,单表存储数据可能会面临性能瓶颈,例如查询、插入、更新等操作的效率会逐渐降低,分表是一种有效的优化策略,它将数据分散存储在... 目录一、项目目的二、实现过程(一)mysql 事件调度器结合存储过程方式1. 开启事件调度器2. 创

SQL Server使用SELECT INTO实现表备份的代码示例

《SQLServer使用SELECTINTO实现表备份的代码示例》在数据库管理过程中,有时我们需要对表进行备份,以防数据丢失或修改错误,在SQLServer中,可以使用SELECTINT... 在数据库管理过程中,有时我们需要对表进行备份,以防数据丢失或修改错误。在 SQL Server 中,可以使用 SE

golang1.23版本之前 Timer Reset方法无法正确使用

《golang1.23版本之前TimerReset方法无法正确使用》在Go1.23之前,使用`time.Reset`函数时需要先调用`Stop`并明确从timer的channel中抽取出东西,以避... 目录golang1.23 之前 Reset ​到底有什么问题golang1.23 之前到底应该如何正确的

mysql外键创建不成功/失效如何处理

《mysql外键创建不成功/失效如何处理》文章介绍了在MySQL5.5.40版本中,创建带有外键约束的`stu`和`grade`表时遇到的问题,发现`grade`表的`id`字段没有随着`studen... 当前mysql版本:SELECT VERSION();结果为:5.5.40。在复习mysql外键约

SQL注入漏洞扫描之sqlmap详解

《SQL注入漏洞扫描之sqlmap详解》SQLMap是一款自动执行SQL注入的审计工具,支持多种SQL注入技术,包括布尔型盲注、时间型盲注、报错型注入、联合查询注入和堆叠查询注入... 目录what支持类型how---less-1为例1.检测网站是否存在sql注入漏洞的注入点2.列举可用数据库3.列举数据库

数据库oracle用户密码过期查询及解决方案

《数据库oracle用户密码过期查询及解决方案》:本文主要介绍如何处理ORACLE数据库用户密码过期和修改密码期限的问题,包括创建用户、赋予权限、修改密码、解锁用户和设置密码期限,文中通过代码介绍... 目录前言一、创建用户、赋予权限、修改密码、解锁用户和设置期限二、查询用户密码期限和过期后的修改1.查询用

Mysql虚拟列的使用场景

《Mysql虚拟列的使用场景》MySQL虚拟列是一种在查询时动态生成的特殊列,它不占用存储空间,可以提高查询效率和数据处理便利性,本文给大家介绍Mysql虚拟列的相关知识,感兴趣的朋友一起看看吧... 目录1. 介绍mysql虚拟列1.1 定义和作用1.2 虚拟列与普通列的区别2. MySQL虚拟列的类型2

mysql数据库分区的使用

《mysql数据库分区的使用》MySQL分区技术通过将大表分割成多个较小片段,提高查询性能、管理效率和数据存储效率,本文就来介绍一下mysql数据库分区的使用,感兴趣的可以了解一下... 目录【一】分区的基本概念【1】物理存储与逻辑分割【2】查询性能提升【3】数据管理与维护【4】扩展性与并行处理【二】分区的

MySQL中时区参数time_zone解读

《MySQL中时区参数time_zone解读》MySQL时区参数time_zone用于控制系统函数和字段的DEFAULTCURRENT_TIMESTAMP属性,修改时区可能会影响timestamp类型... 目录前言1.时区参数影响2.如何设置3.字段类型选择总结前言mysql 时区参数 time_zon

Python MySQL如何通过Binlog获取变更记录恢复数据

《PythonMySQL如何通过Binlog获取变更记录恢复数据》本文介绍了如何使用Python和pymysqlreplication库通过MySQL的二进制日志(Binlog)获取数据库的变更记录... 目录python mysql通过Binlog获取变更记录恢复数据1.安装pymysqlreplicat