Abotts Partners with singapore based tech giant to help migrate their public sector customer from Sybase to SQL server.
Abotts partners with NYPL to integrate with their partner libraries.
Upworks Inc partners with ABOTTS to build their Oracle Cloud Infrastructure (OCI) and migrate their custom applications to OCI.
Abotts Inc Partners with Gnorth consulting to deploy exadata and ODA for a large public sector customer.
ORA-29279: SMTP Permanent Error – 535 Authentication Credentials Invalid
ERROR at line 1:
ORA-20000: Failed to send mail due to the following error: ORA-29279: SMTP permanent error: 535 Authentication credentials invalid
ORA-06512: at “C##CLOUD$SERVICE.DBMS_CLOUD$PDBCS_250124_0”, line 2100
ORA-06512: at “C##CLOUD$SERVICE.DBMS_CLOUD_NOTIFICATION”, line 2593
ORA-06512: at line 2
https://docs.oracle.com/error-help/db/ora-20000/
More Details :
https://docs.oracle.com/error-help/db/ora-20000/
https://docs.oracle.com/error-help/db/ora-06512/
Cause of the Error
- Invalid SMTP Credentials:
- The username or password provided for the SMTP server is incorrect.
- SMTP Server Misconfiguration:
- The SMTP server may not allow relay or external connections.
- Incorrect SMTP settings (e.g., port, TLS/SSL settings).
- Firewall/Network Restrictions:
- SMTP connection blocked by firewall or network settings.
- SMTP over VPN (like Zscaler) may require additional configuration.
- Invalid Email Format:
- Incorrect email format or recipient address.
Solution for the Error
1. Check SMTP Configuration in Oracle:
Verify the SMTP server settings using the following query:
SELECT name, value
FROM v$parameter
WHERE name LIKE ‘%smtp%’;
Ensure the SMTP server settings are correct:
- SMTP_OUT_SERVER → Correct SMTP server address
- SMTP_PORT → Correct port (e.g., 25, 465, 587)
- SMTP_AUTHENTICATION → Authentication method (PLAIN, LOGIN, etc.)
2. Fix Invalid SMTP Credentials:
Use the DBMS_NETWORK_ACL_ADMIN package to modify or recreate the SMTP ACL:
Grant ACL to the Database User:
BEGIN
DBMS_NETWORK_ACL_ADMIN.create_acl (
acl => ’email_acl.xml’,
description => ‘Email ACL’,
principal => ‘DEEKSHITH’,
is_grant => TRUE,
privilege => ‘connect’
);
DBMS_NETWORK_ACL_ADMIN.add_privilege (
acl => ’email_acl.xml’,
principal => ‘DEEKSHITH’,
is_grant => TRUE,
privilege => ‘resolve’
);
DBMS_NETWORK_ACL_ADMIN.assign_acl (
acl => ’email_acl.xml’,
host => ‘smtp.upwork.com’,
lower_port => 465,
upper_port => 465
);
COMMIT;
END;
/
3. Update SMTP Credentials:
Use the DBMS_CLOUD package to update the credentials:
BEGIN
DBMS_CLOUD_ADMIN.UPDATE_CREDENTIAL (
credential_name => ‘SMTP_CREDENTIAL’,
username => ‘your_smtp_user’,
password => ‘your_smtp_password’
);
END;
/
4. Test SMTP Connection:
Test the SMTP connection using UTL_SMTP:
DECLARE
smtp_conn UTL_SMTP.connection;
BEGIN
smtp_conn := UTL_SMTP.open_connection(‘smtp.upwork.com’, 465);
UTL_SMTP.helo(smtp_conn, ‘upwork.com’);
UTL_SMTP.quit(smtp_conn);
DBMS_OUTPUT.put_line(‘SMTP connection successful.’);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line(‘SMTP connection failed: ‘ || SQLERRM);
END;
/
5. Resend Email After Fixing:
Once SMTP credentials are fixed, you can send the email using:
BEGIN
DBMS_CLOUD_NOTIFICATION.SEND_MAIL(
to_list => ‘recipient@upwork.com’,
subject => ‘Test Email’,
body => ‘This is a test email from Oracle.’
);
END;
/
Prevention for Future Errors
- Rotate SMTP Passwords:
- Ensure SMTP passwords are regularly updated and follow password security guidelines.
- Set Up Monitoring:
- Monitor SMTP connection logs and alert for any failures.
- Use TLS/SSL Secure Ports:
- Ensure you are using secure ports (465 or 587) for SMTP authentication.
- Validate Firewall Settings:
- Ensure firewall rules allow outbound SMTP traffic over the configured port.
- Automate Email Testing:
- Set up a scheduled job to send test emails periodically to confirm connectivity.
Conclusion:
The ORA-29279 error occurs due to invalid SMTP authentication credentials while attempting to send an email using the DBMS_CLOUD_NOTIFICATION package. To resolve this, ensure that the correct SMTP username and password are configured, the SMTP server settings are accurate, and the authentication method matches the server requirements. Proper testing and validation of SMTP credentials and network connectivity can help prevent this error in the future.