Sunday , October 22 2017
Home / Oracle DBA / Upgrading to Oracle Database 10G to 11G

Upgrading to Oracle Database 10G to 11G

Upgrading to Oracle Database 10g to 11g

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.
spool anujupgrade.lst
@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
.
**********************************************************************
Table spaces: [make adjustments in the current environment]
**********************************************************************
–> SYSTEM table space is adequate for the upgrade.
…. minimum required size: 727 MB
…. AUTO EXTEND additional space required: 247 MB
–> UN DOTBS1 table space is adequate for the upgrade.
…. minimum required size: 473 MB
…. AUTO EXTEND additional space required: 443 MB
–> SYS AUX table space is adequate for the upgrade.
…. minimum required size: 425 MB
…. AUTO EXTEND additional space required: 175 MB
–> TEMP table space is adequate for the upgrade.
…. minimum required size: 61 MB
…. AUTO EXTEND additional space required: 41 MB
–> EXAMPLE table space is adequate for the upgrade.
…. minimum required size: 69 MB
.
**********************************************************************
Update Parameters: [Update Oracle Database 11.1 init.ora or s p file]
**********************************************************************
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’;

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 SERVER OUTPUT 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
HOW TO APPLY THE PATCH ——–>>

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.

Read More:
Performance Tuning Using AWR, ASH and ADDM
Performance Tuning
Transportable Tablespaces

Comments

comments

Check Also

Moving a database from Normal File System to ASM storage

Default storage for oracle database is File System, where database files resides on local storage. …

Leave a Reply

Your email address will not be published. Required fields are marked *