AdnanDBA: Upgrading Oracle Database 10.2.0.1 to 10.2.0.5 in RedHat Linux 5 (patchset p8202632_10205

Steps to upgrade Oracle Database 10.2.0.1 to 10.2.0.5 :-

1. Download patchset p8202632_10205_Linux-x86-64.zip
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
Column version format a12
Column status format a6
Select comp_name, version, status from sys.dba_registry;SQL> SQL> SQL>

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
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

SQLPlus: 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

[oracle@OWB ~]$ sqlplus / as sysdba
SQLPlus: Release 10.2.0.1.0 - Production on Tue Oct 11 14:47:49 2016
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> shutdown 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@OWB ~]$ lsnrctl stop LISTENER
LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 11-OCT-2016 14:48:48
Copyright (c) 1991, 2005, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.135)(PORT=1521)))
The command completed successfully

[oracle@OWB ~]$ isqlplusctl stop
iSQLPlus 10.2.0.1.0
Copyright (c) 2003, 2005, Oracle. All rights reserved.
iSQLPlus instance on port 5560 is not running ...

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

[oracle@OWB Disk1]$ ./runInstaller
Starting Oracle Universal Installer...
Checking installer requirements...
Checking operating system version: must be redhat-3, SuSE-9, redhat-4, UnitedLinux-1.0, asianux-1 or asianux-2
                                     Passed
All installer requirements met.

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 :-
C. Start the database in UPGRADE mode :-

[oracle@OWB Disk1]$ cd $ORACLE_HOME
[oracle@OWB dbhome_1]$ cd rdbms/admin
[oracle@OWB admin]$ pwd
/u01/app/oracle/product/10.2.0/dbhome_1/rdbms/admin

[oracle@OWB admin]$ sqlplus / as sysdba
SQLPlus: Release 10.2.0.5.0 - Production on Tue Oct 11 15:04:44 2016
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to an idle instance.

SQL> startup upgrade;
ORACLE instance started.
Total System Global Area 536870912 bytes
Fixed Size                  2097624 bytes
Variable Size             159387176 bytes
Database Buffers          369098752 bytes
Redo Buffers                6287360 bytes
Database mounted.
Database opened.
SQL>

d . Run the preupgrade information tool :-

SQL> spool ugrade.log
SQL> @utlu102i.sql
Oracle Database 10.2 Upgrade Information Utility    10-11-2016 15:05:38
.

Database:

--> name:       ITDQF
--> 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: 486 MB
.... AUTOEXTEND additional space required: 6 MB
--> UNDOTBS1 tablespace is adequate for the upgrade.
.... minimum required size: 401 MB
.... AUTOEXTEND additional space required: 376 MB
--> SYSAUX tablespace is adequate for the upgrade.
.... minimum required size: 252 MB
.... AUTOEXTEND additional space required: 12 MB
--> TEMP tablespace is adequate for the upgrade.
.... minimum required size: 58 MB
.... AUTOEXTEND additional space required: 38 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

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
no rows selected
no rows selected
no rows selected
no rows selected
Session altered.
Session altered.
Table created.
.
.
.
.

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UPGRD_END 2016-10-11 15:24:16
.
Oracle Database 10.2 Upgrade Status Utility           10-11-2016 15:24:16
.
Component                                Status         Version HH:MM:SS
Oracle Database Server                    VALID      10.2.0.5.0 00:05:07
JServer JAVA Virtual Machine              VALID      10.2.0.5.0 00:00:56
Oracle XDK                                VALID      10.2.0.5.0 00:00:15
Oracle Database Java Packages             VALID      10.2.0.5.0 00:00:07
Oracle Text                               VALID      10.2.0.5.0 00:00:16
Oracle XML Database                       VALID      10.2.0.5.0 00:01:11
Oracle Workspace Manager                  VALID      10.2.0.5.0 00:00:23
Oracle Data Mining                        VALID      10.2.0.5.0 00:00:09
OLAP Analytic Workspace                   VALID      10.2.0.5.0 00:00:12
OLAP Catalog                              VALID      10.2.0.5.0 00:00:28
Oracle OLAP API                           VALID      10.2.0.5.0 00:00:21
Oracle interMedia                         VALID      10.2.0.5.0 00:01:43
Spatial                                   VALID      10.2.0.5.0 00:01:15
Oracle Expression Filter                  VALID      10.2.0.5.0 00:00:04
Oracle Enterprise Manager                 VALID      10.2.0.5.0 00:00:38
Oracle Rule Manager                       VALID      10.2.0.5.0 00:00:08
.
Total Upgrade Time: 00:14:26
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>

Upgrade completed.
5. Post Upgrade Steps :-

a. Shut Down your database and restart it and run utlrp.sql

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.
Total System Global Area 536870912 bytes
Fixed Size                  2097624 bytes
Variable Size             218107432 bytes
Database Buffers          310378496 bytes
Redo Buffers                6287360 bytes
Database mounted.
Database opened.
SQL>

SQL> @/u01/app/oracle/product/10.2.0/dbhome_1/rdbms/admin/utlrp.sql

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2016-10-11 15:26:17
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 2016-10-11 15:26:39
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
SQL>

B. Check version of database and 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>

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

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
SQL>

Database is Successfully Upgraded from 10.2.0.1 to 10.2.0.5.

1 comment:

Anonymous27 March 2022 at 01:20
Adnandba: Upgrading Oracle Database 10.2.0.1 To 10.2.0.5 In Redhat Linux 5 (Patchset P8202632_10205_Linux-X86-64.Zip) >>>>> Download Now

>>>>> Download Full

Adnandba: Upgrading Oracle Database 10.2.0.1 To 10.2.0.5 In Redhat Linux 5 (Patchset P8202632_10205_Linux-X86-64.Zip) >>>>> Download LINK

>>>>> Download Now

Adnandba: Upgrading Oracle Database 10.2.0.1 To 10.2.0.5 In Redhat Linux 5 (Patchset P8202632_10205_Linux-X86-64.Zip) >>>>> Download Full

>>>>> Download LINK

Upgrading Oracle Database 10.2.0.1 to 10.2.0.5 in RedHat Linux 5 (patchset p8202632_10205_Linux-x86-64.zip)

Steps to upgrade Oracle Database 10.2.0.1 to 10.2.0.5 :-

1 comment: