Dbabc.Net

Oracle 重命名数据文件
Oracle 重命名数据文件的两种方法：ALTER TABLESPACE RENAME DATAFILE和ALTER DATABASE RENAME FILE
语法

ALTER DATABASE RENAME FILE 'old_name' to 'new_name'
ALTER TABLESPACE tablespace_name RENAME DATAFILE 'old_name'  TO 'new_name'

通过这两种方法重命名数据文件必须保证目标文件存在(The operating system files continue to exist),数据库在open状态下重命名数据文件必须保证要重命名的数据文件所在的表空间处于offline的状态，由于在open状态下system和sysaux表空间不能够被offline，所以在open状态只能重命名除去system和sysaux之外的数据文件。
ALTER DATABASE RENAME FILE 不仅仅可以重命名数据文件，同样可以重命名tempfiles, or redo log file
重命名数据文件示例如下:

SQL> select file_name,tablespace_name,status from dba_data_files;

FILE_NAME                                                      TABLESPACE_NAME                STATUS
-------------------------------------------------------------- ------------------------------ ---------
/u01/dbabc.net/pass/users01.dbf                          USERS                          AVAILABLE
/u01/dbabc.net/pass/sysaux01.dbf                         SYSAUX                         AVAILABLE
/u01/dbabc.net/pass/undotbs01.dbf                        UNDOTBS1                       AVAILABLE
/u01/dbabc.net/pass/system01.dbf                         SYSTEM                         AVAILABLE
/u01/dbabc.net/pass/example01.dbf                        EXAMPLE                        AVAILABLE
/dbabc.net/pass/passt/omf/PASS/datafile/o1_mf_passt_6dlc02ws_.dbf   PASST                          AVAILABLE
/dbabc.net/pass/passt/omf/PASS/datafile/o1_mf_tp_6dlc7pd7_.dbf      TP                             AVAILABLE
/dbabc.net/pass/passt/omf/PASS/datafile/o1_mf_users1_6dlccg3l_.dbf  USERS1                         AVAILABLE
/u01/dbabc.net/dbs/hr_tbs01.dbf                              HR_TBS                         AVAILABLE
/u01/dbabc.net/dbs/testdefault01.dbf                         TEST_DEFAULT                   AVAILABLE

10 rows selected

SQL>

重命名PASST tablespace下的 /dbabc.net/pass/passt/omf/PASS/datafile/o1_mf_passt_6dlc02ws_.dbf的数据文件

1、首先将PASST tablespace offline

SQL> alter tablespace passt offline;

Tablespace altered

2、重命名数据文件(OS)

[dbabc.net]cd /dbabc.net/pass/passt/omf/PASS/datafile
[dbabc.net]mv o1_mf_passt_6dlc02ws_.dbf /dbabc.net/pass/passt_01.dbf
[dbabc.net]cd /dbabc.net/pass/
[dbabc.net]ll passt_*
-rw-r----- 1 oracle oinstall 104865792 Nov  5 08:38 passt_01.dbf

3、ALTER TABLESPACE tablespace_name RENAME ‘old_name’ to ‘new_name’

SQL> ALTER TABLESPACE passt
  2          RENAME DATAFILE '/dbabc.net/pass/passt/omf/PASS/datafile/o1_mf_passt_6dlc02ws_.dbf'
  3           TO '/dbabc.net/pass/passt_01.dbf';

Tablespace altered

4、将表空间online

SQL> alter tablespace passt online;

Tablespace altered

5、确认一下

SQL> select file_name,tablespace_name,status from dba_data_files;

FILE_NAME                                                      TABLESPACE_NAME                STATUS
-------------------------------------------------------------- ------------------------------ ---------
/u01/dbabc.net/pass/users01.dbf                          USERS                          AVAILABLE
/u01/dbabc.net/pass/sysaux01.dbf                         SYSAUX                         AVAILABLE
/u01/dbabc.net/pass/undotbs01.dbf                        UNDOTBS1                       AVAILABLE
/u01/dbabc.net/pass/system01.dbf                         SYSTEM                         AVAILABLE
/u01/dbabc.net/pass/example01.dbf                        EXAMPLE                        AVAILABLE
/dbabc.net/pass/passt_01.dbf                                        PASST                          AVAILABLE
/dbabc.net/pass/passt/omf/PASS/datafile/o1_mf_tp_6dlc7pd7_.dbf      TP                             AVAILABLE
/dbabc.net/pass/passt/omf/PASS/datafile/o1_mf_users1_6dlccg3l_.dbf  USERS1                         AVAILABLE
/u01/dbabc.net/dbs/hr_tbs01.dbf                              HR_TBS                         AVAILABLE
/u01/dbabc.net/dbs/testdefault01.dbf                         TEST_DEFAULT                   AVAILABLE

10 rows selected

SQL>

以上是通过ALTER TABLESPACE RENAME的用法。下面示例 ALTER DATABASE 重命名的用法。
以TP tablespace为例

1、offline tablespace TP

SQL> alter tablespace tp offline;

Tablespace altered

2、mv os file of TP tablespace

[dbabc.net]mv /dbabc.net/pass/passt/omf/PASS/datafile/o1_mf_tp_6dlc7pd7_.dbf /dbabc.net/pass/tp_01.dbf

3、ALTER DATABASE RENAME FILE

SQL> ALTER DATABASE RENAME FILE '/dbabc.net/pass/passt/omf/PASS/datafile/o1_mf_tp_6dlc7pd7_.dbf' to '/dbabc.net/pass/tp_01.dbf';

Database altered

4、online tablespace TP

SQL> alter tablespace tp online;

Tablespace altered

5、check

SQL> select file_name,tablespace_name,status from dba_data_files;

FILE_NAME                                                      TABLESPACE_NAME                STATUS
-------------------------------------------------------------- ------------------------------ ---------
/u01/dbabc.net/pass/users01.dbf                          USERS                          AVAILABLE
/u01/dbabc.net/pass/sysaux01.dbf                         SYSAUX                         AVAILABLE
/u01/dbabc.net/pass/undotbs01.dbf                        UNDOTBS1                       AVAILABLE
/u01/dbabc.net/pass/system01.dbf                         SYSTEM                         AVAILABLE
/u01/dbabc.net/pass/example01.dbf                        EXAMPLE                        AVAILABLE
/dbabc.net/pass/passt_01.dbf                                        PASST                          AVAILABLE
/dbabc.net/pass/tp_01.dbf                                           TP                             AVAILABLE
/dbabc.net/pass/passt/omf/PASS/datafile/o1_mf_users1_6dlccg3l_.dbf  USERS1                         AVAILABLE
/u01/dbabc.net/dbs/hr_tbs01.dbf                              HR_TBS                         AVAILABLE
/u01/dbabc.net/dbs/testdefault01.dbf                         TEST_DEFAULT                   AVAILABLE
10 rows selected

SQL>

注：通过 ALTER DATABASE RENAME FILE 在重命名redo file的时候不需要offline 直接执行就ok了。

SQL> alter database rename file '/u01/dbabc.net/pass/redo03.log' to '/u01/dbabc.net/pass/redo03_mv.log';

Database altered

ALTER DATABASE RENAME FILE Clause
参考：http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_1004.htm#i2082829

Use the RENAME FILE clause to rename datafiles, tempfiles, or redo log file members. You must create each filename using the conventions for filenames on your operating system before specifying this clause.

*To use this clause for a datafile or tempfile, the database must be mounted. The database can also be open, but the datafile or tempfile being renamed must be offline. In addition, a file with the new name must exist on the system.
*To use this clause for logfiles, the database must be mounted but not open.
*If you have enabled block change tracking, you can use this clause to rename the block change tracking file. The database must be mounted but not open when you rename the block change tracking file.

This clause renames only files in the control file. It does not actually rename them on your operating system. The operating system files continue to exist, but Oracle Database no longer uses them.

Renaming a Log File Member: Example The following statement renames a redo log file member:

ALTER DATABASE
RENAME FILE ‘diskc:log3.log’ TO ‘diskb:log3.log’;

Manipulating Tempfiles: Example The following takes offline the tempfile temp02.dbf created in Adding and Dropping Datafiles and Tempfiles: Examples and then renames the tempfile:

ALTER DATABASE TEMPFILE ‘temp02.dbf’ OFFLINE;

ALTER DATABASE RENAME FILE ‘temp02.dbf’ TO ‘temp03.dbf’;

The statement renaming the tempfile requires that you first create the file temp03.dbf on the operating system.

ALTER TABLESPACE RENAME DATAFILE
参考
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_3002.htm

Moving and Renaming Tablespaces: Example This example moves and renames a datafile associated with the tbs_02 tablespace, created in “Enabling Autoextend for a Tablespace: Example”, from diskb:tbs_f5.dat to diska:tbs_f5.dat:

1.Take the tablespace offline using an ALTER TABLESPACE statement with the OFFLINE clause:
ALTER TABLESPACE tbs_02 OFFLINE NORMAL;
2.Copy the file from diskb:tbs_f5.dat to diska:tbs_f5.dat using your operating system commands.
3.Rename the datafile using an ALTER TABLESPACE statement with the RENAME DATAFILE clause:
ALTER TABLESPACE tbs_02
RENAME DATAFILE ‘diskb:tbs_f5.dat’
TO ‘diska:tbs_f5.dat’;
4.Bring the tablespace back online using an ALTER TABLESPACE statement with the ONLINE clause:
ALTER TABLESPACE tbs_02 ONLINE;