Timesten
Views
Elitecore | CSM Dept
[edit] Oracle Timesten Installation in Redhat Linux 64 bit machine
Timesten Installation Prerequisites
groupadd ttadmin mkdir /etc/TimesTen
[root@aaarac1 ~]# chown root:ttadmin /etc/TimesTen/ [root@aaarac1 ~]# ls -la /etc/TimesTen/ total 20 drwxr-xr-x 2 root ttadmin 4096 Mar 20 19:10 . drwxr-xr-x 128 root root 12288 Mar 20 19:10 .
useradd -g ttadmin timesten passwd timesten
Timesten Installation Steps
timesten112220.linux8664.tar.gz gunzip timesten112220.linux8664.tar.gz tar -xvf timesten112220.linux8664
[timesten@aaarac1 ~]$ ls -ltr total 316040 drwxr-xr-x 5 timesten ttadmin 4096 Dec 23 21:56 linux8664 -rwxr-xr-x 1 timesten ttadmin 323297280 Mar 20 09:13 timesten112220.linux8664 [timesten@aaarac1 linux8664]$ sh 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. Please choose an instance name for this installation? [ tt1122 ] Instance name will be 'tt1122'. Is this correct? [ yes ] Of the three components: [1] Client/Server and Data Manager [2] Data Manager Only [3] Client Only Which would you like to install? [ 1 ] Of the following options : [1] /home/timesten [2] /home/timesten [3] Specify a location [q] Quit the installation Where would you like to install the tt1122 instance of TimesTen? [ 1 ] Where would you like to create the daemon home directory? [ /home/timesten/TimesTen/tt1122/info ] The daemon logs will be located in /home/timesten/TimesTen/tt1122/info Would you like to specify a different location for the daemon logs? [ no ] Installing into /home/timesten/TimesTen/tt1122 ... 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'. The default port number is 53396. Do you want to use the default port number for the TimesTen daemon? [ yes ] The daemon will run on the default port number (53396). 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. 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 ] 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)? [ ] /u01/app/oracle/product/11.2.0/db_1/network/admin TNS_ADMIN will be set to /u01/app/oracle/product/11.2.0/db_1/network/admin 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 ... What is the TCP/IP port number that you want the TimesTen Server to listen on? [ 53397 ] Do you want to install QuickStart and the TimesTen Documentation? [ no ] Would you like to install the documentation (without QuickStart)? [ yes ] Where would you like to create the doc directory (s=skip)? [ /home/timesten/TimesTen/tt1122/doc ] s 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 /home/timesten/TimesTen/tt1122/bin ./setuproot -install This will move the TimesTen startup script into its appropriate location. The startup script is currently located here : '/home/timesten/TimesTen/tt1122/startup/tt_tt1122'. The documentation was not installed. To manually install the documentation, run the command 'setup.sh -installDoc' The 11.2.2.2 Release Notes are located here : '/home/timesten/TimesTen/tt1122/README.html' Starting the daemon ... TimesTen Daemon startup OK. End of TimesTen installation.
[timesten@aaarac1 bin]$ su - root Password:
[root@aaarac1 ~]# cd /home/timesten/TimesTen/tt1122/bin/ [root@aaarac1 bin]# ./setuproot -install Would you like to install the TimesTen daemon startup scripts into /etc/init.d? [ yes ] Copying /home/timesten/TimesTen/tt1122/startup/tt_tt1122 to /etc/init.d Successfully installed the following scripts : /etc/init.d/tt_tt1122 /etc/rc.d/rc0.d/K45tt_tt1122 /etc/rc.d/rc1.d/K45tt_tt1122 /etc/rc.d/rc2.d/S90tt_tt1122 /etc/rc.d/rc3.d/S90tt_tt1122 /etc/rc.d/rc5.d/S90tt_tt1122 /etc/rc.d/rc6.d/K45tt_tt1122
su – timesten
cd /home/timesten/TimesTen/tt1122/bin
[timesten@aaarac1 bin]$ pwd /home/timesten/TimesTen/tt1122/bin . ttenv.sh vi .bash_profile . /home/timesten/TimesTen/tt1122/bin/ttenv.sh
[timesten@aaarac1 bin]$ ttversion TimesTen Release 11.2.2.2.0 (64 bit Linux/x86_64) (tt1122:53396) 2011-12-23T09:26:28Z Instance admin: timesten Instance home directory: /home/timesten/TimesTen/tt1122 Group owner: ttadmin Daemon home directory: /home/timesten/TimesTen/tt1122/info PL/SQL enabled.
[timesten@aaarac1 bin]$ ttStatus
TimesTen status report as of Tue Mar 20 19:46:07 2012 Daemon pid 29033 port 53396 instance tt1122 TimesTen server pid 29042 started on port 53397 ------------------------------------------------------------------------ Accessible by group ttadmin End of report
[edit] How to Create the Timesten Database in Redhat Linux 64bit
/home/timesten/TimesTen/tt1122/info vi vi sys.odbc.ini
[ODBC Data Sources] my_ttdb=TimesTen 11.2.2 Driver
# New data source definitions can be added below. [my_ttdb] Driver=/home/timesten/TimesTen/tt1122/lib/libtten.so DataStore=/home/timesten/u02/ttdata/datastores/my_ttdb LogDir=/home/timesten/u03/ttdata/logs PermSize=40 TempSize=32 DatabaseCharacterSet=AL32UTF8
–Create a database user and grant him
Command> create user john identified by john; Command>grant create session,create table to john; Command> connect "dsn=my_ttdb;uid=john";
#sample create table:
create table mytable( id NUMBER NOT NULL PRIMARY KEY, title VARCHAR2(300) NOT NULL, code VARCHAR2(50), count NUMBER(3), last_updated DATE);
#same create index:
create index mytable_idx on mytable(code);
tables; --List the tables
indexes; --List the Indexes
[edit] How to create an Oracle Timesten 11.2.2 Cache database on linux
[edit] Oracle Timesten In-Memory Cashe (IMDB) Configuration in Oracle Database and Timesten Database
—Oracle Database
Step-1 —Create a Cache Administrator User.
CREATE TABLESPACE cachetblsp DATAFILE '$ORACLE_BASE/oradata/$ORACLE_SID/datattuser.dbf' SIZE 100M AUTOEXTEND ON; @initCacheGlobalSchema "cachetblsp"
CREATE USER cacheuser IDENTIFIED BY oracle DEFAULT TABLESPACE cachetblsp QUOTA UNLIMITED ON cachetblsp;
Step-2 —Create Schema User oratt
CREATE TABLESPACE oratttblsp DATAFILE '$ORACLE_BASE/oradata/$ORACLE_SID/orattuser.dbf' SIZE 100M AUTOEXTEND ON; CREATE USER oratt IDENTIFIED BY oracle DEFAULT TABLESPACE oratttblsp QUOTA UNLIMITED ON oratttblsp; GRANT CREATE SESSION,RESOURCE TO oratt; @grantCacheAdminPrivileges "cacheuser"
—Create a DSN for the timesten Database
SELECT value FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER='NLS_CHARACTERSET';
–ON Unix
Timesten_install_dir/info/sys.odbc.ini file
[ODBC Data Sources] my_cachedb=TimesTen 11.2.2 Driver elitecachedb=TimesTen 11.2.2 Driver
[elitecachedb] Driver=/home/timesten/TimesTen/tt1122/lib/libtten.so DataStore=/home/timesten/u02/ttdata/datastores/elitecachedb LogDir=/home/timesten/u03/ttdata/logs PermSize=40 TempSize=32 DatabaseCharacterSet=WE8MSWIN1252 OracleNetServiceName=aaadb1
ttisql DSN=elitecachedb
–Cache Manager User
CREATE USER cacheuser IDENTIFIED BY timesten; GRANT CREATE SESSION,CACHE_MANAGER, CREATE ANY TABLE TO cacheuser;
–Cache Table User (which is the same name as the Oracle Schema user that was created earlier.)
CREATE USER oratt IDENTIFIED BY timesten;
Set the Cache Administrator username and password in the timesten database.
1.)(built in procedure to set the oracle cache administration user name and password)
% ttIsql "DSN=elitecachedb;UID=cacheuser;PWD=timesten;OraclePWD=oracle"
command> call ttCacheUidPwdSet('cacheuser','oracle');
2.)creating a cache grid
command> call ttGridCreate('myGrid');
3.)call the ttGirdNameSet
command> call ttGridNameSet('myGrid');
4.) Create cache groups
4.1 Create the Oracle table to be cached.
sqlplus oratt/oracle
CREATE TABLE TBL_CORE_SESS ( CID NUMBER , GATEWAY_URL VARCHAR2(50) , SESSION_ID VARCHAR2(100) NOT NULL, SUBSCRIPTION_ID VARCHAR2(100) , IP_CAN_TYPE VARCHAR2(50) , RAT_TYPE VARCHAR2(50) , PDN_CONNECTION_ID VARCHAR2(50) , FRAMED_IP_ADDRESS VARCHAR2(50) , LOCATION VARCHAR2(50) , TIMEZONE VARCHAR2(50) , CSG_INFORMATION_REPORTING VARCHAR2(50) , CSG_ID VARCHAR2(50) , CSG_ACCESS_MODE VARCHAR2(50) , CSG_MEMBERSHIP_INDICATION VARCHAR2(50) , USER_EQUIPMENT VARCHAR2(50) , SERVICE_IDENTIFIER VARCHAR2(50) , CREATE_TIME TIMESTAMP(6), LAST_UPDATE_TIME TIMESTAMP(6), primary key (CID));
CREATE TABLE TBL_BEARER_SESS ( BID NUMBER , CID NUMBER , BEARER_ID VARCHAR2(50) , PACKET_FILTER_INFORMATION VARCHAR2(50) , PACKET_FILTER_CONTENT VARCHAR2(50) , EVENT_TRIGGER VARCHAR2(50) , CREATE_TIME TIMESTAMP(6), LAST_UPDATE_TIME TIMESTAMP(6), primary key (BID), foreign key (CID) references ORATT.TBL_CORE_SESS (CID) on delete cascade);
–Grant Access to CACHEUSER
GRANT SELECT,INSERT,UPDATE,DELETE ON TBL_CORE_SESS TO CACHEUSER; GRANT SELECT,INSERT,UPDATE,DELETE ON TBL_BEARER_SESS TO CACHEUSER;
4.2 Create the CacheGroup
% ttIsql “DSN=elitecachedb;UID=cacheuser;PWD=timesten;OraclePWD=oracle”
command>
create dynamic asynchronous writethrough cache group ORATT.NM_CACHE from ORATT.TBL_CORE_SESS ( CID NUMBER , GATEWAY_URL VARCHAR2(50) , SESSION_ID VARCHAR2(100) NOT NULL, SUBSCRIPTION_ID VARCHAR2(100) , IP_CAN_TYPE VARCHAR2(50) , RAT_TYPE VARCHAR2(50) , PDN_CONNECTION_ID VARCHAR2(50) , FRAMED_IP_ADDRESS VARCHAR2(50) , LOCATION VARCHAR2(50) , TIMEZONE VARCHAR2(50) , CSG_INFORMATION_REPORTING VARCHAR2(50) , CSG_ID VARCHAR2(50) , CSG_ACCESS_MODE VARCHAR2(50) , CSG_MEMBERSHIP_INDICATION VARCHAR2(50) , USER_EQUIPMENT VARCHAR2(50) , SERVICE_IDENTIFIER VARCHAR2(50) , CREATE_TIME TIMESTAMP(6), LAST_UPDATE_TIME TIMESTAMP(6), primary key (CID)) AGING LRU ON, ORATT.TBL_BEARER_SESS ( BID NUMBER , CID NUMBER , BEARER_ID VARCHAR2(50) , PACKET_FILTER_INFORMATION VARCHAR2(50) , PACKET_FILTER_CONTENT VARCHAR2(50) , EVENT_TRIGGER VARCHAR2(50) , CREATE_TIME TIMESTAMP(6), LAST_UPDATE_TIME TIMESTAMP(6), primary key (BID), foreign key (CID) references ORATT.TBL_CORE_SESS (CID) on delete cascade);
command> call ttCacheStart;
5.) Start the replication agent for the AWT cache group
command> call ttRepStart
ttisql “DSN=elitecachedb;uid=oratt;pwd=timesten;OraclePwd=oracle”
[edit] Changed_Data_Store_Size(Resize DB)
[timesten@aaarac1 info]$ ttAdmin DSN=elitecachedb RAM Residence Policy : inUse Replication Agent Policy : manual Replication Manually Started : True Cache Agent Policy : manual Cache Agent Manually Started : True
[timesten@aaarac1 info]$ ttAdmin -ramPolicy manual DSN=elitecachedb RAM Residence Policy : manual Manually Loaded In RAM : True Replication Agent Policy : manual Replication Manually Started : True Cache Agent Policy : manual Cache Agent Manually Started : True
—ttAdmin -ramUnload DSN=elitecachedb
[timesten@aaarac1 info]$ ttAdmin -ramUnload DSN=elitecachedb RAM Residence Policy : manual Manually Loaded In RAM : False Replication Agent Policy : manual Replication Manually Started : False Cache Agent Policy : manual Cache Agent Manually Started : False
—stop Cache
call ttCacheStop;
–stop Replication
call ttRepStop;
–New Size of Perm/Temp in elitecachedb
[elitecachedb] Driver=/home/timesten/TimesTen/tt1122/lib/libtten.so DataStore=/home/timesten/u02/ttdata/datastores/elitecachedb LogDir=/home/timesten/u03/ttdata/logs PermSize=512 TempSize=256 DatabaseCharacterSet=WE8MSWIN1252 OracleNetServiceName=aaadb1
[timesten@aaarac1 info]$ ttAdmin -ramload DSN=elitecachedb RAM Residence Policy : manual Manually Loaded In RAM : True Replication Agent Policy : manual Replication Manually Started : False Cache Agent Policy : manual Cache Agent Manually Started : False
–Check the size of the Database
ttisql “DSN=elitecachedb;uid=oratt;pwd=timesten;OraclePwd=oracle”
Command> dssize; PERM_ALLOCATED_SIZE: 524288 PERM_IN_USE_SIZE: 9337 PERM_IN_USE_HIGH_WATER: 9337 TEMP_ALLOCATED_SIZE: 262144 TEMP_IN_USE_SIZE: 7817 TEMP_IN_USE_HIGH_WATER: 7880
[edit] Timesten Administrator Commands
ttisql DSN=elitecachedb ttisql "DSN=elitecachedb;uid=oratt;pwd=timesten;OraclePwd=oracle"
ttstatus; --Verify the TimesTen Daemon
ttdaemonadmin -start ttdaemonadmin -stop
dssize; --Check the size of the Database
host ttstatus; --Database Connection status
Manually Stop/Start Timesten Server su - root /etc/init.d/tt_tt1122 stop /etc/init.d/tt_tt1122 start
monitor;
Command> monitor; Example TIME_OF_1ST_CONNECT: Mon Mar 26 19:59:44 2012 DS_CONNECTS: 84 DS_DISCONNECTS: 55 DS_CHECKPOINTS: 34 DS_CHECKPOINTS_FUZZY: 34 DS_COMPACTS: 0 PERM_ALLOCATED_SIZE: 524288 PERM_IN_USE_SIZE: 9557 PERM_IN_USE_HIGH_WATER: 523793 TEMP_ALLOCATED_SIZE: 262144 TEMP_IN_USE_SIZE: 73004 TEMP_IN_USE_HIGH_WATER: 81293 SYS18: 0 TPL_FETCHES: 0 TPL_EXECS: 48 CACHE_HITS: 2758 PASSTHROUGH_COUNT: 1352 XACT_BEGINS: 280293 XACT_COMMITS: 277805 XACT_D_COMMITS: 0 XACT_ROLLBACKS: 1812 LOG_FORCES: 8813 DEADLOCKS: 0 LOCK_TIMEOUTS: 0 LOCK_GRANTS_IMMED: 3261038 LOCK_GRANTS_WAIT: 27 SYS19: 0 CMD_PREPARES: 206 CMD_REPREPARES: 0 CMD_TEMP_INDEXES: 0 LAST_LOG_FILE: 129 REPHOLD_LOG_FILE: 129 REPHOLD_LOG_OFF: 23898376 REP_XACT_COUNT: 6709 REP_CONFLICT_COUNT: 0 REP_PEER_CONNECTIONS: 1 REP_PEER_RETRIES: 0 FIRST_LOG_FILE: 129 LOG_BYTES_TO_LOG_BUFFER: 985922976 LOG_FS_READS: 321685 LOG_FS_WRITES: 9567 LOG_BUFFER_WAITS: 4 CHECKPOINT_BYTES_WRITTEN: 971144 CURSOR_OPENS: 7253 CURSOR_CLOSES: 7253 SYS3: 0 SYS4: 0 SYS5: 0 SYS6: 0 CHECKPOINT_BLOCKS_WRITTEN: 14802 CHECKPOINT_WRITES: 7915 REQUIRED_RECOVERY: 0 SYS11: 0 SYS12: 1 TYPE_MODE: 0 SYS13: 0 SYS14: 0 SYS15: 87914 SYS16: 0 SYS17: 0 SYS9:
ttRepAdmin -showstatus DSN=elitecachedb
Start/Stop replication daemon ttAdmin -repStart/-repStop DSN=elitecachedb
Start/Stop replication cache agent ttAdmin -cacheStart/-cacheStop DSN=elitecachedb
[edit] Oracle Timesten Client/Server Architecture
Basically Oracle Timesten use the TCP/IP Protocol for communicate with the Clients.
Where to find the Oracle Timesten Client Driver for Application?
==> Oracle Timesten Client Driver installed with the Oracle Timesten Client Driver Setup.
Basic FLOW for Client/Server Communication
Client application ===> TT Client ODBC Driver(Client DSN ==> LOGICAL SERVER NAME) || || Server DSN ===> TT Database.
Example
- 192.168.1.160 (Linux Redhat 32 bit )
- Configure the Server DSN on the Server Machine
- Server DSN Entry
cat sys.odbc.ini
[tt_aaatst] Driver=/u01/app/TimesTen/tt1121/lib/libtten.so DataStore=/u01/app/TimesTen/ttdata/datastores/tt_aaatst LogDir=/u01/app/TimesTen/ttdata/logs PermSize=1024 TempSize=512 DatabaseCharacterSet=AL32UTF8 OracleNetServiceName=orcl OraclePassword=sys123
- How to access the Local Timesten Database connection string
ttisql “DSN=tt_aaatst;UID=ELITETT;PWD=timesten;OraclePWD=oracle”
# Configure the Client Machine
192.168.8.94(Sun Solaris 64 bit Machine)
- Define the Logical Server Name for communication with the Server Machine
bash-3.00$ cat sys.ttconnect.ini [ttserver_logical] Description=TimesTen Server Network_Address=192.168.1.160 TCP_PORT=53385
- Define the Client DSN for Access the Remote Timesten Database
bash-3.00$ cat sys.odbc.ini [tt_aaatstCS_1121] TTC_SERVER=ttserver_logical TTC_SERVER_DSN=tt_aaatst
#How to access the Remote Timesten Database
–DB Connection String
ttisqlcs “DSN=tt_aaatstCS_1121;UID=ELITETT;PWD=timesten”
[edit] Oracle Timesten Setting a PassThrough Level
When an application issues statements on a
Timesten Connection, the statement can be
executed in the Timesten database or passed
through to the oracle database for execution.
You can set the PassThrough connection attribute to
define which statements are to be executed locally in
Timesten and which are to be redirected to Oracle
for execution
PassThrough 0;
PassThrough=0 is the default setting and specifies that all statements are to be executed in the Timesten database
PassThrough 1;
Set PassThrough=1 to specify that a statement that references a table that does not exist in the Timesten database is passed through to the Oracle database for execution
No DDL statements are passed through to the Oracle database
Example
ttisql "DSN=elitecachedb;uid=oratt;pwd=timesten;OraclePwd=oracle;PassThrough=1"
[edit] Managing Timesten Databases
Each timesten database is created when the first connection initiates. The database instance is freed only when all exiting connections have been disconnected. The configuration for the timesten database is contained within the first connection attributes.
Java Application interact with Timesten by loading the JDBC library. the timesten JDBC driver uses the ODBC driver to access Timesten databases Java Application || || JDBC Driver || || ODBC Driver || || TT Database Engine
Timesten includes the following ODBC drivers:
Timesten Data Manager driver ==> A Timesten ODBC driver for use with direct connect applications. Timesten Client driver ==> A Timesten Client ODBC driver for use with client/server applications.
[edit] Timesten Learning Resource/Setups/Documentations
ftp://eliteaaa@ftp.elitecore.com/Oracle_Timesten_Resources/ Download http://www.oracle.com/technetwork/products/timesten/downloads/index.html
Documentation http://docs.oracle.com/cd/E21901_01/welcome.html
[edit] Online Oracle Timesten Blogs
https://blogs.oracle.com/timesten/
[edit] Incompatibilities issue between Oracle Timesten and Oracle Database
Oracle ROWNUM Alternative in Oracle TimesTen
select first 10 * from t1 order by x1; /* select first 10 rows from t1 after sorting*/ update first 5 t1 set x1=x1+1; /* update first 5 row from t1 */
Reference ==> https://kr.forums.oracle.com/forums/thread.jspa?threadID=339788
Oracle Current_timestamp alternaive in Oracle Timesten
select tt_sysdate from dual;
select to_char(sysdate,'dd-mm-yyyy hh24:mi:ss.FF') as ts from dual;
Reference ==> https://forums.oracle.com/forums/thread.jspa?messageID=9425810
[edit] Oracle Timesten Cache Grid
A cache grid is a set of distributed TimesTen in-memory databases that work together to cache data from an Oracle database and guarantee cache coherence among the TimesTen databases.
A grid consists of one or more in-memory database grid members that collectively manage the application data using the relational data model.
The members of a grid cache data from a single Oracle database.
A grid member can be either a standalone TimesTen database or an active standby pair.
In a cache grid, cached data is dynamically distributed across multiple grid members without shared storage.
There are two categories of cache groups. A local cache group contains data that is local to it's cache database, the data is not shared by other members in a cache grid. A global cache group contains data that is globally shared by the other grid members, it ensures cache consistency across cache databases participating in the cache grid.
A TimesTen cache grid consists of one or more grid members; each grid member is backed by a TimesTen in-memory database. This section describes how to expand your cache grid by adding additional cache databases with the growth of the processing capacity. Though not required , a grid member's cache database typically runs in a different machine from other grid members.
Grid members can be added and removed on-line while the application is active.
Refer https://hiteshgondalia.wordpress.com/oracle-timesten/