Thursday, March 13, 2008

ORA-01115 and ORA-27067 during crash recovery

Yesterday, one of our development server got a power outage because of which all our databases running on that server crashed.

After the server was brought up, I started these databases manually. One of the db refused to startup with normal "startup" command.

Here was the front-end error.

SQL> startup
ORACLE instance started.

Total System Global Area 432013312 bytes
Fixed Size 2169944 bytes
Variable Size 356115368 bytes
Database Buffers 62914560 bytes
Redo Buffers 10813440 bytes
Database mounted.
ORA-01115: IO error reading block from file 2 (block # 575701)
ORA-01110: data file 2: '/mnt/user/oracle/data/initdb/undotbs01.dbf'
ORA-27067: size of I/O buffer is invalid
Additional information: 2
Additional information: 1572864

And here is what I saw in alert.log

ALTER DATABASE OPEN
Wed Mar 12 10:01:14 2008
Beginning crash recovery of 1 threads
parallel recovery started with 2 processes
Wed Mar 12 10:01:15 2008
Started redo scan
Wed Mar 12 10:01:16 2008
Completed redo scan
130874 redo blocks read, 760 data blocks need recovery
Wed Mar 12 10:01:16 2008
Started redo application at
Thread 1: logseq 49756, block 4723
Wed Mar 12 10:01:16 2008
Recovery of Online Redo Log: Thread 1 Group 1 Seq 49756 Reading mem 0
Mem# 0 errs 0: /mnt/filer15adapp/data/adap_dev/redo1.log
Wed Mar 12 10:01:16 2008
Errors in file /mnt/oracle-dev1/admin/adap_dev/bdump/adap_dev_p001_4721.trc:
ORA-01115: IO error reading block from file 2 (block # 575701)
ORA-01110: data file 2: '/mnt/filer15adapp/data/adap_dev/undotbs01.dbf'
ORA-27067: size of I/O buffer is invalid
Additional information: 2
Additional information: 1409024
Wed Mar 12 10:01:19 2008
Aborting crash recovery due to slave death, attempting serial crash recovery
Wed Mar 12 10:01:19 2008
Beginning crash recovery of 1 threads
Wed Mar 12 10:01:19 2008
Started redo scan
Wed Mar 12 10:01:20 2008
Completed redo scan
130874 redo blocks read, 760 data blocks need recovery
Wed Mar 12 10:01:25 2008
Aborting crash recovery due to error 1115
Wed Mar 12 10:01:25 2008
Errors in file /mnt/oracle-dev1/admin/adap_dev/udump/adap_dev_ora_4717.trc:
ORA-01115: IO error reading block from file 2 (block # 575701)
ORA-01110: data file 2: '/mnt/filer15adapp/data/adap_dev/undotbs01.dbf'
ORA-27067: size of I/O buffer is invalid
Additional information: 2
Additional information: 1572864
ORA-1115 signalled during: ALTER DATABASE OPEN...
Wed Mar 12 10:02:37 2008
Shutting down instance: further logons disabled


This was the first time i got this error in my experience. Db being running in noarchive log mode (this being dev server) just added to my worries.

Googling didnt help. After some research work, i encountered a note on metalink Note:423128.1.

Though they didnt explain the cause of the problem, but suggested the workaround.

i.e.

1) Mount the database
2) Issue "recover database" command
-- Wait for "Media recovery complete"
3) alter database open

I'm s not sure about the cause as this Note on metalink refers to a Bug 5362935 - "CRASH RECOVERY FAILS WITH ORA-27067 ERROR", which is closed with status "
Suspended, Req'd Info not Avail" and lots of useful information is hidden so people like us, who are not working in Oracle Corp cant see it.

1 comment:

Sunil Bhola said...

Hi Sachin,

I think you are hitting up a BUG.. just raise TAR with oracle and confirm with them. But in your case I think we can digest this as you have development server :-)

Sunil Bhola