SQLDeveloper hangs while trying to connect OracleDB

Chandan Pratihast
Chandan Pratihast used Ask the Experts™
on
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

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

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.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

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?
Hi, Hard to understand from this. Restarting the instance should not release space. And is that normal that archive logs are created on +DATA? Usually, +DATA is for datafiles and recovery area goes to a different diskgroup.

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.
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
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

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!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial