ORA-00054: resource busy and acquire with NOWAIT specified

SQL>  create index abc on xys (object_id) tablespace mytbs;

 create index abc on xys (object_id) tablespace mytbs

                     *

ERROR at line 1:

ORA-00054: resource busy and acquire with NOWAIT specified

SELECT a.SID, a.serial#

  2  FROM v$session a, v$locked_object b, DBA_OBJECTS c

  3  WHERE b.object_id = c.object_id

  4  AND a.SID = b.session_id AND OBJECT_NAME='XYS';

       SID    SERIAL#

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

       158      25604

• Commit or rollback the statement  causing issue
• Wait  till  release resources
• Kill that session using

alter system kill session 'sid,serial#'

ORA-02049: time-out: distributed transaction waiting for lock

A DML statement that requires locks on a remote database can be blocked if another transaction own locks on the requested data. If these locks continue to block the requesting SQL statement, then the following sequence of events occurs:

A timeout occurs.

The database rolls back the statement.

The database returns this error message to the user:

ORA-02049: time-out: distributed transaction waiting for lock

Because the transaction did not modify data, no actions are necessary as a result of the timeout. Applications should proceed as if a deadlock has been encountered. The user who executed the statement can try to reexecute the statement later. If the lock persists, then the user should contact an administrator to report the problem.

In our environment a delete statement was running on server on a table and someone

Run delete statement on same table and same row from remote machine. 'row-x'  lock was held by the one session and other requested for it and second session terminate

With error ORA-02049: time-out: distributed transaction waiting for lock

  

We can find the locks held  on table  and  statement behind it.

SQL> select a.sid, a.serial#,sql_id ,(case LOCKED_MODE   when 0 then 'none'

  2  when 1 then  'null'

  3   when  2 then 'row-S'

  4  when 3 then 'row-x'

  5  when 4 then 'share'

  6  when 5 then 'S/Row-X'

  7  when  6 then 'exclusive'

  8   end ) LOCKED_MODE

  9    from v$session a, v$locked_object b, dba_objects c

 10  where b.object_id = c.object_id

 11  and a.sid = b.session_id

 12   and OBJECT_NAME='XYS'

 13  ;

SQL> select sql_text from v$sql where sql_id='8tswhcpv65b35';

SQL_TEXT

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

 delete from xys where id=2

ORA-00376: file 6 cannot be read at this time

15:37:25 SQL> conn scott/scott

Connected.

ERROR at line 1:

ORA-00376: file 6 cannot be read at this time

ORA-01110: data file 6: '/home/oracle/10.2.0/product/oradata/prim/mytbs01.dbf'

Cause :  tablespace  or datafile in not readable form whether it  recovery mode or offline

Solution : You can check  find the tablespace status using below query

15:37:33 SQL> select tablespace_name , status from dba_tablespaces;

TABLESPACE_NAME                STATUS

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

SYSTEM                         ONLINE

UNDOTBS1                       ONLINE

SYSAUX                         ONLINE

TEMP                           ONLINE

USERS                          OFFLINE

EXAMPLE                        ONLINE

MYTBS                          OFFLINE

--or you can check file status

SQL>  select name ,status from v$datafile where name='/home/oracle/10.2.0/product/oradata/prim/mytbs01.dbf';

NAME

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

STATUS

-------

/home/oracle/10.2.0/product/oradata/prim/mytbs01.dbf

RECOVER

Bring tablespace on line

15:38:33 SQL> alter tablespace MYTBS online;

Tablespace altered.

15:38:42 SQL>  select * from tspitrtable

15:38:46   2  ;

NAME

----------

vanita

sumit

--or recover  datafile  using rman

RMAN> recover datafile 6;

Starting recover at 20-MAR-13

using channel ORA_DISK_1

starting media recovery

media recovery complete, elapsed time: 00:00:05

Finished recover at 20-MAR-13

SQL>  alter database datafile '/home/oracle/10.2.0/product/oradata/prim/mytbs01.dbf' online;

Database altered.

ORA-19809: limit exceeded for recovery files

ORA-19809: limit exceeded for recovery files

ORA-19804: cannot reclaim 52428800 bytes disk space from 2147483648 limit

Cause : backup size exceed the size limit of backup destination (set to db_recovery_file_dest  )

Solution :

• Delete or move old backups and make space in db_recovery_file_dest   folder
• Increase the size of  db_recovery_file_dest_size          

How to increase the size of db_recovery_file_dest_size          

Sqlplus / as sysdba

SQL> show parameter recovery

NAME                                 TYPE        VALUE

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

db_recovery_file_dest                string      /home/oracle/10.2.0/product/fl

                                                 ash_recovery_area

db_recovery_file_dest_size           big integer 2G

recovery_parallelism                 integer     0

SQL> alter system set db_recovery_file_dest_size=10g scope=both;

System altered.

Now take backup it will complete successfully

Checkpoint , Log Writer Archiver Process

The Checkpoint Process

The checkpoint process does three things:

• It signals the database write process (DBWn) at each checkpoint.

• It updates the datafile headers with the checkpoint information.

• It updates the control files with the checkpoint information.

The Log Writer Process

Oracle’s online redo log files record all changes made to the database. logs are written to before the datafiles are. Therefore, it is important to  always protect the online logs against loss by ensuring they are multiplexed. Any changes made to the database are first recorded in the redo log buffer, which is part of the SGA.then Log Writer Process write into logfiles. Log writer write in below conditions

• At each commit

• Every three seconds

• When the redo log buffer is one-third full

After crash a database when restart it  then instance reads the redo log files looking for any committed changes that need to be applied to the datafiles .when we commit, Oracle ensures that what you are committing has first been written to the redo log files before these changes are recorded in the actual datafiles. Commit . does’t mean that you have wriiten into datafiles.If  redo files have lost  then it may possible that your data has been lost  even if you committed it.

Archiver Process

The archiver (ARCn) is an optional background process and is in charge of archiving the filled online redo log files, before they can be overwritten by new data. The archiver background process is used only if you’re running your database in archivelog mode.

Top command

top - 09:39:16 up 281 days, 13:03,  3 users,  load average: 3.49, 2.81, 2.33
Tasks: 254 total,   2 running, 252 sleeping,   0 stopped,   0 zombie
Cpu(s): 12.3%us,  1.7%sy,  0.0%ni, 71.7%id, 14.3%wa,  0.0%hi,  0.0%si,  0.0%st
Mem:  325058k total, 266795k used,  58263k free,   2517k buffers
Swap:  83858k total,   20589k used,  817999k free, 2390744k cached
  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND 6371 Vanita     25   0     46748   6064    2320  R 99.6       0.0           20815:15           scp
23921 oracle    18   0     1.9g      2.7g     2.5g  D  8.3       8.6           0:26.03            oracle
22054 oracle    15   0    1.7g      693m    682m S  1.3      2.2             0:37.01             oracle
 2760 oracle    18   0     1.7g      36m     23m D  1.0        0.1            676:09.89               oracle
 1714 oracle    18   0     627m    159m   35m S  0.3        0.5          2:46.30                 java
24068 oracle    15   0   10992  1168    772 R  0.3           0.0          0:00.55                 top

 “top”, indicates that the system has been up and running for 281  days.
 “Tasks”,  indicates the total number of processes along with a breakdown of running, sleeping, stopped and zombie processes count.
“Cpu(s)”  indicates the current CPU utilization of the system. In this example, CPU is  71.7% idle
 “Mem”  indicates provides the memory information.
"swap" swap memory  information
VIRT stands for the virtual size of a process and it represents how much memory the program is able to access at the present moment
RES stands for the resident size, which is an accurate representation of how much actual physical memory a process is consuming.
SHR indicates how much of the VIRT size is actually sharable .
%CPU CPU % using a process
%MEM  Memory using process
TIME+ elapsed time of process

Kill session in oracle

We can find the session information using v$session and v$process

 SELECT
       s.sid,
       s.serial#,
       p.spid,
       s.username,
       s.program
FROM   v$session s
       JOIN v$process p ON p.addr = s.paddr
WHERE  s.type != 'BACKGROUND'

    SID    SERIAL# SPID         USERNAME                       PROGRAM
---------- ---------- ------------ ------------------------------ -------------------------------------------       112      53329 22054        vanita                   sql*plus


We can kill session using below command

SQL> ALTER SYSTEM KILL SESSION 'sid,serial#';

 i.e

SQL> ALTER SYSTEM KILL SESSION '112 ,53329';

DB file scattered read wait event

This event signifies that the user process is reading buffers into the SGA buffer cache and is waiting for a physical I/O call to return. A db file scattered read issues a scattered read to read the data into multiple discontinuous memory locations. A scattered read is usually a multiblock read. It can occur for a fast full scan (of an index) in addition to a full table scan.

The db file scattered read wait event identifies that a full scan is occurring. When performing a full scan into the buffer cache, the blocks read are read into memory locations that are not physically adjacent to each other. Such reads are called scattered read calls, because the blocks are scattered throughout memory. This is why the corresponding wait event is called 'db file scattered read'. multiblock (up to DB_FILE_MULTIBLOCK_READ_COUNT blocks) reads due to full scans into the buffer cache show up as waits for 'db file scattered read'

In one line we can describe scattered read  The Oracle session has requested and is  waiting for multiple contiguous database blocks to be read into the SGA from disk.

Cuase :

• Full Table scans
• Fast Full Index Scans
• Missing or unselective or unusable  index
• Table not analyzed after created index or lack of accurate statistics for the optimizer

If an application that has been running fine for a while suddenly clocks a lot of time on the db file scattered read event and there hasn’t been a code change, you might want to check to see if one or more indexes has been dropped or become unusable.

SELECT p1 "file#", p2 "block#", p3 "class#",event

FROM v$session_wait

WHERE event IN ('db file scattered read')

;

Where P1,P2,P3 are

P1 - The absolute file number

P2 - The block being read

P3 - The number of blocks (should be greater than 1)

SELECT relative_fno, owner, segment_name, segment_type

FROM DBA_EXTENTS

WHERE file_id = &FILE

AND &BLOCK BETWEEN block_id

AND block_id + blocks - 1;

.

SELECT

      s.p1 file_id, s.p2 block_id,o.object_name obj,

       o.object_type otype,

       s.SQL_ID,

       w.CLASS,event

FROM v$session s,

     ( SELECT ROWNUM CLASS#, CLASS FROM v$waitstat ) w,

      ALL_OBJECTS o

WHERE

 event IN ('db file scattered read')

AND

    w.CLASS#(+)=s.p3

   AND o.object_id (+)= s.row_wait_OBJ#

ORDER BY 1;

Find the related SQL statement  using sql_id

SELECT sql_text FROM   v$sqltext WHERE sql_id=&sq_id ORDER BY piece

You can also find the objects using below sql :-

Finding the SQL Statement executed by Sessions Waiting for I/O

SELECT SQL_ADDRESS, SQL_HASH_VALUE

  FROM V$SESSION

 WHERE EVENT ='read by other session';

Finding the Object Requiring I/O

SELECT row_wait_obj#

  FROM V$SESSION

 WHERE EVENT = 'db file scattered read';

To identify the object and object type contended for, query DBA_OBJECTS using the value for ROW_WAIT_OBJ# that is returned from V$SESSION. For example:

SELECT owner, object_name, subobject_name, object_type

  FROM DBA_OBJECTS

 WHERE data_object_id = &row_wait_obj;

Once you identify the hot blocks and the segments they belong to, and related quires then you reduce the using following solutions

• Optimize the SQL statement that  initiated most of the waits. The goal is
• to minimize the number of physical  and logical reads.
• Optimize multi-block I/O by setting the parameter DB_FILE_MULTIBLOCK_READ_COUNT i.e  if DB_FILE_MULTIBLOCK_READ_COUNT initialization parameter  values too high which favors full scans reduce it.
• Partition pruning to reduce number of  blocks visited
• Consider the usage of multiple buffer  pools and cache frequently used  indexes/tables in the KEEP pool
• Make sure that  the query use the right driving table?
• Are the SQL predicates appropriate  for hash or merge join?
• If full scans are appropriate, can  parallel query improve the response
• time?
• The objective is to reduce the demands for both the logical and
• physical I/Os, and this is best  achieved through SQL and application tuning.
• Gather statistics the related objects if they are missing  . Check the LAST_ANALYZED date from user_tables view