checkpoint时的SCN写文件动作

参考自：http://blog.csdn.net/tianlesoftware/article/details/5251916

当发生checkpoint时，会把SCN写到四个地方去：三个地方于control file内，一个在datafile header。

一、实验，如下：
--Control fil e三个地方为：
1.1 System checkpoint SCN ===========> (SYSTEM CHECKPOINT SCN in control file)
SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#
------------------
           3779864

1.2 Datafile checkpoint SCN ===============> (DATAFILE CHECKPOINT SCN in control file)
SQL> set lines 200
SQL> col name for a60
SQL> select name,checkpoint_change# from v$datafile;

NAME                                                         CHECKPOINT_CHANGE#
------------------------------------------------------------ ------------------
/u01/app/oracle/oradata/DBdb/system01.dbf                               3779864
/u01/app/oracle/oradata/DBdb/sysaux01.dbf                               3779864
/u01/app/oracle/oradata/DBdb/undotbs01.dbf                              3779864
/u01/app/oracle/oradata/DBdb/users01.dbf                                3779864
/u01/app/oracle/oradata/DBdb/example01.dbf                              3779864


1.3 Stop SCN ======================> (STOP SCN in control file)
SQL> select name,last_change# from v$datafile;

NAME                                                         LAST_CHANGE#
------------------------------------------------------------ ------------
/u01/app/oracle/oradata/DBdb/system01.dbf
/u01/app/oracle/oradata/DBdb/sysaux01.dbf
/u01/app/oracle/oradata/DBdb/undotbs01.dbf
/u01/app/oracle/oradata/DBdb/users01.dbf
/u01/app/oracle/oradata/DBdb/example01.dbf

正常datafile在read-write mode下  last_change#一定是NULL


--另外一个地方在datafile header内

1.4 Start SCN ================================> (DATAFILE HEADER)
SQL> select name, checkpoint_change# from v$datafile_header;

NAME                                                         CHECKPOINT_CHANGE#
------------------------------------------------------------ ------------------
/u01/app/oracle/oradata/DBdb/system01.dbf                               3779864
/u01/app/oracle/oradata/DBdb/sysaux01.dbf                               3779864
/u01/app/oracle/oradata/DBdb/undotbs01.dbf                              3779864
/u01/app/oracle/oradata/DBdb/users01.dbf                                3779864
/u01/app/oracle/oradata/DBdb/example01.dbf                              3779864

SQL>   

二、相关问题

2.1 为什么储存在CONTROL FILE中要分为两个地方(SYSTEM CHECKPOINT SCN,DATAFILE CHECKPOINT SCN) ?

当你把一个tbs设为read-only时，他的SCN会冻结停止，此时DATAFILE CHECKPOINT SCN是不会再递增改变的， 但是整体的SYSTEM CHECKPOINT SCN却仍然会不断递增前进。
所以，这就是为什么需要分别在两个地方储存SCN

2.2 正常shutdown database后，SCN会发生什么变化？我们可以把数据库开在mount mode，如下：
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup mount;
ORACLE instance started.

Total System Global Area  835104768 bytes
Fixed Size                  2257840 bytes
Variable Size             549456976 bytes
Database Buffers          281018368 bytes
Redo Buffers                2371584 bytes
Database mounted.
SQL>
SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#
------------------
           3782319

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

NAME                                                         CHECKPOINT_CHANGE#
------------------------------------------------------------ ------------------
/u01/app/oracle/oradata/DBdb/system01.dbf                               3782319
/u01/app/oracle/oradata/DBdb/sysaux01.dbf                               3782319
/u01/app/oracle/oradata/DBdb/undotbs01.dbf                              3782319
/u01/app/oracle/oradata/DBdb/users01.dbf                                3782319
/u01/app/oracle/oradata/DBdb/example01.dbf                              3782319

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

NAME                                                         CHECKPOINT_CHANGE# LAST_CHANGE#
------------------------------------------------------------ ------------------ ------------
/u01/app/oracle/oradata/DBdb/system01.dbf                               3782319      3782319
/u01/app/oracle/oradata/DBdb/sysaux01.dbf                               3782319      3782319
/u01/app/oracle/oradata/DBdb/undotbs01.dbf                              3782319      3782319
/u01/app/oracle/oradata/DBdb/users01.dbf                                3782319      3782319
/u01/app/oracle/oradata/DBdb/example01.dbf                              3782319      3782319

可以看到储存在control file中的三个SCN位置都是相同，注意此时的stop scn不会是NULL，而是等于start scn

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

NAME                                                         CHECKPOINT_CHANGE#
------------------------------------------------------------ ------------------
/u01/app/oracle/oradata/DBdb/system01.dbf                               3782319
/u01/app/oracle/oradata/DBdb/sysaux01.dbf                               3782319
/u01/app/oracle/oradata/DBdb/undotbs01.dbf                              3782319
/u01/app/oracle/oradata/DBdb/users01.dbf                                3782319
/u01/app/oracle/oradata/DBdb/example01.dbf                              3782319

当clean shutdown 时，checkpoint会进行，并且此时datafile的stop scn和start scn会相同。 等到我门开启数据库时，Oracle检查datafile header中的start scn和存于control file中的datafile的scn是否相同， 如果相同，接着检查start scn和stop scn是否相同，如果仍然相同，数据库就会正常开启，否则就需要recovery... 等到数据库开启后，储存在control file中的stop scn就会恢复为NULL值，此时表示datafile是open在正常模式下了。

如果不正常SHUTDOWN (shutdown abort)，则mount数据库后，你会发现stop scn并不是等于其它位置的scn， 而是等于NULL，这表示Oracle在shutdown时没有进行checkpoint，下次开机必须进行crash recovery。

crash recovery：
必须先进行roll forward(从redo log file中从目前的start SCN开始，重做后面的已提交之交易)。再从roll back segment 做rollback未完成(dead transaction)交易。检验controlfile中的SCN会等于datafile header的SCN

2.3 先进行备份：（数据库处于mount状态，冷备）；
RMAN> backup database tag='full database';

Starting backup at 28-NOV-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=21 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00004 name=/u01/app/oracle/oradata/DBdb/users01.dbf
input datafile file number=00001 name=/u01/app/oracle/oradata/DBdb/system01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/DBdb/undotbs01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/DBdb/sysaux01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/DBdb/example01.dbf
channel ORA_DISK_1: starting piece 1 at 28-NOV-17
   channel ORA_DISK_1: finished piece 1 at 28-NOV-17
piece handle=/u01/app/oracle/fast_recovery_area/DBDB/backupset/2017_11_28/o1_mf_nnndf_FULL_DATABASE_f1t8rv9q_.bkp tag=FULL DATABASE comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:03:15
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 28-NOV-17
channel ORA_DISK_1: finished piece 1 at 28-NOV-17
piece handle=/u01/app/oracle/fast_recovery_area/DBDB/backupset/2017_11_28/o1_mf_ncsnf_FULL_DATABASE_f1t8z23m_.bkp tag=FULL DATABASE comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 28-NOV-17

RMAN>

--shutdown abort数据库：
SQL> select status from v$instance;

STATUS
------------
MOUNTED

SQL> alter database open;

Database altered.

SQL> shutdown abort;
ORACLE instance shut down.
SQL>
--启库：
SQL>  conn / as sysdba
Connected to an idle instance.
SQL>
SQL> startup nomount;
ORACLE instance started.

Total System Global Area  835104768 bytes
Fixed Size                  2257840 bytes
Variable Size             549456976 bytes
Database Buffers          281018368 bytes
Redo Buffers                2371584 bytes
SQL>
SQL> alter database mount;

Database altered.

--查询scn状态：
SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#
------------------
           3782322

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

NAME                                                         CHECKPOINT_CHANGE# LAST_CHANGE#
------------------------------------------------------------ ------------------ ------------
/u01/app/oracle/oradata/DBdb/system01.dbf                               3782322
/u01/app/oracle/oradata/DBdb/sysaux01.dbf                               3782322
/u01/app/oracle/oradata/DBdb/undotbs01.dbf                              3782322
/u01/app/oracle/oradata/DBdb/users01.dbf                                3782322
/u01/app/oracle/oradata/DBdb/example01.dbf                              3782322

stop scn并不是等于其它位置的scn， 而是等于NULL，表示需要进行crash recovery

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

NAME                                                         CHECKPOINT_CHANGE#
------------------------------------------------------------ ------------------
/u01/app/oracle/oradata/DBdb/system01.dbf                               3782322
/u01/app/oracle/oradata/DBdb/sysaux01.dbf                               3782322
/u01/app/oracle/oradata/DBdb/undotbs01.dbf                              3782322
/u01/app/oracle/oradata/DBdb/users01.dbf                                3782322
/u01/app/oracle/oradata/DBdb/example01.dbf                              3782322


2.4 crash recovery 和media recovery 的比较
启动数据库时，如果发现STOP SCN = NULL，表示需要进行crash recovery；启动数据库时，如果发现有datafile header的START SCN 不等于储存于CONTROLFILE的DATAFILE SCN，表示需要进行Media recovery

STOP SCN equal NULL ==> NEED CRASH RECOVERY

DATAFILE HEADER START SCN not equal CONTROLFILE SCN ==> NEED MEDIA RECOVERY


三、RECOVERY DATABASE 两种常见问题
3.1 RECOVER DATABASE UNTIL CANCEL ==> OPEN DATABASE RESETLOG
==> DATAFILE HEADER SCN一定会小于CONTROLFILE的DATAFILE SCN

如果你有进行RESTORE DATAFILE，则该RESTORE的DATAFILE HEADER SCN一定会小于目前CONTROLFILE的DATAFILE SCN，此时会无法开启数据库，必须进行media recovery。 重做archive log直到该datafile header的SCN=current scn

restore datafile后，可以mount database然后去检查controlfile and datafile header的SCN

select 'controlfile' "SCN location",name,checkpoint_change#
from v$datafile where name like '%users01%'
union
select 'file header',name,checkpoint_change#
from v$datafile_header where name like '%users01%';


3.2  RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE; ===> OPEN DATABASE RESETLOG

==> DATAFILE HEADER SCN一定会大于CONTROLFILE的DATAFILE SCN

如果只是某TABLE被DROP掉，没有破坏数据库整体数据结构，还可以用NCOMPLETE RECOVERY解决 如果是某个TABLESPACE OR DATAFILE被DROP掉，因为档案结构已经破坏，目前的CONTROL FILE内已经没有 该DATAFILE的信息，就算你只RESTORE DATAFILE然后进行INCOMPLETE RECOVERY也无法救回被DROP的DATA FILE。

只好RESOTRE 之前备份的CONTROL FILE(里头被DROP DATAFILE Metadata此时还存在)，不过RESTOREC CONTROL FILE后 此时Oracle会发现CONTROL FILE内的SYSTEM SCN会小于目前的DATAFILE HEADER SCN，也不等于目前储存于LOG FILE内的SCN， 此时就必须使用RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE到DROP DATAFILE OR DROP TABLESPACE之前的SCN。

另一种特殊状况就是，万一不幸地所有CONTROL FILE都遗失了，也必须用这种方式救回，所以请做MULTIPLEXING。