Re: moving a datafile

From: Anne-Marie Ternes <anne-marie.ternes_at_cie.etat.lu>
Date: Tue, 08 Dec 1998 10:17:26 +0100
Message-ID: <366CEEA5.3A13B503@cie.etat.lu>

> Doug Cowles wrote:
>
> > Can anyone outline a procedure for moving a datafile
> > to a different filesystem without creating any problems?
> > I'm running out of room fast on a disk, and don't want to
> > interrupt anything.
> >
> > Thanks,
> > Dc.

Hi,

this is the procedure to move datafiles. 1) Shutdown the database (must be shut down "normal" or "immediate")

- svrmgrl
- SVRMGR> connect internal
- SVRMGR> shutdown
- SVRMGR> exit

2) Move the datafiles (cd, mv etc)
3) Create an SQL script file with as much "alter database" statements as you have to move datafiles:

    alter database rename file
    '/old_directory/datafile.dbf' to
    '/new_directory/datafile.dbf';
Call this script, for example, "move_datafiles.sql". This statement renames the file inside the database, so this has nothing to do with an operating system rename.
4) Execute script in Server Manager.

- svrmgrl
- SVRMGR> connect internal
- SVRMGR> startup mount <SID>
- SVRMGR> @move_datafiles.sql
- SVRMGR> alter database open ;

Attention: you must not exit the Server Manager between the "startup mount" and the "open"!

That's it!

The same procedure is to be used, if you choose to rename the file or the directory which the file is in. It is also the same procedure if you decide to move/rename the redo log files. Control files can be moved in the same manner, but there you must remember to edit the init<SID>.ora file "control_files" parameter.

I hope this can help you,

Anne-Marie Ternes Received on Tue Dec 08 1998 - 03:17:26 CST