TimesTen内存数据库活动主备及AWT实践（三）
创建只读CACHE GROUP
TTAPP@ttcgdb1>create table test_ro_tb1 (id number not null primary key,cont_ro varchar2(100));
Table created.
TTAPP@ttcgdb1>insert into test_ro_tb1 values (1001,'read only data from oracle');
1 row created.
TTAPP@ttcgdb1>insert into test_ro_tb1 values (1002,'read only data from oracle');
1 row created.
TTAPP@ttcgdb1>commit;
Commit complete.
将表的读写权限赋给ttcgadmin用户：
ttapp@ttcgdb1>grant select on test_ro_tb1 to ttcgadmin;
grant succeeded.
ttapp@ttcgdb1>grant insert on test_ ro _tb1 to ttcgadmin;
grant succeeded.
ttapp@ttcgdb1>grant update on test_ ro _tb1 to ttcgadmin;
grant succeeded.
ttapp@ttcgdb1>grant delete on test_ ro _tb1 to ttcgadmin;
grant succeeded.
[timesten@tt_master info]$ ttIsql "DSN=testawtds;UID=ttcgadmin;PWD=ttcgadminttpwd;OraclePWD=ttcgadminorapwd"
[timesten@tt_master ~]$ ttIsql "DSN=testawtds;UID=ttcgadmin;OraclePWD=ttcgadminorapwd"
Command>
在创建AWT以前，先调用下ttCacheUidPwdSet procedure，目的是在ORACLE数据库里验证一下用户名密码：
Command> call ttCacheUidPwdSet('ttcgadmin','ttcgadminorapwd');
执行上述命令后，如果用户名密码正确，且授权正确，则会在ORACLE数据库的ttcgadmin用户下创建如下表：
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
TT_06_AR_PARAMS TABLE
TT_06_CACHE_STATS TABLE
TT_06_DATABASES TABLE
TT_06_DBSPECIFIC_PARAMS TABLE
TT_06_DB_PARAMS TABLE
TT_06_DDL_L TABLE
TT_06_DDL_TRACKING TABLE
TT_06_LOG_SPACE_STATS TABLE
TT_06_USER_COUNT TABLE
9 rows selected.
接下来在tt库里启动cache agent：
Command> call ttcachestart;
接下来，创建CACHE GROUP，类型为READ ONLY，每5秒钟自动从ORACLE获取一次数据：
Command> create readonly cache group ttcgadmin.cgro_test_ro_tb1 autorefresh interval 5 seconds from ttapp.test_ro_tb1 (id number not null primary key,cont_ro varchar2(100));
如果没有报错的话，则执行成功，创建cache group与创建表不一样，创建成功后不会有“cache group created”的提示。
查看一下创建好的cache group信息，执行cachegroups;
Command> cachegroups;
Cache Group TTCGADMIN.CGRO_TEST_RO_TB1:
Cache Group Type: Read Only
Autorefresh: Yes
Autorefresh Mode: Incremental
Autorefresh State: Paused
Autorefresh Interval: 5 Seconds
Autorefresh Status: ok
Aging: No aging defined
Root Table: TTAPP.TEST_RO_TB1
Table Type: Read Only
1 cache group found.
以ttapp用户登录tt数据库，看是否生成了名称为test_ro_tb1的表：
[timesten@tt_master ~]$ ttisql -connstr "dsn=testawtds;uid=ttapp;pwd=ttappttpwd"
查看当前用户下的表，可以执行tables命令：
Command> tables;
TTAPP.TEST_RO_TB1
1 table found.
查看一下数据是否存在：
Command> select * from TTAPP.TEST_RO_TB1;
0 rows found.
可以看到表已经有了，但是数据不存在。
以ttcgadmin用户手动载入数据，然后再次查看：
（载入以前，如果cache agent没有启动，则需要先启动cache agent）
Command> load cache group ttcgadmin.cgro_test_ro_tb1 commit every 1000 rows parallel 2;
2 cache instances affected.
可以看到，这次成功了。这里的cache instance又是另外一个概念，这里可以先简单的理解为2行，暂时不用深究。更详细的解释，可以参考官方文档：
《Oracle TimesTen Application-Tier Database Cache User's Guide》。
查看一下具体的数据：
Command> select * from TTAPP.TEST_RO_TB1;
< 1001, read only data from oracle >
< 1002, read only data from oracle >
2 rows found.
insert两条数据试试：
Command> insert into TTAPP.TEST_RO_TB1 values (2001,'read only data from timesten');
8225: Table TTAPP.TEST_RO_TB1 is read only
The command failed.
提示表为read only，不能插入数据。
在oracle库里新插入两条记录，看5秒钟以后是否可以自动发送到TT库：
ttcgdb1上进行insert操作：
TTAPP@ttcgdb1>insert into TEST_RO_TB1 values (1003,'new read only data from oracle');
1 row created.
TTAPP@ttcgdb1>insert into TEST_RO_TB1 values (1004,'new read only data from oracle');
1 row created.
TTAPP@ttcgdb1>commit;
Commit complete.
TT数据库里进行查询：
Command> select * from ttapp.test_ro_tb1;
< 1001, read only data from oracle >
< 1002, read only data from oracle >
2 rows found.
数据尚未过来，5秒后再看：
Command> sleep 5;
Command> select * from ttapp.test_ro_tb1;
< 1001, read only data from oracle >
< 1002, read only data from oracle >
< 1003, new read only data from oracle >
< 1004, new read only data from oracle >
4 rows found.
可以看到数据已经过来了。
接下来，我们创建TT侧可更改的、异步模式的CACHE GROUP：Asynchronous writethrough (AWT)cache group，以下简称AWT。
创建AWT CACHE GROUP
TTAPP@ttcgdb1>create table ttapp.test_awt_tb1 (id number not null primary key,cont varchar2(100));
Table created.
TTAPP@ttcgdb1>insert into test_awt_tb1 values (1001,'awt data from oracle');
1 row created.
TTAPP@ttcgdb1>insert into test_awt_tb1 values (1002,'awt data from oracle');
1 row created.
TTAPP@ttcgdb1>commit;
将表的读写权限赋给ttcgadmin用户：
ttapp@ttcgdb1>grant select on test_awt_tb1 to ttcgadmin;
grant succeeded.
ttapp@ttcgdb1>grant insert on test_awt_tb1 to ttcgadmin;
grant succeeded.
ttapp@ttcgdb1>grant update on test_awt_tb1 to ttcgadmin;
grant succeeded.
ttapp@ttcgdb1>grant delete on test_awt_tb1 to ttcgadmin;
grant succeeded.
[timesten@tt_master info]$ ttIsql "DSN=testawtds;UID=ttcgadmin;PWD=ttcgadminttpwd;OraclePWD=ttcgadminorapwd"
Command> CREATE WRITETHROUGH CACHE GROUP cgawt_test_awt_tb1 from ttapp.test_awt_tb1 (id number not null primary key,cont varchar2(100));
成功执行的话，没有返回结果。
查看一下创建好的cache group，执行cachegroups;命令：
Command> cachegroups;
Cache Group TTCGADMIN.CGAWT_TEST_AWT_TB1:
Cache Group Type: Asynchronous Writethrough
Autorefresh: No
Aging: No aging defined
Root Table: TTAPP.TEST_AWT_TB1
Table Type: Propagate
Cache Group TTCGADMIN.CGRO_TEST_RO_TB1:
Cache Group Type: Read Only
Autorefresh: Yes
Autorefresh Mode: Incremental
Autorefresh State: On
Autorefresh Interval: 5 Seconds
Autorefresh Status: ok
Aging: No aging defined
Root Table: TTAPP.TEST_RO_TB1
Table Type: Read Only
2 cache groups found.
以ttapp用户登录tt数据库，看是否生成了名称为test_awt_tb1的表：
[timesten@tt_master ~]$ ttisql -connstr "dsn=testawtds;uid=ttapp;pwd=ttappttpwd"
Copyright (c) 1996, 2016, Oracle and/or its affiliates. All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.
connect "dsn=testawtds;uid=ttapp;pwd=********";
Connection successful: DSN=testawtds;UID=ttapp;DataStore=/tt/TimesTen/DS/testawtds/testawtds;DatabaseCharacterSet=ZHS16GBK;ConnectionCharacterSet=US7ASCII;LogFileSize=128;DRIVER=/tt/TimesTen/tt1122/lib/libtten.so;PermSize=512;TempSize=128;Connections=100;RecoveryThreads=4;TypeMode=0;CacheGridEnable=0;OracleNetServiceName=ttcgdb1;LogBufMB=128;LogBufParallelism=6;CkptReadThreads=4;
(Default setting AutoCommit=1)
Command> tables;
TTAPP.TEST_AWT_TB1
TTAPP.TEST_RO_TB1
2 tables found.
Command> select * from TTAPP.TEST_AWT_TB1;
0 rows found.
可以看到表已经有了，但是数据还是不存在。
以ttcgadmin用户手动载入数据，然后再次查看：
Command> load cache group TTCGADMIN.CGAWT_TEST_AWT_TB1 commit every 100 rows;
2 cache instances affected.
可以看到，载入了两条记录，select一下：
Command> select * from TTAPP.TEST_AWT_TB1;
< 1001, awt data from oracle >
< 1002, awt data from oracle >
2 rows found.
可以看到，数据已经过来了。在timesten里做一些dml操作：insert两条数据，update一条记录，delete一条记录：看ORACLE数据库里是否可以自动同步：
ttisql "dsn=testawtds;uid=ttapp;pwd=ttappttpwd" ;
Command> insert into ttapp.test_awt_tb1 values (1101,'awt data from timesten');
1 row inserted.
Command> insert into ttapp.test_awt_tb1 values (1102,'awt data from timesten');
1 row inserted.
Command>update TTAPP.TEST_AWT_TB1 set cont='update awt data from timesten' where id=1001;
1 row updated.
Command> delete from TTAPP.TEST_AWT_TB1 where id=1002;
1 row deleted.
Command> select * from TTAPP.TEST_AWT_TB1;
< 1001, update awt data from timesten >
< 1101, awt data from timesten >
< 1102, awt data from timesten >
3 rows found.
注：TT数据库里不用手动commit，因为默认就是自动提交的。
在ORACLE数据库里看看能不能看到数据：
TTAPP@ttcgdb1>select * from ttapp.test_awt_tb1;
ID CONT
---------- ------------------------------
1001 data from oracle
1002 data from oracle
看不到数据，这是因为在TT数据库侧，还没有启动replication agent，replication agent进程负责将TT数据库侧发生的改变发送至ORACLE数据库，启动的命令为：
call ttrepstart; 也可以在操作系统上执行：ttrepadmin -repstart testawtds
Command> call ttrepstart;
此时，再在ORACLE数据库里查看，可以看到数据已经过来了：
TTAPP@ttcgdb1>/
ID CONT
---------- ----------------------------------------
1001 update awt data from timesten
1101 awt data from timesten
1102 awt data from timesten
3 rows selected.
接下来，我们创建活动主备ACTIVE STANDBY PAIR+只读SUBSCRIBER灾备库，以模拟TT的高可用及读写分离架构，高可用搭建完成后，模拟主备切换：switchover和failover，后再演示AWT cache group的限制。
创建TT ACTIVE STANDBY PAIR
tt active standby pair属于tt replication的一种，默认情况下，它针对全库进行复制，并且可以非常方便的进行switchover和failover，应用的非常广泛。tt replication包含的内容也非常多，由于篇幅有限，这里不详细介绍ttreplication，要想了解更多关于tt replication的信息，可以参考TT官方文档：
《Oracle TimesTen In-Memory DatabaseReplication Guide》
在tt_master主机上，以ttcgadmin用户登录TT并创建，创建以前需要先做如下两个操作：
1、暂停只读cache group的自动刷新操作。
2、停掉replication agent，创建完后，再启动：
[timesten@tt_master ~]$ ttIsql "DSN=testawtds;UID=ttcgadmin;PWD=ttcgadminttpwd;OraclePWD=ttcgadminorapwd"
Command> alter cache group ttcgadmin.cgro_test_ro_tb1 set autorefresh state paused;
Command> call ttrepstop;
Command> create active standby pair
testawtds on tt_master,
testawtds on tt_standby
subscriber testawtds on tt_subscriber
store testawtds on tt_master port 53399
store testawtds on tt_standby port 53399
store testawtds on tt_subscriber port 53399;
这里除了指定了复制时使用的端口外，其余均使用默认的选项。如果不指定端口，TT会自动分配端口（端口不固定），但是在某些情况下，在通过duplicate创建备库时，会出现不能成功连接至自动分配的端口的情况，所以这里我们明确指定一个端口，不使用自动分配的端口，创建完成后，启动复制代理进程：
Command> call ttrepstart;
然后将当前库的状态设置为“ACTIVE”，目的是将当前库的角色设置为“主库”：
Command> call ttrepstateget;
< IDLE, NO GRID >
1 row found.
Command> call ttrepstateset('ACTIVE');
Command> sleep 5;
Command> call ttrepstateget;
< ACTIVE, NO GRID >
1 row found.
可以看到，主库的状态已经成功便成了ACTIVE状态。
启动只读cache group的自动刷新：
Command> alter cache group TTCGADMIN.CGRO_TEST_RO_TB1 set autorefresh state on;
接下来在tt_standby上创建备库。
与ORACLE 11g的DATAGUARD在线duplicate类似，TT的备库也可以通过在线复制方式创建，具体的命令为ttRepAdmin -duplicate，拷贝过程如下所示：
[timesten@tt_standby ~]$ ttRepAdmin -verbosity 2 -duplicate -from testawtds -host tt_master -dsn testawtds -localhost tt_standby -uid ttcgadmin -pwd ttcgadminttpwd -keepcg -cacheuid ttcgadmin -cachepwd ttcgadminorapwd
16:16:56 Contacting remote main daemon at 192.0.2.31 port 53396
16:16:56 Contacting the replication agent for TESTAWTDS ON TT_MASTER (192.0.2.31) port 53399
16:16:56 Beginning transfer from TESTAWTDS ON TT_MASTER to TESTAWTDS ON TT_STANDBY
16:17:00 Checkpoint transfer 100 percent complete
16:17:00 Checkpoint transfer phase complete
16:17:00 Log transfer 100 percent complete
16:17:00 Log transfer phase complete
16:17:00 Transfer complete
Subscriber State
TESTAWTDS ON TT_SUBSCRI START
TESTAWTDS ON TT_MASTER START
_ORACLE ON TT_STANDBY START
ttRepAdmin -duplicate命令使用的的其它参数的作用可以查看附录二。
复制完成后，登录数据库，执行call ttconfiguration，查看数据库配置信息，以便加深对ttrepadmin -duplicate命令的理解。
[timesten@tt_standby ~]$ ttIsql "DSN=testawtds;UID=ttcgadmin;PWD=ttcgadminttpwd;OraclePWD=ttcgadminorapwd"
Command> call ttconfiguration;
Command> call ttconfiguration;
< CacheAwtMethod, 1 >
< CacheAwtParallelism, 1 >
< CacheGridEnable, 0 >
< CacheGridMsgWait, 60 >
< CkptFrequency, 600 >
< CkptLogVolume, 0 >
< CkptRate, 0 >
< CkptReadThreads, 4 >
< CommitBufferSizeMax, 0 >
< ConnectionCharacterSet, US7ASCII >
< ConnectionName, testawtds >
< Connections, 100 >
< DDLCommitBehavior, 0 >
< DDLReplicationAction, INCLUDE >
< DDLReplicationLevel, 2 >
< DataBaseCharacterSet, ZHS16GBK >
< DataStore, /tt/TimesTen/DS/testawtds/testawtds >
< DynamicLoadEnable, 1 >
< DuplicateBindMode, 0 >
< DurableCommits, 0 >
< DynamicLoadErrorMode, 0 >
< Isolation, 1 >
< RangeIndexType, 1 >
< LockLevel, 0 >
< LockWait, 10 >
< LogAutoTruncate, 1 >
< LogBufMB, 128 >
< LogBufParallelism, 6 >
< LogDir, /tt/TimesTen/DS/testawtds >
< LogFileSize, 128 >
< LogFlushMethod, 1 >
< LogPurge, 1 >
< Logging, 1 >
< MemoryLock, 0 >
< NLS_LENGTH_SEMANTICS, BYTE >
< NLS_NCHAR_CONV_EXCP, 0 >
< NLS_SORT, BINARY >
< OracleNetServiceName, ttcgdb1 >
< PLSCOPE_SETTINGS, IDENTIFIERS:NONE >
< PLSQL, 1 >
< PLSQL_CCFLAGS, <NULL> >
< PLSQL_CODE_TYPE, INTERPRETED >
< PLSQL_CONN_MEM_LIMIT, 100 >
< PLSQL_MEMORY_ADDRESS, 0x0000007fa0000000 >
< PLSQL_MEMORY_SIZE, 32 >
< PLSQL_OPTIMIZE_LEVEL, 2 >
< PLSQL_TIMEOUT, 30 >
< PassThrough, 0 >
< PermSize, 512 >
< PermWarnThreshold, 90 >
< Preallocate, 0 >
< PrivateCommands, 0 >
< QueryThreshold, 0 >
< RACCallback, 1 >
< ReceiverThreads, 1 >
< RecoveryThreads, 4 >
< ReplicationApplyOrdering, 0 >
< ReplicationParallelism, 1 >
< ReplicationTrack, 255 >
< SQLQueryTimeout, 0 >
< TempSize, 128 >
< TempWarnThreshold, 90 >
< Temporary, 0 >
< TypeMode, 0 >
< UID, TTCGADMIN >
65 rows found.
Command> select * from user_sys_privs;
< TTCGADMIN, CREATE SESSION, NO >
< TTCGADMIN, ADMIN, YES >
< TTCGADMIN, CACHE_MANAGER, NO >
< TTCGADMIN, CREATE ANY TABLE, NO >
< TTCGADMIN, DROP ANY TABLE, NO >
5 rows found.
启动复制代理cache代理：
Command> CALL ttRepStart;
Command> call ttcachestart;
Command> call ttrepstateget;
< IDLE, NO GRID >
1 row found.
Command>sleep 5;
Command> call ttrepstateget;
< STANDBY, NO GRID >
1 row found.
可以看到，复制的状态已经变成了STANDBY，查看两张cache group表里的数据是否存在：
Command> select * from ttapp.test_ro_tb1;
< 1001, read only data from oracle >
< 1002, read only data from oracle >
< 1003, new read only data from oracle >
< 1004, new read only data from oracle >
4 rows found.
Command> select * from ttapp.test_awt_tb1;
< 1001, update awt data from timesten >
< 1101, awt data from timesten >
< 1102, awt data from timesten >
3 rows found.
Command>
可以看到，数据正常。
接下来在tt_subscriber主机上创建灾备库。
原创文章，版权归本文作者所有，如需转载请注明出处
来自：https://mp.weixin.qq.com/s/d3-MhRL7417zFRT0Wk5rGw
相关文章