Differences

This shows you the differences between two versions of the page.

--- wiki:update_oracle_database_on_windows_server [2024/01/19 14:30] – created wagner.jer
+++ wiki:update_oracle_database_on_windows_server [2025/05/06 08:22] (current) – wagner.jer
@@ Line 1: / Line 1: @@
   * __**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
+ 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
-Once patches numbers are noted you can download from the support page: https://support.oracle.com\\
+**//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**__\\
@@ Line 13: / Line 14: @@
   * Check if there are any invalid packages
 Start CMD as administrator
-<cli type=dos>sqlplus /nolog
+<cli type=dos>sqlplus /nolog</cli>
-SQL> connect as sysdba
+<cli type=dos>SQL> connect as sysdba</cli>
-Enter user-name: sys
+Enter user-namc:\source sys
 Enter password:*****
 Connected
-SQL> set line 2000
+<cli type=dos>SQL> set line 2000</cli>
-SQL> select owner, object_type, object_name,status from dba_objects where status != 'VALID' order by owner, object_type;</cli>
+<cli type=dos>SQL> select owner, object_type, object_name,status from dba_objects where status != 'VALID' order by owner, object_type;
+no rows selected</cli>
+<cli type=dos>SQL> exit</cli>
+  * 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
+<cli type=dos>cd c:\source\35962832</cli>
+<cli type=dos>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.</cli>
+  * 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
+<cli type=dos>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]</cli>
+  * Enter "Y" and press enter
+<cli type=dos>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.</cli>
+  * Check that the ORACLE_HOME variable is defined and that the PATH variable contains %ORACLE_HOME%\perl\bin
+<cli type=dos>echo %ORACLE_HOME%
+C:\oracle\product\19.3.0\dbhome_1</cli>
+<cli type=dos>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</cli>
+  * If not present, type the following:
+<cli type=dos>set ORACLE_HOME=C:\oracle\product\19.3.0\dbhome_1</cli>
+<cli type=dos>set PATH=%ORACLE_HOME%\perl\bin;%PATH%</cli>
+  * Stop all Oracle Services from the services.msc console
+  * Navigate to the extracted patch folder and run following command:
+<cli type=dos>cd c:\source\35962832</cli>
+<cli type=dos>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]</cli>
+  * Type "Y" and press Enter
+<cli type=dos> 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]</cli>
+  * Type "Y" and press Enter
+<cli type=dos>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.</cli>
+  * Start the Oracle services from the services.msc console
+  * Post installation datapatch, navigate to the Oracle installation folder and type the followig command:
+<cli type=dos>c:\source\35962832>cd %ORACLE_HOME%\OPatch</cli>
+<cli type=dos>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</cli>
+  * Navigate to the rdbms\admin folder and recompile object using the following command:
+<cli type=dos>cd %ORACLE_HOME%\rdbms\admin</cli>
+<cli type=dos>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.</cli>
+<cli type=dos>SQL> connect as sysdba
+Enter user-namc:\source sys
+Enter password:
+Connected.</cli>
+<cli type=dos>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
+---------------------------
+
+Function created.
+PL/SQL procedure successfully completed.
+Function dropped.
+PL/SQL procedure successfully completed.
+</cli>
+<cli type=dos>
+SQL> select username,status,logon_time From V$session where status='ACTIVE' order by username;
+USERNAME
+--------------------------------------------------------------------------------
+STATUS   LOGON_TIM
+-------- ---------
+SYS
+ACTIVE   01-FEB-24
+.
+.
+.
+rows selected.
+</cli>
+<cli type=dos>SQL> exit
+Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
+Version 19.22.0.0.0</cli>
+  * Check that patch has been successfully installed
+<cli type=dos>C:\oracle\product\19.3.0\dbhome_1\rdbms\admin>cd C:\oracle\product\19.3.0\dbhome_1\Opatch</cli>
+<cli type=dos>C:\oracle\product\19.3.0\dbhome_1\OPatch>opatch lspatches
+35962832;Windows Database Bundle Patch : 19.22.0.0.240116 (35962832)</cli>
+\\
+\\
+//**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
+<cli type=dos>echo %ORACLE_HOME%
+C:\oracle\product\19.3.0\dbhome_1</cli>
+<cli type=dos>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</cli>
+  * If not present, type the following:
+<cli type=dos>set ORACLE_HOME=C:\oracle\product\19.3.0\dbhome_1</cli>
+<cli type=dos>set PATH=%ORACLE_HOME%\perl\bin;%PATH%</cli>
+  * Navigate to the extracted patch folder and check for any conflict with other already installed patches:
+<cli type=dos>C:\Windows\system32>cd C:\source\35926646</cli>
+<cli type=dos>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.
+</cli>
+  * Apply the patch:
+<cli type=dos>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]</cli>
+  * Confirm with "Y" and press Enter
+<cli type=dos>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]</cli>
+  * Confirm with "Y" and press Enter
+<cli type=dos>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.</cli>
+  * Start all Oracle services
+  * Post installation startup for datapatch
+<cli type=dos>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.
+</cli>
+<cli type=dos>SQL> connect as sysdba
+Enter user-name: sys
+Enter password:
+Connected.</cli>
+<cli type=dos>SQL> shutdown immediate
+Database closed.
+Database dismounted.
+ORACLE instance shut down.</cli>
+<cli type=dos>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.</cli>
+<cli type=dos>SQL> exit
+Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
+Version 19.22.0.0.0</cli>
+<cli type=dos>cd %ORACLE_HOME%\OPatch</cli>
+<cli type=dos>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
+</cli>
+<cli type=dos>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.
+</cli>
+<cli type=dos>SQL> connect as sysdba
+Enter user-name: sys
+Enter password:
+Connected.</cli>
+<cli type=dos>SQL> shutdown immediate
+Database closed.
+Database dismounted.
+ORACLE instance shut down.</cli>
+<cli type=dos>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.</cli>
+<cli type=dos>SQL> exit
+Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
+Version 19.22.0.0.0</cli>
+  * Navigate to the rdbms\admin folder and recomplie object
+<cli type=dos>C:\oracle\product\19.3.0\dbhome_1\OPatch>cd %ORACLE_HOME%\rdbms\admin</cli>
+<cli type=dos>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.
+</cli>
+<cli type=dos>SQL> connect as sysdba
+Enter user-name: sys
+Enter password:
+Connected.</cli>
+<cli type=dos>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
+---------------------------
+
+Function created.
+PL/SQL procedure successfully completed.
+Function dropped.
+PL/SQL procedure successfully completed.</cli>
+<cli type=dos>SQL> select owner, object_type, object_name,status from dba_objects where status != 'VALID' order by owner, object_type;
+no rows selected</cli>
+  * Check the active connections
+  * <cli type=dos>SQL> select username,status,logon_time From V$session where status='ACTIVE' order by username;</cli>