ORA-00257: archiver error. Connect internal only, until freed.

ORA-00257: archiver error. Connect internal only, until freed.

Cause: The archiver process received an error while trying to archive a redo log. If the problem is not resolved soon, the database will stop executing transactions. The most likely cause of this message is the destination device is out of space to store the redo log file.

Action: Check the archiver trace file for a detailed description of the problem. Also, verify that the device specified in the initialization parameter ARCHIVE_LOG_DEST is set up properly for archiving.

ORA-000257 error is somewhat common error and is caused whenever archiver is unable to archive the online redo log files at the desired location. Usually this error cocurs when archive destination is full and the archiver was not able to write any more due to lack of space.

So for understanding this ORA-000257 error, it is better knowing the concepts of Oracle Archiving. SO here are the archiving details

What is archiving and Oracle archived redo logs ?

The folowing explanation excerpt from Oracle documentation
"Oracle Database lets you save filled groups of redo log files to one or more offline destinations, known collectively as the archived redo log, or more simply the archive log. The process of turning redo log files into archived redo log files is called archiving. This process is only possible if the database is running in ARCHIVELOG mode. You can choose automatic or manual archiving.

An archived redo log file is a copy of one of the filled members of a redo log group. It includes the redo entries and the unique log sequence number of the identical member of the redo log group. For example, if you are multiplexing your redo log, and if group 1 contains identical member files a_log1 and b_log1, then the archiver process (ARCn) will archive one of these member files. Should a_log1 become corrupted, then ARCn can still archive the identical b_log1. The archived redo log contains a copy of every group created since you enabled archiving.

When the database is running in ARCHIVELOG mode, the log writer process (LGWR) cannot reuse and hence overwrite a redo log group until it has been archived. The background process ARCn automates archiving operations when automatic archiving is enabled. The database starts multiple archiver processes as needed to ensure that the archiving of filled redo logs does not fall behind."

Why archived redo logs are used?

-To Recover a database

-To Update a standby database

-To Get information about the history of a database using the LogMiner utility


Oracle database can be run in two modes (1) ARCHIVELOG mode (2) NOARCHIVELOG mode

NOARCHIVELOG Mode:

When you run your database in NOARCHIVELOG mode, you disable the archiving of the redo log.

NOARCHIVELOG mode protects a database from instance failure but not from media failure. Only the most recent changes made to the database, which are stored in the online redo log groups, are available for instance recovery. If a media failure occurs while the database is in NOARCHIVELOG mode, you can only restore the database to the point of the most recent full database backup. You cannot recover transactions subsequent to that backup.

In NOARCHIVELOG mode you cannot perform online tablespace backups, nor can you use online tablespace backups taken earlier while the database was in ARCHIVELOG mode. To restore a database operating in NOARCHIVELOG mode, you can use only whole database backups taken while the database is closed. Therefore, if you decide to operate a database in NOARCHIVELOG mode, take whole database backups at regular, frequent intervals.

ARCHIVELOG Mode:

When you run a database in ARCHIVELOG mode, you enable the archiving of the redo log. The database control file indicates that a group of filled redo log files cannot be reused by LGWR until the group is archived. A filled group becomes available for archiving immediately after a redo log switch occurs.

The archiving of filled groups has these advantages:

A database backup, together with online and archived redo log files, guarantees that you can recover all committed transactions in the event of an operating system or disk failure.

If you keep an archived log, you can use a backup taken while the database is open and in normal system use.

You can keep a standby database current with its original database by continuously applying the original archived redo logs to the standby.


So, If you cannot afford to lose any data in your database in the event of a disk failure, use ARCHIVELOG mode.
In reality most of the Oracle databases run in ARCHIVELOG mode.

The Oracle error ORA-000257, which we are discussing occurs in the databases that run in ARCHIVELOG mode.

Where does the Archive Logs stored/resides?

Oracle database lets you specify whether to archive, redo logs to a single destination or multiplex them. If you want to archive only to a single destination, you specify that destination in the LOG_ARCHIVE_DEST initialization parameter. If you want to multiplex the archived logs, you can choose whether to archive to up to ten locations (using the LOG_ARCHIVE_DEST_n parameters) or to archive only to a primary and secondary destination (using LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_DEST).

Two methods for specifying archive destination:

Method 1: Using the LOG_ARCHIVE_DEST_n Parameter

Use the LOG_ARCHIVE_DEST_n parameter (where n is an integer from 1 to 10) to specify from one to ten different destinations for archival. Each numerically suffixed parameter uniquely identifies an individual destination.

Steps for setting archivelog destination:

step1 )Use SQL*Plus to shut down the database.

SHUTDOWN

step2 )Set the LOG_ARCHIVE_DEST_n initialization parameter to specify from one to ten archiving locations. The LOCATION keyword specifies an operating system specific path name. For example, enter:

LOG_ARCHIVE_DEST_1 = 'LOCATION = /disk1/archive'
LOG_ARCHIVE_DEST_2 = 'LOCATION = /disk2/archive'

Step3) Optionally, set the LOG_ARCHIVE_FORMAT initialization parameter, using %t to include the thread number as part of the file name, %s to include the log sequence number, and %r to include the resetlogs ID (a timestamp value represented in ub4). Use capital letters (%T, %S, and %R) to pad the file name to the left with zeroes.
example for UNIX OS:
LOG_ARCHIVE_FORMAT=%t_%s_%r.dbf

Method 2: Using LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_DEST

To specify a maximum of two locations, use the LOG_ARCHIVE_DEST parameter to specify a primary archive destination and the LOG_ARCHIVE_DUPLEX_DEST to specify an optional secondary archive destination. All locations must be local. Whenever the database archives a redo log, it archives it to every destination specified by either set of parameters.

Perform the following steps the use method 2:

step1) Use SQL*Plus to shut down the database.

SHUTDOWN

Step2) Specify destinations for the LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_DEST parameter (you can also specify LOG_ARCHIVE_DUPLEX_DEST dynamically using the ALTER SYSTEM statement). For example, enter:

LOG_ARCHIVE_DEST = '/disk1/archive'
LOG_ARCHIVE_DUPLEX_DEST = '/disk2/archive'

Step3) Set the LOG_ARCHIVE_FORMAT initialization parameter.
example for UNIX OS:
LOG_ARCHIVE_FORMAT=%t_%s_%r.dbf


The Oracle error ORA-000257, which we are discussing occurs in the databases that run in ARCHIVELOG mode when the above mentioned archive destinations are full and when the archiver was unable to write to these archive destinations

In order to resolve this error you need to find the archive log info from the database. For that Oracle provides number of internal views and commands. The following are those details:

Oracle Archive related Views:

V$DATABASE Shows if the database is in ARCHIVELOG or NOARCHIVELOG mode and if MANUAL (archiving mode) has been specified.
V$ARCHIVED_LOG Displays historical archived log information from the control file. If you use a recovery catalog, the RC_ARCHIVED_LOG view contains similar information.
V$ARCHIVE_DEST Describes the current instance, all archive destinations, and the current value, mode, and status of these destinations.
V$ARCHIVE_PROCESSES Displays information about the state of the various archive processes for an instance.
V$BACKUP_REDOLOG Contains information about any backups of archived logs. If you use a recovery catalog, the RC_BACKUP_REDOLOG contains similar information.
V$LOG Displays all redo log groups for the database and indicates which need to be archived.
V$LOG_HISTORY Contains log history information such as which logs have been archived and the SCN range for each archived log.

To find which redo log group requires archiving run the folowing query:

SELECT GROUP#, ARCHIVED
FROM SYS.V$LOG;

GROUP# ARC
-------- ---
1 YES
2 NO

To find the current archiving mode run the following query:

SELECT LOG_MODE FROM SYS.V$DATABASE;

LOG_MODE
------------
NOARCHIVELOG



The ARCHIVE LOG LIST Command
The SQL*Plus command ARCHIVE LOG LIST displays archiving information for the connected instance. For example:

SQL> ARCHIVE LOG LIST

Database log mode Archive Mode
Automatic archival Enabled
Archive destination D:\oracle\oradata\TESTDB1\archive
Oldest online log sequence 11270
Next log sequence to archive 11274
Current log sequence 11274

This display tells you all the necessary information regarding the archived redo log settings for the current instance:

The database is currently operating in ARCHIVELOG mode.

Automatic archiving is enabled.

The archived redo log destination is D:\oracle\oradata\TESTDB1\archive.

The oldest filled redo log group has a sequence number of 11270.

The next filled redo log group to archive has a sequence number of 11274.

The current redo log file has a sequence number of 11274.


RESOLUTION/Fix to ORA-00257 error:

Increase the free space in the archive log destination directories. The location where archiver archives the log is determined by parameter file pfile or spfile.
You can find the archive log destination by running the following command from SQL*Plus
SQL> show parameter log_archive_dest

You can also find archive destination information using above mentioned oracle internal views and commands mainly by using " archive log list" command

In case no more space is available at the current archive destination due to OS / Disk limitations , you can specify a new desination location by setting the parameter log_archive_dest (or log_archive_dest_1 in some cases) so that the new archives are produced at new location.
You can do this modifying init.ora file or using alter system if spfile is present

SQL> alter system set log_archive_dest_1=’LOCATION = /disk9/archive'

The other option is to take a backup of the archives from the current desination and delete those archives from that place so that new archives can generated at the current destination.
The backup can be OS level backup and OS level file deletion BUT the recommended method for ASM in place is taking RMAN backup and delete using RMAN.
Eample:
rman target sys/sys

RMAN> backup archive log all device type disk format ‘/oracle/arch_%U’;

RMAN> delete archive until time ‘trunc(sysdate)’;

This will delete all the archive logs until today and space will freed and the archiver will start archiving redo logs

CONCLUSION:

The ORA-00257 Oracle error is related to archiving of the oracele database redo logs and it requires immmediate attention of the DBA. So Oracle users when you encounter this error notify the DBA so that this error will be corrected abd instance crashes will be avoided.
This error is mainly caused because of lack of space at the archive destination. Oracle provides number of views and commands to find the archivelog, archive destination info as mentioned above in the article, by using them we can find identify which archive destination ran out of space and can take appropriate action as described in the resolution to the error.

I hope this article is informative enough to identify root cause for ORA-00257 error, to provide insights on oracle ARCHIVELOG feature and to help in resolving ORA-000257 error. More Details at http://ora-00257.ora-code.info/.

Mendus, Oracle OCP
www.ora-code.info

ORA-28009 connection to sys should be as sysdba or sysoper

ORA-28009: connection to sys should be as sysdba or sysoper

Cause: Connect sys/password is no longer a valid syntax.

Action: Try connect sys/password as sysdba or connect sys/password as sysoper. ORA-28009 oracle error is raised when the sys user tries to connect without providing "as sysdba" . This s controlled by the O7_DICTIONARY_ACCESSIBILITY parameter. When this parameter value is set to FALSE, sys users experience this ORA-28009 error. Access to dictionary objects is restricted to the users with the system privileges SYSDBA and SYSOPER. Connecting as SYSDBA gives a user unrestricted privileges to perform any operation on a database or the objects within a database. Data dictionary objects is under SYS schema and is protected by O7_DICTIONARY_ACCESSIBILITY to FALSE settings. Example for the ORA-28009 error:
Connecting as sys user without sysdba privilege. SQL> alter user SYS identified by SYS; SQL> connect sys/sys ERROR: ORA-28009: connection to sys should be as sysdba or sysoper Warning: You are no longer connected to ORACLE. Connecting as sys user with sysdba privilege. SQL> connect sys/sys as sysdba Connected. SQL> O7_DICTIONARY_ACCESSIBILITY parameter info: SQL> SHOW PARAMETER O7_DICTIONARY_ACCESSIBILITY NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ O7_DICTIONARY_ACCESSIBILITY boolean FALSE Modifying the O7_DICTIONARY_ACCESSIBILITY parameter value:
SQL> ALTER SYSTEM SET O7_DICTIONARY_ACCESSIBILITY=TRUE scope=spfile; System altered.

In order make the chnages effective shutdown and startup the database:
SQL> show parameter O7_DICTIONARY_ACCESSIBILITY NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ O7_DICTIONARY_ACCESSIBILITY boolean TRUE Now sys user should be able connect with out "as sysdba" as verified below: SQL> conn sys/sys Connected.

CONCLUSION: Oracle Recommendation is not to set O7_DICTIONARY_ACCESSIBILITY to TRUE.
So in order to avoid this ORA-28009 error, always explicitly specify " as sysdba " when connecting as sys user.

More Details at http://ora-28009.ora-code.info/.

Mendus, Oracle OCP
http://www.ora-code.info/

ORA-00060: deadlock detected while waiting for resource

ORA-00060: deadlock detected while waiting for resource
Cause: Transactions deadlocked one another while waiting for resources.
Action: Look at the trace file to see the transactions and resources involved. Retry if necessary.

ORA-00060 error indicates that a dead lock occurred due to a resource contention with another session and Oracle rolled back your current statement to resolve the dead lock. The other session can proceed further as usual . Your current sessions rolled backed statements needs to be resubmitted for the execution after the required resources are available.

These dead locks can occur in different scenarios: They can occur while doing parallel DML operations, while updating/deleting data from same tables from different sessions , while performing transactions on bitmap index tables and so on but the mentioned scenarios are the most common ones.

At this point I think we would like to explore more about dead locks, so here are the in depth details on dead locks:

What is dead lock, when it occurs and how to fix it?

Dead lock occurs when two or more users waiting for the resources locked by each other. The users are stuck and they can not proceed as they may end up waiting indefinitely for the resources form each other. So when this condition occurs these users are stuck (deadly embraced) and cannot continue processing.

Oracle automatically detects deadlocks and resolves them by rolling back one of the transactions/statements involved in the deadlock, thus releasing one set of resources/data locked by that transaction. The session that is rolled back will observe Oracle error: ORA-00060: deadlock detected while waiting for resource. Oracle will also produce detailed information in a trace file under database's UDUMP directory.

Most commonly these deadlocks are caused by the applications that involve multi table updates in the same transaction and multiple applications/transactions are acting on the same table at the same time. These multi-table deadlocks can be avoided by locking tables in same order in all applications/transactions, thus preventing a deadlock condition.

In the following example I am demonstrating a dead lock scenario . In the first session ie. session1 lock table: CUSTOMER then CONTACT; and in session2: CONATCT then CUSTOMER.

Here is an example of how to simulate a deadlock error:

Session 1 lock table CUSTOMER:
SQL> update customer set concatenate_bills_boo = 'T' where customer_ref = '10000000000000000486';

1 row updated.

Session 2 lock table CONTACT:

SQL> update contact set language_id = 8 where customer_ref = '10000000000000000486';

1 row updated.

Session 1 now update CONTACT. The session will hang waiting for a lock (not a deadlock yet!):

SQL> update contact set language_id = 8 where customer_ref = '10000000000000000486';

Session 2 now update CUSTOMER, causing the deadlock:

SQL> update customer set concatenate_bills_boo = 'T' where customer_ref = '10000000000000000486';
update customer set concatenate_bills_boo = 'T' where customer_ref = '10000000000000000486'
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource

SQL>

Resolution/Fix for deadly Embrace Dead Lock:

The session on which ORA-00060 occurred encountered the dead lock and Oracle automatically rolled back the statement that caused the dead lock. No other session got affected, so other sessions functions as usual.

In the affected session, the rolled back statement needs to be re-executed once the resources are available ie. When no other session is competing for the same resource.

Probably it will be a good idea to analyze oracle trace file and then modify the application to avoid this situation.

To avoid deadly embrace dead locks, review the application transaction logic thoroughly in the design phase it-self and ensure tables are ordered with in the transactions/applications in such a way that resource contention not occurs among transactions/applications.

In order to find the lock information from the database on which objects / tables have locked currently, which are the blocking sessions and users, ,you can find lot of useful information and scripts at ORA-00054 resource busy error article.


ITL shortage deadlocks :

ITL shortage deadlocks are those dead locks ORA-00060 errors that occur during the execution of UPDATE and DELETE statements where two processes wait for 'S' mode locks on each other's 'TX' enqueues. This cannot happen with INSERT statements, as Oracle doesn't wait on ITL (Interested Transaction List) slots for inserts, it will simply try to insert the row into the next available block.

Resolution/Fix for ITL Dead locks:

To resolve ITL dead locks , recreate the segment with higher INITTRANS and/or PCTFREE values. This will allow more space in the data blocks for Oracle to allocate more transaction entries (24 bytes at a time) when required.

Monitoring ITL waits :

Query the sys.v_$segment_statistics view for identifying and monitoring ITL waits per segments


Other causes for Dead Locks:

Bitmap indexes and dead locks:

High transaction activity on tables with bitmap indexes often causes dead locks. Bitmap indexes are only appropriate in read only/ read mostly environments.

Resolution to Bitmap Index dead locks:

This can be resolved by setting a very high INITTRANS value for the bitmap index but most cases it would be better & best to disable the index before heavy transactional activity, and to rebuild it after heavy transactional activity is done.

Additional dead lock related information :

Bitmap dead lock related info:

You can find more details on bitmap indexes and how they trigger dead locks at asktom by Thomas Kyte.

ITL Waits :

The following is very insightful article on ITL by Arup Nanda with some scripts for identifying the ITL waits.. Originally I thought to provide a link to this article but later I felt this article is very important , informative so I didn’t want the readers to miss/lose this info if that link changed by chance. So I am providing the article inline here with a sole purpose of making the freely available information on the internet in a readily usable form.

Here is the article on ITL:

“Oracle Interested Transaction List (ITL) Waits
by Arup Nanda
Author of Oracle Privacy Security Auditing

What is ITL? Ever wondered how Oracle locks rows on behalf of transactions? In some RDBMS vendor implementations, a lock manager maintains information on which row is locked by which transaction. This works great in theory, but soon the lock manager becomes a single point of contention, as each transaction must wait to get a lock from the manager and then wait again to release the lock.

This severely limits the scalability of the applications. In fact, application developers of some RDBMS products despise holding locks for a long time, and often resort to a full table lock when all that's needed is to get a few rows locked. This creates further waits, and consequently, scalability suffers.

So how is that different in Oracle? For starters, there is no lock manager. When a row is locked by a transaction, that information is placed in the block header where the row is located. When another transaction wishes to acquire the lock on the same row, it has to travel to the block containing the row anyway, and upon reaching the block, it can easily tell that the row is locked from the block header. There is no need to queue up for some single resource like a lock manager. This makes applications immensely scalable.

So, what portion of the block header contains information on locking? It is a simple data structure called "Interested Transaction List" (ITL), a linked list data structure that maintains information on transaction address and rowid. ITL contains several slots or place holders for transactions. When a row in the block is locked for the first time, the transaction places a lock in one of the slots with the rowid of the row that is locked. In other words, the transaction makes it known that it is interested in the row (hence the name "Interested Transaction List").

When the same transaction or another one locks another row, the information is stored in another slot, and so on. After a transaction ends via commit or a rollback, the locks are released and so are the slots that were used to mark the blocks, and these newly freed slots are reused for the other transactions. So there is in fact a queue, but it's at a block level, not at the entire database level or even at a segment level.

The next logical question that comes up is, how many slots are typically available? During the table creation, the INITRANS parameter defines how many slots are initially created in the ITL. When the transactions exhaust all the available slots and a new transaction comes in to lock a row, the ITL grows to create another slot. The ITL can grow up to the number defined by the MAXTRANS parameter of the table, provided there is space in the block.

Nevertheless, if there is no more room in the block, even if the MAXTRANS is high enough, the ITL cannot grow.

So, what happens when a transaction does not find a free slot to place its lock information? This can occur because either (i) the block is so packed that the ITL cannot grow to create a free slot, or (ii) the MAXTRANS has already been reached. In this case, the transaction that needs to lock a row has to wait until a slot becomes available. This wait is termed as ITL waits and can be seen from the view v$session_wait, in which the session is waiting on an event named "enqueue." Since the INITRANS is one, there is only one slot for the ITL. The rest of the block is empty.

Then another transaction, Txn2, updates the row Row2 and wants to lock the row. However, there are no more slots in the ITL available to service the transaction. The MAXTRANS entry is 11, meaning the ITL can grow up to 11 slots and the block has empty space. Therefore, ITL can grow by another slot and Slot number two is created and allocated to Txn2 (refer to figure 4).

Now the empty space in the block is severely limited, and it will not be able to fit another ITL slot. If at this time another transaction comes in to update the row three, it must have a free slot in the ITL. The MAXTRANS is 11 and currently only two slots have been created, so another one is possible; but since there is no room in the block to grow, the slot can't be created. Therefore, the Txn3 has to wait until either of the other transactions rolls back or commits and the slot held by it becomes free. At this time the session will experience an ITL waits event as seen from the view V$SESSION_WAIT.

To better illustrate the concept, let's illustrate such waits using a case. Create the following table and then populate it with several rows. Note MAXTRANS value.
CREATE TABLE TAB1
( COL1 NUMBER,
COL2 VARCHAR2(200))
INITRANS 1 MAXTRANS 1
/

DECLARE
I NUMBER;
BEGIN
FOR I IN 1..10000 LOOP
INSERT INTO TAB1 VALUES
(I,'SSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSS');
END LOOP;
COMMIT;
END;
/


Now update a row of the table from one session, but do not commit it.
UPDATE TAB1 SET COL2 = 'UPDATED' WHERE COL1 = 1;

From another session, update row number two and do not update it.
UPDATE TAB1 SET COL2 = 'UPDATED' WHERE COL1 = 2;

This session will wait. Why? It's updating a row for COL1 = 2, not the same row updated in the other session for COL1 = 1. So why is the session waiting? It's because the first transaction occupied the only available ITL slot. The second transaction needed another slot to place its lock information, but since the MAXTRANS I defined is one, the ITL could not grow to create another slot. Thus, the latter transaction has to wait until the former session releases the lock and makes the slot available. Now increase the MAXTRANS of the table by issuing

ALTER TABLE TAB1 MAXTRANS 11;

and redo the above test. The second session will not wait this time because the ITL had enough free slots for both transactions. How to Reduce ITL WaitsThe primary cause of ITL waits is that free slots in the ITL are not available. This can be due to low setting of the MAXTRANS, which places a hard limit on the number of transactions that can have locks on a block or, the block is so packed that there is no room for the ITL to grow OR or both.

Therefore, setting a high value of INITRANS will make sure that there are enough free slots in the ITL, and there will be minimal or no dynamic extension of the ITL. However, doing so also means that there is less space in the block for actual data, increasing wasted space.

The other option is to making sure the data is less packed so that ITL can grow enough to accommodate the surges in ITL. This can be done by increasing PCTFREE, increasing FREELISTS and FREELIST GROUPS parameters for a table.

This will make a block hold less data and more room for the ITL to grow. As a direct result of the reduction in packing, the table will experience fewer buffer busy wait events, and performance will be increased.
How to Diagnose the ITL WaitHow do you know that a segment is experiencing ITL waits? The best answer will be found in the Segment Level Statistics provided in Oracle9i Release 2. To check for ITL waits, set up the STATISTICS_LEVEL to TYPICAL in init.ora or via ALTER SYSTEM, then examine the segment statistics for the waits.

SELECT
OWNER,
OBJECT_NAME
FROM
V$SEGMENT_STATISTICS
WHERE
STATISTIC_NAME = 'ITL waits'
AND
VALUE > 0;

This unearths the objects that were subjected to ITL waits since the start up of the instance. Note that this view resets when the instance bounces. (For a more detailed explanation of this view and how to set it up, please refer to the article by this author here in DBAzine.)
In versions earlier than 9i, checking for ITL waits is tricky.

When you suspect that a database is suffering from these waits, query the view v$session_wait. If the event on which the system is waiting is "enqueue," then the session might be experiencing ITL waits. However, enqueue is a very broad event that encompasses any type of locks, so it does not accurately specify the ITL waits. When the wait event is experienced, issue the following complex query:

Select s.sid SID,
s.serial# Serial#,
l.type type,
' ' object_name,
lmode held,
request request
from v$lock l, v$session s, v$process p
where s.sid = l.sid and
s.username <> ' ' and
s.paddr = p.addr and
l.type <> 'TM' and
(l.type <> 'TX' or l.type = 'TX' and l.lmode <> 6)
union
select s.sid SID,
s.serial# Serial#,
l.type type,
object_name object_name,
lmode held,
request request
from v$lock l, v$session s, v$process p, sys.dba_objects o
where s.sid = l.sid and
o.object_id = l.id1 and
l.type = 'TM' and
s.username <> ' ' and
s.paddr = p.addr
union
select s.sid SID,
s.serial# Serial#,
l.type type,
'(Rollback='||rtrim(r.name)||')' object_name,
lmode held,
request request
from v$lock l, v$session s, v$process p, v$rollname r
where s.sid = l.sid and
l.type = 'TX' and
l.lmode = 6 and
trunc(l.id1/65536) = r.usn and
s.username <> ' ' and
s.paddr = p.addr
order by 5, 6
/

The output of the query will look something like this:

SID SERIAL# TY OBJECT_NAM HELD REQUEST
----- ------- -- ---------- ---------- --------
36 8428 TX 0 4
36 8428 TM TAB1 3 0
52 29592 TM TAB1 3 0
52 29592 TX (Rollback=RBS1_6) 6 0

Note how the sessions 36 and 52 both have a TM (DML) lock on the table TAB1 of type 3 (Row Exclusive), but session 52 also holds a TX (Transaction) lock on the rollback segment of mode 6 (Exclusive) and Session 36 is waiting for a mode 4 (Share) lock. If this combination of locking occurs, you can be sure that session 36 is waiting for ITL on the table TAB1. Beware of a similar but different diagnosis when two sessions try to insert the same key value (a real locking – primary key violation). In that case, you would also see an additional TX lock on a rollback segment from the session that is waiting; for ITL waits, this TX lock on the rollback segment would not be seen.

Needless to say, this is a rather convoluted and inaccurate way to diagnose the ITL waits in pre-Oracle9i Release 2 versions.

What INITRANS Value is Optimal? Conversely, how do you know if the INITRANS setting is too high and the space is just being wasted? Ascertaining this is possible by using a few random block dumps from the segment in question. First, find out the header file# and header block# of the segment by issuing the following query:

SELECT HEADER_FILE, HEADER_BLOCK FROM DBA_SEGMENTSWHERE OWNER = '...'AND SEGMENT_NAME = '...';

Use the output of the query to do a block dump of the header block.
ALTER SYSTEM DUMP DATAFILE BLOCK MIN BLOCK MAX ;

This will produce a trace file in the USER_DUMP_DESTINATION directory. Open the trace file and find out the section on extent control via the following:
Extent Control Header

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

Extent Header:: spare1: 0 spare2: 0 #extents: 1 #blocks: 10

last map 0x00000000 #maps: 0 offset: 2080

Highwater:: 0x02011f87 ext#: 0 blk#: 0 ext size: 10

#blocks in seg. hdr's freelists: 0

#blocks below: 0

mapblk 0x00000000 offset: 0

Unlocked

Map Header:: next 0x00000000 #extents: 1 obj#: 53689 flag: 0x40000000

Extent Map

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

0x02011f87 length: 10

Find out the real number of blocks for the segment from dba_segments via the following:
SELECT BLOCKS FROM DBA_SEGMENTS

WHERE OWNER = '...' AND SEGMENT_NAME = '...';

Say this returns 12, and the #blocks shows 10; this means the first two blocks are header blocks; the data starts at the third block. Take a dump of the third block, which is obtained by adding two to the header block# obtained above.
ALTER SYSTEM DUMP DATAFILE BLOCK MIN BLOCK MAX ;

This will produce another trace file in the USER_DUMP_DEST directory. If you issued it during the same session as above, then the trace will be written in the trace file opened earlier. Open the file and locate the following section.
buffer tsn: 8 rdba: 0x02011f88 (8/73608)

scn: 0x0000.389b8d81 seq: 0x01 flg: 0x04 tail: 0x8d810601

frmt: 0x02 chkval: 0x2ef5 type: 0x06=trans data

Block header dump: 0x02011f88

Object id on Block? Y

seg/obj: 0xd1ad csc: 0x00.389b8d81 itc: 4 flg: - typ: 1 - DATA

fsl: 0 fnx: 0x0 ver: 0x01

Itl Xid Uba Flag Lck Scn/Fsc

0x01 0x0003.003.000024cc 0x00804067.050a.13 C-U- 0 scn 0x0000.389b304e

0x02 0x0007.010.00002763 0x00801f49.0453.01 C--- 0 scn 0x0000.389b2628

0x03 0x0002.00a.000025d5 0x00804d42.04b2.25 C--- 0 scn 0x0000.389b2811

0x04 0x0006.006.00002515 0x00800962.03c8.18 CU 0 scn 0x0000.389b3044

This shows some very important information on the block, especially in the ITL section shown above. This table has an INITRANS entry of four, so there are four lines, one each per the ITL. The Flag column above the flag -U- indicates that the particular ITL was used. In this case, only two of the ITLs were used, and the other two were never used. However, this is the case for this block only. By selecting block dumps from other randomly selected blocks, you could have an idea how many ITLs are actually used. Then you may decide to reduce the INITRANS.

Automatic Block Management in Oracle9iIn Oracle9i, the process of space mangement inside a block is somewhat changed due to the introduction of the Automatic Block Management (ABM) feature, also known as Automatic Segment Space Management (ASSM). The option is specified at the tablespace level in the storage parameter as SEGMENT SPACE MANAGEMENT AUTO. For instance, the tablespace TS1 can be created as

CREATE TABESPACE TS1
DATAFILE '...'
EXTENT MANAGEMENT LOCAL

SEGMENT SPACE MANAGEMENT AUTO;The last line of this code does the magic. In the ABM mode, Oracle maintains a bitmap for each segment with the information on the block. A bitmap is a data structure with a bit representing each block. When a block becomes available for INSERT, the information is made available simply by setting the corresponding bit in the bitmap rather than using freelists.

So, what does this have to do with ITL waits? The very cause of ITL waits is not freespace management, but the unavailability of a slot in ITL waits. So you still have to look for ITL waits and correct them using INITRANS and MAXTRANS. In fact, the problem may become exacerbated because the block becomes quite packed following an efficient space management system, and that may lead to lack of space for ITL growth. You can prevent this by keeping a large INITRANS for the segment.


Proper setting of INITRANS and MAXTRANS and packing of the blocks is vital to avoid ITL waits in Oracle. It's interesting to note that locking doesn't cause waits, but rather, the mechanism for locking as well as and poor planning. However, the good news is that this situation can be easily fixed by reorganizing the table and adding more slots to the Interested Transaction List.”



CONCLUSION:

Dead locks do occur in most of the applications and dead locks can be avoided by properly designing the transactions and applications by keeping other transactions and applications in mind. ITL waits and dead locks related to ITL waits can be avoided by setting of INITRANS and MAXTRANS properly. Dead locks during the transactions on bitmap indexed tables can be avoided by performing heavy transactions with no bitmap indexes and after completing the transactions rebuild the bitmap indexes.

More Details at http://ora-00060.ora-code.info/.

I hope you enjoyed the dead lock information and content of this article.

-Mendus, Oracle OCP
http://www.ora-code.info/

ORA-00055 maximum number of DML locks exceeded

ORA-00055 maximum number of DML locks exceeded Cause: Ran out of DML lock state objects. Action: Increase the value of the DML_LOCKS initialization parameter and restart Oracle.
More Details at : http://ora-00055.ora-code.info/

ORA-00054: resource busy and acquire with NOWAIT specified

ORA-00054: resource busy and acquire with NOWAIT specified Cause: Resource interested is busy. Action: Retry if necessary.

ORA-00054 error is the most commonly faced error by the oracle users. This error is related to table locking or object locking. ORA-00054 occur whenever concurrent DDL operations or DML Update/Delete/Insert/Select for Update operations are performed on the same table /object without proper commits.

Usually when ORA 00054 error occurs from a session, at that instant some other session might have acquired "exclusive" lock with uncommited changes on the table or object common to both the sessions.


ORA-00054 Resolution/Fix :

1. Identify the locking session - Find which session or user is blocking your operation.
2. If the lock is free ie. no locking session identified in the first step then you can simply resubmit your operation/statement.
3. If there is a lock then analyze the locking session and user. Based on the priority of your operation vs. locking session/user priority decide whether to wait until lock has been removed or terminate/kill the locking session and proceed with your operation. Usually we end up in waiting for the other session to complete.

So now the question is how to identify the locking sessions?

There are number of inernal tables and views Oracle provides for identifying locks,lock status and their session information.

DBA_BLOCKERS – Shows non-waiting sessions holding locks being waited-on
DBA_DDL_LOCKS – Shows all DDL locks held or being requested
DBA_DML_LOCKS - Shows all DML locks held or being requested
DBA_LOCK_INTERNAL – Displays 1 row for every lock or latch held or being requested with the username of who is holding the lock
DBA_LOCKS - Shows all locks or latches held or being requested
DBA_WAITERS - Shows all sessions waiting on, but not holding waited for locks

These tables or views may not contain all the information about locked session, usename ,process name etc. So you may have to join with v$session , v$locked_object and v$process views to obtain detailed information about the locks.


ORA-00054 and Locking Session Identification Demo:

For this demo you may need use two or three sessions. In the first session perform a DML operation (INSERT) and do not commit
Then in the second session perform another DML operation and try to acquire the lock (ie. select for update ) , we will encounter ORA-00054 error.
To find the locking session execute the provided @lockinfo.sql script
To find the locking sesssion login info execute @Locklogininfo.sql script.

Example:

1st Session:

SQL> create table mytab (n number);

Table created.

SQL> grant all on mytab to migr1dba;

Grant succeeded.

SQL> insert into mytab values(1);

1 row created.

SQL> commit;

Commit complete.

SQL> update mytab set n=2 where n = 1;

1 row updated.

SQL>
........
No Commit yet
........

2nd Session :

SQL> declare
2 n number;
3 begin
4 select n into n from migr1.mytab for update nowait;
5 end;
6 /
declare
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified
ORA-06512: at line 4


SQL>

3rd Session:

SQL> @lockinfo.sql

Wed Oct 15 page 1
Lock Information

Machine name Unix RBS
Locked Object Oracle Username O/S Username of Locker Process SID/Ser# Name Logon Date/Time
-------------------- ---------------- ------------- -------------------- --------- ------------ ---- -------------------
MIGR1.MYTAB MIGR1 (INACTIVE) mig05 INFTEST 9281 151,222 _SYS 2008/10/15 05:39:39
SMU4
$


SQL>

SQL> @Locklogininfo.sql

Oracle Username O/S Username Computer/Terminal SID/Ser# STATUS ID Logon Date/Time Program
-------------------- -------------- --------------------- ------------ -------- ------------ -------------------- --------------------------------------------------
oracle INFTEST 157,1 ACTIVE 4773 2008/10/13 16:47:41 oracle@INFTEST (MMNL)
oracle INFTEST 160,7 ACTIVE 4771 2008/10/13 16:47:41 oracle@INFTEST (MMON)
MIGR1 mig05 INFTEST 151,222 INACTIVE 9281 2008/10/15 05:39:39 sqlplus@INFTEST (TNS V1-V3)
MIGR1DBA mig05 INFTEST 155,34448 INACTIVE 9613 2008/10/15 05:45:58 sqlplus@INFTEST (TNS V1-V3)
MIGR1 mig05 INFTEST 153,65380 ACTIVE 9969 2008/10/15 05:56:49 sqlplus@INFTEST (TNS V1-V3)

14 rows selected.

SQL>


Lock Info Identification Scripts:

The following scripts helps in finding the locking sessions, users , process and their status information.

lockinfo.sql script:

ttitle "Lock Information"
set linesize 150
set echo off
col oruser format a16 heading "Oracle Username"
col osuser format a13 heading "O/S Username"
col obj format a20 heading "Locked Object"
col ss heading "SID/Ser#" format a12
col time heading "Logon Date/Time" format a19
col rs heading "RBS|Name" format a4
col unix heading "Unix|Process" format a9
col computer heading "Machine name|of Locker" format a20
set linesize 120
select owner||'.'||object_name obj
,oracle_username||' ('||s.status||')' oruser
,os_user_name osuser
,machine computer
,l.process unix
,s.sid||','||s.serial# ss
,r.name rs
,to_char(s.logon_time,'yyyy/mm/dd hh24:mi:ss') time
from v$locked_object l
,dba_objects o
,v$session s
,v$transaction t
,v$rollname r
where l.object_id = o.object_id
and s.sid=l.session_id
and s.taddr=t.addr
and t.xidusn=r.usn
order by osuser, ss, obj
/

Locklogininfo.sql script:

set linesize 200
set echo off
set feedback on
col oruser format a20 heading "Oracle Username"
col osuser format a14 heading "O/S Username"
col ss heading "SID/Ser#" format a12
col time heading "Logon Date/Time" format a20
col computer heading "Computer/Terminal" format a21
col program format a50 heading "Program"
col process format 999999 heading "OS|Process|ID"
select username oruser
,osuser osuser
,decode(substr(machine,1,7),'TENFOLD',
substr(machine,9),machine) computer
,s.sid||','||s.serial# ss
,status
,process
,to_char(s.logon_time,'yyyy/mm/dd hh24:mi:ss') time
,program
from v$session s
order by time
/
ttitle off

Conclusion:


ORA-00054 error is the most common error caused by the table or object locks due to concurrent DDL and DML(insert/delete/update/select for update) operations from different sessions.
The resolution for this error is finding the locking sesssion & user then based on priority either wait or terminate the locking session and resubmit the job/operation for successful completion.

In this article scripts have been provided for identifying the locking sessions so that readers can get benifited with the readily available scripts for lock identifiction,

More Details at : http://ora-00054.ora-code.info/

I hope you enjoyed this article

Mendus, Oracle OCP
http://www.ora-code.info/