Investigating "read by other session" Wait Event

This article explains about the wait event “read by other session”. In this article I will explain how to further investigate about SQLs and segments involved in this wait event. This should be noted that unless this wait event is the result of slow IO performance, this wait event has to be tuned at the application side or by tuning the segment. To find out which SQLs and which table/index are causing this wait, we can query v$active_session_history (or DBA_HIST_ACTIVE_SESS_HISTORY); SAMPLE_TIME column can be used in WHERE clause to restrict rows for the event details during a specific period. Alternatively, we can also query v$session_wait to find out sessions currently waiting on this wait event.

ORA-19910: can not change recovery target incarnation in control file

If you are trying to resolve ORA-19909 on your physical standby database which is out of sync with primary because of ORA-19909, and you face ORA-19910, the probably cause is that redo apply process is still active. This was exactly the case with me when I tried to reset the incarnation number of my physical primary database. In the following you can see the exact command and error message returned.

Error ORA-235 occurred during an un-locked control file transaction

Error ORA-235 occurred during an un-locked control file transaction.  This error can be ignored.  The control file transaction will be retried. RFS[2]: Opened log for thread 2 sequence 40781 dbid 2440526278 branch 1089779374 RFS[3]: Opened log for thread 1 sequence 40695 dbid 2440526278 branch 1089779374 RFS[4]: Opened log for thread 1 sequence 40693 dbid 2440526278 branch 1089779374 RFS[1]: Opened log for thread 2 sequence 40779 dbid 2440526278 branch 1089779374 Tue Mar 21 04:11:38 2023 Archived Log entry 66837 added for thread 2 sequence 40781 rlc 1089779374 ID 0x0 dest 2: Tue Mar 21 04:11:40 2023 Archived Log entry 66838 added for thread 1 sequence 40693 rlc 1089779374 ID 0x0 dest 2: Tue Mar 21 04:11:40 2023 Archived Log entry 66840 added for thread 1 sequence 40695 rlc 1089779374 ID 0x0 dest 2: Tue Mar 21 04:11:43 2023

Read by Other Session - Oracle Wait Event

Prior to 10.1 version, this wait event was part of Buffer Busy Wait, however, after 10.1 this wait event was separated from buffer busy wait and is now visible in AWR reports. This wait event is reported if multiple sessions are waiting for the same data block to be read from the disk into the buffer cache. This could be alarming if this wait event is reported as one of the top wait events in the AWR reports. There could be different causes of this wait event to be at the top. In this article I will discuss how we can troubleshoot this wait event.

Recovering a Dropped Table using RMAN Backup

There are different ways to recover back a dropped table i.e. recovering from recycle bin if it is set to on, using flashback, importing from an export backup of the table, or using RMAN backup. In this article I will explain how to recover a table using RMAN backup. Database must be running in archivelog mode with RMAN backup available. Backup should have been from the time when table existed. Using this method, we can also recover tables to a previous state (keeping existing version of table) by providing SCN or timestamp.

Finding High CPU Consuming SQLs with Multiple Plans

 In a DBA’s life this is very common to see high CPU usage of a server. If this is happening most of the time and occurrence is common, you may think of increasing the number of CPUs of the system. This CPU busy rate may be observed in CPU usage percentage or CPU load average matrics. But if this is not a very commonly occurring issue of your system and someday you suddenly see high CPU usage, you may immediately want to kick out the sessions that are consuming high CPU to avoid a system hang and reboot (or node eviction in case of RAC).

How to Invalidate a Cursor to Load a New Plan

If an SQL is frequently in use and has different SQL plans historically for execution, there might be a scenario that current execution plan in use is a bad one. In that case you might want to get rid of this current plan so that optimizer could possibly load a better plan for next executions. In this case, you would need to invalidate the current cursor in shared pool. Following is the way how we invalidate a SQL cursor from share pool.

Moving Accidentally Created Datafile on Local File System in RAC to the Shared File System

 ORA-01157 is a common incident that happens when a DBA accidentally creates a datafile for a cluster database on the local file system of one of the nodes instead of creating on a shared file system or ASM. In this case, all instances other than the instance/node where fiel was locally created would report ORA-01157 and ORA-01110. Alert log file would show entires similar to the following.

ORA-01157: cannot identify/lock data file and ORA-01110

 ORA-01157 error along with ORA-01110 in alert log file means that a datafile that DBWR is trying to access (to read or write) is no longer available. One reason coule be that this file was accidentally removed, or file permissions were accidentally changed, thus hindering oracle processes to read/write this datafile. Another reason could be the file system - where this dataifle existed - is no longer mounted or available.

ORA-01017: invalid username/password during dataguard switchover

 If you have configured dataguard broker and you are performing database switchover using DGMGRL command, you should make sure that you connect with dataguard broker using a privileged user and supply password instead of using a forward slash (‘/’) to log  in and initiating the switchover. If you do not follow this procedure, you would be returned ORA-01017 because a privileged session with both primary and physical standby is required to initiate switchover (or failover). Following is an example of receiving the error.