This discussion is archived
5 Replies Latest reply: Oct 1, 2013 8:42 AM by jgarry RSS

Oracle 11g duplicate emais

1177ee15-ec90-4b7c-aa60-354ce2a7c216 Newbie
Currently Being Moderated

Hi,

 

We are using Oracle Database 11g Enterprise Edition 11.1.0.7.0, using utl_smtp package we are sending emails to users. From Aug'13 last week we have been receiving duplicate emails, before this we had not been getting duplicate emails.

 

We have checked with DBA,SMPT server, Emails server teams,

Email server team is daying, they are getting two hits from SMPT.

SMPT team saying they are also getting two hits from application(DB).

 

We did not do any code changes recently and we are hitting smpt server one time only

 

Could any one help me on this issue? Where we have to check about this issue?

 

Thanks in advance

 

Regards,

Chary

  • 1. Re: Oracle 11g duplicate emais
    Justin Cave Oracle ACE
    Currently Being Moderated

    Are you sending emails from triggers?  Are you sending emails from within transactions?  You haven't given us much to go on but I would suspect that you've embedded a non-transactional process (sending email) within a transaction that is getting rolled back and re-executed (relatively common, for example, in triggers that need to maintain write consistency).

     

    Justin

  • 2. Re: Oracle 11g duplicate emais
    sb92075 Guru
    Currently Being Moderated

    Contrary to your claims, obviously something changed since results are different.

  • 3. Re: Oracle 11g duplicate emais
    jgarry Guru
    Currently Being Moderated

    A couple of possibilities: smtp - What might cause a user to receive duplicate emails? - Server Fault

     

    That makes me wonder if some slow processing is just barely timing out due to some unrelated new additional load on your network.  But more likely, a user screwed up an alias or forward.

  • 4. Re: Oracle 11g duplicate emais
    1177ee15-ec90-4b7c-aa60-354ce2a7c216 Newbie
    Currently Being Moderated

    Hi All,

     

    Thanks for your responce.

     

    We are not using triggers. We wrote SMPT code in procedure and calling where ever we needed (procedures,packages).

     

    We are prefixing emails subject with environment(DEV, QA, PROD) using below query and hiting SMPT.

                SELECT ATRBT_NM INTO v_instance_name

                         FROM PITSTBT_LOOKUP_VALUE

                        WHERE LKP_ID =(SELECT LKP_ID FROM PITSTBT_LOOKUP_MASTER

                                      WHERE LKP_NM= 'PIT_ENVIRONMENT_VARIABLES')

                          AND ATRBT_VAL = (SELECT ora_database_name from dual)

                          AND actv_ind ='Y';

     

    We have written code like, user has to receive only one mails with or without prefix.

     

    But from Aug'13 we are receving one mail with prefix other one without prefix and this is also not happening everytime.

     

    on 27th sep we received below two emails

    : P And L Ageing Metrics Report

    PROD : P And L Ageing Metrics Report

     

    Today(1st Oct) we received one emil only

    PROD : P And L Ageing Metrics Report

     

    Regards,

    Ananth

  • 5. Re: Oracle 11g duplicate emais
    jgarry Guru
    Currently Being Moderated

    I suspect the problem must be how you are loading up PITSTBT_LOOKUP_MASTER.  You must have a situation where ATRBT_NM is null.  Are there temporary tables or views involved?  Or could it be possible that ora_database_name doesn't work right sometimes?  Or maybe there are no activ_ind='Y' sometimes, and you have code that calls the query again on that error?  Could there be several packages that interact, one of which was changed some time before the problem arose, but some of them were pinned by use in the shared pool while others let go?

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points