TIP 82# : Browser craches when Oracle Forms is accessed

If you use internet explorer (Any version) and it crashes at time of accessing Oracle Forms, the following step could resolve the issue.


- Disable 3rd party browser extension (Tools -> Internet Options -> Advanced -> Browsing)
- Deinstall all Jinit versions. (Optional but preferred)
- Cleanup IE cache
- Close IE broswer
- Open IE and access URL


I have tested this with AS 10R2 (10.1.2.3) and IE 6 and IE7 SP2.

TIP #78:Database server gets freezed after increasing memory foot print.

I have a client which went through a switchover practice for the first time.
After switching over to a standby box, I realized a performance issue in terms of IO write.As the result, it was decided to bump up database memory footprint for SGA and PGA since the box has enough memory.
Changes were made on spfile and new SGA was bumped up to 24GB and PGA to 4GB from total 32GB. After bouncing database, database hung in nomount and after a minute,the box was totally freezed which did not allow connection anymore.
The following was reported in Alert log :



ORA-27300: OS system dependent operation:fork failed with status: 12
ORA-27301: OS failure message: Not enough space
ORA-27302: failure occurred at: skgpspawn3



According to Oracle (Metalink note 560309.1), This could be lack of memory or improper setting of swap. Since in my case, physical memory was enough, it turned out that the issue was because of improper setting of swap. Swap needs to be set at least 0.75 times of physical RAM when memory>8GB

In my case, swap was 16GB while physical memory was 32GB which explained the case.
This issue could happen on any platform, my case was on Sun Solaris 64bit.

TIP 74# : Can not run startup nomount

After an upgrade from 10.2.0.2 to 10.2.0.3 for a client, I encountered with the following error :

SQL> startup nomount
ORA-00940: invalid ALTER command

Interestingly, after removing spfile and creating a pfile with simple option, the issue was resolved.
It turned out to be because of a hidden parameter which disable/enable a bug in 10.2.0.2 database which does not exist in 10.2.0.3. Parameter was _fix_control=5385629:on which turns on the fix for bug 5385629 in 10.2.0.2 but since this bug does not exist in 10.2.0.3, having this parameter in spfile caused a problem as Oracle can not enable this bug in 10.2.0.3.

Please be advised to make sure spfile is good and healthy and it has not this parameter if you get this error.

TIP #42: Package compilation takes long time

Last week, I had a request from a client which compilation session was running for hours.It was supposed to be completed in less than a minute. Session was trying to compile a package.
When I checking the compilation session, It was waiting for 'Library cache lock ' and 'Library cache pin'.
Reason : This package was security major package which is used by most sessions.The compiliation did hang because other sessions were executing procedure/function of this package.

Found metalink note 122793.1 very useful which basically recommends two options.

Option 1. Found blocking sessions with enabling trace.

• Run hanging compilation session and find out its process id (PID)
• Login with new session and enable tracing with

ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME SYSTEMSTATE LEVEL 10';

• Check user_dump_dest for trace , find process which match with PID of hanging session (Assume PID of hanging session is 8). Find handle address and find other PID with the same handle.In the following example PID 8 is hanging session and PID 9 is blocking session. With having PID=9, more info about blocking session can be found.

PROCESS 8:
----------------------------------------
.
.
.
.
waiting for 'library cache lock' blocking sess=0x0 seq=253 wait_time=0
handle address=5023ef9c, lock address=5019cad4, 10*mode+namespace=15

PROCESS 9:
----------------------------------------
.
.
.
LIBRARY OBJECT PIN: pin=5019d5e4 handle=5023ef9c mode=X lock=0
user=5005fad4 session=5005fad4 count=1 mask=0511 savepoint=118218 flags=[00]

2. Find blocking sessions with running a query

Run the following query. Basically X$KGLLK has all library cache lock and KGLLKREQ > 0 means that lock was requested by a session but session did not get it .

This query lists all blocking sessions which blocked hanging session.

SELECT SID,USERNAME,TERMINAL,PROGRAM FROM V$SESSION
WHERE SADDR in
(SELECT KGLLKSES FROM X$KGLLK LOCK_A
WHERE KGLLKREQ = 0
AND EXISTS (SELECT LOCK_B.KGLLKHDL FROM X$KGLLK LOCK_B
WHERE KGLLKSES = 'saddr_from_v$session' /* BLOCKED SESSION */
AND LOCK_A.KGLLKHDL = LOCK_B.KGLLKHDL
AND KGLLKREQ > 0)
);