将单个数据文件offline或online

语法：

ALTER DATABASE DATAFILE filenumber或'filename' ONLINE/OFFLINE [FOR DROP];

The DATAFILE clause lets you manipulate a file that you identify by name or by number. If you identify it by number, then filenumber is an integer representing the number found in the FILE# column of the V$DATAFILE dynamic performance view or in the FILE_ID column of the DBA_DATA_FILES data dictionary view. The DATAFILE clauses affect your database files as follows:

ONLINE Specify ONLINE to bring the data file online.

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.

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.

If the database is in ARCHIVELOG mode, then Oracle Database ignores the FOR DROP clause.

实验一：

验证 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> select status from v$Instance;

STATUS
------------
OPEN

SQL> select file#,name,ts#,status,checkpoint_change#,last_change#,offline_change# from v$datafile;

     FILE# NAME                                                      TS# STATUS  CHECKPOINT_CHANGE# LAST_CHANGE# OFFLINE_CHANGE#
---------- -------------------------------------------------- ---------- ------- ------------------ ------------ ---------------
         1 /oracle/oradata/demo/system01.dbf                           0 SYSTEM              244683                            0
         2 /oracle/oradata/demo/undotbs01.dbf                          1 ONLINE              244683                            0
         3 /oracle/oradata/demo/sysaux01.dbf                           2 ONLINE              244683                            0
         4 /oracle/oradata/demo/users01.dbf                            4 ONLINE              244683                            0
         5 /oracle/oradata/demo/lxtbs01.dbf                            5 ONLINE              244683                       243654
         7 /oracle/oradata/demo/testtbs01.dbf                          6 ONLINE              244683                            0
         8 /oracle/oradata/demo/testtbs02.dbf                          6 ONLINE              244794                            0     -- offline之前，checkpoint scn=244794

7 rows selected.

SQL> alter database datafile 8 offline;
alter database datafile 8 offline
*
ERROR at line 1:
ORA-01145: offline immediate disallowed unless media recovery enabled

SQL> alter database datafile 8 offline for drop;  --我的数据库是noarchivelog模式，所以此处必须指定for drop

Database altered.

SQL> select file#,name,ts#,status,checkpoint_change#,last_change#,offline_change# from v$datafile;

     FILE# NAME                                                      TS# STATUS  CHECKPOINT_CHANGE# LAST_CHANGE# OFFLINE_CHANGE#
---------- -------------------------------------------------- ---------- ------- ------------------ ------------ ---------------
         1 /oracle/oradata/demo/system01.dbf                           0 SYSTEM              244683                            0
         2 /oracle/oradata/demo/undotbs01.dbf                          1 ONLINE              244683                            0
         3 /oracle/oradata/demo/sysaux01.dbf                           2 ONLINE              244683                            0
         4 /oracle/oradata/demo/users01.dbf                            4 ONLINE              244683                            0
         5 /oracle/oradata/demo/lxtbs01.dbf                            5 ONLINE              244683                       243654
         7 /oracle/oradata/demo/testtbs01.dbf                          6 ONLINE              244683                            0
         8 /oracle/oradata/demo/testtbs02.dbf                          6 RECOVER             244794       244928               0    -- offline之后，checkpoint scn=244794，没有改变，所以没有在文件上发生检查点

7 rows selected.
由于没有在该文件上发生检查点，所以文件8的CHECKPOINT_CHANGE#和LAST_CHANGE#有了差值，所以在下次online之前，必须要进行recover

SQL> select * from v$recover_file;

     FILE# ONLINE  ONLINE_ ERROR                                                                CHANGE# TIME
---------- ------- ------- ----------------------------------------------------------------- ---------- -------------------
         8 OFFLINE OFFLINE                                                                       244794 2014-06-08 18:48:15

SQL> alter database datafile 8 online;
alter database datafile 8 online
*
ERROR at line 1:
ORA-01113: file 8 needs media recovery  --提示要先进行介质恢复
ORA-01110: data file 8: '/oracle/oradata/demo/testtbs02.dbf'

SQL> recover datafile 8
Media recovery complete.
SQL> select file#,name,ts#,status,checkpoint_change#,last_change#,offline_change# from v$datafile;

     FILE# NAME                                                      TS# STATUS  CHECKPOINT_CHANGE# LAST_CHANGE# OFFLINE_CHANGE#
---------- -------------------------------------------------- ---------- ------- ------------------ ------------ ---------------
         1 /oracle/oradata/demo/system01.dbf                           0 SYSTEM              244683                            0
         2 /oracle/oradata/demo/undotbs01.dbf                          1 ONLINE              244683                            0
         3 /oracle/oradata/demo/sysaux01.dbf                           2 ONLINE              244683                            0
         4 /oracle/oradata/demo/users01.dbf                            4 ONLINE              244683                            0
         5 /oracle/oradata/demo/lxtbs01.dbf                            5 ONLINE              244683                       243654
         7 /oracle/oradata/demo/testtbs01.dbf                          6 ONLINE              244683                            0
         8 /oracle/oradata/demo/testtbs02.dbf                          6 OFFLINE             244928       244928               0  -- 恢复之后，CHECKPOINT_CHANGE#和LAST_CHANGE#保持了一致

7 rows selected.

SQL> select * from v$recover_file;

no rows selected

SQL> alter database datafile 8 online;

Database altered.

SQL> select file#,name,ts#,status,checkpoint_change#,last_change#,offline_change# from v$datafile;

     FILE# NAME                                                      TS# STATUS  CHECKPOINT_CHANGE# LAST_CHANGE# OFFLINE_CHANGE#
---------- -------------------------------------------------- ---------- ------- ------------------ ------------ ---------------
         1 /oracle/oradata/demo/system01.dbf                           0 SYSTEM              244683                            0
         2 /oracle/oradata/demo/undotbs01.dbf                          1 ONLINE              244683                            0
         3 /oracle/oradata/demo/sysaux01.dbf                           2 ONLINE              244683                            0
         4 /oracle/oradata/demo/users01.dbf                            4 ONLINE              244683                            0
         5 /oracle/oradata/demo/lxtbs01.dbf                            5 ONLINE              244683                       243654
         7 /oracle/oradata/demo/testtbs01.dbf                          6 ONLINE              244683                            0
         8 /oracle/oradata/demo/testtbs02.dbf                          6 ONLINE              245433                            0

7 rows selected.