归档与非归档模式下更改数据文件位置

一、归档模式

方法一：

SQL*Plus: Release 11.2.0.1.0 Production on Tue Apr 9 11:17:54 2013
 
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
 
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
 
SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     7
Next log sequence to archive   9
Current log sequence           9
 
SQL> SELECT FILE_ID,FILE_NAME,TABLESPACE_NAME FROM DBA_DATA_FILES;
 
   FILE_ID FILE_NAME                                TABLESPACE_NAME
---------- ---------------------------------------- ------------------------------
         4 /u01/app/oradata/orcl/users01.dbf        USERS
         3 /u01/app/oradata/orcl/ORCL/undotbs01.dbf UNDOTBS1
         2 /u01/app/oradata/orcl/ORCL/sysaux01.dbf  SYSAUX
         1 /u01/app/oradata/orcl/ORCL/system01.dbf  SYSTEM
 
SQL> alter tablespace users offline;
 
Tablespace altered.
 
SQL> ho cp /u01/app/oradata/orcl/users01.dbf /u01/app/oradata/orcl/ORCL/
 
SQL> alter tablespace users rename datafile '/u01/app/oradata/orcl/users01.dbf'
  2  to '/u01/app/oradata/orcl/ORCL/users01.dbf';
 
SQL> alter tablespace users online;
 
Tablespace altered.
 
SQL> SELECT FILE_ID,FILE_NAME,TABLESPACE_NAME FROM DBA_DATA_FILES;
 
   FILE_ID FILE_NAME                                TABLESPACE_NAME
---------- ---------------------------------------- ------------------------------
         4 /u01/app/oradata/orcl/ORCL/users01.dbf   USERS
         3 /u01/app/oradata/orcl/ORCL/undotbs01.dbf UNDOTBS1
         2 /u01/app/oradata/orcl/ORCL/sysaux01.dbf  SYSAUX
         1 /u01/app/oradata/orcl/ORCL/system01.dbf  SYSTEM

方法二：

SQL> ALTERDATABASE DATAFILE 4 OFFLINE;
 
Database altered.
 
SQL> ho cp /u01/app/oradata/orcl/ORCL/users01.dbf /u01/app/oradata/orcl/
 
SQL> altertablespace users rename datafile '/u01/app/oradata/orcl/ORCL/users01.dbf'
  2 to '/u01/app/oradata/orcl/users01.dbf';
 
Tablespace altered.
 
SQL> alterdatabase datafile 4 online;
alter database datafile 4 online
*
ERROR atline 1:
ORA-01113: file 4 needs media recovery
ORA-01110: data file 4:'/u01/app/oradata/orcl/users01.dbf'

    为什么这里会产生不同呢？参见官方文档：

tablespace_state_clauses

ONLINE | OFFLINE

  Specify  NORMAL  to flush all blocks in all data files in the tablespace out of the system global area (SGA). You need not perform media recovery on this tablespace before bringing it back online. This is the default.

alter_datafile_clause

OFFLINE 

Specify  OFFLINE  to take the data file offline. If the database is open, then you must perform media recovery on the data file before bringing it back online,because a checkpoint is not performed on the data file before it is taken offline.

    说白了就是对表空间执行offline的时候会触发checkpoint，而对数据文件执行offline时会立即将其offline，数据文件的终止SCN为无穷大，造成不一致，需要进行recover：

SQL> recover datafile 4;
Media recovery complete.
SQL> alter database datafile 4 online;
 
Database altered.

二、非归档模式

方法一：

$ sqlplus / as sysdba
 
SQL*Plus: Release 11.2.0.1.0 Production on Tue Apr 9 11:17:54 2013
 
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
 
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
 
SQL> archive log list
archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     14
Current log sequence           16
 
SQL> select file_id,file_name,tablespace_name from dba_data_files;
 
   FILE_ID FILE_NAME                                TABLESPACE_NAME
---------- ---------------------------------------- ------------------------------
         4 /u01/app/oradata/SUN/users01.dbf         USERS
         3 /u01/app/oradata/SUN/undotbs01.dbf       UNDOTBS1
         2 /u01/app/oradata/SUN/sysaux01.dbf        SYSAUX
         1 /u01/app/oradata/SUN/system01.dbf        SYSTEM
 
SQL> alter tablespace users offline;
 
Tablespace altered.
 
SQL> ho cp /u01/app/oradata/SUN/users01.dbf
 
SQL> ho mkdir -p /u01/app/oradata/SUN/sun
 
SQL> ho mv /u01/app/oradata/SUN/users01.dbf /u01/app/oradata/SUN/sun
 
SQL> alter tablespace users rename datafile '/u01/app/oradata/SUN/users01.dbf'
  2  to '/u01/app/oradata/SUN/sun/users01.dbf';
 
Tablespace altered.
 
SQL> alter tablespace users online;
 
Tablespace altered.

方法二：

SQL> select file_id,file_name,tablespace_name from dba_data_files;
 
   FILE_ID FILE_NAME                                TABLESPACE_NAME
---------- ---------------------------------------- ------------------------------
         4 /u01/app/oradata/SUN/sun/users01.dbf     USERS
         3 /u01/app/oradata/SUN/undotbs01.dbf       UNDOTBS1
         2 /u01/app/oradata/SUN/sysaux01.dbf        SYSAUX
         1 /u01/app/oradata/SUN/system01.dbf        SYSTEM
 
SQL>
SQL>
SQL> alter database datafile 4 offline;
alter database datafile 4 offline
*
ERROR at line 1:
ORA-01145: offline immediate disallowed unless media recovery enabled

    对于非归档模式下进行数据文件offline操作需要接上drop子句,他不会物理删除文件的。参见官方文档：

FOR DROP  If the database is in  NOARCHIVELOG  mode, then you must specify  FOR  DROP  clause to take a data file offline. However, this clause does not remove the data file from the database. To do that, you must use an operating system command or drop the tablespace in which the data file resides. Until you do so, the data file remains  in the data dictionary with the status  RECOVER  or  OFFLINE  .

SQL> alter database datafile 4 offline drop;
 
Database altered.
 
SQL> ho mv /u01/app/oradata/SUN/sun/users01.dbf /u01/app/oradata/SUN/
 
SQL> alter tablespace users rename datafile '/u01/app/oradata/SUN/sun/users01.dbf'
  2  to '/u01/app/oradata/SUN/users01.dbf';
 
Tablespace altered.
 
SQL> alter database datafile 4 online;
alter database datafile 4 online
*
ERROR at line 1:
ORA-01113: file 4 needs media recovery
ORA-01110: data file 4: '/u01/app/oradata/SUN/users01.dbf'

    此处再次需要recover，之前在FOR DROP选项的说明中已阐明原因。

SQL> recover datafile 4;
Media recovery complete.
SQL> alter database datafile 4 online;
 
Database altered.