2 Simple Oracle Streams Replication Configuration

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.

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 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 4.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 4.c for this clause. Archived redo logs must be available for the until SCN specified and for higher SCN values. Therefore, Step 4.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 44.h. 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 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.

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.

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 Oracle Streams Conflict Resolution for instructions.

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.

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

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.

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

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.

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.

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.

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.

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

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.