oracle移动数据文件-Oracle-运维笔记-技术文档

总有同事把数据文件创建到其他目录，这时候就需要移动一下数据文件了

1、查看是否开启了归档日志，如果没开启，就只能停机移动了

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /oracle/app/archive
Oldest online log sequence 8
Next log sequence to archive 10
Current log sequence 10

2、查看数据文件状态，注意数据文件的编号13

SQL> select name,file#,status from v$datafile;

NAME FILE# STATUS
------------------------------------------------------------------ ---------- --------------
/oracle/app/oracle/oradata/OCP19C/system01.dbf 1 SYSTEM
/oracle/app/oracle/oradata/OCP19C/sysaux01.dbf 3 ONLINE
/oracle/app/oracle/oradata/OCP19C/undotbs01.dbf 4 ONLINE
/oracle/app/oracle/oradata/OCP19C/pdbseed/system01.dbf 5 SYSTEM
/oracle/app/oracle/oradata/OCP19C/pdbseed/sysaux01.dbf 6 ONLINE
/oracle/app/oracle/oradata/OCP19C/users01.dbf 7 ONLINE
/oracle/app/oracle/oradata/OCP19C/pdbseed/undotbs01.dbf 8 ONLINE
/oracle/app/oracle/oradata/OCP19C/pdb/system01.dbf 9 SYSTEM
/oracle/app/oracle/oradata/OCP19C/pdb/sysaux01.dbf 10 ONLINE
/oracle/app/oracle/oradata/OCP19C/pdb/undotbs01.dbf 11 ONLINE
/oracle/app/oracle/oradata/OCP19C/pdb/users01.dbf 12 ONLINE
/home/oracle/bac.dbf 13 ONLINE

3、让数据文件离线

SQL> alter database datafile '/home/oracle/bac.dbf' offline;

4、查看现在的数据文件状态

SQL> select name,file#,status from v$datafile;

5、物理移动数据文件

SQL> !mv /home/oracle/bac.dbf /oracle/app/oracle/oradata/OCP19C/abc.dbf

6、逻辑移动数据文件（重命名）

SQL> alter database rename file '/home/oracle/bac.dbf' to '/oracle/app/oracle/oradata/OCP19C/abc.dbf';

7、恢复数据文件，查看数据文件状态

SQL> alter database datafile 13 online;

Database altered.

SQL> select name,file#,status from v$datafile;

停机移动数据库

1、关闭数据库

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

2、移动数据文件

SQL> !mv /oracle/app/oracle/oradata/OCP19C/abc.dbf /oracle/app/oracle/oradata/OCP19C/abc1.dbf

3、启动数据库到mount状态

SQL> startup mount
ORACLE instance started.

Total System Global Area 2516582192 bytes
Fixed Size 9137968 bytes
Variable Size 536870912 bytes
Database Buffers 1962934272 bytes
Redo Buffers 7639040 bytes
Database mounted.

4、rename数据文件名

SQL> alter database rename file '/oracle/app/oracle/oradata/OCP19C/abc.dbf' to '/oracle/app/oracle/oradata/OCP19C/abc1.dbf';

5、查看数据文件状态

SQL> select name,file#,status from v$datafile;

12 rows selected.

6、启动数据库

SQL> alter database open;

Database altered.

12C之后也可以直接移动数据文件，语句如下

SQL> alter database move datafile '/oracle/app/oracle/oradata/OCP19C/abc1.dbf' to '/oracle/app/oracle/oradata/OCP19C/abc.dbf';

Database altered.

SQL> select name,file#,status from v$datafile;