Depositories on the Oracle platform

The depository database is implemented as an independent tablespace named APPLICATION_TS_TREZORID, where ID=1,2.. etc (e.g. TEST_TS_TREZOR5). The tablespace contains datafiles - their number is defined by the parameter TrezorCountDatafiles (1, by default). Data files are generated in the TrezorPath directory.
If TrezorCountDatafiles=1, the data file name is APPLICATION_TS_TREZORxxID_YYYYMMDD.ORA.
If TrezorCountDatafiles>1, the data file names are APPLICATION_TS_TREZORxxID_01_YYYYMMDD.ORA, APPLICATION_TS_TREZORxxID_02_YYYYMMDD.ORA etc, where xxID is ID enhanced from the left with a zero to the length of 4 characters and YYYYMMDD is the depository database creation date, e.g. TEST_TS_TREZOR0005_03_20060930.ORA. Datafile size is specified by the parameter TrezorStartDatafileSize, the parameter TrezorMaxDatafileSize allows specifying the maximum data file size.

Similarly to Sybase, the configuration of the process D2000 Archiv contains a configured time period TrezorPeriod, during which the process writes data to one depository database. When this period elapses, the database is released and the process starts storing values in a new database. The released database is available for storing delayed data for a given time (configuration parameter TrezorCompressTime). After the expiration of this time, the tablespace is first switched into read-only mode (ALTER TABLESPACE APPLICATION_TS_TREZORID READ ONLY) and then if the value of parameter TrezorCompressOffline=1, switched to offline mode (ALTER TABLESPACE APPLICATION_TS_TREZORID OFFLINE). If the parameters TrezorPath and TrezorCompressPath are defined, data files are to be moved to the TrezorCompressPath directory.
After releasing a depository database (and possibly moving), it is possible to run an external program - its name and path are defined by the parameter TrezorPostCompressCmd, and its parameters are defined by the parameter TrezorPostCompressPar.

The TELL command RELEASE_TREZOR allows releasing a depository database before its time.
The process D2000 Archiv creates its internal list of depository databases - the Trezors table.

Note 1: When switching a tablespace to read-only mode, Oracle requires no active transactions in the whole database. The D2000 Archiv process, therefore, commits the writes to the archive as well as to an active depository database. Therefore it is important for the database containing the tablespace not to be used for other purposes that would require transactions that could take more time because the depository parts of the archive will be blocked until the transactions are finished (see theTrezorReadOnlyTimeout parameter).

Note 2: The Maximum length of a tablespace name in Oracle is 30 characters. That limitation must be taken into account when defining an application name - the name APPLICATION_TS_TREZORID (or APPLICATION_TS_TREZORID_seg in use of depository database segments) cannot be longer than 30 characters.

Note 3: An Oracle database can contain as many data files as defined by the parameter DB_FILES in the initialization file of the database. Therefore, setting up the value of the parameter to a sufficient value before creating the depository databases is enabled.

Depository database segments

Since the 7.01.10 version, the process D2000 Archiv supports the concept of depository database segments on the Oracle platform. Depository database segments is a depository database tablespace, several of which can be created and filled in parallel. Each historical value is stored in one segment, the number of which is specified by the parameter Depository segment in the historical value configuration.

The number of segments created by the process D2000 Archiv is specified by the parameter TrezorCountSegments in the Windows registry. Historical values with the parameter depository segment higher than the value of the parameter TrezorCountSegments are to be stored in the zero depository segment (it is the segment that is also created when depository database segments are disabled).

The main purpose of depository database segments is the creation of several smaller depository databases so that reading one historical value for a long time interval requires less disk space (because only specific segments containing that historical value need to be mounted) - compared to mounting all non-segmented depository databases for a required time interval.
Having depository database segments enabled, the name of the zero segment tablespace is APPLICATION_TS_TREZORID (identical with the name of the depository database tablespace if segments are disabled), and the names of all other segments are APPLICATION_TS_TREZORID_seg, where seg is a double-digit number of the segment (between 01 and TrezorCountSegments).

If TrezorCountDatafiles=1, the names of the data files are APPLICATION_TS_TREZORxxID_Syy_YYYYMMDD.ORA
If TrezorCountDatafiles>1, the names of the data files are APPLICATION_TS_TREZORxxID_Syy_zz_YYYYMMDD.ORA, where xxID is ID enhanced from the left with zeros to the length of 4 characters, yy is the segment number enhanced from the left with a zero to the length of 2 characters, z is the datafile number enhanced from the left with a zero to the length of 2 characters, and YYYYMMDD is the depository database creation time. Example: TEST_TS_TREZOR0005_S02_01_20060930.ORA

Note: Starting with D2000 version 7.02.010, it is possible to specify a different suffix of datafile than ORA by using the parameter TrezorDatafileSuffix.

Note: The default prefix of depository tablespaces and datafiles APPLICATION_TS_TREZOR can be changed using the parameter TrezorPrefix.

Configuration parameters for the Oracle platform:

All the configuration parameters in the Windows Registry are of String type, besides the parameters TrezorCountDatafiles, TrezorCountSegments, TrezorCountDatafiles, and TrezorNoLogging of DWORD type.

• TrezorPath - path to the directory, where the data files of tablespaces are created. You must also enter the character "\" after the directory name. If the parameter is not given, data files are created in the database directory.
• TrezorCompressPath - path to the directory, where the data files of released tablespaces are being moved. You must also enter the character "\" after the directory name. Once the TrezorCompressPath parameter is defined, you must also define TrezorPath, otherwise, data files are not to be moved.
• TrezorPeriod - the period of the depository creation. It is specified in the format NX, where N is an integer number and X defines a time unit.
  Permitted time units are:
  • H - hour
  • D - day
  • W - week
  • M - month.
    Note: Setting the parameter either to an empty value or to 0H disables the functionality of depository databases.
• TrezorCompressOffline - value 1 (default) means that the compressed depository database will be put into offline mode. Value 0 means that the depository database remains in read-only mode and data will be available for reading. Value 2 means that D2000 Archiv will not access the depository database while TrezorPostCompressCmd is running, so this command may perform various maintenance tasks that could otherwise block the archive.
• TrezorCompressTime - time for releasing the depository tablespace. The tablespace is available for writing delayed data during this period. TrezorCompressTime must be less than TrezorPeriod.
• TrezorCountSegments - number of depository database segments (depository database tablespaces), that are being created in parallel. The default value is 0 (only one segment 0 is being created), and the maximum value is 99 (segments 0 to 99 are being created). The parameter is of DWORD type !!!.
• TrezorCountDatafiles - the number of data files in the tablespace, by default, is 1 (the parameter is of DWORD type !!!).
• TrezorStartDatafileSize - the size of the data file that is to be created (in Oracle syntax, e.g., 500K, 300M). The parameter must be specified. 
  Note: For OpenVMS+Oracle 9.2.0.x platform, there is a problem with data files damaged at a size of 4GB - must be tested before use!
• TrezorMaxDatafileSize - maximum size of data file (in Oracle syntax, e.g., 500K, 300M or UNLIMITED). If it is not defined, the size of the data file is specified at its creation (see the note for the previous parameter).
• TrezorDatafileSizeStep - the size of data file increment (in Oracle syntax, e.g., 200K, 1M) during the growth of the datafile (ON NEXT clause of the CREATE TABLESPACE command). If the parameter is not defined, the ON NEXT clause is not used, and the increment is the database default value (8K, by default).
• TrezorDatafileSuffix - suffix of the data file. The default value of this parameter is ORA.
• TrezorPrefix - if this parameter is not set, the prefix of depository tablespaces and datafiles is APPLICATION_TS_TREZOR.
  Setting the value of TrezorPrefix changes this prefix. The parameter can be used together with the DbUsername parameter when migrating the archive database from one application to another (or when renaming an application) and keeping the archive tablespace and existing depositories (i.e. setting the value of TrezorPrefix to OldApp_TS_TREZOR).
• TrezorReadOnlyTimeout - specifies the timeout (in seconds) for switching the depository database into READ ONLY mode. After expiration, the archive generates the system alarm "Changing trezor tablespace read-only takes more than 120 sec, possible Oracle lock!". The default value is 120 seconds.
• TrezorReadSegment0 - Parameter has a meaning only if depository segments are configured (non-zero value of TrezorCountSegments parameter). Activation of a parameter (value 1) instructs the archive to read also from the depository segment 0 when reading from any other depository segment. Reading from depository databases will be slower, but it enables reconfiguring historical values from the default depository segment 0 to a different segment while keeping access to data previously stored in depository segment 0.
  The TrezorReadSegment0 parameter can be changed by the SET_OPTION TELL command.
• TrezorReadSinceCreate - value 1 means that depositories that are older than the Create Time of historical value will not be read. The TrezorReadSinceCreate parameter can be changed by the SET_OPTION TELL command.
• TrezorPostCompressCmd - the name of a program to be run after releasing the depository database and possibly moving data files of the released depository database. The program may be used e.g. to pack data files and copy them via the network.
  Note: before running the command, the environment variables D2000_DBUSER and D2000_DBPASS are set to the name and password that D2000 Archive uses to access the archive database. These variables can be used in a script to connect to the archive database.
• TrezorPostCompressPar - optional parameter, used for the program specified by the parameter TrezorPostCompressCmd. It can contain optional text and predefined macros to be replaced before running the program:
  • #ID# - is replaced by the depository database ID (e.g., 15).
  • #TREZOR# - is replaced by the depository tablespace name (e.g., TEST_TS_TREZOR5).
  • #SEGMENTS# - is replaced by the number of depository database segments (i.e., the value of the parameter TrezorCountSegments).
  • #FILES# - is replaced by the number of data files of the depository database (i.e., the value of the parameter TrezorCountDatafiles).
  • #FILE1#, #FILE2#, etc. - is replaced by the name of the corresponding data file along with the path, if the path is defined by the parameter TrezorPath (e.g. C:\ora920\oradata\D2000\TEST_TS_TREZOR5_1.ORA).
  Example 1: The configuration of depository databases with one data file to be packed into the file TrezorID.zip (ID=1,2, etc.) and moved to the directory D:\backup
  TrezorCountDatafiles 1
  TrezorPostCompressCmd C:\utils\zip.exe
  TrezorPostCompressPar -m D:\backup\Trezor#ID#.zip #FILE1#
  
  After releasing, for example, the depository database nr 5, a program can be run:
  C:\utils\zip.exe -m D:\backup\Trezor5.zip C:\ora920\oradata\D2000\TEST_TS_TREZOR5_1.ORA
  
  Example 2: Configuration, which will remount (using the TELL command) the depository tablespace immediately after it is released by:
  TrezorPostCompressCmd c:\D2000\D2000.E70\bin\tell.exe
  TrezorPostCompressPar dst=SELF.ARC cmd="MOUNT_TREZOR #ID#" uid=myuser pwd=mypassword
• TrezorNoLogging - the parameter of DWORD type, its non-zero value causes the depository tablespace to be created in the NOLOGGING mode. If its value is zero or doesn't exist, the depository tablespace is to be created in the LOGGING mode (it generates REDO logs and is restorable).

Depository databases outside the archive database on the Oracle platform

Oracle-based archives support creating the depository databases in a different database than the archive is in. The TNS of the depository database is defined by the configuration parameter TNS_Service_Name_Trezor. The username and password are the same as in the archive database.
Parameter TNS_Service_Name_Trezor is located in the registry in a branch belonging to the application and archive, e.g., HKEY_LOCAL_MACHINE\Software\Ipesoft\D2000V70\cfg_test\SELF.ARC
The database, in which the depository will be created, must contain the archive tablespace (created in the same way as if the archive was supposed to be located in this database) and appropriate archive user application_name_archiv, e.g., test_archiv.

Common depository database for more archives on the Oracle platform

Oracle-based archives support the configuration where two or more shadow archives work with a common depository database. Only an active instance of the archive uses the depository database. The common depository database is defined by the setting of the configuration parameter Trezor_Active_Only (of REG_DWORD type) to a non-zero value. This parameter is located in the registry in a branch belonging to the application and archive. As the archives are shadow archives, the branch of the archive also contains instance numbers, generally HKEY_LOCAL_MACHINE\Software\Ipesoft\D2000V70\cfg_APPLICATION_NAME\ARCHIVE_NAME.ARC_INSTANCE_NUMBER, e.g. HKEY_LOCAL_MACHINE\Software\Ipesoft\D2000V70\cfg_test\SELF.ARC_2\)

Note 1: Parameter Trezor_Active_Only must be configured on all instances of the shadow archive; otherwise, the archives will block each other from trying to write the same value to the depository database.