Archive for January 7th, 2011
Upgrade or Not (Part 1)
It’s been a while to say the least since Oracle Database 10g Express Edition to give it it’s full title, or XE to give it the title everyone uses was released. As described at http://news.techworld.com/applications/3203909/oracle-stalls-over-11g-express-release/ this situation should be rectified soon. However in the mean time many XE users have been stuck effectively at 10.2.0.1. For a free product this is probably OK but a bit annoying. Somewhat surprisingly Oracle have also not provided a direct upgrade path for XE databases. The only supported route from XE to a paid for version of Oracle Database is export/import (datapump or otherwise).
With the release of 11.2 however Oracle claims to have addressed this issue. The DBUA utility has been enhanced to allow direct upgrades from XE to 11.2. I was naturally curious to see the result of this, so I installed XE on my laptop (I’d forgotten what a smooth installation process it was) which already has 11.2.0.2 installed. I then fired up dbua from the 11.2 home and was pleased to see the note about XE.
I then selected the XE instance
Unfortunately the next screen presented was the famous screen indicating that DBUA could not be used to upgrade this version of the database and please would I consult the Upgrade Guide. The Upgrade Guide clearly states that this upgrade is possible. Moreover running utlu112i.sql against the XE database resulted in the following report indicating that upgrade was possible.
QL> @utlu112i
Oracle Database 11.2 Pre-Upgrade Information Tool 01-07-2011 13:56:39
Script Version: 11.2.0.2.0 Build: 001
.
**********************************************************************
Database:
**********************************************************************
--> name: XE
--> version: 10.2.0.1.0
--> compatible: 10.2.0.1.0
--> blocksize: 8192
--> edition: XE
--> platform: Microsoft Windows IA (32-bit)
--> timezone file: V2
.
**********************************************************************
Tablespaces: [make adjustments in the current environment]
**********************************************************************
--> SYSTEM tablespace is adequate for the upgrade.
.... minimum required size: 524 MB
--> UNDO tablespace is adequate for the upgrade.
.... minimum required size: 455 MB
--> SYSAUX tablespace is adequate for the upgrade.
.... minimum required size: 510 MB
--> TEMP tablespace is adequate for the upgrade.
.... minimum required size: 61 MB
.
**********************************************************************
Flashback: OFF
**********************************************************************
**********************************************************************
Update Parameters: [Update Oracle Database 11.2 init.ora or spfile]
Note: Pre-upgrade tool was run on a lower version 32-bit database.
**********************************************************************
--> If Target Oracle is 32-Bit, refer here for Update Parameters:
-- No update parameter changes are required.
.
--> If Target Oracle is 64-Bit, refer here for Update Parameters:
-- No update parameter changes are required.
.
**********************************************************************
Renamed Parameters: [Update Oracle Database 11.2 init.ora or spfile]
**********************************************************************
-- No renamed parameters found. No changes are required.
.
**********************************************************************
Obsolete/Deprecated Parameters: [Update Oracle Database 11.2 init.ora or spfile]
**********************************************************************
--> background_dump_dest 11.1 DEPRECATED replaced by "diagnostic_dest"
--> user_dump_dest 11.1 DEPRECATED replaced by "diagnostic_dest"
--> sessions 10.1 OBSOLETE
.
**********************************************************************
Components: [The following database components will be upgraded or installed]
**********************************************************************
--> Oracle Catalog Views [upgrade] VALID
--> Oracle Packages and Types [upgrade] VALID
--> Oracle Text [upgrade] VALID
--> Oracle XML Database [upgrade] VALID
--> Oracle Application Express [upgrade]
.
**********************************************************************
Miscellaneous Warnings
**********************************************************************
WARNING: --> Database is using a timezone file older than version 14.
.... After the release migration, it is recommended that DBMS_DST package
.... be used to upgrade the 10.2.0.1.0 database timezone version
.... to the latest version which comes with the new release.
WARNING: --> Your recycle bin is turned on and currently contains no objects.
.... Because it is REQUIRED that the recycle bin be empty prior to upgrading
.... and your recycle bin is turned on, you may need to execute the command:
PURGE DBA_RECYCLEBIN
.... prior to executing your upgrade to confirm the recycle bin is empty.
WARNING: --> Database contains schemas with objects dependent on DBMS_LDAP package.
.... Refer to the 11g Upgrade Guide for instructions to configure Network ACLs.
.... USER FLOWS_020100 has dependent objects.
.
**********************************************************************
Recommendations
**********************************************************************
Oracle recommends gathering dictionary statistics prior to
upgrading the database.
To gather dictionary statistics execute the following command
while connected as SYSDBA:
EXECUTE dbms_stats.gather_dictionary_stats;
**********************************************************************
Oracle recommends reviewing any defined events prior to upgrading.
To view existing non-default events execute the following commands
while connected AS SYSDBA:
Events:
SELECT (translate(value,chr(13)||chr(10),' ')) FROM sys.v$parameter2
WHERE UPPER(name) ='EVENT' AND isdefault='FALSE'
Trace Events:
SELECT (translate(value,chr(13)||chr(10),' ')) from sys.v$parameter2
WHERE UPPER(name) = '_TRACE_EVENTS' AND isdefault='FALSE'
Changes will need to be made in the init.ora or spfile.
I will investigate further, my thoughts at the moment are
- This is a regression in 11.2.0.2
- The message about os authentication is relevant
- The order of software install matters (11.2 was already on this machine).