Automated Patching database

In my daily development activities, I need to apply Database patches in every build cycle per the requirement from development team.

A sample readme file of a database patch is like below,

(1) Prerequisites
Before you install or deinstall the patch, ensure that you meet the following requirements:
Note: In case of an Oracle RAC environment, meet these prerequisites on each of the nodes.

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. Oracle recommends you to use the latest version of OPatch.

3. Ensure that you set the ORACLE_HOME environment variable to the Oracle home of the Oracle Database.

4. Ensure that you set the PATH environment variable to include the location of the unzip executable, and the <ORACLE_HOME>/bin and the <ORACLE_HOME>/OPatch directories present in the Oracle home of the Oracle Database.

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

6. Ensure that you shut down all the services running from the Oracle home.

– For a Non-RAC environment, shut down all the services running from the Oracle home.
– For a RAC environment, shut down all the services (database, ASM, listeners, nodeapps, and CRS daemons) running from the Oracle home of the node you want to patch. After you patch this node, start the services on this node.Repeat this process for each of the other nodes of the Oracle RAC system. OPatch is used on only one node at a time.

(2) Installation
To install the patch, follow these steps:
Note: In case of an Oracle RAC environment, perform these steps on each of the nodes.

1. Maintain a location for storing the contents of the patch ZIP file. In the rest of the document, this location (absolute path) is referred to as <PATCH_TOP_DIR>.

2. Extract the contents of the patch ZIP file to the location you created in Step (1). To do so, run the following command:
$ unzip -d <PATCH_TOP_DIR>

3. Navigate to the <PATCH_TOP_DIR>/123456 directory:
$ cd <PATCH_TOP_DIR>/123456

4. Install the patch by running the following command:
$ opatch apply

When OPatch starts, it validates the patch and ensures that there are no conflicts with the software already installed in the ORACLE_HOME of the Oracle Database. OPatch categorizes conflicts into the following types:
– Conflicts with a patch already applied to the ORACLE_HOME – In this case, stop the patch installation and contact Oracle Support Services.
– Conflicts with a patch already applied to the ORACLE_HOME that is a subset of the patch you are trying to apply – In this case, continue with the patch installation because the new patch contains all the fixes from the existing patch in the ORACLE_HOME. The subset patch will automatically be rolled back prior to the installation of the new patch.

5. Start the services from the Oracle home.

(3) Postinstallation

After you install the patch, reload the packages into the Oracle Database.
For example, for RDBMS, connect as SYSDBA and run the following:
SQL> @?/sqlpatch/123456/postinstall.sql

Let me conclude the process as below:

1. Prerequisites:

Ensure $ORACLE_HOME and $ORACLE_SID are set properly

Ensure opatch is working fine. (export PATH=$ORACLE_HOME/OPatch:$PATH)

Ensure database instance and listener are shut down

2. Apply patch by invoking opatch.

3. Startup the database and listener.

4. Post install step if any.

As these steps are duplicated, then let’s automate them.

(Prepare the script using Perl).

# Prepare:

You need to have two lists. One list defines the to-do patches (todo.lst) and another one defines the post-install steps (post_install.lst).

# Step 1. Generate the inventory Pre application.

system(“opatch lsinventory > inventory_pre.lst”);

# Step 2. Check with the todo.lst and stage the missing patches in file todo_staging.lst

# Step 3. Shutdown listener and database.

system(“lsnrctl stop $ORACLE_SID”);


# Step 4. apply patches,

foreach patchnum in todo_staging.lst

system(“opatch apply -silent -ocmrf ocm.rsp $patchnum”); # How to define a ocm.rsp file, pls. check here.

# Step 5. Startup db and listener,

system(“lsnrctl start $ORACLE_SID”);


# Step 5. Apply post-install steps,

foreach patchnum in todo_staging.lst

foreach item in post_install.lst for patchnum

run the item listed in post_install.lst

# Step 6. Generate the inventory Post application and check if any patches are still missing.

Below are the main steps to automate the database patches application. Actually to enhance it, you can add below steps:

1. In some special patches, they might require you to roll back other conflict patches before apply itself. You need to define a rollback list and rollback it firstly.

2. Better error-control.