Issues found when upgrading 9.2.0 to 10.2.0

Note: I strongly encourage people to read the Upgrade guide, read metalink Notes, get involved with 10g new features and setup a testing environment prior to perform the actual upgrade on a production environment.

ORA-00093: _shared_pool_reserved_min_alloc must be between 4000 and 0

In my particular case, the reason why I faced this issue was because I enabled ASMM and I didn't declare a value for SGA_TARGET. All parameters managed by ASMM were set to zero, but I forgot to explicitly set SGA_TARGET and SGA_MAX_SIZE. After setting suitable values for these parameters I was able to start my database. _shared_pool_reserved_min_alloc is a hidden parameter and this error appears due to ASMM missconfiguration.

This was on a manually upgraded database. I followed the below mentioned documentation, which was of great help.

Upgrade Guide
Oracle 10g New Features

Metalink Notes:

• Complete checklist for manual upgrades of Oracle databases: 421191.1

Heap size nK exceeds notification threshold (2048K) warning
An error similar to this one is frequently seen at the alert.log file. Several queries which used to work without any issue, suddenly start to be reported at the alert.log file without apparent reason.
Memory Notification: Library Cache Object loaded into SGA
Heap size 11369K exceeds notification threshold (2048K)
Details in trace file d:\oracle\admin\aonmxdbf\udump\aonmxdbf_ora_3624.trc
KGL object name :EXPLAIN PLAN SET STATEMENT_ID = 'TRACE156742' INTO DBO.IW_PLAN_TABLE FOR Select

This is actually not an error, and the database is not about to crash, it only means a Heap size is greater than the value registered as a threshold. This is an issue on 10.2.0.1.0, this issue has been fixed on later patchsets. If you want to get rid of this warning, then an internal parameter has to be modified to a value greated than 2M. This should be made from a sql plus prompt connected as sysdba:

SQL> alter system set "_kgl_large_heap_warning_threshold"=8388608 scope=spfile ;


ORA-04030: out of process memory
An error similar to the one shown below is written at the alert.log file, a process has run out of memory. The error manual is funny, according to it you don't have to do any thing.

ORA-04030: out of process memory when trying to allocate string bytes (string,string)
Cause: Operating system process private memory has been exhausted
Action: none

Errors in file d:\oracle\admin\aonmxdbf\udump\aonmxdbf_ora_3176.trc:
ORA-04030: out of process memory when trying to allocate 66172 bytes (pga heap,kco buffer)
ORA-07445: exception encountered: core dump [ACCESS_VIOLATION] [unable_to_trans_pc] [PC:0x60504019] [ADDR:0x4]
[UNABLE_TO_WRITE] []

This error showed up when an end user tried to access an application with a view ranked as one of the top queries in the database. I have increased the PGA_AGGREGATE_TARGET instance parameter and I have enabled ASMM.

I should further clarify, this happened on a Windows 2003 environment, and I found out that the boot didn't have the /3G switch required to access more than 2G RAM memory. So I have set it too since the memory requirements were very close to the 2G limit.

multi(0)disk(0)rdisk(0)partition(1)\WINDOWS="Windows Server 2003, Enterprise" /fastdetect /3GB

The ORA-04030 was accompanied by TNS-12518 (TNS:listener could not hand off client connection)

It happened intermitently and when workload increased. As detailed on my other post this was due to excesive resources allocated by all the new features for this release. It is documented at the metalink note 371983.1.


Instance parameters compatible and log_archive_format
Once the database was upgraded, according to the install guide, the compatible parameter remained at 9.2.0.0.0, once the upgrade process was stabilized the parameter changed to 10.2.0.1.0, this made instance parameter such as log_archive_format to have a mandatory different format: it must contain the archive format mask %s %r %t.