ORACLE data block corrupted 1

[scohan]

Using Oracle 8.1.5 on a SunOS I've been trying to set up a development database where a production database resides (I know, but due to lack of resources that's all we have.) I got the dev db up and running for the most part, but now trying to access the prod db in sqlplus I get the following:

ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-01578: ORACLE data block corrupted (file # 11, block # 504)
ORA-01110: data file 11: '/data/u01/oradata/loanrate/lroldev_rb1.dbf'


The data file mentioned is a rollback datafile for my dev db, not my prod db. Why is my prod db trying to access my dev data files????

Please help ASAP. Demos scheduled all week. Thanks a bunch.

 

[carp]

Is it possible that you are really logged into your DEV database and think it's your PROD database? We've had this happen many times with our customers. A quick execution of
SELECT value FROM v$parameter WHERE name='instance_name';
will confirm/deny this.

 

[scohan]

I'm not able to log in. I get the error mentioned above. But I have made sure my connect strind is correct.

Also, I have since shutdown the prod instance and attemtped to restart it, but get the following error:

ORA-01122: database file 10 failed verification check
ORA-01110: data file 10: '/data/u01/oradata/loanrate/lroldev_temp1.dbf'
ORA-01251: Unknown File Header Version read for file number 10

I'm trying to figure out what's wrong, but I really don't know what to look for. Any help would be greatly appreciated. Maybe taking a look at the init.ora files would help.

 

[karluk]

It sounds as if you are trying to start your production database with an init.ora file that is pointing to your dev control files. That would explain why prod is incorrectly trying to access dev files.

 

[scohan]

I have "/data/u01/oradata/loanrate/control01.ctl",
"/data/u01/oradata/loanrate/control02.ctl" for my prod initprod.ora file and "/data/u01/oradata/loanrate/control03.ctl",
"/data/u01/oradata/loanrate/control04.ctl" from initdev.ora file. Could I be running into the unix 8 character file name limitations? Thanks.

 

[karluk]

Try mounting your database and running the following queries. You should see production names for all files.

select name from v$controlfile;
select name from v$datafile;
select member from v$logfile;

 

[scohan]

The control and logfiles look ok, but for the datafiles I get both the prod and dev files showing up (below.) I shouldn't see the dev files should I when I'm connected to the prod db?

I've since dropped the dev db and when I mount the the prod db I get:

ORACLE instance started.
Total System Global Area 35995024 bytes
Fixed Size 64912 bytes
Variable Size 18972672 bytes
Database Buffers 16777216 bytes
Redo Buffers 180224 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 10 - see DBWR trace file
ORA-01110: data file 10: '/data/u01/oradata/loanrate/lroldev_temp1.dbf'
SVRMGR>

I'm thinking I have to drop and recreate the prod db. Thinks so?

Here's the result of 'select name from v$datafile;'

/data/u01/oradata/loanrate/system01.dbf
/data/u01/oradata/loanrate/oemrep01.dbf
/data/u01/oradata/loanrate/rbs01.dbf
/data/u01/oradata/loanrate/temp01.dbf
/data/u01/oradata/loanrate/users01.dbf
/data/u01/oradata/loanrate/indx01.dbf
/data/u01/oradata/loanrate/drsys01.dbf
/data/u01/oradata/loanrate/lrol_data1.ora
/data/u01/oradata/loanrate/lrol_index1.ora
/data/u01/oradata/loanrate/lroldev_temp1.dbf
/data/u01/oradata/loanrate/lroldev_rb1.dbf
/data/u01/oradata/loanrate/lroldev_data1.dbf
/data/u01/oradata/loanrate/lroldev_index1.dbf

 

[karluk]

Maybe you accidentally did some of the dev tablespace creation while logged on to production. You're right, this mixture of dev and prod files looks really bad. Can you recover prod to a point before you started to build dev, or would you lose too much data?

 

[scohan]

Will an export of the db suffice for what I'd need to do? I don't think there's a bakkup of the datafiles, control files, etc., but I'll check.

I don't have the original db creation scripts (I've taken over this project from another company), but I'm working on getting them (if they exist.)

Thanks, karluk!

 

[karluk]

There are a number of possibilities. The first thing to do is stay calm. The second thing I would do is shutdown production and make a backup of all the database files, log files, and control files. Then you can always get back to the current situation if your recovery doesn't go well.

After this you need a recovery strategy. I gather that you already have an export of at least the application data. Is that right? That will allow you, if necessary, to build a completely new database and import data from the export file. This limits the amount of data you might lose to when the latest export was taken.

The question is whether you can recover the existing database and avoid all data loss. You have a chance, because the ORA 1578 happened on one of the dev files.

On Metalink there is an article on dealing with data block corruption (NOTE 28814.1). It specifically says that if you get an ORA 1578 on startup to contact Oracle customer support. That's your situation, so you should do so. They apparently have some unpublished recovery scenarios that an Oracle rep can walk you through.

 

[scohan]

I think I'll be safe enough with importing with the latest dmp file. Thanks for the help, karluk.

 

[MattDee89]

Check you init****.ora file.
Go to svrmgrl and enter
startup pfile=/usr/oracle/pfile/init***.ora

This will make sure to start the database with the correct init file.

Once this is working go to oradim and edit the parameters to point to that file.

If THIS doesn't work recover the datafile with the recover command at svrmgrl...

Cheers,
Matt

 

[carp]

If you get to the point where you're going to jettison your database, there is one last card you might want to play:

Issue the command
ALTER DATABASE BACKUP CONTROL FILE TO TRACE;

This creates a trace file that includes a CREATE CONTROL FILE script at the end of it.
Find this file, delete everything that precedes the CREATE CONTROL FILE command. Also, delete any references to the datafiles from your DEV database.

Now shutdown your database and delete the control files.
Do a startup nomount, and run the script file with the CREATE CONTROL FILE command in it.

You may be able to now get your database open.

I reiterate - this should be your last gasp effort before destroying the database; sometimes this works perfectly and other times I've seen it do nothing but change the error message.

Good luck!

 

[scohan]

Thanks everyone for the help. What I ultimately did was take the easy way out droppiong the database and recreating it from an export file. The export had the dev tablespaces so I just dropped them after the import. Hopefully, everything's fine. Luckily, I can afford to go to an export file that's few days old. Karluk, I think your right, I must have done some of the dev tablespace creation while logged on to production. Now that I'm awake, I think I'll be able to avoid problems like these - at least I'll be more carelful. Thanks again.
;-)