Moving to a Different Major Release Using ttMigrate

Moving to a different major release is done through migration. Migration includes upgrading from one major TimesTen release to a new major TimesTen release, or changing the operating system platform that TimesTen runs on.

Migration involves copying out the schema and data from one database, creating a new database with the new release, and then creating the schema and inserting the data into the new database. The ttMigrate utility is used to automate the migration of databases. See ttMigrate in the Oracle TimesTen In-Memory Database Reference for information on the ttMigrate utility.

Before migrating a database from one major release to another, ensure you backup the database in the old release. See ttBackup and ttRestore in Oracle TimesTen In-Memory Database Reference and Backing Up and Restoring a Database in this book for details.

Note:

TimesTen replication with Oracle Clusterware is not supported on Oracle Linux for Arm systems. If you are using Oracle Linux for Arm, Oracle Clusterware messages do not appear in any output in this example.

Follow these steps to perform the upgrade:

For the old release:

Use the ttAdmin utility to close the database1 database. This prevents further user connections.

% ttAdmin -close database1
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
Database State                  : Closed

Disconnect all applications from the database. Run the ttStatus utility to verify there are no connections to the database (database1, in this example).

% ttStatus
TimesTen status report as of Sat Oct  2 18:31:59 2021

Daemon pid 28436 port 6624 instance myinstance
TimesTen server pid 28443 started on port 6625
------------------------------------------------------------------------
------------------------------------------------------------------------
Data store /scratch/ttuser/database1
Daemon pid 28436 port 6624 instance myinstance
TimesTen server pid 28443 started on port 6625
There are 13 connections to the data store
Shared Memory KEY 0x061014ae ID 491521
PL/SQL Memory Key 0x071014ae ID 524290 Address 0x5000000000
Type            PID     Context             Connection Name              ConnID
Subdaemon       28440   0x0000000001893250  Manager                        2047
Subdaemon       28440   0x0000000001914210  Rollback                       2046
Subdaemon       28440   0x00007f55d80008c0  Deadlock Detector              2043
Subdaemon       28440   0x00007f55d807f330  Log Marker                     2040
Subdaemon       28440   0x00007f55dc0008c0  Monitor                        2044
Subdaemon       28440   0x00007f55dc07f330  AsyncMV                        2039
Subdaemon       28440   0x00007f55e00008c0  Checkpoint                     2042
Subdaemon       28440   0x00007f55e007f330  Aging                          2041
Subdaemon       28440   0x00007f55e40008c0  Flusher                        2045
Subdaemon       28440   0x00007f55e40a6970  HistGC                         2038
Subdaemon       28440   0x00007f56600008c0  XactId Rollback                2036
Subdaemon       28440   0x00007f56641b9cb0  IndexGC                        2037
Subdaemon       28440   0x00007f5668048360  Garbage Collector              2035
Closed to user connections
RAM residence policy: Manual
Data store is manually loaded into RAM
Replication policy  : Manual
Cache Agent policy  : Manual
PL/SQL enabled.
------------------------------------------------------------------------
Accessible by group g900
End of report

Run the ttVersion utility to verify the current release.

% ttVersion
TimesTen Release tt22.1.1.25.0 (64 bit Linux/x86_64) (myinstance:6624) 2021-09-12T07:34:06Z
  Instance admin: instanceadmin
  Instance home directory: /scratch/ttuser/myinstance
  Group owner: g900
  Daemon home directory: /scratch/ttuser/myinstance/info
  PL/SQL enabled.

Use the ttMigrate utility to copy out the schema and data from the database (database1, in this example).

% ttMigrate -c database1 /tmp/database1.data

Saving profile DEFAULT
Profile successfully saved.

Saving profile SYSTEM
Profile successfully saved.

Saving user PUBLIC
User successfully saved.

Saving table TTUSER.COUNTRIES
  Saving foreign key constraint COUNTR_REG_FK
  Saving rows...
  25/25 rows saved.
Table successfully saved.

Saving table TTUSER.DEPARTMENTS
  Saving foreign key constraint DEPT_LOC_FK
  Saving rows...
  27/27 rows saved.
Table successfully saved.

Saving table TTUSER.EMPLOYEES
  Saving index TTUSER.TTUNIQUE_0
  Saving foreign key constraint EMP_DEPT_FK
  Saving foreign key constraint EMP_JOB_FK
  Saving rows...
  107/107 rows saved.
Table successfully saved.

Saving table TTUSER.JOBS
  Saving rows...
  19/19 rows saved.
Table successfully saved.

Saving table TTUSER.JOB_HISTORY
  Saving foreign key constraint JHIST_DEPT_FK
  Saving foreign key constraint JHIST_EMP_FK
  Saving foreign key constraint JHIST_JOB_FK
  Saving rows...
  10/10 rows saved.
Table successfully saved.

Saving table TTUSER.LOCATIONS
  Saving foreign key constraint LOC_C_ID_FK
  Saving rows...
  23/23 rows saved.
Table successfully saved.

Saving table TTUSER.REGIONS
  Saving rows...
  4/4 rows saved.
Table successfully saved.

Saving view TTUSER.EMP_DETAILS_VIEW
View successfully saved.

Saving sequence TTUSER.DEPARTMENTS_SEQ
Sequence successfully saved.

Saving sequence TTUSER.EMPLOYEES_SEQ
Sequence successfully saved.

Saving sequence TTUSER.LOCATIONS_SEQ
Sequence successfully saved.

Unload the database from memory. This example assumes a RAM policy of manual. See Specifying a RAM Policy in the Oracle TimesTen In-Memory Database Operations Guide for information on the RAM policy.

% ttAdmin -ramUnload database1
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
Database State                  : Closed

Stop the TimesTen main daemon.

% ttDaemonAdmin -stop
TimesTen Daemon (PID: 28436, port: 6624) stopped.

Copy the migrated object files (/tmp/database1.data, in this example) to a file system that is accessible by the instance in the new release.

For the new release:

Create the subdirectory into which you will download and unzip the new full distribution of TimesTen. Navigate to this directory and download the new full distribution into this directory. Then, use the ZIP utility to unpack this distribution. This example creates the new_installation_dir subdirectory and unpacks the timesten2211250.server.linux8664.zip file. Unzipping the timesten2211250.server.linux8664.zip file creates the new installation that will be used for this patch upgrade.
```
% mkdir -p new_installation_dir
% cd new_installation_dir
```
Download the full distribution into the new_installation_dir subdirectory. Then use the ZIP utility to unpack the distribution.
```
% unzip /timesten/installations/timesten2211250.server.linux8664.zip
Archive:  timesten/installations/timesten2211250.server.linux8664.zip
   creating: tt2211250/
...
```

Run the ttInstanceCreate utility to create the instance. This example runs the ttInstanceCreate utility interactively. See ttInstanceCreate in the Oracle TimesTen In-Memory Database Reference and "Creating an Instance on Linux/UNIX: Basics" in this book for details.

User input is shown in bold.

% new_installation_dir/bin/ttInstanceCreate

NOTE: Each TimesTen instance is identified by a unique name.
      The instance name must be a non-null alphanumeric string, not longer
      than 255 characters.

Please choose an instance name for this installation? [ tt221 ] myinstance
Instance name will be 'myinstance'.
Is this correct? [ yes ]
Where would you like to install the myinstance instance of TimesTen? [ /home/ttuser ] /scratch/ttuser
The directory /scratch/ttuser/myinstance does not exist.
Do you want to create it? [ yes ]
Creating instance in /scratch/ttuser/myinstance ...

NOTE: If you are configuring TimesTen for use with Oracle Clusterware, the
      daemon port number must be the same across all TimesTen installations
      managed within the same Oracle Clusterware cluster.

NOTE: All installations that replicate to each other must use the same daemon
      port number that is set at installation time. The daemon port number can
      be verified by running 'ttVersion'.

The default port number is 6624.

Do you want to use the default port number for the TimesTen daemon? [ yes ]
The daemon will run on the default port number (6624).

In order to use the cache features in any TimesTen databases
created within this instance, you must set a value for the TNS_ADMIN
environment variable. It can be left blank, and a value can be supplied later
using <install_dir>/bin/ttInstanceModify.

Please enter a value for TNS_ADMIN (s=skip)? [  ] s
What is the TCP/IP port number that you want the TimesTen Server to listen on? [ 6625 ]

Would you like to use TimesTen Replication with Oracle Clusterware? [ no ]

Would you like to use systemd to manage TimesTen? [ no ]

NOTE: The TimesTen daemon startup/shutdown scripts have not been installed.

The startup script is located here :
        '/scratch/ttuser/myinstance/startup/tt_myinstance'

Run the 'setuproot' script :
        /scratch/ttuser/myinstance/bin/setuproot -install
This will move the TimesTen startup script into its appropriate location.

The 22.1 Release Notes are located here :
  'new_installation_dir/tt22.1.1.1.0/README.html'

Starting the daemon ...
TimesTen Daemon (PID: 2214, port: 6624) startup OK.
Instance created successfully.

From the instance of the new release, create a database. Ensure you have sourced the environment variables, made all necessary changes to your connection attributes in the sys.odbc.ini (or the odbc.ini) file, and started the daemon (if not already started).

To create the database:

% ttIsql -connStr "DSN=mynewdatabase;AutoCreate=1" -e "quit"

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



connect "DSN=mynewdatabase;AutoCreate=1";
Connection successful: DSN=mynewdatabase;UID=ttuser;DataStore=/scratch/ttuser/mynewdatabase;
DatabaseCharacterSet=AL32UTF8;ConnectionCharacterSet=AL32UTF8;LogBufMB=1024;PermSize=500;
TempSize=300;
(Default setting AutoCommit=1)

quit;
Disconnecting...
Done.

The database will be empty at this point.

From the instance of the new release, run the ttMigrate utility with the -r and -relaxedUpgrade options to restore the backed up database to the new release. For example:

% $TIMESTEN_HOME/bin/ttMigrate -r -relaxedUpgrade mynewdatabase /tmp/database1.data

Restoring profile DEFAULT
Profile successfully restored.

Restoring profile SYSTEM
Profile successfully restored.

Restoring table TTUSER.JOBS
  Restoring rows...
  19/19 rows restored.
Table successfully restored.

Restoring table TTUSER.REGIONS
  Restoring rows...
  4/4 rows restored.
Table successfully restored.

Restoring table TTUSER.COUNTRIES
  Restoring rows...
  25/25 rows restored.
  Restoring foreign key dependency COUNTR_REG_FK on TTUSER.REGIONS
Table successfully restored.

Restoring table TTUSER.LOCATIONS
  Restoring rows...
  23/23 rows restored.
  Restoring foreign key dependency LOC_C_ID_FK on TTUSER.COUNTRIES
Table successfully restored.

Restoring table TTUSER.DEPARTMENTS
  Restoring rows...
  27/27 rows restored.
  Restoring foreign key dependency DEPT_LOC_FK on TTUSER.LOCATIONS
Table successfully restored.

Restoring table TTUSER.EMPLOYEES
  Restoring rows...
  107/107 rows restored.
  Restoring foreign key dependency EMP_DEPT_FK on TTUSER.DEPARTMENTS
  Restoring foreign key dependency EMP_JOB_FK on TTUSER.JOBS
Table successfully restored.

Restoring table TTUSER.JOB_HISTORY
  Restoring rows...
  10/10 rows restored.
  Restoring foreign key dependency JHIST_DEPT_FK on TTUSER.DEPARTMENTS
  Restoring foreign key dependency JHIST_EMP_FK on TTUSER.EMPLOYEES
  Restoring foreign key dependency JHIST_JOB_FK on TTUSER.JOBS
Table successfully restored.

Restoring view TTUSER.EMP_DETAILS_VIEW
View successfully restored.

Restoring sequence TTUSER.DEPARTMENTS_SEQ
Sequence successfully restored.

Restoring sequence TTUSER.EMPLOYEES_SEQ
Sequence successfully restored.

Restoring sequence TTUSER.LOCATIONS_SEQ
Sequence successfully restored.

Once the database is operational in the new release, create a backup of this database to have a valid restoration point for your database. Once you have created a backup of your database, you may delete the ttMigrate copy of your database (in this example, /tmp/database1.data). Optionally, for the old release, you can remove the instance and delete the installation.

Ensure you recompile and relink existing ODBC and OCI applications after you perform the upgrade and before you use the new release of TimesTen. See Overview of ODBC API Incompatibilities in the Oracle TimesTen In-Memory Database C Developer's Guide for more information.