Subject: The New DBA FAQs, Part 6

From: Kevin Loney <"75663,327"@compuserve.com>
Newsgroups: comp.databases.oracle
Date: Tue, 19 Nov 1996 13:54:29 -0500

The New DBA FAQs

Part 6. How to Move Database Files

by Kevin Loney


Sixth in a series
*****

Modification History

07-FEB-96 Original post. Kevin Loney
19-NOV-96 Updated. Kevin Loney
*****
This article may not be published.
*****

References: ORACLE DBA Handbook, 7.3 Edition (Loney)
See www.osborne.com for scripts and sample chapters.
*****
The scripts shown here are V7-specific.
*****
The steps shown include important NOTES. Please read them prior to using the associated
commands.
*****
Once a file has been created in a database, it may be necessary to move it in order to better manage its
size or I/O requirements. In the following sections you'll see the procedures for moving datafiles,
online redo log files, and control files. In all of the procedures, operating system commands are used
to move the files; the Oracle commands serve primarily to reset the pointers to those files.

Moving Datafiles

There are two methods for moving datafiles: via the ALTER DATABASE command, and via the
ALTER TABLESPACE command. The ALTER TABLESPACE method only applies to datafiles
whose tablespaces do not include SYSTEM, rollback segments, or temporary segments. The ALTER
DATABASE method will work for all datafiles.

The ALTER DATABASE Method

When using the ALTER DATABASE method to move datafiles, the datafile is moved after the
instance has been shut down. The steps involved, detailed in the following sections, are:

1. Shutdown the instance, using SQLDBA or Server Manager.
2. Use operating system commands to move the datafile.
3. Mount the database and use the ALTER DATABASE to rename the file within the database.
4. Start the instance, using SQLDBA or Server Manager.

In the following examples, the Oracle7 order of commands is shown.

Step 1. Shutdown the instance, using SQLDBA or Server Manager SQLDBA version:

$ sqldba lmode=y
SQLDBA> connect internal;
SQLDBA> shutdown;
SQLDBA> exit;

Server Manager version:

$ svrmgrl
SVRMGR> connect internal;
SVRMGR> shutdown;
SVRMGR> exit;

Step 2. Use operating system commands to move the datafile. Use an operating system command to move the datafile. In UNIX, the 'mv' command moves files to
new locations. The following example shows the 'data01.dbf' file being moved from the device
named '/db01' to one named '/db02'.

$ mv /db01/oracle/CC1/data01.dbf /db02/oracle/CC1

The file name must fully specify a file name using the conventions of your operating system.

Step 3. Mount the database and use the ALTER DATABASE to rename the file within the database. In the following example, the CC1 instance is started and the 'data01.dbf' datafile moved in Step 2 is
renamed within the database. The database will then be able to find that file during instance startup.
The ALTER DATABASE command shown here does not rename the file; the file must have already
been renamed or moved. If you are using Server Manager, replace the sqldba lmode=y command
with svrmgrl.

$ sqldba lmode=y
SQLDBA> connect internal;
SQLDBA> STARTUP MOUNT CC1;
SQLDBA> ALTER DATABASE RENAME FILE
2> '/db01/oracle/CC1/data01.dbf' TO
3> '/db02/oracle/CC1/data01.dbf';

Do not disconnect after this step is complete; stay logged in to the database and proceed to Step 4.

When the ALTER DATABASE command is executed, Oracle will check to see if the name you are
naming the file 'to' exists. If this step fails, check the accuracy of the destination file name.

Step 4. Start the instance. Now that the database knows how to find the moved file, the instance can start. The following
command should be executed within either SQLDBA or Server Manager.

SQLDBA> alter database open;

The instance will now be opened, using the new location for the datafile that was moved.

The ALTER TABLESPACE Method
When using the ALTER TABLESPACE method to move datafiles, the datafile is moved while the
instance is still running. The steps involved, detailed in the following sections, are:

1. Take the tablespace offline.
2. Use operating system commands to move the file.
3. Use the ALTER TABLESPACE command to rename the file within the database.
4. Bring the tablespace back online.

NOTE: This method can only be used for non-SYSTEM tablespaces. It cannot be used for
tablespaces that contain active rollback segments or temporary segments.

In the following examples, the Oracle7 order of commands is shown.

Take the tablespace offline. Use the ALTER TABLESPACE command to put the tablespace into OFFLINE state, as shown in
the following example. This command is executed while the instance is running. It cannot be used
for the SYSTEM tablespace or for tablespace containing active rollback segments or temporary
segments. If you are using Server Manager, replace the sqldba lmode=y command with svrmgrl to
enter Server Manager's line mode interface.

$ sqldba lmode=y
SQLDBA> connect internal;
SQLDBA> ALTER TABLESPACE data OFFLINE;
SQLDBA> exit;

Use operating system commands to move the file. Use an operating system command to move the datafile. In UNIX, the 'mv' command moves files to
new locations. The following example shows the 'data01.dbf' file being moved from the device
named '/db01' to one named '/db02'.

$ mv /db01/oracle/CC1/data01.dbf /db02/oracle/CC1

The file name must fully specify a file name using the conventions of your operating system.

Use the ALTER TABLESPACE command to rename the file within the database. In the following example, the 'data01.dbf' datafile moved in Step 2 is renamed within the database.
The database will then be able to access that file. The ALTER TABLESPACE command shown here
does not rename the file; the file must have already been renamed or moved. If you are using Server
Manager, replace the sqldba lmode=y command with svrmgrl to enter Server Manager's line mode
interface.

$ sqldba lmode=y
SQLDBA> connect internal;
SQLDBA> ALTER TABLESPACE data RENAME DATAFILE
2> '/db01/oracle/CC1/data01.dbf' TO
3> '/db02/oracle/CC1/data01.dbf';

Do not disconnect after this step is complete; stay logged in to the database and proceed to Step 4.

When the ALTER TABLESPACE command is executed, Oracle will check to see if the name you are
naming the file 'to' exists. If this step fails, check the accuracy of the destination file name.

Bring the tablespace back online. Use the ALTER TABLESPACE command to bring the tablespace back online, from within either
SQLDBA or Server Manager.

SQLDBA> ALTER TABLESPACE data ONLINE;

The DATA tablespace will then be brought back online, using the new location for the datafile.

Moving Online Redo Log Files


Online redo log files can be moved while the database is shut down, and renamed to within the
database via the ALTER DATABASE command. The procedures for moving online redo log files are
very similar to those used to move datafiles via the ALTER DATABASE command.

First, the database is shut down and the online redo log file is moved. The database is then mounted
and the ALTER DATABASE command is used to tell the database the new location of the online redo
log file. The instance can then be opened, using the online redo log file in its new location. In the
following examples, the Oracle7 order of commands is shown.

Step 1. Shutdown the instance.
SQLDBA version:

$ sqldba lmode=y
SQLDBA> connect internal;
SQLDBA> shutdown;
SQLDBA> exit;

Server Manager version:

$ svrmgrl
SVRMGRL> connect internal;
SVRMGRL> shutdown;
SVRMGRL> exit;


Step 2. Move the online redo log file.
Use an operating system command to move the file. In UNIX, the 'mv' command moves files to new
locations. The following example shows the 'redo01CC1.dbf' file being moved from the device
named '/db05' to one named '/db02'.

$ mv /db05/oracle/CC1/redo01CC1.dbf /db02/oracle/CC1

The file name must fully specify a file name using the conventions of your operating system.

Step 3. Mount the database and use the ALTER DATABASE to rename the file within the database.
In the following example, the CC1 instance is started and the 'redo01CC1.dbf' file moved in Step 2 is
renamed within the database. The database will then be able to find that file during instance startup.
The ALTER DATABASE command shown here does not rename the file; the file must have already
been renamed or moved. If you are using Server Manager, replace the sqldba lmode=y command
with svrmgrl to enter Server Manager's line mode interface.

$ sqldba lmode=y
SQLDBA> connect internal;
SQLDBA> STARTUP MOUNT CC1;
SQLDBA> ALTER DATABASE RENAME FILE
2> '/db05/oracle/CC1/redo01CC1.dbf' TO
3> '/db02/oracle/CC1/redo01CC1.dbf';

Do not disconnect after this step is complete; stay logged in to the database and proceed to Step 4.

When the ALTER DATABASE command is executed, Oracle will check to see if the name you are
naming the file 'to' exists. If this step fails, check the accuracy of the destination file name.

Step 4. Start the instance.
Now that the database knows how to find the moved file, the instance can start. The following
command can be executed from within either Server Manager or SQLDBA.

SQLDBA> alter database open;

The instance will now be opened, using the new location for the online redo log file that was moved.

Moving Control Files


The location of control files is specified in the INIT.ORA or CONFIG.ORA file for the instance. In
Oracle7, the CONFIG.ORA file usually maintains this information. To move a control file, you must
shut down the instance, move the file, edit the CONFIG.ORA file, and then restart the instance.

Step 1. Shutdown the instance.
SQLDBA version:

$ sqldba lmode=y
SQLDBA> connect internal;
SQLDBA> shutdown;
SQLDBA> exit;

Server Manager version:

$ svrmgrl
SVRMGRL> connect internal;
SVRMGRL> shutdown;
SVRMGRL> exit;


Step 2. Move the control file.
Use an operating system command to move the file. In UNIX, the 'mv' command moves files to new
locations. The following example shows the 'ctrl1CC1.ctl' file being moved from the device named
'/db05' to one named '/db02'.

$ mv /db05/oracle/CC1/ctrl1CC1.ctl /db02/oracle/CC1

The file name must fully specify a file name using the conventions of your operating system.

Step 3. Edit the CONFIG.ORA file.
The CONFIG.ORA file for an instance is usually located in the /dbs subdirectory under the Oracle
software home directory (in 7.3, this changes slightly; in the /dbs subdirectory, you will find a link to
the file's new home). Its name typically includes the name of the instance - for the CC1 instance, the
CONFIG.ORA file may be named configCC1.ora. The exact name and location of the CONFIG.ORA
file is specified in the INIT.ORA file for the instance (usually located in the same directory, with the
same naming convention applied).

Within the CONFIG.ORA file, there will be an entry for the "control_files" parameter; and example is
shown in the following listing.

control_files = (/db01/oracle/CC1/ctrl1CC1.ctl,
/db03/oracle/CC1/ctrl1CC1.ctl,
/db05/oracle/CC1/ctrl1CC1.ctl)

Edit this entry to reflect the change to the file you moved in Step 2.

control_files = (/db01/oracle/CC1/ctrl1CC1.ctl,
/db03/oracle/CC1/ctrl1CC1.ctl,
/db02/oracle/CC1/ctrl1CC1.ctl)

Step 4. Start the instance.
SQLDBA version:

$ sqldba lmode=y
SQLDBA> connect internal;
SQLDBA> startup;
SQLDBA> exit;

Server Manager version:

$ svrmgrl
SVRMGRL> connect internal;
SVRMGRL> startup;
SVRMGRL> exit;

The instance will then be started, using the control file in its new location.
=======
Kevin Loney is the author of the ORACLE DBA Handbook, 7.3 Edition, coauthor of ORACLE: The
Complete Reference, Electronic Edition, and coauthor of Advanced ORACLE Tuning and
Administration, all published under Osborne/McGraw-Hill's Oracle Press imprint. See
www.osborne.com for scripts and sample chapters.

[Top | Back | Home ]