Mohamed Fowjil

Friday, August 9, 2024

Oracle Database Tablesapace usage check

SELECT
tablespace_name,
ROUND((tablespace_size * 8192) / 1024 / 1024 / 1024, 2) AS SIZE_GB,
ROUND((used_space * 8192) / 1024 / 1024 / 1024, 2) AS USED_GB,
ROUND(used_percent, 2) AS used_percent
FROM
dba_tablespace_usage_metrics;

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

Space used and free by tablespace (from SQL Developer)

SELECT   ts.tablespace_name
, 'SQLDEV:GAUGE:0:100:0:0:'
    ||NVL ( ROUND ( ( ( datafile.bytes - NVL ( freespace.bytes, 0 ) ) / datafile.bytes ) * 100, 2 ), 0 ) percent_used
, ROUND ( ( ( datafile.bytes         - NVL ( freespace.bytes, 0 ) ) / datafile.bytes ) * 100, 2 ) PCT_USED
, datafile.bytes                     / 1024 / 1024 allocated
, ROUND ( datafile.bytes             / 1024 / 1024 - NVL ( freespace.bytes, 0 ) / 1024 / 1024, 2 ) used
, ROUND ( NVL ( freespace.bytes, 0 ) / 1024 / 1024, 2 ) free
, datafile.datafiles
FROM dba_tablespaces ts
, (SELECT   tablespace_name
      , SUM ( bytes ) bytes
      FROM dba_free_space
      GROUP BY tablespace_name
    ) freespace
, (SELECT   COUNT ( 1 ) datafiles
      , SUM ( bytes ) bytes
      , tablespace_name
      FROM dba_data_files
      GROUP BY tablespace_name
    ) datafile
WHERE freespace.tablespace_name (+) = ts.tablespace_name
AND datafile.tablespace_name (+)   = ts.tablespace_name
ORDER BY NVL ( ( ( datafile.bytes - NVL ( freespace.bytes, 0 ) ) / datafile.bytes ), 0 ) DESC

Monday, August 5, 2024

Oracle Database Patch 36582781 - Database Release Update 19.24.0.0.240716

Oracle® Database

Patch 36582781 - Database Release Update 19.24.0.0.240716

This document is accurate at the time of release. For any changes and additional information regarding Database Release Update 19.24.0.0.240716, see these related documents that are available at My Oracle Support (http://support.oracle.com/):

Document 19202407.9 Oracle DB/GI/OJVM Update/Revision R19 Jul 2024 Known Issues
Document 555.1 Oracle Database 19c Important Recommended One-off Patches
Document 888.1 Primary Note for Database Proactive Patch Program
Document 1919.2 19c Database Upgrade - Self Guided Assistance with Best Practices

This document includes the following sections:

Section 1, "Patch Information"
Section 2, "Prerequisites"
Section 3, "Installation"
Section 4, "Deinstallation"
Section 5, "Known Issues"
Section 6, "References"
Section 7, "Bugs Fixed by This Patch"
Section 8, "Documentation Accessibility"

1 Patch Information

Database Release Update 19.24.0.0.240716 patches are cumulative. That is, the content of all previous Database bundles is included in the latest Database bundle patch.
To install the Database Release Update 19.24.0.0.240716 patch, the Oracle home must have Oracle Database release 19c installed.
This patch is Oracle RAC Rolling Installable.
This patch is Database Vault installable. Review My Oracle Support document 1195205.1 Primary Note For Oracle Database Vault for details on how to apply this patch to a Database Vault environment.
This patch is Data Guard Standby-First Installable. See My Oracle Support document 1265700.1 Oracle Patch Assurance - Data Guard Standby-First Patch Apply for details on how to remove risk and reduce downtime when applying this patch.
This patch includes the JDK fixes released in the prior cycle and will update the JDK in the Oracle home. For the most recent JDK fixes, a separate patch is available and needs to be installed in addition to this patch. Refer to My Oracle Support document 888.1 Primary Note for Database Proactive Patch Program for the JDK patch number.
Beginning with 19.21 Oct2023 RU, UTL_URI.ESCAPE function is now compliant with RFC 3896 and will treat "#" as a reserved character. See My Oracle Support Note 2981395.1 for more details.
Beginning with 19.22 Jan2024 RU, the 19c database is now certified on Oracle Linux 9.x and RHEL9.x.
Beginning with 19.18 Jan2023 RU, the DBRU contains the CORE DST Time Zone patches. Applying this Release Update (RU) does not change the DST version in any of your databases. The DST patches are only copied into $ORACLE_HOME/oracore/zoneinfo and no DST upgrade is performed. You can decide to upgrade the DST version in your database to a newer DST version independently of applying the RU at any time. Be aware that this incurs downtime.

This DBRU contains the following CORE DST patches:

28852325 - DSTV33 UPDATE - TZDATA2018G
29997937 - DSTV34 UPDATE - TZDATA2019B
31335037 - DSTV35 UPDATE - TZDATA2020A
32327201 - DSTV36 UPDATE - TZDATA2020E
33613829 - DSTV37 UPDATE - TZDATA2021E
34006614 - DSTV38 UPDATE - TZDATA2022A
34533061 - DSTV39 UPDATE - TZDATA2022C
34698179 - DSTV40 UPDATE - TZDATA2022E
35099667 - DSTV41 UPDATE - TZDATA2022G
35220732 - DSTV42 UPDATE - TZDATA2023C
36260493 - DSTV43 UPDATE - TZDATA2024A

If you plan to adjust the DST version of your databases, Oracle recommends adjusting it to the latest available DST version. Then follow the instructions in the Oracle 19c Oracle Database Upgrade Guide.

For more information on various DST patch releases, refer to My Oracle Support document 412160.1 Primary Note DST FAQ: Updated DST Transitions and New Time Zones in Oracle RDBMS and OJVM Time Zone File Patches.

Any new or changed database initialization parameters that might be included into a quarterly patch bundle would be documented in the Oracle Database Reference manual section on initialization parameters. For Oracle Database 19c, refer to Oracle Database Reference.
For the latest Update with Security Fixes that should be used on Client-Only installations, refer to the Critical Patch Update (CPU) Program Patch Availability Document (PAD) section on the Oracle Database, for the cycle in which you are interested.
Applying Database Release Update Database Release Update 19.24.0.0.240716 with Oracle JavaVM 19.x Updates

For customers who want to install both patches together during a single downtime window, follow one of the Patching Options listed in the My Oracle Support document:

1929745.1 - Oracle Recommended Patches - "Oracle JavaVM Component Database PSU and Update" (OJVM PSU and OJVM Update) Patches.

The Oracle JavaVM 19.x Release Update is available as a separate patch. That separate patch has some additional requirements if it is to be installed in a RAC Rolling fashion, as detailed in My Oracle Support document:

2217053.1 - RAC Rolling Install Process for the "Oracle JavaVM Component Database PSU/RU" (OJVM PSU/RU) Patches.

2 Prerequisites

It is highly recommended to take a backup of the Oracle_Home binaries and Central Inventory prior to applying patches. For further information, refer to My Oracle Support document 565017.1 How to Perform ORACLE_HOME Backup?.

This section includes the following section:

Section 2.1, "OPatch Utility"

2.1 OPatch Utility

You must use the OPatch utility version 12.2.0.1.42 or later to apply this patch. Oracle recommends that you use the latest released OPatch version for 12.2, which is available for download from My Oracle Support patch 6880880 by selecting ARU link for the 12.2.0.1.0 OPatch release. It is recommended that you download the OPatch utility and the patch in a shared location to be able to access them from any node in the cluster for the patch application on each node.

When patching the GI home, a shared locaton on Oracle ACFS only needs to be unmounted on the node where the GI home is being patched.

The new OPatch utility should be updated in all of the Oracle RAC database homes and the GI home that are being patched.

For each Oracle RAC database home and the GI home that are being patched, as the home owner, extract the OPatch utility.

For exact instructions to install OPatch, follow the OPatch readme.

A new feature has been added to OPatch to increase performance by deleting inactive patches. See My Oracle Support document 2942102.1 OPatch 12.2.0.1.37+ Introduces a New Feature to Delete Inactive Patches in the ORACLE_HOME/.patch_storage directory.

For information about OPatch documentation, including any known issues, see My Oracle Support document 293369.1 Primary Note For OPatch.

3 Installation

These instructions are for all Oracle Database installations.

Section 3.1, "Patch Pre-Installation Instructions"
Section 3.2, "Patch Installation Instructions"
Section 3.3, "Patch Post-Installation Instructions"
Section 3.4, "Patch Post-Installation Instructions for Databases Created or Upgraded after Installation of this patch in the Oracle Home"

3.1 Patch Pre-Installation Instructions

Before you install Database Release Update 19.24.0.0.240716, perform the following actions to check the environment and to detect and resolve any interim patch conflicts.

3.1.1 Environments with Grid Infrastructure

This patch is not to be installed to an environment that has a Grid Infrastructure home. Refer to the following My Oracle Support document 888.1 to determine the appropriate Grid infrastructure patch to be installed.

3.1.2 Environment Checks

Ensure that the $PATH definition has the following executables: make, ar, ld, and nm.

The location of these executables depends on your operating system. On many operating systems, they are located in /usr/ccs/bin, in which case you can set your PATH definition as follows:

export PATH=$PATH:/usr/ccs/bin

3.1.3 Interim Patch Conflict Detection and Resolution

The fastest and easiest way to determine whether you have interim patches in the Oracle home that conflict with Database Release Update 19.24.0.0.240716, and to get the necessary conflict resolution patches, is to use the Patch Recommendations and Patch Plans features on the Patches & Updates tab in My Oracle Support. These features work in conjunction with the My Oracle Support Configuration Manager. Recorded training sessions on these features can be found in My Oracle Support document 603505.2 My Oracle Support How-to Video Training Series.

However, if you are not using My Oracle Support Patch Plans, the My Oracle Support Conflict Checker tool enables you to upload an OPatch inventory and check the patches that you want to apply to your environment for conflicts.

If no conflicts are found, you can download the patches. If conflicts are found, the tool finds an existing resolution to download. If no resolution is found, it will automatically request a resolution, which you can monitor in the Plans and Patch Requests region of the Patches & Updates tab.

For more information, see Knowledge Document 1091294.1, How to Use My Oracle Support Conflict Checker Tool for Patches Installed with OPatch.

Or, use the following steps to manually discover conflicts and resolutions:

Determine whether any currently installed interim patches conflict with the patch being installed, 36582781:
unzip p36582781_<version>_<platform>.zip
cd 36582781
opatch prereq CheckConflictAgainstOHWithDetail -ph ./
The report indicates the patches that conflict and the patches that are a superset.
Use My Oracle Support document 1321267.1 Database Patch Conflict Resolution to determine, for each conflicting patch, whether a conflict resolution patch is already available, and if you need to request a new conflict resolution patch or if the conflict may be ignored.
When all the interim patches that you have requested are available at My Oracle Support, proceed with Section 3.2, "Patch Installation Instructions".

3.2 Patch Installation Instructions

Follow these steps:

If you are using a Data Guard Physical Standby database, you must install this patch on both the primary database and the physical standby database, as described by My Oracle Support document 278641.1 How do you apply a Patchset, PSU or CPU in a Data Guard Physical Standby configuration.
If this is an Oracle RAC environment, install this patch using the OPatch rolling (no downtime) installation method as this patch is rolling RAC installable. Refer to My Oracle Support document 244241.1 Rolling Patch - OPatch Support for RAC.
If this is not a RAC environment, shut down all instances and listeners associated with the Oracle home that you are updating. For more information, see Oracle Database Administrator's Guide.
Set your current directory to the directory where the patch is located and then run the OPatch utility by entering the following commands:
unzip -d <PATCH_TOP_DIR> p36582781_<version>_<platform>.zip
cd <PATCH_TOP_DIR> 36582781
opatch apply
If there are errors, refer to Section 5, "Known Issues".

3.3 Patch Post-Installation Instructions

On Oracle Database server, after installing the patch, perform the following actions. This is not required on Oracle Database client.

3.3.1 Apply Conflict Resolution Patches

Apply the patch conflict resolution interim patches that were determined to be needed when you performed the steps in Section 3.1.4, "Interim Patch Conflict Detection and Resolution".

3.3.2 Load Modified SQL Files into the Database

The following steps load modified SQL files into the database. For an Oracle RAC environment, perform these steps on only one node.

Datapatch is run to complete the post-install SQL deployment for the patch being installed. For further details about Datapatch, including Known Issues and workarounds to common problems, see My Oracle Support document 1585822.1 Datapatch: Database 12c or later Post Patch SQL Automation and My Oracle Support document 2680521.1 Datapatch User Guide.

For each separate database running on the same shared Oracle home being patched, run the datapatch utility as described in Table 2.

Table 2 Steps to Run the Datapatch Utility for Non-CDB or Non-PDB Database Versus Multitenant (CDB or PDB) Oracle Database

Steps	Non-CDB or Non-PDB Database	Steps	Multitenant (CDB/PDB) Oracle Database
1	sqlplus /nolog	1	sqlplus /nolog
2	SQL> Connect / as sysdba	2	SQL> Connect / as sysdba
3	SQL> startup	3	SQL> startup
4	SQL> quit	4	SQL> alter pluggable database all open;^{Foot 1}
5	cd $ORACLE_HOME/OPatch	5	SQL> quit
6	./datapatch -sanity_checks (optional)	6	cd $ORACLE_HOME/OPatch
7	./datapatch -verbose	7	./datapatch -sanity_checks (optional)
		8	./datapatch -verbose

^{Footnote 1} It is recommended the Post Install step be run on all pluggable databases; however, the following command (SQL> alter pluggable database PDB_NAME open ) could be substituted to only open certain PDBs in the single or multitenant database. Doing so will result in the Post Install step only being run on the CDB and opened PDB's. To update a pluggable database at a later date (skipped or newly plugged in), open the database using the alter pluggable database command mentioned previously and rerun the datapatch utility.

See My Oracle Support document 1935365.1 Multitenant Unplug/Plug Best Practices for more information about the procedure for unplugging/plugging with different patch releases (in both directions).

Recommended: The datapatch -sanity_checks optional step runs a series of environment and database checks to validate if conditions are optimal for patching. Results are shown on screen with severity and potential actions to take.

For more information, refer to My Oracle Support document 2680521.1 Datapatch User Guide for additional information and actions. Oracle highly recommends that you perform this step.

The datapatch utility runs the necessary apply scripts to load the modified SQL files into the database. An entry is added to the dba_registry_sqlpatch view reflecting the patch application. In the dba_registry_sqlpatch view, verify that the status for the APPLY is SUCCESS.

For any other status, refer to My Oracle Support document 2335899.2 Troubleshooting Assistant: 12c Datapatch Issues for additional information and actions.

Check the following log files in $ORACLE_BASE/cfgtoollogs/sqlpatch/36582781/<unique patch ID> for errors:
36582781_apply_<database SID>_<CDB name>_<timestamp>.log

where database SID is the database SID, CDB name is the name of the multitenant container database, and timestamp is of the form YYYYMMMDD_HH_MM_SS.

Any (pluggable) database that has invalid objects after the execution of datapatch should have catcon.pl run to revalidate those objects. For example:
export PATH=$PATH:$ORACLE_HOME/bin
cd $ORACLE_HOME/rdbms/admin

$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -n 1 -e -b utlrp -d $ORACLE_HOME/rdbms/admin utlrp.sql

3.3.3 Upgrade Oracle Recovery Manager Catalog

If you are using the Oracle Recovery Manager, the catalog needs to be upgraded. Enter the following command to upgrade it. The UPGRADE CATALOG command must be entered twice to confirm the upgrade.

$ rman catalog username/password@alias

RMAN> UPGRADE CATALOG;

RMAN> EXIT;

3.3.4 Bug Fixes That May Change an Existing Optimizer Execution Plan

At the successful conclusion of the patching event, none of the database bug fixes that may change an existing optimizer execution plan are delivered with the bug fix disabled by default. The status of any module bug fixes (which cause an execution plan change) that were in an enabled state prior to starting the patching event are preserved, but no new module bug fixes (which cause an execution plan change) are activated automatically.

Details on this, including the commands to explicitly enable such bug fixes are present in My Oracle Support document 2147007.1 Managing "installed but disabled" bug fixes in Database Release Updates using DBMS_OPTIM_BUNDLE.

3.3.5 Update Permissions for extjob

Database release update patch installation involves relinking of libraries and executables to be updated. This can sometimes result in permissions changing and needing to be updated. Execute the following as root user:

# chown root $ORACLE_HOME/bin/extjob

# chmod 4750 $ORACLE_HOME/bin/extjob

3.4 Patch Post-Installation Instructions for Databases Created or Upgraded after Installation of this patch in the Oracle Home

On Oracle Database server, you must execute the steps in Section 3.3.2, "Load Modified SQL Files into the Database" for any new database. There are no actions required for databases that have been upgraded. This is not required on Oracle Database client.

4 Deinstallation

These instructions apply if you need to deinstall the patch.

4.1 Patch Deinstallation Instructions for a Non-RAC Environment

Follow these steps:

Shut down all instances and listeners associated with the Oracle home that you are updating. For more information, see Oracle Database Administrator's Guide.
Run the OPatch utility specifying the rollback argument as follows.
opatch rollback -id 36582781
If there are errors, refer to Section 5, "Known Issues".

4.2 Patch Post-Deinstallation Instructions for a Non-RAC Environment

On Oracle Database server, after deinstalling the patch, perform the following actions. This is not required on Oracle Database client.

Rollback SQL changes from the the Database, as explained in Section 4.2.1.
Upgrade Oracle Recovery Manager Catalog, as explained in Section 4.2.2.

4.2.1 Load Modified SQL Files into the Database

Datapatch is run to complete the post-deinstall SQL deployment for the patch being deinstalled. For further details about Datapatch, including Known Issues and workarounds to common problems, see My Oracle Support document 1585822.1 Datapatch: Database 12c or later Post Patch SQL Automation and My Oracle Support document 2680521.1 Datapatch User Guide.

Follow these steps:

For each separate database running on the same shared Oracle home being patched, run the datapatch utility as described in Table 3. If this is Oracle RAC, run datapatch on only one instance.

Table 3 Steps to Run the datapatch Utility for Non-CDB or Non-PDB Database Versus Multitenant (CDB or PDB) Database

Steps	Non-CDB or Non-PDB Database	Steps	Multitenant (CDB/PDB) Oracle Database
1	sqlplus /nolog	1	sqlplus /nolog
2	SQL> Connect / as sysdba	2	SQL> Connect / as sysdba
3	SQL> startup	3	SQL> startup
4	SQL> quit	4	SQL> alter pluggable database all open^{Foot 1}
5	cd $ORACLE_HOME/OPatch	5	SQL> quit
6	./datapatch -sanity_checks (optional)	6	cd $ORACLE_HOME/OPatch
7	./datapatch -verbose	7	./datapatch -sanity_checks (optional)
		8	./datapatch -verbose

^{Footnote 1} It is recommended the Post Install step be run on all pluggable databases; however, the following command (SQL> alter pluggable database PDB_NAME open ) could be substituted to only open certain PDBs in the single/multitenant database. Doing so will result in the Post Install step only being run on the CDB and opened PDB's. To update a pluggable database at a later date (skipped or newly plugged in), open the database using the alter pluggable database command mentioned previously and rerun the datapatch utility.

See My Oracle Support document 1935365.1 Multitenant Unplug/Plug Best Practices for more information about the procedure for unplugging/plugging with different patch releases (in both directions).

Recommended: The datapatch -sanity_checks optional step runs a series of environment and database checks to validate if conditions are optimal for patching. Results are shown on screen with severity and potential actions to take.

For more information, refer to the My Oracle Support document 2680521.1 Datapatch User Guide for additional information and actions. Oracle highly recommends that you perform this step.

The datapatch utility runs the necessary rollback scripts. An entry is added to the dba_registry_sqlpatch view reflecting the patch application. In the dba_registry_sqlpatch view, verify the Status for the ROLLBACK is SUCCESS.

For any other status, refer to My Oracle Support document 2335899.2 Troubleshooting Assistant: 12c Datapatch Issues for additional information and actions.

Check the following log files in $ORACLE_BASE/cfgtoollogs/sqlpatch/36582781/<unique patch ID> for errors:
36582781_rollback_<database SID>_<CDB name>_<timestamp>.log

where database SID is the database SID, CDB name is the name of the multitenant container database, and timestamp is of the form YYYYMMMDD_HH_MM_SS.

Any (pluggable) database that has invalid objects after the execution of datapatch should have catcon.pl run to revalidate those objects. For example:
cd $ORACLE_HOME/rdbms/admin

$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -n 1 -e -b utlrp -d $ORACLE_HOME/rdbms/admin utlrp.sql

4.2.2 Upgrade Oracle Recovery Manager Catalog

If you are using the Oracle Recovery Manager, the catalog needs to be upgraded. Enter the following command to upgrade it. The UPGRADE CATALOG command must be entered twice to confirm the upgrade.

$ rman catalog username/password@alias

RMAN> UPGRADE CATALOG;

RMAN> EXIT;

4.3 Patch Deinstallation Instructions for Oracle RAC Environment

Patch deinstallation instructions for Oracle RAC includes these environments:

Follow these steps for each node in the cluster, one node at a time.

Shut down the instance on the node.
Run the OPatch utility specifying the rollback argument as follows.
opatch rollback -id 36582781

If there are errors, refer to Section 5, "Known Issues".

Start the instance on the node as follows:
srvctl start instance

4.4 Patch Post-Deinstallation Instructions for Oracle RAC Environment

On Oracle Database server, follow the instructions listed in Section Section 4.2, "Patch Post-Deinstallation Instructions for a Non-RAC Environment" only on the node for which the steps in Section 3.3.2, "Load Modified SQL Files into the Database" were executed during the patch application. This is not required on Oracle Database client.

All other instances can be started and accessed as usual while you are executing the deinstallation steps.

5 Known Issues

For information about OPatch issues, see My Oracle Support document 293369.1 Primary Note For OPatch.

For issues documented after the release of this patch, see My Oracle Support document 19202407.9 Oracle DB/GI/OJVM Update/Revision R19 Jul 2024 Known Issues

Other issues are as follows.

Issue 1

The following ignorable errors may be encountered while running the datapatch/catbundle.sql script or its rollback script:

ORA-00942: table or view does not exist

ORA-00955: name is already used by an existing object

ORA-01430: column being added already exists in table

ORA-01432: public synonym to be dropped does not exist

ORA-01434: private synonym to be dropped does not exist

ORA-01435: user does not exist

ORA-01917: user or role 'XDB' does not exist

ORA-01920: user name '<user-name>' conflicts with another user or role name

ORA-01921: role name '<role name>' conflicts with another user or role name

ORA-01927: cannot REVOKE privileges you did not grant

ORA-01952: system privileges not granted to 'WKSYS'

ORA-02289: sequence does not exist

ORA-02303: cannot drop or replace a type with type or table dependents

ORA-02443: Cannot drop constraint - nonexistent constraint

ORA-04043: object <object-name> does not exist

ORA-06512: at line <line number>. If this error follow any of above errors, then can be safely ignored.

ORA-14452: attempt to create, alter or drop an index on temporary table already in use

ORA-29809: cannot drop an operator with dependent objects

ORA-29830: operator does not exist

ORA-29832: cannot drop or replace an indextype with dependent indexes

ORA-29844: duplicate operator name specified

ORA-29931: specified association does not exist

6 References

The following documents are references for this patch.

Document 36582781.8 Database Release Update 19.24.0.0.240716

Document 1919.2 19c Database Upgrade - Self Guided Assistance with Best Practices

Document 888.1 Primary Note for Database Proactive Patch Program

Document 1585822.1 Datapatch: Database 12c or later Post Patch SQL Automation

Document 293369.1 Primary Note for OPatch

Document 412160.1 Primary Note DST FAQ: Updated DST Transitions and New Time Zones in Oracle RDBMS and OJVM Time Zone File Patches

Document 1321267.1 Database Patch Conflict Resolution

Document 2523220.1 Database 19 Release Updates and Revisions Bugs Fixed Lists

Document 1561792.2 Troubleshooting Assistant: Patching Oracle Database/Client

7 Bugs Fixed by This Patch

See My Oracle Support document 2523220.1 Database 19 Release Updates and Revisions Bugs Fixed Lists for the list of bugs fixed in this patch.

8 Documentation Accessibility

For information about Oracle's commitment to accessibility, visit the Oracle Accessibility Program website at http://www.oracle.com/pls/topic/lookup?ctx=acc&id-docacc.

Access to Oracle Support

Oracle customers that have purchased support have access to electronic support through My Oracle Support. For information, visit http://www.oracle.com/pls/topic/lookup?ctx=acc&id=info or visit http://www.oracle.com/pls/topic/lookup?ctx=acc&id=trs if you are hearing impaired.

Patch 36582781 - Database Release Update 19.24.0.0.240716

Thursday, August 1, 2024

tar backup- ORACLE -Linux

As a standard practice, DBAs often need to take backups of the ORACLE_HOME, GRID_HOME, or Application tier before applying patches.

This precaution ensures that in the event of a patch failure, we can easily roll back the changes.

To avoid any permission issues during this process,

I consistently create a tarball backup of the binary files using the following command:

To Create tar backup of ORACLE_HOME(19c)

My ORACLE_HOME installed on below location

ORACLE_HOME=/u01/app/oracle/product/db12.2

ORACLE_HOME=/u01/app/oracle/product/19.0.0/db_1

cd /u01/app/oracle/product/19.0.0

tar -cvzf /backup/dbhome.tar.gz db_1

tar -cvzf backup_ORACLE_HOME_$(date +%F).tar.gz $ORACLE_HOME

This command archives and compresses the ORACLE_HOME directory into a tar.gz file, appending the current date to the filename for easy identification and retrieval.

untar

tar -xvzf /backup/dbhome.tar.gz -C /u01/app/oracle/product/19.0.0/db_1

Note:- the -C argument is used to specify the path to place the file.

It always recommended taking backup using root user, so that all permission will be reserved.

Oracle WebLogic Server Stack Patch Bundle 12.2.1.4.240710

Oracle WebLogic Server Stack Patch Bundle 12.2.1.4.240710

Stack Patch Bundle (SPB): 36822778

Platform Patch for : Generic Platform

Product Patched : Oracle WebLogic Server

Product Version : 12.2.1.4.0

This document describes how to install patch 36822778.

Section 1: Introduction

The Oracle WebLogic Server (WLS) Stack Patch Bundle (SPB) provides a collection of required patches released quarterly with the Critical Patch Update program.

This WLS SPB is applicable to the following distributions:

WLS 12.2.1.4 Generic
WLS 12.2.1.4 Slim
WLS 12.2.1.4 Lite
FMW 12.2.1.4 Infrastructure

Note: The WebLogic Server SPB is supported only for products installed from the Oracle Fusion Middleware Infrastructure distribution or standalone WebLogic Server distributions. The SPB will only update the components listed in Section 2. Patches for other components included in the FMW Infrastructure distribution must be applied separately using the OPatch napply method. If any other Oracle product installs an FMW Infrastructure, then the SPBAT would not be supported. An example of this is Oracle Enterprise Manager Cloud Control 13c.

1.1 Installation Options

In this SPB release you now have two options to apply the patches. Review the following to determine which patching option to use:

If you are running Linux, Solaris or Windows, then you can use the Stack Patch Bundle Automation Tool (SPBAT) patching option. This tool applies the patches in the SPB, but can also perform a precheck of the environment before patching to ensure that your WebLogic environment meets requirements. SPBAT will also update the OPatch version of the WebLogic environment (if necessary). After completing the required prerequisites, refer to Section 4.1 for more information about using the SPBAT.

Use OPatch napply to install the bundled patches. This is recommended for other platforms or when you need more control over the process. Note the SPBAT can also be run in a precheck phase before upgrading OPatch or running opatch napply. If you prefer to use the OPatch napply method, complete the required prerequisites and then follow the procedures in Section 4.2.

1.2 Additional SPB Program Information

Refer to the following document for more program information, including a Webcast demonstrating how to apply the SPB:

Doc ID 2764636.1 Introducing the Stack Patch Bundle (SPB) with SPBAT Utility for Oracle WebLogic Server

https://support.oracle.com/epmos/faces/DocumentDisplay?id=2764636.1

Section 2: System Overview

Oracle WebLogic Stack Patch Bundle (SPB) contains patches applicable for the following supported platforms:

Linux x86-64
Solaris x86-64
Solaris Sparc
IBM AIX 64
Windows 64
HPUX-IA64
IBM Linux on System Z

Patches included in this SPB are listed below. Note that each patch has its own README that contains a list of the bugs fixed.

Note: As mentioned above, the WebLogic Server SPB is supported only for products installed from the Oracle Fusion Middleware Infrastructure distribution or standalone WebLogic Server distributions. If any other Oracle product installs an FMW Infrastructure, then the SPBAT would not be supported and patches would need to be applied using the OPatch napply method. An example of this is Oracle Enterprise Manager Cloud Control 13c.

Serial Number	Patch Name	Patch Tracking Number	Comments
1	WLS STACK PATCH BUNDLE 12.2.1.4.240710 (PATCH 36822778) (INTERIM PATCH 36822782)	36822804	This patch allows for the Stack Patch Bundle to be included in the OPatch lsinventory output.
2	ADR FOR WEBLOGIC SERVER 12.2.1.4.0 - SIZE OPTIMIZED FOR JAN 2024	35965629	Applicable to Generic and Infrastructure distributions only.
3	WLS PATCH SET UPDATE 12.2.1.4.240704	36805124	Applicable to Generic and Infrastructure distributions only.
4	WEBLOGIC SAMPLES SPU 12.2.1.4.240416	36426672	Applicable to Generic and Infrastructure distributions only.
5	Coherence 12.2.1.4 Cumulative Patch 22 (12.2.1.4.22)	36725924	Applicable to all distributions (lspatches shows patch number as 1221421).
6	FMW PLATFORM 12.2.1.4.0 SPU FOR APRCPU2021	33093748
7	FMW Thirdparty Bundle Patch 12.2.1.4.240625	36770738
8	OPATCH 13.9.4.2.16 FOR EM 13.4 AND FMW/WLS 12.2.1.3.0, 12.2.1.4.0 AND 14.1.1.0.0	28186730	If you are using the opatch napply method, then you will need to manually upgrade OPatch. This is a separate upgrade applicable to all distributions.
9	JDBC 19.3.0.0 FOR CPUAPR2022 (WLS 12.2.1.4, WLS 14.1.1)	32720458
10	RDA release 24.3-2024716 for FMW 12.2.1.4.0	36553894

If you are using WebLogic Server Proxy Plug-In for IIS or Apache, download and apply the latest patches from the following link::

Doc ID 2806740.2, Critical Patch Update (CPU) Patch Advisor for Oracle Fusion Middleware Patch WLS Proxy Plug-In for Third-Party Web Servers

Note: The updated WLS Proxy Plug-In for Oracle HTTP Server (mod_wl_ohs) is included in the latest OHS Bundle Patch for your version.

Section 3: Prerequisites

Follow these required steps before applying the SPB:

3.1 Stage and verify the SPB zip file
3.2 Check Java version
3.3 Create a full backup
3.4 Check OPatch version
3.5 Stop all processes for WebLogic Server
3.6 Disable all anti-virus software

3.1 Stage and Verify SPB Zip File

Check if Patch 23335292 exists in the ORACLE_HOME and rollback the patch before an OPatch upgrade.
You can get the list of patches by executing lspatches: /OPatch/opatch lspatches

If patch 23335292 exists, rollback the patch by executing the following command: /OPatch/opatch rollback -id 23335292

Note: You should wait approximately 15-30 seconds before running any opatch commands. The rollback of Patch 23335292 should not be executed after the upgrade.

Download the p36822778_122140_Generic.zip from My Oracle Support and decide where you want to extract the SPB.

The location must be outside of your Oracle home.
The location should have 4GB free disk space.
The user should have read, write, and execute rights on the filesystem.

Extract the contents of the SPB download on the system you will be applying the patch.

On Windows, use the jar utility to avoid issues with the native zip program:

JDK_HOME\bin\jar -xvf p36822778_122140_Generic.zip
On UNIX platforms, use unzip to preserve permissions:

unzip p36822778_122140_Generic.zip

After extracting the patch, you should have a WLS_SPB_12.2.1.4.<VERSION> directory with the following contents:

Note: Do not make any post-extraction changes to this directory structure.

WLS_SPB_12.2.1.4.<VERSION>/

Navigate to the SPBAT directory and verify it contains the following directory structure:

Note: The default logs directory is created after running the SPBAT (shown below for reference).

WLS_SPB_12.2.1.4.<VERSION>/tools/spbat/generic/SPBAT

3.2 Check for Minimum Java Version

There is a minimum update level of Java SE (JDK/JRE) requirement before installing the WLS SPB.

Included in this Stack Patch Bundle, certain fixes in the WebLogic Server PSU for deserialization vulnerabilities depend on JEP 290 filtering and JEP 290 global scope filtering features provided in July 2018 JDK Updates and later JDK updates. These fixes for deserialization vulnerabilities are not effective without these JDK updates. Users applying this SPB/PSU without these JDK updates will receive warnings in the WebLogic Server console and in WebLogic Server logs. Refer to the following document for details:

Doc ID 2421487.1 Restricting Incoming Serialized Java Objects to Oracle WebLogic Server

Refer to the following table for minimum and recommended Java SE versions:

Platform	JDK Vendor	Major JDK Version	Expected Minimum Version	Latest Recommended Version
Windows 64	Oracle	1.8.0	1.8.0_211+	Java SE 8u421
Linux x86-64	Oracle	1.8.0	1.8.0_211+	Java SE 8u421
Solaris x86-64	Oracle	1.8.0	1.8.0_211+	Java SE 8u421
Solaris Sparc	Oracle	1.8.0	1.8.0_211+	Java SE 8u421
IBM AIX 64	IBM	1.8.0	1.8.0 SR6 FP5+	Obtain from vendor
IBM Linux on System Z	IBM	1.8.0	1.8.0 SR6 FP5+	Obtain from vendor
HPUX-IA64	HP	1.8.0	1.8.0.19+	Obtain from vendor

Refer the following document to verify and upgrade to relevant JDK:

Doc ID 1492980.1 on How to Install and Maintain the Java SE Installed or Used with FMW 11g/12c/14.1.1 Products

3.3 Create a Complete Backup

Create a complete backup of DOMAIN_HOME, ORACLE_HOME and CENTRAL INVENTORY using standard backup software/utilities.

IMPORTANT: In some cases restoring from backup is the only way to roll back the patches. See Section 6 for additional rollback options.

Refer the following Backing Up Your Environment for Oracle recommended backup strategies.

tar -cvf WLS_HOME_BACKUP.tar $ORACLE_HOME

3.4 Check OPatch Version

The Critical Patch Update program for July 2024 includes OPatch 13.9.4.2.16. The following should be noted about this version:

The WebLogic Server Patch Set Update requires OPatch 13.9.4.2.13 or later in order to successfully apply new updates.
The SPBAT script requires OPatch to be at 13.9.4.2.13 or later when performing prerequisite checks (precheck phase).
OPatch will be upgraded if running SPBAT -phase apply during downtime, see "4.1.3 Applying the SPB Patches with SPBAT"

If running the optional SPBAT -phase precheck without being on at least OPatch 13.9.4.2.13:

Unzip OPatch (bundled as part of WLS SPB) to a temporary location outside of your Oracle home.

Example for UNIX:

unzip WLS_SPB_<RELEASE>.<VERSION>/tools/opatch/generic/<VERSION>_Generic.zip -d /tmp/opatch
Refer to the OPatch Readme file for instructions on upgrading OPatch in the ORACLE_HOME.

In general, the command is:

[Linux/UNIX] java -jar <PATCH_HOME>/6880880/opatch_generic.jar -silent oracle_home=<ORACLE_HOME>

[Windows] java -jar <PATCH_HOME>\6880880\opatch_generic.jar -silent oracle_home=<ORACLE_HOME>

Where <PATCH_HOME> is the directory you used to extract the <VERSION>_Generic.zip.
To verify the OPatch version, run the following command:

ORACLE_HOME/OPatch/opatch version

Note: OPatch will be upgraded to 13.9.4.2.16 from any version if only running SPBAT -phase apply. To avoid unexpected issues, ensure all prerequisites are met in your environment.

3.5 Stop All Processes for WebLogic Server

Stop all WebLogic Server processes before proceeding with the patch installation.

3.6 Disable all anti-virus software

On Windows, anti-virus software should be temporarily stopped while applying patches in order to avoid problems copying files.

Section 4: Patch Installation Steps

As noted in the Introduction, there are two ways to apply the SPB. Review the following descriptions and then proceed to the designated section for detailed instructions:

Using SPBAT (Linux, Solaris, and Windows only) SPBAT will help to make the patch installation easier by validating the patch target environment before patching (precheck phase) and then installing the patches (apply phase). The tool can also update your OPatch version if the existing OPatch version is not compatible with the new patches in the SPB. SPBAT can also be run in precheck-only phase, so you can prepare in advance for applying the patches. If you would like to use SPBAT, continue to Section 4.1.

Using OPatch napply (all platforms) If you would like to continue to use the OPatch napply method to apply the SPB, continue to Section 4.2.

4.1 Using SPBAT (Linux, Solaris, and Windows only)

4.1.1 SPBAT Requirements

Log in and run commands as the OS user that installed Oracle WebLogic Server.
As noted in the prerequisites, OPatch 13.9.4.2.13 is the minimum required version to run the SPBAT precheck.
Verify that the JAVA_HOME setting in the ORACLE_HOME/oui/.globalEnv.properties is where you have installed your Java SE (JDK/JRE).

Note: SPBAT will not allow a location within the ORACLE_HOME, see Doc ID 1492980.1 for updating your Java SE.

4.1.2 SPBAT Patching Process Overview

Once the prerequisite steps from Section 3 are completed, the steps to apply the SPB patches using SPBAT include the following:

Run precheck phase (optional)
Stop services
Run precheck and apply phase during downtime
Restart services
Complete validation

4.1.3 Applying the SPB Patches with SPBAT

SPBAT Precheck Phase

Follow the steps below to check prerequisites in the WLS 12.2.1.4 environment during uptime or downtime without an intention of making changes. This requires OPatch 13.9.4.2.13, see the section, "3.4 Check OPatch Version".

Run the SPBAT utility with appropriate options for the optional precheck-only phase:

a. Navigate to the SPBAT directory:

WLS_SPB_<VERSION>/tools/spbat/generic/SPBAT

b. Run the SPBAT utility for the precheck phase:

[Linux/UNIX] ./spbat.sh -phase precheck -oracle_home <ORACLE_HOME>

[Windows] spbat.bat -phase precheck -oracle_home <ORACLE_HOME>

c. Review the standard out reports before proceeding with the next phase.

If there are any issues that need to be addressed, you can run the precheck phase again.
Repeat these steps on each node in a multi-node environment.
To monitor the progress during execution on Windows platform, open a new terminal and monitor the log files as required.

SPBAT Apply Phase

When you are ready to apply updates during downtime, follow the steps below to upgrade OPatch, check prerequisites, and apply patches.

Stop all services and ensure that there are no Java processes running on this Oracle home.
Navigate to the SPBAT directory:

WLS_SPB_<VERSION>/tools/spbat/generic/SPBAT

Run the SPBAT utility for the apply phase:

[Linux/UNIX] ./spbat.sh -phase apply -oracle_home <ORACLE_HOME>

[Windows] spbat.bat -phase apply -oracle_home <ORACLE_HOME>

Where:

-phase: The various phases of applying the patch, valid values are precheck and apply.

-oracle_home: The directory path to Oracle home.

-log_dir: (optional) The default directory is WLS_SPB_<VERSION>/tools/spbat/generic/SPBAT/logs or you can set your own location. The directory should already exist and be writable. Windows users should specify a custom log location with a shorter path in order to prevent long path issues known to Windows platforms.

-skip_acl (optional - Windows platform only): True or False. True will preserve permissions in ORACLE_HOME whereas False will override ACL settings if any.

Review the standard out reports before proceeding with the next phase.

If there are any issues that need to be addressed, you can run the precheck phase again.
Repeat these steps on each node in a multi-node environment.
To monitor the progress during execution on Windows platform, open a new terminal and monitor the log files as required.

Complete the post installation tasks in Section 5.

4.2 Using OPatch napply (All Platforms)

If Patch 23335292 was applied to OPatch 13.9.4.2.13, it should be rolled back before upgrading OPatch:

Check if Patch 23335292 exists in the Inventory: /OPatch/opatch lspatches

If patch 23335292 exists, rollback the patch by executing the following command: /OPatch/opatch rollback -id 23335292

Note:

You should wait approximately 15-30 seconds before running any opatch commands.
The rollback of Patch 23335292 should not be executed after the upgrade.

Unzip OPatch (bundled as part of WLS SPB) to a temporary location outside of your Oracle home.

   Example for UNIX:

   unzip WLS_SPB_<RELEASE>.<VERSION>/tools/opatch/generic/<VERSION>_Generic.zip -d /tmp/opatch
Refer to the OPatch Readme.txt for instructions on upgrading OPatch in the ORACLE_HOME. In general the command is:

[Linux/UNIX] java -jar <PATCH_HOME>/6880880/opatch_generic.jar -silent oracle_home=<ORACLE_HOME>

[Windows] java -jar <PATCH_HOME>\6880880\opatch_generic.jar -silent oracle_home=<ORACLE_HOME>

Where <PATCH_HOME> is the directory you used to extract the <VERSION>_Generic.zip.

4.2.1 OPatch napply Requirements

Log in and run commands as the OS user that installed Oracle WebLogic Server.
Unset the JAVA_HOME and ORACLE_HOME environment variables.
Verify that the JAVA_HOME setting in the ORACLE_HOME/oui/.globalEnv.properties is where you have installed your Java SE (JDK/JRE).

Note: To prevent OPatch from entering into self-patching mode, and to avoid inventory corruption issues, ensure that no OPatch operations are consuming a JDK/JRE located inside the ORACLE_HOME. See Doc ID 1492980.1 for updating your Java SE installation.

4.2.2 OPatch napply - UNIX Platform Steps

(Optional) Run OPatch with the -report flag to produce a log of patches that will be applied, but does not actually update anything:

a. Navigate to the binary patches directory:

WLS_SPB_12.2.1.4.<VERSION>/binary_patches

b. Run OPatch with the -report flag to produce a log to review:

ORACLE_HOME/OPatch/opatch napply -report -oh <ORACLE_HOME> -phBaseFile <patch_list_file>

When composing the command line, make sure the -phBaseFile value corresponds to your specific UNIX operating platform.

Note: Use generic_patchlist.txt to apply generic patches only in case the install platform is different from the list detailed in Section 2 of this document.

Here is an example of the command for UNIX:

ORACLE_HOME/OPatch/opatch napply -report -oh <ORACLE_HOME> -phBaseFile linux64_patchlist.txt

The patchlist files are bundled into the SPB and you do not need to provide a path to the file.
Once the opatch napply -report command is executed, review the output of the command and the logs it produces to be aware of any possible issues.
No changes have been made to your Oracle home even though the command output may say that patches were successfully applied.

2. Run OPatch to install the patches included in the SPB:

a. Navigate to the binary patches directory:

WLS_SPB_12.2.1.4.<VERSION>/binary_patches

b. Run opatch napply:

ORACLE_HOME/OPatch/opatch napply -oh <ORACLE_HOME> -phBaseFile <patch_list_file>

When composing the command line, make sure the -phBaseFile value corresponds to your specific UNIX operating platform.

Note: Use generic_patchlist.txt to apply generic patches only if the install platform is different from the list detailed in Section 2 of this document.

Here is an example of the command for UNIX:

ORACLE_HOME/OPatch/opatch napply -oh <ORACLE_HOME> -phBaseFile linux64_patchlist.txt

The patchlist files are bundled into the SPB, so you do not need to provide a path to the file.
Once the opatch napply command is executed, review the output of the command and the logs it produces to be aware of any possible issues.
Changes have been made to your Oracle home if this command was successful.

4.2.3 OPatch napply - Windows Platform Steps

Open the command prompt as Administrator to execute the following steps:

(Optional) Run OPatch with the -report flag to produce a log to review:

a. Navigate to the binary patches directory:

WLS_SPB_12.2.1.4.<VERSION>\binary_patches

b. Run opatch napply:

ORACLE_HOME\OPatch\opatch napply -report -oh <ORACLE_HOME> -phBaseFile windows64_patchlist.txt

The windows64_patchlist.txt file is bundled into the SPB, so you do not need to provide a path to the file.
Once the opatch napply -report command is executed, review the output of the command and the log files it produces to be aware of any possible issues.
No changes have been made to your Oracle home even though the command output may say that patches were successfully applied.

2. Run OPatch to install the patches included in the SPB:

a. Navigate to the binary patches directory:

WLS_SPB_12.2.1.4.<VERSION>/binary_patches

b. Run opatch napply:

ORACLE_HOME\OPatch\opatch napply -oh <ORACLE_HOME> -phBaseFile windows64_patchlist.txt

The windows64_patchlist.txt file is bundled into the SPB and you do not need to provide a path to the file.
Once the opatch napply command is executed, review the output of the command and the log files it produces to be aware of any possible issues.
Changes have been made to your Oracle home if this command is successful.

Section 5: Post Installation Steps

Verify SPB Patches in ORACLE_HOME inventory.

ORACLE_HOME/OPatch/opatch lspatches

Note: Refer to the SPB patch list under Section 2 to understand the list of expected patches in the lspatches output. For example, the most popular generic installation will have a minimum of the WLS PSU, Coherence and ADR patches. Other components may not be installed, and, in some cases, patching will be skipped.

Refer to the following document for important post-installation information:

Doc ID 2764668.1 for Security Advice and Post-Install Information for Oracle WebLogic Server PSUs
https://support.oracle.com/epmos/faces/DocumentDisplay?id=2764668.1
Start all WebLogic Servers.

Section 6: Rollback Steps

For a complete rollback, use the backups (created before the application of SPB) to restore the ORACLE_HOME and CENTRAL INVENTORY.

If OPatch was upgraded and you need to roll back to the very beginning, then you must restore from backup.
If it is confirmed that the OPatch upgrade was successful, and you only need to roll back the SPB patches, then use the following steps:

If you have applied any patches to resolve conflicts since applying the SPB, first ensure these overlay patches are rolled back first.

Use the following commands to roll back all patches included in the SPB:

a. Navigate to the binary patches directory:

WLS_SPB_12.2.1.4.<VERSION>/binary_patches

b. Run the opatch nrollback command:

ORACLE_HOME/OPatch/opatch nrollback -oh <ORACLE_HOME> -idFile rollback_patchlist.txt

This command will rollback all patches included with the past and present Stack Patch Bundles. If any patch is not present, OPatch will return the patch is "not present in the Oracle Home".

OPatch will re-activate any previously superseded patch (e.g., the WLS PSU). Any previously applied overlay patches will not be re-applied. Unless the goal is to apply a newer SPB or PSU, in order to get back to an original state, you must re-apply any overlay patches you previously had applied with the WLS PSU.

IMPORTANT: Oracle recommends that you do not roll back individual patches. Doing so can impact the integrity of the other patches. Note that some patches are specific to the SPB only.