Shutdown Immediate Hangs

Today we faced Strange issue with Shutdown immediate command on cluster database which is when run shutdown immediate the command hangs and take so long and sometimes it's working and most of the time not working.

the below command was working without any problem :-

    SQL > shutdown abort

To solve this issue & trace the error since the alertlog not indicating to anything interesting we have to dump systemstate every 5 minutes  to know what going on :-


    connect / as sysdba
    alter session set events '10400 trace name context forever, level 1';


After this you can kill the session that preventing shutdown.

The hangs usually happened for two reasons :-

    Query running on Database.
    Transaction happened while you are trying to shutdown database.

For the first reason :

    use v$session_longops Read more here.

Run the below query to check the query.

    select count(*) from v$session_longops where time_remaining>0;


Second reason you can use

v$transaction Read from here.

Useful Document :-
Alert Log: Shutdown Waiting for Active Calls to Complete (Doc ID 1039389.6)

Thanks.
NJ