Archive for the ‘11.2’ tag
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).
Possibly Related Posts:
New Features, New Defaults, New Side-Effects
Starting with 11.2 which is now available for all the major platforms the behaviour of the CREATE TABLE statement has changed. Specifically, as introduced here, when creating a conventional table in a database created with the default options then the initial segment is not created until the first row is inserted into the table.
The good part of this is well described in the link above, specifically
The advantages of this space allocation method are the following:
•A significant amount of disk space can be saved for applications that create hundreds or thousands of tables upon installation, many of which might never be populated.
•Application installation time is reduced
In other words this is a very nice enhancement for applications like, for example, the Oracle E-Business Suite which typically take multiple hours to install and install a large number of objects that will never be used by any given customer.
I am however already aware of 2 potential downsides to this. The first downside will also apply to exactly the ERP type of application that the designers apparently had in mind for this feature. This is well described in Metalink Note 1050193.1 as follows:
The sequences created as default ( start with 1 increment by 1) are not staring with ’1′ when used in insert query script, Instead they are starting with 2 or 4.
This behaviour of sequences is seen in version Oracle11gR2, not seen in earlier versions
In other words if you specify a sequence and a start with value to generate a range of IDs to be used to create a primary key, the sequence will effectively not start with the “START WITH” value. This is likely a minor inconvenience but one to be aware of if you subscribe to the artificial key design paradigm.
The second is described in this forum thread and is a result of the new default behaviour only applying to the Enterprise Edition of Oracle. Should you create an 11.2 database using the default options and later wish to export the data and import into a standard edition database (We have a number of clients for whom this is not a hypothetical case, they’ve done exactly this in the past) then there isn’t currently a satisfactory method to do this for schemas of any size. The issue is compounded currently by the fact that the Enterprise only status of this feature is not correctly documented.
All in all I think I’d rather that the default value for the initialisation parameter DEFERRED_SEGMENT_CREATION was set to false with the option for people who regularly create schemas with large numbers of never to be used tables and for whom application software installation time is a significant concern to set it to TRUE. It just seems like one of those cases where adopting a new option as a default in the very first release in which it is available is a less than optimal idea.