Configuring Table Replication Using the DBMS_STREAMS_ADM Package

Nel documento Oracle® Streams Replication Administrator’s Guide 10g (pagine 132-137)

You can use the MAINTAIN_TABLES in the DBMS_STREAMS_ADM package to configure table replication. The example in this section uses this procedure to configure a Streams replication environment that maintains the tables in 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:

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

departments employees

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

countries regions locations jobs

job_history

Local capture will be configured for the source database.

The replication environment will be single source, not bi-directional.

A Data Pump network import instantiation will be performed.

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.

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 Streams Replication" on page 6-13 for instructions.

For this configuration, the following tasks must be completed:

Configure a Streams administrator at both databases.

Create a database link from the source database stm1.net to the destination database stm2.net.

Because the MAINTAIN_TABLES procedure will perform a Data Pump network import instantiation, create a database link from the destination database stm2.net to the source database stm1.net.

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

Make sure the source database stm1.net is in ARCHIVELOG mode.

Make sure the initialization parameters are set properly at both 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 source database as the Streams administrator and run the MAINTAIN_TABLES procedure:

DBMS_STREAMS_ADM.MAINTAIN_TABLES(

table_names => tables, source_directory_object => NULL, destination_directory_object => NULL, source_database => 'stm1.net',

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.

3. Modify the configure_rep.sql script:

a. Navigate to the directory that corresponds with the SCRIPT_DIRECTORY directory object on the computer system running the source database.

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

c. 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:

Configuring Replication Using the DBMS_STREAMS_ADM Package

dbms_streams_adm.add_table_rules(

table_name => '"HR"."DEPARTMENTS"', streams_type => 'CAPTURE',

streams_name => '"STM1$CAP"',

queue_name => '"STRMADMIN"."STM1$CAPQ"', include_dml => TRUE,

include_ddl => FALSE, include_tagged_lcr => TRUE, source_database => 'STM1.NET', inclusion_rule => TRUE,

and_condition => get_compatible);

dbms_streams_adm.add_table_rules(

table_name => '"HR"."EMPLOYEES"', streams_type => 'CAPTURE', streams_name => '"STM1$CAP"',

queue_name => '"STRMADMIN"."STM1$CAPQ"', include_dml => TRUE,

include_ddl => FALSE, include_tagged_lcr => TRUE, source_database => 'STM1.NET', inclusion_rule => TRUE,

and_condition => get_compatible);

d. 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 => '"STM1$CAP"',

queue_name => '"STRMADMIN"."STM1$CAPQ"', include_dml => TRUE,

include_ddl => TRUE, include_tagged_lcr => TRUE, source_database => 'STM1.NET', inclusion_rule => TRUE,

and_condition => get_compatible);

dbms_streams_adm.add_table_rules(

table_name => '"HR"."EMPLOYEES"', streams_type => 'CAPTURE', streams_name => '"STM1$CAP"',

queue_name => '"STRMADMIN"."STM1$CAPQ"', include_dml => TRUE,

include_ddl => TRUE, include_tagged_lcr => TRUE, source_database => 'STM1.NET', inclusion_rule => TRUE,

and_condition => get_compatible);

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

e. Save and close the configure_rep.sql script.

Configuring Replication Using the DBMS_STREAMS_ADM Package

4. At the source database, connect as the Streams administrator, and run the configuration script:

CONNECT strmadmin/strmadminpw@stm1.net SET ECHO ON

SPOOL configure_rep.out

@configure_rep.sql

The script prompts you to supply information about the database names and the Streams administrators. When this configuration script completes, the 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 stm1.net has a queue and queue table with system-generated names.

The destination database stm2.net 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 propagates 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 Replication Using the DBMS_STREAMS_ADM Package

7

Nel documento Oracle® Streams Replication Administrator’s Guide 10g (pagine 132-137)