Following metalink notes are useful for this upgrade processes.
Upgrade Advisor: Database from 10.2 to 11.2 [ID 251.1]
Complete Checklist to Upgrade the Database to 11gR2 using DBUA [ID 870814.1]
Complete Checklist for Manual Upgrades to 11gR2 [ID 837570.1]
Oracle Database Upgrade Path Reference List [ID 730365.1]
Things to Consider Before Upgrading to 11.2.0.3 to Avoid Poor Performance or Wrong Results [ID 1392633.1]
The database to upgrade is a 10.2.0.4 with CPU 2009 July being the latest CPU that has been applied on it. In other words no CPU or PSU released after July 2009 has been applied on this database.
Secondly it's a standard edition database with the following registry components.
COMP_NAME STATUS ----------------------------------- ------- OLAP Analytic Workspace INVALID OLAP Catalog INVALID Oracle OLAP API INVALID JServer JAVA Virtual Machine VALID Oracle Data Mining VALID Oracle Database Catalog Views VALID Oracle Database Java Packages VALID Oracle Database Packages and Types VALID Oracle Enterprise Manager VALID Oracle Expression Filter VALID Oracle Rules Manager VALID Oracle Text VALID Oracle Workspace Manager VALID Oracle XDK VALID Oracle XML Database VALID Oracle interMedia VALID Spatial VALIDRunning utlrp.sql gives the following output as the number of invalid objects
OBJECTS WITH ERRORS
-------------------
8
DOC> The following query reports the number of errors caught during
..
ERRORS DURING RECOMPILATION
---------------------------
04 OLAPSYS packages and 4 public role synonyms are the objects with errors in this case.OLAP* and several other components are not valid options on a standard edition database. In this case only OLAP* components are invalid but spatial, intermedia, data mining are all not valid for a standard edition database. But the registry will be valid on a fresh installation but after installing CPU or revoking execute on utl* packages for public and etc some of these will become invalid. The earlier blog was of such a fresh installation of 10gR2 that didn't have any components invalid.
Following metalink notes could be used to install/deinstall various components that are not valid in standard edition (if they were install initially).
How To Find Out If OLAP Is Being Used And How To Remove OLAP [ID 739032.1]
Remove Invalid OLAP Objects From SYS And OLAPSYS Schemas [ID 565773.1]
Steps for Manual De-installation of Oracle Spatial [ID 179472.1]
Removed Spatial Option But Spatial Still Appears In V$Option [ID 273573.1]
Where to Find the Information to Install, Upgrade, Downgrade and Deinstall interMedia/Oracle Multimedia? [ID 337415.1](on 10gR2 deinstall $OH/ord/im/admin/imdinst.sql, on 11gR2 $OH/rdbms/admin/catcmprm.sql ORDIM )
Trying to uninstall all non valid components could result in more objects with errors which caused problems during a test upgrade and made the upgrade to fail. If it is decided to remove all the components that are not valid in standard edition it is advisable to make sure that there are no invalid objects prior to upgrade. In this case the upgrade will continue with these three OLAP* components invalid.
The pre-upgrade information tool's output is as follows
SQL> @utlu112i.sql
Oracle Database 11.2 Pre-Upgrade Information Tool 11-29-2011 16:14:13
Script Version: 11.2.0.3.0 Build: 001
.
**********************************************************************
Database:
**********************************************************************
--> name: stdb1
--> version: 10.2.0.4.0
--> compatible: 10.2.0.3.0
--> blocksize: 8192
--> platform: Linux x86 64-bit
--> timezone file: V4
.
**********************************************************************
Tablespaces: [make adjustments in the current environment]
**********************************************************************
--> SYSTEM tablespace is adequate for the upgrade.
.... minimum required size: 725 MB
--> UNDOTBS1 tablespace is adequate for the upgrade.
.... minimum required size: 400 MB
--> SYSAUX tablespace is adequate for the upgrade.
.... minimum required size: 461 MB
--> TEMP tablespace is adequate for the upgrade.
.... minimum required size: 60 MB
.
**********************************************************************
Flashback: OFF
**********************************************************************
**********************************************************************
Update Parameters: [Update Oracle Database 11.2 init.ora or spfile]
Note: Pre-upgrade tool was run on a lower version 64-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"
.
**********************************************************************
Components: [The following database components will be upgraded or installed]
**********************************************************************
--> Oracle Catalog Views [upgrade] VALID
--> Oracle Packages and Types [upgrade] VALID
--> JServer JAVA Virtual Machine [upgrade] VALID
--> Oracle XDK for Java [upgrade] VALID
--> Oracle Workspace Manager [upgrade] VALID
--> OLAP Analytic Workspace [upgrade] INVALID
--> OLAP Catalog [upgrade] INVALID
--> EM Repository [upgrade] VALID
--> Oracle Text [upgrade] VALID
--> Oracle XML Database [upgrade] VALID
--> Oracle Java Packages [upgrade] VALID
--> Oracle interMedia [upgrade] VALID
--> Spatial [upgrade] VALID
--> Data Mining [upgrade] VALID
--> Expression Filter [upgrade] VALID
--> Rule Manager [upgrade] VALID
--> Oracle OLAP API [upgrade] INVALID
.
**********************************************************************
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.4.0 database timezone version
.... to the latest version which comes with the new release.
WARNING: --> Database contains INVALID objects prior to upgrade.
.... The list of invalid SYS/SYSTEM objects was written to
.... registry$sys_inv_objs.
.... The list of non-SYS/SYSTEM objects was written to
.... registry$nonsys_inv_objs.
.... Use utluiobj.sql after the upgrade to identify any new invalid
.... objects due to the upgrade.
.... USER OLAPSYS has 4 INVALID objects.
.... USER PUBLIC has 4 INVALID objects.
WARNING: --> EM Database Control Repository exists in the database.
.... Direct downgrade of EM Database Control is not supported. Refer to the
.... Upgrade Guide for instructions to save the EM data prior to upgrade.
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.
.
**********************************************************************
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.
**********************************************************************Carry out the steps recommended by this tool such as gathering dictionary and schema statistics and etc. More steps available on 837570.1 which are not relevant on this upgrade so they are not listed.Another key difference is that 11.2.0.3 provides a way to upgrade the timezone at the same time the database is upgrade. Which was not there on the base 11gR2 release. Compare the 5th picture on the previous blog with the picture below which gives the option to upgrade the timezone.
There are several metalink notes related to timezone upgrade.
Updating the RDBMS DST version in 11gR2 (11.2.0.1 and up) using DBMS_DST [ID 977512.1]
Actions For DST Updates When Upgrading To Or Applying The 11.2.0.3 Patchset [ID 1358166.1]
Before the upgrade the timezone file version was
upgrade
SQL> select version from v$timezone_file;
VERSION
----------
4Key points relevant to this are (quoting from the metalink note 1358166.1)If this reports a timezone version lower then 14 please see point B.3) in this document.B.3) If your current timezone version is lower than 14 (typically 4):
B.3b) When upgrading from 10.1.0.x , 10.2.0.x or 11.1.0.x to 11.2.0.3:
For 10.2.0.2 , 10.2.0.3 , 10.2.0.4 or 10.2.0.5 there is no need to apply any patchset before upgrading to 11.2.0.3
Upgrade to 11.2.0.3.No need to apply DST patches on the 10.2.0.2 , 10.2.0.3 , 10.2.0.4 or 10.2.0.5 side first. You can skip any DST related upgrade instructions.
The 11.2.0.3 RDBMS DST version after the upgrade to 11.2.0.3 will be the same DST version as used in 10.2.0.2 , 10.2.0.3 , 10.2.0.4 or 10.2.0.5.
After the upgrade to 11.2.0.3 you can:
(recommended) update the 11.2.0.3 database(s) to DSTv14 (standard DST version of 11.2.0.3) by following Note 977512.1 Updating the RDBMS DST version in 11gR2 (11.2.0.1 and up) using DBMS_DST from step 3a) onwards, when going to DSTv14 there is no need to apply any DST patch to the 11.2.0.3 home
(optional) update to a higher DST than 14 version if this is needed.
Apply the latest 11.2.0.3 DST update patch after the upgrade to 11.2.0.3 and use DDBMS_DST.
The lastest DST patch and related note on how to apply this is found in Note 412160.1 Updated Time Zones in Oracle Time Zone File patches under "C) Notes covering the current DST available updates". Simply follow the note for the latest DST update. If decided it is possible to upgrade the database's timezone after the database upgrade by following the above metalink notes. In this case timezone was upgraded at the same time database was upgraded.
Save the em control incase later on it is decided to downgrade back to 10g.
Create a TNS name entry in the 10g Oracle Home (network/admin) with the same name as the SID before running the above
/opt/app/oracle/product/11.2.0/dbhome_1/bin/emdwgrd -save -sid stdb1 -path /home/oracle/emdctl Enter sys password for database stdb1? Database Unique Name : stdb1 Tue Nov 29 17:01:29 2011 - Validating DB Connection to stdb1 ... pass Tue Nov 29 17:01:29 2011 - Verify EM DB Control files ... pass ENV var EM_REMCP not defined, check if rcp or scp is configured. RCP = /usr/bin/rcp -rp, REMSH = /usr/bin/rsh shared = 0 Tue Nov 29 17:01:29 2011 - Creating directory ... created Tue Nov 29 17:01:30 2011 - Stopping DB Control ... stopped Tue Nov 29 17:01:35 2011 - Saving DB Control files ... saved Tue Nov 29 17:01:35 2011 - Recompiling invalid objects ... recompiled Tue Nov 29 17:01:36 2011 - Exporting sysman schema for stdb1 ... exported Tue Nov 29 17:01:59 2011 - DB Control was saved successfully. Tue Nov 29 17:01:59 2011 - Starting DB Control ... started Tue Nov 29 17:03:33 2011 - Dump directory was dropped successfully.Disable all batch and cron jobs.
Take a full backup of the database. (Better if it's a cold backup, not necessary)
Stop the 10g listener and create a listener on 11gR2 home and start it.
Set ORACLE_BASE ,ORACLE_HOME and PATH environment variable pointing to the 11gr2 home and run dbua from the 11gR2 home's bin. Upgrade summary is given below.
Upgrade process will omit some of the components from the upgrade process.
This is also mentioned when selecting the database to upgrade.
During the upgrade it could be seen that some of the components are not upgraded. These components are not valid on standard edition. Also note timezone upgrade.
Upgrade results
SQL> select version from v$timezone_file;
VERSION
----------
14Components status in the database registryCOMP_NAME VERSION STATUS --------------------------------------------- ------------------------------ ----------- Oracle Multimedia 11.2.0.3.0 INVALID OLAP Analytic Workspace 10.2.0.4.0 OPTION OFF OLAP Catalog 10.2.0.4.0 OPTION OFF Oracle Data Mining 10.2.0.4.0 OPTION OFF Oracle OLAP API 10.2.0.4.0 OPTION OFF Spatial 10.2.0.4.0 OPTION OFF JServer JAVA Virtual Machine 11.2.0.3.0 VALID Oracle Database Catalog Views 11.2.0.3.0 VALID Oracle Database Java Packages 11.2.0.3.0 VALID Oracle Database Packages and Types 11.2.0.3.0 VALID Oracle Enterprise Manager 11.2.0.3.0 VALID Oracle Expression Filter 11.2.0.3.0 VALID Oracle Rules Manager 11.2.0.3.0 VALID Oracle Text 11.2.0.3.0 VALID Oracle Workspace Manager 11.2.0.3.0 VALID Oracle XDK 11.2.0.3.0 VALID Oracle XML Database 11.2.0.3.0 VALID 17 rows selected.Oracle Multimedia(Oracle interMedia in 10g) is invalid after the upgrade. Following two metalink notes help identifying the reason for this
Things To Check When Oracle Multimedia/interMedia Is INVALID In DBA_REGISTRY Or The Version Is Not The Same As The Database Version [ID 1065954.1]
Verifying an Installed Version of Oracle Multimedia [ID 458228.1]
Running the validate_ordim gives the following errors
SQL> set serveroutput on SQL> execute validate_ordim; Locator INVALID OBJECTS: PRVT_IDX - 5 - 11 Locator INVALID OBJECTS: SDO_GEOM - 5 - 11 Locator INVALID OBJECTS: SAMCLUST_IMP_T - 5 - 14 PL/SQL procedure successfully completed.From the output it seems that some of the spatial component related objects are invalid (since spatial option is off after the upgrade) thus oracle media that depends on it also becomes invalid. Remove the Oracle media component with (which also removes spatial component which is depends on media)
SQL> @?/rdbms/admin/catcmprm.sql ORDIM About to remove Oracle Multimedia. Checking to see if anyone is using Oracle Multimedia. Oracle Multimedia is not being used PL/SQL procedure successfully completed. Are you sure you want to remove Oracle Multimedia (Y/N): y PL/SQL procedure successfully completed. Removing Oracle Multimedia PL/SQL procedure successfully completed.After remove dba registry status
COMP_NAME VERSION STATUS ----------------------------------- ------------------------------ -------------------------------------------- OLAP Analytic Workspace 10.2.0.4.0 OPTION OFF OLAP Catalog 10.2.0.4.0 OPTION OFF Oracle Data Mining 10.2.0.4.0 OPTION OFF Oracle OLAP API 10.2.0.4.0 OPTION OFF JServer JAVA Virtual Machine 11.2.0.3.0 VALID Oracle Database Catalog Views 11.2.0.3.0 VALID Oracle Database Java Packages 11.2.0.3.0 VALID Oracle Database Packages and Types 11.2.0.3.0 VALID Oracle Enterprise Manager 11.2.0.3.0 VALID Oracle Expression Filter 11.2.0.3.0 VALID Oracle Rules Manager 11.2.0.3.0 VALID Oracle Text 11.2.0.3.0 VALID Oracle Workspace Manager 11.2.0.3.0 VALID Oracle XDK 11.2.0.3.0 VALID Oracle XML Database 11.2.0.3.0 VALID 15 rows selected.This will increase the number of invalid objects but these will be OLAP related objects and has no impact on the rest of the database operation.
SQL>select owner,object_type,count(*) from dba_objects where status='INVALID' group by owner,object_type order by owner,object_type ; OWNER OBJECT_TYPE COUNT(*) ------------------------------ ------------------- ---------- OLAPSYS PACKAGE BODY 3 OLAPSYS VIEW 25 PUBLIC SYNONYM 25It is possible to drop the olapsys user and also drop remaining public synonyms.
DROP USER OLAPSYS CASCADE; select 'drop '||object_type||' '||substr(object_name,1,40) ||';' from dba_objects where status='INVALID';After this expdp/impdp could run into following error
ORA-39127: unexpected error from call to "SYS"."DBMS_CUBE_EXP"."INSTANCE_EXTENDED_INFO_EXP" ORA-44002: invalid object name ORA-06512: at "SYS.DBMS_ASSERT", line 316 ORA-06512: at "SYS.DBMS_METADATA", line 9114Metalink notes How To Remove or De-activate OLAP After Migrating From 9i To 10g or 11g [ID 467643.1] and ORA-39127 SYS.DBMS_CUBE_EXP.INSTANCE_EXTENDED_INFO_EXP ORA-44002 On Expdp After Upgrade To 11.2.0.2 [ID 1353491.1] explains the solution for this, which is If the OLAP option is not used delete DBMS_CUBE_EXP OLAP package from the export view as follows
SQL> select PACKAGE,SCHEMA,class from exppkgact$ where (schema,package) not in (select owner,object_name from dba_objects where object_type='PACKAGE'); PACKAGE SCHEMA CLASS ------------------------------ ------------------------------ ---------- DBMS_CUBE_EXP SYS 4 SQL> delete from sys.exppkgact$ where package = 'DBMS_CUBE_EXP' and schema= 'SYS'; 1 row deleted. SQL> commit; Commit complete.After this export will work as expected and there will no invalid objects nor any invalid database components
@?/rdbms/admin/utlrp
OBJECTS WITH ERRORS
-------------------
0
DOC> The following query reports the number of errors caught during
..
ERRORS DURING RECOMPILATION
---------------------------
0
SQL> select comp_name,version,status from dba_registry order by 3,1,2;
COMP_NAME VERSION STATUS
----------------------------------- ------------------------------ -----------
OLAP Analytic Workspace 10.2.0.4.0 OPTION OFF
Oracle Data Mining 10.2.0.4.0 OPTION OFF
Oracle OLAP API 10.2.0.4.0 OPTION OFF
JServer JAVA Virtual Machine 11.2.0.3.0 VALID
Oracle Database Catalog Views 11.2.0.3.0 VALID
Oracle Database Java Packages 11.2.0.3.0 VALID
Oracle Database Packages and Types 11.2.0.3.0 VALID
Oracle Enterprise Manager 11.2.0.3.0 VALID
Oracle Expression Filter 11.2.0.3.0 VALID
Oracle Rules Manager 11.2.0.3.0 VALID
Oracle Text 11.2.0.3.0 VALID
Oracle Workspace Manager 11.2.0.3.0 VALID
Oracle XDK 11.2.0.3.0 VALID
Oracle XML Database 11.2.0.3.0 VALID
14 rows selected.This concluded the ugprade to 11.2.0.3. After the upgrade the database will exhibit three shared segment behavior that is only found on 11.2.0.3.
