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

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




After we refreshed some of the production schemas to QA database, application users started reporting that the jobs are failing on QA database with below error.Later, we started debugging and  found that the users don't have necessary permissions on ACL.

 ERROR[com.cp.ejb.session.portfoliomgmt.PortfolioDelegatorBean][[ACTIVE] ExecuteThread: '5' for queue: 'weblogic.kernel.Default (self-tuning)']: got Exception while creating portfolio Schedule data:::::::::::

javax.ejb.EJBException: nested exception is: javax.ejb.TransactionRolledbackLocalException: Error committing transact ion:; nested exception is: java.sql.SQLException: ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1722
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "USER1.portfolio", line 135
ORA-04088: error during execution of trigger 


Cause:
If you have upgraded from a release before Oracle Database 11g Release 1 (11.1), and your applications depend on PL/SQL network utility packages such as UTL_TCP, UTL_SMTP, UTL_MAIL, UTL_HTTP, and UTL_INADDR, the following error may occur when you try to run the application:

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

Solution:

Grant the connect, resolve privileges to users




SQL> SET LINESIZE 150

COLUMN acl FORMAT A50
COLUMN principal FORMAT A20
COLUMN privilege FORMAT A10

SELECT acl,
       principal,
       privilege,
       is_grant,
       TO_CHAR(start_date, 'DD-MON-YYYY') AS start_date,
       TO_CHAR(end_date, 'DD-MON-YYYY') AS end_date
FROM   dba_network_acl_privileges
ORDER BY acl, principal, privilege;SQL> SQL> SQL> SQL> SQL> SQL>   2    3    4    5    6    7    8

ACL                                                PRINCIPAL            PRIVILEGE  IS_GRANT             START_DATE           END_DATE
-------------------------------------------------- -------------------- ---------- -------------------- -------------------- --------------------
/sys/acls/oracle-sysman-ocm-Resolve-Access.xml     ORACLE_OCM           resolve    true


Work around:

step 1 : Grant the connect and resolve privileges to SCOTT.

Simply run the below command to grant connect and resolve privileges to SCOTT user.

BEGIN
  DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(acl         => '/sys/acls/network_services_Resolve-Access.xml',
                                    description => 'SCOTT ACL',
                                    principal   => 'SCOTT',
                                    is_grant    => true,
                                    privilege   => 'connect');

  DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl       => '/sys/acls/network_services_Resolve-Access.xml',
                                       principal => 'SCOTT',
                                       is_grant  => true,
                                       privilege => 'resolve');

  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(acl  => '/sys/acls/network_services_Resolve-Access.xml',
                                    host => '*');
END;
/
COMMIT;


To add more users or roles to the access control list, or grant additional privileges to one user or role, use the DBMS_NETWORK_ACL.ADD_PRIVILEGE procedure. The syntax is as follows:

If you want to grant connect and resolve privileges to multiple users on same host there is no need to create the new ACL using CREATE_ACL . we can simply use the existing ACL (/sys/acls/network_services_Resolve-Access.xml) created in previous step and add this privileges to others using ADD_PRIVILEGE procedure as mentioned below.



BEGIN
  DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl         => '/sys/acls/network_services_Resolve-Access.xml',
                                    principal   => 'USER1',
                                    is_grant    => true,
                                    privilege   => 'connect');
  DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl       => '/sys/acls/network_services_Resolve-Access.xml',
                                       principal => 'USER1',
                                       is_grant  => true,
                                       privilege => 'resolve');
  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(acl  => '/sys/acls/network_services_Resolve-Access.xml',
                                    host => '*');
END;
/
COMMIT;

ACL                                                PRINCIPAL            PRIVILEGE  IS_GRANT             START_DATE           END_DATE

-------------------------------------------------- -------------------- ---------- -------------------- -------------------- --------------------

/sys/acls/network_services_Resolve-Access.xml      CP360                connect    true

/sys/acls/network_services_Resolve-Access.xml      CP360                resolve    true

/sys/acls/network_services_Resolve-Access.xml      CP360MFADM           connect    true

/sys/acls/network_services_Resolve-Access.xml      CP360MFADM           resolve    true

/sys/acls/network_services_Resolve-Access.xml      MFEED                connect    true

/sys/acls/network_services_Resolve-Access.xml      MFEED                resolve    true

/sys/acls/network_services_Resolve-Access.xml      UNPRICED             connect    true

/sys/acls/network_services_Resolve-Access.xml      UNPRICED             resolve    true

/sys/acls/oracle-sysman-ocm-Resolve-Access.xml     ORACLE_OCM           resolve    true

If both CPU and PSU are available for given version which one, you will prefer to apply?

Once you apply the PSU then the recommended way is to apply the next PSU only. In fact, no need to apply CPU on the top of PSU as PSU contain CPU (If you apply CPU over PSU will considered you are trying to rollback the PSU and will require more effort in fact). So if you have not decided or applied any of the patches then, I will suggest you to go to use PSU patches. For more details refer: Oracle Products [ID 1430923.1], ID 1446582.1

PSU is superset of CPU and CPU mostly deals with security issues where as PSU fixes security issues as well as functionality changes.
if you are only concerned about security issues then CPU may be the good approach.
Oracle release both the patch sets every quarter.

What is different between TRUNCATE and DELETE?

The Delete command will log the data changes in the log file where as the truncate will simply remove the data without it. Hence Data removed by Delete command can be rolled back but not the data removed by TRUNCATE. Truncate is a DDL statement whereas DELETE is a DML statement.

Explain the difference between a FUNCTION, PROCEDURE and PACKAGE ?

A function and procedure are the same in that they are intended to be a collection of PL/SQL code that carries a single task. While a procedure does not have to return any values to the calling application, a function will return a single value. A package on the other hand is a collection of functions and procedures that are grouped together based on their commonality to a business function or application.

Oracle local and global inventory

What is Global Inventory ?

Global Inventory holds information about Oracle Products on a Machine. These products can be various oracle components like database, oracle application server, collaboration suite, soa suite, forms & reports or discoverer server . This global Inventory location will be determined by file oraInst.loc in /etc (on Linux) or /var/opt/oracle (solaris). If you want to see list of oracle products on machine check for file inventory.xml under ContentsXML in oraInventory Please note if you have multiple global Inventory on machine check all oraInventory directories)

You will see entry like

HOME NAME=”ORA10g_HOME” LOC=”/u01/oracle/10.2.0/db” TYPE=”O” IDX=”1?/

What is Local Inventory ?

Inventory inside each Oracle Home is called as local Inventory or oracle_home Inventory. This Inventory holds information to that oracle_home only.

What is Oracle Home Inventory?

Oracle home inventory or local inventory is present inside each Oracle home. It only contains information relevant to a particular Oracle home. This file is located in the following location:

$ORACLE_HOME/inventory

It contains the following files and folders:

· Components File

· Home Properties File

· Other Folders

What to do if my Global Inventory is corrupted ?

No need to worry if your global Inventory is corrupted, you can recreate global Inventory on machine using Universal Installer and attach already Installed oracle home by option

-attachHome

./runInstaller -silent -attachHome -invPtrLoc $location_to_oraInst.loc

ORACLE_HOME=”Oracle_Home_Location” ORACLE_HOME_NAME=”Oracle_Home_Name”

CLUSTER_NODES=”{}”

Oracle background processes

Background processes started when an Oracle Instance is started.

Background Processes Maintains and enforces relationships between physical and memory structures

There are two types of database processes:

1. Mandatory background processes

2. Optional background processes

Mandatory background processes:

– DBWn, PMON, CKPT, LGWR, SMON

Optional background processes:

– ARCn, LMDn, RECO, CJQ0, LMON, Snnn, Dnnn, Pnnn, LCKn, QMNn

DBWn writes when:

• Checkpoint occurs

• Dirty buffers reach threshold

• There are no free buffers

• Timeout occurs

• RAC ping request is made

• Tablespace OFFLINE

• Tablespace READ ONLY

• Table DROP or TRUNCATE

• Tablespace BEGIN BACKUP

Log Writer (LGWR) writes:

• At commit

• When 1/3rd full

• When there is 1 MB of redo

• Every 3 seconds

• Before DBWn writes

System Monitor (SMON) Responsibilities:

• Instance recovery

– Rolls forward changes in redo logs

– Opens database for user access

– Rolls back uncommitted transactions

• Coalesces free space

• Deallocates temporary segments.

Process Monitor (PMON) Cleans up after failed processes by:

• Rolling back the transaction

• Releasing locks

• Releasing other resources

• Restarting dead dispatchers

Checkpoint (CKPT) Responsible for:

• Signaling DBWn at checkpoints

• Updating datafile headers with checkpoint information

• Updating control files with checkpoint information

Archiver (ARCn)

• Optional background process

• Automatically archives online redo logs when ARCHIVELOG mode is set

• Preserves the record of all changes made to the database

What is main purpose of CHECKPOINT?

A Checkpoint is a database event, which synchronizes the data blocks in memory with the datafiles on disk.
A checkpoint has two purposes:
    1. to establish data consistency
    2. Enable faster database recovery

The following are the parameter that will be used by DBA to adjust time or interval of how frequently its checkpoint should occur in database.
LOG_CHECKPOINT_TIMEOUT = 3600; # Every one hour
LOG_CHECKPOINT_INTERVAL = 1000; # number of OS blocks.