Viewing the Topology of Oracle Streams in 11g Oracle 11g New Features Tips by Donald BurlesonJuly 12, 2015

Oracle 11g New Features Tips

Managing a replicated environment can be a challenge.  Oracle 11g has new views that allow you to view the topology of Streams.  If you?re a forgetful DBA, or having to take over management of Streams from another DBA who has kept poor documentation these new views can be of great assistance:

View	Description
DBA_STREAMS_TP_DATABASE	Displays the databases in the Oracle Streams replication environment
DBA_STREAMS_TP_COMPONENT	Displays the components in the Oracle Streams replication
DBA_STREAMS_TP_COMPONENT_LINK	Displays the paths in the Oracle Streams topology for the components

The following query shows the databases involved in an Oracle Streams replication environment:

column global_name heading 'db name' format a15
column last_queried heading 'queried'
column version heading 'version' format a12
column compatibility heading 'compatible' format a12
column management_pack_access heading 'management pack' format a20

select
  global_name,
  last_queried,
  version,
  compatibility,
  management_pack_access
from dba_streams_tp_database; 

db name         queried   version      compatible   management pack
--------------- --------- ------------ ------------ --------------------
DEV11           13-NOV-07 11.1.0.6.0   11.1.0.0.0   DIAGNOSTIC+TUNING
DG              13-NOV-07 11.1.0.6.0   11.1.0.0.0   DIAGNOSTIC+TUNING

The above query allows the DBA to quickly see the databases involved in the replication of data via Streams. 

Another useful view is DBA_STREAMS_TP_COMPONENT which shows each component in the Stream and the role it plays (i.e. Capture, Queue, Propogation, Apply):

column component_id heading 'comp id' format 999999999
column component_name heading 'component' format a25
column component_name truncated
column component_db heading 'db of component' format a15
column component_type heading 'type' format a12
column component_changed_time heading 'last changed'

select
  *
from dba_streams_tp_component;

   comp id component                 db of component type         last changed
---------- ------------------------- --------------- ------------ ------------
         6 STREAMS_APPLY2            DG              APPLY        11-NOV-07
         2 "STREAMS"."STREAMS_CAPTUR DEV11           PROPAGATION  11-NOV-07
                                                     SENDER
 
         5 DEV11=>"STREAMS"          DG              PROPAGATION  11-NOV-07
                                                     RECEIVER
 
         1 "STREAMS"."STREAMS_CAPTUR DEV11           QUEUE        11-NOV-07
         3 STREAMS_CAPTURE2          DEV11           CAPTURE      11-NOV-07
         4 "STREAMS"."STREAMS_APPLY2 DG              QUEUE        11-NOV-07

From this query above the DBA is able to quickly identify what is happening in this Streams environment.  Data on the DEV11 database is being captured and then applied over to the DG database.  The ?Last Changed? column is useful when the replication suddenly stops working for no apparent reason.  Especially if you have multiple DBAs on a team and they all swear they didn't change anything?

The following query illustrates the flow of data between the Streams components. 

select
  trim(source_component_db) || ' ' ||
  trim(source_component_type) || '-->' ||
  trim(destination_component_db)  || ' ' ||
  trim(destination_component_type) "Path of Streams"
from DBA_STREAMS_TP_COMPONENT_LINK
where active='YES'
order by position;

Path of Streams
--------------------------------------------------------------------
DEV11 CAPTURE-->DEV11 QUEUE
DEV11 QUEUE-->DEV11 PROPAGATION SENDER
DEV11 PROPAGATION SENDER-->DG PROPAGATION RECEIVER
DG PROPAGATION RECEIVER-->DG QUEUE
DG QUEUE-->DG APPLY

The above query show the DBA that DEV11 captures, queues and propagates the data to the DG database where it is received, queued and applied. 

Other new features of Streams in 11g include:

• Performance optimizations (2x faster)

• Hub & Spoke replication ability

• Compare and converge Source and Target data

• Ability to trace Streams messages from start to finish

• Synchronous change capture