You can use the MAINTAIN_SCHEMAS in the DBMS_STREAMS_ADM package to configure schema replication. The example in this section uses this procedure to configure a Streams replication environment that maintains the hr schema. The source database is stm1.net, and the destination database is stm2.net.
Assume that the following decisions were made about the configuration:
■ DDL changes to hr schema and the database objects in the hr schema will be maintained.
■ The replication environment will be bi-directional.
See Also:
■ Chapter 3, "Streams Conflict Resolution" and "Managing Streams Conflict Detection and Resolution" on page 9-21
■ Chapter 4, "Streams Tags"
Configuring Replication Using the DBMS_STREAMS_ADM Package
■ A downstream capture process running on a third database named stm3.net will capture changes made to the source database (stm1.net), and a propagation at stm3.net will propagate these captured changes to the destination database (stm2.net).
■ A Data Pump export dump file instantiation will be performed.
■ The MAINTAIN_SCHEMAS procedure will configure the replication environment directly. A configuration script will not be generated.
The MAINTAIN_SCHEMAS procedure automatically excludes database objects that are not supported by 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 Streams. If unsupported database objects are not excluded, then capture errors will result.
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 Streams Replication" on page 6-13 for instructions.
For this configuration, the following tasks must be completed:
■ Configure a Streams administrator at all three databases.
■ Create a database link from the source database stm1.net to the destination database stm2.net.
■ Because downstream capture will be configured at the third database, create a database link from the third database stm3.net to the source database stm1.net.
■ Because downstream capture will be configured at the third database, create a database link from the third database stm3.net to the destination database stm2.net.
■ Because the replication environment will be bi-directional, create a database link from the destination database stm2.net to the source database stm1.net.
■ 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.
■ Make sure the source database and destination databases are in ARCHIVELOG mode.
■ Because a third database (stm3.net) will be the capture database for changes made to the source database, configure log file copying from the source database stm1.net to the third database stm3.net.
■ Make sure the initialization parameters are set properly at all databases.
See Also: "Decisions to Make Before Configuring Streams
Replication" on page 6-6 for more information about these decisions
Configuring Replication Using the DBMS_STREAMS_ADM Package
2. Connect to the third database as the Streams administrator and run the MAINTAIN_SCHEMAS procedure:
CONNECT strmadmin/strmadminpw@stm3.net BEGIN
DBMS_STREAMS_ADM.MAINTAIN_SCHEMAS(
schema_names => 'hr',
source_directory_object => 'SOURCE_DIRECTORY', destination_directory_object => 'DEST_DIRECTORY', source_database => 'stm1.net', destination_database => 'stm2.net', 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.
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.
If this procedure encounters an error and stops, then see "Recovering from Configuration Errors" on page 13-1 for information about either recovering from the error or rolling back the configuration operation.
3. 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.
Configuring Replication Using the DBMS_STREAMS_ADM Package
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 stm1.net 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 stm2.net 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 stm2.net 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 stm3.net 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 stm3.net database, a 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 stm2.net 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 stm3.net database named prop_hr propagates the captured changes from the queue in the stm3.net database to the queue in the stm2.net database.
■ A propagation running on the stm2.net database named prop_hr propagates the captured changes from the queue in the stm2.net database to the queue in the stm1.net database.
■ At the stm1.net database, an apply process named apply_hr dequeues the changes from rep_dest_queue and applies them to the database objects.
■ At the stm2.net 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 Also:
■ Chapter 3, "Streams Conflict Resolution" and "Managing Streams Conflict Detection and Resolution" on page 9-21
■ Chapter 4, "Streams Tags"
Configuring Replication Using the DBMS_STREAMS_ADM Package