| |
 |
|
sqlplus Hang Tips
Oracle Tips by Burleson Consulting
|
Question:
OS is Redhat AS 2, with Oracle 9iR2 freshly installed. Hard
drives with RAID.
Sometimes (twice a week), it becomes impossible to log in with sqlplus,
the command sqlplus user/password hangs (freezes) on the
server, same thing from a client machine.
We have the following message in listener.log, just before sqlplus hangs :
"Plus d' écoute sur : (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(KEY=EXTPROC)))"
(no more listening on ...)
It happens >3 times a week, on the incriminated machine only, never on the other
machine
Exact hardware configuration is :
Hewlett Packard ProLiant ML350 G3
Processor : 1 x Intel Xeon 2,80 GHz ; 533 MHz
RAM : 1,5 Go
ATI Rage XL, 64 Mo
Controller : 1 x HP Smart Array 642
2 x HP Ultra3 BIOS - 64 Bit SCSI UW 3 (Dual Channel) (integrated)
1 x NIC Ethernet Full Duplex Gigabit 7760 (integrated)
3 x HD 34 Go with RAID 5 for total storage amount of 68 Go
The problem is solved when we restart oracle,
but reappears from time to time.
We have exactly the same configuration on another machine,
except raid and hardware, and we don't have the problem at all.
What could be the cause?
Answer:
The answer to this specific problem was that the database
was in archivelog, but not configured for automatic archiving when the redo log
were filled.
The solution was to set automatic archiving (log_archive_start = true)
and set a destination (log_archive_dest=mydir).
This is just one reason for sqlplus hangs. There are
many other reasons as to why sqlplus could hang.
One would be getting into SQL*Plus, or connecting.
In which case one could try this:
oracle>
sqlplus /nolog
If this gets you to the SQL> prompt, try connecting:
SQL>
connect system/manager@mysid
If it hangs at connect time, check your alert log. Perhaps you have exceeded a
parameter such as "processes".
Another possibility is that RedHat might not support your motherboard.
Be sure your OS is supported on your board
If you have checked the alert log, but nothing out of the normal shows
up and are using UNIX try checking the OS session (ps -ef|grep ora)
It's likely waiting to acquire a resource.
Oracle can "hang" for many reasons. In addition to the alert log, you need to
check server-side logs (/etc/syslog, /var/adm/syslog), and check the listener
log files as well as the bdump, cdump and pfile directories for trace files.
Try to bypass all the settings as for the connection string
SQLPLUS
sys/sysadm@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP) (PORT=1521)
(NODE=yourServerName))) (CONNECT_DATA=(SID=yourSid) (SERVER=DEDICATED))) as
sysdba
|
|
|
