Creating a Database

Create a Database Definition

A database definition contains the description of a database. It defines the database name, as well as the attributes of the database. Once a database definition is added to the current version of the model, it can be used to create a database. Each database has one or more connectables associated with it. Connectables specify how applications connect to the database. See Connecting to a Database.

Creating a Database Definition File

To create a database definition, you need a database definition file. The database definition file must use .dbdef as the file name suffix. The name of the database definition derives from the name of the database definition file. For example, a database definition file named database1.dbdef creates a database definition named database1.

Note:

Database definition names have the same restrictions as Data Source Names. See Specifying Data Source Names to Identify TimesTen Databases in Oracle TimesTen In-Memory Database Operations Guide.

In the database definition file, you specify the connection attributes for the database. The types of connection attributes that a database definition supports are:

Data store attributes are associated with a database when it is created. They can only be modified by recreating the database.

The most commonly used data store attributes are:
- DataStore: Defines the full path and file name prefix of the checkpoint files for every element of the database. Required.
- LogDir: Defines the file system directory of the transaction log files for every element of the database.
- DatabaseCharacterSet: Defines the character set to be used by the database. Required.
- Durability: Defines the degree of durability for transactions.
  
  Note:
  
  Ensure that you set the appropriate durability setting based on your business needs and data loss tolerance. See Durability Settings.
First connection attributes are associated with a database when it is loaded into memory. They can only be modified when the database is unloaded from memory and reloaded with different values for the first connection attributes.

The most commonly used first connection attributes are:
- PermSize: Defines the allocated size of the permanent memory region of each element of the database. The permanent memory region contains persistent database objects, such as tables. TimesTen Scaleout only writes the contents of the permanent memory region to the file system.
- TempSize: Defines the allocated size of the temporary memory region of each element of the database. The temporary memory region contains the transient data generated when executing a statement.
  
  Note:
  
  Each host must have sufficient main memory to accommodate as many elements of the database as data instances associated with the host. See Determining the Value of the PermSize Attribute and Specifying the Memory Region Sizes of a Database and Storage Provisioning for TimesTen in Oracle TimesTen In-Memory Database Operations Guide.
- CacheAdminWallet=1 specifies that credentials for the Oracle cache administration user that are registered with the ttGridAdmin -cacheUidPwdSet command are stored in an Oracle Wallet, rather than in memory.
PL/SQL first connection attributes define the behavior of a database regarding PL/SQL operations and are associated with the database when it is loaded into memory. They can only be modified when the database is unloaded from memory and reloaded with different values for the PL/SQL first connection attributes.
Server connection attributes define the behavior of the database regarding connections and are associated with the database when it is loaded into memory. They can only be modified when the database is unloaded from memory and reloaded with different values for the server connection attributes.

Note:

TimesTen Scaleout adds any connection attribute in the database definition file that is not a data store, first connection, PL/SQL first connection, or server connection attribute to a connectable that TimesTen Scaleout creates by default. See Create a Connectable.

The following example creates a database definition file named database1.dbdef that defines:

The full path for the checkpoint files as /disk1/databases/database1
The directory for the log files as /disk2/logs
The database character set as AL32UTF8
The durability setting as 0.
32 GB for the permanent memory region of every element
4 GB for the temporary memory region of every element
1 GB for the internal transaction log buffer of every element
An upper limit of 2048 user-specified concurrent connections to the database
When the cache administration user credentials are registered with ttGridAdmin -cacheUidPwdSet option, they are stored in an Oracle Wallet.

vi /mydir/database1.dbdef

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

See Connection Attributes in Oracle TimesTen In-Memory Database Reference.

Adding a Database Definition to the Model

The ttGridAdmin dbdefCreate command creates a database definition based on a database definition file. TimesTen Scaleout uses the name of the database definition file to name the database definition.

Create the database1 database definition based on the database1.dbdef file.

% ttGridAdmin dbdefCreate /mydir/database1.dbdef
Database Definition database1 created.

The ttGridAdmin dbdefCreate command also creates a connectable of the same name, which includes any general connection attribute found in the database definition file. Considering that the database1.dbdef file in the previous topic includes no general connection attribute, the database1 connectable contains no attributes. This connectable is always set for direct connections only.

Add the database1 database definition to the current version of the model.

% ttGridAdmin modelApply
...
Updating grid state...................................................OK
Pushing new configuration files to each instance......................OK
...
ttGridAdmin modelApply complete

TimesTen Scaleout adds a database1 connectable to the configuration files of every data instance based on the attributes defined in the database1 database definition.

Note:

TimesTen Scaleout overwrites the configuration files every time you apply the changes made to the latest version of the model to the operational grid. For this reason, you must refrain from modifying these files without the assistance of the ttGridAdmin utility.

For more information on the ttGridAdmin dbdefCreate or ttGridAdmin modelApply command, see Create a Database Definition (dbdefCreate) in Oracle TimesTen In-Memory Database Reference or Applying the Changes Made to the Model, respectively.

Create a Database Based on the Database Definition

In TimesTen Scaleout, user data is distributed to a set of elements that form a database. Each data instance in the current version of the model contains one element of every user database in the grid.

You can create a database based on the attributes stored in a database definition. On database creation, every data instance creates an element of the database and loads it into memory.

The process of creating an element of the database on every data instance is asynchronous. The daemon of each data instance performs the operations necessary to create and load the element into memory independently, as soon as it realizes that there is a new database flagged for creation.

The ttGridAdmin dbCreate command creates a database based on a database definition.

Create the database1 database based on the database1 database definition.

% ttGridAdmin dbCreate database1
Database database1 creation started

Wait until all data instances report that they have loaded their element of the database into memory before proceeding with the definition of the distribution map. You can verify the status of the database creation process with the ttGridAdmin dbStatus command.

The following example shows a status summary for the database1 database. Notice that the report shows all elements of the database as loaded.

% ttGridAdmin dbStatus database1 -element
Database database1 element level status as of Wed Jan 10 14:34:08 PST 2018
 
Host  Instance  Elem Status CA Status Date/Time of Event  Message 
----- --------- ---- ------ –-------- ------------------- ------- 
host3 instance1    1 loaded stopped   2018-01-10 14:33:23         
host4 instance1    2 loaded stopped   2018-01-10 14:33:21         
host5 instance1    3 loaded stopped   2018-01-10 14:33:23         
host6 instance1    4 loaded stopped   2018-01-10 14:33:23         
host7 instance1    5 loaded stopped   2018-01-10 14:33:23         
host8 instance1    6 loaded stopped   2018-01-10 14:33:23

For more information on the ttGridAdmin dbCreate or ttGridAdmin dbStatus command, see Create a Database (dbCreate) or Monitor the Status of a Database (dbStatus), respectively, in Oracle TimesTen In-Memory Database Reference.

Define the Distribution Map of the Database

TimesTen Scaleout allows for elastic scalability. You can increase or reduce the number of elements in your database according to your business needs. When you add new data instances to a grid, TimesTen Scaleout does not automatically re-distribute the data stored in the database across the elements of the new or remaining instances. The way the data is distributed in TimesTen Scaleout is defined by the data space group associated to each host in the grid and the elements of the data instances defined in the distribution map of the database.

Note:

TimesTen Scaleout blocks DDL and DML statements during operations that change the distribution map of the database. Ensure that you make changes to the distribution map while there are no open transactions, such as during a maintenance period or scheduled outage.

The ttGridAdmin dbDistribute command with the -add option adds the element of a data instance to the distribution map of a database. Using all as the parameter for the -add option adds the elements of all the available data instances in the grid. The all parameter is typically used for the initial definition of the distribution map of a new database.

Add all the elements of the available data instances in the grid1 grid to the distribution map of the database1 database.

% ttGridAdmin dbDistribute database1 -add all -apply
Distribution map updated

For more information on the ttGridAdmin dbDistribute command, see Set or Modify the Distribution Scheme of a Database (dbDistribute) in Oracle TimesTen In-Memory Database Reference.

Open the Database for User Connections

For an application to be able to connect to a database, the database needs to be open for user connections. As with the database creation process, the process of opening elements is asynchronous. The daemon of every data instance performs the operations necessary to open its element as soon as it realizes that the database is flagged for opening.

Note:

The instance administrator can connect to the database without it being open for user connections.
Before you open the database to user connections, you may want to create your database users. See Creating or Identifying a Database User in Oracle TimesTen In-Memory Database Security Guide.

Also, you may want to have the SQL schema defined which includes the distribution scheme of each table, as shown in Defining Table Distribution Schemes.

The ttGridAdmin dbOpen command opens a database for user connections.

Open the database1 database for user connections.

% ttGridAdmin dbOpen database1
Database database1 open started

You can verify the status of the database opening process with the ttGridAdmin dbStatus command. The following example shows a status summary for the database1 database. Notice that the report shows all elements of the database as open.

% ttGridAdmin dbStatus database1 -element
Database database1 element level status as of Wed Jan 10 14:34:43 PST 2018
 
Host  Instance  Elem Status CA Status Date/Time of Event  Message 
----- --------- ---- ------ –-------- ------------------- ------- 
host3 instance1    1 opened stopped   2018-01-10 14:34:43         
host4 instance1    2 opened stopped   2018-01-10 14:34:43         
host5 instance1    3 opened stopped   2018-01-10 14:34:42         
host6 instance1    4 opened stopped   2018-01-10 14:34:42         
host7 instance1    5 opened stopped   2018-01-10 14:34:42         
host8 instance1    6 opened stopped   2018-01-10 14:34:42

For more information on the ttGridAdmin dbOpen or ttGridAdmin dbStatus command, see Open a Database (dbOpen) or Monitor the Status of a Database (dbStatus), respectively, in Oracle TimesTen In-Memory Database Reference.