Streams Messaging Sample Application

Table of Contents

• Required Software
• Notations used
• Extracting the source code
• Configuring the Application
• Deploying and Running the Application
• Testing the Application
• CleanUp of the Application
• Finding where the error lies
• Troubleshooting

1) Two Oracle9i 9.2.0.4 or later databases.
Download Oracle9i 9.2.0.1 here and apply patch no. 3095277 for Oracle9i 9.2.0.4.0

2) Oracle9iAS (9.0.3.0.0) Containers for J2EE (or higher) downloadable from here

3) JDK1.3.x or above This can be downloaded from here .

Notation	Description
<SAMPLE_HOME>	Folder where the Streams Messaging sample will be unzipped. For example, C:\StreamsMessagingSample
<ORACLE_HOME>	Folder where Oracle9i database has been installed. For example, D:\Oracle9204
<JAVA_HOME>	Folder where JDK has been installed. For example, C:\jdk1.3.1_06
<OC4J_HOME>	Folder where Oracle9iAS (9.0.3.0.0) Containers for J2EE has been installed. For example, D:\oc4j
<J2EE_HOME>	j2ee\home folder under <OC4J_HOME>. For example, D:\oc4j\j2ee\home
database 1	First database being used. This is the Manufacturers database
database 2	Second database being used. This refers to Retail Shops database.

This sample is provided as a jar file named StreamsMessagingSample.jar .Unjar the jar file using the following command:

> jar xvf StreamsMessagingSample.jar

This results in creation of StreamsMessagingSample directory containing sample files.

This sample assumes that no streams set up has been done on the databases being used. It does the Streams configuration in following steps :

1. Load XML Definition for LCRs
2. Create and Configure the Streams Environment.
3. Configure system parameters
4. Setup the Streams Messaging Application

Load XML Definitions for LCRs

The LCR schema must be loaded into the SYS schema using the catxlcr.sql script in <ORACLE_HOME>\rdbms\admin directory. Run this script now if it has not been run already. Login to SQL* Plus as SYS user and execute this script as follows:

SQL>@<ORACLE_HOME>\rdbms\admin\catxlcr.sql

Execute this script on each database that will be a part of Streams environment.

Create and Configure the Streams Environment

If the streams environment already exists then ignore this step and goto next step i.e Setup the Streams Messaging Application. The script StreamsAdminConfig.sql creates a Streams administrator and assigns necessary privileges to it. Login to SQL* Plus as any user and execute this script as follows:

SQL>@<SAMPLE_HOME>\scripts\StreamsAdminConfig.sql

Execute this script on each database that will be a part of Streams environment. Check spool file named streams_config.log for any errors. For more information on Configuring Streams Environment check the Configuring a Streams Environment chapter in Streams Developer Guide.

Configure system parameters

Some parameters needs to be set to run the StreamsMessaging Application. The name and the value of these parameters and the database in which they need to be set are given below:

 

Database	Parameter Name	Value
database 1	aq_tm_processes	>0
	global_names	TRUE
database 2	aq_tm_processes	>0
	global_names	TRUE

Some initialization parameters are needed to be set for the operation, reliability and performance of Streams environment. Refer documentation for setting up these parameters.

Setup the Streams Messaging Application

Start the database in archive log mode and then run Install.sql script. This script helps to set up the StreamsMessaging Application. It accepts the following parameters from the user for both database 1 and database 2:

1. TNS name
2. SYS password
3. User name and password of the Streams Administrator created in previous step.

The Install script creates the schemas, capture/ propagation/ apply processes required by the application.
Login to SQL*Plus as any user and execute the script Install.sql as follows and provide the above information when asked :

SQL>@<SAMPLE_HOME>\scripts\Install.sql

Check spool file StreamsMessage.log for any errors. Now the application is ready for testing.

Check the flow of the application here. Now we will see how to deploy and run the application using stand alone OC4J using the following steps:

1. Setting the required files for compilation.
2. Setting the OC4J libraries
3. Copying the sources to OC4J
4. Running the application

Setting the required files for compilation

Setting the OC4J libraries

Copying the sources to OC4J

Running the application

The flow of the application is shown here.

To remove the schemas, capture /apply /propagation processes used by the application, run the CleanUp.sql script. To execute the script, open new SQL* Plus prompt and enter the following command:

SQL>@<SAMPLE_HOME>\scripts\CleanUp.sql

Check the spool file CleanUp.log for any errors.

If the replication from source database to destination database is not taking place, login as Stream Administrator in database and follow the steps below to check what is going wrong :

1. Check for the status of capture and apply processes at source and destination database as follows :

    SELECT CAPTURE_NAME, STATUS FROM DBA_CAPTURE;

   If the capture process is disabled, start it using the following query:

   BEGIN
      DBMS_CAPTURE_ADM.START_CAPTURE(
       capture_name  => 'CAPTURE_PRODUCT_MASTER');
   END;
   /

   and try again. If capture process is aborted, check for the errors in the alert_SID.log file located at <ORACLE_HOME>\admin\sid\bdump\ directory of your Oracle installation.

2. If the capture process is enable check if it is capturing the changes as follows :

   SELECT TOTAL_MESSAGES_CAPTURED, TOTAL_MESSAGES_ENQUEUED FROM V$STREAMS_CAPTURE 
                WHERE CAPTURE_NAME= 'CAPTURE_PRODUCT_MASTER';

   If the results of query are non-zero, it means the changes are being captured. If results show zero values, check the alert_SID.log and trace files for details.

3. If the capture is happening properly, check if the propagation is set up correctly and the captured changes are being propagated to destination database as follows:

   Check if source and destination queues are specified correctly for the propagation process using following query :

   SELECT p.SOURCE_QUEUE_OWNER||'.'|| p.SOURCE_QUEUE_NAME||'@'||g.GLOBAL_NAME SOURCE_QUEUE, 
   p.DESTINATION_QUEUE_OWNER||'.'|| p.DESTINATION_QUEUE_NAME||'@'||p.DESTINATION_DBLINK DESTINATION_QUEUE 
   FROM DBA_PROPAGATION p, GLOBAL_NAME g WHERE p.PROPAGATION_NAME ='SITE1_TO_SITE2'

   If the source and destination queues are not correct, drop the propagation process using the following query:

   BEGIN
   	DBMS_PROPAGATION_ADM.DROP_PROPAGATION(propagation_name =>'SITE1_TO_SITE2');
   END;
   /		  
   

   and create a new one with correct source and destination queues in the following way:

   BEGIN
   	DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
   		table_name           => 'prodstore.product_master',
   		streams_name 	     => 'site1_to_site2',
   		source_queue_name    => 'strmadmin.streams_queue',
   		destination_queue_name =>
   		'strmadmin.streams_queue@'||:site2,
   		include_dml 	   => true,
   		include_ddl 	   => false,
   		source_database    => :site1
   	);
   END;
   /

   Check if the events and bytes are being propagated by the propagation process using following query :

   SELECT s.TOTAL_TIME, s.TOTAL_NUMBER, s.TOTAL_BYTES FROM DBA_QUEUE_SCHEDULES s, DBA_PROPAGATION p
   WHERE p.PROPAGATION_NAME =  'SITE1_TO_SITE2' AND p.DESTINATION_DBLINK = s.DESTINATION
   AND s.SCHEMA=p.SOURCE_QUEUE_OWNER  AND s.QNAME = p.SOURCE_QUEUE_NAME;

   You will get non-zero values if bytes are being propagated, else check if the propagation job is enabled and if there are any propagation errors using following query :

   SELECT p.DESTINATION_DBLINK,DECODE(s.SCHEDULE_DISABLED,'Y', 'Disabled','N', 'Enabled') SCHEDULE_DISABLED,
   s.PROCESS_NAME, s.FAILURES, s.LAST_ERROR_TIME, s.LAST_ERROR_MSG
   FROM DBA_QUEUE_SCHEDULES s, DBA_PROPAGATION p
   WHERE p.PROPAGATION_NAME = 'SITE1_TO_SITE2'
   AND p.DESTINATION_DBLINK = s.DESTINATION
   AND s.SCHEMA = p.SOURCE_QUEUE_OWNER
   AND s.QNAME = p.SOURCE_QUEUE_NAME;

4. If capture and propagation process are performing as expected, check the apply process at destination database. First check the status of apply process as follows :

   SELECT APPLY_NAME, STATUS FROM DBA_APPLY;

   If the apply process is disabled, start it. If it is aborted, check errors in alert_SID.log file and trace files. You can also check if any errors occurred when applying the changes as follows :

   SELECT ERROR_MESSAGE FROM DBA_APPLY_ERROR WHERE APPLY_NAME= 'APPLY_PRODUCT_MASTER';

   Correct any errors and start the apply process.
   For example if you get an ORA-26687 Instantiation SCN Not Set error, it means the instantiation SCN is not set on an object for which an apply process is attempting to apply changes. User SET_TABLE_INSTANTIATION_SCN procedure in DBMS_APPLY_ADM package to set instantiation SCN for corresponding table. Refer documentation for correcting other common errors.

   After correcting the error execute following procedure to re-execute the transactions associated with errors :

   EXECUTE DBMS_APPLY_ADM.EXECUTE_ALL_ERRORS(apply_name => 'APPLY_PRODUCT_MASTER');

5. If there are no errors in step 4 , check if messages are being dequeued properly at destination database as follows :

   SELECT TOTAL_MESSAGES_DEQUEUED FROM V$STREAMS_APPLY_READER WHERE APPLY_NAME ='APPLY_PRODUCT_MASTER';

   Check the total messages assigned versus total messages applied by the apply process as follows :

   SELECT TOTAL_RECEIVED, TOTAL_APPLIED, TOTAL_ERRORS FROM V$STREAMS_APPLY_COORDINATOR
   WHERE APPLY_NAME ='APPLY_PRODUCT_MASTER';

Refer documentation for more details on troubleshooting the streams environment.

Problem :

When you login to the Application you get an error as follows:

java.lang.NoSuchFieldError: envCharSetId

                          OR

java.lang.UnsatisfiedLinkError: no ocijdbc10 in java.library.path

Solution : Check if you followed the Step 4 mentioned in Deploying and Running the Application

Problem :

While creating the apply process if the user gets an error as follows:

DECLARE
*
ERROR at line 1:
ORA-26701: STREAMS process APPLY_PRODUCT_MASTER does not exist
ORA-06512: at "SYS.DBMS_STREAMS_ADM_UTL", line 1174
ORA-06512: at "SYS.DBMS_STREAMS_ADM", line 141
ORA-06512: at "SYS.DBMS_STREAMS_ADM", line 96
ORA-06512: at line 7

OR

while starting the apply process the user gets an error as follows:

BEGIN
*
ERROR at line 1:
ORA-26701: STREAMS process APPLY_PRODUCT_MASTER does not exist
ORA-06512: at "SYS.DBMS_LOGREP_UTIL", line 111
ORA-06512: at "SYS.DBMS_APPLY_ADM", line 28
ORA-06512: at line 2

Solution : Connect to Streams administrator of the database where the error is received and check if an apply process with this name (APPLY_PRODUCT_MASTER) already exists using the following query:

SELECT APPLY_NAME, STATUS FROM DBA_APPLY;

If it exists then drop the apply process using the following commands:

-- Remove all the rules associated with the apply process
BEGIN
DBMS_STREAMS_ADM.REMOVE_RULE(
rule_name => NULL,
streams_type => 'apply',
streams_name => 'APPLY_PRODUCT_MASTER');
END;
/

-- Stop the apply process.
BEGIN
DBMS_APPLY_ADM.STOP_APPLY(
apply_name => 'APPLY_PRODUCT_MASTER',
force => true);
END;
/

-- Drop the apply process.
BEGIN
DBMS_APPLY_ADM.DROP_APPLY(apply_name => 'APPLY_PRODUCT_MASTER');
END;
/

If an apply process with this name does not exist then create the apply process with a different name.

Problem :

While creating the propagation process if the user gets an error as follows:

BEGIN
*
ERROR at line 1:
ORA-23600: cannot create PROPAGATION, SITE1_TO_SITE2 already exists
ORA-06512: at "SYS.DBMS_STREAMS_ADM", line 492
ORA-06512: at "SYS.DBMS_STREAMS_ADM", line 431
ORA-06512: at line 2

Solution : Connect to Streams administrator of the database where the error is received and check if a propagation with this name (SITE1_TO_SITE2) already exists using the following query:

SELECT PROPAGATION_NAME, SOURCE_QUEUE_NAME, DESTINATION_QUEUE_NAME FROM DBA_PROPAGATION;

If it exists then drop the apply process using the following commands:

BEGIN
DBMS_PROPAGATION_ADM.DROP_PROPAGATION( propagation_name => 'site1_to_site2' );
END;
/

If a propagation with this name does not exist then create the propagation with a different name.

Problem :

There is an exception after you login to the application as Administrator or you are not getting the expected result

Solution : Open the properties file ConnForSecondDB.properties present in the folder <SAMPLE_HOME>. Change the value of the entry Debug to true. Copy the file to <J2EE_HOME>\default-web-app\WEB-INF\classes. Start OC4J server and login as Administrator. Now open the OC4J console to see where the problem lies.

Please enter your comments about this sample in the OTN Sample Code Discussion Forum