1.脱机数据文件
用户可以将除system表空间的任何表空间offline
如果表空间offline对应的对应数据文件也会offine
测试
19:45:16 sys@ORADB11G> alter tablespace users offline;
Tablespace altered.
Tablespace altered.
查看表空间状态
19:43:51 sys@ORADB11G> select TABLESPACE_NAME,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
RMAN ONLINE
TSPITRS ONLINE
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
RMAN ONLINE
TSPITRS ONLINE
查看数据文件状态
19:47:21 sys@ORADB11G> select file_name,online_status from dba_data_files;
FILE_NAME ONLINE_
-------------------------------------------------- -------
/u01/app/oracle/oradata/ORADB11G/system01.dbf SYSTEM
/u01/app/oracle/oradata/ORADB11G/sysaux01.dbf ONLINE
/u01/app/oracle/oradata/ORADB11G/undotbs01.dbf ONLINE
/u01/app/oracle/oradata/ORADB11G/users01.dbf OFFLINE
/u01/app/oracle/oradata/ORADB11G/catalog.dbf ONLINE
/u01/app/oracle/oradata1/TSPITR01.dbf ONLINE
/u01/app/oracle/oradata/tspitr02.dbf ONLINE
/u01/app/oracle/product/11.2.0/db_1/dbs/user02.dbf OFFLINE
8 rows selected.
FILE_NAME ONLINE_
-------------------------------------------------- -------
/u01/app/oracle/oradata/ORADB11G/system01.dbf SYSTEM
/u01/app/oracle/oradata/ORADB11G/sysaux01.dbf ONLINE
/u01/app/oracle/oradata/ORADB11G/undotbs01.dbf ONLINE
/u01/app/oracle/oradata/ORADB11G/users01.dbf OFFLINE
/u01/app/oracle/oradata/ORADB11G/catalog.dbf ONLINE
/u01/app/oracle/oradata1/TSPITR01.dbf ONLINE
/u01/app/oracle/oradata/tspitr02.dbf ONLINE
/u01/app/oracle/product/11.2.0/db_1/dbs/user02.dbf OFFLINE
8 rows selected.
测试datafile offline 表空间会不会offline
19:53:19 sys@ORADB11G> alter database datafile '/u01/app/oracle/product/11.2.0/db_1/dbs/user02.dbf' offline;
Database altered.
Database altered.
数据文件状态为recover
19:54:46 sys@ORADB11G> select file_name,online_status from dba_data_files;
FILE_NAME ONLINE_
-------------------------------------------------- -------
/u01/app/oracle/oradata/ORADB11G/system01.dbf SYSTEM
/u01/app/oracle/oradata/ORADB11G/sysaux01.dbf ONLINE
/u01/app/oracle/oradata/ORADB11G/undotbs01.dbf ONLINE
/u01/app/oracle/oradata/ORADB11G/users01.dbf ONLINE
/u01/app/oracle/oradata/ORADB11G/catalog.dbf ONLINE
/u01/app/oracle/oradata1/TSPITR01.dbf ONLINE
/u01/app/oracle/oradata/tspitr02.dbf ONLINE
/u01/app/oracle/product/11.2.0/db_1/dbs/user02.dbf RECOVER
FILE_NAME ONLINE_
-------------------------------------------------- -------
/u01/app/oracle/oradata/ORADB11G/system01.dbf SYSTEM
/u01/app/oracle/oradata/ORADB11G/sysaux01.dbf ONLINE
/u01/app/oracle/oradata/ORADB11G/undotbs01.dbf ONLINE
/u01/app/oracle/oradata/ORADB11G/users01.dbf ONLINE
/u01/app/oracle/oradata/ORADB11G/catalog.dbf ONLINE
/u01/app/oracle/oradata1/TSPITR01.dbf ONLINE
/u01/app/oracle/oradata/tspitr02.dbf ONLINE
/u01/app/oracle/product/11.2.0/db_1/dbs/user02.dbf RECOVER
表空间状态还是online 可能有些人认为是因为还有数据文件没有offline
19:53:41 sys@ORADB11G> select TABLESPACE_NAME,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
RMAN ONLINE
TSPITRS ONLINE
7 rows selected.
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
RMAN ONLINE
TSPITRS ONLINE
7 rows selected.
现在将users表空间另外一个数据文件也offline
19:55:15 sys@ORADB11G> alter database datafile '/u01/app/oracle/oradata/ORADB11G/users01.dbf' offline;
Database altered.
Database altered.
此时表空间的状态还是online,所以数据文件的状态不会影响表空间的状态
19:55:51 sys@ORADB11G> select TABLESPACE_NAME,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
RMAN ONLINE
TSPITRS ONLINE
7 rows selected.
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
RMAN ONLINE
TSPITRS ONLINE
7 rows selected.
数据文件offline后所属的schema 不能进行DML,DDL操作
19:56:00 sys@ORADB11G> conn test/test
Connected.
20:00:21 test@ORADB11G> select * from test;
select * from test
*
ERROR at line 1:
ORA-00376: file 4 cannot be read at this time
ORA-01110: data file 4: '/u01/app/oracle/oradata/ORADB11G/users01.dbf'
Connected.
20:00:21 test@ORADB11G> select * from test;
select * from test
*
ERROR at line 1:
ORA-00376: file 4 cannot be read at this time
ORA-01110: data file 4: '/u01/app/oracle/oradata/ORADB11G/users01.dbf'
datafile online 需要recovery,但是tablespace 不需要的
如果不recovery 直接online就会有如下错误
20:03:12 sys@ORADB11G> alter database datafile '/u01/app/oracle/oradata/ORADB11G/users01.dbf' online;
alter database datafile '/u01/app/oracle/oradata/ORADB11G/users01.dbf' online
*
ERROR at line 1:
ORA-01113: file 4 needs media recovery
ORA-01110: data file 4: '/u01/app/oracle/oradata/ORADB11G/users01.dbf'
20:03:14 sys@ORADB11G> alter database datafile '/u01/app/oracle/product/11.2.0/db_1/dbs/user02.dbf' online;
alter database datafile '/u01/app/oracle/product/11.2.0/db_1/dbs/user02.dbf' online
*
ERROR at line 1:
ORA-01113: file 8 needs media recovery
ORA-01110: data file 8: '/u01/app/oracle/product/11.2.0/db_1/dbs/user02.dbf'
alter database datafile '/u01/app/oracle/oradata/ORADB11G/users01.dbf' online
*
ERROR at line 1:
ORA-01113: file 4 needs media recovery
ORA-01110: data file 4: '/u01/app/oracle/oradata/ORADB11G/users01.dbf'
20:03:14 sys@ORADB11G> alter database datafile '/u01/app/oracle/product/11.2.0/db_1/dbs/user02.dbf' online;
alter database datafile '/u01/app/oracle/product/11.2.0/db_1/dbs/user02.dbf' online
*
ERROR at line 1:
ORA-01113: file 8 needs media recovery
ORA-01110: data file 8: '/u01/app/oracle/product/11.2.0/db_1/dbs/user02.dbf'
recover datafile
RMAN> recover datafile 4;
Starting recover at 03-SEP-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=57 device type=DISK
starting media recovery
archived log for thread 1 with sequence 3095 is already on disk as file /u01/app/oracle/archive1/1_3095_817696401.dbf
archived log for thread 1 with sequence 3096 is already on disk as file /u01/app/oracle/archive1/1_3096_817696401.dbf
archived log for thread 1 with sequence 3097 is already on disk as file /u01/app/oracle/archive1/1_3097_817696401.dbf
archived log for thread 1 with sequence 3098 is already on disk as file /u01/app/oracle/archive1/1_3098_817696401.dbf
archived log for thread 1 with sequence 3099 is already on disk as file /u01/app/oracle/archive1/1_3099_817696401.dbf
archived log for thread 1 with sequence 3100 is already on disk as file /u01/app/oracle/archive1/1_3100_817696401.dbf
archived log for thread 1 with sequence 3101 is already on disk as file /u01/app/oracle/archive1/1_3101_817696401.dbf
archived log for thread 1 with sequence 3102 is already on disk as file /u01/app/oracle/archive1/1_3102_817696401.dbf
archived log for thread 1 with sequence 3103 is already on disk as file /u01/app/oracle/archive1/1_3103_817696401.dbf
archived log file name=/u01/app/oracle/archive1/1_3095_817696401.dbf thread=1 sequence=3095
archived log file name=/u01/app/oracle/archive1/1_3096_817696401.dbf thread=1 sequence=3096
media recovery complete, elapsed time: 00:00:00
Finished recover at 03-SEP-13
RMAN> recover datafile 8;
Starting recover at 03-SEP-13
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 3095 is already on disk as file /u01/app/oracle/archive1/1_3095_817696401.dbf
archived log for thread 1 with sequence 3096 is already on disk as file /u01/app/oracle/archive1/1_3096_817696401.dbf
archived log for thread 1 with sequence 3097 is already on disk as file /u01/app/oracle/archive1/1_3097_817696401.dbf
archived log for thread 1 with sequence 3098 is already on disk as file /u01/app/oracle/archive1/1_3098_817696401.dbf
archived log for thread 1 with sequence 3099 is already on disk as file /u01/app/oracle/archive1/1_3099_817696401.dbf
archived log for thread 1 with sequence 3100 is already on disk as file /u01/app/oracle/archive1/1_3100_817696401.dbf
archived log for thread 1 with sequence 3101 is already on disk as file /u01/app/oracle/archive1/1_3101_817696401.dbf
archived log for thread 1 with sequence 3102 is already on disk as file /u01/app/oracle/archive1/1_3102_817696401.dbf
archived log for thread 1 with sequence 3103 is already on disk as file /u01/app/oracle/archive1/1_3103_817696401.dbf
archived log file name=/u01/app/oracle/archive1/1_3095_817696401.dbf thread=1 sequence=3095
media recovery complete, elapsed time: 00:00:00
Finished recover at 03-SEP-13
Starting recover at 03-SEP-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=57 device type=DISK
starting media recovery
archived log for thread 1 with sequence 3095 is already on disk as file /u01/app/oracle/archive1/1_3095_817696401.dbf
archived log for thread 1 with sequence 3096 is already on disk as file /u01/app/oracle/archive1/1_3096_817696401.dbf
archived log for thread 1 with sequence 3097 is already on disk as file /u01/app/oracle/archive1/1_3097_817696401.dbf
archived log for thread 1 with sequence 3098 is already on disk as file /u01/app/oracle/archive1/1_3098_817696401.dbf
archived log for thread 1 with sequence 3099 is already on disk as file /u01/app/oracle/archive1/1_3099_817696401.dbf
archived log for thread 1 with sequence 3100 is already on disk as file /u01/app/oracle/archive1/1_3100_817696401.dbf
archived log for thread 1 with sequence 3101 is already on disk as file /u01/app/oracle/archive1/1_3101_817696401.dbf
archived log for thread 1 with sequence 3102 is already on disk as file /u01/app/oracle/archive1/1_3102_817696401.dbf
archived log for thread 1 with sequence 3103 is already on disk as file /u01/app/oracle/archive1/1_3103_817696401.dbf
archived log file name=/u01/app/oracle/archive1/1_3095_817696401.dbf thread=1 sequence=3095
archived log file name=/u01/app/oracle/archive1/1_3096_817696401.dbf thread=1 sequence=3096
media recovery complete, elapsed time: 00:00:00
Finished recover at 03-SEP-13
RMAN> recover datafile 8;
Starting recover at 03-SEP-13
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 3095 is already on disk as file /u01/app/oracle/archive1/1_3095_817696401.dbf
archived log for thread 1 with sequence 3096 is already on disk as file /u01/app/oracle/archive1/1_3096_817696401.dbf
archived log for thread 1 with sequence 3097 is already on disk as file /u01/app/oracle/archive1/1_3097_817696401.dbf
archived log for thread 1 with sequence 3098 is already on disk as file /u01/app/oracle/archive1/1_3098_817696401.dbf
archived log for thread 1 with sequence 3099 is already on disk as file /u01/app/oracle/archive1/1_3099_817696401.dbf
archived log for thread 1 with sequence 3100 is already on disk as file /u01/app/oracle/archive1/1_3100_817696401.dbf
archived log for thread 1 with sequence 3101 is already on disk as file /u01/app/oracle/archive1/1_3101_817696401.dbf
archived log for thread 1 with sequence 3102 is already on disk as file /u01/app/oracle/archive1/1_3102_817696401.dbf
archived log for thread 1 with sequence 3103 is already on disk as file /u01/app/oracle/archive1/1_3103_817696401.dbf
archived log file name=/u01/app/oracle/archive1/1_3095_817696401.dbf thread=1 sequence=3095
media recovery complete, elapsed time: 00:00:00
Finished recover at 03-SEP-13
重新online
20:03:39 sys@ORADB11G> alter database datafile '/u01/app/oracle/oradata/ORADB11G/users01.dbf' online;
Database altered.
20:23:31 sys@ORADB11G> alter database datafile '/u01/app/oracle/product/11.2.0/db_1/dbs/user02.dbf' online;
Database altered.
20:23:33 sys@ORADB11G>
Database altered.
20:23:31 sys@ORADB11G> alter database datafile '/u01/app/oracle/product/11.2.0/db_1/dbs/user02.dbf' online;
Database altered.
20:23:33 sys@ORADB11G>
总结:
alter datafile 不会触发checkpoint
alter tablespace offline会触发checkpoint