内存数据库TT应用培训

1、内存数据库TT交流 目录3、最佳实践 1、TT总体架构 2、参数配置 2022/8/292TT整体架构2022/8/2932022/8/294TT组成TimesTen products consist of combinations of the following components:内存数据库产品由如下部分组成:1、Shared libraries 共享库2、Memory-resident data structures 基于内存的数据结构3、Database processes 数据库进程4、Administrative programs 管理程序5、Checkpoint and lo

2、g files 日志文件2022/8/295TT组成（一）Shared librariesThe routines that implement the TimesTen functionality are embodied in a set of shared libraries that developers link with their applications and execute as a part of the applications process. This shared library approach is in contrast to a more conventi

3、onal RDBMS, which is implemented as a collection of executable programs to which applications connect, typically over a client/server network. Applications can also use a client/server connection to access an IMDB Cache or TimesTen database, though in most cases the best performance will be realized

4、 with a directly linked application. TimesTen功能是由一系列共享库来体现。开发者利用共享库来连接应用进程。在关系型数据库中，尤其是基于CS架构的应用程序对共享库使用非常广泛。虽然直连是公认的最好的应用方式，但是应用也可以使用C/S模式连接到timesten数据库。2022/8/296TT组成（二）Memory-resident data structuresThe TimesTen database resides entirely in main memory at runtime. It ismaintained in shared memory

5、 segments in the operating system and contains alluser data, indexes, system catalogs, log buffers, lock tables and temp space.Multiple applications can share one database, and a single application can accessmultiple databases on the same system.Timesten在运行状态下是完全保存在内存中的。它保存在操作系统的共享内存段，包含用户数据、索引、系统日志

6、、日志缓存、锁表、临时表空间等。如果操作系统一致，多个应用可以共享一个数据库，一个应用也可以连接多个内存数据库。Database processesTimesTen assigns a separate process to each database to perform operationsincluding the following :对如下每个数据库执行操作,TimesTen的分配一个单独的进程 Loading the database into memory from a checkpoint disk Recovering the database if it needs to

7、be recovered after loading it intoMemory 恢复数据库 Performing periodic checkpoints in the background against the activeDatabase 后台定期检查数据库，保证可用性。 Detecting and handling deadlocks 检测处理死锁 Performing data aging 数据归档2022/8/297TT组成（三）Administrative programsUtility programs are explicitly invoked by users, scr

8、ipts, or applications to perform services such as interactive SQL, bulk copy, backup and restore,database migration and system monitoring.实用程序被用户、脚本应用程序服务等显示调用。如：交互式sql、批量复制、备份、恢复、数据库迁移、系统监控。Checkpoint and log filesCheckpoint files contain an image of the database on disk. TimesTen uses dual checkpo

9、int files for additional safety, in case the system fails while a checkpoint operation is in progress. Changes to databases are captured in transaction logs that are written to disk periodically. If a database needs to be recovered,TimesTen merges the database checkpoint on disk with the completed t

10、ransactions that are still in the log files. Normal disk are used for checkpoints and log files.Checkpoint文件是一个保存在磁盘的数据库镜像。如果，在做checkpoint操作时失败，checkpoint文件时一个有效的安全保障。交互日志里面的所有数据库变更都会定期写入磁盘。如果数据库需要恢复，Timesten能在磁盘文件中找到这个还原点。一般情况下，磁盘文件系统被用来保存checkpoint和log文件。2022/8/298标题黑体，28号字Cache groupREADONLY CACH

11、E GROUP：READONLYASYNCHRONOUS WRITETHROUGH CACHE GROUPSYNCHRONOUS WRITETHROUGH CACHE GROUPUSER MANAGED CACHE GROUPApplications can read from and write to cache groups.Cache groups can be refreshed from Oracle data automatically or manually.Updates to cache groups can be propagated to Oracle tables au

12、tomatically or manually.Changes to either Oracle tables or the cache group can be tracked automatically2022/8/299目录3、最佳实践 1、TT总体架构 2、参数配置 2022/8/2910ODBC.INIDSN：设置data source nameDRIVER：设置TimesTen动态驱动库路经，如果是使用静态库，则不用设置DataStore：设置TimesTen数据文件存放路径，（eg: /timesten6/var/TimesTen/tt60/DemoDataStore/OCS_D

13、ATA）Connections：设置连接数上限PermSize：设定TimesTen的固定内存，可以在第一次连接时增加PermSize，不能在有数据库连接时修改，单位为MB。TempSize：设定TimesTen的临时内存，单位为MB，建议设定值=PermSize / 4ThreadSafe：0：使用非线程安全访问datastore 1：线程安全Logging：0：不写文件日志 1：写文件日志 2：只写系统内部日志到文件OracleID：设置连接oracle的SIDOraclePWD：设置连接oracle的passwordPassThrough：0：SQL只在TimesTen中执行 1：SQL

14、不能在TimesTen中解析，TimesTen把SQL PassThrough到oracle中解析执行ckptFrequency：设置 checkpoint的调用时间间隔，单位为秒CkptLogVolume：当log文件堆积超过设定值时，TimesTen调用checkpointLog：设置日志文件大小，当日志文件超过指定值时，TimesTen会生成新的日志文件，单位为MBLogBuffSize：内存日志缓冲区大小，单位为KB，建议设定值=PermSize/8LogPurge :0表示删除日志，1：不删除日志2022/8/2911ODSC.INI1、Connections=300/连接数，可以在

15、monitor中查看已经的连接数connections和DISConnections2、PermSize=49152/单位是MB，根据实际的需要进行设定，The permanent data partition can be increased in size, but it cannot be decreased.可以根据实际的需要进行估算3、TempSize=6144 /单位是MB，The temporary data partition can be either increased or decreased in size for一般是1/8*PermSize,但一般不超过1G2022

16、/8/2912TimesTen复制什么是复制replication？复制replication怎么工作？怎么配置复制replication？2022/8/2913目录3、最佳实践 1、TT总体架构 2、参数配置 2022/8/2914什么是复制复制就是在多个Data_Store之间数据的相互拷贝，确保应用或者内存数据库在某一台或者多台主机上出现数据问题之后的高可用性。2022/8/2915复制工作原理主备机之间的复制是由replication agent 控制，replication agent 读取transaction log 缓存区中的记录，确定主备机之间需要更新的各元素，replica

17、tion agent 通过TCP/IP STREAM SOCKETS 发送更新请求，对端replication agent 接收请求，应用更新。2022/8/2916复制的兼容性主备机之间的DatabaseCharacterSet和TypeMode必须具备相同的属性主备机之间TimesTen的版本必须一致主备机之间Data Store实例名称必须一致17复制元素Data StoreTablesSequences2022/8/2918复制方式Default replicationReturn receipt replicationReturn twosafe replicationDefault

18、 replication是一种异步方式，是默认方式。Return receipt replication与Return twosafe replication是同步方式，两者的区别在于前者是基于网络层，后者是基于应用层。前者确保对端replication agent 接收到更新内容后结束会话，后者确保对端应用提交更新后结束会话。2022/8/2919Default replication2022/8/2920Return receipt replication2022/8/2921Return twosafe replication2022/8/2922命令行下的命令操作管理TimesTen：

19、ttAdmin -ramPolicy manual tpabm启停Timesten主进程：ttdaemonadmin start|stop启停Timeten缓存：call ttcachestart();call ttcachestop();启停Timeten复制：call ttrepstart();call ttrepstop();ttSize计算表大小ttSize -tbl tb_prd_prd_inst -rows 1000000 DSN=iba4“ttSize -tbl RM_HB.DAT_REALTIME_ACCT_ITEM DSN=iba4tt1“ttschema 到处创建table

20、、cache、replication、sequence脚本ttSchema DSN=iba4 script.txtttSchema -list tables iba4tt1操作系统下检查锁表情况ttxactadmin DSN锁表后的回滚ttxactadmin -xactIdRollback 1.49 DSNttxactadmin -xactIdRollback 通过这种方式回滚之后，手工执行一下 ttckpt; 内存数据库命令行下：call ttckpt();2022/8/2923ttStatusTimesTen status report as of Fri Oct 14 17:19:46

21、2005Daemon pid 1784 port 16000 instance tt60TimesTen server pid 4020 started on port 16002TimesTen webserver pid 240 started on port 16004Data store c:tempRunData_TimesTenThere are 10 connections to the data storeData store is in shared modeShared Memory KEY GlobalDBI43471ef6.0.SHM.17 HANDLE 0 x350T

22、ype PID Context Connection Name ConnIDOracle Agent 4632 0 x00739290 Handler 2Oracle Agent 4632 0 x00becf80 Timer 3Oracle Agent 4632 0 x00c15118 Aging 4Oracle Agent 4632 0 x03ecd018 ttora60 6Oracle Agent 4632 0 x03f16c88 ttora60 7Process 3584 0 x02c07f30 oracletest 1Subdaemon 136 0 x00db00b0 Worker 2

23、044Subdaemon 136 0 x00e2d368 Flusher 2045Subdaemon 136 0 x00e52558 Checkpoint 2046Subdaemon 136 0 x00e78798 Monitor 2047Replication policy : ManualOracle agent policy : ManualTimesTens Oracle agent is running for this data storeEnd of report2022/8/2924常见问题-解决方案TT基本的命令操作（常用）OracleTimesTen数据不同步怎么办？锁表了

24、怎么办？Log文件不段增加，磁盘空间快耗尽了，怎么办？复制不能正常工作怎么办？我想重新装载TimesTen中的数据怎么办？如何监控数据库的运行状态?25数据导入导出拷贝目前的数据到文件中输出为 -ottBulkCp -o DSN=ocs tb_bil_acct_balance acct_balance.dump/字符串不加引号ttBulkCp -o -Q 0 DSN=ocs tb_bil_acct_balance acct_balance.txt输入 -ittBulkCp -i -e ratable_history.err DSN=ocs_serv ratable_history ratabl

25、e_history.dumpThe above command will terminate after the first error occurs. To force the copyto continue until the end of the input file (or a fatal error), use -m 0, as in:ttBulkCp -i -e ratable_history.err -m 0 DSN=ocs_serv ratable_history ratable_history.dump2022/8/2926Ttisql 下的命令call ttConfigur

26、ation 内存数据库配置call ttAgingScheduleNow(时间窗)命令行下查看锁表信息call ttlogholds();statsupdate balance_reserve 表分析Call ttoptupdatestats连接登录数据库ttIsql -connStr DSN=MY_DSN -f create.sql运行脚本Command run create.sql; 2022/8/2927Ttisql 下的命令dssize; PERM_ALLOCATED_SIZE: 1048576 PERM_IN_USE_SIZE: 3736 PERM_IN_USE_HIGH_WATER

27、: 3753 TEMP_ALLOCATED_SIZE: 1048576 TEMP_IN_USE_SIZE: 114865 TEMP_IN_USE_HIGH_WATER: 1149232022/8/2928数据加载和同步加载：LOAD CACHE GROUP UpdateAnywhereCustomers COMMIT EVERY 10 ROWSLOAD CACHE GROUP CustomerOrders WHERE customer.cust_num = 20 COMMIT EVERY 25 ROWS卸载：UNLOAD CACHE GROUP UpdateAnyWhereCustomersU

28、NLOAD CACHE GROUP CustomerOrders WHERE customer.cust_num 100刷新：REFRESH CACHE GROUP SQL statement is equivalent to an UNLOAD CACHE GROUP statement followed by a LOAD CACHE GROUP statementCan be used on any cache group typealter cache group CG_TB_BIL_ACCT_test1 set autorefresh state paused;REFRESH CAC

29、HE GROUP CG_TB_BIL_ACCT_test1 COMMIT EVERY 40 ROWS;REFRESH CACHE GROUP CustomerOrders WHERE customer.cust_num = 50 COMMIT EVERY 40 ROWS增量变更：FLUSH CACHE GROUP SQL manually propagates committed updates on TimesTen cache tables to corresponding Oracle tablesCan only be used on USERMANAGED cache groups

30、Can be used only when commit propagation from TimesTen to Oracle is disabledOnly INSERTs and UPDATEs are propagatedFLUSH CACHE GROUP CG_TB_BIL_ACCT_test1;FLUSH CACHE GROUP UpdateAnywhereCustomers WHERE customer.cust_num 99FLUSH CACHE GROUP UpdateAnywhereCustomers WITH ID (180)2022/8/2929索引设置原则创建正确的索

31、引Hash indexes（比较快的exact match lookup）T-tree index（适用于exact match & range lookup）Primary Key 自动创建 Hash indexHash index ：Pages = Rows expected / 2561、在创建cache 的脚本中：pages=总的记录数/256 ,如果计算出来的pages=1800,则实际pages=2000比较合适，即要比实际的大一些；2、如果脚本已经创建，并且pages过小，可以采用下列的语句来修改：ALTER TABLE tb_prd_prd_inst_551 SET pages

32、=9000;3、修改了pages值后需要分析表:statsupdate tb_prd_prd_inst2022/8/2930内存管理策略1、执行：ttAdmin -ramPolicy always|manual|inuse hb_abm 让数据永远保存在内存中（即使没有应用连接和ttisql 连接，除非把数据库停下来）这样不需要每次进入后都需要从新加载到内存中，2、另外停止TT数据库也需要在断开所有的连接，否则强行stop后再启动，数据库会查找还原点，数据加载时间会很长。2022/8/2931OracleTimesTen数据不同步怎么办？存在多种情况:Cache agent没有启动，使用tts

33、tatus察看TT所有进程的运行状况，使用call ttcachestart();启动cache agent.Oracle table中的数据存在问题，如有NULL值，导致同步异常，异常日志在/var/adm/syslog/syslog.log中，从日志文件中分析出现错误表，在Oracle中修改或删除异常数据 。32锁表了怎么办？解决方案一： 6003: Lock request denied because of time-out Details: Tran 11.74108 (pid 9981) wants W lock on table OCSTEST.PROCESS_STATE. Bu

34、t tran 13.32122 (pid 10003) has it in W (request was W). Holder SQL (update process_state set run_state = 0;) ps 这个进程，确认后killTtxactadmin dsn=？察看系统所有的锁，找到对应的锁，然后ttxactadmin -xactIdRollback 33Log文件不段增加，磁盘空间快耗尽了，怎么办？使用ttstatus命名察看replication agent启动没有？如果没有启动，执行call ttrepstart();使用cal ttckpt()；手工check point；使用call ttbookmark();call ttckpthistory();观察处理情况。可能存在长事务，使用call ttlogholds()命名察看日志情况，如果存在Long-Running Transaction，则根据事务号查询对应的锁，然后rollback就可以了。34日志的自动删除TimesTen purges the log files if all of these conditions are met:1、The contents of the log files have been written to both checkpoint files,2、Th