====== [DOC] Timesten ======



Investigate:
  * SYS.SYSTEMSTATS
  * read committed or <del>serializable isolation</del>? Nosotros somos read committed
  * autocommit defaults on, but recommends off


====== Micro-howtos ======

===== Create database =====

==== Windows ====
  * open dsn admin
  * Create a new dsn
  * Select TimesTen Data manager
  * Select the Data & logs path
  * Configure some ops (if you want)
  * create dsn
  * Open cmd
  * run ttenv.bat
  * run ''"ttIsql InstanceName"''
  * Create a user:
<code sql>
CREATE USER <USERNAME> IDENTIFIED BY <PASSWORD>;
GRANT CREATE SESSION, CREATE TABLE TO <USERNAME> ;
</code>

Maybe you also must give that user

Connect as the user:
<code sql>
connect "dsn=<INSTANCENAME>;uid=<USERNAME>";
</code>

==== Linux ====
  * Login as the user that installed TimesTen
  * cd to the "info" directory":
<code>
cd /home/timesten/TimesTen/tt1122/info
</code>
  * edit ''sys.odbc.ini''
  * Define a new DSN under "[ODBC Data Sources]" section
<code>
[ODBC Data Sources]
test-ttdb=TimesTen 11.2.2 Driver
</code>
  * Define the settings of the new DSN under "new data source"
<code>
#####################################################################
#
# New data source definitions can be added below.
#
#####################################################################
[test-ttdb]
Driver=/home/timesten/TimesTen/tt1122/lib/libtten.so
DataStore=/home/ttdata/datastores/test-ttdb
LogDir=/home/ttdata/logs
PermSize=1024
TempSize=512
DatabaseCharacterSet=AL32UTF8
</code>
  * Create directories with permissions for the user:
<code>
mkdir -p /home/ttdata/datastores/test-ttdb /home/ttdata/logs
</code>
  * run ''"ttIsql InstanceName"''
  * Create a user:
<code sql>
CREATE USER <USERNAME> IDENTIFIED BY <PASSWORD>;
GRANT CREATE SESSION, CREATE TABLE TO <USERNAME> ;
</code>
Connect as the user:
<code sql>
connect "dsn=<INSTANCENAME>;uid=<USERNAME>";
</code>


==== create a cache grid ====
1st of all, in the OracleDB run the following scripts as sysdba:
<code sql>
@initCacheGlobalSchema.sql
@grantCacheAdminPrivileges.sql
</code>
Both are located on the TimesTen installation (//oraclescripts// folder).
This time I use the user named on the TT db creation.

Then create the grid on TimesTen:
<code>
call ttGirdCreate('GridName');
call ttGirdNameSet('GridName');
</code>


=====Steps to remove Cache groups and Cache Grid from TimesTen and Oracle (Doc ID 836887.1)=====

TimesTen side:
<code>
call ttGridDetach; 
call ttrepstop; 
DROP CACHE GROUP tester.writecache; 
DROP CACHE GROUP tester.dummy_cli; 
call ttGridDestroy('ttGrid'); 
call ttCacheStop;
</code>

Oracle Side:
<code>
DROP USER cacheadm CASCADE; 
DROP USER tester CASCADE; 
DROP USER timesten CASCADE; 
DROP ROLE tt_cache_admin_role; 
DROP TABLESPACE cachegrid_tbl INCLUDING CONTENTS AND DATAFILES; 
DROP TABLESPACE cacheadmtblsp INCLUDING CONTENTS AND DATAFILES;
</code>

=====Steps to add a TimesTen node into an existing Cache Grid (Doc ID 842990.1)=====
This assumes you already have a functioning Cache Grid with at least one TT node and one Oracle database.

1. Create the relevant users in the TimesTen datastore to be added to the Cache Grid. The datastore in the examples is called tt1121_2. Connect as the Instance Administrator user. This is assuming the Cache Admin user cacheadm and Oracle user tester are the same users / passwords that you created in previous TT nodes attached to the same grid, and hence do not need to be pre-created on Oracle again.
<code>
CREATE USER cacheadm IDENTIFIED BY cacheadmora; 
CREATE USER tester IDENTIFIED BY tester; 

GRANT CREATE SESSION, CACHE_MANAGER TO cacheadm; 
grant create any table to cacheadm; 
grant drop any table to cacheadm; 

grant admin to tester; 

-- as a shortcut make the TT user an administrator 
-- this isn't recommended anywhere else apart from for testing
 </code>

2. Run the following as the Cache Administrator on the Timesten datastore tt1121_2. This assumes the tester.writetab table has already been created on Oracle:
<code>
call ttCacheUidPwdSet('cacheadm','cacheadmora'); 

call ttGridNameSet('ttGrid'); 
call ttCacheStart; 

CREATE DYNAMIC ASYNCHRONOUS WRITETHROUGH GLOBAL CACHE GROUP tester.writecache 
FROM tester.writetab 
(a NUMBER NOT NULL PRIMARY KEY, b VARCHAR2(31)); 
call ttrepstart; 

call ttGridAttach(1,'tt1121_2','mymachine',9992);
</code>

3. Run the following as Instance Administrator on the Timesten datastore tt1121_2:
<code>
GRANT SELECT ON tester.writetab TO cacheadm; 
GRANT insert ON tester.writetab TO cacheadm; 
GRANT update ON tester.writetab TO cacheadm; 
GRANT delete ON tester.writetab TO cacheadm;
</code>

====== Commands ======
From ttisql
===== Size of the db =====
<code sql>
dssize;
</code>
==== Proceses ====
<code sql>
host ttstatus ;
</code>

==== Pause Cache Group Autorefresh ====
<code sql>
ALTER CACHE GROUP <GROUPNAME> SET AUTOREFRESH STATE PAUSED;
</code>

==== Disable Cache Group Autorefresh ====
<code sql>
ALTER CACHE GROUP <GROUPNAME> SET AUTOREFRESH STATE OFF;
</code>
==== Enable Cache Group Autorefresh ====
<code sql>
ALTER CACHE GROUP <GROUPNAME> SET AUTOREFRESH STATE ON;
</code>

==== Refresh cache group ====
<code sql>REFRESH CACHE GROUP <GROUPNAME> COMMIT EVERY 200 ROWS ;
</code>

==== Load cache group ====
<code sql>LOAD CACHE GROUP <GROUPNAME> COMMIT EVERY 200 ROWS ;
</code>

==== Unload cache group ====
<code sql>UNLOAD CACHE GROUP <GROUPNAME>  ;
</code>


==== list all cache groups ====
<code sql>SELECT CGNAME, CGOWNER FROM SYS.CACHE_GROUP ;
</code>

More detailed
<code sql>cachegroups
</code>

==== list all tables ====
<code sql>SELECT TBLNAME, TBLOWNER FROM SYS.TABLES ;
</code>

With cache group:
<code sql>
SELECT T.TBLNAME, T.TBLOWNER, CG.CGNAME
FROM SYS.TABLES T, SYS.CACHE_GROUP CG
WHERE T.CACHEGROUP=CG.CGID
 ;
</code>

===== Cache Agent =====
==== Stop ====
<code sql>
call ttCacheStop;
</code>
==== Start ====
<code sql>
call ttCacheStart;
</code>

=====Setup TNS_ADMIN=====
<code>
ttmodinstall -tns_admin D:\Oracle\64\product\12.1.0\client_1\network\admin
</code>

====== Utilities ======
From [[http://docs.oracle.com/database/121/TTREF/util.htm#TTREF320|official documentation]].



===== ttAdmin =====

==== Start Caching ====
<code>
ttAdmin -cacheStart "DSN=TIMESTENDSN;UID=AdminUID"
</code>
==== Stop Caching ====
<code>
ttAdmin -cacheStop "DSN=TIMESTENDSN;UID=AdminUID"
</code>

==== Ram Policy ====

  * Always loaded
<code>
ttAdmin -ramPolicy always SalesData
</code>
  * Only in use
<code>
ttAdmin -ramPolicy inUse SalesData
</code>
  * Manually
<code>
ttAdmin -ramPolicy manual SalesData
</code>
  * When manual, load:
<code>
ttAdmin -ramLoad SalesData
</code>
  * When manual, unload:
<code>
ttAdmin -ramUnload SalesData
</code>
====== Troubleshotting ======
===== tt15105 =====
Error sample:
<code>
PS C:\TimesTen\tt1122_64\bin> .\ttIsql.exe myinstance_tt

Copyright (c) 1996, 2014, Oracle and/or its affiliates. All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.

connect "DSN=myinstance_tt";
15105: User XXXX requesting database creation is not the instance administrator.  Please verify user id and password. Only the instance administrator can create a database.
The command failed.
Done.
</code>

The problem is that you're trying to access the instance WITHOUT being super admin (the one who installed the software).

See the admin user with:
<code>
PS C:\TimesTen\tt1122_64\bin> .\ttStatus.exe
TimesTen status report as of Mon Mar 23 18:42:23 2015

Daemon pid 3248 port 53396 instance tt1122_64
TimesTen server pid 2052 started on port 53397
------------------------------------------------------------------------
Accessible by group CIBERTERMINAL\Domain Users
End of report
PS C:\TimesTen\tt1122_64\bin> .\ttVersion.exe
TimesTen Release 11.2.2.7.4 (64 bit NT) (tt1122_64:53396) 2014-07-03T22:56:59Z
  Instance admin: dodger
  Instance home directory: C:\TimesTen\TT1122~1\
  Group owner: CIBERTERMINAL\Domain Users
  Daemon home directory: C:\TimesTen\TT1122~1\srv\info
  PL/SQL enabled.
</code>
  
And Connect/create the database with:
<code>
PS C:\TimesTen\tt1122_64\bin> .\ttIsql.exe "DSN=MYINSTANCE_TT;UID=dodger"

Copyright (c) 1996, 2014, Oracle and/or its affiliates. All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.



connect "DSN=MYINSTANCE_TT;UID=dodger";
Connection successful: DSN=DIVAPRE_TT;UID=dodger;DataStore=C:\TimesTen\MYINSTANCE_TT;DatabaseCharacterSet=AL32UTF8;ConnectionCharacterSet=AL32UTF8;DRIVER=C:\TimesTen\TT1122~1\bin\ttdv1122.dll;Isolation=1;LogDir=C:\TimesTen\MYINSTANCE_TT\txlogs;PermSize=20480;TempSize=5120;PermWarnThreshold=90;TempWarnThreshold=90;PassThrough=2;RecoveryThreads=4;RACCallback=0;TypeMode=0;PLSCOPE_SETTINGS=IDENTIFIERS:NONE;DynamicLoadErrorMode=1;OracleNetServiceName=CIBERTERMINAL;ReplicationParallelismBufferMB=128;RangeIndexType=1;
(Default setting AutoCommit=1)
Command>
</code>

===== No Oracle password =====
This error is either in sqldeveloper or ttIsql:
<code>
No Oracle password found in connection.  This command requires a connection with an Oracle password.
</code>

The cache admin username MUST BE THE SAME as the oracle user tt admin.

In sqldeveloper you must allow the creation of cache groups and fill the connection settings.

===== TT5907 =====

<code> 5907: The cache admin user id was not set before performing the operation.  Set the cache admin user id and password ei
ther through the built-in procedure ttCacheUidPwdSet or the utility ttAdmin and try again.
The command failed.
</code>

You must set the it all:
<code>
ttAdmin -cacheUidPwdSet -cacheUid cache_manager_user -cachePwd ThePassword myDSN
</code>


===== TT5002 / TT5051 =====
<code>
Warning  5002: Unable to connect to the cache agent for c:\timesten\DIVAPRE_TT; check agent status
Warning  5051: Commit message to cache agent failed. Cache agent must be restarted
</code>

Start (or restart) the cache agent:
<code>
ttadmin -cacheStart myDSN
ttadmin -cacheStop myDSN
</code>


===== TT5221 =====
<code>
 5221: Oracle syntax error in OCIStmtExecute(): ORA-00907: missing right parenthesis rc = -1
</code>
While executing [[http://docs.oracle.com/database/121/TTREF/proced.htm#TTREF248|ttGridAttach]]:
<code>ttGridAttach(currentNode, 'name1', IPAddr1, port1)</code>

I found that ALL THE PARAMETERS need to be between single quotation mark!

===== TT3344 =====
Whole stack:
<code>11:23:24.10 Err :    :  1344: 1670/0x14c6030: cacheGrid.c (10349): Member TT001: sbCGAttach failed at line 10193, errCode=3344 - TT3344: Error creating grid threads: cacheGridAPI.c (759): Member DIVAGRID_TT001_1 got error TT3401: P2P error: Failed to bind to socket
</code>

While executing [[http://docs.oracle.com/database/121/TTREF/proced.htm#TTREF248|ttGridAttach]]

The combination ip:port must be FREE! (TT will bind that ip:port combo).



Maybe you've tried early an attachment and the data are logged on the oracle side.
You can see that info in a table matching TT_%_CGNODEINFO, search it:
<code sql>
select owner, object_name, object_type 
from dba_objects 
where object_name like '%CGNODEINFO%' order by 2;
</code>
And delete the data:
<code sql>SELECT 'DELETE FROM ' || OWNER || '.' || OBJECT_NAME || ' ; ' 
FROM dba_objects
where object_name like '%CGNODEINFO%' ;

commit;
</code>

(maybe you want to make a backup of the data).


===== TT3316 =====


Whole stack:
<code>Command> CREATE DYNAMIC ASYNCHRONOUS WRITETHROUGH GLOBAL CACHE GROUP CACHEDUSER."aaaa"
       >        FROM 
       >        "CACHEDUSER"."EMAIL_QUEUE"
       >    ("ID" NUMBER(22,0) NOT NULL,
       > "XML_PARAMS" VARBINARY(4194304) NOT NULL,
       > "PROCESSED" NUMBER NOT NULL,
       > "ERROR_COUNT" NUMBER(22,0) NOT NULL,
       > "CREATION_TIME" TIMESTAMP (0) NOT NULL,
       > "LAST_ERROR_MSG" VARCHAR2(4000),
       > "SENDING_TIME" TIMESTAMP (0) NOT NULL,
       > "SENT_TIME" TIMESTAMP (0),
       > "PRIORITY" NUMBER(22,0),
       > "SENDER_MODULE" NVARCHAR2(280),
       > "SENDER_EMAIL" VARCHAR2(1024) NOT NULL,
       > "RECEIVER_EMAIL" VARCHAR2(1024) NOT NULL,
       >   PRIMARY KEY ("ID")
       >     );
 3316: Cache group definition mismatch among the members of the grid
</code>


Maybe you've tried early a definition of the cache group, maybe not with the same name but using the same table!!
Drop it from Oracle.

You can seek that info in a table matching TT_%CGGROUPDEFS:
<code sql>
select owner, object_name, object_type 
from dba_objects 
where object_name like '%CGGROUPDEFS%' order by 2;
</code>
And delete the data:
<code sql>SELECT 'DELETE FROM ' || OWNER || '.' || OBJECT_NAME || ' ; ' 
FROM dba_objects
where object_name like '%CGGROUPDEFS%' ;

commit;
</code>

(maybe you want to make a backup of the data).


TT_06_DIVAGRI_2CGGROUPDEFS
====== Links ======
  * http://gerardnico.com/wiki/timesten/cache_configuration
  * [[http://docs.oracle.com/cd/E21901_01/timesten.1122/e21634/oracle_tt.htm#TTCAC346|Oracle 2 TimesTen data type conversion table]]
  * [[http://docs.oracle.com/cd/E13085_01/index.htm|Oracle TimesTen documentation index]]
  * [[http://docs.oracle.com/cd/E13085_01/timesten.1121/e13069/util.htm|TimesTen Utilities (commands)]]
<code sql>
</code>