Category Archives: OPatch

Oracle 12c – Apply and review patches

In Oracle 12c, Patching is performed to correct discovered bugs and close security vulnerabilities.   Oracle provides four types of Patches:

Interim Patches – single/collection of bug fixes or one-offs.

Diagnostic Patches – Diagnose or verify a fix.

Patch Set Updates (PSU) – Collection of high impact, low risk, proven fixes for products and components

Critical Patch Updates (CPU) – Collection of patches for security vulnerabilities.

Patching of the RDBMS can be done with two methods: Manually or EM Cloud Control Patch Wizard.   Additionally, the patching processes have added:  Online Patching and Queryable Inventories.   This article will give a brief overview of these Features.

 

Manual Patching

Step in manually patching are:

  1. Logon to oracle support https://support.oracle.com
  2. Click Patches & Updates tab.
  3. Enter the patch number you are looking for on Patches % Updates page.
  4. Click download on patch for your OS
  5. Click download Patch Metadata.
  6. Review the documentation for specific installation instructions.
  7. Use the OPatch utility to install all patches.

 

 

EM Cloud Control Patch Wizard

The EM Cloud Control Patch Wizard has the following benefits over Manual Patching:

  1. Integrate Patch work with Oracle support and provides single view to determine patch needs.
  2. End to End workflow of patching, resolving patch conflicts.
  3. Easy determination of Patch needs for single system.
  4. Patch plans can be saved as templates.
  5. Out of place patching is possible for support of RAC and GRID infrastructure.
  6. EM Cloud allows you create a patch plan to apply patches to one or more targets. A patch can be added to plan only if it’s for same release and OS.

 

EM Cloud can apply patches with two methods:

  1. Online Mode – EM connects to Oracle Support, searches for necessary patches, resolves any conflicts and adds them to patch plan.
  2. Offline Mode – Used when EM cannot connect to Oracle Support. EM searches for patches manually uploaded to software library and adds them to patch plan.

 

 

 

 

Online Patches

Before 11g all patches require relink of RDBMS libraries and a shutdown of system.  Now a large number of Patches can be done online, they contain .so files and do not require relink of RDBMS.

Benefit of Online Patches:

  1. No downtime.
  2. persistent across shutdown
  3. Allow rolling patches in RAC
  4. Fast install.

Downside of Online Patches:

  1. Require more memory
  2. Online patches not available for all OS
  3. Not all patches are hot patches.

 

 

 

Queryable Patch Inventory

Queryable Patch inventory is new 12c, implemented with DBMS_QOPATCH package and provides view of patches installed just as OPATCH lsinventory command.    DBMS_QOPATCH queries the OUI Oracle Universal Installer inventory to gather this metadata.

It provides the following:

  1. Query patches installed from SQL prompt.
  2. Creates reports and provides validation across many installs.
  3. Checks patches on RAC from single node.

 

DBMS_QOPATCH components:

GET_OPTACH_BUGS – Provides list of bug fixes per patch number.

GET_OPATCH_COUNT – Provides total number of patches installed.

GET_OPATCH_DATA – Provides top level patch info – number, creation date.

GET_OPATCH_FILES – Provides list of files updated by patch number.

GET_OPATCH_INSTALL_INFO – Provides info such as ORACLE_HOME, patch, inventory_location

GET_OPATCH_LIST – List of Patches installed.

GET_OPATCH_LSINVENTORY – Whole patch inventory

GET_OPATCH_OLAYS –Overlay for specific patch

GET_OPATCH_PREQS – Provides prerequisites for a patch

GET_OPATCH_XSLT – Provides style sheet for opatch XML inventory

GET_PENDING_ACTIVITY – Provides SQL patches applied to single inventory from binaries.

GET_SQLPATCH_STATUS – Patch status from SQL patch registry.

IS_PATCH_INSTALLED – Patches applied info from XML inventory

PATCH_CONFLICT_DETETION – Patch in conflict for give file.

SET_CURRENT_OPINST – inventory information for specific node in RAC.

 

Larry Catt

OCP

Oracle 12c – Apply and review patches

In Oracle 12c, Patching is performed to correct discovered bugs and close security vulnerabilities.   Oracle provides four types of Patches:

Interim Patches – single/collection of bug fixes or one-offs.

Diagnostic Patches – Diagnose or verify a fix.

Patch Set Updates (PSU) – Collection of high impact, low risk, proven fixes for products and components

Critical Patch Updates (CPU) – Collection of patches for security vulnerabilities.

Patching of the RDBMS can be done with two methods: Manually or EM Cloud Control Patch Wizard.   Additionally, the patching processes have added:  Online Patching and Queryable Inventories.   This article will give a brief overview of these Features.

Manual Patching

Step in manually patching are:

  1. Logon to oracle support https://support.oracle.com
  2. Click Patches & Updates tab.
  3. Enter the patch number you are looking for on Patches % Updates page.
  4. Click download on patch for your OS
  5. Click download Patch Metadata.
  6. Review the documentation for specific installation instructions.
  7. Use the OPatch utility to install all patches.

EM Cloud Control Patch Wizard

The EM Cloud Control Patch Wizard has the following benefits over Manual Patching:

  1. Integrate Patch work with Oracle support and provides single view to determine patch needs.
  2. End to End workflow of patching, resolving patch conflicts.
  3. Easy determination of Patch needs for single system.
  4. Patch plans can be saved as templates.
  5. Out of place patching is possible for support of RAC and GRID infrastructure.
  6. EM Cloud allows you create a patch plan to apply patches to one or more targets. A patch can be added to plan only if it’s for same release and OS.

EM Cloud can apply patches with two methods:

  1. Online Mode – EM connects to Oracle Support, searches for necessary patches, resolves any conflicts and adds them to patch plan.
  2. Offline Mode – Used when EM cannot connect to Oracle Support. EM searches for patches manually uploaded to software library and adds them to patch plan.

Online Patches

Before 11g all patches require relink of RDBMS libraries and a shutdown of system.  Now a large number of Patches can be done online, they contain .so files and do not require relink of RDBMS.

Benefit of Online Patches:

  1. No downtime.
  2. persistent across shutdown
  3. Allow rolling patches in RAC
  4. Fast install.

Downside of Online Patches:

  1. Require more memory
  2. Online patches not available for all OS
  3. Not all patches are hot patches.

Queryable Patch Inventory

Queryable Patch inventory is new 12c, implemented with DBMS_QOPATCH package and provides view of patches installed just as OPATCH lsinventory command.    DBMS_QOPATCH queries the OUI Oracle Universal Installer inventory to gather this metadata.

It provides the following:

  1. Query patches installed from SQL prompt.
  2. Creates reports and provides validation across many installs.
  3. Checks patches on RAC from single node.

 

DBMS_QOPATCH components:

GET_OPTACH_BUGS – Provides list of bug fixes per patch number.

GET_OPATCH_COUNT – Provides total number of patches installed.

GET_OPATCH_DATA – Provides top level patch info – number, creation date.

GET_OPATCH_FILES – Provides list of files updated by patch number.

GET_OPATCH_INSTALL_INFO – Provides info such as ORACLE_HOME, patch, inventory_location

GET_OPATCH_LIST – List of Patches installed.

GET_OPATCH_LSINVENTORY – Whole patch inventory

GET_OPATCH_OLAYS –Overlay for specific patch

GET_OPATCH_PREQS – Provides prerequisites for a patch

GET_OPATCH_XSLT – Provides style sheet for opatch XML inventory

GET_PENDING_ACTIVITY – Provides SQL patches applied to single inventory from binaries.

GET_SQLPATCH_STATUS – Patch status from SQL patch registry.

IS_PATCH_INSTALLED – Patches applied info from XML inventory

PATCH_CONFLICT_DETETION – Patch in conflict for give file.

SET_CURRENT_OPINST – inventory information for specific node in RAC.

 

Larry Catt

OCP

How to debug Oracle opatch utility

The following procedure shows how to debug the Oracle opatch utility when you receive an error.

1. Execution of opatch utility results in the following error.

[oracle@mylinux1 OPatch]$ opatch lsinventory
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/opatch2011-04-02_19-17-51PM.log

Patch history file: /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch_history.txt
Inventory load failed… OPatch cannot load inventory for the given Oracle Home.
LsInventorySession failed: Unable to create patchObject

OPatch failed with error code 73
[oracle@mylinux1 OPatch]$

2. Set the environmental variable OPATCH_DEBUG equal to true.

[oracle@mylinux1 OPatch]$ export OPATCH_DEBUG=true
[oracle@mylinux1 OPatch]$

3. Re-execute your opatch utility and every action performed by opatch will be displayed in your buffer. Use the messages to determine the error.

This completes use of debug with the opatch utility.

Larry J. Catt, OCP
oracle@allcompute.com
www.allcompute.com

Oracle error OUI-10058

The OUI (Oracle Universal Installer) relies on an Oracle inventory to recognize the Oracle software which already exists on the system. Multiple files are read during the OUI startup, the first of which is /etc/oraInst.loc. The oraInst.loc file points to the location of the Oracle inventor directory, which could be anywhere. In order to get an inventory of the current system, OUI must have access to this file. The error OUI-10058 is an indication that OUI does not have access to this file. This article covers the typical error and resolution to this problem

Typical error:

OUI-10058 The OUI Inventory on this system does not exist or is not writable.
1. The /opt/oracle/oraInventory/orainstall…..
or
2. You are not a member of the specified installer group.


Solution: If you executed all previous root.sh script as instructed, the most like cause is permission.

1. Logon to the system as root.
2. Change directory to /etc
3. Check the file permissions with the command ll

[root@mylinux /]# ll *ora*

4. If the file permission is not set to 644, change the permissions with the command chmod

[root@mylinux /]# chmod 644 /etc/oraInst.loc.

5. This completes the resolution of OUI-10058, due to file permissions of /etc/oraInst.loc.

Larry J. Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

Enable ADMIN Tracing on Oracle Listener:

Enable ADMIN Tracing on Oracle Listener:

Most Oracle database connects are made through the use of an Oracle process called the LISTENER which monitors a certain machine and port for connection requests to one or more database instances. There are times when a DBA or SA will want to monitor connects being made through the listener for various reasons. This article covers the setup of ADMINISTRATIVE level tracing of you Oracle Listener and will work on UNIX, Linux, and Windows based machines.

1. Logon to your Oracle database server as the Oracle software owner.

2. Open up the LSNRCTL utility.

mylinux:>lsnrctl

LSNRCTL for LINUX: Version 10.2.0.4.0 – Production on 3-Jul-2010 15:17:05

Copyright (c) 1991, 2007, Oracle. All rights reserved.

Welcome to LSNRCTL, type “help” for information.

LSNRCTL>

3. If you are not using the default name of the Oracle listener (LISTENER) then define the Oracle listener name with the following command: set current_listener

LSNRCTL> set current_listener listener_orcl
Current Listener is listener_orcl
LSNRCTL>

4. If you have established a password for your oracle listener set it in the LSNRCTL session with the following command: set password

LSNRCTL> set password oracle
The command completed successfully
LSNRCTL>

5. Turn on admin level tracing by specifying 10 or admin as the level with the following command: set trc_level

LSNRCTL> set trc_level 10
Connecting to
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.110)(PORT=1521)))
listener_orcl parameter “trc_level” set to admin
The command completed successfully

or

LSNRCTL> set trc_level admin
Connecting to
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.110)(PORT=1521)))
listener_orcl parameter “trc_level” set to admin
The command completed successfully
LSNRCTL>

6. Define the name of the trace file that will be produced by the listener process upon establishment of user connection with the command below: set trc_file

LSNRCTL> set trc_file admin_access_trace
Connecting to
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.110)(PORT=1521)))
listener_orcl parameter “trc_file” set to admin_access_trace.trc
The command completed successfully
LSNRCTL>

NOTE: You do not have to set a trace directory by default the trace files are placed under: $ORACLE_HOME/network/trace

7. Save the changes made to the listener.ora file with the following command: save_config

LSNRCTL> save_config
Connecting to
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.110)(PORT=1521)))
Saved listener_orcl configuration parameters.
Listener Parameter File /opt/app/oracle/10.2.0/network/admin/listener.ora
Old Parameter File /opt/app/oracle/10.2.0/network/admin/listener.bak
The command completed successfully
LSNRCTL>

8. Exit out of LSNRCTL utility, change directories to ORACLE_HOME/network/trace, list the files present and you can see that the trace has already begun.

LSNRCTL> exit
mylinux:> ls -lrt
total 1008
-rw-r—– 1 oracle dba 408755 Jul 3 15:25 listener_orcl.trc
-rw-r—– 1 oracle dba 43347 Jul 3 15:40 user_access_trace.trc
-rw-r—– 1 oracle dba 34150 Jul 3 15:42 admin_access_trace.trc

9. Logon to the RDBMS as sysdba and exit.

mylinux:> sqlplus ‘system/devlmgr as sysdba’

SQL*Plus: Release 10.2.0.4.0 – Production on Sat Jul 3 15:44:02 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 –
64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
mylinux:>

10. Perform another listing of the files under the directory $ORACLE_HOME/network/admin and you will see that the file admin_access_trace.trc has grown by recording the connection
from user in step 9.

mylinux:> ls -lrt
total 1136
-rw-r—– 1 oracle dba 408755 Jul 3 15:25 listener_orcl.trc
-rw-r—– 1 oracle dba 43347 Jul 3 15:40 user_access_trace.trc
-rw-r—– 1 oracle dba 119638 Jul 3 15:47 admin_access_trace.trc
mylinux:>

11 This information can be used to show where connections in you database are coming from and other statistical information about the network connection being established with administrative options.

Larry J. Catt, OCP 91, 10g
oracle@allcompute.com
www.allcompute.com

OPatch Oracle database still running: Code 73

Oracle’s OPatch utility is used for applying interim CPU patches to maintain the security of your RDBMS. In this article we will cover the error which would be received when you attempt to apply an Oracle patch to a currently executing Oracle database.

1. Logon to your Oracle database server as the Oracle software owner.

2. Change directories to the location of your CPU patch and unzip the file.

unzip p9119226_10204_.zip

3. Change directories into your unzipped patch directory which is named with the CPU number you are applying.

cd 9119226
OPatch apply

conflicts against Oracle Home…
OPatch continues with these patches: 7155248 7155249 7155250 7155251
7155252 7155253 7155254 7197583 7375611 7375613 7375617 7609057 7609058
8309592 8309632 8309642 8568395 8568397 8568398 8568402 8568404 8568405
8836667 8836671 8836675 8836677 8836678 8836681 8836683 8836684 8836686
9119226 9173244 9173248 9173253

Do you want to proceed? [y|n]
y
User Responded with: Y

Running prerequisite checks…
Prerequisite check “CheckActiveFilesAndExecutables” failed.
The details are:

Following executables are active :
/u01/prism/oracle/product/10.2.0/db_1/bin/oracle
UtilSession failed: Prerequisite check “CheckActiveFilesAndExecutables” failed.

OPatch failed with error code 73
mylinux :>

4. As seen above the OPatch utility errored out with code 73 and references the Prerequisite check “CheckActiveFilesAndExecutables”. This error normally occurs when the Oracle database is still up and running, so we check it below with the command ps:

mylinux :> ps -ef|grep smon
cimsora 26981 1 0 02:12 ? 00:00:00 ora_smon_prism
cimsora 4326 11581 0 02:34 pts/1 00:00:00 grep smon

5. As seen above the database is in fact up, so logon as sysdba and shut it down.

mylinux :> sqlplus ‘/ as sysdba’

SQL*Plus: Release 10.2.0.4.0 – Production on Sun Feb 2802:38:36 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit

6. Once the database is shutdown we can re-execute our OPatch install.

Larry J Catt, OCP9i, 10g
oracle@allcompute.com
www.allcompute.com

Wrong OPatch utility for CPU being applied: Code 14

Oracle’s OPatch utility is used for applying interim CPU patches to maintain the security of your RDBMS. In this article we will cover the error which would be received when you use OPatch options that are not supported by your currently installed OPatch utility.

1. Logon to your Oracle database server as the Oracle software owner.

2. Change directories to the location of your CPU patch and unzip the file.

unzip p9119226_10204_.zip

3. Change directories into your unzipped patch directory which is named with the CPU number you are applying.

cd 9119226
OPatch napply -skip_subset -skip_duplicate

4. You receive the following error or similar error during installation.

ORCL :> OPatch napply -skip_subset -skip_duplicate
Invoking OPatch 10.2.0.3.0

Oracle interim Patch Installer version 10.2.0.3.0
Copyright (c) 2005, Oracle Corporation. All rights reserved..

Syntax Error… Unrecognized Command or Option: failed to parse arguments
“unknown option ‘-skip_subset'”
Please use the option ‘OPatch -help’ to get correct syntax

OPatch failed with error code 14
ORCL :>

5. This error is being generated because OPatch 10.2.0.3.0 did not support the napply option or its tags. The solution is to download the latest Oracle Patch 6880880, containing the OPatch utility, and unzip it in your ORACLE_HOME directory.

Larry J Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

OPatch picks up incorrect Home from OraInst.loc: Code 73

Oracle’s OPatch utility is used for applying interim CPU patches to maintain the security of your RDBMS. OPatch uses the OraInst.loc file located in /etc or /var/opt/oracle depending on your OS to determine what needs to be applied during patch installation. In this article we will cover the error which would be received if your OraInst.loc file points at the incorrect home or contains invalid information.

1. Logon to your Oracle database server as the Oracle software owner.

2. Change directories to the location of your CPU patch and unzip the file.

unzip p9119226_10204_.zip

3. Change directories into your unzipped patch directory which is named with the CPU number you are applying.

cd 9119226
OPatch napply -skip_subset -skip_duplicate

4. You receive the following error or similar error during installation.

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

Oracle Home : /app/oracle/product/10.2.0
Central Inventory : /app/agent10g
from : /var/opt/oracle/oraInst.loc
OPatch version : 10.2.0.4.9
OUI version : 10.2.0.4.0
OUI location : /app/oracle/product/10.2.0/oui
Log file location : /app/oracle/product/10.2.0/cfgtoollogs/OPatch/OPatch2010
-02-20_14-48-27PM.log

Patch history file:
/app/oracle/product/10.2.0/cfgtoollogs/OPatch/OPatch_history.txt

List of Homes on this system:

Home name= agent10g, Location= “/app/agent10g”
Inventory load failed… OPatch cannot load inventory for the given Oracle Home.
Possible causes are:
Oracle Home dir. path does not exist in Central Inventory
Oracle Home is a symbolic link
Oracle Home inventory is corrupted
LsInventorySession failed: OracleHomeInventory gets null oracleHomeInfo

OPatch failed with error code 73

5. As seen by the error message, the OPatch utility is picking up the incorrect inventory location from the file OraInst.loc. Open up the OraInst.loc file, correct the location of your inventory directory, and re-execute OPatch.

Larry J Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

OPatch apply lib .so file not writable: Code 73

Oracle’s OPatch utility is used for applying interim CPU patches to maintain the security of your RDBMS. In this article we will cover the error which would be received if the file permissions in your Oracle Home are not correctly set and a work-around to continue your patch installation.

1. Logon to your Oracle database server as the Oracle software owner.

2. Change directories to the location of your CPU patch and unzip the file.

unzip p9119226_10204_.zip

3. Change directories into your unzipped patch directory which is named with the CPU number you are applying.

cd 9119226
OPatch napply -skip_subset -skip_duplicate

4. You receive the following error or similar error during installation.

Running prerequisite checks…
Prerequisite check “CheckApplicable” failed.
The details are:

Patch 8568398:
Copy Action: Desctination File “/app/oracle/product/10.2.0/lib/libjox10.so”
is not writeable.
‘oracle.rdbms, 10.2.0.4.0’: Cannot copy file from ‘libjox10.so’ to
‘/app/oracle/product/10.2.0/lib/libjox10.so’

UtilSession failed: Prerequisite check “CheckApplicable” failed.

OPatch failed with error code 73
$

5. The OPatch utility was unable to update one of the lib files for installation, due to a file permission error. To resolve this issue, change directories to your ORACLE_HOME/lib directory.

$ cd /app/oracle/product/10.2.0/lib/

6. Check the existence of the lib file in question with the command ls.

$ ls libjox10.so
-r-xr-xr-x 1 fpdfqt dba 20041728 Jan 07 2009 libjox10.so

7. Make a backup copy of this lib file with the command cp.

$ cp libjox10.so libjox10.so_bak

8. Change permission on the lib file in question.

$ chmod 777 libjox10.so
$

8. Attempt re-installation of your Oracle patch with the OPatch utility.

OPatch napply -skip_subset -skip_duplicate

Return Code = 0

The local system has been patched and can be restarted.

UtilSession: N-Apply done.

OPatch succeeded.
$

As seen above the patch set is successfully installed.

Larry J Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com