oracle scn与备份恢复backup recovery(一)

最新推荐文章于 2023-09-05 21:57:31 发布

clg10051

最新推荐文章于 2023-09-05 21:57:31 发布

阅读量136

点赞数

文章标签：数据库

　前面文章中,多次涉及了scn,比如block dump中的scn, scn对于构造cr从而实现consistent read

;至少重要，而且scn与数据库整体的备份恢复有着密不可分的关系；

oracle scn里面的类别有很多种,让人有些晕着不知所向；比如在控制文件中有scn,数据库有scn,数据文件有scn;

数据文件头有scn;日志文件有scn;block有scn;
　
它们之间有何联系；如何有机组合服务于备份与恢复;我们一步步来讲,千里之行，始于足下:
　
如下摘集一些关于scn的贴子：

http://logzgh.itpub.net/post/3185/42513

http://www.doc88.com/p-146571552776.html

1,先研究下scn与checkpoint的关系；自然就要知道何为检查点，检查点oracle具体什什么工作；
　
　 /**********检查点概念及简介*****************/
　 http://space.itpub.net/?uid-9240380-action-viewspace-itemid-757005

ckpt进程触发检查点时,把当前最新的scn写入到数据文件头部和控制文件中,同时ckpt通知dbwr把buffer cache
的dirty buffer写入到数据文件中

与checkpoint相关的scn有几个呢,上面说了，发生检查点会在数据文件头部和控制文件中写入：
所以我们检查与数据文件头部和控制文件有关的视图即可：

/*关于v$database可参见*************http://docs.oracle.com/cd/E11882_01/server.112/e25513/dynviews_1096.htm#REFRN30047************/
/***第一列会随机变化,而第二列是最新发生checkpoint的scn******v$database还有其它的scn,比如与backup及resetslog相关的，本文不关注**/
SQL> select current_scn,checkpoint_change# from v$database;--v$database是记录控制文件的scn

CURRENT_SCN CHECKPOINT_CHANGE#
----------- ------------------
10558777 10514593

SQL> alter system checkpoint;

System altered

/*************产生检查点发checkpoint_change#变化了*/
SQL> select current_scn,checkpoint_change# from v$database;

CURRENT_SCN CHECKPOINT_CHANGE#
----------- ------------------
   10558978           10558976

/*******数据文件的checkpoint scn*****你会发现它与控制文件的scn一样，因为v$datafile也是来自于控制文件**/
SQL> select checkpoint_change# from v$datafile;

CHECKPOINT_CHANGE#
------------------
          10558976
          10558976
          10558976
          10558976
          10558976
          10558976
          10558976
          10558976
          10558976
          10558976
          10558976
          10558976
          10558976
          10558976
          10558976
          10558976
          10558976
          10558976
          10558976
          10558976

CHECKPOINT_CHANGE#
------------------
          10558976

21 rows selected

/**************数据文件头部的scn也与上面的一样/
SQL> select checkpoint_change# from v$datafile_header;

CHECKPOINT_CHANGE#
------------------
          10558976
          10558976
          10558976
          10558976
          10558976
          10558976
          10558976
          10558976
          10558976
          10558976
          10558976
          10558976
          10558976
          10558976
          10558976
          10558976
          10558976
          10558976
          10558976
          10558976

CHECKPOINT_CHANGE#
------------------
          10558976

21 rows selected

小小结:
　　1,一般情况下控制文件的checkpoint scn与数据文件头的checkpoint scn一样
　 2,v$database与v$datafile取自控制文件
    3,v$datafile_header记录数据文件头信息

/********介质恢复概念*********/
http://space.itpub.net/?uid-9240380-action-viewspace-itemid-757008

　数据库启动时，如果上述的checkpoint scn与datafile checkpoint scn和数据文件头的checkpoint scn不一样，
就要执行介质恢复；或者说是v$database.checkpoint_change#与v$datafile.checkpoint_change#与v$datafile_header.checkpoint_change#
不一样就要执行介质恢复

SQL> alter tablespace TBS_16K read only;

Tablespace altered

SQL> select file#,name,checkpoint_change# from v$datafile_header where file#=21;

     FILE# NAME                                                                             CHECKPOINT_CHANGE#
---------- -------------------------------------------------------------------------------- ------------------
        21 C:\TBS16K_1.DBF                                                                            10560282

SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#
------------------
          10558976

SQL> select file#,name,checkpoint_change# from v$datafile where file#=21;

     FILE# NAME                                                                             CHECKPOINT_CHANGE#
---------- -------------------------------------------------------------------------------- ------------------
        21 C:\TBS16K_1.DBF                                                                            10560282

SQL> create table t_hb(a int) tablespace tbs_16k;

Table created

SQL> select file#,name,checkpoint_change# from v$datafile_header where file#=21;

     FILE# NAME                                                                             CHECKPOINT_CHANGE#
---------- -------------------------------------------------------------------------------- ------------------
        21 C:\TBS16K_1.DBF                                                                            10560282

SQL> select file#,name,checkpoint_change# from v$datafile where file#=21;

     FILE# NAME                                                                             CHECKPOINT_CHANGE#
---------- -------------------------------------------------------------------------------- ------------------
        21 C:\TBS16K_1.DBF                                                                            10560282

SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#
------------------
          10558976

小结：1,对于read only操作触发的检查点,数据文件及数据文件头的checkpoint scn发生变化，而
　　　　v$database.checkpoint_change#控制文件的检查点scn被冻结不变化
　　　2,如一直处于read only期间，三者scn皆不再变化

--回复表空间为正常状态
SQL> alter tablespace TBS_16K read write;

Tablespace altered

SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#
------------------
          10558976

SQL> select file#,name,checkpoint_change# from v$datafile where file#=21;

     FILE# NAME                                                                             CHECKPOINT_CHANGE#
---------- -------------------------------------------------------------------------------- ------------------
        21 C:\TBS16K_1.DBF                                                                            10560682

SQL> select file#,name,checkpoint_change# from v$datafile_header where file#=21;

     FILE# NAME                                                                             CHECKPOINT_CHANGE#
---------- -------------------------------------------------------------------------------- ------------------
        21 C:\TBS16K_1.DBF                                                                            10560682

小结：表空间恢复正常后，控制文件的检查点scn及数据文件及头部的检查点scn开始变化
　　　alter tablespace read only;一般用于备份表空间

SQL> select file#,name,checkpoint_change# from v$datafile_header where file#=21;

     FILE# NAME                                                                             CHECKPOINT_CHANGE#
---------- -------------------------------------------------------------------------------- ------------------
        21 C:\TBS16K_1.DBF                                                                            10560682

SQL> select file#,name,checkpoint_change# from v$datafile where file#=21;

     FILE# NAME                                                                             CHECKPOINT_CHANGE#
---------- -------------------------------------------------------------------------------- ------------------
        21 C:\TBS16K_1.DBF                                                                            10560682

SQL> insert into t_hb values(1);

1 row inserted

SQL> select file#,name,checkpoint_change# from v$datafile_header where file#=21;

     FILE# NAME                                                                             CHECKPOINT_CHANGE#
---------- -------------------------------------------------------------------------------- ------------------
        21 C:\TBS16K_1.DBF                                                                            10560682

SQL> select file#,name,checkpoint_change# from v$datafile where file#=21;

     FILE# NAME                                                                             CHECKPOINT_CHANGE#
---------- -------------------------------------------------------------------------------- ------------------
        21 C:\TBS16K_1.DBF                                                                            10560682

SQL> commit;

Commit complete

SQL> select file#,name,checkpoint_change# from v$datafile_header where file#=21;

     FILE# NAME                                                                             CHECKPOINT_CHANGE#
---------- -------------------------------------------------------------------------------- ------------------
        21 C:\TBS16K_1.DBF                                                                            10560682

SQL> select file#,name,checkpoint_change# from v$datafile where file#=21;

     FILE# NAME                                                                             CHECKPOINT_CHANGE#
---------- -------------------------------------------------------------------------------- ------------------
        21 C:\TBS16K_1.DBF                                                                            10560682

小结：1,commit不会触发检查点

QL> select file#,name,checkpoint_change# from v$datafile_header where file#=21;

     FILE# NAME                                                                             CHECKPOINT_CHANGE#
---------- -------------------------------------------------------------------------------- ------------------
        21 C:\TBS16K_1.DBF                                                                            10560682

SQL> select file#,name,checkpoint_change# from v$datafile where file#=21;

     FILE# NAME                                                                             CHECKPOINT_CHANGE#
---------- -------------------------------------------------------------------------------- ------------------
        21 C:\TBS16K_1.DBF                                                                            10560682

SQL> alter system switch logfile;

System altered

SQL> select file#,name,checkpoint_change# from v$datafile_header where file#=21;

     FILE# NAME                                                                             CHECKPOINT_CHANGE#
---------- -------------------------------------------------------------------------------- ------------------
        21 C:\TBS16K_1.DBF                                                                            10560682

SQL> select file#,name,checkpoint_change# from v$datafile where file#=21;

     FILE# NAME                                                                             CHECKPOINT_CHANGE#
---------- -------------------------------------------------------------------------------- ------------------
        21 C:\TBS16K_1.DBF                                                                            10560682

小结：alter system switch logfile切换日志不会触发检查点

SQL> select file#,name,checkpoint_change#,last_change# from v$datafile where file#=21;

     FILE# NAME                                                                             CHECKPOINT_CHANGE# LAST_CHANGE#
---------- -------------------------------------------------------------------------------- ------------------ ------------
        21 C:\TBS16K_1.DBF                                                                            10561215

SQL> alter tablespace tbs_16k read only;

Tablespace altered

SQL> select file#,name,checkpoint_change#,last_change# from v$datafile where file#=21;

     FILE# NAME                                                                             CHECKPOINT_CHANGE# LAST_CHANGE#
---------- -------------------------------------------------------------------------------- ------------------ ------------
        21 C:\TBS16K_1.DBF                                                                            10561320     10561320

SQL> alter tablespace tbs_16k read write;

Tablespace altered

SQL> select file#,name,checkpoint_change#,last_change# from v$datafile where file#=21;

     FILE# NAME                                                                             CHECKPOINT_CHANGE# LAST_CHANGE#
---------- -------------------------------------------------------------------------------- ------------------ ------------
        21 C:\TBS16K_1.DBF                                                                            10561336

SQL>

小结：表空间或数据文件正常情况下，last_change#为空，若read only切回到正常模式，则为上次检查点的scn

二。SCN号与数据库启动
　1,数据库启动过程中，如果v$database.checkpoint_change#,v$datafile.checkpoint_change#及v$datafile_header.checkpoint_change#三者
　　一致，则数据库可以正常打开，否则需要介质恢复
　
　2,数据库启动过程中，如果end_scn即v$datafile.last_change#为null，但需要作实例恢复；
　　也就是说数据库关闭时，会把v$datafile.last_change#设置为v$datafile.checkpoint_change#;
3,数据库启动之后，会把v$datafile.last_change#配置为null

如果在启动的过程中，End SCN号为NULL，则需要做instance recovery.
ORACLE在启动过程中首先检查是否需要media recovery，然后再检查是否需要instance recovery.
三。SCN号与数据库关闭
如果数据库的正常关闭的话，将会触发一个checkpoint，同时将数据文件的END SCN号设置为相应
数据文件的Start SCN号。

当数据库启动时，发现它们是一致的，则不需要做instance recovery。在数据库正常启动后，ORACLE
会将END SCN号设置为NULL.
如果数据库异常关闭的话，则END SCN号将为NULL.

/---************我们测试下实例恢复-----------------/
SQL> select checkpoint_change#,last_change# from v$datafile;

CHECKPOINT_CHANGE# LAST_CHANGE#
------------------ ------------
          10561215
          10561215
          10561215
          10561215
          10561215
          10561215
          10561215
          10561215
          10561215
          10561215
          10561215
          10561215
          10561215
          10561215
          10561215
          10561215
          10561215
          10561215
          10561215
          10561215

CHECKPOINT_CHANGE# LAST_CHANGE#
------------------ ------------
          10561336

21 rows selected

/***********非常关闭数据库*************/
SQL> shutdown abort
ORACLE instance shut down.

/********启动到mount***********/
SQL> startup mount
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area 238034944 bytes
Fixed Size                  2174520 bytes
Variable Size             159384008 bytes
Database Buffers           71303168 bytes
Redo Buffers                5173248 bytes
Database mounted.

/****测试是否下面就要实例恢复***********
alter database open
SQL> alter database open;

Database altered.

/*****************看alert,确实发生了实例恢复***************/
alter database open
Beginning crash recovery of 1 threads
parallel recovery started with 3 processes --开启了并行恢复
Started redo scan　--实例恢复要应用在线日志
Completed redo scan
read 836 KB redo, 421 data blocks need recovery
Started redo application at
Thread 1: logseq 822, block 30632　--自异常中断库当前重作日志的rba为822.30632
Recovery of Online Redo Log: Thread 1 Group 8 Seq 822 Reading mem 0
Mem# 0: D:\ORACLE11G_64BIT\ORADATA\ORCL\REDO08.LOG
Completed redo application of 0.61MB
Completed crash recovery at
Thread 1: logseq 822, block 32304, scn 10588920 --中止于异常中断库当前重作日志的rba为822.32304
421 data blocks read, 421 data blocks written, 836 redo k-bytes read

/************附上shutdown abort之前的日志信息*******/
SQL> select * from v$log;

    GROUP#    THREAD# SEQUENCE#      BYTES BLOCKSIZE    MEMBERS ARCHIVED STATUS           FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- -------- ---------------- ------------- ----------- ------------ -----------
         3          1        817   52428800        512          1 NO       INACTIVE              10453013 2013/3/24 1     10454569 2013/3/24 1
         4          1        818 209715200        512          1 NO       INACTIVE              10454569 2013/3/24 1     10492795 2013/3/24 1
         5          1        819 209715200        512          1 NO       INACTIVE              10492795 2013/3/24 1     10560992 2013/3/25 1
         6          1        820 209715200        512          1 NO       INACTIVE              10560992 2013/3/25 1     10561002 2013/3/25 1
         7          1        821 209715200        512          1 NO       INACTIVE              10561002 2013/3/25 1     10561202 2013/3/25 1
         8          1        822 209715200        512          1 NO       CURRENT               10561202 2013/3/25 1 281474976710              ---实例恢复就是操作这个日志文件
         9          1        816 209715200        512          1 NO       INACTIVE              10446578 2013/3/24 1     10453013 2013/3/24 1
        10          1        815   20971520        512          1 NO       INACTIVE              10445875 2013/3/24 1     10446578 2013/3/24 1

8 rows selected

/********数据库启动后,last_change#结束scn也为空，除非read only或begin backup********/
SQL> select checkpoint_change#,last_change# from v$datafil

CHECKPOINT_CHANGE# LAST_CHANGE#
------------------ ------------
          10588923
          10588923
          10588923
          10588923
          10588923
          10588923
          10588923
          10588923
          10588923
          10588923
          10588923

CHECKPOINT_CHANGE# LAST_CHANGE#
------------------ ------------
          10588923
          10588923
          10588923
          10588923
          10588923
          10588923
          10588923
          10588923
          10588923
          10588923

21 rows selected.

/*********再看下正常关闭库结束scn的情况,是否为数据文件的checkpoint_change#**********/

/---数据库open期间,数据文件的检查点scn及结束scn************/
SQL> select checkpoint_change#,last_change# from v$datafile;

21 rows selected.

/******数据库open期间数据文件头部**************/
SQL> select checkpoint_change# from v$datafile_header;

CHECKPOINT_CHANGE#
------------------
          10588923
          10588923
          10588923
          10588923
          10588923
          10588923
          10588923
          10588923
          10588923
          10588923
          10588923

CHECKPOINT_CHANGE#
------------------
          10588923
          10588923
          10588923
          10588923
          10588923
          10588923
          10588923
          10588923
          10588923
          10588923

21 rows selected.

/**********控制文件***************/
SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#
------------------
10588923

/********日志文件*******发现当前日志文件的scn与数据文件及数据头和控制文件是一致的；这样就把日志文件，控制文件，数据文件结合起来了************/
SQL> select * from v$log;

    GROUP#    THREAD# SEQUENCE#      BYTES BLOCKSIZE    MEMBERS ARCHIVED STATUS           FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- -------- ---------------- ------------- ----------- ------------ -----------
         3          1        817   52428800        512          1 NO       INACTIVE              10453013 2013/3/24 1     10454569 2013/3/24 1
         4          1        818 209715200        512          1 NO       INACTIVE              10454569 2013/3/24 1     10492795 2013/3/24 1
         5          1        819 209715200        512          1 NO       INACTIVE              10492795 2013/3/24 1     10560992 2013/3/25 1
         6          1        820 209715200        512          1 NO       INACTIVE              10560992 2013/3/25 1     10561002 2013/3/25 1
         7          1        821 209715200        512          1 NO       INACTIVE              10561002 2013/3/25 1     10561202 2013/3/25 1
         8          1        822 209715200        512          1 NO       INACTIVE              10561202 2013/3/25 1     10588922 2013/3/25 1
         9          1        816 209715200        512          1 NO       INACTIVE              10446578 2013/3/24 1     10453013 2013/3/24 1
        10          1        823   20971520        512          1 NO       CURRENT               10588922 2013/3/25 1 281474976710 --当前日志文件的first_change#比数据文件的checkpoint_change#小１

8 rows selected

/**********关库***********/
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

/*********启动库到mount**************/
SQL> startup mount
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

/********正常关库启动后控制文件，数据文件，数据文件头三者检查点scn是一致；且数据文件的检查点scn与结束scn是一样的，不用实例恢复**********/
SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#
------------------
10590727

SQL> select checkpoint_change# from v$datafile_header;

CHECKPOINT_CHANGE#
------------------
          10590727
          10590727
          10590727
          10590727
          10590727
          10590727
          10590727
          10590727
          10590727
          10590727
          10590727

CHECKPOINT_CHANGE#
------------------
          10590727
          10590727
          10590727
          10590727
          10590727
          10590727
          10590727
          10590727
          10590727
          10590727

21 rows selected.

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

CHECKPOINT_CHANGE# LAST_CHANGE#
------------------ ------------
          10590727     10590727
          10590727     10590727
          10590727     10590727
          10590727     10590727
          10590727     10590727
          10590727     10590727
          10590727     10590727
          10590727     10590727
          10590727     10590727
          10590727     10590727
          10590727     10590727

21 rows selected.

SQL>

/********打开数据库*****未发现实例恢复操作***********/
SQL> alter database open;

Database altered.

alter database open
Mon Mar 25 19:34:24 2013
Thread 1 opened at log sequence 823
Current log# 10 seq# 823 mem# 0: D:\ORACLE11G_64BIT\ORADATA\ORCL\REDO10.LOG
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Mon Mar 25 19:34:24 2013
SMON: enabling cache recovery
Successfully onlined Undo Tablespace 2.
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Database Characterset is AL32UTF8
No Resource Manager plan active
Starting background process QMNC
Mon Mar 25 19:34:28 2013
QMNC started with pid=22, OS id=8208
Completed: alter database open
/**********正常关闭库打开库数据文件的结束scn,last_change#还是设置为空/
SQL> select checkpoint_change#,last_change# from v$datafile

CHECKPOINT_CHANGE# LAST_CHANGE#
------------------ ------------
          10590730
          10590730
          10590730
          10590730
          10590730
          10590730
          10590730
          10590730
          10590730
          10590730
          10590730

CHECKPOINT_CHANGE# LAST_CHANGE#
------------------ ------------
          10590730
          10590730
          10590730
          10590730
          10590730
          10590730
          10590730
          10590730
          10590730
          10590730

21 rows selected.

SQL>

/*********实例恢复是从当前日志的first_change#到下面查询的scn之间应用日志*************/
SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
594373
如果需要进行实例恢复，则需要恢复的记录为587705至594373中redo log中的记录。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9240380/viewspace-757067/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9240380/viewspace-757067/