Oracle Force open erroring out with ORA-00704 ORA-00604 ORA-01555

If you cannot recover data by yourself, ask Parnassusdata, the professional ORACLE database recovery team for help.

Parnassusdata Software Database Recovery Team

Service Hotline:  +86 13764045638 E-mail: service@parnassusdata.com

Applies to:
Oracle Server – Enterprise Edition – Version 10.2.0.4 and later
Information in this document applies to any platform.

Symptoms

Database is been Force opened using the following Document 283945.1

Alter database open resetlog fails with

ORA-00704: bootstrap process failure
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 1
ORA-01555: snapshot too old: rollback segment number 11 with name “_SYSSMU11$” too small

Tue Jan 17 04:46:17 2012

Error 704 happened during db open, shutting down database

USER: terminating instance due to error 704

Instance terminated by USER, pid = 5496

ORA-1092 signalled during: ALTER DATABASE OPEN RESETLOGS…

Changes

Database is been Force open

Cause

Database is been forced open.All files are not in sycn.

File would be having block scn higher than database scn

Solution

If no trace file is available for Ora-1555 please do the following to generate a trace file for 704 And ora-1555 :-

SQL>Startup mount ;
SQL>Alter session set tracefile_identifier=new1555 ;
Now try open resetlogs

SQL>Alter database open resetlogs ;
It will error out with ORA-1092
Alert log would show 1555 error message.
Go to udump or trace directory
ls -lrt *new1555*
Case one undo segment name is reported in alert log
ORA-01555: snapshot too old: rollback segment number 11 with name “_SYSSMU11$” too small
In the above case the Ora-1555 was reported on _SYSSMU11$ which is undo segment number 11.
Hex value of same is 11 –> b
Search the trace file from Table or index block header dump whose transaction layer has an undo segment 11
been used in the ITL.Scroll up to get the Buffer header dump of that block
BH (0xacff8e48) file#: 1 rdba: 0x0040003e (1/62) class: 1 ba: 0xacf66000
set: 3 blksize: 8192 bsi: 0 set-flg: 2 pwbcnt: 0
dbwrid: 0 obj: 18 objn: 18 tsn: 0 afn: 1
hash: [d0e04c98,d0e04c98] lru: [acff8fd8,acff8db8]
ckptq: [NULL] fileq: [NULL] objq: [cc9a3d00,cc9a3d00]
use: [ce699910,ce699910] wait: [NULL]
st: CR md: EXCL tch: 0
cr: [scn: 0x0.4124e1e],[xid: 0x6.0.c28d],[uba: 0x820075.ea1.23],[cls: 0x0.46b5261],[sfl: 0x1]
flags:
Using State Objects
—————————————-
SO: 0xce6998d0, type: 24, owner: 0xd04439e8, flag: INIT/-/-/0x00
(buffer) (CR) PR: 0xd02fa378 FLG: 0x500000
class bit: (nil)
kcbbfbp: [BH: 0xacff8e48, LINK: 0xce699910]
where: kdswh02: kdsgrp, why: 0
buffer tsn: 0 rdba: 0x0040003e (1/62)
scn: 0x0000.046b527a seq: 0x00 flg: 0x00 tail: 0x527a0600
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00000000ACF66000 to 0x00000000ACF68000
0ACF67FF0 FFFF02C1 01FF8001 02C10280 527A0600 […………..zR]

Block header dump: 0x0040003e
Object id on Block? Y
seg/obj: 0x12 csc: 0x00.46b519e itc: 1 flg: – typ: 1 – DATA
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x000b.00b.00000e7a 0x00802042.00db.1a C— 0 scn 0x0000.04624228

So here we see ITL allocated is 0x01.
Transaction identifier –> <undo no>.<slot>.<wrap> —> 0x000b.00b.00000e7a
Undo segment no —> 0x000b –> 11 in decimal .
This block belong to 0x0040003e –(1/62)
Find the SCN of this block from the BH(Buffer header) for 0x0040003e –(1/62)
So in this case its the one highlighted above in trace scn: 0x0000.046b527a
Now set _mimimum_giga_scn value based on this SCN
scn: 0x0000.046b527a
Convert 0x0000 –> Decimal –> 0
Convert 046b527a –> Decimal –>74142330
Combine both these value and find the value for _minimum_giga_scn
Convert –> 074142330 /1024/1024/1024 =0.069050
Add + 2G to the above value and round it up
_minimum_giga_scn = 3G
Set this parameter in the pfile along with the other force open parameter
SQL>Startup mount pfile=<> ;
SQL>recover database using backup controlfile until cancel ;
Cancel
SQL>Alter database open resetlogs ;
As per the force open steps do complete export and create new database and do import