Link to home
Start Free TrialLog in
Avatar of Chandan Pratihast
Chandan PratihastFlag for Japan

asked on

SQLDeveloper hangs while trying to connect OracleDB

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.
User generated image
Then I tried to connect using sqlplus and there also it remains unresponsive.

How to find the reason for this? Kindly help.
Avatar of Chandan Pratihast
Chandan Pratihast
Flag of Japan image


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?
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
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.
With the above steps solved my problem.
Avatar of Franck Pachot
Next time don't forget to take a system state for post mortem analysis, but as described here :
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?
Avatar of Franck Pachot
Franck Pachot
Flag of Switzerland image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
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)
your disk is full ?

and it's hanging, coz that's what it does, when the disk is full
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!