Re: Database error and couldn't be opened

You have no backups, and no archivelogs. Even if you had, the database is Standard Edition (but you did not tell us what release / patchset), so block level recovery would not be an option.

The *first* thing to do, as already mentioned, is to try to determine *why* you are getting IO errors. This may be physical damage to the storage media, or it may be a problem with software or interconnect, or a dozen other things.

Does the database always report IO errors in the same file and block number? Or do the errors seem to "move around"?

If the errors are always in the same place in the same file, it is possible you have encountered a storage (media) failure. You should probably get your system / storage administrators to *confirm* this before you leap to conclusions, though. While you wait for them to do that, you can use the *dbv* utility to check the datafile(s) in question for corruption; you might learn something new if *dbv* reports different errors -- or none.

*IF* you have encountered a storage error, at least some of your data is going to be lost. But you can probably get most of it back. You *could* try something like:

1. *Engage Oracle Support, and do NOTHING without their approval.*
2. Get a new disk to replace the failed one. Have your sysadmin create a filesystem on it, and mount it.
3. Copy *all* datafiles from the "damaged" disk (here we assume your storage admins have already confirmed physical media failure) to the new disk. It has been suggested that you use the *dd* utility with the *conv=noerror* option. Research this first, and understand what it does, before attempting it. (I expect that it *should* copy the files and skip over any blocks that cannot be read, but I don't have documentation handy to confirm that.) Be careful with *dd* -- it is a low-level utility with few safety checks, and it will happily destroy your data if you tell it to!
4. Unmount the damaged disk, and (re)mount the new one in its place.
5. *Engage Oracle Support again.* You should now be able to open the database, but some of your files *will* be corrupt. If there is corruption in an online redolog, you will probable need help from Oracle Support to "encourage" the database to open anyway.
6. *The moment the database is open, use RMAN to make a complete backup.* Then put the database in ARCHIVELOG mode. Any database containing data that you care about should ALWAYS run in archivelog mode. And nobody goes to this sort of trouble for data that they do not care about.
7. Start dealing with the corrupt blocks in your table(s). Again, Oracle Support can probably provide procedures for rebuilding your tables minus the rows that were in the corrupt blocks.

If you have not noticed a recurring theme in this, here it is: you are going to need a lot of help from Oracle Support. Bring them in on this early. Give them complete information. UNDERSTAND their suggestions and FILTER them carefully before you implement them. (Its not like Oracle Support has never expressed a "bad idea" before!) But most of all, be polite and pleasant and cooperative. They are here to help you, after all.

On Sun, Dec 28, 2014 at 1:11 PM, Peter Hitchman <pjhoraclel_at_gmail.com> wrote:

> Hi
> Try to use rman to do a block level recovery.
> If that does not work and you do have an Oracle support contract, contact
> support to get help on extracting what data can be saved from the database.
> Oracle support have tools that can read the database files, I cannot
> remember what they are called as its been a long time since I was were you
> are and things will have changed.
>
> Good luck.
>
> Pete
>
> On 28 December 2014 at 07:13, Mostafa Eletriby <
> dmarc-noreply_at_freelists.org> wrote:
>
>> Hello DBAs,
>> Please I need top resolve these errors at database as database is mounted
>> but couldn't be open.
>> There is no backup and database is in noarchivelog mode.
>> Database version:- standard edition.
>> O.s:- linux redhat v5
>>
>> Please check.
>> Thanks
>>
>> SQL> select status from v$instance;
>>
>> STATUS
>> ------------------------------------
>> MOUNTED
>>
>> SQL>
>> SQL> alter database open;
>> alter database open
>> *
>> ERROR at line 1:
>> ORA-01115: IO error reading block from file 7 (block # 44988)
>> ORA-01110: data file 7: '/oraData/cdsdbdata/ENRPGEST.dbf'
>> ORA-27072: File I/O error
>> Linux-x86_64 Error: 5: Input/output error
>> Additional information: 4
>> Additional information: 44988
>> Additional information: -1
>>
>>
>> SQL>
>>
>>
>>
>
>
> --
> Regards
>
> Pete
>

--
http://www.freelists.org/webpage/oracle-l