recovery from ORA-01200 error

recovery from ORA-01200 error

Post by Braxton Robbaso » Fri, 16 Mar 2001 08:07:37



hi all. I have a development db server that I crashed in a most horrible
way. I had a program writing data into the USERS tablespace, which was
stored in one big file that got to be too large for the disk to hold.

now, when I try to start the database I get:

ORA-01122: database file 5 failed verification check
ORA-01110: data file 5: '/data/oradata/ORCL/users01.dbf'
ORA-01200: actual file size of 2027014 is smaller than correct size of
2097138 blocks

I know I am supposed to restore from a backup at this point. I don't have a
backup because this is just a development server.  do I have to delete this
database and make a new one or is there a way to maybe even lose the whole
tablespace but recover the database itself?  I feel like there should be a
way to hack it.

Sheepishly,

Braxton

 
 
 

recovery from ORA-01200 error

Post by Brad Pee » Fri, 16 Mar 2001 15:55:51


I probably shouldn't try to answer this because its been a while since I've
done it but....

The short answer is "you are pretty much hosed".  However, if you happen to
have ALL of the archived redo logs since the data file was created, there is
a way to do it.   My guess is that you aren't even running in ARCHIVELOG
mode.  Right?   Let me know if you want to try it and I can point you to
some doc on how to do it (not too bad).   Here is an excerpt:

=================================
To re-create a datafile for recovery:

Create a new, empty datafile to replace a damaged datafile that has no
corresponding backup. For example, assume that the datafile disk1:users1 has
been damaged, and no backup is available. The following statement re-creates
the original datafile (same size) on disk2:

ALTER DATABASE CREATE DATAFILE 'disk1:users1' AS 'disk2:users1';

This statement creates an empty file that matches the lost file. Oracle
looks at information in the control file and the data dictionary to obtain
size information. The old datafile is renamed as the new datafile.

Perform media recovery on the empty datafile. For example, enter:

RECOVER DATAFILE 'disk2:users1'

All archived redo logs written since the original datafile was created must
be mounted and reapplied to the new, empty version of the lost datafile
during recovery.
=========================================

If recovering from the redo logs is not an option, you MAY be able to come
up with a running instance if you are willing to drop the tablespace and
lose all of its contents.  This is where my memory is fuzzy, so check it out
before you try it....

startup nomount
alter database datafile '/data/oradata/ORCL/users01.dbf' offline;
alter database open;
alter tablespace USERS offline;
DROP TABLESPACE users
    INCLUDING CONTENTS
        CASCADE CONSTRAINTS;

Good luck


Quote:> hi all. I have a development db server that I crashed in a most horrible
> way. I had a program writing data into the USERS tablespace, which was
> stored in one big file that got to be too large for the disk to hold.

> now, when I try to start the database I get:

> ORA-01122: database file 5 failed verification check
> ORA-01110: data file 5: '/data/oradata/ORCL/users01.dbf'
> ORA-01200: actual file size of 2027014 is smaller than correct size of
> 2097138 blocks

> I know I am supposed to restore from a backup at this point. I don't have
a
> backup because this is just a development server.  do I have to delete
this
> database and make a new one or is there a way to maybe even lose the whole
> tablespace but recover the database itself?  I feel like there should be a
> way to hack it.

> Sheepishly,

> Braxton


 
 
 

1. ORA-12154: Error while trying to retrieve text for error ORA-12154

 Hello All, I keep receiving these error messages when I try to connect
 to a network server using MS Access (DSN):

 Error while trying to retrieve text for error ORA-12154
 Error while trying to retrieve text for error ORA-12538

 I know my tnsnames.ora file is correct. I am using the Microsoft
 Oracle ODBC driver. I can ping the server,.other applications (Vision
 ClearAccess and other company related programs) can access the
 database but MS Access. SQL+ can access the server too. Help !

 Thanks in Advance
 Sharron D Allen

2. Object Oriented Wrapper classes to SQL statements

3. ORA-600, ORA-8177, ORA-6512 Errors via ODBC

4. Library printing reports on text printers !

5. Create new DB errors (ORA-01610, ORA-01194, ORA-01110)

6. oninit -i

7. ORA-01115,ORA-01110,ORA-27070,OSD-04006,O/S-Error OS 38

8. Format - Pop-up Menu

9. Error: ORA-01034, ORA-09243, ORA-02042

10. RMAN point in time recovery error ORA-01830

11. RMAN point in time recovery error ora-01830

12. ORA-20001 error in Recovery Manager

13. PL/SQL ERROR ORA-06552 ORA-06553: