Home
All Oracle Error Codes
Oracle DBA Forum

Frequent Oracle Errors

TNS:could not resolve the connect identifier specified
Backtrace message unwound by exceptions
invalid identifier
PL/SQL compilation error
internal error
missing expression
table or view does not exist
end-of-file on communication channel
TNS:listener unknown in connect descriptor
insufficient privileges
PL/SQL: numeric or value error string
TNS:protocol adapter error
ORACLE not available
target host or object does not exist
invalid number
unable to allocate string bytes of shared memory
resource busy and acquire with NOWAIT specified
error occurred at recursive SQL level string
ORACLE initialization or shutdown in progress
archiver error. Connect internal only, until freed
snapshot too old
unable to extend temp segment by string in tablespace
Credential retrieval failed
missing or invalid option
invalid username/password; logon denied
unable to create INITIAL extent for segment
out of process memory when trying to allocate string bytes
shared memory realm does not exist
cannot insert NULL
TNS:unable to connect to destination
remote database not found ora-02019
exception encountered: core dump
inconsistent datatypes
no data found
TNS:operation timed out
PL/SQL: could not find program
existing state of packages has been discarded
maximum number of processes exceeded
error signaled in parallel query server
ORACLE instance terminated. Disconnection forced
TNS:packet writer failure
see ORA-12699
missing right parenthesis
name is already used by an existing object
cannot identify/lock data file
invalid file operation
quoted string not properly terminated

Investigating ORA-01552 / ORA-00376 errors

Renee Dekenah

2006-06-21

Replies:
Hi,

I am investigating the cause of a problem we had with an undo tablespace
and would appreciate hearing your thoughts on this. While I have fixed
the problem by creating a new replacement undo tablespace, and
everything now seems fine, I am interested in why the problem occurred,
and whether there is a better solution than the one I employed.

Problem Description:

We are running IBM Rational ClearQuest on Oracle 10g R2. On Monday
ClearQuest users started getting the error ORA-01552: cannot use system
rollback segment for non-system tablespace 'CQ_USER' when trying to add
a new entry within ClearQuest.

Full error message from ClearQuest
ERROR! SQLExecute: RETCODE=-1, State=HY000, Native Error=1552
SQL statement="update dbglobal set next_request_id=906 where site_id=2
and next_request_id=905"
[DataDirect][ODBC Oracle Wire Protocol driver][Oracle]ORA-01552: cannot
use system rollback segment for non-system tablespace 'CQ_USER'.

Further Details:

Upon investigation using Enterprise Manager, it appeared that the
CQ_USER tablespace had no space left - a critical error for this was
logged on the preceding Friday - and it was not set to autoextend. We
rectified this by changing it to autoextend in Enterprise Manager and
applying the changes, thus clearing the tablespace critical errors.
(please don't kill me for the tablespace being full, I'm just helping
out an ex-employer, monitoring the database was not my responsibility)

However, this did not resolve the initial Oracle error shown in ClearQuest, so we investigated further.

On Thursday/Friday, the alert error log in BDUMP logged a few ORA-1653
errors relating to tables that could not be extended in the (then)
almost full CQ_USER tablespace.

On the Saturday, there was the following:
KCF: write/open error block 0x29 online=1
file=2 D:\ORACLE\ORADATA\...\UNDOTBS01.DBF
error=27072 text: 'OSD-04008: WriteFile() failure, unable to write to file
O/S-Error: (OS 33) The process cannot access the file because another
process has locked a portion of the file.'
Automatic datafile offline due to write error on
file 2: D:\ORACLE\ORADATA\...\UNDOTBS01.DBF

Since then - even after extending the CQ_USER tablespace on Monday - the
system logged many of these:
Errors in file c:\oracle\admin\...\..._pmon_2604.trc:
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: 'D:\ORACLE\ORADATA\...\UNDOTBS01.DBF'

Enterprise Manager claimed the undo tablespace (UNDOTBS1) was ReadWrite
status, and 0MB in size. When I used the SQLPlus command line to confirm
the status of this tablespace it was listed as AVAILABLE. Dbverify on
the file UNDOTBS01.DBF showed nothing failing or corrupt, but Enterprise
Manager did list this file as "Offline (Needs Recovery)". Based on this,
and some information I found online, I decided to create a new undo
tablespace and switched the database to use that instead. The ClearQuest
error no longer occurs.

Questions:

1. Was the undo datafile being locked/going offline a result of the
tablespace maxing out, or could it have been an unrelated error? (note
that we have Symantec backups running daily on the database server and
database, I seem to remember reading somewhere that backup software can
sometimes cause datafile problems?)

2. Was recreating the undo tablespace the best solution, or should I
have done something else?

Many thanks for any assistance you can offer,

Renee


--
http://www.freelists.org/webpage/oracle-l