Differences

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

--- oracle:timesten [2018/04/27 08:59] – dodger
+++ dba:oracle:docs:timesten [2023/01/31 08:22] – removed - external edit (Unknown date) 127.0.0.1
@@ 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 ====
-st 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.
-. 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>
-. 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>
-. 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";
-: 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=jholgado";
-Connection successful: DSN=DIVAPRE_TT;UID=jholgado;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>
-: 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")
-       >     );
-: 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>