TimesTen 与Oracle 数据库 12c的结合

之前的例子都是TimesTen与Oracle 11gr2结合，今天试了一下与DB 12c的结合。
从TimesTen的角度来说，12c和11g没有区别，都是通过tnsnames.ora来连接，主要的工作还是在12c这边。

本文介绍TimesTen 11.2.2.8.11和Oracle DB 12.1.0.2.0的结合，平台为Oracle Linux Server release 6.6

安装完Oracle后，已有一个PDB:pdborcl，以下是一些常用的12c命令
主要的工作是用alter pluggable database pdborcl open将PDB启动，并且利用netca定义tnsnames.ora

[oracle@tt12c ~]$ . oraenv
ORACLE_SID = [orcl] ? orcl
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@tt12c ~]$ echo $ORACLE_HOME
/u01/app/oracle/product/12.1.0/dbhome_1

[oracle@tt12c ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Apr 20 19:48:06 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1660944384 bytes
Fixed Size          2925072 bytes
Variable Size        1056968176 bytes
Database Buffers      587202560 bytes
Redo Buffers           13848576 bytes
Database mounted.
Database opened.

SQL> select NAME, DECODE(CDB, 'YES', 'Multitenant Option enabled', 'Regular 12c Database: ') "Multitenant Option ?"
, OPEN_MODE, CON_ID from V$DATABASE;  2  

NAME      Multitenant Option ?       OPEN_MODE            CON_ID
--------- -------------------------- -------------------- ----------
ORCL      Multitenant Option enabled READ WRITE            0

SQL> select CON_ID, NAME, OPEN_MODE from V$PDBS;

    CON_ID NAME               OPEN_MODE
---------- ------------------------------ ----------
     2 PDB$SEED              READ ONLY
     3 PDBORCL            MOUNTED <-还没有open，不可用
SQL> alter pluggable database pdborcl open;

Pluggable database altered.

SQL> select CON_ID, NAME, OPEN_MODE from V$PDBS;

    CON_ID NAME               OPEN_MODE
---------- ------------------------------ ----------
     2 PDB$SEED              READ ONLY
     3 PDBORCL            READ WRITE


SQL> show con_name;

CON_NAME
------------------------------
CDB$ROOT
SQL> alter session set container = pdborcl;

Session altered.

SQL> show con_name;

CON_NAME
------------------------------
PDBORCL

SQL> ALTER SESSION SET CONTAINER = CDB$ROOT;

Session altered.

使用netca配置tnsnames.ora，如下：

然后登录：
[oracle@localhost admin]$ sqlplus sys/Passw0rd@pdborcl as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Apr 20 15:48:07 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> show con_name;

CON_NAME
------------------------------
PDBORCL
SQL> create user tthr identified by oracle;

User created.
SQL> grant create session,create table to tthr;

Grant succeeded.

以下测试TimesTen与Oracle的连接，第一次测试出了个小错，原因是Oracle与TimesTen的字符集不一致，看来passthrough模式也需要两个数据库的字符集一致。

[oracle@tt12c ~]$ sqlplus tthr/oracle@pdborcl

SQL*Plus: Release 12.1.0.2.0 Production on Wed Apr 20 19:51:15 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Wed Apr 20 2016 15:53:45 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> 

SQL> create table oratab(a int);

Table created.

[oracle@tt12c ~]$ ttisql sampledb_1122
Command> create user tthr identified by timesten;
Command> grant create session, create table to tthr;
[oracle@tt12c ~]$ ttisql sampledb_1122

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



connect "DSN=sampledb_1122";
Connection successful: DSN=sampledb_1122;UID=oracle;DataStore=/home/oracle/TimesTen/tt1122/info/DemoDataStore/sampledb_1122;DatabaseCharacterSet=US7ASCII;ConnectionCharacterSet=US7ASCII;DRIVER=/home/oracle/TimesTen/tt1122/lib/libtten.so;PermSize=40;TempSize=32;TypeMode=0;
(Default setting AutoCommit=1)
Command> create user tthr identified by timesten;

User created.

Command> grant create session, create table to tthr;
Command> disconnect
Disconnecting...
Command> connect "dsn=sampledb_1122;uid=tthr;pwd=timesten";
Connection successful: DSN=sampledb_1122;UID=tthr;DataStore=/home/oracle/TimesTen/tt1122/info/DemoDataStore/sampledb_1122;DatabaseCharacterSet=US7ASCII;ConnectionCharacterSet=US7ASCII;DRIVER=/home/oracle/TimesTen/tt1122/lib/libtten.so;PermSize=40;TempSize=32;TypeMode=0;
(Default setting AutoCommit=1)
Command> create table tttab(a int);
Command> disconnect
Disconnecting...
Command> connect "dsn=sampledb_1122;uid=tthr;pwd=timesten"
       > ;
Connection successful: DSN=sampledb_1122;UID=tthr;DataStore=/home/oracle/TimesTen/tt1122/info/DemoDataStore/sampledb_1122;DatabaseCharacterSet=US7ASCII;ConnectionCharacterSet=US7ASCII;DRIVER=/home/oracle/TimesTen/tt1122/lib/libtten.so;PermSize=40;TempSize=32;TypeMode=0;
(Default setting AutoCommit=1)
Command> create table tttab(a int);
Command> 

[oracle@tt12c info]$ ttisql "dsn=sampledb_1122;uid=tthr;pwd=timesten;OracleNetServiceName=pdborcl;OraclePWD=oracle"

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



connect "dsn=sampledb_1122;uid=tthr;pwd=********;OracleNetServiceName=pdborcl;OraclePWD=********";
Connection successful: DSN=sampledb_1122;UID=tthr;DataStore=/home/oracle/TimesTen/tt1122/info/DemoDataStore/sampledb_1122;DatabaseCharacterSet=US7ASCII;ConnectionCharacterSet=US7ASCII;DRIVER=/home/oracle/TimesTen/tt1122/lib/libtten.so;PermSize=40;TempSize=32;TypeMode=0;OracleNetServiceName=pdborcl;
(Default setting AutoCommit=1)
Command> select * from tttab;
0 rows found.
Command> select * from oratab;
 2206: Table TTHR.ORATAB not found
The command failed.
Command> passthrough 3
Passthrough command has set autocommit off.
Command> select * from oratab;
 8296: TimesTen and Oracle database character sets do not match.  TimesTen: US7ASCII, Oracle: AL32UTF8
The command failed.
Command>  

修改字符集后，第二次测试成功了

Command> select * from tttab;
0 rows found.
Command> select * from oratab;
 2206: Table TTHR.ORATAB not found
The command failed.
Command> passthrough 3
Passthrough command has set autocommit off.
Command> select * from oratab;
0 rows found.
Command> passthrough 0;