A Example for Deploying a Grid and Database

Ensure that TimesTen Scaleout supports the OS installed on each host

Once you know which systems you are going to use as hosts in your grid, ensure that TimesTen Scaleout supports the platform and operating system installed on each host. All hosts must run the same platform and OS version and release.

For a list of the operating systems that TimesTen Scaleout supports, see the Oracle TimesTen In-Memory Database Release Notes that are located in the installation directory.

Configure all hosts in the same internal network

Create a single internal network for all hosts to communicate with each other. Client connections to the database may be handled through a external network, if available.

See "Network requirements" for more information.

Create a TimesTen user group and OS user

Create the GID for the TimesTen users group and the username and UID for the role of instance administrator. Ensure that they exist and are the same on all hosts.

% sudo groupadd -g 10000 timesten
% sudo useradd -u 55000 -g timesten instanceadmin
% sudo passwd instanceadmin

See "Understanding the TimesTen users group and the operating system user" for more information.

Set the Linux system kernel parameters

Configure the following parameters of the system kernel on all hosts with a data instance. These values are based on your database requirements:

% sudo vi /etc/sysctl.conf

...
kernel.shmmax=51539607552
kernel.shmall=14680064
vm.nr_hugepages=24576
vm.hugetlb_shm_group=10000
...

Enable these settings without restarting on all modified hosts. Consider that the HugePages parameters may require a system reboot to take full effect.

sudo /sbin/sysctl -p

See "Configure shmmax and shmall" and "Configure HugePages" for more information on how you calculate the values for these parameters.

Set the memlock settings for the instance administrator

Set the recommended memlock settings for the instance administrator based on the shared memory segment of each host.

% sudo vi /etc/security/limits.conf

...
instanceadmin soft   memlock 50331648
instanceadmin hard   memlock 50331648
...

See "Modify the memlock settings" for more information on how you calculate the values for these parameters.

Set the semaphore values

Configure the semaphore values of the system kernel on all hosts based on your database requirements:

% sudo vi /etc/sysctl.conf

...
kernel.sem = 4000 400000 2000 2560
...

Enable this setting without restarting the system on all modified hosts.

sudo /sbin/sysctl -p

See "Set the semaphore values" for more information on how you calculate the values for this parameter.

Set passwordless SSH between all hosts

Use the ttGridAdmin gridSshConfig command to set up the required passwordless SSH access between the internal network addresses of all hosts for the instance administrator.

% /grid/tt18.1.4.1.0/bin/ttGridAdmin gridSshConfig
 -mgmtAddress int-host1 int-host2
 -dataAddress int-host3 int-host4 int-host5 int-host6 int-host7 int-host8

See "Setting passwordless SSH" for more information.

Install ZooKeeper

Unpack Apache ZooKeeper on each system that you defined for the role of a membership server.

% mkdir -p /grid/membership
% tar -zvxf apache-zookeeper-3.5.8-bin.tar.gz -C /grid/membership

Configure the ZooKeeper servers

Once the installation files are available on all the systems defined as membership servers, create the zoo.cfg and myid configuration files on those systems.

% vi /grid/membership/apache-zookeeper-3.5.8-bin/conf/zoo.cfg

tickTime=250
initLimit=40
syncLimit=12
dataDir=grid/membership/apache-zookeeper-3.5.8-bin/data
clientPort=2181
server.1=ms-host1:2888:3888
server.2=ms-host2:2888:3888
server.3=ms-host3:2888:3888
autopurge.snapRetainCount=3
autopurge.purgeInterval=1
4lw.commands.whitelist=stat, ruok, conf, isro

Ensure that in the myid file you assign the same n value as in the server.n parameter of the zoo.cfg file. For example, since the ms-host1 system is identified as server.1 in the zoo.cfg file, then the myid file of that system must contain a single line with a 1.

% vi /grid/membership/apache-zookeeper-3.5.8-bin/conf/myid

1

Also, create the location specified for the dataDir parameter.

% mkdir -p /grid/membership/apache-zookeeper-3.5.8-bin/data

See "Configuring Apache ZooKeeper as the membership service" for more information on the parameters included in the zoo.cfg and myid configuration files.

Start the ZooKeeper servers

Start the ZooKeeper server on all the systems that you defined for the role of a membership server.

% /grid/membership/apache-zookeeper-3.5.8-bin/bin/zkServer.sh start

If you want to verify that ZooKeeper is running properly, use:

% /grid/membership/apache-zookeeper-3.5.8-bin/bin/zkCli.sh -server ms-host1:2181

Create the client configuration file

The client configuration file identifies the host names and client TCP/IP ports of all membership servers.

Create a client configuration file in a directory on the system defined as the host of the active management instance, as shown in Example A-1.

% vi /mydir/membership.conf

Servers ms-host1!2181,ms-host2!2181,ms-host3!2181

Create a database definition file

The database definition file (suffix of .dbdef) contains the data store and first connection attributes of a database. You must name the file as database_name.dbdef. For example, for a database named database1, the database definition file would be database1.dbdef.

Create a database definition file in a directory on the system defined as the host of the active management instance, as shown in Example A-2.

% vi /mydir/database1.dbdef

[database1]
DataStore=/disk1/databases/database1
LogDir=/disk2/logs
DatabaseCharacterSet=AL32UTF8
Durability=0
PermSize=32768
TempSize=4096
LogBufMB=1024
Connections=2048

See "Creating a database definition file" for more information on the database definition file.

Create a connectable file

The connectable file (suffix of .connect) contains the general connection attributes for a connection to a database. TimesTen Scaleout supports connectables that can be either for direct or client/server connections to the database.

Create a connectable file in a directory on the system defined as the host of the active management instance, as shown in Example A-3.

% vi /mydir/database1CS.connect

ConnectionCharacterSet=AL32UTF8

See "Creating a connectable file" for more information on the connectable file.

Create a SQL script file for your database

The SQL script file contains the SQL statements to create SQL objects for your database.

Create a SQL script file in a directory on the system defined as the host of the active management instance, as shown in Example A-4.

% vi /mydir/database1.sql

CREATE USER terry IDENTIFIED BY password;
 
GRANT CREATE SESSION TO terry;
 
CREATE TABLE terry.account_type
(
    type            CHAR(1) NOT NULL PRIMARY KEY,
    description     VARCHAR2(100) NOT NULL
)
DUPLICATE;
 
CREATE TABLE terry.account_status
(
    status          NUMBER(2,0) NOT NULL PRIMARY KEY,
    description     VARCHAR2(100) NOT NULL
)
DUPLICATE;
 
CREATE TABLE terry.customers
(
    cust_id            NUMBER(10,0) NOT NULL PRIMARY KEY,
    first_name         VARCHAR2(30) NOT NULL,
    last_name          VARCHAR2(30) NOT NULL,
    addr1              VARCHAR2(64),
    addr2              VARCHAR2(64),
    zipcode            VARCHAR2(5),
    member_since       DATE NOT NULL
)
DISTRIBUTE BY HASH;
 
CREATE TABLE terry.accounts
(
    account_id         NUMBER(10,0) NOT NULL PRIMARY KEY,
    phone              VARCHAR2(16) NOT NULL,
    account_type       CHAR(1) NOT NULL,
    status             NUMBER(2,0) NOT NULL,
    current_balance    NUMBER(10,2) NOT NULL,
    prev_balance       NUMBER(10,2) NOT NULL,
    date_created       DATE NOT NULL,
    cust_id            NUMBER(10,0) NOT NULL,
    CONSTRAINT fk_customer
        FOREIGN KEY (cust_id)
            REFERENCES terry.customers(cust_id),
    CONSTRAINT fk_acct_type
        FOREIGN KEY (account_type)
            REFERENCES terry.account_type(type),
    CONSTRAINT fk_acct_status
        FOREIGN KEY (status)
            REFERENCES terry.account_status(status)
)
DISTRIBUTE BY REFERENCE (fk_customer);
 
CREATE TABLE terry.transactions
(
    transaction_id      NUMBER(10,0) NOT NULL,
    account_id          NUMBER(10,0) NOT NULL ,
    transaction_ts      TIMESTAMP NOT NULL,
    description         VARCHAR2(60),
    optype              CHAR(1) NOT NULL,
    amount              NUMBER(6,2) NOT NULL,
    PRIMARY KEY (account_id, transaction_id, transaction_ts),
    CONSTRAINT fk_accounts
        FOREIGN KEY (account_id)
            REFERENCES terry.accounts(account_id)
)
DISTRIBUTE BY REFERENCE (fk_accounts);
 
CREATE SEQUENCE terry.txn_seq CACHE 100 BATCH 1000000;

Create a configuration file for the ttGridRollout utility

The configuration file for the ttGridRollout utility defines all the necessary parameters to successfully create and deploy a grid and database in TimesTen Scaleout.

Create a configuration file for the ttGridRollout utility, as shown in Example A-5. The configuration file in Example A-5:

• Names the grid as grid1.

• Defines the membership servers provided by the membership.conf file.

• Defines the location for the installation files for every installation object as /grid/tt18.1.4.1.0 on their respective host.

• Defines the location for the instance files of every instance object as /grid on their respective hosts.

• Creates the database definition provided by the database1.dbdef file.

• Creates the client/server connectable provided by the database1CS.connect file.

• Adds the SQL schema provided by the database1.sql file to the database1 database.

• Creates two management instances, including their respective hosts and installations.

• Creates six data instances, including their respective hosts and installations, evenly assigned to two data space groups. The ttGridRollout utility sets K-safety to 2 at grid creation to satisfy the need of two data space groups.

  Note:

  See "Define the network parameters of each host and membership server" for details on the attributes used for every instance in this example.

% vi /mydir/grid1.conf

grid_name = grid1
zoo_conf = /mydir/membership.conf
instance_location = /grid
installation_location = /grid
dbdef_file = /mydir/database1.dbdef
cs_connect_files = /mydir/database1CS.connect
init_script = /mydir/database1.sql
mgmt_instances = [
        { "host":"host1", "address":"int-host1", "instance":"instance1",
 "daemonport":6624, "csport":6625, "mgmtport":3754},
        { "host":"host2", "address":"int-host2", "instance":"instance1",
 "daemonport":6624, "csport":6625, "mgmtport":3754}
]
data_instances = [
        { "host":"host3", "internalAddress":"int-host3",
 "externalAddress":"ext-host3.example.com", "dataspacegroup":1,
  "instance":"instance1", "daemonport":6624, "csport":6625},
        { "host":"host4", "internalAddress":"int-host4",
 "externalAddress":"ext-host4.example.com", "dataspacegroup":2,
 "instance":"instance1", "daemonport":6624, "csport":6625},
        { "host":"host5", "internalAddress":"int-host5",
 "externalAddress":"ext-host5.example.com", "dataspacegroup":1,
 "instance":"instance1", "daemonport":6624, "csport":6625},
        { "host":"host6", "internalAddress":"int-host6",
 "externalAddress":"ext-host6.example.com", "dataspacegroup":2,
 "instance":"instance1", "daemonport":6624, "csport":6625},
        { "host":"host7", "internalAddress":"int-host7",
 "externalAddress":"ext-host7.example.com", "dataspacegroup":1,
 "instance":"instance1", "daemonport":6624, "csport":6625},
        { "host":"host8", "internalAddress":"int-host8",
 "externalAddress":"ext-host8.example.com", "dataspacegroup":2,
 "instance":"instance1", "daemonport":6624, "csport":6625}
]

Create a grid and database

Use the ttGridRollout utility to create a grid and database based on the configuration file you provide.

% /grid/tt18.1.4.1.0/bin/ttGridRollout /mydir/grid1.conf
INFO: Checking Zookeeper on ms-host1!2181 -- OK
INFO: Checking Zookeeper on ms-host2!2181 -- OK
INFO: Checking Zookeeper on ms-host3!2181 -- OK
INFO: Checking the address for the management database -- OK
INFO: Checking connectivity to int-host1 -- OK
 
================================================================================
 
/grid/tt18.1.4.1.0/bin/ttInstanceCreate -grid -location /grid -name
 instance1 -daemonport 6624 -csport 6625
Creating instance in /grid/instance1 ...
 
NOTE: The TimesTen daemon startup/shutdown scripts have not been installed.
 
The startup script is located here :
        '/grid/instance1/startup/tt_instance1'
 
Run the 'setuproot' script :
        /grid/instance1/bin/setuproot -install
This will move the TimesTen startup script into its appropriate location.
 
The 18.1.4.1 Release Notes are located here :
  '/grid/tt18.1.4.1.0/README.html'
 
/grid/instance1/bin/ttenv ttGridAdmin gridCreate grid1 -k 2 -host host1 -address
 int-host1 -membership zookeeper -membershipConfig /mydir/membership.conf
 -mgmtport 3754
/grid/instance1/bin/ttenv ttGridAdmin hostCreate host2 -address int-host2
/grid/instance1/bin/ttenv ttGridAdmin installationCreate host2 -location /grid
/grid/instance1/bin/ttenv ttGridAdmin instanceCreate host2.instance1 -location
 /grid -type management -daemonport 6624 -csport 6625 -mgmtport 3754
/grid/instance1/bin/ttenv ttGridAdmin modelApply
/grid/instance1/bin/ttenv ttGridAdmin hostCreate host3 -externaladdress int-host3
 -internaladdress ext-host3.example.com -dataspacegroup 1
/grid/instance1/bin/ttenv ttGridAdmin installationCreate host3 -location /grid
/grid/instance1/bin/ttenv ttGridAdmin hostCreate host4 -externaladdress int-host4
 -internaladdress ext-host4.example.com -dataspacegroup 2
/grid/instance1/bin/ttenv ttGridAdmin installationCreate host4 -location /grid
/grid/instance1/bin/ttenv ttGridAdmin hostCreate host5 -externaladdress int-host5
 -internaladdress ext-host5.example.com -dataspacegroup 1
/grid/instance1/bin/ttenv ttGridAdmin installationCreate host5 -location /grid
/grid/instance1/bin/ttenv ttGridAdmin hostCreate host6 -externaladdress int-host6
 -internaladdress ext-host6.example.com -dataspacegroup 2
/grid/instance1/bin/ttenv ttGridAdmin installationCreate host6 -location /grid
/grid/instance1/bin/ttenv ttGridAdmin hostCreate host7 -externaladdress int-host7
 -internaladdress ext-host7.example.com -dataspacegroup 1
/grid/instance1/bin/ttenv ttGridAdmin installationCreate host7 -location /grid
/grid/instance1/bin/ttenv ttGridAdmin hostCreate host8 -externaladdress int-host8
 -internaladdress ext-host8.example.com -dataspacegroup 2
/grid/instance1/bin/ttenv ttGridAdmin installationCreate host8 -location
 /grid
/grid/instance1/bin/ttenv ttGridAdmin instanceCreate host3.instance1 -location
 /grid -daemonport 6624 -csport 6625
/grid/instance1/bin/ttenv ttGridAdmin instanceCreate host4.instance1 -location
 /grid -daemonport 6624 -csport 6625
/grid/instance1/bin/ttenv ttGridAdmin instanceCreate host5.instance1 -location
 /grid -daemonport 6624 -csport 6625
/grid/instance1/bin/ttenv ttGridAdmin instanceCreate host6.instance1 -location
 /grid -daemonport 6624 -csport 6625
/grid/instance1/bin/ttenv ttGridAdmin instanceCreate host7.instance1 -location
 /grid -daemonport 6624 -csport 6625
/grid/instance1/bin/ttenv ttGridAdmin instanceCreate host8.instance1 -location
 /grid -daemonport 6624 -csport 6625
/grid/instance1/bin/ttenv ttGridAdmin dbdefCreate /mydir/database1.dbdef
/grid/instance1/bin/ttenv ttGridAdmin modelApply
/grid/instance1/bin/ttenv ttGridAdmin dbCreate -wait 180 database1
/grid/instance1/bin/ttenv ttGridAdmin dbDistribute database1 -add all -apply
/grid/instance1/bin/ttenv ttGridAdmin dbOpen -wait 180 database1
/grid/instance1/bin/ttenv ttGridAdmin connectableCreate -dbdef database1 -cs
 /mydir/database1CS.connect
/grid/instance1/bin/ttenv ttGridAdmin modelApply
/grid/instance1/bin/ttenv ttGridAdmin instanceExec -only host3.instance1 "ttIsql
 database1 <<EOF
CREATE USER terry IDENTIFIED BY password;
 
GRANT CREATE SESSION TO terry;
 
CREATE TABLE terry.account_type
(
    type            CHAR(1) NOT NULL PRIMARY KEY,
    description     VARCHAR2(100) NOT NULL
)
DUPLICATE;
 
CREATE TABLE terry.account_status
(
    status          NUMBER(2,0) NOT NULL PRIMARY KEY,
    description     VARCHAR2(100) NOT NULL
)
DUPLICATE;
 
CREATE TABLE terry.customers
(
    cust_id            NUMBER(10,0) NOT NULL PRIMARY KEY,
    first_name         VARCHAR2(30) NOT NULL,
    last_name          VARCHAR2(30) NOT NULL,
    addr1              VARCHAR2(64),
    addr2              VARCHAR2(64),
    zipcode            VARCHAR2(5),
    member_since       DATE NOT NULL
)
DISTRIBUTE BY HASH;
 
CREATE TABLE terry.accounts
(
    account_id         NUMBER(10,0) NOT NULL PRIMARY KEY,
    phone              VARCHAR2(16) NOT NULL,
    account_type       CHAR(1) NOT NULL,
    status             NUMBER(2,0) NOT NULL,
    current_balance    NUMBER(10,2) NOT NULL,
    prev_balance       NUMBER(10,2) NOT NULL,
    date_created       DATE NOT NULL,
    cust_id            NUMBER(10,0) NOT NULL,
    CONSTRAINT fk_customer
        FOREIGN KEY (cust_id)
            REFERENCES terry.customers(cust_id),
    CONSTRAINT fk_acct_type
        FOREIGN KEY (account_type)
            REFERENCES terry.account_type(type),
    CONSTRAINT fk_acct_status
        FOREIGN KEY (status)
            REFERENCES terry.account_status(status)
)
DISTRIBUTE BY REFERENCE (fk_customer);
 
CREATE TABLE terry.transactions
(
    transaction_id      NUMBER(10,0) NOT NULL,
    account_id          NUMBER(10,0) NOT NULL ,
    transaction_ts      TIMESTAMP NOT NULL,
    description         VARCHAR2(60),
    optype              CHAR(1) NOT NULL,
    amount              NUMBER(6,2) NOT NULL,
    PRIMARY KEY (account_id, transaction_id, transaction_ts),
    CONSTRAINT fk_accounts
        FOREIGN KEY (account_id)
            REFERENCES terry.accounts(account_id)
)
DISTRIBUTE BY REFERENCE (fk_accounts);
 
CREATE SEQUENCE terry.txn_seq CACHE 100 BATCH 1000000;
 
EOF"
 
================================================================================
6-instance (3x2) grid successfully created.
 
 
Management Instance Locations
-----------------------------
- int-host1:/grid/instance1
- int-host2:/grid/instance1
 
Please source ttenv script under Management Instances for grid management via
 "ttGridAdmin" commands.
 
  For example, to use the first management instance, on int-host1:
  sh:  . /grid/instance1/bin/ttenv.sh
  csh: source /grid/instance1/bin/ttenv.csh
 
 
Data Instance Locations
-----------------------
- host3.instance1 ==> int-host3:/grid/instance1
- host4.instance1 ==> int-host4:/grid/instance1
- host5.instance1 ==> int-host5:/grid/instance1
- host6.instance1 ==> int-host6:/grid/instance1
- host7.instance1 ==> int-host7:/grid/instance1
- host8.instance1 ==> int-host8:/grid/instance1
 
Please source ttenv script under Data Instances for database operations.
 
  For example, to use instance1, on int-host3:
  sh:  . /grid/instance1/bin/ttenv.sh
  csh: source /grid/instance1/bin/ttenv.csh

Connect to the database

Connect to your database through a direct or client connection. For a direct connection, set your environment to one of the data instances, host3.instance1 for example, and use the database1 connectable to connect to the database.

% source /grid/instance1/bin/ttenv.csh
...
% ttIsql -connStr "DSN=database1;UID=terry"

See "Connecting to a database" for more information on how to connect to a database in TimesTen Scaleout.