Re: URGENT: Need to move TS from ufs to vxfs file system

Both ways are perfectly correct, of course. The 'shutdown ...alter database' technique was specifically designed for the SYSTEM tablespace, because (as you pointed out) you can't offline that particular tablespace. But it can apply to any datafile or redo log at any time if you don't mind the downtime.

The 'alter tablespace offline' technique obviously only works for data files, not redo logs. And it was designed to allow you to move data files without the enormous expense of having the entire database out of action for the duration. But if you don't mind the down time, it's not compulsory.

In either case, all that is happening is that you are re-directing the pointers in the controlfile to the new location. It matters not a jot how you achieve that, so long as you achieve it.

So yes, he could have done it more 'efficiently' by not downing the entire database. But no, he was under no obligation to do so, and the method he actually used should be just as effective as the one he could have used.

So why does he get the hangs on selects, which is the real issue, not the perfection of technique? Well, my guess is that file permissions might be at fault, but maybe not because the database opened OK, and I'd expect file permission problems to prevent that. The other guess i have is that the copying of the datafile introduced corruption into the file, and the select just happens to be encountering it. I'd run dbv against the new file to be sure. But since everything works fine when he copies the files back to the original locations, I doubt that this is the issue either.

I've meant 'rename file' problems when what you type in as part of the rename command is not *exactly* what the Control File thinks it should be: I've gotten into the habit of doing a select name from v$datafile before attempting a move, and using precisely what's displayed there as the source filename.

Otherwise, I'd suspect the Veritas file system is not set up correctly. But we're into hardware issues there, and I can't comment meaningfully on those.

So: note to original poster. Ignore criticism of the technique you used. The problem lies elsewhere. But I can't be sure where. I'd suggest you create a very small (10Mb?) tablespace on the original file system, create a pathetic test table in that, and then try and move it to the Veritas file system. See what happens then. If a select * from pathetic works, then we exclude hardware/O/s setup issues for Veritas. If it doesn't, we know it's specific to that datafile, and we can think again.

Regards
HJR

--
-----------------------------------------------
Resources for Oracle : http://www.hjrdba.com
===============================

"koert54" <koert54_at_nospam.com> wrote in message
news:%A3r8.52112$DE4.6755_at_afrodite.telenet-ops.be...


> Nice ...

> So what does your DBA book say about moving datafiles from the system

> tablespace ? (which btw can't be taken offline when the database is open)

> 1. shutdown the db

> 2. startup mount (this does not open the datafiles, but opens init.ora

> (nomount) & controlfiles (mount)

> 3. move the file (s)

> 4. alter database rename file 'xxxxx' to 'yyyyy' ; (this updates the

> controlfile)

> 5. alter database open (open the datafiles)

> 6. Bada Bing

>

> notice the keyword 'file' in the 'alter database rename' command - so you

> can use it for either datafiles or redolog files ...

>

> So basically his procedure is correct (except for the 'alter files'

command


> ...) ...

>

> I can hardly believe that the book doesn't mention this as this is pretty

> basic stuff ...

>

> Kind regards

> Koert

>

> "damorgan" <damorgan_at_exesolutions.com> wrote in message

> news:3CACC182.BC16EA61_at_exesolutions.com...

> > According to the DBA handbook the steps are:

> >

> > 1. Do not shut down the database

> > 2. Take the tablespace off-line

> > 3. Move the datafile(s)

> > 4. Use the ALTER TABLESPACE command to rename the file within the

database


> >

> > 5. Bring the tablespace back on-line.

> >

> > This does not correspond with what you did.

> >

> > Reference: Oracle 8i DBA Handbook, Loney & Theriault, pg 138.

> >

> > Daniel Morgan

> >

> >

> >

> > Larry wrote:

> >

> > > I really need help!  FAST!

> > >

> > > I have some large tablespaces I need to move from a ufs file system to

> > > a new-and-improved veritas file system.  We are out of space and I

> > > have to get this done.  I followed the instructions to do this task...

> > >

> > > shutdown

> > > copy dbf files to new location (I didn't move the files...)

> > > startup mount

> > > alter files '/v01/...' to '/v05/..'

> > > alter database open

> > >

> > > Problem is, when I run a query against a table that is in the

> > > relocated TS, the query just hangs.  If I shutdown and put everything

> > > back the

> > > way it was, the query works fine.

> > >

> > > I am running Oracle 8.1.7.

> > >

> > > Any thoughts?

> >

>

>