Monday, September 20, 2010

Upgrade ASM and database from 10g to 11g

Links to this post

The upgrade will fall in three phase

1) Install 11g software.

2) Upgrade ASM

3) Upgrade database.


phase 1) Install 11g software.



























phase 2) upgrade ASM

Oracle cluster Synchronization Services (CSS) needs to be upgraded as follow:

Connect as root user and execute:

export ORACLE_HOME=/u01/app/oracle/product/11.1/db_1

Where: $ORACLE_HOME is your new 11g ASM Oracle Home.

$ORACLE_HOME/bin/localconfig reset





Startup the ASM instance using the old ASM Oracle Home e.g. 10.1.0.5.



From another graphical session, set the environment variables

(ORACLE_HOME, PATH, etc.) pointing to your new ASM Oracle Home 11g

Execute the DBUA from the new 11g ASM Oracle Home:

$ORACLE_HOME/bin/dbua

Where: $ORACLE_HOME is the new ASM Oracle Home





DBUA will show 2 options:

=)> upgrade a database

=)> upgrade ASM instance.

Select [upgrade ASM instance] option.

The DBUA will upgrade your ASM instance to the latest release automatically.

























Finally, please startup the databases that are using ASM as

storage option.

Note: The databases will not be affected with the ASM upgrade.

The databases will remain in the original 10g version only since they do not share the same Oracle Home.

START ASM INSTANCE FROM NEW 11G ORACLE_HOME note:it wil be already started


PHASE 3) UPGRADE DATABASE.


START DATABASE from old ORACLE_HOME 10.2.0.1

$ORACLE_HOME/rdbms/admin/utlu111i.sql

SQL*Plus: Release 10.2.0.1.0 - Production on Sat Sep 4 18:32:16 2010

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

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

SQL> @/u01/app/oracle/product/11.1/db_1/rdbms/admin/utlu111i.sql

Oracle Database 11.1 Pre-Upgrade Information Tool 09-04-2010 18:35:20

.

**********************************************************************

Database:

**********************************************************************

--> name: PRODASM

--> version: 10.2.0.1.0

--> compatible: 10.2.0.1.0

--> blocksize: 8192

--> platform: Linux IA (32-bit)

--> timezone file: V2

.

**********************************************************************

Tablespaces: [make adjustments in the current environment]

**********************************************************************

--> SYSTEM tablespace is adequate for the upgrade.

.... minimum required size: 721 MB

.... AUTOEXTEND additional space required: 241 MB

--> UNDOTBS1 tablespace is adequate for the upgrade.

.... minimum required size: 472 MB

.... AUTOEXTEND additional space required: 447 MB

--> SYSAUX tablespace is adequate for the upgrade.

.... minimum required size: 410 MB

.... AUTOEXTEND additional space required: 180 MB

--> TEMP tablespace is adequate for the upgrade.

.... minimum required size: 61 MB

.... AUTOEXTEND additional space required: 41 MB

.

**********************************************************************

Update Parameters: [Update Oracle Database 11.1 init.ora or spfile]

**********************************************************************

WARNING: --> "sga_target" needs to be increased to at least 336 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 is using an old timezone file version.

.... Patch the 10.2.0.1.0 database to timezone file version 4

.... BEFORE upgrading the database. Re-run utlu111i.sql after

.... patching the database to record the new timezone file version.

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

WARNING: --> Database contains schemas with objects dependent on network

packages.

.... Refer to the 11g Upgrade Guide for instructions to configure Network ACLs.

.... USER SYSMAN has dependent objects.

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.

NOTE: TO AVOID BELOW ERROR APPLY PATCH 5632264

WARNING: --> Database is using an old timezone file version.

.... Patch the 10.2.0.1.0 database to timezone file version 4

.... BEFORE upgrading the database. Re-run utlu111i.sql after

.... patching the database to record the new timezone file version.

if patch cant apply do as below

The manual process is:

a) download the correct 5632264 patch for your base version and platform.

b) unzip the 6672979 patch

c) make a backup of the 2 *.dat files in your $ORACLE_HOME/oracore/zoneinfo

directory and removed them (Databases may be running but if possible stop them to avoid any file locks)

d) copy the 2 *.dat files and the readme.txt from the unzipped patch 5632264/files/oracore/zoneinfo directory to your $ORACLE_HOME/oracore/zoneinfo directory (Databases may be running but if possible stop them to avoid any file locks).

e) stop all databases using this $ORACLE_HOME and after all databases are stopped start them again

startup database

SQL> select * from v$timezone_file;

FILENAME VERSION

------------ ----------

timezlrg.dat 4


EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

PL/SQL procedure successfully completed.

EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SYS');

PL/SQL procedure successfully completed.

create pfile from spfile;

copy pfile from old home to new 11g home

cp initPRODASM.ora /u01/app/oracle/product/11.1/db_1/dbs

cp orapwPRODASM /u01/app/oracle/product/11.1/db_1/dbs

copy init+ASM.ora if its not there on new 11g home/dbs

change init parameters in new file

export new 11g path

export ORACLE_SID=PRODASM

export ORACLE_BASE=/u01/app/oracle

export ORACLE_HOME=$ORACLE_BASE/product/11.1/db_1

#export ORA_CRS_HOME=$ORACLE_BASE/product/10.2.0/crs_1

export LD_LIBRARY_PATH=$ORACLE_HOME/lib

export PATH=$ORACLE_HOME/bin:$ORA_CRS_HOME/bin:/bin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/X11R6/bin

update /etc/oratab

#PRODASM:/u01/app/oracle/product/10.2.0/db_1:N

PRODASM:/u01/app/oracle/product/11.1/db_1:N

+ASM:/u01/app/oracle/product/11.1/db_1:N

export parameters

PRDASM-> cp initPRODASM.ora /u01/app/oracle/product/11.1/db_1/dbs

PRDASM-> cp orapwPRODASM /u01/app/oracle/product/11.1/db_1/dbs

PRDASM-> export ORACLE_HOME=$ORACLE_BASE/product/11.1/db_1

PRDASM-> export ORACLE_SID=PRODASM

PRDASM-> RS_HOME/bin:/bin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/X11R6/bin <

PRDASM-> which sqlplus

/u01/app/oracle/product/11.1/db_1/bin/sqlplus

PRDASM-> sqlplus / as sysdba

SQL*Plus: Release 11.1.0.6.0 - Production on Sat Sep 4 19:49:23 2010

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

Connected to an idle instance.

SQL> startup upgrade

ORA-32004: obsolete and/or deprecated parameter(s) specified

ORACLE instance started.

Total System Global Area 188313600 bytes

Fixed Size 1298780 bytes

Variable Size 71306916 bytes

Database Buffers 113246208 bytes

Redo Buffers 2461696 bytes

Database mounted.

Database opened.

19) run catupgrd.sql

SQL> @/u01/app/oracle/product/11.1/db_1/rdbms/admin/catupgrd.sql

check if any component or database object is not a invalid