8 Information Provisioning

Information provisioning makes information available when and where it is needed. Information provisioning is part of Oracle grid computing, which pools large numbers of servers, storage areas, and networks into a flexible, on-demand computing resource for enterprise computing needs. Information provisioning uses many of the features that also are used for information integration.

This chapter contains these topics:

Overview of Information Provisioning
Bulk Provisioning of Large Amounts of Information
Incremental Information Provisioning with Streams
On-Demand Information Access

See Also:

Chapter 16, "Using Information Provisioning"
Oracle Database Concepts for more information about information integration

Overview of Information Provisioning

Oracle grid computing enables resource provisioning with features such as Oracle Real Application Clusters (RAC), Oracle Scheduler, and Database Resource Manager. RAC enables you to provision hardware resources by running a single Oracle database server on a cluster of physical servers. Oracle Scheduler enables you to provision database workload over time for more efficient use of resources. Database Resource Manager provisions resources to database users, applications, or services within an Oracle database.

In addition to resource provisioning, Oracle grid computing also enables information provisioning. Information provisioning delivers information when and where it is needed, regardless of where the information currently resides on the grid. In a grid environment with distributed systems, the grid must move or copy information efficiently to make it available where it is needed.

Information provisioning can take the following forms:

Bulk Provisioning of Large Amounts of Information: Data Pump export/import, transportable tablespaces, the DBMS_STREAMS_TABLESPACE_ADM package, and the DBMS_FILE_TRANSFER package all are ways to provide large amounts of information. Data Pump export/import enables you to move or copy information at the database, tablespace, schema, or table level. Transportable tablespaces enables you to move or copy tablespaces from one database to another efficiently. The procedures in the DBMS_STREAMS_TABLESPACE_ADM package enable you to clone, detach, and attach tablespaces. In addition, some procedures in this package enable you to store tablespaces in a tablespace repository that provides versioning of tablespaces. When tablespaces are needed, they can be pulled from the tablespace repository and plugged into a database. The procedures in the DBMS_FILE_TRANSFER package enable you to copy a binary file within a database or between databases.
Incremental Information Provisioning with Streams: Some data must be shared as it is created or changed, rather than occasionally shared in bulk. Oracle Streams can stream data between databases, nodes, or blade farms in a grid and can keep two or more copies synchronized as updates are made.
On-Demand Information Access: You can make information available without moving or copying it to a new location. Oracle Distributed SQL allows grid users to access and integrate data stored in multiple Oracle databases and, through Gateways, non-Oracle databases.

These information provisioning capabilities can be used individually or in combination to provide a full information provisioning solution in your environment. The remaining sections in this chapter discuss the ways to provision information in more detail.

See Also:

Oracle Database Oracle Clusterware and Oracle Real Application Clusters Administration and Deployment Guide for more information about RAC
Oracle Database Administrator's Guide for information about Oracle Scheduler and Database Resource Manager

Bulk Provisioning of Large Amounts of Information

Oracle provides several ways to move or copy large amounts of information from database to database efficiently. Data Pump can export and import at the database, tablespace, schema, or table level. There are several ways to move or copy a tablespace set from one Oracle database to another. Transportable tablespaces can move or copy a subset of an Oracle database and "plug" it in to another Oracle database. Transportable tablespace from backup with RMAN enables you to move or copy a tablespace set while the tablespaces remain online. The procedures in the DBMS_STREAMS_TABLESPACE_ADM package combine several steps that are required to move or copy a tablespace set into one procedure call.

Each method for moving or copying a tablespace set requires that the tablespace set is self-contained. A self-contained tablespace has no references from the tablespace pointing outside of the tablespace. For example, if an index in the tablespace is for a table in a different tablespace, then the tablespace is not self-contained. A self-contained tablespace set has no references from inside the set of tablespaces pointing outside of the set of tablespaces. For example, if a partitioned table is partially contained in the set of tablespaces, then the set of tablespaces is not self-contained. To determine whether a set of tablespaces is self-contained, use the TRANSPORT_SET_CHECK procedure in the Oracle supplied package DBMS_TTS.

The following sections describe the options for moving or copying large amounts of information and when to use each option:

Data Pump Export/Import
Transportable Tablespace from Backup with RMAN
DBMS_STREAMS_TABLESPACE_ADM Procedures
Options for Bulk Information Provisioning

Data Pump Export/Import

Data Pump export/import can move or copy data efficiently between databases. Data Pump can export/import a full database, tablespaces, schemas, or tables to provision large or small amounts of data for a particular requirement. Data Pump exports and imports can be performed using command line clients (expdp and impdp) or the DBMS_DATAPUMP package.

A transportable tablespaces export/import is specified using the TRANSPORT_TABLESPACES parameter. Transportable tablespaces enables you to unplug a set of tablespaces from a database, move or copy them to another location, and then plug them into another database. The transport is quick because the process transfers metadata and files. It does not unload and load the data. In transportable tablespaces mode, only the metadata for the tables (and their dependent objects) within a specified set of tablespaces are unloaded at the source and loaded at the target. This allows the tablespace datafiles to be copied to the target Oracle database and incorporated efficiently.

The tablespaces being transported can be either dictionary managed or locally managed. Moving or copying tablespaces using transportable tablespaces is faster than performing either an export/import or unload/load of the same data. To use transportable tablespaces, you must have the EXP_FULL_DATABASE and IMP_FULL_DATABASE role. The tablespaces being transported must be read-only during export, and the export cannot have a degree of parallelism greater than 1.

See Also:

Oracle Database Utilities for more information about Data Pump
Oracle Database Administrator's Guide for more information about using Data Pump with the TRANSPORT_TABLESPACES option

Transportable Tablespace from Backup with RMAN

The Recovery Manager (RMAN) TRANSPORT TABLESPACE command copies tablespaces without requiring that the tablespaces be in read-only mode during the transport process. Appropriate database backups must be available to perform RMAN transportable tablespace from backup.

See Also:

DBMS_STREAMS_TABLESPACE_ADM Procedures

The following procedures in the DBMS_STREAMS_TABLESPACE_ADM package can be used to move or copy tablespaces:

ATTACH_TABLESPACES: Uses Data Pump to import a self-contained tablespace set previously exported using the DBMS_STREAMS_TABLESPACE_ADM package, Data Pump export, or the RMAN TRANSPORT TABLESPACE command.
CLONE_TABLESPACES: Uses Data Pump export to clone a set of self-contained tablespaces. The tablespace set can be attached to a database after it is cloned. The tablespace set remains in the database from which it was cloned.
DETACH_TABLESPACES: Uses Data Pump export to detach a set of self-contained tablespaces. The tablespace set can be attached to a database after it is detached. The tablespace set is dropped from the database from which it was detached.
PULL_TABLESPACES: Uses Data Pump export/import to copy a set of self-contained tablespaces from a remote database and attach the tablespace set to the current database.

In addition, the DBMS_STREAMS_TABLESPACE_ADM package also contains the following procedures: ATTACH_SIMPLE_TABLESPACE, CLONE_SIMPLE_TABLESPACE, DETACH_SIMPLE_TABLESPACE, and PULL_SIMPLE_TABLESPACE. These procedures operate on a single tablespace that uses only one datafile instead of a tablespace set.

File Group Repository

In the context of a file group, a file is a reference to a file stored on hard disk. A file is composed of a file name, a directory object, and a file type. The directory object references the directory in which the file is stored on hard disk. A version is a collection of related files, and a file group is a collection of versions.

A file group repository is a collection of all of the file groups in a database. A file group repository can contain multiple file groups and multiple versions of a particular file group.

For example, a file group named reports can store versions of sales reports. The reports can be generated on a regular schedule, and each version can contain the report files. The file group repository can version the file group under names such as sales_reports_v1, sales_reports_v2, and so on.

File group repositories can contain all types of files. You can create and manage file group repositories using the DBMS_FILE_GROUP package.

See Also:

"Using a File Group Repository"
Oracle Database PL/SQL Packages and Types Reference for more information about the DBMS_FILE_GROUP package

Tablespace Repository

A tablespace repository is a collection of tablespace sets in a file group repository. Tablespace repositories are built on file group repositories, but tablespace repositories only contain the files required to move or copy tablespaces between databases. A file group repository can store versioned sets of files, including, but not restricted to, tablespace sets.

Different tablespace sets can be stored in a tablespace repository, and different versions of a particular tablespace set can also be stored. A version of a tablespace set in a tablespace repository consists of the following files:

The Data Pump export dump file for the tablespace set
The Data Pump log file for the export
The datafiles that make up the tablespace set

All of the files in a version can reside in a single directory, or they can reside in different directories. The following procedures can move or copy tablespaces with or without using a tablespace repository:

ATTACH_TABLESPACES
CLONE_TABLESPACES
DETACH_TABLESPACES

If one of these procedures is run without using a tablespace repository, then a tablespace set is moved or copied, but it is not placed in or copied from a tablespace repository. If the CLONE_TABLESPACES or DETACH_TABLESPACES procedure is run using a tablespace repository, then the procedure places a tablespace set in the repository as a version of the tablespace set. If the ATTACH_TABLESPACES procedure is run using a tablespace repository, then the procedure copies a particular version of a tablespace set from the repository and attaches it to a database.

When to Use a Tablespace Repository

A tablespace repository is useful when you need to store different versions of one or more tablespace sets. For example, a tablespace repository can be used to accomplish the following goals:

You want to run quarterly reports on a tablespace set. You can clone the tablespace set quarterly for storage in a versioned tablespace repository, and a specific version of the tablespace set can be requested from the repository and attached to another database to run the reports.
You want applications to be able to attach required tablespace sets on demand in a grid environment. You can store multiple versions of several different tablespace sets in the tablespace repository. Each tablespace set can be used for a different purpose by the application. When the application needs a particular version of a particular tablespace set, the application can scan the tablespace repository and attach the correct tablespace set to a database.

Differences Between the Tablespace Repository Procedures

The procedures that include the file_group_name parameter in the DBMS_STREAMS_TABLESPACE_ADM package behave differently with regard to the tablespace set, the datafiles in the tablespace set, and the export dump file. Table 8-1 describes these differences.

Table 8-1 Tablespace Repository Procedures

Procedure Tablespace Set Datafiles Export Dump File

Procedure	Tablespace Set	Datafiles	Export Dump File
`ATTACH_TABLESPACES`	The tablespace set is added to the local database.	If the `datafiles_directory_object` parameter is non-`NULL`, then the datafiles are copied from their current location(s) for the version in the tablespace repository to the directory object specified in the `datafiles_directory_object` parameter. The attached tablespace set uses the datafiles that were copied. If the `datafiles_directory_object` parameter is `NULL`, then the datafiles are not moved or copied. The datafiles remain in the directory object(s) for the version in the tablespace repository, and the attached tablespace set uses these datafiles.	If the `datafiles_directory_object` parameter is non-`NULL`, then the export dump file is copied from its directory object for the version in the tablespace repository to the directory object specified in the `datafiles_directory_object` parameter. If the `datafiles_directory_object` parameter is `NULL`, then the export dump file is not moved or copied.
`CLONE_TABLESPACES`	The tablespace set is retained in the local database.	The datafiles are copied from their current location(s) to the directory object specified in the `tablespace_directory_object` parameter or in the default directory for the version or file group. This parameter specifies where the version of the tablespace set is stored in the tablespace repository. The current location of the datafiles can be determined by querying the `DBA_DATA_FILES` data dictionary view. A directory object must exist, and must be accessible to the user who runs the procedure, for each datafile location.	The export dump file is placed in the directory object specified in the `tablespace_directory_object` parameter or in the default directory for the version or file group.
`DETACH_TABLESPACES`	The tablespace set is dropped from the local database.	The datafiles are not moved or copied. The datafiles remain in their current location(s). A directory object must exist, and must be accessible to the user who runs the procedure, for each datafile location. These datafiles are included in the version of the tablespace set stored in the tablespace repository.	The export dump file is placed in the directory object specified in the `export_directory_object` parameter or in the default directory for the version or file group.

ATTACH_TABLESPACES

The tablespace set is added to the local database.

If the datafiles_directory_object parameter is non-NULL, then the datafiles are copied from their current location(s) for the version in the tablespace repository to the directory object specified in the datafiles_directory_object parameter. The attached tablespace set uses the datafiles that were copied.

If the datafiles_directory_object parameter is NULL, then the datafiles are not moved or copied. The datafiles remain in the directory object(s) for the version in the tablespace repository, and the attached tablespace set uses these datafiles.

If the datafiles_directory_object parameter is non-NULL, then the export dump file is copied from its directory object for the version in the tablespace repository to the directory object specified in the datafiles_directory_object parameter.

If the datafiles_directory_object parameter is NULL, then the export dump file is not moved or copied.

CLONE_TABLESPACES

The tablespace set is retained in the local database.

The datafiles are copied from their current location(s) to the directory object specified in the tablespace_directory_object parameter or in the default directory for the version or file group. This parameter specifies where the version of the tablespace set is stored in the tablespace repository. The current location of the datafiles can be determined by querying the DBA_DATA_FILES data dictionary view. A directory object must exist, and must be accessible to the user who runs the procedure, for each datafile location.

The export dump file is placed in the directory object specified in the tablespace_directory_object parameter or in the default directory for the version or file group.

DETACH_TABLESPACES

The tablespace set is dropped from the local database.

The datafiles are not moved or copied. The datafiles remain in their current location(s). A directory object must exist, and must be accessible to the user who runs the procedure, for each datafile location. These datafiles are included in the version of the tablespace set stored in the tablespace repository.

The export dump file is placed in the directory object specified in the export_directory_object parameter or in the default directory for the version or file group.

Remote Access to a Tablespace Repository

A tablespace repository can reside in the database that uses the tablespaces, or it can reside in a remote database. If it resides in a remote database, then a database link must be specified in the repository_db_link parameter when you run one of the procedures, and the database link must be accessible to the user who runs the procedure.

Only One Tablespace Version Can Be Online in a Database

A version of a tablespace set in a tablespace repository can be either online or offline in a database. A tablespace set version is online in a database when it is attached to the database using the ATTACH_TABLESPACES procedure. Only a single version of a tablespace set can be online in a database at a particular time. However, the same version or different versions of a tablespace set can be online in different databases at the same time. In this case, it might be necessary to ensure that only one database can make changes to the tablespace set.

Tablespace Repository Procedures Use the DBMS_FILE_GROUP Package Automatically

Although tablespace repositories are built on file group repositories, it is not necessary to use the DBMS_FILE_GROUP package to create a file group repository before using one of the procedures in the DBMS_STREAMS_TABLESPACE_ADM package. If you run the CLONE_TABLESPACES or DETACH_TABLESPACES procedure and specify a file group that does not exist, then the procedure creates the file group automatically.

A Tablespace Repository Provides Versioning but Not Source Control

A tablespace repository provides versioning of tablespace sets, but it does not provide source control. If two or more versions of a tablespace set are changed at the same time and placed in a tablespace repository, then these changes are not merged.

Read-Only Tablespaces Requirement During Export

The procedures in the DBMS_STREAMS_TABLESPACE_ADM package that perform a Data Pump export make any read/write tablespace being exported read-only. After the export is complete, if a procedure in the DBMS_STREAMS_TABLESPACE_ADM package made a tablespace read-only, then the procedure makes the tablespace read/write.

Automatic Platform Conversion for Tablespaces

When one of the procedures in the DBMS_STREAMS_TABLESPACE_ADM package moves or copies tablespaces to a database that is running on a different platform, the procedure can convert the datafiles to the appropriate platform if the conversion is supported. The V$TRANSPORTABLE_PLATFORM dynamic performance view lists all platforms that support cross-platform transportable tablespaces.

When a tablespace repository is used, the platform conversion is automatic if it is supported. When a tablespace repository is not used, you must specify the platform to which or from which the tablespace is being converted.

See Also:

Chapter 16, "Using Information Provisioning" for information about using the procedures in the DBMS_STREAMS_TABLESPACE_ADM package, including usage scenarios
Oracle Database PL/SQL Packages and Types Reference for reference information about the DBMS_STREAMS_TABLESPACE_ADM package and the DBMS_FILE_GROUP package

Options for Bulk Information Provisioning

Table 8-2 describes when to use each option for bulk information provisioning.

Table 8-2 Options for Moving or Copying Tablespaces

Option	Use this Option Under these Conditions
Data Pump export/import	You want to move or copy data at the database, tablespace, schema, or table level. You want to perform each step required to complete the Data Pump export/import.
Data Pump export/import with the `TRANSPORT_TABLESPACES` option	The tablespaces being moved or copied can be read-only during the operation. You want to perform each step required to complete the Data Pump export/import.
Transportable tablespace from backup with the RMAN `TRANSPORT` `TABLESPACE` command	The tablespaces being moved or copied must remain online (writeable) during the operation.
`DBMS_STREAMS_TABLESPACE_ADM` procedures without a tablespace repository	The tablespaces being moved or copied can be read-only during the operation. You want to combine multiple steps in the Data Pump export/import into one procedure call. You do not want to use a tablespace repository for the tablespaces being moved or copied.
`DBMS_STREAMS_TABLESPACE_ADM` procedures with a tablespace repository	The tablespaces being moved or copied can be read-only during the operation. You want to combine multiple steps in the Data Pump export/import into one procedure call. You want to use a tablespace repository for the tablespaces being moved or copied. You want platform conversion to be automatic.

Incremental Information Provisioning with Streams

Streams can share and maintain database objects in different databases at each of the following levels:

Database
Schema
Table
Table subset

Streams can keep shared database objects synchronized at two or more databases. Specifically, a Streams capture process captures changes to a shared database object in a source database's redo log, one or more propagations propagate the changes to another database, and a Streams apply process applies the changes to the shared database object. If database objects are not identical at different databases, then Streams can transform them at any point in the process. That is, a change can be transformed during capture, propagation, or apply. In addition, Streams provides custom processing of changes during apply with apply handlers. Database objects can be shared between Oracle databases, or they can be shared between Oracle and non-Oracle databases through the use of Oracle Transparent Gateways. In addition to data replication, Streams provides messaging, event management and notification, and data warehouse loading.

A combination of Streams and bulk provisioning enables you to copy and maintain a large amount of data by running a single procedure. The following procedures in the DBMS_STREAMS_ADM package use Data Pump to copy data between databases and configure Streams to maintain the copied data incrementally:

MAINTAIN_GLOBAL configures a Streams environment that replicates changes at the database level between two databases.
MAINTAIN_SCHEMAS configures a Streams environment that replicates changes to specified schemas between two databases.
MAINTAIN_SIMPLE_TTS clones a simple tablespace from a source database to a destination database and uses Streams to maintain this tablespace at both databases.
MAINTAIN_TABLES configures a Streams environment that replicates changes to specified tables between two databases.
MAINTAIN_TTS uses transportable tablespaces with Data Pump to clone a set of tablespaces from a source database to a destination database and uses Streams to maintain these tablespaces at both databases.

In addition, the PRE_INSTANTIATION_SETUP and POST_INSTANTIATION_SETUP procedures configure a Streams environment that replicates changes either at the database level or to specified tablespaces between two databases. These procedures must be used together, and instantiation actions must be performed manually, to complete the Streams replication configuration.

Using these procedures, you can export data from one database, ship it to another database, reformat the data if the second database is on a different platform, import the data into the second database, and start syncing the data with the changes happening in the first database. If the second database is on a grid, then you have just migrated your application to a grid with one command.

These procedures can configure Streams clients to maintain changes originating at the source database in a single-source replication environment, or they can configure Streams clients to maintain changes originating at both databases in a bidirectional replication environment. By maintaining changes to the data, it can be kept synchronized at both databases. These procedures can either perform these actions directly, or they can generate one or more scripts that performs these actions.

See Also:

Chapter 1, "Introduction to Streams"
Oracle Database PL/SQL Packages and Types Reference for reference information about the DBMS_STREAMS_ADM package
Oracle Streams Replication Administrator's Guide for information about using the DBMS_STREAMS_ADM package

On-Demand Information Access

Users and applications can access information without moving or copying it to a new location. Distributed SQL allows grid users to access and integrate data stored in multiple Oracle and, through Oracle Transparent Gateways, non-Oracle databases. Transparent remote data access with distributed SQL allows grid users to run their applications against any other database without making any code change to the applications. While integrating data and managing transactions across multiple data stores, the Oracle database optimizes the execution plans to access data in the most efficient manner.

See Also:

Oracle Database Administrator's Guide for information about distributed SQL
Oracle Database Heterogeneous Connectivity Administrator's Guide for more information about Oracle Transparent Gateways