Platform: Oracle 9.2.0.6 on Solaris 5.9

Hit a snag today on a box that holds two instances - one for dev, one
for QA for the same app. Started getting alerts that two of the file
systems had filled up. The SA had recently added a LUN to another disk
group, so we created a couple of 'overflow' file systems there. One of
the full FS's had normal data files, and I was able to relocate some of
them to these new file systems on the other disk group. The other
tablespace has only one of the control file mirrors, and the undo
tablespace. I'm a little antsy about relocating that one. The
procedure I used for the normal data files was:
1) offline the TS,
2) move the data file,
3) alter database rename file,
4) online the TS

Doesn't seem like this would work for the one and only undo tablespace.
Am thinking of treating it like the system TS ..

1) shutdown
2) move the data file
3) startup mount
4) alter database rename file
5) alter database open

Am I on track or getting ready to drive over a cliff?

Ed, 'i like the second method because it means I do not have to worry
about any dead sessions or uncommitted distributed transactions holding
undo entries and slowing things down. Kill everybody and roll back
with the shutdown immediate then change the location before anyone can
connect. Simple, clean, and reliable assuming you can type the file
name correctly on the rename.

I would copy rather than move the file. Then once the database is
successfully opened I would remove the old file. Might as well play it
extra safe.

You could also just create a new undo tablespace using the new file
name and switch over to using it. Once the undo retention period has
passed you could then drop the old undo tablespace. Being old
fashioned and wanting to get the job done and over with I would take
advantage of the window and do what you wanted.

HTH -- Mark D Powell --

I agree with Mark.

Shut it down, copy it, bring it back up mount, alter the database, open
the database, breath a sigh of relief then start
running your statspack to make sure the SA didn't create the new LUN in
some nasty RAID configuration and you aren't losing any performance.

On Tue, 07 Mar 2006 17:08:53 -0800, Mark D Powell wrote:

> I would copy rather than move the file. Then once the database is
> successfully opened I would remove the old file. Might as well play it
> extra safe.

Rename file will require both the old and the new one to be
present. If "mv" is used, he'll have to re-create control file.

--
http://www.mgogala.com

On Tue, 07 Mar 2006 19:16:27 -0800, Herod wrote:

> statspack to make sure the SA didn't create the new LUN in
> some nasty RAID configuration and you aren't losing any performance.

And how exactly will statspack show that?

--
http://www.mgogala.com

Mladen Gogala wrote:
> On Tue, 07 Mar 2006 17:08:53 -0800, Mark D Powell wrote:
>
> > I would copy rather than move the file. Then once the database is
> > successfully opened I would remove the old file. Might as well play it
> > extra safe.
>
> Rename file will require both the old and the new one to be
> present. If "mv" is used, he'll have to re-create control file.
>
> --
> http://www.mgogala.com

Mmm ... don't think so. My understanding is ALTER DATABASE RENAME ...
is simply making the necessary change in the control file -- while the
file is offline to the DB.. Doesn't matter if, at the OS level -- and
wile the file is offline to the DB --- you 'move' the file or 'copy'
the file.

Your kidding right?

That is the kind of thing statspack is exactly for.

And as long as the database is brought up properly, only one copy of
the file is correct - EdStevens is correct
as well as the rest of us that said to do that

Herod wrote:
> Your kidding right?
>
> That is the kind of thing statspack is exactly for.
>
> And as long as the database is brought up properly, only one copy of
> the file is correct - EdStevens is correct
> as well as the rest of us that said to do that

In Mladen's defense, I'm not sure I see how statspack will show
anything about where the SA placed a file system. What it will show is
poor performance. It can even point that the poor performance is due
to too many xxx_write wait events. That MAY (or may not) be due to the
configuration of the disk farm. I don't think statspack can
distinguish a db_file_parallel_write on a RAID-5 system from that on a
SAME system.

EdStevens wrote:
> Platform: Oracle 9.2.0.6 on Solaris 5.9
>
> Hit a snag today on a box that holds two instances - one for dev, one
> for QA for the same app. Started getting alerts that two of the file
> systems had filled up. The SA had recently added a LUN to another disk
> group, so we created a couple of 'overflow' file systems there. One of
> the full FS's had normal data files, and I was able to relocate some of
> them to these new file systems on the other disk group. The other
> tablespace has only one of the control file mirrors, and the undo
> tablespace. I'm a little antsy about relocating that one. The
> procedure I used for the normal data files was:
> 1) offline the TS,
> 2) move the data file,
> 3) alter database rename file,
> 4) online the TS
>
> Doesn't seem like this would work for the one and only undo tablespace.
> Am thinking of treating it like the system TS ..
>

> 1) shutdown
> 2) move the data file
> 3) startup mount
> 4) alter database rename file
> 5) alter database open
>
> Am I on track or getting ready to drive over a cliff?

It's nice to be able to sleep on a problem instead of grasping the
first solution that comes to mind.

Last night it struck me there was a simpler, and in my case better,
solution. The only files on this particular FS were the UNDO and a
control file copy from each of the two instances on the box. If I
moved the UNDO, the FS would be nearly empty and unused. The UNDO
isn't *that* much different from other table spaces .... why not just
resize the file in place?

ALTER DATABASE DATAFILE ... RESIZE ...

Poof! My FS usage went from 100% to 70%. The SA is happy, I'm happy,
my files remain in their 'standard' locations ...

You missed/misread the first post on it.

> statspack to make sure the SA didn't create the new LUN in
> some nasty RAID configuration and you aren't losing any performance.