Fatal error trying to open a database
septiembre 19, 2014 Deja un comentario
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:
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:
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 184.108.40.206.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 220.127.116.11.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.