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.