|Oracle® Streams Concepts and Administration
11g Release 2 (11.2)
Part Number E17069-04
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.
The following topics contain information about information provisioning:
Oracle grid computing enables resource provisioning with features such as Oracle Real Application Clusters (Oracle RAC), Oracle Scheduler, and Database Resource Manager. Oracle 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 Oracle 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.
Oracle Real Application Clusters Administration and Deployment Guide for more information about Oracle RAC
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
The following sections describe the options for moving or copying large amounts of information and when to use each option:
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 (
impdp) or the
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 data files 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
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.
The Recovery Manager (RMAN)
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.
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
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:
PULL_SIMPLE_TABLESPACE. These procedures operate on a single tablespace that uses only one data file instead of a tablespace set.
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_v2, and so on.
File group repositories can contain all types of files. You can create and manage file group repositories using the
Oracle Database PL/SQL Packages and Types Reference for more information about the
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 data files that comprise 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:
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
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.
A tablespace repository is useful when you must 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.
The procedures that include the
file_group_name parameter in the
DBMS_STREAMS_TABLESPACE_ADM package behave differently for the tablespace set, the data files in the tablespace set, and the export dump file. Table 35-1 describes these differences.
Table 35-1 Tablespace Repository Procedures
|Procedure||Tablespace Set||Data Files||Export Dump File|
The tablespace set is added to the local database.
The tablespace set is retained in the local database.
The data files are copied from their current location(s) to the directory object specified in the
The export dump file is placed in the directory object specified in the
The tablespace set is dropped from the local database.
The data files are not moved or copied. The data files remain in their current location(s). A directory object must exist, and must be accessible to the user who runs the procedure, for each data file location. These data files 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
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.
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.
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
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 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.
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.
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 data files 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.
Chapter 36, "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
Table 35-2 describes when to use each option for bulk information provisioning.
Table 35-2 Options for Moving or Copying Tablespaces
|Option||Use this Option Under these Conditions|
Data Pump export/import
Data Pump export/import with the
Transportable tablespace from backup with the RMAN
The tablespaces being moved or copied must remain online (writeable) during the operation.
Oracle Streams can share and maintain database objects in different databases at each of the following levels:
Oracle Streams can keep shared database objects synchronized at two or more databases. Specifically, an Oracle Streams capture process or synchronous capture captures changes to a shared database object in a source database, one or more propagations propagate the changes to another database, and an Oracle Streams apply process applies the changes to the shared database object. If database objects are not identical at different databases, then Oracle Streams can transform them at any point in the process. That is, a change can be transformed during capture, propagation, or apply. In addition, Oracle 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 with an Oracle Database Gateway. In addition to data replication, Oracle Streams provides messaging, event management and notification, and data warehouse loading.
A combination of Oracle 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 Oracle Streams to maintain the copied data incrementally:
MAINTAIN_GLOBAL configures an Oracle Streams environment that replicates changes at the database level between two databases.
MAINTAIN_SCHEMAS configures an Oracle Streams environment that replicates changes to specified schemas between two databases.
MAINTAIN_TABLES configures an Oracle 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 Oracle Streams to maintain these tablespaces at both databases.
In addition, the
POST_INSTANTIATION_SETUP procedures configure an Oracle 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 Oracle 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 Oracle Streams clients to maintain changes originating at the source database in a single-source replication environment, or they can configure Oracle 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.
Oracle Database PL/SQL Packages and Types Reference for reference information about the
Oracle Streams Replication Administrator's Guide for information about using the
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 Database Gateway, 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.
Oracle Database Administrator's Guide for information about distributed SQL
Oracle Database Heterogeneous Connectivity User's Guide for more information about Oracle Database Gateway