oracle scn之基本概念

一、scn的作用

Scn的作用主要是保证数据库的一致性。它是oracle的内部时钟机制。Scn是实施对oracle恢复非常重要的机制。

Scn在数据库中无处不在，一般在控制文件，数据文件头，日志文件，数据文件等都记录scn值。

system scn从视图v$database中获得，对应checkpoint_change#字段；

datafile scn、last scn（stop scn）分别对应视图v$datafile中的checkpoint_change#，last_change#，

start scn则从v$datafile_header中checkpoint_change#得到。

二、查看当前scn

control中有三种SCN分别为：system SCN、datafile SCN、last SCN（stop scn），

System scn

SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#

------------------

451490

Datafile scn

SQL> select name,checkpoint_change# from v$datafile;

NAME

--------------------------------------------------------------------------------

CHECKPOINT_CHANGE#

------------------

/opt/app/oracle/oradata/wolf/system01.dbf

451490

/opt/app/oracle/oradata/wolf/undotbs01.dbf

451490

/opt/app/oracle/oradata/wolf/sysaux01.dbf

451490

NAME

--------------------------------------------------------------------------------

CHECKPOINT_CHANGE#

------------------

/opt/app/oracle/oradata/wolf/users01.dbf

451490

Last scn（stop scn，正常启动的时候stop scn无穷大， last_change#一定是NULL）

SQL> select name,last_change# from v$datafile;

NAME

--------------------------------------------------------------------------------

LAST_CHANGE#

------------

/opt/app/oracle/oradata/wolf/system01.dbf

/opt/app/oracle/oradata/wolf/undotbs01.dbf

/opt/app/oracle/oradata/wolf/sysaux01.dbf

NAME

--------------------------------------------------------------------------------

LAST_CHANGE#

------------

/opt/app/oracle/oradata/wolf/users01.dbf

数据文件头中有一种SCN ：start SCN

SQL> select name,checkpoint_change# from v$datafile_header;

NAME

--------------------------------------------------------------------------------

CHECKPOINT_CHANGE#

------------------

/opt/app/oracle/oradata/wolf/system01.dbf

451490

/opt/app/oracle/oradata/wolf/undotbs01.dbf

451490

/opt/app/oracle/oradata/wolf/sysaux01.dbf

451490

NAME

--------------------------------------------------------------------------------

CHECKPOINT_CHANGE#

------------------

/opt/app/oracle/oradata/wolf/users01.dbf

451490

三、各种状态下scn变化。

1、正常关库（immediate,noraml,translate），四个scn会应执行full checkpoint 而相等。

正常关库时scn应该保持一致，start scn=stop scn

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area 285212672 bytes

Fixed Size 1218992 bytes

Variable Size 92276304 bytes

Database Buffers 188743680 bytes

Redo Buffers 2973696 bytes

Database mounted.

SQL> select name,checkpoint_change# from v$database;

NAME CHECKPOINT_CHANGE#

--------------------------- ------------------

WOLF 461843

SQL> select name,checkpoint_change#,last_change# from v$datafile;

NAME

--------------------------------------------------------------------------------

CHECKPOINT_CHANGE# LAST_CHANGE#

------------------ ------------

/opt/app/oracle/oradata/wolf/system01.dbf

461843 461843

/opt/app/oracle/oradata/wolf/undotbs01.dbf

461843 461843

/opt/app/oracle/oradata/wolf/sysaux01.dbf

461843 461843

NAME

--------------------------------------------------------------------------------

CHECKPOINT_CHANGE# LAST_CHANGE#

------------------ ------------

/opt/app/oracle/oradata/wolf/users01.dbf

461843 461843

SQL> select name,checkpoint_change# from v$datafile_header;

NAME

--------------------------------------------------------------------------------

CHECKPOINT_CHANGE#

------------------

/opt/app/oracle/oradata/wolf/system01.dbf

461843

/opt/app/oracle/oradata/wolf/undotbs01.dbf

461843

/opt/app/oracle/oradata/wolf/sysaux01.dbf

461843

NAME

--------------------------------------------------------------------------------

CHECKPOINT_CHANGE#

------------------

/opt/app/oracle/oradata/wolf/users01.dbf

461843

2、系统正常关闭

断电（shutdown abort模拟断电）

这个时候last scn依然为无穷大，那么当重新启动实例时，系统首先会比较start scn与system scn，如果一致，那么再比较start scn 与last scn是否一样大，因为是非正常关闭，这里会不一样大，那么就需要恢复。

如果last scn无穷大，则由数据库自己做实例恢复。

如果打开数据库时发现system scn>datafile scn，那么以为着使用旧的备份数据文件，也就是需要介质恢复
如果是system scn<datafile scn，及控制文件scn是旧的，代表使用了老的控制文件，需要recover using backup controlfile进行恢复。

SQL> shutdown abort

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 285212672 bytes

Fixed Size 1218992 bytes

Variable Size 96470608 bytes

Database Buffers 184549376 bytes

Redo Buffers 2973696 bytes

Database mounted.

SQL> select name,checkpoint_change#,last_change# from v$datafile;

NAME

--------------------------------------------------------------------------------

CHECKPOINT_CHANGE# LAST_CHANGE#

------------------ ------------

/opt/app/oracle/oradata/wolf/system01.dbf

461844

/opt/app/oracle/oradata/wolf/undotbs01.dbf

461844

/opt/app/oracle/oradata/wolf/sysaux01.dbf

461844

NAME

--------------------------------------------------------------------------------

CHECKPOINT_CHANGE# LAST_CHANGE#

------------------ ------------

/opt/app/oracle/oradata/wolf/users01.dbf

461844

这里发现last_change#为无穷大。说明非正常关库。表示Oracle在shutdown时没有进行checkpoint，下次开机必须进行crash recovery

SQL> select name,checkpoint_change# from v$database;

NAME CHECKPOINT_CHANGE#

--------------------------- ------------------

WOLF 461844

SQL> select name,checkpoint_change# from v$datafile_header;

NAME

--------------------------------------------------------------------------------

CHECKPOINT_CHANGE#

------------------

/opt/app/oracle/oradata/wolf/system01.dbf

461844

/opt/app/oracle/oradata/wolf/undotbs01.dbf

461844

/opt/app/oracle/oradata/wolf/sysaux01.dbf

461844

NAME

--------------------------------------------------------------------------------

CHECKPOINT_CHANGE#

------------------

/opt/app/oracle/oradata/wolf/users01.dbf

461844

SQL> alter database open;

Database altered.

SQL> select * from scott.laolang;

ID

----------

1

2

3

上面的实验是用scott做的，关闭数据库的前，切换了用户（conn），发生了checkpoint，所以值还在

关于提交

http://blog.youkuaiyun.com/yujin2010good/article/details/7713534

SQL> create table wolf1(id number);

Table created.

SQL> insert into wolf1 values(1);

1 row created.

SQL> commit;

Commit complete.

SQL> insert into wolf1 values(2);

1 row created.

SQL> shutdown abort;

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 285212672 bytes

Fixed Size 1218992 bytes

Variable Size 100664912 bytes

Database Buffers 180355072 bytes

Redo Buffers 2973696 bytes

Database mounted.

SQL> select name,checkpoint_change#,last_change# from v$datafile;

NAME

--------------------------------------------------------------------------------

CHECKPOINT_CHANGE# LAST_CHANGE#

------------------ ------------

/opt/app/oracle/oradata/wolf/system01.dbf

483694

/opt/app/oracle/oradata/wolf/undotbs01.dbf

483694

/opt/app/oracle/oradata/wolf/sysaux01.dbf

483694

NAME

--------------------------------------------------------------------------------

CHECKPOINT_CHANGE# LAST_CHANGE#

------------------ ------------

/opt/app/oracle/oradata/wolf/users01.dbf

483694

SQL>

SQL>

SQL>

SQL> select name,checkpoint_change# from v$database;

NAME CHECKPOINT_CHANGE#

--------------------------- ------------------

WOLF 483694

SQL> select name,checkpoint_change# from v$datafile_header;

NAME

--------------------------------------------------------------------------------

CHECKPOINT_CHANGE#

------------------

/opt/app/oracle/oradata/wolf/system01.dbf

483694

/opt/app/oracle/oradata/wolf/undotbs01.dbf

483694

/opt/app/oracle/oradata/wolf/sysaux01.dbf

483694

NAME

--------------------------------------------------------------------------------

CHECKPOINT_CHANGE#

------------------

/opt/app/oracle/oradata/wolf/users01.dbf

483694

SQL> alter database open;

Database altered.

SQL> select * from wolf1;

ID

----------

1

未提交的数据丢失。

SQL>这时发现start scn 与last scn不等，last scn为无穷大，需要实例恢复

这是日志文件内容

[root@test bdump]# pwd

/opt/app/oracle/admin/wolf/bdump

[root@test bdump]# tail -f alert_wolf.log

starting up 1 shared server(s) ...

Sun Jul 8 06:29:32 2012

ALTER DATABASE MOUNT

Sun Jul 8 06:29:36 2012

Setting recovery target incarnation to 2

Sun Jul 8 06:29:36 2012

Successful mount of redo thread 1, with mount id 3753997644

Sun Jul 8 06:29:36 2012

Database mounted in Exclusive Mode

Completed: ALTER DATABASE MOUNT

Sun Jul 8 06:42:49 2012

alter database open

Sun Jul 8 06:42:49 2012

Beginning crash recovery of 1 threads

Sun Jul 8 06:42:49 2012

Started redo scan

Sun Jul 8 06:42:49 2012

Completed redo scan

106 redo blocks read, 34 data blocks need recovery

Sun Jul 8 06:42:49 2012

Started redo application at

Thread 1: logseq 2, block 1779

Sun Jul 8 06:42:49 2012

Recovery of Online Redo Log: Thread 1 Group 1 Seq 2 Reading mem 0

Mem# 0 errs 0: /opt/app/oracle/oradata/wolf/redo01.log

Sun Jul 8 06:42:49 2012

Completed redo application

Sun Jul 8 06:42:49 2012

Completed crash recovery at

Thread 1: logseq 2, block 1885, scn 504788

34 data blocks read, 34 data blocks written, 106 redo blocks read

Sun Jul 8 06:42:49 2012

Thread 1 advanced to log sequence 3

Thread 1 opened at log sequence 3

Current log# 2 seq# 3 mem# 0: /opt/app/oracle/oradata/wolf/redo02.log

Successful open of redo thread 1

Sun Jul 8 06:42:49 2012

MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set

Sun Jul 8 06:42:49 2012

SMON: enabling cache recovery

Sun Jul 8 06:42:49 2012

Successfully onlined Undo Tablespace 1.

Sun Jul 8 06:42:49 2012

SMON: enabling tx recovery

Sun Jul 8 06:42:50 2012

Database Characterset is WE8ISO8859P1

replication_dependency_tracking turned off (no async multimaster replication found)

Starting background process QMNC

QMNC started with pid=16, OS id=20256

Sun Jul 8 06:42:50 2012

db_recovery_file_dest_size of 2048 MB is 0.00% used. This is a

user-specified limit on the amount of space that will be used by this

database for recovery-related files, and does not reflect the amount of

space available in the underlying filesystem or ASM diskgroup.

Sun Jul 8 06:42:51 2012

Completed: alter database open

明显做了一个实例恢复，其实就是一个redo（rollforward）和undo（rollback）操作。

欢迎加入：
119224876(db china联盟),233065499(db china联盟),229845401(虚拟化-云计算-物联网)