Moving datafile

[fabiosax]

Hello there,
could you please help me with the following:
As my actual datafile as corrupted blocks i wish to alter the tablespace replacing the actual datafile with an old one with less data.
Could be possible?
May i follow the following steps:
-alter tablespace offline
-alter database rename file
-alter tablespace online
may i rename the datafile with an mounted database?
Thansk in advance for any tips.
Regards,
Fabio

[Steven E. Protter]

Shalom Fabio,

Why not just down the database and overwrite with a recent cold backup.

Then you can apply archive logs if you have them to make your data as current as possible.

If its just one tablespace, depending on your data relationship, your replacement method may work. It really depends on key relationships between the tables.

SEP

[fabiosax]

Thanks for your prompt reply....
So may I shutdown the db put it on open state and then replace the datafile?
For sure few blocks are corrupted into the tablespace and some indexes too.
After having replaced the datafile should i rebuild or analyse teh tablespace structure?
Thanks and sorry for my questions but i'm not an expert...
Thanks so much....
Fabio

[Rick Garland]

If the database is shutdown, cold, then these steps are not really necessary. Just put the older datafile in place and recover. It should work as you stated but does the older datafile have the same relationships as the newer file?

[fabiosax]

Hi..
the restored datafile has less data respect the corrupted one but it's identical as structure.
So I need to shutdown the db replace the datafile and the raise a startup?
Thanks...
Fabio

[Rick Garland]

A shutdown would be better.

[fabiosax]

Thansk to all .. I'm going to follow what you suggested.. and let you know...
but just the last question:
after having replaced the datafile with the restored one should i just restart the db?
I'm in the meanwhile readind documentation and they suggested to do a recover tablespace after teh startup, should I?

Regards,
Fabio

[Yogeeraj_1]

STOP!


with Oracle it is not just as easy as that!
You can't just replace the datafile like that. You may corrupt your database further!!!


At worst you will have to restore your whole database on another machine and do recovery using sqlcopy or export.

kind regards
yogeeraj

[Yogeeraj_1]

hi again,

What backup strategy to you have in place?

If you have a cold backup, you will have to do a full restore and recovery.


You should follow the steps as per the strict guidelines.

If this is something mission critical, it will be preferrable to contact oracle support!


kind regards
yogeeraj

[fabiosax]

Ok..
so may i follow these instructions:
--ALTER TABLESPACE xxxx OFFLINE IMMEDIATE;
--cp
--RECOVER TABLESPACE xxxx
--ALTER TABLESPACE xxxx ONLINE;

Thanks so much.
Fabio