Apply DB PSU 11.2.0.4.160119 (Jan2016) on Standalone Grid,ASM and Database 11.2.0.4

This document describe step to step procedure to apply Patch Set Update (PSU) on Oracle Standalone Grid, ASM and Database version 11.2.0.4 on Oracle Linux 6.5
Patch Set Update (PSU) patches are cumulative. That is, the content of all previous PSUs is included in the latest PSU patch.
This is a convenience patch that includes both the OJVM Component 11.2.0.4.160119 patch and the Database PSU 11.2.0.4.160119 (Jan2016) patch and can be downloaded via a single zip from My Oracle Support.
The Patch contains the following list of patches:

Patch 21948347 - Database Patch Set Update 11.2.0.4.160119 (Jan2016) --> RAC-Rolling Installable
Patch 22139245 - Oracle JavaVM Component 11.2.0.4.160119 Database PSU (JAN2016) --> Non RAC-Rolling Installable

To install patches, complete the following steps:

1. Download and extract the  patch file available under Patch 22378146 to a directory of your choice.
[root@oracle123 oracle]# unzip p22378146_112040_Linux-x86-64.zip

2. You must use the OPatch utility version 11.2.0.3.6 or later to apply this patch. Oracle recommends that you use the latest released OPatch version for 11.2, which is available for download from My Oracle Support patch 6880880 by selecting the 11.2.0.0.0 release.

Goto OPatch directory and check the OPatch version in Grid and DB homes.
[root@oracle123 oracle]# cd /u01/app/grid/product/11.2.0/grid/OPatch/

[root@oracle123 OPatch]# ./opatch version
OPatch Version: 11.2.0.3.4

OPatch succeeded.

If its old version. take a backup of the existing OPatch directory.
[root@oracle123 grid]# mv OPatch/ bkp-OPatch

Copy latest downloaded OPatch into Grid and DB homes and unzip.
[root@oracle123 grid]# cp /home/oracle/p6880880_112000_LINUX.zip /u01/app/grid/product/11.2.0/grid/
[root@oracle123 grid]# unzip p6880880_112000_LINUX.zip
[root@oracle123 grid]# ls -l OPatch/

Verify the OPatch version.
[root@oracle123 OPatch]# ./opatch version
OPatch Version: 11.2.0.3.12

OPatch succeeded.

Change the ownership of the OPatch dir.
[root@oracle123 db_1]# chown -R oracle:oinstall OPatch/

3. OCM Configuration
The OPatch utility will prompt for your OCM (Oracle Configuration Manager) response file when it is run. You should enter a complete path of OCM response file if you already have created this in your environment.
OCM response file is required and is not optional.
[oracle@oracle123 bin]$ cd /u01/app/grid/product/11.2.0/grid/OPatch/ocm/bin/
[oracle@oracle123 bin]$ ./emocmrsp

4. Validation of Oracle Inventory
Before beginning patch application, check the consistency of inventory information for GI home and each database home to be patched.
[root@oracle123 OPatch]#./opatch lsinventory -detail -oh /u01/app/grid/product/11.2.0/grid

5. Patching Oracle Grid home.
As root user, execute the following command on each node of the cluster:
[root@oracle123 OPatch]# ./opatch auto /home/oracle/22378146/ -oh /u01/app/grid/product/11.2.0/grid
Executing /u01/app/grid/product/11.2.0/grid/perl/bin/perl ./crs/patch11203.pl -patchdir /home/oracle -patchn 22378146 -oh /u01/app/grid/product/11.2.0/grid -paramfile /u01/app/grid/product/11.2.0/grid/crs/install/crsconfig_params

This is the main log file: /u01/app/grid/product/11.2.0/grid/cfgtoollogs/opatchauto2016-03-22_00-57-00.log

This file will show your detected configuration and all the steps that opatchauto attempted to do on your system:
/u01/app/grid/product/11.2.0/grid/cfgtoollogs/opatchauto2016-03-22_00-57-00.report.log

2016-03-22 00:57:00: Starting Oracle Restart Patch Setup
Using configuration parameter file: /u01/app/grid/product/11.2.0/grid/crs/install/crsconfig_params
Enter 'yes' if you have unzipped this patch to an empty directory to proceed  (yes/no):yes
Enter 'yes' if you have unzipped this patch to an empty directory to proceed  (yes/no):yes
OPatch  is bundled with OCM, Enter the absolute OCM response file path:
/u01/app/grid/product/11.2.0/grid/OPatch/ocm/bin/ocm.rsp

Stopping CRS...
Stopped CRS successfully

patch /home/oracle/22378146/21948347  apply successful for home  /u01/app/grid/product/11.2.0/grid
patch /home/oracle/22378146/22139245  apply successful for home  /u01/app/grid/product/11.2.0/grid

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

opatch auto succeeded.

Check opatch lsinventory to verify the latest PSU applied successfully.

[oracle@oracle123 OPatch]$ ./opatch lsinventory
Oracle Interim Patch Installer version 11.2.0.3.12
Copyright (c) 2016, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/app/grid/product/11.2.0/grid
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/grid/product/11.2.0/grid/oraInst.loc
OPatch version    : 11.2.0.3.12
OUI version       : 11.2.0.4.0
Log file location : /u01/app/grid/product/11.2.0/grid/cfgtoollogs/opatch/opatch2016-03-22_01-18-00AM_1.log

Lsinventory Output file location : /u01/app/grid/product/11.2.0/grid/cfgtoollogs/opatch/lsinv/lsinventory2016-03-22_01-18-00AM.txt

--------------------------------------------------------------------------------
Local Machine Information::
Hostname: oracle123
ARU platform id: 226
ARU platform description:: Linux x86-64

Installed Top-level Products (1):

Oracle Grid Infrastructure 11g                                       11.2.0.4.0
There are 1 products installed in this Oracle Home.


Interim patches (2) :

Patch  22139245     : applied on Tue Mar 22 01:07:18 PHT 2016
Unique Patch ID:  19668301
Patch description:  "ORACLE JAVAVM COMPONENT 11.2.0.4.160119 DATABASE PSU (JAN2016)"
   Created on 12 Dec 2015, 09:49:36 hrs PST8PDT
   Bugs fixed:
     19058059, 18933818, 19176885, 17201047, 19007266, 19554117, 14774730
     17285560, 19153980, 21911849, 18166577, 19187988, 18458318, 19374518
     19006757, 17056813, 21811517, 19909862, 19223010, 22118835, 19895326
     22253904, 20408829, 19852360, 17804361, 21047766, 19231857, 17528315, 21566944

Patch  21948347     : applied on Tue Mar 22 01:05:35 PHT 2016
Unique Patch ID:  19564435
Patch description:  "Database Patch Set Update : 11.2.0.4.160119 (21948347)"
   Created on 14 Dec 2015, 03:31:48 hrs PST8PDT
Sub-patch  21352635; "Database Patch Set Update : 11.2.0.4.8 (21352635)"
Sub-patch  20760982; "Database Patch Set Update : 11.2.0.4.7 (20760982)"
Sub-patch  20299013; "Database Patch Set Update : 11.2.0.4.6 (20299013)"
Sub-patch  19769489; "Database Patch Set Update : 11.2.0.4.5 (19769489)"
Sub-patch  19121551; "Database Patch Set Update : 11.2.0.4.4 (19121551)"
Sub-patch  18522509; "Database Patch Set Update : 11.2.0.4.3 (18522509)"
Sub-patch  18031668; "Database Patch Set Update : 11.2.0.4.2 (18031668)"
Sub-patch  17478514; "Database Patch Set Update : 11.2.0.4.1 (17478514)"
   Bugs fixed:
     17288409, 21051852, 18607546, 17205719, 17811429, 17816865, 20506699
     17922254, 17754782, 16934803, 13364795, 17311728, 17441661, 17284817
     16992075, 17446237, 14015842, 19972569, 17449815, 21538558, 20925795
     17375354, 19463897, 17982555, 17235750, 13866822, 17478514, 18317531
     18235390, 14338435, 20803583, 13944971, 20142975, 17811789, 16929165
     18704244, 20506706, 17546973, 20334344, 14054676, 17088068, 18264060
     17346091, 17343514, 21538567, 19680952, 18471685, 19211724, 13951456
     21847223, 16315398, 18744139, 16850630, 19049453, 18673304, 17883081
     19915271, 18641419, 18262334, 17006183, 16065166, 18277454, 16833527
     10136473, 18051556, 17865671, 17852463, 18554871, 17853498, 18334586
     17588480, 17551709, 19827973, 17842825, 17344412, 18828868, 17025461
     11883252, 13609098, 17239687, 17602269, 19197175, 22195457, 18316692
     17313525, 12611721, 19544839, 18964939, 17600719, 18191164, 19393542
     17571306, 18482502, 20777150, 19466309, 17040527, 17165204, 18098207
     16785708, 17174582, 16180763, 17465741, 16777840, 12982566, 19463893
     22195465, 12816846, 16875449, 17237521, 19358317, 17811438, 17811447
     17945983, 18762750, 17184721, 16912439, 18061914, 17282229, 18331850
     18202441, 17082359, 18723434, 21972320, 19554106, 14034426, 18339044
     19458377, 17752995, 20448824, 17891943, 17258090, 17767676, 16668584
     18384391, 17040764, 17381384, 15913355, 18356166, 14084247, 20506715
     13853126, 18203837, 14245531, 21756699, 16043574, 22195441, 17848897
     17877323, 21453153, 17468141, 20861693, 17786518, 17912217, 17037130
     18155762, 16956380, 17478145, 17394950, 18189036, 18641461, 18619917
     17027426, 21352646, 16268425, 22195492, 19584068, 18436307, 17265217
     17634921, 13498382, 21526048, 20004087, 22195485, 17443671, 18000422
     22321756, 20004021, 17571039, 21067387, 16344544, 18009564, 14354737
     18135678, 18614015, 20441797, 18362222, 17835048, 16472716, 17936109
     17050888, 17325413, 14010183, 18747196, 17761775, 16721594, 17082983
     20067212, 21179898, 17302277, 18084625, 15990359, 18203835, 17297939
     17811456, 16731148, 21168487, 17215560, 13829543, 14133975, 17694209
     18091059, 17385178, 8322815, 17586955, 17201159, 17655634, 18331812
     19730508, 18868646, 17648596, 16220077, 16069901, 17348614, 17393915
     17274537, 17957017, 18096714, 17308789, 18436647, 14285317, 19289642
     14764829, 18328509, 17622427, 22195477, 16943711, 14368995, 17346671
     18996843, 17783588, 21343838, 16618694, 17672719, 18856999, 18783224
     17851160, 17546761, 17798953, 18273830, 22092979, 19972566, 16384983
     17726838, 17360606, 22321741, 13645875, 18199537, 16542886, 21787056
     17889549, 14565184, 17071721, 17610798, 20299015, 21343897, 20657441
     17397545, 18230522, 16360112, 19769489, 12905058, 18641451, 12747740
     18430495, 17042658, 17016369, 14602788, 17551063, 19972568, 21517440
     18508861, 19788842, 14657740, 17332800, 13837378, 19972564, 17186905
     18315328, 19699191, 17437634, 19006849, 19013183, 17296856, 18674024
     17232014, 16855292, 21051840, 14692762, 17762296, 17705023, 19121551
     21330264, 19854503, 19309466, 18681862, 18554763, 20558005, 17390160
     18456514, 16306373, 13955826, 18139690, 17501491, 21668627, 17299889
     17752121, 17889583, 18673325, 18293054, 17242746, 17951233, 17649265
     18094246, 19615136, 17011832, 16870214, 17477958, 18522509, 20631274
     16091637, 17323222, 16595641, 16524926, 18228645, 18282562, 17596908
     17156148, 18031668, 16494615, 17545847, 17655240, 17614134, 13558557
     17341326, 17891946, 17716305, 16392068, 19271443, 21351877, 18092127
     18440047, 17614227, 14106803, 16903536, 18973907, 18673342, 19032867
     17389192, 17612828, 16194160, 17006570, 17721717, 17570240, 17390431
     16863422, 18325460, 19727057, 16422541, 19972570, 17267114, 18244962
     21538485, 18765602, 18203838, 16198143, 17246576, 14829250, 17835627
     18247991, 14458214, 21051862, 16692232, 17786278, 17227277, 16042673
     16314254, 16228604, 16837842, 17393683, 17787259, 20331945, 20074391
     15861775, 16399083, 18018515, 21051858, 18260550, 17036973, 16613964
     17080436, 16579084, 18384537, 18280813, 20296213, 16901385, 15979965
     18441944, 16450169, 9756271, 17892268, 11733603, 16285691, 17587063
     21343775, 16538760, 18180390, 18193833, 21051833, 17238511, 17824637
     16571443, 18306996, 14852021, 18674047, 17853456, 12364061, 22195448



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

OPatch succeeded.

6. Loading Modified SQL Files into the Database
For each database instance running on the Oracle home being patched, connect to the database using SQL*Plus. Connect as SYSDBA and run the catbundle.sql script as follows:

SQL> @?/rdbms/admin/catbundle.sql psu apply

Check if the registry  is updated

select ACTION_TIME,ACTION,NAMESPACE,VERSION,BUNDLE_SERIES,ID from registry$history;

PSU has been applied successfully.

for more info pls refer to the Readme provided with patch.

Thanks.

Drop last active Diskgroup in Oracle 11g ASM

This document will describe how to drop the last active Diskgroup in Oracle 11g ASM.
In my testing virtual box i installed standalone Grid 11g with ASM on Oracle Linux 6.4
i simulated the scenario to drop the last active disk group.

First tried to drop the last active diskgroup, Oracle thrown  the error like below.

SQL> drop diskgroup data including contents;
drop diskgroup data including contents
*
ERROR at line 1:
ORA-15039: diskgroup not dropped
ORA-15027: active use of diskgroup "DATA" precludes its dismount


Solution:- dismount the active diskdroup with force.

SQL> alter diskgroup data dismount force;

Diskgroup altered.

Drop the dismounted diskdroup using force.

SQL> drop diskgroup data force including contents;

Diskgroup dropped.

Verify  the diskgroups.

SQL> select * from V$ASM_DISKGROUP;

no rows selected

SP2-0310: unable to open file "catbundle.sql"

After up gradation from Oracle database 11.2.0.3 to 11.2.0.4  when its come to execute catbundle.sql i faced this issue.



SQL> @catbundle.sql psu apply
SP2-0310: unable to open file "catbundle.sql"


Below is solution to execute the script successfully.


SQL> @?/rdbms/admin/catbundle.sql psu apply

PL/SQL procedure successfully completed.

Thanks..

ORA-01207: file is more recent than control file - old control file

 In my test server during migration from Oracle Database 11.2.0.3 to 11.2.0.4. i have faced below errors. i had no backup of the database. here, i'm sharing my experience to recover the Database using redolog files.


When i tried to connect to database i faced this error.

ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: '/u02/app/oracle/oradata/db11g/system01.dbf'
ORA-01207: file is more recent than control file - old control file


 Check the files required recovery.

SQL> select * from v$recover_file;
    FILE# ONLINE  ONLINE_ ERROR CHANGE# TIME
---------- ------- ------- ----------------------------------------------------------------- ---------- ---------
1 ONLINE  ONLINE  UNKNOWN ERROR 2062569 17-MAR-16
2 ONLINE  ONLINE  UNKNOWN ERROR 2062569 17-MAR-16
3 ONLINE  ONLINE  UNKNOWN ERROR 2062569 17-MAR-16
4 ONLINE  ONLINE  UNKNOWN ERROR 2062569 17-MAR-16
5 ONLINE  ONLINE  UNKNOWN ERROR 2062569 17-MAR-16


 Execute the recover database command using controlfile. it will prompt for redolog files.
provide complete path of the redo log files and observer the output of results.

SQL> recover database until cancel using backup controlfile;


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u02/app/oracle/oradata/db11g/redo01.log
ORA-00328: archived log ends at change 2053116, need later change 2061969
ORA-00334: archived log: '/u02/app/oracle/oradata/db11g/redo01.log'


ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/u02/app/oracle/oradata/db11g/system01.dbf'

Alter applying one redo log file. i tried to open the database with resetlogs. but still error message, datafile was not restored from sufficient backp.

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/u02/app/oracle/oradata/db11g/system01.dbf'


 Execute the recover database command again and enter the second redo log file.

SQL> recover database until cancel using backup controlfile;
ORA-00279: change 2061969 generated at 03/17/2016 21:31:59 needed for thread 1
ORA-00289: suggestion : /u02/app/oracle/fast_recovery_area/DB11G/archivelog/2016_03_18/o1_mf_1_65_%u_.arc
ORA-00280: change 2061969 for thread 1 is in sequence #65

This time i seen the message Media recovery complete.

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u02/app/oracle/oradata/db11g/redo02.log
Log applied.
Media recovery complete.

Then open the Database with resetlogs.

SQL> alter database open resetlogs;

Database altered.

Verify the status of the Database.

SQL> select status from v$instance;

STATUS
------------
OPEN

Database is recovered with redolog files.

Cheers!!!