moving datafiles

[Chuck]

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'

[craig]

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.

Post by Chuck
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
*
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

[Howard J. Rogers]

Post by craig
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).

Post by craig
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.

Post by craig
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.

Post by craig
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]

Post by Howard J. Rogers

Post by craig
I'm no pro to take it all with a grain of salt. As far as I know,

making

Post by Howard J. Rogers
a

Post by craig
tablespace read only just means no more transactions can be made against

the

Post by craig
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?

[Howard J. Rogers]

Comments inline.

Post by craig

Post by Howard J. Rogers

Post by craig
I'm no pro to take it all with a grain of salt. As far as I know,

making

Post by Howard J. Rogers
a

Post by craig
tablespace read only just means no more transactions can be made against

the

Post by craig
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).

Post by craig
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?

Post by craig
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.

Post by craig
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.

Post by craig
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.

Post by craig
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.

Post by craig
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

[Chuck]

Post by Howard J. Rogers
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. "

Post by Howard J. Rogers
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.