Legacy database has a corrupt datafile.

[Former Member]

Our legacy sap oracle 9.2 database has a corrupt datafile for tablespace psapbtabi. It's actually the last datafile added for the tablespace. This system is only used to run some reports and no transactional changes are being made to the data.

We have NO backups or archivelog backups to restore from.

The datafile is flagged to be recovered.

What do you suggest to get the database back to a running state?

Thanks in advance.

Scott

[Reagan · Accepted solution]

There are few SAP notes addressing corruption issues. You may start with this SAP note and also the ones mentioned in it.

1559652 - How to deal with block corruptions on Oracle

It appears to me that this system is not a critical system (not important system) as there are no valid backups and no backups for the archived logs.

PSAPBTABI is an index tablespace.

As the corrupt datafile is part of the tablespace PSAPBTABI then I would drop the datafile and extend the tablespace with a new datafile.

Check the indexes in DB02 and create the missing indexes using SE14.

Regards

RB

[Former Member · Accepted solution]

How do I drop the corrupt datafile that has data in it?

Regards.

Scott

[Reagan · Accepted solution]

Scott

I overlooked there. Apologies

You cannot drop a datafile which is not empty.

Here are the options I can think of.

Run this SQL query and provide the datafile.

SELECT owner, segment_name FROM dba_extents a, dba_data_files b WHERE a.file_id = b.file_id AND b.file_name = '&Datafile';

This will list all the extents in the datafile. Drop them.

Finally drop the datafile.

If this is not possible then you will have to drop the trablespace PSAPBTABI and recreate it and recreate the indexes.

Regards

RB

[Former Member · Accepted solution]

I think I'm missing something????

SQL> SELECT owner, segment_name FROM dba_extents a, dba_data_files b WHERE a.fil
e_id = b.file_id AND b.file_name = '&Datafile';
Enter value for datafile: H:\oracle\pr1\sapdata8\btabi_35\BTABI.DATA35
old   1: SELECT owner, segment_name FROM dba_extents a, dba_data_files b WHERE a
.file_id = b.file_id AND b.file_name = '&Datafile'
new   1: SELECT owner, segment_name FROM dba_extents a, dba_data_files b WHERE a
.file_id = b.file_id AND b.file_name = 'H:\oracle\pr1\sapdata8\btabi_35\BTABI.DA
TA35'

no rows selected

[Reagan · Accepted solution]

sqlplus "/as sysdba"

SELECT owner, segment_name FROM dba_extents a, dba_data_files b WHERE a.file_id = b.file_id AND b.file_name = 'H:\oracle\pr1\sapdata8\btabi_35\BTABI.DATA35';

and now ?

[Former Member · Accepted solution]

I'm sorry but I'm having a really hard time with this. Here is the output.

G:\usr\sap\PR1\sys\exe\run>sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.5.0 - Production on Fri Apr 25 13:23:25 2014

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production

SQL> SELECT owner, segment_name FROM dba_extents a, dba_data_files b WHERE a.fil
e_id = b.file_id AND b.file_name = 'H:\oracle\pr1\sapdata8\btabi_35\BTABI.DATA35
';

no rows selected

[Reagan · Accepted solution]

In that case what does Oracle say when you try to drop the datafile ?

[janos_mucsi-besze · Accepted solution]

Hi Scott,

You get zero results with the select query against an offline datafile. As soon as a datafile gets offline or recover state, you can not reach the segments/extents from it.

Also you can not drop a datafile which is offline or in recover status.
The only thing you could do is to recover the datafile, but to do this you will need all redolog files coming from the date when the datafile got offline.

Or there is another alternative solution, but it is risky.

You can use point '2' of note 19519. But before doing this please create a backup!

Regards,

János

[former_member188883]

Hi Scott,

tablespace psapbtabi ideally contains only indexes.

You may use SAP note 365481 - Block corruptions

to identify which indexes have problem.

You may drop the index and re-create them using db02.

Hope this helps.

Regards,

Deepak Kori