Troubleshooting

1
Troubleshooting
2
Trace Files Each server and background process
can write to an associated trace file When an
internal error is detected by a process, it
dumps information about the error to its trace
file. One special trace file is the ALERT file.
It Keeps the information all internal errors
(ORA-600), block corruption errors (ORA-1578),
and deadlock errors (ORA-60) that occur
3
Administrative operations, such as
CREATE/ALTER/DROP DATABASE/TABLESPACE/ROLLBACK
SEGMENT SQL statements and STARTUP, SHUTDOWN,
ARCHIVE LOG, and RECOVER SQLDBA
statements Several messages and errors relating
to the functions of shared server and dispatcher
processes The values of all initialization
parameters when the database and instance start
4
Specifying the Location of Trace Files All
trace files for background processes and the
ALERT file are written to the destination
specified by the initialization parameter
BACKGROUND_DUMP_DEST. All trace files for
server processes are written to the destination
specified by the initialization parameter
USER_DUMP_DEST
5
ORA-00604 error occurred at recursive SQL level
num CauseAn error occurred while processing a
recursive SQL statement (a statement applying to
internal dictionary tables). Action If the
situation described in the next message on the
stack can be corrected, do so otherwise, contact
Worldwide Customer Support. Such problem is
accompanied by some Secondary errors like
ora-01652. Also this error with secondary error
will be written to trace
6
Common Oracle Errors that DBA face on day-to
-day basis
7
ORA-01000 maximum open cursors exceeded
Cause A host language program attempted to
open too many cursors. The maximum number of
cursors per user is determined by the
initialization parameter OPEN_CURSORS.
Action Modify the program to use fewer
cursors. If this error occurs often, shut down
ORACLE, increase the value of OPEN_CURSORS,
and then restart ORACLE. This problem is
generally encountered in Pro C programs
,VB,D2K applications etc change the
OPEN_CURSORS parameter for init.ora file
8
ORA-01122 data file name - failed verification
check CauseThe information in the data file
is inconsistent with information from the
control file. This could be because the control
file is from a time earlier than the data files,
or the data file size does not match the size
specified in the control file, or the data file
is corrupted. ActionMake certain that the
data files and control files are the correct
files for this database, then retry the
operation. This error is accompanied with
other messages and are encountered during the
startup of the Database. These errors are
reported when we attempt to recover from a
backup.
9
ORA-01545 rollback segment 'name' was not
available Cause Either 1) A non-existent
rollback segment was specified. 2) An instance
tried to acquire a rollback segment that is in
use or offline. 3) An attempt was made to drop
a rollback segment that contains active
transactions. Action Either 1) Check
spelling and specify a valid rollback segment
name. If the name of the rollback segment to be
changed is unknown, reopen the database and
query the data dictionary for the names of
existing rollback segments.
10
2) To start up an instance that tried to
acquire this rollback segment, specify another
rollback segment in the initialization parameter
ROLLBACK_SEGMENTS or bring the tablespace
containing the rollback segment online. To
drop a rollback segment that is in use, shut down
the instance using it or if it needs recovery,
find out the errors that are preventing the
rolling back of the transactions, and take
appropriate actions.
11
By simply taking the rollback name from the
ROLLBACK_SEGMENTS parameter we can start the
Database. Most probable reason is that the
tablespace must be taken offline with IMMEDAITE
option..
12
ORA-1652 to 1654 01652-- No More space for
temporary segment 01653-- No More space to
allocate for table 01654-- No More space to
allocate index 01652 -00000, "unable to extend
temp segment by s in tablespace s" //
Cause Failed to allocate an extent for temp
segment in tablespace. // Action Use ALTER
TABLESPACE ADD DATAFILE statement to add one or
more files to the tablespace indicated.
13
The ORA-165X error is possible the most
encountered errors by the DBAs. It provides 2
parameters SIZE and TABLESPACE_NAME If the
SYSTEM tablespace has space problem than this
error will be accompanied by ORA-604
14
ORA-01628 max of extents (number) reached for
rollback segment number CauseAn attempt was
made to extend a rollback segment that already
has reached its maximum size. ActionIf
possible, increase the value of either the
MAXEXTENTS or PCTINCREASE initialization
parameters. Related errors 163X 1630- for
temp segment in tablespace 1631- for extents in
table 1632- For extents in index
15
ORA-03113 end-of-file on communication channel
This error is always followed by
ORA-03114 not connected to ORACLE Cause A
call to ORACLE was attempted when no connection
was established. Usually this happens because a
user-written program has not logged on. It may
also happen if communication trouble causes a
disconnection. ActionTry again. If the
message recurs and the program is user-written,
check the program For such type of errors
Alert log on server should be looked into.
16
ORA-04031 Out of shared memory when trying to
allocate num bytes(str) CauseMore shared
memory is needed than was allocated in the
SGA. ActionReduce use of shared memory, or
increase the amount of available shared memory
by increasing the value of the
initialization parameter SHARED_POOL_SIZE.
Fragmentation of shared pool memory is
common problem.This is faced when trying to load
a big size packages,procedures so on.
17
ORA-01045 user name lacks CREATE SESSION
privilege logon denied CauseAn attempt was
made to connect to a userid that does not have
create session privilege. ActionIf required,
GRANT the user the CREATE SESSION privilege.
Connect as system and give the grants
required
18
ORA-01950 no privileges on tablespace 'name'
CauseThe attempt to give the user a
tablespace quota failed because the user does
not have the necessary system privileges.
ActionEither grant the user the system
privileges needed to create objects in the
specified tablespace, or grant the user a
specific space resource in the tablespace.
Alter user ltusername gt quota 10M on lttbspnmgt
19
SQLgtDrop user username ORA-01922 CASCADE must
be specified to drop 'name' CauseThe user owns
objects that need to be dropped along with the
user. ActionUse the CASCADE command. Use
cascade to drop user and all objects owned by
user permanently
20
Connectivity Errors All connections to ORACLE
on different computer require listener process
running on the server Example listener.ora and
TNSnames.ora Path is OS dependent usually
located at ORACLE_HOME/network/admin
21
12203, 00000, "TNSunable to connect to
destination" // Cause Invalid TNS address
supplied or destination is not listening.
This error can also occur because of underlying
network transport // problems. // Action Verify
that the service name you entered on the command
line was correct. Ensure that the listener is
running at the remote node and that the ADDRESS
parameters specified in TNSNAMES.ORA are correct.
Finally, check that all Interchanges needed to
make the connection are up and running Start
listener on the Server
22
12154, 00000, "TNScould not resolve service
name" // Cause The service name specified is
not defined correctly in the // TNSNAMES.ORA
file. Enter/Update the entry for the SID for
which connection is sought 12545, 00000,
"Connect failed because target host or object
does not exist" // Cause The address specified
is not valid, or the program being connected to
does not exist. Update the host connection
entry in the TCP/IP alias.
23
ERROR ORA-12505 TNSlistener could not resolve
SID given in connect descriptor // Action
Check to make sure that the SID specified is
correct. The SIDs that are currently registered
with the listener can be obtained by typing
"LSNRCTL SERVICES ltlistener namegt". These SIDs
correspond to SID_NAMEs in TNSNAMES.ORA, or
db_names in INIT.ORA. // Comment This error
will be returned if the database instance has not
registered with the listener the instance may
need to be started.
24
12500, 00000, "TNSlistener failed to start a
dedicated server process" // Cause The
process of starting up a dedicated server
process failed. The executable could not be
found or the environment may be set up
incorrectly. This is a O/S error . Such problem
arises only when the number of oracle sessions
exhaust. In SCO Unix The increasing MAXUP
process solves the problem