Home » Server Options » Replication » Sending email to administrator when Refresh fails (oracle 10g)
Sending email to administrator when Refresh fails [message #399423] Wed, 22 April 2009 02:56 Go to next message
summoner
Messages: 44
Registered: March 2009
Member
Dear all,

I have a refresh group that will refresh several materialized views at 4:00am.
Sometimes the job failed. We need to enter to the enterprise manager to check the status of the refresh group.

Our target is that notification email is sent to our email once the refresh job failed. I have found some software that can help us to do that. However, we need to handle ourselves. I would like to ask whether we can do that using enterprise manager.

If not, then we may need to implement program ourselves.

Thanks.
Re: Sending email to administrator when Refresh fails [message #399428 is a reply to message #399423] Wed, 22 April 2009 03:06 Go to previous messageGo to next message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member


If you want notification mail; you need to write trigger in dba_repcatalog or deferr or deftrandest.

Also implement utl_smtp.

Babu
Re: Sending email to administrator when Refresh fails [message #399603 is a reply to message #399428] Thu, 23 April 2009 02:22 Go to previous messageGo to next message
summoner
Messages: 44
Registered: March 2009
Member
Thank you for your help

Is that a record with 'ERROR' will be inserted into dba_repcatalog once an error occur? Will the system retry several times and then delete the record once the retry succeed?

So I need to create a trigger after an ERROR record is inserted into the table. In the trigger, I use utl_smtp to send an email to corresponding users.

Actually I have a dll file which is used in our VB.net applications. There is a function that will insert records in our logging system and send email to users. Is it possible to call that dll to handle this case?

[Updated on: Thu, 23 April 2009 02:24]

Report message to a moderator

Re: Sending email to administrator when Refresh fails [message #399654 is a reply to message #399603] Thu, 23 April 2009 05:39 Go to previous messageGo to next message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member

Oaky,

Could you explain me how your transfering data in replication??

Your using any jobs? Or manual transfer ??

Babu
Re: Sending email to administrator when Refresh fails [message #399765 is a reply to message #399654] Thu, 23 April 2009 22:41 Go to previous messageGo to next message
summoner
Messages: 44
Registered: March 2009
Member
We have not used any jobs
Here is the steps we have taken:

1. we create a DB link to the source database called DB01
2. We create a materialized view MV01 in enterprise manager, select all data from a table MYTABLE in DB01
3. We create a refresh group called RG_INCR that refresh MV01 everyday


Now we want to do the following:
1. If the schedule is run successfully, we call a function in that dll file. That function will mark the record in a database
2. If the schedule is run unsuccessfully, we call a function in that dll file. That function will mark the record in a database and also send an email to notify the administrator

Re: Sending email to administrator when Refresh fails [message #399779 is a reply to message #399765] Fri, 24 April 2009 01:14 Go to previous messageGo to next message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member

Quote:
1. we create a DB link to the source database called DB01
2. We create a materialized view MV01 in enterprise manager, select all data from a table MYTABLE in DB01
3. We create a refresh group called RG_INCR that refresh MV01 everyday


Looks like your donig basic replication;

>>We create a refresh group called RG_INCR that refresh MV01 everyday

I thing using dba_jobs only refresh happen. CHeck your jobs and confirm me same.

Babu
Re: Sending email to administrator when Refresh fails [message #400439 is a reply to message #399779] Tue, 28 April 2009 04:03 Go to previous messageGo to next message
summoner
Messages: 44
Registered: March 2009
Member
Quote:
I thing using dba_jobs only refresh happen. CHeck your jobs and confirm me same.
Babu


Yes, there is a job in dba_jobs that run the refresh
Re: Sending email to administrator when Refresh fails [message #400566 is a reply to message #400439] Tue, 28 April 2009 18:13 Go to previous message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member


Sorry for delay response.

Then try to write another job; Once successfully completed your refresh job cal your dll

Smile

Babu
Previous Topic: Database Synchronisation.
Next Topic: Oracle migration
Goto Forum:
  


Current Time: Thu Mar 28 07:30:41 CDT 2024