Upgrade Oracle Database 10g to 11g Manual Upgrade

We are going to use Manual Upgrade

Install 11g database software in different ORACLE_HOME from source Database



oracle@apt-amd-02:/opt/app/oracle/product/11.1/rdbms/admin> pwd
/opt/app/oracle/product/11.1/rdbms/admin
oracle@apt-amd-02:/opt/app/oracle/product/11.1/rdbms/admin> ls -lt utlu111i.sql
-rw-r--r-- 1 oracle oinstall 138636 2007-07-11 09:01 utlu111i.sql
oracle@apt-amd-02:/opt/app/oracle/product/11.1/rdbms/admin> pwd
/opt/app/oracle/product/11.1/rdbms/admin
oracle@apt-amd-02:/opt/app/oracle/product/11.1/rdbms/admin> sqlplus /nolog

SQL*Plus: Release 10.2.0.4.0 - Production on Tue Apr 27 09:28:04 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

SQL> spool anujupgrade.lst

SQL> @utlu111i.sql
Oracle Database 11.1 Pre-Upgrade Information Tool 04-27-2010 09:29:25
.
**********************************************************************
Database:
**********************************************************************
--> name: VIHAAN
--> 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: 727 MB
.... AUTOEXTEND additional space required: 247 MB
--> UNDOTBS1 tablespace is adequate for the upgrade.
.... minimum required size: 473 MB
.... AUTOEXTEND additional space required: 443 MB
--> SYSAUX tablespace is adequate for the upgrade.
.... minimum required size: 425 MB
.... AUTOEXTEND additional space required: 175 MB
--> TEMP tablespace is adequate for the upgrade.
.... minimum required size: 61 MB
.... AUTOEXTEND additional space required: 41 MB
--> EXAMPLE tablespace is adequate for the upgrade.
.... minimum required size: 69 MB
.
**********************************************************************
Update Parameters: [Update Oracle Database 11.1 init.ora or spfile]
**********************************************************************
WARNING: --> "sga_target" needs to be increased to at least 672 MB
.
**********************************************************************
Renamed Parameters: [Update Oracle Database 11.1 init.ora or spfile]
**********************************************************************
-- No renamed parameters found. No changes are required.
.
**********************************************************************
Obsolete/Deprecated Parameters: [Update Oracle Database 11.1 init.ora or spfile]
**********************************************************************
--> "background_dump_dest" replaced by "diagnostic_dest"
--> "user_dump_dest" replaced by "diagnostic_dest"
--> "core_dump_dest" 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] VALID
--> OLAP Catalog [upgrade] VALID
--> 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] VALID
.
**********************************************************************
Miscellaneous Warnings
**********************************************************************
WARNING: --> Database contains stale optimizer statistics.
.... Refer to the 11g Upgrade Guide for instructions to update
.... statistics prior to upgrading the database.
.... Component Schemas with stale statistics:
.... SYS
.... OLAPSYS
.... SYSMAN
.... CTXSYS
.... XDB
.... MDSYS
WARNING: --> Database contains INVALID objects prior to upgrade.
.... USER PUBLIC has 1 INVALID objects.
.... USER SYS has 2 INVALID objects.
WARNING: --> Database contains schemas with objects dependent on network
packages.
.... Refer to the 11g Upgrade Guide for instructions to configure Network ACLs.
WARNING: --> EM Database Control Repository exists in the database.
.... Direct downgrade of EM Database Control is not supported. Refer to the
.... 11g Upgrade Guide for instructions to save the EM data prior to upgrade.
.

PL/SQL procedure successfully completed.

SQL> select * from v$timezone_file;
If time zone file version is less than 4 then apply time zone patch 5632264 manually

select object_name, owner, object_type from all_objects where status like 'INVALID';


SQL> select object_name, owner, object_type from all_objects where status like 'INVALID';

OBJECT_NAME OWNER
------------------------------ ------------------------------
OBJECT_TYPE
-------------------
DBMS_REGISTRY SYS
PACKAGE BODY

DBA_REGISTRY_DATABASE SYS
VIEW

DBA_REGISTRY_DATABASE PUBLIC
SYNONYM

col COMP_NAME format a50
set linesize 200
set pagesize 200
SQL> r
1* select comp_name,version, status from dba_registry

COMP_NAME VERSION STATUS
-------------------------------------------------- ------------------------------ --------------------------------------------
Oracle Database Catalog Views 10.2.0.4.0 VALID
Oracle Database Packages and Types 10.2.0.4.0 VALID
Oracle Workspace Manager 10.2.0.4.3 VALID
JServer JAVA Virtual Machine 10.2.0.4.0 VALID
Oracle XDK 10.2.0.4.0 VALID
Oracle Database Java Packages 10.2.0.4.0 VALID
Oracle Expression Filter 10.2.0.4.0 VALID
Oracle Data Mining 10.2.0.4.0 VALID
Oracle Text 10.2.0.4.0 VALID
Oracle XML Database 10.2.0.4.0 VALID
Oracle Rules Manager 10.2.0.4.0 VALID
Oracle interMedia 10.2.0.4.0 VALID
OLAP Analytic Workspace 10.2.0.4.0 VALID
Oracle OLAP API 10.2.0.4.0 VALID
OLAP Catalog 10.2.0.4.0 VALID
Spatial 10.2.0.4.0 VALID
Oracle Enterprise Manager 10.2.0.4.0 VALID

17 rows selected.



1.7 If you are using spfile, create pfile
SQL> create pfile from spfile ;

This will create pfile in 10g $ORACLE_HOME/dbs/init[SID].ora





a) Remove *.background_dump_dest, *.core_dump_dest, *.user_dump_dest and add
*.diagnostic_dest=’/11g_base’ (11g Base Directory)
b) Change
*.compatible='10.2.0.1.0'
to
*.compatible=’11.1.0′




vihaan.__db_cache_size=377487360
vihaan.__java_pool_size=4194304
vihaan.__large_pool_size=4194304
vihaan.__shared_pool_size=142606336
vihaan.__streams_pool_size=0
*.control_files='/opt/app/oracle/datafile/vihaan/control01.ctl','/opt/app/oracle/datafile/vihaan/control02.ctl','/opt/app/oracle/datafile/vihaan/control03.ctl'
*.db_block_size=8192
*.db_domain='apt-amd-02'
*.db_file_multiblock_read_count=16
*.db_name='vihaan'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=vihaanXDB)'
*.job_queue_processes=10
*.nls_language='ENGLISH'
*.nls_territory='UNITED KINGDOM'
*.open_cursors=300
*.pga_aggregate_target=199229440
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=536870912
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.compatible='11.1.0'
*.diagnostic_dest='/opt/app/oracle/admin/vihaan/diagnostic'



oracle@apt-amd-02:/opt/app/oracle/product/10.2/dbs> mkdir -p /opt/app/oracle/admin/vihaan/diagnostic
oracle@apt-amd-02:/opt/app/oracle/product/10.2/dbs> cd /opt/app/oracle/admin/vihaan/
oracle@apt-amd-02:/opt/app/oracle/admin/vihaan> ls -lt
total 8
drwxr-xr-x 2 oracle oinstall 6 2010-04-27 09:43 diagnostic
drwxr-x--- 2 oracle oinstall 4096 2010-04-27 09:29 adump
drwxr-x--- 2 oracle oinstall 4096 2010-04-27 09:20 udump
drwxr-x--- 2 oracle oinstall 141 2010-04-27 09:20 bdump
drwxr-x--- 2 oracle oinstall 35 2010-04-26 13:26 pfile
drwxr-x--- 2 oracle oinstall 6 2010-04-26 13:20 cdump
drwxr-x--- 2 oracle oinstall 6 2010-04-26 13:20 dpdump


2. Upgrade Database

Shut down source database (10g) - Your downtime starts here

SQL> connect sys/sys as sysdba
Connected.
SQL> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.



2.2 Set your environment variables to Oracle Database 11g Release 1 (11.1) :

export ORACLE_HOME=/u01/oracle/11gbase/11.1.0
export ORACLE_SID=TEST
export PATH=$ORACLE_HOME/bin:$PATH

export ORACLE_BASE=/opt/app/oracle
export ORACLE_HOME=/opt/app/oracle/product/10.2
#export ORACLE_HOME=/opt/app/oracle/product/11.1
export ORACLE_SID=vihaan
export PATH=$ORACLE_HOME/bin:$PATH:.
export LIBXCB_ALLOW_SLOPPY_LOCK=1
export TZ=GMT

comment the oracle 10g home and uncomment 11g




2.3 Start Upgrade
oracle@apt-amd-02:~> pwd
/home/oracle
oracle@apt-amd-02:~> sqlplus / as sysdba

SQL*Plus: Release 11.1.0.6.0 - Production on Tue Apr 27 09:50:25 2010

Copyright (c) 1982, 2007, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup upgrade pfile='/tmp/initvihaan.ora' ;
ORACLE instance started.

Total System Global Area 534462464 bytes
Fixed Size 2146112 bytes
Variable Size 150995136 bytes
Database Buffers 377487360 bytes
Redo Buffers 3833856 bytes
Database mounted.
Database opened.


Check shared_pool & java_pool size, to set new values



SQL> spool anujupgrade.log
SQL> @?/rdbms/admin/catupgrd.sql

after catupgrd.sql database will be shutdown .


SQL> /*****************************************************************************/
SQL> /* Step 10 - SHUTDOWN THE DATABASE..!!!!!
SQL> */
SQL> /*****************************************************************************/
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL>
SQL> DOC
DOC>#######################################################################
DOC>#######################################################################
DOC>
DOC> The above sql script is the final step of the upgrade. Please
DOC> review any errors in the spool log file. If there are any errors in
DOC> the spool file, consult the Oracle Database Upgrade Guide for
DOC> troubleshooting recommendations.
DOC>
DOC> Next restart for normal operation, and then run utlrp.sql to
DOC> recompile any invalid application objects.
DOC>
DOC>#######################################################################
DOC>#######################################################################
DOC>#
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> Rem Set errorlogging off
SQL> SET ERRORLOGGING OFF;
SQL>
SQL> Rem *********************************************************************
SQL> Rem END catupgrd.sql
SQL> Rem *********************************************************************
SQL>

=====


SQL> startup pfile='/tmp/initvihaan.ora' ;
ORACLE instance started.

Total System Global Area 534462464 bytes
Fixed Size 2146112 bytes
Variable Size 150995136 bytes
Database Buffers 377487360 bytes
Redo Buffers 3833856 bytes
Database mounted.
Database opened.

SQL> @?/rdbms/admin/utlu111s.sql




Oracle Database 11.1 Post-Upgrade Status Tool 04-27-2010 12:11:48
.
Component Status Version HH:MM:SS
.
Oracle Server
. VALID 11.1.0.6.0 00:42:49
JServer JAVA Virtual Machine
. VALID 11.1.0.6.0 00:20:34
Oracle Workspace Manager
. VALID 10.2.0.4.3 00:00:01
OLAP Analytic Workspace
. VALID 11.1.0.6.0 00:00:55
OLAP Catalog
. VALID 11.1.0.6.0 00:01:40
Oracle OLAP API
. VALID 11.1.0.6.0 00:00:26
Oracle Enterprise Manager
. ORA-06550: line 5, column 35:
. PL/SQL: ORA-00942: table or view does not exist
. ORA-06550: line 5, column 1:
. PL/SQL: SQL Statement ignored
. ORA-00001: unique constraint (SYSMAN.PARAMETERS_PRIMARY_KEY) violated
. ORA-06512: at "SYSMAN.MGMT_TIME_SYNC", line 108
. ORA-06512: at "SYSMAN.MGMT_TIME_SYNC", line 166
. ORA-06512: at line 2
. VALID 11.1.0.6.0 00:20:30
Oracle XDK
. VALID 11.1.0.6.0 00:01:37
Oracle Text
. VALID 11.1.0.6.0 00:01:41
Oracle XML Database
. VALID 11.1.0.6.0 00:13:43
Oracle Database Java Packages
. VALID 11.1.0.6.0 00:00:42
Oracle Multimedia
. VALID 11.1.0.6.0 00:09:04
Spatial
. VALID 11.1.0.6.0 00:06:41
Oracle Expression Filter
. VALID 11.1.0.6.0 00:00:15
Oracle Rules Manager
. VALID 11.1.0.6.0 00:00:13
Gathering Statistics
. 00:05:25
Total Upgrade Time: 02:06:28

PL/SQL procedure successfully completed.

SQL>
SQL> SET SERVEROUTPUT OFF
SQL> SET VERIFY ON



Workaround:

These errors do not result in any data loss. Therefore, you can ignore these errors.


===============================
or apply Patch 7449757

oracle@apt-amd-02:~> unzip p7449757_111070_Generic.zip
Archive: p7449757_111070_Generic.zip
creating: 7449757/
creating: 7449757/etc/
creating: 7449757/etc/xml/
inflating: 7449757/etc/xml/ShiphomeDirectoryStructure.xml
inflating: 7449757/etc/xml/GenericActions.xml
creating: 7449757/etc/config/
inflating: 7449757/etc/config/actions.xml
inflating: 7449757/etc/config/inventory.xml
inflating: 7449757/README.txt
creating: 7449757/files/
creating: 7449757/files/rdbms/
creating: 7449757/files/rdbms/admin/
inflating: 7449757/files/rdbms/admin/i1002000.sql

oracle@apt-amd-02:~> cd 7449757/
oracle@apt-amd-02:~/7449757> pwd
/home/oracle/7449757
oracle@apt-amd-02:~/7449757> /opt/app/oracle/product/11.1/OPatch/opatch apply
Invoking OPatch 11.1.0.6.0


====


SQL> @?/rdbms/admin/catuppst.sql
SQL> @?/rdbms/admin/utlrp.sql


SQL> create spfile from pfile='/tmp/initvihaan.ora' ;

File created.


Check invalid objects
SQL> select count(*) from dba_objects where status like 'INVALID';

COUNT(*)
----------
0


Post Upgrade steps

Check status of database components
SQL>select comp_name,version, status from dba_registry;

SQL> col COMP_NAME format a40
SQL> set pagesize 200
SQL> set linesize 200
SQL> col STATUS format a15
SQL> select comp_name,version, status from dba_registry

COMP_NAME VERSION STATUS
---------------------------------------- ------------------------------ ---------------
Oracle Enterprise Manager 11.1.0.6.0 VALID
OLAP Catalog 11.1.0.6.0 VALID
Spatial 11.1.0.6.0 VALID
Oracle Multimedia 11.1.0.6.0 VALID
Oracle XML Database 11.1.0.6.0 VALID
Oracle Text 11.1.0.6.0 VALID
Oracle Data Mining 11.1.0.6.0 VALID
Oracle Expression Filter 11.1.0.6.0 VALID
Oracle Rules Manager 11.1.0.6.0 VALID
Oracle Workspace Manager 10.2.0.4.3 VALID
Oracle Database Catalog Views 11.1.0.6.0 VALID
Oracle Database Packages and Types 11.1.0.6.0 VALID
JServer JAVA Virtual Machine 11.1.0.6.0 VALID
Oracle XDK 11.1.0.6.0 VALID
Oracle Database Java Packages 11.1.0.6.0 VALID
OLAP Analytic Workspace 11.1.0.6.0 VALID
Oracle OLAP API 11.1.0.6.0 VALID

17 rows selected.




Copy tnsnames.ora, listener.ora, sqlnet.ora and include file from source (10g)
oracle_home to target (11g) oracle_home


===
useful link

# 429825.1 Complete Checklist for Manual Upgrades to 11gR1
# 744693.1 ORA-00001 For SYS.DIANA_VERSION$ During RDBMS Upgrade From 11.1.0.6 To 11.1.0.7
# 413671.1 Applying version 4 Time Zone Files on an Oracle Database
# 396387.1 Workarounds when Database time zone patches are not available for your patchset
# 396671.1 Usage of utltzuv2.sql before updating time zone files in Oracle 10
# 730057.1 Upgrading to 11g Fails with ORA-01722: invalid number

Check Metalink note: Complete Checklist for Manual Upgrades to 11gR1 - 429825.1