2 Simple Oracle Streams Replication Configuration

Local or Downstream Capture for the Source Database

These procedures can either configure local capture or downstream capture for the database specified in the source_database parameter. The database that captures changes made to the source database is called the capture database. See "Decide Whether to Configure Local or Downstream Capture for the Source Database" for more information.

The database on which the procedure is run is configured as the capture database for changes made to the source database. Therefore, to configure local capture at the source database, run the procedure at the source database. To configure downstream capture at the destination database, run the procedure at the database specified in the destination_database parameter. To configure downstream capture at the at a third database, run the procedure at a database that is not specified in the source_database or destination_database parameter.

If the source database or a third database is the capture database, then these procedures configure a propagation to send changes from the capture database to the destination database. If the destination database is the capture database and you are not configuring bi-directional replication, then this propagation between databases is not needed. In this case, the propagation is not configured if the capture_queue_name and apply_queue_name parameters have the same value. If these values are different, then a propagation is configured between the two queues within the destination database.

Perform Configuration Actions Directly or With a Script

These procedures can configure the Oracle Streams replication environment directly, or they can generate a script that configures the environment. Using a procedure to configure replication directly is simpler than running a script, and the environment is configured immediately. However, you might choose to generate a script for the following reasons:

• You want to review the actions performed by the procedure before configuring the environment.

• You want to modify the script to customize the configuration.

For example, you might want an apply process to use apply handlers for customized processing of the changes to certain tables before applying these changes. In this case, you can use the procedure to generate a script and modify the script to add the apply handlers.

You also might want to maintain DML changes for several tables, but you might want to maintain DDL changes for a subset of these tables. In this case, you can generate a script by running the MAINTAIN_TABLES procedure with the include_ddl parameter set to FALSE. You can modify the script to maintain DDL changes for the appropriate tables.

The perform_actions parameter controls whether the procedure configures the replication environment directly:

• To configure an Oracle Streams replication environment directly when you run one of these procedures, set the perform_actions parameter to TRUE. The default value for this parameter is TRUE.

• To generate a configuration script when you run one of these procedures, set the perform_actions parameter to FALSE, and use the script_name and script_directory_object parameters to specify the name and location of the configuration script.

Oracle Streams Components Configured by These Procedures

These procedures configure the following Oracle Streams clients:

• These procedures configure a capture process that captures changes to the source database. If bi-directional replication is configured, then these procedures also configure a capture process that captures changes to the destination database.

• If the capture database and the destination database are different databases, then these procedures configure a propagation that sends changes from the capture database to the destination database.

• If the capture database and the destination database are the same database, then the queue names determine whether a propagation is created:

  • If the capture_queue_name and apply_queue_name parameters specify different queue names, then a propagation is created between the two queues within the destination database.

  • If the capture_queue_name and apply_queue_name parameters specify the same queue name, then a propagation is not created, and the downstream capture process and the apply process use the same queue. This configuration is possible only if the bi_directional parameter is set to FALSE to configure a single source replication environment.

• If bi-directional replication is configured, then these procedures configure a propagation that sends changes from the destination database to the source database.

• These procedures configure an apply process that applies changes at the destination database. These changes originated at the source database. If bi-directional replication is configured, then these procedures also configure an apply process that applies changes to the source database. These changes originated at the destination database.

By default, the capture_queue_name and apply_queue_name parameters are set to NULL. When these parameters are set to NULL, these procedures configure a separate queue for each capture process and apply process. The Oracle Streams replication environment might operate more efficiently if each Oracle Streams client has its own separate queue.

However, two Oracle Streams clients share a queue in the following configurations:

• The configuration described previously in this section in which the downstream capture process and the apply process at the destination database share a queue.

• A configuration in which all of the following conditions are met:

  • The capture database is the source database or a third database.

  • The bi_directional parameter is set to TRUE.

  • The same queue name is specified for the capture_queue_name and apply_queue_name parameters.

  In this case, the local capture process and the apply process at the destination database share the same queue. If the source database is the capture database, then the local capture process and the apply process at the source database also share the same queue.

Also, the capture_name and capture_queue_name parameters must be set to NULL when both of the following conditions are met:

• The destination database is the capture database.

• The bi_directional parameter is set to TRUE.

When both of these conditions are met, these procedure configure two capture processes at the destination database, and these capture processes must have different names. One capture process is the downstream capture process for the source database, while the other capture process is the local capture process that captures changes made to the destination database. When the capture_name and capture_queue_name parameters are set to NULL, the system generates a different name for the capture processes. These procedures raise an error if both conditions are met and either the capture_name parameter or the capture_queue_name parameter is set to a non-NULL value.

One-Way or Bi-Directional Replication

These procedures set up either a one-way (or single-source) Oracle Streams configuration with the database specified in the source_database parameter as the source database, or a bi-directional Oracle Streams configuration with both databases acting as source and destination databases. See "Decide Whether Changes Are Allowed at One Database or at Multiple Databases" for more information.

The bi_directional parameter in each procedure controls whether the Oracle Streams configuration is single source or bi-directional:

• If the bi_directional parameter is FALSE, then a capture process captures changes made to the source database and an apply process at the destination database applies these changes. If the destination database is not the capture database, then a propagation sends the captured changes to the destination database. The default value for this parameter is FALSE.

• If the bi_directional parameter is TRUE, then a separate capture process captures changes made to each database, propagations send these changes to the other database, and each database applies changes from the other database.

When a replication environment is not bi-directional, and no changes are allowed at the destination database, Oracle Streams keeps the shared database objects synchronized at the databases. However, when a replication environment is not bi-directional, and independent changes are allowed at the destination database, the shared database objects might diverge between the databases. Independent changes can be made by users, by applications, or by replication with a third database.

Data Definition Language (DDL) Changes

The include_ddl parameter controls whether the procedure configures Oracle Streams replication to maintain DDL changes:

• To configure an Oracle Streams replication environment that does not maintain DDL changes, set the include_ddl parameter to FALSE when you run one of these procedures. The default value for this parameter is FALSE.

• To configure an Oracle Streams replication environment that maintains DDL changes, set the include_ddl parameter to TRUE when you run one of these procedures.

Instantiation

The MAINTAIN_GLOBAL, MAINTAIN_SCHEMAS, and MAINTAIN_TABLES procedures provide options for instantiation. Instantiation is the process of preparing database objects for instantiation at a source database, optionally copying the database objects from a source database to a destination database, and setting the instantiation SCN for each instantiated database object.

When you run one of these three procedures, you can choose to perform the instantiation in one of the following ways:

• Data Pump Export Dump File Instantiation: This option performs a Data Pump export of the shared database objects at the source database and a Data Pump import of the export dump file at the destination database. The instantiation SCN is set for each shared database object during import.

  To specify this instantiation option, set the instantiation parameter to one of the following values:

  • DBMS_STREAMS_ADM.INSTANTIATION_FULL if you run the MAINTAIN_GLOBAL procedure

  • DBMS_STREAMS_ADM.INSTANTIATION_SCHEMA if you run the MAINTAIN_SCHEMAS procedure

  • DBMS_STREAMS_ADM.INSTANTIATION_TABLE if you run the MAINTAIN_TABLES procedure

  If the bi_directional parameter is set to TRUE, then the procedure also sets the instantiation SCN for each shared database object at the source database.

  When you use this option, you must create directory objects to hold the Data Pump files. See "Creating the Required Directory Objects".

• Data Pump Network Import Instantiation: This option performs a network Data Pump import of the shared database objects. A network import means that Data Pump performs the import without using an export dump file. Therefore, directory objects do not need to be created for instantiation purposes when you use this option. The instantiation SCN is set for each shared database object during import.

  To specify this instantiation option, set the instantiation parameter to one of the following values:

  • DBMS_STREAMS_ADM.INSTANTIATION_FULL_NETWORK if you run the MAINTAIN_GLOBAL procedure

  • DBMS_STREAMS_ADM.INSTANTIATION_SCHEMA_NETWORK if you run the MAINTAIN_SCHEMAS procedure

  • DBMS_STREAMS_ADM.INSTANTIATION_TABLE_NETWORK if you run the MAINTAIN_TABLES procedure

  If the bi_directional parameter is set to TRUE, then the procedure also sets the instantiation SCN for each shared database object at the source database.

• Generate a Configuration Script with No Instantiation Specified: This option does not perform an instantiation. This setting is valid only if the perform_actions parameter is set to FALSE, and the procedure generates a configuration script. In this case, the configuration script does not perform an instantiation and does not set the instantiation SCN for each shared database object. Instead, you must perform the instantiation and ensure that instantiation SCN values are set properly.

  To specify this instantiation option, set the instantiation parameter to DBMS_STREAMS_ADM.INSTANTIATION_NONE in each procedure.

When one of these procedures performs a table instantiation, the tablespace that contains the table must exist at the destination database. When one of these procedures performs a schema instantiation, the tablespace that contains the schema must exist at the destination database.

When these procedures perform a dump file or network instantiation and an instantiated database object does not exist at the destination database, the database object is imported at the destination database, including its supplemental logging specifications from the source database and its supporting database objects, such as indexes and triggers. However, if the database object already exists at the destination database before instantiation, then it is not imported at the destination database. Therefore, the supplemental logging specifications from the source database are not specified for the database object at the destination database, and the supporting database objects are not imported.

The PRE_INSTANTIATION_SETUP and POST_INSTANTIATION_SETUP procedures do not perform an instantiation. You must perform any required instantiation actions manually after running PRE_INSTANTIATION_SETUP and before running POST_INSTANTIATION_SETUP. You also must perform any required instantiation actions manually if you use the MAINTAIN_GLOBAL, MAINTAIN_SCHEMAS, and MAINTAIN_TABLES procedures and set the instantiation parameter to DBMS_STREAMS_ADM.INSTANTIATION_NONE.

In these cases, you can use any instantiation method. For example, you can use Recovery Manager (RMAN) to perform a database instantiation using the RMAN DUPLICATE or CONVERT DATABASE command or a tablespace instantiation using the RMAN TRANSPORT TABLESPACE command. If the bi_directional parameter is set to TRUE, then ensure that the instantiation SCN values are set properly at the source database and the destination database.

Configuring Two-Database Global Replication with Local Capture

You can use the following procedures in the DBMS_STREAMS_ADM package to configure replication at the database level:

• MAINTAIN_GLOBAL

• PRE_INSTANTIATION_SETUP and POST_INSTANTIATION_SETUP

The MAINTAIN_GLOBAL procedure automatically excludes database objects that are not supported by Oracle Streams from the replication environment. The PRE_INSTANTIATION_SETUP and POST_INSTANTIATION_SETUP procedures do not automatically exclude database objects. Instead, these procedures enable you to specify which database objects to exclude from the replication environment. Query the DBA_STREAMS_UNSUPPORTED data dictionary view to determine which database objects are not supported by Oracle Streams. If unsupported database objects are not excluded, then capture errors will result.

The following table lists the decisions that were made about the Oracle Streams replication environment configured in this example.

In this example, the procedures will configure the replication environment directly. Configuration scripts will not be generated. An RMAN database instantiation will be performed.

As noted in the previous table, this example uses the PRE_INSTANTIATION_SETUP and POST_INSTANTIATION_SETUP procedures to configure database replication. The replication configuration will exclude all database objects that are not supported by Oracle Streams. In this example, the source database is dbs1.example.com, and the destination database is dbs2.example.com.

Figure 2-1 provides an overview of the replication environment created in this example.

Figure 2-1 Sample Oracle Streams Environment That Replicates an Entire Database

Description of "Figure 2-1 Sample Oracle Streams Environment That Replicates an Entire Database"

Complete the following steps to use the PRE_INSTANTIATION_SETUP and POST_INSTANTIATION_SETUP procedures to configure the replication environment:

1. Complete the required tasks before running the PRE_INSTANTIATION_SETUP procedure. See "Tasks to Complete Before Configuring Oracle Streams Replication" for instructions.

   For this configuration, the following tasks must be completed:

   • Configure an Oracle Streams administrator at both databases. See "Configuring an Oracle Streams Administrator on All Databases".

   • Configure network connectivity and database links:

     • Configure network connectivity between the source database dbs1.example.com and the destination database dbs2.example.com.

     • Create a database link from the source database dbs1.example.com to the destination database dbs2.example.com.

     See "Configuring Network Connectivity and Database Links".

   • Ensure that both databases are in ARCHIVELOG mode. See "Ensuring That Each Source Database Is In ARCHIVELOG Mode".

   • Ensure that the initialization parameters are set properly at both databases. See "Setting Initialization Parameters Relevant to Oracle Streams".

   • Configure the Oracle Streams pool properly at both databases. See "Configuring the Oracle Streams Pool".

   A database link is required from the destination database to the source database. However, because RMAN will be used for database instantiation, this database link must be created after instantiation. This database link is required because the replication environment will be bi-directional and because RMAN will be used for database instantiation.

2. In SQL*Plus, connect to the source database dbs1.example.com as the Oracle Streams administrator.

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

3. Run the PRE_INSTANTIATION_SETUP procedure:

   DECLARE
     empty_tbs  DBMS_STREAMS_TABLESPACE_ADM.TABLESPACE_SET; 
   BEGIN
     DBMS_STREAMS_ADM.PRE_INSTANTIATION_SETUP(
       maintain_mode        => 'GLOBAL',
       tablespace_names     => empty_tbs,
       source_database      => 'dbs1.example.com',
       destination_database => 'dbs2.example.com',
       perform_actions      => TRUE,
       bi_directional       => TRUE,
       include_ddl          => TRUE,
       start_processes      => TRUE,
       exclude_schemas      => '*',
       exclude_flags        => DBMS_STREAMS_ADM.EXCLUDE_FLAGS_UNSUPPORTED + 
                               DBMS_STREAMS_ADM.EXCLUDE_FLAGS_DML + 
                               DBMS_STREAMS_ADM.EXCLUDE_FLAGS_DDL);
   END;
   /
   

   Notice that the start_processes parameter is set to TRUE. Therefore, each capture process and apply process created during the configuration is started automatically.

   Also, notice the values specified for the exclude_schemas and exclude_flags parameters. The asterisk (*) specified for exclude_schemas indicates that certain database objects in every schema in the database might be excluded from the replication environment. The value specified for the exclude_flags parameter indicates that DML and DDL changes for all unsupported database objects are excluded from the replication environment. Rules are placed in the negative rule sets for the capture processes to exclude these database objects.

   Because the procedure is run at the source database, local capture is configured at the source database.

   Because this procedure configures a bi-directional replication environment, do not allow DML or DDL changes to the shared database objects at the destination database while the procedure is running.

   The procedure does not specify the apply_name parameter. Therefore, the default, NULL, is specified for this parameter. When the apply_name parameter is set to NULL, no apply process that applies changes from the source database can exist on the destination database. If an apply process that applies changes from the source database exists at the destination database, then specify a non-NULL value for the apply_name parameter.

   To monitor the progress of the configuration operation, follow the instructions in "Monitoring Oracle Streams Configuration Progress".

   If this procedure encounters an error and stops, then see "Recovering from Operation Errors" for information about either recovering from the error or rolling back the configuration operation.

4. Perform the instantiation. You can use any of the methods described in Chapter 8, "Instantiation and Oracle Streams Replication" to complete the instantiation. This example uses the RMAN DUPLICATE command to perform the instantiation by performing the following steps:

   1. Create a backup of the source database if one does not exist. RMAN requires a valid backup for duplication. In this example, create a backup of dbs1.example.com if one does not exist.

      Note:

      A backup of the source database is not necessary if you use the FROM ACTIVE DATABASE option when you run the RMAN DUPLICATE command. For large databases, the FROM ACTIVE DATABASE option requires significant network resources. This example does not use this option.

   2. In SQL*Plus, connect to the source database dbs1.example.com as the Oracle Streams administrator.

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

   3. Determine the until SCN for the RMAN DUPLICATE command:

      SET SERVEROUTPUT ON SIZE 1000000
      DECLARE
        until_scn NUMBER;
      BEGIN
        until_scn:= DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER;
            DBMS_OUTPUT.PUT_LINE('Until SCN: ' || until_scn);
      END;
      /
      

      Make a note of the until SCN returned. You will use this number in Step h. For this example, assume that the returned until SCN is 45442631.

   4. In SQL*Plus, connect to the source database dbs1.example.com as an administrative user.

   5. Archive the current online redo log:

      ALTER SYSTEM ARCHIVE LOG CURRENT;
      

   6. Prepare your environment for database duplication, which includes preparing the destination database as an auxiliary instance for duplication. See Oracle Database Backup and Recovery User's Guide for instructions.

   7. Start the RMAN client, and connect to the source database dbs1.example.com as TARGET and to the destination database dbs2.example.com as AUXILIARY.

      See Also:

      Oracle Database Backup and Recovery Reference for more information about the RMAN CONNECT command

   8. Use the RMAN DUPLICATE command with the OPEN RESTRICTED option to instantiate the source database at the destination database. The OPEN RESTRICTED option is required. This option enables a restricted session in the duplicate database by issuing the following SQL statement: ALTER SYSTEM ENABLE RESTRICTED SESSION. RMAN issues this statement immediately before the duplicate database is opened.

      You can use the UNTIL SCN clause to specify an SCN for the duplication. Use the until SCN determined in Step c for this clause. Archived redo logs must be available for the until SCN specified and for higher SCN values. Therefore, Step e archived the redo log containing the until SCN.

      Ensure that you use TO database_name in the DUPLICATE command to specify the name of the duplicate database. In this example, the duplicate database is dbs2.example.com. Therefore, the DUPLICATE command for this example includes TO dbs2.example.com.

      The following is an example of an RMAN DUPLICATE command:

      RMAN> RUN
            { 
              SET UNTIL SCN 45442631;
              ALLOCATE AUXILIARY CHANNEL dbs2 DEVICE TYPE sbt; 
              DUPLICATE TARGET DATABASE TO dbs2 
              NOFILENAMECHECK
              OPEN RESTRICTED;
            }
      

      See Also:

      Oracle Database Backup and Recovery Reference for more information about the RMAN DUPLICATE command

   9. In SQL*Plus, connect to the destination database as an administrative user.

   10. Rename the global name. After an RMAN database instantiation, the destination database has the same global name as the source database. Rename the global name of the destination database back to its original name with the following statement:

       ALTER DATABASE RENAME GLOBAL_NAME TO dbs2.example.com;
       

   11. In SQL*Plus, connect to the destination database dbs2.example.com as the Oracle Streams administrator.

   12. Drop the database link from the source database to the destination database that was cloned from the source database:

       DROP DATABASE LINK dbs2.example.com;
       

5. While still connected to the destination database as the Oracle Streams administrator, create a database link from the destination database to the source database:

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

   See Step 1 for information about why this database link is required.

6. In SQL*Plus, connect to the source database dbs1.example.com as the Oracle Streams administrator.

7. Run the POST_INSTANTIATION_SETUP procedure:

   DECLARE
     empty_tbs  DBMS_STREAMS_TABLESPACE_ADM.TABLESPACE_SET; 
   BEGIN
     DBMS_STREAMS_ADM.POST_INSTANTIATION_SETUP(
       maintain_mode        => 'GLOBAL',
       tablespace_names     => empty_tbs,
       source_database      => 'dbs1.example.com',
       destination_database => 'dbs2.example.com',
       perform_actions      => TRUE,
       bi_directional       => TRUE,
       include_ddl          => TRUE,
       start_processes      => TRUE,
       instantiation_scn    => 45442630,
       exclude_schemas      => '*',
       exclude_flags        => DBMS_STREAMS_ADM.EXCLUDE_FLAGS_UNSUPPORTED + 
                               DBMS_STREAMS_ADM.EXCLUDE_FLAGS_DML + 
                               DBMS_STREAMS_ADM.EXCLUDE_FLAGS_DDL);
   END;
   /
   

   The parameter values specified in both the PRE_INSTANTIATION_SETUP and POST_INSTANTIATION_SETUP procedures must match, except for the values of the following parameters: perform_actions, script_name, script_directory_object, and start_processes.

   Also, notice that the instantiation_scn parameter is set to 45442630. The RMAN DUPLICATE command duplicates the database up to one less than the SCN value specified in the UNTIL SCN clause. Therefore, you should subtract one from the until SCN value that you specified when you ran the DUPLICATE command in Step 4h. In this example, the until SCN was set to 45442631. Therefore, the instantiation_scn parameter should be set to 45442631 - 1, or 45442630.

   If the instantiation SCN was set for the shared database objects at the destination database during instantiation, then the instantiation_scn parameter should be set to NULL. For example, the instantiation SCN might be set during a full database export/import.

   Because this procedure configures a bi-directional replication environment, do not allow DML or DDL changes to the shared database objects at the destination database while the procedure is running.

   To monitor the progress of the configuration operation, follow the instructions in "Monitoring Oracle Streams Configuration Progress".

   If this procedure encounters an error and stops, then see "Recovering from Operation Errors" for information about either recovering from the error or rolling back the configuration operation.

8. At the destination database, connect as an administrative user in SQL*Plus and use the ALTER SYSTEM statement to disable the RESTRICTED SESSION:

   ALTER SYSTEM DISABLE RESTRICTED SESSION;
   

9. Configure conflict resolution for the shared database objects if necessary.

   Typically, conflicts are possible in a bi-directional replication environment. If conflicts are possible in the environment created by the PRE_INSTANTIATION_SETUP and POST_INSTANTIATION_SETUP procedures, then configure conflict resolution before you allow users to make changes to the shared database objects.

   See Chapter 9, "Oracle Streams Conflict Resolution" for more information.

The bi-directional replication environment configured in this example has the following characteristics:

• Database supplemental logging is configured at both databases.

• The dbs1.example.com database has two queues and queue tables with system-generated names. One queue is for the local capture process, and one queue is for the apply process.

• The dbs2.example.com database has two queues and queue tables with system-generated names. One queue is for the local capture process, and one queue is for the apply process.

• At the dbs1.example.com database, a capture process with a system-generated name captures DML and DDL changes to all of the database objects in the database that are supported by Oracle Streams.

• At the dbs2.example.com database, a capture process with a system-generated name captures DML and DDL changes to all of the database objects in the database that are supported by Oracle Streams.

• A propagation running on the dbs1.example.com database with a system-generated name sends the captured changes from a queue at the dbs1.example.com database to a queue at the dbs2.example.com database.

• A propagation running on the dbs2.example.com database with a system-generated name sends the captured changes from a queue at the dbs2.example.com database to a queue at the dbs1.example.com database.

• At the dbs1.example.com database, an apply process with a system-generated name dequeues the changes from its queue and applies them to the database objects.

• At the dbs2.example.com database, an apply process with a system-generated name dequeues the changes from its queue and applies them to the database objects.

• Tags are used to avoid change cycling. Specifically, each apply process uses an apply tag so that redo records for changes applied by the apply process include the tag. Each apply process uses an apply tag that is unique in the replication environment. Each propagation discards changes that have the tag of the apply process running on the same database. See "Change Cycling and Tags" for more information.

Configuring Two-Database Schema Replication with Local Capture

This example configures an Oracle Streams replication environment that replicates data manipulation language (DML) changes to all of the tables in the hr schema. This example configures a two-database replication environment with local capture processes to capture changes. This example uses the global database names db1.example.com and db2.example.com. However, you can substitute databases in your environment to complete the example.

The following table lists the decisions that were made about the Oracle Streams replication environment configured in this example.

The database objects being configured for replication might or might not exist at the destination database when you run the MAINTAIN_SCHEMAS procedure. If the database objects do not exist at the destination database, then the MAINTAIN_SCHEMAS procedure instantiates them at the destination database using a Data Pump export/import. During instantiation, the instantiation SCN is set for these database objects. If the database objects already exist at the destination database, then the MAINTAIN_SCHEMAS procedure retains the existing database objects and sets the instantiation SCN for them. In this example, the hr schema exists at both the db1.example.com database and the db2.example.com database before the MAINTAIN_SCHEMAS procedure is run.

In this example, the MAINTAIN_SCHEMAS procedure will configure the replication environment directly. A configuration script will not be generated. A Data Pump export dump file instantiation will be performed.

Figure 2-2 provides an overview of the environment created in this example. The additional components required for bi-directional replication are shown in gray, and their actions are indicated by dashed lines.

Figure 2-2 Two-Database Replication Environment with Local Capture Processes

Description of "Figure 2-2 Two-Database Replication Environment with Local Capture Processes"

Complete the following steps to use the MAINTAIN_SCHEMAS procedure to configure the environment:

1. Complete the following tasks to prepare for the two-database replication environment:

   1. Configure network connectivity so that the db1.example.com database can communicate with the db2.example.com database.

      See Oracle Database 2 Day DBA for information about configuring network connectivity between databases.

   2. Configure an Oracle Streams administrator at each database that will participate in the replication environment. See "Configuring an Oracle Streams Administrator on All Databases" for instructions. This example assumes that the Oracle Streams administrator is strmadmin.

   3. Create a database link from the db1.example.com database to the db2.example.com database.

      The database link should be created in the Oracle Streams administrator's schema. Also, the database link should connect to the Oracle Streams administrator at the other database. Both the name and the service name of the database link must be db2.example.com. See "Configuring Network Connectivity and Database Links" for instructions.

   4. Configure the db1.example.com database to run in ARCHIVELOG mode. For a capture process to capture changes generated at a source database, the source database must be running in ARCHIVELOG mode. See Oracle Database Administrator's Guide for information about configuring a database to run in ARCHIVELOG mode.

2. To configure a bi-directional replication environment, complete the following steps. If you are configuring a one-way replication environment, then these steps are not required, and you can move on to Step 3.

   1. Create a database link from the db2.example.com database to the db1.example.com database.

      The database link should be created in the Oracle Streams administrator's schema. Also, the database link should connect to the Oracle Streams administrator at the other database. Both the name and the service name of the database link must be db1.example.com. See "Configuring Network Connectivity and Database Links" for instructions.

   2. Configure the db2.example.com database to run in ARCHIVELOG mode. For a capture process to capture changes generated at a source database, the source database must be running in ARCHIVELOG mode. See Oracle Database Administrator's Guide for information about configuring a database to run in ARCHIVELOG mode.

3. Set initialization parameters properly at each database that will participate in the Oracle Streams replication environment. See "Setting Initialization Parameters Relevant to Oracle Streams""Setting Initialization Parameters Relevant to Oracle Streams" for instructions.

4. Create the following required directory objects:

   • A source directory object at the source database. This example assumes that this directory object is source_directory.

   • A destination directory object at the destination database. This example assumes that this directory object is dest_directory.

   See "Creating the Required Directory Objects" for instructions.

5. In SQL*Plus, connect to the db1.example.com database as the Oracle Streams administrator.

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

6. Run the MAINTAIN_SCHEMAS procedure to configure replication of the hr schema between the db1.example.com database and the db2.example.com database.

   Ensure that the bi_directional parameter is set properly for the replication environment that you are configuring. Either set this parameter to FALSE for one-way replication, or set it to TRUE for bi-directional replication.

   BEGIN
     DBMS_STREAMS_ADM.MAINTAIN_SCHEMAS(
       schema_names                 => 'hr',
       source_directory_object      => 'source_directory',
       destination_directory_object => 'dest_directory',
       source_database              => 'db1.example.com',
       destination_database         => 'db2.example.com',
       bi_directional               => FALSE); -- Set to TRUE for bi-directional
   END;
   /
   

   The MAINTAIN_SCHEMAS procedure can take some time to run because it is performing many configuration tasks. Do not allow data manipulation language (DML) or data definition language (DDL) changes to the replicated database objects at the destination database while the procedure is running.

   When a configuration procedure is run, information about its progress is recorded in the following data dictionary views: DBA_RECOVERABLE_SCRIPT, DBA_RECOVERABLE_SCRIPT_PARAMS, DBA_RECOVERABLE_SCRIPT_BLOCKS, and DBA_RECOVERABLE_SCRIPT_ERRORS. If the procedure stops because it encounters an error, then see Oracle Streams Replication Administrator's Guide for instructions about using the RECOVER_OPERATION procedure in the DBMS_STREAMS_ADM package to recover from these errors.

7. If you configured bi-directional replication, then configure latest time conflict resolution for all of the tables in the hr schema at both databases. This schema includes the countries, departments, employees, jobs, job_history, locations, and regions tables. See Chapter 9, "Oracle Streams Conflict Resolution" for instructions.

Configuring Two-Database Table Replication with Local Capture

You can use the MAINTAIN_TABLES procedure in the DBMS_STREAMS_ADM package to configure table replication. The example in this section uses this procedure to configure an Oracle Streams replication environment that maintains specific tables in the hr schema. The source database is dbs1.example.com, and the destination database is dbs2.example.com.

The following table lists the decisions that were made about the Oracle Streams replication environment configured in this example.

The replication environment maintains the following DML and DDL changes for the shared database objects:

• The replication environment will maintain DML changes to the following tables in the hr schema:

  • departments

  • employees

  • countries

  • regions

  • locations

  • jobs

  • job_history

• The replication environment will maintain DDL changes to the following tables in the hr schema:

  • departments

  • employees

The replication environment does not maintain DDL changes to the following tables in the hr schema:

• countries

• regions

• locations

• jobs

• job_history

In this example, the MAINTAIN_TABLES procedure will not configure the replication environment directly. Instead, a configuration script will be generated, and this script will be modified so that DDL changes to the following tables are maintained: departments and employees. A Data Pump network import instantiation will be performed.

Ensure that you do not try to replicate tables that are not supported by Oracle Streams.

Figure 2-3 provides an overview of the replication environment created in this example.

Figure 2-3 Sample Oracle Streams Environment That Replicates Tables

Description of "Figure 2-3 Sample Oracle Streams Environment That Replicates Tables"

Complete the following steps to use the MAINTAIN_TABLES procedure to configure the environment:

1. Complete the required tasks before running the MAINTAIN_TABLES procedure. See "Tasks to Complete Before Configuring Oracle Streams Replication" for instructions.

   For this configuration, the following tasks must be completed:

   • Configure an Oracle Streams administrator at both databases. See "Configuring an Oracle Streams Administrator on All Databases".

   • Configure network connectivity and database links:

     • Configure network connectivity between the source database dbs1.example.com and the destination database dbs2.example.com.

     • Create a database link from the source database dbs1.example.com to the destination database dbs2.example.com.

     • Because the MAINTAIN_TABLES procedure will perform a Data Pump network import instantiation, create a database link from the destination database dbs2.example.com to the source database dbs1.example.com.

     See "Configuring Network Connectivity and Database Links".

   • Ensure that the source database dbs1.example.com is in ARCHIVELOG mode. See "Ensuring That Each Source Database Is In ARCHIVELOG Mode".

   • Ensure that the initialization parameters are set properly at both databases. See "Setting Initialization Parameters Relevant to Oracle Streams".

   • Configure the Oracle Streams pool properly at both databases. See "Configuring the Oracle Streams Pool".

2. Create a script directory object at the source database. This example assumes that this directory object is script_directory.

   See "Creating the Required Directory Objects" for instructions.

3. In SQL*Plus, connect to the source database dbs1.example.com as the Oracle Streams administrator.

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

4. Run the MAINTAIN_TABLES procedure:

   DECLARE
     tables DBMS_UTILITY.UNCL_ARRAY;
     BEGIN
       tables(1) := 'hr.departments';
       tables(2) := 'hr.employees';
       tables(3) := 'hr.countries';
       tables(4) := 'hr.regions';
       tables(5) := 'hr.locations';
       tables(6) := 'hr.jobs';
       tables(7) := 'hr.job_history';
       DBMS_STREAMS_ADM.MAINTAIN_TABLES(
         table_names                  => tables,
         source_directory_object      => NULL,
         destination_directory_object => NULL,
         source_database              => 'dbs1.example.com',
         destination_database         => 'dbs2.example.com',
         perform_actions              => FALSE,
         script_name                  => 'configure_rep.sql',
         script_directory_object      => 'script_directory',
         bi_directional               => FALSE,
         include_ddl                  => FALSE,
         instantiation      => DBMS_STREAMS_ADM.INSTANTIATION_TABLE_NETWORK);
   END;
   /
   

   The configure_rep.sql script generated by the procedure uses default values for the parameters that are not specified in the procedure call. The script uses system-generated names for the ANYDATA queues, queue tables, capture process, propagation, and apply process it creates. You can specify different names by using additional parameters available in the MAINTAIN_TABLES procedure. Notice that the include_ddl parameter is set to FALSE. Therefore, the script does not configure the replication environment to maintain DDL changes to the tables.

   The procedure does not specify the apply_name parameter. Therefore, the default, NULL, is specified for this parameter. When the apply_name parameter is set to NULL, no apply process that applies changes from the source database can exist on the destination database. If an apply process that applies changes from the source database exists at the destination database, then specify a non-NULL value for the apply_name parameter.

5. Modify the configure_rep.sql script:

   1. Navigate to the directory that corresponds with the script_directory directory object on the computer system running the source database.

   2. Open the configure_rep.sql script in a text editor. Consider making a backup of this script before modifying it.

   3. In the script, find the ADD_TABLE_RULES and ADD_TABLE_PROPAGATION_RULES procedure calls that create the table rules for the hr.departments and hr.employees tables. For example, the procedure calls for the capture process look similar to the following:

      dbms_streams_adm.add_table_rules(
          table_name => '"HR"."DEPARTMENTS"', 
          streams_type => 'CAPTURE', 
          streams_name => '"DBS1$CAP"', 
          queue_name => '"STRMADMIN"."DBS1$CAPQ"', 
          include_dml => TRUE,
          include_ddl => FALSE,
          include_tagged_lcr => TRUE,
          source_database => 'DBS1.EXAMPLE.COM', 
          inclusion_rule => TRUE,
          and_condition => get_compatible);
      
      dbms_streams_adm.add_table_rules(
          table_name => '"HR"."EMPLOYEES"', 
          streams_type => 'CAPTURE', 
          streams_name => '"DBS1$CAP"', 
          queue_name => '"STRMADMIN"."DBS1$CAPQ"', 
          include_dml => TRUE,
          include_ddl => FALSE,
          include_tagged_lcr => TRUE,
          source_database => 'DBS1.EXAMPLE.COM', 
          inclusion_rule => TRUE,
          and_condition => get_compatible);
      

   4. In the procedure calls that you found in Step c, change the setting of the include_ddl parameter to TRUE. For example, the procedure calls for the capture process should look similar to the following after the modification:

      dbms_streams_adm.add_table_rules(
          table_name => '"HR"."DEPARTMENTS"', 
          streams_type => 'CAPTURE', 
          streams_name => '"DBS1$CAP"', 
          queue_name => '"STRMADMIN"."DBS1$CAPQ"', 
          include_dml => TRUE,
          include_ddl => TRUE,
          include_tagged_lcr => TRUE,
          source_database => 'DBS1.EXAMPLE.COM', 
          inclusion_rule => TRUE,
          and_condition => get_compatible);
      
      dbms_streams_adm.add_table_rules(
          table_name => '"HR"."EMPLOYEES"', 
          streams_type => 'CAPTURE', 
          streams_name => '"DBS1$CAP"', 
          queue_name => '"STRMADMIN"."DBS1$CAPQ"', 
          include_dml => TRUE,
          include_ddl => TRUE,
          include_tagged_lcr => TRUE,
          source_database => 'DBS1.EXAMPLE.COM', 
          inclusion_rule => TRUE,
          and_condition => get_compatible);
      

      Remember to change the procedure calls for all capture processes, propagations, and apply processes.

   5. Save and close the configure_rep.sql script.

6. In SQL*Plus, connect to the source database dbs1.example.com as the Oracle Streams administrator.

7. At the source database, run the configuration script:

   SET ECHO ON
   SPOOL configure_rep.out
   @configure_rep.sql
   

   The script prompts you to supply information about the database names and the Oracle Streams administrators. When this configuration script completes, the Oracle Streams single-source replication environment is configured. The script also starts the queues, capture process, propagations, and apply process.

The resulting single-source replication environment has the following characteristics:

• At the source database, supplemental logging is configured for the shared database objects.

• The source database dbs1.example.com has a queue and queue table with system-generated names.

• The destination database dbs2.example.com has a queue and queue table with system-generated names.

• At the source database, a capture process with a system-generated name captures DML changes to all of the tables in the hr schema and DDL changes to the hr.departments and hr.employees tables.

• A propagation running on the source database with a system-generated name sends the captured changes from the queue at the source database to the queue at the destination database.

• At the destination database, an apply process with a system-generated name dequeues the changes from the queue and applies them to the tables at the destination database.

Configuring Tablespace Replication with Downstream Capture at Destination

You can use the following procedures in the DBMS_STREAMS_ADM package to configure tablespace replication:

• MAINTAIN_SIMPLE_TTS

• MAINTAIN_TTS

• PRE_INSTANTIATION_SETUP and POST_INSTANTIATION_SETUP

You can use the MAINTAIN_SIMPLE_TTS procedure to configure Oracle Streams replication for a simple tablespace, and you can use the MAINTAIN_TTS procedure to configure Oracle Streams replication for a set of self-contained tablespaces. These procedures use transportable tablespaces, Data Pump, the DBMS_STREAMS_TABLESPACE_ADM package, and the DBMS_FILE_TRANSFER package to configure the environment.

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 simple tablespace is a self-contained tablespace that uses only one data file. When there are multiple tablespaces in a tablespace set, a self-contained tablespace set has no references from inside the set of tablespaces pointing outside of the set of tablespaces.

These procedures clone the tablespace or tablespaces being configured for replication from the source database to the destination database. The MAINTAIN_SIMPLE_TTS procedure uses the CLONE_SIMPLE_TABLESPACE procedure in the DBMS_STREAMS_TABLESPACE_ADM package, and the MAINTAIN_TTS procedure uses the CLONE_TABLESPACES procedure in the DBMS_STREAMS_TABLESPACE_ADM package. When a tablespace is cloned, it is made read-only automatically until the clone operation is complete.

The example in this section uses the MAINTAIN_TTS procedure to configure an Oracle Streams replication environment that maintains the following tablespaces using Oracle Streams:

• tbs1

• tbs2

The source database is dbs1.example.com, and the destination database is dbs2.example.com.

The following table lists the decisions that were made about the Oracle Streams replication environment configured in this example.

In this example, the MAINTAIN_TTS procedure will configure the replication environment directly. A configuration script will not be generated. In addition, this example makes the following assumptions:

• The tablespaces tbs1 and tbs2 make a self-contained tablespace set at the source database dbs1.example.com.

• The data files for the tablespace set are both in the /orc/dbs directory at the source database dbs1.example.com.

• The dbs2.example.com database does not contain the tablespace set currently.

The MAINTAIN_SIMPLE_TTS and MAINTAIN_TTS procedures automatically exclude database objects that are not supported by Oracle Streams from the replication environment by adding rules to the negative rule set of each capture and apply process. The PRE_INSTANTIATION_SETUP and POST_INSTANTIATION_SETUP procedures enable you to specify which database objects to exclude from the replication environment.

Query the DBA_STREAMS_UNSUPPORTED data dictionary view to determine which database objects are not supported by Oracle Streams. If unsupported database objects are not excluded, then capture errors will result.

Figure 2-4 provides an overview of the replication environment created in this example.

Figure 2-4 Sample Oracle Streams Environment That Replicates Tablespaces

Description of "Figure 2-4 Sample Oracle Streams Environment That Replicates Tablespaces"

Complete the following steps to use the MAINTAIN_TTS procedure to configure the environment:

1. Complete the required tasks before running the MAINTAIN_TTS procedure. See "Tasks to Complete Before Configuring Oracle Streams Replication" for instructions.

   For this configuration, the following tasks must be completed:

   • Configure an Oracle Streams administrator at both databases. See "Configuring an Oracle Streams Administrator on All Databases".

   • Configure network connectivity and database links:

     • Configure network connectivity between the source database dbs1.example.com and the destination database dbs2.example.com.

     • Create a database link from the source database dbs1.example.com to the destination database dbs2.example.com.

     • Because downstream capture will be configured at the destination database, create a database link from the destination database dbs2.example.com to the source database dbs1.example.com.

     See "Configuring Network Connectivity and Database Links".

   • Ensure that both databases are in ARCHIVELOG mode. See "Ensuring That Each Source Database Is In ARCHIVELOG Mode".

   • Ensure that the initialization parameters are set properly at both databases. See "Setting Initialization Parameters Relevant to Oracle Streams".

   • Configure the Oracle Streams pool properly at both databases. See "Configuring the Oracle Streams Pool".

   • Because the destination database will be the capture database for changes made to the source database, configure log file copying from the source database dbs1.example.com to the destination database dbs2.example.com. The capture process will be an archived-log downstream capture process. See "Configuring Log File Transfer to a Downstream Capture Database".

2. Create the following required directory objects:

   • A source directory object at the source database. This example assumes that this directory object is source_directory.

   • A destination directory object at the destination database. This example assumes that this directory object is dest_directory.

   See "Creating the Required Directory Objects" for instructions.

3. In SQL*Plus, connect to the database that contains the tablespace set as the Oracle Streams administrator. In this example, connect to the dbs1.example.com database.

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

4. Create a directory object for the directory that contains the data files for the tablespaces in the tablespace set. For example, the following statement creates a directory object named tbs_directory that corresponds to the /orc/dbs directory:

   CREATE DIRECTORY tbs_directory AS '/orc/dbs';
   

   If the data files are in multiple directories, then a directory object must exist for each of these directories, and the user who runs the MAINTAIN_TTS procedure in Step 6 must have READ privilege on these directory objects. In this example, the Oracle Streams administrator has this privilege because this user creates the directory object.

5. In SQL*Plus, connect to the destination database dbs2.example.com as the Oracle Streams administrator.

6. Run the MAINTAIN_TTS procedure:

   DECLARE
     t_names  DBMS_STREAMS_TABLESPACE_ADM.TABLESPACE_SET;
   BEGIN
     -- Tablespace names
     t_names(1) := 'TBS1';
     t_names(2) := 'TBS2';
     DBMS_STREAMS_ADM.MAINTAIN_TTS(
        tablespace_names             => t_names,
        source_directory_object      => 'source_directory',
        destination_directory_object => 'dest_directory',
        source_database              => 'dbs1.example.com',
        destination_database         => 'dbs2.example.com',
        perform_actions              => TRUE,
        capture_name                 => 'capture_tts',
        capture_queue_table          => 'streams_queue_table',
        capture_queue_name           => 'streams_queue',
        apply_name                   => 'apply_tts',
        apply_queue_table            => 'streams_queue_table',
        apply_queue_name             => 'streams_queue');
        bi_directional               => FALSE,
        include_ddl                  => TRUE);
   END;
   /
   

   When this procedure completes, the Oracle Streams single-source replication environment is configured.

   Because the procedure is run at the destination database, downstream capture is configured at the destination database for changes to the source database. When you use a configuration procedure to configure downstream capture, the parameters that specify the queue and queue table names are important. In such a configuration, it is more efficient for the capture process and apply process to use the same queue at the downstream capture database to avoid propagating changes between queues. To improve efficiency in this sample configuration, notice that streams_queue is specified for both the capture_queue_name and apply_queue_name parameters. Also, streams_queue_table is specified for both the capture_queue_table and apply_queue_table parameters.

   To monitor the progress of the configuration operation, follow the instructions in "Monitoring Oracle Streams Configuration Progress".

   If this procedure encounters an error and stops, then see "Recovering from Operation Errors" for information about either recovering from the error or rolling back the configuration operation.

The resulting single-source replication environment has the following characteristics:

• Supplemental logging is configured for the shared database objects at the source database dbs1.example.com.

• The dbs1.example.com database has a queue named streams_queue which uses a queue table named streams_queue_table. This queue is for the apply process.

• The dbs2.example.com database has a queue named streams_queue which uses a queue table named streams_queue_table. This queue is shared by the downstream capture process and the apply process.

• At the dbs2.example.com database, an archived-log downstream capture process named capture_tts captures changes made to the source database. Specifically, this downstream capture process captures DML changes made to the tables in the tbs1 and tbs2 tablespaces and DDL changes to these tablespaces and the database objects in them.

  If the capture process is not enabled after an inordinately long time, then check the alert log for errors. See Oracle Streams Concepts and Administration for more information.

• At the dbs2.example.com database, an apply process named apply_tts dequeues the changes from its queue and applies them to the shared database objects.

Configuring Schema Replication with Downstream Capture at Destination

This example configures an Oracle Streams replication environment that replicates data manipulation language (DML) changes to all of the tables in the hr schema. This example configures a two-database replication environment with a downstream capture process at the destination database. This example uses the global database names src.example.com and dest.example.com. However, you can substitute databases in your environment to complete the example. See "Decide Which Type of Replication Environment to Configure" for more information about two-database replication environments.

In this example, the downstream capture process runs on the destination database dest.example.com. Therefore, the resources required to capture changes are freed at the source database src.example.com. This example configures a real-time downstream capture process, not an archived-log downstream capture process. The advantage of real-time downstream capture is that it reduces the amount of time required to capture the changes made at the source database. The time is reduced because the real-time downstream capture process does not need to wait for the redo log file to be archived before it can capture data from it.

The following table lists the decisions that were made about the Oracle Streams replication environment configured in this example.

The database objects being configured for replication might or might not exist at the destination database when you run the MAINTAIN_SCHEMAS procedure. If the database objects do not exist at the destination database, then the MAINTAIN_SCHEMAS procedure instantiates them at the destination database using a Data Pump export/import. During instantiation, the instantiation SCN is set for these database objects. If the database objects already exist at the destination database, then the MAINTAIN_SCHEMAS procedure retains the existing database objects and sets the instantiation SCN for them. In this example, the hr schema exists at both the src.example.com database and the dest.example.com database before the MAINTAIN_SCHEMAS procedure is run.

In this example, the MAINTAIN_SCHEMAS procedure will configure the replication environment directly. A configuration script will not be generated. A Data Pump export dump file instantiation will be performed.

Figure 2-5 provides an overview of the environment created in this example.

Figure 2-5 Two-Database Replication Environment with a Downstream Capture Process

Description of "Figure 2-5 Two-Database Replication Environment with a Downstream Capture Process"

Complete the following steps to use the MAINTAIN_SCHEMAS procedure to configure the environment:

1. Complete the following tasks to prepare for the two-database replication environment:

   1. Configure network connectivity so that the src.example.com database and the dest.example.com database can communicate with each other.

      See Oracle Database 2 Day DBA for information about configuring network connectivity between databases.

   2. Configure an Oracle Streams administrator at each database that will participate in the replication environment. See "Configuring an Oracle Streams Administrator on All Databases" for instructions. This example assumes that the Oracle Streams administrator is strmadmin.

   3. Create a database link from the source database to the destination database and from the destination database to the source database. In this example, create the following database links:

      • From the src.example.com database to the dest.example.com database. Both the name and the service name of the database link must be dest.example.com.

      • From the dest.example.com database to the src.example.com database. Both the name and the service name of the database link must be src.example.com.

      The database link from the dest.example.com database to the src.example.com database is necessary because the src.example.com database is the source database for the downstream capture process at the dest.example.com database. This database link simplifies the creation and configuration of the capture process.

      Each database link should be created in the Oracle Streams administrator's schema. Also, each database link should connect to the Oracle Streams administrator at the other database. See "Configuring Network Connectivity and Database Links" for instructions.

   4. Set initialization parameters properly at each database that will participate in the Oracle Streams replication environment. See "Setting Initialization Parameters Relevant to Oracle Streams" for instructions.

   5. Configure both databases to run in ARCHIVELOG mode. For a downstream capture process to capture changes generated at a source database, both the source database and the downstream capture database must be running in ARCHIVELOG mode. In this example, the src.example.com and dest.example.com databases must be running in ARCHIVELOG mode. See Oracle Database Administrator's Guide for information about configuring a database to run in ARCHIVELOG mode.

   6. Because the destination database (dest.example.com) will be the capture database for changes made to the source database, configure log file copying from the source database src.example.com to the destination database dest.example.com. See "Configuring Log File Transfer to a Downstream Capture Database".

   7. Because this example configures a real-time downstream capture process, add standby redo logs at the downstream database. See "Adding Standby Redo Logs for Real-Time Downstream Capture".

2. Create the following required directory objects:

   • A source directory object at the source database. This example assumes that this directory object is source_directory.

   • A destination directory object at the destination database. This example assumes that this directory object is dest_directory.

   See "Creating the Required Directory Objects" for instructions.

3. In SQL*Plus, connect to the dest.example.com database as the Oracle Streams administrator.

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

4. While still connected to the dest.example.com database as the Oracle Streams administrator, run the MAINTAIN_SCHEMAS procedure to configure replication between the src.example.com database and the dest.example.com database:

   BEGIN
     DBMS_STREAMS_ADM.MAINTAIN_SCHEMAS(
       schema_names                 => 'hr',
       source_directory_object      => 'source_directory',
       destination_directory_object => 'dest_directory',
       source_database              => 'src.example.com',
       destination_database         => 'dest.example.com',
       capture_name                 => 'capture',
       capture_queue_table          => 'streams_queue_qt',
       capture_queue_name           => 'streams_queue',
       apply_name                   => 'apply',
       apply_queue_table            => 'streams_queue_qt',
       apply_queue_name             => 'streams_queue');
   END;
   /
   

   The MAINTAIN_SCHEMAS procedure can take some time to run because it is performing many configuration tasks. Do not allow data manipulation language (DML) or data definition language (DDL) changes to the replicated database objects at the destination database while the procedure is running.

   In the MAINTAIN_SCHEMAS procedure, only the following parameters are required: schema_names, source_directory_object, destination_directory_object, source_database, and destination_database.

   This example specifies the other parameters to show that you can choose the name for the capture process, capture process's queue table, capture process's queue, apply process, apply process's queue table, and apply process's queue. If you do not specify these parameters, then system-generated names are used.

   When you use a configuration procedure to configure downstream capture, the parameters that specify the queue and queue table names are important. In such a configuration, it is more efficient for the capture process and apply process to use the same queue at the downstream capture database to avoid propagating changes between queues. To improve efficiency in this sample configuration, notice that streams_queue is specified for both the capture_queue_name and apply_queue_name parameters. Also, streams_queue_qt is specified for both the capture_queue_table and apply_queue_table parameters.

   When a configuration procedure is run, information about its progress is recorded in the following data dictionary views: DBA_RECOVERABLE_SCRIPT, DBA_RECOVERABLE_SCRIPT_PARAMS, DBA_RECOVERABLE_SCRIPT_BLOCKS, and DBA_RECOVERABLE_SCRIPT_ERRORS. If the procedure stops because it encounters an error, then see Oracle Streams Replication Administrator's Guide for instructions about using the RECOVER_OPERATION procedure in the DBMS_STREAMS_ADM package to recover from these errors.

   Wait until the procedure completes successfully before proceeding to the next step.

5. While still connected to the dest.example.com database as the Oracle Streams administrator, set the downstream_real_time_mine capture process parameter to Y:

   BEGIN
     DBMS_CAPTURE_ADM.SET_PARAMETER(
       capture_name => 'capture',
       parameter    => 'downstream_real_time_mine',
       value        => 'Y');
   END;
   /
   

6. In SQL*Plus, connect to the source database src.example.com as an administrative user.

7. Archive the current log file at the source database:

   ALTER SYSTEM ARCHIVE LOG CURRENT;
   

   Archiving the current log file at the source database starts real-time mining of the source database redo log.

   If the capture process appears to be waiting for redo data for an inordinately long time, then check the alert log for errors. See Oracle Streams Concepts and Administration for more information.

Configuring Schema Replication with Downstream Capture at Third Database

You can use the MAINTAIN_SCHEMAS procedure in the DBMS_STREAMS_ADM package to configure schema replication. The example in this section uses this procedure to configure an Oracle Streams replication environment that maintains the hr schema. The source database is dbs1.example.com, and the destination database is dbs3.example.com.

The following table lists the decisions that were made about the Oracle Streams replication environment configured in this example.

In this example, the MAINTAIN_SCHEMAS procedure will configure the replication environment directly. A configuration script will not be generated. A Data Pump export dump file instantiation will be performed.

The MAINTAIN_SCHEMAS procedure automatically excludes database objects that are not supported by Oracle Streams from the replication environment by adding rules to the negative rule set of each capture and apply process. Query the DBA_STREAMS_UNSUPPORTED data dictionary view to determine which database objects are not supported by Oracle Streams. If unsupported database objects are not excluded, then capture errors will result.

Figure 2-6 provides an overview of the replication environment created in this example.

Figure 2-6 Sample Oracle Streams Environment That Replicates a Schema

Description of "Figure 2-6 Sample Oracle Streams Environment That Replicates a Schema"

Complete the following steps to use the MAINTAIN_SCHEMAS procedure to configure the environment:

1. Complete the required tasks before running the MAINTAIN_SCHEMAS procedure. See "Tasks to Complete Before Configuring Oracle Streams Replication" for instructions.

   For this configuration, the following tasks must be completed:

   • Configure an Oracle Streams administrator at all three databases. See "Configuring an Oracle Streams Administrator on All Databases".

   • Configure network connectivity and database links:

     • Configure network connectivity between all three databases: the source database dbs1.example.com, the destination database dbs3.example.com, and the third database dbs2.example.com.

     • Create a database link from the source database dbs1.example.com to the destination database dbs3.example.com.

     • Because downstream capture will be configured at the third database, create a database link from the third database dbs2.example.com to the source database dbs1.example.com.

     • Because downstream capture will be configured at the third database, create a database link from the third database dbs2.example.com to the destination database dbs3.example.com.

     • Because the replication environment will be bi-directional, create a database link from the destination database dbs3.example.com to the source database dbs1.example.com.

     See "Configuring Network Connectivity and Database Links".

   • Ensure that the source database, the destination databases, and the third database are in ARCHIVELOG mode. See "Ensuring That Each Source Database Is In ARCHIVELOG Mode".

   • Ensure that the initialization parameters are set properly at all databases. See "Setting Initialization Parameters Relevant to Oracle Streams".

   • Configure the Oracle Streams pool properly at both databases. See "Configuring the Oracle Streams Pool".

   • Because a third database (dbs2.example.com) will be the capture database for changes made to the source database, configure log file copying from the source database dbs1.example.com to the third database dbs2.example.com. See "Configuring Log File Transfer to a Downstream Capture Database".

   • Because this example configures a real-time downstream capture process, add standby redo logs at the downstream database (dbs2.example.com). See "Adding Standby Redo Logs for Real-Time Downstream Capture".

2. Create the following required directory objects:

   • A source directory object at the source database. This example assumes that this directory object is source_directory.

   • A destination directory object at the destination database. This example assumes that this directory object is dest_directory.

   See "Creating the Required Directory Objects" for instructions.

3. In SQL*Plus, connect to the third database dbs2.example.com as the Oracle Streams administrator.

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

4. Run the MAINTAIN_SCHEMAS procedure:

   BEGIN
     DBMS_STREAMS_ADM.MAINTAIN_SCHEMAS(
       schema_names                 => 'hr',
       source_directory_object      => 'source_directory',
       destination_directory_object => 'dest_directory',
       source_database              => 'dbs1.example.com',
       destination_database         => 'dbs3.example.com',
       perform_actions              => TRUE,
       dump_file_name               => 'export_hr.dmp',
       capture_queue_table          => 'rep_capture_queue_table',
       capture_queue_name           => 'rep_capture_queue',
       capture_queue_user           => NULL,
       apply_queue_table            => 'rep_dest_queue_table',
       apply_queue_name             => 'rep_dest_queue',
       apply_queue_user             => NULL,
       capture_name                 => 'capture_hr',
       propagation_name             => 'prop_hr',
       apply_name                   => 'apply_hr',
       log_file                     => 'export_hr.clg',
       bi_directional               => TRUE,
       include_ddl                  => TRUE,
       instantiation                => DBMS_STREAMS_ADM.INSTANTIATION_SCHEMA);
   END;
   /
   

   Because this procedure configures a bi-directional replication environment, do not allow DML or DDL changes to the shared database objects at the destination database while the procedure is running.

   Because the procedure is run at the third database, downstream capture is configured at the third database for changes to the source database.

   To monitor the progress of the configuration operation, follow the instructions in "Monitoring Oracle Streams Configuration Progress".

   If this procedure encounters an error and stops, then see "Recovering from Operation Errors" for information about either recovering from the error or rolling back the configuration operation.

5. Set the downstream_real_time_mine capture process parameter to Y:

   BEGIN
     DBMS_CAPTURE_ADM.SET_PARAMETER(
       capture_name => 'capture_hr',
       parameter    => 'downstream_real_time_mine',
       value        => 'Y');
   END;
   /
   

6. Connect to the source database dbs1.example.com as an administrative user with the necessary privileges to switch log files.

7. Archive the current log file at the source database:

   ALTER SYSTEM ARCHIVE LOG CURRENT;
   

   Archiving the current log file at the source database starts real time mining of the source database redo log.

   If the capture process appears to be waiting for redo data for an inordinately long time, then check the alert log for errors. See Oracle Streams Concepts and Administration for more information.

8. Configure conflict resolution for the shared database objects if necessary.

   Typically, conflicts are possible in a bi-directional replication environment. If conflicts are possible in the environment created by the MAINTAIN_SCHEMAS procedure, then configure conflict resolution before you allow users to make changes to the shared database objects.

   See Chapter 9, "Oracle Streams Conflict Resolution" for information.

The bi-directional replication environment configured in this example has the following characteristics:

• Supplemental logging is configured for the shared database objects at the source and destination databases.

• The dbs1.example.com database has a queue named rep_dest_queue which uses a queue table named rep_dest_queue_table. This queue is for the apply process.

• The dbs3.example.com database has a queue named rep_capture_queue which uses a queue table named rep_capture_queue_table. This queue is for the local capture process.

• The dbs3.example.com database has a queue named rep_dest_queue which uses a queue table named rep_dest_queue_table. This queue is for the apply process.

• The dbs2.example.com database has a queue named rep_capture_queue which uses a queue table named rep_capture_queue_table. This queue is for the downstream capture process.

• At the dbs2.example.com database, a real-time downstream capture process named capture_hr captures DML and DDL changes to the hr schema and the database objects in the schema at the source database.

• At the dbs3.example.com database, a local capture process named capture_hr captures DML and DDL changes to the hr schema and the database objects in the schema at the destination database.

• A propagation running on the dbs2.example.com database named prop_hr sends the captured changes from the queue in the dbs2.example.com database to the queue in the dbs3.example.com database.

• A propagation running on the dbs3.example.com database named prop_hr sends the captured changes from the queue in the dbs3.example.com database to the queue in the dbs1.example.com database.

• At the dbs1.example.com database, an apply process named apply_hr dequeues the changes from rep_dest_queue and applies them to the database objects.

• At the dbs3.example.com database, an apply process named apply_hr dequeues the changes from rep_dest_queue and applies them to the database objects.

• Tags are used to avoid change cycling. Specifically, each apply process uses an apply tag so that redo records for changes applied by the apply process include the tag. Each apply process uses an apply tag that is unique in the replication environment. Each propagation discards changes that have the tag of the apply process running on the same database. See "Change Cycling and Tags" for more information.