Re: Is it possible to add existing datafiles to an oracle database?

From: Srinivas Chintamani <>
Date: Mon, 28 Apr 2008 09:12:30 +0530
Message-ID: <>

The post to is not mine. Having read through the said post, I thought I might get some ideas from more experienced DBAs help solve my issue. I am TOTALLY new to Oracle backup/recovery, its something that got pushed onto me ...

Anyways, let me try an explain my situation in a little bit more detail ...

Ours is an Agile shop and each developer gets his/her own schema on the db.

We have an imp dump, that gets loaded into a new schema, whenever a new developer joins the team. Each new schema gets its own tablespace - each tablespace consisting of two datafiles each.

Once the db was setup (on a windows 2003 standard server) a few months ago, more and more developers added their own schemas into the db - the count being about 40 (fourty) schemas when the db crashed.

The db refused to startup, since the UNDO tablespace file was smaller than what Oracle expected. The error was:

  • Error Start --------------- ORA-01122: database file 2 failed verification check ORA-01110: data file 2: 'C:\ORACLE\ORADATA\TRACS3\UNDOTBS01.DBF' ORA-01200: actual file size of 83976 is smaller than correct size of 138240 blocks
  • Error End -----------------

I think that all the other datafiles are ok and was thinking if it were possible at all to take some schema's datafiles and load them up into some other database.

I had logged an SR with Oracle support, which they wanted us to bump to SEV-1 (after someone from Oracle support tried to bring the db up over CollabSuite for about four/five hours). Since I had already spent about a day trying to get this thing to come up, we could not afford to loose any more time, hence we deleted the db and created a new one.

Since, I do have a full file sytem backup of the relevant Oracle files (Control files, datafiles, archived redo log files), I was trying to see if I can recover any of the data in those datafiles into a new oracle db. My apologies, if I sound too simplistic... as I said, I am a rookie in this arena.


On Sat, Apr 26, 2008 at 1:37 PM, Hemant K Chitale <> wrote:

> "Just take a backup, ...."
> Even in Oracle it is very easy . You need to know which files to backup.
> However, I believe that you had posted this on as well.
> You had a datafile where the filesize in the header doesn't match the size
> on
> the filesystem -- possibly an instance abort / server failure while the
> file was
> being resized.
> At 09:31 PM Friday, Srinivas Chintamani wrote:
> > Hi,
> > The crashed database is deleted now and I only have the datafiles of the
> > crashed database somewhere on my filesystem. What I am trying to do is to
> > find out if, it is possible to load the data from the datafiles of the
> > crashed database into another new database.
> >
> > Earlier when working with SQL Server, it was simple to backup / restore
> > the db. Just take a backup, dump the backup file anywhere on the filesystem
> > and point to SQL Server, where the backup file is at and it happily
> > recovered the db, all in a few seconds.
> >
> > Wonder why restoring an oracle db is such a pain ...
> >
> > Regards,
> > Srinivas.
> >
> >
> Hemant K Chitale
> "A 'No' uttered from the deepest conviction is better than a 'Yes' merely
> uttered to please, or worse, to avoid trouble."
> Mohandas Gandhi Quotes :

Srinivas Chintamani

Received on Sun Apr 27 2008 - 22:42:30 CDT

Original text of this message