1、下面是通过视图来观察数据文件的一些scn信息
select file#,name,
CREATION_CHANGE#, --创建数据文件时的scn
RESETLOGS_CHANGE#, --最后一次使用resetlog打开数据库的scn
CHECKPOINT_CHANGE# --数据文件当前scn
from v$datafile_header;
FILE# NAME CREATION_CHANGE# RESETLOGS_CHANGE# CHECKPOINT_CHANGE#
---------- -------------------------------------------------- ---------------- ----------------- ------------------
1 /u01/app/oracle/oradata/normal/system01.dbf 8 1 924853
2 /u01/app/oracle/oradata/normal/undotbs01.dbf 5224 1 924853
3 /u01/app/oracle/oradata/normal/sysaux01.dbf 5349 1 924853
4 /u01/app/oracle/oradata/normal/users01.dbf 5826 1 924853
5 /u01/app/oracle/oradata/normal/chenhao01.dbf 7948 1 924853
6 /u01/app/oracle/oradata/normal/undotbs02.dbf 155204 1 924853
7 /u01/app/oracle/oradata/normal/system02.dbf 517792 1 924853
8 /u01/app/oracle/oradata/normal/outln01.dbf 769957 1 924853
9 /u01/app/oracle/oradata/normal/test01.dbf 818947 1 924853
select FILE#,
CHANGE# --数据文件最后一次做热备时pin住的scn(使用begin backup备份的)
from v$backup;
FILE# CHANGE#
---------- ----------
1 0
2 0
3 0
4 0
5 0
6 0
7 0
8 0
9 0
2、下面使用trace文件来观察数据文件的scn信息(这里我们以system表空间为例)
SQL> alter session set events 'immediate trace name file_hdrs level 3';
Session altered.
SQL> alter session set events 'immediate trace name file_hdrs off';
Session altered.
在trace文件中能看到以下信息:
Tablespace #0 - SYSTEM rel_fn:1
Creation at scn: 0x0000.00000008 08/21/2014 16:26:11 #创建数据文件时的scn,和v$datafile_header.CREATION_CHANGE#对应
Backup taken at scn: 0x0000.00000000 01/01/1988 00:00:00 thread:0 #数据文件最后一次做热备时pin住的scn,和#v$backup.CHANGE#对应
reset logs count:0x33088679 scn: 0x0000.00000001 #最后一次使用resetlog打开数据库的scn,和v$datafile_header.RESETLOGS_CHANGE#对应
prev reset logs count:0x0 scn: 0x0000.00000000
recovered at 08/22/2014 12:54:15
status:0x2004 root dba:0x00402901 chkpt cnt: 133 ctl cnt:132
begin-hot-backup file size: 0
Checkpointed at scn: 0x0000.000e1cb5 08/27/2014 18:00:11 #数据文件当前scn,和v$datafile_header.CHECKPOINT_CHANGE#
3、下面使用bbed来查看这些scn(这里在11g中怎么安装bbed就不说了)
SQL> select file#||' '||name||' '||bytes from v$datafile;
FILE#||''||NAME||''||BYTES
----------------------------------------------------------
1 /u01/app/oracle/oradata/normal/system01.dbf 340787200
2 /u01/app/oracle/oradata/normal/undotbs01.dbf 209715200
3 /u01/app/oracle/oradata/normal/sysaux01.dbf 340787200
4 /u01/app/oracle/oradata/normal/users01.dbf 524288000
5 /u01/app/oracle/oradata/normal/chenhao01.dbf 104857600
6 /u01/app/oracle/oradata/normal/undotbs02.dbf 209715200
7 /u01/app/oracle/oradata/normal/system02.dbf 314572800
8 /u01/app/oracle/oradata/normal/outln01.dbf 20971520
9 /u01/app/oracle/oradata/normal/test01.dbf 10485760
[oracle@normal ~]$ cat filelist.txt
1 /u01/app/oracle/oradata/normal/system01.dbf 340787200
2 /u01/app/oracle/oradata/normal/undotbs01.dbf 209715200
3 /u01/app/oracle/oradata/normal/sysaux01.dbf 340787200
4 /u01/app/oracle/oradata/normal/users01.dbf 524288000
5 /u01/app/oracle/oradata/normal/chenhao01.dbf 104857600
6 /u01/app/oracle/oradata/normal/undotbs02.dbf 209715200
7 /u01/app/oracle/oradata/normal/system02.dbf 314572800
8 /u01/app/oracle/oradata/normal/outln01.dbf 20971520
9 /u01/app/oracle/oradata/normal/test01.dbf 10485760
[oracle@normal ~]$ cat bbed.par
blocksize=8192
listfile=/home/oracle/filelist.txt
mode=edit
[oracle@normal ~]$ bbed parfile=bbed.par
BBED> set dba 1,1
-------------------------------------------------
--先说明以下scn是有basescn、和wrapscn两部分构成的
-------------------------------------------------
--查看创建数据文件时的scn,和v$datafile_header.CREATION_CHANGE#对应
BBED> print kcvfhcrs
struct kcvfhcrs, 8 bytes @100
ub4 kscnbas @100 0x00000008
ub2 kscnwrp @104 0x0000
--查看#最后一次使用resetlog打开数据库的scn,和v$datafile_header.RESETLOGS_CHANGE#对应
BBED> print kcvfhrls
struct kcvfhrls, 8 bytes @116
ub4 kscnbas @116 0x00000001
ub2 kscnwrp @120 0x0000
--查看数据文件最后一次做热备时pin住的scn,和#v$backup.CHANGE#对应
BBED> print kcvfhbsc
struct kcvfhbsc, 8 bytes @128
ub4 kscnbas @128 0x00000000
ub2 kscnwrp @132 0x0000
--查看数据文件当前scn,和v$datafile_header.CHECKPOINT_CHANGE#
BBED> print kcvfhckp
struct kcvfhckp, 36 bytes @484
struct kcvcpscn, 8 bytes @484
ub4 kscnbas @484 0x000e1cb5
ub2 kscnwrp @488 0x0000
-------------------------------------------------------------
--通过上面显示的偏移量(offset,如:@100)我们来看看具体呈现的样子
--这里只做数据文件创建时scn的例子。
--------------------------------------------------------------
BBED> set dba 1,1
DBA 0x00400001 (4194305 1,1)
BBED> dump offset 100 count 8
File: /u01/app/oracle/oradata/normal/system01.dbf (1)
Block: 1 Offsets: 100 to 115 Dba:0x00400001
------------------------------------------------------------------------
08000000 00000000 --这里由于大小端的问题需要倒过来看 如:08000000 -> 00000008
<32 bytes per line>