Dear all,
On 13/Feb/2014@8.02AM13/Feb/2014@8.02AM, I'm found problem about oracle database (10.2.0.4) on Solaris10(Sparc) can connect only user sys (user system and all can't)
sqlplus promp is not immediately return can't connect. It's pending long time and return "connection lost"
I'm check on alert_SID.log and not found any error/warning
Problem Information:
1. Not involve about tns configuration or network. Cause of this problem still occur when logon by other user (not sys) at DB Server machine too.
and we can connect sys as sysdba from client by tns alias
2. After connect to database by sys user. We can't query anything (such as select * from dba_tablespaces is freezing)
3. User system wih role DBA can't connect too.
4. I'm check udump, bdump, cdump, adump for useful trace information. And not found anything durning this error
My Action:
- Restart listener, It's still problem
- Shutdown database by immediate. It's hanging after step "Stopping background process MMNL"
so, I'm shutdown abort and startup. Problem is clear
Thu Feb 13 08:28:16 2014
Shutting down instance: further logons disabled
Thu Feb 13 08:28:16 2014
Stopping background process CJQ0
Thu Feb 13 08:28:16 2014
Stopping background process QMNC
Thu Feb 13 08:28:18 2014
Stopping background process MMNL <-- this is last step show on alert.log
Thu Feb 13 08:30:08 2014
Shutting down instance (abort)
License high water mark = 2926
Instance terminated by USER, pid = 23752
Thu Feb 13 08:30:24 2014
Starting ORACLE instance (normal)
Other action before problem.
We have resize datafile on 7:49AM with complete state and I think not cause of problem.
Thu Feb 13 07:49:12 2014
/* OracleOEM */ ALTER DATABASE DATAFILE '/data/appdb/ar_data01.dbf' RESIZE 32767M
Thu Feb 13 07:49:43 2014
Completed: /* OracleOEM */ ALTER DATABASE DATAFILE '/data/appdb/ar_data01.dbf' RESIZE 32767M
My investigate
1 First I found about this note "No Database User Can Login Except Sys And System because Resource Manager Internal_Quiesce Plan Enabled (Doc ID 396970.1)"
But after check scheduler window. It's not enable.
I'm already test change Devlop Database to "QUIESCE mode". It's can login only sys. Other user can't login. But not same my situation. Because user sys
can query and opeartion normal (my situation user sys is hang after query too)
2 I'm check statistic of server (Solaris10) resource during hang. It's not peak anything (cpu, mem, iostat is OK)
I'm check /var/adm/message, /var/log/syslog. It's not show any information
Please help me investigate. I'm already open SR to Oracle support. But they ask me very simple missing point such as Tns configuration, connection string.. (-_-).
Thank you