Oracle移动数据文件不停机和停机两种方式详解

2 /oradata/orcl11g/sysaux01.dbf ONLINE
3 /oradata/orcl11g/undotbs01.dbf ONLINE
4 /oradata/orcl11g/users01.dbf ONLINE
5 /oradata/orcl11g/example01.dbf ONLINE
6 /oradata/orcl11g/test01.dbf ONLINE
7 /oradata/ORCL11G/datafile/o1_mf_test_j7jgpq7k_.dbf ONLINE
7 rows selected.
SQL alter database datafile 7 offline;
Database altered.
物理层移动数据文件
SQL !mv /oradata/ORCL11G/datafile/o1_mf_test_j7jgpq7k_.dbf /oradata/orcl11g/test02.dbf
SQL !ls /oradata/orcl11g/test02.dbf
/oradata/orcl11g/test02.dbf
逻辑层rename数据文件
SQL alter database rename file /oradata/ORCL11G/datafile/o1_mf_test_j7jgpq7k_.dbf to /oradata/orcl11g/test02.dbf
Database altered.
恢复数据文件
SQL recover datafile 7;
Media recovery complete.
online数据文件
SQL alter database datafile 7 online;
Database altered.
SQL select file#,name,status from v$datafile;
FILE# NAME STATUS
- -
1 /oradata/orcl11g/system01.dbf SYSTEM
2 /oradata/orcl11g/sysaux01.dbf ONLINE
3 /oradata/orcl11g/undotbs01.dbf ONLINE
4 /oradata/orcl11g/users01.dbf ONLINE
5 /oradata/orcl11g/example01.dbf ONLINE
6 /oradata/orcl11g/test01.dbf ONLINE
7 /oradata/orcl11g/test02.dbf ONLINE
7 rows selected.

二、停机移动数据文件

完整步骤：

1、关闭数据库

2、物理层移动数据文件（可重命名）

3、开启数据库到mount

4、逻辑层rename数据文件路径及名称

5、开启数据库

创建一个TEST表空间，发现建在了/oradata/ORCL11G/下，希望移动到/oradata/orcl11g/下
SQL create tablespace TEST;
Tablespace created.
SQL select name from v$datafile;
NAME

/oradata/orcl11g/system01.dbf
/oradata/orcl11g/sysaux01.dbf
/oradata/orcl11g/undotbs01.dbf
/oradata/orcl11g/users01.dbf
/oradata/orcl11g/example01.dbf
/oradata/ORCL11G/datafile/o1_mf_test_j7jfm30c_.dbf
尝试在线移动数据文件
SQL alter database rename file /oradata/ORCL11G/datafile/o1_mf_test_j7jfm30c_.dbf to /oradata/orcl11g/test01.dbf
alter database rename file /oradata/ORCL11G/datafile/o1_mf_test_j7jfm30c_.dbf to /oradata/orcl11g/test01.dbf
*
ERROR at line 1:
ORA-01511: error in renaming log/data files
ORA-01121: cannot rename database file 6 file is in use or recovery
ORA-01110: data file 6: /oradata/ORCL11G/datafile/o1_mf_test_j7jfm30c_.dbf
报错ORA-01121
[oracle@orcl11g:/home/oracle]$ oerr ORA 01121
01121, 00000, cannot rename database file %s file is in use or recovery
// *Cause: Attempted to use ALTER DATABASE RENAME to rename a
// datafile that is online in an open instance or is being recovered.
// *Action: Close database in all instances and end all recovery sessions.

明确无法在线移动数据文件，需要关闭数据库。

操作系统层面移动数据文件，并且重命名
[oracle@orcl11g:/oradata/ORCL11G/datafile]$ ll
total 102408
-rw-r 1 oracle oinstall 104865792 Apr 15 20:55 o1_mf_test_j7jfm30c_.dbf
[oracle@orcl11g:/oradata/ORCL11G/datafile]$ mv o1_mf_test_j7jfm30c_.dbf /oradata/orcl11g/
control01.ctl example01.dbf redo01.log redo02.log redo03.log sysaux01.dbf system01.dbf temp01.dbf undotbs01.dbf users01.dbf
[oracle@orcl11g:/oradata/ORCL11G/datafile]$ mv o1_mf_test_j7jfm30c_.dbf /oradata/orcl11g/test01.dbf
[oracle@orcl11g:/oradata/ORCL11G/datafile]$ ll /oradata/orcl11g/test01.dbf
开启数据库到mount
SQL startup mount
ORACLE instance started.
Total System Global Area 1603411968 bytes
Fixed Size 2253664 bytes
Variable Size 452988064 bytes
Database Buffers 1140850688 bytes
Redo Buffers 7319552 bytes
Database mounted.
rename数据文件名称
SQL select name from v$datafile;
NAME

/oradata/orcl11g/system01.dbf
/oradata/orcl11g/sysaux01.dbf
/oradata/orcl11g/undotbs01.dbf
/oradata/orcl11g/users01.dbf
/oradata/orcl11g/example01.dbf
/oradata/ORCL11G/datafile/o1_mf_test_j7jfm30c_.dbf
6 rows selected.
SQL alter database rename file /oradata/ORCL11G/datafile/o1_mf_test_j7jfm30c_.dbf to /oradata/orcl11g/test01.dbf
Database altered.
SQL select name from v$datafile;
NAME

/oradata/orcl11g/system01.dbf
/oradata/orcl11g/sysaux01.dbf
/oradata/orcl11g/undotbs01.dbf
/oradata/orcl11g/users01.dbf
/oradata/orcl11g/example01.dbf
/oradata/orcl11g/test01.dbf
6 rows selected.
开启数据库
SQL alter database open;
Database altered.

12C and later

支持在线移动数据文件：

可参考：Online Move Datafile in Oracle Database 12c Release 1 (12.1)

语法如下：

ALTER DATABASE MOVE DATAFILE ( filename | ASM_filename | file_number )
[ TO ( filename | ASM_filename ) ]
[ REUSE ] [ KEEP ]

本篇文章到此结束，如果您有相关技术方面疑问可以联系我们技术人员远程解决，感谢大家支持本站！

我想要获取技术服务或软件
服务范围：MySQL、ORACLE、SQLSERVER、MongoDB、PostgreSQL 、程序问题
服务方式：远程服务、电话支持、现场服务，沟通指定方式服务
技术标签：数据恢复、安装配置、数据迁移、集群容灾、异常处理、其它问题

本站部分文章参考或来源于网络，如有侵权请联系站长。
数据库远程运维 Oracle移动数据文件不停机和停机两种方式详解