1 Managing TimesTen Databases

Connecting using TimesTen ODBC drivers

TimesTen includes the following ODBC drivers:

• TimesTen Data Manager driver: A TimesTen ODBC driver for use with direct connect applications.

• TimesTen Client driver: A TimesTen Client ODBC driver for use with client/server applications.

TimesTen includes the following two versions of the Data Manager ODBC driver:

• Production: Use the production version of the TimesTen Data Manager driver for most application development and for all deployment.

• Debug: Use the debug version of the TimesTen Data Manager driver only if you encounter problems with TimesTen itself. This version performs additional internal error checking and is slower than the production version. On UNIX, the TimesTen debug libraries are compiled with the -g option to display additional debug information.

On Windows, the production version of the TimesTen Data Manager is installed by default. To install the debug version, choose Custom setup. To install the TimesTen Client driver, choose either Typical or Custom setup.

Table 1-1 lists the ODBC drivers for Windows:

On UNIX, depending on the options selected at install time, TimesTen may install the Client driver and both the production version and the debug version of the TimesTen Data Manager ODBC driver.

Table 1-2 lists the TimesTen ODBC drivers for UNIX platforms.

Connecting using the TimesTen JDBC driver and driver manager

JDBC enables Java applications to issue SQL statements to TimesTen and process the results. It is the primary interface for data access in the Java programming language. For TimesTen installations, JDBC is installed with the TimesTen Data Manager.

As shown in Figure 1-1, the TimesTen JDBC driver uses the ODBC driver to access TimesTen databases. For each JDBC method, the driver executes a set of ODBC functions to perform the appropriate operation. Since the JDBC driver depends on ODBC for all database operations, the first step in using JDBC is to define a TimesTen database and the ODBC driver that accesses it on behalf of JDBC.

The TimesTen JDBC API is implemented using native methods to bridge to the TimesTen native API and provides a driver manager that can support multiple drivers connecting to separate databases. The JDBC driver manager in the DriverManager class keeps track of all JDBC drivers that have been loaded and are available to the Java application. The application may load several drivers and access each driver independently. For example, both the TimesTen Client JDBC driver and the TimesTen direct driver can be loaded by an application. Then, Java applications can access databases either on the local system or a remote system.

For a list of the Java functions supported by TimesTen, see "Support for interfaces in the java.sql package" in the Oracle TimesTen In-Memory Database Java Developer's Guide.

Overview of user and system DSNs

DSNs are resolved using a two-tiered naming system, where TimesTen first tries to resolve the DSN within the defined user DSNs and secondly within the defined system DSNs.

• A user DSN can be used only by the user who created the DSN.

  • On Windows, user DSNs are defined from the User DSN tab of the ODBC Data Source Administrator.

  • On UNIX, define user DSNs in the user odbc.ini file. TimesTen locates this file by first finding if a file is specified by the ODBCINI environment variable. If not, TimesTen locates the $HOME/.odbc.ini file.

  Although a user DSN is private to the user who created it, it is only the DSN, consisting of the character-string name and its attributes, that is private. The underlying database can be referenced by other users' user DSNs or by system DSNs.

  TimesTen supports data sources for both the TimesTen Data Manager and the TimesTen Client in the .odbc.ini file.

• A system DSN can be used by any user on the system on which the system DSN is defined to connect to the TimesTen database.

  • On Windows, system DSNs are defined from the System DSN tab of the ODBC Data Source Administrator.

  • On UNIX, system DSNs are defined in the sys.odbc.ini file, which is referred to as the system odbc.ini file. TimesTen locates the system DSN file in the following order:

    • The file is located if it is specified by the SYSODBCINI environment variable.

    • In a non-root installation, the file is located in install_dir/info/sys.odbc.ini. In a root installation, the file is located in /var/TimesTen/InstanceName/sys.odbc.ini.

    • If not found in either the root or non-root locations, TimesTen looks for the /var/TimesTen/sys.odbc.ini file.

    • If not found in any of these locations, TimesTen looks on the system for the /etc/odbc.ini file.

Defining DSNs for direct or client/server connections

DSNs are created to uniquely identify a database, whether local or remote. The following explains the type of DSN to use for either a direct or client/server connection:

• Data Manager DSN: A DSN that specifies a local database uses the TimesTen Data Manager ODBC driver, which is the direct driver. You can use either the production version or the debug version of the TimesTen Data Manager driver.

  A Data Manager DSN refers to a database using a path name and a file name prefix. The database path name specifies the directory location of the database and the prefix for the database, such as C:\data\chns\AdminDS or /home/chns/AdminDS.

  Note:

  This path name and prefix does not define a file name, but the name of the directory where the database is located and the prefix for all database files. The actual files used by the database append file suffixes, such as C:\data\chns\AdminDS.ds0 or /home/chns/AdminDS.log2.

  A Data Manager DSN that refers to a given TimesTen database must be defined on the same system on which the database resides. TimesTen creates dsName.resn files for each database. These files are used internally by TimesTen for maintaining logs.

  If multiple Data Manager DSNs refer to the same database, they must all use exactly the same database path name, even if some other path name identifies the same location. For example, you cannot use a symbolic link to refer to the database in one DSN and the actual path name in another DSN. On Windows, you cannot use a mapped drive letter in the database path name.

• Client DSN: A client DSN specifies a remote database and uses the TimesTen client. A client DSN refers to a TimesTen database indirectly by specifying a hostname, DSN pair, where the hostname represents the server system on which TimesTen Server is running and the DSN refers to a server DSN that specifies the TimesTen database on the server host.

• Server DSN: A server DSN is always defined as a system DSN and is defined on the server system for each database on that server that can be accessed by client applications. The format and attributes of a server DSN are very similar to those of a Data Manager DSN.

On UNIX, all user DSNs including both client DSNs and Data Manager DSNs that are created by a specific user are defined in the same user odbc.ini file. Similarly, all system DSNs are defined in the same system odbc.ini file.

The following table indicates the types of DSN supported by TimesTen, whether to create a user or system DSN and the location of the DSN.

For more information about client DSNs and server DSNs, see "Working with the TimesTen Client and Server".

Connection attributes for Data Manager DSNs or server DSNs

There are four types of TimesTen Data Manager DSN or server DSN attributes:

• Data Store attributes are associated with a database when it is created and cannot be modified by subsequent connections. They can only be changed by destroying and re-creating the database.

  The following are the most commonly used data store attributes:

  • DataStore: Directory name and file name prefix of the database.

  • LogDir: Directory name of the database transaction log files. By default, the transaction log files reside in the checkpoint files directory. Placing the transaction log files and checkpoint files on different disks can improve system throughput.

  • DatabaseCharacterSet: Required character set specification that defines the storage encoding.

• First connection attributes are used when the TimesTen database is loaded into memory. Only the instance administrator can load a database with first connection attribute settings. By default, TimesTen loads an idle database, which is a database with no connections, into memory when a first connection is made to it. These attributes persist for all subsequent connections until the last connection to the database is closed. First connection attributes can be modified only when the TimesTen database is unloaded and then the instance administrator reconnects with different values for the first connection attributes.

  The following are the most commonly used first connection attributes:

  • PermSize: Configures the allocated size of the database's permanent memory region. The permanent memory region contains persistent database elements. TimesTen only writes the permanent memory region to disk during a checkpoint operation.

  • TempSize: Configures the allocated size of the database's temporary memory region. The temporary memory region contains transient data generated when executing statements.

    Note:

    Your system must have sufficient main memory to accommodate the entire database. For more details on setting region sizes, see "Specifying the size of a database".

• General connection attributes are set by each connection and persist for the duration of the connection. Each concurrent connection can have different values.

  The following are the most commonly used general connection attributes:

  • UID: Specifies the user name to be used for the connection to the database, whether using a direct or client/server connection. To connect as the instance administrator or as an external user, you do not need to specify an user name. When you do not specify an user name, TimesTen assumes that the UID is the user name identified by the operating system.

  • PWD: Specifies the password that corresponds with the specified UID. For internal users, if you do not set the PWD attribute in the odbc.ini file for the specified DSN or in the connection string, TimesTen prompts for the password. For external users, you do not provide the password as it is verified by the operating system.

    When you initiate a client/server connection, the password sent for the connection is encrypted by the client/server protocol.

    Note:

    For more information on the UID and PWD general connection attributes, see "UID and PWD" in the Oracle TimesTen In-Memory Database Reference. See "Managing users to control authentication" for details on the instance administrator, external users, or internal users.

  • PWDCrypt: Specifies the encrypted password that corresponds with the specified UID.

  Note:

  If you provide connection attributes in the connection string, this overrides the connection attributes set in the DSN. See "Connecting to a database using a connection string" for details.

• TimesTen Cache attributes enable you to enter the Oracle Service Identifier for the Oracle database instance from which data is loaded into TimesTen.

On Windows, you specify attributes in the ODBC Data Source Administrator.

On UNIX, you specify attributes in the odbc.ini file. Attributes that do not appear in the odbc.ini file assume their default value.

Creating a Data Manager DSN on Windows

The following sections describe how to create a DSN on Windows:

• Specify the ODBC driver

• Specify the Data Manager DSN

• Specify the connection attributes

Specify the Data Manager DSN

On the Data Store tab of the TimesTen ODBC Setup dialog, specify a data source name (DSN), a database directory path and prefix, and a database character set. The database directory path cannot reference a mapped drive. See Figure 1-2.

Figure 1-2 Data Store tab

Description of "Figure 1-2 Data Store tab"

For an explanation of the DSN, database path and prefix, see "Specifying Data Source Names to identify TimesTen databases". For an explanation of database character sets, see "Choosing a database character set". The description field is optional.

Specify the connection attributes

Indicate the desired connection attributes under the First Connection, General Connection, and NLS Connection tabs of the TimesTen ODBC Setup dialog as shown in Figure 1-3, Figure 1-4,and Figure 1-5. In addition, if you are using TimesTen Cache for an Oracle database, specify the connection attributes shown in Figure 1-6. If you are using a multi-threaded client/server configuration, specify the connection attributes shown in Figure 1-7.

Note:

For a description of the connection attributes, see "Connection Attributes" in Oracle TimesTen In-Memory Database Reference.

Figure 1-3 First Connection Attributes

Description of "Figure 1-3 First Connection Attributes"

Figure 1-4 General Connection Attributes

Description of "Figure 1-4 General Connection Attributes"

Figure 1-5 NLS Connection Attributes

Description of "Figure 1-5 NLS Connection Attributes"

Figure 1-6 TimesTen Cache Attributes

Description of "Figure 1-6 TimesTen Cache Attributes"

Figure 1-7 Server Attributes

Description of "Figure 1-7 Server Attributes"

Figure 1-8 PL/SQL Attributes

Description of "Figure 1-8 PL/SQL Attributes"

Click OK when finished.

Creating a Data Manager DSN on UNIX

This section includes the following topics:

• Create a user or system odbc.ini file

• Using environment variables in database path names

Setting up a default DSN

Optionally, you can add a default data source definition. At connect time, if an application specifies a DSN that is not in the odbc.ini file or if the application does not specify a DSN, the default DSN is used to configure the connection to the TimesTen database.

The default data source must be named default when defined. The default DSN can be defined with the same attributes as any other data source, which are described in "DSN specification".

When connecting, TimesTen uses the default DSN in any of the following scenarios:

• When you specify the DSN=default keyword-value pair in the connection string.

• When you specify an undefined value for the DSN connection attribute in the connection string.

• When you do not specify any value for the DSN connection attribute in the connection string.

However, in general, it is best to connect with a specific data source.

When using a default DSN, provide default as the DSN name when performing TimesTen utility operations that require a DSN name, such as destroying the database with the ttDestroy utility.

The following example shows the user invoking ttIsql to connect using an undefined DSN. Since there is no definition for doesNotExist in the odbc.ini file, TimesTen uses the default DSN to create the database and initiate a connection to it. It also demonstrates the user invoking both the ttStatus and ttDestroy utilities with default specified as the DSN. In addition, it shows the error thrown if the user provides doesNotExist as the DSN, instead of default.

$ ttIsql doesNotExist;
 
Copyright (c) 1996-2011, Oracle.  All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.
 
connect "DSN=doesNotExist";
Connection successful:
Command> exit
Disconnecting...
Done.
]$ ttStatus default
TimesTen status report as of Mon Oct 22 12:27:52 2012
 
Daemon pid 13623 port 16138 instance myhost
TimesTen server pid 13632 started on port 16140
------------------------------------------------------------------------
Data store /timesten/install/info/default
There are no connections to the data store
Replication policy  : Manual
Cache Agent policy  : Manual
PL/SQL enabled.
------------------------------------------------------------------------
Accessible by group xyz
End of report
$ ttDestroy doesNotExist;
Failed to destroy data store: Specified DSN is not found in user and system
odbc.ini files (or registry)
$ ttDestroy default; 

The following example shows how to configure connection attributes for a default DSN. While not necessary, you can configure connection attributes for a default DSN as you would configure any other DSN. Notice that it is not specified in the ODBC Data Sources section.

[ODBC Data Sources]
sampledb_1122=TimesTen 11.2.2 Driver
...
 
[default]
Driver=install_dir/lib/libtten.so
DataStore=install_dir/info/DemoDataStore/default
PermSize=40
TempSize=64
DatabaseCharacterSet=US7ASCII

[sampledb_1122]
Driver=install_dir/lib/libtten.so
DataStore=install_dir/info/DemoDataStore/sampledb_1122
PermSize=40
TempSize=32
PLSQL=1
DatabaseCharacterSet=US7ASCII

Setting up a temporary database

This example illustrates how to set up a temporary database. For information on temporary databases, see "Database overview".

On Windows, you can use the settings in the TimesTen ODBC Setup dialog to set up a temporary database. See Figure 1-9 and Figure 1-10.

Figure 1-9 Data Store tab

Description of "Figure 1-9 Data Store tab"

Figure 1-10 First Connection Attributes

Description of "Figure 1-10 First Connection Attributes"

To set up a temporary database on UNIX, create the following entries in your odbc.ini file. For a list of drivers for all UNIX platforms, see the table in "Connecting using TimesTen ODBC drivers".

The text in square brackets is the data source name.

[TempDs]
Driver=install_dir/lib/libtten.so
DataStore=/users/robin/TempDs
#this is a temporary database
Temporary=1
#create database if it is not found
AutoCreate=1
#log database updates to disk
LogPurge=1
DatabaseCharacterSet=US7ASCII

Specifying PL/SQL connection attributes in a DSN

You can specify values for PL/SQL general connection attributes.

The following are some PL/SQL connection attributes:

• PLSCOPE_SETTINGS - Controls whether the PL/SQL compiler generates cross-reference information.

• PLSQL_OPTIMIZE_LEVEL - Sets the optimization level that is used to compile PL/SQL library units.

• PLSQL_MEMORY_ADDRESS - Specifies the virtual address, as a hexadecimal value, at which the PL/SQL shared memory segment is loaded into each process that uses the TimesTen direct drivers. This memory address must be identical in all connections to your database and in all processes that connect to your database.

• PLSQL_MEMORY_SIZE - Determines the size, in megabytes, of the PL/SQL shared memory segment.

This example creates the PLdsn DSN, enables PL/SQL by setting PLSQL to "1" and sets the PL/SQL shared memory segment size to 32 MB.

[PLdsn]
Datastore=/users/user1/PLdsn
PermSize=32
DatabaseCharacterSet=AL32UTF8
ConnectionCharacterSet=AL32UTF8
PLSQL=1
PLSQL_MEMORY_SIZE=32

For more examples, see "PL/SQL connection attributes" in Oracle TimesTen In-Memory Database PL/SQL Developer's Guide.

Creating multiple DSNs to a single database

You can create two or more DSNs that refer to the same database but have different connection attributes.

This example creates two DSNs, AdminDSN and GlobalDSN. The DSNs are identical except for their connection character sets. Applications that use the US7ASCII character set can connect to the TTDS database by using AdminDSN. Applications that use multibyte characters can connect to the TTDS database by using GlobalDSN.

For Windows, use the ODBC Data Source Administrator to define AdminDSN as shown in Figure 1-11. AdminDSN is created with the AL32UTF8 database character set. Figure 1-12 shows that US7ASCII is the connection character set for AdminDSN.

Figure 1-11 Creating AdminDSN using TTDS database

Description of "Figure 1-11 Creating AdminDSN using TTDS database"

Figure 1-12 Setting the connection character set for AdminDSN

Description of "Figure 1-12 Setting the connection character set for AdminDSN"

GlobalDSN is also created with the AL32UTF8 database character set, as shown in Figure 1-13. Figure 1-14 shows that the connection character set for GlobalDSN is AL32UTF8.

Figure 1-13 Creating GlobalDSN using TTDS database

Description of "Figure 1-13 Creating GlobalDSN using TTDS database"

Figure 1-14 Setting the connection character set for GlobalDSN

Description of "Figure 1-14 Setting the connection character set for GlobalDSN"

The next example shows how to specify the DSNs on UNIX. It uses the TimesTen Data Manager ODBC driver for Solaris.

The text in square brackets is the data source name.

[AdminDSN]
Driver=install_dir/lib/libtten.so
Datastore=/data/TTDS
DatabaseCharacterSet=AL32UTF8
ConnectionCharacterSet=US7ASCII

[GlobalDSN]
Driver=install_dir/lib/libtten.so
DataStore=/data/TTDS
DatabaseCharacterSet=AL32UTF8
ConnectionCharacterSet=AL32UTF8

ODBC Data Sources

Each entry in the optional ODBC Data Sources section lists a data source and a description of the driver it uses. The data source section has the following format:

[ODBC Data Sources]
DSN=driver-description

• The DSN is required and it identifies the data source to which the driver connects. You choose this name.

• The driver-description is required. It describes the driver that connects to the data source.

The optional Data Sources section, when present in the system DSN file on the TimesTen Server, is used during the setup of client DSNs. All system DSNs are made available to the client DSN setup for the ODBC Data Source Administrator on the client, which displays all available DSNs on the TimesTen Server. The user can always add a new system DSN in the ODBC Data Source Administrator. When adding DSNs to the system DSN file, you should only include those DSNs that can be advertised to clients. All system DSNs are potentially accessible through the client/server configuration, even if they are not advertised.

DSN specification

Each DSN listed in the ODBC Data Sources section has its own DSN specification. The DSN specification for Data Manager DSN has the format shown in Table 1-3.

For example, the sampledb_1122 DSN could have a specification that includes the following:

[sampledb_1122]
Driver=install_dir/lib/libtten.so
DataStore=install_dir/info/DemoDataStore/sampledb_1122
...

The database specification for TimesTen client DSN has the format shown in Table 1-4.

For example, the client/server data source sampledbCS_1122 that connects to sampledb_1122 on the TimesTen Server ttserver could have a data source specification that includes the following:

[sampledbCS_1122]
TTC_Server=ttserver
TTC_SERVER_DSN=sampledb_1122
TTC_Timeout=30

odbc.ini file example

The following example shows portions of a UNIX .odbc.ini file:

...
[ODBC Data Sources]
sampledb_1122=TimesTen 11.2.2 Driver
...
 
[sampledb_1122]
Driver=install_dir/lib/libtten.so
DataStore=install_dir/info/DemoDataStore/sampledb_1122
PermSize=40
TempSize=32
PLSQL=1
DatabaseCharacterSet=US7ASCII
...
 
########################################################################
# This following sample definitions should be in the .odbc.ini file
# that is used for the TimesTen 11.2.2 Client.
# The Server Name is set in the TTC_SERVER attribute.
# The Server DSN is set in the TTC_SERVER_DSN attribute.
#########################################################################
 
[ODBC Data Sources]
sampledbCS_1122=TimesTen 11.2.2 Client Driver
...
 
[sampledbCS_1122]
TTC_SERVER=localhost
TTC_SERVER_DSN=sampledb_1122
...

Changes to RAM policy after automatic recovery fails

If a fatal error invalidates the database and the automatic database recovery performed by TimesTen is unsuccessful, the following occurs by default:

• TimesTen changes the always and manual RAM policies to InUse to prevent reoccurring errors. For both the always and manual RAM policies, TimesTen automatically reloads the database after each failure, even if the same failure occurs multiple times. However, for the InUse RAM policy, TimesTen does not automatically reload the database until a process tries to connect.

• If a fatal error causes the database to be invalidated, user processes connected to this database may not know that the database has been invalidated. In this case, the invalidated database exists in memory until all user processes close their connections. Thus, the invalidated database could coexist in memory with a newly reloaded database.

You can modify the default database recovery behavior by setting the -enablePolicyInactive option in the ttendaemon.options file before you start the TimesTen daemon. Once set, the following is the behavior when automatic recovery fails:

• The RAM policies of manual and always remain unchanged.

• The replication and cache agents are not restarted.

• After several failed attempts to reload the database, TimesTen sets the policyInactive mode, which prevents any more attempts at loading the database.

Any one of the following clears the policyInactive mode, so that normal behavior can resume:

• The TimesTen daemon restarts.

• A process connects successfully.

• The administrator executes a ttAdmin command for the database that changes the RAM policy, performs a RAM load, or starts either the cache or replication agents.

You can see if policyInactive mode is active when one of the following messages is displayed in the ttStatus output:

• "Automatic reloads disabled."

• "Data store should be manually loaded, but inactive due to failures." This is shown when the RAM policy is manual.

• "Data store should be loaded, but inactive due to failures." This is shown if the RAM policy is always.

Preventing an automatic reload of the database after failure

After a fatal error that causes the database to be invalidated, TimesTen attempts to reload and recover the database, as long as it is consistent with the settings for the RAM policy, cache agent policy, and replication agent policy. However, user processes could still be connected to the invalidated database if they do not know that the original database has been invalidated. In this case, the invalidated database exists in memory until all user processes close their connections. Thus, the invalidated database could coexist in memory with the newly reloaded database. This can be an issue if the database is large.

You can prevent the database from being automatically reloaded after an invalidation using the ttAdmin -noautoreload command. You can reset to the default automatic database reload behavior with the ttAdmin -autoreload command. See "ttAdmin" in the Oracle TimesTen In-Memory Database Reference for more details.

Any one of the following initiates a reload and recovery of the database so that normal behavior can resume:

• The TimesTen daemon restarts.

• A process connects successfully.

• The administrator executes a ttAdmin command for the database that changes the RAM policy, performs a RAM load, or starts either the cache or replication agents.

If you set the behavior to prevent automatic reloads of the database, you may receive the following error when connecting to a database that was not reloaded.

Error 707, "Attempt to connect to a data store that has been manually unloaded from RAM"

Loading the database into memory

Set the RAM policy to manual or inUse before loading the database into memory. If the RAM policy is set to always, then the database is already loaded since this policy states to keep the database loaded at all times. Also, ensure that the TimesTen daemon is running. The default RAM policy for a TimesTen database is inUse. For more information on specifying a RAM policy, see "Specifying a RAM policy".

Before you try to load the database into memory, confirm that the TimesTen daemon is running:

ttDaemonAdmin -start

To load the database into memory, run the ttAdmin utility:

• The following example sets the RAM policy of a TimesTen database to manual:

  ttAdmin -ramPolicy manual sampledb_1122
  
  RAM Residence Policy            : manual
  Manually Loaded In RAM          : False
  Replication Agent Policy        : manual
  Replication Manually Started    : False
  Cache Agent Policy              : manual
  Cache Agent Manually Started    : False
  

  Then, load the TimesTen database into memory with the ttAdmin -ramload utility. The -ramLoad option of the ttAdmin utility can only be used with the manual RAM policy:

  ttAdmin -ramLoad sampledb_1122
  
  RAM Residence Policy            : manual
  Manually Loaded In RAM          : True
  Replication Agent Policy        : manual
  Replication Manually Started    : False
  Cache Agent Policy              : manual
  Cache Agent Manually Started    : False
  

• The following example defines the database RAM policy as inUse with a grace period of 200 seconds:

  ttAdmin -ramPolicy inUse -ramGrace 200 sampledb_1122
  
  RAM Residence Policy            : inUse plus grace period
  RAM Residence Grace (Secs)      : 200
  Replication Agent Policy        : manual
  Replication Manually Started    : False
  Cache Agent Policy              : manual
  Cache Agent Manually Started    : False
  

If your database is configured replication or cache for your database, run the ttAdmin utility to start the replication and cache agents.

ttAdmin -repStart sampledb_1122

ttAdmin -cacheStart sampledb_1122

For more information on these utilities, see "ttAdmin" and "ttDaemonAdmin" in the Oracle TimesTen In-Memory Database Reference.

Unloading the database from memory

Before unloading the database from memory, you must first close all active connections to the database and set the RAM policy of the database to manual or inUse.

• To close all active connections to the database, run the ttStatus utility to find processes connected to the database and stop them. For more information on ttStatus, see "ttStatus" in the Oracle TimesTen In-Memory Database Reference.

  If you configured cache or replication for your database, stop the cache and replication agents with the -cacheStop and -repStop options of the ttAdmin utility.

• To set the RAM policy to manual or inUse, see "Specifying a RAM policy" for information.

• To unload a database with a manual RAM policy from memory, run the ttAdmin -ramUnload utility. TimesTen unloads a database with an inUse RAM policy from memory once you close all active connections. For more information on unloading a database with a manual RAM policy from memory, see "Unloading a database from memory" in the Oracle TimesTen In-Memory Database Installation Guide.

For more information on the ttAdmin utility, see "ttAdmin" in the Oracle TimesTen In-Memory Database Reference.

Estimating and modifying the memory region sizes for the database

Procedures, tables, or rows cannot be created in the database if the permanent or temporary memory region is full. In order to have the correct size for your database, set the appropriate size in the PermSize and TempSize connection attributes.

• PermSize connection attribute: You can increase but not decrease the permanent memory region.

• TempSize connection attribute: You can increase or decrease the size of the temporary memory region for databases that do not participate in replication.

To make size estimates, use the ttSize utility or run the application until you can make a reasonable estimate.

You must make sure that you have a shared memory segment that is large enough to hold the database. In general, the minimum size of this shared memory segment should be:

PermSize + TempSize + LogBufMB + 64 MB overhead

When you are calculating the amount of PermSize to allocate, take into account that PL/SQL procedures, functions and packages occupy space in the permanent memory region. The amount of permanent memory region required by a stored PL/SQL unit depends on the size and complexity of the unit. Small procedures can take less than 3 KBs, while larger ones can take considerably more. On average, reasonably complex units could be expected to use about 20 KBs of permanent memory region space.

If the database is configured for replication, reconfigure the database sizes for all replicas of the database. Once you have made the change in database size, load the database into memory and restart the cache and replication agents.

For more details, see "Installation prerequisites" in Oracle TimesTen In-Memory Database Installation Guide and the descriptions of the "TempSize" and "PermSize" attributes in Oracle TimesTen In-Memory Database Reference.

Ensuring sufficient disk space

TimesTen saves a copy of the database in two checkpoint files, each of which is stored in the directory that is specified with the DataStore attribute. As each checkpoint file grows on disk, it never decreases in size. This can result in the size of each checkpoint file being equal to the maximum size that the database has ever reached in the permanent memory region. The maximum size for each checkpoint file is PermSize + the database header. For each permanent database, you must have enough disk space for both checkpoint files and all transaction log files. For more information about the DataStore attribute, see "DataStore" in the Oracle TimesTen In-Memory Database Reference.

You can set the Preallocate connection attribute to 1 to have TimesTen reserve disk space at connect time for checkpoint files. This is useful for big databases, to ensure that the disk always has room for the checkpoint files as data is added to the database. For more information about the Preallocate connection attribute, see "Preallocate" in the Oracle TimesTen In-Memory Database Reference.

Monitoring PermSize and TempSize attributes

The TimesTen table SYS.MONITOR contains several columns that can be used to monitor usage of PermSize and TempSize. These columns include PERM_ALLOCATED_SIZE, TEMP_ALLOCATED_SIZE, PERM_IN_USE_SIZE, PERM_IN_USE_HIGH_WATER, TEMP_IN_USE_SIZE, and TEMP_IN_USE_HIGH_WATER. Each of these columns show in KB units the currently allocated size of the database and the in-use size of the database. The system updates this information each time a connection is made or released and each time a transaction is committed or rolled back.

You can monitor block-level fragmentation in the database by calling the ttBlockInfo built-in procedure. For more details, see "ttBlockInfo" in the Oracle TimesTen In-Memory Database Reference.

Receiving out-of-memory warnings

TimesTen provides two general connection attributes that determine when a low memory warning should be issued: PermWarnThreshold and TempWarnThreshold. Both attributes take a percentage value.

To receive out-of memory warnings, applications must call the built-in procedure ttWarnOnLowMemory.

These attributes also set the threshold for SNMP warning. See "Diagnostics through SNMP Traps" in the Oracle TimesTen In-Memory Database Error Messages and SNMP Traps.

Offline defragmentation of TimesTen databases

To defragment a database, use the ttMigrate utility as follows:

1. Stop all connections to the database.

2. Save a copy of the database using ttMigrate.

   ttMigrate -c ttdb ttdb.dat
   

3. As the administration user, rebuild the ttdb database:

   ttMigrate -r -relaxedUpgrade -connstr "dsn=ttdb" ttdb.dat
   

   Note:

   If you do not want to condense table partitions, remove the -relaxedUpgrade option when executing the ttMigrate -r command.

At this time:

• All the users, cache groups, and the active standby pair have been restored to ttdb.

• The cache groups are in AUTOREFRESH STATE = OFF.

• The cache agent and replication agent are not running.

Table partitions can be added when columns are added to tables with the ALTER TABLE ADD SQL statement. See the notes on "ALTER TABLE" in the Oracle TimesTen In-Memory Database SQL Reference for more information.

For more information on ttMigrate, see "ttMigrate" in the Oracle TimesTen In-Memory Database Reference.

Online defragmentation of TimesTen databases

Use a combination of the ttMigrate -relaxedUpgrade and ttRepAdmin -duplicate utilities to defragment TimesTen databases (with minimal overall service downtime) that are involved in a replication scheme where TABLE DEFINITION CHECKING is set to RELAXED. In addition, the ttMigrate -relaxedUpgrade option condenses partitions.

The following sections describe how to defragment TimesTen databases that are involved in a replication scheme:

• Online defragmentation of databases in an active standby pair replication scheme

• Online defragmentation of databases in a non active standby pair replication scheme