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

  • From: "Srinivas Chintamani" <srinivas.chintamani@xxxxxxxxx>
  • To: "Hemant K Chitale" <hkchital@xxxxxxxxxxxxxx>
  • Date: Mon, 28 Apr 2008 09:12:30 +0530

Hi,
The post to forums.oracle.com 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.

Regards,
Srinivas.


On Sat, Apr 26, 2008 at 1:37 PM, Hemant K Chitale <hkchital@xxxxxxxxxxxxxx>
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 forums.oracle.com 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
> http://hemantoracledba.blogspot.com
>
> "A 'No' uttered from the deepest conviction is better than a 'Yes' merely
> uttered to please, or worse, to avoid trouble."
> Mohandas Gandhi Quotes :
> http://www.brainyquote.com/quotes/authors/m/mohandas_gandhi.html
>
>


-- 
Regards,
Srinivas Chintamani

Other related posts: