oracle数据库offline,oracle 数据文件offline

===============================

按表空间---移动数据文件(数据库打开，归档模式)

===============================

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