How to Stream - a 10g Release 1 Example

Posted at Tuesday, January 03, 2006
One of the newest weapons in the Oracle DBA's high-availability arsenal is Oracle's Streams functionality. Streams allows for the propagation of data between multiple database systems at the table, schema, and/or database levels. Complex business rules - including transformations and the like - can be achieved using the product. One of the most common uses I have seen is the use of a bidirectional model in which, one or more databases (with the same structures) can be updated simultaneous and changes from all are propagated universally.

Having said that, first things first. I have created an example (contained within a zip file, consisting of scripts and batch files, etc.), which will build 2 - 10g Release 1 databases and after words create a simple, unidirectional Streams implementation. From this baseline, more complicated implementations can be derived.

The following example assumes the following:

  • You are on an Oracle-supported, Windows platform, which has the 10g Release 1 Database software installed.


  • You have adequate memory and disk space for the 2 databases, which the script creates - a powerful laptop with a 1 GB of memory and a couple of free GBs of disk space should do.

To get started, save the zip archive in question locally (Note: Right-click 'Save Target As'):
10GR1_Unidirectional_Streams.zip

There are 3 batch files whose configuration sections will need to be modified:
drop_create_databases.bat; setup_streams.bat, and remove_streams.bat. Change the following 4 environment variables in each: DB_DOMAIN; ORACLE_HOME (path to Oracle 10g Release 1 software home); ORACLE_LISTENER_SERVICE (name of the listener service created during the software installation process); LSNR_PORT (a valid listening port for automatic service registration of the instances), and DIR_PATH (the directory location, under which the database files as well as configuration files will be kept).

Once the aforementioned environment variables have been set appropriate for your installation - and assuming that the prerequisites described earlier are met - then you are ready to install the databases, set up the streams implementation, and test. Do so by running the setup.bat batch file. This will do all the work and might take up to half an hour to complete, so be patient.

After running the setup, check the test_streams.log log file to see the results. A successful run should
contain the following:

DECODE(TOTAL_BYTES,0,'P                                                         
-----------------------
PROPAGATION WORKING

DECODE(COUNT(*)
---------------
DML WORKING

DECODE(COUNT(*)
---------------
DDL WORKING


The next step is to make this configuration bidirectional.

Save the zip archive in question locally (Note: Right-click 'Save Target As'):
10GR1_Bidirectional_Streams.zip and once again update the values of the environment variables listed prior. Once accomplished, you can make the previous implementation bidirectional by executing the make_streams_bidirectional.bat.

After running the batch file, check the test_streams_bi.log log file to see the results. A successful run should
contain the following:

DECODE(TOTAL_BYTES,0,'P                                                         
-----------------------
PROPAGATION WORKING

DECODE(COUNT(DB DBNAME
--------------- --------------------
DML WORKING TEST_BI

DECODE(COUNT(CO TABLE_NAME COLUMN_NAME
--------------- ------------------------------ ------------------------------
OWNER
------------------------------
DDL WORKING HEARTBEAT_TBL I_WORK
HEARTBEAT


Labels: ,