Systems Engineering and RDBMS

Oracle 10gR2 upgrade

Posted by decipherinfosys on February 12, 2007

Oracle documentation on the 10g Release 2 upgrade is pretty nice but we ran into couple of glitches when doing the upgrade at a client site and here is a cheat sheet that might help you in case you are running into similiar issues:


• 10g release2 must be installed on new home. It can not be used to upgrade the current ORACLE_HOME
• Pre-upgrade script available in R2 which will suggests the parameter changes/deprecated parameters etc.
• Purge the dba recycle bin as SYS. Purge dba_recyclebin;
• Gather dictionary stats are required after upgrade: exec dbms_stats.gather_dictionary_stats;
• Run utlrp to recompile all invalid objects before and after upgrade.
• You need root privilege to run and to stop the CSS service on DB server.
• Move all the trace files from bdump and udump location to archive directory and make the alert file null echo > alertSID.ora. This is helpful to monitor the upgrade related errors easily.
• There are many memory errors during the upgrade process. Set the following parameters (Metalink ID: 330239.1)

alter system set “_kgl_large_heap_warning_threshold”=16777216
scope=spfile ;

During the Upgrade process:

• DBUA utility will perform upgrade process. First upgrade the database and then ASM instance.
• Upgrade process will take time and appears to be hanged some time. Always check the runtime progress at location $ORACLE_HOME/cfgtoollogs/dbua
• If you suspect that upgrade is stuck up, do tail –f on the alert file. Continuous switching of log files indicates that DB activity is happening in background. During recompilation and gather stats, an upgrade log file does not grow, however alert file will indicate the background activity.

Post Upgrade:

• Run utlrp to recompile all invalid objects.
• Run gather_stats on all schemas.
• Re-create the MV definitions in all schemas.
• Check alert log and other trace files.
• Execute following script to tackle exports related errors Metalink ID: 339938.1

SQL> @?/rdbms/admin/catmetx.sql
SQL> @?/rdbms/admin/utlrp.sql

• CONNECT has been replaced with create session privilege which means we need to grant these privileges explicitly to all users.

Better way to create XYZ_ROLE role and grant that to all users;

/* Replace XYZ with your preferred name */


• Handling ORA-3136 errors: Metalink ID: 345197.1

The Oracle Net 10g parameters SQLNET.INBOUND_CONNECT_TIMEOUT and INBOUND_CONNECT_TIMEOUT_listenername default to 0 (indefinite) in 10.1.  To address Denial of Service (DOS) issues, the parameters were set to have a default of 60 (seconds) in Oracle 10.2. If applications are longer than 60 secs to authenticate with the Oracle database, the errors occur.  The following may be seen in the alert log:

WARNING: inbound connection timed out (ORA-3136) SQLNET.INBOUND_CONNET_TIMEOUT is set to a value in seconds and determines how long a client has to provide the necessary authentication information to a database.

Add these parameters on the SERVER side: value in seconds.

listener.ora: INBOUND_CONNECT_TIMEOUT_listenername =240

Sorry, the comment form is closed at this time.

%d bloggers like this: