wiki:update_oracle_database_on_windows

Download the required patches to perform update

Latest versions can be found on the following page https://support.oracle.com/knowledge/Oracle%20Cloud/2521164_1.html

Windows Bundle Patch - R19x Database Release Update Revision, column "Included in Windows Bundle"
OJVM - R19x OJVM Release Update

Note: Windows patches release date might be delayed of other patches. You can find real release dates on the following link: https://dbsguru.com/patching/
Once patches numbers are noted you can download from the support pagc:\source https://support.oracle.com

Install patches on Windows server

Extract the zipped files in a folder
Install the Windows Database bundle patch
Install OJVM

Installation of the Windows Database bundle patch:

Check if there are any invalid packages

Start CMD as administrator

sqlplus /nolog

SQL> connect as sysdba

Enter user-namc:\source sys Enter password:* Connected

SQL> set line 2000

SQL> select owner, object_type, object_name,status from dba_objects where status != 'VALID' order by owner, object_type;

no rows selected

SQL> exit

Check if there is any conflict with any installed patch

Start CMD as administrator Navigate to the folder where you extracted the Windows Bundle Patch and run the command to check for any conflict from that folder

cd c:\source\35962832

c:\source\35962832>C:\oracle\product\19.3.0\dbhome_1\Opatch\opatch prereq CheckConflictAgainstOHWithDetail -ph ./
Oracle Interim Patch Installer version 12.2.0.1.41
Copyright (c) 2024, Oracle Corporation.  All rights reserved.

PREREQ session
.
.
.
Prereq "checkConflictAgainstOHWithDetail" failed.

The details are:
Reason -
Superset Patch 35962832 has
Subset Patch 35681552 which has overlay patches [35648110] and these overlay patches conflict with Superset Patch
.
.
.
Patch : 35962832

        Conflict with 35648110
        Conflict details:
        C:\oracle\product\19.3.0\dbhome_1\bin\oracle.exe
        C:\oracle\product\19.3.0\dbhome_1\rdbms\admin\oracle.sym
.
.
.
OPatch succeeded.

Note the patch number that has conflict with the one that you want to apply and run the following command to rollback the conflicting patch

C:\oracle\product\19.3.0\dbhome_1\OPatch\opatch rollback -id 35648110
Oracle Interim Patch Installer version 12.2.0.1.41
Copyright (c) 2024, Oracle Corporation.  All rights reserved.


Oracle Home       : C:\oracle\product\19.3.0\dbhome_1
Central Inventory : C:\Program Files\Oracle\Inventory
   from           :
OPatch version    : 12.2.0.1.41
OUI version       : 12.2.0.7.0
Log file location : C:\oracle\product\19.3.0\dbhome_1\cfgtoollogs\opatch\opatch2024-02-01_09-39-04AM_1.log


Patches will be rolled back in the following order:
   35648110
The following patch(es) will be rolled back: 35648110

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = 'C:\oracle\product\19.3.0\dbhome_1')

Is the local system ready for patching? [y|n]

Enter “Y” and press enter

User Responded with: Y

Rolling back patch 35648110...

RollbackSession rolling back interim patch '35648110' from OH 'C:\oracle\product\19.3.0\dbhome_1'

Patching component oracle.rdbms, 19.0.0.0.0...

Patching component oracle.javavm.server.core, 19.0.0.0.0...

Patching component oracle.rdbms.dbscripts, 19.0.0.0.0...

Patching component oracle.javavm.server, 19.0.0.0.0...

Patching component oracle.javavm.client, 19.0.0.0.0...
RollbackSession removing interim patch '35648110' from inventory
Log file location: C:\oracle\product\19.3.0\dbhome_1\cfgtoollogs\opatch\opatch2024-02-01_09-39-04AM_1.log

OPatch succeeded.

Check that the ORACLE_HOME variable is defined and that the PATH variable contains %ORACLE_HOME%\perl\bin

echo %ORACLE_HOME%
C:\oracle\product\19.3.0\dbhome_1

c:\source\35962832>echo %PATH%
C:\oracle\product\19.3.0\dbhome_1\bin;C:\Install_Oracle19c\bin;C:\Windows\system32;C:\Windows;C:\Windows\System32\Wbem;C:\Windows\System32\WindowsPowerShell\v1.0\;C:\oracle\product\19.3.0\dbhome_1\perl\bin

If not present, type the following:

set ORACLE_HOME=C:\oracle\product\19.3.0\dbhome_1

set PATH=%ORACLE_HOME%\perl\bin;%PATH%

Stop all Oracle Services from the services.msc console
Navigate to the extracted patch folder and run following command:

cd c:\source\35962832

c:\source\35962832>C:\oracle\product\19.3.0\dbhome_1\OPatch\opatch apply
Oracle Interim Patch Installer version 12.2.0.1.41
Copyright (c) 2024, Oracle Corporation.  All rights reserved.


Oracle Home       : C:\oracle\product\19.3.0\dbhome_1
Central Inventory : C:\Program Files\Oracle\Inventory
   from           :
OPatch version    : 12.2.0.1.41
OUI version       : 12.2.0.7.0
Log file location : C:\oracle\product\19.3.0\dbhome_1\cfgtoollogs\opatch\opatch2024-02-01_09-45-37AM_1.log

Verifying environment and performing prerequisite checks...
OPatch continues with these patches:   35962832

Do you want to proceed? [y|n]

Type “Y” and press Enter

 User Responded with: Y
All checks passed.

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = 'C:\oracle\product\19.3.0\dbhome_1')


Is the local system ready for patching? [y|n]

Type “Y” and press Enter

User Responded with: Y
Backing up files...
Applying interim patch '35962832' to OH 'C:\oracle\product\19.3.0\dbhome_1'
.
.
.
Patch 35962832 successfully applied.
Sub-set patch [35681552] has become inactive due to the application of a super-set patch [35962832].
Please refer to Doc ID 2161861.1 for any possible further required actions.
Log file location: C:\oracle\product\19.3.0\dbhome_1\cfgtoollogs\opatch\opatch2024-02-01_09-45-37AM_1.log

OPatch succeeded.

Start the Oracle services from the services.msc console
Post installation datapatch, navigate to the Oracle installation folder and type the followig command:

c:\source\35962832>cd %ORACLE_HOME%\OPatch

C:\oracle\product\19.3.0\dbhome_1\OPatch>datapatch -verbose
SQL Patching tool version 19.22.0.0.0 Production on Thu Feb  1 10:14:49 2024
Copyright (c) 2012, 2024, Oracle.  All rights reserved.

Log file for this invocation: C:\oracle\cfgtoollogs\sqlpatch\sqlpatch_1756_2024_02_01_10_14_49\sqlpatch_invocation.log

Connecting to database...OK
.
.
.
Validating logfiles...done
Patch 35648110 rollback: SUCCESS
  logfilc:\source C:\oracle\cfgtoollogs\sqlpatch\35648110\25431514/35648110_rollback_SLMTST_2024Feb01_10_17_22.log (no errors)
Patch 35962832 apply: SUCCESS
  logfilc:\source C:\oracle\cfgtoollogs\sqlpatch\35962832\25463642/35962832_apply_SLMTST_2024Feb01_10_17_22.log (no errors)
SQL Patching tool complete on Thu Feb  1 10:18:00 2024

Navigate to the rdbms\admin folder and recompile object using the following command:

cd %ORACLE_HOME%\rdbms\admin

C:\oracle\product\19.3.0\dbhome_1\rdbms\admin>sqlplus /nolog

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Feb 1 10:19:07 2024
Version 19.22.0.0.0

Copyright (c) 1982, 2023, Oracle.  All rights reserved.

SQL> connect as sysdba
Enter user-namc:\source sys
Enter password:
Connected.

SQL> @.\utlrp.sql

Session altered.


TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN              2024-02-01 10:19:25

DOC>   The following PL/SQL block invokes UTL_RECOMP to recompile invalid
.
.
.
ERRORS DURING RECOMPILATION
---------------------------
                          0


Function created.


PL/SQL procedure successfully completed.


Function dropped.


PL/SQL procedure successfully completed.

SQL> select username,status,logon_time From V$session where status='ACTIVE' order by username;

USERNAME
--------------------------------------------------------------------------------
STATUS   LOGON_TIM
-------- ---------
SYS
ACTIVE   01-FEB-24
.
.
.
48 rows selected.

SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.22.0.0.0

Check that patch has been successfully installed

C:\oracle\product\19.3.0\dbhome_1\rdbms\admin>cd C:\oracle\product\19.3.0\dbhome_1\Opatch

C:\oracle\product\19.3.0\dbhome_1\OPatch>opatch lspatches
35962832;Windows Database Bundle Patch : 19.22.0.0.240116 (35962832)

Installation of the OJVM patch:
Note: Make sure that the Windows Database bundle patch has been successfully installed before proceeding with OJVM patch

Check that the ORACLE_HOME variable is defined and that the PATH variable contains %ORACLE_HOME%\perl\bin

echo %ORACLE_HOME%
C:\oracle\product\19.3.0\dbhome_1

c:\source\35962832>echo %PATH%
C:\oracle\product\19.3.0\dbhome_1\bin;C:\Install_Oracle19c\bin;C:\Windows\system32;C:\Windows;C:\Windows\System32\Wbem;C:\Windows\System32\WindowsPowerShell\v1.0\;C:\oracle\product\19.3.0\dbhome_1\perl\bin

If not present, type the following:

set ORACLE_HOME=C:\oracle\product\19.3.0\dbhome_1

set PATH=%ORACLE_HOME%\perl\bin;%PATH%

Navigate to the extracted patch folder and check for any conflict with other already installed patches:

C:\Windows\system32>cd C:\source\35926646

C:\source\35926646>C:\oracle\product\19.3.0\dbhome_1\OPatch\opatch prereq CheckConflictAgainstOHWithDetail -ph ./
Oracle Interim Patch Installer version 12.2.0.1.41
Copyright (c) 2024, Oracle Corporation.  All rights reserved.

PREREQ session

Oracle Home       : C:\oracle\product\19.3.0\dbhome_1
Central Inventory : C:\Program Files\Oracle\Inventory
   from           :
OPatch version    : 12.2.0.1.41
OUI version       : 12.2.0.7.0
Log file location : C:\oracle\product\19.3.0\dbhome_1\cfgtoollogs\opatch\opatch2024-02-01_10-24-17AM_1.log

Invoking prereq "checkconflictagainstohwithdetail"

Prereq "checkConflictAgainstOHWithDetail" passed.

OPatch succeeded.

Apply the patch:

c:\source\35926646>C:\oracle\product\19.3.0\dbhome_1\OPatch\opatch apply
Oracle Interim Patch Installer version 12.2.0.1.41
Copyright (c) 2024, Oracle Corporation.  All rights reserved.


Oracle Home       : C:\oracle\product\19.3.0\dbhome_1
Central Inventory : C:\Program Files\Oracle\Inventory
   from           :
OPatch version    : 12.2.0.1.41
OUI version       : 12.2.0.7.0
Log file location : C:\oracle\product\19.3.0\dbhome_1\cfgtoollogs\opatch\opatch2024-02-01_10-27-16AM_1.log

Verifying environment and performing prerequisite checks...
OPatch continues with these patches:   35926646

Do you want to proceed? [y|n]

Confirm with “Y” and press Enter

User Responded with: Y
All checks passed.

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = 'C:\oracle\product\19.3.0\dbhome_1')


Is the local system ready for patching? [y|n]

Confirm with “Y” and press Enter

User Responded with: Y
Backing up files...
Applying interim patch '35926646' to OH 'C:\oracle\product\19.3.0\dbhome_1'

Patching component oracle.rdbms, 19.0.0.0.0...

Patching component oracle.javavm.server.core, 19.0.0.0.0...

Patching component oracle.rdbms.dbscripts, 19.0.0.0.0...

Patching component oracle.javavm.server, 19.0.0.0.0...

Patching component oracle.javavm.client, 19.0.0.0.0...
Patch 35926646 successfully applied.
Log file location: C:\oracle\product\19.3.0\dbhome_1\cfgtoollogs\opatch\opatch2024-02-01_10-27-16AM_1.log

OPatch succeeded.

Start all Oracle services
Post installation startup for datapatch

sqlplus /nolog

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Feb 1 10:37:19 2024
Version 19.22.0.0.0

Copyright (c) 1982, 2023, Oracle.  All rights reserved.

SQL> connect as sysdba
Enter user-name: sys
Enter password:
Connected.

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

SQL> startup upgrade
ORACLE instance started.

Total System Global Area 2768238136 bytes
Fixed Size                  9078328 bytes
Variable Size            1308622848 bytes
Database Buffers         1442840576 bytes
Redo Buffers                7696384 bytes
Database mounted.
Database opened.

SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.22.0.0.0

cd %ORACLE_HOME%\OPatch

C:\oracle\product\19.3.0\dbhome_1\OPatch>datapatch -verbose
SQL Patching tool version 19.22.0.0.0 Production on Thu Feb  1 10:41:09 2024
Copyright (c) 2012, 2024, Oracle.  All rights reserved.

Log file for this invocation: C:\oracle\cfgtoollogs\sqlpatch\sqlpatch_7552_2024_02_01_10_41_09\sqlpatch_invocation.log

Connecting to database...OK
.
.
.
Installing patches...
Patch installation complete.  Total patches installed: 1

Validating logfiles...done
Patch 35926646 apply: SUCCESS
  logfile: C:\oracle\cfgtoollogs\sqlpatch\35926646\25549578/35926646_apply_SLMTST_2024Feb01_10_42_00.log (no errors)
SQL Patching tool complete on Thu Feb  1 10:42:01 2024

C:\oracle\product\19.3.0\dbhome_1\OPatch>sqlplus /nolog

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Feb 1 10:42:08 2024
Version 19.22.0.0.0

Copyright (c) 1982, 2023, Oracle.  All rights reserved.

SQL> connect as sysdba
Enter user-name: sys
Enter password:
Connected.

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

SQL> startup
ORACLE instance started.

Total System Global Area 2768238136 bytes
Fixed Size                  9078328 bytes
Variable Size            1308622848 bytes
Database Buffers         1442840576 bytes
Redo Buffers                7696384 bytes
Database mounted.
Database opened.

SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.22.0.0.0

Navigate to the rdbms\admin folder and recomplie object

C:\oracle\product\19.3.0\dbhome_1\OPatch>cd %ORACLE_HOME%\rdbms\admin

C:\oracle\product\19.3.0\dbhome_1\rdbms\admin>sqlplus /nolog

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Feb 1 10:44:32 2024
Version 19.22.0.0.0

Copyright (c) 1982, 2023, Oracle.  All rights reserved.

SQL> connect as sysdba
Enter user-name: sys
Enter password:
Connected.

SQL> @.\utlrp.sql

Session altered.


TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN              2024-02-01 10:44:53

DOC>   The following PL/SQL block invokes UTL_RECOMP to recompile invalid
.
.
.
ERRORS DURING RECOMPILATION
---------------------------
                          0


Function created.


PL/SQL procedure successfully completed.


Function dropped.


PL/SQL procedure successfully completed.

SQL> select owner, object_type, object_name,status from dba_objects where status != 'VALID' order by owner, object_type;

no rows selected

Check the active connections

SQL> select username,status,logon_time From V$session where status='ACTIVE' order by username;