Creating depository databases of archive database on Oracle platform

The PL/SQL script Arc2Trezor.sql (located in the installation directory - the Templates\Oracle subdirectory) allows the creation of depository databases of archive databases on Oracle platforms.
This manual assumes at least basic knowledge of Oracle management (creating tablespaces, working with SqlPlus).
Depository databases can be created with an 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 databases or different databases (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:

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

2. Running the script in SqlPlus and starting the 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 the 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 a 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 working 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 leaves 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 working 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 a depository database automatically and then create depository databases according to the method described above, it is necessary for the D2000 Archiv process 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.