Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted Mar 28, 2003

Change data capture implementation in Oracle Data warehouses - Part 2 - Oracle Streams implementation - Page 4

By DatabaseJournal.com Staff

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.



Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM


















Thanks for your registration, follow us on our social networks to keep up-to-date