Oracle数据库更改数据文件位置

前言

近期IPCC服务器的数据库数据目录/oradata磁盘空间不足，由于无法添加磁盘来扩容，需要将数据库数据目录从/oradata更改为/home/oracle/oradata。

由于数据库文件和表空间具有不同的性质，将数据文件位置更改分为四个步骤：控制文件位置更改、数据文件位置更改(不包括临时数据文件)、临时数据文件位置更改、日志文件位置更改。

一、控制文件位置更改

停止数据库监听，防止有应用连接对数据库进行修改。

控制文件有3个：/oradata/ipcc/control01.ctl、/oradata/ipcc/control02.ctl、/oradata/ipcc/control02.ctl。

在数据库open的状态下：

alter system set 
control_files='/home/oracle/oradata/ipcc/control01.ctl','/home/oracle/oradata/ipcc/control02.ctl','/home/oracle/oradata/ipcc/control03.ctl' scope=spfile;

然后将数据库shutdown。

SQL> shutdown immediate;

将控制文件移动到新目录/home/oracle/oradata/ipcc下。

mv /oradata/ipcc/control* /home/oracle/oradata/ipcc/

将数据库启动到mount状态，至此，控制文件位置更改结束。

二、数据文件位置更改（不包括临时数据文件）

数据文件位置更改也分为两部分：system表空间和默认undo表空间数据文件、普通表空间数据文件，因为system表空间和默认undo表空间是不能被offline的，只能在mount状态对其文件位置进行修改。

1. system表空间和默认undo表空间数据文件位置更改

数据库必须在mount状态。

将 system表空间和默认undo表空间数据文件复制到/home/oracle/oradata目录下。

用以下SQL语句生成更改文件位置的SQL语句。

SELECT 'alter database rename file ''' || Ddf.File_Name || ''' to ''' ||
       '/home/oracle' || Ddf.File_Name || ''';'
  FROM Dba_Data_Files Ddf
 WHERE Ddf.Tablespace_Name = 'SYSTEM'
    OR Ddf.Tablespace_Name = 'UNDOTBS1';

生成的SQL语句如下，使用sysdba账户执行。

alter database rename file '/oradata/ipcc/undotbs01.dbf' to '/home/oracle/oradata/ipcc/undotbs01.dbf';
alter database rename file '/oradata/ipcc/system01.dbf' to '/home/oracle/oradata/ipcc/system01.dbf';
alter database rename file '/oradata/ipcc/undotbs02.dbf' to '/home/oracle/oradata/ipcc/undotbs02.dbf';    

使用sysdba账户执行以上语句。

2. 普通表空间数据文件位置更改

数据库需要在open状态下。

需要先将表空间全部offline，使用以下语句生成表空间offline的SQL语句。

SELECT 'alter tablespace ' || Dt.Tablespace_Name || ' offline;'
  FROM Sys.Dba_Tablespaces Dt
 WHERE Dt.Contents <> 'TEMPORARY'
   AND Dt.Tablespace_Name <> 'UNDOTBS1'
   AND Dt.Tablespace_Name <> 'SYSTEM';

生成的SQL语句如下，使用sysdba账户执行。

alter tablespace SYSAUX offline;
alter tablespace USERS offline;
......

将这些表空间的数据文件复制到新目录/home/oracle/oradata目录下，数据文件rename的时候，需要两边都有数据文件。

使用以下语句生成rename数据文件的SQL语句。

SELECT 'alter tablespace ' || Ddf.Tablespace_Name || ' rename datafile ''' ||
       Ddf.File_Name || ''' to ''' || '/home/oracle' || Ddf.File_Name ||
       ''';'
  FROM Sys.Dba_Data_Files Ddf
 WHERE Ddf.Tablespace_Name <> 'SYSTEM'
   AND Ddf.Tablespace_Name <> 'UNDOTBS1';

生成的SQL语句如下，使用sysdba账户执行。

alter tablespace USERS rename datafile '/oradata/ipcc/users01.dbf' to '/home/oracle/oradata/ipcc/users01.dbf';
alter tablespace SYSAUX rename datafile '/oradata/ipcc/sysaux01.dbf' to '/home/oracle/oradata/ipcc/sysaux01.dbf';
......

执行结束之后，将表空间全部online，使用以下语句生成表空间online的SQL语句。

SELECT 'alter tablespace ' || Dt.Tablespace_Name || ' online;'
  FROM Sys.Dba_Tablespaces Dt
 WHERE Dt.Contents <> 'TEMPORARY'
   AND Dt.Tablespace_Name <> 'UNDOTBS1'
   AND Dt.Tablespace_Name <> 'SYSTEM';

生成的SQL语句如下，使用sysdba账户执行。

alter tablespace SYSAUX online;
alter tablespace USERS online;
......

至此，数据文件位置更改结束。

三、临时数据文件位置更改

临时表空间比较特殊，不能将临时表空间offline，需要将临时表空间的临时数据文件offline。

数据库在open状态下。

生成临时数据文件offline的SQL语句如下。

SELECT 'alter database tempfile ''' || File_Name || ''' offline;'
   FROM Dba_Temp_Files
  ORDER BY File_Id;  

生成的SQL语句如下，使用sysdba账户执行。

alter database tempfile '/oradata/ipcc/temp01.dbf' offline;
alter database tempfile '/oradata/ricd_temp10' offline;
......

将临时数据文件复制到/home/oracle/oradata目录下。

生成rename临时数据文件的SQL语句如下。

SELECT 'alter database rename file ''' || File_Name || ''' to ''' ||
       '/home/oracle' || File_Name || ''';'
  FROM Dba_Temp_Files
 ORDER BY File_Id;

 生成的SQL语句如下，使用sysdba账户执行。

alter database rename file '/oradata/ipcc/temp01.dbf' to '/home/oracle/oradata/ipcc/temp01.dbf';
alter database rename file '/oradata/ricd_temp10' to '/home/oracle/oradata/ricd_temp10';
......

 执行结束之后，将临时数据文件online，生成临时数据文件online的SQL语句如下。

SELECT 'alter database tempfile ''' || File_Name ||
       ''' online;'
  FROM Dba_Temp_Files
 ORDER BY File_Id;

生成的SQL语句如下，使用sysdba账户执行。

alter database tempfile '/home/oracle/oradata/ipcc/temp01.dbf' online;
alter database tempfile '/home/oracle/oradata/ricd_temp10' online;
......

至此，临时数据文件位置更改结束。

四、日志文件位置更改

数据库可以在mount状态或open状态下。

查看日志文件状态

SQL> select group#,thread#,sequence#,members,archived,status from v$log;
GROUP#    THREAD#  SEQUENCE#    MEMBERS ARC STATUS
---------- ---------- ---------- ---------- --- ----------------
	 1	    1	    8746	  2 YES ACTIVE
	 2	    1	    8747	  2 YES ACTIVE
	 3	    1	    8748	  2 YES ACTIVE
	 4	    1	    8749	  2 NO	CURRENT
	 5	    1	    8745	  2 YES ACTIVE

SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/oradata/ipcc/redo11.log
/oradata/ipcc/redo12.log
/oradata/ipcc/redo21.log
/oradata/ipcc/redo41.log
/oradata/ipcc/redo42.log
/oradata/ipcc/redo51.log
/oradata/ipcc/redo52.log
/oradata/ipcc/redo22.log
/oradata/ipcc/redo31.log
/oradata/ipcc/redo32.log

当日志文件状态不为current的时候，将日志文件复制到新目录/home/oracle/oradata下，然后可以切换日志文件位置。也可以使用alter system switch logfile，切换日志文件状态。

SQL> alter database rename file '/oradata/ipcc/redo11.log' to '/home/oracle/oradata/ipcc/redo11.log';
SQL> alter database rename file '/oradata/ipcc/redo12.log' to '/home/oracle/oradata/ipcc/redo12.log';

SQL> alter database rename file '/oradata/ipcc/redo21.log' to '/home/oracle/oradata/ipcc/redo21.log';
SQL> alter database rename file '/oradata/ipcc/redo22.log' to '/home/oracle/oradata/ipcc/redo22.log';

SQL> alter database rename file '/oradata/ipcc/redo31.log' to '/home/oracle/oradata/ipcc/redo31.log';
SQL> alter database rename file '/oradata/ipcc/redo32.log' to '/home/oracle/oradata/ipcc/redo32.log';

SQL> alter database rename file '/oradata/ipcc/redo41.log' to '/home/oracle/oradata/ipcc/redo41.log';
SQL> alter database rename file '/oradata/ipcc/redo42.log' to '/home/oracle/oradata/ipcc/redo42.log';

SQL> alter database rename file '/oradata/ipcc/redo51.log' to '/home/oracle/oradata/ipcc/redo51.log';
SQL> alter database rename file '/oradata/ipcc/redo52.log' to '/home/oracle/oradata/ipcc/redo52.log';

 至此，日志文件位置更改结束。

 当所有文件位置均更改结束时，测试数据库的可用性；如果无问题，可以删除旧数据目录下的文件。