Guenadi N Jilevski's Oracle BLOG

Oracle RAC, DG, EBS, DR and HA DBA BLOG

Explanation of ORA-00054 Error

Explanation of ORA-00054 Error

Generally ORA-00054 is reported if a SQL statement would have blocked waiting for another user to complete some operation.

Issuing any of the following two type of SQL statements can cause ORA-54 problem.
i) SQL statement had a ‘NOWAIT’ clause so instead of blocking the statement returns this error.
or
ii) SQL statement was DDL operation and was blocked.

So for both DDL or SELECT .. FOR UPDATE NOWAIT can raise this error if an incompatible lock is held.

Diagnosis of the problem
A) The V$LOCK view lists the locks currently held by the Oracle Database and outstanding
requests for a lock or latch.
Let’s have a look at column of v$lock view.

SQL> desc v$lock;
Name                                      Null?    Type
—————————————– ——– ————
ADDR                                               RAW(4)
KADDR                                              RAW(4)
SID                                                NUMBER
TYPE                                               VARCHAR2(2)
ID1                                                NUMBER
ID2                                                NUMBER
LMODE                                              NUMBER
REQUEST                                            NUMBER
CTIME                                              NUMBER
BLOCK                                              NUMBER
– Where TYPE indicates type of user or system lock.
The locks on the user types are obtained by user applications. Any process that is blocking others is likely to be holding one of these locks.
The user type locks are:
i) TM – DML enqueue
ii) TX – Transaction enqueue
iii)UL – User supplied

The system type locks are:

1)   BL – Buffer hash table instance
2)   NA..NZ – Library cache pin instance (A..Z = namespace)
3)   CF – Control file schema global enqueue
4)   PF – Password File
5)   CI – Cross-instance function invocation instance
6)   PI, PS – Parallel operation
7)   CU – Cursor bind
8)   PR – Process startup
9)   DF – Data file instance
10)  QA..QZ – Row cache instance (A..Z = cache)
11)  DL – Direct loader parallel index create
12)  RT – Redo thread global enqueue
13)  DM – Mount/startup db primary/secondary instance
14)  SC – System change number instance
15)  DR – Distributed recovery process
16)  SM – SMON
17)  DX – Distributed transaction entry
18)  SN – Sequence number instance
19)  FS – File set
20)  SQ – Sequence number enqueue
21)  HW – Space management operations on a specific segment
22)  SS – Sort segment
23)  IN – Instance number
24)  IR -Instance recovery serialization global enqueue
25)  SV – Sequence number value
26)  IS – Instance state
27)  TA – Generic enqueue
28)  IV – Library cache invalidation instance
29)  TS – Temporary segment enqueue (ID2=0)
30)  JQ – Job queue
31)  TS – New block allocation enqueue (ID2=1)
32)  KK – Thread kick
33)  TT – Temporary table enqueue
34)  LA .. LP – Library cache lock instance lock (A..P = namespace)
35)  UN – User name
36)  MM – Mount definition global enqueue
37)  US – Undo segment DDL
38)  MR – Media recovery
39)  WL – Being-written redo log instance
40)  ST Space transaction enqueue

– LDOME is Lock mode in which the session holds the lock.
– REQUEST is Lock mode in which the process requests the lock.
– Both LMODE and REQUEST can have 0 to 6 value which means
0 – none
1 – null (NULL)
2 – row-S (SS)
3 – row-X (SX)
4 – share (S)
5 – S/Row-X (SSX)
6 – exclusive (X)

B) To find out the blocked process issue,
SQL> select * from v$lock where request!=0;

C) If you see locks being taken out on a child table when doing deletes on the parent then indexing the foreign key column on the child table will stop happening error ORA-00054.

D) In order to find the list of locks in a database issue following query,

SQL> column lock_id1 format a10
SQL> set pagesize 120
SQL> column lock_type format a12
SQL> column mode_held format a10
SQL> column mode_requested format a10
SQL> column blocking_others format a20
SQL> column username format a10
SQL> SELECT     session_id
,  lock_type
,  mode_held
,  mode_requested
,  blocking_others
,  lock_id1
FROM       dba_lock l
WHERE      lock_type NOT IN (‘Media Recovery’, ‘Redo Thread’)
/

SESSION_ID LOCK_TYPE    MODE_HELD  MODE_REQUE BLOCKING_OTHERS      LOCK_ID1
———- ———— ———- ———- ——————– ———-
159 XR           Null       None       Not Blocking         4
159 Control File Row-S (SS) None       Not Blocking         0
159 RS           Row-S (SS) None       Not Blocking         25
153 AE           Share      None       Not Blocking         99
158 Temp Segment Row-X (SX) None       Not Blocking         3
161 PW           Row-X (SX) None       Not Blocking         1
132 AE           Share      None       Not Blocking         99
156 AE           Share      None       Not Blocking         99
132 TO           Row-X (SX) None       Not Blocking         62986
127 CU           Exclusive  None       Not Blocking         640372732
127 AE           Share      None       Not Blocking         99
127 FU           Exclusive  None       Not Blocking         0
132 DML          Row-X (SX) None       Not Blocking         70833
127 DML          Row-X (SX) None       Not Blocking         5750
127 DML          Row-X (SX) None       Not Blocking         56048
153 DML          Row-X (SX) None       Not Blocking         70833
127 Transaction  Exclusive  None       Not Blocking         65550
153 Transaction  Exclusive  None       Not Blocking         589833

18 rows selected.

June 11, 2009 Posted by | oracle | Leave a comment

DDL with the WAIT option in 11g

DDL with the WAIT option in 11g

Whenever you perform any DDL operation on a table, oracle try to lock the table exclusively. In oracle version 10g and earlier, whenever you issue DDL, oracle try to lock the table exclusively with NOWAIT option and if it fails then immediately “ORA-00054 resource busy and acquire with NOWAIT specified” error returned. In fact, in oracle 10g we didn’t have any other alternatives. So we had to wait until resource is free and in fact in production database you might need hours to complete your DDL jobs and you might need to try it frequently to test when resource become free.

Oracle database 11g includes the DDL_LOCK_TIMEOUT parameter, which you can set using the ALTER SYSTEM and ALTER SESSION commands. Now you have the opportunity not to issue DDL frequently and query itself can wait a specified time and if it find resource free within this time DDL operation will be completed.

The parameter DDL_LOCK_TIMEOUT specifies a time limit for how long DDL statements will wait in a DML lock queue.
– The default value is zero which indicates a status of NOWAIT.
– The maximum value is 1,000,000 seconds which indicates the DDL statement will wait forever to acquire a DML lock.
– If you specify time in the DDL_LOCK_TIMEOUT parameter and if a lock is not acquired before the timeout period expires, then an error is returned.

To demonstrate this scenario let’s create a simple example in oracle database 11g.

1)Create table named test_wait, insert a row and leave the session alone.

SQL> create table test_wait(col1 number);

Table created.

SQL> insert into test_wait values(1);

1 row created.

2)Now from another session do DDL operation.
Note that you may be succeed while adding column to the table in this case. Here in another session I am practising DROP DDL command.

SQL> drop table test_wait;
drop table test_wait
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

SQL> show parameter ddl_lock_

NAME                                 TYPE        VALUE
———————————— ———– ——————————
ddl_lock_timeout                     integer     0

It failed because in the above you see ddl_lock_timeout is set to 0 which means NOWAIT and so if query see someone lock the row error returned immediately.

Now we change to parameter to 100. Now query will wait for 100 seconds in order to see whenever resource become free.

SQL> alter session set ddl_lock_timeout=100;

Session altered.

Now in the first session issue commit.
SQL> commit;

And in the second session try to drop the table and you see drop become successful if you do commit within 100 seconds.
SQL> drop table test_wait;

Table dropped.

However if you did not do commit within 100 seconds in the first session then after 100 seconds in the second session DROP command would fail with “ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired”.

June 10, 2009 Posted by | oracle | Leave a comment

ORA-00054: resource busy and acquire with NOWAIT specified

ORA-00054: resource busy and acquire with NOWAIT specified

Problem Description

In my  database Oracle 10.2g while I was adding column to one of my transaction table it fails with ORA-54 error as below.

SQL> alter table student add b number;
alter table student add b number
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified

Description of the Problem
Let’s try to produce the problem in our test environment. I have opened two session that connected to database under a schema. In one session, I have created a table and inserted data into it.
SQL> create table a (a number);

Table created.

SQL> insert into a values(1);

1 row created.

I did not yet committed data in session 1. Now in another session whenever I try to any ddl like (alter table, drop table) ORA-00054 will produce.

In another session,
SQL> alter table a add b number;
alter table a add b number
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified

SQL> drop table a;
drop table a
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified

SQL> lock table a in exclusive mode nowait;
lock table a in exclusive mode nowait
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified

Cause of the Problem
Whenever you try to do any structural changes on a table oracle try to lock the table exclusively with NOWAIT option(this is in 10.2g while in 11g you can change the wait timeout). If oracle fails to lock the table exclusively then ORA-00054 will occur.

Solution of the Problem
In 10.2g you are limited to several choices to solve the problem. To avoid it,

-Re run the DDL at a later time when the database become idle.
or,

-Kill the sessions that are preventing the exclusive lock.
or,

-Prevent end user to connect to the database and then run the DDL.

You have different views to see locking information about the table.
1)DBA_BLOCKERS: Displays a session if it is not waiting for a locked object but is holding a lock on an object for which another session is waiting. In our scenario this view will not help.

2)DBA_DDL_LOCKS: It lists all DDL locks held in the database and all outstanding requests for a DDL lock.

3)DBA_DML_LOCKS: It lists all DML locks held in the database and all outstanding requests for a DML lock.
If you query from it in the mode_held field you will see ‘row exclusive lock’.
SQL> select mode_held from dba_dml_locks where owner=’MAXIMSG’;

MODE_HELD
————-
Row-X (SX)

4)DBA_LOCK: It lists all locks or latches held in the database, and all outstanding requests for a lock or latch.

5)DBA_LOCK_INTERNAL: It displays a row for each lock or latch that is being held, and one row for each outstanding request for a lock or latch.

6)DBA_LOCKS is a synonym for DBA_LOCK.

7)DBA_WAITERS: Shows all the sessions that are waiting for a lock.

8)V$LOCK: It lists the locks currently held by the Oracle Database and outstanding requests for a lock or latch.

9)V$LOCK_ACTIVITY: This view is deprecated.

10)V$LOCKED_OBJECT: This view lists all locks acquired by every transaction on the system.

In order to see locked object query,

SQL> set linesize 130
SQL> set pages 100
SQL> col username       format a20
SQL> col sess_id        format a10
SQL> col object format a25
SQL> col mode_held      format a10
SQL> select     oracle_username || ‘ (‘ || s.osuser || ‘)’ username
,  s.sid || ‘,’ || s.serial# sess_id
,  owner || ‘.’ || object_name object
,  object_type
,  decode( l.block
,       0, ‘Not Blocking’
,       1, ‘Blocking’
,       2, ‘Global’) status
,  decode(v.locked_mode
,       0, ‘None’
,       1, ‘Null’
,       2, ‘Row-S (SS)’
,       3, ‘Row-X (SX)’
,       4, ‘Share’
,       5, ‘S/Row-X (SSX)’
,       6, ‘Exclusive’, TO_CHAR(lmode)) mode_held
from       v$locked_object v
,  dba_objects d
,  v$lock l
,  v$session s
where      v.object_id = d.object_id
and        v.object_id = l.id1
and        v.session_id = s.sid
order by oracle_username
,  session_id
/

USERNAME             SESS_ID    OBJECT                    OBJECT_TYPE         STATUS       MODE_HELD
——————– ———- ————————- ——————- ———— ———-
MAXIMSG (A)          142,232    MAXIMSG.A                 TABLE               Not Blocking Row-X (SX)
OMS (DBA2\ershad)    143,280    OMS.T                     TABLE               Not Blocking Row-X (SX)
OMS (DBA2\ershad)    143,280    OMS.T1                    TABLE               Not Blocking Row-X (SX)

Here we see there is 3 types of locking. In our case the object is A. And we see the sid=142 and serial#=232 is preventing us from locking the table in exclusive mode. So to achieve our task we can kill the session as below.

SQL> alter system kill session ‘142, 232’;

System altered.

SQL> alter table a add b number;

Table altered.

Now in the first session whenever you try to access error will be generated saying that session has been killed. In the first session.
SQL> commit;
commit
*
ERROR at line 1:
ORA-00028: your session has been killed

June 7, 2009 Posted by | oracle | 2 Comments

What to do with targets that have not been discovered

What to do with targets that have not been discovered

I was trying to manage my 11g Data Guard using the Enterprise Manager Grid Control Agent, but I had some hitches.
The management server had been installed on grid10g and the data guard pair on ha1 and ha2

The standby instance on ha2 is SBF1 and is up

ha2-> ps -ef | grep oracle

oracle 3978 1 0 12:18 ? 00:00:01 /opt/oracle/product/11g/db_1/bin/tnslsnr LISTENER_SBF1 -inherit

oracle 4016 1 0 12:19 ? 00:00:06 ora_pmon_SBF1

oracle 4018 1 0 12:19 ? 00:00:11 ora_vktm_SBF1

oracle 4022 1 0 12:19 ? 00:00:01 ora_diag_SBF1

oracle 4024 1 0 12:19 ? 00:00:00 ora_dbrm_SBF1

oracle 4026 1 0 12:19 ? 00:00:01 ora_psp0_SBF1

oracle 4030 1 0 12:19 ? 00:00:22 ora_dia0_SBF1

But the only target visible on ha2 is the host and the agent

Checking oratab I noticed an error

cat /etc/oratab

#

# Multiple entries with the same $ORACLE_SID are not allowed.

#

SBF1:opt/oracle/product/11g/db_1:Y

The slash before opt is missing, should be /opt

The question is now how to have the agent re-discover the targets on the node ha2;
I know that agentca is to be used (Agent Configuration Assistant)

ha2-> agentca

Either rediscover(-d) or reconfigure(-f) option is mandatory

Let’s try -d ha2-> agentca -d

Stopping the agent using /oragrid/OracleHomes/agent10g/bin/emctl stop agent

Oracle Enterprise Manager 10g Release 10.2.0.1.0.

Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.

Stopping agent … stopped.

Running agentca using /oragrid/OracleHomes/agent10g/oui/bin/runConfig.sh

ORACLE_HOME=/oragrid/OracleHomes/agent10g ACTION=Configure MODE=Perform RESPONSE_FILE=/oragrid/OracleHomes/agent10g/response_file

RERUN=TRUE INV_PTR_LOC=/oragrid/OracleHomes/agent10g/oraInst.loc COMPONENT_XML={oracle.sysman.top.agent.10_2_0_1_0.xml}

Perform – mode is starting for action: Configure

Perform – mode finished for action: Configure

ha2-> emctl status agent

Oracle Enterprise Manager 10g Release 10.2.0.1.0.

Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.

—————————————————————

Agent is Not Running

ha2-> emctl start agent

Oracle Enterprise Manager 10g Release 10.2.0.1.0.

Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.

Starting agent ….. started.

ha2-> cat /oragrid/OracleHomes/agent10g/sysman/emd/targets.xml

<Targets AGENT_TOKEN=”18bcd1427584a9f57b95ef01a0b1245bc98dcb8b”>

<Target TYPE=”oracle_emd” NAME=”ha2:3872″/>

<Target TYPE=”host” NAME=”ha2″/>

</Targets>

ha2-> ls -l /oragrid/OracleHomes/agent10g/sysman/emd/targets.xml
-rw-r—– 1 oracle dba 155 Jul 4 17:00 /oragrid/OracleHomes/agent10g/sysman/emd/targets.xml

The file targets.xml has been modified, but still no /opt/oracle/product/11g/db_1

ha2-> ls /oragrid/OracleHomes2/agent10g/network/admin

shrept.lst

ha2-> rm -rf /oragrid/OracleHomes2/agent10g/network/admin

ha2-> ln -s /opt/oracle/product/11g/db_1/network/admin /oragrid/OracleHomes2/agent10g/network/admin

ha2-> set -o vi

ha2-> ls /oragrid/OracleHomes2/agent10g/network/admin

listener.ora samples shrept.lst tnsnames.ora

ha2-> agentca -d

Stopping the agent using /oragrid/OracleHomes2/agent10g/bin/emctl stop agent

Oracle Enterprise Manager 10g Release 10.2.0.1.0.

Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.

Agent is Not Running

Running agentca using /oragrid/OracleHomes2/agent10g/oui/bin/runConfig.sh

ORACLE_HOME=/oragrid/OracleHomes2/agent10g ACTION=Configure MODE=Perform

RESPONSE_FILE=/oragrid/OracleHomes2/agent10g/response_file

RERUN=TRUE INV_PTR_LOC=/oragrid/OracleHomes2/agent10g/oraInst.loc COMPONENT_XML={oracle.sysman.top.agent.10_2_0_1_0.xml}

Perform – mode is starting for action: Configure

Perform – mode finished for action: Configure

You can see the log file: /oragrid/OracleHomes2/agent10g/cfgtoollogs/oui/configActions2009-07-05_11-46-05-AM.log

ha2-> cat /oragrid/OracleHomes2/agent10g/cfgtoollogs/oui/configActions2009-07-05_11-46-05-AM.log

Unable to complete request: Parameter id=b_isoneoffrequired was not found

in aggregate oracle.sysman.top.agent:10.2.0.1.1:common:family=CFM:oh=/oragrid/OracleHomes2/agent10g:label=1

oracle.sysman.emCfg.common.CfwException: Parameter id=b_isoneoffrequired was not found

in aggregate oracle.sysman.top.agent:10.2.0.1.1:common:family=CFM:oh=/oragrid/OracleHomes2/agent10g:label=1

at oracle.sysman.emCfg.core.CfmAggregateHolder.getParameterPair(CfmAggregateHolder.java:265)

at oracle.sysman.emCfg.core.CfmAggregateHolder.getParameterReference(CfmAggregateHolder.java:876)

at oracle.sysman.emCfg.core.CfmSession.processAggregateCommands(CfmSession.java:231)

at oracle.sysman.emCfg.core.CfmSession.command(CfmSession.java:136)

at oracle.sysman.emCfg.core.CfmSession.cmd(CfmSession.java:106)

at oracle.sysman.emCfg.client.CfwSession.cmd(CfwSession.java:194)

at oracle.sysman.emCfg.client.CfwAggregate.getParameterReference(CfwAggregate.java:58)

at oracle.sysman.emCfg.common.EmCfgActionPerform.updateParameters(EmCfgActionPerform.java:810)

at oracle.sysman.emCfg.common.EmCfgActionPerform.initialize(EmCfgActionPerform.java:300)

at oracle.sysman.emCfg.common.EmCfgActionPerform.perform(EmCfgActionPerform.java:307)

at oracle.sysman.emCfg.common.EmCfgActionPerform.serviceRequest(EmCfgActionPerform.java:734)

at oracle.sysman.emCfg.common.EmCfgActionPerform.start(EmCfgActionPerform.java:713)

at oracle.sysman.oii.oiic.OiicRunConfig.main(OiicRunConfig.java:998)

It seems I am the first person in the world having this stack; b_isoneoffrequired cannot be found on the search engines.
In reality it is contained in agentca.pl

if ( $action eq “REDISCOVER”)

{

print RSPFILE “${prefix}b_isoneoffrequired=false\n”;

}

elsif( $action eq “RECONFIGURE”)

{

print RSPFILE “${prefix}b_isoneoffrequired=true\n”;

}

ha2-> find /oragrid -name response_file

/oragrid/OracleHomes2/agent10g/response_file

ha2-> cat /oragrid/OracleHomes2/agent10g/response_file

oracle.sysman.top.agent|b_startAgent=TRUE

oracle.sysman.top.agent|b_onlyStartAgent=FALSE

oracle.sysman.top.agent|b_upgrade=FALSE

oracle.sysman.top.agent|b_agentUpgrade=FALSE

oracle.sysman.top.agent|b_noUpgrade=TRUE

oracle.sysman.top.agent|b_configure=FALSE

oracle.sysman.top.agent|b_doDiscovery=TRUE

oracle.sysman.top.agent|b_secureAgent=FALSE

oracle.sysman.top.agent|b_isoneoffrequired=false


The temptation is now to modify targets.xml manually following the example on ha1; let’s see what happens

ha2-> cat /oragrid/OracleHomes2/agent10g/sysman/emd/targets.xml

<Targets AGENT_TOKEN=”96298cdb5b8d2e6573665ae2c456fc6ccc53cb65″>

<Target TYPE=”oracle_emd” NAME=”ha2:3872″/>

<Target TYPE=”host” NAME=”ha2″/>

<Target TYPE=”oracle_database” NAME=”SBF1″>

<Property NAME=”OracleHome” VALUE=”/opt/oracle/product/11g/db_1″/>

<Property NAME=”UserName” VALUE=”ad1571087c13ba1a” ENCRYPTED=”TRUE”/>

<Property NAME=”MachineName” VALUE=”ha2″/>

<Property NAME=”Port” VALUE=”1521″/>

<Property NAME=”SID” VALUE=”SBF1″/>

<Property NAME=”ServiceName” VALUE=”SBF1″/>

</Target>

<Target TYPE=”oracle_listener” NAME=”LISTENER_SBF1_ha2″>

<Property NAME=”ListenerOraDir” VALUE=”/opt/oracle/product/11g/db_1/network/admin”/>

<Property NAME=”LsnrName” VALUE=”LISTENER_SBF1″/>

<Property NAME=”Machine” VALUE=”ha2″/>

<Property NAME=”OracleHome” VALUE=”/opt/oracle/product/11g/db_1″/>

<Property NAME=”Port” VALUE=”1521″/>

</Target>

</Targets>

By stopping and restarting the agent, the targets on ha2 are now visible

This article seems to teach that manually modifying targets.xml works … well, in this case it did.

Yet, I will try to figure out why the database and the listener are not discovered by the agent.

June 2, 2009 Posted by | oracle | Leave a comment