Re: moving datafiles

Comments inline.

"craig" <someone_at_nowhere.com> wrote in message news:flcXb.72446$fD.52568_at_fed1read02...
>
> >
> > "craig" <someone_at_nowhere.com> wrote in message
> > news:gBaXb.72411$fD.38968_at_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
--------------------------------------------