Upgrade Oracle database from 10.2.0.1 to 10.2.0.4

Oracle Database Upgrade from 10.2.0.1 to 10.2.0.4

Oracle Database 10g Release 2 (10.2.0.4) Patch Set 3 Installation steps:

1)This is not a complete software distribution. You must install it in an existing Oracle Database 10g Oracle home. To identify Oracle home directories, view the/etc/oratab file.

2)Oracle Database List of Bugs Fixed, 10g Release 2 (10.2.0.4) Patch Set
The List of Bugs Fixed is a list of all generic bugs related to Oracle Database that have been fixed to date.

3)Set the ORACLE_HOME and ORACLE_SID Environment Variables

$ ORACLE_HOME=Oracle_home
$ ORACLE_SID=sid
$ export ORACLE_HOME ORACLE_SID
In these examples, Oracle_home is the Oracle home directory where the Oracle Database 10g installation that you want to upgrade is installed, and sid is the system identifier of the database that you want to upgrade

4)Stop All Processes

it will be based on the Oracle Database Installation type:

•Stopping All Processes for a Single Instance Installation

we are using Single Instance Installation

Shut down the following Oracle Database 10g processes in the order specified before installing the patch set:

–>Shut down all processes in the Oracle home that might be accessing a database, for example Oracle Enterprise Manager Database Control or iSQL*Plus:

$ emctl stop dbconsole
$ isqlplusctl stop
$ lsnrctl stop

Note:
Before you shut down all processes that are monitored by Oracle Enterprise Manager Grid Control, set a blackout in Grid Control for the processes that you intend to shut down. This is necessary so that the availability records for these processes indicate that the shutdown was planned downtime rather than an unplanned system outage.

–>Shut down all database instances running in the Oracle home directory, where you need to install the patch set.
–>Shut down all listeners running in the Oracle home directory, where you need to install the patch set

5)Back Up the System
Oracle recommends that you create a backup of the Oracle Inventory, Oracle 10g home and Oracle 10g Database before you install the patch set. If you are planning to apply this patch to Oracle RAC, ensure that you create a backup of these components on all nodes before applying the patch

6)Installation Tasks

You can install the patch set either interactively or noninteractively. See one of the following sections for information about how to complete the installation:

•Installing the Oracle Database 10g Patch Set Interactively

•Installing the Oracle Database 10g Patch Set Noninteractively

–>To install the Oracle Database 10g patch set interactively:
Note:

If you attempt to install this patch set in an Oracle home directory that does not contain an Oracle Database 10g release 10.2.0.1 or higher installation, Oracle Universal Installer displays a warning dialog with the following error:

OUI-10091: There are no patches that need to be applied from the patch set Oracle Database 10g Release 2 Patch Set 3
10.2.0.4

The Oracle Universal Installer does not allow the installation to proceed. Click OK, then click Cancel to end the installation

1.Log in as the oracle user
2.Now to enable X applications, run the following command on the machine that you want to use to display Oracle Universal Installer:

$ xhost + [fully_qualified_remote_host_name]

Ex:xhost + dba.oracle.com

3.Enter the following commands to start Oracle Universal Installer, where patchset_directory is the directory where you unpacked the patch set software:

# cd patchset_directory/Disk1
# ./runInstaller

4.On the Welcome screen, click Next.

patchset1

5.On the Specify Home Details screen, select the name of the Oracle home that you want to update, or select the Path that you want to update from the list, then click Next. patchset2 patchset3

6.On the Summary screen, click Install. patchset4 patchset5

This screen lists all of the patches available for installation.

7.When prompted, run the $ORACLE_HOME/root.sh script as the root user.  patchset6 patchset7

8.On the End of Installation screen, click Exit, then click Yes to exit from Oracle Universal Installer. patchset8 patchset9

–>Installing the Oracle Database 10g Patch Set Noninteractively

To install the Oracle Database 10g patch set noninteractively:

1.Copy the response file template provided in the response directory where you unpacked the patch set archive file.

2.Edit the values for all fields labeled as <Value Required> as described by the comments and examples in the template.

3.To run Oracle Universal Installer, enter a command similar to the following, where response_file is the full path to the response file that you edited:

$ ./runInstaller -silent -responseFile response_file

4.After the installation, run the $ORACLE_HOME/root.sh script as the root user. If you are applying the patch set to an Oracle RAC installation, then run the root.sh script on each node of the cluster.

7)Postinstallation Tasks

we can do 2 ways to upgrade

1)Using DBUA
2)Manually
here we will follow manually

1)Run the Pre-Upgrade Information Tool

If you are upgrading database manually, then you should analyze it by running the Pre-Upgrade Information Tool.
The Pre-Upgrade Information Tool is a SQL script that ships with Oracle Database 10.2. Complete the following procedure to run the Pre-Upgrade Information Tool:

1.startup upgrade
SQL>startup upgrade

2.Set the system to spool results to a log file for later analysis:

SQL>spool upgrade_info.log

3.Run the Pre-Upgrade Information Tool:

@/u01/app/oracle/product/10.2.0/rdbms/admin/utlu102i.sql

4.Turn off the spooling of script results to the log file:

SQL>SPOOL OFF

Check the output of the Pre-Upgrade Information Tool in the upgrade_info.log file. The following is an example of the output generated by the Pre-Upgrade Information Tool:

Oracle Database 10.2 Upgrade Information Utility 01-22-2013 02:20:15
.
**********************************************************************
Database:
**********************************************************************
–> name: PROD
–> 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: 488 MB
…. AUTOEXTEND additional space required: 8 MB
–> UNDOTBS1 tablespace is adequate for the upgrade.
…. minimum required size: 400 MB
…. AUTOEXTEND additional space required: 370 MB
–> SYSAUX tablespace is adequate for the upgrade.
…. minimum required size: 246 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.

The following sections describe the output of the Pre-Upgrade Information Tool.

Database

This section displays global database information about the current database, such as the database name and release number before the database is upgraded.

Tablespaces

This section displays a list of tablespaces in the current database. For each tablespace, the tablespace name and minimum required size is displayed. In addition, a message is displayed if the tablespace is adequate for the upgrade. If the tablespace does not have enough free space, then space must be added to the tablespace in the current database. Tablespace adjustments must be made before the database is upgraded.

Update/Obsolete/Deprecated Parameters

These sections display a list of initialization parameters in the parameter file of the current database that should be adjusted before the database is upgraded. The adjustments must be made to the Oracle Database 10.2 init.ora or spfile.

Components

This section displays a list of database components that are upgraded or installed when the current database is upgraded.

Even though autoextend in enabled we have to add or resize tablespace or else we may get problem while upgrading and follow the below steps to move forward.

SQL>show user ‘SYS’
SQL> select FILE#,NAME from v$datafile;
SQL>alter database datafile 1 autoextend on maxsize unlimited;
SQL>alter database datafile 2 autoextend on maxsize unlimited;
SQL>alter database datafile 3 autoextend on maxsize unlimited;
SQL>alter database datafile 5 autoextend on maxsize unlimited;

SQL>ALTER DATABASE DATAFILE 1 RESIZE 500m;

SQL>ALTER DATABASE DATAFILE 2 RESIZE 800m;

SQL>ALTER DATABASE DATAFILE 3 RESIZE 270m;

SQL>ALTER DATABASE DATAFILE 5 RESIZE 70m;

SQL>ALTER TABLESPACE TEMP ADD TEMPFILE ‘/u01/app/oracle/oradata/PROD/temp02.dbf’ size 100m;

and shutdown the database before you are going to next step

8)Upgrading a Release 10.2 Database

After you install the patch set, you must perform the following steps on every database associated with the upgraded Oracle home:

Note:

If you do not run the catupgrd.sql script as described in this section and you start up a database for normal operation, then ORA-01092: ORACLE instance terminated. Disconnection forced errors will occur and the error ORA-39700: database must be opened with UPGRADE option will be in the alert log.

1.Log in as the Oracle software owner user.

2.start the listener as follows:

$ lsnrctl start

3.use SQL*Plus to log in to the database as the SYS user with SYSDBA privileges:

$ sqlplus /nolog
SQL> CONNECT SYS AS SYSDBA
Enter password:SYS_password

4.Enter the following SQL*Plus commands:

SQL> STARTUP UPGRADE
SQL> SPOOL patch.log
SQL> @/u01/app/oracle/product/10.2.0/rdbms/admin/catupgrd.sql
SQL> SPOOL OFF

5.Review the patch.log file for errors and inspect the list of components that is displayed at the end of catupgrd.sql script.

This list provides the version and status of each SERVER component in the database.

Oracle Database 10.2 Upgrade Status Utility 01-22-2013 04:06:07
.
Component Status Version HH:MM:SS
Oracle Database Server VALID 10.2.0.4.0 00:12:14
JServer JAVA Virtual Machine VALID 10.2.0.4.0 00:03:17
Oracle XDK VALID 10.2.0.4.0 00:00:49
Oracle Database Java Packages VALID 10.2.0.4.0 00:00:23
Oracle Text VALID 10.2.0.4.0 00:00:25
Oracle XML Database VALID 10.2.0.4.0 00:02:07
Oracle Workspace Manager VALID 10.2.0.4.3 00:00:49
Oracle Data Mining VALID 10.2.0.4.0 00:00:27
OLAP Analytic Workspace VALID 10.2.0.4.0 00:00:21
OLAP Catalog VALID 10.2.0.4.0 00:01:09
Oracle OLAP API VALID 10.2.0.4.0 00:00:57
Oracle interMedia VALID 10.2.0.4.0 00:04:54
Spatial VALID 10.2.0.4.0 00:02:11
Oracle Expression Filter VALID 10.2.0.4.0 00:00:13
Oracle Enterprise Manager VALID 10.2.0.4.0 00:01:52
Oracle Rule Manager VALID 10.2.0.4.0 00:00:08
.
Total Upgrade Time: 00:32: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>#######################################################################

6.If necessary, rerun the catupgrd.sql script after correcting any problems.

7.Restart the database:

SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP

8.Run the utlrp.sql script to recompile all invalid PL/SQL packages now instead of when the packages are accessed for the first time. This step is optional but recommended.

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

Note:

When the 10.2.0.4 patch set is applied to an Oracle Database 10g Standard Edition database, there may be 54 invalid objects after the utlrp.sql script runs. These objects belong to the unsupported components and do not affect the database operation.
Ignore any messages indicating that the database contains invalid recycle bin objects similar to the following:

BIN$4lzljWIt9gfgMFeM2hVSoA==$0

9.Run the following command to check the status of all the components after the upgrade:

SQL> select comp_name, version, status from sys.dba_registry;

In the output of the preceding command, the status of all the components should be VALID for a successful upgrade.

10.To configure and secure Enterprise Manager follow these steps:
Ensure the database and Listener are operational.
-In the case of a single instance, execute
#emca -upgrade db

Note:

If you are upgrading a database having dbcontrol configured in non-secure mode, after upgrade dbconsole will run in secure mode.

9)Running changePerm.sh Script on an Oracle Database Server Home

Important:

Oracle recommends using the most restrictive file permissions possible for the given implementation. Perform these optional steps only after considering all security ramifications and only if you need to share this installation.

During patch set installation, all new files and directories are created with restricted access, by default. Users or third party applications with a different group identifier from that of the database, which try to access client-side utilities or libraries in the database home, will see permission errors when trying to access these files or directories. Perform the following steps to change the permissions:

1.Change to the install directory by using the following command:

$ cd $ORACLE_HOME/install

2.Run changePerm.sh and specify the patched server Oracle home location, before accessing client-side utilities or libraries in the database home.

3.If you wish to disable connections with SYSDBA privileges, then re-create the password file with the nosysdba=y and force=y flags. Use the following syntax:

orapwd file=$ORACLE_HOME/dbs/orapwSID password=password force=y nosysdba=y

Here SID is the Oracle system identifier (SID) of the database, and password is the password for the SYS account.

Finish installing 10g patchset 3 and to check the version query it from

SQL>select banner from v$version;
BANNER
———————————————————————————————————————
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – Prod
PL/SQL Release 10.2.0.4.0 – Production
CORE 10.2.0.4.0 Production
TNS for Linux: Version 10.2.0.4.0 – Production
NLSRTL Version 10.2.0.4.0 – Production
===================================================================

Nagulu Polagani

"We are all apprentices in a craft where no one ever becomes a master."

Comments