How to Relocate and Rename SYSTEM datafiles (Doc ID 191540.1)

官方文档：

 
 
goal: How to Relocate and Rename SYSTEM datafiles
fact: Oracle Server - Enterprise Edition



fix:

Use the SQL statement ALTER DATABASE with the RENAME DATAFILE option


Renaming and Relocating Datafiles for Multiple Tablespaces
============================================================

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. 

1.Ensure that the database is mounted but closed. 

2.Copy the datafiles to be renamed to their new locations and new names, using 
operating system commands. 

3.Make sure the new copies of the datafiles have different fully specified 
filenames from the datafiles currently in use. 

4.Use the SQL statement ALTER DATABASE to rename the file pointers in the 
database's control file. 

5.Open the database. 


For example, the following statement renames the datafiles FILENAME 1 and 
FILENAME2 to FILENAME3 and FILENAME4, respectively: 

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_FILES view of the data dictionary. 


NOTE 
===== 

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.

AFTER making any structural changes to a database, always perform an immediate 
and complete backup.

示例：

SQL> select tablespace_name,file_name from dba_data_files;

TABLESPACE_NAME
------------------------------
FILE_NAME
--------------------------------------------------------------------------------
USERS
/u01/app/oracle/oradata/orcl/users01.dbf

UNDOTBS1
/u01/app/oracle/oradata/orcl/undotbs01.dbf

SYSAUX
/u01/app/oracle/oradata/orcl/sysaux01.dbf


TABLESPACE_NAME
------------------------------
FILE_NAME
--------------------------------------------------------------------------------
SYSTEM
/u01/app/oracle/oradata/orcl/system01.dbf

TBS_DATA
/u01/app/oracle/oradata/orcl/tbs_data01.dbf

UNDOTBS1
/u01/app/oracle/oradata/orcl/undotbs02.dbf


6 rows selected.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area  776646656 bytes
Fixed Size                  2257272 bytes
Variable Size             507514504 bytes
Database Buffers          264241152 bytes
Redo Buffers                2633728 bytes
Database mounted.

[oracle@orcl orcl]$ cp system01.dbf /u01/app/oracle/system01.dbf

SQL>alter database rename file '/u01/app/oracle/oradata/orcl/system01.dbf' to '/u01/app/oracle/system01.dbf';

Database altered.

SQL> alter database open;

Database altered.

SQL> select tablespace_name,file_name from dba_data_files where tablespace_name='SYSTEM';

TABLESPACE_NAME
------------------------------
FILE_NAME
--------------------------------------------------------------------------------
SYSTEM
/u01/app/oracle/system01.dbf