Can I use streams to replicate a schema to another database ?

The Oracle (tm) Users' Co-Operative FAQ

Setting up one-way replication at the schema level between two databases using Oracle Streams 10gR2

Back to index of questions

        
复制

Data to be replicated is not obtained by direct access to OLTP database. Data extraction process interferes with regular database operation. The source of the information is Oracle's archived log files. This is much less intrusive for online users. Streams uses Oracle’s Log Miner infrastructure to read redo logs.

 
复制

(For an introduction to Streams, basic concepts, list of reference documentation, and an example of a single table one-way replication procedure, please refer to Sanjay Mishra's article,  “Making Data Flow” at http://www.oracle.com/technology/oramag/oracle/04-nov/o64streams.html)

 
复制

The single PL/SQL procedure - dbms_streams_adm.maintain_schemas completely sets up basic schema level replication. Certain tasks must be completed, though, before this procedure runs in step 8.

 
复制

1. Configure source database to run in archivelog mode

2. Set up Streams administrator strmadmin on both databases (source and target)

3. Adjust initialization parameters on both databases for Streams replication

4. Create database link from source (ORCLA.WORLD)  to target (ORCLB.WORLD) database

5. Create database link from target (ORCLB.WORLD)  to source (ORCLA.WORLD) database

(This link is needed because I will use the network Data Pump export/import which is not using an export dump file; standard export/import using dump file is also possible )

6. (Recommended) Create directory on source machine (It will contain generated replication script.)

7.( Recommended) Run dbms_streams_adm.maintain_schemas with perform_actions  parameter set to false on source database to generate the replication script. This is a dry run to verify the script’s correctness and avoid time-consuming replication cleanup (in case the replication setup script fails.)

8. Run dbms_streams_adm.maintain_schemas procedure on the source database with perform_actions parameter set to true. This will startt the replication setup.

9. Verify that DDL and DML changes on ORCLA.WORLD are propagated to ORCLB.WORLD 

 
复制

Steps in detail

For steps 1 – 4, please refer to the Mishra's article mentioned above. They are identical.

5. Create a database link on target database:

connect strmadmin/strmadmin@ORCLB.world

CREATE DATABASE LINK ORCLA.WORLD

CONNECT TO STRMADMIN

IDENTIFIED BY STRMADMIN

USING ‘ORCLA.WORLD’;

6. Create a directory on the source database where the script generated by dbms_streams_adm.maintain_schemas will be stored:

CONNECT strmadmin/strmadmin@orcla.world

CREATE OR REPLACE DIRECTORY ADMIN AS '/home/oracle/Streams';

7. Generate script schema_replication.sql in Oracle admin directory 

(/home/oracle/Streams on Linux):

CONN strmadmin/strmadmin@orcla.world

BEGIN

  DBMS_STREAMS_ADM.MAINTAIN_SCHEMAS(

    schema_names                      =>             'scott',

    source_database                   =>             'orcla.world',

    destination_database      =>             'orclb.world',

    capture_name                      =>             'capture_scott',

    capture_queue_table                =>             'rep_capture_queue_table',

    capture_queue_name                =>             'rep_capture_queue',

    capture_queue_user                =>              null,

    apply_name                        =>             'apply_scott',

    apply_queue_table                 =>             'rep_dest_queue_table',

    apply_queue_name                  =>             'rep_dest_queue',

    apply_queue_user                =>                null,

    propagation_name                  =>             'prop_scott',

    log_file                          =>             'exp.log',

    bi_directional                  =>                false,

    include_ddl                       =>              true,

    instantiation                     =>              dbms_streams_adm.instantiation_schema_network,

    perform_actions                   =>              false,       

    script_name                       =>             'schema_replication.sql',

    script_directory_object           =>             'admin'

    );

END;

The /home/oracle/Streams/schema_replication.sql script now contains commands to completely configure SCOTT schema replication. A brief description of scripts contents follows:

- Supplemental logging data is added for all tables in the SCOTT schema; it is required to log additional data to redo logs if you want Streams to work properly

- dbms_streams_adm.set_up_queue procedure is run to create capture queue and capture table

- dbms_streams_adm.add_schema_propagation_rules is run to add rules to the positive rule set for propagation; this is queue-to-queue propagation

- Propagation is temporarily disabled

- dbms_streams_adm.add_schema_rules is run to add rules to capture process

-  Datapump schema mode import (network) is run from ORCLB.WORLD database ( SCOTT schema is exported from ORCLA.WORLD and imported into ORCLB.WORLD; DBMS_DATAPUMP package is used for this purpose)

- Capture process is started on ORCLA.WORLD database

- Apply queue rep_dest_queue on the target database is configured using the dbms_streams_adm.set_up_queue procedure

- Schema rules for the apply process are added (dbms_streams_adm.add_schema_rules procedure is executed )

- A tag is added to apply process and apply process is started

- Propagation is enabled

8. We will now execute maintain_schemas procedure; perform_actions parameter is changed to true.

BEGIN

  DBMS_STREAMS_ADM.MAINTAIN_SCHEMAS(

    schema_names                              =>      'scott',

    source_directory_object                   =>       NULL,

    destination_directory_object              =>       NULL,

    source_database                           =>      'orcla.world',

    destination_database                      =>      'orclb.world',

    capture_name                              =>      'capture_scott',

    capture_queue_table                       =>      'rep_capture_queue_table',

    capture_queue_name                        =>      'rep_capture_queue',

    capture_queue_user                        =>       null,

    apply_name                                =>      'apply_scott',

    apply_queue_table                         =>      'rep_dest_queue_table',

    apply_queue_name                          =>      'rep_dest_queue',

    apply_queue_user                          =>       null,

    propagation_name                          =>      'prop_scott',

    log_file                                  =>      'exp.log',

    bi_directional                            =>       false,

    include_ddl                               =>       true,

    instantiation                             =>       dbms_streams_adm.instantiation_schema_network,

    perform_actions                           =>       true         

    );

END;

/

The execution of this procedure will take some time, depending on the schema size, number of objects, volume of data etc. When it completes all DML and DDL changes to tables in the SCOTT schema on ORCLA.WORLD, the database will be propagated to ORCLB.WORLD database.

Oracle Streams is relatively new tool primarily designed to help move real time data between databases.

The easy setup, possibility of light data transformation, and good performance make it welcome addition to a DBA's toolset. 

 
复制

Back to index of questions