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

How to find the reason for this? Kindly help.
Chandan PratihastAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Chandan PratihastAuthor 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?
Chandan PratihastAuthor 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
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 PratihastAuthor Commented:
With the above steps solved my problem.
Ensure Business Longevity with As-A-Service

Using the as-a-service approach for your business model allows you to grow your revenue stream with new practice areas, without forcing you to part ways with existing clients just because they don’t fit the mold of your new service offerings.

Franck PachotOracle DBACommented:
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 PratihastAuthor 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?
Franck PachotOracle DBACommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Chandan PratihastAuthor 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 DBACommented:
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 dbaCommented:
your disk is full ?

and it's hanging, coz that's what it does, when the disk is full
jtriftsMI and AutomationCommented:
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!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.