在Oracle中，如何移动或重命名数据文件？
👉 本文共约1350个字，系统预计阅读时间或需6分钟。
简介
数据文件查询：
1 2 3 4 5 | 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拷贝，该方法适用于任何类型的文件，步骤如下所示：
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！
1 2 3 4 5 | A、SHUTDOWN IMMEDIATE关闭数据库 B、在OS下拷贝数据文件到新的地点 C、STARTUP MOUNT启动数据库到MOUNT状态下 D、ALTER DATABASE RENAME FILE '老文件' TO '新文件'; E、ALTER DATABASE OPEN;打开数据库 |
其实利用OS拷贝也可以联机操作，不关闭数据库，但是只针对可以OFFLINE的数据文件，步骤如下所示：
1 2 3 4 5 | ① 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联机操作
1 2 3 4 5 6 7 8 9 10 11 | 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用来更新控制文件。
示例：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 | 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方法步骤：
1 2 3 4 | ① 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; |
示例：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | 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，反之亦然。
重命名数据文件：
1 | SQL> ALTER DATABASE MOVE DATAFILE '/u00/data/users01.dbf' TO '/u00/data/users_01.dbf'; |
从非ASM迁移数据文件至ASM：
1 | SQL> ALTER DATABASE MOVE DATAFILE '/u00/data/users_01.dbf' TO '+DG_DATA'; |
将数据文件从一个ASM磁盘群组迁移至另一个ASM磁盘群组：
1 | SQL> ALTER DATABASE MOVE DATAFILE '+DG_DATA/DBNAME/DATAFILE/users_01.dbf ' TO '+DG_DATA_02'; |
在数据文件已存在于新路径的情况下，以相同的命名将其覆盖：
1 | SQL> ALTER DATABASE MOVE DATAFILE '/u00/data/users_01.dbf' TO '/u00/data_new/users_01.dbf' REUSE; |
复制文件到一个新路径，同时在原路径下保留其拷贝：
1 | SQL> ALTER DATABASE MOVE DATAFILE '/u00/data/users_01.dbf' TO '/u00/data_new/users_01.dbf' KEEP; |
当通过查询v$session_longops动态视图来移动文件时，你可以监控这一过程。另外，你也可以引用alert.log，Oracle会在其中记录具体的行为。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | 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; |
需要注意的是，在12c中，移动数据文件必须进入到相关的容器中才可以，否则会报错“ORA-01516: nonexistent log file, data file, or temporary file "12" in the current container”
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 | 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数据文件和文件系统文件互相转换的方法
总结
1、若是12c，则可以在线直接操作
2、若是11g，请严格按照步骤来操作，在offline后记得执行recover操作
3、最最重要的一点：在移动数据文件之前，一定记得先查看目的地是否有重名的数据文件，否则会导致数据文件物理覆盖，造成不可恢复的损失！！！！ 已碰到网友出现过此类情况！！！
