實戰:oracletimesten11.2.2.7.0oncentos6.5
入門級別，但是步驟比較細致，沒有理論，可以當做入門手冊.
************************************************************************
1.新建用戶
************************************************************************
-----1.create os user and group
groupadd ttadmin
groupadd timesten
mkdir /etc/TimesTen
mkdir -p /app/timesten
chgrp -R ttadmin /etc/TimesTen
chgrp -R ttadmin /app/timesten
chmod 770 /etc/TimesTen/
chmod 770 /app/timesten
chmod 660 /etc/TimesTen/*
useradd -d /home/ocpyang -g ttadmin -G timesten,dba ocpyang
passwd ocpyang
ocpyang
usermod -a -G timesten,oinstall,dba ocpyang
************************************************************************
2.Linux平台安裝准備工作
************************************************************************
-------------1.OS kernel parameter
---1.1 shared memorary config
kernel.shmmax=68719476736
kernel.shmall=16777216
/****config 64g memorary
64 x 1024 x 1024 x 1024 = 68719476736 and 68719476736 / 4096 = 16777216
****/
/sbin/sysctl -p
---1.2HugePages config
echo 32 > /proc/sys/vm/nr_hugepages
vi /etc/sysctl.conf
#config timesten instance 16g and huge page size is 2M.
vm.nr_hugepages = 8192
#ttadmin group id
vm.hugetlb_shm_group = 503
/sbin/sysctl -p
---1.3 Semaphores config
#vi /etc/sysctl.conf
#/sbin/sysctl -a | grep sem
kernel.sem = 400 32000 100 128
/sbin/sysctl -p
---1.4 REP和IMDB Cache
1).配置網絡參數
針對復制，TCP發送和接收buffers應該增加到4MB，在/etc/sysctl.conf文件加入以下行：
#vi /etc/sysctl.conf
net.ipv4.tcp_rmem=4096 4194304 4194304
net.ipv4.tcp_wmem=98304 4194304 4194304
net.core.rmem_default=65535
net.core.wmem_default=65535
net.core.rmem_max=4194304
net.core.wmem_max=4194304
net.ipv4.tcp_window_scaling=1
2).配置網絡參數
針對IMDB Cache，TCP發送和接收buffers應該增加得更大，修改/etc/sysctl.conf文件添加以下行：
#vi /etc/sysctl.conf
net.ipv4.tcp_rmem=4096 4194304 4194304
net.ipv4.tcp_wmem=98304 4194304 4194304
net.core.rmem_default=262144
net.core.wmem_default=262144
net.core.rmem_max=4194304
net.core.wmem_max=4194304
net.ipv4.tcp_window_scaling=1
net.ipv4.ip_local_port_range=1024 65000
將上訴兩項合並修改為:
#####REP和IMDB Cache
net.ipv4.tcp_rmem=4096 4194304 4194304
net.ipv4.tcp_wmem=98304 4194304 4194304
net.core.rmem_default=262144
net.core.wmem_default=262144
net.core.rmem_max=4194304
net.core.wmem_max=4194304
net.ipv4.tcp_window_scaling=1
net.ipv4.ip_local_port_range=1024 65000
2).使配置生效
重啟或者運行以下命令：
#/sbin/sysctl -p
************************************************************************
3.soft install
************************************************************************
---3.1安裝jdk
java -version
rpm -qa | grep java
$ whoami
ocpyang
---3.2 安裝timesten
cd /soft/linux8664/
$ ./setup.sh
NOTE: Each TimesTen installation is identified by a unique instance name.
The instance name must be a non-null alphanumeric string, not longer
than 255 characters.
#步驟1：輸入安裝實例名稱
Please choose an instance name for this installation? [ tt1122 ] ttwind #輸入自定義的名字
Instance name will be 'ttwind'.
Is this correct? [ yes ] yes
#步驟2：安裝組件
Of the three components:
[1] Client/Server and Data Manager
[2] Data Manager Only
[3] Client Only
Which would you like to install? [ 1 ] 1 #輸入1
#步驟3：安裝位置
Of the following options :
[1] /home/ocpyang
[2] /soft
[3] Specify a location
[q] Quit the installation
Where would you like to install the ttwind instance of TimesTen? [ 1 ] 3 #輸入3 自定義一個安裝目錄
Please specify a directory to install TimesTen? [ /home/ocpyang ] /app/timesten #輸入自定義一個安裝目錄
Where would you like to create the daemon home directory? [ /app/timesten/TimesTen/ttwind/info ]
The daemon logs will be located in /app/timesten/TimesTen/ttwind/info
Would you like to specify a different location for the daemon logs? [ no ]
Uncompressing ...
NOTE: If you are configuring TimesTen for use with Oracle Clusterware, the
daemon port number must be the same across all TimesTen installations
managed within the same Oracle Clusterware cluster.
NOTE: All installations that replicate to each other must use the same daemon
port number that is set at installation time. The daemon port number can
be verified by running 'ttVersion'.
#步驟4：輸入自定義的主進程端口號
The default port number is 53396.
Do you want to use the default port number for the TimesTen daemon? [ yes ] no #輸入no 自定義端口
Please enter a unique port number for the TimesTen daemon (<CR>=list)? [ ] 53355 #輸入自定義端口
NOTE: For security, we recommend that you restrict access to the
TimesTen installation to members of a single OS group. Only members of
that OS group will be allowed to perform direct mode connections to
TimesTen, and only members of that OS group will be allowed to perform
operations that access TimesTen data stores, TimesTen files and shared
memory. The OS group defaults to the primary group of the instance
administrator. You can default to this group, choose another OS group
or you can make this instance world-accessible. If you choose to make
this instance world-accessible, all database files and shared memory
are readable and writable by all users.
#步驟5:受限會話和PL/SQL功能默認即可
Restrict access to the the TimesTen installation to the group 'ttadmin'? [ yes ]
NOTE: Enabling PL/SQL will increase the size of some TimesTen libraries.
Would you like to enable PL/SQL for this instance? [ yes ] yes
#步驟6：安裝的時候指定TNS_ADMIN環境變量
------------------------------------------------------------------------------
切記:最後的 tnsnames.ora需要放入這個目錄一份，否則報錯
Command> create readonly cache group cachtest
> from scott.t2
> (
> sid int not null primary key,
> sname varchar2(10)
> );
Warning 5923: Cache agent has deferred the create/alter cache group request because it is not connected to Oracle yet
或
Command> load cache group cachtest commit every 10 rows;
5056: The cache operation fails: error_type=<Oracle Error>, error_code=<12541>, error_message: ORA-12541: TNS:no listener
The command failed.
------------------------------------------------------------------------------
In order to use the 'In-Memory Database Cache' feature in any databases
created within this installation, you must set a value for the TNS_ADMIN
environment variable. It can be left blank, and a value can be supplied later
using <install_dir>/bin/ttModInstall.
Please enter a value for TNS_ADMIN (s=skip)? [ ]/app/timesten/TimesTen/ttwind/ #輸入/app/timesten/TimesTen/ttwind/n
TNS_ADMIN will be set to /app/timesten/TimesTen
You can change TNS_ADMIN later by running <install_dir>/bin/ttmodinstall.
NOTE: It appears that you are running version 4 or higher of the g++
compiler. TimesTen ships with multiple sets of client libraries and server
binaries : one built for compatibility with g++ 3.4.6 and one with
g++ 4.1.0. The installer has created links to the 4.1.0 library in the
<install_dir>/lib directory and to the 4.1.0 server binary in the
<install_dir>/bin directory. If you want to use a different compiler,
please modify the links to point to the desired library and server binary.
Installing server components ...
#步驟7:TimesTen服務器端口號
Installing server components ...
What is the TCP/IP port number that you want the TimesTen Server to listen on? [ 53356 ] 53356 #主進程端口號加1
Do you want to install the Quick Start Sample Programs and the TimesTen Documentation? [ no ]
Would you like to install the documentation (without the Quick Start Sample Programs)? [ yes ]
Where would you like to create the doc directory? [ /app/timesten/TimesTen/ttwind/doc ]
The TimesTen documentation has been installed in /app/timesten/TimesTen/ttwind/doc.
Installing client components ...
Would you like to use TimesTen Replication with Oracle Clusterware? [ no ]
NOTE: The TimesTen daemon startup/shutdown scripts have not been installed.
Run the 'setuproot' script :
cd /app/timesten/TimesTen/ttwind/bin
./setuproot -install
This will move the TimesTen startup script into its appropriate location.
The startup script is currently located here :
'/app/timesten/TimesTen/ttwind/startup/tt_ttwind'.
The 11.2.2.7 Release Notes are located here :
'/app/timesten/TimesTen/ttwind/README.html'
Starting the daemon ...
TimesTen Daemon startup OK.
End of TimesTen installation.
---3.3設置用戶變量
vi ~/.bash_profile
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
export TNS_ADMIN=$ORACLE_HOME/network/admin
export TT_HOME=/app/timesten/TimesTen/ttwind/
export PATH=$PATH:$TT_HOME/bin:$ORACLE_HOME/bin
LD_LIBRARY_PATH=/app/timesten/TimesTen/ttwind/lib
export LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:/lib:/usr/lib:/usr/local/lib:$ORACLE_HOME/lib
source ~/.bash_profile
---3.4 拷貝tnsnames文件
cp /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora /app/timesten/TimesTen/ttwind/
************************************************************************
4.新建緩存實例
************************************************************************
---------4.1 Oracle數據庫創建相關用戶和權限
--步驟1：新建表空間存儲timesten的數據字典表
CREATE TABLESPACE ttspace DATAFILE
'/u01/app/oracle/oradata/orcl/ttspace01.dbf'
SIZE 10M autoextend on next 10m maxsize 30g;
@ $TT_HOME/oraclescripts/initCacheGlobalSchema.sql "TTSPACE"
alter user timesten identified by timesten;
--步驟2：新建數據庫同步用戶
create user cacheuser identified by cacheuser
DEFAULT TABLESPACE TTSPACE
QUOTA UNLIMITED ON TTSPACE;
grant connect,resource to cacheuser;
@ $TT_HOME/oraclescripts/grantCacheAdminPrivileges "cacheuser"
--步驟3：授權表給cacheuser用戶
conn scott/tiger
create table t1
(
sid int not null primary key,
sname varchar2(10)
);
insert into t1 values(101,'wind');
insert into t1 values(102,'snow');
grant select,insert,update,delete on scott.t1 to cacheuser;
grant all on scott.t1 to cacheuser;
-------4.2 timesten端創建相關用戶和權限
----步驟0：修改DSN並設置需要的數據目錄文件
#vi /app/timesten/TimesTen/ttwind/info/sys.odbc.ini
[ttwind]
Driver=/app/timesten/TimesTen/ttwind/lib/libtten.so
DataStore=/app/timesten/TimesTen/ttwind/info/datastore/ttwind
PermSize=40
TempSize=32
PLSQL=1
DatabaseCharacterSet=ZHS16GBK
ConnectionCharacterSet=ZHS16GBK
OracleNetServiceName=orcl
mkdir -p /app/timesten/TimesTen/ttwind/info/datastore/ttwind
chmod -R 770 /app/timesten/TimesTen/ttwind/info/datastore/ttwind
ttIsql ttwind
--步驟1：cache管理用戶
CREATE USER cacheuser IDENTIFIED BY cacheuser ;
GRANT CREATE SESSION, CACHE_MANAGER, CREATE ANY TABLE,DROP ANY TABLE TO cacheuser;
--步驟2：cache對應用戶
create user scott identified by tiger;
grant create session ,adminto scott;
--步驟3：將oracle cache管理用戶與timesten關聯,設置oracle連接的用戶名和密碼,
cache agent將會使用這個用戶名和密碼去oracle中讀取
call ttcacheuidpwdset ('cacheuser','cacheuser');
--步驟4：創建cache grid
$ ttIsql "DSN=ttwind;UID=cacheuser;PWD=cacheuser;OraclePWD=cacheuser"
Command> call ttGridCreate('myGrid');
--步驟5：將cache database 和cache grid關聯即把當前的grid設為剛創建好的myGrid
Command> call ttGridNameSet('myGrid');
--步驟6：測試
autocommit 0;
set passthrough 2;
select * from v$version;
select table_name from user_tables where table_name like '%MYGRID%';
set passthrough 0;
--步驟7：測試只讀緩存集合
$ ttIsql "DSN=ttwind;UID=cacheuser;PWD=cacheuser;OraclePWD=cacheuser"
ttIsql "DSN=ttwind;UID=cacheuser;PWD=cacheuser;OraclePWD=cacheuser;OracleNetServiceName=orcl"
call ttCacheStart;
call ttCacheStop;
drop Cache Group Cachtest;
create readonly cache group cachtest
autorefresh interval 5 seconds
mode incremental
from scott.t1
(
sid int not null primary key,
sname varchar2(10)
);
查看用戶下所有的用戶表
sqltables;
查看用戶下所有Cache
cachegroups;
--刪除cache group
drop Cache Group Cachtest;
--加載
load cache group cachtest commit every 10 rows;
************************************************************************
5.Timesten 快捷加載oracle數據庫中的表和數據
************************************************************************
ttisql "DSN=ttwind;UID=cacheuser;PWD=cacheuser;"
ttIsql "DSN=ttwind;UID=cacheuser;PWD=cacheuser;OraclePWD=cacheuser;OracleNetServiceName=orcl"
ttIsql "DSN=ttwind;UID=cacheuser;PWD=cacheuser;OraclePWD=cacheuser; OracleId=ORCL"
autocommit 0;
set passthrough 2;
--生成表結構
call ttTableSchemaFromOraQueryGet('scott','emp','SELECT * FROM scott.emp');
--導入數據
CALL ttLoadFromOracle ('scott','emp','SELECT * FROM scott.emp');
CREATE TABLE "SCOTT"."EMP" (
"EMPNO" number(4,0) NOT NULL,
"ENAME" varchar2(10 byte),
"JOB" varchar2(9 byte),
"MGR" number(4,0),
"HIREDATE" date,
"SAL" number(7,2),
"COMM" number(7,2),
"DEPTNO" number(2,0)
) >
create readonly cache group cacheuser.emp
autorefresh interval 5 seconds
mode incremental
from scott.t1
(
sid int not null primary key,
sname varchar2(10)
);
