联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
自认为对v$datafile视图算比较了解,但是今天看到一网友的v$datafile.enabled=DISABLED,我一时没有想出来是为什么,这里通过试验对v$datafile.enabled各种情况进行了试验并简单说明scn的变化情况
模拟环境(READ WRITE)
SQL> alter tablespace users add datafile '/u01/oracle/oradata/XFF/users02.dbf' size 10m;
Tablespace altered.
SQL> select file#,ts#,CHECKPOINT_CHANGE#,enabled,status,name from v$datafile;
FILE# TS# CHECKPOINT_CHANGE# ENABLED STATUS NAME
---------- ---------- ------------------ ---------- ------- ---------------------------------------
1 0 456727 READ WRITE SYSTEM /u01/oracle/oradata/XFF/system01.dbf
2 1 456727 READ WRITE ONLINE /u01/oracle/oradata/XFF/undotbs01.dbf
3 2 456727 READ WRITE ONLINE /u01/oracle/oradata/XFF/sysaux01.dbf
4 4 456727 READ WRITE ONLINE /u01/oracle/oradata/XFF/users01.dbf
5 4 458322 READ WRITE ONLINE /u01/oracle/oradata/XFF/users02.dbf
QL> select file#,status, CHECKPOINT_CHANGE# from v$datafile_header;
FILE# STATUS CHECKPOINT_CHANGE#
---------- ------- ------------------
1 ONLINE 456727
2 ONLINE 456727
3 ONLINE 456727
4 ONLINE 456727
5 ONLINE 458322
加入数据文件scn不一样是因为:加入数据文件的时候,使用是当前scn,而数据库未做checkpoint,所以出现数据文件scn不一致现象
datafile offline(READ WRITE)
SQL> alter database datafile 5 offline;
Database altered.
SQL> alter system checkpoint;
System altered.
SQL> /
System altered.
SQL> select file#,status, CHECKPOINT_CHANGE# from v$datafile_header;
FILE# STATUS CHECKPOINT_CHANGE#
---------- ------- ------------------
1 ONLINE 458392
2 ONLINE 458392
3 ONLINE 458392
4 ONLINE 458392
5 OFFLINE 458322
SQL> select file#,ts#,CHECKPOINT_CHANGE#,enabled,status,name from v$datafile;
FILE# TS# CHECKPOINT_CHANGE# ENABLED STATUS NAME
---------- ---------- ------------------ ---------- ------- ----------------------------------------
1 0 458392 READ WRITE SYSTEM /u01/oracle/oradata/XFF/system01.dbf
2 1 458392 READ WRITE ONLINE /u01/oracle/oradata/XFF/undotbs01.dbf
3 2 458392 READ WRITE ONLINE /u01/oracle/oradata/XFF/sysaux01.dbf
4 4 458392 READ WRITE ONLINE /u01/oracle/oradata/XFF/users01.dbf
5 4 458322 READ WRITE RECOVER /u01/oracle/oradata/XFF/users02.dbf
这里可以看出来数据文件offline之后,v$datafile.enabled依然是READ WRITE,但是该数据文件的scn不再变化
tablespace offline(DISABLED)
SQL> recover datafile 5;
Media recovery complete.
SQL> alter database datafile 5 online;
Database altered.
SQL> select file#,ts#,CHECKPOINT_CHANGE#,enabled,status,name from v$datafile;
FILE# TS# CHECKPOINT_CHANGE# ENABLED STATUS NAME
---------- ---------- ------------------ ---------- ------- --------------------------------------
1 0 458392 READ WRITE SYSTEM /u01/oracle/oradata/XFF/system01.dbf
2 1 458392 READ WRITE ONLINE /u01/oracle/oradata/XFF/undotbs01.dbf
3 2 458392 READ WRITE ONLINE /u01/oracle/oradata/XFF/sysaux01.dbf
4 4 458392 READ WRITE ONLINE /u01/oracle/oradata/XFF/users01.dbf
5 4 458430 READ WRITE ONLINE /u01/oracle/oradata/XFF/users02.dbf
SQL> select file#,status, CHECKPOINT_CHANGE# from v$datafile_header;
FILE# STATUS CHECKPOINT_CHANGE#
---------- ------- ------------------
1 ONLINE 458392
2 ONLINE 458392
3 ONLINE 458392
4 ONLINE 458392
5 ONLINE 458430
SQL> alter system checkpoint;
System altered.
SQL> select file#,ts#,CHECKPOINT_CHANGE#,enabled,status,name from v$datafile;
FILE# TS# CHECKPOINT_CHANGE# ENABLED STATUS NAME
---------- ---------- ------------------ ---------- ------- ---------------------------------------
1 0 458443 READ WRITE SYSTEM /u01/oracle/oradata/XFF/system01.dbf
2 1 458443 READ WRITE ONLINE /u01/oracle/oradata/XFF/undotbs01.dbf
3 2 458443 READ WRITE ONLINE /u01/oracle/oradata/XFF/sysaux01.dbf
4 4 458443 READ WRITE ONLINE /u01/oracle/oradata/XFF/users01.dbf
5 4 458443 READ WRITE ONLINE /u01/oracle/oradata/XFF/users02.dbf
SQL> select file#,status, CHECKPOINT_CHANGE# from v$datafile_header;
FILE# STATUS CHECKPOINT_CHANGE#
---------- ------- ------------------
1 ONLINE 458443
2 ONLINE 458443
3 ONLINE 458443
4 ONLINE 458443
5 ONLINE 458443
SQL> alter tablespace users offline;
Tablespace altered.
SQL> alter system checkpoint;
System altered.
SQL> /
System altered.
SQL> select file#,ts#,CHECKPOINT_CHANGE#,enabled,status,name from v$datafile;
FILE# TS# CHECKPOINT_CHANGE# ENABLED STATUS NAME
---------- ---------- ------------------ ---------- ------- --------------------------------------
1 0 458497 READ WRITE SYSTEM /u01/oracle/oradata/XFF/system01.dbf
2 1 458497 READ WRITE ONLINE /u01/oracle/oradata/XFF/undotbs01.dbf
3 2 458497 READ WRITE ONLINE /u01/oracle/oradata/XFF/sysaux01.dbf
4 4 458457 DISABLED OFFLINE /u01/oracle/oradata/XFF/users01.dbf
5 4 458457 DISABLED OFFLINE /u01/oracle/oradata/XFF/users02.dbf
SQL> select file#,status, CHECKPOINT_CHANGE# from v$datafile_header;
FILE# STATUS CHECKPOINT_CHANGE#
---------- ------- ------------------
1 ONLINE 458497
2 ONLINE 458497
3 ONLINE 458497
4 OFFLINE 0
5 OFFLINE 0
SQL> alter system checkpoint;
System altered.
SQL> /
System altered.
SQL> select file#,ts#,CHECKPOINT_CHANGE#,enabled,status,name from v$datafile;
FILE# TS# CHECKPOINT_CHANGE# ENABLED STATUS NAME
---------- ---------- ------------------ ---------- ------- --------------------------------------------------
1 0 458512 READ WRITE SYSTEM /u01/oracle/oradata/XFF/system01.dbf
2 1 458512 READ WRITE ONLINE /u01/oracle/oradata/XFF/undotbs01.dbf
3 2 458512 READ WRITE ONLINE /u01/oracle/oradata/XFF/sysaux01.dbf
4 4 458457 DISABLED OFFLINE /u01/oracle/oradata/XFF/users01.dbf
5 4 458457 DISABLED OFFLINE /u01/oracle/oradata/XFF/users02.dbf
SQL> select file#,status, CHECKPOINT_CHANGE# from v$datafile_header;
FILE# STATUS CHECKPOINT_CHANGE#
---------- ------- ------------------
1 ONLINE 458512
2 ONLINE 458512
3 ONLINE 458512
4 OFFLINE 0
5 OFFLINE 0
SQL> alter tablespace users online;
Tablespace altered.
SQL> select file#,status, CHECKPOINT_CHANGE# from v$datafile_header;
FILE# STATUS CHECKPOINT_CHANGE#
---------- ------- ------------------
1 ONLINE 458512
2 ONLINE 458512
3 ONLINE 458512
4 ONLINE 458526
5 ONLINE 458526
SQL> select file#,ts#,CHECKPOINT_CHANGE#,enabled,status,name from v$datafile;
FILE# TS# CHECKPOINT_CHANGE# ENABLED STATUS NAME
---------- ---------- ------------------ ---------- ------- --------------------------------------------------
1 0 458512 READ WRITE SYSTEM /u01/oracle/oradata/XFF/system01.dbf
2 1 458512 READ WRITE ONLINE /u01/oracle/oradata/XFF/undotbs01.dbf
3 2 458512 READ WRITE ONLINE /u01/oracle/oradata/XFF/sysaux01.dbf
4 4 458526 READ WRITE ONLINE /u01/oracle/oradata/XFF/users01.dbf
5 4 458526 READ WRITE ONLINE /u01/oracle/oradata/XFF/users02.dbf
SQL> alter system checkpoint;
System altered.
SQL> select file#,ts#,CHECKPOINT_CHANGE#,enabled,status,name from v$datafile;
FILE# TS# CHECKPOINT_CHANGE# ENABLED STATUS NAME
---------- ---------- ------------------ ---------- ------- --------------------------------------------------
1 0 458551 READ WRITE SYSTEM /u01/oracle/oradata/XFF/system01.dbf
2 1 458551 READ WRITE ONLINE /u01/oracle/oradata/XFF/undotbs01.dbf
3 2 458551 READ WRITE ONLINE /u01/oracle/oradata/XFF/sysaux01.dbf
4 4 458551 READ WRITE ONLINE /u01/oracle/oradata/XFF/users01.dbf
5 4 458551 READ WRITE ONLINE /u01/oracle/oradata/XFF/users02.dbf
SQL> select file#,status, CHECKPOINT_CHANGE# from v$datafile_header;
FILE# STATUS CHECKPOINT_CHANGE#
---------- ------- ------------------
1 ONLINE 458551
2 ONLINE 458551
3 ONLINE 458551
4 ONLINE 458551
5 ONLINE 458551
以上部分证明：
1.online datafile也不触发database checkpoint
2.tablespace offline后v$datafile.enabled为DISABLED
3.控制文件对应表空间scn不再变化,datafile header scn变为0
4.tablespace online不需要recover
tablespace read only(READ ONLY)
SQL> alter tablespace users read only;
Tablespace altered.
SQL> select file#,ts#,CHECKPOINT_CHANGE#,enabled,status,name from v$datafile;
FILE# TS# CHECKPOINT_CHANGE# ENABLED STATUS NAME
---------- ---------- ------------------ ---------- ------- --------------------------------------------------
1 0 458551 READ WRITE SYSTEM /u01/oracle/oradata/XFF/system01.dbf
2 1 458551 READ WRITE ONLINE /u01/oracle/oradata/XFF/undotbs01.dbf
3 2 458551 READ WRITE ONLINE /u01/oracle/oradata/XFF/sysaux01.dbf
4 4 458567 READ ONLY ONLINE /u01/oracle/oradata/XFF/users01.dbf
5 4 458567 READ ONLY ONLINE /u01/oracle/oradata/XFF/users02.dbf
SQL> select file#,status, CHECKPOINT_CHANGE# from v$datafile_header;
FILE# STATUS CHECKPOINT_CHANGE#
---------- ------- ------------------
1 ONLINE 458551
2 ONLINE 458551
3 ONLINE 458551
4 ONLINE 458567
5 ONLINE 458567
SQL> alter system checkpoint;
System altered.
SQL> select file#,ts#,CHECKPOINT_CHANGE#,enabled,status,name from v$datafile;
FILE# TS# CHECKPOINT_CHANGE# ENABLED STATUS NAME
---------- ---------- ------------------ ---------- ------- --------------------------------------------------
1 0 458581 READ WRITE SYSTEM /u01/oracle/oradata/XFF/system01.dbf
2 1 458581 READ WRITE ONLINE /u01/oracle/oradata/XFF/undotbs01.dbf
3 2 458581 READ WRITE ONLINE /u01/oracle/oradata/XFF/sysaux01.dbf
4 4 458567 READ ONLY ONLINE /u01/oracle/oradata/XFF/users01.dbf
5 4 458567 READ ONLY ONLINE /u01/oracle/oradata/XFF/users02.dbf
SQL> select file#,status, CHECKPOINT_CHANGE# from v$datafile_header;
FILE# STATUS CHECKPOINT_CHANGE#
---------- ------- ------------------
1 ONLINE 458581
2 ONLINE 458581
3 ONLINE 458581
4 ONLINE 458567
5 ONLINE 458567
SQL> alter tablespace users read write;
Tablespace altered.
SQL> select file#,ts#,CHECKPOINT_CHANGE#,enabled,status,name from v$datafile;
FILE# TS# CHECKPOINT_CHANGE# ENABLED STATUS NAME
---------- ---------- ------------------ ---------- ------- --------------------------------------------------
1 0 458581 READ WRITE SYSTEM /u01/oracle/oradata/XFF/system01.dbf
2 1 458581 READ WRITE ONLINE /u01/oracle/oradata/XFF/undotbs01.dbf
3 2 458581 READ WRITE ONLINE /u01/oracle/oradata/XFF/sysaux01.dbf
4 4 458635 READ WRITE ONLINE /u01/oracle/oradata/XFF/users01.dbf
5 4 458635 READ WRITE ONLINE /u01/oracle/oradata/XFF/users02.dbf
SQL> select file#,status, CHECKPOINT_CHANGE# from v$datafile_header;
FILE# STATUS CHECKPOINT_CHANGE#
---------- ------- ------------------
1 ONLINE 458581
2 ONLINE 458581
3 ONLINE 458581
4 ONLINE 458635
5 ONLINE 458635
SQL> alter system checkpoint;
System altered.
SQL> select file#,ts#,CHECKPOINT_CHANGE#,enabled,status,name from v$datafile;
FILE# TS# CHECKPOINT_CHANGE# ENABLED STATUS NAME
---------- ---------- ------------------ ---------- ------- --------------------------------------------------
1 0 458649 READ WRITE SYSTEM /u01/oracle/oradata/XFF/system01.dbf
2 1 458649 READ WRITE ONLINE /u01/oracle/oradata/XFF/undotbs01.dbf
3 2 458649 READ WRITE ONLINE /u01/oracle/oradata/XFF/sysaux01.dbf
4 4 458649 READ WRITE ONLINE /u01/oracle/oradata/XFF/users01.dbf
5 4 458649 READ WRITE ONLINE /u01/oracle/oradata/XFF/users02.dbf
SQL> select file#,status, CHECKPOINT_CHANGE# from v$datafile_header;
FILE# STATUS CHECKPOINT_CHANGE#
---------- ------- ------------------
1 ONLINE 458649
2 ONLINE 458649
3 ONLINE 458649
4 ONLINE 458649
5 ONLINE 458649
以上部分证明：
1.tablespace read only 对应的v$datafile.enabled为READ ONLY
2.tablespace read only与tablespace read write之间的转换也不会触发database checkpoint
补充说明
官方文档给出来的v$datafile.enabled有以下几种
DISABLED – No SQL access allowed
READ ONLY – No SQL updates allowed
READ WRITE – Full access allowed
UNKNOWN – should not occur unless the control file is corrupted
但是对于UNKNOWN我通过模拟重建控制文件,缺少数据文件(为READ WRITE),通过在线删除数据文件(为READ WRITE),都不能出现UNKNOWN状态,如果知道的朋友请告知我.