Opatch failed with "Make failed to invoke "/usr/bin/make -f ins_net_client.mk client_sharedlib"

I encountered a patch failure while upgrading to Oracle 19.23. I received "Opatch session completed with warnings".

The error clearly indicates that the root cause is a missing "shrept.lst" file.

Make failed to invoke "/usr/bin/make -f ins_net_client.mk client_sharedlib ORACLE_HOME=/oracle/product/19.x.0.0/dbhome_1 OPATCH_SESSION=apply"....'genclntsh: genclntsh: Could not locate /oracle/product/19.x.0.0/dbhome_1/network/admin/shrept.lst

make: *** [ins_net_client.mk:143: client_sharedlib] Error 1
'
Make failed to invoke "/usr/bin/make -f ins_rdbms.mk client_sharedlib ORACLE_HOME=/oracle/product/19.x.0.0/dbhome_1 OPATCH_SESSION=apply"....'genclntsh: genclntsh: Could not locate /oracle/product/19.x.0.0/dbhome_1/network/admin/shrept.lst
make: *** [ins_rdbms.mk:56: client_sharedlib] Error 1
'
Make failed to invoke "/usr/bin/make -f ins_ldap.mk ldapsearch ORACLE_HOME=/oracle/product/19.x.0.0/dbhome_1 OPATCH_SESSION=apply"....'genclntsh: genclntsh: Could not locate /oracle/product/19.x.0.0/dbhome_1/network/admin/shrept.lst

make: *** [/oracle/product/19.x.0.0/dbhome_1/ldap/lib/env_ldap.mk:2474: /oracle/product/19.x.0.0/dbhome_1/lib/libclntsh.so] Error 1
'
The following make actions have failed :

Re-link fails on target "client_sharedlib".
Re-link fails on target "client_sharedlib".
Re-link fails on target "ldapsearch".

Do you want to proceed? [y|n]
y
User Responded with: Y
Patch 36233263 successfully applied.
Sub-set patch [34765931] has become inactive due to the application of a super-set patch [36233263].
Please refer to Doc ID 2161861.1 for any possible further required actions.
OPatch Session completed with warnings.
Log file location: /oracle/product/19.x.0.0/dbhome_1/cfgtoollogs/opatch/opatch2024-08-24_22-25-17PM_1.log

I have reviewed the opatch log file to verify the error and I can see the the same error.

[Aug 24, 2024 11:32:40 PM] [INFO] Deleted the file "/oracle/product/19.x.0.0/dbhome_1/.patch_storage/unzip_action/1723357551241/inventory/Templates/perl/bin/zipdetails"

[Aug 24, 2024 11:32:40 PM] [INFO] --------------------------------------------------------------------------------
[Aug 24, 2024 11:32:40 PM] [INFO] The following warnings have occurred during OPatch execution:
[Aug 24, 2024 11:32:40 PM] [INFO] 1) OUI-67200:Make failed to invoke "/usr/bin/make -f ins_net_client.mk client_sharedlib ORACLE_HOME=/oracle/product/19.x.0.0/dbhome_1 OPATCH_SESSION=apply"....'genclntsh: genclntsh: Could not locate /oracle/product/19.x.0.0/dbhome_1/network/admin/shrept.lst
make: *** [ins_net_client.mk:143: client_sharedlib] Error 1
'
[Aug 24, 2024 11:32:40 PM] [INFO] 4) OUI-67124:Re-link fails on target "client_sharedlib".
Re-link fails on target "client_sharedlib".
Re-link fails on target "ldapsearch".
[Aug 24, 2024 11:32:40 PM] [INFO] --------------------------------------------------------------------------------
[Aug 24, 2024 11:32:40 PM] [SEVERE] OUI-67008:OPatch Session completed with warnings.
[Aug 24, 2024 11:32:40 PM] [INFO] Finishing UtilSession at Sat Aug 24 23:32:40 PDT 2024
[Aug 24, 2024 11:32:40 PM] [INFO] Log file location: /oracle/product/19.x.0.0/dbhome_1/cfgtoollogs/opatch/opatch2024-08-24_22-25-44PM_1.log

we found that shrept.lst file was missing and have rollback the patch using
$ opatch rollback -id <xxxxxx>

We copied the shrept.lst file from another environment, re-applied the patch using "opatch apply," and the process completed successfully without any issues.

Thanks & Regards

https://oracleracexpert.com

Bugs in Oracle Apex 23.2 and ORDS 23.4

I have recently installed Apex 23.2, ORDS 23.4 for POC purpose and come across below issues when working with APEX and ORDS.

Bug: Oracle Apex 23.2 with Interactive Grid

When working with Interactive Grid, the filters saved in the Interactive Grid not displaying correct values. The issue happens intermittently, and filter values change from date to string or filter operators may drop out of the list.

When users working with operators they see inconsistency in the values, which is a big concern.

After research I found that its known issue ang filed bug #36200437

For more detailed info please check below Oracle forum link

BUG in APEX 23.2 Interactive Grid: Multiple Criteria Filter Issue - Oracle Forums

Bug: ORDS 23.4 with PL/SQL

Please see the cause and action for error PLS-00306: wrong number or types of arguments in call to 'string”

Cause: 
This error occurs when the named subprogram call cannot be matched to any declaration for that subprogram name. The subprogram name might be misspelled, a parameter might have the wrong datatype, the declaration might be faulty, or the declaration might be placed incorrectly in the block structure. For example, this error occurs if the built-in square root function SQRT is called with a misspelled name or with a parameter of the wrong datatype.

Action: 
Check the spelling and declaration of the subprogram name. Also confirm that its call is correct, its parameters are of the right datatype, and, if it is not a built-in function, that its declaration is placed correctly in the block structure.

But in my case when working with PL/SQL code users receiving “PLS-00306: wrong number or types of arguments in call”. The same code works after some time, after research found that it’s a known bug.

For more details, please see the below Oracle forums link.

ORDS 23.4 with PL/SQL. PLS-00306: wrong number or types of arguments in call to - Oracle Forums

Hope this helps.

Thanks & Regards.

Satishbabu Gunukula, Oracle ACE Pro

https://oracleracexpert.com

UN World Innovation Day: Igniting Change through Creativity and Collaboration

Whenever there is a global challenge, the Innovation has always played an important role to solving challenges. Every year the United Nations celebrates Innovation Day on the 21st of April to recognizes the importance of innovation in harnessing the economic potential of nations. As world faces unprecedented crises in health, education, the future of work and economy, it is important for us to find innovative solutions together

The United Nations (UN) has set forth 17 Sustainable Development Goals (SDGs) to tackle wide range of global challenges by 2030 which includes climate change, poverty, eradication and beyond. We will explore how Generative Artificial Intelligence (AI) can be used to achieve of these goals for better future.

                           

The UN World Innovation Day Hack 2023 is not just another hackathon; it's a unique opportunity to collaborate with encouraging individuals to creatively utilize their skills to generate new ideas and solutions for various critical areas. These include health and wellbeing, economic growth, decent work, and quality education. Hackathon brings together creative minds from around the world to generate innovative solutions for pressing issues. By bringing together a diverse group of talented individuals, the hackathon helps to provide meaningful solutions to pressing challenges.

Hackathon gives an opportunity to connect with new people from various backgrounds, experiences and expertise come together to collaborate, share ideas, and push the boundaries of innovation. You will have the opportunity to learn from each other, explore, generate innovative solutions, and network with inspiring individual beyond the event.

This year's hackathon introduces two brand new challenges:

• GPT4Good
• Data4Good

These challenges can address crucial areas where innovation can make a significant impact.

In the "GPT4Good" challenge, participants will explore the potential of GPT (Generative Pre-trained Transformer) models to address social, environmental, and humanitarian issues. The GPT is a cutting-edge artificial intelligence technology, has the power to generate human-like text, making it a valuable tool for various applications. From enhancing accessibility to fostering sustainable practices, participants will harness the power of GPT to create innovative solutions that contribute to the greater good.

The "Data4Good" challenge focuses on leveraging the power of data to drive positive change. With the proliferation of data in our digital age, there is a tremendous opportunity to extract insights and make informed decisions that benefit society. To address critical issues such as participants will work with diverse datasets, apply data analytics, visualization, and machine learning techniques to address critical issues. Participants will be encouraged to explore how data can be ethically collected, analyzed, and utilized to address pressing global issues.

The UN World Innovation Day Hack 2023 event schedule, spanning from May12th to May 15th, promises an exhilarating journey filled with creativity, collaboration, and problem-solving.

The UN World Innovation Day Hack 2023 is made possible through collaboration with Hackmakers, a global community of changemakers and innovators, joins hands with sponsors such as Oracle, AI4Diversity, Polygon, Unstoppable Domains, Buzzy and Wand to support this event. These organizations bring their expertise, resources, and mentorship to provide participants with the necessary tools to transform their ideas into tangible solutions.

The Hackathons bring over 1000+ participants and mentors around the world. In this blog post, I express my deep gratitude to all the mentors who generously contribute their time, expertise, and support to guide teams and projects during these hackathons as they play essential role in making these events successful.

To find answers to common queries and gain a better understanding of the hackathon logistics visit the official hackathon FAQ page at https://www.worldinnovationday.com/faq .

To access valuable resources and guides visit resources page at https://www.worldinnovationday.com/resources. It will provide necessary tools and knowledge to overcome challenges and push the boundaries of innovation.

To access Challenge and Sponsored resources details, visit the challenge resource page
https://www.worldinnovationday.com/challenge-resources.

Join on this transformative journey, meet incredible individuals, test your skills, and deliver meaningful impacts. Together, let's unleash the potential of innovation and create a world that thrives on collaboration, inclusivity, and positive change.

Conclusion: The UN World Innovation Day Hack 2023 is a global celebration of creativity, collaboration, and innovation. As an official Mentor for this event, I am honored to be part of a community to address pressing global challenges through technology and creative thinking. As we work together, let us continue to inspire, guide, and empower the next generation of innovators for better future.

#WorldInnovationDay #Hackathon #GptForGood #DataForGood #Innovation #Collaboration

Regards

Satishbabu G, Oracle ACE Pro

https://oracleracexpert.com

ORA-20011: Approximate NDV failed: ORA-08103: object no longer exists

The ORA-20011, can be found in alert.log and this error occurs when DBMS_STATS:GATHER_STATS_JOB gathering stats

ORA-20011: Approximate NDV failed: ORA-08103: object no longer exists

You identify the issue set the following trace events
SQL> alter session set events '10384 trace name context forever , level 16384';

After research found that object truncate and starts are gathering at same time. In case of partition set granularity to ‘PARTITION’

DB level
SQL> exec dbms_stats.set_global_prefs(pname=>'GRANULARITY',pvalue=>'PARTITION')

Table level
SQL> exec dbms_stats.set_table_prefs(ownname=>'Owner_Name',tabname=>'<Table_Name>',pname=>'GRANULARITY',pvalue=>'PARTITION')

Run the gather stats after DDL or DML have been completed.

Also note that “ORA-08103 object no longer exists” error occurs when another user has deleted object since the operation began. In case of incomplete recovery of the object, the work around would be to remove references to the object or delete the object.

When using Global temporary table with ON COMMIT DELETE ROWS options, may encounter ORA-08103: object no longer exists when commit statement that followed right after the delete statement.

In this case recreation of global temporary table with ON COMMIT PRESERVE ROWS clause will helps to safely fetch data

There are several bugs related to ORA-8103 error, check oracle support for more details.

Bug 13618170

Bug 5523799
Bug 5637976

Thanks & Regards,

http://oracleracexpert.com, Oracle ACE

ORA-39346: data loss in character set conversion for object TABLE_STATISTICS

When doing export/import using data pump encountered below error. This happens when invalid or corrupt characters are stored in the database.

When using export/import instead of data pump, no errors reported.

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
ORA-39346: data loss in character set conversion for object TABLE_STATISTICS:"ORCL"."TABLE1"
ORA-39346: data loss in character set conversion for object TABLE_STATISTICS:"ORCL"."TABLEMAT"
Job "SYS"."SYS_IMPORT_SCHEMA_01" completed with 2 error(s) at Sun Apr 11 12:05:25 2021 elapsed 0 00:20:13

After research found that this is a known issue due to Bug 13696958 .

The fix for Bug 13696958 

added warning messages in 12.1 datapump import for characters that couldn't be converted. Previously, a fix for Bug 16173664 was made available to report the affected object in the import log.

Workaround/fix:-

Needs to apply interim patch 21342624 
and execute post-install step:

$ datapatch

If the patch 21342624 installed then impdp log will report the objects that are experiencing data loss during characterset conversion. If the fix then character data is checked even when the client and database character sets are the same, to prevent input of invalid character data.

That being said the fix for Bug 21342624 does not stop the error messages or repair any bad data on the source database

In case of any further issues , patch questions or concerns, please contact Oracle Support.

Refer Oracle support notes: -

Unpublished Bug 21342624 - DATA LOSS DUE TO CHARACTER SET NOT SHOWING CORRECT SET OF OBJECTS DURING IMPORT.

Note 1317012.1 - How To Use MOS Patch Planner To Check And Request The Conflict Patches?

Thanks & Regards,
http://oracleracexpert.com, Oracle ACE

Oracle 19c Security New Features

This webinar is intended for database administrators (DBAs), security admins, developers, and others tasked with performing operations securely and efficiently.

Join the Webinar to learn New Security Features in Oracle 19c

Date and time: Dec 18th 2020 7:00am-8:00am
Pacific Daylight Time (San Francisco, GMT-07:00)

To register for this Webinar, please send an email to SatishbabuGunukula@gmail.com. Note that registrations are limited, first come and first serve basis only. You will receive an email confirmation with meeting session link.

For Presentation, link "Click here"

Thanks & Regards
http://www.oracleracexpert.com

ORA-27090: Unable to reserve kernel resources for asynchronous disk I/O

User may receive below error when connecting to Database, I have encountered this issue in various versions of Oracle such as 10g, 11g, 12c and 19c.

ERROR:
ORA-01017: invalid username/password; logon denied
ORA-27090: Unable to reserve kernel resources for asynchronous disk I/O
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Additional information: 128
Additional information: 2022083592

You may see below messages in alert.log

ORA-27090: Message 27090 not found; product=RDBMS; facility=ORA
Additional information: 3
Additional information: 128
Additional information: 65536
OR

ORA-27090: UNABLE TO RESERVE KERNEL RESOURCES FOR ASYNCHRONOUS DISK I/O
Additional information: 3
Additional information: 128
Additional information: 65536
OR

ORA-27090: Message 27090 not found; product=RDBMS; facility=ORA
Linux-x86_64 Error: 4: Interrupted system call
Additional information: 3
Additional information: 128
Additional information: 65536

DBWR trace
io_setup(4096, 0x66e1588) = -1 EAGAIN (Resource temporarily unavailable),

You will see above error when "aio-max-nr" kernel limit is too low.

Increasing the "aio-max-nr" kernel limit as per the Oracle recommendation. I have set the value as
fs.aio-max-nr= 3145728 in my environment.

Thanks

Satishbabu Gunukula, Oracle ACE

http://oracleracexpert.com

ORA-00600, ORA-07445 and ORA-00020 errors and related bugs in 12c (12.2.0.1)

I have come across below error recently in 12c (12.2.0.1). It first started with ORA-00600 followed by ORA-07445 and ORA-00020. The ORA-00600, ORA-07445 looks like real culprit and which lead to ORA-00020 error.

ORA-00600: internal error code, arguments: [17126], [0x0B0D29528], [], [], [], [], [], [], [], [], [], []
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
2020-01-13T23:20:30.493308-08:00
Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x32000000170] [PC:0x10F9D3FD, kghfnd_in_free_lists()+717] [flags: 0x0, count: 1]
Errors in file /home/oracle/diag/rdbms/ORADB/ORADB/trace/ORADB_ora_62437.trc (incident=41538):
ORA-07445: exception encountered: core dump [kghfnd_in_free_lists()+717] [SIGSEGV] [ADDR:0x32000000170] [PC:0x10F9D3FD] [Address not mapped to object] []
Incident details in: /home/oracle/diag/rdbms/ORADB/ORADB/incident/incdir_41538/ORADB_ora_62437_i41538.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
2020-01-13T23:20:31.182120-08:00
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
2020-01-13T23:20:31.182223-08:00
opidcl aborting process unknown ospid (48392) as a result of ORA-600
……

2020-01-14T01:29:37.222083-08:00
ORA-00020: maximum number of processes (500) exceeded
ORA-20 errors will not be written to the alert log for
the next minute. Please look at trace files to see all
the ORA-20 errors.
After investigation and troubleshooting I found below bugs

1. There is a unpublished bug related to Unified memory KGH related crashes/errors. After discussing with support come to know that particular fix was found on an internal note to already be included in Oracle 12.2.0.1

Bug 30053838 - LNX-20-ATP: HIT ORA-7445 [KGHFND_IN_FREE_LISTS] AND ORA-600 [KSM_PGA_UM_EXT_FREE:INVALID_EXT_MAGIC], INST CRASH

2. There is a published bug 24596874 and the fix fix for 24596874 is first included in 20.1.0 . Also in term patches may be available for earlier versions.

For bug 24596874 , users notice Memory corruption when CDB environment is setup with star schema on one PDB. Redaction policies enabled on tables/view of the schema and query workload executed with 50 concurrent sessions. Multiple ORA 600 [kghfrh:ds] were seen in the alert log.

So both of the above bugs are ruled out as unpublished bug already part of 12.2.0.1 and published bug is related to Star schema on PDB.

After further investigation with Oracle support found another unpublished Bug 30448845 : DATABASE HANG WITH SIGNATURE: 'PMON TIMER'<='PRIOR SPAWNER CLEAN UP'<='LATCH FREE'<='RELIABLE MESSAGE'

As per Oracle support, this is fixed in 18.1 but the fix is not easy to backports. Oracle suggest to update parameter _pmon_slaves_arr_size=0 as workaround in Oracle 12.2

Users cannot update internal oracle parameters directly in SPFILE, please follow the steps mentioned in the below blog post
https://www.oracleracexpert.com/2020/01/ora-00800-soft-external-error-arguments.html

Note that every environment, errors, load, use cases are different, I would highly recommend users to open a ticket with Oracle support and get confirmation before making any changes to your databases.
Regards
Satishbabu Gunukula, Oracle ACE
http://oracleracexpert.com 

ORA-00800: soft external error, arguments: [Set Priority Failed], [VKTM], [Check traces and OS configuration], [Check Oracle document and MOS notes]

When working with Oracle DB Version 19.5 recently noticed below errors in alert.log file

Starting background process VKTM
2020-02-04T17:05:58.711181-08:00
Errors in file /home/oracle/diag/rdbms/testdb/TESTDB/trace/TESTDB_vktm_43832.trc (incident=42521):
ORA-00800: soft external error, arguments: [Set Priority Failed], [VKTM], [Check traces and OS configuration], [Check Oracle document and MOS notes], []
Incident details in: /home/oracle/diag/rdbms/testdb/TESTDB/incident/incdir_42521/TESTDB_vktm_43832_i42521.trc
2020-02-04T17:05:58.713567-08:00
Error attempting to elevate VKTM's priority: no further priority changes will be attempted for this process
VKTM started with pid=5, OS id=43832

After research found that parameter _high_priority_processes="VKTM" may help, but it is a internally parameter you cannot able to apply directly on SPFILE. I always raise a ticket with Oracle support to got the confirmation before making changes.

This issue has been reported in the internal bug - Bug 30664554 : ORA-00800: SOFT EXTERNAL ERROR, ARGUMENTS: [SET PRIORITY FAILED], [VKTM]
In order to update internal parameters on a DB using SPFILE you need to follow bellows steps.

1. Take a backup of SPFILE and PFILE to revert changes in case of any isuses.
2. Create PFILE from SPFILE
SQL> create pfile=’$ORACLE_HOME/dbs/initTESTDB.ora’ from spfile
3. Add/update the parameter in PFILE
4. Rename SPFILE and stop/start the DB to use PFILE that was created
5. Create SPFILE from PFILE
SQL> create SPFILE=’$ORACLE_HOME/dbs/spfileTESTDB.ora’ from pfile;
6. stop/start the DB to use SPFILE that was created
Now the changes are applied to SPFILE.

Note that every environment, errors, load, use cases are different, I would highly recommend users to open a ticket with Oracle support and get confirmation before making any changes to your databases.
Thanks
Satishbabu Gunukula,ORACLE ACE
https://oracleracexpert.com

Oracle Database Upgrade to 12c and Available Methods

Oracle 12c offers many new features and upgrading database can bring many advantages to organization. There are various upgrade and migration methods available and the best method to use for your upgrade/migration scenario depends on the source database version, the source and destination operating systems, your downtime requirements, and the personal preference of the DBA. Based upon factors there is a method available to best fit your organization needs.

We have several upgrade methods available

• DBUA
• command line
• Full Transportable Export/Import
• Transportable Tablespaces
• Data Pump Export/Import
• Original Export/Import

For complete Article Please check below link

https://www.linkedin.com/pulse/oracle-database-upgrade-12c-available-methods-gunukula-oracle-ace

Regards
Satishbabu Gunukula, Oracle ACE
http://www.oracleracexpert.com

ORA-00600: internal error code, arguments: [kkdlron-max-objid], [4254950911], [4254950914]

When the database reached maximum object id or if the new object id exceeds the limit you will see below error. This situation normally happen in Data ware house databases, especially with SAP BW using Oracle as database.

< ORA-00600: internal error code, arguments: [kkdlron-max-objid], [4254950911], [4254950914], [], [], [], [], [], [], [], [], [] < ORA-00600: internal error code, arguments: [kewrose_1], [600]

You will get the max object id by running below commands.
SQL> select max(obj#) from SYS.obj$;
MAX(OBJ#)
----------
4254909633
SQL> SELECT dataobj# FROM sys.obj$ where name='_NEXT_OBJECT';
SQL> select max(object_id),max(DATA_OBJECT_ID) from dba_objects ;


Oracle Support Document 20529650.8 (Bug 20529650 - ORA-600 [kkdlron-max-objid], [4254950911]) can be found at: https://support.oracle.com/epmos/faces/DocumentDisplay?id=20529650.8

Also check SAP note : 2137109 - Data Object ID limit , if you see the issue on SAP related applications.

I see 2 options here
1. Logically rebuild the DB - Permanent fix
2. Apply the patch 20529650 - workaround/temporary fix

The option 2 applying the patch will brings us the new limit 4293950911 (4254950911+39000000). But keep in mind that this new limit can reach quickly in BW systems. Note that the patch will give team some time but the permanent solution is to rebuilding the database.

You can use below query to estimate how much time you might have , but note that this is just an estimate only.

Regards

Satishbabu Gunukula, Oracle ACE

http://www.oracleracexpert.com

Oracle 11g and 12c Latest Database or OJVM Patch Set Update or Release Update

I have received several emails related to Latest patch set, OJVM and security patch update for Oracle 11g and Oracle 12c versions. Here is the summarized copy of latest patches from Oracle support side

Please see the Blog post on “How to Apply Database or JVM Patch set update or release update”

If you still have any questions, I would always recommend to raise a ticket with Oracle support.

11g latest patch info

11.2.0.4

Patch 27734982 - Oracle Database Patch Set Update 11.2.0.4.180717
https://updates.oracle.com/Orion/Services/download?type=readme&aru=22229176

Patch 27923163: OJVM PATCH SET UPDATE 11.2.0.4.180717
https://updates.oracle.com/Orion/Services/download?type=readme&aru=22212880

Patch 27870645: DATABASE SECURITY PATCH UPDATE 11.2.0.4.180717
https://updates.oracle.com/Orion/Services/download?type=readme&aru=22229821

12c Latest Patch info
12.1.0.2
Patch 27547329 - Database Patch Set Update 12.1.0.2.180717
https://updates.oracle.com/Orion/Services/download?type=readme&aru=22280349

Patch 27923320: OJVM PATCH SET UPDATE 12.1.0.2.180717
https://updates.oracle.com/Orion/Services/download?type=readme&aru=22224206

12.2.0.1
Patch 28163133: DATABASE JUL 2018 RELEASE UPDATE 12.2.0.1.180717
https://updates.oracle.com/Orion/Services/download?type=readme&aru=22313390

Patch 27923353: OJVM RELEASE UPDATE 12.2.0.1.180717
https://updates.oracle.com/Orion/Services/download?type=readme&aru=22237223

Patch 27427077: DATABASE OCT 2017 RELEASE UPDATE REVISION 12.2.0.1.180417
https://updates.oracle.com/Orion/Services/download?type=readme&aru=22113335

Please review below master link for Oracle JVM component database PSU and update”

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

Thanks 

Satishbabu Gunukula, Oracle ACE

http://oracleracexpert.com

How to Apply Oracle Latest patch set, OJVM and security patch...etc

When planning for Oracle patches make sure all pre-requisites are met and required backups are in place. Please refer below link to find out the required patches for 11g and 12c.

Oracle 11g and 12c Latest Database or OJVM Patch Set Update or Release Update

Pre-requisites

1. Before you apply patch make sure you are using latest Opatch version.

$./opatch version
OPatch Version: 11.2.0.3.19
OPatch succeeded.

Downloaded the latest opatch for 12.2 version using the link - https://updates.oracle.com/ARULink/PatchDetails/process_form?patch_num=6880880

To apply latest Opatch follow the instructions
OPatch - Where Can I Find the Latest Version of OPatch(6880880)? [Video] (Doc ID 224346.1)


2. Download the required PSU or JVN or intern patch and unzip

3. Make sure there are no conflicts and run below script to find out the conflicts. If you see any conflicts the make sure you fix the reported conflicts.

$ opatch prereq CheckConflictAgainstOHWithDetail -ph ./

In case of any conflicts you will see the message like below

There are no patches that can be applied now.
Following patches have conflicts. Please contact Oracle Support and get the merged patch of the patches :xxxxxxx, xxxxxx

In case if there are no conflicts then you will see the message like below

Invoking prereq "checkconflictagainstohwithdetail"
Prereq” CheckConflictAgainstOHWithDetail” passed.
Opatch Succeeded.

4. Using the -report option to simulate the application of the interim patch or PSU

Using “-report” option with "opatch apply" to simulate the apply without updating any files. Using this option you no need to shutdown the databases and it can be up and running.

$ opatch apply -report
...
Skip patching component oracle.rdbms, 11.2.0.4.0 and its actions.
The actions are reported here, but are not performed.

ApplySession skipping inventory update.

Verifying the update...
Inventory and System verification is performed here.
Patch xxxxxx successfully applied
Log file location: /oracle/product/11.2.0.4/cfgtoollogs/opatch/12834800_May_02_2019_10_27_17/apply2019-05-12_10-27-17AM_1.log

OPatch succeeded

5. Run utlrp.sql to recompile any invalid objects
$ORACLE_HOME/rdbms/admin/utlrp.sql

6. Check Oracle Components are VALID or not. Please make sure you fix INVALID component.
If NOT required then you might have to remove INVALID components.

SQL> select substr(comp_name,1,30) comp_name, substr(comp_id,1,10)
comp_id,substr(version,1,12) version,status from dba_registry; 

COMP_NAME                                                    COMP_ID              VERSION         STATUS

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

Oracle Multimedia                                            ORDIM                11.2.0.4.0      VALID

Oracle XML Database                                          XDB                  11.2.0.4.0      VALID

Oracle Expression Filter                                     EXF                  11.2.0.4.0      VALID

Oracle Rules Manager                                         RUL                  11.2.0.4.0      VALID

Oracle Workspace Manager                                     OWM                  11.2.0.4.0      VALID

Oracle Database Catalog Views                                CATALOG              11.2.0.4.0      VALID

Oracle Database Packages and T                               CATPROC              11.2.0.4.0      VALID

JServer JAVA Virtual Machine                                 JAVAVM               11.2.0.4.0      VALID

Oracle XDK                                                   XML                  11.2.0.4.0      VALID

Oracle Database Java Packages                                CATJAVA              11.2.0.4.0      VALID

7. Make sure you have taken TAR of the ORACLE_HOME, in case of any issues

tar -cvf oracle_home_12c.tar 12.2.0.1

Applying interim patch or PSU or JVM

1. Stop the databases and listener

Sqlplus / as sysdba
Shutdown immediate
$lsnrctl stop LISTENER

2. Make sure you are in the patch directory and run the below command to apply the patch

$cd <patch number>
$ORACLE_HOME/OPatch/opatch apply

3. Run post patch script against each database

Sqlplus /as sysdba
startup
@catbundle.sql psu apply
quit

Starting version 12.1 we need to use datapatch to complete post patch actions upon any 12c or later database restart. The datapatch contains the logic to identify if any post-patch SQL actions are pending.

Sqlplus / as sysdba
Startup upgrade

$ cd $ORACLE_HOME/Opatch
./datapatch -verbose

-verbose
Output additional information used for debugging

After datapatch restart the database.
Sqlplus / as sysdba
Shutdown immediate
startup

Post-verification:-
1. After patching check the inventory, to make sure patch has been updated or not
$ORACLE_HOME/OPatch/opatch lsinventory

Oracle Interim Patch Installer version 11.2.0.3.19
Copyright (c) 2019, Oracle Corporation. All rights reserved.

Oracle Home : /oracle/product/11.2.0/db_1
Central Inventory : /u01/app/oraInventory
from : /oracle/product/11.2.0/db_1/oraInst.loc
OPatch version : 11.2.0.3.19
OUI version : 11.2.0.4.0

Log file location : /oracle/product/11.2.0/db_1/cfgtoollogs/opatch/opatch2019-05-02_09-41-09AM_1.log
Lsinventory Output file location : /oracle/product/11.2.0/db_1/cfgtoollogs/opatch/lsinv/lsinventory2019-05-02_09-41-09AM.txt
……
OPatch succeeded.

2. Start the database and Listener, in case if they are still down

Sqlplus / as sysdba
startup

$lsnrctl start LISTENER

3. Run “utlrp.sql” to re-compile all invalid object

$ORACLE_HOME/rdbms/admin/utlrp.sql

4. Verify the patch history using from dba_registry_history using below query

SQL>select action_time,action from dba_registry_history;

ACTION_TIME ACTION

-------------------------------------------------- ------------------------------
02-MAY-19 06.35.23.191942 PM VIEW INVALIDATE
02-MAY-19 06.35.23.217686 PM UPGRADE
02-MAY-19 06.35.41.668820 PM APPLY

5. Please make sure all Oracle COMPONENTS are VALID

select substr(comp_name,1,30) comp_name, substr(comp_id,1,10)
comp_id,substr(version,1,12) version,status from dba_registry;

Thanks

Satishbabu Gunukula

ORA-00060: deadlock resolved & ORA-12012: error on auto execute of job

We have recently upgraded a dataset to 12c and started receiving below errors in alert.log

ORA-00060: deadlock resolved; details in file /home/oracle/diag/rdbms/ORCL/ORCL/trace/ORCL_j001_200521.trc
ORA-12012: error on auto execute of job "SYS"."ORA$AT_SA_SPC_SY_5723"
ORA-00060: deadlock detected while waiting for resource < ORA-06512: at "SYS.DBMS_SPACE", line 2741 < ORA-06512: at "SYS.DBMS_HEAT_MAP_INTERNAL", line 716 < ORA-06512: at "SYS.DBMS_HEAT_MAP_INTERNAL", line 1164 < ORA-06512: at "SYS.DBMS_HEAT_MAP", line 228 < ORA-06512: at "SYS.DBMS_SPACE", line 2747

After investigation found that there is a bug in 12.2 Release and Oracle working to it. You can refer below oracle support notes on this.

Bug 24687075 - SPACE ADVISOR TASKS/JOBS HITTING DEADLOCKS WITH GATHER DB STATS JOBS
In 12.2 Auto Space Advisor Job Fails With ORA-60 (Doc ID 2321020.1)

If you have setup alerts for ORA- errors then either you add exclusions to your script or you can run below command to disable the job Until Bug 24687075 gets fixed

SQL> EXEC DBMS_AUTO_TASK_ADMIN.DISABLE('AUTO SPACE ADVISOR',NULL, NULL);

Regards

Satishbabu Gunukula, Oracle ACE

https://oracleracexpert.com

Oracle Application Express is INVALID and Remove Oracle Apex

We are trying to upgrade a database and noticed that “Oracle Application Express” component is “INVALID” .

SQL> select COMP_ID,VERSION,STATUS from dba_registry;
COMP_ID VERSION STATUS
----------------------------------- --------------- ------------------------------
APEX 3.2.1.00.12 INVALID
CATALOG 11.2.0.4.0 VALID
CATPROC 11.2.0.4.0 VALID

SQL> Select COMP_NAME,VERSION,STATUS from dba_registry where COMP_ID=’APEX’;
COMP_NAME STATUS VERSION
----------------------------------- --------------- ------------------------------
Oracle Application Express INVALID 3.2.1.00.10

To avoid issues during the upgrade we decided to remove the Oracle Apex as it is not in use. We have followed below steps to remove the Oracle Application Express.

$ sqlplus / as sysdba
SQL> @$ORACLE_HOME/apex/apxremov.sql

After removing Application Express started the upgrade but received warning that “Oracle Server” component is INVALID.

SQL> select COMP_ID,VERSION,STATUS from dba_registry;
COMP_ID VERSION STATUS
----------------------------------- --------------- ------------------------------
CATALOG 11.2.0.4.0 INVALID
CATPROC 11.2.0.4.0 INVALID

We noticed that object SYS.HTMLDB_SYSTEM is shown as invalid after removing the Oracle APEX. After investigation I have noticed that apexremov.sql script removes APEX, but leaves the HTMLDB_SYSTEM package and synonym.

I ran below command to drop the invalid packages
SQL> drop package htmldb_system;
SQL> drop public synonym htmldb_system;

Now I don’t see any more INVALID objects but I still see that CATALOG,CATPROC are still INVALID.

SQL> select object_name, object_type from dba_objects
2 where owner = 'SYS' and status = 'INVALID';
no rows selected

SQL> select COMP_ID,VERSION,STATUS from dba_registry;
COMP_ID VERSION STATUS
----------------------------------- --------------- ------------------------------
CATALOG 11.2.0.4.0 INVALID
CATPROC 11.2.0.4.0 INVALID

I ran below script to validate CATPROC and CATALOG. Note that run the same CATALOG you need to change the two entries in the script. (i.e. replace CATPROC with CATALOG before running).

sqlplus / as sysdba
set serveroutput on;
declare
start_time date;
end_time date;
object_name varchar(100);
object_id char(10);
begin
SELECT date_loading, date_loaded into start_time, end_time FROM registry$
WHERE cid = 'CATPROC';
SELECT obj#,name into object_id,object_name
FROM obj$
WHERE status > 1 AND
(ctime BETWEEN start_time AND end_time OR
mtime BETWEEN start_time AND end_time OR
stime BETWEEN start_time AND end_time) AND
ROWNUM <=1;
dbms_output.put_line('Please compile Invalid object '||object_name||'
Object_id '||object_id );
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('CATPROC can be validated now' );
end;
/

Ran utlrp.sql script and now the CATALOG and CATPROC showing as VALID.

SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql

SQL> select COMP_ID,VERSION,STATUS from dba_registry;
COMP_ID VERSION STATUS
----------------------------------- --------------- ------------------------------
CATALOG 11.2.0.4.0 VALID
CATPROC 11.2.0.4.0 VALID

We are able to upgrade the database successfully.

Thanks,
Satishbabu Gunukula, Oracle ACE

http://oracleracexpert.com

Oracle Database Release Lifetime Support

Oracle Support info:

Oracle Premier Support provides a standard five-year support policy for Oracle Technology products. You can extend support for an additional three years with Extended Support for specific releases or receive indefinite technical support with Sustaining Support.

Oracle Release Extended support info.

Please review below link for more details about Oracle Lifetime Support Policy.
http://www.oracle.com/us/support/library/lifetime-support-technology-069183.pdf

Regards

Satishbabu Gunukula, Oracle ACE

http://oracleracexpert.com

What is Orachk tool?

I come across an issue in Oracle Database environment and none of the team aware of what is Orachk and how to use it.

ORAchk tool:
ORAchk tool perform proactive health checks for Oracle software stack and this tool replaces RACcheck. Using this tool you can scan problems with oracle products

· Standalone Oracle Database
· Oracle Grid Infrastructure
· Oracle Golden Gate
· Upgrade Readiness Validations
· Oracle Real Application Clusters
· Maximum Availability Architecture (MAA) Validation

It is always  recommended to run this tool before upgrade.

ORAchk tool has below features

· Proactively scans for problems across the different layers and will not send any config data
· Provides a single dashboard view for your enterprise environment.
· Investigation and analyzing the issue makes simple and provides a report with risks
· You can configure to send email notifications when it detects problems
· Its free of Cost

How to run:

1. Download the orachk.zip and unzip

$unzip orachk.zip
Archive: orachk.zip
inflating: CollectionManager_App.sql
inflating: UserGuide.txt
inflating: orachk
creating: .cgrep/
inflating: .cgrep/init.tmpl
inflating: .cgrep/checkDiskFGMapping.sh
inflating: .cgrep/exalogic_zfs_checks_el_extensive.aksh
inflating: .cgrep/scnhealthcheck.sql
inflating: .cgrep/merge_collections.pl

2. Run the tool

$ ./orachk

This computer is for [S]ingle instance database or part of a [C]luster to run RAC database [S|C] [C]:S

List of running databases

1.ORCL1

2.TSTDB

3.MRST

…..

21. ROCL

22. All of above

23. None of above

Select databases from list for checking best practices. For multiple databases, select 32 for All or comma separated number like 1,2 etc [1-23][22].22

Searching out ORACLE_HOME for selected databases.

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

Checking Status of Oracle Software Stack - Clusterware, ASM, RDBMS

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

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

                                                 Oracle Stack Status

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

Host Name  CRS Installed  ASM HOME       RDBMS Installed  CRS UP    ASM UP    RDBMS UP  DB Instance Name

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

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

Copying plug-ins

. .  . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. .. . . .

*** Checking Best Practice Recommendations (PASS/WARNING/FAIL) ***

Collections and audit checks log file is

/tmp/oracheck/orachk_server1_ORCL1_052615_194626/log/orachk.log

Checking for prompts in /u01/home/oracle/.profile on server1 for oracle user...

=============================================================

                    Node name - server1

=============================================================

Collecting - Database Parameters for ORCL1database

Collecting - Database Parameters for TSTDB database

Collecting - Database Parameters for MRST database

……..

============

Best Practice checking completed.Checking recommended patches on server1.

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

Collecting patch inventory on ORACLE_HOME /oracle/product/10.2.0/db_1

Collecting patch inventory on ORACLE_HOME /oracle/product/11.2.0.4/dbhome_1

Collecting patch inventory on ORACLE_HOME /oracle/product/11.2.0/dbhome_1

Collecting patch inventory on ORACLE_HOME /oracle/product/12.1.0.2/dbhome_1

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

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

1 Recommended RDBMS patches for 102040 from /oracle/product/10.2.0/db_1 on server1

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

Patch#   RDBMS    ASM     type                Patch-Description

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

12879929  no             merge               DATABASE PATCH SET UPDATE 10.2.0.4.11 (PRE-REQ 10.2.0.4.4|INCLUDES CPUJAN2012)

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

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

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

1 Recommended RDBMS patches for 112040 from /oracle/product/11.2.0.4/dbhome_1 on server1

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

Patch#   RDBMS    ASM     type                Patch-Description

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

19769489  no             merge               DATABASE PATCH SET UPDATE 11.2.0.4.5 (INCLUDES CPUJAN2015)

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

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

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

1 Recommended RDBMS patches for 112010 from /oracle/product/11.2.0/dbhome_1 on server1

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

Patch#   RDBMS    ASM     type                Patch-Description

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

12419378  no             merge               DATABASE PSU 11.2.0.1.6 (INCLUDES CPUJUL2011)

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

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

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

1 Recommended RDBMS patches for 121020 from /oracle/product/12.1.0.2/dbhome_1 on server1

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

Patch#   RDBMS    ASM     type                Patch-Description

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

19769480  no             merge               DATABASE PATCH SET UPDATE 12.1.0.2.2 (JAN2015)

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

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

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

              RDBMS homes patches summary report

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

Total patches  Applied on RDBMS Applied on ASM ORACLE_HOME

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

 1              0              0                /oracle/product/10.2.0/db_1

 1              0              0                /oracle/product/11.2.0.4/dbhome_1

 1              0              0                /oracle/product/11.2.0/dbhome_1

 1              0              0                /oracle/product/12.1.0.2/dbhome_1

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

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

Detailed report (html) - /tmp/oracheck/orachk_server1_ORCL1_052615_194626/orachk_server1_ORCL1_052615_194626.html

UPLOAD(if required) /tmp/oracheck/orachk_server1_ORCL1_052615_194626.zip

/oracle:DBA>

Regards,

Satishbabu Gunukula, Oracle ACE

http://www.oracleracexpert.com

ORA-00600: internal error code, arguments: [krcrfr_nohist]

Users may see below error in alert_<SID>.log file.

Errors in file /oracle/ORAC/saptrace/diag/rdbms/ORAC/ORAC/trace/ORAC_ora_6240.trc (incident=211514):
ORA-00600: internal error code, arguments: [krcrfr_nohist], [2596956098], [49126131], [],  
ORA-00600: internal error code, arguments: [krcrfr_nohist], [2596956098], [49126131], [],
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Fri Sep 27 21:44:47 2013
Errors in file /oracle/ORAC/saptrace/diag/rdbms/ORAC/ORAC/trace/ORAC_ora_7583.trc (incident=211585):
ORA-00600: internal error code, arguments: [krcrfi_nohist], [2596956688], [2594394265], [], [], [], [], [], [], [], [], []

Cause:
Users may see this error on databases where Block change tracking enabled. The corrupted blocks in the Block Change Tracking are causing the ORA-600 [krcrfr_nohist], which is a mismatch in the Block Change Tracking information.

This applies to Oracle 11.2.0.2 and later versions. You might be hitting a BUG:13701312 and check with Oracle Support to see any patch available.

Workaround: Disable and enable change tracking.

SQL> alter database disable block change tracking;
SQL> alter database enable block change tracking [using file '<file-name>'];

You may see below errors after ORA-00600

minact-scn: got error during useg scan e:12751 usn:1
minact-scn: useg scan erroring out with error e:12751
Suspending MMON action 'Block Cleanout Optim, Undo Segment Scan' for 82800 seconds

The above warning is raised while scanning UNDO segment. It indicates that undo segment scan has failed with ORA-12751 error.

The ora-12751 indicates that some part of MMON Operation is either taking too long to complete and taking too much CPU. Due to “Suspending MMON” you may not able to see AWR reports during the issue period.

Reference:
ORA-12751 "cpu time or run time policy violation" in the MMON Slave Process File during Feature Usage Statistics [FUS] (Doc ID 1291296.1)
Alert log shows: "Minact-scn: Useg Scan Erroring Out With Error E:12751" Warning (Doc ID 1478691.1)

Regards,
Satishbabu Gunukula, Oracle ACE
http://www.oracleracexpert.com