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.csdn.net/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）操作。

 

 

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