沧州网站建设 3tseo/徐州网站关键词排名
数据文件中的SCN都可以在V$DATAFILE_HEADER中查询,主要四类。
1、数据文件中的四种SCN
1.1 CREATION_CHANGE#
表示数据文件创建时的SCN,该值也保存在控制文件中。可以通过V$DATAFILE_HEADER.CREATION_CHANGE#查询。
SQL> select FILE#,NAME,CREATION_CHANGE# from V$DATAFILE_HEADER;FILE# NAME CREATION_CHANGE#
---------- -------------------------------------------------- ----------------1 /u01/app/oracle/oradata/panda/system01.dbf 72 /u01/app/oracle/oradata/panda/sysaux01.dbf 21643 /u01/app/oracle/oradata/panda/undotbs01.dbf 7520024 /u01/app/oracle/oradata/panda/users01.dbf 18243
1.2 CHECKPOINT_CHANGE#
表示数据文件头当前的SCN,该值也保存在控制文件中。由CKPT进程在全量CHECKPOINT时跟新,可以通过V$DATAFILE_HEADER.CHECKPOINT_CHANGE#查询。
SQL> select FILE#,NAME,CHECKPOINT_CHANGE# from V$DATAFILE_HEADER;FILE# NAME CHECKPOINT_CHANGE#
---------- -------------------------------------------------- ------------------1 /u01/app/oracle/oradata/panda/system01.dbf 14632912 /u01/app/oracle/oradata/panda/sysaux01.dbf 14632913 /u01/app/oracle/oradata/panda/undotbs01.dbf 14632914 /u01/app/oracle/oradata/panda/users01.dbf 1463291
1.3 RESETLOGS_CHANGE#
表示数据库以resetlogs打开时的SCN。在做数据文件的在线热备份时,可以通过V$DATAFILE_HEADER.RESETLOGS_CHANGE#查询。
SQL> select FILE#,NAME,RESETLOGS_CHANGE# from V$DATAFILE_HEADER;FILE# NAME RESETLOGS_CHANGE#
---------- -------------------------------------------------- -----------------1 /u01/app/oracle/oradata/panda/system01.dbf 7544882 /u01/app/oracle/oradata/panda/sysaux01.dbf 7544883 /u01/app/oracle/oradata/panda/undotbs01.dbf 7544884 /u01/app/oracle/oradata/panda/users01.dbf 754488
1.4 CHANGE#
表示数据文件头冻结时的SCN。在做数据文件在线热备份时,常用BEGIN BACKUP命令将数据文件头冻结。表明从CHANGE#这个点开始对数据文件进行备份,可以从V$BACKUP.CHANGE#查询。
SQL> select file#,change# from v$backup;FILE# CHANGE#
---------- ----------1 02 03 04 0
2、dump数据文件头观察scn
ALTER SESSION SET EVENTS 'immediate trace name file_hdrs level n';
1 控制文件中的文件头
2 level 1 + 文件有信息
3 level 2 + 数据文件头信息
10 level 3
SQL> ALTER SESSION SET EVENTS 'immediate trace name file_hdrs level 3';Session altered.SQL> select value from v$diag_info where name = 'Default Trace File'; VALUE
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/panda/panda/trace/panda_ora_7357.trcSQL> ! vim /u01/app/oracle/diag/rdbms/panda/panda/trace/panda_ora_7357.trc.....
DUMP OF DATA FILES: 4 files in databaseDATA FILE #1:name #7: /u01/app/oracle/oradata/panda/system01.dbf
creation size=0 block size=8192 status=0xe head=7 tail=7 dup=1tablespace 0, index=1 krfil=1 prev_file=0unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00Checkpoint cnt:170 scn: 0x0000.001653fb 01/17/2015 14:47:11Stop scn: 0xffff.ffffffff 01/17/2015 14:40:38Creation Checkpointed at scn: 0x0000.00000007 08/13/2009 23:00:53thread:0 rba:(0x0.0.0)enabled threads: 00000000 00000000 00000000 00000000 00000000 0000000000000000 00000000 00000000 00000000 00000000 00000000 00000000 0000000000000000 00000000 00000000 00000000 00000000 00000000 00000000 0000000000000000 00000000 00000000 00000000 00000000 00000000 00000000 0000000000000000 00000000 00000000 00000000 00000000 00000000 00000000 0000000000000000 00000000 00000000 00000000 00000000 00000000 00000000 0000000000000000 00000000 00000000 00000000 00000000 00000000 00000000 0000000000000000 00000000 00000000 00000000 00000000 00000000 00000000 0000000000000000 00000000 00000000 00000000 00000000 00000000 00000000 0000000000000000 00000000 00000000 00000000 00000000 00000000 00000000 0000000000000000 00000000 00000000 00000000 00000000 00000000 00000000 0000000000000000 00000000 00000000 00000000 00000000 00000000 00000000 0000000000000000 00000000 00000000 00000000 00000000 00000000 00000000 0000000000000000 00000000 00000000 00000000 00000000 00000000 00000000 0000000000000000 00000000 00000000 00000000 00000000 00000000 00000000 0000000000000000 00000000 00000000 00000000 00000000 00000000 00000000 0000000000000000 00000000 00000000 00000000 00000000 00000000Offline scn: 0x0000.000b8337 prev_range: 0Online Checkpointed at scn: 0x0000.000b8338 06/29/2014 21:15:46thread:1 rba:(0x1.2.0)enabled threads: 01000000 00000000 00000000 00000000 00000000 0000000000000000 00000000 00000000 00000000 00000000 00000000 00000000 0000000000000000 00000000 00000000 00000000 00000000 00000000 00000000 0000000000000000 00000000 00000000 00000000 00000000 00000000 00000000 0000000000000000 00000000 00000000 00000000 00000000 00000000 00000000 0000000000000000 00000000 00000000 00000000 00000000 00000000 00000000 0000000000000000 00000000 00000000 00000000 00000000 00000000 00000000 0000000000000000 00000000 00000000 00000000 00000000 00000000 00000000 0000000000000000 00000000 00000000 00000000 00000000 00000000 00000000 0000000000000000 00000000 00000000 00000000 00000000 00000000 00000000 0000000000000000 00000000 00000000 00000000 00000000 00000000 00000000 0000000000000000 00000000 00000000 00000000 00000000 00000000 00000000 0000000000000000 00000000 00000000 00000000 00000000 00000000 00000000 0000000000000000 00000000 00000000 00000000 00000000 00000000 00000000 0000000000000000 00000000 00000000 00000000 00000000 00000000 00000000 0000000000000000 00000000 00000000 00000000 00000000 00000000 00000000 0000000000000000 00000000 00000000 00000000 00000000 00000000Hot Backup end marker scn: 0x0000.00000000aux_file is NOT DEFINEDPlugged readony: NOPlugin scnscn: 0x0000.00000000Plugin resetlogs scn/timescn: 0x0000.00000000 01/01/1988 00:00:00Foreign creation scn/timescn: 0x0000.00000000 01/01/1988 00:00:00Foreign checkpoint scn/timescn: 0x0000.00000000 01/01/1988 00:00:00Online move state: 0V10 STYLE FILE HEADER:Compatibility Vsn = 186646528=0xb200000Db ID=368565311=0x15f7dc3f, Db Name='PANDA'Activation ID=0=0x0Control Seq=2107=0x83b, File size=87040=0x15400File Number=1, Blksiz=8192, File Type=3 DATA
Tablespace #0 - SYSTEM rel_fn:1
Creation at scn: 0x0000.00000007 08/13/2009 23:00:53
Backup taken at scn: 0x0000.00000000 01/01/1988 00:00:00 thread:0reset logs count:0x32c19982 scn: 0x0000.000b8338prev reset logs count:0x296a3120 scn: 0x0000.00000001recovered at 01/17/2015 09:43:15status:0x2004 root dba:0x00400208 chkpt cnt: 170 ctl cnt:169
begin-hot-backup file size: 0
Checkpointed at scn: 0x0000.001653fb 01/17/2015 14:47:11thread:1 rba:(0x2a.7d09.10)enabled threads: 01000000 00000000 00000000 00000000 00000000 0000000000000000 00000000 00000000 00000000 00000000 00000000 00000000 0000000000000000 00000000 00000000 00000000 00000000 00000000 00000000 0000000000000000 00000000 00000000 00000000 00000000 00000000 00000000 0000000000000000 00000000 00000000 00000000 00000000 00000000 00000000 0000000000000000 00000000 00000000 00000000 00000000 00000000 00000000 0000000000000000 00000000 00000000 00000000 00000000 00000000 00000000 0000000000000000 00000000 00000000 00000000 00000000 00000000 00000000 0000000000000000 00000000 00000000 00000000 00000000 00000000 00000000 0000000000000000 00000000 00000000 00000000 00000000 00000000 00000000 0000000000000000 00000000 00000000 00000000 00000000 00000000 00000000 0000000000000000 00000000 00000000 00000000 00000000 00000000 00000000 0000000000000000 00000000 00000000 00000000 00000000 00000000 00000000 0000000000000000 00000000 00000000 00000000 00000000 00000000 00000000 0000000000000000 00000000 00000000 00000000 00000000 00000000 00000000 0000000000000000 00000000 00000000 00000000 00000000 00000000 00000000 0000000000000000 00000000 00000000 00000000 00000000 00000000
Backup Checkpointed at scn: 0x0000.00000000thread:0 rba:(0x0.0.0)enabled threads: 00000000 00000000 00000000 00000000 00000000 0000000000000000 00000000 00000000 00000000 00000000 00000000 00000000 0000000000000000 00000000 00000000 00000000 00000000 00000000 00000000 0000000000000000 00000000 00000000 00000000 00000000 00000000 00000000 0000000000000000 00000000 00000000 00000000 00000000 00000000 00000000 0000000000000000 00000000 00000000 00000000 00000000 00000000 00000000 0000000000000000 00000000 00000000 00000000 00000000 00000000 00000000 0000000000000000 00000000 00000000 00000000 00000000 00000000 00000000 0000000000000000 00000000 00000000 00000000 00000000 00000000 00000000 0000000000000000 00000000 00000000 00000000 00000000 00000000 00000000 0000000000000000 00000000 00000000 00000000 00000000 00000000 00000000 0000000000000000 00000000 00000000 00000000 00000000 00000000 00000000 0000000000000000 00000000 00000000 00000000 00000000 00000000 00000000 0000000000000000 00000000 00000000 00000000 00000000 00000000 00000000 0000000000000000 00000000 00000000 00000000 00000000 00000000 00000000 0000000000000000 00000000 00000000 00000000 00000000 00000000 00000000 0000000000000000 00000000 00000000 00000000 00000000 00000000
External cache id: 0x0 0x0 0x0 0x0
Absolute fuzzy scn: 0x0000.00000000
Recovery fuzzy scn: 0x0000.00000000 01/01/1988 00:00:00
Terminal Recovery Stamp 01/01/1988 00:00:00
Platform Information: Creation Platform ID: 10
Current Platform ID: 10 Last Platform ID: 10
.....
注:数据文件创建时的SCN(V$DATAFILE_HEADER.CREATION_CHANGE#): Creation Checkpointed at scn: 0x0000.00000007 = 7
数据文件头当前的SCN(V$DATAFILE_HEADER.CHECKPOINT_CHANGE#): Checkpoint cnt:170 scn: 0x0000.001653fb = 1463291
以resetlogs打开时的SCN(V$DATAFILE_HEADER.RESETLOGS_CHANGE#): reset logs count:0x32c19982 scn: 0x0000.000b8338 = 754488
数据文件头冻结时的SCN(V$BACKUP.CHANGE#):Backup taken at scn: 0x0000.00000000 = 0
可以看到dump数据文件头查出来的scn和使用视图查询出来的scn是一致的。