v$datafile和v$datafile_header以及scn的关系

set pages 1000 lines 1000 
col file# for 99999999999
col CHECKPOINT_CHANGE# for 99999999999
col NAME for a60
col LAST_CHANGE# for 99999999999999
select FILE# , CHECKPOINT_CHANGE#        ,NAME, LAST_CHANGE# from v$datafile ;
 来源于控制文件
 
select STATUS,RECOVER, CHECKPOINT_CHANGE# ,NAME from v$datafile_header ;

来源于数据文件头

SQL> set pages 1000 lines 1000 
SQL> col file# for 99999999999
SQL> col CHECKPOINT_CHANGE# for 99999999999
col NAME for a60
col LAST_CHANGE# for 99999999999999
SQL> SQL> SQL> select FILE# , CHECKPOINT_CHANGE#        ,NAME, LAST_CHANGE# from v$datafile ;

       FILE# CHECKPOINT_CHANGE# NAME                                                            LAST_CHANGE#
------------ ------------------ ------------------------------------------------------------ ---------------
           1           12075457 +DATADG/hisdb/datafile/system.262.1096198825
           2           12075457 +DATADG/hisdb/datafile/sysaux.263.1096198835
           3           12075457 +DATADG/hisdb/datafile/undotbs1.264.1096198845
           4           12075457 +DATADG/hisdb/datafile/undotbs2.266.1096198857
           5           12075457 +DATADG/hisdb/datafile/users.267.1096198861
           6           12075457 +DATADG/hisdb/datafile/soe.273.1096202257

6 rows selected.

SQL> select STATUS,RECOVER, CHECKPOINT_CHANGE# ,NAME from v$datafile_header ;

STATUS         RECOVE CHECKPOINT_CHANGE# NAME
-------------- ------ ------------------ ------------------------------------------------------------
ONLINE         NO               12075457 +DATADG/hisdb/datafile/system.262.1096198825
ONLINE         NO               12075457 +DATADG/hisdb/datafile/sysaux.263.1096198835
ONLINE         NO               12075457 +DATADG/hisdb/datafile/undotbs1.264.1096198845
ONLINE         NO               12075457 +DATADG/hisdb/datafile/undotbs2.266.1096198857
ONLINE         NO               12075457 +DATADG/hisdb/datafile/users.267.1096198861
ONLINE         NO               12075457 +DATADG/hisdb/datafile/soe.273.1096202257

6 rows selected.

SQL> 

此时的数据库是处于alter database begin backup;模式

SQL> alter database datafile 6 offline ;

Database altered.

SQL> select FILE# , CHECKPOINT_CHANGE#        ,NAME, LAST_CHANGE# from v$datafile ;

       FILE# CHECKPOINT_CHANGE# NAME                                                            LAST_CHANGE#
------------ ------------------ ------------------------------------------------------------ ---------------
           1           12075457 +DATADG/hisdb/datafile/system.262.1096198825
           2           12075457 +DATADG/hisdb/datafile/sysaux.263.1096198835
           3           12075457 +DATADG/hisdb/datafile/undotbs1.264.1096198845
           4           12075457 +DATADG/hisdb/datafile/undotbs2.266.1096198857
           5           12075457 +DATADG/hisdb/datafile/users.267.1096198861
           6           12075457 +DATADG/hisdb/datafile/soe.273.1096202257                           12464245

6 rows selected.

SQL>  select STATUS,RECOVER, CHECKPOINT_CHANGE# ,NAME from v$datafile_header ;

STATUS         RECOVE CHECKPOINT_CHANGE# NAME
-------------- ------ ------------------ ------------------------------------------------------------
ONLINE         NO               12075457 +DATADG/hisdb/datafile/system.262.1096198825
ONLINE         NO               12075457 +DATADG/hisdb/datafile/sysaux.263.1096198835
ONLINE         NO               12075457 +DATADG/hisdb/datafile/undotbs1.264.1096198845
ONLINE         NO               12075457 +DATADG/hisdb/datafile/undotbs2.266.1096198857
ONLINE         NO               12075457 +DATADG/hisdb/datafile/users.267.1096198861
OFFLINE        YES              12075457 +DATADG/hisdb/datafile/soe.273.1096202257

6 rows selected.

SQL> 

此时datafile 6显示需要recover，注意控制文件的last_change#显示了datafile 6要恢复到的scn点是12464245，这个值并不是当前数据库v$database中的scn点；

SQL> alter database datafile 6 online ;
alter database datafile 6 online
*
ERROR at line 1:
ORA-01113: file 6 needs media recovery
ORA-01110: data file 6: '+DATADG/hisdb/datafile/soe.273.1096202257'

oracle@rac2[/home/oracle]$rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Tue Mar 21 14:03:23 2023

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: HISDB (DBID=2008814877)

RMAN> recover datafile 6 until scn 12464245 ;

Starting recover at 21-MAR-23
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=301 instance=hisdb2 device type=DISK

starting media recovery

archived log for thread 2 with sequence 9 is already on disk as file +DATADG/hisdb/archivelog/2023_03_11/thread_2_seq_9.375.1131213617
archived log for thread 2 with sequence 10 is already on disk as file +DATADG/hisdb/archivelog/2023_03_12/thread_2_seq_10.376.1131249611
archived log for thread 2 with sequence 11 is already on disk as file +DATADG/hisdb/archivelog/2023_03_12/thread_2_seq_11.377.1131300009
archived log for thread 2 with sequence 12 is already on disk as file +DATADG/hisdb/archivelog/2023_03_13/thread_2_seq_12.378.1131350415
archived log for thread 2 with sequence 13 is already on disk as file +DATADG/hisdb/archivelog/2023_03_13/thread_2_seq_13.379.1131403809
archived log for thread 2 with sequence 14 is already on disk as file +DATADG/hisdb/archivelog/2023_03_14/thread_2_seq_14.380.1131451263
archived log for thread 2 with sequence 15 is already on disk as file +DATADG/hisdb/archivelog/2023_03_15/thread_2_seq_15.381.1131501659
archived log for thread 2 with sequence 16 is already on disk as file +DATADG/hisdb/archivelog/2023_03_15/thread_2_seq_16.382.1131552349
archived log for thread 2 with sequence 17 is already on disk as file +DATADG/hisdb/archivelog/2023_03_16/thread_2_seq_17.383.1131606019
archived log for thread 2 with sequence 18 is already on disk as file +DATADG/hisdb/archivelog/2023_03_16/thread_2_seq_18.384.1131660023
archived log for thread 2 with sequence 19 is already on disk as file +DATADG/hisdb/archivelog/2023_03_17/thread_2_seq_19.385.1131714039
archived log for thread 2 with sequence 20 is already on disk as file +DATADG/hisdb/archivelog/2023_03_18/thread_2_seq_20.386.1131764437
archived log for thread 2 with sequence 21 is already on disk as file +DATADG/hisdb/archivelog/2023_03_18/thread_2_seq_21.387.1131818421
archived log for thread 2 with sequence 22 is already on disk as file +DATADG/hisdb/archivelog/2023_03_19/thread_2_seq_22.388.1131872451
archived log for thread 2 with sequence 23 is already on disk as file +DATADG/hisdb/archivelog/2023_03_20/thread_2_seq_23.389.1131926443
archived log for thread 2 with sequence 24 is already on disk as file +DATADG/hisdb/archivelog/2023_03_20/thread_2_seq_24.390.1131929771
archived log for thread 2 with sequence 25 is already on disk as file +DATADG/hisdb/archivelog/2023_03_20/thread_2_seq_25.391.1131987641
archived log for thread 2 with sequence 26 is already on disk as file +DATADG/hisdb/archivelog/2023_03_20/thread_2_seq_26.392.1132010949
archived log for thread 2 with sequence 27 is already on disk as file +DATADG/hisdb/archivelog/2023_03_20/thread_2_seq_27.393.1132012187
archived log for thread 2 with sequence 28 is already on disk as file +DATADG/hisdb/archivelog/2023_03_20/thread_2_seq_28.394.1132012189
archived log for thread 2 with sequence 29 is already on disk as file +DATADG/hisdb/archivelog/2023_03_20/thread_2_seq_29.395.1132012189
archived log for thread 2 with sequence 30 is already on disk as file +DATADG/hisdb/archivelog/2023_03_20/thread_2_seq_30.396.1132012191
archived log for thread 2 with sequence 31 is already on disk as file +DATADG/hisdb/archivelog/2023_03_20/thread_2_seq_31.397.1132012191
archived log for thread 2 with sequence 32 is already on disk as file +DATADG/hisdb/archivelog/2023_03_20/thread_2_seq_32.398.1132012193
archived log for thread 2 with sequence 33 is already on disk as file +DATADG/hisdb/archivelog/2023_03_21/thread_2_seq_33.399.1132013507
archived log file name=+DATADG/hisdb/archivelog/2023_03_11/thread_2_seq_9.375.1131213617 thread=2 sequence=9
archived log file name=+DATADG/hisdb/archivelog/2023_03_12/thread_2_seq_10.376.1131249611 thread=2 sequence=10
archived log file name=+DATADG/hisdb/archivelog/2023_03_12/thread_2_seq_11.377.1131300009 thread=2 sequence=11
archived log file name=+DATADG/hisdb/archivelog/2023_03_13/thread_2_seq_12.378.1131350415 thread=2 sequence=12
archived log file name=+DATADG/hisdb/archivelog/2023_03_13/thread_2_seq_13.379.1131403809 thread=2 sequence=13
archived log file name=+DATADG/hisdb/archivelog/2023_03_14/thread_2_seq_14.380.1131451263 thread=2 sequence=14
archived log file name=+DATADG/hisdb/archivelog/2023_03_15/thread_2_seq_15.381.1131501659 thread=2 sequence=15
archived log file name=+DATADG/hisdb/archivelog/2023_03_15/thread_2_seq_16.382.1131552349 thread=2 sequence=16
archived log file name=+DATADG/hisdb/archivelog/2023_03_16/thread_2_seq_17.383.1131606019 thread=2 sequence=17
archived log file name=+DATADG/hisdb/archivelog/2023_03_16/thread_2_seq_18.384.1131660023 thread=2 sequence=18
archived log file name=+DATADG/hisdb/archivelog/2023_03_17/thread_2_seq_19.385.1131714039 thread=2 sequence=19
archived log file name=+DATADG/hisdb/archivelog/2023_03_18/thread_2_seq_20.386.1131764437 thread=2 sequence=20
archived log file name=+DATADG/hisdb/archivelog/2023_03_18/thread_2_seq_21.387.1131818421 thread=2 sequence=21
archived log file name=+DATADG/hisdb/archivelog/2023_03_19/thread_2_seq_22.388.1131872451 thread=2 sequence=22
archived log file name=+DATADG/hisdb/archivelog/2023_03_20/thread_2_seq_23.389.1131926443 thread=2 sequence=23
archived log file name=+DATADG/hisdb/archivelog/2023_03_20/thread_2_seq_24.390.1131929771 thread=2 sequence=24
archived log file name=+DATADG/hisdb/archivelog/2023_03_20/thread_2_seq_25.391.1131987641 thread=2 sequence=25
archived log file name=+DATADG/hisdb/archivelog/2023_03_20/thread_2_seq_26.392.1132010949 thread=2 sequence=26
archived log file name=+DATADG/hisdb/archivelog/2023_03_20/thread_2_seq_27.393.1132012187 thread=2 sequence=27
archived log file name=+DATADG/hisdb/archivelog/2023_03_20/thread_2_seq_28.394.1132012189 thread=2 sequence=28
archived log file name=+DATADG/hisdb/archivelog/2023_03_20/thread_2_seq_29.395.1132012189 thread=2 sequence=29
archived log file name=+DATADG/hisdb/archivelog/2023_03_20/thread_2_seq_30.396.1132012191 thread=2 sequence=30
archived log file name=+DATADG/hisdb/archivelog/2023_03_20/thread_2_seq_31.397.1132012191 thread=2 sequence=31
archived log file name=+DATADG/hisdb/archivelog/2023_03_20/thread_2_seq_32.398.1132012193 thread=2 sequence=32
media recovery complete, elapsed time: 00:00:13
Finished recover at 21-MAR-23

RMAN> 

SQL> set pages 1000 lines 1000 
col file# for 99999999999
SQL> SQL> col CHECKPOINT_CHANGE# for 99999999999
col NAME for a60
col LAST_CHANGE# for 99999999999999
SQL> SQL> SQL> select FILE# , CHECKPOINT_CHANGE#        ,NAME, LAST_CHANGE# from v$datafile ;

       FILE# CHECKPOINT_CHANGE# NAME                                                            LAST_CHANGE#
------------ ------------------ ------------------------------------------------------------ ---------------
           1           12075457 +DATADG/hisdb/datafile/system.262.1096198825
           2           12075457 +DATADG/hisdb/datafile/sysaux.263.1096198835
           3           12075457 +DATADG/hisdb/datafile/undotbs1.264.1096198845
           4           12075457 +DATADG/hisdb/datafile/undotbs2.266.1096198857
           5           12075457 +DATADG/hisdb/datafile/users.267.1096198861
           6           12464245 +DATADG/hisdb/datafile/soe.273.1096202257                           12464245

6 rows selected.

SQL> 
SQL> 
SQL> select STATUS,RECOVER, CHECKPOINT_CHANGE# ,NAME from v$datafile_header ;

STATUS         RECOVE CHECKPOINT_CHANGE# NAME
-------------- ------ ------------------ ------------------------------------------------------------
ONLINE         NO               12075457 +DATADG/hisdb/datafile/system.262.1096198825
ONLINE         NO               12075457 +DATADG/hisdb/datafile/sysaux.263.1096198835
ONLINE         NO               12075457 +DATADG/hisdb/datafile/undotbs1.264.1096198845
ONLINE         NO               12075457 +DATADG/hisdb/datafile/undotbs2.266.1096198857
ONLINE         NO               12075457 +DATADG/hisdb/datafile/users.267.1096198861
OFFLINE        NO               12464245 +DATADG/hisdb/datafile/soe.273.1096202257

6 rows selected.

SQL> 
SQL> alter database datafile 6 online ;

Database altered.

SQL> select FILE# , CHECKPOINT_CHANGE#        ,NAME, LAST_CHANGE# from v$datafile ;

       FILE# CHECKPOINT_CHANGE# NAME                                                            LAST_CHANGE#
------------ ------------------ ------------------------------------------------------------ ---------------
           1           12075457 +DATADG/hisdb/datafile/system.262.1096198825
           2           12075457 +DATADG/hisdb/datafile/sysaux.263.1096198835
           3           12075457 +DATADG/hisdb/datafile/undotbs1.264.1096198845
           4           12075457 +DATADG/hisdb/datafile/undotbs2.266.1096198857
           5           12075457 +DATADG/hisdb/datafile/users.267.1096198861
           6           12464540 +DATADG/hisdb/datafile/soe.273.1096202257

6 rows selected.

SQL> 
SQL>  select STATUS,RECOVER, CHECKPOINT_CHANGE# ,NAME from v$datafile_header ;

STATUS         RECOVE CHECKPOINT_CHANGE# NAME
-------------- ------ ------------------ ------------------------------------------------------------
ONLINE         NO               12075457 +DATADG/hisdb/datafile/system.262.1096198825
ONLINE         NO               12075457 +DATADG/hisdb/datafile/sysaux.263.1096198835
ONLINE         NO               12075457 +DATADG/hisdb/datafile/undotbs1.264.1096198845
ONLINE         NO               12075457 +DATADG/hisdb/datafile/undotbs2.266.1096198857
ONLINE         NO               12075457 +DATADG/hisdb/datafile/users.267.1096198861
ONLINE         NO               12464540 +DATADG/hisdb/datafile/soe.273.1096202257

6 rows selected.

SQL> alter tablespace users read only ;
alter tablespace users read only
*
ERROR at line 1:
ORA-01150: cannot prevent writes - file 5 has online backup set
ORA-01110: data file 5: '+DATADG/hisdb/datafile/users.267.1096198861'

SQL> alter tablespace users end backup ;

Tablespace altered.

SQL> alter tablespace users read only ;

Tablespace altered.

SQL> select STATUS,RECOVER, CHECKPOINT_CHANGE# ,NAME from v$datafile_header ;

STATUS         RECOVE CHECKPOINT_CHANGE# NAME
-------------- ------ ------------------ ------------------------------------------------------------
ONLINE         NO               12075457 +DATADG/hisdb/datafile/system.262.1096198825
ONLINE         NO               12075457 +DATADG/hisdb/datafile/sysaux.263.1096198835
ONLINE         NO               12075457 +DATADG/hisdb/datafile/undotbs1.264.1096198845
ONLINE         NO               12075457 +DATADG/hisdb/datafile/undotbs2.266.1096198857
ONLINE         NO               12464787 +DATADG/hisdb/datafile/users.267.1096198861
ONLINE         NO               12464540 +DATADG/hisdb/datafile/soe.273.1096202257

6 rows selected.

SQL> select FILE# , CHECKPOINT_CHANGE#        ,NAME, LAST_CHANGE# from v$datafile ;

       FILE# CHECKPOINT_CHANGE# NAME                                                            LAST_CHANGE#
------------ ------------------ ------------------------------------------------------------ ---------------
           1           12075457 +DATADG/hisdb/datafile/system.262.1096198825
           2           12075457 +DATADG/hisdb/datafile/sysaux.263.1096198835
           3           12075457 +DATADG/hisdb/datafile/undotbs1.264.1096198845
           4           12075457 +DATADG/hisdb/datafile/undotbs2.266.1096198857
           5           12464787 +DATADG/hisdb/datafile/users.267.1096198861                         12464787
           6           12464540 +DATADG/hisdb/datafile/soe.273.1096202257

6 rows selected.

SQL> alter tablespace users  read write ;

Tablespace altered.

SQL> select STATUS,RECOVER, CHECKPOINT_CHANGE# ,NAME from v$datafile_header ;

STATUS         RECOVE CHECKPOINT_CHANGE# NAME
-------------- ------ ------------------ ------------------------------------------------------------
ONLINE         NO               12075457 +DATADG/hisdb/datafile/system.262.1096198825
ONLINE         NO               12075457 +DATADG/hisdb/datafile/sysaux.263.1096198835
ONLINE         NO               12075457 +DATADG/hisdb/datafile/undotbs1.264.1096198845
ONLINE         NO               12075457 +DATADG/hisdb/datafile/undotbs2.266.1096198857
ONLINE         NO               12464843 +DATADG/hisdb/datafile/users.267.1096198861
ONLINE         NO               12464540 +DATADG/hisdb/datafile/soe.273.1096202257

6 rows selected.

SQL> 
SQL> select FILE# , CHECKPOINT_CHANGE#        ,NAME, LAST_CHANGE# from v$datafile ;

       FILE# CHECKPOINT_CHANGE# NAME                                                            LAST_CHANGE#
------------ ------------------ ------------------------------------------------------------ ---------------
           1           12075457 +DATADG/hisdb/datafile/system.262.1096198825
           2           12075457 +DATADG/hisdb/datafile/sysaux.263.1096198835
           3           12075457 +DATADG/hisdb/datafile/undotbs1.264.1096198845
           4           12075457 +DATADG/hisdb/datafile/undotbs2.266.1096198857
           5           12464843 +DATADG/hisdb/datafile/users.267.1096198861
           6           12464540 +DATADG/hisdb/datafile/soe.273.1096202257

6 rows selected.

SQL>  alter tablespace users offline normal; 

Tablespace altered.

SQL> select STATUS,RECOVER, CHECKPOINT_CHANGE# ,NAME from v$datafile_header ;

STATUS         RECOVE CHECKPOINT_CHANGE# NAME
-------------- ------ ------------------ ------------------------------------------------------------
ONLINE         NO               12075457 +DATADG/hisdb/datafile/system.262.1096198825
ONLINE         NO               12075457 +DATADG/hisdb/datafile/sysaux.263.1096198835
ONLINE         NO               12075457 +DATADG/hisdb/datafile/undotbs1.264.1096198845
ONLINE         NO               12075457 +DATADG/hisdb/datafile/undotbs2.266.1096198857
OFFLINE                                0
ONLINE         NO               12464540 +DATADG/hisdb/datafile/soe.273.1096202257

6 rows selected.

SQL> select FILE# , CHECKPOINT_CHANGE#        ,NAME, LAST_CHANGE# from v$datafile ;

       FILE# CHECKPOINT_CHANGE# NAME                                                            LAST_CHANGE#
------------ ------------------ ------------------------------------------------------------ ---------------
           1           12075457 +DATADG/hisdb/datafile/system.262.1096198825
           2           12075457 +DATADG/hisdb/datafile/sysaux.263.1096198835
           3           12075457 +DATADG/hisdb/datafile/undotbs1.264.1096198845
           4           12075457 +DATADG/hisdb/datafile/undotbs2.266.1096198857
           5           12464884 +DATADG/hisdb/datafile/users.267.1096198861                         12464884
           6           12464540 +DATADG/hisdb/datafile/soe.273.1096202257

6 rows selected.

SQL> alter tablespace users online;

Tablespace altered.

SQL> select STATUS,RECOVER, CHECKPOINT_CHANGE# ,NAME from v$datafile_header ;

STATUS         RECOVE CHECKPOINT_CHANGE# NAME
-------------- ------ ------------------ ------------------------------------------------------------
ONLINE         NO               12075457 +DATADG/hisdb/datafile/system.262.1096198825
ONLINE         NO               12075457 +DATADG/hisdb/datafile/sysaux.263.1096198835
ONLINE         NO               12075457 +DATADG/hisdb/datafile/undotbs1.264.1096198845
ONLINE         NO               12075457 +DATADG/hisdb/datafile/undotbs2.266.1096198857
ONLINE         NO               12464951 +DATADG/hisdb/datafile/users.267.1096198861
ONLINE         NO               12464540 +DATADG/hisdb/datafile/soe.273.1096202257

6 rows selected.

SQL> select FILE# , CHECKPOINT_CHANGE#        ,NAME, LAST_CHANGE# from v$datafile ;

       FILE# CHECKPOINT_CHANGE# NAME                                                            LAST_CHANGE#
------------ ------------------ ------------------------------------------------------------ ---------------
           1           12075457 +DATADG/hisdb/datafile/system.262.1096198825
           2           12075457 +DATADG/hisdb/datafile/sysaux.263.1096198835
           3           12075457 +DATADG/hisdb/datafile/undotbs1.264.1096198845
           4           12075457 +DATADG/hisdb/datafile/undotbs2.266.1096198857
           5           12464951 +DATADG/hisdb/datafile/users.267.1096198861
           6           12464540 +DATADG/hisdb/datafile/soe.273.1096202257

6 rows selected.

结论：

1、v$datafile里面的last_change#字段，表示了该数据文件到达一致性需要到达的scn点；

2、alter database datafile 6 offline语句不会自动刷脏数据到datafile，online的时候需要先recover；

3、alter tablespace users read only ;语句会自动刷脏数据到datafile，read write的时候可以直接拉起来，不需要recover；

4、 alter tablespace users offline normal; 语句也会自动刷最新脏数据到datafile，online的时候不需要recover可以直接拉起来；