Skip to content

How to Setup Data Archiving

Before starting to archive data, there are some prerequisites that you need to do.

Configure Data Archive Process

The data archive process is controlled by two System Parameters. Data Archive process startup enables the data archiving process and Data Archive process startup interval, sets the time frame to start the process.

The data archive process gets the start date and time when you save the parameter values.  Using dbms_job.next_date(job, next_date) can change the next executing date for the data archive process.

When started it reads Data Archive process startup interval to determine how often it should wake up and puts an entry in Oracle’s job scheduler.

When stopped it removes the entry from Oracle’s job scheduler.

Using the Monitoring/Application Monitoring in Solution Manager, you can monitor the data archive process. If anything goes wrong in the data archive process that can’t be handled by normal error handling, the error is written in Oracle’s alert log file.

The data archive process can be monitored using Runtime Monitoring Server Processes form. The data archive process name is Data_Archive_Util_API.Process_Archive_(0). It is also important to look in Oracle’s own alert.log file for fatal errors that can’t be handled inside the data archive process.

The Data Archive Process

The data archive process is the process that executes all archiving.

  1. Wait until its time to wakeup, depending on the system parameter Data Archive process startup interval.
  2. Get data archive orders that have a next order date less than current time and are set to active. The archive orders are sorted by next execution date and order id.
  3. Set next order date for the data archive order with information from the execution plan.
  4. Get all data archive order executions that are active, sorted by sequence no.
  5. Get all the master rows for the data archive object connected to the execution. For every master, restricted by master where-clause and execution parameters, set a savepoint, move the master to archive destination.
  6. Get the all rows for the master’s first child table. Execute the first child row, move the child row to the data archive destination. Archive the child’s children and so on. When getting back remove the child row. Process the next child row.
  7. When all children are processed, remove the master. If everything goes right, commit the transaction and process the next master. When all master rows are processed, write to the archive log. If anything goes wrong rollback to savepoint and write to the data archive log and exit the execution. Triggers the event Data Archive Executed if event is enabled.
  8. Process the next execution and when all executions for one data archive order are executed; Get the next data archive order.
  9. When all data archive orders are finished go to sleep and wait for next time to wakeup.

Install Data Archive Objects

Below follows a description of how to install a running data archive object:

  1. Define a data archive object. You can also deploy an exported data archive object directly in the database. For this go to Data Archive Objects
  2. If the data archive destination is Oracle Table;
    • Create the destination schema owner if other than appowner.
    • Create a database link pointing from appowner to the destination schema owner (if destination owner is other than appowner).
    • Get the.cre file for creating destination tables using Generate Archive Storage in the Data Archive Object form.
    • Deploy the destination tables by running the above file logged on as destination schema directly in the database.
  3. Export the Archive Object Package using the Generate Archive Package in Data Archive Objects Form to a file and deploy this file directly in the database.
  4. Make sure to enable the Archive Object. Otherwise, you will not be able to Archive.
  5. Create a data archive order and a data archive execution in the Data Archive Order form. Data archive execution parameters may also be defined. Check the master statement using Check master statement on the data archive Order execution.
  6. Enable the Data Archive Order and the executions.
  7. View the data archive destination files or data archive destination tables or the Data Archive Log to see the results of the data archive process.

Calculate Initial Archive Execution Time

When archiving a data archive object the first time on a production database, where it exists a lot of objects to archive, it is wise to start with calculating the time it would take to execute an exact amount of objects. Then you can calculate an approximation on how long the whole operation will take. This can be accomplished by doing the following:

  1. Set up the archive object with data archive destination None and data archive type None for all tables within the archive object. Generate the archive package.
  2. Run the select statement for the master manually in SQL*Plus or a similar tool to calculate how many objects will be archived the first time. ”SELECT count(*) FROM master_table” + the master table where-clause and eventually execution parameter values.
    You can see the SQL statement generated in Data archive object form with method Check Master Statement.
  3. Define a data archive order that executes the data archive object. Restrict the data archive object master where-clause with the following statement: ’AND  rownum <= x’, where x is a number you choose depending on how many objects you want to execute and see how long it takes to execute them. Execute the data archive order and look in the data archive log how long it took to execute the data archive object.
  4. (The total amount / the restricted amount) * the time to execute the restricted amount;
    this formula will give you an estimation of how long the first execution of the data archive object will take.
  5. If the description above went well, change the data archive destination and data archive type for the tables to the one wanted to use in production and run again with the restricted where-clause. It is a good idea to test the data archive execution in a test environment first or run the data archive execution for only one data archive object to verify that it does the right thing. Remember that executing the data archive object with data archive type set to Move, Copy or Remove will take some more time depending on network speed (Oracle tables in another Oracle instance) and how much data there is in the data archive object tables.

If the calculated time for the first execution of the data archive objects is longer than the scheduled time for batch jobs, it is a good idea to restrict the master where-clause so the data archive process finishes in time. Perhaps the data archive object must be run with the restricted where-clause for a while before the data archive process has caught up with the production of new objects to archive.

Note: Data Archive Objects, Data Archive Orders and Data Archive Order Executions has a parameter called Active connected to them. If the data archive process should be able to process a data archive execution all the three active parameters must be set to TRUE (checked) otherwise the data archive process don’t process the data archive order execution.