lunes, 21 de junio de 2010

Recovery

SQL> select segment_name, status from dba_rollback_segs;

SEGMENT_NAME STATUS
------------------------------ ----------------
SYSTEM ONLINE
_SYSSMU4$ ONLINE
_SYSSMU5$ ONLINE
_SYSSMU6$ ONLINE
_SYSSMU7$ ONLINE
_SYSSMU8$ ONLINE
_SYSSMU9$ ONLINE
_SYSSMU10$ ONLINE
_SYSSMU11$ ONLINE
_SYSSMU12$ ONLINE
_SYSSMU13$ ONLINE
_SYSSMU14$ ONLINE
_SYSSMU15$ ONLINE
_SYSSMU16$ ONLINE
_SYSSMU17$ ONLINE
_SYSSMU18$ ONLINE
_SYSSMU19$ ONLINE
_SYSSMU20$ ONLINE
_SYSSMU21$ OFFLINE
_SYSSMU22$ NEEDS RECOVERY
_SYSSMU23$ OFFLINE
_SYSSMU24$ OFFLINE
_SYSSMU25$ OFFLINE
_SYSSMU26$ OFFLINE
_SYSSMU27$ OFFLINE
_SYSSMU28$ OFFLINE
_SYSSMU29$ OFFLINE
_SYSSMU30$ OFFLINE
_SYSSMU31$ OFFLINE
_SYSSMU32$ OFFLINE
_SYSSMU33$ OFFLINE
_SYSSMU34$ OFFLINE
_SYSSMU35$ OFFLINE
_SYSSMU36$ OFFLINE
_SYSSMU37$ OFFLINE
_SYSSMU38$ OFFLINE
_SYSSMU39$ OFFLINE
_SYSSMU40$ OFFLINE
_SYSSMU41$ ONLINE
_SYSSMU42$ ONLINE
_SYSSMU43$ ONLINE
_SYSSMU44$ OFFLINE
_SYSSMU45$ OFFLINE
_SYSSMU46$ OFFLINE
_SYSSMU47$ OFFLINE
_SYSSMU48$ OFFLINE
_SYSSMU49$ OFFLINE
_SYSSMU50$ OFFLINE
_SYSSMU51$ OFFLINE
_SYSSMU52$ OFFLINE
_SYSSMU53$ OFFLINE
_SYSSMU54$ OFFLINE
_SYSSMU55$ OFFLINE
_SYSSMU56$ OFFLINE
_SYSSMU57$ OFFLINE
_SYSSMU58$ OFFLINE
_SYSSMU59$ OFFLINE
_SYSSMU60$ OFFLINE
_SYSSMU61$ OFFLINE
_SYSSMU62$ OFFLINE
_SYSSMU63$ OFFLINE
_SYSSMU64$ OFFLINE
_SYSSMU65$ OFFLINE
_SYSSMU66$ OFFLINE
_SYSSMU67$ OFFLINE
_SYSSMU68$ OFFLINE
_SYSSMU69$ OFFLINE
_SYSSMU70$ OFFLINE
_SYSSMU71$ OFFLINE
_SYSSMU72$ OFFLINE
_SYSSMU73$ OFFLINE
_SYSSMU74$ OFFLINE
_SYSSMU75$ OFFLINE
_SYSSMU76$ OFFLINE
_SYSSMU77$ OFFLINE
_SYSSMU78$ OFFLINE
_SYSSMU79$ OFFLINE
_SYSSMU80$ OFFLINE
_SYSSMU81$ OFFLINE
_SYSSMU82$ OFFLINE
_SYSSMU83$ OFFLINE
_SYSSMU84$ OFFLINE
_SYSSMU85$ OFFLINE
_SYSSMU86$ OFFLINE
_SYSSMU87$ OFFLINE
_SYSSMU88$ OFFLINE
_SYSSMU89$ OFFLINE
_SYSSMU90$ OFFLINE
_SYSSMU91$ OFFLINE
_SYSSMU92$ OFFLINE
_SYSSMU93$ OFFLINE
_SYSSMU94$ OFFLINE
_SYSSMU95$ OFFLINE
_SYSSMU96$ OFFLINE
_SYSSMU97$ OFFLINE
_SYSSMU98$ OFFLINE
_SYSSMU99$ OFFLINE
_SYSSMU100$ OFFLINE
_SYSSMU101$ OFFLINE
_SYSSMU102$ OFFLINE
_SYSSMU103$ ONLINE
_SYSSMU104$ OFFLINE
_SYSSMU105$ OFFLINE
_SYSSMU106$ OFFLINE
_SYSSMU107$ OFFLINE
_SYSSMU108$ ONLINE
_SYSSMU109$ ONLINE
_SYSSMU110$ OFFLINE
_SYSSMU111$ OFFLINE
_SYSSMU112$ OFFLINE
_SYSSMU113$ OFFLINE
_SYSSMU114$ OFFLINE
_SYSSMU115$ OFFLINE
_SYSSMU116$ OFFLINE
_SYSSMU117$ OFFLINE
_SYSSMU118$ OFFLINE
_SYSSMU119$ OFFLINE
_SYSSMU120$ OFFLINE
_SYSSMU121$ OFFLINE
_SYSSMU122$ OFFLINE
_SYSSMU123$ OFFLINE
_SYSSMU124$ OFFLINE
_SYSSMU125$ OFFLINE
_SYSSMU126$ OFFLINE
_SYSSMU127$ OFFLINE
_SYSSMU128$ OFFLINE
_SYSSMU129$ OFFLINE
_SYSSMU130$ OFFLINE
_SYSSMU131$ OFFLINE
_SYSSMU132$ OFFLINE
_SYSSMU133$ OFFLINE
_SYSSMU134$ OFFLINE
_SYSSMU135$ OFFLINE
_SYSSMU136$ OFFLINE
_SYSSMU137$ OFFLINE

135 rows selected.

SQL>


drop the UNDO tablespace que estan corruptos

SQL> drop tablespace UNDO_name including contents ;

Tablespace dropped.

Remover datafiles physicall

$ rm /path/undo_name.dbf

Recreate the tablespace.

Examples

SQL> create undo tablespace “UNDO_name″ datafile ‘/path/undo_name.dbf’ size 5000M AUTOEXTEND ON;

Tablespace created.

l. shutdown db and restart the db with original init file.


SQL> alter tablespace UNDOTBS1 offline immediate;
Tablespace altered.


Para Recuperar un Bloque Corrupto si los hay

execute dbms_repair.skip_corrupt_blocks(’OWNER’,'TABLE_NAME’) ;

Add the following line to pfile:

_corrupted_rollback_segments =(‘_SYSSMU11$’,'_SYSSMU12$’,'_SYSSMU13$’,'_SYSSMU14$’,'_SYSSMU15$’,'_SYSSMU16$’,
‘_SYSSMU17$’,'_SYSSMU18$’,'_SYSSMU19$’,'_SYSSMU20$’)


SQL> drop rollback segment "_SYSSMU22$";
Rollback segment dropped.



SQL > drop tablespace undotbs including contents and datafiles;
Tablespace dropped.