Can someone explain to me why this won't work?

I need to move a datafile on a very busy tablespace. The objects in the
tablespace are only updated once a day, after that it's 100% read. I want
to move the file during this 100% read time. The conventional method is
to take the tablespace offline, move the file in the o/s, rename it in
Oracle, and bring the tablespace back online. I want to keep the
tablespace available throughout the entire operation so I thought I would
try the following.

o Place the tablespace in read only mode.
o Copy the datafile to the new filesystem
o Rename the datafile in Oracle.
o Bring the tablespace back online
o Delete the original file from the operating system.


This is pretty much what's documented in the 8i SQL reference page 8-73
(for those who require proof you've actually read the documentation).

"Once a tablespace is read only, you can copy its files to read-only
media. You must then rename the datafiles in the control file to point
to the new location by using the SQL statement ALTER DATABASE ...
RENAME."

The only difference is I'm copying it to another read/write media, not
read-only media. When I try it however I get the following error when I
try to rename the file in oracle. Why?

SQL> alter database
2 rename file '/u23/oradata/DBATOOL/test01.dbf'
3 to '/u22/oradata/DBATOOL/test01.dbf';
alter database
*
ERROR at line 1:
ORA-01511: error in renaming log/data files
ORA-01121: cannot rename database file 11 - file is in use or recovery
ORA-01110: data file 11: '/u23/oradata/DBATOOL/test01.dbf'


--
Chuck
Remove "_nospam" to reply by email

I'm no pro to take it all with a grain of salt. As far as I know, making a
tablespace read only just means no more transactions can be made against the
database.

Existing transactions could still be pending against that Tablespace? Or not
because you just update once a day, but does Oracle know this fact? What
about forcing a checkpoint?

Have you putting the tablespace in backup mode to do this?

Just bouncing off some ideas, but my best guess is that oracle is preventing
the move because it "thinks" the file is being used or "could" have pending
updates.











"Chuck" <chuckh_nospam@softhome.net> wrote in message
news:Xns948E9046CF8DBchuckhsofthomenet@130.133.1.1 7...
> Can someone explain to me why this won't work?
>
> I need to move a datafile on a very busy tablespace. The objects in the
> tablespace are only updated once a day, after that it's 100% read. I want
> to move the file during this 100% read time. The conventional method is
> to take the tablespace offline, move the file in the o/s, rename it in
> Oracle, and bring the tablespace back online. I want to keep the
> tablespace available throughout the entire operation so I thought I would
> try the following.
>
> o Place the tablespace in read only mode.
> o Copy the datafile to the new filesystem
> o Rename the datafile in Oracle.
> o Bring the tablespace back online
> o Delete the original file from the operating system.
>
>
> This is pretty much what's documented in the 8i SQL reference page 8-73
> (for those who require proof you've actually read the documentation).
>
> "Once a tablespace is read only, you can copy its files to read-only
> media. You must then rename the datafiles in the control file to point
> to the new location by using the SQL statement ALTER DATABASE ...
> RENAME."
>
> The only difference is I'm copying it to another read/write media, not
> read-only media. When I try it however I get the following error when I
> try to rename the file in oracle. Why?
>
> SQL> alter database
> 2 rename file '/u23/oradata/DBATOOL/test01.dbf'
> 3 to '/u22/oradata/DBATOOL/test01.dbf';
> alter database
> *
> ERROR at line 1:
> ORA-01511: error in renaming log/data files
> ORA-01121: cannot rename database file 11 - file is in use or recovery
> ORA-01110: data file 11: '/u23/oradata/DBATOOL/test01.dbf'
>
>
> --
> Chuck
> Remove "_nospam" to reply by email

"craig" <someone@nowhere.com> wrote in message
news:gBaXb.72411$fD.38968@fed1read02...
> I'm no pro to take it all with a grain of salt. As far as I know, making
a
> tablespace read only just means no more transactions can be made against
the
> database.
>
> Existing transactions could still be pending against that Tablespace?

No, because if there's a pending transaction, then you can't actually make
it read-only. And if there are dirty buffers floating around from finished
transactions, then those are flushed to disk before the thing becomes
read-only (ie, making something read-only causes a checkpoint).

>Or not
> because you just update once a day, but does Oracle know this fact? What
> about forcing a checkpoint?

Irrelevant. There's already a checkpoint done when he makes it read-only.

> Have you putting the tablespace in backup mode to do this?

Er, it is actually impossible to put a read-only tablespace into hot backup
mode, and there is in any case precisely zero need to do so. Since it is
read-only, the data file header checkpoint change number is already locked,
and users can't be modifying any blocks so there can be no possibility of
block fracturing.

> Just bouncing off some ideas, but my best guess is that oracle is
preventing
> the move because it "thinks" the file is being used or "could" have
pending
> updates.

Close. The problem is actually that the control file is aware that the file
is online, and you can't syntactically rename a file unless and until it is
offline. The fact that it is read-only is irrelevant: suppose a user went to
select some data just as you'd moved the file? The user wouldn't have a clue
where to look... well, he would, because that's what the control file's job
is. But the control file is either pointing at the old destination, or the
new, or God knows what else.

You *have* to take the file offline before you can rename it. Read-only,
read-write. Zilch difference I'm afraid.

Regards
HJR

>
> "craig" <someone@nowhere.com> wrote in message
> news:gBaXb.72411$fD.38968@fed1read02...
> > I'm no pro to take it all with a grain of salt. As far as I know,
making
> a
> > tablespace read only just means no more transactions can be made against
> the
> > database.
> >
> > Existing transactions could still be pending against that Tablespace?
>
> No, because if there's a pending transaction, then you can't actually make
> it read-only. And if there are dirty buffers floating around from finished
> transactions, then those are flushed to disk before the thing becomes
> read-only (ie, making something read-only causes a checkpoint).
>

Thanks for the info, I am trying to put a lot of stuff together in my head
as a neophyte.

Not sure if I had the terminology all right but my meaning was that the
tablespace goes into a transitional read-only mode (or so my 9i docs say)
where pending transactions can still commit/rollback but no new ones can be
started. Else, it would be hard to go into read-only mode in a db with
activity. Guess thiss all happens before SQL returns the prompt to the user.
Not sure how it could apply to this case anyhow.

What's strange is his doc that says to do it with read only. It does seem
odd that there is no way to do this transition online. Guess in the world
of high end hardware striping/lvm/etc moving data files isn't common enough
to get Oracle's attention?

I guess the question is the affect of taking a highly utilized tablespace
down. If timed right it wouldn't be out of commision too long?

Comments inline.

"craig" <someone@nowhere.com> wrote in message
news:flcXb.72446$fD.52568@fed1read02...
>
> >
> > "craig" <someone@nowhere.com> wrote in message
> > news:gBaXb.72411$fD.38968@fed1read02...
> > > I'm no pro to take it all with a grain of salt. As far as I know,
> making
> > a
> > > tablespace read only just means no more transactions can be made
against
> > the
> > > database.
> > >
> > > Existing transactions could still be pending against that Tablespace?
> >
> > No, because if there's a pending transaction, then you can't actually
make
> > it read-only. And if there are dirty buffers floating around from
finished
> > transactions, then those are flushed to disk before the thing becomes
> > read-only (ie, making something read-only causes a checkpoint).
> >
>
> Thanks for the info, I am trying to put a lot of stuff together in my head
> as a neophyte.
>
> Not sure if I had the terminology all right but my meaning was that the
> tablespace goes into a transitional read-only mode (or so my 9i docs say)
> where pending transactions can still commit/rollback but no new ones can
be
> started.

I think you might be confusing making a tablespace read-only and taking a
rollback segment offline. It's the only thing I can think of. Rollback
segments definitely did go PENDING OFFLINE if you offlined them whilst they
were in use (and went fully offline when the transaction committed). But if
you try and make a tablespace read-only when there is a pending transaction,
the alter tablespace command simply hangs. Forever (or until the commit,
anyway).

>Else, it would be hard to go into read-only mode in a db with
> activity.

Yes, but think about it. You wouldn't *want* to make a heavily DML'd
tablespace read only, since it clearly isn't. Only a tablespace that is
extremely quiet, DML-wise, would be *worth* making read-only, and then it
wouldn't matter so much, would it?

>Guess thiss all happens before SQL returns the prompt to the user.
> Not sure how it could apply to this case anyhow.

No. The prompt for read-only doesn't return until it is genuinely read-only.
I think you're confusing it with something else.

> What's strange is his doc that says to do it with read only.

No, they don't actually. The documents say 'you can move a read-only
tablespace onto a read-only medium' -but that doesn't mean 'you can
*immediately and without prior action on your part* move a read-only
tablespace'. There was a mistaken logical leap from the words in the
documentation to the idea that you didn't have to bother with offlining the
thing first.

> It does seem
> odd that there is no way to do this transition online.

Why? This is maintenance. Maintenance is inevitably tricky when people are
actually using the thing you are trying to maintain. I can see it is an
inconvenience, but it is the nature of the beast, and that's why you have
maintenance windows.

> Guess in the world
> of high end hardware striping/lvm/etc moving data files isn't common
enough
> to get Oracle's attention?

Pretty accurate, that statement, I think.

> I guess the question is the affect of taking a highly utilized tablespace
> down. If timed right it wouldn't be out of commision too long?

Seconds. He can copy something which is read-only, at any time. He only has
to issue the 'alter tablespace X offline' and 'alter database rename 'x' to
'y'' commands when he's ready. And they will return practically instantly.

Regards
HJR
--
--------------------------------------------
Oracle Insights: www.dizwell.com
--------------------------------------------

"Howard J. Rogers" <hjr@dizwell.com> wrote in
news:402d55ab$0$5870$afc38c87@news.optusnet.com.au :

> No, they don't actually. The documents say 'you can move a read-only
> tablespace onto a read-only medium' -but that doesn't mean 'you can
> *immediately and without prior action on your part* move a read-only
> tablespace'. There was a mistaken logical leap from the words in the
> documentation to the idea that you didn't have to bother with
> offlining the thing first.

Actually the documentation makes that leap, completely bypassing the
step of offlining the tablespace (which is what I was trying to avoid).

From the 8i docs on ALTER TABLESPACE READ ONLY:

"Once a tablespace is read only, you can copy its files to read-only
media. You must then rename the datafiles in the control file to point to
the new location by using the SQL statement ALTER DATABASE ... RENAME. "

Then if you hit the link to the ALTER DATABASE, there is again no mention
needing to offline the tablespace.

"RENAME FILE
Use the RENAME FILE clause to rename datafiles, tempfiles, or redo log
file members. This clause renames only files in the control file. It does
not actually rename them on your operating system. You must specify each
filename using the conventions for filenames on your operating system
before specifying this clause. Do not use this clause when the database
is mounted. "

> Seconds. He can copy something which is read-only, at any time. He
> only has to issue the 'alter tablespace X offline' and 'alter database
> rename 'x' to 'y'' commands when he's ready. And they will return
> practically instantly.

Maybe even milliseconds, which is much better than the other method.
--
Chuck
Remove "_nospam" to reply by email