Day: October 21, 2014

ORA-24247: network access denied by access control list (ACL)

While executing PL/SQL procedure to send mails by using UTL_SMTP, it fails with error “ORA-24247” in 11g databases.

SOLUTION

ACL needs to be created with following instructions.

DECLARE
acl_path VARCHAR2(4000);
BEGIN
  SELECT acl INTO acl_path FROM dba_network_acls
  WHERE host = '*' AND lower_port IS NULL AND upper_port IS NULL;
  IF DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(acl_path,'SCOTT','connect')
     IS NULL 
 THEN
     DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl_path,'SCOTT', TRUE,    'connect');
  END IF;
EXCEPTION
WHEN no_data_found THEN
    DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('utl_mail.xml','ACL description',  'SCOTT', TRUE, 'connect');
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('utl_mail.xml','*');
END;

COMMIT;
SQL> select ACL,PRINCIPAL,PRIVILEGE,IS_GRANT 
       from DBA_NETWORK_ACL_PRIVILEGES;

ACL                    PRINCIPAL  PRIVILEGE IS_GRANT
---------------------- ---------- --------- --------------------
/sys/acls/utl_mail.xml SCOTT      connect   true

ORA-24247 Trying To Send Email Using UTL_SMTP from 11gR1 (11.1.0.6) or higher (Doc ID 557070.1)

Advertisement

The dataguard broker keeps killing process RSM

Dataguard broker keeps killing RSM process overnight and oncall DBA is pagered by alerts.

DG 2014-10-04-04:17:17 0 2 0 DMON: killing process RSM0, pid = 26742
DG 2014-10-04-04:17:18 0 2 0 DMON: waiting for subscribers to disappear...
DG 2014-10-04-04:17:18 0 2 0 PMON: delete state object for RSM0
DG 2014-10-04-04:17:18 0 2 0 PMON: RSM0 died unexpectedly while processing request 1.1.833218860,
DG 2014-10-04-04:17:18 0 2 0 notifying DMON of RSM failure
DG 2014-10-04-04:17:18 0 2 0 DMON: Creating process RSM0
DG 2014-10-04-04:17:21 0 2 0 RSM0: Attach state object
DG 2014-10-04-04:17:21 0 2 0 DMON: Process RSM0 re-created with pid = 22898

WORKAROUND

DGMGRL> show configuration OperationTimeout;
OperationTimeout = '30'

DGMGRL> EDIT CONFIGURATION SET PROPERTY OperationTimeout=120;
Property "operationtimeout" updated

DGMGRL> show configuration OperationTimeout;
OperationTimeout = '120'
DGMGRL>

Reference Oracle Doc:
Data Guard: Server Hang And Crash Because RSM0 Keeps Re-spawning (Doc ID 1322877.1)
Cause : The Broker is killing the RSM0 process and restarting it because of an internal timeout that is too low.