An example of the error reported after SHUTDOWN IMMEDIATE:

SQL*Plus: Release 10.2.0.3.0 - Production on Sob Jan 26 14:23:59 2008

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

Connected.
Database closed.
Database dismounted.
ORA-00600: internal error code, arguments: [LibraryCacheNotEmptyOnClose], [], [], [], [], [], [], []
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

According to Metalink note 4483084.8 “Bug 4483084 – OERI[LibraryCacheNotEmptyOnClose] on shutdown” this error message could be ignored. Bug is fixed in 11.1.0.6.

We’re getting this error sporadically after we migrated to 64-bit 10g R2, never received the error before (on 32-bit OS/RDBMS).

Another test case scenario (in addition to one described here) for Oracle Word size change, from 32-bit to 64-bit.

We want source database (32-bit Oracle 10.2.0.2, Windows 2003 SP1, 32-bit) to convert to target database
(64-bit Oracle 10.2.0.3 + Patch 12, Windows 2003 SP2, x64). Basically we’re changing the word size of the database and patching the database at the same time. This scenario was tested in test environment and will be used shortly on one of our production database.

We already installed 64-bit Oracle Software (10.2.0.3 + Patch 12) on our new machine and made necessary configuration of the storage, directories, spfile, password file etc. We also copied all database/ctl files from production server to the new, x64 machine (after shutdown normal).

You must be aware of the following:

• it’s important that target (64-bit) Oracle10g is of the proper release (minimum 10.2.0.3 + Patch 5, in our case is Patch 12, otherwise known as CPUOCT2007) or you might hit ORA-600 error that I wrote about.
• make sure SQLNET.ORA is present on target database and that contains NAMES.DIRECTORY_PATH= (TNSNAMES), or you might hit another bug.
• don’t even think of using Oracle on Windows 2003 with SP1 and large amount of memory! Read this before you decide what to do.
• make sure you read Metalink Note:62290.1 “Changing between 32-bit and 64-bit Word Sizes”
• read and understand patchset readme (10.2.0.3) as well as readme that comes with Patch 12.
• this test scenario is for single, non-RAC, Oracle instance

From here on the steps are:

1) create service on x64:

set ORACLE_SID=ORCL
D:\oracle\ORA10\bin\oradim.exe -new -sid ORCL -startmode manual -spfile 
D:\oracle\ORA10\bin\oradim.exe -edit -sid ORCL -startmode auto -srvcstart system 

2) start the instance in upgrade mode

I’m intentionally using pfile for the sake of simplicity of editing; after migration spfile is used)

startup upgrade pfile=D:\ORACLE\ORA10\database\initorcl.ora

3) invalidate PL/SQL packages

spool CATOUT_32_64.log
@D:\ORACLE\ORA10\RDBMS\ADMIN\UTLIRP.SQL
spool off

Check CATOUT_32_64.log.

Do not shutdown and restart the database!

4) Upgrade

SPOOL CATUPGRD.LOG
@D:\ORACLE\ORA10\RDBMS\ADMIN\CATUPGRD.SQL
SPOOL OFF

Check CATUPGRD.LOG for errors – some errors can be ignored, refer to readme that comes with Patchset 10.2.0.3.

5) Check status for the package DBMS_STANDARD

select status from dba_objects where object_name='DBMS_STANDARD'
and object_type='PACKAGE' and owner='SYS';

Recompile if package is invalid:
alter package dbms_standard compile;

6) Check the number of invalid objects for SYS and recompile if needed

select count(*) from dba_objects where owner='SYS' and status='INVALID';

@D:\ORACLE\ORA10\RDBMS\ADMIN\UTLRP.SQL

7) Check status of database components

column comp_name format a40
column version format a10
select comp_name, version, status from dba_registry;

COMP_NAME                                VERSION    STATUS
---------------------------------------- ---------- -----------
Oracle interMedia                        10.2.0.3.0 VALID
Oracle Enterprise Manager                10.2.0.3.0 VALID
Oracle XML Database                      10.2.0.3.0 VALID
Oracle Text                              10.2.0.3.0 VALID
Oracle Expression Filter                 10.2.0.3.0 VALID
Oracle Rule Manager                      10.2.0.3.0 VALID
Oracle Workspace Manager                 10.2.0.1.0 VALID
Oracle Data Mining                       10.2.0.3.0 VALID
Oracle Database Catalog Views            10.2.0.3.0 VALID
Oracle Database Packages and Types       10.2.0.3.0 VALID
JServer JAVA Virtual Machine             10.2.0.3.0 VALID
Oracle XDK                               10.2.0.3.0 VALID
Oracle Database Java Packages            10.2.0.3.0 VALID
SQL>

8) Apply CPUOCT2007

In addition of running CATUPGRD.SQL we have to run CATCPU script as well, in our case for CPUOct2007.

cd %ORACLE_HOME%\CPU\CPUOct2007
sqlplus /nolog 
CONNECT / AS SYSDBA 
@catcpu.sql
QUIT 


The script will end with report similar as:

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP CPU_END    2008-01-18 20:22:54

No. of Invalid Objects is :575
Please refer to README.html to for instructions on validating these objects

PL/SQL procedure successfully completed.

Logfile for the current catcpu.sql session is : APPLY_ORAX_18Jan2008_20_18_52.log

Check log file mentioned in catcpu.sql report - some errors can be ignored (refer to CPU readme)!

At this stage you can check dba_registry again:

SQL> column comp_name format a40
SQL> column version format a10
SQL> select comp_name, version, status from dba_registry;

COMP_NAME                                VERSION    STATUS
---------------------------------------- ---------- -----------
Oracle interMedia                        10.2.0.3.0 VALID
Oracle Enterprise Manager                10.2.0.3.0 VALID
Oracle XML Database                      10.2.0.3.0 VALID
Oracle Text                              10.2.0.3.0 VALID
Oracle Expression Filter                 10.2.0.3.0 VALID
Oracle Rule Manager                      10.2.0.3.0 VALID
Oracle Workspace Manager                 10.2.0.1.0 VALID
Oracle Data Mining                       10.2.0.3.0 VALID
Oracle Database Catalog Views            10.2.0.3.0 VALID
Oracle Database Packages and Types       10.2.0.3.0 UPGRADED
JServer JAVA Virtual Machine             10.2.0.3.0 VALID

COMP_NAME                                VERSION    STATUS
---------------------------------------- ---------- -----------
Oracle XDK                               10.2.0.3.0 VALID
Oracle Database Java Packages            10.2.0.3.0 VALID

13 rows selected.

9) Run UTLRP.SQL once again…

@D:\ORACLE\ORA10\RDBMS\ADMIN\UTLRP.SQL 

## WELCOME TO 64-bit World – END OF TEST CASE ##

If you’re using Oracle on Windows 2003 SP1 on a machine with lots of memory, you should install SP2 as soon as possible.

Check Metalink note: 464683.1 “Unexplained Database Slowdown Seen on Windows 2003 Service Pack 1”

and Microsoft Knowledge base articles:

• A program that uses structured exception handling may stop responding for several seconds in Microsoft Windows Server 2003 with SP1
• SQL Server 2000 or SQL Server 2005 may temporarily stop responding on a Windows Server 2003 Service Pack 1-based computer

I can barely remember how many times we noticed exact symptoms, without having a clue what is going on…part of the problem is certainly slow adoption of Windows 2003 in the first year or two after release, otherwise this sorts of bugs would crop out earlier. The MS decision to wait so long with SP1/SP2 release was also a bummer for MS customers.

Today, I noticed a hang of the session that was executing a simple MERGE statement(s) with
“cursor: pin S wait on X” wait event.

Killed the session and tried to shutdown immediate. Shutdown process hanged as well (only session with the status killed was present in V$SESSION prior to shutdown), with the message in alert.log:

Sat Jan 19 12:56:05 2008
Active call for process 2100 user 'SYSTEM' program 'ORACLE.EXE (SHAD)'
Active call for process 1736 user 'SYSTEM' program 'ORACLE.EXE (SHAD)'
SHUTDOWN: waiting for active calls to complete.

A quick search on Metalink revealed that this is a bug introduced in 10.2.0.3.

References:

Note 5907779 – “Self deadlock hang on “cursor: pin S wait on X” (typically from DBMS_STATS)”
Note 401435.1 -“10.2.0.3 Patch Set – Availability and Known Issues”

The bug will be fixed in Patchset 10.2.0.4. For the time being you can use interim patch 5907779, if you’re not using Windows because patch for this platform is not available at the moment.

If the problem re-appear I’ll first check the V$MUTEX_SLEEP and V$MUTEX_SLEEP_HISTORY views, before killing the sessions.