Saturday, October 31, 2009

Common ORA Errors Faced

ORA:12154 TNS could not resolve Service Name
1) Check the listener, if it is started and listening or not.
2) Check that the instance or database name is being typed in correctly.
3)This is an issue with tnsnames.ora or listener.ora or sqlnet.ora.
Need to check with naming methods we are using and correct for those methods.
The entries for Alias Name with which we are trying to connect got to have same values,
Ie. for Hostname, Port number, Service Name or protocol used.
4) Or the issue might arise because of NIC Card or Network card settings.
The card might have got disabled.
If there are 2 NIC cards, then there might be a conflict with the hostname entries ,
ie. the card with which the database is installed and used might have been disabled.
The IP set for card and the entry in hosts file may get changed.
5) Sometimes if it is taking the localhost as the default entry for the card,
on the same systems where there is no IP set for the system.
Then the loopback address needs to be checked. The entry should be there in hosts file like localhost.localdomain localhost

ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist

1) The instance is actually trying to acquire the shared memory it is assigned and is unable to do the same.
ORACLE_HOME and ORACLE_SID should be set correctly for this to be resolved.
2) The SGA parameters might have been edited and the instance is unable to get the new SGA size allocated.
Replace the original parameters and try again.
3) An abrupt shutdown of the database or the server might have happened,
by which the previous database session has not released the memory yet.
Shutdown the database gracefully, restart the server and try again.

ORA:01652 Unable to extend Temp Segment
This might be caused when TEMP tablespace is out of space. This happens when there is a sorting or any similar activity by a session.
Check the free space using
select sum(free_blocks)
from v$sort_segment
where tablespace_name = 'TEMP’
IF the result is 0,Then add a datafile to the TEMP tablespace
If it is found that there is free space available, then the reason is instance is unable to extend the segment.
Resize the tablespace and add space to it.

Increase sort_area_size and/or pga_aggregate_target using alter session command