Simpler Navigation for Servers and Operating Systems - Please Update Your Bookmarks
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
If you have bookmarked forums or discussion boards in Servers and Operating Systems, we suggest you check and update them as needed.
cancel
Showing results for 
Search instead for 
Did you mean: 

Moving datafile

fabiosax
Advisor

Moving datafile

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
15 REPLIES
Steven E. Protter
Exalted Contributor

Re: Moving datafile

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
Steven E Protter
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com
fabiosax
Advisor

Re: Moving datafile

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
Honored Contributor

Re: Moving datafile

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
Advisor

Re: Moving datafile

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
Honored Contributor

Re: Moving datafile

A shutdown would be better.
fabiosax
Advisor

Re: Moving datafile

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
Honored Contributor

Re: Moving datafile

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
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Yogeeraj_1
Honored Contributor

Re: Moving datafile

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
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
fabiosax
Advisor

Re: Moving datafile

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

Thanks so much.
Fabio
Yogeeraj_1
Honored Contributor

Re: Moving datafile

hi fabio,

i would prefer to do a safe:

a. create new tablespace
b. move tables to new tablespace using: 'alter table move tablespace
c. rebuild associated indexes


how big is the tablespace?

kind regards
yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
fabiosax
Advisor

Re: Moving datafile

Hi..
about 160Mb with 135Md of data.
The problem is that the actual datafile has 3 blocks corrupted and after having tried to fix them i had problems fixing orphan keys.
So i thought to replace the actual datafile with an old one but with less data and with the same structure.
Sorry for all my questions but i' trying to fix something i never faced.
So I wonder if exist a method to replace the actual defect datafile with an old one but with less data.
Thanks yogeeraj really appreciated!!!
//FAbio
Yogeeraj_1
Honored Contributor

Re: Moving datafile

hi fabio,

this is the last help that i can provide you for today.

A document containing information on how to Handle Oracle Block Corruptions in Oracle7/8/8i/9i/10g

hope this helps!

kind regards
yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
fabiosax
Advisor

Re: Moving datafile

Thanks so much.
Regards
//Fabio
Yogeeraj_1
Honored Contributor

Re: Moving datafile

hi fabio,

hope you managed to fix the problem.

keep us updated.

kind regaards
yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
fabiosax
Advisor

Re: Moving datafile

Hello there,
just to let you know that we managed to fix the problem avoiding to use the old datafile.
We just used the rowid to select data from the corrupted datafile ( the errors was into a table) and then insert it to a new table.

Thanks to everyone for your support.
Regards,
Fabio