v$datafile.enabled相关值说明

于 2020-05-07 18:54:02 发布
阅读量500 收藏
点赞数
CC 4.0 BY-SA版权
分类专栏： 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/gshzh00/article/details/105979304
本文详细探讨了Oracle数据库中v$datafile.enabled视图字段在不同操作下的状态变化，包括数据文件离线、表空间离线及只读转换等场景下SCN的变化情况，揭示了数据文件状态与数据库检查点的关系。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

 

自认为对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

评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

G_G#

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值