A Data Archive Object specifies how and what data that should be archived. It consists of one or more tables defined in a tree hierarchy. The first table is called the master table and it is the driving object when archiving a data archive object. The master table is the only table that restricts what objects that shall be processed by the data archive process. It is possible to restrict the master table either by the where-clause on the master table or by parameters that extends a where-clause on the master’s columns. The parameters are connected to the data archive executions. The tables in the data archive object are connected to each other by pointing back to its parent. Each table is described with its columns. The columns are used to see which columns are the primary keys and what columns are used to point back to the parent table. For the master table it is possible to define which columns that can be used as parameters by the archive executions. Columns used, as parameters are probably scope columns like Company or Site, but can be other parameters like date or time parameters. Restrictions on the master tables where-clause are probably state or status columns.
Note! To define a data archive object requires total knowledge of the data model and the business logic. You must fully understand the impact of moving away some of the data from the application, otherwise data archiving can cause logical errors or fatal errors that are impossible to undo.
It is possible that there exist business objects that are impossible to archive.
Concepts for Data Archiving are described in the section About Data Archiving.
Each data archive object must have a data archive package created before it can be executed. The data archive package holds the code needed for the data archive process to run the archive execution. After changing a data archive object it is needed to regenerate the data archive package so it reflects the changes. The only dynamic information is the master where-clause and the execution parameters.
The data archive package is named “archive_object”_ARC_API.
Note! If there is any trouble with creating of the data archive packages you might have to run the SQL-statement DROP PACKAGE “archive_object”_ARC_API; in SQL*Plus or similar tool. After dropping the package you should be able to generate it again.
Data archiving objects can have its destination either to an Sql File, Oracle Table or to None (a non destination).
Moving data to data archive destination Sql-file requires set up of the database so the database server can write on a server disk. Inserting one line for each directory in Oracle instance parameter initialization file Init.ora makes it possible for the database server to write to the server disks.
A data archive destination SQL File contains insert statements, pointing back to the data archive objects source tables, which restore all objects archived in a data archive execution for a specific data archive object. The rows in one data archive object instance is placed in a hierarchy with the master table first and then the masters children and so on. No transaction handling is included in the files.
Files are named “archive_object”_”exec_id”_”archive_date”.txt, like CUSTOMER_ORDER_NO1_20000401_163204.txt.
#Sample init.ora file #On NT utl_file_dir=C:\production\archiving\customer_order utl_file_dir=C:\production\archiving\maintenance #On Unix utl_file_dir=/production/archiving/customer_order utl_file_dir=/production/archiving/maintenance
Note! When specifying utl_file_dir you must specify one row for each directory. You cannot access subdirectories without inserting one line for each subdirectory. The directory specification is case sensitive and you must spell the directory correctly. The owner process that runs Oracle must have operating system write access to the specified directories. If someone else should access the files, created by the database server, they must have operating system access to the files too.
Note! You must restart the database to set the utl_file_dir parameter.
For additional information see Oracle Manuals about Supplied Packages and read about the Utl_File package.
Data archive destination Oracle Table requires that the destination tables are created either in the same schema or in a schema that can be reached by a database link associated to the data archive object. With a database link you can reach schemas on other instances that can reside on another machine. For performance reasons its recommended to use a quick network connection to the destination instance.
Note! The database link must include a username and password. Anonymous database links will cause an error in the data archive process. Create the database link either as appowner or as a public database link. Database links must be created with a SQL tool outside Foundation1. See Oracle manual SQL Reference for details about database links.
Example:
CREATE PUBLIC DATABASE LINK db_link_name CONNECT TO archive_destination_username IDENTIFIED BY archive_destination_password USING 'service';
Remember that Init.ora parameter global_names defines how database links can be named.
A create table script file for the destination tables can be generated from data archive objects definitions. The create table script file can be executed in the database using SQL Plus to create the destination tables. Remember to log on to Oracle as the destination schema user.
Data archive tables are named as the source tables, except that their extension
is “table_name”_ARC instead of “table_name”_TAB for source tables. Data archive
tables are extended with 2 columns Data_Archive_Date (Date) and
Data_Archive_Id (Varchar2(55)). The columns are used to identify which
data archive execution that made the archive copy of the data. Data archive tables
have a primary key called “table_name”_PKA, instead of “table_name”_PK
for source tables.
None data archive destination alternative is included for creating of a cleanup or for testing of data archiving executions. If using the None destination the only data archiving types that makes sense for the tables in the data archive object is Move and Remove because they removes the object from the source tables. None or Copy will do nothing but can be used for testing of a data archive object.
Each table in a data archive object can have its own data archive type, which tells the data archive process how to handle the rows in that table. There are four different data archive types Move, Remove, Copy and None.
Move copies the data to the destination, if the destination is not set to None, and removes the data from the source. This is the setting that should be used for normal data archiving either to SQL File or Oracle Table.
Note that Move will generate an error if the data has been run with data archive type Copy and data archive destination Oracle Table before, because then the data already exists in the data archive destination. The primary key index on the data archive destination tables will not allow duplicate rows.
Remove just removes the object from the source. This setting should be used with caution though it just removes the data from the source table. It can be useful for creating cleanup executions with data archive destination None. It also can be useful on tables in the hierarchy that is not wanted to archive for some reason, e.g. tables with historical data about its parent table like Customer order line history.
Copy checks if the object exists on the data archive destination and if it exists it removes the object from the destination and copies the object to the destination and leaves the source as it was. Note that if destination is SQL File the object will exist in many files over the time, due to it is not possible to remove the object from the destination SQL File. Copy can be useful for an object that exists and changes under a long period of time and it needs to be archived during that time.
Note that if archiving to archive destination Oracle Table only the latest version of every row is archived and the union of all child rows over the time is archived.
None performs nothing to the object. It just selects the object so the whole tree can be walked through. None is useful when performing tests on a data archive object.
The data archive object window is where you define the data archive objects. In the tree list box you can see all defined data archived objects with its connected tables. Depending on which object you mark different forms will be shown to the right of the tree list box.
In the data archive object detail you define details about the data archive object. Data archive destination can be Oracle Table, SQL File or None. Destination directory is only important when you have chosen data archive destination SQL File. DB link is only important when you have chosen Oracle Table as data archive destination and the destination tables exists on another schema or another instance.
You also define your data archive object tables, starting
with the master table in this view.
| Column | Description |
|---|---|
| Archive Object Id | A unique id identifying a data archive object. The name should be descriptive if possible. |
| Description | Description of the data archive object. |
| Active | If checked then the data archive object is active and will be executed by the data archive process. |
| Data Archive Destination | Destination for archived data. Can have three different values today. Oracle Table, SQL File and None. |
| Destination dir | Directory to be written to if data archive destination is SQL File. |
| DB Link | Database link name. Must be filled if data archive destination is Oracle Table and the data archive destination tables are in another schema or in another instance. LOV. |
Methods for Data Archive Objects:
| Method | Description |
|---|---|
| Generate | Generates the data archive object PL/SQL package needed for the data archive process. This method creates a file which should be deployed directly in the database using a tool like SQL Plus. The data archive package can be viewed from the PL/SQL Package code form. |
| Export Storage | If the data archive destination is Oracle Table this method creates a SQL script file that can be used for creating the data archive destination tables. Deploy this file directly in the database using a tool like SQl Plus logged on as the destination schema owner. The script asks for data archive data and index tablespace and storage clause. It is possible to edit the file for setting own definitions. |
| Export Settings | Exports the values for the data archive object to a SQL file that can be executed at another installation of Foundation1. This file can be included in a release and used in normal installation procedures. |
| Copy | Copies the data archive object to a new data archive object with a new name and description. |
| Check Master Statement | Checks if the master select clause is expanded to a correct SQL statement. Generates an error if anything is wrong. It is possible to copy the statement and try it in SQL*Plus or similar tool. |
| Drop Package | Drops the data archive object PL/SQL package. |
| View archived data | If data archive destination is set to Oracle Table you can use this form to create a query on the archived master table. Its then possible to mark the selected archived data and use method Restore to restore the archived data back to the source tables. |
Marking one of the tables in the data archive object will show the data archive tables detail view. This form will show the details about the tables in the data archive object and also the details about a specific tables column and how the table is connected to its parent table. The columns table alias, hint text and where clause is only interesting information for the master table. Parent table name is only relevant for child tables.
| Column | Description |
|---|---|
| Table name |
Name of the table included in the data archive object. LOV. |
| Description |
Description of the table. |
| Archive Object Id |
Data archive object id. LOV. |
| Table alias |
A table alias can be set for the master table. The table alias can be used in sub-queries in the where clause. |
| Data archive Type | The data archive type decides how the data for this table is handled in the data archive process. The data archive type can have the values Move, Copy, Remove and None. |
| Master table | Checked if the table is the master table. There can and must exist only one master table per data archive object. The first table in the data archive object must be the master table. |
| Parent table name | If the table is the master table then this field must be empty otherwise this field should point to the tables parent. LOV. |
| Hint text | If the table is a master table you can write a hint text used in the select statement that fetches all master records. The hint should only be used when trying to improve performance for the master select. If you want to use the hint /*+ ALL_ROWS */ you should write ALL_ROWS in this field. The select statement expands with /*+ and */. |
| Where clause | If the table is a master table you can write a valid where clause here to restrict the number of master records fetched. You should omit the word WHERE, which the select statement is expanded with. |
In the data archive object table columns window you define how to child tables are connected to its parent. In parent key name column you sets the parent column name connected to the child column name. The columns defined are only used to where-clauses for connecting tables. In all other cases Oracle dictionary is used. The column information is generated into this table when creating the table definition in the previous form. As much information as possible is generated, when entering table information, but sometimes you must correct this generated information.
| Column | Description |
|---|---|
| Column name | Name of the column. LOV. |
| Primary key | Shows which columns that are primary key for this table. |
| Parent Key Name | If the table is not a master table is must at least have one parent key name filled in. This is used to create the where clause between the parent table and the child table. Only the parent keys should be included here. |
| Seq No | Sequence no. Only used for ordering the columns. |
| Allow as Parameter | If the table is a master table one can check those columns that can be used as execution parameters. |