We help IT Professionals succeed at work.

SQLDeveloper hangs while trying to connect OracleDB

Chandan Pratihast
on
2,185 Views
1 Endorsement
Last Modified: 2018-09-23
Hi Experts

Suddenly sqldeveloper is not connecting to oracle instance. It is just trying to connect and hangs on like shown in the below image.
OralceConnection.png
Then I tried to connect using sqlplus and there also it remains unresponsive.


How to find the reason for this? Kindly help.
Comment
Watch Question

Author

Commented:
I am trying to shutdown the instance even that is taking long time and no response. In the alert.log it is giving the message: "PMON has failed to cleanup a dead process for 10200 attempts". How to recover from here?

Author

Commented:
So, I managed to solve it.
Symptoms: Even "shutdown immediate" was also hanging and it was giving message like "PMON has failed..." as mentioned above.
(A) Cancelled the hanging "shutdown immediate" and then executed "shutdown abort".
(B) Then when tried to connect with "sqlplus / as sysdba" it gave TNS-ORA listener error. So, I set ORACLE_SID and then connected.
(C) After that tried "startup restrict". It gave error
 ORA-01078: failure in processing system parameters
LRM-00109
To solve this corrected the file related to mySID.ora.
Then tried again the  "startup restrict" and it completed successfully.
(D) executed the "shutdown immediate", successful this time.
(E) Then startup force; and finally DB up and sql developer connection all okay.

Author

Commented:
With the above steps solved my problem.
Franck PachotOracle DBA
CERTIFIED EXPERT

Commented:
Hi,
Next time don't forget to take a system state for post mortem analysis, but as described here : https://blog.dbi-services.com/oracle-is-hanging-dont-forget-hanganalyze-and-systemstate/

Author

Commented:
Hi FrancK

Thanks for the information about the hang analyze. But fortunately in my case the alert.log is giving enough information.

In the alert.log and it seems that error happened with creation of archive log. Then check the trace file pointed in the alert log.
That also says that it did not find space. Please see the trace log excerpts below.

------ alert log----------------------------------
ORA-19504: failed to create file "+DATA"
ORA-17502: ksfdcre:4 Failed to create file +DATA
ORA-15041: diskgroup "DATA" space exhausted
ARC0: Error 19504 Creating archive log file to '+DATA'
Fri Sep 14 23:04:32 2018
minact-scn: useg scan erroring out with error e:12751
Suspending MMON action 'Block Cleanout Optim, Undo Segment Scan' for 82800 seconds
Fri Sep 14 23:06:42 2018
Unable to create archive log file '+DATA'
Fri Sep 14 23:06:42 2018


-------------------- tracelog---------------
DE rules only execution for: ORA 15041
----- START Event Driven Actions Dump ----
---- END Event Driven Actions Dump ----
----- START DDE Actions Dump -----
Executing SYNC actions
Executing ASYNC actions
----- END DDE Actions Dump (total 0 csec) -----
*** 2018-09-14 22:44:41.750562 4929 krsh.c
Unable to create archive log file '+DATA'
ORA-19504: failed to create file "+DATA"
ORA-17502: ksfdcre:4 Failed to create file +DATA
ORA-15041: diskgroup "DATA" space exhausted
*** 2018-09-14 22:44:41.753977 4929 krsh.c
ARC3: Error 19504 Creating archive log file to '+DATA'
*** 2018-09-14 22:44:41.753994 6959 krsi.c
krsi_dst_fail_caller: dest:1 err:19504 force:0 blast:1
DDE rules only execution for: ORA 312

But why it cause this problem on this Friday night?
Oracle DBA
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION

Author

Commented:
Yes, again some batch executed and same problem re-occuring. SqlDeveloper started to hang. There is no space /opt/app. Taking the back and removing the file from $ORACLE_HOME/trace. Then at the start of the instance will switch of the archivelog.
Franck PachotOracle DBA
CERTIFIED EXPERT

Commented:
Hi, archivelog is mandatory if you want to be able to recover the transactions or do online backups. Check the archive log destination, recovery area usage. Maybe the size is too small, or backup did run (the archive log should be reclaimable when backed up)
Geert GOracle dba
CERTIFIED EXPERT
Top Expert 2009

Commented:
your disk is full ?

and it's hanging, coz that's what it does, when the disk is full
jtriftsMI and Automation
CERTIFIED EXPERT

Commented:
If you are working with a production system, or one that might require point-in-ti e recovery, DO NOT place your DB in NOARCHIVELOG mode!