36 Using Information Provisioning

This chapter describes how to use information provisioning. This chapter includes an example that creates a tablespace repository, examples that transfer tablespaces between databases, and an example that uses a file group repository to store different versions of files.

The following topics describe using information provisioning:

See Also:

Chapter 35, "Information Provisioning Concepts"

Using a Tablespace Repository

The following procedures in the DBMS_STREAMS_TABLESPACE_ADM package can create a tablespace repository, add versioned tablespace sets to a tablespace repository, and copy versioned tablespace sets from a tablespace repository:

  • ATTACH_TABLESPACES: This procedure copies a version of a tablespace set from a tablespace repository and attaches the tablespaces to a database.

  • CLONE_TABLESPACES: This procedure adds a new version of a tablespace set to a tablespace repository by copying the tablespace set from a database. The tablespaces in the tablespace set remain part of the database from which they were copied.

  • DETACH_TABLESPACES: This procedure adds a new version of a tablespace set to a tablespace repository by moving the tablespace set from a database to the repository. The tablespaces in the tablespace set are dropped from the database from which they were copied.

This section illustrates how to use a tablespace repository with an example scenario. In the scenario, the goal is to run quarterly reports on the sales tablespaces (sales_tbs1 and sales_tbs2). Sales are recorded in these tablespaces in the inst1.example.com database. The example clones the tablespaces quarterly and stores a new version of the tablespaces in the tablespace repository. The tablespace repository also resides in the inst1.example.com database. When a specific version of the tablespace set is required to run reports at a reporting database, it is copied from the tablespace repository and attached to the reporting database.

In this example scenario, the following databases are the reporting databases:

  • The reporting database inst2.example.com shares a file system with the inst1.example.com database. Also, 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, and, 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.

  • The reporting system inst3.example.com does not share a file system with the inst1.example.com database. 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, the original version of the tablespace files remains in a single directory.

The following sections describe how to create and populate the tablespace repository and how to use the tablespace repository to run reports at the other databases:

These examples must be run by an administrative user with the necessary privileges to run the procedures listed previously.

See Also:

Oracle Database PL/SQL Packages and Types Reference for more information about these procedures and the privileges required to run them

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 follows
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.

Version Directory Object Corresponding File System Directory
v_q1fy2005 q1fy2005 /home/sales/q1fy2005
v_q2fy2005 q2fy2005 /home/sales/q2fy2005

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 follows
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 follows
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".

Version Directory Object Corresponding File System Directory New?
v_q1fy2005 q1fy2005 /home/sales/q1fy2005 No
v_q1fy2005_r q1fy2005_r /home/sales/q1fy2005_r Yes
v_q2fy2005 q2fy2005 /home/sales/q2fy2005 No
v_q2fy2005_r q2fy2005_r /home/sales/q2fy2005_r Yes

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 follows
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".

Directory Object Database Version Corresponding File System Directory New?
q1fy2005 inst1.example.com v_q1fy2005 /home/sales/q1fy2005 No
q2fy2005 inst1.example.com v_q2fy2005 /home/sales/q2fy2005 No
q1fy2005 inst3.example.com Not associated with a tablespace repository version /usr/sales_data/fy2005q1 Yes
q2fy2005 inst3.example.com Not associated with a tablespace repository version /usr/sales_data/fy2005q2 Yes

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.

Using a File Group Repository

The DBMS_FILE_GROUP package can create a file group repository, add versioned file groups to the repository, and copy versioned file groups from the repository. This section illustrates how to use a file group repository with a scenario that stores reports in the repository.

In this scenario, a business sells books and music over the internet. The business runs weekly reports on the sales data in the inst1.example.com database and stores these reports in two HTML files on a computer file system. The book_sales.htm file contains the report for book sales, and the music_sales.htm file contains the report for music sales. The business wants to store these weekly reports in a file group repository at the inst2.example.com remote database. Every week, the two reports are generated on the inst1.example.com database, transferred to the computer system running the inst2.example.com database, and added to the repository as a file group version. The file group repository stores all of the file group versions that contain the reports for each week.

Figure 36-5 provides an overview of the file group repository created in this example:

Figure 36-5 Example File Group Repository

Description of Figure 36-5 follows
Description of "Figure 36-5 Example File Group Repository"

The benefits of the file group repository are that it stores metadata about each file group version in the data dictionary and provides a standard interface for managing the file group versions. For example, when the business must view a specific sales report, it can query the data dictionary in the inst2.example.com database to determine the location of the report on the computer file system.

The following table shows the directory objects created in this example. It shows the directory object created on the inst1.example.com database to store new reports, and it shows the directory objects that are associated with file group repository versions on the inst2.example.com database.

Directory Object Database Version Corresponding File System Directory
sales_reports inst1.example.com Not associated with a file group repository version /home/sales_reports
sales_reports1 inst2.example.com sales_reports_v1 /home/sales_reports/fg1
sales_reports2 inst2.example.com sales_reports_v1 /home/sales_reports/fg2

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 do not share a file system.

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

  • The inst1.example.com database runs reports on the books and music sales data in the database and stores the reports as HTML files on the computer file system.

The following steps configure and populate a file group repository at a remote database:

  1. Connect as an administrative user to the remote database that will contain the file group repository. In this example, connect to the inst2.example.com database.

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

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

  2. Create a directory object to hold the first version of the file group:

    CREATE OR REPLACE DIRECTORY sales_reports1 AS '/home/sales_reports/fg1';

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

  3. Connect as an administrative user to the database that runs the reports. In this example, connect to the inst1.example.com database.

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

  4. Create a directory object to hold the latest reports:

    CREATE OR REPLACE DIRECTORY sales_reports AS '/home/sales_reports';

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

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

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

  6. Run the reports on the inst1.example.com database. Running the reports should place the book_sales.htm and music_sales.htm files in the directory specified in Step 4.

  7. Transfer the report files from the computer system running the inst1.example.com database to the computer system running the inst2.example.com database using file transfer protocol (FTP) or some other method. Ensure that the files are copied to the directory that corresponds to the directory object created in Step 2.

  8. Connect as an administrative user to the remote database that will contain the file group repository. In this example, connect to the inst2.example.com database.

  9. Create the file group repository that will contain the reports:

    BEGIN
  DBMS_FILE_GROUP.CREATE_FILE_GROUP(
    file_group_name => 'strmadmin.reports');
END;
/

    The reports file group repository is created with the following default properties:

    • The minimum number of versions in the repository is 2. When the file group is purged, the number of versions cannot drop below 2.

    • The maximum number of versions is infinite. A file group version is not purged because of the number of versions in the file group in the repository.

    • The retention days is infinite. A file group version is not purged because of the amount of time it has been in the repository.

  10. Create the first version of the file group:

    BEGIN
  DBMS_FILE_GROUP.CREATE_VERSION(
    file_group_name => 'strmadmin.reports',
    version_name    => 'sales_reports_v1',
    comments        => 'Sales reports for week of 06-FEB-2005');
END;
/

  11. Add the report files to the file group version:

    BEGIN
 DBMS_FILE_GROUP.ADD_FILE(
    file_group_name  => 'strmadmin.reports',
    file_name        => 'book_sales.htm',
    file_type        => 'HTML',
    file_directory   => 'sales_reports1',
    version_name     => 'sales_reports_v1');
 DBMS_FILE_GROUP.ADD_FILE(
    file_group_name  => 'strmadmin.reports',
    file_name        => 'music_sales.htm',
    file_type        => 'HTML',
    file_directory   => 'sales_reports1',
    version_name     => 'sales_reports_v1');
END;
/

  12. Create a directory object on inst2.example.com to hold the next version of the file group:

    CREATE OR REPLACE DIRECTORY sales_reports2 AS '/home/sales_reports/fg2';

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

  13. At the end of the next week, run the reports on the inst1.example.com database. Running the reports should place new book_sales.htm and music_sales.htm files in the directory specified in Step 4. If necessary, remove the old files from this directory before running the reports.

  14. Transfer the report files from the computer system running the inst1.example.com database to the computer system running the inst2.example.com database using file transfer protocol (FTP) or some other method. Ensure that the files are copied to the directory that corresponds to the directory object created in Step 12.

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

  16. Create the next version of the file group:

    BEGIN
  DBMS_FILE_GROUP.CREATE_VERSION(
    file_group_name => 'strmadmin.reports',
    version_name    => 'sales_reports_v2',
    comments        => 'Sales reports for week of 13-FEB-2005');
END;
/

  17. Add the report files to the file group version:

    BEGIN
 DBMS_FILE_GROUP.ADD_FILE(
    file_group_name  => 'strmadmin.reports',
    file_name        => 'book_sales.htm',
    file_type        => 'HTML',
    file_directory   => 'sales_reports2',
    version_name     => 'sales_reports_v2');
 DBMS_FILE_GROUP.ADD_FILE(
    file_group_name  => 'strmadmin.reports',
    file_name        => 'music_sales.htm',
    file_type        => 'HTML',
    file_directory   => 'sales_reports2',
    version_name     => 'sales_reports_v2');
END;
/

The file group repository now contains two versions of the file group that contains the sales report files. Repeat steps 12-17 to add new versions of the file group to the repository.

See Also: