一、归档模式
方法一:
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_clausesONLINE | OFFLINESpecify 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_clauseOFFLINESpecify 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.
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.