As a large number of the Oracle community would I guess be in the process of upgrading to 11g (Release 2 and its patch is already available), I am including some presentations taken from the 11g Upgrade Workshop which was conducted by Oracle Corp staff members Roy Swonger & Mike Dietrich.
The workshop was highly detailed but very practical in nature and is a must read for anyone who has not attended these workshops, but are considering upgrading to 11g in the near future.
Here are the workshop slides:
Here are some other upgrade tips which I have picked up along the way.
Upgrade of the database is NOT dependent of size of the database. It depends on options installed in the database like Spatial, Text, Java, Intermedia etd.
Upgrade typically can take between 90-180 minutes on a machine with fairly standard specifications.
Compilation of INVALID objects is where a majority of the time is spent in the upgrade via DBUA. Before the upgrade try and compile all invalid objects and keep a note of any objects still left in an INVALID state. This needs to be compared with the list of invalid objects after the 11g upgrade is completed as we would like to know if any objects got invalidated as part of the upgrade procedure.
Backup and truncate the SYS.AUD$ table before doing the upgrade. The AUD$ table can contain potentially millions of rows and large number of rows in this table can significantly increase the upgrade duration.
Put the application data and index tablespaces in read only mode before the upgrade and just take a backup of tablespaces like SYSTEM, SYSAUX, Undo and Temp tablespaces, redo log files and controlfiles. This will significantly reduce the backup time required pre-upgrade.
Be careful about changing the COMPATIBLE parameter after the upgrade. Note that the DBUA will not change the value of the COMPATIBLE parameter and this is something which will need to be done manually. Test the application after the upgrade with the COMPATIBLE value unchanged and once we verify that the pre 11g functionality is all fine, we can then go and change the COMPATIBLE to say 11.2.0. Remember, once this is done, downgrade is NOT POSSIBLE. We will need to restore our pre-11g backup.
Another issue which we encountered on a Solaris platform while upgrading from 9i to 10g. While the upgrade of the XDB was in progress we got an ORA-03114 Not connected to Oracle error – the alert log had an ORA-07445 error as well …
ORA-07445: exception encountered: core dump [memcpy()+40] [SIGSEGV] [Address not mapped to object]
This error occured while running dbms_xmlschema.registerschema and we fixed this by changing the LD_LIBRARY_PATH which was still pointing to the original Oracle 9i environment so was referencing the wrong libxdb.so file.
Remove any underscore ‘_*’ parameters from the init.ora file and also any parameters related to ‘events’.
