Transfer Files From One Server To Another With Oracle Streams

You can leverage Oracle Streams to transfer files stored on a file system from one server to another. Not only, those files can be images or documents, assuming they are multiple of 512 bytes, but they also can be Oracle files, like Datapump exports, backups or data files. As a matter of fact, to me, sharing files across servers is probably the #1 reason why you would want to use Streams user messages.

In this post, you'll find an easy to reproduce sample application. You'll create a User Define Type (UDT) with a BFILE (i.e. a file located outside the database on a file system or in ASM). Once done, you'll create an instance of that type, you'll wrap it in an ANYDATA type and will share it between databases with Streams. You'll figure out that, not only the message with a BFILE locator, but also the corresponding file will be transferred from the source database to the destination database.

This post is made of the following sections:

• Step 1: Configure Databases, Streams Administrators and Directories
• Step 2: Configure Directories and the User Defined Type
• Step 3: Create the Database Link and the Propagation Between the Queues
• Step 4: Create A Streams Message Consumer and a Dequeue Program
• Step 5: Test The Streams configuration is working
• Step 6: Create a File and Transfer It with Streams
• Step 7: Troubleshooting
• Step 8: Clean-up the environment

Step 1: Configure Databases and Streams Administrators

If you read this blog or the documentation, you've seen it dozens of time already! In order for Streams to work, you must setup a Streams Administrator, a queue and, though it's not mandatory, it's strongly recommended to set the global_names parameter of the two databases to TRUE. In this sample application, you'll assume we have 2 databases called BLACK, the source, and WHITE, the destination. You'll find below a table with the commands to run on the 2 databases to setup the Streams Administrator and the parameters:

On BLACK	On WHITE
select * from global_name; GLOBAL_NAME ----------- BLACK alter system set global_names=true;	select * from global_name; GLOBAL_NAME ----------- WHITE alter system set global_names=true;
connect / as sysdba CREATE TABLESPACE streams_tbs DATAFILE '/u01/app/oracle/oradata/BLACK/streams_tbs.dbf' SIZE 25M AUTOEXTEND ON MAXSIZE 256M; CREATE USER strmadmin IDENTIFIED BY strmadmin DEFAULT TABLESPACE streams_tbs QUOTA UNLIMITED ON streams_tbs; grant dba to strmadmin; BEGIN DBMS_STREAMS_ADM.SET_UP_QUEUE( queue_table => 'strmadmin.streams_queue_table', queue_name => 'strmadmin.streams_queue'); END; / begin dbms_streams_auth.grant_admin_privilege( grantee => 'strmadmin', grant_privileges => true); end; /	connect / as sysdba CREATE TABLESPACE streams_tbs DATAFILE '/u01/app/oracle/oradata/WHITE/streams_tbs.dbf' SIZE 25M AUTOEXTEND ON MAXSIZE 256M; CREATE USER strmadmin IDENTIFIED BY strmadmin DEFAULT TABLESPACE streams_tbs QUOTA UNLIMITED ON streams_tbs; grant dba to strmadmin; BEGIN DBMS_STREAMS_ADM.SET_UP_QUEUE( queue_table => 'strmadmin.streams_queue_table', queue_name => 'strmadmin.streams_queue'); END; / begin dbms_streams_auth.grant_admin_privilege( grantee => 'strmadmin', grant_privileges => true); end; /

Note:
Because we don't use any Streams capture process, those databases don't have to be in archivelog mode.

Step 2: Configure Directories and the User Defined Type

Once the Streams Administrator created, you'll create a directory with the same name on the 2 databases as well as a User Defined Type. To allow the instances associated with the 2 databases to run on the same server, the directory path for BLACK differs from the directory path for WHITE:

On BLACK	On WHITE
!mkdir -p /tmp/black create directory tmp_dir as '/tmp/black'; grant read, write, execute on directory tmp_dir to strmadmin;	!mkdir -p /tmp/white create directory tmp_dir as '/tmp/white'; grant read, write, execute on directory tmp_dir to strmadmin;
connect strmadmin/strmadmin create type demotyp as object (id number, myfile bfile); /	connect strmadmin/strmadmin create type demotyp as object (id number, myfile bfile); /

Step 3: Create the Database Link and the Propagation Between the Queues

Make sure you can add an alias WHITE to connect to the WHITE database in the Oracle*Net configuration of the BLACK database (e.g. in the tnsnames.ora file). Once done, you can create a database link from BLACK to WHITE and create a Propagation process to send messages from the source to the destination database. Connect to the BLACK and run the script below:

connect strmadmin/strmadmin

select * from GLOBAL_NAME;

GLOBAL_NAME
----------
BLACK

CREATE DATABASE LINK WHITE
  connect to STRMADMIN
  identified by strmadmin
  using 'WHITE';

BEGIN
  DBMS_PROPAGATION_ADM.CREATE_PROPAGATION(
    propagation_name   => 'bfile_propagation',
    source_queue       => 'strmadmin.streams_queue',
    destination_queue  => 'strmadmin.streams_queue',
    destination_dblink => 'white',
    rule_set_name      => null,
    queue_to_queue     => TRUE);
END;
/

Step 4: Create A Streams Message Consumer And A Dequeue Program

You can now configure the WHITE database so that a program of yours dequeues messages from its Streams queue. The script below creates a Streams DEQUEUE client and execute an anonymous PL/SQL block that receives and removes any messages (if ID>0):

connect strmadmin/strmadmin

begin
 DBMS_STREAMS_ADM.ADD_MESSAGE_RULE (
   message_type   => 'strmadmin.demotyp',
   rule_condition => ':MSG.ID > 0',
   streams_type   => 'DEQUEUE',
   streams_name   => 'demostrm',
   queue_name     => 'strmadmin.streams_queue');
end;
/

select streams_name,
      queue_owner||'.'||queue_name queue
 from DBA_STREAMS_MESSAGE_CONSUMERS;

set serveroutput on
declare
  v_demotyp demotyp;
  v_any     anydata;
  v_out     pls_integer;
begin
   dbms_streams_messaging.dequeue(
      queue_name   => 'strmadmin.streams_queue',
      streams_name => 'demostrm',
      payload      => v_any,
      dequeue_mode => 'REMOVE',
      navigation   => 'NEXT TRANSACTION',
      wait         => DBMS_STREAMS_MESSAGING.FOREVER);
   v_out := v_any.getobject(v_demotyp);
   dbms_output.put_line('Message Received: '||
                        to_char(v_demotyp.id));
   commit;
end;
/

Step 5: Test The Streams Configuration

You can now test your settings. In order to do it, enqueue a message on the BLACK side. For this first test, don't "attach" any file to the message; leave myfile to NULL:

connect strmadmin/strmadmin

declare
 v_demotyp demotyp:=demotyp(1, null);
 v_any     anydata;
begin
 v_any:=anydata.convertobject(v_demotyp);
 dbms_streams_messaging.enqueue(
   'strmadmin.streams_queue',
   v_any);
end;
/
commit;

The program running on the WHITE should display the following line and stop:

Message Received: 1

Step 6: Create a File and Transfer It with Streams

You'll perform the same test, but this time with a file attached. Restart the program on WHITE:

select * from GLOBAL_NAME; 

GLOBAL_NAME
----------
WHITE 

set serveroutput on

declare
  v_demotyp demotyp;
  v_any     anydata;
  v_out     pls_integer;
begin
  dbms_streams_messaging.dequeue(
     queue_name   => 'strmadmin.streams_queue',
     streams_name => 'demostrm',
     payload      => v_any,
     dequeue_mode => 'REMOVE',
     navigation   => 'NEXT TRANSACTION',
     wait         => DBMS_STREAMS_MESSAGING.FOREVER);
  v_out := v_any.getobject(v_demotyp);
  dbms_output.put_line('Message Received: '||
                       to_char(v_demotyp.id));
  commit;
end;
/

Create a file that is a multiple of 512 bytes in size on the BLACK side, allocate a BFILE that points to it and send it to WHITE with Streams:

connect strmadmin/strmadmin

select * from GLOBAL_NAME; 

GLOBAL_NAME
----------
BLACK

!dd if=/dev/zero of=/tmp/black/demo.zero bs=512 count=1 

declare
   v_demotyp demotyp:=demotyp(2, bfilename('tmp_dir','demo.zero')); 
   v_any     anydata;
begin 
   v_any:=anydata.convertobject(v_demotyp); 
   dbms_streams_messaging.enqueue(
      'strmadmin.streams_queue',
      v_any);
end;
/
commit;

The program you've restarted on WHITE should display something like:

Message Received: 2

And the file should now be present on the WHITE side too:

!ls -l /tmp/white
total 4
-rw-r----- 1 oracle oinstall 512 2009-08-24 00:25 demo.zero

Step 7: Troubleshooting

Troubleshooting this sample is beyond the scope of the post. However if the configuration is not working, that's very likely it's related to the propagation of the file (the file size, some privileges on the /tmp/white directory, etc). You can query DBA_PROPAGATION and the queue tables to know more about the status of your message. If there is any error at the propagation level, fix it and you restart the propagation:

exec dbms_propagation_adm.stop_propagation('bfile_propagation',true);
exec dbms_propagation_adm.start_propagation('bfile_propagation');

Step 8: Clean-up the environment

As always, leave the database as it was when you've first started. The scripts below removes all the components:

On BLACK

On WHITE

connect / as sysdba !rm /tmp/black/demo.zero begin dbms_propagation_adm.stop_propagation( 'bfile_propagation',true); dbms_propagation_adm.drop_propagation( 'bfile_propagation',true); end; / drop directory tmp_dir; begin DBMS_STREAMS_ADM.REMOVE_QUEUE( queue_name => 'strmadmin.streams_queue', cascade => true, drop_unused_queue_table=> true); end; / drop user strmadmin cascade; drop tablespace streams_tbs including contents and datafiles;

connect / as sysdba !rm /tmp/white/demo.zero col streams_name format a15 col queue format a40 select streams_name, queue_owner||'.'||queue_name queue from DBA_STREAMS_MESSAGE_CONSUMERS; begin for i in (select rule_owner, rule_name from DBA_STREAMS_RULES where STREAMS_TYPE='DEQUEUE' and STREAMS_NAME='DEMOSTRM') loop dbms_streams_adm.remove_rule( rule_name => i.rule_owner||'.'||i.rule_name, streams_type => 'DEQUEUE', streams_name => 'DEMOSTRM', drop_unused_rule => true); end loop; end; / col streams_name format a15 col queue format a40 select streams_name, queue_owner||'.'||queue_name queue from DBA_STREAMS_MESSAGE_CONSUMERS; drop directory tmp_dir; drop user strmadmin cascade; drop tablespace streams_tbs including contents and datafiles;

In the next post, we'll get a bit deeper in the troubleshooting part; we'll generate an error with the propagation and we'll see how to fix it. Stay tuned!