Basic Concept: Upgradation/Migration Process

Before starting actual operation we will understand some of the basic concept related to Migration and Upgradation tools and technique in oracle database.

Upgradation:  Upgrade is the process of replacing our existing software with a newer version of the same product type. Upgrading our applications usually does not require special tools. Our existing reports should look and behave the same in both products. However, sometimes minor changes may be seen in product. Upgradation is done at Software level. For Example: Replacing Oracle 9i release to oracle 10g release, SQL Server 2005 to SQL Server 2008

Migration:   Migration is the process of replicating applications from one product type to another product type. A migration is any change that transforms our hardware and/or software architecture to a new state (process to transfer database between different OS). Migration is done as database level. For example:  Migrating data from DB2 to Oracle, Oracle to SQL server and Transforming existing Oracle 9i application to oracle 10g application.
Now It is easier to upgrade your existing Oracle Database. In the past, there were several tedious, manual processes that had to be outlined and documented, tested and proven, prior to upgrading. With Oracle Database 10g, the upgrade process is available through the Oracle 10g Enterprise Manager. All tasks, processes, and post-upgrade tasks are available after your installation of the Oracle Database 10g binaries. There is direct upgrade from 8.1.7.4, 9.0.1.4 or higher, 9.2.0.4 or higher, and 10.1.0.2 or higher to the newest Oracle Database 10g release is supported.
Look at the below figure to see what each part of release number represents.
Best of my knowledge you can Migrate/Upgrade your database through the following ways:

  1. Use Export/Import Utilities.
  2. Use the Database Upgrade Assistant (DBUA) tool to Upgrade.
  3. Manual Upgrade using SQL Scripts.
  4. Use of ‘CREATE TABLE AS SQL Statement’.
  5. Use of RMAN

Unlike DBUA or a manual upgrade, the Export and Import or Oracle Data Pump utilities physically copy data from your current database to a new database. This upgrade method does not change the current database, which enables the database to remain available throughout the upgrade process.

The benefit of doing and EXPORT and IMPORT are you get to unload and reload data thus coalescing space, resetting your high water marks, and an opportunity to clean up your database and the negative side is if you have huge data it will take time through export/import.
Where as the DBUA can perform Migration/Upgration quickly and easily with its GUI wizard. When you install oracle 10g on that database server, it will detect the presence of oracle 9i and ask you, want it to be migrated to the latest version. You can also launch this tools later. It allows you to upgrade the database in place. You have to be at 9.2.0.4+ to be able to use the DBUA. It provides support for Oracle Real Application Clusters (RAC). In an Oracle RAC environment, DBUA upgrades all the database and configuration files on all nodes in the cluster.
A manual upgrade consists of running SQL scripts and utilities from a command line to upgrade a database to the new Oracle Database release. It keeps your full intention or control over the upgrade process.
If you are planning Migrate/Upgrade your database then you must read latest release guide. For example if you are going to upgrade oracle 9i to 10g then read this guide Oracle Database 10g Release 2 (10.2) Upgrade Guide before actual task performing and make a note for every important aspect and apply first it is on test environment. The release guide contains more depth information, will solve your lot of Migration/Upgration related issues. There are many new features in Oracle 10g which you might want to read about in the  Oracle Database 10g Release 2 (10.2) New Features Guide.

Oracle provides the latest updates, pre-upgrade, post upgrade compatibility on My Oracle Support: https://support.oracle.com. You are also able to find here latest information about patch set, pre-upgrade information tools and other companion.

Steps or Role of DBA during Upgrade/Migrate:

  1. Read the Upgrade/Migrate release guide and try to collect new features.
  2. Make a note for preparing Upgrade/Migrate
  3. Apply the process on Test Environment.
  4. Test the Upgraded Test database.
  5. Prepare and preserve the Production DB (Estimate the downtime requires for backup and upgrade)
  6. Upgrade/Migrate the Production DB
  7. Tune and Adjust the Production DB

Click on the below Link for the Example of different way of Upgradation/Migration in Oracle Database:

Upgrading Step by Step Oracle 9i to 10g with DBUA

Manual Upgrading Oracle 9i database to 10g

Migrating or Transferring data from Oracle 9i to 10g using import/export

Migrate from 32 bit to 64 bit using RMAN

Post Upgradation Task for Oracle Database

Upgrading Oracle Database Server 10g Release 10.2.0.1 to Oracle Database 10g Release 10.2.0.3 on Linux/Fedora core 6

Note: This is only for Oracle Database Server Upgrade.
If you have Oracle E-Business Suite, Physical/Logical Standby database, RAC, and Other separate options (i.e. RMAN) enabled in place, please read the README.html document included in the Patchset (5337014).

Details:

Operating Version Details: Linux localhost.localdomain 2.6.18-1.2798.fc6 #1 SMP Mon Oct 16 14:54:20 EDT 2006 i686 i686 i386 GNU/Linux
Oracle Version Details (Current) : Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
Oracle Instance Name: testdb
Patchset (to be applied on) : p5337014_10203_LINUX.zip (Downloaded from http://www.metalink.oracle.com/)

Requirements for installing the Patchset # 5337014

Oracle Universal Installer Version Requirements
The Oracle Universal Installer release must be 10.1.0.3 (This includes in the Patchset).

System Requirements
* Operating System must be redhat-3, SuSE-9, SuSE-10, redhat-4, UnitedLinux-1.0, asianux-1 or asianux-2
Note: Installation/Upgrade of Oracle 10g on Fedora Core 6 Is not supported by Oracle, but as a workaround, we had to change the the /etc/redhat-release file replacing the current release information (Fedora Core release 6 (Zod)) with redhat-4
* Oracle Database 10g (10.2.0.1) or later

Preinstallation Tasks

To download and extract the patch set installation software:
* Download the p5337014_10203_LINUX.zip patch set installation archive to a directory “/home/oracle/patches” that is not the Oracle home directory or under the Oracle home directory.
* To unzip and extract the installation files:
$ unzip p5337014_10203_LINUX.zip

Set the ORACLE_HOME and ORACLE_SID Environment Variables
Bourne, Bash, or Korn shell:·
$ ORACLE_HOME=/u01/app/oracle
$ ORACLE_SID=testdb·
$ export ORACLE_HOME ORACLE_SID
C shell:·
% setenv ORACLE_HOME /u01/app/oracle
% setenv ORACLE_SID testdb
The oracle_home is the Oracle home directory where the Oracle10g installation that you want to upgrade is installed, and sid is the SID of the database that you want to upgrade.

Stopping All Processes for a Single Instance Installation
### To stop Listener Service
$lsnrctl
LSNRCTL> stop
### To stop Enterprise Manager Service
$ emctl stop dbconsole
### To stop iSQLPlus Service
$ isqlplusctl stop
### To Shut Down Oracle Databases
$ sqlplus /nolog
$ connect /as sysdba
SQL> SHUTDOWN IMMEDIATE;

Back Up the System
Oracle recommends that you create a backup of the Oracle10g installation before you install the patch set.We took the backup of Oracle home and all the database files.

Installation Tasks

Installing the Oracle Database 10g Patch Set Interactively
** Log in as the oracle user.
su – oracle
password:
** If you are not installing the software on the local system then, run the following command on remote machine:
Bourne, Bash, or Korn shell:
$ export DISPLAY=localhost:0.0
C shell:
% setenv DISPLAY local_host:0.0
Now to enable X applications, run the following command on the machine that you want to use to display Oracle Universal Installer (your workstation or PC):
$ xhost + [fully_qualified_remote_host_name]
** To start Oracle Universal Installer, where patchset_directory is the directory where you unpacked the patch set software
$ cd /home/oracle/patches/Disk1
$ ./runInstaller
** On the Welcome screen, click Next.
** In 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.
** On the Summary screen, click Install.
This screen lists all of the patches available for installation.
** When prompted, run the $ORACLE_HOME/root.sh script as the root user.
** On the End of Installation screen, click Exit, then click Yes to exit from Oracle Universal Installer.

Postinstallation Tasks

Upgrading of Oracle 10g Software from Release 10.2.0.1 to 10.2.0.3 is now successfully done. Now, earlier Oracle Database needs to be upgraded from 10.2.0.1 to 10.2.0.3. Oracle recommends two ways to upgrade the database, one is using DBUA (Database Upgrade Assistance), and another is Manual database upgrade. Here we have followed manual method for upgrade.

Manually Upgrading a Release 10.2 Database

* Required Preupgrade Checks
** Check the SYSTEM Tablespace Size
SQL> SELECT sum(bytes/1024/1024) “Size in MB”
2 FROM dba_free_space
3 WHERE tablespace_name=’SYSTEM’;

Size in MB
———-
97.125

We have enough free space in SYSTEM tablespace.
If not enough free space in SYSTEM tablespace, then add/resize the datafile as below.
## TO add a datafile
ALTER TABLESPACE SYSTEM
ADD ‘ /u02/app/oradata/unic/system02.dbf’
SIZE 100M;
## To resize a existing datafile.
ALTER DATABASE DATAFILE ‘ /u02/app/oradata/unic/system01.dbf’
RESIZE 500M;

* Set the SHARED_POOL_SIZE and JAVA_POOL_SIZE Initialization Parameters
##Start the database with the NOMOUNT option:
SQL> STARTUP NOMOUNT
## To check initialization parameter file (initsid.ora or spfilesid.ora)
SQL> SHOW PARAMETER PFILE;
## To determine the current values of these parameters:
SQL> SHOW PARAMETER SHARED_POOL_SIZE
SQL> SHOW PARAMETER JAVA_POOL_SIZE
##If the system is using a server parameter file:
If necessary, set the value of the SHARED_POOL_SIZE initialization parameter to at least 150 MB:
SQL> ALTER SYSTEM SET SHARED_POOL_SIZE=’150M’ SCOPE=spfile;
If necessary, set the value of the JAVA_POOL_SIZE initialization parameter to at least 150 MB:
SQL> ALTER SYSTEM SET JAVA_POOL_SIZE=’150M’ SCOPE=spfile;
##If the system uses an initialization parameter file
If necessary change the values of the SHARED_POOL_SIZE and the JAVA_POOL_SIZE initialization parameters to at least 150 MB in the initialization parameter file (initsid.ora).

Shut down the database:
SQL> SHUTDOWN

Upgrading a Release 10.2 Database
##Login as Oracle User
Su – oracle
Password:
##Start the Listener
$lsnrctl
LSNRCTL> START
##Connect to sqlplus as sysdba and start the database in upgrade mode
$sqlplus /nolog
SQL> connect /as sysdba
SQL> STARTUP UPGRADE;
##Run the catupgrd.sql scrips

SQL> SPOOL patch.log

SQL> @?/rdbms/admin/catupgrd.sql

SQL> SPOOL OFF;

Review the patch.log file for errors and inspect the list of components that is displayed at the

end of catupgrd.sql script.

## Restart the database:

SQL> SHUTDOWN IMMEDIATE;

SQL> STARTUP;

Run the utlrp.sql script to recompile all invalid PL/SQL packages now instead of when the packages are accessed for the first time.

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

Start All Oracle Related Services

To Check Current Oracle Version

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

Common Errors:

** 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 10.2.0.2 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 2
10.2.0.3

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

** If you do not run the Oracle Database Upgrade Assistant as described in this section, then the following errors are displayed:
ORA-01092: ORACLE instance terminated.
ORA-39700: database must be opened with UPGRADE option.

$sqlplus /nolog
SQL> connect /as sysdba
SQL>STARTUP UPGRADE;Reference:

Follow the README.html document of patch set # 5337014

Regards,

Sabdar Syed.

Tuesday, February 20, 2007

Upgrade Oracle Database from 9.2.0.1 to 9.2.0.7 on Soalris 10 (Sun Sparc)

As per the requirement I have upgrded one of our test database from the Version Oracle 9.2.0.1 to 9.2.0.7 on Solaris 10 (Sun Sparc)

Details:

Operating Version Details : SunOS libtest 5.10 Generic_118833-23 sun4u sparc SUNW,Sun-Fire
Oracle Version Details (Current) : Oracle9i Enterprise Edition Release 9.2.0.1.0 – 64bit Production
Oracle Instance Name : testdb
Patchset (to be applied on) : p4163445_9207_solaris64.zip (Downloaded from www.metalink.oracle.com)

Requirements for installing the patchset # 4163445

Oracle Universal Installer Version Requirements
The Oracle Universal Installer release must be 10.1.0.4 (This includes in the patchset).

System Requirements
* Operating System is Solaris 10 (But it can be Solaris 8 or 9)
* Oracle9i release 2 (9.2.0.1.0) or later

Preinstallation Tasks

Upgrade and SYS Schema

During an upgrade from release 9.2.0.1 or later, the catpatch.sql script can take a long time if there are statistics for the SYS schema. Delete the statistics on all of the objects in the SYS schema, and then recollect the statistics after normal database open, if necessary.

To drop and re-create the statistics.
SQL> EXECUTE DBMS_STATS.DELETE_SCHEMA_STATS(‘SYS’);
SQL> EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS(‘SYS’);

Download and Extract the Installation Software

To download and extract the patch set installation software:
Download and extract the p4163445_9207_solaris64.zip patch set installation archive to a directory ” /oracle9i/9207_patch”

To unzip and extract the installation files:
$ unzip p4163445_9207_solaris64.zip

Set the ORACLE_HOME and ORACLE_SID Environment Variables

To set the ORACLE_HOME and ORACLE_SID environment variables:
Bourne, Bash, or Korn shell:

$ ORACLE_HOME=/oracle9i
$ ORACLE_SID=testdb
$ export ORACLE_HOME ORACLE_SID

C shell:

% setenv ORACLE_HOME /oracle9i
% setenv ORACLE_SID testdb

The oracle_home is the Oracle home directory where the Oracle9i installation that you want to upgrade is installed, and sid is the SID of the database that you want to upgrade.

Shut Down Oracle Databases

Shut down any existing Oracle database instances with normal or immediate priority.
SQL> SHUTDOWN IMMEDIATE;

Stop All Processes

Stop all listener and other processes running in the Oracle home directory where you want to install the patch set.
LSNRCTL> stop

Back Up the System

Oracle recommends that you create a backup of the Oracle9i installation before you install the patch set.
We took the backup of Oracle home and all the database files.

Installation Tasks

Installing the Patch Set Interactively

To install the patch set interactively:
* Log in as the Oracle software owner (typically oracle).
su – oracle
* If you are not installing the software on the local system, enter the following command to direct X applications to display on the local system:
Bourne, Bash, or Korn shell:
$ DISPLAY=libtest:0.0 ; export DISPLAY
C shell:
% setenv DISPLAY libtest:0.0
Here, local_host is the host name or IP address of the system that you want to use to display the Installer (your workstation or PC).
* Enter following commands to start the Installer where patchset_directory is the directory where you unzipped the patch set software:
$ cd /oracle9i/9207_patch/Disk1
$ ./runInstaller
* On the Welcome screen, click Next.
* On the Specify File Locations screen, click Browse next to the Path field in the Source section.
* Select the products.xml file from the stage directory where you unpacked the patch set files, then click Next. i,e
/oracle9i/9207_patch/Disk1/stage/products.xml
* In the Name field in the Destination section, select the name of the Oracle home that you want to update from the drop down list, then click Next.
Name: Home1
Path: /oracle9i.
* On the Summary screen, click Install.
* This screen lists all of the patches available for installation.
* When prompted, run the $ORACLE_HOME/root.sh script as the root user.
* On the End of Installation screen, click Exit, then click Yes to exit from the Installer.

Postinstallation Tasks

Required Postinstallation Tasks

Check SYSTEM Tablespace Size
If JServer is part of the installation ensure that there is at least 10 MB of free space allocated to the SYSTEM tablespace.
SQL> alter database datafile ‘/u02/oradata/testdb/SYSTEM01.DBF’ resize 500m;
Database altered.

Set the SHARED_POOL_SIZE and JAVA_POOL_SIZE Initialization Parameters
Set the value of the SHARED_POOL_SIZE and the JAVA_POOL_SIZE initialization parameters as follows:

* Start the database:
SQL> STARTUP;

* If necessary, enter the following command to determine whether the system uses an initialization parameter file (initsid.ora) or a server parameter file (spfiledbname.ora):
SQL> SHOW PARAMETER PFILE;

* This command displays the name and location of the server parameter file or the initialization parameter file.
Determine the current values of these parameters:
SQL> SHOW PARAMETER SHARED_POOL_SIZE
SQL>
SHOW PARAMETER JAVA_POOL_SIZE

* If the system is using a server parameter file:
If necessary, set the value of the SHARED_POOL_SIZE initialization parameter to at least 150 MB:
SQL> ALTER SYSTEM SET SHARED_POOL_SIZE=’150M’ SCOPE=spfile;

* If necessary, set the value of the JAVA_POOL_SIZE initialization parameter to at least 150 MB:
SQL> ALTER SYSTEM SET JAVA_POOL_SIZE=’150M’ SCOPE=spfile;

* If the system uses an initialization parameter file, if necessary change the values of the SHARED_POOL_SIZE and the JAVA_POOL_SIZE initialization parameters to at least 150 MB in the initialization parameter file (initsid.ora).

* Shut down the database:
SQL> SHUTDOWN

Upgrade the Database

After you install the patch set, you must perform the following steps on every database associated with the upgraded Oracle home:
* Log in as the Oracle software owner (typically oracle).
su – oracle

* Start the Oracle Net listener as follows:
$ lsnrctl start

* For single-instance installations, use SQL*Plus to log in to the database as the SYS user with SYSDBA privileges:
$ sqlplus /nolog
SQL> CONNECT / AS SYSDBA

* Enter the following SQL*Plus commands:
SQL> STARTUP MIGRATE
SQL> SPOOL patch.log
SQL> @?/rdbms/admin/catpatch.sql
SQL> SPOOL OFF

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

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

* If necessary, rerun the catpatch.sql script after correcting any problems.

* Restart the database:
SQL> SHUTDOWN
SQL> STARTUP

* 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> @?/rdbms/admin/utlrp.sql

Conclusion: It is importnat note that We have followed the above process as per our requirement and perticular limites to our test instance. But this is not the complete upgrade procedure, please refer the read me document of README for 4163445 ” Oracle9i Patch Set Notes Release 2 (9.2.0.7) Patch Set 6 for Solaris Operating System (SPARC 64-bit) ” .

Question with Answer on Oracle database Patches

Patches are a small collection of files copied over to an existing installation. They are associated with particular versions of Oracle products.

The discussion will especially help for those beginners who are preparing for interview and inexperienced to apply the patches. In this article you will find all those things briefly with an example. For more details please study the oracle documentation and try to search with separate topics on this blog.

What are different Types of Patches?

Regular Patcheset: To upgrade to higher version we use database patchset. Please do not confuse between regular patchests and patch set updates (PSU). Consider the regular patchset is super set of PSU. Regular Patchset contain major bug fixes. In comparison to regular patch PSU will not change the version of oracle binaries such as sqlplus, import/export etc. The importance of PSU is automatically minimized once a regular patchset is released for a given version. It is mainly divided into two types:

Security or Critical Patch Update (CPU): Critical patch update quarterly delivered by oracle to fix security issues.

Patch set updated (PSU): It include CPU and bunch of other one-off patches. It is also quarterly delivered by oracle.

Interim (one-off) Patch: It is also known as patchset exception or one-off patch or interim patch. This is usually a single fix for single problem or enhancement. It released only when there is need of immediate fix or enhancement that cannot wait until for next release of patchset or bundle patch. It is applied using OPATCH utility and is not cumulative.

Bundle Patches: Bundle Patches includes both the quarterly security patches as well as recommended fixes (for Windows and Exadata only). When you try to download this patch you will find bundle of patches (different set of file) instead of single downloaded file (usually incase patchset).

Is Opatch (utility) is also another type of patch?

OPatch is utility from oracle corp. (Java based utility) that helps you in applying interim patches to Oracle’s software and rolling back interim patches from Oracle’s software. Opatch also able to Report already installed interim patch and can detect conflict when already interim patch has been applied. This program requires Java to be available on your system and requires installation of OUI. Thus from the above discussion coming to your question it is not ideal to say OPATCH is another patch.

When we applying single Patch, can you use OPATCH utility?

Yes, you can use Opatch incase of single patch. The only type of patch that cannot be used with OPatch is a patchset

When you applying Patchsets, You can use OUI.

Yes, Patcheset uses OUI. A patch set contains a large number of merged patches, to change the version of the product or introduce new functionality. Patch sets are cumulative bug fixes that fix all bugs and consume all patches since the last base release. Patch sets and the Patch Set Assistant are usually applied through OUI-based product specific installers.

Can you Apply OPATCH without downtime?

As you know for apply patch your database and listener must be down. When you apply OPTACH it will update your current ORACLE_HOME. Thus coming to your question to the point in fact it is not possible in case of single instance but in RAC you can Apply Opatch without downtime as there will be more separate ORACLE_HOME and more separate instances (running once instance on each ORACLE_HOME).

You have collection of patch (nearly 100 patches) or patchset. How can you apply only one patch from patcheset or patch bundle at ORACLE_HOME?
With Napply itself (by providing patch location and specific patch id) you can apply only one patch from a collection of extracted patch. For more information check the opatch util NApply –help. It will give you clear picture.

For Example:

opatch util napply <patch_location> -id 9 -skip_subset -skip_duplicate

This will apply only the patch id 9 from the patch location and will skip duplicate and subset of patch installed in your ORACLE_HOME.

How can you get minimum/detail information from inventory about patches applied and components installed?

You can try below command for minimum and detail information from inventory
C:ORACLE_HOMEOpatchopatch lsinventory –invPtrLoc “location of oraInst.loc file”

$ORACLE_HOMEOPatchopatch lsinventory -detail -invPtrLoc “location of oraInst.loc file”
Differentiate Patcheset, CPU and PSU patch? What kind of errors usually resolved from them?

Critical Patch Update (CPU) was the original quarterly patches that were released by oracle to target the specific security fixes in various products. CPU is a subset of patchset updates (PSU). CPU are built on the base patchset version where as PSU are built on the base of previous PSU

Patch Set Updates (PSUs) are also released quarterly along with CPU patches are a superset of CPU patches in the term that PSU patch will include CPU patches and some other bug fixes released by oracle. PSU contain fixes for bugs that contain wrong results, Data Corruption etc but it doe not contain fixes for bugs that that may result in: Dictionary changes, Major Algorithm changes, Architectural changes, Optimizer plan changes

Regular patchset: Please do not confuse between regular patchests and patch set updates (PSU). Consider the regular patchset is super set of PSU. Regular Patchset contain major bug fixes. The importance of PSU is minimizing once a regular patchset is released for a given version. In comparison to regular patch PSU will not change the version of oracle binaries such as sqlplus, import/export etc.

If both CPU and PSU are available for given version which one, you will prefer to apply?
From the above discussion it is clear once you apply the PSU then the recommended way is to apply the next PSU only. In fact, no need to apply CPU on the top of PSU as PSU contain CPU (If you apply CPU over PSU will considered you are trying to rollback the PSU and will require more effort in fact). So if you have not decided or applied any of the patches then, I will suggest you to go to use PSU patches. For more details refer: Oracle Products [ID 1430923.1], ID 1446582.1

PSU is superset of CPU then why someone choose to apply a CPU rather than a PSU?

CPUs are smaller and more focused than PSU and mostly deal with security issues. It seems to be theoretically more consecutive approach and can cause less trouble than PSU as it has less code changing in it. Thus any one who is concerned only with security fixes and not functionality fixes, CPU may be good approach.

How can you find the PSU installed version?

PSU references at 5th place in the oracle version number which makes it easier to track such as (e.g. 10.2.0.3.1). To determine the PSU version installed, use OPATCH utility:

OPATCH lsinv -bugs_fixed | grep -i  PSU

To find from the database:

Select substr(action_time,1,30) action_time, substr(id,1,10) id, substr(action,1,10) action,substr(version,1,8) version, substr(BUNDLE_SERIES,1,6) bundle, substr(comments,1,20) comments from registry$history;

Note: You can find the details from the above query if you already executed the catbundle.sql

Click to Check Existing Oracle Database Patch Status

Will Patch Application affect System Performance?
Sometimes applying certain patch could affect Application performance of SQL statements. Thus it is recommended to collect a set of performance statistics that can serve as a baseline before we make any major changes like applying a patch to the system.

Can you stop applying a patch after applying it to a few nodes? What are the possible issues?

Yes, it is possible to stop applying a patch after applying it to a few nodes. There is a prompt that allows you to stop applying the patch. But, Oracle recommends that you do not do this because you cannot apply another patch until the process is restarted and all the nodes are patched or the partially applied patch is rolled back.

How you know impact of patch before applying a patch?

OPATCH <option> -report

You can use the above command to know the impact of the patch before actually applying it.

How can you run patching in scripted mode?

opatch <option> -silent

You can use the above command to run the patches in scripted mode.

Can you use OPATCH 10.2 to apply 10.1 patches?

No, Opatch 10.2 is not backward compatible. You can use Opatch 10.2 only to apply 10.2 patches.

What you will do if you lost or corrupted your Central Inventory?

In that case when you lost or corrupted your Central Inventory and your ORACLE_HOME is safe, you just need to execute the command with –attachHomeflag, OUI automatically setup the Central Inventory for attached home.

What you will do if you lost your Oracle home inventory (comps.xml)?

Oracle recommended backup your ORACLE_HOME before applying any patchset. In that case either you can restore your ORACLE_HOME from the backup or perform the identical installation of the ORACLE_HOME.

When I apply a patchset or an interim patch in RAC, the patch is not propagated to some of my nodes. What do I do in that case?

In a RAC environment, the inventory contains a list of nodes associated with an Oracle home. It is important that during the application of a patchset or an interim patch, the inventory is correctly populated with the list of nodes. If the inventory is not correctly populated with values, the patch is propagated only to some of the nodes in the cluster.

OUI allows you to update the inventory.xml with the nodes available in the cluster using the -updateNodeList flag in Oracle Universal Installer.

When I apply a patch, getting the following errors:

“Opatch Session cannot load inventory for the given Oracle Home <Home_Location> Possible causes are: No read or write permission to ORACLE_HOME/.patch_storage; Central Inventory is locked by another OUI instance; No read permission to Central Inventory; The lock file exists in ORACLE_HOME/.patch_storage; The Oracle Home does not exist in Central Inventory”. What do I do?

This error may occur because of any one or more of the following reasons:

  • – The ORACLE_HOME/.patch_storage may not have read/write permissions. Ensure that you give read/write permissions to this folder and apply the patch again.
  • – There may be another OUI instance running. Stop it and try applying the patch again.
  • – The Central Inventory may not have read permission. Ensure that you have given read permission to the Central Inventory and apply the patch again.
  • – The ORACLE_HOME/.patch_storage directory might be locked. If this directory is locked, you will find a file named patch_locked inside this directory. This may be due to a previously failed installation of a patch. To remove the lock, restore the Oracle home and remove the patch_locked file from the ORACLE_HOME/.patch_storage
  • – The Oracle home may not be present in the Central Inventory. This may be due to a corrupted or lost inventory or the inventory may not be registered in the Central Inventory.

We should check for the latest security patches on the Oracle metalink website http://metalink.oracle.com/ and we can find the regular security alert at the location http://technet.oracle.com/deploy/security/alert.htm

Caution: It is not advisable to apply the patches directly into the production server. The ideal solution is to apply or test the patches in test server before being moved into the production system.

About Patching in Oracle

Patches are a small collection of files copied over to an existing installation. They are associated with particular versions of Oracle products.
The article is specially for beginners who don’t know about patching, how to apply it, how to get patches, what are the step or process when you need to apply patches  and what are the precaution to be considered while applying patching. Here in this article you will find all those things briefly with an example. For more details please study the oracle documentation.

Types of Patches:

Database patches are various kinds but mainly categories into three parts:

  1. Patchset Exception (also known as PSE, one-off, interim patch)
  2. Patch bundle (also known as MLR patch or Patchset)
  3. Security Patches (also know as CPU and PSU)
  4. Patchset

PSE, one-off or interim patch: This is usually a single fix for single problem. Interim patches are bug fixes given to the customers in critical need and are not cumulative, regressed or versioned. It is applied via opatch. You can use opatch lsinventory command to see which one-off patch are installed on your system.

MRL patch: This type of patch is created by putting several fixes into a single patch. It is also applied using opatch.

Security Patches: Security Patches are mainly divided into two types

  • (i) CPU (Critical Patch Update): These patches are quarterly delivered by oracle to fix security issues.  It is also applied using opatch.
  • (ii) PSU (Patch Set Updates): Patchset updated also quarterly delivered by oracle. It includes CPU and a bunch of other one-off patches. CPU and PSU are Cumulative but we can stop to apply CPU if not required.

Patchset: Patchsets are applied via OUI (Oracle Universal Installer). It is usually applied to upgrade oracle version. For Example if you want to upgrade oracle version from 9.2.0.1.0 to 9.2.0.7.0 then Apply the patchset 4163445. you can find the stepwise details how to Apply patchset on my blog: http://shahiddba.blogspot.com/

About Opatch:

OPatch is a Java-based utility that runs on all supported operating systems and requires installation of the Oracle Universal Installer.

Types of Patches That can not be Used with OPatch

The only type of patch that cannot be used with OPatch is a patchset. A patchset contains a large number of merged patches, is thoroughly tested, changes the version of the product it is applied to, can sometimes introduce new functionality, and should be applied when suitable. Patch sets are cumulative bug fixes that fix all bugs and consume all patches since the last base release. Patchset are usually applied through OUI-based product specific installers.

How to Get Opatch:

  • (i) Access and log into My Oracle Support at the following location: http://support.oracle.com/
  • (ii) In the Search Knowledge Base field, enter 224346.1. This is the ID of the document that describes how to obtain the latest version of OPatch.
  • (iii) In the search results, click on the link corresponding to document ID 224346.1.
  • (iv) In the document, click on the Patch 6880880 link which will take you to the screen where you can obtain the latest version of OPatch based on release versions and platforms.

OPatch System Requirements

The OPatch utility has the following requirements:

  • The ORACLE_HOME environment variable must point to a valid Oracle home directory and match the value used during installation of the Oracle home directory.
  • If the -invPtrLoc command-line argument was used during installation, and then it must be used when using the OPatch utility. Oracle recommends the use of the default central inventory for a platform.
  • The java, ar, cp, and make commands must be available in one of the directories listed in the PATH environment variable. The commands are not available for all platforms.

Getting Patches:

  • (i) Access and log into My Oracle Support at the following location: http://support.oracle.com/
  • (ii) Click the Patches & Updates link.
  • (iii) Enter the Patch ID or Number, then click Search. A Patch Search Results table appears.
  • (iv) Using the Release and Platform columns, find the desired patch, then click the associated Patch ID.
  • (v) In the page that now appears, click the Download button in the right-hand column.

Backup and Recovery Considerations for Patching

It is highly recommended that you back up the ORACLE_HOME before any patch operation. You can back up the ORACLE_HOME using your preferred method. You can use any method such as zip, cp -r, tar, and cpio to compress the ORACLE_HOME.

Steps and Process when you need to apply patches:

First, determine the patch you have to apply, if you are applying a patchset (e.g., 10.2.0.1 to 10.2.0.3) or a one-off patch or CPU patch. These are applied in different ways. Applying a patchset usually requires the use of the Oracle Universal Installer (OUI) and then running a script inside each database using that ORACLE_HOME. When you download the patchset, it will be accompanied by a detailed, step-by-step guide for how to apply the patchset.
Step 1: Contact Oracle Support

You can contact to the oracle support representative and you can go to the MY Oracle Support: https://support.oracle.com/CSP/ui/flash.html

Step 2: Check for Existing patches

If Oracle Support is not able to resolve the issue, they may ask to see if you have any patches already installed on your system. To determine this information, you should run the opatch lsinventory command or opatch checkInstalledOneOffs command.
Example:
D:>cd %ORACLE_HOME%opatch
D:oracleora92OPatch>opatch lsinventory
Note: Opatch does not list the patch applied on Database. It list only the interim patch applied on oracle binaries. You can list patch applied on database with the help of below query in 10g R2.
SQL>Select * from sys.registry$history;

Step 3. Obtain OPatch and the Necessary Patch

Upon determining that you are in need of a new patch and it has not already been installed on your system, you should do the above steps: Getting Patches:

Step 4. Determine the Oracle Home

Once you obtain the patch, determine the Oracle home directory to which you are going to apply the patch.

Step 5. Read the README File

Read the README file that accompanies the patch. This file contains important information and instructions that must be followed prior to applying your patch.

Step 6. Apply the Patch

After you determine the Oracle home to which you need to apply the patch, you should apply the patch with the opatch apply command

The syntax for the apply command is shown below:

opatch apply    [-auto [-domain domain_name]]    [-mw_home MW_HOME]    [-oh ORACLE_HOME]    [-property_file path_to_property_file]    [-report]]

After Applying the Patch…

In most cases, after you apply the patch the instructions in the README file will tell you to run the opatch start command to re-start your servers.

After the patching is complete and your servers are restarted, you should check your product software to verify that the issue has been resolved.

If for some reason the result is not satisfactory, you can use the opatch rollback command to remove the patch from the Oracle home.

The syntax for the rollback option is shown below:

opatch rollback -id patch_id    [-auto [-domain domain_name]]    [-mw_home MW_HOME]    [-oh ORACLE_HOME]    [-property_file path_to_property_file    [-report]]

How to apply Patchset updates or Critical Patch Updates with Example

PSUs (PatchSet Updates) or CPUs(Critical Patch Updates) are applied via Opatch utility.
Opatch is an interim utility for applying Database interim patches which will fix the bug in the release. For making use of the opatch utility you have to download the opatch recent version and apply as follow:
Opatch is very useful for applying the database patches to fix various bugs and it is essential to have the latest version.
1) Download the latest OPatch version from My Oracle Support.
a) Click on the “Patches & Updates” tab
b) In the “Patch Name or Number” field type 6880880
c) In the “Platform” field select the relevant platform
d) Click the Search button.
e) Select the patch that corresponds to the Oracle release installed:
6880880 Universal Installer: Patch OPatch 11.2
f) Click the Download button
Once the above task is done copy the patch to $ORACLE_HOME directory and move the previous OPatch directory to separate directory in OS. We can use winscp or ftp for copying this patch from MOS to windows and than windows to linux Box.
g) Now, unzip this patch zip file and apply it.

How to apply patch:

C: CD oracle1Opatch      — “C:ORACLE1” is the ORACLE-HOME
C:oracle1Optachopatch> apply D:Patch_dir   — “D:Patch_dir” is the path where you have downloaded or kept the patch.
Note: Before going to apply the patch you must search or check these file Users_Guide.txt, opatch_history.txt.

PATCHING AND UPGRADING 10.2.0.1 RAC Environment to 10.2.0.4

PATCHING AND UPGRADING 10.2.0.1 RAC Environment to 10.2.0.4

>Patch is a bug fix

>collection of bug fixes is called a patch set

>Different types of patches released by oracle are:
1.Interim patch / one-off patch
2.Patch sets
3.Critical patch updates.
4.Patchset updates
5.CRS Hash bundle patches

>All above patches can be installed using Opatch utility except Patchsets.

>Patchsets are installed by invoking runinstaller.

>CRS_HASH bundle patches are patches to fix the bugs in the cluster.

>Clusterware can be patched in two ways
1.Rolling upgrade
2.Non-Rolling upgrade

>Incase of rolling upgrade we will bringdown all the services on then ode that we
wish to install the patch set.This is node by node activity.

>In case of non-rolling upgrade we bring down the entire cluster and install the patch set.

>To know the list of patches installed in CRS_HOME
$ Opatch lsinventory -detail $ORA_CRS_HOME

>To know the list of patches installed in ORACLE_HOME
$ opatch lsinventory -detail $ORACLE_HOME

Upgrading from Oracle 10.2.0.1 to 10.2.0.4

On this post I’d like to share with you how to install and setup the Oracle 10.2.0.4 patch. This guide also applies for other patches and versions.


1 – Downloading the patch

Go to Oracle Metalink website (metalink.oracle.com) and login.

Click on “Patches & Updates” tab and search for patch number 6810189 as you can see below:

The search should bring the following result:

If your search retrieves too many results try filtering by the platform you’re looking for.

Download the file (if you are downloading from windows transfer it via FTP to the Linux OS you will perform the upgrade. For more details on how to FTP files check on this posthttp://dbaseworld.wordpress.com/2009/07/25/how-to-ftp-and-command-list/).


2 – Unzipping and Installing Patch

Since you have the file in the server you need, unzip the package as follows

unzip p6810189_10204_Linux-x86.zip

After unzipping the package, go to the directory Disk1/response and edit the response file patchset.rsp. Setup the ORACLE_HOME to your actual value and the OUI variables to False since it’s a silent installation.

Run the command below from the Disk1 directory:

. runInstaller -silent -responseFile Disk1/response/patchset.rsp


3 – Startup database and running scripts

After installation completion login the database as sysdba (sqlplus / as sysdba) and startup the database in upgrade mode

SQL> startup upgrade

The database should start and you can run the upgrade script. This may take several minutes to run.

@$ORACLE_HOME/rdbms/admin/catupgrd.sql

When finished shutdown the database and start it normally (startup command) and then run the script below to check if there’s any inactive object.

@/oracle/SID/102_64/rdbms/admin/utlrp.sql

When the script completes the upgrade is done.

To check if the new patch is up and running when you login in sqlplus you should see something like this:

SQL*Plus: Release 10.2.0.4.0

That’s all. If you have any doubts please comment.

Thanks