|
FAQ Part 1 | FAQ Part 2 | FAQ Part 3 | FAQ Part 4 | FAQ Part 5 | FAQ Part 6
Modification History
07-FEB-96 Original post. 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:
In the following examples, the Oracle7 order of commands is shown. Step 1. Shutdown the instance, using SQLDBA or Server Manager Server Manager version: 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. 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:
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. 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. 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. Server Manager version: 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. 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. Server Manager version: 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. Edit this entry to reflect the change to the file you moved in Step 2. Step 4. Start the instance. SQLDBA version: Server Manager version: 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.
FAQ Part 1 | FAQ Part 2 | FAQ Part 3 | FAQ Part 4 | FAQ Part 5 | FAQ Part 6
|