How To Use Transportable Tablespaces To Copy OLAP Analytic Workspaces (Doc ID 438035.1)

Last updated on OCTOBER 29, 2019

Applies to:

Goal

This document describes how to rapidly copy a tablespace holding an Analytic Workspace using the Data Pump expdp and impdp facility.

This is different from utilizing the OLAP component's facility of exporting an Analytic Workspace to a so-called EIF file and re-creating the Analytic Workspace by importing the EIF file.

Terminology

Transportable Tablespace is the concept that permits copying tablespaces without having to write all the data contained in it to a dump file and import the data from there. Only control information (metadata) is written to the dump file and the data is copied using the datafiles.

Source instance is the instance that holds the Analytic Workspace data that should be copied to another instance.

Target instance is the instance that should receive the Analytic Workspace from the source instance.

Assumptions

The source and target instances that are involved in this process are of identical platform. The Analytic Workspace internal structure will be copied verbatim, and no conversions will take place. This process will not work if different platforms (say, Windows 32 and Linux x86-64) are involved. If you are using different platforms, you need to use EIF file transfer instead.

The process described here handles entire tablespaces. Analytic Workspaces are stored in tables named AW$name, where name is the name of the Analytic Workspace.

At creation time, Analytic Workspaces can be forced to reside in a dedicated tablespace. This is the preferred scenario since the process described here moved entire tablespaces.

This note does not cover Automatic Storage Management (ASM). If one or both of the instances use ASM, an extra step needs to be performed to move the datafiles out of ASM and / or into ASM. This can slow the process down significantly.

Please note: If the tablespace has already been transported before, then the AW will not be directly useful.
There is an article 853335.1 that covers the additional steps required.

You can check this by checking the dba_tablespaces view:

If the PLUGGED_IN value is YES, then please follow the instructions in this Note.


Advantages

• Datafiles are moved directly. There is no need to create dump files or EIF files, often containing huge amounts of data.
• No additional disk space is required to hold dump files of significant size; data files are copied directly and are utilized by the source and target instances without export/import or conversion.
• The process can be performed entirely with RDBMS and OS tools - no OLAP DML is required.
• Switchover to the new data is fast and does not depend on the amount of data moved.

Disadvantages

• The Analytic Workspaces are not reorganized or optimized during the process. Using EIF files in contrast will optimize the AW and may perform better and use less space.
• Tablespaces on the source system that contain the Analytic Workspaces have to be set to Read Only access for the time the metadata dump file is created and while copying datafiles.
• Tablespaces holding the Analytic Workspaces on the target system have to be dropped and re-created. This will cause a (very brief) unavailability of the Analytic Workspaces.
• EIF files are platform independent, where transportable tablespaces are not (in regard to OLAP).

Example useage

Load new data into an Analytic Workspace using a staging instance.
The data is loaded using the staging instance, the production system is instance for queries.
When all the data has been loaded and verified, the AW can be copied to the production instance (or made available through a shared disk storage device).

The process of activating the new version of the Analytic Workspace can be accomplished very quickly as the duration of the switchover depends on the number of objects, not their size.

Since the data is loaded on the staging system and queried on the production system at different times, the RDBMS does not have to maintain too many copies of older data during the data load. In contrast, loading data into an Analytic Workspace while at the same time querying it will require overhead to keep data in a consistent state.

Solution

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.