Query for CPU patches

Here's my updated query to identify the CPU patches (April 2011 - October 2012) applied on your 11i or R12 instance:

column BUG format a8;

column PATCH format a60;
set linesize 100;
set pagesize 200;

select b.bug_number BUG, b.LAST_UPDATE_DATE LDATE, decode(bug_number,
14321237, 'Oct 2012 CPU Patch for R12.1 + ATG_PF.B.Delta3',
14321239, 'Oct 2012 CPU Patch for R12.0.6',
14321240, 'Oct 2012 CPU Patch for 11i+RUP7',
14321241, 'Oct 2012 CPU Patch for 11i+RUP6',
13979374, 'July 2012 CPU patch for 11i+RUP7',
13979377, 'July 2012 CPU patch for 11i+RUP6',
13979372, 'July 2012 CPU patch for R12.1+ATG_PF.B.Delta3',
13979375, 'July 2012 CPU for R12.0+ATG_PF.A.Delta6',
13621942, 'April 2012 CPU for R12.1+ATG_PF.B.Delta2',
13621941, 'April 2012 CPU for R12.0+ATG_PF.A.Delta6',
13621940, 'April 2012 CPU for 11i+RUP7',
13621939, 'April 2012 CPU for 11i+RUP6',
13322561, 'Jan 2012 CPU for R12.1+ATG_PF.B.Delta2',
12794416, 'Jan 2012 CPU for R12.0+ATG_PF.A.Delta6',
13322559, 'Jan 2012 CPU for 11i+RUP7',
13322557, 'Jan 2012 CPU for 11i+RUP6',
12794417, 'Oct 2011 CPU for R12.1+ATG_PF.B.Delta2',
12794416, 'Oct 2011 CPU for R12.0+ATG_PF.B.Delta6',
12794415, 'Oct 2011 CPU for 11i+RUP7',
12794414, 'Oct 2011 CPU for 11i+RUP6',
12406916, 'Jul 2011 CPU for R12.1',
12406915, 'Jul 2011 CPU for R12.0',
12406914, 'Jul 2011 CPU for 11i+RUP7',
12406913, 'Jul 2011 CPU for 11i+RUP6',
11660357, 'Apr 2011 CPU for R12.1',
11660356, 'Apr 2011 CPU for R12.0',
11660355, 'Apr 2011 CPU for 11i+RUP7',
11660354, 'Apr 2011 CPU for 11i+RUP6'
) PATCH
from APPS.AD_BUGS b
where b.BUG_NUMBER in
('14321237','14321239','14321240','14321240',
'13979374','13979377','13979372','13979375',
'13621942','13621941','13621940','13621939',
'13322561','12794416','13322559','13322557',
'12794417','12794416','12794415','12794414',
'12406916','12406915','12406914','12406913',
'11660357''11660356','11660355','11660354')
order by patch;

No application machine is available to service any funcationality in DBA domain

After a recent clone of OTM loosely based on method described in http://otm62.blogspot.com, when we brought up the services, the following message appeared on accessing the OTM URL:

Error found handling the request.
No application machine is available to service any funcationality in DBA domain.

glog.exception.log was showing this error:

OTMActivationThread]
2012-06-28 13:36:25.728 0       Error   Exception       Server t3://otmapp1.example.com:9001 is not responding
  3:09 PM
[OTMActivationThread]
2012-06-28 13:36:53.804 0       Error   Exception       Could not notify APPSERVER2 of APPSERVER2 activation
[OTMActivationThread]
2012-06-28 13:39:07.491 306049  Error   Exception       cause.QueryFoundNoRecords
  3:09 PM


After going through the contents of glog.properties of both nodes, the cluster tables app_server, app_machine, app_server_machine for many hours, I noticed that the port number was incorrect in the MACHINE_URL column of app_machine. 

SQL> select MACHINE_URL from app_machine;

MACHINE_URL
--------------------------------------------------------------------------------
t3://otmapp1.example.com:9001
t3://otmapp2.example.com:9001 
It had the apache port 9001, instead of the weblogic port 7001

So I updated app_machine table to change it to:

MACHINE_URL
--------------------------------------------------------------------------------
t3://otmapp1.example.com:7001
t3://otmapp2.example.com:7001

After correcting this, the services came up and we were able to login without issues.

File too large zip error: Output file write failure

Recently while zipping a few directories for a clone, I got this error when the file size became greater than 2 GB.

File too large

zip error: Output file write failure (write error on zip file)

I tested the file system's ability to create files greater than 2 GB by creating a dummy 3GB file:

dd if=/dev/zero of=3gb-file bs=1024k count=3000

The 3 GB file got created without issues.

Then I checked the zip version.

$ zip -v
Copyright (c) 1990-2006 Info-ZIP - Type 'zip "-L"' for software license.
This is Zip 2.32 (June 19th 2006), by Info-ZIP.
Currently maintained by Onno van der Linden. Please send bug reports to
the authors using http://www.info-zip.org/zip-bug.html; see README for details.

So I checked http://www.info-zip.org

I clicked on the zip link: http://www.info-zip.org/Zip.html and found these lines:

Latest Release
New features in Zip 3.0, released 7 July 2008:


•large-file support (i.e., > 2GB)
•support for more than 65536 files per archive
•multi-part archive support
•bzip2 compression support
•Unicode (UTF-8) filename and (partial) comment support
•difference mode (for incremental backups)
•filesystem-synch mode
•cross-archive copy mode
•extended progress info and logging
•improved archive-fixing support
•improved streaming and piping
•improved command-line parser
•improved Unix FIFO support
•Unix 32-bit UIDs/GIDs (requires UnZip 6.0 to restore)

So I went to www.sunfreeware.com, downloaded zip 3.0 package and installed it on the server. Once zip 3.0 was installed, the zip command succeeded without errors

What languages and currencies are enabled in EBS

Raghava asked me about the languages and currencies being used in a new instance which has gone live. For languages we have this query:

select language_code
from fnd_languages
where installed_flag='I'

For currencies, I told him that in EBS all currencies are enabled. I executed the following query:

select CURRENCY_CODE,symbol
from apps.fnd_currencies
where enabled_flag='Y'

It returned 71 rows.

Total rows in fnd_currencies was 249.

However, I thought of a more logical way of getting this data. That would be to query transaction tables. Which transaction table to query here ? I queried dba_segments for the largest objects:

select object_name,bytes
from dba_segments
order by bytes desc;

After the usual fnd_lobs etc., gl_interface table came up. So I created this query:

SQL> select currency_code,count(*)
from apps.gl_interface
group by currency_code; 2 3

CURRENCY_CODE COUNT(*)
--------------- ----------
VND xxx
EUR xxx
USD xxx
SGD xxx
JPY xxx


VND = Vietnamese Dollar
SGD = Singapore Dollar
JPY = Japanese Yen
EUR = Euro
USD = US Dollar

I feel that this query does give a good estimate about the currencies in use.

inspection.wsil shows incorrect hostname

In SOA Suite the inspection.wsil URL is:

http://soahostname:soaport/inspection.wsil

However this was showing the physical hostname of the server on which SOA Suite was installed. We had configured SOA to listen on virtual names. Rajiv Bhalla provided the solution to this problem by providing the My Oracle Support Article: Applications like Oracle IPM fail to retrieve WSDL's to SOA composites via URL's that use different protocol/hostname/port than they are configured to [ID 1290812.1]


Solution
1.Access the WebLogic server Admin console: e.g. http://host:port/console.


2.From the left pane, use the following path through the hierarchical menu to navigate to the configuration screen containing the "Front End" configuration

Domain
-> Environment
-> Servers
-> soa_server1
-> Protocols
-> HTTP

3.Review and adjust the values of the following entries:

Frontend Host: myLoadBalancerHost.myDomain
Frontend Port: 80
Frontend HTTPS Port: 443

You'll need to restart the SOA server for this to take effect, even though it says No restarts necessary.

BEA-141281 unable to get file lock

./startManagedWebLogic.sh soa_server1
SOA Managed Server wouldn't start with this error:

BEA-141281 unable to get file lock, will retry

cd $MW_HOME/user_projects/domains/soa_domain/servers/soa_server1/tmp
rm *.lok

Retry command
./startManagedWebLogic.sh soa_server1