Home » RDBMS Server » Server Administration » DB look like hang or spin, Only sys user can connect to DB but can't query anything (Oracle DB 10.2.0.4 Enterprise, Solaris10 Sparce 64bit)
- DB look like hang or spin, Only sys user can connect to DB but can't query anything [message #607952] Thu, 13 February 2014 19:04 Go to next message
kittipong.at
Messages: 4
Registered: February 2014
Location: Thailand
Junior Member
DB look like hang or spin, Only sys user can connect to DB but can't query anything

On 13/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.
I'm check on alert_SID.log and not found any error/warning

Problem Information:
1. Not involve about tnsping, listener 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.. (-_-).

[Updated on: Thu, 13 February 2014 20:21]

Report message to a moderator

- Re: DB look like hang or spin, Only sys user can connect to DB but can't query anything [message #607970 is a reply to message #607952] Fri, 14 February 2014 00:27 Go to previous messageGo to next message
John Watson
Messages: 8976
Registered: January 2010
Location: Global Village
Senior Member
Welcome to the forum. Please read our OraFAQ Forum Guide and please read How to use [code] tags and make your code easier to read

You say that SYS cannot query a DBA view, can you query a V$ view? If so, attempt to logon as a normal user, and then while that session is hanging,
Code: [Select all] [Show/ hide]
select event from v$session_wait where wait_time=0 and wait_class<>'Idle';

Report message to a moderator

- Re: DB look like hang or spin, Only sys user can connect to DB but can't query anything [message #607978 is a reply to message #607970] Fri, 14 February 2014 00:50 Go to previous messageGo to next message
kittipong.at
Messages: 4
Registered: February 2014
Location: Thailand
Junior Member
Hi, John

1. I'm not sure sys can't query v$ or not. I'm test on select dba_tablespace. it's freeze.
2. If so, attempt to logon as a normal user
(Did you mean login sys user by not option "as sysdba"?)
3. Other user can't connect to database (include system user). It's freeze after enter.

Best regards,

Report message to a moderator

- Re: DB look like hang or spin, Only sys user can connect to DB but can't query anything [message #607982 is a reply to message #607978] Fri, 14 February 2014 00:56 Go to previous messageGo to next message
John Watson
Messages: 8976
Registered: January 2010
Location: Global Village
Senior Member
kittipong.at wrote on Fri, 14 February 2014 06:50
Hi, John

1. I'm not sure sys can't query v$ or not. I'm test on select dba_tablespace. it's freeze.

So try to query a V$ view. Go on: just do it.
Quote:
2. If so, attempt to logon as a normal user
(Did you mean login sys user by not option "as sysdba"?)
I meant "logon as a normal user". ie, some who is not SYS.
Quote:
3. Other user can't connect to database (include system user). It's freeze after enter
Yes, that is what you said already. So do it again, and then run my query to find out WHY it is freezing.

Report message to a moderator

- Re: DB look like hang or spin, Only sys user can connect to DB but can't query anything [message #607986 is a reply to message #607982] Fri, 14 February 2014 01:11 Go to previous messageGo to next message
Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

Also check that "audit_file_dest" FS is not full.

[Edit: removed "diagnostic_dest" that does not exist in 10g]

[Updated on: Fri, 14 February 2014 01:12]

Report message to a moderator

- Re: DB look like hang or spin, Only sys user can connect to DB but can't query anything [message #608039 is a reply to message #607986] Fri, 14 February 2014 08:19 Go to previous message
kittipong.at
Messages: 4
Registered: February 2014
Location: Thailand
Junior Member
Hi John,

This problem already gone. Please read my action.
- shutdown database by immediate. It's hanging after step "Stopping background process MMNL"
so, I'm shutdown abort and startup. Problem is clear.

So, I can't simulate same situation for test select v$ as your recommend.

Thank you

Hi, Michel
audit_file_dest is adump on same location with bdump, cdump, udump. It's no problem about free space
and I try to check server have problem about mount point lossing or not. I'm not found any problem like this in OS log file.
Thank you

Best regards,

Report message to a moderator

Previous Topic: Hi folks, We have a production database on RAC Mode which has 4 node setup , we need to refresh
Next Topic: The doubts about column segment_file & relative_fno in v$sort_segment
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sat May 03 11:44:46 CDT 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.