dcsimg
Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
SQL Scripts & Samples
Tips
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:



News Via RSS Feed


Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted June 14, 2018

Creating an Oracle Patching Strategy

By David Fitzjarrell

Patching is a necessity, and CPU updates can include patches some installations may not need. This 'all or nothing' approach may create issues that didn't exist prior to the patch, which is why it's recommended to backup both the database and the software home before making any changes. Of course, if a patch needs to be backed out, it's two restores -- the database home and the database -- can take considerable time. Let's look at a possible solution to this problem.

If sufficient planning is done ahead of time a tar or pax archive of the software home can be made before any changes occur; this provides a clean slate, if you will, for either restoring the original software to the home or for creating an exact replica under a different directory. The time and labor savings are realized if that archive is used to create a second home, one that patches can be applied to without disrupting the daily workflow. All that is required is to register this new home in the registry and set a single session to use it. The OUI can register new homes with a single-line command:

./runInstaller -silent -invPtrLoc $ORACLE_HOME/oraInst.loc -attachHome ORACLE_HOME="/oracle/12c2/stage_apr2018" ORACLE_HOME_NAME="Ora12c2StagePtchHome"

Execute an opatch lsinventory after the home has been added to ensure that the installer recognizes the new home, otherwise any patching attempt will fail:


$ OPatch/opatch lsinventory
Oracle Interim Patch Installer version 12.2.0.1.13
Copyright (c) 2018, Oracle Corporation.  All rights reserved.


Oracle Home       : ...
Central Inventory : /export/home/oracle/oraInventory
   from           : ...
OPatch version    : 12.2.0.1.13
OUI version       : 12.2.0.1.4
Log file location : ...

Lsinventory Output file location : ...

--------------------------------------------------------------------------------
Local Machine Information::
Hostname: ...
ARU platform id: 23
ARU platform description:: Solaris Operating System (SPARC 64-bit)


Installed Top-level Products (1):

Oracle Database 12c                                                  12.2.0.1.0
There are 1 products installed in this Oracle Home.


There are no Interim patches installed in this Oracle Home.


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

OPatch succeeded.

The above output proves the home is configured in the inventory and all is in order to proceed with patching.

In one session logged in as 'oracle' set the ORACLE_HOME variable to the location of this new Oracle home -- a possible naming convention is to use the SID of the database in the path and the month and year of the CPU being applied, such as /u01/app/oracle/product/bubba_apr_2018/dbhome_1. This readily identifies the home as having the current CPU applied to it. Other naming conventions can also be used; it is a good idea to use a convention that clearly identifies this as a patched home, so it can be distinguished from the original, unpatched version.

Verify that the version of OPatch is the version required to apply the CPU -- it may be necessary to download and install a newer release of the utility (this is available at https://support.oracle.com). To preserve the old version, move the OPatch directory to OPatch_old, then unzip the archive into the new home.

Applying the CPU is quite easy in such a configuration -- the destination database is still running from its original home, so no processes need to be stopped. Verify that the home being patched is the correct one (opatch reports this prior to asking for approval to proceed) and then continue as expected. The new home will be patched at the software level; the database level patching is left for the scheduled outage taken to complete the task.

Any additional patching or any recovery patches that need to be applied should follow immediately upon completion of the CPU. Again, since there is no database yet running in this new home no work is disrupted and any number of patches can be applied without affecting the end users. Once patching is complete some files will need to be copied to the new Oracle home; the dbs, netwoerk/admin and hs/admin directories should be copied from the existing home to the new home to ensure no functionality is lost in the move. For those not using extproc the original $ORACLE_HOME/hs/admin directory will not need to be copied.

Patching is now complete and a new, patched home awaits its database. On the day of the switch simply stop all services and processes running from the original home, change the oratab file to reference the new database home, properly set the environment and start the database. Check the alert log for any unexpected messages (there should be none) and proceed with executing $ORACLE_HOME/OPatch/datapatch -verobose. This process can take a bit of time so be patient -- progress will be displayed on the terminal as each section of the patch is applied to the data dictionary objects.

When the datapatch utility has finished its tasks it's time to start the listener and test local and remote connectivity. Also check if any extproc functionality is missing in this new installation. In 12.1 and later releases extproc is no longer configured in the listener.ora file; it uses the extproc.ora file located under $ORACLE_HOME/hs/admin (which explains the note a bit earlier in the article). When all looks good the patched database can be released to a waiting user community.

Occasionally the unthinkable happens and the patch creates issues not previously experienced, requiring that it be backed out. With a configuration described here such a backout takes little effort -- shutdown the running database, restore the original home location in the oratab file, reset the environment to use the unpatched home then restore the database via RMAN, recover to just before the patch and open the database. All pieces of the patch are now gone and no tedious patch backout procedures were necessary.

Presuming the patch was successful the original home can be backed up to tape and then deleted. If a 'backout' occurred, then the patched home can be removed without any backup necessary. Space is reclaimed for the next CPU endeavor, if it's determined that the CPU is necessary to fix any current issues.

This may appear to be a rather unorthodox way to apply CPU patches, but it may also save time and effort in the long run by allowing the DBA team to patch a database home without disrupting the workflow. An hour or two to finish the database patching is far less time than patching an existing home given that backups (both operating system and database) need to be taken and planning must include considerable time to restore the original software from the tape archives in the event of a patching failure. It's also a fairly 'clean' method since no remnants may be left behind from overlaying the old software on top of the patched home (new files for the patched version won't be removed by restoring the original software to the patched location).

Consider this method before applying the next CPU; it will likely save time, money and resources better used for more pressing projects.

See all articles by David Fitzjarrell



Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM