How do you rollback/remove a CPU/PSU Patch from an ORACLE_HOME?

Sometimes you may be in need to rollback a CPU/PSU patch from an ORACLE_HOME you just have applied in so in order to rollback or remove a CPU/PSU patch you have to follow the following commands for rolling back a single or N number of patches at a time.


Rolling back a single CPU Patch:
========================

Pre-requisites : Oralce service should be down for ORACLE_HOME from where you are rolling back the CPU/PSU patches.

Suppose you want to remove/rollback a single interim patch from an ORACLE_HOME you just applied so what you need to do is just to invoke the Opatch from the ORACLE_HOME from where you want to rollback the patch with below syntax command.

[oracle@oem12c OPatch]$./opatch rollback -id [patch_number]

[oracle@oem12c OPatch]$ ./opatch rollback -id 12419278
Picked up _JAVA_OPTIONS: -Xms256m -Xmx512m
Invoking OPatch 11.1.0.6.6

Oracle Interim Patch Installer version 11.1.0.6.6
Copyright (c) 2009, Oracle Corporation.  All rights reserved.

UTIL session

Oracle Home       : /u01/app/oracle/product/11.2.0/dbhome_1
Central Inventory : /u01/app/oraInventory
   from           : /etc/oraInst.loc
OPatch version    : 11.1.0.6.6
OUI version       : 11.2.0.1.0
OUI location      : /u01/app/oracle/product/11.2.0/dbhome_1/oui
Log file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch2015-10-20_10-55-23AM.log

Patch history file: /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch_history.txt

Invoking utility "rollback"

Patches will be rolled back in the following order:
   12419278

Running prerequisite checks...
The following patch(es) will be rolled back: 12419278

OPatch detected non-cluster Oracle Home from the inventory and will patch the local system only.


Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/u01/app/oracle/product/11.2.0/dbhome_1')


Is the local system ready for patching? [y|n]  -- Make sure oracle services are brought down and press y to proceed here
y
User Responded with: Y
Backing up files affected by the patch 'NRollback' for restore. This might take a while...
Execution of 'sh /u01/app/oracle/product/11.2.0/dbhome_1/.patch_storage/12419278_May_19_2011_06_17_30/original_patch/custom/scripts/pre -rollback 12419278 ':


Return Code = 0

Rolling back patch 12419278...

RollbackSession rolling back interim patch '12419278' from OH '/u01/app/oracle/product/11.2.0/dbhome_1'

Patching component oracle.rdbms.rsf, 11.2.0.1.0...

Patching component oracle.rdbms, 11.2.0.1.0...
Copying file to "/u01/app/oracle/product/11.2.0/dbhome_1/rdbms/admin/bundledata_CPU.xml"
RollbackSession removing interim patch '12419278' from inventory

The local system has been patched and can be restarted.

UtilSession: Rollback done.

OPatch succeeded.




Rolling Back N number of patches at at time:
==============================

Now suppose you want to remove/rollback N number/multiple(set of patches) of patches from an Oracle HOme then you just have to follow the same syntex like you do to remove a single patch with set of N number of patches seprated with command as an argument for -id parameter as given below.

Note : - Here command is slightly different than previous one as "nrollback"parameter is used instead of simple "rollback". If we want to remove/rollback N number of patches at a time then we use "nrollback" along with the set of patch numbers separated with command as an argument for the parameter "-id" of nrollback command.

[oracle@oem12c OPatch]$ ./opatch nrollback -id 11794167,12534742,12534743,12534745,12534746,12534747,12534748,12534749,12534750,12534751,12534752,12534753,12534754
Picked up _JAVA_OPTIONS: -Xms256m -Xmx512m
Invoking OPatch 11.1.0.6.6

Oracle Interim Patch Installer version 11.1.0.6.6
Copyright (c) 2009, Oracle Corporation.  All rights reserved.

UTIL session

Oracle Home       : /u01/app/oracle/product/11.2.0/dbhome_1
Central Inventory : /u01/app/oraInventory
   from           : /etc/oraInst.loc
OPatch version    : 11.1.0.6.6
OUI version       : 11.2.0.1.0
OUI location      : /u01/app/oracle/product/11.2.0/dbhome_1/oui
Log file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch2015-10-26_12-13-43PM.log

Patch history file: /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch_history.txt

Invoking utility "nrollback"

Patches will be rolled back in the following order:
   11794167   12534742   12534743   12534745   12534746   12534747   12534748   12534749   12534750   12534751   12534752   12534753   12534754

Running prerequisite checks...
The following patch(es) will be rolled back: 11794167  12534742  12534743  12534745  12534746  12534747  12534748  12534749  12534750  12534751  12534752  12534753  12534754

OPatch detected non-cluster Oracle Home from the inventory and will patch the local system only.


Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/u01/app/oracle/product/11.2.0/dbhome_1')


Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files affected by the patch 'NRollback' for restore. This might take a while...

Rolling back patch 11794167...

RollbackSession rolling back interim patch '11794167' from OH '/u01/app/oracle/product/11.2.0/dbhome_1'

Patching component oracle.ldap.rsf.ic, 11.2.0.1.0...
Copying file to "/u01/app/oracle/product/11.2.0/dbhome_1/lib/libnnz11.so"

Patching component oracle.ldap.rsf, 11.2.0.1.0...
Updating archive file "/u01/app/oracle/product/11.2.0/dbhome_1/lib/libzt11.a"  with "lib/libzt11.a/nzdacval.o"
Updating archive file "/u01/app/oracle/product/11.2.0/dbhome_1/lib/libnnz11.a"  with "lib/libnnz11.a/nzu.o"
Updating archive file "/u01/app/oracle/product/11.2.0/dbhome_1/lib/libnnz11.a"  with "lib/libnnz11.a/ssl_hshk_priv_ssl2_write_client_certificate_handler.o"

Patching component oracle.rdbms, 11.2.0.1.0...
RollbackSession removing interim patch '11794167' from inventory

Rolling back patch 12534742...

RollbackSession rolling back interim patch '12534742' from OH '/u01/app/oracle/product/11.2.0/dbhome_1'

Patching component oracle.sysman.console.db, 11.2.0.1.0...
Updating jar file "/u01/app/oracle/product/11.2.0/dbhome_1/sysman/jlib/emCORE.jar" with "/u01/app/oracle/product/11.2.0/dbhome_1/.patch_storage/12534742_May_18_2011_06_13_07/files//sysman/jlib/emCORE.jar/oracle/sysman/eml/admin/rep/UserData.class"
Updating jar file "/u01/app/oracle/product/11.2.0/dbhome_1/sysman/jlib/emCORE.jar" with "/u01/app/oracle/product/11.2.0/dbhome_1/.patch_storage/12534742_May_18_2011_06_13_07/files//sysman/jlib/emCORE.jar/oracle/sysman/eml/admin/rep/AdminResourceBundle.class"
Updating jar file "/u01/app/oracle/product/11.2.0/dbhome_1/sysman/jlib/emCORE.jar" with "/u01/app/oracle/product/11.2.0/dbhome_1/.patch_storage/12534742_May_18_2011_06_13_07/files//sysman/jlib/emCORE.jar/oracle/sysman/eml/admin/rep/AdminResourceBundleID.class"
Copying file to "/u01/app/oracle/product/11.2.0/dbhome_1/oc4j/j2ee/oc4j_applications/applications/em/em/admin/rep/editUserSummary.uix"

Patching component oracle.sysman.oms.core, 10.2.0.4.2...
Copying file to "/u01/app/oracle/product/11.2.0/dbhome_1/j2ee/OC4J_EM/applications/em/em/admin/rep/editUserSummary.uix"
RollbackSession removing interim patch '12534742' from inventory

Rolling back patch 12534743...

RollbackSession rolling back interim patch '12534743' from OH '/u01/app/oracle/product/11.2.0/dbhome_1'

Patching component oracle.sysman.console.db, 11.2.0.1.0...
Updating jar file "/u01/app/oracle/product/11.2.0/dbhome_1/sysman/jlib/emDB.jar" with "/u01/app/oracle/product/11.2.0/dbhome_1/.patch_storage/12534743_May_18_2011_06_13_08/files//sysman/jlib/emDB.jar/oracle/sysman/emo/adm/schema/srctype/trigger/Trigger.class"
Updating jar file "/u01/app/oracle/product/11.2.0/dbhome_1/sysman/jlib/emDB.jar" with "/u01/app/oracle/product/11.2.0/dbhome_1/.patch_storage/12534743_May_18_2011_06_13_08/files//sysman/jlib/emDB.jar/oracle/sysman/emo/adm/schema/srctype/trigger/TriggerAttributes.class"
RollbackSession removing interim patch '12534743' from inventory

Rolling back patch 12534745...

RollbackSession rolling back interim patch '12534745' from OH '/u01/app/oracle/product/11.2.0/dbhome_1'

Patching component oracle.sysman.plugin.db.main.repository, 11.2.0.1.0...
Copying file to "/u01/app/oracle/product/11.2.0/dbhome_1/sysman/admin/emdrep/sql/db/latest/policy/config_util_pkgdef.sql"
Copying file to "/u01/app/oracle/product/11.2.0/dbhome_1/sysman/admin/emdrep/sql/db/latest/policy/config_util_pkgbody.sql"
Copying file to "/u01/app/oracle/product/11.2.0/dbhome_1/sysman/admin/emdrep/sql/db/latest/policy/config_metric_setup.sql"
RollbackSession removing interim patch '12534745' from inventory

Rolling back patch 12534746...

RollbackSession rolling back interim patch '12534746' from OH '/u01/app/oracle/product/11.2.0/dbhome_1'

Patching component oracle.sysman.console.db, 11.2.0.1.0...
Updating jar file "/u01/app/oracle/product/11.2.0/dbhome_1/sysman/jlib/emCORE.jar" with "/u01/app/oracle/product/11.2.0/dbhome_1/.patch_storage/12534746_May_18_2011_06_13_13/files//sysman/jlib/emCORE.jar/oracle/sysman/emSDK/sec/auth/EMLoginServlet.class"
RollbackSession removing interim patch '12534746' from inventory

Rolling back patch 12534747...

RollbackSession rolling back interim patch '12534747' from OH '/u01/app/oracle/product/11.2.0/dbhome_1'

Patching component oracle.sysman.console.db, 11.2.0.1.0...
Updating jar file "/u01/app/oracle/product/11.2.0/dbhome_1/sysman/jlib/emDB.jar" with "/u01/app/oracle/product/11.2.0/dbhome_1/.patch_storage/12534747_May_18_2011_06_13_16/files//sysman/jlib/emDB.jar/oracle/sysman/db/adm/inst/SqlWorksheetController.class"
Updating jar file "/u01/app/oracle/product/11.2.0/dbhome_1/sysman/jlib/emDB.jar" with "/u01/app/oracle/product/11.2.0/dbhome_1/.patch_storage/12534747_May_18_2011_06_13_16/files//sysman/jlib/emDB.jar/oracle/sysman/db/rsc/DBObjectMsgID.class"
RollbackSession removing interim patch '12534747' from inventory

Rolling back patch 12534748...

RollbackSession rolling back interim patch '12534748' from OH '/u01/app/oracle/product/11.2.0/dbhome_1'

Patching component oracle.sysman.console.db, 11.2.0.1.0...
Updating jar file "/u01/app/oracle/product/11.2.0/dbhome_1/sysman/jlib/emCORE.jar" with "/u01/app/oracle/product/11.2.0/dbhome_1/.patch_storage/12534748_May_18_2011_06_13_18/files//sysman/jlib/emCORE.jar/oracle/sysman/eml/ecm/compare/CompareQuery.class"
Copying file to "/u01/app/oracle/product/11.2.0/dbhome_1/sysman/admin/emdrep/sql/core/latest/ecm/ecm_util_pkgdef.sql"
Copying file to "/u01/app/oracle/product/11.2.0/dbhome_1/sysman/admin/emdrep/sql/core/latest/ecm/ecm_views.sql"
Copying file to "/u01/app/oracle/product/11.2.0/dbhome_1/sysman/admin/emdrep/sql/core/latest/sdk/sdk_views.sql"
RollbackSession removing interim patch '12534748' from inventory

Rolling back patch 12534749...

RollbackSession rolling back interim patch '12534749' from OH '/u01/app/oracle/product/11.2.0/dbhome_1'

Patching component oracle.sysman.console.db, 11.2.0.1.0...
Updating jar file "/u01/app/oracle/product/11.2.0/dbhome_1/sysman/jlib/emCORE.jar" with "/u01/app/oracle/product/11.2.0/dbhome_1/.patch_storage/12534749_May_18_2011_06_13_20/files//sysman/jlib/emCORE.jar/oracle/sysman/eml/mntr/vltn/AlertDetailsDataObject.class"
RollbackSession removing interim patch '12534749' from inventory

Rolling back patch 12534750...

RollbackSession rolling back interim patch '12534750' from OH '/u01/app/oracle/product/11.2.0/dbhome_1'

Patching component oracle.ldap.rsf, 11.2.0.1.0...
Updating archive file "/u01/app/oracle/product/11.2.0/dbhome_1/lib/libztkg11.a"  with "lib/libztkg11.a/accept_sec_context.o"

Patching component oracle.rdbms, 11.2.0.1.0...
RollbackSession removing interim patch '12534750' from inventory

Rolling back patch 12534751...

RollbackSession rolling back interim patch '12534751' from OH '/u01/app/oracle/product/11.2.0/dbhome_1'

Patching component oracle.rdbms.rsf, 11.2.0.1.0...
Updating archive file "/u01/app/oracle/product/11.2.0/dbhome_1/lib/libcommon11.a"  with "lib/libcommon11.a/ttcx2y.o"

Patching component oracle.rdbms, 11.2.0.1.0...
RollbackSession removing interim patch '12534751' from inventory

Rolling back patch 12534752...

RollbackSession rolling back interim patch '12534752' from OH '/u01/app/oracle/product/11.2.0/dbhome_1'

Patching component oracle.rdbms.dbscripts, 11.2.0.1.0...
Copying file to "/u01/app/oracle/product/11.2.0/dbhome_1/rdbms/admin/prvthsld.plb"
RollbackSession removing interim patch '12534752' from inventory

Rolling back patch 12534753...

RollbackSession rolling back interim patch '12534753' from OH '/u01/app/oracle/product/11.2.0/dbhome_1'

Patching component oracle.rdbms.rsf, 11.2.0.1.0...
Updating archive file "/u01/app/oracle/product/11.2.0/dbhome_1/lib/libpls11.a"  with "lib/libpls11.a/pef.o"
Updating archive file "/u01/app/oracle/product/11.2.0/dbhome_1/lib/libpls11.a"  with "lib/libpls11.a/pei.o"
Updating archive file "/u01/app/oracle/product/11.2.0/dbhome_1/lib/libpls11.a"  with "lib/libpls11.a/pgmc.o"

Patching component oracle.rdbms, 11.2.0.1.0...
Updating archive file "/u01/app/oracle/product/11.2.0/dbhome_1/rdbms/lib/libknlopt.a"  with "rdbms/lib/libknlopt.a/kkxwtp.o"
Updating archive file "/u01/app/oracle/product/11.2.0/dbhome_1/lib/libserver11.a"  with "lib/libserver11.a/qeroc.o"
RollbackSession removing interim patch '12534753' from inventory

Rolling back patch 12534754...

RollbackSession rolling back interim patch '12534754' from OH '/u01/app/oracle/product/11.2.0/dbhome_1'

Patching component oracle.rdbms, 11.2.0.1.0...
Updating archive file "/u01/app/oracle/product/11.2.0/dbhome_1/lib/libserver11.a"  with "lib/libserver11.a/qmtest_qmr.o"
RollbackSession removing interim patch '12534754' from inventory
Running make for target client_sharedlib
Running make for target ioracle
Running make for target client_sharedlib

The local system has been patched and can be restarted.

UtilSession: N-Rollback done.

OPatch succeeded.


This is how we rollback/remove CPU/PSU patches from an ORACLE_HOME and at last you can verify if patches are removed from the Oracle Inventory by using the following command.

[oracle@oem12c OPatch]$./opatch lsinventory

How to Apply Critical Patch Updates in Oracle Database?

Just thought to create a small paper that how we apply critical patch updates in oracle database on request of my two friends - Neha Mehra & Jatin Wadhwa

Please follow the below steps to learn how to apply critical patch update in Oracle database.

I have used Oracle database 11.2.0.1.0 and CPU2011 patch to write this paper and have restricted/removed some outputs(messages/standard outputs) to make the paper not lengthy.

Solution:

Pre-requisites: 

Always make sure you have appropriate Opatch version in place to apply CPU/PSU patches. Latest can be downloaded from Metalik patch number #6880880. 

Download Oracle CPU2011 patch - p12419278_112010_LINUX.zip from metalink with patch number - 12419278 and unzip it somewhere at your database server. In my case I have inflated inside /u01/app directory.

[oracle@oem12c app]$ pwd

/u01/app

[oracle@oem12c app]$ ls -ltr

total 53192

drwxrwxr-x 31 oracle  oinstall     4096 Jul  8  2011 12419278  -- unzipped CPU patch bundle

-rwxr-xr-x  1 oragrid oinstall  4633877 Oct 15 15:59 p12419278_112010_LINUX.zip -- downloaded CPU patch zip file

List of any previous interim one off patches if already applied any:

[oracle@oem12c OPatch]$ ./opatch lsinv

Picked up _JAVA_OPTIONS: -Xms256m -Xmx512m

Invoking OPatch 11.1.0.6.6

Oracle Interim Patch Installer version 11.1.0.6.6

Copyright (c) 2009, Oracle Corporation.  All rights reserved.

Oracle Home       : /u01/app/oracle/product/11.2.0/dbhome_1

Central Inventory : /u01/app/oraInventory

   from           : /etc/oraInst.loc

OPatch version    : 11.1.0.6.6

OUI version       : 11.2.0.1.0

OUI location      : /u01/app/oracle/product/11.2.0/dbhome_1/oui

Log file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch2015-10-26_12-38-45PM.log

Patch history file: /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch_history.txt

Lsinventory Output file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/lsinv/lsinventory2015-10-26_12-38-45PM.txt

--------------------------------------------------------------------------------

Installed Top-level Products (1): 

Oracle Database 11g                                                  11.2.0.1.0

There are 1 products installed in this Oracle Home.

There are no Interim patches installed in this Oracle Home.

--------------------------------------------------------------------------------

OPatch succeeded.

Note : - We can see no any interim patches applied to this ORACLE_HOME.

Here we go and Apply CPU2011 patch now.

================================

Just navigate inside the unzipped patch directory and find the list of patches which will be applied.

[oracle@oem12c app]$ cd 12419278

[oracle@oem12c 12419278]$ pwd

/u01/app/12419278

Below are the list of interim patches inside this CPU patch bundle which will be applied to this ORACLE_HOME.

[oracle@oem12c 12419278]$ pwd

/u01/app/12419278

[oracle@oem12c 12419278]$ ls -ltr

total 168

drwxr-xr-x 4 oracle oinstall  4096 Mar 15  2010 9454038

drwxr-xr-x 4 oracle oinstall  4096 Mar 24  2010 9454037

drwxr-xr-x 4 oracle oinstall  4096 May 31  2010 9676419

drwxr-xr-x 4 oracle oinstall  4096 May 31  2010 9676420

drwxr-xr-x 4 oracle oinstall  4096 Sep 14  2010 9971778

drwxr-xr-x 4 oracle oinstall  4096 Sep 14  2010 9971779

drwxr-xr-x 4 oracle oinstall  4096 Sep 14  2010 9971780

drwxr-xr-x 4 oracle oinstall  4096 Nov 23  2010 10323077

drwxr-xr-x 4 oracle oinstall  4096 Nov 23  2010 10323079

drwxr-xr-x 4 oracle oinstall  4096 Nov 23  2010 10323081

drwxr-xr-x 4 oracle oinstall  4096 Nov 23  2010 10323082

drwxr-xr-x 4 oracle oinstall  4096 Feb 23  2011 11794164

drwxr-xr-x 4 oracle oinstall  4096 Feb 23  2011 11794167

drwxr-xr-x 4 oracle oinstall  4096 Mar 25  2011 11794163

drwxr-xr-x 4 oracle oinstall  4096 May 18  2011 12534742

drwxr-xr-x 4 oracle oinstall  4096 May 18  2011 12534743

drwxr-xr-x 4 oracle oinstall  4096 May 18  2011 12534745

drwxr-xr-x 4 oracle oinstall  4096 May 18  2011 12534746

drwxr-xr-x 4 oracle oinstall  4096 May 18  2011 12534747

drwxr-xr-x 4 oracle oinstall  4096 May 18  2011 12534748

drwxr-xr-x 4 oracle oinstall  4096 May 18  2011 12534749

drwxr-xr-x 4 oracle oinstall  4096 May 18  2011 12534750

drwxr-xr-x 4 oracle oinstall  4096 May 18  2011 12534752

drwxr-xr-x 4 oracle oinstall  4096 May 18  2011 12534753

drwxr-xr-x 4 oracle oinstall  4096 May 18  2011 12534754

drwxr-xr-x 4 oracle oinstall  4096 May 18  2011 12534755

drwxr-xr-x 4 oracle oinstall  4096 May 18  2011 12534756

drwxr-xr-x 5 oracle oinstall  4096 May 19  2011 12419278

drwxr-xr-x 4 oracle oinstall  4096 Jul  8  2011 12534751

-rw-r--r-- 1 oracle oinstall    21 Jul  8  2011 README.txt

-rwxr-xr-x 1 oracle oinstall  2872 Jul  8  2011 patchmd.xml

-rw-rw-r-- 1 oracle oinstall 42530 Jul 18  2011 README.html

Note : - you can see there are many interim patches since 2010, yes Oracle critical patches are cumulative which includes all previous CPU patches released for that database version so far this is why it is recommended to apply latest CPU/PSU patches to an Oracle database.

Now let's invoke opatch from ORACLE_HOME where we are going to apply these N number of CPU patches included in CPU2011 CPU patch bundle.

[oracle@oem12c 12419278]$ /u01/app/oracle/product/11.2.0/dbhome_1/OPatch/opatch napply 

Picked up _JAVA_OPTIONS: -Xms256m -Xmx512m

Invoking OPatch 11.1.0.6.6

Oracle Interim Patch Installer version 11.1.0.6.6

Copyright (c) 2009, Oracle Corporation.  All rights reserved.

UTIL session

Oracle Home       : /u01/app/oracle/product/11.2.0/dbhome_1

Central Inventory : /u01/app/oraInventory

   from           : /etc/oraInst.loc

OPatch version    : 11.1.0.6.6

OUI version       : 11.2.0.1.0

OUI location      : /u01/app/oracle/product/11.2.0/dbhome_1/oui

Log file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch2015-10-26_12-56-58PM.log

Patch history file: /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch_history.txt

Invoking utility "napply"

Checking conflict among patches...

Checking if Oracle Home has components required by patches...

Checking conflicts against Oracle Home...

OPatch continues with these patches:   10323077  10323079  10323081  10323082  11794163  11794164  11794167  12419278  12534742  12534743  12534745  12534746  12534747  12534748  12534749  12534750  12534751  12534752  12534753  12534754  12534755  12534756  9454037  9454038  9676419  9676420  9971778  9971779  9971780  

Do you want to proceed? [y|n]   -- here press y to agree on applying all patches inside this patch bundle.

y       

User Responded with: Y

Running prerequisite checks...

Patch 12534751: Optional component(s) missing : [ oracle.client, 11.2.0.1.0 ] 

OPatch detected non-cluster Oracle Home from the inventory and will patch the local system only.

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.

(Oracle Home = '/u01/app/oracle/product/11.2.0/dbhome_1')

Is the local system ready for patching? [y|n]  -- Here press y if your all oracle services are down which are running from the ORACLE_HOME in order to proceed ahead for patching activity.

y

User Responded with: Y

Backing up files affected by the patch 'NApply' for restore. This might take a while...

Execution of 'sh /u01/app/12419278/12419278/custom/scripts/pre -apply 12419278 ':

Return Code = 0

Applying patch 10323077...

ApplySession applying interim patch '10323077' to OH '/u01/app/oracle/product/11.2.0/dbhome_1'

Backing up files affected by the patch '10323077' for rollback. This might take a while...

Patching component oracle.rdbms.dv.oc4j, 11.2.0.1.0...

Updating jar file "/u01/app/oracle/product/11.2.0/dbhome_1/dv/jlib/dva_webapp.ear" with "/dv/jlib/dva_webapp.ear/dva_webapp.war/WEB-INF/lib/dva_webapp.jar/oracle/security/datval/dva/BasePageHandler.class"

Updating jar file "/u01/app/oracle/product/11.2.0/dbhome_1/dv/jlib/dva_webapp.ear" with "/dv/jlib/dva_webapp.ear/dva_webapp.war/WEB-INF/lib/dva_webapp.jar/oracle/security/datval/dva/admin/AdminPageHandler.class"

Updating jar file "/u01/app/oracle/product/11.2.0/dbhome_1/dv/jlib/dva_webapp.ear" with "/dv/jlib/dva_webapp.ear/dva_webapp.war/WEB-INF/lib/dva_webapp.jar/oracle/security/datval/dva/security/LoginPageHandler.class"

Updating jar file "/u01/app/oracle/product/11.2.0/dbhome_1/dv/jlib/dva_webapp.ear" with "/dv/jlib/dva_webapp.ear/dva_webapp.war/WEB-INF/lib/dva_webapp.jar/oracle/security/datval/util/ViewUtil.class"

Updating jar file "/u01/app/oracle/product/11.2.0/dbhome_1/dv/jlib/dva_webapp.ear" with "/dv/jlib/dva_webapp.ear/dva_webapp.war/WEB-INF/lib/dva_webapp_jsp.jar/_home/_about.class"

Updating jar file "/u01/app/oracle/product/11.2.0/dbhome_1/dv/jlib/dva_webapp.ear" with "/dv/jlib/dva_webapp.ear/dva_webapp.war/WEB-INF/lib/dva_webapp_jsp.jar/_delete.class"

Updating jar file "/u01/app/oracle/product/11.2.0/dbhome_1/dv/jlib/dva_webapp.ear" with "/dv/jlib/dva_webapp.ear/dva_webapp.war/WEB-INF/lib/dva_webapp_jsp.jar/_errorPage.class"

.

.

.

.

.

.

.

.

.

.

.

Applying patch 9971779...

ApplySession applying interim patch '9971779' to OH '/u01/app/oracle/product/11.2.0/dbhome_1'

Backing up files affected by the patch '9971779' for rollback. This might take a while...

Patching component oracle.javavm.server, 11.2.0.1.0...

Copying file to "/u01/app/oracle/product/11.2.0/dbhome_1/javavm/install/jvm_exp.sql"

Copying file to "/u01/app/oracle/product/11.2.0/dbhome_1/javavm/install/initjvm.sql"

Copying file to "/u01/app/oracle/product/11.2.0/dbhome_1/javavm/install/jvmursc.sql"

ApplySession adding interim patch '9971779' to inventory

Verifying the update...

Inventory check OK: Patch ID 9971779 is registered in Oracle Home inventory with proper meta-data.

Files check OK: Files from Patch ID 9971779 are present in Oracle Home.

Applying patch 9971780...

ApplySession applying interim patch '9971780' to OH '/u01/app/oracle/product/11.2.0/dbhome_1'

Backing up files affected by the patch '9971780' for rollback. This might take a while...

Patching component oracle.rdbms.dbscripts, 11.2.0.1.0...

Copying file to "/u01/app/oracle/product/11.2.0/dbhome_1/rdbms/admin/dbmscdcu.sql"

Copying file to "/u01/app/oracle/product/11.2.0/dbhome_1/rdbms/admin/prvtcdcu.plb"

Copying file to "/u01/app/oracle/product/11.2.0/dbhome_1/rdbms/admin/prvtcdcp.plb"

Copying file to "/u01/app/oracle/product/11.2.0/dbhome_1/rdbms/admin/prvtcdcs.plb"

Patching component oracle.rdbms, 11.2.0.1.0...

Updating archive file "/u01/app/oracle/product/11.2.0/dbhome_1/lib/libserver11.a"  with "lib/libserver11.a/kkzl.o"

ApplySession adding interim patch '9971780' to inventory

Verifying the update...

Inventory check OK: Patch ID 9971780 is registered in Oracle Home inventory with proper meta-data.

Files check OK: Files from Patch ID 9971780 are present in Oracle Home.

Running make for target ioracle

Running make for target client_sharedlib

Running make for target client_sharedlib

Running make for target itnslsnr

The local system has been patched and can be restarted.

UtilSession: N-Apply done.

OPatch succeeded.   

Post OPatch steps:

=============

As in the previous step, all patches have been applied to the ORACLE_HOME so we can start Oracle services now and run the following catcpu.sql script from the ORACLE_HOME where we applied the patch.

[oracle@oem12c CPUJul2011]$ pwd

/u01/app/oracle/product/11.2.0/dbhome_1/cpu/CPUJul2011

Connect as sysdba and run the script as below.

SQL>@/u01/app/oracle/product/11.2.0/dbhome_1/cpu/CPUJul2011/catcpu.sql

After running above CPU bundle script, run utlrp.sql script to compile database objects gone in INVALID state during patching activity.

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

Let's verify the number of patches applied to this ORACLE_HOME

===================================================

[oracle@oem12c OPatch]$ ./opatch lsinv

Picked up _JAVA_OPTIONS: -Xms256m -Xmx512m

Invoking OPatch 11.1.0.6.6

Oracle Interim Patch Installer version 11.1.0.6.6

Copyright (c) 2009, Oracle Corporation.  All rights reserved.

Oracle Home       : /u01/app/oracle/product/11.2.0/dbhome_1

Central Inventory : /u01/app/oraInventory

   from           : /etc/oraInst.loc

OPatch version    : 11.1.0.6.6

OUI version       : 11.2.0.1.0

OUI location      : /u01/app/oracle/product/11.2.0/dbhome_1/oui

Log file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch2015-10-26_16-18-51PM.log

Patch history file: /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch_history.txt

Lsinventory Output file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/lsinv/lsinventory2015-10-26_16-18-51PM.txt

--------------------------------------------------------------------------------

Installed Top-level Products (1): 

Oracle Database 11g                                                  11.2.0.1.0

There are 1 products installed in this Oracle Home.

Interim patches (29) :

Patch  9971780      : applied on Mon Oct 26 13:15:17 IST 2015

Unique Patch ID:  13710306

   Created on 14 Sep 2010, 06:04:59 hrs PST8PDT

   Bugs fixed:

     9971780

Patch  9971779      : applied on Mon Oct 26 13:15:10 IST 2015

Unique Patch ID:  13710306

   Created on 14 Sep 2010, 06:04:14 hrs PST8PDT

   Bugs fixed:

     9971779, 9454036

Patch  9971778      : applied on Mon Oct 26 13:15:07 IST 2015

Unique Patch ID:  13710306

   Created on 14 Sep 2010, 06:02:16 hrs PST8PDT

   Bugs fixed:

     9971778

Patch  9676420      : applied on Mon Oct 26 13:15:05 IST 2015

Unique Patch ID:  13710306

   Created on 31 May 2010, 02:36:09 hrs PST8PDT

   Bugs fixed:

     9676420

Patch  9676419      : applied on Mon Oct 26 13:15:03 IST 2015

Unique Patch ID:  13710306

   Created on 31 May 2010, 02:07:11 hrs PST8PDT

   Bugs fixed:

     9676419

Patch  9454038      : applied on Mon Oct 26 13:15:00 IST 2015

Unique Patch ID:  13710306

   Created on 15 Mar 2010, 08:56:17 hrs PST8PDT

   Bugs fixed:

     9454038

Patch  9454037      : applied on Mon Oct 26 13:14:58 IST 2015

Unique Patch ID:  13710306

   Created on 24 Mar 2010, 06:44:08 hrs PST8PDT

   Bugs fixed:

     9454037

Patch  12534756     : applied on Mon Oct 26 13:14:55 IST 2015

Unique Patch ID:  13710306

   Created on 18 May 2011, 06:13:30 hrs PST8PDT

   Bugs fixed:

     12534756, 11794165

Patch  12534755     : applied on Mon Oct 26 13:14:51 IST 2015

Unique Patch ID:  13710306

   Created on 18 May 2011, 06:13:28 hrs PST8PDT

   Bugs fixed:

     12534755, 8702535, 8991997

Patch  12534754     : applied on Mon Oct 26 13:14:35 IST 2015

Unique Patch ID:  13710306

   Created on 18 May 2011, 06:13:27 hrs PST8PDT

   Bugs fixed:

     12534754

Patch  12534753     : applied on Mon Oct 26 13:14:29 IST 2015

Unique Patch ID:  13710306

   Created on 18 May 2011, 06:13:25 hrs PST8PDT

   Bugs fixed:

     12534753

Patch  12534752     : applied on Mon Oct 26 13:13:58 IST 2015

Unique Patch ID:  13710306

   Created on 18 May 2011, 06:13:24 hrs PST8PDT

   Bugs fixed:

     12534752

Patch  12534751     : applied on Mon Oct 26 13:13:55 IST 2015

Unique Patch ID:  13710306

   Created on 8 Jul 2011, 02:03:42 hrs PST8PDT

   Bugs fixed:

     12534751

Patch  12534750     : applied on Mon Oct 26 13:13:53 IST 2015

Unique Patch ID:  13710306

   Created on 18 May 2011, 06:13:21 hrs PST8PDT

   Bugs fixed:

     12534750

Patch  12534749     : applied on Mon Oct 26 13:13:49 IST 2015

Unique Patch ID:  13710306

   Created on 18 May 2011, 06:13:20 hrs PST8PDT

   Bugs fixed:

     12534749

Patch  12534748     : applied on Mon Oct 26 13:13:36 IST 2015

Unique Patch ID:  13710306

   Created on 18 May 2011, 06:13:18 hrs PST8PDT

   Bugs fixed:

     12534748

Patch  12534747     : applied on Mon Oct 26 13:12:01 IST 2015

Unique Patch ID:  13710306

   Created on 18 May 2011, 06:13:16 hrs PST8PDT

   Bugs fixed:

     12534747

Patch  12534746     : applied on Mon Oct 26 13:07:56 IST 2015

Unique Patch ID:  13710306

   Created on 18 May 2011, 06:13:13 hrs PST8PDT

   Bugs fixed:

     12534746

Patch  12534745     : applied on Mon Oct 26 13:07:46 IST 2015

Unique Patch ID:  13710306

   Created on 18 May 2011, 06:13:11 hrs PST8PDT

   Bugs fixed:

     12534745

Patch  12534743     : applied on Mon Oct 26 13:07:32 IST 2015

Unique Patch ID:  13710306

   Created on 18 May 2011, 06:13:08 hrs PST8PDT

   Bugs fixed:

     12534743

Patch  12534742     : applied on Mon Oct 26 13:04:44 IST 2015

Unique Patch ID:  13710306

   Created on 18 May 2011, 06:13:07 hrs PST8PDT

   Bugs fixed:

     12534742

Patch  12419278     : applied on Mon Oct 26 13:04:18 IST 2015

Unique Patch ID:  13710306

   Created on 19 May 2011, 06:17:30 hrs PST8PDT

   Bugs fixed:

     9655013, 12419278, 9952260, 9369797, 11724991, 10249532

Patch  11794167     : applied on Mon Oct 26 13:04:14 IST 2015

Unique Patch ID:  13710306

   Created on 23 Feb 2011, 04:04:04 hrs PST8PDT

   Bugs fixed:

     11794167

Patch  11794164     : applied on Mon Oct 26 13:04:05 IST 2015

Unique Patch ID:  13710306

   Created on 23 Feb 2011, 00:52:19 hrs PST8PDT

   Bugs fixed:

     11794164

Patch  11794163     : applied on Mon Oct 26 13:04:02 IST 2015

Unique Patch ID:  13710306

   Created on 25 Mar 2011, 02:54:11 hrs PST8PDT

   Bugs fixed:

     10323080, 11794163

Patch  10323082     : applied on Mon Oct 26 13:03:43 IST 2015

Unique Patch ID:  13710306

   Created on 15 Dec 2010, 05:59:50 hrs PST8PDT

   Bugs fixed:

     10323082

Patch  10323081     : applied on Mon Oct 26 13:03:22 IST 2015

Unique Patch ID:  13710306

   Created on 15 Dec 2010, 05:59:55 hrs PST8PDT

   Bugs fixed:

     10323081

Patch  10323079     : applied on Mon Oct 26 13:03:18 IST 2015

Unique Patch ID:  13710306

   Created on 15 Dec 2010, 06:00:22 hrs PST8PDT

   Bugs fixed:

     10323079

Patch  10323077     : applied on Mon Oct 26 13:03:05 IST 2015

Unique Patch ID:  13710306

   Created on 15 Dec 2010, 06:00:48 hrs PST8PDT

   Bugs fixed:

     10323077

--------------------------------------------------------------------------------

OPatch succeeded.

Now we are done with CPU patching activity.
Hope it would help someone in understanding how to apply CPU patches in oracle database.

Cannot configure two CRS instances on the same cluster

CRS is already configured on this node :


While setting up 2 node RAC, node 2 went down in the middle of root.sh execution. And at re-run of root.sh at node 2 it ended with the message that "Can't configure two CRS instances on the same cluster" so in order to execute root.sh with success you will have to de-configure root.sh first and then try to run it again as follows.



[root@host02 grid]# ./root.sh
Running Oracle 11g root.sh script...

The following environment variables are set as:
    ORACLE_OWNER= oragrid
    ORACLE_HOME=  /u01/app/11.2.0/grid

Enter the full pathname of the local bin directory: [/usr/local/bin]:
The file "dbhome" already exists in /usr/local/bin.  Overwrite it? (y/n)
[n]: y
   Copying dbhome to /usr/local/bin ...
The file "oraenv" already exists in /usr/local/bin.  Overwrite it? (y/n)
[n]: y
   Copying oraenv to /usr/local/bin ...
The file "coraenv" already exists in /usr/local/bin.  Overwrite it? (y/n)
[n]: y
   Copying coraenv to /usr/local/bin ...

Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root.sh script.
Now product-specific root actions will be performed.
2015-07-22 01:16:26: Parsing the host name
2015-07-22 01:16:26: Checking for super user privileges
2015-07-22 01:16:26: User has super user privileges
Using configuration parameter file: /u01/app/11.2.0/grid/crs/install/crsconfig_params
CRS is already configured on this node for crshome=0
Cannot configure two CRS instances on the same cluster.
Please deconfigure before proceeding with the configuration of new home.




Step : 1 -
=========De-configure CRS =========


[root@host02 grid]# crs/install/rootcrs.pl -verbose -deconfig -force
2015-07-22 01:31:30: Parsing the host name
2015-07-22 01:31:30: Checking for super user privileges
2015-07-22 01:31:30: User has super user privileges
Using configuration parameter file: crs/install/crsconfig_params
VIP exists.:host01
VIP exists.: /192.9.201.254/192.9.201.254/255.255.255.0/eth0
VIP exists.:host02
VIP exists.: //192.9.201.187/255.255.255.0/eth0
GSD exists.
ONS daemon exists. Local port 6100, remote port 6200
eONS daemon exists. Multicast port 23815, multicast IP address 234.216.11.17, listening port 2016
PRKO-2425 : VIP is already stopped on node(s): host02

ADVM/ACFS is not supported on oraclelinux-release-5-8.0.2

ACFS-9201: Not Supported
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'host02'
CRS-2673: Attempting to stop 'ora.crsd' on 'host02'
CRS-2790: Starting shutdown of Cluster Ready Services-managed resources on 'host02'
CRS-2673: Attempting to stop 'ora.OCR_VOTE.dg' on 'host02'
CRS-2677: Stop of 'ora.OCR_VOTE.dg' on 'host02' succeeded
CRS-2673: Attempting to stop 'ora.asm' on 'host02'
CRS-2677: Stop of 'ora.asm' on 'host02' succeeded
CRS-2792: Shutdown of Cluster Ready Services-managed resources on 'host02' has completed
CRS-2677: Stop of 'ora.crsd' on 'host02' succeeded
CRS-2673: Attempting to stop 'ora.cssdmonitor' on 'host02'
CRS-2673: Attempting to stop 'ora.ctssd' on 'host02'
CRS-2673: Attempting to stop 'ora.evmd' on 'host02'
CRS-2673: Attempting to stop 'ora.asm' on 'host02'
CRS-2673: Attempting to stop 'ora.mdnsd' on 'host02'
CRS-2677: Stop of 'ora.cssdmonitor' on 'host02' succeeded
CRS-2677: Stop of 'ora.mdnsd' on 'host02' succeeded
CRS-2677: Stop of 'ora.evmd' on 'host02' succeeded
CRS-2677: Stop of 'ora.ctssd' on 'host02' succeeded
CRS-2677: Stop of 'ora.asm' on 'host02' succeeded
CRS-2673: Attempting to stop 'ora.cssd' on 'host02'
CRS-2677: Stop of 'ora.cssd' on 'host02' succeeded
CRS-2673: Attempting to stop 'ora.gpnpd' on 'host02'
CRS-2673: Attempting to stop 'ora.diskmon' on 'host02'
CRS-2677: Stop of 'ora.gpnpd' on 'host02' succeeded
CRS-2673: Attempting to stop 'ora.gipcd' on 'host02'
CRS-2677: Stop of 'ora.diskmon' on 'host02' succeeded
CRS-2677: Stop of 'ora.gipcd' on 'host02' succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'host02' has completed
CRS-4133: Oracle High Availability Services has been stopped.
error: package cvuqdisk is not installed
Successfully deconfigured Oracle clusterware stack on this node




Step : 2 -

========== Run root.sh again ==============


[root@host02 grid]# ./root.sh
Running Oracle 11g root.sh script...

The following environment variables are set as:
    ORACLE_OWNER= oragrid
    ORACLE_HOME=  /u01/app/11.2.0/grid

Enter the full pathname of the local bin directory: [/usr/local/bin]:
The file "dbhome" already exists in /usr/local/bin.  Overwrite it? (y/n)
[n]:
The file "oraenv" already exists in /usr/local/bin.  Overwrite it? (y/n)
[n]:
The file "coraenv" already exists in /usr/local/bin.  Overwrite it? (y/n)
[n]:

Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root.sh script.
Now product-specific root actions will be performed.
2015-07-22 01:35:19: Parsing the host name
2015-07-22 01:35:19: Checking for super user privileges
2015-07-22 01:35:19: User has super user privileges
Using configuration parameter file: /u01/app/11.2.0/grid/crs/install/crsconfig_params
LOCAL ADD MODE
Creating OCR keys for user 'root', privgrp 'root'..
Operation successful.
Adding daemon to inittab
CRS-4123: Oracle High Availability Services has been started.
ohasd is starting
ADVM/ACFS is not supported on oraclelinux-release-5-8.0.2



CRS-4402: The CSS daemon was started in exclusive mode but found an active CSS daemon on node host01, number 1, and is terminating
An active cluster was found during exclusive startup, restarting to join the cluster
CRS-2672: Attempting to start 'ora.mdnsd' on 'host02'
CRS-2676: Start of 'ora.mdnsd' on 'host02' succeeded
CRS-2672: Attempting to start 'ora.gipcd' on 'host02'
CRS-2676: Start of 'ora.gipcd' on 'host02' succeeded
CRS-2672: Attempting to start 'ora.gpnpd' on 'host02'
CRS-2676: Start of 'ora.gpnpd' on 'host02' succeeded
CRS-2672: Attempting to start 'ora.cssdmonitor' on 'host02'
CRS-2676: Start of 'ora.cssdmonitor' on 'host02' succeeded
CRS-2672: Attempting to start 'ora.cssd' on 'host02'
CRS-2672: Attempting to start 'ora.diskmon' on 'host02'
CRS-2676: Start of 'ora.diskmon' on 'host02' succeeded
CRS-2676: Start of 'ora.cssd' on 'host02' succeeded
CRS-2672: Attempting to start 'ora.ctssd' on 'host02'
CRS-2676: Start of 'ora.ctssd' on 'host02' succeeded
CRS-2672: Attempting to start 'ora.asm' on 'host02'
CRS-2676: Start of 'ora.asm' on 'host02' succeeded
CRS-2672: Attempting to start 'ora.crsd' on 'host02'
CRS-2676: Start of 'ora.crsd' on 'host02' succeeded
CRS-2672: Attempting to start 'ora.evmd' on 'host02'
CRS-2676: Start of 'ora.evmd' on 'host02' succeeded

host02     2015/07/22 01:38:12     /u01/app/11.2.0/grid/cdata/host02/backup_20150722_013812.olr
Preparing packages for installation...
cvuqdisk-1.0.7-1
Configure Oracle Grid Infrastructure for a Cluster ... succeeded
Updating inventory properties for clusterware
Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB.   Actual 4094 MB    Passed
The inventory pointer is located at /etc/oraInst.loc
The inventory is located at /u01/app/oraInventory
'UpdateNodeList' was successful.



Note :- Now re-run of root.sh script on node2 went fine.

Oracle 11g R2 Grid Infrastructure/ASM Setup for Standalone Server Step By Step

Oracle Database Automatic Storage Management(ASM):
-----------------------------------------------------------------------------------

DBA's sometimes maintain thousands of datafiles for each database they manage, so an Oracle storage solution should provide both high-performance I/O and failure-proof storage hardware. In fact, file and I/O management what what usually takes up a large part of an Oracle DBA's time. With Oracle's new ASM feature, you can automate traditional file management task. Under an ASM system, the Oracle DBA is in change of the management of physical storage from within Oracle's framework, instead of relying on the system administrator. Using the ASM diskgroups, you can address sets of disk simultaneously, instead of individual disks, and the database can dynamically configure storage based on changing workloads. By allowing the Oracle DBA the flexibility to manage complex storage-management devices across various server and storage platforms, ASM becomes a crucial part of Oracle's grid computing initiative.


ASM is built on OMF, which means you don't have to worry about specifying filenames and location when creating new databases - all you have to do is identifying an ASM disk group, which consists of a set of disks. When you create a database or add a file, you can use familiar CREATE, ALTER and DROP SQL statements to allocate disk space. ASM acts as Oracle's built-in Logical Volume Manager by handling stripping and mirroring functions previously managed by third party tools. Under ASM, disk are grouped and managed by the database itself and made available for creating tablespaces. You don't have to mount the files as with the normal Linux or UNIX file systems. You also can't use the traditional tools, such as cp and tar, to copy the ASM files, nor you can describe them using the ls command. The database holds all information regarding ASM files. If you use ASM for an oracle file, the operating system can't see it. but RMAN and Oracle's other tool can.

For example, issue the following command:

SQL> ALTER DATABASE BACKUP CONTROFILE TO TRACE AS <filename>;

The file generated by the previous statement will display the names of any ASM files. If ASM uses fully qualified names, you can see datafiles in the views such as V$DATAFILE and V$LOGFILE.
When assigning a file to a tablespace or other object in an ASM file system, you don't need to know its name, You can simply refer to a disk group, and ASM automatically generates the filename.
Instead of learning to utilize a whole set of commands to manage ASM database, you can just use the OEM Database Control to manage virtually all ASM operations. You can create a new ASM instance with the DBCA or with the Oracle Universal Installer(which uses DBCA behind the scenes) and you can migrate an exising database to an ASM system with the Database Control.



Benefits of ASM
============

By using ASM, you can manage data by selecting reliability and performance characteristics for data classes, rather than working with the large storage systems on a per-file basis. An ASM file system offers the following benefits:

1 - ASM provides automatic load balancing over all the available disks, thus reducing hot spots in the file system.

2 - ASM prevents fragmentation of disks, so you don't need to manually relocate data to tune I/O performance.

3 - Adding disks is straightforward - ASM automatically performs online disk reorganization when you add or remove storage.

4 - ASM uses redundancy features available in intelligent storage arrays.

5 - The ASM storage system stores all types of database files.

6 - ASM makes your file management tasks easier, because you will be dealing with just a few groups of disks, rather than a multitude of database files. ASM automatically creates the database files and places then in appropriate disk groups.

7 - ASM does mirroring and stripping, which in turn increases reliability and performance. You can select different reliability and performance characteristics for various types of data. For example, you can use fine-grained stripping for redo log files and coarse-grained stripping for regular datafiles.

8 - ASM is free!


Please refer the book  - Expert Oracle Database 11g Administration, written by Sam R. Alapati for detailed information.
===================================================


I have used Oracle Enterprise Linux 5.8 platform for this paper and all required pre-requisites are already done on the host. I would show step by step installation of Grid Infrastructure and it doesn't include Oracle 11g R2 RDBMS installation as its database binary is already installed over there.


This Paper has two parts:

1 - Configuring and Installing Oracle 11g R2 Grid Infrastructure.

2 - Create Oracle 11g R2 Database based on ASM storage.


Let's Proceed with the 1st part : Configure and Install Grid Infrastructure


Step : 1 - Download Oracle 11g R2 Grid Infrastructure and Oracle 11g R2 database software from Oracle portal [http://www.oracle.com/technetwork/database/clusterware/overview/index-096607.html]


linux_11gR2_grid.zip  --- Grid Infrastructure Software

linux_11gR2_database_1of2.zip   --- Database Software Part - I
linux_11gR2_database_2of2.zip   --- Database Software Part - II



[root@localhost disks]# rpm -qa | grep oracleasm
oracleasm-support-2.1.7-1.el5

Note : - As we are using OEL 5.8 so oracle has already included ASM library packages in there by default so no need to install them separately. All you need is just a oracleasm-support package.

Step : 2 - In order to install Oracle 11g R2 Grid Infrastructure with success, list of below OS rpm packages need to installed on the server.


32-bit (x86) Installations
------------------------

binutils-2.17.50.0.6
compat-libstdc++-33-3.2.3
elfutils-libelf-0.125
elfutils-libelf-devel-0.125
elfutils-libelf-devel-static-0.125
gcc-4.1.2
gcc-c++-4.1.2
glibc-2.5-24
glibc-common-2.5
glibc-devel-2.52
glibc-headers-2.5
kernel-headers-2.6.18
ksh-20060214
libaio-0.3.106
libaio-devel-0.3.106
libgcc-4.1.2
libgomp-4.1.2
libstdc++-4.1.2
libstdc++-devel-4.1.2
make-3.81
pdksh-5.2.14
sysstat-7.0.2
unixODBC-2.2.11
unixODBC-devel-2.2.11


Please Refer Oracle Doc - [http://docs.oracle.com/cd/E11882_01/install.112/e41961.pdf] for completed OS level configuration for Grid Infrastructure Installation.


Step : 3 - Create appropriate OS groups and oragrid user for Grid installation.

[root@localhost ~]# groupadd oinstall
[root@localhost ~]# groupadd osdba
[root@localhost ~]# groupadd asmadmin
[root@localhost ~]# groupadd asmdba
[root@localhost ~]# groupadd asmoper
[root@localhost ~]# groupadd osoper


[root@localhost ~]# useradd -g oinstall -G asmdba,asmadmin,asmoper oragrid


[oragrid@localhost ~]$ id
uid=1101(oragrid) gid=1000(oinstall) groups=1000(oinstall),1201(asmdba),1202(asmadmin),1203(asmoper)

[root@localhost ~]# useradd -u 1101 -g oinstall -G dba oracle - This account is used for Oracle Database installation.


Step : 4 -  We will be installing Oracle 11g R2 GI under "oragrid" user which would be separate from oracle database user (oracle). As we know, Oracle Automatic Storage Management needs devices not formatted with any file system so we will be adding 4 new Hard Disks in the VM, first two of them will be used for  OCR_DATA disk group which has Normal redundancy, in our case. Third disk will be used for FRA diskgroup and 4th device will be used for DATA diskgroup to store all database files in there.

a) Click on VM at menu bar and click on Settings.

b) Now select Hard Disk at left panel and click on Add at bottom.

c) Again select Hard Disk and Click on Next.

d) Select SCSI(Recommended) Option and Click on Next.

e) Select "Create a new virtual disk"and click on Next.

f) Specify the disk size to be added - 2.00GB and click on Next.

g) Click on Finish then you would see a new HDD of 2GB added.

Following the above steps(a-g) add two other 2GB device and add another 20GB device as depicted below.

Step : 5 - In order to list the devices using fdisk -l command, restart the system.

Step : 6 - Create partitions of the devices you added above. 

[root@localhost ~]# fdisk -l

Disk /dev/sda: 85.8 GB, 85899345920 bytes

255 heads, 63 sectors/track, 10443 cylinders

Units = cylinders of 16065 * 512 = 8225280 bytes

   Device Boot      Start         End      Blocks   Id  System

/dev/sda1   *           1          13      104391   83  Linux

/dev/sda2              14        5235    41945715   83  Linux

/dev/sda3            5236        7846    20972857+  83  Linux

/dev/sda4            7847       10443    20860402+   5  Extended

/dev/sda5            7847        8368     4192933+  82  Linux swap / Solaris

/dev/sda6            8369        8559     1534176   83  Linux

Disk /dev/sdb: 2147 MB, 2147483648 bytes

255 heads, 63 sectors/track, 261 cylinders

Units = cylinders of 16065 * 512 = 8225280 bytes

Disk /dev/sdb doesn't contain a valid partition table

Disk /dev/sdc: 2147 MB, 2147483648 bytes

255 heads, 63 sectors/track, 261 cylinders

Units = cylinders of 16065 * 512 = 8225280 bytes

Disk /dev/sdc doesn't contain a valid partition table

Disk /dev/sdd: 2147 MB, 2147483648 bytes

255 heads, 63 sectors/track, 261 cylinders

Units = cylinders of 16065 * 512 = 8225280 bytes

Disk /dev/sdd doesn't contain a valid partition table

Disk /dev/sde: 21.4 GB, 21474836480 bytes

255 heads, 63 sectors/track, 2610 cylinders

Units = cylinders of 16065 * 512 = 8225280 bytes

Disk /dev/sde doesn't contain a valid partition table

Above highlighted Disks are the Hard Disk devices you added in previous step 4. Now let's create partition of these one by one.

a) [root@localhost ~]# fdisk /dev/sdb

Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel

Building a new DOS disklabel. Changes will remain in memory only,

until you decide to write them. After that, of course, the previous

content won't be recoverable.

Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)

Command (m for help): n   - here n stand for Create New partition

Command action

   e   extended

   p   primary partition (1-4)

p  --  Here p for primary partition

Partition number (1-4): 1  -- Given 1 here

First cylinder (1-261, default 1): 

Using default value 1

Last cylinder or +size or +sizeM or +sizeK (1-261, default 261):   -- Press Enter to accept its default value.

Using default value 261

Command (m for help): w  -- press w here to save the info in partition table and quit.

The partition table has been altered!

Calling ioctl() to re-read partition table.

WARNING: Re-reading the partition table failed with error 16: Device or resource busy.

The kernel still uses the old table.

The new table will be used at the next reboot.

Syncing disks.

======= Same way partition all other 3 devices added and listed above ======

Now you would partitions are created for all the devices as follows:

[root@localhost ~]# fdisk -l

Disk /dev/sda: 85.8 GB, 85899345920 bytes

255 heads, 63 sectors/track, 10443 cylinders

Units = cylinders of 16065 * 512 = 8225280 bytes

   Device Boot      Start         End      Blocks   Id  System

/dev/sda1   *           1          13      104391   83  Linux

/dev/sda2              14        5235    41945715   83  Linux

/dev/sda3            5236        7846    20972857+  83  Linux

/dev/sda4            7847       10443    20860402+   5  Extended

/dev/sda5            7847        8368     4192933+  82  Linux swap / Solaris

/dev/sda6            8369        8559     1534176   83  Linux

Disk /dev/sdb: 2147 MB, 2147483648 bytes

255 heads, 63 sectors/track, 261 cylinders

Units = cylinders of 16065 * 512 = 8225280 bytes

   Device Boot      Start         End      Blocks   Id  System

/dev/sdb1               1         261     2096451   83  Linux

Disk /dev/sdc: 2147 MB, 2147483648 bytes

255 heads, 63 sectors/track, 261 cylinders

Units = cylinders of 16065 * 512 = 8225280 bytes

   Device Boot      Start         End      Blocks   Id  System

/dev/sdc1               1         261     2096451   83  Linux

Disk /dev/sdd: 2147 MB, 2147483648 bytes

255 heads, 63 sectors/track, 261 cylinders

Units = cylinders of 16065 * 512 = 8225280 bytes

   Device Boot      Start         End      Blocks   Id  System

/dev/sdd1               1         261     2096451   83  Linux

Disk /dev/sde: 21.4 GB, 21474836480 bytes

255 heads, 63 sectors/track, 2610 cylinders

Units = cylinders of 16065 * 512 = 8225280 bytes

   Device Boot      Start         End      Blocks   Id  System

/dev/sde1               1        2610    20964793+  83  Linux

Step : 7 - Create Oracle ASM disks to make it available to Oracle ASM to created disk groups for the grid/database data storage.

[root@localhost ~]# oracleasm createdisk DISK1 '/dev/sdb1'

Writing disk header: done

Instantiating disk: done

[root@localhost ~]# oracleasm createdisk DISK2 '/dev/sdc1'

Writing disk header: done

Instantiating disk: done

[root@localhost ~]# oracleasm createdisk DISK3 '/dev/sdd1'

Writing disk header: done

Instantiating disk: done

[root@localhost ~]# oracleasm createdisk DISK4 '/dev/sde1'

Writing disk header: done

Instantiating disk: done

Step : 8 - Configure the Oracle ASM library owner and start mode of ASM library driver on start-up and scanning of Oracle ASM disk on system reboot automatically for you so that you don't have to start ASM services manually.

[root@localhost ~]# oracleasm configure -i

Configuring the Oracle ASM library driver.

This will configure the on-boot properties of the Oracle ASM library

driver.  The following questions will determine whether the driver is

loaded on boot and what permissions it will have.  The current values

will be shown in brackets ('[]').  Hitting <ENTER> without typing an

answer will keep that current value.  Ctrl-C will abort.

Default user to own the driver interface [oragrid]: 

Default group to own the driver interface [oinstall]: 

Start Oracle ASM library driver on boot (y/n) [y]: y

Scan for Oracle ASM disks on boot (y/n) [y]: y

Writing Oracle ASM library driver configuration: done

Note : In this case oracleasm was already configured for the user oragrid so I didn't provide any input for default user and default group but if in your case the user is root then provide appropriate user and default group accordingly. Now if you go the physical location of disks created, you would see its owner has changed to oragrid as follows.

[root@localhost disks]# pwd

/dev/oracleasm/disks

[root@localhost disks]# ls -ltr

total 0

brw-rw---- 1 oragrid oinstall 8, 65 Jul 14 15:09 DISK4

brw-rw---- 1 oragrid oinstall 8, 49 Jul 14 15:09 DISK3

brw-rw---- 1 oragrid oinstall 8, 33 Jul 14 15:09 DISK2

brw-rw---- 1 oragrid oinstall 8, 17 Jul 14 15:09 DISK1

Note : If above user and group don't belong to the user/group under which you are running Oracle Universal Installer to install Grid Infrastructure then you won't to be able to see the list of disks on disk group creation page. This is where if you are not properly configured/set then you won't be able to see any disks at GUI even try with changing the disk discovery path so be ensured you have configured oracle asm($oracleasm configure -i) with correct user/group.

Step : 9 - Check to see the list of ASM disks created.

[root@localhost ~]# oracleasm scandisks

Reloading disk partitions: done

Cleaning any stale ASM disks...

Scanning system for ASM disks...

[root@localhost ~]# 

[root@localhost ~]# oracleasm listdisks

DISK1

DISK2

DISK3

DISK4

Fine ASM disks are created now.

Step : 10 - Now login with oragrid user(I have created this separate user to run ASM instance. we will be using oracle user for oracle database instance).

Navigate to the appropriate installer location and run the ./runInstaller

[oragrid@localhost grid]$ ./runInstaller 

Starting Oracle Universal Installer...

Checking Temp space: must be greater than 80 MB.   Actual 998 MB    Passed

Checking swap space: must be greater than 150 MB.   Actual 4094 MB    Passed

Checking monitor: must be configured to display at least 256 colors.    Actual 16777216    Passed

Preparing to launch Oracle Universal Installer from /tmp/OraInstall2015-07-14_12-34-17PM. Please wait ...

Select Install and Configure Grid Infrastructure for  Standalone Server and Click on Next >> again click on Next.

Well, no disks are listed here to be used to create Disk Group so we will change the disk discovery path where oracle ASM disks physically located. Click on "Change Discovery Path" and give this path in the new pop-up box - /dev/oracleasm/disks(this is where your oracle ASM disks physically exists) and click on OK

Now we can see all disks are listed as in the above Fig.

TO store OCR/voting disk and all we will create OCR_DATA disk group with "Normal Redundancy" with two disks as Normal Redundancy requires at least two disks for mirroring its data across disks to prevent disk failure and survive its ASM operation. Click on Next.

Here I have chosen same password for SYS ans ASMSNMP accounts but if you want to separate then choose accordingly.

As Oracle recommends to use complex password so it flashes a warning message if we choose normal password so you can ignore and click on Next.

Click on Next.

Click on Next.

It's all ignorable so just click on Next but don't compromise for production server :-)

Step : 11 - Finally it asks to run root.sh script as root user in order to complete the installation process and Click on OK.

[root@localhost ~]# /u01/app/oragrid/product/11.2.0/grid/root.sh

Running Oracle 11g root.sh script...

The following environment variables are set as:

    ORACLE_OWNER= oragrid

    ORACLE_HOME=  /u01/app/oragrid/product/11.2.0/grid

Enter the full pathname of the local bin directory: [/usr/local/bin]: 

The file "dbhome" already exists in /usr/local/bin.  Overwrite it? (y/n) 

[n]: 

The file "oraenv" already exists in /usr/local/bin.  Overwrite it? (y/n) 

[n]: 

The file "coraenv" already exists in /usr/local/bin.  Overwrite it? (y/n) 

[n]: 

Creating /etc/oratab file...

Entries will be added to the /etc/oratab file as needed by

Database Configuration Assistant when a database is created

Finished running generic part of root.sh script.

Now product-specific root actions will be performed.

2015-07-14 13:07:21: Checking for super user privileges

2015-07-14 13:07:21: User has super user privileges

2015-07-14 13:07:21: Parsing the host name

Using configuration parameter file: /u01/app/oragrid/product/11.2.0/grid/crs/install/crsconfig_params

Creating trace directory

LOCAL ADD MODE 

Creating OCR keys for user 'oragrid', privgrp 'oinstall'..

Operation successful.

CRS-4664: Node localhost successfully pinned.

Adding daemon to inittab

CRS-4123: Oracle High Availability Services has been started.

ohasd is starting

ADVM/ACFS is not supported on oraclelinux-release-5-8.0.2

localhost     2015/07/14 13:07:47     /u01/app/oragrid/product/11.2.0/grid/cdata/localhost/backup_20150714_130747.olr

Successfully configured Oracle Grid Infrastructure for a Standalone Server

Updating inventory properties for clusterware

Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB.   Actual 4094 MB    Passed

The inventory pointer is located at /etc/oraInst.loc

The inventory is located at /u01/app/oraInventory

'UpdateNodeList' was successful.

Finally Click on Close button page.

Step : 12 - Now Let's verify our Grid Infrastructure Installation.

[oragrid@localhost ~]$ ps -ef | grep pmon

oragrid  10309 10151  0 17:03 pts/1    00:00:00 grep pmon

oragrid  10618     1  0 13:15 ?        00:00:03 asm_pmon_+ASM

[oragrid@localhost ~]$ ps -ef | grep css

oragrid  10311     1  0 13:14 ?        00:00:08 /u01/app/oragrid/product/11.2.0/grid/bin/cssdagent

oragrid  10348     1  1 13:14 ?        00:02:31 /u01/app/oragrid/product/11.2.0/grid/bin/ocssd.bin 

oragrid  10367 10151  0 17:03 pts/1    00:00:00 grep css

[oragrid@localhost ~]$ ps -ef | grep asm

oragrid  10618     1  0 13:15 ?        00:00:04 asm_pmon_+ASM

oragrid  10622     1  0 13:15 ?        00:00:46 asm_vktm_+ASM

oragrid  10628     1  0 13:15 ?        00:00:00 asm_gen0_+ASM

oragrid  10632     1  0 13:15 ?        00:00:01 asm_diag_+ASM

oragrid  10636     1  0 13:15 ?        00:00:00 asm_psp0_+ASM

oragrid  10640     1  0 13:15 ?        00:00:09 asm_dia0_+ASM

oragrid  10644     1  0 13:15 ?        00:00:00 asm_mman_+ASM

oragrid  10648     1  0 13:15 ?        00:00:03 asm_dbw0_+ASM

oragrid  10652     1  0 13:15 ?        00:00:01 asm_lgwr_+ASM

oragrid  10656     1  0 13:15 ?        00:00:01 asm_ckpt_+ASM

oragrid  10660     1  0 13:15 ?        00:00:00 asm_smon_+ASM

oragrid  10664     1  0 13:15 ?        00:00:03 asm_rbal_+ASM

oragrid  10668     1  0 13:15 ?        00:00:10 asm_gmon_+ASM

oragrid  10672     1  0 13:15 ?        00:00:01 asm_mmon_+ASM

oragrid  10676     1  0 13:15 ?        00:00:03 asm_mmnl_+ASM

We can see ASM instance is running. Let's login and validate its instance.

[oragrid@localhost ~]$ . oraenv

ORACLE_SID = [] ? +ASM

The Oracle base for ORACLE_HOME=/u01/app/oragrid/product/11.2.0/grid is /u01/app/oragrid

[oragrid@localhost ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Tue Jul 14 17:05:59 2015

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Automatic Storage Management option

SQL> set linesize 220

SQL> select * from v$instance;

INSTANCE_NUMBER INSTANCE_NAME    HOST_NAME                                                        VERSION           STARTUP_T STATUS       PAR    THREAD# ARCHIVE LOG_SWITCH_WAIT LOGINS     SHU DATABASE_STATUS

--------------- ---------------- ---------------------------------------------------------------- ----------------- --------- ------------ --- ---------- ------- --------------- ---------- --- -----------------

INSTANCE_ROLE      ACTIVE_ST BLO

------------------ --------- ---

              1 +ASM             localhost.localdomain                                            11.2.0.1.0        14-JUL-15 STARTED      NO           0 STOPPED               ALLOWED    NO  ACTIVE

UNKNOWN            NORMAL    NO

SQL> select name from v$asm_diskgroup;

NAME

------------------------------

OCR_DATA

DATA

FRA

[oragrid@localhost ~]$ asmcmd

ASMCMD> lsdg

State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name

MOUNTED  EXTERN  N         512   4096  1048576     20473    19017                0           19017              0             N  DATA/

MOUNTED  EXTERN  N         512   4096  1048576      2047     1735                0            1735              0             N  FRA/

MOUNTED  NORMAL  N         512   4096  1048576      4094     3974                0            1987              0             N  OCR_DATA/

Note : - I have created two other diskgroups named DATA and FRA to be used for database creation in part 2. I will explain how to create and manage an Oracle ASM diskgroup in a separate post.

Oracle 11g R2 Grid Infrastructure Installation completed successfully now.

Let's proceed to create a database based on the above Automatic Storage Management.

 As earlier I discussed that we have separate user for oracle database instance  and oracle binary already installed over there so in order to create a database based on ASM storage let's just switch to oracle user and run DBCA from its home 

Step : 1 - Launch DBA and click on Next. On Create Database page click on Next.

Step : 2 - Select General Purpose or Transaction Procession >> Click on Next

Step : 3 - Provided database name : testdb >> Click on Next.

Step : 4 -  Click on Next.

Step : 5 - Provide passwords and click on Next.

Step : 6 - Select Automatic Storage Management for Storage Type option. For Storage Locations - Use Oracle-Managed Files >> Click on browse and select appropriate diskgroup(DATA in our case) to be used for oracle database files and click on OK. >> Finally click on Next and provide the password you given earlier in step 5.

Step : 7 - Select option Flash Recovery Area and click on browse to choose appropriate diskgroup to be used for FRA and click enable archiving option and click on Next.

Step : 8 - Click on Next.

Step : 9 - Click on Next.

Step : 10 - Click on Next.

Step : 11 - Click on Finish and OK in order to starting database creation.

Now Database creation completed so lets verify its pmon process.

[oracle@localhost ~]$ ps -ef | grep pmon

oragrid  10618     1  0 13:15 ?        00:00:04 asm_pmon_+ASM

oracle   16770     1  0 17:43 ?        00:00:00 ora_pmon_testdb

oracle   26885 10204  0 18:02 pts/2    00:00:00 grep pmon

Note :- Here you can see that ASM instance is running under oragrid user and oracle database instance is running under oracle user.

[oracle@localhost ~]$ . oraenv   -- To set correct environment 

ORACLE_SID = [asmdb] ? testdb  -- given testdb in order to set its env

The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1 is /u01/app/oracle

[oracle@localhost ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Tue Jul 14 18:04:06 2015

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

and Real Application Testing options

SQL> set linesize 220

SQL> select name from v$datafile;

NAME

----------------------------------------------------------------

+DATA/testdb/datafile/system.260.885058597

+DATA/testdb/datafile/sysaux.265.885058597

+DATA/testdb/datafile/undotbs1.264.885058597

+DATA/testdb/datafile/users.263.885058599

SQL> show parameter control

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

control_file_record_keep_time        integer     7

control_files                        string      +DATA/testdb/controlfile/curre

                                                 nt.262.885058851, +FRA/testdb/

                                                 controlfile/current.256.885058

                                                 853

control_management_pack_access       string      DIAGNOSTIC+TUNING

SQL> 

SQL> select group#, status, member from v$logfile;

    GROUP# STATUS  MEMBER

---------- ------- ------------------------------------------------------------

         3         +DATA/testdb/onlinelog/group_3.258.885058861

         3         +FRA/testdb/onlinelog/group_3.257.885058863

         2         +DATA/testdb/onlinelog/group_2.259.885058859

         2         +FRA/testdb/onlinelog/group_2.258.885058859

         1         +DATA/testdb/onlinelog/group_1.261.885058855

         1         +FRA/testdb/onlinelog/group_1.259.885058857

So our database is finally created over Automatic Storage Management(ASM) storage.

Hope it would help someone....!!