how to add existing datafiles to database?

how to add existing datafiles to database?

Post by inn » Sat, 08 Sep 2001 05:49:50



Hi, This is my question:
The server where Oracle 8.0 was installed , crushed .
I have to reinstall Oracle. But I have still Ora_home\database folder
where I have datafiles of old databases, I have backup of pfile , but
I am not sure about .ctl file if I have it good.
Is it possible after I install Oracle on another drive, to recover
those databases,  datafiles of which I have.  If Yes How?
Thank you.
 
 
 

how to add existing datafiles to database?

Post by Howard J. Roger » Sat, 08 Sep 2001 09:38:04



Quote:> Hi, This is my question:
> The server where Oracle 8.0 was installed , crushed .
> I have to reinstall Oracle. But I have still Ora_home\database folder
> where I have datafiles of old databases, I have backup of pfile , but
> I am not sure about .ctl file if I have it good.
> Is it possible after I install Oracle on another drive, to recover
> those databases,  datafiles of which I have.  If Yes How?
> Thank you.

Yes, you can do it, though life will be much, much easier if the directory
layouts can remain exactly as before.

Just re-install Oracle, re-set all your environment variables (ORACLE_SID,
ORACLE_HOME etc), and you should simply then be able to say 'startup' in
server manager.  Recovery might be needed, but that will either happen
automatically without even being noticed, or you'll be prompted to apply
redo from archive logs (and I assume they are still available?)

Your problem might well be the control file, however, in which case you'd
best research the 'create controlfile' SQL syntax... you'll have to startup
nomount first, then issue that command.  It can get tricky if you had read
only tablespaces, but otherwise, it will recreate a functional controlfile
from scratch.  The syntax can be awkward to construct from scratch, because
it must include a pointer (ie, path and file name) to every data file and
redo log that used to exist.

And this is yet another reason why anyone who isn't doing an 'alter database
backup controlfile to trace' every night wants their heads examined... it
produces a text file output that *is* the SQL syntax, with all the right
paths to your datafiles and redo logs built in.  There's never a question
about whether it's 'right' or not (I assume you're referring to whether it's
consistent and useable or not?), because it's just a text file.

Before you start doing any attempts at startup, I strongly suggest you take
a complete backup of every data file and redo log you've got.  At least that
way you can restore and try again if anything goes horribly wrong.

Regards
HJR

 
 
 

how to add existing datafiles to database?

Post by godma » Sat, 08 Sep 2001 17:58:56


Hi there,

   Is the database archive on or off?
   Did you shutdown the database gracefully the very last time?

Allan W. Tham
DBA

 
 
 

how to add existing datafiles to database?

Post by Mark D Powe » Sat, 08 Sep 2001 21:56:29



> Hi, This is my question:
> The server where Oracle 8.0 was installed , crushed .
> I have to reinstall Oracle. But I have still Ora_home\database folder
> where I have datafiles of old databases, I have backup of pfile , but
> I am not sure about .ctl file if I have it good.
> Is it possible after I install Oracle on another drive, to recover
> those databases,  datafiles of which I have.  If Yes How?
> Thank you.

If ALL your database files including redo, control are still present
then once you re-install Oracle then providing it is the same version
you may not have to manually recover your database at all.  You would
need to recreate your init.ora for any non-default settings you were
using but if the other files are still present you should be able to
start Oracle and let it go into recovery mode.  If everything is
present it will be fine.

If on the other hand you lost a few of the database files then how you
preceede depends on what you lost and what kind of backups you have:
hot or cold physical backups or only logical backups exports or data
extractions.

Which brings up the point, Why are you re-installing Oracle? Don't you
have a backup of the Oracle binary (Oracle Home)?  If so just recover
it.  Otherwise besides the init.ora file you may have to reconfigure
your Net8 files depending where your system keeps them: /etc,
/var/opt/oracle, or $ORACLE_HOME/network/admin.

If your system runs in archive log mode then you also need to make
sure that your archived redo logs are available.

If you have hot backups you would copy any missing files from the hot
backup location overlaying the database file.  Then start the db and
Oracle will notify you of what it needs.

If you have cold backups then you must overlay all database data files
including the control file but not the redo logs to put the entire
database at a consistent point in time.

There is an excellent chapter in the DBA Admin manual on recovery.  It
does a very good job of explaining how to perform backups and
recovery, and should help you figure out what it is you need to do.
There is a good chance you will not even have to consult the Backup
and Recovery Manual unless you made your backups using rman.

I hope this helps.

-- Mark D Powell --

 
 
 

how to add existing datafiles to database?

Post by inn » Wed, 12 Sep 2001 04:39:03




> > Hi, This is my question:
> > The server where Oracle 8.0 was installed , crushed .
> > I have to reinstall Oracle. But I have still Ora_home\database folder
> > where I have datafiles of old databases, I have backup of pfile , but
> > I am not sure about .ctl file if I have it good.
> > Is it possible after I install Oracle on another drive, to recover
> > those databases,  datafiles of which I have.  If Yes How?
> > Thank you.

> If ALL your database files including redo, control are still present
> then once you re-install Oracle then providing it is the same version
> you may not have to manually recover your database at all.  You would
> need to recreate your init.ora for any non-default settings you were
> using but if the other files are still present you should be able to
> start Oracle and let it go into recovery mode.  If everything is
> present it will be fine.

> If on the other hand you lost a few of the database files then how you
> preceede depends on what you lost and what kind of backups you have:
> hot or cold physical backups or only logical backups exports or data
> extractions.

> Which brings up the point, Why are you re-installing Oracle? Don't you
> have a backup of the Oracle binary (Oracle Home)?  If so just recover
> it.  Otherwise besides the init.ora file you may have to reconfigure
> your Net8 files depending where your system keeps them: /etc,
> /var/opt/oracle, or $ORACLE_HOME/network/admin.

> If your system runs in archive log mode then you also need to make
> sure that your archived redo logs are available.

> If you have hot backups you would copy any missing files from the hot
> backup location overlaying the database file.  Then start the db and
> Oracle will notify you of what it needs.

> If you have cold backups then you must overlay all database data files
> including the control file but not the redo logs to put the entire
> database at a consistent point in time.

> There is an excellent chapter in the DBA Admin manual on recovery.  It
> does a very good job of explaining how to perform backups and
> recovery, and should help you figure out what it is you need to do.
> There is a good chance you will not even have to consult the Backup
> and Recovery Manual unless you made your backups using rman.

> I hope this helps.

> -- Mark D Powell --

Thank you very match this forum is very helpful for me.
 
 
 

1. Add Existing Data File To Existing Database?

All,

In SQL Server 2000, I've inhereted an interesting situation from my
predecessor.  There's an existing database that contains SALES
information.  I have another, completely independent database for
CUSTOMERS.  I would like to merge them into a single database and then
do some normalization.  Since they're already in separate data files,
and I planned to use multiple data files across a RAID anyway, I
figured I could use ALTER DATABASE to add the existing CUSTOMERS data
file (CUSTOMER_DATA.MDF) to the SALES database.

Alas, I can't seem to find a way to do this.  The ALTER TABLE command
allows me to add a *new* data file, but not attach, append or merge an
existing one.

Does anyone have any suggestions?  Does anyone have any experience doing this?

TIA,

:Pete

2. xp_cmdshell error

3. Problem with rollback segments

4. How to modified existing field and add new field in existing table for Access database

5. installing 'help' files

6. Resizing Datafiles vs Adding Datafiles

7. Audit Logout in Profiler in between Processing

8. adding datafile to a database

9. Adding a datafile to a standby database

10. How do I add one column (if not Exist) to 10 Databases

11. Adding column to an existing database

12. Adding an existing Database to an SQL Server