Undo Segments : Drop Problematic Undo Segments
ERROR
about to recover undo segment 98 status:6 inst:0mark undo segment 98 as available
status:6 ret:1
about to recover undo segment 46
status:6 inst:0
mark undo segment 46 as available
status:6 ret:1
SQL> select * from V$FAST_START_TRANSACTIONS;
USN SLT SEQ STATE UNDOBLOCKSDONE
UNDOBLOCKSTOTAL PID CPUTIME PARENTUSN PARENTSLT PARENTSEQ XID PXID RCVSERVERS
CON_ID
---------- ---------- ----------
---------------- -------------- --------------- ---------- ----------
---------- ----------
46 46 2313064 RECOVERING 505 24992423
77 5586 0 0 0 10001000684B2300 0000000000000000 1 0
98 25 1352150 RECOVERING 0 226231 78
5586 0 0 0 30001900D6A11400 0000000000000000 1 0
SQL> SELECT segment_name,
tablespace_name FROM dba_rollback_segs WHERE segment_id IN (98,
46);
SEGMENT_NAME
TABLESPACE_NAME
------------------------------
------------------------------
_SYSSMU46_5249279471$
UNDOTEST1
_SYSSMU98_5249279471$
UNDOTEST1
SQL> select min(sample_time), max(sample_time), sql_id, xid, count(1) from dba_hist_active_sess_history where xid in ('10001000684B2300','30001900D6A11400') group by sql_id, xid;
MIN(SAMPLE_TIME) MAX(SAMPLE_TIME) SQL_ID XID COUNT(1)
---------------------------------------------------------------------------
-------------------
15-SEP-24 01.22.25.446 PM 15-SEP-24
05.51.22.340 PM 30001900D6A11400 3213
15-SEP-24 10.22.46.218 AM 15-SEP-24
01.22.15.440 PM ac5hhandj9fh1 30001980D6A11400 2158 -------------->
13-SEP-24 08.31.54.374 PM 14-SEP-24
02.53.45.723 AM annqr822no0a1 10001090684B2300 4578 -------------->
14-SEP-24 02.53.55.731 AM 15-SEP-24
05.51.22.340 PM 10001000684B2300 27781
SQL> select sql_id, sql_text from dba_hist_sqltext
where sql_id in ('annqr822no0a1','ac5hhandj9fh1o');
SQL_ID SQL_TEXT
-------------
--------------------------------------------------------------------------------
annqr822no0a1 INSERT INTO
monkey.ah_ah3_xaa_131C (
ac5hhandj9fh1o /* MV_REFRESH (INS) */INSERT
/*+ BYPASS_RECURSIVE_CHECK */ INTO "monkey"."test_
SQL> ALTER ROLLBACK SEGMENT "_SYSSMU46_5249279471$"
offline;
Rollback segment altered.
SQL> ALTER ROLLBACK SEGMENT "_SYSSMU98_5249279471$"
offline;
Rollback segment altered.
SQL> SELECT segment_name, status, tablespace_name FROM dba_rollback_segs WHERE segment_name IN ('_SYSSMU98_5249279471$', '_SYSSMU46_5249279471$');
SEGMENT_NAME
STATUS TABLESPACE_NAME
------------------------------
---------------- ------------------------------
_SYSSMU46_5249279471$
PARTLY AVAILABLE UNDOTEST1
_SYSSMU98_5249279471$
PARTLY AVAILABLE UNDOTEST1
SQL> SELECT KTUXEUSN, KTUXESLT, KTUXESQN, /* Transaction ID */ KTUXESTA Status, KTUXECFL Flags FROM x$ktuxe WHERE ktuxesta!='INACTIVE' AND ktuxeusn in (46, 98);
KTUXEUSN KTUXESLT KTUXESQN STATUS FLAGS
---------- ---------- ----------
---------------- ------------------------
98 25 1352150 ACTIVE DEAD
46 46 2313064 ACTIVE DEAD
Run below query to dynamically get
alter statements for segments which we need to set offline.
SQL> select 'ALTER SYSTEM SET
"_OFFLINE_ROLLBACK_SEGMENTS"='||listagg(''''||segment_name||'''',',')
WITHIN GROUP (ORDER BY segment_name)||' scope=spfile;' from dba_rollback_segs
where tablespace_name='UNDOTBS1' and status
='NEEDS RECOVERY';
Alter System set "_OFFLINE_ROLLBACK_SEGMENTS"="_SYSSMU98_5249279471$"
scope=spfile;
Alter System set "_OFFLINE_ROLLBACK_SEGMENTS"="_SYSSMU46_5249279471$"
scope=spfile;
Restart the database
shutdown immediate;
startup;
Drop the rollback segment
SQL> select 'drop rollback segment
"'||segment_name||'";' from dba_rollback_segs
where tablespace_name='UNDOTBS1' and
status ='NEEDS RECOVERY';
drop rollback segment "_SYSSMU98_5249279471$";
drop rollback segment "_SYSSMU46_5249279471$";
Bounce the database and drop the
problematic undo tablespace.
SQL> shutdown immediate;
SQL> startup;
SQL> drop tablespace
UNDOTEST1;
Reset the ‘_OFFLINE_ROLLBACK_SEGMENTS’
parameter and bounce the DB
SQL> Alter System reset "_OFFLINE_ROLLBACK_SEGMENTS";
SQL> shutdown immediate;
SQL> startup;
Comments
Post a Comment