There's a earlier blog about
upgrading from 10g to 11gR2. This blog is to add some missing points in the
previous blog and additional information with regard to upgrading from 10.2.0.4 to 11.2.0.3. Another post is available with
upgrading RAC environments from 10.2.0.5 to 11.2.0.3
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 VALID
Running
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
Verifying the timezone upgrade
SQL> select version from v$timezone_file;
VERSION
----------
14Components status in the database registry
COMP_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 25
It 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 9114
Metalink 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 followsSQL> 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.
