sqlplus / as sysdba hung state

[Former Member]

Hi All,

When we try to do a sqlplus / as sysdba it gets into hung state.

sapkxqap03:oraqs9 2> sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Wed Feb 16 19:06:28 2011

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

We dont get anything after this prompt.Is there a way we can get to know as to what might be the issue.Thanks.

[stefan_koehler · Accepted solution]

Hello Ambarish,

logon to your database with "prelim" option and create a hanganalyze trace and a systemstate dump.

http://tech.e2sn.com/oracle/troubleshooting/hang/how-to-log-on-even-when-sysdba-can-t-do-so

After that check these files or upload them to a file hoster so that we can check them.

Regards

Stefan

[Former Member · Accepted solution]

HI Stefan,

That was simply too good.Finally the issue got resolved by box restart.I dont know why we faced an issue?Its a mystery.The solution proposed by you was simply too good.Thanks.

[Former Member]

Did you apply any patches (OPatches)?

This is just to know whether the above situation is occured post applying of patchsets, Because there was a patch which causes to sqlplus hung situations (It got corrected in latest bundle patch).

Regards,

Nick Loy

[Former Member]

Hi Nick.

No patches had been applied.Thanks.

[Former Member]

Maybe an archiver stuck? But so far we can only guess...

Does R3trans -d work? Check the oracle alertlog and the listener log (even if sqlplus / as sysdba does not connect to the listener).

Post any error messages you might encounter.

Cheers Michael

[Former Member]

Hi Michael,

Nice to hear from you Michael.Long time.Thanks for the response

Checked no archiver struck since the file systems have enough space.The problem is that alter log has not been updated for the past 1 week.R3trans also gets struck since it also uses sqlplus to login.Thanks.,

4 ETW000 R3trans version 6.05 (release 46D - 04.12.08 - 14:43:00).

4 ETW000 ===============================================

4 ETW000

4 ETW000 control file: <no ctrlfile>

4 ETW000 R3trans was called as follows: R3trans -d

4 ETW000 date&time : 16.02.2011 - 21:11:13

4 ETW000 trace at level 2 opened for a given file pointer

4 ETW000 [developertra,00000] Wed Feb 16 21:11:13 2011

232 0.000232

4 ETW000 [developertra,00000] db_con_init called

14 0.000246

4 ETW000 [developertra,00000] create_con (con_name=R/3)

34 0.000280

4 ETW000 [developertra,00000] Loading DB library '/usr/sap/QS9/SYS/exe/run/dbo

raslib.so' ...

4 ETW000

30 0.000310

4 ETW000 [developertra,00000] load shared library (/usr/sap/QS9/SYS/exe/run/db

oraslib.so), hdl 0

4 ETW000

15933 0.016243

4 ETW000 [developertra,00000] Library '/usr/sap/QS9/SYS/exe/run/dboraslib.so'

sapkxqap03:qs9adm 3> more trans.log

4 ETW000 R3trans version 6.05 (release 46D - 04.12.08 - 14:43:00).

4 ETW000 ===============================================

4 ETW000

4 ETW000 control file: <no ctrlfile>

4 ETW000 R3trans was called as follows: R3trans -d

4 ETW000 date&time : 16.02.2011 - 21:11:13

4 ETW000 trace at level 2 opened for a given file pointer

4 ETW000 [developertra,00000] Wed Feb 16 21:11:13 2011 232 0.000232

4 ETW000 [developertra,00000] db_con_init called 14 0.000246

4 ETW000 [developertra,00000] create_con (con_name=R/3) 34 0.000280

4 ETW000 [developertra,00000] Loading DB library '/usr/sap/QS9/SYS/exe/run/dboraslib.so' ...

4 ETW000 30 0.000310

4 ETW000 [developertra,00000] load shared library (/usr/sap/QS9/SYS/exe/run/dboraslib.so), hdl 0

4 ETW000 15933 0.016243

4 ETW000 [developertra,00000] Library '/usr/sap/QS9/SYS/exe/run/dboraslib.so' loaded

4 ETW000 18 0.016261

4 ETW000 [developertra,00000] function DbSlExpFuns loaded from library /usr/sap/QS9/SYS/exe/run/dboraslib.so

4 ETW000 22 0.016283

4 ETW000 [developertra,00000] Version of library '/usr/sap/QS9/SYS/exe/run/dboraslib.so' is "46D.00", patchlevel (0.2438)

4 ETW000 99 0.016382

4 ETW000 [developertra,00000] function dsql_db_init loaded from library /usr/sap/QS9/SYS/exe/run/dboraslib.so

4 ETW000 12 0.016394

4 ETW000 [developertra,00000] function dbdd_exp_funs loaded from library /usr/sap/QS9/SYS/exe/run/dboraslib.so

4 ETW000 19 0.016413

4 ETW000 [developertra,00000] New connection 0 created 17 0.016430

4 ETW000 [developertra,00000] db_con_connect (con_name=R/3) 13 0.016443

4 ETW000 [developertra,00000] find_con found the following connection for reuse:

4 ETW000 10 0.016453

4 ETW000 [developertra,00000] Got ORACLE_HOME=/oracle/QS9/102_64 from environment

4 ETW000 434 0.016887

4 ETW000 [developertra,00000] -->oci_initialize 23 0.016910

4 ETW000 [developertra,00000] Got ORACLE_SID=QS9 from environment 24 0.016934

4 ETW000 [developertra,00000] Got NLS_LANG=AMERICAN_AMERICA.WE8DEC from environment

4 ETW000 13 0.016947

4 ETW000 [developertra,00000] Logon as OPS$-user to get SAPR3's password 11 0.016958

4 ETW000 [developertra,00000] Connecting as /@QS9 on connection 0 ... (dbsl 46D 220209)

4 ETW000 15 0.016973

4 ETW000 [developertra,00000] -->oci_logon(con_hdl=0, user='', dbname='QS9') 15 0.016988

[Former Member]

Hm, no error, ok more guessing. You might be running out of processes, file handles, sockets or something like that. Check parameter processes in your spfile and compare to a 'ps -ef| grep -l oracle|wc -l'.

Also check your operating system log.

Does the SAP system still run? Can you logon?

I assume this is only a test system? A restart of the database will most probably resolve the issues (maybe only for short time). As you cannot logon to sqlplus to do a clean shutdown, you could kill -9 <ora_pmon>. But be warned this will crash the instance, but you should be able to startup again with sqlplus.

Cheers Michael

[volker_borowski2]

> Checked no archiver struck since the file systems have enough space.

Hi,

there are several reasons for archiver stuck, and only

1) is the freespace of the archive destination

2) permission to write on archive destination

3) archiver processs dead

4) alertlog not able to grow

5) controlfile can't grow

there is a 6) that I regret I did forget.

Nevertheless, a "connect / as sysdba" should work even in a arch-stuck situation

May be you are not able to spawn a new shadow process.

Check if you did run out of system kernel resources.

What does a "lsnrctl services" report ?

Volker

[Former Member]

HI Volker,

Thanks a ton for the response.The issue got reolved by box reboot which ideally should not have been the case.

[Former Member]

Thanks a ton Micheal for the response.Couldnt take the risk of killing the process.So went ahead with box reboot and it resolved the issue.