martes, 20 de enero de 2015

recreating idl_ub1$ idl_char$ idl_ub2$ and idl_sb4$ caused by corruption in 11g

RMAN couldn't backup the database because there was corruption in one of this tables.
CHECK IN A TEST DATABASE FIRST!!!

This is how we fixed corruption in this sys tables, and worked.

SQLPLUS /NOLOG
CONN SYS@db AS SYSDBA
shutdown immediate
startup upgrade
truncate table idl_ub1$;
truncate table idl_char$;
truncate table idl_ub2$;
truncate table idl_sb4$;
@c:\oracle\product\10.2.0\db_1\rdbms\admin\utlirp
shutdown immediate
startup upgrade
@c:\oracle\product\10.2.0\db_1\javavm\install\rmjvm
shutdown immediate
exit

SQLPLUS /NOLOG
CONN SYS@db AS SYSDBA
shutdown immediate
startup upgrade
alter system set "_system_trig_enabled"=false scope=memory;

@c:\oracle\product\10.2.0\db_1\javavm\install\initjvm.sql
-- if you have xml installed
@c:\oracle\product\10.2.0\db_1\xdk\admin\initxml.sql
@c:\oracle\product\10.2.0\db_1\xdk\admin\xmlja.sql
@c:\oracle\product\10.2.0\db_1\rdbms\admin\catjava.sql
--

shutdown immediate
exit

SQLPLUS /NOLOG
CONN SYS@db AS SYSDBA
shutdown immediate
startup upgrade
@c:\oracle\product\10.2.0\db_1\rdbms\admin\utlrp
exit


** reiniciar servicio
** reiniciar servicio
** reiniciar servicio

SQLPLUS /NOLOG
CONN SYS@db AS SYSDBA
shutdown immediate
startup
execute utl_recomp.recomp_serial();
exit

-- prueba de fullbackup
RMAN
connect target SYS@db