Tuesday, February 11, 2020

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

Monday, February 10, 2020

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 

Wednesday, January 29, 2020

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

Thursday, January 16, 2020

ORA-00942: table or view does not exist

When calling a table directly or through procedure/function...etc user may receive error “ORA-00942: table or view does not exist”.

For ex:-
CREATE OR REPLACE FUNCTION PROC1
(Num IN NUMBER)
RETURN NUMBER
IS
BEGIN
INSERT INTO Table1 SELECT * FROM SCOTT.Table2 WHERE ID = Num;
END;

PL/SQL: ORA-00942: table or view does not exist

SQL> select * from table1;
select * from table1
              *
ERROR at line 1:
ORA-00942: table or view does not exist

Possible reasons:
1. The table name or view name spelled wrongly
2. The table or view doesn’t exist
3. The user doesn’t have required permissions

In some cases users have select access and able to query data but when running from a procedure they still receive “ORA-00942: table or view does not exist”

The reason for this error is the access granted trough a ROLE not directly. In order to access another user table from a procedure you need to have SELECT privilege granted directly.

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

Friday, December 13, 2019

RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process

 The warning is clear that archive log not deleted and the below are the possible reasons. This post will help you how to manage the archive logs on standby.

RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process

1. The archive log destination on Standby is full
2. Standby destination not accepting any logs due to issues
3. RMAN archive log configuration is set to none/default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default

First run below command to identify the archive log gap

SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH, (SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE ARCH.THREAD# = APPL.THREAD#

Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
1 185685 185669 16

If there is an archive gap then the issue is either standby archive destination is full or standby destination has some issues and not accepting any more logs, based up on the error take appropriate action. As long as you archive logs backed upon on Primary you can proceed deleting the archive logs on standby

In order to manage the archive logs you have 2 options

1. Create a shell script to check applied logs on standby and delete rest on periodic basis
2. Configure FRA for archive logs and set the RMAN archive log deletion policy as per below

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=800GBG;
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='/u01/oracle/FRA';

In case if you are using ASM specify disk group
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='+FRA_DISK';
You need to set LOG_ARCHIVE_DEST_1 parameter DB_RECOVERY_FILE_DEST so that archived logfiles will be created at Flash recovery area.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST';

Set the RMAN archived log deletion policy as follows.
RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;

With above setting the applied archived logs will be deleted automatically when there is a space constraint in flash recovery area.

It’s different for the database where you do the backup, because you want to be sure that the backup is done before an archive log is deleted:
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY BACKED UP 1 TIMES TO DISK;

Regards
Satishababu Gunukula, Oracle ACE