Month: December 2015

Applying Patch 21555791: ORACLE JAVAVM COMPONENT 11.2.0.4.5 DATABASE PSU (OCT2015)

Few concerns for this patch:

  1. This patch is not Oracle RAC Rolling installable.
  2. This patch is not Data Guard Standby First Installable.
  3. This patch requires any one of the following Oct2014 or greater to be already installed prior to installing this patch:
    • Database PSU 11.2.0.4.4 (Oct2014)
    • Database SPU 11.2.0.4 (CPUOct2014)
    • Database patch for Exadata 11.2.0.4.12 (Oct2014)

Note: We have applied Database SPU/CPU 11.2.0.4 (CPUOCT2015) Patch in our previous post so we are good for now.

Patch Information:

The Oracle JavaVM Component 11.2.0.4.5 Database PSU is cumulative and includes the Database CPU program security content including Oracle JavaVM Component 11.2.0.4.1 Database PSU – Generic JDBC Patch 19852360 that was released as a standalone patch as part of CPUOct2014 cycle.

Prerequisites:

  1. Ensure that the Oracle Database on which you are installing the patch or from which you are rolling back the patch is Oracle Database 11g Release 2 (11.2.0.4.0).
  2. You must use the OPatch utility version 11.2.0.3.5 or later to apply this patch. Oracle recommends that you use the latest released OPatch version for 11.2, which is available.
  3. Ensure that you set the ORACLE_HOME environment variable to the Oracle home of the Oracle Database.
  4. Ensure that the $PATH definition is configured correctly to call important utilities like opatch ( we have already discussed – hoe to get latest opatch utility in our previous posts)
  5. Ensure that you verify the Oracle Inventory because OPatch accesses it to install the patches. To verify the inventory, run the following command. If the command displays some errors, then contact Oracle Support and resolve the issue: opatch lsinventory.

Installing of Patch – Applying on ORACLE_HOME:

Step 1: Configure OPATCH utility in PATH variable if its not there already:

[oracle@ora-linux ~]$ which opatch
/usr/bin/which: no opatch in (.:/usr/local/java/bin:/usr/lib64/qt-3.3/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin:/home/oracle/bin:/u01/app/oracle/product/11.2.0.4/db_1/bin:/usr/bin:/bin:/usr/bin/X11:/usr/local/bin:/u01/app/common/oracle/bin)
[oracle@ora-linux ~]$

Its not configured so we have to configure it to call opatch from anywhere:

[oracle@ora-linux ~]$ export PATH=$ORACLE_HOME/OPatch:$PATH
[oracle@ora-linux ~]$
[oracle@ora-linux ~]$ which opatch
/u01/app/oracle/product/11.2.0.4/db_1/OPatch/opatch
[oracle@ora-linux ~]$

Now optach utility is being picked up:

Check the inventory of currently installed patches:

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

[oracle@ora-linux ~]$ opatch lsinventory
Oracle Interim Patch Installer version 11.2.0.3.12
Copyright (c) 2015, Oracle Corporation. All rights reserved.
Oracle Home : /u01/app/oracle/product/11.2.0.4/db_1
Central Inventory : /u01/app/oraInventory
from : /u01/app/oracle/product/11.2.0.4/db_1/oraInst.loc
OPatch version : 11.2.0.3.12
OUI version : 11.2.0.4.0
Log file location : /u01/app/oracle/product/11.2.0.4/db_1/cfgtoollogs/opatch/opatch2015-12-08_14-48-37PM_1.log

Lsinventory Output file location : /u01/app/oracle/product/11.2.0.4/db_1/cfgtoollogs/opatch/lsinv/lsinventory2015-12-08_14-48-37PM.txt

——————————————————————————–
Local Machine Information::
Hostname: ora-linux.oragyan.com
ARU platform id: 226
ARU platform description:: Linux x86-64

Installed Top-level Products (1):

Oracle Database 11g 11.2.0.4.0
There are 1 products installed in this Oracle Home.
Interim patches (27) :

Patch 21538567 : applied on Mon Dec 07 23:51:17 IST 2015
Unique Patch ID: 19281142
Created on 14 Aug 2015, 11:06:40 hrs PST8PDT
Bugs fixed:
20441797, 21538567

Patch 21538558 : applied on Mon Dec 07 23:51:16 IST 2015
Unique Patch ID: 19281142
Created on 14 Aug 2015, 11:06:18 hrs PST8PDT
Bugs fixed:
21538558, 20925795

Patch 21538485 : applied on Mon Dec 07 23:51:07 IST 2015
Unique Patch ID: 19281142
Created on 28 Aug 2015, 12:15:47 hrs PST8PDT
Bugs fixed:
20448824, 21538485

Patch 21352646 : applied on Mon Dec 07 23:51:06 IST 2015
Unique Patch ID: 19281142
Created on 1 Sep 2015, 08:43:27 hrs PST8PDT
Bugs fixed:
20299015, 18681862, 19727057, 21352646, 20803583, 18139690, 17343514
19271443, 19854503, 17551709

Patch 21179898 : applied on Mon Dec 07 23:51:04 IST 2015
Unique Patch ID: 18970187
Created on 4 Jun 2015, 11:58:09 hrs PST8PDT
Bugs fixed:
21179898, 19699191

Patch 21051862 : applied on Mon Dec 07 23:50:56 IST 2015
Unique Patch ID: 18970187
Created on 22 May 2015, 10:38:06 hrs PST8PDT
Bugs fixed:
20004021, 20004087, 21051862

Patch 21051858 : applied on Mon Dec 07 23:50:39 IST 2015
Unique Patch ID: 18970187
Created on 22 May 2015, 10:37:23 hrs PST8PDT
Bugs fixed:
20334344, 21051858

Patch 21051852 : applied on Mon Dec 07 23:50:28 IST 2015
Unique Patch ID: 18970187
Created on 27 May 2015, 14:03:47 hrs PST8PDT
Bugs fixed:
20331945, 21051852

Patch 21051840 : applied on Mon Dec 07 23:50:27 IST 2015
Unique Patch ID: 18970187
Created on 22 May 2015, 10:36:14 hrs PST8PDT
Bugs fixed:
20558005, 21051840

Patch 21051833 : applied on Mon Dec 07 23:50:26 IST 2015
Unique Patch ID: 18970187
Created on 22 May 2015, 11:39:55 hrs PST8PDT
Bugs fixed:
21051833, 20657441

Patch 20631274 : applied on Mon Dec 07 23:50:24 IST 2015
Unique Patch ID: 18695736
Created on 13 Mar 2015, 10:25:35 hrs PST8PDT
Bugs fixed:
20296213, 17600719, 19458377, 19554106, 20631274, 16524926, 19466309

Patch 20506715 : applied on Mon Dec 07 23:50:10 IST 2015
Unique Patch ID: 18695736
Created on 13 Mar 2015, 10:23:10 hrs PST8PDT
Bugs fixed:
19049453, 19915271, 16450169, 17006570, 17811456, 18554871, 17786278
18096714, 18744139, 20506715, 18061914

Patch 20506699 : applied on Mon Dec 07 23:49:58 IST 2015
Unique Patch ID: 18695736
Created on 13 Mar 2015, 11:23:48 hrs PST8PDT
Bugs fixed:
20506699, 19358317, 19197175, 19972570, 17762296

Patch 20142975 : applied on Mon Dec 07 23:49:53 IST 2015
Unique Patch ID: 18406185
Created on 22 Dec 2014, 00:47:05 hrs PST8PDT
Bugs fixed:
19827973, 19463897, 19289642, 20142975

Patch 19972569 : applied on Mon Dec 07 23:49:52 IST 2015
Unique Patch ID: 18406185
Created on 22 Dec 2014, 00:21:09 hrs PST8PDT
Bugs fixed:
19972569, 19006849

Patch 19972568 : applied on Mon Dec 07 23:49:51 IST 2015
Unique Patch ID: 18406185
Created on 22 Dec 2014, 00:13:36 hrs PST8PDT
Bugs fixed:
19972568, 19309466

Patch 19972566 : applied on Mon Dec 07 23:49:49 IST 2015
Unique Patch ID: 18406185
Created on 22 Dec 2014, 00:03:20 hrs PST8PDT
Bugs fixed:
19972566, 18641461, 18641451

Patch 19972564 : applied on Mon Dec 07 23:31:09 IST 2015
Unique Patch ID: 18406185
Created on 21 Dec 2014, 23:54:58 hrs PST8PDT
Bugs fixed:
19972564, 18436647

Patch 19584068 : applied on Mon Dec 07 23:30:59 IST 2015
Unique Patch ID: 18084194
Created on 24 Sep 2014, 07:43:52 hrs PST8PDT
Bugs fixed:
18262334, 19584068

Patch 19544839 : applied on Mon Dec 07 23:30:58 IST 2015
Unique Patch ID: 18084194
Created on 11 Oct 2014, 01:43:34 hrs PST8PDT
Bugs fixed:
18673325, 19544839, 19211724, 18673304

Patch 19463893 : applied on Mon Dec 07 23:30:46 IST 2015
Unique Patch ID: 18084194
Created on 24 Sep 2014, 06:28:06 hrs PST8PDT
Bugs fixed:
19463893, 18641419

Patch 18203838 : applied on Mon Dec 07 23:30:38 IST 2015
Unique Patch ID: 17351342
Created on 26 Feb 2014, 23:57:58 hrs PST8PDT
Bugs fixed:
17865671, 18203838

Patch 18203837 : applied on Mon Dec 07 23:30:30 IST 2015
Unique Patch ID: 17351342
Created on 26 Feb 2014, 23:48:38 hrs PST8PDT
Bugs fixed:
17716305, 18203837

Patch 18203835 : applied on Mon Dec 07 23:30:16 IST 2015
Unique Patch ID: 17351342
Created on 26 Feb 2014, 23:43:04 hrs PST8PDT
Bugs fixed:
17721717, 18203835

Patch 17811447 : applied on Mon Dec 07 23:30:15 IST 2015
Unique Patch ID: 17066906
Created on 11 Dec 2013, 02:20:51 hrs PST8PDT
Bugs fixed:
17811447, 17088068

Patch 17811438 : applied on Mon Dec 07 23:30:13 IST 2015
Unique Patch ID: 17066906
Created on 11 Dec 2013, 02:37:11 hrs PST8PDT
Bugs fixed:
17811438, 16721594

Patch 17811429 : applied on Mon Dec 07 23:30:03 IST 2015
Unique Patch ID: 17066906
Created on 11 Dec 2013, 02:36:54 hrs PST8PDT
Bugs fixed:
17811429, 13944971

——————————————————————————–

OPatch succeeded.
[oracle@ora-linux ~]$

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

Step 2: Determine whether any currently installed interim patches conflict with this patch 21555791 as shown as follows:

We can find that running the below command:

opatch prereq CheckConflictAgainstOHWithDetail -ph ./

We have to run this command being present in the patch directory.

[oracle@ora-linux 21555791]$ cd /tmp/oracle_patches/Patch_p21555791/21555791
[oracle@ora-linux 21555791]$
[oracle@ora-linux 21555791]$ ls -lrt
total 76
-rw-r–r–. 1 oracle oinstall 21 Oct 7 18:37 README.txt
drwxr-xr-x. 4 oracle oinstall 4096 Oct 7 18:37 etc
drwxr-xr-x. 2 oracle oinstall 4096 Oct 7 18:37 custom
drwxr-xr-x. 9 oracle oinstall 4096 Oct 7 18:37 files
-rw-r–r–. 1 oracle oinstall 5192 Oct 7 18:38 postinstall.sql
-rw-r–r–. 1 oracle oinstall 5201 Oct 7 18:38 postdeinstall.sql
-rw-r–r–. 1 oracle oinstall 2877 Oct 8 15:27 patchmd.xml
-rw-rw-r–. 1 oracle oinstall 37966 Oct 17 04:23 README.html
[oracle@ora-linux 21555791]$
[oracle@ora-linux 21555791]$ opatch prereq CheckConflictAgainstOHWithDetail -ph ./
Oracle Interim Patch Installer version 11.2.0.3.12
Copyright (c) 2015, Oracle Corporation. All rights reserved.

PREREQ session

Oracle Home : /u01/app/oracle/product/11.2.0.4/db_1
Central Inventory : /u01/app/oraInventory
from : /u01/app/oracle/product/11.2.0.4/db_1/oraInst.loc
OPatch version : 11.2.0.3.12
OUI version : 11.2.0.4.0
Log file location : /u01/app/oracle/product/11.2.0.4/db_1/cfgtoollogs/opatch/opatch2015-12-08_14-56-36PM_1.log

Invoking prereq “checkconflictagainstohwithdetail”

Prereq “checkConflictAgainstOHWithDetail” passed.

OPatch succeeded.
[oracle@ora-linux 21555791]$

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

Step 3: Apply the patch finally now:

Go to the patch directory and execute the below command:

opatch apply

=============================================================
[oracle@ora-linux ~]$ cd /tmp/oracle_patches/Patch_p21555791/21555791
[oracle@ora-linux 21555791]$
[oracle@ora-linux 21555791]$ ls
custom etc files patchmd.xml postdeinstall.sql postinstall.sql README.html README.txt
[oracle@ora-linux 21555791]$
[oracle@ora-linux 21555791]$ opatch apply
Oracle Interim Patch Installer version 11.2.0.3.12
Copyright (c) 2015, Oracle Corporation. All rights reserved.
Oracle Home : /u01/app/oracle/product/11.2.0.4/db_1
Central Inventory : /u01/app/oraInventory
from : /u01/app/oracle/product/11.2.0.4/db_1/oraInst.loc
OPatch version : 11.2.0.3.12
OUI version : 11.2.0.4.0
Log file location : /u01/app/oracle/product/11.2.0.4/db_1/cfgtoollogs/opatch/21555791_Dec_08_2015_15_21_12/apply2015-12-08_15-21-12PM_1.log

Verifying environment and performing prerequisite checks…
OPatch continues with these patches: 21555791

Do you want to proceed? [y|n]
y
User Responded with: Y
All checks passed.
Provide your email address to be informed of security issues, install and
initiate Oracle Configuration Manager. Easier for you if you use your My
Oracle Support Email address/User Name.
Visit http://www.oracle.com/support/policies.html for details.
Email address/User Name:

You have not provided an email address for notification of security issues.
Do you wish to remain uninformed of security issues ([Y]es, [N]o) [N]: y
Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = ‘/u01/app/oracle/product/11.2.0.4/db_1’)

Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files…
Applying interim patch ‘21555791’ to OH ‘/u01/app/oracle/product/11.2.0.4/db_1’
ApplySession: Optional component(s) [ oracle.sqlj, 11.2.0.4.0 ] , [ oracle.sqlj.companion, 11.2.0.4.0 ] not present in the Oracle Home or a higher version is found.

Patching component oracle.javavm.server, 11.2.0.4.0…

Patching component oracle.rdbms, 11.2.0.4.0…

Patching component oracle.rdbms.dbscripts, 11.2.0.4.0…

Patching component oracle.dbjava.jdbc, 11.2.0.4.0…

Patching component oracle.dbjava.ic, 11.2.0.4.0…

Patching component oracle.javavm.client, 11.2.0.4.0…

——————————————————————————–

*******************************************************************************************************
*******************************************************************************************************
** ATTENTION **
** **
** It is critical and essential to complete the post installation (if you are applying the patch) or **
** post deinstallation (if you are doing rollback of the patch) as per Readme to have a working **
** and functional Oracle JavaVM Component. **
** Failure to follow the instructions could lead to inconsistencies in the database. **
** **
*******************************************************************************************************
*******************************************************************************************************

——————————————————————————–

Patch 21555791 successfully applied.
Log file location: /u01/app/oracle/product/11.2.0.4/db_1/cfgtoollogs/opatch/21555791_Dec_08_2015_15_21_12/apply2015-12-08_15-21-12PM_1.log

OPatch succeeded.
[oracle@ora-linux 21555791]$

Check the inventory once again:

[oracle@ora-linux 21555791]$ opatch lsinventory
Oracle Interim Patch Installer version 11.2.0.3.12
Copyright (c) 2015, Oracle Corporation. All rights reserved.
Oracle Home : /u01/app/oracle/product/11.2.0.4/db_1
Central Inventory : /u01/app/oraInventory
from : /u01/app/oracle/product/11.2.0.4/db_1/oraInst.loc
OPatch version : 11.2.0.3.12
OUI version : 11.2.0.4.0
Log file location : /u01/app/oracle/product/11.2.0.4/db_1/cfgtoollogs/opatch/opatch2015-12-08_16-36-09PM_1.log

Lsinventory Output file location : /u01/app/oracle/product/11.2.0.4/db_1/cfgtoollogs/opatch/lsinv/lsinventory2015-12-08_16-36-09PM.txt

——————————————————————————–
Local Machine Information::
Hostname: ora-linux.oragyan.com
ARU platform id: 226
ARU platform description:: Linux x86-64

Installed Top-level Products (1):

Oracle Database 11g 11.2.0.4.0
There are 1 products installed in this Oracle Home.
Interim patches (28) :

Patch 21555791 : applied on Tue Dec 08 16:17:01 IST 2015
Unique Patch ID: 19332276
Patch description: “ORACLE JAVAVM COMPONENT 11.2.0.4.5 DATABASE PSU (OCT2015)”
Created on 7 Oct 2015, 06:07:08 hrs PST8PDT
Bugs fixed:
19058059, 18933818, 19176885, 17201047, 19007266, 19554117, 14774730
17285560, 19153980, 18166577, 18458318, 19374518, 19006757, 17056813
21811517, 19909862, 19223010, 19895326, 20408829, 19852360, 17804361
21047766, 19231857, 17528315, 21566944

Patch 21538567 : applied on Mon Dec 07 23:51:17 IST 2015
Unique Patch ID: 19281142
Created on 14 Aug 2015, 11:06:40 hrs PST8PDT
Bugs fixed:
20441797, 21538567

Patch 21538558 : applied on Mon Dec 07 23:51:16 IST 2015
Unique Patch ID: 19281142
Created on 14 Aug 2015, 11:06:18 hrs PST8PDT
Bugs fixed:
21538558, 20925795

Patch 21538485 : applied on Mon Dec 07 23:51:07 IST 2015
Unique Patch ID: 19281142
Created on 28 Aug 2015, 12:15:47 hrs PST8PDT
Bugs fixed:
20448824, 21538485

Patch 21352646 : applied on Mon Dec 07 23:51:06 IST 2015
Unique Patch ID: 19281142
Created on 1 Sep 2015, 08:43:27 hrs PST8PDT
Bugs fixed:
20299015, 18681862, 19727057, 21352646, 20803583, 18139690, 17343514
19271443, 19854503, 17551709

Patch 21179898 : applied on Mon Dec 07 23:51:04 IST 2015
Unique Patch ID: 18970187
Created on 4 Jun 2015, 11:58:09 hrs PST8PDT
Bugs fixed:
21179898, 19699191

Patch 21051862 : applied on Mon Dec 07 23:50:56 IST 2015
Unique Patch ID: 18970187
Created on 22 May 2015, 10:38:06 hrs PST8PDT
Bugs fixed:
20004021, 20004087, 21051862

Patch 21051858 : applied on Mon Dec 07 23:50:39 IST 2015
Unique Patch ID: 18970187
Created on 22 May 2015, 10:37:23 hrs PST8PDT
Bugs fixed:
20334344, 21051858

Patch 21051852 : applied on Mon Dec 07 23:50:28 IST 2015
Unique Patch ID: 18970187
Created on 27 May 2015, 14:03:47 hrs PST8PDT
Bugs fixed:
20331945, 21051852

Patch 21051840 : applied on Mon Dec 07 23:50:27 IST 2015
Unique Patch ID: 18970187
Created on 22 May 2015, 10:36:14 hrs PST8PDT
Bugs fixed:
20558005, 21051840

Patch 21051833 : applied on Mon Dec 07 23:50:26 IST 2015
Unique Patch ID: 18970187
Created on 22 May 2015, 11:39:55 hrs PST8PDT
Bugs fixed:
21051833, 20657441

Patch 20631274 : applied on Mon Dec 07 23:50:24 IST 2015
Unique Patch ID: 18695736
Created on 13 Mar 2015, 10:25:35 hrs PST8PDT
Bugs fixed:
20296213, 17600719, 19458377, 19554106, 20631274, 16524926, 19466309

Patch 20506715 : applied on Mon Dec 07 23:50:10 IST 2015
Unique Patch ID: 18695736
Created on 13 Mar 2015, 10:23:10 hrs PST8PDT
Bugs fixed:
19049453, 19915271, 16450169, 17006570, 17811456, 18554871, 17786278
18096714, 18744139, 20506715, 18061914

Patch 20506699 : applied on Mon Dec 07 23:49:58 IST 2015
Unique Patch ID: 18695736
Created on 13 Mar 2015, 11:23:48 hrs PST8PDT
Bugs fixed:
20506699, 19358317, 19197175, 19972570, 17762296

Patch 20142975 : applied on Mon Dec 07 23:49:53 IST 2015
Unique Patch ID: 18406185
Created on 22 Dec 2014, 00:47:05 hrs PST8PDT
Bugs fixed:
19827973, 19463897, 19289642, 20142975

Patch 19972569 : applied on Mon Dec 07 23:49:52 IST 2015
Unique Patch ID: 18406185
Created on 22 Dec 2014, 00:21:09 hrs PST8PDT
Bugs fixed:
19972569, 19006849

Patch 19972568 : applied on Mon Dec 07 23:49:51 IST 2015
Unique Patch ID: 18406185
Created on 22 Dec 2014, 00:13:36 hrs PST8PDT
Bugs fixed:
19972568, 19309466

Patch 19972566 : applied on Mon Dec 07 23:49:49 IST 2015
Unique Patch ID: 18406185
Created on 22 Dec 2014, 00:03:20 hrs PST8PDT
Bugs fixed:
19972566, 18641461, 18641451

Patch 19972564 : applied on Mon Dec 07 23:31:09 IST 2015
Unique Patch ID: 18406185
Created on 21 Dec 2014, 23:54:58 hrs PST8PDT
Bugs fixed:
19972564, 18436647

Patch 19584068 : applied on Mon Dec 07 23:30:59 IST 2015
Unique Patch ID: 18084194
Created on 24 Sep 2014, 07:43:52 hrs PST8PDT
Bugs fixed:
18262334, 19584068

Patch 19544839 : applied on Mon Dec 07 23:30:58 IST 2015
Unique Patch ID: 18084194
Created on 11 Oct 2014, 01:43:34 hrs PST8PDT
Bugs fixed:
18673325, 19544839, 19211724, 18673304

Patch 19463893 : applied on Mon Dec 07 23:30:46 IST 2015
Unique Patch ID: 18084194
Created on 24 Sep 2014, 06:28:06 hrs PST8PDT
Bugs fixed:
19463893, 18641419

Patch 18203838 : applied on Mon Dec 07 23:30:38 IST 2015
Unique Patch ID: 17351342
Created on 26 Feb 2014, 23:57:58 hrs PST8PDT
Bugs fixed:
17865671, 18203838

Patch 18203837 : applied on Mon Dec 07 23:30:30 IST 2015
Unique Patch ID: 17351342
Created on 26 Feb 2014, 23:48:38 hrs PST8PDT
Bugs fixed:
17716305, 18203837

Patch 18203835 : applied on Mon Dec 07 23:30:16 IST 2015
Unique Patch ID: 17351342
Created on 26 Feb 2014, 23:43:04 hrs PST8PDT
Bugs fixed:
17721717, 18203835

Patch 17811447 : applied on Mon Dec 07 23:30:15 IST 2015
Unique Patch ID: 17066906
Created on 11 Dec 2013, 02:20:51 hrs PST8PDT
Bugs fixed:
17811447, 17088068

Patch 17811438 : applied on Mon Dec 07 23:30:13 IST 2015
Unique Patch ID: 17066906
Created on 11 Dec 2013, 02:37:11 hrs PST8PDT
Bugs fixed:
17811438, 16721594

Patch 17811429 : applied on Mon Dec 07 23:30:03 IST 2015
Unique Patch ID: 17066906
Created on 11 Dec 2013, 02:36:54 hrs PST8PDT
Bugs fixed:
17811429, 13944971

——————————————————————————–

OPatch succeeded.
[oracle@ora-linux 21555791]$

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

Step 4: Post Installation Steps:

The following steps load modified SQL files into the database.

Install the SQL portion of the patch by running the following command for a single instance environment.

cd /tmp/oracle_patches/Patch_p21555791/21555791
sqlplus /nolog
SQL> CONNECT / AS SYSDBA
SQL> startup upgrade
SQL> @postinstall.sql
SQL> shutdown
SQL> startup
After installing the SQL portion of the patch, some packages could become INVALID. This will get recompiled upon access or you can run utlrp.sql to get them back into a VALID state.

cd $ORACLE_HOME/rdbms/admin
sqlplus /nolog
SQL> CONNECT / AS SYSDBA
SQL> @utlrp.sql
That’s it, patch has been applied on the Oracle Home and Instance too, Post Installation step needs to be done on all instances.

Hope it helps…

Applying – Patch 21352646: DATABASE SECURITY PATCH UPDATE 11.2.0.4.0 (CPUOCT2015)

So we have already discussed in our previous posts how to download this patch along with OPatch utility to apply this patch.

In this post we are going to discuss how to apply this patch:

Step 1: Go to patch directory and take out README.html file to your desktop, to do that we can use WinSCP software.

1

Step 2: As this patch which we downloaded is for Oracle Database 11.2.0.4, so we have to apply this patch on 11.2.0.4 ORACLE_HOME.

Step 3: Just find out what are the instances currently running using ORACLE_HOME.

2

So only 1 Instance is running for now, DB11G, and this one using 11.2.0.4 ORACLE_HOME.

We can have multiple ORACLE_HOME in our environment, may be few will point to 10gR1, few 10gR2, few 11gR1 or may be 11gR2. We need to check ORACLE_HOME always where its is pointing to.

Step 4: Check the listener status too, it looks like, it is also started up from 11.2.0.4 binaries.

3

Step 5: Shutdown all instances and listener which is pointing to 11.2.0.4 ORACLE_HOME. If we will not do that, error will be thrown while applying the patches.

4

Just check once, whether there is anything missed.  Listener Status and Oracle Processes.

5

So we are all set to apply the Patch. We usually need to go through README.html file to perform every step.

Step 6: Open the README.html file and and perform prerequisites:

It looks like below when we open it:

6

Ideally we will start following – Point No : 3 Patch Installation Procedures for Oracle Database Release 11.2.0.4

Normally Point No 3.1.2 says about OPatch Utility and Other information about patch we are applying on RAC (3.1.1).

We can directly go to 3.2.1 which says – Patch Installation Instructions.

By default opatch utility is not being configured, we have two ways to use it. Either we call it from ORACLE_HOME directly or we configure the PATH Variable.

  1. $ORACLE_HOME/OPatch/opatch  <— First Way to use opatch utility
  2. export PATH=$ORACLE_HOME/OPatch:$PATH  <—Second way to call opatch utility.

We will check by both the ways:

[oracle@ora-linux ~]$ opatch version
-bash: opatch: command not found
[oracle@ora-linux ~]$

So opatch is not configured. No use both the ways one by one:

1st Way:

[oracle@ora-linux ~]$ $ORACLE_HOME/OPatch/opatch version
OPatch Version: 11.2.0.3.12

OPatch succeeded.
[oracle@ora-linux ~]$

2nd Way:

[oracle@ora-linux ~]$ export PATH=$ORACLE_HOME/OPatch:$PATH
[oracle@ora-linux ~]$
[oracle@ora-linux ~]$ opatch version
OPatch Version: 11.2.0.3.12

OPatch succeeded.
[oracle@ora-linux ~]$

Step 7: Apply the patch now finally – This step will have the patch applied on ORACLE_HOME:

Go to the patch directory:

[oracle@ora-linux ~]$ cd /tmp/oracle_patches/Patch_p21352646/21352646/
[oracle@ora-linux 21352646]$
[oracle@ora-linux 21352646]$ ls
17811429 17811447 18203837 19463893 19584068 19972566 19972569 20506699 20631274 21051840 21051858 21179898 21538485 21538567 README.html
17811438 18203835 18203838 19544839 19972564 19972568 20142975 20506715 21051833 21051852 21051862 21352646 21538558 patchmd.xml README.txt
[oracle@ora-linux 21352646]$

Check if there is any patch applied on this home before:

7

So there is nothing as of now in this ORACLE_HOME.

Now apply the patch:

[oracle@ora-linux ~]$ cd /tmp/oracle_patches/
OPatch_Utility_11.2.0.3.12/ Patch_p21352646/ Patch_p21555791/
[oracle@ora-linux ~]$ cd /tmp/oracle_patches/Patch_p21352646/21352646/
[oracle@ora-linux 21352646]$
[oracle@ora-linux 21352646]$ export PATH=$ORACLE_HOME/OPatch:$PATH
[oracle@ora-linux 21352646]$
[oracle@ora-linux 21352646]$ opatch napply -skip_subset -skip_duplicate
Oracle Interim Patch Installer version 11.2.0.3.12
Copyright (c) 2015, Oracle Corporation. All rights reserved.
Oracle Home : /u01/app/oracle/product/11.2.0.4/db_1
Central Inventory : /u01/app/oraInventory
from : /u01/app/oracle/product/11.2.0.4/db_1/oraInst.loc
OPatch version : 11.2.0.3.12
OUI version : 11.2.0.4.0
Log file location : /u01/app/oracle/product/11.2.0.4/db_1/cfgtoollogs/opatch/opatch2015-12-07_23-48-28PM_1.log

Verifying environment and performing prerequisite checks…

The following patches are duplicate and are skipped:
[ 17811429 17811438 17811447 18203835 18203837 18203838 19463893 19544839 19584068 19972564 ]

OPatch continues with these patches: 19972566 19972568 19972569 20142975 20506699 20506715 20631274 21051833 21051840 21051852 21051858 21051862 21179898 21352646 21538485 21538558 21538567

Do you want to proceed? [y|n]
y
User Responded with: Y
All checks passed.
Provide your email address to be informed of security issues, install and
initiate Oracle Configuration Manager. Easier for you if you use your My
Oracle Support Email address/User Name.
Visit http://www.oracle.com/support/policies.html for details.
Email address/User Name:

You have not provided an email address for notification of security issues.
Do you wish to remain uninformed of security issues ([Y]es, [N]o) [N]: y

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = ‘/u01/app/oracle/product/11.2.0.4/db_1’)
Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files…
Applying interim patch ‘19972566’ to OH ‘/u01/app/oracle/product/11.2.0.4/db_1’

Patching component oracle.rdbms.dbscripts, 11.2.0.4.0…

Patching component oracle.rdbms, 11.2.0.4.0…
Applying interim patch ‘19972568’ to OH ‘/u01/app/oracle/product/11.2.0.4/db_1’

Patching component oracle.oraolap, 11.2.0.4.0…

Patching component oracle.rdbms, 11.2.0.4.0…
Applying interim patch ‘19972569’ to OH ‘/u01/app/oracle/product/11.2.0.4/db_1’

Patching component oracle.ovm, 11.2.0.4.0…
Applying interim patch ‘20142975’ to OH ‘/u01/app/oracle/product/11.2.0.4/db_1’

Patching component oracle.rdbms.dbscripts, 11.2.0.4.0…
Applying interim patch ‘20506699’ to OH ‘/u01/app/oracle/product/11.2.0.4/db_1’

Patching component oracle.xdk.rsf, 11.2.0.4.0…

Patching component oracle.xdk.parser.java, 11.2.0.4.0…

Patching component oracle.rdbms.rsf, 11.2.0.4.0…

Patching component oracle.rdbms, 11.2.0.4.0…
Applying interim patch ‘20506715’ to OH ‘/u01/app/oracle/product/11.2.0.4/db_1’

Patching component oracle.rdbms.rsf, 11.2.0.4.0…

Patching component oracle.rdbms, 11.2.0.4.0…

Patching component oracle.rdbms.dbscripts, 11.2.0.4.0…
Applying interim patch ‘20631274’ to OH ‘/u01/app/oracle/product/11.2.0.4/db_1’

Patching component oracle.rdbms, 11.2.0.4.0…
Applying interim patch ‘21051833’ to OH ‘/u01/app/oracle/product/11.2.0.4/db_1’

Patching component oracle.rdbms, 11.2.0.4.0…
Applying interim patch ‘21051840’ to OH ‘/u01/app/oracle/product/11.2.0.4/db_1’

Patching component oracle.rdbms, 11.2.0.4.0…
Applying interim patch ‘21051852’ to OH ‘/u01/app/oracle/product/11.2.0.4/db_1’

Patching component oracle.rdbms, 11.2.0.4.0…

Patching component oracle.rdbms.dbscripts, 11.2.0.4.0…
Applying interim patch ‘21051858’ to OH ‘/u01/app/oracle/product/11.2.0.4/db_1’

Patching component oracle.rdbms, 11.2.0.4.0…
Applying interim patch ‘21051862’ to OH ‘/u01/app/oracle/product/11.2.0.4/db_1’

Patching component oracle.sysman.console.db, 11.2.0.4.0…
Applying interim patch ‘21179898’ to OH ‘/u01/app/oracle/product/11.2.0.4/db_1’

Patching component oracle.rdbms, 11.2.0.4.0…
Applying interim patch ‘21352646’ to OH ‘/u01/app/oracle/product/11.2.0.4/db_1’

Patching component oracle.rdbms.rsf, 11.2.0.4.0…

Patching component oracle.rdbms, 11.2.0.4.0…
Applying interim patch ‘21538485’ to OH ‘/u01/app/oracle/product/11.2.0.4/db_1’

Patching component oracle.sysman.agent, 10.2.0.4.5…
Applying interim patch ‘21538558’ to OH ‘/u01/app/oracle/product/11.2.0.4/db_1’

Patching component oracle.rdbms, 11.2.0.4.0…
Applying interim patch ‘21538567’ to OH ‘/u01/app/oracle/product/11.2.0.4/db_1’

Patching component oracle.rdbms.rsf, 11.2.0.4.0…

OPatch found the word “warning” in the stderr of the make command.
Please look at this stderr. You can re-run this make command.
Stderr output:
ins_emagent.mk:113: warning: overriding commands for target `nmosudo’
ins_emagent.mk:52: warning: ignoring old commands for target `nmosudo’
/u01/app/oracle/product/11.2.0.4/db_1/sysman/lib/ins_emagent.mk:113: warning: overriding commands for target `nmosudo’
/u01/app/oracle/product/11.2.0.4/db_1/sysman/lib/ins_emagent.mk:52: warning: ignoring old commands for target `nmosudo’
Patches 19972566,19972568,19972569,20142975,20506699,20506715,20631274,21051833,21051840,21051852,21051858,21051862,21179898,21352646,21538485,21538558,21538567 successfully applied.
OPatch Session completed with warnings.
Log file location: /u01/app/oracle/product/11.2.0.4/db_1/cfgtoollogs/opatch/opatch2015-12-07_23-48-28PM_1.log

OPatch completed with warnings.
[oracle@ora-linux 21352646]$

The warning which we got above, can be safely ignored.

Step 8: Post Installation Steps

As per Step 3.2.2.1 from README.html file, we have to load 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:

cd $ORACLE_HOME/rdbms/admin
sqlplus /nolog
SQL> CONNECT / AS SYSDBA
SQL> STARTUP
SQL> @catbundle.sql cpu apply
SQL> QUIT

To compile all objects in the database:

cd $ORACLE_HOME/rdbms/admin
sqlplus /nolog
SQL> CONNECT / AS SYSDBA
SQL> @utlrp.sql

We are done with the patch.

Hope its helps..

How to download latest CPU/PSU Patches

As we are aware of that Oracle releases patches every quarter ( Jan, April, July and Oct ), however many does not know how to get these patches and apply them on our Database or GRID etc.

So this post is to help those guys, now go to http://www.google.com and simple search “cpu patch Oct 2015“, why this because i know this will give me the latest one. If anyone is not interested in latest once, they can also search – “cpu patch july 2015” or “cpu patch april 2015” whatever quarter you feel so.

So click on first link:

1

It will go to the below page, where we have to select yellowed one:

23

Once you click on that it will lead to MOS Username?password Screen, where we have to put our username and password:

4

Once you Sign IN, below page will come up which will have information about Patches.

5

Click on yellowed One to go to specific area which is Oracle Database in our case:

6

It will point to – Oracle Database.

7

Once you click on that, it will go to specific section where we can choose Oracle Database 11.2.0.4.

8

Once we click on that, it goes to the Patch Section where we can choose the Patches – CPU/PSU/SPU for our DATABASE or GRID.

9

So we will download the two yellowed patches for now…

10

Click on First one, below page will be displayed, select the specific OS for which you will be needing this Patch and click Download.

11

Once you click on Download, below page will be displayed, once you click on the yellow portion, it will ask you to save, save it at your desktop or any location on your machine.

12

After 1st is done, we will do the same thing and download the next patch.

1314

One both the patches are downloaded, place it them on the server in /tmp directory.

Go to /tmp directory and create a directory oracle_patches, transfer both the patches there. Here we go :

15

Now create two separate directory for each patch and move each patch in its own specific directory. Here you go:

16

Now go to each directory and unzip both the patches in their own directory.

——————————————————–

First Patch Unzipped:

[oracle@ora-linux ~]$ cd /tmp/oracle_patches/
[oracle@ora-linux oracle_patches]$ ls -lrt
total 12
drwxr-xr-x. 2 oracle oinstall 4096 Dec 7 15:11 Patch_p21352646
drwxr-xr-x. 2 oracle oinstall 4096 Dec 7 15:12 Patch_p21555791
drwxr-xr-x. 2 oracle oinstall 4096 Dec 7 15:49 OPatch_Utility_11.2.0.3.12
[oracle@ora-linux oracle_patches]$
[oracle@ora-linux oracle_patches]$
[oracle@ora-linux oracle_patches]$ cd Patch_p21352646/
[oracle@ora-linux Patch_p21352646]$
[oracle@ora-linux Patch_p21352646]$ pwd
/tmp/oracle_patches/Patch_p21352646
[oracle@ora-linux Patch_p21352646]$
[oracle@ora-linux Patch_p21352646]$ ls -lrt
total 12292
-rw-r–r–. 1 oracle oinstall 12586668 Dec 7 14:48 p21352646_112040_Linux-x86-64.zip
[oracle@ora-linux Patch_p21352646]$

[oracle@ora-linux Patch_p21352646]$
[oracle@ora-linux Patch_p21352646]$ unzip p21352646_112040_Linux-x86-64.zip
Archive: p21352646_112040_Linux-x86-64.zip
creating: 21352646/
creating: 21352646/21538567/
creating: 21352646/21538567/etc/
creating: 21352646/21538567/etc/config/
inflating: 21352646/21538567/etc/config/deploy.xml
inflating: 21352646/21538567/etc/config/inventory.xml
inflating: 21352646/21538567/etc/config/actions.xml
.

.  <—Output Trimmed

.

creating: 21352646/17811438/files/
creating: 21352646/17811438/files/lib/
creating: 21352646/17811438/files/lib/libserver11.a/
inflating: 21352646/17811438/files/lib/libserver11.a/kqlf.o
inflating: 21352646/17811438/files/lib/libserver11.a/kkss.o
inflating: PatchSearch.xml
[oracle@ora-linux Patch_p21352646]$
[oracle@ora-linux Patch_p21352646]$ ls -lrt
total 12312
drwxr-xr-x. 29 oracle oinstall 4096 Oct 5 18:32 21352646
-rw-rw-r–. 1 oracle oinstall 13975 Oct 24 09:24 PatchSearch.xml
-rw-r–r–. 1 oracle oinstall 12586668 Dec 7 14:48 p21352646_112040_Linux-x86-64.zip
[oracle@ora-linux Patch_p21352646]$

So now we have got 21352646 directory after unzipping the patch software.

——————————————————–

2nd Patch Unzipped:

[oracle@ora-linux oracle_patches]$ pwd
/tmp/oracle_patches
[oracle@ora-linux oracle_patches]$ ls -lrt
total 12
drwxr-xr-x. 2 oracle oinstall 4096 Dec 7 15:12 Patch_p21555791
drwxr-xr-x. 2 oracle oinstall 4096 Dec 7 15:49 OPatch_Utility_11.2.0.3.12
drwxr-xr-x. 3 oracle oinstall 4096 Dec 7 17:15 Patch_p21352646
[oracle@ora-linux oracle_patches]$
[oracle@ora-linux oracle_patches]$ cd Patch_p21555791/
[oracle@ora-linux Patch_p21555791]$
[oracle@ora-linux Patch_p21555791]$ ls -lrt
total 40716
-rw-r–r–. 1 oracle oinstall 41692702 Dec 7 14:51 p21555791_112040_Linux-x86-64.zip
[oracle@ora-linux Patch_p21555791]$
[oracle@ora-linux Patch_p21555791]$ unzip p21555791_112040_Linux-x86-64.zip
Archive: p21555791_112040_Linux-x86-64.zip
creating: 21555791/
inflating: 21555791/README.html
creating: 21555791/etc/
creating: 21555791/etc/config/
inflating: 21555791/etc/config/deploy.xml
inflating: 21555791/etc/config/inventory.xml
inflating: 21555791/etc/config/actions.xml
creating: 21555791/etc/xml/
inflating: 21555791/etc/xml/GenericActions.xml
inflating: 21555791/etc/xml/ShiphomeDirectoryStructure.xml
.

. <==Output Trimmed

.
creating: 21555791/files/jdbc/lib/ojdbc5.jar/oracle/jdbc/driver/
inflating: 21555791/files/jdbc/lib/ojdbc5.jar/oracle/jdbc/driver/OracleSql$1.class
inflating: 21555791/files/jdbc/lib/ojdbc5.jar/oracle/jdbc/driver/OracleSql.class
inflating: 21555791/files/jdbc/lib/ojdbc5.jar/oracle/jdbc/driver/OracleSql$ParseMode.class
inflating: 21555791/patchmd.xml
inflating: PatchSearch.xml
[oracle@ora-linux Patch_p21555791]$

[oracle@ora-linux Patch_p21555791]$ ls -lrt
total 40728
drwxr-xr-x. 5 oracle oinstall 4096 Oct 8 15:27 21555791
-rw-rw-r–. 1 oracle oinstall 5823 Oct 20 19:09 PatchSearch.xml
-rw-r–r–. 1 oracle oinstall 41692702 Dec 7 14:51 p21555791_112040_Linux-x86-64.zip
[oracle@ora-linux Patch_p21555791]$

So now we have got – 21555791, in our next post we will be applying these 2 Patches one by one.

——————————————————–

Thanks for watching.

Hope it helps..

 

How to Download and Configure Latest OPatch Utility to apply CPU/PSU Patches

This post is to describe how to get latest opatch utility to apply patches on Oracle Database or GRID Home.

Why do we need latest one – Oracle Recommends to use that latest one so that there will not be any backward compatibility issue with OPatch and CPU/PSU patches (however i never got that).

Make sure you have credentials to get software from MOS, Edelivery websites which has all related oracle software.

Now to get the latest OPatch Utility, go to below link:

https://updates.oracle.com/download/6880880.html

3

It will go to below page where it will ask you to provide MOS Username/Password.

4

Once you Sign In you will get the below image, select appropriate version with Release and Click on Download, save the software somewhere at your desktop.

5

Once its downloaded, we have to keep this software on our Linux Server.

Go to /tmp/oracle_patches. If this directory does not exist create it. Go to /tmp and – mkdir oracle_patches.

Once you are in /tmp/oracle_patches, create a different directory to keep OPatch utility software. Using WInSCP or FileZilla utility, transfer the software in the directory newly created:

6

Unzip it and you will get the OPatch Directory Created.

————————————————————

[oracle@ora-linux OPatch_Utility_11.2.0.3.12]$ unzip p6880880_112000_Linux-x86-64.zip

[oracle@ora-linux OPatch_Utility_11.2.0.3.12]$ ls -lrt
total 51504
drwxr-x—. 10 oracle oinstall 4096 Aug 18 14:33 OPatch
-rw-r–r–. 1 oracle oinstall 52733900 Dec 7 15:16 p6880880_112000_Linux-x86-64.zip
[oracle@ora-linux OPatch_Utility_11.2.0.3.12]$

————————————————————

7

Now we will replaces our old OPatch Utility with the new one.

Before that we will check the version of each utility.

Go to $ORACLE_HOME/OPatch  (This ORACLE_HOME contains software required for Oracle Database.) and in the new OPatch directory to find out the version of each utility:

8

So what we have in ORACLE_HOME is – 11.2.0.3.4 and what we have in newly downloaded one is – 11.2.0.3.12.

So now we will take backup of what we have in $ORACLE_HOME and replace it with the newer version.

Go to $ORACLE_HOME, rename the current one and replace it with new one:

Here we are moving the current one to some other name, we can also choose to remove it, there is no harm. rm -r OPatch can also be used instead of moving it, because we are not going to use it anyways.

So after we moved it to some other name, we don’t have OPatch Utility anymore.

9

Now we will go to oracle_patches directory and will move the new one to $ORACLE_HOME. Make sure your ORACLE_HOME is correctly configured:

10

Now go to ORACLE_HOME again and in OPatch directory and try opatch version, it will give you the new one now:

11

By this way we are done with the OPatch utility Download and replacing with current one.

Let me know if any questions.

Hope it helps…

 

How to Backup and Restore Database which is being Configured in ASM

Here in this Post – i have installed and configured – 11gR2 GRID and Database Software (11.2.0.3) and have running TESTDB1 instance.

We have 1 Database TESTDB1 which is there in ASM, we will find out the status and information about – Data Files, Control Files and Redo Log Files before we start our process of backup and restore.

It shows Datafile and Redo Log files location (see we have redo log files in DATA and LOG both disk groups for redundancy):

4

Check for Control File location too and Database Name ( We have 2 Control Files, 1 is in DATA and 1 is in LOG):

5

Check the Database Size, just the sake of it:

6

As we are planning to take Cold Backup, so we will shutdown if its up and start in mount state.

This command will shutdown the database (srvctl stop database -d testdb1). Check the status of the Grid and Database ( crs_stat -t -v ). Database is down now.

1

We will start the database in mount state ( srvctl start database -d testdb1 -o mount ), because we have to take the backup from RMAN.

Check the status again (crs_stat -t -v):

2

Just to verify, connect from SQLPLUS and find out the status of database, it shows MOUNTED:

3

Now connect to ORACLE User and initiate the Backup using RMAN, make sure ORACLE_SID points to TESTDB1.

So we don’t have any prior backup (list backup; and list archivelog all; shows that blank).

Now make sure we have this directory created – /home/oracle/backup.

Now take the backup in the directory /home/oracle/backup using backup as compressed backupset database format ‘/home/oracle/backup/%d_%Y%M%D_%s_%p’;

7

So now we have our backup ready at – /home/oracle/backup:

7

Now remove files from ASM Disk Groups – DATA and LOG:

Now connect to ASM using GRID user :

[grid@ora-linux ~]$ . oraenv
ORACLE_SID = [+ASM] ?
The Oracle base for ORACLE_HOME=/u01/app/11.2.0/grid_11.2.0.3 is /u01/app/grid
[grid@ora-linux ~]$

Remove Datafiles from +DATA DiskGroup, because all datafiles belong to +DATA, we have to remove it and restore it back using backups which we took:

[grid@ora-linux ~]$ asmcmd
ASMCMD>
ASMCMD> ls
CRS/
DATA/
LOG/
ASMCMD>
ASMCMD> cd data
ASMCMD> ls
TESTDB1/
ASMCMD>
ASMCMD> cd testdb1
ASMCMD> ls
CONTROLFILE/
DATAFILE/
ONLINELOG/
PARAMETERFILE/
TEMPFILE/
spfileTESTDB1.ora
ASMCMD>
ASMCMD> cd datafile
ASMCMD> ls
SYSAUX.257.897332263
SYSTEM.256.897332263
UNDOTBS1.258.897332263
USERS.259.897332263
ASMCMD>
ASMCMD> rm *
You may delete multiple files and/or directories.
Are you sure? (y/n) y
ASMCMD> exit
[grid@ora-linux ~]$

Datafiles removed now from +DATA DiskGroup.

Remove control file from +DATA DiskGroup:

[grid@ora-linux ~]$ asmcmd -p
ASMCMD [+] >
ASMCMD [+] > ls
CRS/
DATA/
LOG/
ASMCMD [+] > cd data
ASMCMD [+data] > ls
TESTDB1/
ASMCMD [+data] > cd testdb1
ASMCMD [+data/testdb1] >
ASMCMD [+data/testdb1] > ls
CONTROLFILE/
ONLINELOG/
PARAMETERFILE/
TEMPFILE/
spfileTESTDB1.ora
ASMCMD [+data/testdb1] >
ASMCMD [+data/testdb1] > cd CONTROLFILE/
ASMCMD [+data/testdb1/CONTROLFILE] > ls
Current.260.897332375
ASMCMD [+data/testdb1/CONTROLFILE] >
ASMCMD [+data/testdb1/CONTROLFILE] > rm *
You may delete multiple files and/or directories.
Are you sure? (y/n) y
ASMCMD [+data/testdb1/CONTROLFILE] >
ASMCMD [+data/testdb1/CONTROLFILE] > exit
[grid@ora-linux ~]$

Control files has been removed from +DATA.

Remove Parameter File from +DATA DiskGroup:

[grid@ora-linux ~]$ asmcmd -p
ASMCMD [+] >
ASMCMD [+] > ls
CRS/
DATA/
LOG/
ASMCMD [+] > cd data
ASMCMD [+data] > ls
TESTDB1/
ASMCMD [+data] > cd testdb1
ASMCMD [+data/testdb1] > ls
ONLINELOG/
PARAMETERFILE/
TEMPFILE/
spfileTESTDB1.ora
ASMCMD [+data/testdb1] > cd PARAMETERFILE/
ASMCMD [+data/testdb1/PARAMETERFILE] > ls
spfile.265.897332583
ASMCMD [+data/testdb1/PARAMETERFILE] >
ASMCMD [+data/testdb1/PARAMETERFILE] > rm *
You may delete multiple files and/or directories.
Are you sure? (y/n) y
ASMCMD [+data/testdb1/PARAMETERFILE] > exit
[grid@ora-linux ~]$

Parameter file has been removed from +DATA DiskGroup.

Remove Redo Log Files from +DATA Disk Group:

[grid@ora-linux ~]$ asmcmd -p
ASMCMD [+] > cd +data/tetsdb1
ASMCMD [+data/testdb1] > cd onlinelog
ASMCMD [+data/testdb1/onlinelog] > ls
group_1.261.897332379
group_2.262.897332383
group_3.263.897332385
ASMCMD [+data/testdb1/onlinelog] >
ASMCMD [+data/testdb1/onlinelog] >
ASMCMD [+data/testdb1/onlinelog] > rm *
You may delete multiple files and/or directories.
Are you sure? (y/n) y
ASMCMD [+data/testdb1/onlinelog] >
ASMCMD [+data/testdb1/onlinelog] > exit
[grid@ora-linux ~]$

Redo Log file has been removed.

Remove temp file from +DATA DiskGroup:

[grid@ora-linux ~]$ asmcmd -p
ASMCMD [+] >
ASMCMD [+] > cd +DATA/testdb1/tempfile
ASMCMD [+DATA/testdb1/tempfile] >
ASMCMD [+DATA/testdb1/tempfile] > rm *
You may delete multiple files and/or directories.
Are you sure? (y/n) y
ASMCMD [+DATA/testdb1/tempfile] > cd +DATA
ASMCMD [+DATA] > ls
ASMCMD [+DATA] >
ASMCMD [+DATA] > exit
[grid@ora-linux ~]$

Temp file has been removed form +DATA.

Remove Control Files and Redo Log Files from +LOG DiskGroup too ( Why do we have in +LOG – Because Oracle Creates redundant copy in different Disk Groups – so in our case it went to +DATA and +LOG):

[grid@ora-linux ~]$ asmcmd -p
ASMCMD [+] >
ASMCMD [+] > cd +LOG
ASMCMD [+LOG] > ls
TESTDB1/
ASMCMD [+LOG] >
ASMCMD [+LOG] >
ASMCMD [+LOG] > cd testdb1
ASMCMD [+LOG/testdb1] > ls
CONTROLFILE/
ONLINELOG/
ASMCMD [+LOG/testdb1] > cd controlfile
ASMCMD [+LOG/testdb1/controlfile] > ls
Current.260.897332375
ASMCMD [+LOG/testdb1/controlfile] >
ASMCMD [+LOG/testdb1/controlfile] > rm *
You may delete multiple files and/or directories.
Are you sure? (y/n) y
ASMCMD [+LOG/testdb1/controlfile] >
ASMCMD [+LOG/testdb1/controlfile] > cd +LOG/testdb1
ASMCMD [+LOG/testdb1] > ls
ONLINELOG/
ASMCMD [+LOG/testdb1] > cd ONLINELOG/
ASMCMD [+LOG/testdb1/ONLINELOG] > ls
group_1.261.897332381
group_2.269.897332385
group_3.268.897332387
ASMCMD [+LOG/testdb1/ONLINELOG] > rm *
You may delete multiple files and/or directories.
Are you sure? (y/n) y
ASMCMD [+LOG/testdb1/ONLINELOG] >
ASMCMD [+LOG/testdb1/ONLINELOG] > cd +LOG
ASMCMD [+LOG] > ls
ASMCMD [+LOG] >
ASMCMD [+LOG] > cd +DATA
ASMCMD [+DATA] > ls
ASMCMD [+DATA] >
ASMCMD [+DATA] > exit
[grid@ora-linux ~]$

Control Files and Redo Log Files has been removed from +LOG.

Now we have removed everything from ASM Disk Groups, means there is nothing in our Disk Groups which can make our database up. Let just try starting up the database:

8

So its failed, it did not even get the SPFILE to start up the database.

Now we will restore our Database using RMAN from Backup:

Step 1: We have to start our instance using (startup force nomount) – Why, because we don’t have either pfile or spfile to start our database, so this command will just start the database for the sake of restoring the spfile.

[oracle@ora-linux ~]$ rman target /

Recovery Manager: Release 11.2.0.3.0 – Production on Thu Dec 3 12:38:20 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

connected to target database (not started)

RMAN> startup force nomount;

startup failed: ORA-01078: failure in processing system parameters
ORA-01565: error in identifying file ‘+DATA/TESTDB1/spfileTESTDB1.ora’
ORA-17503: ksfdopn:2 Failed to open file +DATA/TESTDB1/spfileTESTDB1.ora
ORA-15056: additional error message
ORA-17503: ksfdopn:2 Failed to open file +DATA/testdb1/spfiletestdb1.ora
ORA-15173: entry ‘testdb1’ does not exist in directory ‘/’
ORA-06512: at line 4

starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started

Total System Global Area 158662656 bytes

Fixed Size 2226456 bytes
Variable Size 92276456 bytes
Database Buffers 58720256 bytes
Redo Buffers 5439488 bytes

RMAN>

Now restore the spfile:

RMAN> restore spfile from ‘/home/oracle/backup/TESTDB1_20151203_2_1’;

Starting restore at 03-DEC-2015 12:42:59
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK

channel ORA_DISK_1: restoring spfile from AUTOBACKUP /home/oracle/backup/TESTDB1_20151203_2_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 12/03/2015 12:43:00
ORA-19870: error while restoring backup piece /home/oracle/backup/TESTDB1_20151203_2_1
ORA-32015: unable to restore SPFILE
ORA-17502: ksfdcre:4 Failed to create file +DATA/testdb1/spfiletestdb1.ora
ORA-15056: additional error message
ORA-17502: ksfdcre:4 Failed to create file +DATA/testdb1/spfiletestdb1.ora
ORA-15173: entry ‘testdb1’ does not exist in directory ‘/’

RMAN>

It says, testdb1 does not exist in +DATA Disk Group. So now we have to create it before we restore it back.

[grid@ora-linux ~]$ asmcmd -p
ASMCMD [+] >
ASMCMD [+] > ls -lt
State Type Rebal Name
MOUNTED EXTERN N CRS/
MOUNTED EXTERN N DATA/
MOUNTED EXTERN N LOG/
ASMCMD [+] > cd data
ASMCMD [+data] >
ASMCMD [+data] > ls -lt
ASMCMD [+data] >
ASMCMD [+data] > mkdir testdb1
ASMCMD [+data] >
ASMCMD [+data] > ls -lt
Type Redund Striped Time Sys Name
N testdb1/
ASMCMD [+data] > exit
[grid@ora-linux ~]$

So now we have testdb1 directory created under TESTDB1. Try again restoring spfile from the previous command:

RMAN> restore spfile from ‘/home/oracle/backup/TESTDB1_20151203_2_1’;

Starting restore at 03-DEC-2015 12:44:42
using channel ORA_DISK_1

channel ORA_DISK_1: restoring spfile from AUTOBACKUP /home/oracle/backup/TESTDB1_20151203_2_1
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 03-DEC-2015 12:44:43

RMAN>

Spfile has been restored, now start the database in nomount state:

RMAN> startup force nomount;

Oracle instance started

Total System Global Area 2505338880 bytes

Fixed Size 2230952 bytes
Variable Size 553649496 bytes
Database Buffers 1946157056 bytes
Redo Buffers 3301376 bytes

RMAN>

Now we have our database started from SPFILE, we can restore our Control File Now:

RMAN> RESTORE CONTROLFILE FROM ‘/home/oracle/backup/TESTDB1_20151203_2_1’;

Starting restore at 03-DEC-2015 12:47:55
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=22 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
output file name=+DATA/testdb1/controlfile/current.263.897482877
output file name=+LOG/testdb1/controlfile/current.268.897482877
Finished restore at 03-DEC-2015 12:47:59

RMAN>

So our control file has been restored in +LOG and +DATA, locations are being picked up from SPFILE.

Now we have our control files restored, we have to start the database in mount state:

RMAN> startup force mount;

Oracle instance started
database mounted

Total System Global Area 2505338880 bytes

Fixed Size 2230952 bytes
Variable Size 553649496 bytes
Database Buffers 1946157056 bytes
Redo Buffers 3301376 bytes

RMAN>

Now we have our database being mounted, we can restore our database from the backup (There is a catch, we will not be mentioning from where we have to restore because location is already there in control file, so restore database will be sufficient instead of restore database from ‘/home/oracle/backup/TESTDB1_20151203_1_1’):

RMAN> RESTORE DATABASE;

Starting restore at 03-DEC-2015 12:48:46
Starting implicit crosscheck backup at 03-DEC-2015 12:48:46
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=22 device type=DISK
Crosschecked 1 objects
Finished implicit crosscheck backup at 03-DEC-2015 12:48:47

Starting implicit crosscheck copy at 03-DEC-2015 12:48:47
using channel ORA_DISK_1
Finished implicit crosscheck copy at 03-DEC-2015 12:48:47

searching for all files in the recovery area
cataloging files…
no files cataloged

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to +DATA/testdb1/datafile/system.256.897332263
channel ORA_DISK_1: restoring datafile 00002 to +DATA/testdb1/datafile/sysaux.257.897332263
channel ORA_DISK_1: restoring datafile 00003 to +DATA/testdb1/datafile/undotbs1.258.897332263
channel ORA_DISK_1: restoring datafile 00004 to +DATA/testdb1/datafile/users.259.897332263
channel ORA_DISK_1: reading from backup piece /home/oracle/backup/TESTDB1_20151203_1_1
channel ORA_DISK_1: piece handle=/home/oracle/backup/TESTDB1_20151203_1_1 tag=TAG20151203T120046
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:55
Finished restore at 03-DEC-2015 12:49:42

RMAN>

Now start the database with open resetlogs to start the logs from scratch, previous redo log files are of no use, anyways we have removed them already from ASM.

RMAN> ALTER DATABASE OPEN RESETLOGS;

database opened

RMAN>

So database is open now. Check the status and Data Files, Control Files and Redo Log files location etc..

[oracle@ora-linux backup]$ . oraenv
ORACLE_SID = [TESTDB1] ?
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0.3/db_1 is /u01/app/oracle
[oracle@ora-linux backup]$
[oracle@ora-linux backup]$
[oracle@ora-linux backup]$
[oracle@ora-linux backup]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Dec 3 16:24:19 2015

Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL>
SQL> select file_name from dba_data_files;

FILE_NAME
————————————————————-
+DATA/testdb1/datafile/users.260.897482929
+DATA/testdb1/datafile/undotbs1.265.897482927
+DATA/testdb1/datafile/sysaux.261.897482927
+DATA/testdb1/datafile/system.262.897482927

SQL>

SQL> select name from v$tempfile;

NAME
————————————————————-
+DATA/testdb1/tempfile/temp.256.897483027

SQL>

SQL> select file_name from dba_temp_files;

FILE_NAME
————————————————————-
+DATA/testdb1/tempfile/temp.256.897483027

SQL>

SQL> select member from v$logfile;

MEMBER
————————————————————-
+DATA/testdb1/onlinelog/group_3.257.897483023
+LOG/testdb1/onlinelog/group_3.260.897483023
+DATA/testdb1/onlinelog/group_2.258.897483019
+LOG/testdb1/onlinelog/group_2.261.897483021
+DATA/testdb1/onlinelog/group_1.259.897483017
+LOG/testdb1/onlinelog/group_1.269.897483017

6 rows selected.

SQL>

SQL> show parameter spfile;

NAME TYPE VALUE
——————- ———– ——————————
spfile                                    string               +DATA/testdb1/spfiletestdb1.ora
SQL>

So we are done with backup and restore of Database being configured in ASM.

Let me know if there is any question.

Hope it helps..

 

Oracle Database 11.2.0.3 Installation on ASM – Part 7

In this post we will be discussing over Database Installation on ASM (which is discussed in my previous posts – follow my old posts for ASM Disk Groups and other related information).

Note: Before we start installing, we must complete all our perquisites for sure.

Go to the the location where oracle database software has been download and extracted. Execute runInstaller from the same folder:

1234

For ASM Configuration, it is always recommended that you only install the software first and create the database later.

5

As this is for Single Database Installation, we will select the option accordingly.

67

We will go for Enterprise Edition to get the full fledged features with free license.

8

Before we go ahead and select the directories, make sure these directories really exist physically.

9

Select the OS Groups for the required Roles.

10

As this one is just Test Machine, we can ignore SWAP Memory check for now.

11

Press Yes for the confirmation.

12

Verify the detail below and Click on Install.

1314

Execute the script from root user.

15

Make sure, whenever you execute root.sh it will ask you to replace the file from /usr/local/bin directory if you have Oracle Installed on your machine previously. So if there are no custom changes made by you or your team in oraenv, coraenv or dnhome then you can replace it with the new on or else type “n” to ignore it.

16

Installation is successful click Close.

17

Creating the database:

Now we have the Software Installed, we will be creating a database using dbca:

Press Next…

18

Select the required option and Press Next…

19

Select the required Template and Press Next…

20

Select the name for your database, you can keep Global Database Name and SID same, however i always recommend to keep Global Name with Domain and SIS as it is. Press Next…

21

For now we will not be using EM in our environment, so i just unchecked the option. As this blog whole purpose to create database using ASM. Press Next..

22

Configure the password, as this is just a Test Environment, we can keep the same password.

23

Here we go, this is the location where we actually have to select, ASM Location using ASM from Storage Type. Select the required one from the list, we created DATA for datafiles so we will be using the same.

24

So we selected DATA, for ASMs all file system starts from “+”

25

Below we are selecting LOG to keep Archive Log and Backup Pieces.

26

Press Next after you verify everything has been setup correctly:

2728

Select the SGA and PGA Size, it is recommended that we always check the “Use Automatic Memory Management” to handle SGA and PGA memory share automatically by Oracle.

29

Normally i prefer to use AL32UTF8 character sets for my database because it supports global language as well. Press Next..

30

Press Next…

31

So finally we are proceeding with – Creating a Database. Click Finish…

3233

34

Verify everything and Press OK.

35

So database installation has been started..

36

Once its done, you will get the below with basic detail of your database like SPFILE Location of our database. Click EXIT.

37

UI will be closed once we click on EXIT button. Now we can verify our database status from back end and front end too.

38

Check out the database files location. If you see below its +DATA.

39

Fast Recovery Area is +LOG.

40

That’s all Guys.

Hope it helps…