Ciberterminal Wiki

User Tools

Site Tools

Trace:
dba:oracle:docs:timesten

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revisionPrevious revision
dba:oracle:docs:timesten [2023/01/31 08:22] – removed - external edit (Unknown date) 127.0.0.1dba:oracle:docs:timesten [2023/01/31 08:22] (current) – ↷ Page moved from dba:oracle:timesten to dba:oracle:docs:timesten dodger
Line 1: Line 1:
 +====== [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>