Not long time ago, I came across a usage of dbms_alert to manage running jobs. As the solution implemented must work also for RAC, I wanted to know whether dbms_alert works on RAC across instances.
The documentation nor Metalink does not say anything (contrary to dbms_pipe, which does NOT work on RAC). So, if they don't warn, it should work... However, Julian Dyke says, that dbms_alert does not work and is the same as dbms_pipe (sources: http://juliandyke.com/Presentations/Presentations.html#ARoughGuideToRAC, page 17, or Pro Oracle Database 10g RAC on Linux, page 426).
You know, never trust anybody, so I conducted a test case (10.2.0.3 on Linux x86_64, VMware ESX server, 2-node RAC):
You will need two simultaneous sessions, I mark them with DWH1> and DWH2> here.
So, as you see, it works even on RAC. Just test it yourself, and never trust anybody anything without proper testing. Everyone does mistakes.
The documentation nor Metalink does not say anything (contrary to dbms_pipe, which does NOT work on RAC). So, if they don't warn, it should work... However, Julian Dyke says, that dbms_alert does not work and is the same as dbms_pipe (sources: http://juliandyke.com/Presentations/Presentations.html#ARoughGuideToRAC, page 17, or Pro Oracle Database 10g RAC on Linux, page 426).
You know, never trust anybody, so I conducted a test case (10.2.0.3 on Linux x86_64, VMware ESX server, 2-node RAC):
You will need two simultaneous sessions, I mark them with DWH1> and DWH2> here.
DWH1> select instance_name from v$instance;
INSTANCE_NAME
----------------
DWH1
DWH2> select instance_name from v$instance;
INSTANCE_NAME
----------------
DWH2
DWH2> exec dbms_alert.register('TST');
DWH2> set serveroutput on
DWH2> declare
message varchar2(2000);
status integer;
name varchar2(2000);
begin
DBMS_ALERT.WAITANY (
name ,
message,
status);
dbms_output.put_line(name);
dbms_output.put_line(message);
end;
/
DWH1> exec dbms_alert.signal('TST','msg1');
DWH2> TST
msg1
PL/SQL procedure successfully completed.
DWH2> declare
message varchar2(2000);
status integer;
begin
DBMS_ALERT.WAITONE (
'TST',
message,
status );
dbms_output.put_line(message);
end;
/
DWH1> exec dbms_alert.signal('TST','msg1');
msg1
PL/SQL procedure successfully completed.
So, as you see, it works even on RAC. Just test it yourself, and never trust anybody anything without proper testing. Everyone does mistakes.
Comments