Oracle Database Upgrade from 10.2.0.1 to 10.2.0.3

Long day off makes me tired of being at home so thought to do something for my friends........

People, who are new to Oracle world think that database upgrade is a big deal go walk with, yes - it is if we talk about a production and mission critical systems. But once you will go through this article and implement it at our own then you would realize - yeah, it is not that big deal ;)

In this section I will describe how we apply an oracle platform upgrade patch step by step to upgrade the database from 10.2.0.1. To 10.2.0.3

Pre-requisites:

1. First of all, stop all the running oracle components:

 [oracle@ora10srv bin]$ ./emctl stop dbconsole

TZ set to Asia/Calcutta

Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0

Copyright (c) 1996, 2005 Oracle Corporation.  All rights reserved.

http://ora10srv:1158/em/console/aboutApplication

Stopping Oracle Enterprise Manager 10g Database Control ...

 ...  Stopped.

[oracle@ora10srv bin]$ lsnrctl stop listener

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))

The command completed successfully

LSNRCTL>

 [oracle@ora10srv bin]$ ./isqlplusctl stop

iSQL*Plus 10.2.0.1.0

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

Stopping iSQL*Plus ...

iSQL*Plus stopped.

[oracle@ora10srv bin]$ sqlplus /nolog;

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Oct 28 21:45:47 2014

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

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

2. Second step is to take a backup of existing Oracle home for restore in case of failure in upgrade process to get our database operation back to its previous state for normal business operations.

 [oracle@ora10srv bin]$ echo $ORACLE_HOME

/u01/app/oracle/product/10.2.0/db_1

$tar –zcvf /u01/app//oracle/OraHomeBkp.tar.gz /u01/app/oracle/product/10.2.0/db_1

3. Take a database cold backup now.

Copy the following list of physical database file sets

a.       Datafile

b.      Control files

c.       Redo log files

d.      Pfile & password file can optionally be copies to backup location as well.

4. Extract the downloaded database patch to a /tmp or other location on the database server.

[oracle@ora10srv bin]$ Unzip p5337014_10203_LINUX.zip

It will unzip the archive to a folder named ‘Disk1’

Now navigate to the extracted folder “Disk1” and start the installer.

./runInstaller

Click on the Next Button.

  

Specify the Oracle Home Path and its Name – Click Next.

Click on Install.

Now login as root user on a separate terminal and run the root.sh script and then click on OK button.

 Click on Exit.

5. Start the Database in upgrade mode now.

6. Run the Database Upgrade Pre-Requisite Information Tool to get and implement the recommendation before actual upgrade script execution.

SQL> @$ORACLE_HOME/rdbms/admin/utlu102i.sql

Oracle Database 10.2 Upgrade Information Utility    10-28-2014 22:31:10

.**********************************************************************

Database:

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

--> name:       ORA10DB

--> version:    10.2.0.1.0

--> compatible: 10.2.0.1.0

--> blocksize:  8192

.**********************************************************************

Tablespaces: [make adjustments in the current environment]

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

--> SYSTEM tablespace is adequate for the upgrade.

.... minimum required size: 491 MB

.... AUTOEXTEND additional space required: 11 MB

--> UNDOTBS1 tablespace is adequate for the upgrade.

.... minimum required size: 13 MB

--> SYSAUX tablespace is adequate for the upgrade.

.... minimum required size: 235 MB

.... AUTOEXTEND additional space required: 5 MB

--> TEMP tablespace is adequate for the upgrade.

.... minimum required size: 58 MB

.... AUTOEXTEND additional space required: 38 MB

--> EXAMPLE tablespace is adequate for the upgrade.

.... minimum required size: 69 MB

.

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

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

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

WARNING: --> "streams_pool_size" needs to be increased to at least 50331648

.**********************************************************************

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

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

-- No renamed parameters found. No changes are required.

.**********************************************************************

Obsolete/Deprecated Parameters: [Update Oracle Database 10.2 init.ora or spfile]

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

-- No obsolete parameters found. No changes are required

.**********************************************************************

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 Java Packages         [upgrade]  VALID

--> Oracle Text                  [upgrade]  VALID

--> Oracle XML Database          [upgrade]  VALID

--> Oracle Workspace Manager     [upgrade]  VALID

--> Oracle Data Mining           [upgrade]  VALID

--> OLAP Analytic Workspace      [upgrade]  VALID

--> OLAP Catalog                 [upgrade]  VALID

--> Oracle OLAP API              [upgrade]  VALID

--> Oracle interMedia            [upgrade]  VALID

--> Spatial                      [upgrade]  VALID

--> Expression Filter            [upgrade]  VALID

--> EM Repository                [upgrade]  VALID

--> Rule Manager                 [upgrade]  VALID

.

PL/SQL procedure successfully completed.

Note: - Above you can see that Database upgrade Pre-Requisite Tool has given the recommendation for Streams_pool_size parameter to set to the recommended size so now we will alter that parameter with the recommended size and will run the pre-requisite tool again until it has no more recommendations to implement.

SQL> alter system set streams_pool_size=50331648 scope=both;

System altered.

Run the Pre-requisite Tool again:

SQL> @$ORACLE_HOME/rdbms/admin/utlu102i.sql

Oracle Database 10.2 Upgrade Information Utility    10-28-2014 22:37:14

.**********************************************************************

Database:

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

--> name:       ORA10DB

--> version:    10.2.0.1.0

--> compatible: 10.2.0.1.0

--> blocksize:  8192

.**********************************************************************

Tablespaces: [make adjustments in the current environment]

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

--> SYSTEM tablespace is adequate for the upgrade.

.... minimum required size: 491 MB

.... AUTOEXTEND additional space required: 11 MB

--> UNDOTBS1 tablespace is adequate for the upgrade.

.... minimum required size: 13 MB

--> SYSAUX tablespace is adequate for the upgrade.

.... minimum required size: 235 MB

.... AUTOEXTEND additional space required: 5 MB

--> TEMP tablespace is adequate for the upgrade.

.... minimum required size: 58 MB

.... AUTOEXTEND additional space required: 38 MB

--> EXAMPLE tablespace is adequate for the upgrade.

.... minimum required size: 69 MB

.**********************************************************************

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

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

-- No update parameter changes are required.

.**********************************************************************

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

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

-- No renamed parameters found. No changes are required.

.

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

Obsolete/Deprecated Parameters: [Update Oracle Database 10.2 init.ora or spfile]

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

-- No obsolete parameters found. No changes are required

.

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

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 Java Packages         [upgrade]  VALID

--> Oracle Text                  [upgrade]  VALID

--> Oracle XML Database          [upgrade]  VALID

--> Oracle Workspace Manager     [upgrade]  VALID

--> Oracle Data Mining           [upgrade]  VALID

--> OLAP Analytic Workspace      [upgrade]  VALID

--> OLAP Catalog                 [upgrade]  VALID

--> Oracle OLAP API              [upgrade]  VALID

--> Oracle interMedia            [upgrade]  VALID

--> Spatial                      [upgrade]  VALID

--> Expression Filter            [upgrade]  VALID

--> EM Repository                [upgrade]  VALID

--> Rule Manager                 [upgrade]  VALID

.PL/SQL procedure successfully completed.

SQL> spool off

7. Now you can see no more recommendation to implement so we would run the actual database upgrade script(catupgrd.sql) in next step.

SQL> @$ORACLE_HOME/rdbms/admin/catupgrd.sql

After running the above script just go out for a walk and have some tea/fun as it will take more than half an hour for a starter database to complete ;-)

DOC>######################################################################
DOC>######################################################################
DOC> The following statement will cause an "ORA-01722: invalid number"
DOC> error if the user running this script is not SYS.  Disconnect
DOC> and reconnect with AS SYSDBA.

DOC>######################################################################
DOC>######################################################################
DOC>#no rows selected
DOC>#######################################################################
DOC>#######################################################################
DOC> The following statements will cause an "ORA-01722: invalid number"
DOC> error if the SYSAUX tablespace does not exist or is not
DOC> ONLINE for READ WRITE, PERMANENT, EXTENT MANAGEMENT LOCAL, and
DOC> SEGMENT SPACE MANAGEMENT AUTO.
DOC>
DOC> The SYSAUX tablespace is used in 10.1 to consolidate data from
DOC> a number of tablespaces that were separate in prior releases.
DOC> Consult the Oracle Database Upgrade Guide for sizing estimates.
DOC>
DOC> Create the SYSAUX tablespace, for example,
DOC>
DOC> create tablespace SYSAUX datafile 'sysaux01.dbf'
DOC>      size 70M reuse
DOC>      extent management local
DOC>      segment space management auto
DOC>      online;
DOC>
DOC> Then rerun the catupgrd.sql script.
DOC>#######################################################################
DOC>#######################################################################
DOC>#no rows selected
no rows selected
no rows selected
no rows selected
no rows selected
Session altered.
Session altered.
Table created.
2 rows deleted.
1 row created.
Commit complete.
TIMESTAMP                                                                      ------------------------------------------------------------                   COMP_TIMESTAMP UPGRD__BGN 2014-10-28 22:38:32 2456959 81512                    

Table created.
Index created.
Table created.
Index created.
Table created.
Index created.
Index created.
Table created.
Index created.
Index created.
Table created.
Index created.
Table created.
Table altered.……………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………TIMESTAMP--------------------------------------------------------------------------------COMP_TIMESTAMP UPGRD_END  2014-10-28 23:11:29.Oracle Database 10.2 Upgrade Status Utility           10-28-2014 23:11:29.Component                                Status         Version  HH:MM:SSOracle Database Server                    VALID      10.2.0.3.0  00:15:26JServer JAVA Virtual Machine              VALID      10.2.0.3.0  00:04:53Oracle XDK                                VALID      10.2.0.3.0  00:00:33Oracle Database Java Packages             VALID      10.2.0.3.0  00:00:23Oracle Text                               VALID      10.2.0.3.0  00:00:32Oracle XML Database                       VALID      10.2.0.3.0  00:00:52Oracle Data Mining                        VALID      10.2.0.3.0  00:00:26OLAP Analytic Workspace                   VALID      10.2.0.3.0  00:00:36OLAP Catalog                              VALID      10.2.0.3.0  00:01:10Oracle OLAP API                           VALID      10.2.0.3.0  00:00:54Oracle interMedia                         VALID      10.2.0.3.0  00:03:35Spatial                                   VALID      10.2.0.3.0  00:01:04Oracle Expression Filter                  VALID      10.2.0.3.0  00:00:13Oracle Enterprise Manager                 VALID      10.2.0.3.0  00:02:07Oracle Rule Manager                       VALID      10.2.0.3.0  00:00:07.Total Upgrade Time: 00:32:56
DOC>#######################################################################
DOC>#######################################################################
DOC>
DOC>   The above PL/SQL lists the SERVER components in the upgraded
DOC>   database, along with their current version and status.
DOC>
DOC>   Please review the status and version columns and look for
DOC>   any errors in the spool log file.  If there are errors in the spool
DOC>   file, or any components are not VALID or not the current version,
DOC>   consult the Oracle Database Upgrade Guide for troubleshooting
DOC>   recommendations.
DOC>
DOC>   Next shutdown immediate, restart for normal operation, and then
DOC>   run utlrp.sql to recompile any invalid application objects.
DOC>
DOC>#######################################################################
DOC>#######################################################################
DOC>#
SQL>


SQL> spool off

8. Wow…… database upgrade script finished with success as above we can see no any database component in INVALID state so let’s restart the database instance now normally.

SQL> shutdown immediate;
Database closed.

SQL> startup
ORACLE instance started.
Total System Global Area  436207616 bytes
Fixed Size                  1262236 bytes
Variable Size             222301540 bytes
Database Buffers          209715200 bytes
Redo Buffers                2928640 bytes
Database mounted.
Database opened.

9. The database upgrade may leave many database objects in INVALID state so in order to compile those INVALID objects we would have to run the oracle provided re-compile in-build tool making it in VALID state.

SQL> @/ORACLE_HOME/rdbms/admin/utlrp.sql

10. Finally, Let’s verify if our all database components are upgraded and are in VALID state.

SQL> set linesize 220
SQL> column COMP_NAME format a50;
SQL> select comp_name, version, status from sys.dba_registry;

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

17 rows selected.

[oracle@ora10srv bin]$ ./sqlplus /nolog

SQL*Plus: Release 10.2.0.3.0 - Production on Wed Oct 29 00:56:22 2014Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

SQL> conn /as sysdba
Connected.

SQL> select * from v$version;

BANNER
----------------------------------------------------------------Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 
- ProdPL/SQL Release 10.2.0.3.0 
- ProductionCORE 10.2.0.3.0 
- ProductionTNS for Linux: Version 10.2.0.3.0 
- ProductionNLSRTL Version 10.2.0.3.0 - Production

Yes..looks fine as all oracle database components got upgraded successfully to 10.2.0.3 with VALID state ……….Enjoy the DB upgrade now :-)