lunes, 3 de febrero de 2014

Como reducir el UNDO Tablespace

1 – Verificar el UNDO Tablespace
SQL> show parameters undo_tablespace
 NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace                      string      UNDOTBS1

2 – Crear un UNDO tablespace temporal
SQL> create undo tablespace UNDOTBS2  datafile '/u02/oradata/ORCL/undotbs2.dbf' size 100M;
Tablespace created.

3) Cambiar al nuevo Undo tablespace
SQL> alter system set undo_tablespace='UNDOTBS2' scope=both;
System altered.

NOTA: Si se esta usando pfile en vez de spfile, se debe cambiar en el init.ora, luego bajar y subir la base.

4- Verificar
SQL> show parameters undo_tablespace
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace                      string      UNDOTBS2

5- Eliminar UNDO TABLESPACE original
SQL>drop  tablespace UNDOTBS1 including contents and datafiles;
Tablespace dropped.

6 – Crear el UNDO Tablespace
SQL>  create undo tablespace UNDOTBS1 datafile '/u02/oradata/ORCL/undotbs.dbf' size 100M autoextend on maxsize 4G;
Tablespace created.

7 – Cambiar por el definitivo
SQL> alter system set undo_tablespace='UNDOTBS1';
System altered.

8- Verificar
SQL> show parameters undo_tablespace
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace                      string      UNDOTBS1

9 - Eliminar Undo temporal
  SQL> drop tablespace UNDOTBS2 including contents and datafiles;
Tablespace dropped.




TROUBLESHOOTING
ERROR ORA-30013: undo tablespace 'UNDOTBS2' is currently in use

Al ejecutar el drop puede dar este mensaje de error debido a que tiene transacciones activas.

  SQL> drop tablespace UNDOTBS2 including contents and datafiles
*
ERROR at line 1:
ORA-30013: undo tablespace 'UNDOTBS2' is currently in use


Tenemos varias opciones:
1 - Esperar a que finalicen y volver a ejecutar el DROP
2 - Eliminar las transacciones activas
3 - Bajar y subir la base de datos

OPCION 1 -Esperar a que finalicen y volver a ejecutar el DROP. 
Podemos ir controlando cuando lo libera en el alert.log.

En el alert.log cuando ejecutamos el DROP quedo el mensaje :
Wed Jan 29 12:24:34 ARST 2014
Successfully onlined Undo Tablespace 1.
Undo Tablespace 5 moved to Pending Switch-Out state.
*** active transactions found in undo tablespace 5 during switch-out.

Finalmente cuando no tiene mas transacciones activas aparece :
Wed Jan 29 12:29:32 ARST 2014
Undo Tablespace 5 successfully switched out.

 Volver a ejecutar: 
SQL> drop tablespace UNDOTBS2 including contents and datafiles;


OPCION 2 - Consultar las transacciones activas
SQL> SELECT 
a.usn,
a.name,
b.status,
c.tablespace_name,
d.addr,
e.sid, 
e.serial#,
e.username,
e.program,
e.machine, 
e.osuser
FROM 
v$rollname a, 
v$rollstat b, 
dba_rollback_segs c, 
v$transaction d, 
v$session e 
WHERE 
a.usn=b.usn AND 
a.name=c.segment_name AND
a.usn=d.xidusn AND
d.addr=e.taddr AND
b.status='PENDING OFFLINE';



Se puede en este punto decidir matar las sesiones activas si no es posible avisar al usuario que cierre la sesion o esperar.


Luego volver a ejecutar: 
SQL> drop tablespace UNDOTBS2 including contents and datafiles;

OPCION 3 - Bajar y subir la base. 

SQL>Shutdown immediate
SQL>startup

Luego volver a ejecutar: 
SQL> drop tablespace UNDOTBS2 including contents and datafiles;