B TimesTen Cache Example

This appendix provides a working example for using TimesTen Cache in your Kubernetes environment. This example should not be used for production purposes. It assumes a test environment. Your Oracle Database should be customized with the settings specific to your environment.

Topics:

Setting up the Oracle Database to cache data

The following sections describe the tasks that must be performed in the Oracle Database:

Create the Oracle Database users

Before you can use TimesTen Cache, you must create the following users in your Oracle database:

  • A cache administration user. This user creates and maintains Oracle Database objects that store information about the cache environment. This user also enforces predefined behaviors of cache group types.

  • One or more schema users who owns Oracle Database tables that are cached in a TimesTen database.

See "Create the Oracle database users" in the Oracle TimesTen Application-Tier Database Cache User's Guide for information.

This example creates the cacheuser2 cache administration user and the oratt schema user in the Oracle Database.

  1. Create a shell from which you can access your Oracle Database and then use SQL*Plus to connect to the Oracle Database as the sys user. Then, create a default tablespace to store the TimesTen Cache management objects. See "Create the Oracle database users" in the Oracle TimesTen Application-Tier Database Cache User's Guide for information.

    This example creates the cachetablespace2 tablespace.

    % sqlplus sys/syspwd@oracache as sysdba
 
SQL*Plus: Release 12.1.0.2.0 Production on Fri Oct 23 22:10:20 2020
 
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> CREATE TABLESPACE cachetablespace2 DATAFILE 'datatt2.dbf' SIZE 100M;
 
Tablespace created.

  2. Use SQL*Plus to create the schema user. Grant this schema user the minimum privileges required to create tables in the Oracle Database to be cached in your TimesTen database.

    This example creates the oratt schema user.

    SQL> CREATE USER oratt IDENTIFIED BY oraclepwd;
 
User created.
 
SQL> GRANT CREATE SESSION, RESOURCE TO oratt;
 
Grant succeeded.

  3. Use SQL*Plus to create the cache administration user. Assign the cachetablespace2 tablespace to this user. You will later use the same name of this Oracle cache administration user in the cacheUser metadata file. See "cacheUser" and see "Creating the metadata files and the Kubernetes facility" for details on the cacheUser metadata file.

    This example creates the cacheuser2 user.

    SQL> CREATE USER cacheuser2 IDENTIFIED BY oraclepwd 
       DEFAULT TABLESPACE cachetablespace2 
       QUOTA UNLIMITED ON cachetablespace2;
 
User created.

SQL> commit;
 
Commit complete.

SQL> exit

Grant privileges to the cache administration user

The cache administration user must be granted a specific set of privileges depending on the cache group types that will be created in the TimesTen databases and the operations performed on those cache groups. TimesTen provides the grantCacheAdminPrivileges.sql SQL*Plus script that you can run in your Oracle Database to grant the cache administration user the minimum set of privileges required to perform cache operations. See "Grant privileges to the Oracle database users" and see "Required privileges for the cache administration user and the cache manager user" in the Oracle TimesTen Application-Tier Database Cache User's Guide for more information on these privileges.

Perform these steps to run the grantCacheAdminPrivileges.sql script:

  1. Create a shell from which you can access your Oracle Database, and then from the directory of your choice, create an empty subdirectory. This example creates the oraclescripts subdirectory.

    % mkdir -p oraclescripts

  2. From your Linux development host, use the kubectl cp command to copy the grantCacheAdminPrivileges.sql script from the installation_dir/oraclescripts directory on your Linux development host to the oraclescripts directory that you just created. Recall that the installation_dir directory was created when you unpacked the TimesTen distribution. See "Downloading TimesTen and the TimesTen Operator" for information on unpacking the TimesTen distribution.

    % cp /installation_dir/oraclescripts/grantCacheAdminPrivileges.sql 
database-oracle:oraclescripts/grantCacheAdminPrivileges.sql

  3. From your shell, verify the script is located in the oraclescripts directory.

    % ls oraclescripts
grantCacheAdminPrivileges.sql

  4. Use SQL*Plus to connect to the Oracle Database as the sys user. Then, run the oraclescripts/grantCacheAdminPrivileges.sql script. This script grants the cacheuser2 cache administration user the minimum set of privileges required to perform cache group operations. See "Grant privileges to the Oracle database users" in the Oracle TimesTen Application-Tier Database Cache User's Guide for more information.

    % sqlplus sys/syspwd@oracache as sysdba
 
SQL*Plus: Release 12.1.0.2.0 Production on Fri Oct 23 22:10:20 2020
 
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> @grantCacheAdminPrivileges "cacheuser2";
 
Please enter the administrator user id
The value chosen for administrator user id is cacheuser2
 
TT_CACHE_ADMIN_ROLE role already exists
***************** Initialization for cache admin begins ******************
0. Granting the CREATE SESSION privilege to CACHEUSER2
1. Granting the TT_CACHE_ADMIN_ROLE to CACHEUSER2
2. Granting the DBMS_LOCK package privilege to CACHEUSER2
3. Granting the DBMS_DDL package privilege to CACHEUSER2
4. Granting the DBMS_FLASHBACK package privilege to CACHEUSER2
5. Granting the CREATE SEQUENCE privilege to CACHEUSER2
6. Granting the CREATE CLUSTER privilege to CACHEUSER2
7. Granting the CREATE OPERATOR privilege to CACHEUSER2
8. Granting the CREATE INDEXTYPE privilege to CACHEUSER2
9. Granting the CREATE TABLE privilege to CACHEUSER2
10. Granting the CREATE PROCEDURE  privilege to CACHEUSER2
11. Granting the CREATE ANY TRIGGER  privilege to CACHEUSER2
12. Granting the GRANT UNLIMITED TABLESPACE privilege to CACHEUSER2
13. Granting the DBMS_LOB package privilege to CACHEUSER2
14. Granting the SELECT on SYS.ALL_OBJECTS privilege to CACHEUSER2
15. Granting the SELECT on SYS.ALL_SYNONYMS privilege to CACHEUSER2
16. Checking if the cache administrator user has permissions on the 
    default tablespace
    Permission exists
18. Granting the CREATE TYPE privilege to CACHEUSER2
19. Granting the SELECT on SYS.GV$LOCK privilege to CACHEUSER2
20. Granting the SELECT on SYS.GV$SESSION privilege  to CACHEUSER2
21. Granting the SELECT on SYS.DBA_DATA_FILES privilege  to CACHEUSER2
22. Granting the SELECT on SYS.USER_USERS privilege  to CACHEUSER2
23. Granting the SELECT on SYS.USER_FREE_SPACE privilege  to CACHEUSER2
24. Granting the SELECT on SYS.USER_TS_QUOTAS privilege  to CACHEUSER2
25. Granting the SELECT on SYS.USER_SYS_PRIVS privilege  to CACHEUSER2
26. Granting the SELECT on SYS.V$DATABASE privilege  to CACHEUSER2 (optional)
27. Granting the SELECT ANY TRANSACTION privilege to CACHEUSER2
********* Initialization for cache admin user done successfully *********

You have successfully run the grantCacheAdminPrivileges.sql script in the Oracle Database.

Create the Oracle Database tables to be cached

This example creates two tables in the oratt user schema. See "Create the Oracle Database users" for information on this user.

  • readtab: This table will later be cached in a read-only cache group.

  • writetab: This table will later be cached in an AWT cache group.

  1. Create a shell from which you can access your Oracle Database and then use SQL*Plus to connect to the Oracle Database as the sys user. Then create the oratt.readtab and the oratt.writetab tables.

    % sqlplus sys/syspwd@oracache as sysdba
 
SQL*Plus: Release 12.1.0.2.0 Production on Fri Oct 23 22:10:20 2020
 
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> CREATE TABLE oratt.readtab (keyval NUMBER NOT NULL PRIMARY KEY, 
       str VARCHAR2(32));
 
Table created.

SQL> CREATE TABLE oratt.writetab (pk NUMBER NOT NULL PRIMARY KEY, 
       attr VARCHAR2(40));
 
Table created.

  2. Use SQL*Plus to insert rows into the oratt.readtab and the oratt.writetab tables. Then verify the rows have been inserted.

    SQL> INSERT INTO oratt.readtab VALUES (1,'Hello');
 
1 row created.

SQL> INSERT INTO oratt.readtab VALUES (2,'World');
 
1 row created.

SQL> INSERT INTO oratt.writetab VALUES (100, 'TimesTen');
 
1 row created.

SQL> INSERT INTO oratt.writetab VALUES (101, 'Cache');
 
1 row created.

SQL> commit;
 
Commit complete.

    Verify the rows have been inserted into the tables.

    SQL> SELECT * FROM oratt.readtab;
 
    KEYVAL STR
---------- --------------------------------
         1 Hello
         2 World
 
SQL>  SELECT * FROM oratt.writetab;
 
        PK ATTR
---------- ----------------------------------------
       100 TimesTen
       101 Cache

  3. Use SQL*Plus to grant the SELECT privilege on the oratt.readtab table and the SELECT, INSERT, UPDATE, and DELETE privileges on the oratt.writetab table to the cache administration user (cacheuser2, in this example).

    SQL> GRANT SELECT ON oratt.readtab TO cacheuser2;
 
Grant succeeded.
 
SQL> GRANT SELECT ON oratt.writetab TO cacheuser2;
 
Grant succeeded.
 
SQL> GRANT INSERT ON oratt.writetab TO cacheuser2;
 
Grant succeeded.
 
SQL> GRANT UPDATE ON oratt.writetab TO cacheuser2;
 
Grant succeeded.
 
SQL> GRANT DELETE ON oratt.writetab TO cacheuser2;
 
Grant succeeded.

  4. Use SQL*Plus to query the nls_database_parameters system view to determine the Oracle Database database character set. The Oracle Database database character set must match the TimesTen database character set. (The TimesTen database character set will be set later. See "Creating the metadata files and the Kubernetes facility" for details.)

    In this example, the query returns the AL32UTF8 database character set.

    SQL> SELECT value FROM nls_database_parameters WHERE 
       parameter='NLS_CHARACTERSET';
 
VALUE
------------------------------------------------------------------------------
AL32UTF8

You have successfully created the Oracle Database tables that will be cached in the TimesTen cache group tables.

Creating the metadata files and the Kubernetes facility

There are metadata files that are specific to using TimesTen Cache:

  • cacheUser: This file is required. The user in this file is created in the TimesTen databases and serves as the cache manager. The name of this user must match the name of the cache administration user that you created in the Oracle Database. See "Create the Oracle Database users" for information on the cache administration user in the Oracle Database. Also see "cacheUser" for more information on the cacheUser metadata file.

  • cachegroups.sql: This file is required. The contents of this file contain the CREATE CACHE GROUP definitions. The file can also contain the LOAD CACHE GROUP statement and the built-in procedures to update statistics on the cache group tables (such as, ttOptEstimateStats and ttOptUpdateStats). See "cachegroups.sql" for more information on this file.

  • tnsnames.ora: This file is required. It defines Oracle Net Services to which applications connect. For TimesTen Cache, this file configures the connectivity between TimesTen and the Oracle Database (from which data is being cached). In this context, TimesTen is the application that is the connection to the Oracle Database. See "tnsnames.ora file" for more information on this file.

  • sqlnet.ora: This file may be required. It may be necessary depending on your Oracle Database configuration. The file defines options for how client applications communicate with the Oracle Database. In this context, TimesTen is the application. The tnsnames.ora and sqlnet.ora files together define how an application communicates with the Oracle Database. See "sqlnet.ora file" for information on this file.

  • db.ini: This file is required if you are using TimesTen Cache. The contents of this file contain TimesTen connection attributes for your TimesTen databases, which will be included in TimesTen's sys.odbc.ini file. For TimesTen Cache, you must specify the OracleNetServiceName and the DatabaseCharacterSet connection attributes. The DatabaseCharacterSet connection attribute must match the Oracle database character set. See "db.ini file" for more information on this file.

  • schema.sql: The contents of this file contain database objects, such as tables, sequences, and users. The instance administrator uses the ttIsql utility to run this file immediately after the database is created. This file is run before the Operator configures TimesTen Cache or replication, so ensure there are no cache definitions in this file.

    In TimesTen Cache, one or more cache table users own the cache tables. If this cache table user is not the cache manager user, then you must specify the schema.sql file and in it you must include the schema user and assign the appropriate privileges to this schema user. For example, if the oratt schema user was created in the Oracle Database, and this user is not the TimesTen cache manager user, you must create the TimesTen oratt user in this file. See "Create the Oracle Database users" for more information on the schema users in the Oracle Database. Also see "schema.sql file" for more information on the schema.sql file.

In addition, you can use these other supported metadata files:

  • adminUser: The user in this file is created in the TimesTen databases and is granted ADMIN privileges. See "adminUser file" for more information on this file.

  • epilog.sql: The contents of this file contain operations that must be performed after the Operator configures replication. For example, if you are using XLA, you could create replicated bookmarks for XLA in this file. This file is run after cache and replication have been configured. See "epilog.sql" for more information on this file.

You can include these metadata files in one or more Kubernetes facilities (for example, in a Kubernetes Secret, in a ConfigMap, or in an init container). This ensures the metadata files are populated in the /ttconfig directory of the TimesTen containers. Note that there is no requirement as to how to get the metadata files into this /ttconfig directory. See "Populating the /ttconfig directory" for more information.

This example uses the ConfigMap facility to populate the /ttconfig directory in your TimesTen containers. The adminUser, db.ini, schema.sql, cacheUser, cachegroups.sql, tnsnames.ora, and sqlnet.ora metadata files are used in this example.

On your Linux development host:

  1. From the directory of your choice, create an empty subdirectory for the metadata files. This example creates the cm_cachetest subdirectory. (The cm_cachetest directory is used in the remainder of this example to denote this directory.)

    % mkdir -p cm_cachetest

  2. Navigate to the ConfigMap directory.

    % cd cm_cachetest

  3. Create the adminUser file in this ConfigMap directory (cm_cachetest, in this example). In this adminUser file, create the scott user with the tiger password.

    vi adminUser

    scott/tiger

  4. Create the db.ini file in this ConfigMap directory (cm_cachetest, in this example). In this db.ini file, define the PermSize, DatabaseCharacterSet, and the OracleNetServiceName connection attributes. The DatabaseCharacterSet value must match the database character set value in the Oracle Database. See "Create the Oracle Database tables to be cached" for information on how to query the nls_database_parameters system view to determine the Oracle Database database character set. In this example, the value is AL32UTF8.

    vi db.ini

    PermSize=200
DatabaseCharacterSet=AL32UTF8
OracleNetServiceName=Oracache

  5. Create the schema.sql file in this ConfigMap directory (cm_cachetest, in this example). In this example, create the oratt user. Recall that this user was previously created in the Oracle Database. See "Create the Oracle Database users" for information on the oratt user in the Oracle Database.

    vi schema.sql

create user oratt identified by ttpwd;
grant admin to oratt;

  6. Create the cacheUser metadata file in this ConfigMap directory (cm_cachetest, in this example). The cacheUser file must contain one line of the form cacheuser/ttpassword/orapassword, where cacheuser is the user you wish to designate as the cache manager in the TimesTen database, ttpassword is the TimesTen password you wish to assign to this user, and orapassword is the Oracle Database password that has already been assigned to the Oracle Database cache administration user. Note that the cacheUser name in this file must match the Oracle Database cache administration user that you previously created. See "Create the Oracle Database users" for more information on the Oracle Database cache administration user.

    In this example, the cacheuser2 user with password of oraclepwd was already created in the Oracle Database. Therefore, supply cacheuser2 as the TimesTen cache manager user. You can assign any TimesTen password to this TimesTen cache manager user. This example assigns ttpwd.

    vi cacheuser

cacheuser2/ttpwd/oraclepwd

  7. Create the cachegroups.sql metadata file in this ConfigMap directory (cm_cachetest, in this example). The cachegroups.sql file contains the cache group definitions. In this example, a dynamic AWT cache group and a read-only cache group are created. In addition, the LOAD CACHE GROUP statement is included to load rows from the oratt.readtab cached table in the Oracle Database into the oratt.readtab cache table in the TimesTen database.

    vi cachegroups.sql

CREATE DYNAMIC ASYNCHRONOUS WRITETHROUGH CACHE GROUP writecache
FROM oratt.writetab (
  pk NUMBER NOT NULL PRIMARY KEY,
  attr VARCHAR2(40)
);
 
CREATE READONLY CACHE GROUP readcache
AUTOREFRESH
  INTERVAL 5 SECONDS
FROM oratt.readtab (
  keyval NUMBER NOT NULL PRIMARY KEY,
  str VARCHAR2(32)
);
 
LOAD CACHE GROUP readcache COMMIT EVERY 256 ROWS;

  8. Create the tnsnames.ora metadata file in this ConfigMap directory (cm_cachetest, in this example).

    vi tnsnames.ora

OraTest =   
 (DESCRIPTION = 
   (ADDRESS = (PROTOCOL = TCP)(HOST = database.myhost.svc.cluster.local)
     (PORT = 1521))     
   (CONNECT_DATA =       
     (SERVER = DEDICATED)       
     (SERVICE_NAME = OraTest.my.domain.com)))
OraCache =  
 (DESCRIPTION =   
   (ADDRESS = (PROTOCOL = TCP)(HOST = database.myhost.svc.cluster.local)
     (PORT = 1521))     
   (CONNECT_DATA =       
     (SERVER = DEDICATED)       
     (SERVICE_NAME = OraCache.my.domain.com)))

  9. Create the sqlnet.ora metadata file in this ConfigMap directory (cm_cachetest, in this example).

    vi sqlnet.ora

NAME.DIRECTORY_PATH= {TNSNAMES, EZCONNECT, HOSTNAME}
SQLNET.EXPIRE_TIME = 10
SSL_VERSION = 1.2

  10. Use the Linux ls command to verify the metadata files are in the ConfigMap directory (cm_cachetest, in this example).

    % ls
adminUser        cacheUser  schema.sql  tnsnames.ora
cachegroups.sql  db.ini     sqlnet.ora

  11. Create the ConfigMap. The files in the cm_cachetest directory are included in the ConfigMap and, later, will be available in the TimesTen containers.

    In this example:

    • The name of the ConfigMap is cachetest. Replace cachetest with a name of your choosing. (cachetest is represented in bold in this example.)

    • This example uses cm_cachetest as the directory where the files that will be copied into the ConfigMap reside. If you use a different directory, replace cm_cachetest with the name of your directory. (cm_cachetest is represented in bold in this example.)

    Use the kubectl create command to create the ConfigMap:

    % kubectl create configmap cachetest --from-file=cm_cachetest
configmap/cachetest created

  12. Use the kubectl describe command to verify the contents of the ConfigMap. (cachetest, in this example.) The metadata files are represented in bold.

    % kubectl describe configmap cachetest;
Name:         cachetest
Namespace:    mynamespace
Labels:       <none>
Annotations:  <none>
 
Data
====
tnsnames.ora:
----

OraTest =   
 (DESCRIPTION = 
   (ADDRESS = (PROTOCOL = TCP)(HOST = database.myhost.svc.cluster.local)
     (PORT = 1521))     
   (CONNECT_DATA =       
     (SERVER = DEDICATED)       
     (SERVICE_NAME = OraTest.my.domain.com)))
OraCache =  
 (DESCRIPTION =   
   (ADDRESS = (PROTOCOL = TCP)(HOST = database.myhost.svc.cluster.local)
     (PORT = 1521))     
   (CONNECT_DATA =       
     (SERVER = DEDICATED)       
     (SERVICE_NAME = OraCache.my.domain.com)))
 
adminUser:
----
scott/tiger
 
cacheUser:
----
cacheuser2/ttpwd/oraclepwd
 
cachegroups.sql:
----
CREATE DYNAMIC ASYNCHRONOUS WRITETHROUGH CACHE GROUP writecache
FROM oratt.writetab (
  pk NUMBER NOT NULL PRIMARY KEY,
  attr VARCHAR2(40)
);
 
CREATE READONLY CACHE GROUP readcache
AUTOREFRESH
  INTERVAL 5 SECONDS
FROM oratt.readtab (
  keyval NUMBER NOT NULL PRIMARY KEY,
  str VARCHAR2(32)
);
 
LOAD CACHE GROUP readcache COMMIT EVERY 256 ROWS;
 
db.ini:
----
permSize=200
databaseCharacterSet=AL32UTF8
oracleNetServiceName=Oracache
 
schema.sql:
----
create user oratt identified by ttpwd;
grant admin to oratt;
 
sqlnet.ora:
----
NAME.DIRECTORY_PATH= {TNSNAMES, EZCONNECT, HOSTNAME}
SQLNET.EXPIRE_TIME = 10
SSL_VERSION = 1.2
 
 
Events:  <none>

You have successfully created and deployed the cachetest ConfigMap.

Creating the TimesTenClassic object

This section creates the TimesTenClassic object. See "Defining and creating the TimesTenClassic object" and "The TimesTenClassic object type" for detailed information on the TimesTenClassic object.

Perform these steps:

  1. Create an empty YAML file. You can choose any name, but you may want to use the same name you used for the name of the TimesTenClassic object. (In this example, cachetest.) The YAML file contains the definitions for the TimesTenClassic object. See "TimesTenClassicSpecSpec" for information on the fields that you must specify in this YAML file as well as the fields that are optional.

    In this example, note these fields:

    • name: Replace cachetest with the name of your TimesTenClassic object (represented in bold).

    • storageClassName: Replace oci with the name of the storage class used to allocate PersistentVolumes to hold TimesTen.

    • storageSize: Replace 250G with the amount of storage that should be requested for each Pod to hold TimesTen. Note: This example assumes a production environment and uses a value of 250G for storageSize. For demonstration purposes, a value of 50G is adequate.

    • image: Replace phx.ocir.io/youraccount/tt1814110:3 with the location of the image registry (phx.ocir.io/youraccount) and the image containing TimesTen (tt1814110:3).

    • imagePullSecret: Replace sekret with the image pull secret that Kubernetes should use to fetch the TimesTen image.

    • dbConfigMap: This example uses one ConfigMap (called cachetest) for the metadata files (represented in bold).

    % vi cachetest.yaml

apiVersion: timesten.oracle.com/v1
kind: TimesTenClassic
metadata:
  name: cachetest
spec:
  ttspec:
    storageClassName: oci
    storageSize: 250G
    image: phx.ocir.io/youraccount/tt1814110:3
    imagePullSecret: sekret
    imagePullPolicy: Always
    dbConfigMap:
    - cachetest

  2. Use the kubectl create command to create the TimesTenClassic object from the contents of the YAML file (in this example, cachetest.yaml). Doing so begins the process of deploying your active standby pair of TimesTen databases in the Kubernetes cluster.

    % kubectl create -f cachetest.yaml
timestenclassic.timesten.oracle.com/cachetest created

You have successfully created the TimesTenClassic object in the Kubernetes cluster. The process of deploying your TimesTen databases begins, but is not yet complete.

Monitoring the deployment of the TimesTenClassic object

Use the kubectl get and the kubectl describe commands to monitor the progress of the active standby pair as it is provisioned.

  1. Use the kubectl get command and review the STATE field. Observe the value is Initializing. The active standby pair provisioning has begun, but is not yet complete.

    % kubectl get ttc cachetest
NAME        STATE          ACTIVE   AGE
cachetest   Initializing   None     41s

  2. Use the kubectl get command again to see if value of the STATE field has changed. In this example, the value is Normal, indicating the active standby pair of databases are now provisioned and the process is complete.

    % kubectl get ttc cachetest
NAME        STATE    ACTIVE        AGE
cachetest   Normal   cachetest-0   3m58s

  3. Use the kubectl describe command to view the active standby pair provisioning in detail.

    Note the following:

    • The cachetest Configmap has been correctly referenced in the dbConfigMap field (represented in bold).

    • The cache agent is running in the active and the standby Pods (represented in bold).

    • The cache administration user UID and password have been set in the active and the standby Pods (represented in bold).

    • Two cache groups have been created in the active and the standby Pods (represented in bold).

    • The replication agent is running in the active and standby Pods (represented in bold).

    % kubectl describe ttc cachetest
Name:         cachetest
Namespace:    mynamespace
Labels:       <none>
Annotations:  <none>
API Version:  timesten.oracle.com/v1
Kind:         TimesTenClassic
Metadata:
  Creation Timestamp:  2020-10-24T03:29:48Z
  Generation:          1
  Resource Version:    78390500
  Self Link:           /apis/timesten.oracle.com/v1/namespaces/mynamespace/timestenclassics/cachetest
  UID:                 2b18d81d-15a9-11eb-b999-be712d29a81e
Spec:
  Ttspec:
    Db Config Map:
      cachetest
    Image:               phx.ocir.io/youraccount/tt1814110:3
    Image Pull Policy:   Always
    Image Pull Secret:   sekret
    Storage Class Name:  oci
    Storage Size:        250G
Status:
  Active Pods:       cachetest-0
  High Level State:  Normal
  Last Event:        28
  Pod Status:
    Cache Status:
      Cache Agent:        Running
      Cache UID Pwd Set:  true
      N Cache Groups:     2
    Db Status:
      Db:            Loaded
      Db Id:         30
      Db Updatable:  Yes
    Initialized:     true
    Pod Status:
      Agent:                Up
      Last Time Reachable:  1603510527
      Pod IP:               10.244.7.92
      Pod Phase:            Running
    Replication Status:
      Last Time Rep State Changed:  0
      Rep Agent:                    Running
      Rep Peer P State:             start
      Rep Scheme:                   Exists
      Rep State:                    ACTIVE
    Times Ten Status:
      Daemon:          Up
      Instance:        Exists
      Release:         18.1.4.11.0
    Admin User File:   true
    Cache User File:   true
    Cg File:           true
    High Level State:  Healthy
    Intended State:    Active
    Name:              cachetest-0
    Schema File:       true
    Cache Status:
      Cache Agent:        Running
      Cache UID Pwd Set:  true
      N Cache Groups:     2
    Db Status:
      Db:            Loaded
      Db Id:         30
      Db Updatable:  No
    Initialized:     true
    Pod Status:
      Agent:                Up
      Last Time Reachable:  1603510527
      Pod IP:               10.244.8.170
      Pod Phase:            Running
    Replication Status:
      Last Time Rep State Changed:  1603510411
      Rep Agent:                    Running
      Rep Peer P State:             start
      Rep Scheme:                   Exists
      Rep State:                    STANDBY
    Times Ten Status:
      Daemon:            Up
      Instance:          Exists
      Release:           18.1.4.11.0
    Admin User File:     true
    Cache User File:     true
    Cg File:             true
    High Level State:    Healthy
    Intended State:      Standby
    Name:                cachetest-1
    Schema File:         true
  Rep Create Statement:  create active standby pair "cachetest" on 
  "cachetest-0.cachetest.mynamespace.svc.cluster.local", "cachetest" on 
  "cachetest-1.cachetest.mynamespace.svc.cluster.local" NO RETURN store 
  "cachetest" on "cachetest-0.cachetest.mynamespace.svc.cluster.local" 
  PORT 4444 FAILTHRESHOLD 0 store "cachetest" on 
 "cachetest-1.cachetest.mynamespace.svc.cluster.local" PORT 4444 
 FAILTHRESHOLD 0
  Rep Port:              4444
  Status Version:        1.0
Events:
  Type  Reason       Age    From       Message
  ----  ------       ----   ----       -------
  -     Create       5m40s  ttclassic  Secret 
tt2b18d81d-15a9-11eb-b999-be712d29a81e created
  -     Create       5m40s  ttclassic  Service cachetest created
  -     Create       5m40s  ttclassic  StatefulSet cachetest created
  -     StateChange  4m28s  ttclassic  Pod cachetest-0 Agent Up
  -     StateChange  4m28s  ttclassic  Pod cachetest-0 Release 18.1.4.11.0
  -     StateChange  4m28s  ttclassic  Pod cachetest-0 Daemon Up
  -     StateChange  3m18s  ttclassic  Pod cachetest-0 RepScheme None
  -     StateChange  3m18s  ttclassic  Pod cachetest-0 RepAgent Not Running
  -     StateChange  3m18s  ttclassic  Pod cachetest-0 RepState IDLE
  -     StateChange  3m18s  ttclassic  Pod cachetest-0 Database Loaded
  -     StateChange  3m18s  ttclassic  Pod cachetest-0 Database Updatable
  -     StateChange  3m18s  ttclassic  Pod cachetest-0 CacheAgent Not Running
  -     StateChange  2m57s  ttclassic  Pod cachetest-0 CacheAgent Running
  -     StateChange  2m47s  ttclassic  Pod cachetest-1 Agent Up
  -     StateChange  2m47s  ttclassic  Pod cachetest-1 Release 18.1.4.11.0
  -     StateChange  2m46s  ttclassic  Pod cachetest-0 RepAgent Running
  -     StateChange  2m46s  ttclassic  Pod cachetest-0 RepScheme Exists
  -     StateChange  2m46s  ttclassic  Pod cachetest-0 RepState ACTIVE
  -     StateChange  2m46s  ttclassic  Pod cachetest-1 Daemon Up
  -     StateChange  2m9s   ttclassic  Pod cachetest-1 CacheAgent Running
  -     StateChange  2m9s   ttclassic  Pod cachetest-1 Database Not Updatable
  -     StateChange  2m9s   ttclassic  Pod cachetest-1 Database Loaded
  -     StateChange  2m9s   ttclassic  Pod cachetest-1 RepAgent Not Running
  -     StateChange  2m9s   ttclassic  Pod cachetest-1 RepScheme Exists
  -     StateChange  2m9s   ttclassic  Pod cachetest-1 RepState IDLE
  -     StateChange  2m3s   ttclassic  Pod cachetest-1 RepAgent Running
  -     StateChange  118s   ttclassic  Pod cachetest-1 RepState STANDBY
  -     StateChange  118s   ttclassic  TimesTenClassic was Initializing, now 
Normal

Your active standby pair of TimesTen databases are successfully deployed (as indicated by Normal.) You are now ready to verify that TimesTen Cache is configured correctly and is working properly.

Verifying that TimesTen Cache is configured correctly

To verify that TimesTen Cache is configured correctly and is working properly, perform the following steps:

  1. Review the active (cachetest-0, in this example) Pod and the standby Pod (cachetest-1, in this example).

    % kubectl get pods
NAME                                       READY   STATUS    RESTARTS   AGE
cachetest-0                                2/2     Running   0          8m16s
cachetest-1                                2/2     Running   0          8m15s
timestenclassic-operator-f84766548-tch7s   1/1     Running   0          36d

  2. Use the kubectl exec -it command to invoke the shell in the active Pod (cachetest-0, in this example).

    % kubectl exec -it cachetest-0 -c tt -- /usr/bin/su - oracle

  3. Use ttIsql to connect to the cachetest database. Confirm the TimesTen connection attributes are correct. In particular, note that the OracleNetServiceName connection attribute is correctly set to Oracache (represented in bold).

    % ttIsql cachetest;
 
Copyright (c) 1996, 2020, Oracle and/or its affiliates. All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.
 
 
 
connect "DSN=cachetest";
Connection successful: DSN=cachetest;UID=oracle;DataStore=/tt/home/oracle/datastore/cachetest;
DatabaseCharacterSet=AL32UTF8;ConnectionCharacterSet=US7ASCII;AutoCreate=0;
PermSize=200;OracleNetServiceName=Oracache;DDLReplicationLevel=3;
ForceDisconnectEnabled=1;
(Default setting AutoCommit=1)

  4. Use the ttIsql cachegroups to view the definition of the cacheuser2.readcache and the cacheuser2.writecache cache groups.

    Command> cachegroups;
 
Cache Group CACHEUSER2.READCACHE:
 
  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: ORATT.READTAB
  Table Type: Read Only
 
Cache Group CACHEUSER2.WRITECACHE:
 
  Cache Group Type: Asynchronous Writethrough (Dynamic)
  Autorefresh: No
  Aging: LRU on
 
  Root Table: ORATT.WRITETAB
  Table Type: Propagate
 
2 cache groups found.

  5. Use ttIsql to query the oratt.readtab cache table. Note that the data from the oratt.readtab cached table in the Oracle Database is correctly loaded in the oratt.readcache cache table in the TimesTen database. Recall that you specified the LOAD CACHE GROUP statement in the cachegroups.sql metadata file. See "Creating the metadata files and the Kubernetes facility" for information on this cachegroups.sql metadata file.

    Command> SELECT * FROM oratt.readtab;
< 1, Hello >
< 2, World >
2 rows found.

You have verified that the cache groups were created and data was correctly loaded in the oratt.readtab table.

Performing operations on the cache group tables

The examples in this section perform operations on the oratt.readtab and the oratt.writetab tables to verify that TimesTen Cache is working properly.

Perform operations on the oratt.readtab table

This section performs operations on the oratt.readtab table.

  1. Create a shell from which you can access your Oracle Database and then use SQL*Plus to connect to the Oracle Database as the schema user (oratt, in this example). Then, insert a new row, delete an existing row, and update an existing row in the oratt.readtab table of the Oracle Database and commit the changes.

    % sqlplus oratt/oraclepwd@oracache;
 
SQL*Plus: Release 12.1.0.2.0 Production on Fri Oct 23 21:57:42 2020
 
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> INSERT INTO oratt.readtab VALUES (3,'Welcome');
 
1 row created.
 
SQL> DELETE FROM oratt.readtab WHERE keyval=2;
 
1 row deleted.
 
SQL> UPDATE oratt.readtab SET str='Hi' WHERE keyval=1;
 
1 row updated.
 
SQL> COMMIT;
 
Commit complete.

    Since the read-only cache group was created with an autorefresh interval of 5 seconds, the TimesTen oratt.readtab cache table in the readcache cache group is automatically refreshed after 5 seconds with the committed updates from the cached oratt.readtab table of the Oracle Database. The next step is to test that the data was correctly propagated from the Oracle Database to the TimesTen database.

  2. Use the kubectl exec -it command to invoke the shell in the container of the Pod that is running the TimesTen active database (cachetest-0, in this example).

    % kubectl exec -it cachetest-0 -c tt -- /usr/bin/su - oracle

  3. Use the TimesTen ttIsql utility to connect to the cachetest database. Query the TimesTen oratt.readtab table to verify that the table has been updated with the committed updates from the Oracle Database.

    % ttIsql cachetest;
 
Copyright (c) 1996, 2020, Oracle and/or its affiliates. All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.
 
 
 
connect "DSN=cachetest";
Connection successful: DSN=cachetest;UID=oracle;DataStore=/tt/home/oracle/datastore/cachetest;
DatabaseCharacterSet=AL32UTF8;ConnectionCharacterSet=US7ASCII;AutoCreate=0;
PermSize=200;OracleNetServiceName=Oracache;DDLReplicationLevel=3;
ForceDisconnectEnabled=1;
(Default setting AutoCommit=1)

Command> SELECT * FROM oratt.readtab;
< 1, Hi >
< 3, Welcome >
2 rows found.

You have verified that TimesTen Cache is working correctly for the oratt.readtab table and the readcache cachegroup.

Perform operations on the oratt.writetab table

This example performs operations on the oratt.writetab table.

  1. Use the kubectl exec -it command to invoke the shell in the container of the Pod that is running the TimesTen active database (cachetest-0, in this example).

    % kubectl exec -it cachetest-0 -c tt -- /usr/bin/su - oracle

  2. Use the TimesTen ttIsql utility to connect to the cachetest database as the cache manager user (cacheuser2, in this example). Issue a SELECT statement on the TimesTen oratt.writetab table. Recall that the writecache cache group is a dynamic cache group. Thus by issuing the SELECT statement, the cache instance is automatically loaded from the cached Oracle Database table, if the data is not found in the TimeTen cache table.

    % ttIsql "DSN=cachetest;UID=cacheuser2;PWD=ttpwd;OraclePWD=oraclepwd";
 
Copyright (c) 1996, 2020, Oracle and/or its affiliates. All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.
 
 
 
connect "DSN=cachetest;UID=cacheuser2;PWD=********;OraclePWD=********";
Connection successful: DSN=cachetest;UID=cacheuser2;DataStore=/tt/home/oracle/datastore/cachetest;
DatabaseCharacterSet=AL32UTF8;ConnectionCharacterSet=US7ASCII;AutoCreate=0;
PermSize=200;OracleNetServiceName=Oracache;DDLReplicationLevel=3;
ForceDisconnectEnabled=1;
(Default setting AutoCommit=1)

Command> SELECT * FROM oratt.writetab WHERE pk=100;
< 100, TimesTen >
1 row found.

  3. Use ttIsql to insert a new row, delete an existing row, and update an existing row in the TimesTen oratt.writetab cache table, and commit the changes.

    Command> INSERT INTO oratt.writetab VALUES (102,'Cache');
1 row inserted.
Command> DELETE FROM oratt.writetab WHERE pk=101;
1 row deleted.
Command> UPDATE oratt.writetab SET attr='Oracle' WHERE pk=100;
1 row updated.
Command> COMMIT;

    The committed updates on the TimesTen oratt.writetab cache table in the writecache cache group should automatically be propagated to the oratt.writetab table in the Oracle Database.

  4. Create a shell from which you can access your Oracle Database and then use SQL*Plus to connect to the Oracle database as the schema user (oratt, in this example). Then query the contents of the oratt.writetab table in the Oracle Database to verify the committed updates from the TimesTen database have been propagated to the oratt.writetab table of the Oracle Database.

    % sqlplus oratt/oraclepwd@orapcache;
 
SQL*Plus: Release 12.1.0.2.0 Production on Fri Oct 23 21:57:42 2020
 
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> SELECT * FROM oratt.writetab ORDER BY pk;
 
        PK ATTR
---------- ----------------------------------------
       100 Oracle
       102 Cache

You have verified that TimesTen Cache is working correctly for the oratt.writetab table and the writecache cachegroup.

Cleaning up the cache metadata on the Oracle Database

When you create certain types of cache groups in a TimesTen database, TimesTen stores metadata about that cache group in the Oracle Database. If you later delete that TimesTen database, TimesTen does not automatically delete the metadata in the Oracle Database. As a result, metadata can accumulate on the Oracle Database. See "Dropping Oracle Database objects used by autorefresh cache groups" in the Oracle TimesTen Application-Tier Database Cache User's Guide for more information.

However, in a Kubernetes environment, if you provide a cacheUser metadata file and a cachegroups.sql metadata file when you initially create the TimesTenClassic object, then, by default, the Operator automatically cleans up the Oracle Database metadata if you delete that TimesTenClassic object.

If you do not want the Operator to automatically clean up the Oracle Database, you set the cacheCleanup field in the TimesTenClassic object definition to false. See the cacheCleanup entry in Table 11-3, "TimesTenClassicSpecSpec" for more information. Also see "The supported metadata files" for information on the cacheUser and the cachegroups.sql files.