Bookmark and Share

Sunday, August 23, 2009

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 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 BLACKOn 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 BLACKOn 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 BLACKOn 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!

No comments:

Post a Comment