Patch 34935010 - DATABASE MRP 19.17.0.0.230117

 

Issues while applying Oracle 19.17.0 MRP [34935010]

#################################################################

 

Error:

[oracle@localhost 34935010]$ opatch apply /home/oracle/34935010/ -oh /opt/oracle/product/19c/dbhome_1

Oracle Interim Patch Installer version 12.2.0.1.36

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

 

 

ZOP-51: The patch location is not valid for apply, because it doesn't have correct metadata, or it points to a patch directory.

Argument(s) Error... Patch location is not valid for apply

 

Please check the arguments and try again.

 

OPatch failed with error code 135

 

Reason For this Error:

 

Instead of “opatch”, We should use “opatchauto” to apply the patch.

 

Command:

 opatchauto apply -binary /home/oracle/34935010/ -oh /opt/oracle/product/19c/dbhome_1 -target_type oracle_database

 

Sample Upgrade Output:

[oracle@localhost 34935010]$ opatchauto apply -binary /home/oracle/34935010/ -oh /opt/oracle/product/19c/dbhome_1 -target_type oracle_database

Oracle Home : /opt/oracle/product/19c/dbhome_1

 

OPatchAuto binary patching Tool

Copyright (c)2014, Oracle Corporation. All rights reserved.

 

OPatchauto Version : 13.9.5.0.0

Running from       : /opt/oracle/product/19c/dbhome_1

 

opatchauto log file: /opt/oracle/product/19c/dbhome_1/cfgtoollogs/opatchauto/opatchauto_2023-01-24_03-18-28_binary.log

 

Target type : oracle_database

 

Patch selected: /home/oracle/34935010

 

 

Analysing this list of patches :

[/home/oracle/34935010/33838019, /home/oracle/34935010/34938232, /home/oracle/34935010/34545238, /home/oracle/34935010/34724125, /home/oracle/34935010/34366627, /home/oracle/34935010/34538232, /home/oracle/34935010/34333986, /home/oracle/34935010/34715072, /home/oracle/34935010/32295794, /home/oracle/34935010/34816203, /home/oracle/34935010/34574048, /home/oracle/34935010/34953559] ...

 

Analysis completed.

 

Applying the patches ...

 

 

Patches successfully applied.

 

 

 

 

==Following patches were SUCCESSFULLY applied:

 

Patch: /home/oracle/34935010/32295794

Log: /opt/oracle/product/19c/dbhome_1/cfgtoollogs/opatchauto/core/opatch/opatch2023-01-24_03-18-30AM_1.log

 

Patch: /home/oracle/34935010/33838019

Log: /opt/oracle/product/19c/dbhome_1/cfgtoollogs/opatchauto/core/opatch/opatch2023-01-24_03-18-30AM_1.log

 

Patch: /home/oracle/34935010/34333986

Log: /opt/oracle/product/19c/dbhome_1/cfgtoollogs/opatchauto/core/opatch/opatch2023-01-24_03-18-30AM_1.log

 

Patch: /home/oracle/34935010/34366627

Log: /opt/oracle/product/19c/dbhome_1/cfgtoollogs/opatchauto/core/opatch/opatch2023-01-24_03-18-30AM_1.log

 

Patch: /home/oracle/34935010/34538232

Log: /opt/oracle/product/19c/dbhome_1/cfgtoollogs/opatchauto/core/opatch/opatch2023-01-24_03-18-30AM_1.log

 

Patch: /home/oracle/34935010/34545238

Log: /opt/oracle/product/19c/dbhome_1/cfgtoollogs/opatchauto/core/opatch/opatch2023-01-24_03-18-30AM_1.log

 

Patch: /home/oracle/34935010/34574048

Log: /opt/oracle/product/19c/dbhome_1/cfgtoollogs/opatchauto/core/opatch/opatch2023-01-24_03-18-30AM_1.log

 

Patch: /home/oracle/34935010/34715072

Log: /opt/oracle/product/19c/dbhome_1/cfgtoollogs/opatchauto/core/opatch/opatch2023-01-24_03-18-30AM_1.log

 

Patch: /home/oracle/34935010/34724125

Log: /opt/oracle/product/19c/dbhome_1/cfgtoollogs/opatchauto/core/opatch/opatch2023-01-24_03-18-30AM_1.log

 

Patch: /home/oracle/34935010/34816203

Log: /opt/oracle/product/19c/dbhome_1/cfgtoollogs/opatchauto/core/opatch/opatch2023-01-24_03-18-30AM_1.log

 

Patch: /home/oracle/34935010/34938232

Log: /opt/oracle/product/19c/dbhome_1/cfgtoollogs/opatchauto/core/opatch/opatch2023-01-24_03-18-30AM_1.log

 

Patch: /home/oracle/34935010/34953559

Log: /opt/oracle/product/19c/dbhome_1/cfgtoollogs/opatchauto/core/opatch/opatch2023-01-24_03-18-30AM_1.log

 

 

opatchauto SUCCEEDED.

 

 

[oracle@localhost 34935010]$

[oracle@localhost 34935010]$ datapatch

SQL Patching tool version 19.17.0.0.0 Production on Tue Jan 24 03:30:01 2023

Copyright (c) 2012, 2022, Oracle.  All rights reserved.

 

Log file for this invocation: /opt/oracle/cfgtoollogs/sqlpatch/sqlpatch_12121_2023_01_24_03_30_01/sqlpatch_invocation.log

 

Connecting to database...OK

Gathering database info...done

 

Note:  Datapatch will only apply or rollback SQL fixes for PDBs

       that are in an open state, no patches will be applied to closed PDBs.

       Please refer to Note: Datapatch: Database 12c Post Patch SQL Automation

       (Doc ID 1585822.1)

 

Bootstrapping registry and package to current versions...done

Determining current state...done

 

Current state of interim SQL patches:

  No interim patches found

 

Current state of release update SQL patches:

  Binary registry:

    19.17.0.0.0 Release_Update 220924224051: Installed

  PDB CDB$ROOT:

    Applied 19.17.0.0.0 Release_Update 220924224051 successfully on 19-JAN-23 04.56.50.910268 AM

  PDB ORCLPDB1:

    Applied 19.17.0.0.0 Release_Update 220924224051 successfully on 23-JAN-23 07.19.12.154816 AM

  PDB PDB$SEED:

    Applied 19.17.0.0.0 Release_Update 220924224051 successfully on 19-JAN-23 04.56.55.596701 AM

 

Adding patches to installation queue and performing prereq checks...done

Installation queue:

  For the following PDBs: CDB$ROOT PDB$SEED ORCLPDB1

    No interim patches need to be rolled back

    No release update patches need to be installed

    No interim patches need to be applied

 

 

Undo Retention and undo tablespace size calculation [To Resolve ora-0555 issues]

SQL Query find the current actual undo size and undo retention ================================================================ SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]", SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]", ROUND((d.undo_size / (to_number(f.value) * g.undo_block_per_sec))) "OPTIMAL UNDO RETENTION [Sec]" FROM ( SELECT SUM(a.bytes) undo_size FROM v$datafile a, v$tablespace b, dba_tablespaces c WHERE c.contents = 'UNDO' AND c.status = 'ONLINE' AND b.name = c.tablespace_name AND a.ts# = b.ts# ) d, v$parameter e, v$parameter f, ( SELECT MAX(undoblks/((end_time-begin_time)*3600*24)) undo_block_per_sec FROM v$undostat 21 ) g WHERE e.name = 'undo_retention' AND f.name = 'db_block_size' / Sample output ============== ACTUAL UNDO SIZE [MByte] UNDO RETENTION [Sec] OPTIMAL UNDO RETENTION [Sec] ------------------------ ---------------------------------------------------------------------------------------------------- ---------------------------- 180812.922 3600 69354 SQL Query to find the required undo size and undo retention ================================================================ SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]", SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]", (TO_NUMBER(e.value) * TO_NUMBER(f.value) * g.undo_block_per_sec) / (1024*1024) "NEEDED UNDO SIZE [MByte]" 6 FROM ( SELECT SUM(a.bytes) undo_size FROM v$datafile a, v$tablespace b, dba_tablespaces c WHERE c.contents = 'UNDO' AND c.status = 'ONLINE' AND b.name = c.tablespace_name AND a.ts# = b.ts# ) d, v$parameter e, v$parameter f, ( SELECT MAX(undoblks/((end_time-begin_time)*3600*24)) undo_block_per_sec FROM v$undostat ) g WHERE e.name = 'undo_retention' AND f.name = 'db_block_size' / Sample output ============== ACTUAL UNDO SIZE [MByte] UNDO RETENTION [Sec] NEEDED UNDO SIZE [MByte] ------------------------ ---------------------------------------------------------------------------------------------------- ------------------------ 180812.922 3600 9385.5

Difference output while querying systimestamp within server and from client

Difference result while querying systimestamp within server and from client

Issue: -

The below query is different data if the query executes within the server and from outside of the server (through client).

select systimestamp from dual;

systimestamp, sysdate give the server date and time.

Reason: -

Cluster and database installed in the server if different time zone and the time zone changed later in the server. Cluster keeps the time zone information at the time of installation.

If the time zone changed, we have to update the same information in the cluster.

1)Edit the file in “s_crsconfig__env.txt” in $GRID_HOME/ crs/install.

Change the TZ value accordingly.

Example to change the timezone from Malaysia to India,

TZ=Asia/Kuala_Lumpur  (Old Value)

TZ=Asia/Kolkatta (New Value)

2)Update the information through srvctl in crs file.

srvctl setenv database -d -T TZ=Asia/Kolkatta

3)Restart the cluster

nls_session_parameters give different output sql devloper server

The output of nls_session_parameters parameter is different  in sql developer and server. Its because of  default parameter configuration in sql developer.

Server:-

Sql Developer:-

Tools ->Preferences->Database->NLS