Configure the Streams bidirectional transmission test based on Oracle 11g Streams one-way transmission

Description:
Originally, one-way transmission of Streams was directly configured on two database servers,
The original source database is two clusters of 64-bit HP-UNIX Oracle11gR2 database,
The original target database is a 64-bit Linux Oracle11gR2 database,
Now on the basis of the above, the Oracle11gR2 of a 64-bit Linux as the source database, the two clusters of 64-bit HP-UNIX Oracle11gR2 database as the target database,
Install the one-way transmission step of Streams for configuration, so that it becomes a two-way Steams transmission between two database servers.

10.2.2.1: The target database is the source database 10.1.1.1 and 10.1.1.2 cluster databases.

1. source database archiving Mode
Source:
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 420
Next log sequence to archive 422
Current log sequence 422

2. Enable append logs in the source database
Enable secondary log
SQL> alter database add supplemental log data;

Database altered.

3. Data of testUser needs to be transmitted and the source queue needs to be created in the source database.

> Sqlplus strmadmin/strmadmin

SQL> BEGIN DBMS_STREAMS_ADM.SET_UP_QUEUE (
2 queue_table => 'source _ QUEUE_TABLE ',
3 queue_name => 'source _ QUEUE ',
4 queue_user => 'strmadmin ');
5 END;
6/

PL/SQL procedure successfully completed.

Or:

SQL> EXEC DBMS_STREAMS_ADM.SET_UP_QUEUE ();

PL/SQL procedure successfully completed.
This command creates a queue with the default name streams_queue. The default queue list is STREAMS_QUEUE_TABLE.

The object type stored in the queue is anaydata.

Remove queue:
Exec dbms_streams_adm.remove_queue (
Queue_name => 'streams _ queue ',
Cascade => true,
Drop_unused_queue_table => true );

You can use dba_queues and dba_queue_tables to check:

SQL> select owner, queue_table, name from dba_queues where wner = 'strmadmin ';

OWNER QUEUE_TABLE NAME

------------------------------------------------------------

STRMADMIN SOURCES_QUEUE_TABLE SOURCES_QUEUE

STRMADMIN SOURCES_QUEUE_TABLE AQ $ _ SOURCES_QUEUE_TABLE_E

 

SQL> select owner, queue_table, object_type from dba_queue_tables where wner = 'strmadmin ';

OWNER QUEUE_TABLE OBJECT_TYPE

-----------------------------------------------------

STRMADMIN SOURCES_QUEUE_TABLE SYS. ANYDATA

4. Create a receiving queue in the target database
$ Sqlplus strmadmin/strmadmin

 

SQL> BEGIN DBMS_STREAMS_ADM.SET_UP_QUEUE (
2 queue_table => 'target _ QUEUE_TABLE ',
3 queue_name => 'target _ QUEUE ',
4 queue_user => 'strmadmin ');
5 END;
6/

PL/SQL procedure successfully completed.

5. Create a capture process in the source database
CBDBS01-> sqlplusstrmadmin/strmadmin @ CBOMS

SQL> BEGIN DBMS_STREAMS_ADM.ADD_SCHEMA_RULES (
2 schema_name => 'testuser ',
3 streams_type => 'capture ',
4 streams_name => 'capture _ Stream ',
5 queue_name => 'strmadmin. SOURCE_QUEUE ',
6 required de_dml => true,
7 include_ddl => true,
8 SOURCE_DATABASE => 'bhoms ',
9 include_tagged_lcr => false,
10 inclusion_rule => true );
11 END;
12/

PL/SQL procedure successfully completed.

You can view the following information through dba_capture:

SQL> select CAPTURE_NAME, QUEUE_NAME, START_SCN, STATUS, CAPTURE_TYPE from dba_capture;

SQL> select * from ALL_CAPTURE_PREPARED_SCHEMAS;

6. Create a propagation process in the source database
CBDBS01-> sqlplus strmadmin/strmadmin

SQL> BEGIN DBMS_STREAMS_ADM.ADD_SCHEMA_PROPAGATION_RULES (
2 schema_name => 'testuser ',
3 streams_name => 'source _ to_target ',
4 source_queue_name => 'strmadmin. SOURCE_QUEUE ',
5 destination_queue_name => 'strmadmin. TARGET_QUEUE @ CBOMS ',
6 required de_dml => true,
7 include_ddl => true,
8 source_database => 'bhoms ',
9 inclusion_rule => true,
10 queue_to_queue => true );
11 END;
12/

PL/SQL procedure successfully completed.

Restart propagation process
View the propagation process name
Select * from all_propagation;

You can use dba_propagations to check whether propagation is enabled.

SQL> select PROPAGATION_NAME, SOURCE_QUEUE_NAME, DESTINATION_QUEUE_NAME, DESTINATION_DBLINK, STATUS from dba_propagation
If the status is not "ENABLED" but "DISABLED"

Can be started
SQL> exec dbms_propagation_adm.start_propagation ('source _ to_target '); (close to exec dbms_propagation_adm.stop_propagation ('source _ to_target ');)

7. Create the apply process in the target database
$ Sqlplus strmadmin/strmadmin

SQL> BEGIN
2 DBMS_STREAMS_ADM.ADD_SCHEMA_RULES (
3 schema_name => 'testuser ',
4 streams_type => 'application ',
5 streams_name => 'target _ apply_stream ',
6 queue_name => 'strmadmin. TARGET_QUEUE ',
7 include_dml => true,
8 required de_ddl => true,
9 include_tagged_lcr => false,
10 source_database => 'bhoms ',
11 inclusion_rule => true );
12 END;
13/

PL/SQL procedure successfully completed.

You can use:

Dba_apply

V $ streams_apply_reader

V $ streams_apply_coordinator

V $ streams_apply_server

1 2 Next Page