测试表空间和数据文件脱机影响

1.脱机数据文件

用户可以将除system表空间的任何表空间offline

如果表空间offline对应的对应数据文件也会offine

测试

19:45:16 sys@ORADB11G> alter tablespace users offline;
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

 

查看数据文件状态

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.

测试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.

数据文件状态为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

 

表空间状态还是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.

 

 

现在将users表空间另外一个数据文件也offline

19:55:15 sys@ORADB11G> alter database datafile '/u01/app/oracle/oradata/ORADB11G/users01.dbf' offline;
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.

 

数据文件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'

 

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'

 

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

重新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>

总结:

alter datafile 不会触发checkpoint

alter tablespace offline会触发checkpoint