简介
数据文件查询：
代码语言：javascript复制col name format a60
set pagesize 9999
select ts#,file#,name,status from v$datafile d where d.status!='ONLINE' ;
select * from v$tablespace;
需要注意的是，对于SYSTEM、SYSAUX和UNDO表空间的数据文件的移动或重命名，强烈建议关闭数据库进行操作，否则可能会引起意外的错误，甚至宕机。
方法1：利用OS拷贝
关闭数据库，利用OS拷贝，该方法适用于任何类型的文件，步骤如下所示：
代码语言：javascript复制A、SHUTDOWN IMMEDIATE关闭数据库
B、在OS下拷贝数据文件到新的地点
C、STARTUP MOUNT启动数据库到MOUNT状态下
D、ALTER DATABASE RENAME FILE '老文件' TO '新文件';
E、ALTER DATABASE OPEN;打开数据库
其实利用OS拷贝也可以联机操作，不关闭数据库，但是只针对可以OFFLINE的数据文件，步骤如下所示：
代码语言：javascript复制① alter database datafile '/home/oracle/ocplhr1_test.dbf' offline;
② recover datafile '/home/oracle/ocplhr1_test.dbf' ;
③ ! cp /home/oracle/ocplhr1_test.dbf /u01/app/oracle/oradata/OCPLHR1/ocplhr1_test01.dbf
④ ALTER DATABASE RENAME FILE '/home/oracle/ocplhr1_test.dbf' TO '/u01/app/oracle/oradata/OCPLHR1/ocplhr1_test01.dbf';
⑤ alter database datafile '/u01/app/oracle/oradata/OCPLHR1/ocplhr1_test01.dbf' online;
方法2：利用RMAN联机操作
代码语言：javascript复制RMAN> sql "alter database datafile ''file name'' offline";
RMAN> recover datafile 文件号;
RMAN> run {
2> copy datafile 'old file location' to 'new file location';
3> switch datafile ' old file location' to datafilecopy ' new file location';
4> }
RMAN> sql "alter database datafile ''file name'' online";
-- 或者使用文件号
RMAN> sql "alter database datafile 4 online";
利用RMAN与利用OS拷贝的原理一样。在RMAN中，COPY命令是拷贝数据文件，相当于OS的cp命令，而SWITCH则相当于ALTER DATABASE RENAME用来更新控制文件。
示例：
代码语言：javascript复制SYS@LHR11G> col name format a60
SYS@LHR11G> set pagesize 9999
SYS@LHR11G> select ts#,file#,name,status from v$datafile d where d.status!='ONLINE' ;
TS# FILE# NAME STATUS
---------- ---------- ------------------------------------------------------------ --------------
0 1 /u01/app/oracle/oradata/LHR11G/system01.dbf SYSTEM
4 4 /u01/app/oracle/oradata/LHR11G/users01.dbf RECOVER
RMAN> sql "alter database datafile 4 offline";
sql statement: alter database datafile 4 offline
RMAN> recover datafile 4;
Starting recover at 2022-11-14 16:06:56
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 2022-11-14 16:06:56
RMAN> run {
2> copy datafile '/u01/app/oracle/oradata/LHR11G/users01.dbf' to '/u01/app/oracle/oradata/LHR11G/users01_test.dbf';
3> switch datafile '/u01/app/oracle/oradata/LHR11G/users01.dbf' to datafilecopy '/u01/app/oracle/oradata/LHR11G/users01_test.dbf';
4> }
Starting backup at 2022-11-14 16:09:24
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/u01/app/oracle/oradata/LHR11G/users01.dbf
output file name=/u01/app/oracle/oradata/LHR11G/users01_test.dbf tag=TAG20221114T160924 RECID=2 STAMP=1120752565
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 2022-11-14 16:09:25
datafile 4 switched to datafile copy
input datafile copy RECID=2 STAMP=1120752565 file name=/u01/app/oracle/oradata/LHR11G/users01_test.dbf
RMAN> sql "alter database datafile 4 online";
sql statement: alter database datafile 4 online
SYS@LHR11G> select ts#,file#,name,status from v$datafile d ;
TS# FILE# NAME STATUS
---------- ---------- ------------------------------------------------------------ --------------
0 1 /u01/app/oracle/oradata/LHR11G/system01.dbf SYSTEM
1 2 /u01/app/oracle/oradata/LHR11G/sysaux01.dbf ONLINE
2 3 /u01/app/oracle/oradata/LHR11G/undotbs01.dbf ONLINE
4 4 /u01/app/oracle/oradata/LHR11G/users01_test.dbf OFFLINE
6 5 /u01/app/oracle/oradata/LHR11G/example01.dbf ONLINE
SYS@LHR11G> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@test ~]$ ll /u01/app/oracle/oradata/LHR11G/
total 2601172
-rw-r----- 1 oracle oinstall 9846784 Nov 14 16:09 control01.ctl
-rw-r----- 1 oracle oinstall 363077632 Nov 14 13:41 example01.dbf
-rw-r----- 1 oracle oinstall 52429312 Nov 14 16:09 redo01.log
-rw-r----- 1 oracle oinstall 52429312 Nov 14 06:06 redo02.log
-rw-r----- 1 oracle oinstall 52429312 Nov 14 13:36 redo03.log
-rw-r----- 1 oracle oinstall 859840512 Nov 14 16:09 sysaux01.dbf
-rw-r----- 1 oracle oinstall 817897472 Nov 14 16:09 system01.dbf
-rw-r----- 1 oracle oinstall 54534144 Nov 14 12:18 temp01.dbf
-rw-r----- 1 oracle oinstall 382738432 Nov 14 16:09 undotbs01.dbf
-rw-r----- 1 oracle oinstall 9183232 Nov 14 16:06 users01.dbf
-rw-r----- 1 oracle oinstall 9183232 Nov 14 16:09 users01_test.dbf
方法3：利用ALTER TABLESPACE操作
ALTER TABLESPACE只能用于下面情况下的数据文件：不是SYSTEM表空间，不包含激活的回滚段，还有临时段，但是用ALTER TABLESPACE可以在实例启动的时候来执行，而ALTER DATABASE则适应于任何的数据文件，对于不能执行OFFLINE操作的数据文件，则此时数据库要在MOUNT状态下；而对于可以执行OFFLINE操作的数据文件，则数据库可以在OPEN状态下。
ALTER TABLESPACE方法步骤：
代码语言：javascript复制① OFFLINE相应的表空间：ALTER TABLESPACE TS_LHRDATA OFFLINE;
② 用操作系统命令重命名或者移动数据文件
③ 用ALTER TABLESPACE命令来重命名数据库中的文件：ALTER TABLESPACE TS_LHRDATA RENAME DATAFILE '/u01/lhrdb/data01.dbf' TO '/u02/lhrdb/data01_new.dbf';
④ ONLINE表空间：ALTER TABLESPACE TS_LHRDATA ONLINE;
示例：
代码语言：javascript复制SYS@LHR11G> alter tablespace users offline;
Tablespace altered.
SYS@LHR11G> ! cp /u01/app/oracle/oradata/LHR11G/users01_test.dbf /u01/app/oracle/oradata/LHR11G/users01_test2.dbf
SYS@LHR11G> ALTER TABLESPACE users RENAME DATAFILE '/u01/app/oracle/oradata/LHR11G/users01_test.dbf' TO '/u01/app/oracle/oradata/LHR11G/users01_test2.dbf';
Tablespace altered.
SYS@LHR11G> ALTER TABLESPACE users ONLINE;
Tablespace altered.
SYS@LHR11G> select ts#,file#,name,status from v$datafile d ;
TS# FILE# NAME STATUS
---------- ---------- ------------------------------------------------------------ --------------
0 1 /u01/app/oracle/oradata/LHR11G/system01.dbf SYSTEM
1 2 /u01/app/oracle/oradata/LHR11G/sysaux01.dbf ONLINE
2 3 /u01/app/oracle/oradata/LHR11G/undotbs01.dbf ONLINE
4 4 /u01/app/oracle/oradata/LHR11G/users01_test2.dbf ONLINE
6 5 /u01/app/oracle/oradata/LHR11G/example01.dbf ONLINE
SYS@LHR11G>
方法4：12c新特性
不同于以往的版本，在Oracle数据库12c R1版本中对数据文件的迁移或重命名不再需要太多繁琐的步骤。在12c R1中，可以使用ALTER DATABASE MOVE DATAFILE这样的SQL语句对数据文件进行在线重命名和移动。而当此数据文件正在传输时，终端用户可以执行查询，DML以及DDL方面的任务。另外，数据文件可以在存储设备间迁移，如从非ASM迁移至ASM，反之亦然。
重命名数据文件：
SQL> ALTER DATABASE MOVE DATAFILE '/u00/data/users01.dbf' TO '/u00/data/users_01.dbf';
从非ASM迁移数据文件至ASM：
代码语言：javascript复制SQL> ALTER DATABASE MOVE DATAFILE '/u00/data/users_01.dbf' TO ' DG_DATA';
将数据文件从一个ASM磁盘群组迁移至另一个ASM磁盘群组：
代码语言：javascript复制SQL> ALTER DATABASE MOVE DATAFILE ' DG_DATA/DBNAME/DATAFILE/users_01.dbf ' TO ' DG_DATA_02';
在数据文件已存在于新路径的情况下，以相同的命名将其覆盖：
代码语言：javascript复制SQL> ALTER DATABASE MOVE DATAFILE '/u00/data/users_01.dbf' TO '/u00/data_new/users_01.dbf' REUSE;
复制文件到一个新路径，同时在原路径下保留其拷贝：
代码语言：javascript复制SQL> ALTER DATABASE MOVE DATAFILE '/u00/data/users_01.dbf' TO '/u00/data_new/users_01.dbf' KEEP;
当通过查询v$session_longops动态视图来移动文件时，你可以监控这一过程。另外，你也可以引用alert.log，Oracle会在其中记录具体的行为。
代码语言：javascript复制SELECT a.USERNAME,
(SELECT upper(nb.OSUSER) FROM v$session nb WHERE nb.SID = a.sid) OSUSER,
(SELECT nb.sid || ',' || nb.SERIAL# || ',' || pr.SPID
FROM v$process pr, v$session nb
WHERE nb.PADDR = pr.ADDR
and nb.sid = a.SID
and nb.SERIAL# = a.SERIAL#) session_info,
a.opname,
to_char(a.START_TIME, 'YYYY-MM-DD HH24:MI:SS') start_time,
round(a.SOFAR * 100 / a.TOTALWORK, 2) || '%' AS progress,
a.TIME_REMAINING TIME_REMAINING,
a.elapsed_seconds elapsed_seconds,
message message,
(SELECT nb.EVENT FROM V$session_Wait nb WHERE nb.SID = a.SID) wait_event,
(SELECT nb.STATUS FROM v$session nb WHERE nb.SID = a.SID) STATUS
FROM v$session_longops a
WHERE a.time_remaining <> 0
ORDER BY status, a.TIME_REMAINING DESC, a.SQL_ID, a.sid;
代码语言：javascript复制需要注意的是，在12c中，移动数据文件必须进入到相关的容器中才可以，否则会报错“ORA-01516: nonexistent log file, data file, or temporary file "12" in the current container”
SYS@ORCLCDB> col name format a60
SYS@ORCLCDB> set pagesize 9999
SYS@ORCLCDB> select ts#,file#,name,status from v$datafile d ;
TS# FILE# NAME STATUS
---------- ---------- ------------------------------------------------------------ --------------
0 1 /opt/oracle/oradata/ORCLCDB/system01.dbf SYSTEM
1 3 /opt/oracle/oradata/ORCLCDB/sysaux01.dbf ONLINE
2 4 /opt/oracle/oradata/ORCLCDB/undotbs01.dbf ONLINE
0 5 /opt/oracle/oradata/ORCLCDB/pdbseed/system01.dbf SYSTEM
1 6 /opt/oracle/oradata/ORCLCDB/pdbseed/sysaux01.dbf ONLINE
4 7 /opt/oracle/oradata/ORCLCDB/users01.dbf ONLINE
2 8 /opt/oracle/oradata/ORCLCDB/pdbseed/undotbs01.dbf ONLINE
0 9 /opt/oracle/oradata/ORCLCDB/ORCLPDB1/system01.dbf SYSTEM
1 10 /opt/oracle/oradata/ORCLCDB/ORCLPDB1/sysaux01.dbf ONLINE
2 11 /opt/oracle/oradata/ORCLCDB/ORCLPDB1/undotbs01.dbf ONLINE
5 12 /opt/oracle/oradata/ORCLCDB/ORCLPDB1/users01.dbf ONLINE
11 rows selected.
SYS@ORCLCDB>
SYS@ORCLCDB> ALTER DATABASE MOVE DATAFILE 12 TO '/opt/oracle/oradata/ORCLCDB/ORCLPDB1/users01_test.dbf';
ALTER DATABASE MOVE DATAFILE 12 TO '/opt/oracle/oradata/ORCLCDB/ORCLPDB1/users01_test.dbf'
*
ERROR at line 1:
ORA-01516: nonexistent log file, data file, or temporary file "12" in the current container
SYS@ORCLCDB> select ts#,file#,name,status,con_id from v$datafile d ;
TS# FILE# NAME STATUS CON_ID
---------- ---------- ------------------------------------------------------------ -------------- ------
0 1 /opt/oracle/oradata/ORCLCDB/system01.dbf SYSTEM 1
1 3 /opt/oracle/oradata/ORCLCDB/sysaux01.dbf ONLINE 1
2 4 /opt/oracle/oradata/ORCLCDB/undotbs01.dbf ONLINE 1
0 5 /opt/oracle/oradata/ORCLCDB/pdbseed/system01.dbf SYSTEM 2
1 6 /opt/oracle/oradata/ORCLCDB/pdbseed/sysaux01.dbf ONLINE 2
4 7 /opt/oracle/oradata/ORCLCDB/users01.dbf ONLINE 1
2 8 /opt/oracle/oradata/ORCLCDB/pdbseed/undotbs01.dbf ONLINE 2
0 9 /opt/oracle/oradata/ORCLCDB/ORCLPDB1/system01.dbf SYSTEM 3
1 10 /opt/oracle/oradata/ORCLCDB/ORCLPDB1/sysaux01.dbf ONLINE 3
2 11 /opt/oracle/oradata/ORCLCDB/ORCLPDB1/undotbs01.dbf ONLINE 3
5 12 /opt/oracle/oradata/ORCLCDB/ORCLPDB1/users01.dbf ONLINE 3
11 rows selected.
SYS@ORCLCDB> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCLPDB1 READ WRITE NO
SYS@ORCLCDB> alter session set container=orclpdb1;
Session altered.
SYS@ORCLCDB> ALTER DATABASE MOVE DATAFILE 12 TO '/opt/oracle/oradata/ORCLCDB/ORCLPDB1/users01_test.dbf';
Database altered.
SYS@ORCLCDB> select ts#,file#,name,status,con_id from v$datafile d ;
TS# FILE# NAME STATUS CON_ID
---------- ---------- ------------------------------------------------------------ -------------- ------
0 9 /opt/oracle/oradata/ORCLCDB/ORCLPDB1/system01.dbf SYSTEM 3
1 10 /opt/oracle/oradata/ORCLCDB/ORCLPDB1/sysaux01.dbf ONLINE 3
2 11 /opt/oracle/oradata/ORCLCDB/ORCLPDB1/undotbs01.dbf ONLINE 3
5 12 /opt/oracle/oradata/ORCLCDB/ORCLPDB1/users01_test.dbf ONLINE 3
SYS@ORCLCDB> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
ll[oracle@lhrora19c ~]$ ll /opt/oracle/oradata/ORCLCDB/ORCLPDB1/users01_*
-rw-r----- 1 oracle oinstall 5251072 Nov 14 16:54 /opt/oracle/oradata/ORCLCDB/ORCLPDB1/users01_test.dbf
有关ASM数据文件和文件系统文件互相转换的方法
ASM数据文件和OS文件系统互相转移方法总结
总结
1、若是12c，则可以在线直接操作
2、若是11g，请严格按照步骤来操作，在offline后记得执行recover操作
3、最最重要的一点：在移动数据文件之前，一定记得先查看目的地是否有重名的数据文件，否则会导致数据文件物理覆盖，造成不可恢复的损失！！！！ 已碰到网友出现过此类情况！！！
