The PL/SQL script Arc2Trezor.sql (located in the installation directory - the Templates\Oracle subdirectory) allows to create depository databases of archive database on Oracle platforms.
This manual assumes at least basic knowledge on the Oracle management (creating tablespaces, work with SqlPlus).
Depository databases can be created with optional period that may differ from the parameter TrezorPeriod. The script Arc2Trezor.sql can be run in the SqlPlus tool on Windows or Linux platforms. It supports creating depository databases in archive database or different database (see the configuration parameter TNS_Service_Name_Trezor). The procedure to use the script Arc2Trezor.sql is following:

  1. Editing the script Arc2Trezor.sql and definition of parameters.
  2. Running the script in SqlPlus and starting the saved procedure that will generate the file Arc2Trezor_out.sql.
  3. If depository databases are being created in archive database or in different database:
    • execution of the file Arc2Trezor_out.sql in SqlPlus (depository in archive database),
    • execution of the file Arc2Trezor_out.sql in SqlPlus, creation and starting batch file (depository databases in different database).

1. Editing the script Arc2Trezor.sql and definition of parameters

  1. Save the script Arc2Trezor.sql on a disk.
  2. Modify the following parameters in the script header:


    3. Parameter Description
    TrezorId Number of depository database that is to be created. Positive integer (1,2,3,...) - later depository means a higher number.
    AppName Application name. The names of depository database tablespaces are to be composed of the application name and the number of depository database.
    TrezorPath Directory to store datafiles of depository database tablespace (see the configuration parameter TrezorPath). You must also enter the slash at the end. Example:
    • C:\ora920\oradata\d2000\ (Windows)
    • /opt/ora9/oradata/D2000/ (Linux)
    TrezorCountDatafiles Number of datafiles in tablespace (see the configuration parameter TrezorCountDatafiles).
    TrezorStartDatafileSize Initial size of datafile  (see the configuration parameter TrezorStartDatafileSize).
    TrezorMaxDatafileSize Maximum size of datafile (see the configuration parameter TrezorMaxDatafileSize).
    TrezorDatafileSizeStep Size of datafile increment (see the configuration parameter TrezorDatafileSizeStep).
    StartDateUTC, EndDateUTC Time interval of data exported to the depository database (in UTC time).

    Note:     Values in depository database are saved in UTC time, values in Oracle archive usually in UTC + 2 hrs.

    If depository database is in different database, you must also define the following parameters:

    Parameters Description
    TrezorsInArchiveDatabase Its value must be TRUE.
    ArchivDBLogonInfo Name, password and TNS for logon to archive (aplikacia_archiv/swx@D2000, by default).
    TrezorDBLogonInfo Name, password and TNS for logon to the database that will contain depository databases. The database must contain the archive tablespace that is available for the user aplikacia_archiv with the password swx. That tablespace must contain at least one empty table Tabver that belongs to the user aplikacia_archiv.
    OSTypeIsWindows TRUE if SqlPlus will be run on Windows or FALSE if on Linux. The database may be on optional platform.
    ParallelLoading The value of TRUE causes that data export from archive table will be executed in parallel with import of archive data from previous archive table. This setting speeds up the export and the import in case that the archive and the depository database are on different servers and data export/import is executed on workstation that is speed enough (mostly yes - the workstation can handle export as well as import).

2. Running the script in SqlPlus and starting saved procedure that will generate the file Arc2Trezor_out.sql

  1. In the directory where the script Arc2Trezor.sql is placed, run SqlPlus and log on as the user application_archiv:
    sqlplus aplikacia_archiv/swx@D2000
  2. Run the SQL script that creates the saved procedure Arc2Trezor:
    @Arc2Trezor;
  3. Set the SqlPlus output formatting properties and redirect the output to the file:
    SET SERVEROUTPUT ON
    SET LINE 260
    SPOOL .\Arc2Trezor_out.sql
    Note: For Linux platforms, the last line is SPOOL ./Arc2Trezor_out.sql.
  4. Run the procedure Arc2Trezor:
    EXEC Arc2Trezor;
  5. Disable the redirecting of the output to the file:
    SPOOL OFF

3.A. If depository databases are in archive database: execution of the file Arc2Trezor_out.sql in SqlPlus

  1. Using a text editor, remove the last line in the file Arc2Trezor_out.sql:
    SQL>        EXEC Arc2Trezor;
  2. In the file Arc2Trezor_out.sql remove last three lines:
    PL/SQL procedure successfully completed.

    SQL>        SPOOL OFF
  3. Run creating the archive:
    @Arc2Trezor_out;
  4. Exit SqlPlus:
    exit

3.B: If depository databases are in different database: execution of part of file, creation and running batch file

  1. In the file Arc2Trezor_out.sql select the part that begins
    REM /* Start of CreateTrezor.sql                       */
    and ends
    REM /* End of CreateTrezor.sql                         */
    and copy it to the file CreateTrezor.sql
  2. Run SqlPlus and log on as the user application_archiv@depository_database_TNS:
    sqlplus aplication_archiv/swx@depository_database_TNS
  3. By running the script CreateTrezor.sql in SqlPlus create depository database tablespace and basic depository database tables:
    @CreateTrezor;
  4. Exit SqlPlus:
    exit
  5. In the file Arc2Trezor_out.sql select the part that begins
    REM /* Start of Exp.bat                                */
    and ends
    REM /* End of Exp.bat                                  */
    and copy it to the fileExp.bat in the work directory
  6. Copy the file d2expdt.sql (located in the installation directory - the Templates\Oracle subdirectory) to the work directory.
  7. Run
    Exp.bat
    to export data from the archive database into text files on your computer.
    Note: If you do not have enough free space on your disk, enabling the compression of the directory, where the file Exp.bat is stored, reduces the required space (text files will be compressed).
    Note: If the parameter ParallelLoading=TRUE, the file Exp.bat executes export along with import and leave out the following two steps.
  8. In the file Arc2Trezor_out.sql select the part that begins
    REM /* Start of Imp.bat                                */
    and ends
    REM /* End of Imp.bat                                  */
    and copy it to the file Imp.bat in the work directory.
  9. Run
    Imp.bat
    to import the data from the text files into the depository database.
  10. Run SqlPlus and log on as the user application_archiv@depository_database_TNS:
    sqlplus aplikacia_archiv/swx@depository_database_TNS
  11. Perform two commands ALTER TABLESPACE ... placed at the end of the file Arc2Trezor_out.sql below the line
    REM /* End of Imp.bat                                  */
    and exit SqlPlus
    exit

The procedure, described above, must be used for each new depository database. If you first want to reconfigure the archive to create depository database automatically and then create depository databases according to the method described above, it is necessary for the process D2000 Archiv to start creating depository databases not from the number of 1 but from e.g. 13 (if you want to create depository databases 1...12 manually according to the method above). To do that, insert the line containing the number of the last depository database you want to create manually into the table TREZORS in the database that will contain the depository databases:
INSERT INTO TREZORS(ID,TIME_FROM,TIME_TO,STATUS,DATAFILES) VALUES(12,SYSDATE,SYSDATE,40,1)
After the process D2000 Archiv is started, it begins to create the depository databases numbered from 13.
After you create the depository databases 1...12 manually, you must delete the inserted line to avoid creating lines with duplicate ID.

Related pages:

Depository databases

Napíšte komentár