“cursor: pin S wait on X” Wait Event in Oracle 10g Release 2

After a pleasant weekend, when I returned back to work I found one of our development databases on Oracle 10g was in a hung state. Later, upon investigation I found 23 sessions were waiting on “cursor: pin S wait on X” wait event.

Searching on Oracle Metalink, I came to know that this is a bug in Oracle 10.2.0.3 on all platforms. The note explains that this could happen when you typically use DBMS_STATS to gather statistics.

We had a job scheduled to run on the weekend to gather statistics. I identified the session which was gathering statistics and killed that session both from Oracle and OS. Soon after that everything was back to normal.

A patch is available on Oracle Metalink which addresses this issue.

References from Metalink:

Subject: 10.2.0.3 Patch Set - Availability and Known Issues

Note:401435.1

Last Revision Date: 14-JUN-2007

Subject: Bug 5907779 - Self deadlock hang on "cursor: pin S wait on X" (typically from DBMS_STATS)

Note:5907779.8

Last Revision Date: 10-APR-2007

Happy reading !!!

How to find my Windows is 32-bit or 64-bit?

How to find my Windows is 32-bit or 64-bit? To find the answer to this question, follow this link http://support.f-secure.com/enu/home/supportissue/fsis2007/before_install_q07.shtml How to find the OS block size of my Windows OS? Microsoft defines Cluster as “In data storage, the smallest amount of disk space that can be allocated to hold a file. All file systems used by Windows organize hard disks based on clusters, which consist of one or more contiguous sectors.” To find the OS block size: Right Click on “My Computer” icon on the desktop Click on “Manage” Expand “Storage” and select “Disk Defragmenter” Press the “Analyze” button Once your windows completes the analysis, it presents you with a report. Press “View Report” button The block size is indicated by “Cluster Size”. In my case it’s 4K. How to set the OS block size on my Windows OS? Open the Windows Explorer. Right Click on your “C drive” and click on “Format”. A window is presented with couple of options. The OS block size is displayed by a drop-down list labeled “Allocation unit size”.

How We Upgraded our Oracle 9i Database to Oracle 10g Database with Near-Zero Downtime

Dear all,

My article has been accepted and is published under Metalink's Customer Knowledge Exchange Program. The title of my article is "How We Upgraded our Oracle 9i Database to Oracle 10g Database with Near-Zero Downtime" and the Metalink note id is 431430.1.

Abstract of the document:

An Oracle database upgrade is a process of transforming an existing Oracle database into a later or the current release of the Oracle Database. This upgrade process normally requires a significant amount of database down time. This task of upgrade becomes more difficult for businesses which need to be 24*7 operational and any outage negatively affects business operations and customer service. In 24*7 organizations like Banks the database availability is so crucial that the downtime is bargained in minutes.

Minimizing downtime is a challenge every organization faces. With the advent of Oracle Streams, this challenge merely remains a challenge. Oracle Streams replication enables us to perform an upgrade with near-zero downtime. This is accomplished by configuring Oracle Streams on the existing Oracle 9i database which captures all the changes on the source database and replicates the changes to the target database (Oracle 10g). The rest of the document presents a step-by-step approach of setting the Oracle Streams replication and performing an Upgrade to Oracle 10g database.

Have a look at it and pass your comments.

Happy reading :)

Updated: 14 May, 2008