General Possible Problem Oracle Database

General Possible Problem

1.  Event after killing session, Sessions are keep on regenerating and lead to  database in hang state

Check session which is coming continuously

set head on feed on pagesize 500 linesize 1000

col  machine for a40

col module for a40

col program for a40

select count(*) , module , status, program, machine, username from v$session

group by status, module, program, machine, username;

SET LINESIZE 500

SET PAGESIZE 1000

SET VERIFY OFF

 SELECT a.sql_text,b.sid,b.serial#,username,b.status,b.sql_id,to_char(b.logon_time,'dd-mon:hh24:mi'),b.machine,b.module

FROM   v$sqltext a,

       v$session b

WHERE  a.address = b.sql_address

AND    a.hash_value = b.sql_hash_value

AND    b.username = &1

Check if any problematic event on Database

select count(1),event from v$session group by event;

Map database session with OS process if you got any top consumer process from OS end.

select s.sid,s.serial#,s.username,to_char(s.logon_time,'dd-mon:hh24:mi'),machine,module,sql_id  from v$session s, v$process p

where p.addr=s.paddr and p.spid in ('41484692','49021256');

kill the new/upcoming incoming session

alter system kill session 'sid,serial#' immediate;

ALTER SYSTEM KILL SESSION '2777,32093' immediate

OR

Kill session at OS level

kill -9 <pid>

if require kill session module wise as below

ps -eaf | grep DTLBS1 | grep "LOCAL=NO" | awk '{ print "kill -9 " $2 }' > kill211a.log

ps -eaf | grep RCC1 | grep "ora_j" | awk '{ print "kill -9 " $2 }' > kill41.log

select 'ALTER SYSTEM KILL SESSION'''||SID||','||SERIAL#||'''IMMEDIATE;' from v$session where username like 'U%'

select '!kill -9 '||spid from v$process where addr in (select paddr from v$session where username ='SERVRECO' and status='INACTIVE');

Check database status and try to switch log on database

   

alter system switch logfile;

alter system archive log current

Check alert log if any other issue.

Database is not getting shutdown due to active session

Investigation for issue.:

As part of investigation, check  alert log of database

if you find active sessions from alert log as below mean PMON process is unable to clear any uncommited transaction and oracle hung for long time and DBA will not be able to login on database via SQL*Plus to release the source of the hanging.

Massege from alert log

Active call for process 60228112 user 'lprd001' program 'oracle@linwood (TNS V1-V3)'

Active call for process 46662294 user 'lprd001' program 'oracle@linwood (TNS V1-V3)'

Active call for process 14877444 user 'lprd001' program 'oracle@linwood (TNS V1-V3)'

Active call for process 16188536 user 'lprd001' program 'oracle@linwood (TNS V1-V3)'

Active call for process 33555092 user 'lprd001' program 'oracle@linwood (TNS V1-V3)'

Active call for process 29360252 user 'lprd001' program 'oracle@linwood (TNS V1-V3)'

Active call for process 58655778 user 'lprd001' program 'oracle@linwood (TNS V1-V3)'

Active call for process 6816242 user 'lprd001' program 'oracle@linwood (TNS V1-V3)'

Active call for process 27329312 user 'lprd001' program 'oracle@linwood (TNS V1-V3)'

Active call for process 11600716 user 'lprd001' program 'oracle@linwood (TNS V1-V3)'

Active call for process 12649448 user 'lprd001' program 'oracle@linwood (TNS V1-V3)'

Active call for process 1246730 user 'lprd001' program 'oracle@linwood (TNS V1-V3)'

Active call for process 59507126 user 'lprd001' program 'oracle@linwood (TNS V1-V3)'

Active call for process 60424736 user 'lprd001' program 'oracle@linwood (TNS V1-V3)'

Active call for process 11993870 user 'lprd001' program 'oracle@linwood (TNS V1-V3)'

SHUTDOWN: waiting for active calls to complete.

Workaround/Solution

The workaround in such a situation will be to kill such processes

1. Now check if there are any connection present at the database as:

$ ps -eaf | grep LOCAL

  or

ps -eaf | grep <Database Name> | grep "LOCAL=NO" | awk '{ print "kill -9 " $2 }' > kill211a.log

It will give  the OSPIDs of the client connected to database.

2 Manually kill them as:

# Kill -9 <OSPID>

or

  

# sh kill211a.log

3. Issue shutdown immediate now or  As the shutdown was in progress ,the base automatically may came down

Or

The other workaround in this case will be to shutdown with:

shutdown abort
startup restrict
shutdown normal

 I would suspect that there might be some long running transaction going on in any of the sessions that were present at the time of shutdown. This could have caused because the shutdown was waiting for this transaction to roll back completely.

The other possibility of this could be that there are some blocking processes present in the system, which caused some of the other sessions to hang