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

Where is listener information stored in Database

In E-Business Suite, the information about listeners is stored in the tables

APPS.FND_TNS_LISTENERS
APPS.FND_TNS_LISTENER_PORTS

SQL> SELECT A.LISTENER_NAME,B.PORT
2 FROM APPS.FND_TNS_LISTENERS A, APPS.FND_TNS_LISTENER_PORTS B
3 WHERE A.LISTENER_GUID = B.LISTENER_GUID
4 /

LISTENER_NAME
--------------------------------------------------------------------------------
PORT
----------
VISIONDBSERVER_VISION_DB
1521

APPS_VISIONDBSERVER_VISION_APPS
1621

APPS_VISIONAPPSERVER_VISION_APPS
1622

Use wget with proxy

Rajeswari sent a mail with this query:

Can some one help us out in unzipping the files which are there on the server by using a program and put those extracted files to a particular folder on the server path.

Example: Go to the link http://www.thaifxrates.net/

Click on CSV Button, you will see a zip file.

This was my response:

Hi Rajeswari,

You can use wget utility to get the file from the server

You can use unzip utility which is present in all unix servers like this as an operating system call in your program

$ /usr/sfw/bin/wget http://www.thaifxrates.net/ER_CSV_TH.zip
--11:07:42-- http://www.thaifxrates.net/ER_CSV_TH.zip
=> `ER_CSV_TH.zip'
Resolving http-proxy.justanexample.com... 192.168.40.200
Connecting to http-proxy.justanexample.com[192.168.40.200]:80... connected.
Proxy request sent, awaiting response... 407 Proxy Authentication Required
11:07:42 ERROR 407: Proxy Authentication Required.

It errored out because I had not put in the proxy username and password. So I added that:

$ export http_proxy="http://proxyuser:password@http-proxy.justanexample.com:80"
$ echo $http_proxy
http://proxyuser:password@http-proxy.justanexample.com:80

$ /usr/sfw/bin/wget http://www.thaifxrates.net/ER_CSV_TH.zip
--11:08:57-- http://www.thaifxrates.net/ER_CSV_TH.zip
=> `ER_CSV_TH.zip'
Resolving http-proxy.justanexample.com... 192.168.40.200
Connecting to http-proxy.justanexample.com[192.168.40.200]:80... connected.
Proxy request sent, awaiting response... 200 OK
Length: 2,256 [application/zip]

100%[=====================================>] 2,256 --.--K/s

11:08:58 (21.51 MB/s) - `ER_CSV_TH.zip' saved [2256/2256]

$ unzip ER_CSV_TH.zip
Archive: ER_CSV_TH.zip
inflating: ER_CSV1_2009-07-14.csv
inflating: ER_CSV2_2009-07-14.csv
inflating: ER_CSV3_2009-07-14.csv
$

In case your download breaks due to whatever reason, you can resume the download with wget option -c (continue)

So you can execute

/usr/sfw/bin/wget -c http://www.thaifxrates.net/ER_CSV_TH.zip to resume your download

July 2009 CPU and DSTv11 patches

Metalink Note 458452.1: DSV v11 patches

Metalink Note 836258.1: July 2009 CPU for E-Business Suite

Metalink Note 835649.1: July 2009 CPU for Oracle Database

Oracle Database 10.2.0.4 CPU Patch 8534387 (Apply patch 8434935 before applying the 10.2.0.4 July 09 CPU patch.)

No new Developer6i patches

Following ATG patches for apps if you are on ATG RUP6

7758943
8528340
8488738
8225016
8412015 (iStore customers only)

Monitoring Applications username in OEM

Mohan pinged with a query about registering Oracle E-Business Suite Systems with Grid Control.
He said that for "Monitoring Schema Username" he had given em_monitor user password

He wanted to know the values for

Monitoring Applications Username
Monitoring Applications User Password

He tried giving the username as APPS but it failed with Io exception: The Network Adapter could not establish the connection

So I did a google search for keywords "Monitoring Applications Username". Two links turned up and the second link was the guide Oracle® Application Management Pack for Oracle
E-Business Suite User's Guide http://www.oracle.com/technology/products/oem/pdf/usergd_appl_mgmt_ebs.pdf

On searching inside the guide it said Please refer to the chapter Application Service-Level Monitoring, page 5-1 for the steps required for creating a
Monitoring Applications user. After following these steps, enter the credentials of the Applications user into these fields.

Page 5-1 had this information:

Setup Tasks Prior to recording Web Transactions
Create an Oracle Applications user for beacon monitoring and playback
In your Oracle Applications system, create an Applications user for beacon monitoring
and playback from Enterprise Manager. Ensure that this user has all the responsibilities
required to perform the Web transactions that you will be recording.
Update Fixed Key profile options
1. Set profile "FND: Fixed Key Enabled" to "Yes" for the Oracle Applications user
created above.
2. Set profile "FND: Fixed Key" to a hexadecimal String of length 64 for the Oracle
Applications user created above. Example "AAAA....A" (x64).
3. Set the profiles "FND: Fixed Key Enabled" to "Yes" and "FND: Fixed Key" to the
same value as set in item (2) above for the Guest user. The Guest user is determined
by the profile "GUEST_USER_PWD" and is typically the user "GUEST".

So in short, you need to provide a front end username like SYSADMIN

Mohan further clarified whether we can give "guest" user

I advised him not to use Guest user as it is restricted and to create a new user for monitoring.

Solaris Error 126

Anand Reddy got this error while starting listener

TNS-12545: Connect failed because target host or object does not exist
TNS-12560: TNS:protocol adapter error
TNS-00515: Connect failed because target host or object does not exist
Solaris Error: 126: Cannot assign requested address

Here's a description of the Solaris OS error code 126

126 EADDRNOTAVAIL Cannot assign requested address
Results from an attempt to create a transport endpoint with an address not on the current machine.

It turned out that he was starting the listener with an incorrect hostname.

Once he corrected it, the error stopped coming.

Shoaib pinged me with the same error and told me that he had located this blog post on a google search.  In Shoaib's case the error was same, but cause was different:

TNSLSNR for Solaris: Version 10.1.0.5.0 - Production
System parameter file is $INST_TOP/ora/10.1.2/network/admin/listener.ora
Log messages written to $LOG_HOME/ora/10.1.2/network/apps_appsr12.log
Error listening on: (ADDRESS=(PROTOCOL=TCP)(Host=apps101)(Port=1705))
TNS-12545: Connect failed because target host or object does not exist
TNS-12560: TNS:protocol adapter error
 TNS-00515: Connect failed because target host or object does not exist
  Solaris Error: 126: Cannot assign requested address

Listener failed to start. See the error message(s) above...

When I pinged apps101 on the apps101 server it returned the address 192.168.1.20


When I pinged apps101 from my laptop it returned the address 192.168.2.20


The IP address of apps101 was incorrect in the /etc/hosts file.


Whenever we were starting the listener, it was trying to access the non existant IP 192.168.1.20.


I verified this with ifconfig -a command which did not show any IP with 192.168.1.* address.


After correcting the IP address in the /etc/hosts file, the listener started without issues:

Everything over IP

I am currently reading the book "The Illustrated Network, How TCP IP works in a modern network" by Walter Goralski. Here's a passage from the foreword which I found interesting:

Things move fast in the networking industry; technologies can go from cutting edge to obsolete in a decade or less (think ATM, frame relay, token ring, and FDDI among others). It is therefore amazing that TCP/IP is 35 years old and evolved from ideas originating in the early 1960s. Yet while the protocol invented by Vint Cerf and Bob Kahn in 1973 has undergone—and continues to undergo—hundreds of enhancements and one version upgrade, its core functions are essentially the same as they were in the mid 1980s. TCP/IP’s antiquity, in an industry that unceremoniously discards technologies when something better comes along, is a testament to the protocol’s elegance and flexibility.

And there is no sign that IP is coming to the end of its useful life. To the contrary, so many new IP-capable applications, devices, and services are being added to networks every day that a newer version, IPv6, has become necessary to provide sufficient IP addresses into the foreseeable future. As this foreword is written, IPv6 is in the very early stages of deployment; readers will still be learning from this book when IPv6 is the only version most people know.

The story of how TCP/IP came to dominate the networking industry is well known. Cerf, Kahn, Jon Postel, and many others who contributed to the early development of TCP/IP did so as a part of their involvement in creating ARPANET, the predecessor of the modern Internet. The protocol stack became further embedded in the infant industry when it was integrated into Unix, making it popular with developers.

But its acceptance was far from assured in those early years. Organizations such as national governments and telcos were uncomfortable with the informal “give it a try and see what works” process of the Working Groups—primarily made up of enthusiastic graduate students—that eventually became the Internet Engineering Task Force (IETF). Those cautious organizations wanted a networking protocol developed under a rigorous standardization process. The International Organization for Standardization (ISO) was tapped to develop a “mature” networking protocol suite, which was eventually to become the Open Systems Interconnection (OSI).

The ISO’s modus operandi of establishing dense, thorough standards and releasing them only in complete, production-ready form took time. Even strong OSI advocates began using TCP/IP as a temporary but working solution while waiting for the ISO standards committees to fi nish their work. By the time OSI was ready, TCP/IP was so widely deployed, proven, and understood that few network operators could justify undertaking a migration to something different.

OSI survives today mainly in a few artifacts such as IS–IS and the ubiquitous OSI reference model. TCP/IP, in the meantime, is becoming an almost universal communications transport protocol.

- Jeff Doyle

How to check if a forms patch is applied

There is no direct way to find out if a forms patch is applied. This is because forms patches are shell scripts and no history is maintained. DBAs usually create directories inside 806 ORACLE_HOME whose name is same as the patch number. However these may be deleted subsequently to reclaim space.

Metalink Note 781022.1 How To Check To See If A Forms Patch Is Applied?

If you find that it is not applied, then please apply it.

I think, since Oracle is moving to a java based future, they are not going to create the functionality of maintaining the patch history for Forms patches. This will have to be done manually by DBAs. Maybe they can create a forms_patches table with the columns patchno and description. But anything manual always requires discipline.