Blog dedicated to Oracle Applications (E-Business Suite) Technology; covers Apps Architecture, Administration and third party bolt-ons to Apps

Monday, December 28, 2009

Applications System appears as Production SID after clone

Sandeep Nagpal called today. He told me that after a clone, in OAM Workflow manager screen it showed Applications System name and Workflow System name as Production SID.

I asked him to run these queries and check the values:

select applications_system_name from apps.fnd_product_groups;

select name from apps.wf_systems;

These two had production sid instead of the cloned sid.

I told Sandeep to:

1. Update the two tables with correct values
2. Stop Apache
3. rm $COMMON_TOP/_pages
4. Restart Apache.

Once he did the above 4 steps, OAM started showing the cloned system SID.

Tuesday, December 22, 2009

ERROR: Invalid java version found

Lalitha reported this issue today :

AutoConfig is configuring the Applications environment...

AutoConfig will consider the custom templates if present.
Using APPL_TOP location : $APPL_TOP
ERROR: Invalid java version found:
Error occurred during initialization of VM
Could not reserve enough space for object heap

ERROR: Invalid java version found:
Error occurred during initialization of VM
Could not reserve enough space for object heap

Classpath :

Version Conflicts among development maintained and customized templates encountered; aborting AutoConfig run.

The logfile for this session is located at: $APPL_TOP/admin/$CONTEXT_NAME/log/12221350/adconfig.log

It looks like a resource constraint on the server. Unix team is working on it. Will update, when I learn more.

Tuesday, December 1, 2009

How to boot DOS with network support

Recently I got hold of a box which didn't have a CD drive. We wanted to install Windows Server 2003 on that box for some R&D work. The only way to boot was through a floppy disk. It had been ages since I used a floppy. Fortunately I found some blank floppy disks. Googling on the net, I found the Universal TCP/IP network disk on www.netbootdisk.com. It allowed me to create a bootable floppy disk with TCP/IP network support. The bootable floppy asked me for username, password and Domain/Workgroup, which I entered dutifully. I was able to map the CD in my laptop's CD drive thorugh this command:

net use x: \\laptop\cd

The boot floppy did not have format.com, fdisk.exe and smartdrv.exe. fdisk and format were needed to format the hard disk and smartdrv.exe was required by Windows 2003 setup. I downloaded these from www.allbootdisks.com.

I was able to install Windows Server 2003 on this box without a CD drive with this approach.

Tuesday, November 17, 2009

See Legacy SR# in new Metalink

I found this from Migration FAQ of Classic Metalink available on
http://www.oracle.com/us/support/040368

How do I view the old Service Request number on my Service Request?
As part of Oracle's move to a single system for tracking SRs, SR numbers were changed.

You can view the Classic MetaLink SR number in the Legacy SR Number field in the SR views on My Oracle Support. To add the Legacy SR Number field to the Service Request list region click the table icon in the upper left hand corner of the list view. Select Columns > Legacy SR. You may sort on this field by clicking the column header.

Monday, November 16, 2009

Notes from the field: Issues after going live with Sun JRE plugin

We went live with Sun JRE plugin on our first instance in the weekend. As expected, we got lot of calls for issues. Here's a list:

1. Proxy issues: All our ERPs are configured with load balancers. However, whenever a JVM like Jinitiator / Sun JRE is used, the connection is always between the forms server port and the user's PC directly. This is blocked by proxies. Being an internal site, we have configured IE to exclude the internal site's name in exclusion list. We also configured Java Control Panel applet to connect directly instead of using Browser Settings.

2. Popup windows don't popup but appear minimized: Described in Metalink Note 864861.1:

Cause
This issue is caused by a new 'feature' in Java 1.6.0_10 and above
More details of the feature can be found at:
http://www.java.com/en/download/help/new_plugin.xml?jre=6.0

Solution
To implement the solution, please execute the following steps:
Note - this is a client machine specific solution:
1. Click Start.
2. Click Control Panel.
3. Double click on Java Control Panel.
4. Click Advance tab in Java Control Panel.
5. Scroll to Java Plug-in entry.
6. Uncheck the check box for Enable next-generation Java Plug-in

3. JRE refuses to install : This was a weird one as nothing happened when we clicked on the Sun JRE executable downloaded to local disk. It turns out that the user's PC had issues and many other executables could not be installed as well. I have suggested to install this after booting Windows in Safe Mode.

Will update as I learn more.

Are there any standards / templates for development forms that are pure jsp within Oracle ERP?

Steve asked me this question today. Many months back Srinivas Reddy had asked me a similar question and I had found out about the existance of Oracle Applications Framework Developer's Guide. So I searched My Oracle Support for this and landed up with two metalink notes:

Note 269138.1 Oracle Applications Framework Developer's Guide 11.5.10 RUP5

Note 858398.1 Oracle Applications Framework Developer's Guide 11.5.10 RUP7

Unfortunately, I was unable to locate a version for RUP6.

Thursday, November 12, 2009

How to start trace of an existing session in 10g

10g has a new package called DBMS_MONITOR

EXECUTE DBMS_MONITOR.SESSION_TRACE_ENABLE(&sid,&serial,waits=>true, binds=>false);

To disable
EXECUTE DBMS_MONITOR.SESSION_TRACE_DISABLE(&sid,&serial)

My old article on tracing

http://oracleappstechnology.blogspot.com/2007/07/activate-extended-sql-trace-for-given.html

Tuesday, November 10, 2009

Account setup is still in progress. Please come back later.

We were informed by Oracle that Classic Metalink will retire this weekend and everyone has to start using the new metalink or My Oracle Support. They sneaked in Oracle SSO (OTN user id) integration sometime back. I set up the relationship between my metalink id and existing Oracle SSO (OTN id). During the weekend, we had some issues and I was not able to login with my Oracle SSO (OTN id). It gave this message:

Account setup is still in progress. Please come back later.

I tested with different browsers:

IE6 gives this error:

My Oracle Support Serivces Error

A server connection error occurred.

IO Error Error #2032

Please Try again later.

Firefox, Chrome and Opera give this error:
Account setup is still in progress. Please come back later.

After googling for sometime I found that the new site has an html interface which can be accessed through

http://supporthtml.oracle.com

I tried logging in through this and was successful.

I called Oracle Support and they have logged in an SR on my behalf for this issue. They told me that it is a known issue and a lot of users are facing this. They will inform me via email, when this gets resolved.

Sunday, November 8, 2009

How to install lsof version 4.8

In Solaris 10, lsof version 4.7 and lower gives incorrect results. To get correct results from lsof you must install lsof version 4.8 or higher.

First download lsof version 4.8 from sunfreeware
ftp://ftp.sunfreeware.com/pub/freeware/sparc/10/lsof_1106-4.80-sol10-sparc-local.gz

Login as root

Remove the existing lsof version which is lower than 4.8

# pkgrm SMClsof

# pkgadd -d lsof_1106-4.80-sol10-sparc-local

The following packages are available:
1 SMClsof lsof
(sparc) 4.80

Select package(s) you wish to process (or 'all' to process
all packages). (default: all) [?,??,q]: all

Processing package instance from

lsof(sparc) 4.80
Vic Abell
Using as the package base directory.
## Processing package information.
## Processing system information.
3 package pathnames are already properly installed.
## Verifying disk space requirements.
## Checking for conflicts with packages already installed.

The following files are already installed on the system and are being
used by another package:
/usr/local/doc
/usr/local/man
/usr/local/man/man8

Do you want to install these conflicting files [y,n,?,q] y
## Checking for setuid/setgid programs.

The following files are being installed with setuid and/or setgid
permissions:
/usr/local/bin/lsof
/usr/local/bin/sparcv7/lsof
/usr/local/bin/sparcv9/lsof

Do you want to install these as setuid/setgid files [y,n,?,q] y

Installing lsof as

## Installing part 1 of 1.
/usr/local/bin/lsof
/usr/local/bin/sparcv7/lsof
/usr/local/bin/sparcv9/lsof
/usr/local/doc/lsof/00.README.FIRST
/usr/local/doc/lsof/00CREDITS
/usr/local/doc/lsof/00DCACHE
/usr/local/doc/lsof/00DIALECTS
/usr/local/doc/lsof/00DIST
/usr/local/doc/lsof/00FAQ
/usr/local/doc/lsof/00LSOF-L
/usr/local/doc/lsof/00MANIFEST
/usr/local/doc/lsof/00PORTING
/usr/local/doc/lsof/00QUICKSTART
/usr/local/doc/lsof/00README
/usr/local/doc/lsof/00TEST
/usr/local/doc/lsof/00XCONFIG
/usr/local/doc/lsof/lsof.man
/usr/local/man/man8/lsof.8
[ verifying class ]

Installation of was successful.
# lsof -v
lsof version information:
revision: 4.80
latest revision: ftp://lsof.itap.purdue.edu/pub/tools/unix/lsof/
latest FAQ: ftp://lsof.itap.purdue.edu/pub/tools/unix/lsof/FAQ
latest man page: ftp://lsof.itap.purdue.edu/pub/tools/unix/lsof/lsof_man
configuration info: 64 bit kernel
constructed: Thu Jul 10 20:12:17 EDT 2008
constructed by and on: steve@v480
compiler: gcc
compiler version: 3.4.3 (csl-sol210-3_4-branch+sol_rpath)
compiler flags: -Dsolaris=100000 -DHASPR_GWINDOWS -m64 -DHASIPv6 -DHASSOUXSOUA -DHAS_IPCLASSIFIER_H -DHAS_CRED_IMPL_H -DHAS_V_PATH -DHASZONES -DHAS_ZFS=1 -DHASUTMPX -DHAS_VSOCK -DHAS_STRFTIME -DLSOF_VSTR="5.10" -O
loader flags: -L./lib -llsof -lkvm -lelf -lsocket -lnsl
system info: SunOS v480 5.10 Generic_118833-33 sun4u sparc SUNW,Sun-Fire-480R Solaris
Anyone can list all files.
/dev warnings are enabled.
Kernel ID check is enabled.
WARNING: privately defined ZFS structures may be incorrect.
Device cache file read-only paths:
Named via -D: none
Named in environment variable LSOFDEVCACHE: none
Personal path format (HASPERSDC): "%h/%p.lsof_%L"
Modified personal path environment variable: LSOFPERSDCPATH
LSOFPERSDCPATH value: none
Personal path: /.lsof_server1000
Device cache file write paths:
Named via -D: none
Named in environment variable LSOFDEVCACHE: none
Personal path format (HASPERSDC): "%h/%p.lsof_%L"
Modified personal path environment variable: LSOFPERSDCPATH
LSOFPERSDCPATH value: none
Personal path: /.lsof_server1000

Friday, November 6, 2009

Configurator performance issue in Order Entry

Today, Bimal pinged me about a performance issue with Configurator in Order Entry. He told me that the page CZInitialize.jsp was taking 7 minutes to appear. In a different instance on the same server it was taking 30 seconds. I asked him whether he had collected statistics for ONT. He said he had collected for ONT and APPLSYS.

I searched in Metalink with keywords Configurator performance Order Entry and came across Note 130511.1 which gives this suggestion:

The first step in troubleshooting performance problems is to ensure that the customer has recently gathered statistics and that it was done correctly. The following schemas may impact performance in the OM product suite: ONT, WSH, QP, INV, AK, MRP, HZ, CZ, APPLSYS.

I told Bimal about this and he executed the following commands:

exec fnd_stats.gather_schema_statistics('ONT') ;
exec fnd_stats.gather_schema_statistics('WSH') ;
exec fnd_stats.gather_schema_statistics('QP') ;
exec fnd_stats.gather_schema_statistics('INV') ;
exec fnd_stats.gather_schema_statistics('AK') ;
exec fnd_stats.gather_schema_statistics('MRP') ;
exec fnd_stats.gather_schema_statistics('HZ') ;
exec fnd_stats.gather_schema_statistics('CZ') ;
exec fnd_stats.gather_schema_statistics('APPLSYS') ;

The issue was resolved immediately. Always check for statistics as a cause for performance problems in a CBO world.


Thursday, October 29, 2009

How to find the no. of cores in a Solaris box

I have finally found how to find the no. of cores in a Solaris Box on newer T class servers. The clue was given on Steve Sistare's blog post CPU to Core Mapping. He mentioned that:



"psrinfo -pv" only prints the core information on systems running OpenSolaris or Solaris Express, because psrinfo was enhanced by this CR:



6316187 Need interface to determine core sharing by CPUs

which was never backported to a Solaris 10 update.



So I googled for psrinfo opensolaris and found this perl script.



Here's the ouptut of running this perl script on a T5220 server:




$ perl psrinfo.pl -pv

The physical processor has 4 cores and 13 virtual processors (-50)


Another command to get the number of cores is:

kstat cpu_infogrep core_idsort -uwc -l



The core has 4 virtual processors (0-3)

The core has 3 virtual processors (16-18)

The core has 3 virtual processors (32-34)

The core has 3 virtual processors (48-50)

UltraSPARC-T2 (clock 1415 MHz)




Tuesday, October 27, 2009

Warning: log write time

After upgrading to 10.2.0.4, we started seeing messages in alert log

*** 2009-10-27 11:12:26.575
Warning: log write time 590ms, size 22KB

Metalink Note 601316.1 describes this issue. LGWR starts producing trace files with this kind of information:

Elapsed times include waiting on following events:

Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 3 0.00 0.00
SQL*Net message from client 3 0.00 0.00
db file sequential read 7080 3.83 196.43
latch: session allocation 1 0.00 0.00
read by other session 2 0.53 0.59
latch free 1 0.00 0.00

*** 2009-10-27 11:12:26.575
Warning: log write time 590ms, size 22KB
*** 2009-10-27 11:12:28.294
Warning: log write time 610ms, size 43KB
*** 2009-10-27 11:12:30.257
Warning: log write time 570ms, size 69KB
*** 2009-10-27 11:12:47.335
Warning: log write time 700ms, size 2213KB
*** 2009-10-27 11:14:02.255
Warning: log write time 560ms, size 1372KB
*** 2009-10-27 11:14:03.104
Warning: log write time 850ms, size 2823KB
*** 2009-10-27 11:16:10.756
Warning: log write time 640ms, size 261KB
*** 2009-10-27 11:16:51.149
Warning: log write time 500ms, size 1703KB
*** 2009-10-27 11:16:51.800
Warning: log write time 650ms, size 3317KB

As per the note

These messages are very much expected in 10.2.0.4 database in case the log write is more than 500 ms.

This is a warning which means that the write process is not as fast as it intented to be. So probably you need to check if the disk is slow or not or for any potential OS causes. If everything looks fine at the hardware level or OS level then you can safely ignore these messages.

The trace file can easily be deleted or truncated.

Wednesday, October 7, 2009

How to download password protected patch from updates.oracle.com

Akhilesh asked me this question today. A metalink search revealed note 731257.1 which has the details. In short before issuing the get command you are required to issue the command site protected .

In fact if you directly issue the get command you get the error message which says the same:

ftp> bin
g200 TYPE changed to I.
ftp> et p8995444_11i_d.zip
200 PORT command OK.
550-File or directory not found.
550-This is a password protected patch.
550-Enter the password with site command:
550-quote site protected
550 Then get the patch again.

After issuing the site protected command it worked

ftp> site protected V5pIHQqk
200 The password was received successfully.
ftp> get p8995444_11i_d.zip
200 PORT command OK.
150 Opening BINARY mode data connection for file p8995444_11i_d.zip.

It worked with quote site protected command also:

ftp> quote site protected V5pIHQqk
200 The password was received successfully.
ftp> get p8995444_11i_d.zip
200 PORT command OK.
150 Opening BINARY mode data connection for file p8995444_11i_d.zip.

This is of limited use now as Oracle is decomissioning their ftp service and you'll need to use wget and URL from the patch download button in new metalink.

Tuesday, September 29, 2009

R12.1.1 Apache won't start libopmnoraclenls.so unable to load

After R12.1.1 install, Apache wouldn't start and we had these errors in the log

libopmnoraclenls.so unable to load

One of the metalink notes advised running genclntsh on IAS_ORACLE_HOME.

$ ./genclntsh -32
cat: cannot open $IAS_ORACLE_HOME/lib32/ldflags
Undefined first referenced
symbol in file
nnfhboot $IAS_ORACLE_HOME/lib32/libn10.a(nnfgt.o)
nnflboot $IAS_ORACLE_HOME/lib32/libn10.a(nnfgt.o)
nnflgapc $IAS_ORACLE_HOME/lib32/libnl10.a(nlpa.o)
nnftboot $IAS_ORACLE_HOME/lib32/libn10.a(nnfgt.o)
nnflgav $IAS_ORACLE_HOME/lib32/libnl10.a(nlol.o)
nnflfrm $IAS_ORACLE_HOME/lib32/libnl10.a(nlol.o)
nnflrne $IAS_ORACLE_HOME/lib32/libnl10.a(nlol.o)
ntusini $IAS_ORACLE_HOME/lib32/libn10.a(ntcontab.o)
ntpini $IAS_ORACLE_HOME/lib32/libn10.a(ntcontab.o)
nttini $IAS_ORACLE_HOME/lib32/libn10.a(ntcontab.o)
ntzini $IAS_ORACLE_HOME/lib32/libn10.a(ntcontab.o)
ld: fatal: Symbol referencing errors. No output written to $IAS_ORACLE_HOME/lib32/libclntsh.so.10.1


We went through release notes for 12.1.1 present in Metalink note 798258.1 again and
unzipped the Rapidwiz patch 8626041 in stage and reinstalled, but we still faced the same issue.

One of the metalink notes mentioned recreating softlinks, so I checked the soft links and found that Oracle stage had hardcoded softlinks pointing to a mount name /d4/R12/ab/apps/R1211XB9 most probably on Oracle's test servers.

$ ls -ltr |grep d4
lrwxrwxrwx 1 applmgr dba 56 Sep 29 16:58 ldflags -> /d4/R12/ab/apps /R1211XB9/apps/tech_st/10.1.3/lib/ldflags
lrwxrwxrwx 1 applmgr dba 66 Sep 29 16:59 libocci.so -> /d4/R12/ab/a pps/R1211XB9/apps/tech_st/10.1.3/lib32/libocci.so.10.1


$ ls -ltr |grep d4
lrwxrwxrwx 1 applmgr dba 63 Sep 29 16:58 hsdb_odbc.so -> /d4/R12/ab/apps/R1211XB9/apps/tech_st/10.1.3/lib32/hsdb_odbc.so
lrwxrwxrwx 1 applmgr dba 63 Sep 29 16:58 hsdb_oing.so -> /d4/R12/ab/apps/R1211XB9/apps/tech_st/10.1.3/lib32/hsdb_oing.so
lrwxrwxrwx 1 applmgr dba 62 Sep 29 16:58 hsdb_ora.so -> /d4/R12/ab/apps/R1211XB9/apps/tech_st/10.1.3/lib32/hsdb_ora.so
lrwxrwxrwx 1 applmgr dba 62 Sep 29 16:58 hsdb_syb.so -> /d4/R12/ab/apps/R1211XB9/apps/tech_st/10.1.3/lib32/hsdb_syb.so
lrwxrwxrwx 1 applmgr dba 62 Sep 29 16:59 libnavhoa.a -> /d4/R12/ab/apps/R1211XB9/apps/tech_st/10.1.3/lib32/libnavhoa.a
applmgr@tsgsd2106 $ pwd
$IAS_ORACLE_HOME/lib

Once we removed these soft links and pointed them to the existing mounts, we were able to run relink all on 10.1.2 and 10.1.3 homes.

Apache started without issues after we did relink all on 10.1.3 IAS_ORACLE_HOME.

Monday, September 21, 2009

unable to get pty!

Recently on a Solaris 8 box when I executed this command, I go the error unable to get pty

$ /usr/dt/bin/dtterm &
[1] 7507
$ grantpt: Error 0
Warning:
Name: dtTerm
Class: DtTerm
unable to get pty

A google search returned this link from docs.sun.com:

http://docs.sun.com/app/docs/doc/806-1075/6jacsnin4?a=view

unable to get pty!

Cause
When trying to open a Terminal window (dtterm) in CDE, a pop-up window appears stating, Unable to get pty!

dtterm is not able to open /dev/pts/int (where int is an integer). The user cannot open this file because grantpt(3C) failed to change the permissions on the file. grantpt(3C) failed because the binary /usr/lib/pt_chmod is not setuid root. The permissions on /usr/lib/pt_chmod must be 4111.

Action
To restore the correct permissions to pt_chmod, use the following command (as root):

# chmod 4111 /usr/lib/pt_chmod

So I logged in as root and executed the above command.

Issue was resolved.

Monday, September 14, 2009

You are not authorized to access this site.

On accessing Extranet URL from Internet, we got this message:

Access Denied

You are not authorized to access this site.

The application you are attempting to access requires additional authorization. If you feel you have received this message in error, please contact your local business Help Desk.

This error is coming because we have SSO enabled our Extranet also. If the user exists in the SSO database and doesn't exist in the FND_USER table of Oracle E-Business Suite, this error would appear.

After creating the users locally in E-Business Suite, this error should disappear.

Friday, September 11, 2009

ORA-01031: insufficient privileges

Recently we implemented a third party software for Mobile users called Ventureforth Mi2k Server. After doing the install which creates a custom schema and creates packages which call Oracle API to insert, update, delete data into APPS, we got this error when they executed a test call:

ORA-01031: insufficient privileges

We had a tough time figuring out which object didn't have the grant. After a lot of trials through Toad debugging and tracing through DBMS_SYSTEM, I recalled that we could trap any error message in alert log and get more details about the error by using this command:

alter system set events '1031 trace name errorstack level 3';

Here 1031 is ORA-01031. You can put any error code in this command by removing the 0 prefix from the error code. Once you execute this command as SYS/SYSTEM, anytime ORA-1031 is issued by the database, the alert log will record this error and a trace file will be generated to give more details about the error.

Once we did this, we immediately came to know that it was failing when the API was calling UPDATE APPS.JTF_TASK_ALL_RESPONSIBILITIES which was a synonym to JTF.JTF_TASK_RESPONSIBILITIES. The custom schema had rights on JTF.JTF_TASK_RESPONSIBILITIES but not on JTF_TASK_ALL_RESPONSIBILITIES synonym. So we granted the rights:

grant all on APPS.JTF_TASK_ALL_RESPONSIBILITIES TO I2K;

After this the error disappeared.

Monday, August 24, 2009

How to get to Proxy settings dialog box from command line

Internet Explorer proxy settings dialog box is usually accessed with this navigation:

Start Internet Explorer
Click on Tools Menu > Internet Options
Connections Tab
Lan Settings button

You can also access this directly from command line by this:

Start Button > Run > inetcpl.cpl

Friday, August 21, 2009

AC-00002: Error: Unable to create log file

Makarand was doing a Fresh R12 install. The install was successful on the Database Node and Primary Application Node. On Primary Application node only 'Batch Processing Services' are enabled.

However while installing on the additonal application node the rapidwiz was giving errors

'AC-00002: Error: Unable to create log file - $INST_TOP/$CONTEXT_NAME/logs/08031631.log
Exception in thread "AWT-EventQueue-0" java.lang.RuntimeException: AC-00002: Unable to create log file -
$INST_TOP/$CONTEXT_NAME/logs/08031631.log

Makarand had tried installing multiple times but every time it failed with the same message

The truss of the rapidwiz process showed this:

/40: open64("$INST_TOP/$CONTEXT_NAME/logs/08200953.log",
O_WRONLY|O_APPEND|O_CREAT, 0666) Err#13 EACCES


Here’s the reason for the failure:

apps11i is the app tier and you are trying to write to an NFS mount. If you access an NFS mount from a server other than its origin, the root user of the application server is treated as nobody user. I verified this by touching a file while logged in as root:

-rw-r--r-- 1 nobody nobody 0 Aug 20 10:59 abc

This happens if the NFS share is shared without root= in the NFS mount command.

An examination of the /etc/dfs/dfstab file revealed that:

share -F nfs -o rw=visionerp.justanexample.com,root=visionerp.justanexample.com /gpshpc76/custom
share -F nfs -o rw=erpr12.justanexample.com,root=erpr12.justanexample.com /erpr12/erpapp
share -F nfs -o rw=erp11i.justanexample.com,root=erp11i.justanexample.com /erp11i/erpapp

If you compare the last line with a line for other instances it clearly shows that the application server has no read write rights and no root equivalence. I corrected this by executing this command:

share -F nfs -o rw=apps11i.justanexample.com,root=apps11i.justanexample.com /arpsysd1/erpapp

I also corrected it in the /etc/dfs/dfstab.

After this we retried the rapidwiz and it worked.

Wednesday, August 19, 2009

File Either has No Version or Unknown error(-4)

While running Update Current Snapshot during migration to shared application file system the DBAs reported this error:

“Update current view snapshot” throwing following error message.

Done processing object modules

File "INVRCVVB.pls"

Either has No Version or Unknown error(-4)


Failed in adusnapGetDetailFileInfo()

AD Administration error:

Failed store Snapshot file info

adusnapMaintainSnapshot() : Error calling adusnapUpdateCurrentView()

Error calling adusnapMaintainSnapshot


Backing up restart files, if any......Done.


You should check the file

$APPL_TOP/admin/$TWO_TASK/log/adadmin.log for errors.

The file INVRCVVB.pls in $INV_TOP/sql and $INV_TOP/patch/115/sql had a string _new in its version information:


$ adident Header INVRCVVB.pls.original

INVRCVVB.pls.original:

$Header INVRCVVB.pls 115.107.115100.85_new 2008/03/20 08:51:49 kagupta ship $

After removing the _new from the version information inside the file, the issue was resolved.

Monday, August 17, 2009

MAPI32.DLL is corrupt or the wrong version

Recently, I was helping Ashok in installing Outlook 2003 on his Laptop which had Vista Home Premium running on Windows Vista.

The setup program cancelled with this message:

Cannot start Microsoft Outlook. MAPI32.DLL is
corrupt or the wrong version. This could have
been caused by installing other messaging system.
Please reinstall Outlook.

I found a technote from Microsoft which asked to run windows\system32\fixmapi.exe.

That didn't help.

I found some other posts on the net which advised doing this:

Locate the file MSMAPI32.DLL in Program Files\Common Files\System\MSMAPI\1033
Rename the file to something like MSMAPI32.XXX or MSMAPI32.OLD

This worked for us.

Thursday, August 13, 2009

C program to test open file descriptors

Sun has given this program to test the number of open file descriptors here.


vi fopentestcase.c

#include
#include


#define NoOfFILES 65536

int main()
{
char filename[10];
FILE *fds[NoOfFILES];
int i;

for (i = 0; i < NoOfFILES; ++i)
{
sprintf (filename, "/tmp/%d.log", i);
fds[i] = fopen(filename, "w");

if (fds[i] == NULL)
{
printf("\nNumber of open files = %d. " \
"fopen() failed with error: ", i);
perror("");
exit(1);
}
else
{
fprintf (fds[i], "some string");
}
}
return (0);
}

/usr/local/bin/gcc -o fopentestcase fopentestcase.c

$ ./fopentestcase

Number of open files = 253. fopen() failed with error: Too many open files
$

It errors out on the 254th file. This is because it is a 32 bit program.

Monday, August 3, 2009

oaj2se.exe doesn't install

Michael sent me a mail with a screenshot where it was showing a dialog box with

The publisher could not be verified, Are you sure you want to install this software.

Name: oaj2se.exe

Publisher: Unknown publisher

There were two buttons Install and Don't Install.

The DBAs had recently migrated from jintiator to Sun JRE plugin on this instance. I checked by downloading JRE directly http://dev11i.justanexample.com/OA_HTML/oaj2se.exe

appesx76@tnsd2005 $ file j2se16014.exe.old
j2se16014.exe.old: executable shell script

$ head oaj2se.exe
#!/bin/sh
PATH=/usr/bin:/bin
umask 022

release_comp() {
if [ "$1" = "$2" ] ; then
echo "eq"
else
lrel=`printf "%s\n%s\n" $1 $2 | \
sort -t . -k 1,1n -k 2,2n -k 3,3n -k 4,4n -k 5,5n | \
appesx76@tnsd2005 $ head -100 j2se16014.exe.old
#!/bin/sh
PATH=/usr/bin:/bin
umask 022

release_comp() {
if [ "$1" = "$2" ] ; then
echo "eq"
else
lrel=`printf "%s\n%s\n" $1 $2 | \
sort -t . -k 1,1n -k 2,2n -k 3,3n -k 4,4n -k 5,5n | \
head -1`
if [ "$1" = "${lrel}" ]; then
echo "lt"
else
echo "gt"
fi
fi
}
.
.
.

This was strange. That file is supposed to be binary. So I downloaded Sun JRE client 1.6.0_14. Sure enough the file sizes differed. I uploaded the file and did these steps:

Login as applmgr on the web tier

cp jre-6uX-windows-i586-p.exe $COMMON_TOP/util/jinitiator/j2se160x.exe
X is the latest update version, for example:

mv jre-6u14-windows-i586-p.exe $COMMON_TOP/util/jinitiator/j2se16014.exe

cp $COMMON_TOP/util/jinitiator/j2se16014.exe $OA_HTML/oaj2se.exe

I stopped Apache.

Cleared Apache Cache: rm -rf $COMMON_TOP/_pages

Restarted Apache.

The problem was resolved. I informed the DBA team about this issue so that they could avoid this in future. Most probably the DBA got mixed up between Sun JRE migration and JDK upgrade. Sun JRE migration requires JRE package on client PC, which runs on Windows. Sun JDK upgrade requires JDK which runs on server, which is Solaris SPARC 10 in our case.



Thursday, July 30, 2009

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.

Wednesday, July 29, 2009

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.

Tuesday, July 28, 2009

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.

Monday, July 27, 2009

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.

Thursday, July 23, 2009

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

Tuesday, July 21, 2009

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

Wednesday, July 15, 2009

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

Tuesday, July 14, 2009

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)

Thursday, July 9, 2009

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.

Wednesday, July 8, 2009

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:

Monday, July 6, 2009

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

Wednesday, July 1, 2009

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.

Tuesday, June 30, 2009

Error Message: OutOfMemory What does it mean ?

In the SUN virtual machine there are two reasons why an OutOfMemoryError might occur, because the SUN VM uses two different memory blocks for allocating objects and for loading classes. So possible reasons are:

1. The VM cannot allocate memory for a new object because there is no space in the heap available.

2. A classloader tries to load a new class but cannot do so because
the perm space of the memory is full.

Monday, June 29, 2009

RMAN works best with RBO

Eventhough Oracle forced everyone to move to Cost Based Optimizer (CBO), their own tools continue to use RBO. RMAN is a classic example. Refer to Metalink Note 375386.1 which describes performance problems selecting from V$RMAN_STATUS. The cause is given as bug 5247609 and workaround provided is:

Add the following to your rman script:

sql "alter session set optimizer_mode=RULE";

A few customers have found that using RULE does not workaround this but gathering fixed statistics does:

SQL> exec dbms_stats.gather_fixed_objects_stats()

Friday, June 26, 2009

Oracle and the Law of Attraction

If you have seen the movie "The Secret", you would be well aware of Law of Attraction. One of the tenets of Law of Attraction is that you should always focus on what you want and never on what you do not want. The universe is all inclusive and doesn't exclude. So if you say you want money, you'll get money, but if you say I don't want poverty, you'll get poverty. If you say I don't want disease, you get disease. So you should say that I want wellness instead of saying I don't want disease.

During the HotSoS Oracle performance management, I learnt that Oracle's optimizer also follows similar principle.

The clauses NOT EXISTS and NOT IN take more time and do more LIOs. This is because the optimizer will grab all rows and check each row for the NOT condition and drop it. This always takes more time. For example

select name,salary from emp where salary <>50000 is going to take more time than

select name,salary from emp where salary < 50000

You should avoid these NOT clauses and try to create your query with EXISTS or IN.

Thursday, June 25, 2009

Hotsos PRO training Day 3/3

Today we learnt how to read raw trace files and manually calculate what HotSoS profiler does programmatically.

Metalink Note 39817.1 explains the same. In Oracle 10g we have a new package DBMS_MONITOR through which trace should be done.

We learnt to analyze trace files and did various exercises with profiler reports.

Ric said, he has stopped working with tkprof ever since he got introduced to HotSos profiler and that if he ever were to go independent, he would sell his soul for keeping the HotSos profiler in his bag of tools.

HotSoS profiler is the best. I have compared the outputs of other profilers like Oracle's trace analyzer and OraSRP. However none of the alternatives provides the depth of HotSoS profiler.

Hotsos PRO training Day 2/3

Today we learnt how to collect trace data with proper instrumentation, Hotsos free package ILO for implementing instrumentation in PL/SQL code was shown. We learnt how to read trace files.

One of the students, Rob had brought a production trace file. Our instructor, Ric, generated the profiler report for that trace file. There were 3 statements:

1. UPDATE 30% time
2. INSERT 25% time
3. UPDATE 27% time

Ric said that the first statement looked optimized as it was doing 1 LIO per execution which is very good. However, it was doing this thousands of time. In HotSos World, the best way to optimize something is not to do it. Rob explained that they were updating all the rows with very same data everytime. Ric told Rob to explore MERGE command which will combine INSERT and UPDATE and avoid the unnecessary UPDATE of very same data again.

Tuesday, June 23, 2009

Hotsos PRO training Day 1/3

I am attending the world famous 3 day training on Oracle Performance from Hotsos. Our instructor is Ric Van Dyke. We have 6 students in the class. Ric mentioned that he was our instructor and not a teacher as an instructor presents the information, but a teacher makes sure that the student learns whatever is taught. 3 days is a short period to teach.

7 years back when I was in New Delhi, India, I had read Cary Milsap's book Optimizing Oracle Performance from cover to cover. It had enlightened me about Oracle performance.

Here are some of the things we learnt today apart from the regular curriculum:

1. For each row returned you should touch 10 or fewer blocks
2. No. of Logical IOs = No. of tables in a query x No. of rows x 10
3. Nested loops work great with small data sets.

One of the students asked Ric "How do we find out what a query is doing if it is taking a dirt nap" and Rick said that he had never heard of the term "dirt nap". I also never heard it.

Apparently "dirt nap" is a colloquial term which means dead. A dirt nap is when you die, you take the big dirt bath to be buried underground. similar to death.

Thursday, June 18, 2009

Oracle's v$ views

Vinu asked me whether we can insert a new directory for utl_file_dir into v$parameter. I explained to him that v$views are not tables and are synonyms of v_$ dynamic performance views. These views are available even before database is in OPEN state. Although these views appear to be regular database tables, they are not. These views provide data on internal disk structures and memory structures. These views can be selected from, but never updated or altered by the user.

The file $ORACLE_HOME/rdbms/admin/catalog.sql contains definitions of the views and public synonyms for the dynamic performance views.

Once the instance is started, the V_$ views that read from memory are accessible. Views that read data from disk require that the database be mounted.

You can find the description of the v$ synonyms here.

Monday, June 15, 2009

Classic Metalink to be retired by end of 2009

Oracle has announced that classic metalink is going to be retired by end of 2009. I don't like the new metalink much as it times out regularly and is not very intuitive. However, what must be must be. Oracle has a metalink note 838708.1 which has information about what you can do to prepare in advance. Adobe Flash player 9 is a pre-requisite and needs to be installed.

I have tried using the new metalink or "My Oracle Support", however I get frustrated when I am not able to do simple tasks. I'll have to get used to it, as Oracle will retire classic metalink which I access through metalink2.oracle.com now.

Wednesday, June 10, 2009

Xms Xmx and PermSpace

Guru asked me this question today about the Xms Xmx and Permspace parameters passed to java programs.

-Xms is the initial heap size and

-Xmx is the maximum

PermSpace is the area of the VM that is used to store data structures
and class information (not instances, but the class definitions
themselves).

From: http://forums.sun.com/thread.jspa?threadID=775925

Keep in mind that the permanent area is not a subset of the heap.
The pictures on the page http://java.sun.com/docs/hotspot/gc/index.html
erroneously show permspace as a subset of the heap defined by -Xmx
parameter. This is a bug in the documentation (state February 2004).

What is Cost ?

Alok asked this question today. The cost was coming as 231 in an explain plan. The best answer is given by Jonathan Lewis in his book "Cost Based Oracle". Chapter 1 of the book is called "What is Cost ?" He has defined it as:

The cost is the time spent on single-block reads, plus the time spent on multiblock reads, plus the CPU time required, all divided by the time it takes to do a single-block read. Which means the cost is the total predicted execution time for the statement, expressed in units of the single-block read time.

You can read the addenda on that chapter which has information about cost here.

Cost is time (30th Dec 2005)
Page 3, last complete paragraph. Following my claim that ‘cost’ really is ‘estimated time to run’, I explain that Oracle 9i chooses the sreadtim as the unit of time measurement for backwards compatibility so that the cost reported by 9i will generally be close to the cost reported by 8i for small, precise OLTP activity.

Another way of thinking about this is as follows:

By the definition in the 9.2 Performance Tuning Guide:

Cost = (
#SRds * sreadtim +
#MRds * mreadtim +
#CPUCycles / cpuspeed
) / sreadtim
This says the cost is: “the time to estimated run in milliseconds, divided the sreadtim”. But in 8i, Oracle assumes that all read requests take the same time, and that CPU doesn’t count; in other words sreadtim = mreadtim, and CPUCycles = 0. So what happens if you put those assumptions into the 9i formula:

Cost = (
#SRds * sreadtim +
#MRds * sreadtim +
0
) / sreadtim = #SRds + #MRds
So the 9i formula gives the 8i result when you apply the 8i assumptions. In other words, Oracle 9i estimates the time to run, then divides by the sreadtim so that a query that doesn’t involved multi-block reads will show (nearly) the same cost in 8i and 9i.

As a final thought, the execution plan that you get from dbms_xplan.display() in 10g includes a time column. Here’s the execution plan for a simple query to count the rows in a table. Note that the execution plan is a full tablescan, doing multi-block reads only, but that I’ve modified the single-block read time before generating the execution plan:

/* execute dbms_stats.set_system_stats('SREADTIM',10) */
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 329 (1)| 00:00:04 |
| 1 | SORT AGGREGATE | | 1 | | | |
| 2 | TABLE ACCESS FULL| T2 | 48000 | | 329 (1)| 00:00:04 |
---------------------------------------------------------------------------
And here is it again, just moments later:

/* execute dbms_stats.set_system_stats('SREADTIM',2) */
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1641 (1)| 00:00:04 |
| 1 | SORT AGGREGATE | | 1 | | | |
| 2 | TABLE ACCESS FULL| T2 | 48000 | | 1641 (1)| 00:00:04 |
---------------------------------------------------------------------------
What’s the difference between these two runs that could cause such a change in the cost ? The fact that I changed the optimizer’s assumption about the time it would take for a single block read to complete. (By scaling the single block read down by a factor of five, I effectively scaled the multiblock read time up by a factor of five – so this query, which did nothing but multiblock reads increased in cost by a factor of five).

Notice that even though the cost of the query has changed dramatically the time for the query to complete has not changed. So how has the optimizer calculated the time? It’s simply taken the 9i formula and multiplied the sreadtim back in to get back to the original time. Go back to my settings for the sreadtim statistic.

In the first case (10 milliseconds): ceiling (329 * 10/1000) = 4, hence the four second time.

In the second case (2 milliseconds): ceiling(1641 * 2/1000) = 4, hence the same four second time.

Cost is Time – but the units are a bit funny.

- Jonathan Lewis

Tuesday, June 9, 2009

Aliases or directories which can be accessed from 11i URL

Anand asked me the direct link to jinitiator for an instance. I launched a forms session and the window which launches the JVM had this text:

In order to access this application, you must install the Oracle JInitiator Plugin version 1.3.1.29. To install this plugin, click here to download the oajinit.exe executable. Once the download is complete, double-click the oajinit.exe file to install the plugin. You will be prompted to restart your browser when the installation is complete.

The click here was a hyperlink pointing to http://www.justanexample.com/jinitiator/oajinit.exe

To find out which directory on the server was pointing to /jinitiator I did this:

cd $IAS_CONFIG_HOME/Apache/Apache/conf
grep jinitiator *.conf

apps.conf:Alias /jinitiator/ "/gpscd140/erpapp/comn/html/"
apps.conf:Location /jinitiator/
apps.conf:Location ~ /(OA_HTML|html|jinitiator)/env.txt*
apps.conf:Location ~ "/(OA_HTML|html|jinitiator)/oam/"
apps.conf:Location ~ "/(OA_HTML|html|jinitiator)/oam/helpdoc/"
apps.conf:Location ~ "/(OA_HTML|html|jinitiator)/oam/images/"
apps.conf:Location ~ "/(OA_HTML|html|jinitiator)/oam/nonUix/launchMode/"
trusted.conf:# To add /html & /jinitiator as directories accessed by trusted nodes and remove /xsql/admin/xml.properties bug # 3851839
trusted.conf: Location ~ "/(OA_HTML|html|jinitiator)/bin/appsweb*"
url_fw.conf:#RewriteRule ^/jinitiator/oajinit\.exe$ - [L]

So then I opened apps.conf and found all the aliases:

Alias /OA_JAVA/ "$COMMON_TOP/java/"
Location /OA_JAVA/
Order allow,deny
Allow from all
/Location

Alias /OA_HTML/ "$COMMON_TOP/html/"
Location /OA_HTML/
Order allow,deny
Allow from all
/Location

Alias /OA_SECURE/ "$COMMON_TOP/secure/"
Location /OA_SECURE/
Order allow,deny
Allow from all
/Location

Alias /media/ "$COMMON_TOP/java/oracle/apps/media/"
Location /media/
ExpiresActive on
ExpiresDefault "now plus 1 day"
Order allow,deny
Allow from all
/Location

Alias /html/ "$COMMON_TOP/html/"
Location /html/
Order allow,deny
Allow from all
/Location

Alias /OA_MEDIA/ "$COMMON_TOP/java/oracle/apps/media/"
Location /OA_MEDIA/
ExpiresActive on
ExpiresDefault "now plus 1 day"
Order allow,deny
Allow from all
/Location

Alias /OA_TEMP/ "$COMMON_TOP/temp/"
Location /OA_TEMP/
Order allow,deny
Allow from all
/Location

Alias /jinitiator/ "$COMMON_TOP/html/"
Location /jinitiator/
Order allow,deny
Allow from all
/Location

Alias /dev60html/ "$ORACLE_HOME/tools/web60/html/"
Location /dev60html/
Order allow,deny
Allow from all
/Location

Alias /forms60java/ "$ORACLE_HOME/forms60/java/"
Location /forms60java/
Order allow,deny
Allow from all
/Location

Alias /dev60temp/ "$ORACLE_HOME/tools/web60/temp/"
Location /dev60temp/
Order allow,deny
Allow from all
/Location

Monday, June 8, 2009

Query to check characterset of your DB

A character set is the mapping of specific set of characters to binary values. In 8-bit character sets like ASCII, the values range from 0-255 and one character will be mapped to each (or most of) these values. This means that if your terminal is set to support a particular character set, every time you press a certain key, that key will send its value eg: 65, to the terminal, which will interpret that value as the letter A. It can also be known as an encoding scheme. Microsoft and IBM have traditionally called it a code page.

The query to check the characterset of your Oracle database is:

select value from NLS_DATABASE_PARAMETERS where parameter='NLS_CHARACTERSET';

To enable your E-Business Suite instance for multiple languages, the characterset should be set to UTF8 or higher.

Friday, June 5, 2009

ERP Concurrent User Estimator

I found a very interesting spreadsheet called ERP Software Concurrent User Estimator from TGI on this link.

The formula used by them is:

No. of Concurrent Users = (0.3 x Low Activity Users) + (0.6 x Medium Activity Users) + High Activity Users

This is a good estimator which can be used to come up with number of concurrent users, which is needed for sizing an environment.

Thursday, June 4, 2009

Disabling core dump generation in Oracle Database 10g

In a recent SR with Oracle, the support analyst told us to disable core dumps as all the information captured in core dumps is also present in ORA-7445 trace files.

Metalink Note 397404.1 gives these two methods for disabling core dumps.

Method #1 - Set database parameter SHADOW_CORE_DUMP to NONE.

SHADOW_CORE_DUMP is a static parameter so the database must be restarted in order for the parameter value to be enabled. Setting SHADOW_CORE_DUMP to NONE will block the generation of core dumps from user (client) processes attached to the SGA. Setting this parameter has no impact on core dumps being generated by background processes. It is not possible to set BACKGROUND_CORE_DUMP to NONE. So this method will prevent core dumps from being generated by user processes but will not prevent the generation of core dumps by background processes.

Method #2 - modify the OS ULIMIT
ulimit -c 0

doing this will block core dumps from being generated by all processes executed by this user, which should include all user and background processes on the server. The database will have to be restarted in order for the new ulimit settings to take effect.

R12 Logo customization

Mohan queried me about logo personalization in R12. He had copied custom ORACLE_HR.jpg and FNDSSCORP.gif to $OA_MEDIA and bounced Apache. However custom logo is not displaying on R12 pages
He wanted to know whether he needed to call these two files in any of the file
He had already cleared cache on desktop and server.

The profile option "Corporate Branding Image for Oracle Applications" controls the
corporate branding image "ORACLE" displayed at the top of all OA Framework pages.
If no value is set for this profile, OA Framework renders the corporate branding image by using the $OA_MEDIA/FNDSSCORP.gif file.

If you change the value of this profile with the name of your custom image you will see your logo in all pages except in the login page because the "FNDSSCORP.gif " is hard coded in MainLoginPG.xml.

Metalink Note 741459.1 has some details about logo customization. Hence if you want to change the corporate branding image with your own logo and thus in all pages including the login page the simplest solution is to:

- rename the FNDSSCORP.gif file for backup
- edit the FNDSSCORP.gif with your preferred graphic software
- log out and log into the Applications
- you should see the new logo at the top of all the page (including the login page)

Mohan said that it is null. I asked him whether he replaced the file with his own gif or did he copy it. I mean modified the existing FNDSSCORP.gif after taking backup

In case you want a different logo for the login page then the only (non supported) solution consists in editing the $FND_TOP/mds/sso/login/webui/MainLoginPG.xml file,

Erroring with Product 'Company' is not a currently-installed product

Still working on this one.

Wednesday, June 3, 2009

Forms not launching from self service after migration to new app tier

Raju reported the issue of forms not launching from self service after migration to new desktop. Sure enough it was happening for me too.

192.168.10.188 - - [03/Jun/2009:23:21:22 -0400] "GET /dev60cgi/f60cgi?&appletmode=nonforms&HTMLpageTitle=&HTMLpreApplet=&code=oracle/apps/fnd/formsClient/FormsLauncher.class&width=400&height=100&archive=/OA_JAVA/oracle/apps/fnd/jar/fndforms.jar,/OA_JAVA/oracle/apps/fnd/jar/fndformsi18n.jar,/OA_JAVA/oracle/apps/fnd/jar/fndewt.jar,/OA_JAVA/oracle/apps/fnd/jar/fndswing.jar,/OA_JAVA/oracle/apps/fnd/jar/fndbalishare.jar,/OA_JAVA/oracle/apps/fnd/jar/fndaol.jar,/OA_JAVA/oracle/apps/fnd/jar/fndctx.jar,/OA_JAVA/oracle/apps/fnd/jar/fndlist.jar&gp14=jinit_appletcache&gv14=offjinit_appletcache=off&gp2=resp_app&gv2=SYSADMIN&gp3=resp&gv3=SYSTEM_ADMINISTRATOR&gp4=sec_group&gv4=STANDARD&gp5=function&gv5=FND_FNDCPQCR_SYS&gp6=other_params&gv6=&gp7=forms_url&gv7=http%3A%2F%2F11ioltp.justanexample.com%3A8029%2Fpls%2Ferppgrd2%2Ffnd_icx_launch.runforms%3FICX_TICKET%3D%26resp_app%3DSYSADMIN%26resp_key%3DSYSTEM_ADMINISTRATOR%26secgrp_key%3DSTANDARD%26start_func%3DFND_FNDCPQCR_SYS%26other_params%3D&encoding=UTF-8&gp8=error_url&gv8=http%3A%2F%2Fdev56.justanexample.com%3A8029%2FOA_HTML%2Fjsp%2Ffnd%2Ffnderror.jsp&gp12=port&gv12=6945&gp13=dbc&gv13=dev56&gp15=icx_ticket&gv15=qT_sKHY-DtwzZo6CWStyqg.. HTTP/1.1" 200 42954 0




Do you wish to force regeneration of all jar files? [No] ? Yes


Forcing generation of all product jar files.
Creating and signing every jar file can take about thirty
minutes depending on the hardware being used.
You can watch the file $APPL_TOP/admin/$TWO_TASK/log/adadmin.log to see the progress of jar file generation.

Signing product JAR files in JAVA_TOP -
$OA_JAVA
using entity DEV21_ERPDEV21 and certificate 1.

Successfully created javaVersionFile.

Generating product JAR files in JAVA_TOP -
$OA_JAVA with command:

adjava -mx512m -nojit oracle.apps.ad.jri.adjmx @$APPL_TOP/admin/$TWO_TASK/out/genjars.cmd

After regenerating jar files and logging in as administrator on my laptop, it worked fine. It did not work for Raju as he did not have administrator rights on his laptop.

Concurrent Managers require physical hostname

In our environment we have DB and Concurrent Manager on one box. This box is clustered with Veritas 5.1. We use virtual cluster name for connecting to DB. However we manually add the physical hostnames of all the servers in the cluster.

This is because the Concurrent Managers require the physical hostname to be registered and enabled for Concurrent Processing.

Please refer to Metalink Note 568115.1 for more details.

Tuesday, June 2, 2009

mwactl.sh start gives java.lang.NoClassDefFoundError: oracle/jdbc/driver/OracleStatement

Anand called at 7 AM today to report this error which was coming in a Production environment when Mobile and Wireless service was started with mwactl.sh start:

oracle.apps.mwa.tools.GenerateLauncher.main(GenerateLauncher.java:60)

java.lang.NoClassDefFoundError: oracle/jdbc/driver/OracleStatement

We compared the instance with Development instances. The classpath matched. We even compared the truss and found that in the instances working, it was unable to find OracleStatement.class but was going ahead and looking for other classes.

Later in the day, Ben asked Anand to put jdbc111.zip in the classpath as a workaround. The root cause was also found. Someone had issued the command compress appsbrog2.zip which had converted appsborg2.zip to appsborg2.zip.Z. Uncompressing it and reinstating it resolved the issue.

Monday, June 1, 2009

Oracle.Forms.Engine.Main Not Found

Sandeep gave me a call in the evening today and told me that he is getting this error when he tries to launch forms from self service interface:

Oracle.Forms.Engine.Main Not Found

Metalink Note 465063.1 has this error and advises this:

-- To implement the solution, please execute the following steps::
1. Go to control panel. Jinitiator 1.3.1.18
2. Click on the proxy TAB
3. Enter Proxy name and port
Or
hardcode proxy name in the browser.

It was indeed because of the proxy. Sandeep confirmed this after checking with another user.

Wednesday, May 20, 2009

How to disable automatic SGA tuning in 10g

On many of our production E-Business Suite 11.5.10.2 instances which are on 10.2.0.3 and 10.2.0.4, we have been getting ORA-600 and ORA-7445 errors. In one of the severity one TARs, for these errors, which were occuring when a business critical program was run, Oracle support advised us to disable automatic SGA tuning by setting sga_target=0. Here's an excerpt of the TAR:

ISSUE CLARIFICATION
====================

Multiple errors ORA-7445, ORA-3136 and ORA-4031's


.
ISSUE VERIFICATION
===================

Verified the issue by the trace file

Verified the issue by the alert log file


.
RESEARCH
=========

Point 1: We can see that next to Free memory the KGH: No Access is the biggest memory consumer.

This can been one of two things. The SGA_TARGET is configured too low causing frequent growing and shrinking of the SGA components to accomi
date the needs.

or

There is a known issue where the growing and shrinking is due to no minimums being set for the db_buffer_cache and the Shared_pool_size.

Bug 6528336 Automatic SGA may repeatedly shrink / grow the shared pool
Fixed-Releases: A204 B200

Details:
Alternating frequent shrink and grow of the buffer cache and
shared pool may be seen with automatic memory management enabled
causing various waits in sessions.


.
CAUSE DETERMINATION
====================

Bug 6528336 Automatic SGA may repeatedly shrink / grow the shared pool
Fixed-Releases: A204 B200


CAUSE JUSTIFICATION
====================

Known issue when KGH No Access show as large memory consumer.



.
PROPOSED SOLUTION(S)
======================
List the solution option(s) and/or workarounds here - What will work.

1). Apply the 10.2.0.4.0 patchset to get the full fix for the issue.

- or -

2). Disable ASMM by setting SGA_TARGET=0

- or -

3). Note that even after you have applied a fix, frequent resizing between shared poo
l and buffer cache can still lead to buildup of "KGH: NOACCESS" allocations. The
frequent resizing is an indication that sga_target is too low. Increase sga_tar
get . Also, recommend setting a minimum size for shared pool by setting value f
or parameter shared_pool_size to prevent it being shrunk below that level, thus
reducing chances for ORA-4031 to occur.

- or -

4). alter system set "_memory_broker_stat_interval"=999; --- 999sec between resizes

This will increase the time between resize to atleast 999 seconds.
This will reduce the number of resizes.

_memory_broker_stat_interval is in seconds,By Default it is 30 seconds.
You can set _memory_broker_stat_interval to a larger value







PROPOSED SOLUTION JUSTIFICATION(S)
====================================
Known to resolve the ORA-4031 KGH: No Access.



.
SOLUTION / ACTION PLAN
=======================
To implement the solution, please execute the following steps:


1). Apply the 10.2.0.4.0 patchset to get the full fix for the issue.

- or -

2). Disable ASMM by setting SGA_TARGET=0

- or -

3). Note that even after you have applied a fix, frequent resizing between shared pool and buffer cache can still lead to buildup
of "KGH: NOACCESS" allocations. The frequent resizing is an indication that sga
_target is too low. Increase sga_target . Also, recommend setting a minimum siz
e for shared pool by setting value for parameter shared_pool_size to prevent it
being shrunk below that level, thus reducing chances for ORA-4031 to occur.

- or -

4). alter system set "_memory_broker_stat_interval"=999; --- 999sec between resizes

This will increase the time between resize to atleast 999 seconds.
This will reduce the number of resizes.

_memory_broker_stat_interval is in seconds,By Default it is 30 seconds.
You can set _memory_broker_stat_interval to a larger value


Thanks
Kevin

Hi,

We can disable ASSM by setting sga_target to zero. However that will need us
to manually give the values for:

Database buffer cache (Default pool)
Shared pool
Large pool
Java pool
10gR2 the streams pool

This is as per metalink note 257643.1 which says that the above are set auto by
automatic tuning.

However Metalink Note 216205.1 says that large_pool_size, java_pool_size are
obsolete. Should we set these parameters ?

- Vikram



04-JUN-09 21:02:45 GMT

.
UPDATE
=======


The java_pool_size, and large_pool_size are not obsolete, they are not set manaully when SGA_TARGET is set.

Both are listed in the 10.2. reference guide and are not flagged as obsoleted.


I would suggest it might be easier for you to set minimums fort the shared_pool_size and the db_buffer_cache and leave the SGA_TARGET set.

The choice of all the above solutions is of course up to you.


Thanks
Kevin

We decided to disable automatic SGA tuning and set sga_target=0 and manually set values for db_cache_size, shared_pool_size and java_pool_size.

Section 3.4 of Metalink Note 295626.1 has the details about disabling automatic SGA or ASMM:

3.4 ASMM to Manual

You can revert the ASMM mechanism at any time by setting the SGA_TARGET value to 0.

In this case the current_size will be used by default as shown below:

SQL> alter system set sga_target=0;

System altered.

SQL> 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;

COMPONENT CURRENT_SIZE MIN_SIZE USER_SPECIFIED_SIZE TYPE
------------------------- ------------ ---------- ------------------- -------------
shared pool 104 80 104 GROW
<<<<< 104 has become the current value
large pool 8 8 8 STATIC
java pool 48 48 48 STATIC
streams pool 12 0 12 GROW
DEFAULT buffer cache 24 24 24 SHRINK
KEEP buffer cache 0 0 0 STATIC
RECYCLE buffer cache 0 0 0 STATIC
DEFAULT 2K buffer cache 0 0 0 STATIC
DEFAULT 4K buffer cache 0 0 0 STATIC
DEFAULT 8K buffer cache 0 0 0 STATIC
DEFAULT 16K buffer cache 0 0 0 STATIC
DEFAULT 32K buffer cache 0 0 0 STATIC
OSM Buffer Cache 0 0 24 STATIC

If you shutdown the instance now, you will record the CURRENT_SIZE value for all the parameters.

After the restart, the MIN_SIZE values are equal to CURRENT_SIZE.

SQL> 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;

COMPONENT CURRENT_SIZE MIN_SIZE USER_SPECIFIED_SIZE TYPE
------------------------- ------------ ---------- ------------------
shared pool 104 104 104 STATIC
large pool 8 8 8 STATIC
java pool 48 48 48 STATIC
streams pool 12 12 12 STATIC
DEFAULT buffer cache 24 24 24 STATIC
KEEP buffer cache 0 0 0 STATIC
RECYCLE buffer cache 0 0 0 STATIC
DEFAULT 2K buffer cache 0 0 0 STATIC
DEFAULT 4K buffer cache 0 0 0 STATIC
DEFAULT 8K buffer cache 0 0 0 STATIC
DEFAULT 16K buffer cache 0 0 0 STATIC
DEFAULT 32K buffer cache 0 0 0 STATIC
OSM Buffer Cache 0 0 24 STATIC

It is strongly advised to use an spfile with your your instance as you will record any changes applied.

The ORA-600, ORA-7445 errors have disappeared after disabling automatic SGA tuning.

Tuesday, May 19, 2009

Active Responsibilities log shows Rep-3000 Error

Akhilesh and Makarand called me today. When they ran Active Responsibilities concurrent program, the log showed this error:

ERROR
REP-3000: Internal error starting Oracle Toolkit.

This error was not appearing for Active Users program which completed normal.

Metalink Note 467148.1 describes this problem. The note says that Solaris requires window manager for before the X server is started. We use xfvb as the X server. On checking the server for the windows manager twm, I found that it was not running. After starting the twm process, and then starting xfvb (Xsun), the log was clean. It seems that either the twm process died, or someone killed it by mistake.

Monday, May 18, 2009

Script to get the process which listens on a port number

In Solaris 10, lsof utility doesn't work. Here's a script given by Oracle in Metalink Note 839919.1 to help us:

#!/bin/bash

# Get the process which listens on port

# $1 is the port we are looking for

if [ $# -lt 1 ]
then
echo "Please provide a port number parameter for this script"
echo "e.g. $0 22"
exit
fi

echo "Greping for your port, please be patient (CTRL+C breaks) ... "

for i in `ls /proc`
do
[ -d /proc/$i ] && pfiles $i | grep AF_INET | grep $1
if [ $? -eq 0 ]
then
echo Is owned by pid $i
fi
done

Thursday, May 14, 2009

The Applications File Server could not open the file FNDCPGSC

Anand pinged about this error in concurrent manager logs about Output Post Processor (FNDCOPP):

The Applications File Server could not open the file $APPLCSF/$APPLLOG/FNDCPGSC*txt for read

Starting FNDCPOPP Concurrent Manager : 16-MAY-2009 08:24:59

Found dead process: spid=(999999), cpid=(13200), Service Instance=(5271)

Metalink Note 331942.1 describes this issue. As per Oracle, it is an environment issue and running autoconfig corrects it.

However, we restarted the concurrent managers and it worked fine the second time. Root cause is not yet known.

Wednesday, May 13, 2009

cannot open /etc/mail/local-host-names

Recently I added a new hostname to /etc/mail/local-host-names and bounced sendmail:

svcadm restart sendmail.

But sendmail didn't come up and started logging these errors in /var/adm/messages:

May 13 14:21:50 mailserver1 sendmail[19725]: [ID 801593 mail.crit] NOQUEUE: SYSERR(root): /etc/mail/sendmail.cf: line 80: fileclass: cannot open '/etc/mail/loca
l-host-names': Group writable directory
May 13 14:21:50 mailserver1 sendmail[19725]: [ID 801593 mail.crit] NOQUEUE: SYSERR(root): /etc/mail/sendmail.cf: line 561: fileclass: cannot open '/etc/mail/tru
sted-users': Group writable directory


We engaged unix team and they did something and the error changed to:

May 13 14:33:38 tsgp1403 sendmail[13585]: [ID 801593 mail.alert] n4DHe0KO027983: queueup: cannot create ./tfn4DHe0KO027983, uid=25: File exists
May 13 14:35:34 tsgp1403 sendmail[15873]: [ID 801593 mail.alert] n4DHe0KO027983: queueup: cannot create ./tfn4DHe0KO027983, uid=25: File exists
May 13 14:35:35 tsgp1403 sendmail[15916]: [ID 801593 mail.alert] n4DHe0KO027983: queueup: cannot create ./tfn4DHe0KO027983, uid=25: File exists

Finally they realized that the permissions of /etc were set to 775. Once they changed it, sendmail started.

In a different article on oreillynet, I found the information that sendmail won't work properly if permissions are incorrect on any of these directories:

You have to check the permissions on / and all the subdirectories like /etc, /etc/mail for write permissions. If there is no write permission on / or /etc then give the write permissions and check.

This will set the permissions for every directory sendmail needs to be
non-group writable:

sudo chmod go-w / /etc /etc/mail /usr /var /var/spool /var/spool/mqueue /private

Check all these permissions are same as below.

drwxr-xr-x 45 root sys 3584 Feb 3 10:24 etc
drwxr-xr-x 2 root mail 512 Feb 3 11:54 mail
-rw-r--r-- 1 root bin 153 Sep 30 10:24 Mail.rc
-rw-r--r-- 1 root bin 1201 Sep 30 10:24 aliases
-rw-r--r-- 1 root mail 0 Oct 10 04:58 aliases.dir
-rw-r--r-- 1 root mail 1024 Oct 10 04:58 aliases.pag
-rw-r--r-- 1 root bin 5266 Sep 27 19:07 helpfile
-rw-r--r-- 1 root bin 0 Sep 24 2001 local-host-names
-rw-r--r-- 1 root bin 1829 Sep 30 10:19 mailx.rc
-r--r--r-- 1 root bin 34924 Sep 30 10:24 main.cf
-r--r--r-- 1 root bin 35625 Oct 1 05:09 sendmail.cf
-r--r--r-- 1 root other 35625 Oct 1 05:09
sendmail.cf.pre110615-05
lrwxrwxrwx 1 root root 8 Sep 30 10:24 sendmail.hf ->
helpfile
-r--r--r-- 1 root bin 35625 Sep 30 10:24 subsidiary.cf
-rw-r--r-- 1 root bin 5 Sep 24 2001 trusted-users

Tuesday, May 12, 2009

Check what a session does on OS level

First find the oracle session id of the concurrent program with this sql:

SELECT 'Request id: '||request_id ,
'Trace id: '||oracle_Process_id,
'Trace Flag: '||req.enable_trace,
'Trace Name:
'||dest.value||'/'||lower(dbnm.value)||'_ora_'||oracle_process_id||'.trc',
'Prog. Name: '||prog.user_concurrent_program_name,
'File Name: '||execname.execution_file_name|| execname.subroutine_name ,
'Status : '||decode(phase_code,'R','Running')
||'-'||decode(status_code,'R','Normal'),
'SID Serial: '||ses.sid||','|| ses.serial#,
'Module : '||ses.module
from apps.fnd_concurrent_requests req, v$session ses, v$process proc,
v$parameter dest, v$parameter dbnm, apps.fnd_concurrent_programs_vl prog,
apps.fnd_executables execname
where req.oracle_process_id=proc.spid(+)
and proc.addr = ses.paddr(+)
and dest.name='user_dump_dest'
and dbnm.name='db_name'
and req.concurrent_program_id = prog.concurrent_program_id
and req.program_application_id = prog.application_id
and prog.application_id = execname.application_id
and prog.executable_id=execname.executable_id
and request_id=&request_ID

Get the OS Process id by passing the session id obtained from above query to the query below:

SELECT p.program, p.spid, s.saddr, s.sid, s.serial#, s.username,
s.osuser, s.machine, s.program, s.logon_time, s.status
FROM v$session s, v$process p
WHERE s.paddr = p.addr
AND s.sid = &sid;

The above query will give you the os process id in the column spid. Once you get the os process id, go to unix prompt

truss -p os_process_id

APP-FND-00092: Routine PRINT_OUTPUT cannot find the file

Venkat reported this issue today. Concurrent programs were erroring out with this message:

APP-FND-00092: Routine PRINT_OUTPUT cannot find the file $COMMON_TOP/admin/out/o65905566.out to print.

It turned out that someone had updated the profile option: Concurrent Report:Copies to 1. The value used to be 0.

Once the value was changed back to 0, the error stopped coming.