36 Using Information Provisioning

Creating and Populating a Tablespace Repository

This example creates a tablespaces repository and adds a new version of a tablespace set to the repository after each quarter. The tablespace set consists of the sales tablespaces for a business: sales_tbs1 and sales_tbs2.

Figure 36-1 provides an overview of the tablespace repository created in this example:

Figure 36-1 Example Tablespace Repository

Description of "Figure 36-1 Example Tablespace Repository"

The following table shows the tablespace set versions created in this example, their directory objects, and the corresponding file system directory for each directory object.

This example makes the following assumptions:

• The inst1.example.com database exists.

• The sales_tbs1 and sales_tbs2 tablespaces exist in the inst1.example.com database.

The following steps create and populate a tablespace repository:

1. Connect as an administrative user to the database where the sales tablespaces are modified with new sales data. In this example, connect to the inst1.example.com database.

   The administrative user must have the necessary privileges to run the procedures in the DBMS_STREAMS_TABLESPACE_ADM package and must have the necessary privileges to create directory objects.

   See Oracle Database Administrator's Guide for information about connecting to a database in SQL*Plus.

2. Create a directory object for the first quarter in fiscal year 2005 on inst1.example.com:

   CREATE OR REPLACE DIRECTORY q1fy2005 AS '/home/sales/q1fy2005';
   

   The specified file system directory must exist when you create the directory object.

3. Create a directory object that corresponds to the directory that contains the data files for the tablespaces in the inst1.example.com database. For example, if the data files for the tablespaces are in the /orc/inst1/dbs directory, then create a directory object that corresponds to this directory:

   CREATE OR REPLACE DIRECTORY dbfiles_inst1 AS '/orc/inst1/dbs';
   

4. Clone the tablespace set and add the first version of the tablespace set to the tablespace repository:

   DECLARE
     tbs_set DBMS_STREAMS_TABLESPACE_ADM.TABLESPACE_SET;
   BEGIN
     tbs_set(1) := 'sales_tbs1';
     tbs_set(2) := 'sales_tbs2';
     DBMS_STREAMS_TABLESPACE_ADM.CLONE_TABLESPACES(
       tablespace_names            => tbs_set,
       tablespace_directory_object => 'q1fy2005',
       file_group_name             => 'strmadmin.sales',
       version_name                => 'v_q1fy2005');
   END;
   /
   

   The sales file group is created automatically if it does not exist.

5. When the second quarter in fiscal year 2005 is complete, create a directory object for the second quarter in fiscal year 2005:

   CREATE OR REPLACE DIRECTORY q2fy2005 AS '/home/sales/q2fy2005';
   

   The specified file system directory must exist when you create the directory object.

6. Clone the tablespace set and add the next version of the tablespace set to the tablespace repository at the inst1.example.com database:

   DECLARE
     tbs_set DBMS_STREAMS_TABLESPACE_ADM.TABLESPACE_SET;
   BEGIN
     tbs_set(1) := 'sales_tbs1';
     tbs_set(2) := 'sales_tbs2';
     DBMS_STREAMS_TABLESPACE_ADM.CLONE_TABLESPACES(
       tablespace_names            => tbs_set,
       tablespace_directory_object => 'q2fy2005',
       file_group_name             => 'strmadmin.sales',
       version_name                => 'v_q2fy2005');
   END;
   /
   

Steps 5 and 6 can be repeated whenever a quarter ends to store a version of the tablespace set for each quarter. Each time, create a directory object to store the tablespace files for the quarter, and specify a unique version name for the quarter.

Using a Tablespace Repository for Remote Reporting with a Shared File System

This example runs reports at inst2.example.com on specific versions of the sales tablespaces stored in a tablespace repository at inst1.example.com. These two databases share a file system, and the reports that are run on inst2.example.com might make changes to the tablespace. Therefore, the tablespaces are made read/write at inst2.example.com. When the reports are complete, a new version of the tablespace files is stored in a separate directory from the original version of the tablespace files.

Figure 36-2 provides an overview of how tablespaces in a tablespace repository are attached to a different database in this example:

Figure 36-2 Attaching Tablespaces with a Shared File System

Description of "Figure 36-2 Attaching Tablespaces with a Shared File System"

Figure 36-3 provides an overview of how tablespaces are detached and placed in a tablespace repository in this example:

Figure 36-3 Detaching Tablespaces with a Shared File System

Description of "Figure 36-3 Detaching Tablespaces with a Shared File System"

The following table shows the tablespace set versions in the tablespace repository when this example is complete. It shows the directory object for each version and the corresponding file system directory for each directory object. The versions that are new are created in this example. The versions that existed before this example were created in "Creating and Populating a Tablespace Repository".

This example makes the following assumptions:

• The inst1.example.com and inst2.example.com databases exist.

• The inst1.example.com and inst2.example.com databases can access a shared file system.

• Networking is configured between the databases so that these databases can communicate with each other.

• A tablespace repository that contains a version of the sales tablespaces (sales_tbs1 and sales_tbs2) for various quarters exists in the inst1.example.com database. This tablespace repository was created and populated in the example "Creating and Populating a Tablespace Repository".

Complete the following steps:

1. In SQL*Plus, connect to inst1.example.com as an administrative user.

   The administrative user must have the necessary privileges to create directory objects.

   See Oracle Database Administrator's Guide for information about connecting to a database in SQL*Plus.

2. Create a directory object that will store the tablespace files for the first quarter in fiscal year 2005 on inst1.example.com after the inst2.example.com database has completed reporting on this quarter:

   CREATE OR REPLACE DIRECTORY q1fy2005_r AS '/home/sales/q1fy2005_r';
   

   The specified file system directory must exist when you create the directory objects.

3. Connect to the inst2.example.com database as an administrative user.

   The administrative user must have the necessary privileges to run the procedures in the DBMS_STREAMS_TABLESPACE_ADM package, create directory objects, and create database links.

4. Create two directory objects for the first quarter in fiscal year 2005 on inst2.example.com. These directory objects must have the same names and correspond to the same directories on the shared file system as the directory objects used by the tablespace repository in the inst1.example.com database for the first quarter:

   CREATE OR REPLACE DIRECTORY q1fy2005 AS '/home/sales/q1fy2005';
   
   CREATE OR REPLACE DIRECTORY q1fy2005_r AS '/home/sales/q1fy2005_r';
   

5. Create a database link from inst2.example.com to the inst1.example.com database. For example:

   CREATE DATABASE LINK inst1.example.com CONNECT TO strmadmin 
      IDENTIFIED BY password 
      USING 'inst1.example.com';
   

6. Attach the tablespace set to the inst2.example.com database from the strmadmin.sales file group in the inst1.example.com database:

   DECLARE
     tbs_set DBMS_STREAMS_TABLESPACE_ADM.TABLESPACE_SET;
   BEGIN
     DBMS_STREAMS_TABLESPACE_ADM.ATTACH_TABLESPACES(
       file_group_name            => 'strmadmin.sales',
       version_name               => 'v_q1fy2005',
       datafiles_directory_object => 'q1fy2005_r',
       repository_db_link         => 'inst1.example.com',
       tablespace_names           => tbs_set);
   END;
   /
   

   Notice that q1fy2005_r is specified for the datafiles_directory_object parameter. Therefore, the data files for the tablespaces and the export dump file are copied from the /home/sales/q1fy2005 location to the /home/sales/q1fy2005_r location by the procedure. The attached tablespaces in the inst2.example.com database use the data files in the /home/sales/q1fy2005_r location. The Data Pump import log file also is placed in this directory.

   The attached tablespaces use the data files in the /home/sales/q1fy2005_r location. However, the v_q1fy2005 version of the tablespaces in the tablespace repository consists of the files in the original /home/sales/q1fy2005 location.

7. Make the tablespaces read/write at inst2.example.com:

   ALTER TABLESPACE sales_tbs1 READ WRITE;
   
   ALTER TABLESPACE sales_tbs2 READ WRITE;
   

8. Run the reports on the data in the sales tablespaces at the inst2.example.com database. The reports make changes to the tablespaces.

9. Detach the version of the tablespace set for the first quarter of 2005 from the inst2.example.com database:

   DECLARE
     tbs_set  DBMS_STREAMS_TABLESPACE_ADM.TABLESPACE_SET;
   BEGIN
     tbs_set(1) := 'sales_tbs1';
     tbs_set(2) := 'sales_tbs2';
     DBMS_STREAMS_TABLESPACE_ADM.DETACH_TABLESPACES(
       tablespace_names        => tbs_set,
       export_directory_object => 'q1fy2005_r',
       file_group_name         => 'strmadmin.sales',
       version_name            => 'v_q1fy2005_r',
       repository_db_link      => 'inst1.example.com');
   END;
   /
   

   Only one version of a tablespace set can be attached to a database at a time. Therefore, the version of the sales tablespaces for the first quarter of 2005 must be detached from inst2.example.com before the version of this tablespace set for the second quarter of 2005 can be attached.

   Also, notice that the specified export_directory_object is q1fy2005_r, and that the version_name is v_q1fy2005_r. After the detach operation, there are two versions of the tablespace files for the first quarter of 2005 stored in the tablespace repository on inst1.example.com: one version of the tablespace before reporting and one version after reporting. These two versions have different version names and are stored in different directory objects.

10. Connect to the inst1.example.com database as an administrative user.

11. Create a directory object that will store the tablespace files for the second quarter in fiscal year 2005 on inst1.example.com after the inst2.example.com database has completed reporting on this quarter:

    CREATE OR REPLACE DIRECTORY q2fy2005_r AS '/home/sales/q2fy2005_r';
    

    The specified file system directory must exist when you create the directory object.

12. Connect to the inst2.example.com database as an administrative user.

13. Create two directory objects for the second quarter in fiscal year 2005 at inst2.example.com. These directory objects must have the same names and correspond to the same directories on the shared file system as the directory objects used by the tablespace repository in the inst1.example.com database for the second quarter:

    CREATE OR REPLACE DIRECTORY q2fy2005 AS '/home/sales/q2fy2005';
    
    CREATE OR REPLACE DIRECTORY q2fy2005_r AS '/home/sales/q2fy2005_r';
    

14. Attach the tablespace set for the second quarter of 2005 to the inst2.example.com database from the sales file group in the inst1.example.com database:

    DECLARE
      tbs_set DBMS_STREAMS_TABLESPACE_ADM.TABLESPACE_SET;
    BEGIN
      DBMS_STREAMS_TABLESPACE_ADM.ATTACH_TABLESPACES(
        file_group_name            => 'strmadmin.sales',
        version_name               => 'v_q2fy2005',
        datafiles_directory_object => 'q2fy2005_r',
        repository_db_link         => 'inst1.example.com',
        tablespace_names           => tbs_set);
    END;
    /
    

15. Make the tablespaces read/write at inst2.example.com:

    ALTER TABLESPACE sales_tbs1 READ WRITE;
    
    ALTER TABLESPACE sales_tbs2 READ WRITE;
    

16. Run the reports on the data in the sales tablespaces at the inst2.example.com database. The reports make changes to the tablespace.

17. Detach the version of the tablespace set for the second quarter of 2005 from inst2.example.com:

    DECLARE
      tbs_set  DBMS_STREAMS_TABLESPACE_ADM.TABLESPACE_SET;
    BEGIN
      tbs_set(1) := 'sales_tbs1';
      tbs_set(2) := 'sales_tbs2';
      DBMS_STREAMS_TABLESPACE_ADM.DETACH_TABLESPACES(
        tablespace_names        => tbs_set,
        export_directory_object => 'q2fy2005_r',
        file_group_name         => 'strmadmin.sales',
        version_name            => 'v_q2fy2005_r',
        repository_db_link      => 'inst1.example.com');
    END;
    /
    

Steps 11-17 can be repeated whenever a quarter ends to run reports on each quarter.

Using a Tablespace Repository for Remote Reporting without a Shared File System

This example runs reports at inst3.example.com on specific versions of the sales tablespaces stored in a tablespace repository at inst1.example.com. These two databases do not share a file system, and the reports that are run on inst3.example.com do not make any changes to the tablespace. Therefore, the tablespaces remain read-only at inst3.example.com, and, when the reports are complete, there is no need for a new version of the tablespace files in the tablespace repository on inst1.example.com.

Figure 36-4 provides an overview of how tablespaces in a tablespace repository are attached to a different database in this example:

Figure 36-4 Attaching Tablespaces without a Shared File System

Description of "Figure 36-4 Attaching Tablespaces without a Shared File System"

The following table shows the directory objects used in this example. It shows the existing directory objects that are associated with tablespace repository versions on the inst1.example.com database, and it shows the new directory objects created on the inst3.example.com database in this example. The directory objects that existed before this example were created in "Creating and Populating a Tablespace Repository".

This example makes the following assumptions:

• The inst1.example.com and inst3.example.com databases exist.

• The inst1.example.com and inst3.example.com databases do not share a file system.

• Networking is configured between the databases so that they can communicate with each other.

• The sales tablespaces (sales_tbs1 and sales_tbs2) exist in the inst1.example.com database.

Complete the following steps:

1. In SQL*Plus, connect to the inst3.example.com database as an administrative user.

   The administrative user must have the necessary privileges to run the procedures in the DBMS_STREAMS_TABLESPACE_ADM package, create directory objects, and create database links.

   See Oracle Database Administrator's Guide for information about connecting to a database in SQL*Plus.

2. Create a database link from inst3.example.com to the inst1.example.com database. For example:

   CREATE DATABASE LINK inst1.example.com CONNECT TO strmadmin 
      IDENTIFIED BY password 
      USING 'inst1.example.com';
   

3. Create a directory object for the first quarter in fiscal year 2005 on inst3.example.com. Although inst3.example.com is a remote database that does not share a file system with inst1.example.com, the directory object must have the same name as the directory object used by the tablespace repository in the inst1.example.com database for the first quarter. However, the directory paths of the directory objects on inst1.example.com and inst3.example.com do not need to match.

   CREATE OR REPLACE DIRECTORY q1fy2005 AS '/usr/sales_data/fy2005q1';
   

   The specified file system directory must exist when you create the directory object.

4. Connect to the inst1.example.com database as an administrative user.

   The administrative user must have the necessary privileges to run the procedures in the DBMS_FILE_TRANSFER package and create database links. This example uses the DBMS_FILE_TRANSFER package to copy the tablespace files from inst1.example.com to inst3.example.com. If some other method is used to transfer the files, then the privileges to run the procedures in the DBMS_FILE_TRANSFER package are not required.

5. Create a database link from inst1.example.com to the inst3.example.com database. For example:

   CREATE DATABASE LINK inst3.example.com CONNECT TO strmadmin 
      IDENTIFIED BY password 
      USING 'inst3.example.com';
   

   This database link will be used to transfer files to the inst3.example.com database in Step 6.

6. Copy the data file for each tablespace and the export dump file for the first quarter to the inst3.example.com database:

   BEGIN
     DBMS_FILE_TRANSFER.PUT_FILE(
       source_directory_object      => 'q1fy2005',
       source_file_name             => 'sales_tbs1.dbf',
       destination_directory_object => 'q1fy2005',
       destination_file_name        => 'sales_tbs1.dbf',
       destination_database         => 'inst3.example.com');
     DBMS_FILE_TRANSFER.PUT_FILE(
       source_directory_object      => 'q1fy2005',
       source_file_name             => 'sales_tbs2.dbf',
       destination_directory_object => 'q1fy2005',
       destination_file_name        => 'sales_tbs2.dbf',
       destination_database         => 'inst3.example.com');
     DBMS_FILE_TRANSFER.PUT_FILE(
       source_directory_object      => 'q1fy2005',
       source_file_name             => 'expdat16.dmp',
       destination_directory_object => 'q1fy2005',
       destination_file_name        => 'expdat16.dmp',
       destination_database         => 'inst3.example.com');
   END;
   /
   

   Before you run the PUT_FILE procedure for the export dump file, you can query the DBA_FILE_GROUP_FILES data dictionary view to determine the name and directory object of the export dump file. For example, run the following query to list this information for the export dump file in the v_q1fy2005 version:

   COLUMN FILE_NAME HEADING 'Export Dump|File Name' FORMAT A35
   COLUMN FILE_DIRECTORY HEADING 'Directory Object' FORMAT A35
   
   SELECT FILE_NAME, FILE_DIRECTORY FROM DBA_FILE_GROUP_FILES
     where FILE_GROUP_NAME = 'SALES' AND
           VERSION_NAME    = 'V_Q1FY2005';
   

7. Connect to the inst3.example.com database as an administrative user.

8. Attach the tablespace set for the first quarter of 2005 to the inst3.example.com database from the sales file group in the inst1.example.com database:

   DECLARE
     tbs_set DBMS_STREAMS_TABLESPACE_ADM.TABLESPACE_SET;
   BEGIN
     DBMS_STREAMS_TABLESPACE_ADM.ATTACH_TABLESPACES(
       file_group_name            => 'strmadmin.sales',
       version_name               => 'v_q1fy2005',
       datafiles_directory_object => 'q1fy2005',
       repository_db_link         => 'inst1.example.com',
       tablespace_names           => tbs_set);
   END;
   /
   

   The tablespaces are read-only when they are attached. Because the reports on inst3.example.com do not change the tablespaces, the tablespaces can remain read-only.

9. Run the reports on the data in the sales tablespaces at the inst3.example.com database.

10. Drop the tablespaces and their contents at inst3.example.com:

    DROP TABLESPACE sales_tbs1 INCLUDING CONTENTS;
    
    DROP TABLESPACE sales_tbs2 INCLUDING CONTENTS;
    

    The tablespaces are dropped from the inst3.example.com database, but the tablespace files remain in the directory object.

11. Create a directory object for the second quarter in fiscal year 2005 on inst3.example.com. The directory object must have the same name as the directory object used by the tablespace repository in the inst1.example.com database for the second quarter. However, the directory paths of the directory objects on inst1.example.com and inst3.example.com do not need to match.

    CREATE OR REPLACE DIRECTORY q2fy2005 AS '/usr/sales_data/fy2005q2';
    

    The specified file system directory must exist when you create the directory object.

12. Connect to the inst1.example.com database as an administrative user.

13. Copy the data file and the export dump file for the second quarter to the inst3.example.com database:

    BEGIN
      DBMS_FILE_TRANSFER.PUT_FILE(
        source_directory_object      => 'q2fy2005',
        source_file_name             => 'sales_tbs1.dbf',
        destination_directory_object => 'q2fy2005',
        destination_file_name        => 'sales_tbs1.dbf',
        destination_database         => 'inst3.example.com');
      DBMS_FILE_TRANSFER.PUT_FILE(
        source_directory_object      => 'q2fy2005',
        source_file_name             => 'sales_tbs2.dbf',
        destination_directory_object => 'q2fy2005',
        destination_file_name        => 'sales_tbs2.dbf',
        destination_database         => 'inst3.example.com');
      DBMS_FILE_TRANSFER.PUT_FILE(
        source_directory_object      => 'q2fy2005',
        source_file_name             => 'expdat18.dmp',
        destination_directory_object => 'q2fy2005',
        destination_file_name        => 'expdat18.dmp',
        destination_database         => 'inst3.example.com');
    END;
    /
    

    Before you run the PUT_FILE procedure for the export dump file, you can query the DBA_FILE_GROUP_FILES data dictionary view to determine the name and directory object of the export dump file. For example, run the following query to list this information for the export dump file in the v_q2fy2005 version:

    COLUMN FILE_NAME HEADING 'Export Dump|File Name' FORMAT A35
    COLUMN FILE_DIRECTORY HEADING 'Directory Object' FORMAT A35
    
    SELECT FILE_NAME, FILE_DIRECTORY FROM DBA_FILE_GROUP_FILES
      where FILE_GROUP_NAME = 'SALES' AND
            VERSION_NAME    = 'V_Q2FY2005';
    

14. Connect to the inst3.example.com database as an administrative user.

15. Attach the tablespace set for the second quarter of 2005 to the inst3.example.com database from the sales file group in the inst1.example.com database:

    DECLARE
      tbs_set DBMS_STREAMS_TABLESPACE_ADM.TABLESPACE_SET;
    BEGIN
      DBMS_STREAMS_TABLESPACE_ADM.ATTACH_TABLESPACES(
        file_group_name            => 'strmadmin.sales',
        version_name               => 'v_q2fy2005',
        datafiles_directory_object => 'q2fy2005',
        repository_db_link         => 'inst1.example.com',
        tablespace_names           => tbs_set);
    END;
    /
    

    The tablespaces are read-only when they are attached. Because the reports on inst3.example.com do not change the tablespace, the tablespaces can remain read-only.

16. Run the reports on the data in the sales tablespaces at the inst3.example.com database.

17. Drop the tablespaces and their contents:

    DROP TABLESPACE sales_tbs1 INCLUDING CONTENTS;
    
    DROP TABLESPACE sales_tbs2 INCLUDING CONTENTS;
    

    The tablespaces are dropped from the inst3.example.com database, but the tablespace files remain in the directory object.

Steps 11-17 can be repeated whenever a quarter ends to run reports on each quarter.