What is a queue in Oracle ?

As per orafaq.com, a queue is a first-in first-out data structure. Objects are added (enqueued) to the tail of the queue and taken off the head (dequeued). This is same as a real life queue for tickets or food.

Queues are used to process multiple demands for a resource such as a printer, processor or communications channel (deferred execution of work). Queues also serve as foundation technology for workflow applications.

Queue Tables can be created on an Oracle database with Oracle's Advanced Queueing feature.

Oracle Advanced Queueing (AQ) is the Oracle database's queue management feature. AQ provides a message queuing infrastructure as integral part of the Oracle server engine. It provides an API for enqueing messages to database queues. These messages can later be dequeued for asynchronous processing. Oracle AQ also provides functionality to preserve, track, document, correlate, and query messages in queues.

Oracle AQ was first introduced in Oracle 8.
As of Oracle release 9.2, AQ is bundled with Standard Edition and Enterprise Edition at no extra cost.
As of Oracle release 10.1, AQ is integrated into Oracle Streams, and is called "Oracle Streams AQ".

Metalink Note 316889.1 about 10gr2 manual upgrade recommends that streams_pool_size set to 200MB as an ideal setting.

You can check the value and current size of streams_pool_size with this query:

select component, current_size/1024/1024 "CURRENT_SIZE",
min_size/1024/1024 "MIN_SIZE",
user_specified_size/1024/1024 "USER_SPECIFIED_SIZE",
last_oper_type "TYPE"
from v$sga_dynamic_components;

In Oracle Applications world, the Workflow Notification Mailer is the heaviest user of Oracle streams. One of the recommendation for all the workflow queue's health would be to set the streams_pool_size parameter to 300M.

The Applications System names per the APPL_TOP and the database are different

Sandeep reported this error after cloning:

The Applications System names per the APPL_TOP and the database are different.

Beginning database cleanup for previous session ...

Completed database cleanup for previous session.


The Applications System names per the APPL_TOP and the database are different.

Applications System name per the APPL_TOP: UAT11i

Applications System name per the database: PRD11i

If you continue, the Applications System name per the APPL_TOP will be ignored.

Do you wish to continue [No] ?

He solved it by doing this:

update FND_PRODUCT_GROUPS
set APPLICATIONS_SYSTEM_NAME ='UAT11i' ;
commit;

I am not sure why the name of the production instance remained in FND_PRODUCT_GROUPS inspite of running adcfgclone.pl and executing FND_CONC_CLONE.SETUP_CLEAN.

What is the ECCN of Oracle E-Business Suite 11i ?

Sudarshan asked this question today.

Most of the Oracle Product's ECCN is given here.

The ECCN of E-Business Suite 11i is 5D002.

Oracle products classified as 5D002, qualify for shipment under ENC Guidelines. Oracle products listed on the ECCN Product Matrix are classified as Retail or Non-Retail, as applicable.

ORA-25253: listen failed, queue. is not enabled for dequeue

When an invoice was submitted through a request set following error appeared:

ORA-01116: error in opening database file 54
ORA-01110: data file 54: /erp11i/datafiles1/a_txn_ind08.dbf
ORA-27041: unable to open file
SVR4 Error 24: Too many open files
Additional information: 3

I asked the DBAs to check the open file descriptor limit by issuing commnad:

ulimit -n

The result was 65536 which is the max value.

I asked them to reduce it by 2 and set it to 65534 and bounce the database.

The error changed after making this change to ulimit and bouncing the DB. Now the error is:

While compiling the flexfields, the procedure wf_event.raise failed with following parameters:

event_name=oracle.apps.fnd.flex.dff.compiled
event_key = SBX.$SRS.SBXAPPROM

The error raised is
ORA-25253: listen failed, queue. is not enabled for dequeue

I have asked the DBAs to recreate the workflow queues and see if it helps.

What does APPLCSF stand for ?

A few weeks ago, Jim had asked this question “What does APPLCSF stand for ?”

APPLCSF stands for Applications Common Support/Script Files. In a freshly installed Oracle E-Business Suite environment, if you cd to $APPLCSF, it will take you to $COMMON_TOP/admin/ directory which has these subdirectories:

outbound
inbound
install
out
log
scripts

The Apps DBA supporting Oracle E-Business Suite goes to $APPLCSF/$APPLLOG to check the output of concurrent requests for troubleshooting.

Apache Lock and Mutex files

We had issues in one of our Production Apache. The root cause is that the apache lock files are present on NFS mount:

[Sun Jan 11 03:44:37 2009] [emerg] (151)Stale NFS file handle: fcntl: F_SETLKW: Error getting accept lock, exiting! Perhaps you need to use the LockFile directive to place your lock file on a local disk!
[Sun Jan 11 03:44:38 2009] [alert] Child 26287 returned a Fatal error...
Apache is exiting!
[Sun Jan 11 03:44:39 2009] [emerg] (151)Stale NFS file handle: fcntl: F_SETLKW: Error getting accept lock, exiting! Perhaps you need to use the LockFile directive to place your lock file on a local disk!
[Sun Jan 11 03:44:44 2009] [emerg] (5)I/O error: fcntl: F_SETLKW: Error freeing accept lock, exiting! Perhaps you need to use the LockFile directive to place your lock file on a local disk!
[Sun Jan 11 03:44:44 2009] [error] OPM:hc:Bad chunk-size value in response body
[Sun Jan 11 03:44:56 2009] [emerg] (151)Stale NFS file handle: fcntl: F_SETLKW: Error getting accept lock, exiting! Perhaps you need to use the LockFile directive to place your lock file on a local disk!
[Sun Jan 11 03:44:56 2009] [emerg] (151)Stale NFS file handle: fcntl: F_SETLKW: Error getting accept lock, exiting! Perhaps you need to use the LockFile directive to place your lock file on a local disk!
[Sun Jan 11 03:44:56 2009] [emerg] (151)Stale NFS file handle: fcntl: F_SETLKW: Error getting accept lock, exiting! Perhaps you need to use the LockFile directive to place your lock file on a local disk!
[Sun Jan 11 03:44:57 2009] [emerg] (151)Stale NFS file handle: fcntl: F_SETLKW: Error getting accept lock, exiting! Perhaps you need to use the LockFile directive to place your lock file on a local disk!
[Sun Jan 11 03:45:08 2009] [emerg] (151)Stale NFS file handle: fcntl: F_SETLKW: Error getting accept lock, exiting! Perhaps you need to use the LockFile directive to place your lock file on a local disk!
[Sun Jan 11 03:45:47 2009] [emerg] (151)Stale NFS file handle: fcntl: F_SETLKW: Error getting accept lock, exiting! Perhaps you need to use the LockFile directive to place your lock file on a local disk!
[Sun Jan 11 03:45:57 2009] [emerg] (151)Stale NFS file handle: fcntl: F_SETLKW: Error getting accept lock, exiting! Perhaps you need to use the LockFile directive to place your lock file on a local disk!
[Sun Jan 11 18:20:40 2009] [warn] pid file /erp11i/erpapp/comn/conf/erp11i_app11i/iAS/Apache/Apache/logs/erp11i_erp11i
overwritten -- Unclean shutdown of previous Apache run?
[Sun Jan 11 18:20:40 2009] [notice] FastCGI: process manager initialized (pid 9972)
[Sun Jan 11 18:20:41 2009] [notice] Oracle HTTP Server Powered by Apache/1.3.19 configured -- resuming normal operations

It is Oracle and Apache’s recommendation that we put Apache Lock and Mutex files on local disk instead of NFS mount (Metalink Note 233428.1):

Apache Lock and OPM Mutex Files
Oracle HTTP Server and the Oracle Process Manager processes create temporary lock files for their internal operations. The location of these lock files are specified in httpd.conf by the LockFile and OpmMtxFile respectively. You must ensure that value of the autoconfig variable s_lock_pid_dir is set to a location on the local file system to avoid file locking issues on the network file system.

http://httpd.apache.org/docs/1.3/mod/core.html#lockfile
LockFile directive
Syntax: LockFile file-path
Default: LockFile logs/accept.lock
Context: server config
Status: core
The LockFile directive sets the path to the lockfile used when Apache is compiled with either USE_FCNTL_SERIALIZED_ACCEPT or USE_FLOCK_SERIALIZED_ACCEPT. This directive should normally be left at its default value. The main reason for changing it is if the logs directory is NFS mounted, since the lockfile must be stored on a local disk. The PID of the main server process is automatically appended to the filename.
SECURITY: It is best to avoid putting this file in a world writable directory such as /var/tmp because someone could create a denial of service attack and prevent the server from starting by creating a lockfile with the same name as the one the server will try to create.
Apache works fine on systems that mount disks via NFS. However there are some files which Apache uses which should not be stored on NFS mounts. The most important is the "lock file" which is used by Apache to efficiently let multiple processes access the same network socket. This default location for this file is the "logs" directory under the server root, unless changed by the "LockFile" (autoconfig variable lock_pid_dir) directive. If the server root or the logs directory is NFS mounted, the location of the lock file must be changed. A directory such as /tmp or /var/tmp is often a good location. This lock file should not be NFS mounted because many implementations of NFS do not lock files properly.

For achieving this, we need to do the following:
1. Make certain that the following Autoconfig variables point to a "local disk" (/export/home/applmgr). If one has multiple application servers then this must be checked and changed on each application tier.
s_lock_pid_dir
s_web_pid_file
2. Run Autoconfig after the change
3. Restart your middle tier services
References: Metalink Notes: 560853.1, 605596.1, 738612.1, 732857.1