• Inicio
• Quiénes Somos
• Eventos
• Blog
• Contáctenos
• Registrarse »

Me quede sin espacio en disco y mi UNDOTBS sufrio las consecuencias…

Ayer por la mañana recibí la llamada de una amiga, con problemas de LUNES!!!. Me contó que se habia llenado el disco donde se guardaban los datafiles y esto originó que su UNDOTBS no pueda crecer para guardar la información necesaria. A continuación presento la alternativa de solución para que ella pueda estar mas tranquila y logicamente notificar a sus usuarios que el servicio de base de datos habia sido recuperado.

NOTA: Esto NO se hubiera podido lograr si su base de datos NO se encontraba en modo ARCHIVELOG y tuviera los ARCHIVED REDO LOGS necesarios.

Primero: El Error en el Alert Log

SMON: enabling cache recovery
Mon May 18 08:17:23 2009
Errors in file /oracle/admin/bd/udump/bd_ora_20169.trc:
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 2
ORA-00376: file 29 cannot be read at this time
ORA-01110: data file 29: ‘/database2/bd/undotbs02.dbf’
Mon May 18 08:17:23 2009
Error 704 happened during db open, shutting down database
USER: terminating instance due to error 704
Instance terminated by USER, pid = 20169
ORA-1092 signalled during: alter database open…
Mon May 18 08:27:47 2009

Segundo: El Sintoma era que NO podia poner en OPEN a su base de datos

Instance terminated by USER, pid = 20169
ORA-1092 signalled during: alter database open…
Mon May 18 08:27:47 2009

Tercero: El status del datafile 29: ‘/database2/bd/undotbs02.dbf’

SQL> startup mount

SQL> select name,status from v$datafile ;
NAME STATUS
—————————————————————————
/database/bd/system01.dbf SYSTEM
/database/bd/system02.dbf SYSTEM
/database/bd/sysaux01.dbf ONLINE
/database/bd/users01.dbf ONLINE
/database2/bd/undotbs02.dbf RECOVER
/database/bd/sysaux02.dbf ONLINE
/database/bd/user02.dbf ONLINE

Cuarto: Grave confusión, intentar borrar el tablespace UNDOTBS.

SQL> DROP TABLESPACE undotbs INCLUDING CONTENTS AND DATAFILES ;
DROP TABLESPACE undotbs INCLUDING CONTENTS AND DATAFILES
*
ERROR at line 1:
ORA-01109: database not open

Quinto: Iniciar proceso de RECOVER del datafile UNDOTBS02.DBF

SQL> conn sys as sysdba
Enter password:
Connected.
SQL> recover datafile ‘/database2/bd/undotbs02.dbf’;
ORA-00279: change 1522582151 generated at 05/18/2009 01:03:59 needed for thread 1
ORA-00289: suggestion : /database2/archive/bd1/1_23317_668994063.dbf
ORA-00280: change 1522582151 for thread 1 is in sequence #23317

Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00279: change 1522612846 generated at 05/18/2009 01:44:49 needed for thread 1
ORA-00289: suggestion : /database2/archive/bd1/1_23318_668994063.dbf
ORA-00280: change 1522612846 for thread 1 is in sequence #23318
ORA-00278: log file ‘/database2/archive/bd1/1_23317_668994063.dbf’ no longer needed for this recovery

Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00279: change 1522633097 generated at 05/18/2009 08:05:22 needed for thread 1
ORA-00289: suggestion : /database2/archive/bd1/1_23319_668994063.dbf
ORA-00280: change 1522633097 for thread 1 is in sequence #23319
ORA-00278: log file ‘/database2/archive/bd1/1_23318_668994063.dbf’ no longer needed for this recovery

Specify log: {=suggested | filename | AUTO | CANCEL}

Log applied.
Media recovery complete.

Sexto: Volver a validar el status del datafile UNDOTBS02.DBF

SQL> select name,status from v$datafile ;
NAME STATUS
—————————————————————————
/database/bd/system01.dbf SYSTEM
/database/bd/system02.dbf SYSTEM
/database/bd/sysaux01.dbf ONLINE
/database/bd/users01.dbf ONLINE
/database2/bd/undotbs02.dbf OFFLINE
/database/bd/sysaux02.dbf ONLINE
/database/bd/user02.dbf ONLINE

Setimo: Alterar el datafile UNDOTBS02.DBF para colocarlo en ONLINE

SQL> alter database datafile ‘/database2/bd/undotbs02.dbf’ online;

Database altered.

SQL> select name,status from v$datafile ;
NAME STATUS
—————————————————————————
/database/bd/system01.dbf SYSTEM
/database/bd/system02.dbf SYSTEM
/database/bd/sysaux01.dbf ONLINE
/database/bd/users01.dbf ONLINE
/database2/bd/undotbs02.dbf ONLINE
/database/bd/sysaux02.dbf ONLINE
/database/bd/user02.dbf ONLINE

Octavo: Abrir (OPEN) la base de datos y realizar un Shutdown Immediate

SQL> alter database open;

Database altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1946157056 bytes
Fixed Size 1268244 bytes
Variable Size 1191183852 bytes
Database Buffers 738197504 bytes
Redo Buffers 15507456 bytes
Database mounted.
Database opened.
SQL>

En: Oracle Database, Recovery el 05/19/2009