Complete Guide to Oracle Database Patching:

CPU, PSU, and Bug Fix Patches

What is Oracle Patching?

Patching is a crucial process in Oracle Database Management, where software updates are applied to fix bugs, security vulnerabilities, and performance issues. Oracle releases patches regularly to enhance the database's stability and security.

Why is Patching Important?

  • Fixes known bugs that affect database performance.

  • Addresses security vulnerabilities to protect against cyber threats.

  • Ensures better stability and database uptime.

  • Required for Oracle Support compliance.

How Oracle Database Patching Works

Oracle Database is written in Java and is used to store and manage large amounts of data. Patching is performed at the Oracle Software level rather than the database level.

For example, after upgrading Oracle Database from 12c to 19c, a job that used to run for 2 hours started taking 8 hours. Upon contacting Oracle Support, it was identified as a bug, and they recommended applying a bug fix patch to resolve the issue.

Types of Oracle Patches

Oracle provides different types of patches:

1. CPU (Critical Patch Update) - Security Patches

  • Released quarterly to address security threats.

  • Fixes security vulnerabilities identified by Oracle.

  • Helps protect sensitive database information.

2. PSU (Patch Set Update) - Security + Priority Fixes

  • Includes CPU patches along with additional fixes.

  • If applied, no need to install CPU patches separately.

  • Fixes major issues impacting business operations.

3. Bug Fix Patches

  • Address specific unresolved issues that are not covered in PSU patches.

  • Provided by Oracle Support based on reported issues.

  • Typically included in the next PSU patch cycle.

👉 All these patches are available at support.oracle.com

How to Apply Oracle Patches

Pre-Requisites Before Applying a Patch

  1. Take backup using RMAN.

  2. Obtain client approval for database downtime.

  3. Ensure sufficient disk space in $ORACLE_HOME.

  4. Identify and verify existing patches using:

    $ORACLE_HOME/OPatch/opatch lsinventory

  5. Check for invalid objects in the database: SELECT COUNT(*) FROM dba_objects WHERE status = 'INVALID';

Steps to Apply a Patch

Step 1: Download the Patch

  • Download from support.oracle.com

  • Extract and read the README file for instructions.

Step 2: Upgrade OPatch Utility (If Required)

  • Check OPatch version:

    $ORACLE_HOME/OPatch/opatch version

  • If the version is outdated, upgrade it:

          cp -R Opatch $ORACLE_HOME

Step 3: Apply the Patch

  • Shutdown the database and related services.

  • Navigate to the extracted patch directory.

  • Run the OPatch command:

    $ORACLE_HOME/OPatch/opatch apply

  • Provide YES when prompted.

Step 4: Apply Patch on Oracle Database Using Datapatch

  • Start the database and apply patch at the database level:

    nohup ./datapatch -verbose &

  • Monitor logs using:

        tail -100f nohup.out

Step 5: Verify Patch Installation

  • Check applied patches:

    $ORACLE_HOME/OPatch/opatch lspatches

  • Verify invalid objects count:

SELECT COUNT(*) FROM dba_objects WHERE status = 'INVALID';

Step 6: Rollback the Patch (If Required)

  • In case of issues, rollback using:

opatch rollback -id <Patch_ID>

Common Issues and Troubleshooting

1. Patch Conflict Issues

  • If a patch is already applied, remove it before reapplying

opatch rollback -id <Patch_ID>

 Check conflicts using:

$ORACLE_HOME/OPatch/opatch lsinventory | grep -i "conflict"

2. Space Issues in ORACLE_HOME

Check available space before applying the patch.

df -h | grep ORACLE_HOME

3. Invalid Objects Post-Patching

If the count increases after patching, run:

@utlrp.sql

Frequently Asked Questions (FAQ)

Q1. What is the difference between CPU and PSU patches?

  • CPU Patch: Security updates only.

  • PSU Patch: Includes CPU patches + additional fixes.

Q2. How can I check the OPatch version?

$ORACLE_HOME/OPatch/opatch version

Q3. How to check applied patches?

$ORACLE_HOME/OPatch/opatch lsinventory

Q4. What should I do if I find conflicts while applying a patch?

  • Identify conflicting patches and rollback the conflicting patch before applying the new one.

  • Use opatch rollback -id <Patch_ID>

Conclusion

Oracle Patching is a crucial maintenance activity that ensures database security, stability, and performance. Always follow best practices, take backups, and monitor logs while applying patches.

✅ Need more help? Visit support.oracle.com for official documentation and updates.


 

Comments

Post a Comment

Popular posts from this blog

Image
Oracle Export Metadata only And Import the Metadata only Guide – Step-by-Step Tutorial Scenario : Client is wanted to export and import the metadata In this guide, we will learn how to use expdp (Data Pump Export) and impdp (Data Pump Import) in Oracle Database to back up metadata and restore the same only. To create a backup, first we have to set up a physical backup location. Creating a Backup location: On Linux server: mkdir -p /data/backup On Oracle Database: Command: create a directory TEST_DIR2 as '/data/backup'; Here, TEST_DIR2 is a logical directory pointing to the physical backup location. To verify the directory: SELECT * FROM dba_directories WHERE directory_name = 'TEST_DIR2'; Exporting a Metadata using expdp: To backup the Metadata for Schema U2,  expdp schemas=U2 directory=TEST_DIR2 dumpfile=schdata_only2.dmp logfile=schdata_only.log content=METADATA_ONLY Note:   Dump File (.dump): A binary file generated by expdp that contains exported schema objects, data...
Read more
Image
How to Enable Archive Log Mode in Oracle (Switch to ARCHIVELOG Mode) If you want to disable archive log mode (switch to NOARCHIVELOG mode), follow these steps: 1. Check Current Archive Log Mode Before making changes, check if your database is in NO ARCHIVELOG mode: command -> select log_mode from v$database; The database in noarchivelog mode   If the output is NO ARCHIVELOG , it means archive logging is disabled. 2. Shutdown the Database You cannot switch to ARCHIVELOG mode while the database is running, so first shut it down: commnd -> shut immediate; 3. Start the Database in MOUNT Mode the database should be in mount stage to disable or enable it 4. Enable Archive Log Mode (Switch to ARCHIVELOG Mode) Run the following command: ALTER DATABASE ARCHIVELOG; 5. Open the Database Normally ALTER DATABASE OPEN; 6. Verify the Change Run this command again to confirm the mode: SELECT log_mode FROM v$database; Impact of Enabling Archive Log Mode ✅ Point-in-Time Recovery: With...
Read more
Image
Oracle Export Previous SCN Data & Import into an Existing Table – Step-by-Step Guide Scenario: In this guide, we will explore how to use Oracle Data Pump Export (expdp) and Import (impdp) to back up historical data from a previous SCN (System Change Number) and restore it into an existing table. This approach is useful when you need to retrieve older data while preserving the table structure. Step 1: Setting Up a Backup Location Before exporting data, we need to create a physical backup location where the dump files will be stored. On Linux server: mkdir -p /data/backup On Oracle Database: We create a logical directory pointing to the physical backup location: Command: create a directory TEST_DIR2 as '/data/backup'; TEST_DIR2 -->  Logical directory pointing to the physical backup location. To verify the directory: SELECT * FROM dba_directories WHERE directory_name = 'TEST_DIR2'; Step 2: Checking Current Table Data Inserted few more records Before exporting, ...
Read more