About Me

My photo
Bangalore, India
I am an Oracle Certified Professional working in SAP Labs. Everything written on my blog has been tested on my local environment, Please test before implementing or running in production. You can contact me at amit.rath0708@gmail.com.

Saturday, September 28, 2013

step by step Upgrading Oracle Database 10.2.0.1 to 10.2.0.5 in RedHat Linux 5

PFB steps to upgrade Oracle Database 10.2.0.1 to 10.2.0.5 :-

1. Download patchset p8202632_10205_Linux-x86-64.zip  from metalink for respective OS, in my case for RHEL 5.

2. Unzip patchset to respective directory .

3. Backup your database before Upgrade.

4. Prerequisites for applying patchset 10.2.0.5

a.  Check Version of all registry components :-

SQL> Column comp_name format a40
SQL> Column version format a12
SQL> Column status format a6
SQL> Select comp_name, version, status from sys.dba_registry;

COMP_NAME                                VERSION      STATUS
---------------------------------------- ------------ ------
Oracle Database Catalog Views            10.2.0.1.0   VALID
Oracle Database Packages and Types       10.2.0.1.0   VALID
Oracle Workspace Manager                 10.2.0.1.0   VALID
JServer JAVA Virtual Machine             10.2.0.1.0   VALID
Oracle XDK                               10.2.0.1.0   VALID
Oracle Database Java Packages            10.2.0.1.0   VALID
Oracle Expression Filter                 10.2.0.1.0   VALID
Oracle Data Mining                       10.2.0.1.0   VALID
Oracle Text                              10.2.0.1.0   VALID
Oracle XML Database                      10.2.0.1.0   VALID
Oracle Rules Manager                     10.2.0.1.0   VALID

COMP_NAME                                VERSION      STATUS
---------------------------------------- ------------ ------
Oracle interMedia                        10.2.0.1.0   VALID
OLAP Analytic Workspace                  10.2.0.1.0   VALID
Oracle OLAP API                          10.2.0.1.0   VALID
OLAP Catalog                             10.2.0.1.0   VALID
Spatial                                  10.2.0.1.0   VALID
Oracle Enterprise Manager                10.2.0.1.0   VALID

17 rows selected.

b. Check version of database :-

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

c. Check INVALID objects in database :-

SQL> select object_name,status from dba_objects where status='INVALID';

no rows selected

if invalid objects exists then run below command :-

SQL> exec utl_recomp.recomp_serial ();

PL/SQL procedure successfully completed.

d. Check Version of TimeZone, Manage your data with timezone before upgrade :-

SQL> set lin 400
SQL> select version from v$timezone_file;

   VERSION
----------
         2

if current timezone version equals 2 then move forward with upgrade process
if current timezone version greater than 4 then check on metalink Note 553812.1.
if current timezone is less than 4 then PFB steps:-

Download utltzpv4.zip from metalink and run it on your database :-

[oracle@localhost DBSOFT]$ unzip utltzpv4.zip
Archive:  utltzpv4.zip
  inflating: utltzpv4.sql
[oracle@localhost DBSOFT]$ sqlplus

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Sep 27 10:53:14 2013

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

Enter user-name: /as sysdba

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> @utltzpv4.sql
DROP TABLE sys.sys_tzuv2_temptab CASCADE CONSTRAINTS
               *
ERROR at line 1:
ORA-00942: table or view does not exist

Table created.

DROP TABLE sys.sys_tzuv2_affected_regions CASCADE CONSTRAINTS
               *
ERROR at line 1:
ORA-00942: table or view does not exist

Table created.

Your current timezone version is 2!
now checking all TIMESTAMP WITH TIMEZONE data..
.
Do a select * from sys.sys_tzuv2_temptab; to see if any TIMEZONE
WITH TIMEZONE data is affected by the update to RDBMS DSTv4 in the patchset.
.
Any table with YES in the nested_tab column (last column) needs
a manual check as these are nested tables.

PL/SQL procedure successfully completed.

Commit complete.

SQL> column table_owner format a4
column column_name format a18
select * from sys_tzuv2_temptab;SQL> SQL>

TABL TABLE_NAME                     COLUMN_NAME          ROWCOUNT NES
---- ------------------------------ ------------------ ---------- ---
SYS  SCHEDULER$_JOB                 LAST_ENABLED_TIME           3
SYS  SCHEDULER$_JOB                 LAST_END_DATE               1
SYS  SCHEDULER$_JOB                 LAST_START_DATE             1
SYS  SCHEDULER$_JOB                 START_DATE                  1
SYS  SCHEDULER$_JOB_RUN_DETAILS     REQ_START_DATE              1
SYS  SCHEDULER$_JOB_RUN_DETAILS     START_DATE                  1
SYS  SCHEDULER$_WINDOW              LAST_START_DATE             2

7 rows selected.

If the output of above query returns "NO ROWS" then move forward with the upgrade.
If output contains column names containing TZ data, which will be affected by the upgrade then see metalink Note 553812.1.
If output contains scheduler objects owned by SYS then we can ignore those, but if it contains user owned objects then take a backup and restore them after the upgrade.

4. Start the Upgrade :-

a. Stop database , listener, EM console, isqlplus(optional)

[oracle@localhost ~]$ sqlplus

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Sep 27 13:20:57 2013

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

Enter user-name: /as sysdba

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
[oracle@localhost ~]$ 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://localhost.localdomain:1158/em/console/aboutApplication
Stopping Oracle Enterprise Manager 10g Database Control ...
 ...  Stopped.
[oracle@localhost ~]$ lsnrctl stop

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 27-SEP-2013 13:23:34

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
The command completed successfully
[oracle@localhost ~]$ isqlplusctl stop
iSQL*Plus 10.2.0.1.0
Copyright (c) 2003, 2005, Oracle.  All rights reserved.
Stopping iSQL*Plus ...
iSQL*Plus stopped.

b. unzip the p8202632_10205_Linux-x86-64.zip file , and run start applying patch

[oracle@localhost Disk1]$ ./runInstaller 
Starting Oracle Universal Installer...

Checking installer requirements...

Checking operating system version: must be redhat-3, SuSE-9, SuSE-10, redhat-4, redhat-5, redhat-6, UnitedLinux-1.0, asianux-1, asianux-2, asianux-3, enterprise-4, enterprise-5 or SuSE-11
                                      Passed

All installer requirements met.

Preparing to launch Oracle Universal Installer from /tmp/OraInstall2013-09-27_01-32-30PM. Please wait ...[oracle@localhost Disk1]$







Kernal parameters for 10.2.0.1 and 10.2.0.5 are different , Kindly change that.






Now Oracle Home is patched with 10.2.0.5 patch , Now upgrade your database :-

[oracle@localhost ~]$ cd $ORACLE_HOME
[oracle@localhost app]$ pwd
/oracle_home/app
[oracle@localhost app]$ cd rdbms/admin/

c. Start the database in UPGRADE mode :-

[oracle@localhost admin]$ sqlplus

SQL*Plus: Release 10.2.0.5.0 - Production on Fri Sep 27 14:31:38 2013

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

Enter user-name: /as sysdba
Connected to an idle instance.

SQL> startup upgrade
ORACLE instance started.

Total System Global Area  599785472 bytes
Fixed Size                  2098112 bytes
Variable Size             163580992 bytes
Database Buffers          427819008 bytes
Redo Buffers                6287360 bytes
Database mounted.
Database opened.

d . Run the preupgrade information tool :-

SQL> spool ugrade.log
SQL> @utlu102i.sql
Oracle Database 10.2 Upgrade Information Utility    09-27-2013 14:32:49
.
**********************************************************************
Database:
**********************************************************************
--> name:       ORCL
--> 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: 490 MB
.... AUTOEXTEND additional space required: 10 MB
--> UNDOTBS1 tablespace is adequate for the upgrade.
.... minimum required size: 403 MB
.... AUTOEXTEND additional space required: 368 MB
--> SYSAUX tablespace is adequate for the upgrade.
.... minimum required size: 256 MB
.... AUTOEXTEND additional space required: 16 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>
SQL> spool off

Reflect the changes mentioned by above utility and start the Upgrade process

SQL> spool final_upgrade.log
SQL> @catupgrd.sql
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 statement will cause an "ORA-01722: invalid number"
DOC>    error if the database server version is not correct for this script.
DOC>    Shutdown ABORT and use a different script or a different server.
DOC>######################################################################
DOC>######################################################################
DOC>#

no rows selected

DOC>#######################################################################
DOC>#######################################################################
DOC>   The following statement will cause an "ORA-01722: invalid number"
DOC>   error if the database has not been opened for UPGRADE.
DOC>
DOC>   Perform a "SHUTDOWN ABORT"  and
DOC>   restart using UPGRADE.
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

.
.
.
..
.
..
.

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UPGRD_END  2013-09-27 14:59:32
.
Oracle Database 10.2 Upgrade Status Utility           09-27-2013 14:59:32
.
Component                                Status         Version  HH:MM:SS
Oracle Database Server                    VALID      10.2.0.5.0  00:08:52
JServer JAVA Virtual Machine              VALID      10.2.0.5.0  00:02:22
Oracle XDK                                VALID      10.2.0.5.0  00:00:24
Oracle Database Java Packages             VALID      10.2.0.5.0  00:00:18
Oracle Text                               VALID      10.2.0.5.0  00:00:26
Oracle XML Database                       VALID      10.2.0.5.0  00:01:35
Oracle Workspace Manager                  VALID      10.2.0.5.0  00:00:47
Oracle Data Mining                        VALID      10.2.0.5.0  00:00:26
OLAP Analytic Workspace                   VALID      10.2.0.5.0  00:00:21
OLAP Catalog                              VALID      10.2.0.5.0  00:01:07
Oracle OLAP API                           VALID      10.2.0.5.0  00:00:54
Oracle interMedia                         VALID      10.2.0.5.0  00:02:57
Spatial                                   VALID      10.2.0.5.0  00:02:01
Oracle Expression Filter                  VALID      10.2.0.5.0  00:00:13
Oracle Enterprise Manager                 VALID      10.2.0.5.0  00:01:13
Oracle Rule Manager                       VALID      10.2.0.5.0  00:00:08
.
Total Upgrade Time: 00:25:24
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>#

Upgrade completed. 

5. Post Upgrade Steps :-

a. Shut Down your database and restart it.

SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  599785472 bytes
Fixed Size                  2098112 bytes
Variable Size             209718336 bytes
Database Buffers          381681664 bytes
Redo Buffers                6287360 bytes
Database mounted.
Database opened.

b. Check Invalid objects after upgrade :-

SQL> @utlrp.sql

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN  2013-09-27 15:41:49
DOC>   The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC>   objects in the database. Recompilation time is proportional to the
DOC>   number of invalid objects in the database, so this command may take
DOC>   a long time to execute on a database with a large number of invalid
DOC>   objects.
DOC>
DOC>   Use the following queries to track recompilation progress:
DOC>
DOC>   1. Query returning the number of invalid objects remaining. This
DOC>      number should decrease with time.
DOC>         SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC>   2. Query returning the number of objects compiled so far. This number
DOC>      should increase with time.
DOC>         SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC>   This script automatically chooses serial or parallel recompilation
DOC>   based on the number of CPUs available (parameter cpu_count) multiplied
DOC>   by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC>   On RAC, this number is added across all RAC nodes.
DOC>
DOC>   UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC>   recompilation. Jobs are created without instance affinity so that they
DOC>   can migrate across RAC nodes. Use the following queries to verify
DOC>   whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC>   1. Query showing jobs created by UTL_RECOMP
DOC>         SELECT job_name FROM dba_scheduler_jobs
DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC>   2. Query showing UTL_RECOMP jobs that are running
DOC>         SELECT job_name FROM dba_scheduler_running_jobs
DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END  2013-09-27 15:42:55
DOC> The following query reports the number of objects that have compiled
DOC> with errors (objects that compile with errors have status set to 3 in
DOC> obj$). If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#

OBJECTS WITH ERRORS
-------------------
                  0
DOC> The following query reports the number of errors caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC>#

ERRORS DURING RECOMPILATION
---------------------------
                          0

c. Check Version of database and all components:-

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE    10.2.0.5.0      Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production

SQL> Column comp_name format a40
SQL> Column version format a12
SQL> Column status format a6
SQL> Select comp_name, version, status from sys.dba_registry;

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

Database is Successfully Upgraded from 10.2.0.1 to 10.2.0.5. 

I hope this article helped you.

Regards,
Amit Rath

8 comments:

  1. Hi Amit,

    We are planning to upgrade our R12 apps db from 10.2.0.3 to 10.2.0.5, will you pls share step by step methods to apply patch and will there be any changes we need to perform in application level and there is oracle discoverer configured in R12 server.

    My email id is jaisrisubramani1991@gmail.com

    ReplyDelete
  2. Hi Amit,

    Our DB time zone version is 3, as per your article i am not able to find utltzpv4.zip from metalink. Kindly suggest me from where i can get this sql file.

    ReplyDelete
  3. Very helpfull. Thank you.

    ReplyDelete
  4. its helped me a lot

    ReplyDelete
  5. Great post.Thanks for sharing your knowledge!!!
    Regards, Alvaro

    ReplyDelete
  6. Simply wish to say your article is as astounding.
    The clearness to your publish is just spectacular and
    i could suppose you are a professional in this subject.
    Fine along with your permission let me to grab your feed to stay up to date with imminent post.
    Thanks a million and please keep up the gratifying work.

    ReplyDelete
  7. Hello there, just became aware of your blog through Google,
    and found that it is truly informative. I'm going to watch out for brussels.

    I will appreciate if you continue this in future.

    A lot of people will be benefited from your writing.
    Cheers!

    ReplyDelete
  8. Hey there, You have done an incredible job. I will definitely
    digg it and personally recommend to my friends.
    I am sure they'll be benefited from this web site.

    ReplyDelete