Home » Oracle DBA » Keeping Propagation Always Enabled In Oracle Streams

Keeping Propagation Always Enabled In Oracle Streams

In this post I will describe to how to keep enabled Oracle Streams Propagation service for the target database by creating a Schedule Job and procedure.
The Propagation is service that transfers the new changes from the source database to target database, if this service get stopped then no data changes will apply to the target database. I already explained in my previous post to how to create Oracle Streams Replication.
1. First login into database with Streams admin credentials.
CONNECT [SOURCE_STREAM_ADMIN]
2. Then create the table to maintain the propagation log, so that you can view when the propagation service was stopped and when it get started again.
Create Table Propagation_Audit (
Propagation_name Varchar2(30)
,Source_queue_name Varchar2(30)
,Destination_queue_name Varchar2(30)
,Destination_dblink Varchar2(128)
,Status Varchar2(8)
,Error_message Varchar2(4000)
,Error_date Date
)
/
3. Then create a procedure to log the disable status and re-enabled it.
Create Or Replace PROCEDURE Ckprop_Enable
As
   Errnum   Number;
   Errmsg   Varchar2 (4000);
   CURSOR Prop_status
   Is
      Select Propagation_name, Destination_dblink, Status, Source_queue_name,
             Destination_queue_name, Error_message, Error_date
        From DBA_PROPAGATION
       Where Status != 'ENABLED';
BEGIN
   For Rec In Prop_status
   LOOP
      Insert Into Propagation_Audit
           Values (Rec.Propagation_name, Rec.Source_queue_name, Rec.Destination_queue_name,
                   Rec.Destination_dblink, Rec.Status, Rec.Error_message, Rec.Error_date);
      COMMIT;
      BEGIN
         Dbms_Aqadm.enable_Propagation_Schedule (Rec.Source_queue_name, Rec.Destination_dblink);
      EXCEPTION
         When Others
         Then
            Errnum := SQLCODE;
            Errmsg := SQLERRM;
            Insert Into Propagation_Audit
                 Values ('CKPROP_ENABLE', Rec.Source_queue_name, 're-enable propagation for',
                         Rec.Destination_dblink, 'ERROR', Errnum || ': ' || Errmsg, Sysdate);
            COMMIT;
      END;
   END LOOP;
EXCEPTION
   When Others
   Then
      Errnum := SQLCODE;
      Errmsg := SQLERRM;
      Insert Into Propagation_Audit
           Values ('CKPROP_ENABLE', 'Exception handler', Null, Null, 'ERROR',
                   Errnum || ': ' || Errmsg, Sysdate);
      COMMIT;
END Ckprop_Enable;
/
4. Then create a scheduled job to check at specified interval.
Exec DBMS_SCHEDULER.CREATE_JOB (

job_name => 'propagation_check', 
job_type => 'STORED_PROCEDURE', 
job_action => 'ckprop_enable', 
number_of_arguments => 0, 
start_date =>Sysdate, 
repeat_interval => 'FREQ=MINUTELY;INTERVAL=5', 
end_date => Null, 
enabled => TRUE, 
auto_drop=>FALSE, 
comments => 'EVERY 15 MIN');
Its done.