| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Moved Database need help
If the system tablespace is located in the same volumn name, then you can
try this procedure :
The following step is copied from Oracle administrator's guide :
You can rename datafiles to change either their names or locations. Oracle7 provides options to make the following changes:
Rename and relocate datafiles in a single offline tablespace (for example,
FILENAME1 and FILENAME2 in TBSPACE1) while the rest of the database is open.
Rename and relocate datafiles in several tablespaces simultaneously (for
example, FILE1 in TBSP1 and FILE2 in TBSP2) while the database is mounted
but closed.
Note: To rename or relocate datafiles of the SYSTEM tablespace, you must use
the second option, because you cannot take the SYSTEM tablespace offline.
Renaming and relocating datafiles with these procedures only change the
pointers to the datafiles, as recorded in the database's control file; it
does not physically rename any operating system files, nor does it copy
files at the operating system level. Therefore, renaming and relocating
datafiles involve several steps. Read the steps and examples carefully
before performing these procedures.
You must have the ALTER TABLESPACE system privilege to rename datafiles of a single tablespace.
Renaming and Relocating Datafiles for a Single Tablespace The following steps describe how to rename or relocate datafiles from a single tablespace.
To Rename or Relocate Datafiles for a Single Tablespace
ALTER TABLESPACE users
RENAME DATAFILE 'filename1', 'filename2'
TO 'filename3', 'filename4';
The new file must already exist; this command does not create a file. Also,
always provide complete filenames (including their paths) to properly
identify the old and new datafiles. In particular, specify the old filename
exactly as it appears in the DBA_DATA_FILE view of the data dictionary.
Renaming and Relocating Datafiles for Multiple Tablespaces
You can rename and relocate datafiles of one or more tablespaces with the
SQL command ALTER DATABASE with the RENAME FILE option. This option is the
only choice if you want to rename or relocate datafiles of several
tablespaces in one operation, or rename or relocate datafiles of the SYSTEM
tablespace. If the database must remain open, consider instead the procedure
outlined in the previous section.
To rename datafiles of several tablespaces in one operation or to rename
datafiles of the SYSTEM tablespace, you must have the ALTER DATABASE system
privilege.
To Rename and Relocate Datafiles for Multiple Tablespaces
ALTER DATABASE
RENAME FILE 'filename1', 'filename2'
TO 'filename3', 'filename4';
The new file must already exist; this command does not create a file. Also,
always provide complete filenames (including their paths) to properly
identify the old and new datafiles. In particular, specify the old filename
exactly as it appears in the DBA_DATA_FILE view of the data dictionary.
Relocating Datafiles: Example
For this example, assume the following conditions:
An open database has a tablespace named USERS that is comprised of datafiles
located on the same disk of a computer.
The datafiles of the USERS tablespace are to be relocated to a different
disk drive.
You are currently connected with administrator privileges to the open
database while using Server Manager.
To Relocate Datafiles
The following query of the data dictionary view DBA_DATA_FILES lists the datafile names and respective sizes (in bytes) of the USERS tablespace: SELECT file_name, bytes FROM sys.dba_data_files
WHERE tablespace_name = 'USERS';
FILE_NAME BYTES
FILENAME1 102400000 FILENAME2 102400000
Here, FILENAME1 and FILENAME2 are two fully specified filenames, each 1MB in size.
2. Back up the database.
Before making any structural changes to a database, such as renaming and relocating the datafiles of one or more tablespaces, always completely back up the database.
3. Take the tablespace containing the datafile offline, or shut down the database and restart and mount it, leaving it closed. Either option closes the datafiles of the tablespace.
4. Copy the datafiles to their new locations using operating system
commands. For this example, the existing files FILENAME1 and FILENAME2 are
copied to FILENAME3 and FILENAME4.
Suggestion: You can execute an operating system command to copy a file
without exiting Server Manager/LineMode by using the HOST command.
5. Rename the datafiles within Oracle.
The datafile pointers for the files that comprise the USERS tablespace, recorded in the control file of the associated database, must now be changed from FILENAME1 and FILENAME2 to FILENAME3 and FILENAME4, respectively.
If the tablespace is offline but the database is open, use the Server Manager Rename Datafiles dialog box or ALTER TABLESPACE...RENAME DATAFILE command. If the database is mounted but closed, use the ALTER DATABASE...RENAME FILE command.
6. Bring the tablespace online, or shut down and restart the database.
If the USERS tablespace is offline and the database is open, bring the tablespace back online. If the database is mounted but closed, open the database.
7. Back up the database. After making any structural changes to a database, always perform an immediate and complete backup.
 |
 |