Shulou(Shulou.com)06/01 Report--

Offline scn/online scn

When a tablespace or data file is offline, the scn of the corresponding data file will be written to a value, called offline scn. However, there are some noteworthy points, which I use experiments to analyze here to make it easier for you to understand:

View the scn SYS@hyyk > select file#,checkpoint_change# from v$datafile where ts#=4 of the current tablespace 4

FILE# CHECKPOINT_CHANGE#-4 1819632

SYS@hyyk > select file#,checkpoint_change# from v$datafile_header where ts#=4

FILE# CHECKPOINT_CHANGE#-4 1819632

SYS@hyyk > alter tablespace users offline

Tablespace altered.

SQL > oradebug setmypid SQL > alter session set events' immediate trace name CONTROLF level 4'

SQL > alter session set events' immediate trace name FILE_HDRS level 3'

SQL > oradebug close_trace

SYS@hyyk > oradebug tracefile_name / u01/app/oracle/diag/rdbms/hyyk/hyyk/trace/hyyk_ora_25045.trc

Control file DATA FILE # 4: name # 4: / u01/app/oracle/oradata/hyyk/users01.dbf creation size=0 block size=8192 status=0x80 head=4 tail=4 dup=1 tablespace 4 Index=4 krfil=4 prev_file=0 unrecoverable scn: 0x0000.00000000 01 enabled threads 01 Checkpoint cnt:201 scn: 00:00:00 Checkpoint cnt:201 scn: 0x0000.001be049 02 22 Stop scn 2018 13:02:20 Stop scn: 0x0000.001be049 02 Grammer 2018 13:02:20 Creation Checkpointed at scn: 0x0000.00004649 08 Greater 15 Greater thread:0 rba: (0x0.0.0) enabled threads: 00000000 00000000 00000000 000000000000000000000000. Offline scn: 0x0000.001bc3b5 prev_range: 1 Online Checkpointed at scn: 0x0000.001bc3f0 02 Plus 12 thread:1 rba: (0x3c.183.10) 11:35:20 in 2018

Data file DATA FILE # 4: name # 4: / u01/app/oracle/oradata/hyyk/users01.dbf creation size=0 block size=8192 status=0x80 head=4 tail=4 dup=1 tablespace 4 Index=4 krfil=4 prev_file=0 unrecoverable scn: 0x0000.00000000 01 0x0000.00004649 01 enabled threads: 00:00:00 Checkpoint cnt:201 scn: 0x0000.001be049 02 22 Stop scn 2018 13:02:20 Stop scn: 0x0000.001be049 02 Grammer 2018 13:02:20 Creation Checkpointed at scn: 0x0000.00004649 08 Greater 15 thread:0 rba: (0x0.0.0) enabled threads: 00000000 00000000 00000000 00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 Offline scn: 0x0000.001bc3b5 prev_range: 1-offline scn Online Checkpointed at scn: 0x0000.001bc3f0 02 offline scn Online Checkpointed at scn 12, 2018 11:35:20-if this datafile online, then the value will be updated.. Thread:1 rba: (0x3c.183.10) enabled threads: 01000000 00000000 00000000 00000000 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000

First, let's switch the log once and observe the change of checkpoint again: SQL > alter system switch logfile; SYS@hyyk > select file#,STATUS,CHECKPOINT_CHANGE#,ONLINE_CHANGE# from vault datafile;-control file

FILE# STATUS CHECKPOINT_CHANGE# ONLINE_CHANGE#-1 SYSTEM 1827251 945184 2 ONLINE 1827251 945184 3 ONLINE 1827251 945184 4 OFFLINE 1826889 1819632 5 ONLINE 1827251 974060 6 ONLINE 1827251 0 7 ONLINE 1827251 0 8 ONLINE 1827251 0

SYS@hyyk > select file#,STATUS,CHECKPOINT_CHANGE#,CHECKPOINT_COUNT from v$datafile_header order by 1;-data file header

FILE# STATUS CHECKPOINT_CHANGE# CHECKPOINT_COUNT-1 ONLINE 1827251 201 2 ONLINE 1827251 201 3 ONLINE 1827251 126 4 OFFLINE 0 05 ONLINE 1827251 122 6 ONLINE 1827251 72 7 ONLINE 1827251 59 8 ONLINE 1827251 59

SYS@hyyk > alter tablespace users online

Tablespace altered.

SQL > oradebug setmypid Statement processed. SQL > alter session set events' immediate trace name CONTROLF level 4'

SQL > alter session set events' immediate trace name FILE_HDRS level 3'

SQL > oradebug close_trace

SQL > oradebug tracefile_name / u01/app/oracle/diag/rdbms/hyyk/hyyk/trace/hyyk_ora_26044.trc

DATA FILE # 4: name # 4: / u01/app/oracle/oradata/hyyk/users01.dbf creation size=0 block size=8192 status=0xe head=4 tail=4 dup=1 tablespace 4, index=4 krfil=4 prev_file=0 unrecoverable scn: 0x0000.00000000 01ame01 Checkpoint cnt:202 scn: 1988 00:00:00 Checkpoint cnt:202 scn: 0x0000.001be1dd 02 Stop scn 22 Stop scn: 2018 13:20:24 Stop scn: 0xffff.ffffffff 02 SNR 2018 13:02:20 Creation Checkpointed at scn: 0x0000.00004649 08 Creation Checkpointed at scn 15 thread:0 rba: (0x0.0.0) enabled threads: 00000000 00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000

Offline scn: 0x0000.001be049 prev_range: 2 Online Checkpointed at scn: 0x0000.001be1dd 02ax 22 Online Checkpointed at scn-you can see here that the online scn value of the file header has also been updated. Thread:1 rba: (0x3f.8.10) enabled threads: 01000000 00000000 00000000 00000000

From the above small experiments, we can draw the following conclusions:

1. In archivelog mode, when the tablespace offline, the corresponding data file header stop scn will be updated, and the stop scn information of the datafile in controlfile will also be updated. Offline scn is also updated, and offline scn equals stop scn.

two。 When online, the online scn of the corresponding data file header is equal to the datafile checkpoint SCN value.

3. After online, the datafile corresponding to the empty table and the stop scn of the corresponding data file header in controlfile will be reset to the maximum value, that is, infinity.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.