RE-CREAR TBS UNDOTBS ORACLE RAC

COMO RE-CREAR TABLESPACE UNDOTBS EN ORACLE RAC de 4 nodos:

EL UNDO UNDOTBS4 esta mal construido, se debe re crear con los valores correctos,
este undo pertenece a la instancia 4 del RAC, para esto se debe trabajar en el nodo
4.

AUTOEXTENT de 1024M
MAXSIZE    de 20480M
SIZE       de 1024M

1.- Creamos un UNDO de PASO:
Nos conectamos al nodo 4 del RAC.

CREATE UNDO TABLESPACE UNDOTBS5 DATAFILE '+CROSS_DATA' SIZE 1024M
REUSE AUTOEXTEND ON NEXT 1024M MAXSIZE 20480M;

ALTER system SET undo_tablespace=UNDOTBS5 sid='cross4';


2.- Revisamos si tenemos segmentos de UNDO:

 SELECT a.name,b.status , d.username , d.sid , d.serial#
   FROM v$rollname a,v$rollstat b, v$transaction c , v$session d
   WHERE a.usn = b.usn
   AND a.usn = c.xidusn
   AND c.ses_addr = d.saddr
   AND a.name IN (
   SELECT segment_name
   FROM dba_segments
   WHERE tablespace_name = 'UNDOTBS4'
   );


select owner, segment_name, tablespace_name, status from dba_rollback_segs where tablespace_name='UNDOTBS4' and status='ONLINE';

 alter rollback segment "_SYSSMU31_2521593980$" offline;
 alter rollback segment "_SYSSMU32_1804532818$" offline;       
 alter rollback segment "_SYSSMU33_3611631434$" offline;                      
 alter rollback segment "_SYSSMU34_1975830645$" offline;                      
 alter rollback segment "_SYSSMU35_1514068533$" offline;                      
 alter rollback segment "_SYSSMU36_412588268$ " offline;                      
 alter rollback segment "_SYSSMU37_1727375658$" offline;                      
 alter rollback segment "_SYSSMU38_4056056697$" offline;                      
 alter rollback segment "_SYSSMU39_1922814765$" offline;                      
 alter rollback segment "_SYSSMU40_1873340872$" offline;


REFERENCIA: http://neeraj-dba.blogspot.cl/2011/05/how-to-drop-undo-tablespace.html


3.- Borramos el UNDOTBS4 de paso:

DROP tablespace UNDOTBS4 including contents and datafiles;


4.- Ahora podemos re crear el UNDOTBS4 con la correccion de estructura:


CREATE UNDO TABLESPACE UNDOTBS4 DATAFILE '+CROSS_DATA' SIZE 1024M
REUSE AUTOEXTEND ON NEXT 1024M MAXSIZE 20480M;

ALTER system SET undo_tablespace=UNDOTBS4 sid='cross4';



5.- Ahora borramos el UNDO de PASO. UNDOTBS5


DROP tablespace UNDOTBS5 including contents and datafiles;