10 Replies Latest reply: May 28, 2008 4:05 AM by 639692 RSS

    Rebuild datafile

      New to a job

      Oracle ver and Sun solaris

      Noarchivelog, no backup.

      I try to open the database and it give me these error

      ORA-01122: database file 46 failed verification check
      ORA-01110: data file 46: '...../xxxx_index_01.dbf'
      ORA-01200: actual file size of 2316800 is smaller than correct size of 2355200

      SQL> alter database datafile '...../xxxx_index_01.dbf' offline drop;

      and found out that there are 12 datafile giving me these errors. I did it for all just to get the database open.

      Do you know how I go about fixing these files?

      If I have 20 datafile for 1 tablespace and only 5 datafiles are bad, how do I fix this? Do I just rebuild the index?

      Thank You
        • 1. Re: Rebuild datafile
          Without backup and archive logging, you can't recover your database.
          • 2. Re: Rebuild datafile
            How about just rebuild the datafiles?
            • 3. Re: Rebuild datafile

              Only solution is to restore datafiles from backup. without backup you cant open your database, is this a production DB.


              • 4. Re: Rebuild datafile
                Yes. this is a production DB. I got the database open by taking all the bad datafiles offline drop.

                Any idea that I can try?

                Thank You
                • 5. Re: Rebuild datafile
                  Shouldn't you have stopped when you realised that 12 datafiles were failing ?

                  There must be a reason why so many datafiles failed. Dropping the datafiles
                  wasn't the right thing to do immediately.
                  It could have been filesystem corruption and reusing the same filesystem isn't
                  a good idea if the filesystem is corrupt.

                  You cannot rebuild a datafile. You must rebuild the objects (ie indexes) that
                  existed in the tablespace. Tables, Indexes etc are associated with Tablespaces
                  and may span multiple datafiles existant in the same tablespace.
                  • 6. Re: Rebuild datafile
                    As long as these datafiles are not belong to system tablespace and active undo tablespace, you can drop the tablespace. However you will lost the data forever.
                    • 7. Re: Rebuild datafile
                      If my tablespace have mult datafiles, what happen if only one is bad? Can I fix that datafile?

                      Thank You
                      • 8. Re: Rebuild datafile
                        you can RESTORE a datafile from a backup to RECOVER it, if you have all the
                        ArchiveLogs from the backup.

                        There's no such thing as "fixing" a datafile in Oracle.
                        You can either
                        a) RESTORE and RECOVER a datafile
                        b) RECREATE the Tablespace with all it's objects.
                        • 9. Re: Rebuild datafile
                          No that's not possible, especially when you are in non archivelog mode.
                          • 10. Re: Rebuild datafile

                            It's time to raise an SR with Oracle support. They can usually work some magic if the lost data is important enough.

                            I guess you've learnt the lessons around taking backups and noarchivelog mode now. If you are unlucky enough to ever experience this sort of thing again, it's well worth taking a backup of the broken database if you can. Then at least you can go back a step if you make a bad decision.

                            Good luck,
                            Andy Barry