Installing HAKOM TSM on Oracle

Requirements

A list of supported Oracle Versions can be found here: System Requirements

To run the scripts Oracle SQL*Plus® is required. This tool can be downloaded for your platform from the Oracle websites directly.

A full installation of the Oracle Client is not necessary. All HAKOM Products include their own copy of Oracles portable driver libraries.

Depending on whether a new HAKOM database is to be created or an existing one is to be updated, please refer to the sections below.

Path to install package

All neccessary scripts for Oracle can be found under the following path: HAKOM TSM Standard_3.9.x.x\_Database Scripts\Oracle

New installation

This section describes how to set up a fresh HAKOM database on an existing Oracle instance.

Preparing scripts

Basic components

The file install.sql under ...\Database Scripts\Oracle represents the main script. It contains several sections for different components of the HAKOM database system. Commenting-in or out individual sections determines which of these components are installed when executing the scripts.

For a fresh install, the sections

Creating HAKOM tablespaces and user
Creating HAKOM ACL objects,
Creating HAKOM TSM objects and
Creating HAKOM Framework objects

should be activated. (remove the "--" preceding each relevant line

Example install.sql

SQL

-- The script contents are thoroughly commented out.
-- It is necessary to activate the required scripts manually.
-- In case of an error the script aborts at the point where the error occurs.
set feedback off
set scan on

WHENEVER SQLERROR EXIT

prompt ********** Setting variables
@install.ini

prompt ********** Creating HAKOM tablespaces and user
@connect_SYSTEM.sql
@'_specific scripts\HAKOM_Framework_CreateTablespaceUser.sql'
disc
@pause

prompt ********** Creating/Updating HAKOM ACL objects
@connect_ACL.sql
prompt &&Username_of_ACL/...
@HAKOM_ACL_Update.sql
DEFINE Username_of_PROTOCOL = &Username_of_ACL
@HAKOM_Protocol_Update
disc
@pause

prompt ********** Creating/Updating HAKOM Framework objects
@connect_FW.sql
prompt &&Username_of_FW/...
@HAKOM_Framework_Update.sql
DEFINE Username_of_PROTOCOL = &Username_of_FW
@HAKOM_Protocol_Update
disc
@pause

prompt ********** Creating/Updating HAKOM TSM objects
@connect_TSM.sql
prompt &&Username_of_TSM/...
@HAKOM_TSM_Update.sql
disc
@pause

set feedback on

In the next step, each compontent enabled in the previous must be configured. Comment-in the initial scripts from version 3.8.0 up to the latest version in the respective SQL scripts.

Component	Script	Example
ACL (User Management and Access Control)	HAKOM_ACL_Update.sql	Example HAKOM_ACL_Update.sql SQL -- The content of the script is in general commented out. -- It is necessary to activate the particular scripts manually. -- In case of error the script will interrupt at the position at which it occured. set feedback off set scan on WHENEVER SQLERROR EXIT --ACL DB Version 3.8.0 for new installation @V3.8.0/Initialscripts/HAKOM_Framework_ACL.sql --Initial data loading V3.8.0 @V3.8.0/Initialscripts/HAKOM_Framework_ACL_InitialData.sql @V3.8.2/V3.8.2.0/HAKOM_ACL.sql @V3.8.4/V3.8.4.0/HAKOM_ACL.sql @V3.8.5/V3.8.5.0/HAKOM_ACL.sql @compile_ACL.sql set feedback on
Time Series Storage	HAKOM_Framework_Update.sql	Example Framework_Update.sql SQL -- The content of the script is in general commented out. -- It is necessary to activate the particular scripts manually. -- In case of error the script will interrupt at the position at which it occured. set feedback off set scan on WHENEVER SQLERROR EXIT --@V3.8.0/V3.8.0.0/HAKOM_Framework.sql --Manual scripts available! Please go to "V3.8.0/V3.8.0.0/Manual scripts after upgrade/HAKOM_Framework" and run the scripts according to the description. --FW DB Version 3.8.0 for new installation @V3.8.0/Initialscripts/HAKOM_Framework.sql --Initial data loading: @V3.8.0/Initialscripts/HAKOM_Framework_InitialData.sql --Manual scripts available! Please go to "V3.8.1/V3.8.1.0/Manual scripts before upgrade/HAKOM_Framework" and run the scripts according to the description. @V3.8.1/V3.8.1.0/HAKOM_Framework.sql @V3.8.2/V3.8.2.0/HAKOM_Framework.sql --Following script adjusts the index for FWT_TSDATAAUDIT and can thereore take longer (depending on ammount of data): @V3.8.2/V3.8.2.0/HAKOM_Framework_Index.sql @V3.8.3/V3.8.3.0/HAKOM_Framework.sql --Following script adjusts several indices and can therefore take longer (depending on ammount of data): @V3.8.3/V3.8.3.0/HAKOM_Framework_Index.sql --Manual scripts available! Please go to "V3.8.4/V3.8.4.0/Manual scripts before upgrade/HAKOM_Framework" and run the scripts according to the description. @V3.8.4/V3.8.4.0/HAKOM_Framework.sql @V3.8.4/V3.8.4.1/HAKOM_Framework.sql @V3.8.4/V3.8.4.5/HAKOM_Framework.sql @V3.8.5/V3.8.5.0/HAKOM_Framework.sql @V3.8.6/V3.8.6.0/HAKOM_Framework.sql --Following script adjusts several indices and can therefore take longer (depending on ammount of data): @V3.8.6/V3.8.6.0/HAKOM_Framework_Index.sql @V3.8.7/V3.8.7.0/HAKOM_Framework.sql @compile_FW.sql set feedback on
Logging	HAKOM_Protocol_Update.sql	Example HAKOM_Protocol_Update.sql SQL `-- The content of the script is in general commented out. -- It is necessary to activate the particular scripts manually. -- In case of error the script will interrupt at the position at which it occured. set feedback off set scan on WHENEVER SQLERROR EXIT --Initialscript V3_8_0 @V3.8.0/Initialscripts/HAKOM_Framework_Protocol.sql @V3.8.3/V3.8.3.0/HAKOM_Framework_Protocol.sql @V3.8.6/V3.8.6.0/HAKOM_Framework_Protocol.sql @compile_Protocol.sql set feedback on`
Additional Storage required by TSM APP	HAKOM_TSM_Update.sql	Example HAKOM_TSM_Update.sql SQL `-- The content of the script is in general commented out. -- It is necessary to activate the particular scripts manually. -- In case of error the script will interrupt at the position at which it occured. set feedback off set scan on WHENEVER SQLERROR EXIT --TSM DB Version 3.8.0: new installation @V3.8.0/Initialscripts/HAKOM_TSM.sql @V3.8.5/V3.8.5.0/HAKOM_TSM.sql set feedback on`

Configuring install.ini

Finally, under ...\Database Scripts\Oracle the file install.ini must be configured. Using a text editor, assign values to the defined variables that are used in the scripts (note: the values must be inserted between single quotes (' '). )

Description of the variables in install.ini

PY

-- ----------------------------------------
-- Environment variables / passwords
-- ----------------------------------------
define Connect_Identifier = ''				#<--- Name of the DB (not of the schema) or the complete connection string
-- ----------------------------------------
-- Passwort
-- ----------------------------------------
define Password_of_SYSTEM 	= ''			#<--- DB Server admin password
define Password_of_ACL          = ''		#<--- Password of the schema is defined here
define Password_of_FW		= ''			#<--- Password of the schema is defined here
define Password_of_TSM          = ''

-- ----------------------------------------
-- USER
-- ----------------------------------------
define Username_of_ACL  	= ''			#<--- Name of the schema is defined here
define Username_of_FW		= ''			#<--- Name of the schema is defined here
define Username_of_TSM  	= ''

-- ----------------------------------------
-- TABLESPACES
-- ----------------------------------------
define DataTableSpace	= ''				#<--- The tablespaces to use
define IndexTableSpace	= ''				#<--- The tablespaces to use
define TempTableSpace	= ''				#<--- The tablespaces to use
-- ----------------------------------------
-- DATAFILE
-- ATTENTION! If using ASM managed tablespaces, the ASM disk group must be specified by the plus-character: +
-- Because otherwise the tablespaces will be created in the file system.
-- Example: define DataDataFile= +DATA\HAKOM_DATA
-- ----------------------------------------
define DataDataFile	= ''					#<--- Path under which the tablespaces should be physically created (should contain path for the DataTableSpace)
define DataIndexFile	= ''				#<--- Path under which the tablespaces should be physically created (should contain path for the IndexTableSpace)

Executing the scripts

Now go to the local script directory and set the parameters for SQLPlus as followed:

POWERSHELL

SET ORACLE_HOME= (Oracle client path)
CHCP 65001 (UTF-8 code page required for UTF-8 scripts)

Then start SQLPlus and install.sql in the folder with the scripts:

POWERSHELL

sqlplus /nolog

POWERSHELL

@install.sql

Milliseconds support

To activate time series with intervals less than one second, a change must be made to all databases used by the TSM during installation. The database contains the function CA_InternalTimeUnit. It returns a fixed value:

0, seconds, beginn: 1972-01-01T00:00:00+01:00
1 (default), seconds, beginn: 1970-01-01T00:00:00Z
2, milliseconds, beginn: 1970-01-01T00:00:00Z

On Oracle Server the above function can be found under: "Functions"

The return value of the above function must be altered with the according numeric representation of the required time unit.

SQL

create or replace FUNCTION "CA_INTERNALTIMEUNIT" RETURN NUMBER
IS
BEGIN
        --0: Seconds, HAKOM-Time
		--1: Seconds, Epoch-Time
		--2: Millisecond, Epoch-Time
		RETURN 0;
END CA_InternalTimeUnit;

HAKOM Compressed Storage

In order to view compressed data on the Database directly, Java is required on the Database server, and has to be activated:

CODE

loadjava –user [Schema]/[Password][@Database] commons-codec-1.6.jar

This is optional and in no way required for using and interacting with compressed time series through the TSM or other HAKOM solutions.

Update

This section describes how to upgrade an existing database which already has some HAKOM components installed to a more recent version.

Preparation

At first the current database version of ACL, FW, Protocol and TSM objects has to be checked. To do that, check the entries in the table DBVERSION. The interpretation of the entries is explained in the following example:

Table DBVERSION:

ID	NAME	DESCRIPTION	CREATIONDATE	REVISION	PATCH	MAJOR	MINOR
1	HAKOM TSM	Initialscript	01.07.2020	4	0	3	8
2	HAKOM Framework	-	01.07.2020	7	0	3	8

Interpretation:

Version HAKOM TSM 3.8.4.0

Version HAKOM Framework 3.8.7.0

Manual check scripts

Before the update scripts are installed, it is important to check if manual check scripts have to be installed. You can find them under ...\Databasae Scripts\Oracle\V3.8.x\V3.8.x.x\_manual check script and if necessary, run them manually and follow the instructions within the scripts.

Preparing scripts

Basic components

The file install.sql under ...\Database Scripts\Oracle represents the main script. Commenting in or out determines which sub-scripts are called.

In install.sql the sections Create HAKOM Tablespaces and User, Create HAKOM-ACL Objects, Create HAKOM-TSM Objects and Create HAKOM-FW Objects must be activated.

Example install.sql

SQL

-- The content of the script is in general commented out.
-- It is necessary to activate the particular scripts manually.
-- In case of error the script will interrupt at the position at which it occured.
set feedback off
set scan on

WHENEVER SQLERROR EXIT

prompt ********** Setting variables
@install.ini

--prompt ********** Creating HAKOM tablespaces and user
--@connect_SYSTEM.sql
--@'_specific scripts\HAKOM_Framework_CreateTablespaceUser.sql'
--disc
--@pause


--prompt ********** Creating/Updating HAKOM ACL objects
@connect_ACL.sql
prompt &&Username_of_ACL/...
@HAKOM_ACL_Update.sql
DEFINE Username_of_PROTOCOL = &Username_of_ACL
@HAKOM_Protocol_Update
disc
@pause


--prompt ********** Creating/Updating HAKOM Framework objects
@connect_FW.sql
prompt &&Username_of_FW/...
@HAKOM_Framework_Update.sql
DEFINE Username_of_PROTOCOL = &Username_of_FW
@HAKOM_Protocol_Update
disc
@pause

--prompt ********** Creating/Updating HAKOM TSM objects
@connect_TSM.sql
prompt &&Username_of_TSM/...
@HAKOM_TSM_Update.sql
disc
@pause

set feedback on

Comment all lines in the update scripts up to (and including) the currently installed version.

Example for an update from 3.8.4 to 3.8.7

Component	Script	Example
ACL (User Management and Access Control)	HAKOM_ACL_Update.sql	Example HAKOM_ACL_Update.sql SQL -- The content of the script is in general commented out. -- It is necessary to activate the particular scripts manually. -- In case of error the script will interrupt at the position at which it occured. set feedback off set scan on WHENEVER SQLERROR EXIT --ACL DB Version 3.8.0 for new installation --@V3.8.0/Initialscripts/HAKOM_Framework_ACL.sql --Initial data loading V3.8.0 --@V3.8.0/Initialscripts/HAKOM_Framework_ACL_InitialData.sql --@V3.8.2/V3.8.2.0/HAKOM_ACL.sql --@V3.8.4/V3.8.4.0/HAKOM_ACL.sql @V3.8.5/V3.8.5.0/HAKOM_ACL.sql @compile_ACL.sql set feedback on
Time Series Storage	HAKOM_Framework_Update.sql	Example Framework_Update.sql SQL -- The content of the script is in general commented out. -- It is necessary to activate the particular scripts manually. -- In case of error the script will interrupt at the position at which it occured. set feedback off set scan on WHENEVER SQLERROR EXIT --@V3.8.0/V3.8.0.0/HAKOM_Framework.sql --Manual scripts available! Please go to "V3.8.0/V3.8.0.0/Manual scripts after upgrade/HAKOM_Framework" and run the scripts according to the description. --FW DB Version 3.8.0 for new installation --@V3.8.0/Initialscripts/HAKOM_Framework.sql --Initial data loading: --@V3.8.0/Initialscripts/HAKOM_Framework_InitialData.sql --Manual scripts available! Please go to "V3.8.1/V3.8.1.0/Manual scripts before upgrade/HAKOM_Framework" and run the scripts according to the description. --@V3.8.1/V3.8.1.0/HAKOM_Framework.sql --@V3.8.2/V3.8.2.0/HAKOM_Framework.sql --Following script adjusts the index for FWT_TSDATAAUDIT and can thereore take longer (depending on ammount of data): --@V3.8.2/V3.8.2.0/HAKOM_Framework_Index.sql --@V3.8.3/V3.8.3.0/HAKOM_Framework.sql --Following script adjusts several indices and can therefore take longer (depending on ammount of data): --@V3.8.3/V3.8.3.0/HAKOM_Framework_Index.sql --Manual scripts available! Please go to "V3.8.4/V3.8.4.0/Manual scripts before upgrade/HAKOM_Framework" and run the scripts according to the description. --@V3.8.4/V3.8.4.0/HAKOM_Framework.sql --@V3.8.4/V3.8.4.1/HAKOM_Framework.sql --@V3.8.4/V3.8.4.5/HAKOM_Framework.sql @V3.8.5/V3.8.5.0/HAKOM_Framework.sql @V3.8.6/V3.8.6.0/HAKOM_Framework.sql --Following script adjusts several indices and can therefore take longer (depending on ammount of data): @V3.8.6/V3.8.6.0/HAKOM_Framework_Index.sql @V3.8.7/V3.8.7.0/HAKOM_Framework.sql @compile_FW.sql set feedback on
Logging	HAKOM_Protocol_Update.sql	Example HAKOM_Protocol_Update.sql SQL `-- The content of the script is in general commented out. -- It is necessary to activate the particular scripts manually. -- In case of error the script will interrupt at the position at which it occured. set feedback off set scan on WHENEVER SQLERROR EXIT --Initialscript V3_8_0 --@V3.8.0/Initialscripts/HAKOM_Framework_Protocol.sql --@V3.8.3/V3.8.3.0/HAKOM_Framework_Protocol.sql @V3.8.6/V3.8.6.0/HAKOM_Framework_Protocol.sql @compile_Protocol.sql set feedback on`
Additional Storage required by TSM APP	HAKOM_TSM_Update.sql	Example HAKOM_TSM_Update.sql SQL `-- The content of the script is in general commented out. -- It is necessary to activate the particular scripts manually. -- In case of error the script will interrupt at the position at which it occured. set feedback off set scan on WHENEVER SQLERROR EXIT --TSM DB Version 3.8.0: new installation --@V3.8.0/Initialscripts/HAKOM_TSM.sql @V3.8.5/V3.8.5.0/HAKOM_TSM.sql set feedback on`

Configuring install.ini

Finally, under ...\Database Scripts\Oracle the file install.ini must be configured. Using a text editor, assign values to the defined variables which will then be used in the other scripts (note: the values must be inserted between ' '. )

Description of the variables in install.ini

PY

-- ----------------------------------------
-- Environment variables / passwords
-- ----------------------------------------
define Connect_Identifier = ''
-- ----------------------------------------
-- Passwort
-- ----------------------------------------
define Password_of_Admin		= ''
define Password_of_ACL			= ''
define Password_of_FW			= ''
define Password_of_TSM			= ''
-- ----------------------------------------
-- USER
-- ----------------------------------------
define Username_of_Admin		= ''
define Username_of_ACL			= ''
define Username_of_FW			= ''
define Username_of_TSM			= ''
-- ----------------------------------------
-- TABLESPACES
-- ----------------------------------------
define DataTableSpace	= ''
define IndexTableSpace	= ''
define TempTableSpace	= ''
-- ----------------------------------------
-- DATAFILE
-- ATTENTION! If using ASM managed tablespaces, the ASM disk group must be specified by the plus-character: +
-- Because otherwise the tablespaces will be created in the file system.
-- Example: define DataDataFile= +DATA\HAKOM_DATA
-- ----------------------------------------
define DataDataFile	= ''
define DataIndexFile	= ''

Executing the scripts

Now go to the local script directory and set the following environment variables:

POWERSHELL

SET ORACLE_HOME= (Oracle client path)
CHCP 65001 (UTF-8 code page required for UTF-8 scripts)

Then start SQLPlus and install.sql in the folder with the FW scripts:

POWERSHELL

sqlplus /nolog

POWERSHELL

@install.sql