ORA-29278 When Trying to Use SMTP with GMail

Hi all,
Some time ago I got reached by a client with this error to send SMTP emails using GMail service with a wallet.
This is a generic error, in general the string identified the error returned from email service layer.

# Generic:

ORA-29278: SMTP transient error: string

# Error I was facing:

ORA-29278: SMTP transient error: 421 Service not available

Basically it was related to some intermittence on GMail’s service and got back to normal after a while. This is also well documented in this post by Hâvard Kristiansen.
However, I couldn’t use scripts there because of my wallet. Then I found the very useful commands below, that is what I want to share with you:

# Checking for SSL Certificate:

[oracle@grepora-srvr]$ openssl s_client -crlf -quiet -connect smtp.gmail.com:587 -starttls smtp
depth=3 C = US, O = Equifax, OU = Equifax Secure Certificate Authority
verify return:1
depth=2 C = US, O = GeoTrust Inc., CN = GeoTrust Global CA
verify return:1
depth=1 C = US, O = Google Inc, CN = Google Internet Authority G2
verify return:1
depth=0 C = US, ST = California, L = Mountain View, O = Google Inc, CN = smtp.gmail.com
verify return:1
250 SMTPUTF8
^C
(return:1 means "passed".)

# Testing SMTP on Database:
DECLARE
  k_host            CONSTANT VARCHAR2(100) := 'smtp.gmail.com';
  k_port            CONSTANT INTEGER       := 587;
  k_wallet_path     CONSTANT VARCHAR2(100) := 'file:/u01/app/oracle/admin/grepora/wallet/email';
  k_wallet_password CONSTANT VARCHAR2(100) := '****';
  k_domain          CONSTANT VARCHAR2(100) := 'grepora.com'; 
  k_username        CONSTANT VARCHAR2(100) := 'suporte@grepora.com';
  k_password        CONSTANT VARCHAR2(100) := 'mysecurepass';
  k_sender          CONSTANT VARCHAR2(100) := 'suporte@grepora.com';
  k_recipient       CONSTANT VARCHAR2(100) := 'suporte@grepora.com';
  k_subject         CONSTANT VARCHAR2(100) := 'Test TLS mail';
  k_body            CONSTANT VARCHAR2(100) := 'Message body';
  l_conn    utl_smtp.connection;
  l_reply   utl_smtp.reply;
  l_replies utl_smtp.replies;
  BEGIN
    dbms_output.put_line('utl_smtp.open_connection');

    l_reply := utl_smtp.open_connection
               ( host                          => k_host
               , port                          => k_port
               , c                             => l_conn
               , wallet_path                   => k_wallet_path
               , wallet_password               => k_wallet_password
               , secure_connection_before_smtp => FALSE
               );

    IF l_reply.code != 220
    THEN
      raise_application_error(-20000, 'utl_smtp.open_connection: '||l_reply.code||' - '||l_reply.text);
    END IF;

    dbms_output.put_line('utl_smtp.ehlo');

    l_replies := utl_smtp.ehlo(l_conn, k_domain);

    FOR ri IN 1..l_replies.COUNT
    LOOP
      dbms_output.put_line(l_replies(ri).code||' - '||l_replies(ri).text);
    END LOOP;

    dbms_output.put_line('utl_smtp.starttls');

    l_reply := utl_smtp.starttls(l_conn);

    IF l_reply.code != 220
    THEN
      raise_application_error(-20000, 'utl_smtp.starttls: '||l_reply.code||' - '||l_reply.text);
    END IF;

    dbms_output.put_line('utl_smtp.ehlo');

    l_replies := utl_smtp.ehlo(l_conn, k_domain);

    FOR ri IN 1..l_replies.COUNT
    LOOP
      dbms_output.put_line(l_replies(ri).code||' - '||l_replies(ri).text);
    END LOOP;

    dbms_output.put_line('utl_smtp.auth');

    l_reply := utl_smtp.auth(l_conn, k_username, k_password, 'PLAIN');

    IF l_reply.code != 235
    THEN
      raise_application_error(-20000, 'utl_smtp.auth: '||l_reply.code||' - '||l_reply.text);
    END IF;

    dbms_output.put_line('utl_smtp.mail');

    l_reply := utl_smtp.mail(l_conn, k_sender);

    IF l_reply.code != 250
    THEN
      raise_application_error(-20000, 'utl_smtp.mail: '||l_reply.code||' - '||l_reply.text);
    END IF;

    dbms_output.put_line('utl_smtp.rcpt');

    l_reply := utl_smtp.rcpt(l_conn, k_recipient);

    IF l_reply.code NOT IN (250, 251)
    THEN
      raise_application_error(-20000, 'utl_smtp.rcpt: '||l_reply.code||' - '||l_reply.text);
    END IF;

    dbms_output.put_line('utl_smtp.open_data');

    l_reply := utl_smtp.open_data(l_conn);

    IF l_reply.code != 354
    THEN
      raise_application_error(-20000, 'utl_smtp.open_data: '||l_reply.code||' - '||l_reply.text);
    END IF;

    dbms_output.put_line('utl_smtp.write_data');

    utl_smtp.write_data(l_conn, 'From: '||k_sender||utl_tcp.crlf);
    utl_smtp.write_data(l_conn, 'To: '||k_recipient||utl_tcp.crlf);
    utl_smtp.write_data(l_conn, 'Subject: '||k_subject||utl_tcp.crlf);
    utl_smtp.write_data(l_conn, utl_tcp.crlf||k_body);

    dbms_output.put_line('utl_smtp.close_data');

    l_reply := utl_smtp.close_data(l_conn);

    IF l_reply.code != 250
    THEN
      raise_application_error(-20000, 'utl_smtp.close_data: '||l_reply.code||' - '||l_reply.text);
    END IF;

    dbms_output.put_line('utl_smtp.quit');

    l_reply := utl_smtp.quit(l_conn);

    IF l_reply.code != 221
    THEN
      raise_application_error(-20000, 'utl_smtp.quit: '||l_reply.code||' - '||l_reply.text);
    END IF;

  EXCEPTION
    WHEN    utl_smtp.transient_error
         OR utl_smtp.permanent_error
    THEN
      BEGIN
        utl_smtp.quit(l_conn);
      EXCEPTION
        WHEN    utl_smtp.transient_error
             OR utl_smtp.permanent_error
        THEN
          NULL;
      END;

      raise_application_error(-20000, 'Failed to send mail due to the following error: '||SQLERRM);

  END;
  /

Hope it helps you!

Advertisements

Orphan AWR Data Consuming SYSAUX Space

Hi all!
Some time ago I faced the this situation in a client. SYSAUX was simply growing with no reason, but retention and everything has not changed.

After some research, could map issue to Bug 14084247: STBH: ORA-01555 DUE TO WRH$_ACTIVE_SESSION_HISTORY NOT PURGED.

There is an one-ooff patch, but needs also extra steps to make it work, as stated on MOS: Excessive AWR Growth From Partitioned Objects Such as SYS.WRH$_EVENT_HISTOGRAM Causing Sysaux to Grow (Doc ID 1912201.1):

However, even after applying bug patch 14084247, this fix will not have an immediate effect and may take some time to "catch up".

The good new is that this can also be applied in on-line patching mode. I did another post documenting this patch applying: Online Data Patch Apply with multiple Databases on same Oracle Home: OPatch failed with error code 26

Some other relevant MOS Documents under this subject are:
WRH$_ACTIVE_SESSION_HISTORY Does Not Get Purged Based Upon the Retention Policy (Doc ID 387914.1)
Suggestions if Your SYSAUX Tablespace Grows Rapidly or Too Large (Doc ID 1292724.1)

Ok, but waht can I do right now?
You can purge the top AWR tables, as described on MOS: WRH$_LATCH, WRH$_SYSSTAT, and WRH$_PARAMETER Consume the Majority of Space within SYSAUX (Doc ID 2099998.1).

Check steps below on how it can be done:

Continue reading

Oracle Database Lock Performance / False Locks

Hello all!
This is a very interesing one… what about database taking too long or even showing locks that don’t really exists?
I faced some weird situations related to the size of DBMS_LOCK_ALLOCATED.

After some research, seems root cause is descripted in MOS DBMS_LOCK_ALLOCATED Table Keeps Growing Eventually Depleting Tablespace (Doc ID 1054343.6).

The issue is:
– DBMS_LOCK_ALLOCATED is the table that keep all locks on database.
– This table keeps growing even though many of the locks are probably no longer used.
– Upon checking the EXPIRED column, it reveals that the locks’ expiration date is a year or more in the future.

Besides the mentioned “storage space issue” in MOS Doc, there is also the situation that we have actually 1073741823 “lockhandlers” available. And what if this ‘not releasing’ cause we run out of handlers? Unlike, but possible.
Having a big table can also cause bad performance on DBMS_LOCK.REQUEST, that is the basic mechanism for locks (passing the handler, lockmode, timeout and release instructions).

Continue reading

Purging a SQL Cursor in 10g

Hello all,
Having issues to purge SQL Cursor in a 10g database?

DBMS_SHARED_POOL.PURGE is available from 10.2.0.4 on, but according to “DBMS_SHARED_POOL.PURGE Is Not Working On 10.2.0.4 (Doc ID 751876.1)”, it is event-protected.
This means you need to set event 5614566 to be able to use the purge.

Example:

sys@PRODB10> select count(*) from v$sqlarea where sql_id='gbaq7x7myqxjw';

  COUNT(*)
----------
         1

sys@PRODB10> exec dbms_shared_pool.purge('00000003B0898118,1807156054','C')

PL/SQL procedure successfully completed.


sys@PRODB10> select count(*) from v$sqlarea where sql_id='gbaq7x7myqxjw';

  COUNT(*)
----------
         1

sys@PRODB10> alter session set events '5614566 trace name context forever';

Session altered.

sys@PRODB10> exec dbms_shared_pool.purge('00000003B0898118,1807156054','C')

PL/SQL procedure successfully completed.

sys@PRODB10> select count(*) from v$sqlarea where sql_id='gbaq7x7myqxjw';

  COUNT(*)
----------
         0

Install Oracle Client 12c on Windows 10 – INS-20802: Oracle Net Configuration Assistant failed

Hello all!
Some days ago a client reach me because he was facing this error when installing Oracle Client 12.1.0.2.0 on Windows 10:

oracle12Error

Researching on topic found this.

Seems Oracle client 12.1.0.2 requires MSVC 2010 redistributable to proceed Oracle Net Configuration step.
After this requested client to install “Microsoft Visual C++ 2010 Redistributable Package” on server. And issue solved! 🙂

The download of Package can be performed from here: https://www.microsoft.com/en-gb/download/details.aspx?id=5555

Hope it helps you!
See you next week!

ORA-00932 to Export Statistics After 12c Upgrade – Undocumented

Hello All,
I have a client that use to Export and Import Dictionary Statistics using a stats table. However, after upgrading database to 12c, is started to raise error below to export data to a stats table (created in 11g) or even to update stats in this table.

SQL> exec dbms_stats.upgrade_stat_table('GREPORA','MYSTATSTABLE');
DECLARE
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected BINARY got NUMBER
ORA-06512: at "SYS.DBMS_STATS", line 40183
ORA-06512: at line 28

SQL> EXEC DBMS_STATS.EXPORT_FIXED_OBJECTS_STATS (stattab => 'MYSTATSTABLE',statid  => 'TEST_MATHEUS',statown => 'GREPORA' );
BEGIN DBMS_STATS.EXPORT_FIXED_OBJECTS_STATS (stattab => 'MYSTATSTABLE',statid  => 'TEST_MATHEUS',statown => 'GREPORA' ); END;

*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected BINARY got NUMBER
ORA-06512: at "SYS.DBMS_STATS", line 37085
ORA-06512: at line 1

I reviewed several references (all very interesting, by the way, recommend you to take a look):
– ORA-20002 on Importing Statistics using DBMS_STATS Procedures (Doc ID 740345.1)
– Datapump Export Fails With Ora-00932 (Doc ID 1300803.1)
– Error “ORA-00932 – Inconsistent Datatypes: Expected BINARY Got NUMBER” When Executing the Primavera Gather-Statistics Scripts on an Oracle 12c Database (Doc ID 2111114.1)
https://oracle-base.com/articles/12c/concurrent-statistics-collection-12cr1
– http://www.morganslibrary.org/reference/pkgs/dbms_stats.html
– How To Export and Import Dictionary Statistics (Doc ID 1450820.1)
– Fixed Objects Statistics (GATHER_FIXED_OBJECTS_STATS) Considerations (Doc ID 798257.1)

But no reference found to this specific issue, not even in MOS.

After some tests, I realized that maybe columns order in stats table can be changed in implementation between 11g and 12c. Bingo! The same columns and datatypes are in place, but in different order.

This is not documented in MOS neither on internet/independent blogs, but seems EXPORT% on DBMS_STATS presume stats table in it’s internal code without specified column orders.
As we know, a good development practice is to specify column names when performing any operation. Bad on you, Oracle!

Continue reading

Long running jobs AQ$_PLSQL_NTFN_%

Hello all!

Some time ago I had an issue with some jobs running for 64 days. Cool han?
All jobs names were AQ$_PLSQL_NTFN_%. All activity of jobs were related to SQLID f7zggdz9p7bhk in wait event “Streams AQ: waiting for messages in the queue”.

# SQLID f7zggdz9p7bhk:
begin SYS.SCHEDULER$_JOB_EVENT_HANDLER(context => :1,reginfo => sys.aq$_reg_info(:2, :3, :4, :5, :6, :7),descr => sys.aq$_descriptor(:8, :9, :10, sys.msg_prop_t(:11, :12, :13, :14, :15, :16, :17, :18, sys.aq$_agent(:19, :20, :21), :22, :23), sys.aq$_ntfn_descriptor(:24), :25, :26), payload => :27, payloadl => :28); end;

Reviewing on MOS, found a match to Bug 20528052 – Many AQ$_PLSQL_NTFN jobs executed affecting database performance (Doc ID 20528052.8).

The root cause is procedure SYS.SCHEDULER$_JOB_EVENT_HANDLER keep waiting AQ PL/SQL Notification callbacks associated with scheduler job email notifications for messages in “SYS”.”SCHEDULER$_EVENT_QUEUE” which no longer exist.

From MOS, this can be easily solved with fix for Bug 16623661 – No email notification is sent by scheduler, wich is included in 12.1.0.1 (base) and affect versions 11.2.0.2 and later.
Also from MOS, and which I did, is drop these jobs. The problem is these jobs just restart and have the same problem again after automated recreation.

So, apply the fix or upgrade your database. 😉

Some related references:
Bug 20528052 – Many AQ$_PLSQL_NTFN jobs executed affecting database performance (Doc ID 20528052.8)
AQ$_PLSQL_NTFN Scheduler Jobs Executed in large numbers affecting Database performance (Doc ID 2001165.1)
Bug 21665897: FIX FOR BUG 14712567 CAUSES LARGE NUMBERS OF AQ$_PLSQL_NTFN_% TO BE SPAWNED

Hope it helps!
Cheers!