Oracle data block corrupted

[alfie002]

Dear all,

ORA-01578

This post is just for general advice, particularly if some has experienced this error message. I have read through a number of articles on this issue and before I start dealing with it any advice or tips would be appreciated.

Thanks

Alf

 

[SantaMufasa]

Alf,

If you Google for "ORA-01578", you receive 47K+ hits. The very first link provides excellent guidance on specific actions you can follow to both diagnose and resolve your corrupt block.

Let us know if you have additional questions following your perusal of Googled links.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[alfie002]

hello Dave,

Many thanks for the advice. I did spot this one yesterday and it is on my reading list. I'll give it a go and take it from there. Dave, I take it that you have experienced something similiar ? If so, is this a tricky job ( suppose it depends on the scale of corruption ). I have a copy of the DB but I suspect it contains the same corruption. I suppose I can have several goes knowing that I can return to the start position by restoring a copy of the original database.

Thanks

Alf

 

[SantaMufasa]

Yes, Alf, I have had experience, on occasion, with a block corruption. There are multiple flavours of such a problem:[ul][li]Hard (physical) error. This happens if a problem arises with the magnetic integrity of the media upon which your database object resides.

If this happens, and I am not in a position to replace the storage media, then here is how I have dealt with it: Since reformatting will not solve the problem, I rename the affected object to something like "OBJ_HAS_MEDIA_DEFECT_DONT_DROP". I then restore/reconstruct that object with its original name. If the renamed object is a table, be sure to drop any dependent objects such as indexes, triggers, views, et cetera, then re-create those objects on the newly created replacement.

[/li][li]Soft (logical) error. This occurs if Oracle detects some sort of "confusion" in the data representation, such as a Cyclical-Redundancy-Check (CRC) problem or other algorithmic problem.

If this should occur, simply DROPping and re-CREATE-ing the object solves the problem.[/li][/ul]Your operating-system should include diagnostic utilities that can determine if your problem is physical. If the utility turns up no evidence of a physical defect, then you can proceed under the presumption that it is a logical problem.


Let us know how things turn out for you.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[alfie002]

Hello Dave,

I have read this article in addition to several others. I have noticed that RMAN keeps cropping up in other documents.

It is also mentioned in the article that you recommended.

From what I can tell, RMAN seems to be the tool for me in this case. We don't have a catalogue of backups to work with but RMAN seems to have the ability to recover datafiles in situ.

Another command "recover datafile <file_name>" also appears to be suitable for the job !!

What are your thoughts on this please?

Thanks

Alf

 

[SantaMufasa]

I highly recommend your trying it, but if your database is not in ARCHIVELOG mode, then you may have trouble...success will depend upon your on-line redo log files containing all of the transactions necessary for recovery.

Let us know the outcome.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[Thargy]

alfie,

if you consult the documentation, you will see that RMAN is the only tool which can cure block corruption, without blanket restoration.

Because RMAN is "aware" of Oracle's internal structure, it can perform fine-grained restoration, down to the individual blocks. This is the only tool capable of such precise action. Make every effort to use RMAN!

Regards

Tharg

Grinding away at things Oracular

 

[alfie002]

Hello Dave and thargtheslayer,

I have started to attempt the recovery of datafile, after many hours of readin material.

My first attempt, in line with my reading of RMAN, did work at all.

I recorded this error message;

RMAN> blockrecover datafile 1 block 54861;

Starting blockrecover at 12-NOV-07
using channel ORA_DISK_1


RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of blockrecover command at 11/12/2007 16:54:13
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 1 found to restore

RMAN>


My understanding from the Oracle material is that RMAN can recover corrupt blocks itself and doesn't necessarily need to resort to a previous backup. This error message tends to suggest this. Have I got something wrong here ??

Many thanks in advance

Alf

 

[alfie002]

Hello Dave,

if any earlier post, you mentioned the following;

"If this should occur, simply DROPping and re-CREATE-ing the object solves the problem".

In my case the system01.dbf datafile is being reported at fault. So, in your example Dave, do I drop the tablespace, table associated with that datafile ?

Also, I'm not having much joy with the RMAN program, it looks as though it needs historical data with which to work, which is something we don't have with this database. This will rule out the use of the RMAN program.

Thanks

Alf

 

[SantaMufasa]

Alf,

If your problem is with the system01.dbf datafile, then that opens up additional issues, since data dictionary objects could be involved.

If I were in your situation, then I, frankly, would do the following:[ul][li]export all applications users/schemas[/li][li]create a brand-new Oracle database[/li][li]import the dump files exported from the problematic database[/li][li]erase the entire problematic database.[/li][/ul]...But that's what Mufasa would do (and actually has done before).

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[alfie002]

Hello Dave,

having given the problem some thought, I think this is the best option. The reason for why is, although this file may be corrupt and is preventing the database from starting, the database that I am actually after is held in separate tablespace and respective datafiles.

i have checked a number of these datafiles and although some report a very small number of corruptions, I don't think it is a problem as they are already marked as corrupt.

The longer it has been taking me to get a fix for this, the more thought I have given to killing the existing Oracle instance, creating a new oracle instance, this will get around the system01.dbf file corruption. Then create the tablespace and users for the custom database. Then use the existing DBF files from this custom database.

Does this sound about right?

One thing I am not sure about though, is when the new Oracle instance is created, I had planned to use the Oracle EMS tool to establish the link between the new tablespace and the corresponding dbf files on disk. On second thoughts though the create tablespace task will require me to provide this information anyway.

So, once this has been done, I hope to be in a position to connect to the new tablespace and datafiles using the new Oracle instance.

Does this sound about right ? Any additional pointers, much appreciated.

Thanks

Alf

 

[SantaMufasa]

Alf,

You cannot use the .dbf files created by one database on another database.

Here are your options:[ul][li]Restore a database:[/li][ul][li]Physical Restore: Steps:[ul][li]Shutdown database.[/li][li]Copy the following files from archives back to their production locations[/li][ul][li]Control files[/li][li]On-line Redo Log Files[/li][li]Database Data Files[/li][/ul][li]Startup database.[/li][/ul][/li][li]Logical Restore: Import dump files into empty schemas.[/li][/ul][/ul][ul][li]Recover a database:[/li][ul][li]Pre-requisites:[/li][ul][li]Database must be in ARCHIVELOG mode.[/li][li]You must take a full database backup (which includes the above three sets of files) following your placing the database in ARCHIVELOG mode.[/li][li]You must have all archive log files available since the full database backup.[/li][/ul][li]Recovery Steps:[/li][ul][li]Restore one or more backup files over files needing recovery.[/li][li]Issue commands:

SQL> startup mount
SQL> recover database
SQL> alter database open

[/li][/ul][/ul][/ul]Let us know if you have additional questions.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[alfie002]

Hello again Dave,

Dave, apologies, I should have made it a bit more clear. What I had planned to do was.

1. Backup the control files
2. Backup the redo log file
3. Backup the DBF files

Then, install a new Oracle server, ideally on another machine, then move the trio of file set to this Oracle server and start the Oracle server with these file sets.

This way I alleviate the issue of the corrupt system01.dbf file, allow the Orcale server to start, at least, and then ascertain what damage may or may not exist to the DBF files. Does this sound ok with you.

Thanks

Alf

 

[SantaMufasa]

Does this sound ok with you.

You can certainly try it, but if there is a corruption in the the old "system01.dbf" file, then the chances are excellent that the backup of that corrupt file will preserve the corruption...That is why I suggested that you export the application users/schemas: doing so avoids/reduces the likelihood of touching any information from the "system01.dbf" file.

Let us know the outcome.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[alfie002]

hello Dave,

Thanks for the update.

As I understand it, this isn't my system ( I have inherited this system (unforuntately) ), is that when the application installs, it creates its tablespace and then store its data in 10 dbf files which are separate to the Oracle DBF files.

I do believe it installs one user account at the start of the installation stage with DBA rights.

Are you saying this user account will be contained in the System01.dbf file, or rather the User01.dbf file ?

In the case above, I would expect this application's data ( I'm 99% sure ) to be contained in separate tablespace's from the system data in the system tablespaces.

This is the theory behind my reasoning. The idea being to rebuild a new oracle server platform, containing the following Oracle dbf files;

22/11/2007 10:39 20,979,712 CWMLITE01.DBF
22/11/2007 10:39 20,979,712 DRSYS01.DBF
22/11/2007 10:39 156,639,232 EXAMPLE01.DBF
22/11/2007 10:39 26,222,592 INDX01.DBF
22/11/2007 10:39 20,979,712 ODM01.DBF
22/11/2007 10:39 618,668,032 SYSTEM01.DBF
01/11/2007 19:15 1,048,584,192 TEMP01.DBF
22/11/2007 10:39 10,493,952 TOOLS01.DBF
22/11/2007 10:39 3,093,307,392 UNDOTBS01.DBF
22/11/2007 10:39 26,222,592 USERS01.DBF
22/11/2007 10:39 49,160,192 XDB01.DBF

My plan was to then create the new tablespaces for the 10 application data files using the EMS.

Is this method viable ?

Thanks

Alf

 

[SantaMufasa]

The idea being to rebuild a new oracle server platform, containing the following Oracle dbf files

My question is, "How do you plan to rebuild a new Oracle server?

The options for a rebuild are either:

1) a physical rebuild, which requires non-corrupted datafiles from the original database, or

2) a logical rebuild, which requires the loading of previousloy exported dumpfiles (or some other data load [such as from .csv files]) from pre-existing archives.

Per option 1, you do not have a complete set of non-corrupted datafiles from the original database (since system01.dbf is corrupted).

Per option 2, if you do not have recently exported dumpfiles from the corrupted database, then you must attempt to export your applications' data from the corrupted database, then import those dumpfiles into another (un-corrupted) database.

How do your perceptions differ from my assertions, above?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[alfie002]

Hello Dave,

Thanks again for the update.

Dave, when I refer to a new oracle server platformm I mean start again from CD 1, CD 2 and CD 3 ( and patching ) for the installation of the Oracle software.

This will allow for the new build to be completed. At this stage there are no custom/user databases created. Just the sample DB's with the Oracle installation. This will allow the oracle server to be started with sqlplus or the Oracle EMS front end.

Now, from the point of view of accessing the data from the datafiles, the relevant tablespaces can be created using an application script. The relevent user(s) can be created using a application script.

(It may be at this stage where my understanding falls over)

From your previous updates, you refer to physical and logical rebuilds, at this stage the physical has to be ruled out. As you say the system01.dbf is broken. From a logical point of view once I create the tablespaces, why can I not use the Oracle EMS to create another (custom) datbase and use the data contained in the datafiles. ( maybe I am missing something at this stage )

Thanks

Alf

 

[alfie002]

Dave, if I cannot get the database started how can I use the Oracle export/import utility. The data that I am after is contained in the dbf files, separate to the system DBF files.

Can I not use an existing new build Oracle instance to access this data ?

Thanks

Alf

 

[SantaMufasa]

If I cannot get the database started how can I use the Oracle export/import utility.

If the database with the corrupted data file will not start (and if you do not already have either logical or physical backups), then you are S.O.L (Surely Outta Luck) for getting data from that database.

The data that I am after is contained in the dbf files, separate to the system DBF files. Can I not use an existing new build Oracle instance to access this data ?

You cannot mix database data files from different databases. Therefore, you cannot take the non-corrupted database data files from the "old" instance and access them from the "new" database that has a non-corrupted system01.dbf file.

I'm sorry to be the bearer of bad news, but that is also why we tend to take regular physical and logical backups (of which it sounds like you have neither).

Taking backups is like paying insurance premiums...If you haven't been paying the premiums, then you cannot expect recovery when a disaster happens.



Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]