FAQ
we have ORACLE 10.2.0.4 with Redhat AS 4.4 on DELL server.  Recently we are test "UTL_SMTP" on ORACLE PL/SQL program and tried to send MAIL to customers.

The problem is this send MAIL program always one time work and second time not work (loop).  I don't think problem come from ORACLE bug or any ORACLE relate problem.  If I copy database and put on anyother server, it work correctly.

Does anyone have suggestion?

====== sample event happen  ===

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> @mailbody
31  .
SQL> /

Declare
*
ERROR at line 1:

ORA-29278: SMTP transient error: 421 Service not available
ORA-06512: at "SYS.UTL_SMTP", line 21
ORA-06512: at "SYS.UTL_SMTP", line 97
ORA-06512: at "SYS.UTL_SMTP", line 139
ORA-06512: at line 15

SQL> /

PL/SQL procedure successfully completed.


SQL> /

Declare
*
ERROR at line 1:

ORA-29278: SMTP transient error: 421 Service not available
ORA-06512: at "SYS.UTL_SMTP", line 21
ORA-06512: at "SYS.UTL_SMTP", line 97
ORA-06512: at "SYS.UTL_SMTP", line 139
ORA-06512: at line 15

Search Discussions

  • Pedro Espinoza at Jun 26, 2008 at 4:25 pm
    Is it too *difficult* for you to show the script mailbody.sql?

    Next, check your mail submission agent logs: grep mail /etc/syslog.conf

    On my box, i see something like thus on the smtp server which your
    pl/sql program is connecting to.

    # Log anything (except mail) of level info or higher.

    *.info;mail.none;authpriv.none;cron.none /var/log/messages
    # Log all the mail messages in one place.
    mail.* -/var/log/maillog

    Then, check /var/log/maillog for any errors related to sending mail.
    On Thu, Jun 26, 2008 at 11:56 AM, dba1 mcc wrote:
    we have ORACLE 10.2.0.4 with Redhat AS 4.4 on DELL server. Recently we are
    test "UTL_SMTP" on ORACLE PL/SQL program and tried to send MAIL to
    customers.

    The problem is this send MAIL program always one time work and second time
    not work (loop). I don't think problem come from ORACLE bug or any ORACLE
    relate problem. If I copy database and put on anyother server, it work
    correctly.

    Does anyone have suggestion?
    --
    http://www.freelists.org/webpage/oracle-l
  • Blanchard William at Jun 26, 2008 at 5:00 pm
    Hello everyone,

    The Oracle recommended strategy for backing up a 10g database is image
    copies with incremental updates. Is there something similar in 9i.
    What is the Oracle recommended strategy in 9i?

    Thank you,

    William
  • Jared Still at Jun 26, 2008 at 6:02 pm

    On Thu, Jun 26, 2008 at 10:00 AM, Blanchard William < William.Blanchard_at_kohler.com> wrote:

    The Oracle recommended strategy for backing up a 10g database is image
    copies with incremental updates. Is there something similar in 9i.
    What is the Oracle recommended strategy in 9i?
    Q: Do you have a URL for that recommended strategy?

    Q: Maybe Oracle recommends it, but does that mean you should use it.

    Unless you're maintaining images in the FRA and updating them, I'm not
    too sure I see the point in image copies. (It would be nice to have that
    much storage BTW)

    Maybe someone can enlighten me on that.

    Otherwise, why not just determine what works best for your company?

    Do you seem some particular advantage for image copies as opposed
    to backup sets with incrementals?

    --
    Jared Still
    Certifiable Oracle DBA and Part Time Perl Evangelist

    --
    http://www.freelists.org/webpage/oracle-l
  • Blanchard William at Jun 26, 2008 at 6:04 pm
    I don't see the need for image copies either but I need to break the old
    habits slowly.


    William

    From: Jared Still
    Sent: Thursday, June 26, 2008 1:03 PM
    To: Blanchard William
    Cc: oracle-l_at_freelists.org
    Subject: Re: Oracle 9i RMAN

    On Thu, Jun 26, 2008 at 10:00 AM, Blanchard William
    wrote:

    The Oracle recommended strategy for backing up a 10g database is
    image

    copies with incremental updates. Is there something similar in
    9i.

    What is the Oracle recommended strategy in 9i?


    Q: Do you have a URL for that recommended strategy?

    Q: Maybe Oracle recommends it, but does that mean you should use it.

    Unless you're maintaining images in the FRA and updating them, I'm not
    too sure I see the point in image copies. (It would be nice to have that

    much storage BTW)

    Maybe someone can enlighten me on that.

    Otherwise, why not just determine what works best for your company?

    Do you seem some particular advantage for image copies as opposed
    to backup sets with incrementals?

    --
    Jared Still
    Certifiable Oracle DBA and Part Time Perl Evangelist

    --
    http://www.freelists.org/webpage/oracle-l
  • Jared Still at Jun 26, 2008 at 4:56 pm
    More information required:

    PL/SQL code

    Q: Are you using the sendmail on the local server?
    A: No, then talk to your mail admin
    A: Yes, then ensure sendmail ( or whatever mail daemon) has been setup

    properly.

    Q: can you reliably send mail from the command line using mail, mailx, etc?
    On Thu, Jun 26, 2008 at 8:56 AM, dba1 mcc wrote:

    we have ORACLE 10.2.0.4 with Redhat AS 4.4 on DELL server. Recently we
    are test "UTL_SMTP" on ORACLE PL/SQL program and tried to send MAIL to
    customers.

    The problem is this send MAIL program always one time work and second time
    not work (loop). I don't think problem come from ORACLE bug or any ORACLE
    relate problem. If I copy database and put on anyother server, it work
    correctly.

    Does anyone have suggestion?

    ====== sample event happen ===

    Connected to:
    Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
    With the Partitioning, OLAP and Data Mining options
    *SQL> @mailbody
    31 .
    SQL> /
    Declare
    *
    ERROR at line 1:
    ORA-29278: SMTP transient error: 421 Service not available
    ORA-06512: at "SYS.UTL_SMTP", line 21
    ORA-06512: at "SYS.UTL_SMTP", line 97
    ORA-06512: at "SYS.UTL_SMTP", line 139
    ORA-06512: at line 15*

    *SQL> /*
    *PL/SQL procedure successfully completed.*
    **

    *SQL> /
    Declare
    *
    ERROR at line 1:
    ORA-29278: SMTP transient error: 421 Service not available
    ORA-06512: at "SYS.UTL_SMTP", line 21
    ORA-06512: at "SYS.UTL_SMTP", line 97
    ORA-06512: at "SYS.UTL_SMTP", line 139
    ORA-06512: at line 15*
    --
    Jared Still
    Certifiable Oracle DBA and Part Time Perl Evangelist

    --
    http://www.freelists.org/webpage/oracle-l
  • Robert Freeman at Jun 26, 2008 at 5:38 pm
    Where did you see this particular recommendation from Oracle as *the* Oracle recommended way to backup your database?

    RF

    Robert G. Freeman
    Author:
    Oracle Database 11g New Features (Oracle Press)
    Portable DBA: Oracle (Oracle Press)
    Oracle Database 10g New Features (Oracle Press)
    Oracle9i RMAN Backup and Recovery (Oracle Press)
    Oracle9i New Feature
    Blog: http://robertgfreeman.blogspot.com (Oracle Press)
    The LDS Church is looking for DBA's. You must be LDS to apply (please don't write to me and tell me I'm breaking the law. A church can choose to hire members of it's own faith. Look it up if you don't believe me).

    Original Message ----
    From: Blanchard William
    To: oracle-l_at_freelists.org
    Sent: Thursday, June 26, 2008 11:00:14 AM
    Subject: Oracle 9i RMAN

    Hello everyone,

    The Oracle recommended strategy for backing up a 10g database is image
    copies with incremental updates. Is there something similar in 9i.
    What is the Oracle recommended strategy in 9i?

    Thank you,

    William
  • Blanchard William at Jun 26, 2008 at 5:51 pm
    In EM, go to Maintenance -> Schedule Backup -> Schedule Oracle-Suggested
    Backup

    Daily Script:
    run {
    allocate channel oem_disk_backup device type disk;
    recover copy of database with tag 'ORA$OEM_LEVEL_0';
    backup incremental level 1 cumulative copies=1 for recover of copy with
    tag 'ORA$OEM_LEVEL_0' database;
    }



    -----Original Message-----
    From: Robert Freeman
    Sent: Thursday, June 26, 2008 12:38 PM
    To: Blanchard William; oracle-l_at_freelists.org
    Subject: Re: Oracle 9i RMAN

    Where did you see this particular recommendation from Oracle as *the*
    Oracle recommended way to backup your database?

    RF

    Robert G. Freeman
    Author:
    Oracle Database 11g New Features (Oracle Press) Portable DBA: Oracle
    (Oracle Press) Oracle Database 10g New Features (Oracle Press) Oracle9i
    RMAN Backup and Recovery (Oracle Press) Oracle9i New Feature
    Blog: http://robertgfreeman.blogspot.com (Oracle Press) The LDS Church
    is looking for DBA's. You must be LDS to apply (please don't write to me
    and tell me I'm breaking the law. A church can choose to hire members of
    it's own faith. Look it up if you don't believe me).

    Original Message ----
    From: Blanchard William
    To: oracle-l_at_freelists.org
    Sent: Thursday, June 26, 2008 11:00:14 AM
    Subject: Oracle 9i RMAN

    Hello everyone,

    The Oracle recommended strategy for backing up a 10g database is image
    copies with incremental updates. Is there something similar in 9i.
    What is the Oracle recommended strategy in 9i?

    Thank you,

    William
    --
    http://www.freelists.org/webpage/oracle-l
    --
    http://www.freelists.org/webpage/oracle-l
  • Robert Freeman at Jun 26, 2008 at 5:44 pm
    It occurred to me that maybe you were basing this on the OEM suggested backup strategy. One major sort coming to this strategy is that it requires significant amounts of space (the image copies are not compressed). Personally, unless I need very fast recovery, I tend to shy away from this due to the size of the backup image. If I have a need for fast recoveries, then one can often justify stand-by database in those cases.

    I'd prefer a compressed backupset myself, with incrementals.

    RF

    Robert G. Freeman
    Author:
    Oracle Database 11g New Features (Oracle Press)
    Portable DBA: Oracle (Oracle Press)
    Oracle Database 10g New Features (Oracle Press)
    Oracle9i RMAN Backup and Recovery (Oracle Press)
    Oracle9i New Feature
    Blog: http://robertgfreeman.blogspot.com (Oracle Press)
    The LDS Church is looking for DBA's. You must be LDS to apply (please don't write to me and tell me I'm breaking the law. A church can choose to hire members of it's own faith. Look it up if you don't believe me).

    Original Message ----
    From: Blanchard William
    To: oracle-l_at_freelists.org
    Sent: Thursday, June 26, 2008 11:00:14 AM
    Subject: Oracle 9i RMAN

    Hello everyone,

    The Oracle recommended strategy for backing up a 10g database is image
    copies with incremental updates. Is there something similar in 9i.
    What is the Oracle recommended strategy in 9i?

    Thank you,

    William
  • Blanchard William at Jun 26, 2008 at 5:54 pm
    I would much rather use an incremental strategy with compressed
    backupsets. However, I'm limited by what the other DBAs will agree to
    (we currently use cold and hot user-managed backups).

    William

    -----Original Message-----
    From: Robert Freeman
    Sent: Thursday, June 26, 2008 12:44 PM
    To: Blanchard William; oracle-l_at_freelists.org
    Subject: Re: Oracle 9i RMAN

    It occurred to me that maybe you were basing this on the OEM suggested
    backup strategy. One major sort coming to this strategy is that it
    requires significant amounts of space (the image copies are not
    compressed). Personally, unless I need very fast recovery, I tend to shy
    away from this due to the size of the backup image. If I have a need for
    fast recoveries, then one can often justify stand-by database in those
    cases.

    I'd prefer a compressed backupset myself, with incrementals.

    RF

    Robert G. Freeman
    Author:
    Oracle Database 11g New Features (Oracle Press) Portable DBA: Oracle
    (Oracle Press) Oracle Database 10g New Features (Oracle Press) Oracle9i
    RMAN Backup and Recovery (Oracle Press) Oracle9i New Feature
    Blog: http://robertgfreeman.blogspot.com (Oracle Press) The LDS Church
    is looking for DBA's. You must be LDS to apply (please don't write to me
    and tell me I'm breaking the law. A church can choose to hire members of
    it's own faith. Look it up if you don't believe me).

    Original Message ----
    From: Blanchard William
    To: oracle-l_at_freelists.org
    Sent: Thursday, June 26, 2008 11:00:14 AM
    Subject: Oracle 9i RMAN

    Hello everyone,

    The Oracle recommended strategy for backing up a 10g database is image
    copies with incremental updates. Is there something similar in 9i.
    What is the Oracle recommended strategy in 9i?

    Thank you,

    William

    --
    http://www.freelists.org/webpage/oracle-l
    --
    http://www.freelists.org/webpage/oracle-l
  • Jared Still at Jun 26, 2008 at 9:09 pm

    On Thu, Jun 26, 2008 at 10:54 AM, Blanchard William < William.Blanchard_at_kohler.com> wrote:

    I would much rather use an incremental strategy with compressed
    backupsets. However, I'm limited by what the other DBAs will agree to
    (we currently use cold and hot user-managed backups).
    Interesting. What is the justification for cold backups?

    --
    Jared Still
    Certifiable Oracle DBA and Part Time Perl Evangelist

    --
    http://www.freelists.org/webpage/oracle-l
  • Blanchard William at Jun 26, 2008 at 9:10 pm
    Just the way they've always done it.

    From: Jared Still
    Sent: Thursday, June 26, 2008 4:10 PM
    To: Blanchard William
    Cc: Robert Freeman; oracle-l_at_freelists.org
    Subject: Re: Oracle 9i RMAN

    On Thu, Jun 26, 2008 at 10:54 AM, Blanchard William
    wrote:

    I would much rather use an incremental strategy with compressed
    backupsets. However, I'm limited by what the other DBAs will
    agree to
    (we currently use cold and hot user-managed backups).

    Interesting. What is the justification for cold backups?

    --
    Jared Still
    Certifiable Oracle DBA and Part Time Perl Evangelist

    --
    http://www.freelists.org/webpage/oracle-l
  • Jared Still at Jun 26, 2008 at 10:05 pm

    On Thu, Jun 26, 2008 at 2:10 PM, Blanchard William < William.Blanchard_at_kohler.com> wrote:

    Just the way they've always done it.

    ------------------------------
    Now there's a topic that has raged across the pages of this
    forum a few times.

    To be or not to be, should a database ever be backed up cold?

    My vote is "almost never"

    Prior to 10g, a resetlogs required a cold backup.

    Well, not actually required, but if anyone did any work on the newly
    opened database, and the database crashed before a backup was
    completed, there was a very good chance of losing that work.

    10g simplified recovering a database through a resetlogs ( I haven't tried
    it)
    but I would still do a cold backup on it.

    Sometime "the way we've always done it" needs to be examined in the
    light of "is this how we should still be doing it?" .

    That reminds me of the story of the young lady that purchased a roast
    from the meat department at the grocery store, and asked the butcher
    to cut in half.

    Why? Because that's how her mother had always done it.

    When her mother learned of this she told her daughter that she had always
    asked for it to be cut in half because simplyu she didn't have a pan large
    enough to cook the whole thing.

    "But that's the way we always did it" :)

    --
    Jared Still
    Certifiable Oracle DBA and Part Time Perl Evangelist

    --
    http://www.freelists.org/webpage/oracle-l
  • Blanchard William at Jun 26, 2008 at 10:13 pm
    Well said.

    From: Jared Still
    Sent: Thursday, June 26, 2008 5:05 PM
    To: Blanchard William
    Cc: Robert Freeman; oracle-l_at_freelists.org
    Subject: Re: Oracle 9i RMAN

    On Thu, Jun 26, 2008 at 2:10 PM, Blanchard William
    wrote:

    Just the way they've always done it.

    Now there's a topic that has raged across the pages of this
    forum a few times.

    To be or not to be, should a database ever be backed up cold?

    My vote is "almost never"

    Prior to 10g, a resetlogs required a cold backup.

    Well, not actually required, but if anyone did any work on the newly
    opened database, and the database crashed before a backup was
    completed, there was a very good chance of losing that work.

    10g simplified recovering a database through a resetlogs ( I haven't
    tried it)
    but I would still do a cold backup on it.

    Sometime "the way we've always done it" needs to be examined in the
    light of "is this how we should still be doing it?" .

    That reminds me of the story of the young lady that purchased a roast
    from the meat department at the grocery store, and asked the butcher
    to cut in half.

    Why? Because that's how her mother had always done it.

    When her mother learned of this she told her daughter that she had
    always
    asked for it to be cut in half because simplyu she didn't have a pan
    large
    enough to cook the whole thing.

    "But that's the way we always did it" :)

    --
    Jared Still
    Certifiable Oracle DBA and Part Time Perl Evangelist

    --
    http://www.freelists.org/webpage/oracle-l
  • Tony Aponte at Jun 26, 2008 at 6:21 pm
    I had a similar experience and it was due to the load on the local
    server, which was also acting as the mail server. The mail daemon was
    configured to "back off" when the load average reached a predefined
    threshold. That prevented the UTL_SMTP calls from completing which got
    propagated to the PL/SQL package.



    Tony Aponte



    From: oracle-l-bounce_at_freelists.org
    On Behalf Of dba1 mcc
    Sent: Thursday, June 26, 2008 11:57 AM
    To: oracle-l_at_freelists.org
    Subject: UTL_SMTP send mail wield problem????



    we have ORACLE 10.2.0.4 with Redhat AS 4.4 on DELL server. Recently we
    are test "UTL_SMTP" on ORACLE PL/SQL program and tried to send MAIL to
    customers.



    The problem is this send MAIL program always one time work and second
    time not work (loop). I don't think problem come from ORACLE bug or any
    ORACLE relate problem. If I copy database and put on anyother server,
    it work correctly.



    Does anyone have suggestion?



    sample event happen ===

    Connected to:
    Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
    With the Partitioning, OLAP and Data Mining options

    SQL> @mailbody
    31 .
    SQL> /

    Declare
    *
    ERROR at line 1:

    ORA-29278: SMTP transient error: 421 Service not available
    ORA-06512: at "SYS.UTL_SMTP", line 21
    ORA-06512: at "SYS.UTL_SMTP", line 97
    ORA-06512: at "SYS.UTL_SMTP", line 139
    ORA-06512: at line 15

    SQL> /

    PL/SQL procedure successfully completed.





    SQL> /

    Declare
    *
    ERROR at line 1:

    ORA-29278: SMTP transient error: 421 Service not available
    ORA-06512: at "SYS.UTL_SMTP", line 21
    ORA-06512: at "SYS.UTL_SMTP", line 97
    ORA-06512: at "SYS.UTL_SMTP", line 139
    ORA-06512: at line 15

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedJun 26, '08 at 3:56p
activeJun 26, '08 at 10:13p
posts15
users6
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase