Fatal error trying to open a database

Since now on, and perhaps because my need of improve, i will write my post in English. Sorry for the spanish readers, but i need to improve my English, and write in that language is a way.

This was a very complicated case, because like as usual, there was no procedure to fix it in an easy way. I hope you, reader, who come here looking for a similar error, to have a little more luck than me, and finally find what were you looking for.

The facts were as follows. In the morning of July first (2014), one of my company’s Oracle databases was down. When the operator tried to open it, these were the errors:

SMON: enabling cache recovery
Tue Jul 1 13:26:22 2014
Undo Segment 1 Onlined
Undo Segment 2 Onlined
Undo Segment 3 Onlined
Undo Segment 4 Onlined
Undo Segment 5 Onlined
Undo Segment 6 Onlined
Undo Segment 7 Onlined
Undo Segment 8 Onlined
Undo Segment 9 Onlined
Undo Segment 10 Onlined
Successfully onlined Undo Tablespace 1.
Dictionary check beginning
Dictionary check complete
Tue Jul 1 13:26:23 2014
SMON: enabling tx recovery
Tue Jul 1 13:26:23 2014
Database Characterset is WE8ISO8859P1
ORA-01555 caused by SQL statement below (Query Duration=0 sec, SCN: 0x0000 4001871b):
Tue Jul 1 13:26:23 2014
select increment$,minvalue,maxvalue,cycle#,order$,cache,highwater,audit$,f ags from seq$ where obj#=:1
Tue Jul 1 13:26:23 2014
Errors in file /u01/app/oracle/admin/BDPR/udump/itdb_ora_26510.trc:
ORA-00604: error producido a nivel 1 de SQL recursivo
ORA-01555: instantanea demasiado antigua: numero de segmento de rollback 8 con nombre "_SYSSMU8$" demasiado pequeño
Error 604 happened during db open, shutting down database
USER: terminating instance due to error 604
Instance terminated by USER, pid = 26510
ORA-1092 signalled during: alter database open...

Very ugly. So, we had one or more ‘ORA-01555’ (snapshot too old) every time we tried to open the database.

Looking for the error in Oracle Support and different web pages, i found some parameters (Oracle hidden parameters) which could be included in init.ora file to tell Oracle to ignore some undo corrupted segments. So i created a pfile (from spfile in use). Then i added to the end these lines:

*._allow_resetlogs_corruption=TRUE
*._corrupted_rollback_segments='_SYSSMU8$'

WARNING: the use of those paramameters implies that database will be useless. You will have to create a new one, and move data from the old one.

Like you can see (before the warning), i use the segment’s name that appears in the error log above. Then, i turned off the database, and mounted it with the created pfile. When i tried to open it, i received a similar error, but with other segment. Same operation: shutdown database, change last line in pfile (added new corrupted segment), mount database with pfile.

By the trial and error way, i found that the value for my ‘_corrupted_rollback_segments‘ parameter should be like this:

*._corrupted_rollback_segments='_SYSSMU8$','_SYSSMU5$','_SYSSMU9$'

Once i had database mounted with no errors, i went to the next step. What I needed was open it and to extract the data of a schema (schema’s export).

So next step was to recover database; but not from sqlplus, from rman. Not the same result; proved.

[oracle@itdmbd1 ]$ rman target=/

Recovery Manager: Versión 9.2.0.4.0 - Production

Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.

conectado a la base de datos destino: BDPR (DBID=2700800217)

RMAN> recover database;

Iniciando recover en 01/07/14
se utiliza el archivo de control de la base de datos destino en lugar del catálogo de recuperación
canal asignado: ORA_DISK_1
canal ORA_DISK_1: sid=11 devtype=DISK

iniciando la recuperación del medio físico

thread de archivelog 1, secuencia 1 ya está en disco como archivo /u03/oradata/BDPR/redo01_2.log
archive log nombre=/u03/oradata/BDPR/redo01_2.log thread=1 secuencia=1
recuperación del medio físico terminada
recover terminado en 01/07/14

RMAN> exit

Recovery Manager terminado.

Almost in the end. Next step, open database in read only mode:

[oracle@localhost ~]$ sqlplus /nolog

SQL*Plus: Release 9.2.0.4.0 - Production on Mar Jul 1 18:43:19 2014

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

SQL> conn / as sysdba
Conectado.
SQL> alter database open read only;

Base de datos modificada.

SQL> exit

At this point, you can not export data yet. You need to create a new temp datafile, as you can read in this note. Once you have new temp file, you will be able to successfully execute the export command.

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

A %d blogueros les gusta esto: