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.

How to pass a value from Oracle to Unix shell environment variable?

Though its too trivial, but I thought its not a bad idea to put it here to help the newbies.
Following information can be of help when you want to store a table record or some computation in a shell variable.

env_variable=`sqlplus username}/{password} <<>
set echo off verify off feed off termout off pages 0
{{SELECT STMT}};
exit
EOF`


For example:

-bash-3.00$ DB_USED=`sqlplus -s "/ as sysdba" << EOF
> set echo off verify off feed off termout off pages 0
> select sum(tot.bytes/1024-sum(nvl(fre.bytes,0))/1024)/3.8
> from dba_free_space fre,
> (select tablespace_name, sum(bytes) bytes
> from dba_data_files
> group by tablespace_name) tot,
> dba_tablespaces tbs
> where tot.tablespace_name = tbs.tablespace_name
> and fre.tablespace_name(+) = tbs.tablespace_name
> group by tbs.tablespace_name, tot.bytes/1024, tot.bytes
> /
> exit
> EOF`

-bash-3.00$ echo $DB_USED
4189795.79

This technique can be used in various places where you need to pass a value from Oracle to Unix shell environment.

How to rename Materialized view? ORA-32318

Now - this is what you call a long break .. almost a year. Well - The personal life has been rocking all during last year and I have good news to share, I'm a proud father of a cute little girl. We are still searching a name for her. That is some relief in Hindu religion that we can take up to some days before finalizing the name of baby.

I hope to be more punctual in writing my experiences here on the blog.
Today, I have something on materialized views. How to rename them?

Last week, I had this requirement to rename a materialized view and initial search on how to the same landed me on to ORA-32318.

ORA-32318: cannot rename a materialized view
Cause: Renaming a materialized view or its base table is not supported.
Action: Do not rename the base table of a materialized view.

Now, this is what I dont expect Oracle to do. when rename of table is possible, then why not rename of mat view possible? If any one knows a logical reason, please share.

Anyways, I gave it a further thought on how to accomplish this and an idea struck me.

I'm giving the information of our system but with changed names.
DB1 - where base table exists - 10.2.0.2
DB2 - where mview exists - 10.2.0.2
T1 - name of the table on DB1
M1 - name of the mview - Fast/Incremental refresh on demand

Now as discussed the requirement is to convert M1 to M2, with least down-time (at least for selects).

Here is some test setup scripts:

On DB1:

SQL> create table t1 as select * from all_objects;

Table created.

SQL> alter table t1 add primary key (object_id);

Table altered.

SQL> create materialized view log on t1;

Materialized view log created.

On DB2:


SQL> create materialized view m1 refresh fast on demand as select * from t1@db1; - db1 is database link

Materialized view created.

SQL> select count(*) from m1;

COUNT(*)
----------
62551

Now steps to change the name:

Step1: On DB1
- Stop DML/DDL activities on the base table T1

Step2: On DB2
- Refresh the M1 mview to ensure that there are no changes in there, so we can plan to change the name.
- Create another table with new name. This new name should be the name of mview intended.

SQL> create table m2 as select * from m1;

Table created.

>> Well this can help you in moving non-partition base-table/mview to partitioned base-table/mview

Step3: On DB2

Create new materialized view using "prebuilt" option.

SQL> create materialized view m2 ON PREBUILT TABLE REFRESH fast on demand as select * from t1@db1;

Materialized view created.

Step4: On DB2

Drop the old materialized view.

SQL> drop materialized view m1;

Materialized view dropped.

Thats it!

Now to check if thats working.

Delete few rows on t1@db1

SQL> delete from t1 where object_id in (2,3);

2 rows deleted.

SQL> commit;

Commit complete.

>> And now refresh the new mview.

SQL> exec dbms_mview.refresh('M2')

PL/SQL procedure successfully completed.

>> Check the no of rows:
SQL> select count(*) from m2;

COUNT(*)
----------
62549

Finally check the way oracle refreshed this mview.


SQL> select LAST_REFRESH_TYPE from user_mviews where MVIEW_NAME='M2';

LAST_REF
--------
FAST


Well that's just a confirmation :)

Note: I havent tested other functionalities of this test. So please do a thorough testing of this code before pushing into production database system.
BTW - we are doing the same.