Oracle Shutdown Immediate Hang

 



oracle Shutdown hang 

or
before shutdown 


prompt  long time wait for shutdown to get completed if sum value is high

after kill session  long transaction , then run this query 

--- dictionary managed tablespaces, extents are maintained in uet$

prompt Is SMON clearing the temp space?  
prompt removed from the table for used extents UET$ ;
prompt placed on the table for free extents FET$ ;

select count(f.block#) "Should Increase >", count(u.block#) "Should Decrease <" from fet$ f ,uet$ u;



--  monitor clean up the session 

select sum(used_ublk) from v$transaction;  
                                                              
                                                              
 event="10513 trace name context forever, level 2" 
                                                              
          
  alter system set events '10513 trace name context forever, level 2' ;

System altered.


on spfile

SQL> alter system set event='10513 trace name context forever, level 2' scope=spfile;

System altered.


SQL> alter system reset event scope=spfile sid='*';

System altered.


alter system reset parameter  sid=’sid|*’



Specify SID = '*' if you want Oracle Database to change the value of the parameter for all instances.

-Specify SID = 'sid_of_a_database' if you want Oracle Database to change the value of the parameter only for the instance sid.



SQL> alter system reset control_file_record_keep_time scope=spfile sid='*';

System altered.

****** Don't change the value of a parameter with '' or null to reset it to default ******




col NAME format a20
col DISPLAY_VALUE format a60
select NAME ,DISPLAY_VALUE from  V$SPPARAMETER where NAME like '%even%';


NAME                 DISPLAY_VALUE
-------------------- ------------------------------------------------------------
event                10513 trace name context forever, level 2
xml_db_events



col DISPLAY_VALUE format a60
select NAME ,DISPLAY_VALUE from  V$SYSTEM_PARAMETER where NAME like '%even%';


NAME                 DISPLAY_VALUE
-------------------- ------------------------------------------------------------
event
xml_db_events        enable




shutdown immediate to get complete without transaction recovery.SMON will not do a transaction recovery untill this event 

select SID, SERIAL#, EVENT#, EVENT from v$session  where  SERIAL# >1 ;


Recovery after killing a large transaction
The message 'Waiting for smon to disable tx recovery' will posted in the alert log as well.


alter session set NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS'; 

SELECT usn, state, undoblockstotal "Total", undoblocksdone "Done", 
undoblockstotal-undoblocksdone "ToDo", 
DECODE(cputime,0,'unknown',SYSDATE+(((undoblockstotal-undoblocksdone) / (undoblocksdone / cputime)) / 86400)) 
"Finish at" 
FROM v$fast_start_transactions; 

  
  

alter session set NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS'; 

SELECT usn, state, undoblockstotal "Total", undoblocksdone "Done",
 undoblockstotal-undoblocksdone "ToDo", DECODE(cputime,0,'unknown',SYSDATE+(((undoblockstotal-undoblocksdone) / (undoblocksdone / cputime)) / 86400))  "Finish at" 
FROM v$fast_start_transactions; 
  

SELECT state, undoblocksdone, undoblockstotal, cputime   FROM v$fast_start_transactions;

V$FAST_START_TRANSACTIONS contains information both for transactions that the Oracle server is recovering(the STATE is RECOVERING) and for transactions that the Oracle server has recovered(the STATE is RECOVERED). New columns are added to this view as;

• XID: Transaction ID of this transaction
• PXID: Transaction ID of the parent transaction
• RCVSERVERS: Number of servers working on this transaction including the coordinator server(It can be 1 if only SMON is doing the recovery).




- In some versions the cputime does not work (always 0), hence the estimated completion time will not be displayed
- In some cases the v$fast_start_transactions view will not work. If this is the case then you can query the internal data dictionary
view x$ktuxe, the 'ktuxesiz' column represents the remaining number of undo blocks required for rollback:

select ktuxeusn, to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') "Time", ktuxesiz, ktuxesta
from x$ktuxe
where ktuxecfl = 'DEAD'; 


The message 'Waiting for smon to disable tx recovery' will posted in the alert log as well.

First we need to determine the progress SMON is making. It is very possible the SMON process to work with one rollback segment. You can find it using the following query:

col LOGON format a10
col ACTION format a10
col PROGRAM format a10
col MODULE format a10
col USERNAME format a10


SELECT r.NAME "RB Segment Name", dba_seg.size_mb,
DECODE(TRUNC(SYSDATE - LOGON_TIME), 0, NULL, TRUNC(SYSDATE - LOGON_TIME) || ' Days' || ' + ') || 
TO_CHAR(TO_DATE(TRUNC(MOD(SYSDATE-LOGON_TIME,1) * 86400), 'SSSSS'), 'HH24:MI:SS') LOGON, 
v$session.SID, v$session.SERIAL#, p.SPID, v$session.process,
v$session.USERNAME, v$session.STATUS, v$session.OSUSER, v$session.MACHINE, 
v$session.PROGRAM, v$session.module, action 
FROM v$lock l, v$process p, v$rollname r, v$session, 
(SELECT segment_name, ROUND(bytes/(1024*1024),2) size_mb FROM dba_segments 
WHERE segment_type = 'TYPE2 UNDO' ORDER BY bytes DESC) dba_seg 
WHERE l.SID = p.pid(+) AND 
v$session.SID = l.SID AND 
TRUNC (l.id1(+)/65536)=r.usn
-- AND l.TYPE(+) = 'TX' AND 
-- l.lmode(+) = 6 
AND r.NAME = dba_seg.segment_name
--AND v$session.username = 'SYSTEM'
--AND status = 'INACTIVE'
ORDER BY size_mb DESC;

RB Segment Name                   SIZE_MB LOGON             SID    SERIAL# SPID                     PROCESS                  USERNAME   STATUS   OSUSER
------------------------------ ---------- ---------- ---------- ---------- ------------------------ ------------------------ ---------- -------- ------------------------------
MACHINE                                                          PROGRAM    MODULE     ACTION
---------------------------------------------------------------- ---------- ---------- ----------
_SYSSMU1_3780397527$                10.13 3 Days + 2         54      13021                          6325                     SYS        INACTIVE oracle
                                          0:10:54
apt-amd-02                                                       sqlplus@ap sqlplus@ap
                                                                 t-amd-02 ( t-amd-02 (
                                                                 TNS V1-V3) TNS V1-V3)