Avatar of Chandan Pratihast
Chandan Pratihast
Flag 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.
OralceConnection.png
Then I tried to connect using sqlplus and there also it remains unresponsive.


How to find the reason for this? Kindly help.
Oracle Database

Avatar of undefined
Last Comment
jtrifts

8/22/2022 - Mon
Chandan Pratihast

ASKER
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?
Chandan Pratihast

ASKER
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.
Chandan Pratihast

ASKER
With the above steps solved my problem.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Franck Pachot

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/
Chandan Pratihast

ASKER
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?
ASKER CERTIFIED SOLUTION
Franck Pachot

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Chandan Pratihast

ASKER
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.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Franck Pachot

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 G

your disk is full ?

and it's hanging, coz that's what it does, when the disk is full
jtrifts

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!
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck