topic Re: Moving datafile in Operating System - HP-UX

Moving datafile

fabiosax — Fri, 18 Aug 2006 07:20:07 GMT

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

Re: Moving datafile

Steven E. Protter — Fri, 18 Aug 2006 07:30:48 GMT

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

Re: Moving datafile

fabiosax — Fri, 18 Aug 2006 07:38:52 GMT

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

Re: Moving datafile

Rick Garland — Fri, 18 Aug 2006 07:39:09 GMT

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?

Re: Moving datafile

fabiosax — Fri, 18 Aug 2006 07:49:03 GMT

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

Re: Moving datafile

Rick Garland — Fri, 18 Aug 2006 07:57:42 GMT

A shutdown would be better.

Re: Moving datafile

fabiosax — Fri, 18 Aug 2006 08:12:37 GMT

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

Re: Moving datafile

Yogeeraj_1 — Fri, 18 Aug 2006 08:38:44 GMT

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

Re: Moving datafile

Yogeeraj_1 — Fri, 18 Aug 2006 08:44:57 GMT

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

Re: Moving datafile

fabiosax — Fri, 18 Aug 2006 08:45:57 GMT

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

Thanks so much.
Fabio

Re: Moving datafile

Yogeeraj_1 — Fri, 18 Aug 2006 08:50:29 GMT

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

Re: Moving datafile

fabiosax — Fri, 18 Aug 2006 08:59:54 GMT

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

Re: Moving datafile

Yogeeraj_1 — Fri, 18 Aug 2006 09:00:09 GMT

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

Re: Moving datafile

fabiosax — Fri, 18 Aug 2006 09:07:05 GMT

Thanks so much.
Regards
//Fabio

Re: Moving datafile

Yogeeraj_1 — Sat, 19 Aug 2006 00:26:08 GMT

hi fabio,

hope you managed to fix the problem.

keep us updated.

kind regaards
yogeeraj

Re: Moving datafile

fabiosax — Thu, 24 Aug 2006 08:41:53 GMT

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