Test your Streams configuration.
The simplest way to test whether the streams setup is
successful is to use the Oracle Enterprise Manager Console (OEM). When you
login OEM console (you have to configure the OMS) you will see the Streams
process status as shown below in fig 1:
Figure 1: OEM console showing Streams setup status
The other option is to check the dictionary views.
At the destination database
SQL> select apply_name,queue_name,apply_user, status from dba_apply;
SQL>
APPLY_NAME QUEUE_NAME APPLY_USER STATUS
------------------------------ ------------------------------ ---------------------
STRMADMIN_LOCAL STREAMS_QUEUE STRMADMIN ENABLED
At the source database
SQL> select capture_name,queue_name,status from dba_capture;
SQL>
CAPTURE_NAME QUEUE_NAME STATUS
------------------------------ ------------------------------ --------
STRMADMIN_CAPTURE STREAMS_QUEUE ENABLED
Once the above tests are successful, you are all set to see how Streams
works.
Login to DEMO@local.world
schema and update the DEPT or the EMP tables and commit the changes.
Login to DEMO@remote.world
, you will see the changes applied to the destination tables. Depending on the
resources available, the process may take some time. The status of the change
capture and apply process can also be seen in the OEM console.
Other Streams Related Dictionary Views
|
ALL_CAPTURE
|
Details about each capture process that stores the
captured changes in a queue
|
|
ALL_CAPTURE_PARAMETERS
|
Details about parameters for each capture process that
stores the captured changes in a queue visible to the current user
|
|
ALL_CAPTURE_PREPARED_DATABASE
|
Is the local database prepared for instantiation?
|
|
ALL_CAPTURE_PREPARED_SCHEMAS
|
All schemas at the local database that are prepared for
instantiation
|
|
ALL_CAPTURE_PREPARED_TABLES
|
All tables visible to the current user that are prepared
for instantiation
|
|
DBA_CAPTURE
|
Details about the capture process
|
|
DBA_CAPTURE_PARAMETERS
|
All parameters for capture process
|
|
DBA_CAPTURE_PREPARED_DATABASE
|
Is the local database prepared for instantiation?
|
|
DBA_CAPTURE_PREPARED_SCHEMAS
|
All schemas at the local database that are prepared for
instantiation
|
|
DBA_CAPTURE_PREPARED_TABLES
|
All tables prepared for instantiation
|
|
ALL_APPLY
|
Details about each apply process that dequeues from the
queue visible to the current user
|
|
ALL_APPLY_CONFLICT_COLUMNS
|
Details about conflict resolution on tables visible to the
current user
|
|
ALL_APPLY_DML_HANDLERS
|
Details about the dml handler on tables visible to the
current user
|
|
ALL_APPLY_ERROR
|
Error transactions that were generated after dequeuing
from the queue visible to the current user
|
|
ALL_APPLY_KEY_COLUMNS
|
Alternative key columns for a STREAMS table visible to the
current user
|
|
ALL_APPLY_PARAMETERS
|
Details about parameters of each apply process that dequeues
from the queue visible to the current user
|
|
ALL_APPLY_PROGRESS
|
Information about the progress made by the apply process
that dequeues from the queue visible to the current user
|
|
DBA_APPLY
|
Details about the apply process
|
|
DBA_APPLY_CONFLICT_COLUMNS
|
Details about conflict resolution
|
|
DBA_APPLY_DML_HANDLERS
|
Details about the dml handler
|
|
DBA_APPLY_ERROR
|
Error transactions
|
|
DBA_APPLY_INSTANTIATED_OBJECTS
|
Details about objects instantiated
|
|
DBA_APPLY_KEY_COLUMNS
|
alternative key columns for a table for STREAMS
|
|
DBA_APPLY_PARAMETERS
|
All parameters for apply process
|
|
For information on other view, query the Dictionary views
as
select * from dict
where table_name like '%APPLY%'
or table_name like '%CAPTURE%'
or table_name like '%PROPAGATION%'
or table_name like '%STREAMS%'
|
Streams Set up using Oracle Enterprise Manager (OEM)
Oracle Streams environment can also be used to set up the
Streams environment. However, you should use a patch which is available on the
Oracle technology website. This patch should be saved under
$ORACLE_HOME/classes in Windows environment. In addition to the scripts
generated by OEM, you've got to use some additional steps that are already
introduced in the steps above. The more complex configuration of Streams such
as that between a non-oracle database and an Oracle database requires custom
programming using Oracle supplied PLSQL packages.
Conclusion
In the third part of this article,
we will cover the Oracle 9i Change Data Capture framework using the DBMS_CAPTURE_ADM
PL/SQL package.
» 
Comment and Contribute
