更改数据文件位置演练

denglj

数据库：oracle 19.3
系统：Windows 11
实验目标：测试更改数据库数据文件位置.
1、更改临时文件位置
SQL> set line 200
SQL> select file_name,autoextensible,tablespace_name,bytes/1024/1024 from dba_temp_files order by 1;

FILE_NAME                                                    AUT TABLESPACE_NAME                BYTES/1024/1024
------------------------------------------------------------ --- ------------------------------ ---------------
F:\APP\ADMINISTRATOR\ORADATA\ORCL\TEMP01.DBF                 YES TEMP                                        20

SQL> create temporary tablespace local_temp tempfile 'E:\dmp\temp02.dbf' size 50m autoextend on;

Tablespace created.

SQL> select file_name,autoextensible,tablespace_name,bytes/1024/1024 from dba_temp_files order by 1;

FILE_NAME                                                    AUT TABLESPACE_NAME                BYTES/1024/1024
------------------------------------------------------------ --- ------------------------------ ---------------
E:\DMP\TEMP02.DBF                                            YES LOCAL_TEMP                                  50
F:\APP\ADMINISTRATOR\ORADATA\ORCL\TEMP01.DBF                 YES TEMP                                        20

SQL> select v.file#,t.file_name,v.status from dba_temp_files t,v$tempfile v where t.file_id=v.file#;

     FILE# FILE_NAME                                                    STATUS
---------- ------------------------------------------------------------ -------
         2 E:\DMP\TEMP02.DBF                                            ONLINE
         1 F:\APP\ADMINISTRATOR\ORADATA\ORCL\TEMP01.DBF                 ONLINE

SQL> alter database tempfile 'E:\DMP\TEMP02.DBF' offline;

Database altered.

此时系统层面将E:\DMP\TEMP02.DBF复制到F:\APP\ADMINISTRATOR\ORADATA\ORCL\目录下，并命名为TEMP02.DBF
SQL> alter database rename file 'E:\DMP\TEMP02.DBF' to 'F:\APP\ADMINISTRATOR\ORADATA\ORCL\TEMP02.DBF';

Database altered.

SQL> alter database tempfile 'F:\APP\ADMINISTRATOR\ORADATA\ORCL\TEMP02.DBF' online;

Database altered.

SQL> select file_name,autoextensible,bytes/1024/1024,tablespace_name from dba_temp_files;

FILE_NAME                                                    AUT BYTES/1024/1024 TABLESPACE_NAME
------------------------------------------------------------ --- --------------- ------------------------------
F:\APP\ADMINISTRATOR\ORADATA\ORCL\TEMP01.DBF                 YES              20 TEMP
F:\APP\ADMINISTRATOR\ORADATA\ORCL\TEMP02.DBF                 YES              50 LOCAL_TEMP
如上，成功更改临时文件位置.

2、更改数据文件位置
2.1、运用更改临时文件的方法更改数据文件位置
SQL> alter tablespace test add datafile 'E:\DMP\test03.DBF' size 30m autoextend on;

表空间已更改.

SQL> select file_name,autoextensible,tablespace_name,bytes/1024/1024 from dba_data_files where tablespace_name='TEST';

FILE_NAME                                                    AUTOEX TABLESPACE_NAME      BYTES/1024/1024
------------------------------------------------------------ ------ -------------------- ---------------
F:\APP\ADMINISTRATOR\ORADATA\ORCL\TEST01.DBF                 NO     TEST                             100
F:\APP\ADMINISTRATOR\ORADATA\ORCL\TEST02.DBF                 NO     TEST                              50
E:\DMP\TEST03.DBF                                            YES    TEST                              30

SQL> select v.file#,t.file_name,v.status from dba_data_files t,v$datafile v where t.file_id=v.file# and t.tablespace_name='TEST';

     FILE# FILE_NAME                                                    STATUS
---------- ------------------------------------------------------------ --------------
         5 F:\APP\ADMINISTRATOR\ORADATA\ORCL\TEST01.DBF                 ONLINE
         2 F:\APP\ADMINISTRATOR\ORADATA\ORCL\TEST02.DBF                 ONLINE
        16 E:\DMP\TEST03.DBF                                            ONLINE

SQL> alter database datafile 'E:\DMP\TEST03.DBF' offline;

数据库已更改.

此时系统层面复制E:\DMP\TEST03.DBF到F:\APP\ADMINISTRATOR\ORADATA\ORCL\目录下

SQL> alter database rename file 'E:\DMP\TEST03.DBF' to 'F:\APP\ADMINISTRATOR\ORADATA\ORCL\Test03.DBF';

数据库已更改.

SQL> alter database datafile 'F:\APP\ADMINISTRATOR\ORADATA\ORCL\Test03.DBF' online;
alter database datafile 'F:\APP\ADMINISTRATOR\ORADATA\ORCL\Test03.DBF' online
*
第 1 行出现错误:
ORA-01113: 文件 16 需要介质恢复
ORA-01110: 数据文件 16: 'F:\APP\ADMINISTRATOR\ORADATA\ORCL\TEST03.DBF'

如上，当修改新目录下的TEST03.DBF的数据文件为online时，报错ORA-01113.

SQL> select v.file#,t.file_name,v.status from dba_data_files t,v$datafile v where t.file_id=v.file# and t.tablespace_name='TEST';

     FILE# FILE_NAME                                                    STATUS
---------- ------------------------------------------------------------ --------------
         5 F:\APP\ADMINISTRATOR\ORADATA\ORCL\TEST01.DBF                 ONLINE
         2 F:\APP\ADMINISTRATOR\ORADATA\ORCL\TEST02.DBF                 ONLINE
        16 F:\APP\ADMINISTRATOR\ORADATA\ORCL\TEST03.DBF                 RECOVER

以下解决过程：               
SQL> shutdown immediate
数据库已经关闭.
已经卸载数据库.
ORACLE 例程已经关闭.
SQL> recover datafile 'F:\APP\ADMINISTRATOR\ORADATA\ORCL\TEST03.DBF';
ORA-01034: ORACLE not available
进程 ID: 0
会话 ID: 0 序列号: 0


SQL> startup mount;
ORACLE 例程已经启动.

Total System Global Area 1.2583E+10 bytes
Fixed Size                 12581272 bytes
Variable Size            4194304000 bytes
Database Buffers         8355053568 bytes
Redo Buffers               20971520 bytes
数据库装载完毕.
SQL> recover datafile 'F:\APP\ADMINISTRATOR\ORADATA\ORCL\TEST03.DBF';
完成介质恢复.
SQL> alter database open;

数据库已更改.

SQL> select v.file#,t.file_name,v.status from dba_data_files t,v$datafile v where t.file_id=v.file# and t.tablespace_name='TEST';

     FILE# FILE_NAME                                                    STATUS
---------- ------------------------------------------------------------ --------------
         5 F:\APP\ADMINISTRATOR\ORADATA\ORCL\TEST01.DBF                 ONLINE
         2 F:\APP\ADMINISTRATOR\ORADATA\ORCL\TEST02.DBF                 ONLINE
        16 F:\APP\ADMINISTRATOR\ORADATA\ORCL\TEST03.DBF                 OFFLINE

SQL> alter database datafile 'F:\APP\ADMINISTRATOR\ORADATA\ORCL\TEST03.DBF' online;

数据库已更改.

SQL> select v.file#,t.file_name,v.status from dba_data_files t,v$datafile v where t.file_id=v.file# and t.tablespace_name='TEST';

     FILE# FILE_NAME                                                    STATUS
---------- ------------------------------------------------------------ --------------
         5 F:\APP\ADMINISTRATOR\ORADATA\ORCL\TEST01.DBF                 ONLINE
         2 F:\APP\ADMINISTRATOR\ORADATA\ORCL\TEST02.DBF                 ONLINE
        16 F:\APP\ADMINISTRATOR\ORADATA\ORCL\TEST03.DBF                 ONLINE

2.2、修改数据文件位置无报错的方法
SQL> select v.file#,t.file_name,v.status from dba_data_files t,v$datafile v where t.file_id=v.file# and t.tablespace_name='TEST';

     FILE# FILE_NAME                                                    STATUS
---------- ------------------------------------------------------------ --------------
         5 F:\APP\ADMINISTRATOR\ORADATA\ORCL\TEST01.DBF                 ONLINE
         2 F:\APP\ADMINISTRATOR\ORADATA\ORCL\TEST02.DBF                 ONLINE
        16 F:\APP\ADMINISTRATOR\ORADATA\ORCL\TEST03.DBF                 ONLINE

SQL> alter tablespace test add datafile 'E:\DMP\test04.DBF' size 20m autoextend on;

表空间已更改.

SQL> shutdown immediate
数据库已经关闭.
已经卸载数据库.
ORACLE 例程已经关闭.
SQL>
SQL> startup mount
ORACLE 例程已经启动.

Total System Global Area 1.2583E+10 bytes
Fixed Size                 12581272 bytes
Variable Size            4194304000 bytes
Database Buffers         8355053568 bytes
Redo Buffers               20971520 bytes
数据库装载完毕.

此时系统层面复制E:\DMP\TEST04.DBF到F:\APP\ADMINISTRATOR\ORADATA\ORCL\目录下
SQL> alter database rename file 'E:\DMP\test04.DBF' to 'F:\APP\ADMINISTRATOR\ORADATA\ORCL\TEST04.dbf';

数据库已更改.

SQL> alter database open;

数据库已更改.

SQL> select v.file#,t.file_name,v.status from dba_data_files t,v$datafile v where t.file_id=v.file# and t.tablespace_name='TEST';

     FILE# FILE_NAME                                                    STATUS
---------- ------------------------------------------------------------ --------------
         5 F:\APP\ADMINISTRATOR\ORADATA\ORCL\TEST01.DBF                 ONLINE
         2 F:\APP\ADMINISTRATOR\ORADATA\ORCL\TEST02.DBF                 ONLINE
        16 F:\APP\ADMINISTRATOR\ORADATA\ORCL\TEST03.DBF                 ONLINE
        17 F:\APP\ADMINISTRATOR\ORADATA\ORCL\TEST04.DBF                 ONLINE
如上，数据文件位置成功修改.

郑全

12c以上版本，可以直接alter database move  datafile 'E:\DMP\test04.DBF'  to 'F:\APP\ADMINISTRATOR\ORADATA\ORCL\TEST04.dbf'  
不再需要操作系统层面去进行文件的移动。