v$datafile.enabled相关值说明

最新推荐文章于 2024-08-17 20:59:34 发布

G_G#

最新推荐文章于 2024-08-17 20:59:34 发布

阅读量445

点赞数

分类专栏：数据库

本文链接：https://blog.csdn.net/gshzh00/article/details/105979304

版权

数据库专栏收录该内容

21 篇文章 0 订阅

订阅专栏

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