===============================
按表空间---移动数据文件(数据库打开,归档模式)
===============================
SQL> create tablespace t1 datafile '/u01/app/oracle/oradata/orcl/t1.dbf' size 1M;
SQL> create table test(id number) tablespace t1;
SQL> insert into test values(1);
SQL> commit;
SQL> select * from test;
ID
----------
1
SQL> select name from v$dbfile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/users01.dbf
/u01/app/oracle/oradata/orcl/undotbs01.dbf
/u01/app/oracle/oradata/orcl/sysaux01.dbf
/u01/app/oracle/oradata/orcl/system01.dbf
/u01/app/oracle/oradata/orcl/example01.dbf
/u01/app/oracle/oradata/orcl/t1.dbf
SQL> alter tablespace t1 offline;
SQL> col name for a45
SQL> select name,status from v$datafile;
NAME STATUS
--------------------------------------------- -------
/u01/app/oracle/oradata/orcl/system01.dbf SYSTEM
/u01/app/oracle/oradata/orcl/sysaux01.dbf ONLINE
/u01/app/oracle/oradata/orcl/undotbs01.dbf ONLINE
/u01/app/oracle/oradata/orcl/users01.dbf ONLINE
/u01/app/oracle/oradata/orcl/example01.dbf ONLINE
/u01/app/oracle/oradata/orcl/t1.dbf OFFLINE
[oracle@chen ~]$ cd /u01/app/oracle/oradata/orcl/
[oracle@chen orcl]$ mv t1.dbf /home/oracle/ttt.dbf
SQL> alter tablespace t1 rename datafile '/u01/app/oracle/oradata/orcl/t1.dbf' to '/home/oracle/ttt.dbf';
SQL> alter tablespace t1 online;
SQL> select status,name from v$datafile;
STATUS NAME
------- ---------------------------------------------
SYSTEM /u01/app/oracle/oradata/orcl/system01.dbf
ONLINE /u01/app/oracle/oradata/orcl/sysaux01.dbf
ONLINE /u01/app/oracle/oradata/orcl/undotbs01.dbf
ONLINE /u01/app/oracle/oradata/orcl/users01.dbf
ONLINE /u01/app/oracle/oradata/orcl/example01.dbf
ONLINE /home/oracle/ttt.dbf
SQL> select * from test;
ID
----------
1
================================================
按数据文件---移动数据文件(数据库打开,归档模式)
================================================
SQL> alter database datafile '/home/oracle/ttt.dbf' offline;
SQL> select status,name from v$datafile;
STATUS NAME
------- ---------------------------------------------
SYSTEM /u01/app/oracle/oradata/orcl/system01.dbf
ONLINE /u01/app/oracle/oradata/orcl/sysaux01.dbf
ONLINE /u01/app/oracle/oradata/orcl/undotbs01.dbf
ONLINE /u01/app/oracle/oradata/orcl/users01.dbf
ONLINE /u01/app/oracle/oradata/orcl/example01.dbf
RECOVER /home/oracle/ttt.dbf
[oracle@chen orcl]$ mv /home/oracle/ttt.dbf /u01/app/oracle/oradata/orcl/t1.dbf
SQL> alter database rename file '/home/oracle/ttt.dbf' to '/u01/app/oracle/oradata/orcl/t1.dbf';
SQL> recover datafile '/u01/app/oracle/oradata/orcl/t1.dbf';
Media recovery complete.
SQL> alter database datafile '/u01/app/oracle/oradata/orcl/t1.dbf' online;
SQL> select status,name from v$datafile;
STATUS NAME
------- ---------------------------------------------
SYSTEM /u01/app/oracle/oradata/orcl/system01.dbf
ONLINE /u01/app/oracle/oradata/orcl/sysaux01.dbf
ONLINE /u01/app/oracle/oradata/orcl/undotbs01.dbf
ONLINE /u01/app/oracle/oradata/orcl/users01.dbf
ONLINE /u01/app/oracle/oradata/orcl/example01.dbf
ONLINE /u01/app/oracle/oradata/orcl/t1.dbf
SQL> select * from test;
ID
----------
1
===================================
非归档模式,关闭数据库移动数据文件
===================================
SQL> shutdown immediate
[oracle@chen ~]$ cd /u01/app/oracle/oradata/orcl/
[oracle@chen orcl]$ mv t1.dbf /home/oracle/ttt.dbf
SQL> startup mount
SQL> alter database rename file '/u01/app/oracle/oradata/orcl/t1.dbf' to '/home/oracle/ttt.dbf';
SQL> alter database open;
SQL> col name for a45
SQL> select file#,name,status from v$datafile;
FILE# NAME STATUS
---------- --------------------------------------------- -------
1 /u01/app/oracle/oradata/orcl/system01.dbf SYSTEM
2 /u01/app/oracle/oradata/orcl/sysaux01.dbf ONLINE
3 /u01/app/oracle/oradata/orcl/undotbs01.dbf ONLINE
4 /u01/app/oracle/oradata/orcl/users01.dbf ONLINE
5 /u01/app/oracle/oradata/orcl/example01.dbf ONLINE
6 /home/oracle/ttt.dbf ONLINE
SQL> select * from test;
ID
----------
1
参考与:
http://blog.csdn.net/tianlesoftware/article/details/4899172
================
数据文件offline
================
归档模式下
alter database datafile '/home/oracle/t1.dbf' offline;
和
alter database datafile '/home/oracle/t1.dbf' offline drop; 一样
非归档模式下
只有
alter database datafile '/home/oracle/t1.dbf' offline drop;
没有
alter database datafile '/home/oracle/t1.dbf' offline;
该命令不会删除数据文件,只是将数据文件的状态更改为recover。 offline drop命令相当于把一个数据文件至于离线状态,并且需要恢复,并非删除数据文件。
数据文件的相关信息还会存在数据字典和控制文件中。
SQL> create tablespace card datafile '/home/oracle/card01.dbf' size 1M;
SQL> create user chen identified by chen default tablespace t1;
SQL> grant connect,resource,dba to chen;
SQL> create table test(id number);
SQL> declare
2 begin
3 for i in 1..5 loop
4 insert into test values(i);
5 end loop;
6 end;
7 /
SQL> col name for a45
SQL> select name,status from v$datafile;
NAME STATUS
--------------------------------------------- -------
/u01/app/oracle/oradata/orcl/system01.dbf SYSTEM
/u01/app/oracle/oradata/orcl/sysaux01.dbf ONLINE
/u01/app/oracle/oradata/orcl/undotbs01.dbf ONLINE
/u01/app/oracle/oradata/orcl/users01.dbf ONLINE
/u01/app/oracle/oradata/orcl/example01.dbf ONLINE
/home/oracle/t1.dbf ONLINE
SQL> alter database datafile '/home/oracle/t1.dbf' offline;
数据文件变为recover状态
SQL> select name,status from v$datafile;
NAME STATUS
--------------------------------------------- -------
/u01/app/oracle/oradata/orcl/system01.dbf SYSTEM
/u01/app/oracle/oradata/orcl/sysaux01.dbf ONLINE
/u01/app/oracle/oradata/orcl/undotbs01.dbf ONLINE
/u01/app/oracle/oradata/orcl/users01.dbf ONLINE
/u01/app/oracle/oradata/orcl/example01.dbf ONLINE
/home/oracle/t1.dbf RECOVER
SQL> select * from test;
select * from test
*
ERROR at line 1:
ORA-00376: file 6 cannot be read at this time
ORA-01110: data file 6: '/home/oracle/t1.dbf'
recover状态数据文件不能直接online
SQL> alter database datafile '/home/oracle/t1.dbf' online;
alter database datafile '/home/oracle/t1.dbf' online
*
ERROR at line 1:
ORA-01113: file 6 needs media recovery
ORA-01110: data file 6: '/home/oracle/t1.dbf'
需要先恢复
恢复需要利用当前日志或历史归档
数据库归档模式下,在删除归档之前,可以进行恢复
非归档模式下,只有当前日志没有被覆盖,才可以恢复
SQL> recover datafile 6;
Media recovery complete.
SQL> select name,status from v$datafile;
NAME STATUS
--------------------------------------------- -------
/u01/app/oracle/oradata/orcl/system01.dbf SYSTEM
/u01/app/oracle/oradata/orcl/sysaux01.dbf ONLINE
/u01/app/oracle/oradata/orcl/undotbs01.dbf ONLINE
/u01/app/oracle/oradata/orcl/users01.dbf ONLINE
/u01/app/oracle/oradata/orcl/example01.dbf ONLINE
/home/oracle/t1.dbf OFFLINE
SQL> alter database datafile '/home/oracle/t1.dbf' online;
SQL> select name,status from v$datafile;
NAME STATUS
--------------------------------------------- -------
/u01/app/oracle/oradata/orcl/system01.dbf SYSTEM
/u01/app/oracle/oradata/orcl/sysaux01.dbf ONLINE
/u01/app/oracle/oradata/orcl/undotbs01.dbf ONLINE
/u01/app/oracle/oradata/orcl/users01.dbf ONLINE
/u01/app/oracle/oradata/orcl/example01.dbf ONLINE
/home/oracle/t1.dbf ONLINE
无法删除offline状态的数据文件
SQL> alter tablespace t1 add datafile '/home/oracle/t2.dbf' size 1M;
SQL> alter database datafile '/home/oracle/t1.dbf' offline;
SQL> alter tablespace t1 drop datafile '/home/oracle/t1.dbf';
alter tablespace t1 drop datafile '/home/oracle/t1.dbf'
*
ERROR at line 1:
ORA-03263: cannot drop the first file of tablespace T1
SQL> alter tablespace t1 drop datafile '/home/oracle/t2.dbf';
==============
表空间offline
==============
表空间offline,表空间及其表空间内所以数据库都处于offline状态;
表空间online,表空间及其表空间内所以数据库都处于online状态;
表空间Offline时,数据文件的SCN会被冻结,而且表空间的数据文件offline/online时又会发生文件检查点,使单个数据文件SCN和数据库其他问题不一致。
表空间online时,Oracle会取得当前SCN,解冻offline文件SCN,和当前SCN同步。
单纯的offline datafile,将不会触发文件检查点,只有针对offline tablespace的时候才会触发文件检查点,这也是为什么online datafile需要media recovery而online tablespace不需要。
SQL> alter tablespace t1 offline;
SQL> select tablespace_name,status from user_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
EXAMPLE ONLINE
T1 OFFLINE
SQL> select name,status from v$datafile;
NAME STATUS
--------------------------------------------- -------
/u01/app/oracle/oradata/orcl/system01.dbf SYSTEM
/u01/app/oracle/oradata/orcl/sysaux01.dbf ONLINE
/u01/app/oracle/oradata/orcl/undotbs01.dbf ONLINE
/u01/app/oracle/oradata/orcl/users01.dbf ONLINE
/u01/app/oracle/oradata/orcl/example01.dbf ONLINE
/home/oracle/t1.dbf ONLINE
SQL> alter tablespace t1 online;
SQL> select tablespace_name,status from user_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
EXAMPLE ONLINE
T1 ONLINE
7 rows selected.
SQL> select name,status from v$datafile;
NAME STATUS
--------------------------------------------- -------
/u01/app/oracle/oradata/orcl/system01.dbf SYSTEM
/u01/app/oracle/oradata/orcl/sysaux01.dbf ONLINE
/u01/app/oracle/oradata/orcl/undotbs01.dbf ONLINE
/u01/app/oracle/oradata/orcl/users01.dbf ONLINE
/u01/app/oracle/oradata/orcl/example01.dbf ONLINE
/home/oracle/t1.dbf ONLINE
6 rows selected.
参考与:
http://blog.csdn.net/tianlesoftware/article/details/4899172
http://blog.csdn.net/tianlesoftware/archive/2009/11/29/4898800.aspx
http://www.cnblogs.com/sopost/p/3589731.html