Transcript Troubleshooting

Troubleshooting
Nitin Singh,AAO/EDP,RTI Allahabad
1
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
Nitin Singh,AAO/EDP,RTI Allahabad
2
Administrative operations, such as
CREATE/ALTER/DROP
DATABASE/TABLESPACE/ROLLBACK SEGMENT SQL
statements and STARTUP, SHUTDOWN, ARCHIVE
LOG, and RECOVER SQL*DBA 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
Nitin Singh,AAO/EDP,RTI Allahabad
3
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
Nitin Singh,AAO/EDP,RTI Allahabad
4
ORA-00604 error occurred at recursive SQL level num
Cause:An 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
Nitin Singh,AAO/EDP,RTI Allahabad
5
Common Oracle Errors that DBA
face on day-to -day basis
Nitin Singh,AAO/EDP,RTI Allahabad
6
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_CURSORSNitin
parameter
for init.ora file
Singh,AAO/EDP,RTI Allahabad
7
ORA-01122 data file name - failed verification check
Cause:The 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.
Action:Make 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. Nitin Singh,AAO/EDP,RTI Allahabad
8
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.
Nitin Singh,AAO/EDP,RTI Allahabad
9
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.
Nitin Singh,AAO/EDP,RTI Allahabad
10
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..
Nitin Singh,AAO/EDP,RTI Allahabad
11
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.
Nitin Singh,AAO/EDP,RTI Allahabad
12
The ORA-165X error is possible the most
encountered errors by the DBA’s. It provides 2
parameters SIZE and TABLESPACE_NAME
If the SYSTEM tablespace has space problem than
this error will be accompanied by ORA-604
Nitin Singh,AAO/EDP,RTI Allahabad
13
ORA-01628 max # of extents (number) reached for
rollback segment number
Cause:An attempt was made to extend a rollback
segment that already has reached its maximum size.
Action:If 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
Nitin Singh,AAO/EDP,RTI Allahabad
14
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.
Action:Try 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.
Nitin Singh,AAO/EDP,RTI Allahabad
15
ORA-04031 Out of shared memory when trying to
allocate num bytes(str)
Cause:More shared memory is needed than was
allocated in the SGA.
Action:Reduce 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.
Nitin Singh,AAO/EDP,RTI Allahabad
16
ORA-01045 user name lacks CREATE SESSION
privilege; logon denied
Cause:An attempt was made to connect to a userid
that does not have create session privilege.
Action:If required, GRANT the user the CREATE
SESSION privilege.
Connect as system and give the grants required
Nitin Singh,AAO/EDP,RTI Allahabad
17
ORA-01950 no privileges on tablespace 'name'
Cause:The attempt to give the user a tablespace
quota failed because the user does not have the
necessary system privileges.
Action:Either 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 <username > quota 10M on <tbspnm>
Nitin Singh,AAO/EDP,RTI Allahabad
18
SQL>Drop user username
ORA-01922 CASCADE must be specified to drop
'name'
Cause:The user owns objects that need to be dropped
along with the user.
Action:Use the CASCADE command.
Use cascade to drop user and all objects owned by
user permanently
Nitin Singh,AAO/EDP,RTI Allahabad
19
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
Nitin Singh,AAO/EDP,RTI Allahabad
20
12203, 00000, "TNS:unable 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
Nitin Singh,AAO/EDP,RTI Allahabad
21
12154, 00000, "TNS:could 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.
Nitin Singh,AAO/EDP,RTI Allahabad
22
ERROR: ORA-12505: TNS:listener 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 <listener
name>". 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.
Nitin Singh,AAO/EDP,RTI Allahabad
23
12500, 00000, "TNS:listener 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
Nitin Singh,AAO/EDP,RTI Allahabad
24