RMAN备份过程中会写脏块吗？

àì夳堔傛蜴生んèń 2022-08-11 16:50 2阅读 0赞

一次在群里看见有人讨论：  
数据库在做rman备份的时候比如是10点整，数据块的scn号被锁定在10点这个时间点，这时候用户在10：05做操作的时候产生的数据是向数据块里写？还是只生成日志 等rman完了再写。  
自己试验探讨一下（不知对否，互相交流）  
思路：  
创建一个每秒向一张表插入一行记录的job，并且每行记录alter system checkpoint确保触发DBWR；  
1：执行JOB、2：开始备份、3、新环境用该备份恢复  
如果备份过程中不写脏块，开始备份时锁定所有数据块SCN不在增大，因为备份有UNDO表空间，那么restore until开始备份时间，可以执行。

如果备份过程中一直在写脏块，随着备份时间增加，数据块SCN不断增大，那么只有restore until 备份结束时间，应用日志，在可以执行。

1、创建1张表
    create table system_scn_time(scn varchar2(10),scn_time varchar2(40),systime varchar2(30)) tablespace system;

2、创建存储过程
    alter session set nls_date_format = 'yyyy-dd-mm hh24-MI-Ss';
    
    create or replace procedure proc_test as 
    begin
      insert into system_scn_time(scn,scn_time,systime)                                                                            
      select dbms_flashback.get_system_change_number, SCN_TO_TIMESTAMP(dbms_flashback.get_system_change_number),sysdate from dual;
      commit;
      execute immediate 'alter system checkpoint';
    end;
    /

3、新建JOB每秒执行一次JOB
    VAR JOB NUMBER
    BEGIN
      DBMS_JOB.SUBMIT
        ( job      => :job
        ,what      => 'proc_test;'
        ,next_date => sysdate
        ,interval  => 'sysdate + 1/ (24*60*60*5)'
        ,no_parse  => TRUE
        );
    END;
    /
    （有个疑问interval不论我设置'sysdate + 1/ (24*60*60*5)'还是'sysdate + 1/ (24*60*60)'，间隔基本都为5秒）
    
    SYS@ fyl>select job from dba_jobs;
           JOB
    ----------
          4001
          4002
            42
           
    exec dbms_job.run(42);
    EXEC DBMS_JOB.BROKEN(42,FALSE);
    EXEC DBMS_JOB.remove(42);

4、数据库备份0级
    [fyl]数据库[0]级备份开始[2014/08/31 15:44:05] ...  
    [fyl]数据库[0]级备份结束[2014/08/31 15:45:39] ...  
    
    SYS@ fyl>select count(*) from system_scn_time;
    
      COUNT(*)
    ----------
            39
    
    SYS@ fyl>select * from system_scn_time;
    SCN                            SCN_TIME                                 SYSTIME
    ------------------------------ ---------------------------------------- ------------------------------
    650472                         31-AUG-14 03.43.55.000000000 PM          2014-31-08 15-43-56
    650479                         31-AUG-14 03.44.00.000000000 PM          2014-31-08 15-44-01
    650500                         31-AUG-14 03.44.04.000000000 PM          2014-31-08 15-44-06      ----15-44-05开始备份
    650508                         31-AUG-14 03.44.13.000000000 PM          2014-31-08 15-44-15
    650516                         31-AUG-14 03.44.19.000000000 PM          2014-31-08 15-44-21
    650525                         31-AUG-14 03.44.25.000000000 PM          2014-31-08 15-44-26
    650532                         31-AUG-14 03.44.29.000000000 PM          2014-31-08 15-44-31
    650540                         31-AUG-14 03.44.34.000000000 PM          2014-31-08 15-44-36
    650550                         31-AUG-14 03.44.40.000000000 PM          2014-31-08 15-44-41      
    650557                         31-AUG-14 03.44.45.000000000 PM          2014-31-08 15-44-46
    650565                         31-AUG-14 03.44.56.000000000 PM          2014-31-08 15-44-56
    650573                         31-AUG-14 03.44.59.000000000 PM          2014-31-08 15-45-01
    650581                         31-AUG-14 03.45.05.000000000 PM          2014-31-08 15-45-06
    650589                         31-AUG-14 03.45.10.000000000 PM          2014-31-08 15-45-11
    650596                         31-AUG-14 03.45.16.000000000 PM          2014-31-08 15-45-17
    650602                         31-AUG-14 03.45.20.000000000 PM          2014-31-08 15-45-22
    650612                         31-AUG-14 03.45.28.000000000 PM          2014-31-08 15-45-28
    650618                         31-AUG-14 03.45.33.000000000 PM          2014-31-08 15-45-33
    650634                         31-AUG-14 03.45.37.000000000 PM          2014-31-08 15-45-38        ---15-45-39结束备份
    650661                         31-AUG-14 03.45.42.000000000 PM          2014-31-08 15-45-43
    650701                         31-AUG-14 03.45.48.000000000 PM          2014-31-08 15-45-48
    650709                         31-AUG-14 03.45.52.000000000 PM          2014-31-08 15-45-53
    650716                         31-AUG-14 03.45.58.000000000 PM          2014-31-08 15-45-58

5、恢复备份，resetlog打开
    恢复前手动切几个日志，然后删除，防止恢复的时候应用online redo
    手动指定时间点
    RMAN> run{
    2> set until time "to_date('2014-08-31 15:45:00','yyyy-mm-dd hh24:mi:ss')";
    3> restore database;
    4> recover database;
    5> }
    
    executing command: SET until clause
    
    Starting restore at 31-AUG-14
    using channel ORA_DISK_1
    
    creating datafile file number=1 name=+ORADATA/fyl/datafile/system.260.845725985
    RMAN-00571: ===========================================================
    RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
    RMAN-00571: ===========================================================
    RMAN-03002: failure of restore command at 08/31/2014 16:09:28
    ORA-01180: can not create datafile 1
    ORA-01110: data file 1: '+ORADATA/fyl/datafile/system.260.845725985'

尝试过15-45-39之前任意时间都报错

RMAN> run{
    2> set until time "to_date('2014-08-31 15:45:39','yyyy-mm-dd hh24:mi:ss')";
    3> restore database;
    4> recover database;
    5> }
    
    executing command: SET until clause
    
    Starting restore at 31-AUG-14
    using channel ORA_DISK_1
    
    channel ORA_DISK_1: starting datafile backup set restore
    channel ORA_DISK_1: specifying datafile(s) to restore from backup set
    channel ORA_DISK_1: restoring datafile 00001 to +ORADATA/fyl/datafile/system.260.845725985
    channel ORA_DISK_1: restoring datafile 00002 to +ORADATA/fyl/datafile/sysaux.261.845726017
    channel ORA_DISK_1: restoring datafile 00003 to +ORADATA/fyl/datafile/undotbs1.262.845726043
    channel ORA_DISK_1: restoring datafile 00004 to +ORADATA/fyl/datafile/users.264.845726073
    channel ORA_DISK_1: reading from backup piece /oracle/rman_fyl/20140831_0/fyl_1_60_1sphbau5.dbf
    channel ORA_DISK_1: piece handle=/oracle/rman_fyl/20140831_0/fyl_1_60_1sphbau5.dbf tag=TAG20140831T154405
    channel ORA_DISK_1: restored backup piece 1
    channel ORA_DISK_1: restore complete, elapsed time: 00:01:25
    Finished restore at 31-AUG-14
    
    Starting recover at 31-AUG-14
    using channel ORA_DISK_1
    
    starting media recovery
    
    channel ORA_DISK_1: starting archived log restore to default destination
    channel ORA_DISK_1: restoring archived log
    archived log thread=1 sequence=4
    channel ORA_DISK_1: restoring archived log
    archived log thread=1 sequence=5
    channel ORA_DISK_1: reading from backup piece /oracle/rman_fyl/20140831_0/fyl_1_65_21phbb17.arc
    channel ORA_DISK_1: piece handle=/oracle/rman_fyl/20140831_0/fyl_1_65_21phbb17.arc tag=TAG20140831T154539
    channel ORA_DISK_1: restored backup piece 1
    channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
    archived log file name=/oracle/arch_fyl/1_4_857051384.dbf thread=1 sequence=4
    archived log file name=/oracle/arch_fyl/1_5_857051384.dbf thread=1 sequence=5
    unable to find archived log
    archived log thread=1 sequence=6   ---手动切的日志，然后删除了
    RMAN-00571: ===========================================================
    RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
    RMAN-00571: ===========================================================
    RMAN-03002: failure of recover command at 08/31/2014 16:11:13
    RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 6 and starting SCN of 650648
    
    RMAN> alter database open resetlogs;

alter日志

Sun Aug 31 16:00:32 2014
    Checker run found 5 new persistent data failures
    Sun Aug 31 16:09:48 2014
    Full restore complete of datafile 4 +ORADATA/fyl/datafile/users.264.845726073.  Elapsed time: 0:00:06 
      checkpoint is 650498                             -------650498开始备份时间点对于的SCN由快照控制文件记录
      last deallocation scn is 551013               -------？没找到什么意思~
    Sun Aug 31 16:10:20 2014
    Full restore complete of datafile 3 +ORADATA/fyl/datafile/undotbs1.262.845726043.  Elapsed time: 0:00:38 
      checkpoint is 650498                           -------650498开始备份时间点对于的SCN由快照控制文件记录
      last deallocation scn is 627897             -------？
      Undo Optimization current scn is 624066        -------？
    Sun Aug 31 16:10:52 2014
    Full restore complete of datafile 2 +ORADATA/fyl/datafile/sysaux.261.845726017.  Elapsed time: 0:01:10 
      checkpoint is 650498                           -------650498开始备份时间点对于的SCN由快照控制文件记录
      last deallocation scn is 648869               -------？
    Sun Aug 31 16:11:07 2014
    Full restore complete of datafile 1 +ORADATA/fyl/datafile/system.260.845725985.  Elapsed time: 0:01:25 
      checkpoint is 650498                         -------650498开始备份时间点对于的SCN由快照控制文件记录
      last deallocation scn is 650368                      -------？
      Undo Optimization current scn is 624066        -------？
    Sun Aug 31 16:11:10 2014
    alter database recover datafile list clear

6、查看system_scn_time表
    
    SYS@ fyl>select count(*) from system_scn_time;
      COUNT(*)
    ----------
            24
    SCN                            SCN_TIME                                 SYSTIME
    ------------------------------ ---------------------------------------- ------------------------------
    650451                         31-AUG-14 03.43.40.000000000 PM          2014-31-08 15-43-42
    650458                         31-AUG-14 03.43.45.000000000 PM          2014-31-08 15-43-46
    650465                         31-AUG-14 03.43.50.000000000 PM          2014-31-08 15-43-51
    650472                         31-AUG-14 03.43.55.000000000 PM          2014-31-08 15-43-56
    650479                         31-AUG-14 03.44.00.000000000 PM          2014-31-08 15-44-01
    650500                         31-AUG-14 03.44.04.000000000 PM          2014-31-08 15-44-06             ----15-44-05开始备份
    650508                         31-AUG-14 03.44.13.000000000 PM          2014-31-08 15-44-15
    650516                         31-AUG-14 03.44.19.000000000 PM          2014-31-08 15-44-21
    650525                         31-AUG-14 03.44.25.000000000 PM          2014-31-08 15-44-26
    650532                         31-AUG-14 03.44.29.000000000 PM          2014-31-08 15-44-31
    650540                         31-AUG-14 03.44.34.000000000 PM          2014-31-08 15-44-36
    650550                         31-AUG-14 03.44.40.000000000 PM          2014-31-08 15-44-41
    650557                         31-AUG-14 03.44.45.000000000 PM          2014-31-08 15-44-46
    650565                         31-AUG-14 03.44.56.000000000 PM          2014-31-08 15-44-56
    650573                         31-AUG-14 03.44.59.000000000 PM          2014-31-08 15-45-01
    650581                         31-AUG-14 03.45.05.000000000 PM          2014-31-08 15-45-06
    650589                         31-AUG-14 03.45.10.000000000 PM          2014-31-08 15-45-11
    650596                         31-AUG-14 03.45.16.000000000 PM          2014-31-08 15-45-17
    650602                         31-AUG-14 03.45.20.000000000 PM          2014-31-08 15-45-22
    650612                         31-AUG-14 03.45.28.000000000 PM          2014-31-08 15-45-28
    650618                         31-AUG-14 03.45.33.000000000 PM          2014-31-08 15-45-33
    650634                         31-AUG-14 03.45.37.000000000 PM          2014-31-08 15-45-38         ---15-45-39结束备份
    650908                         31-AUG-14 04.12.32.000000000 PM          2014-31-08 16-12-33        --resetlogs open后job的插入
    650929                         31-AUG-14 04.12.37.000000000 PM          2014-31-08 16-12-37

7、RMAN备份是一种物理的备份，它直接去读取数据块，因此rman是块级别的备份。从备份的那个时间点开始rman将锁定此刻的数据文件信息，也就是说只是备份数据文件到此刻的信息为止。但是rman并不锁定数据文件的使用，也就是说rman的备份，不是数据库一致性状态的备份，由于rman备份是块级别的，它只备份控制文件中已经存在的数据块，同时数据库还在运行之中，那么就有可能会出现某些已经提交的操作，但是dbwn还没有写入数据文件，或者已经被rman备份过的数据块，又重新被修改，等等。这些信息rman备份都不会记录，也是rman无法记录的。但是记录这些信息的是redo file，所以在rman完毕建议马上执行日志切换，然后备份归档日志，因为在rman恢复过程中，对于inconsistent backup，RMAN要靠这些已经归档的redo file信息恢复和保持数据库的一直状态。  
由此，我们可以可以看出，其实归档文件中真正有用的是从rman备份开始到rman备份结束时刻系统产生的归档日志。同时rman在恢复的时候，restore database完毕后，会依次利用归档日志和联机日志进行完全恢复。此时利用的这些归档就是从rman备份开始到rman备份结束产生的归档日志。  
  
8、这么验证麻烦了，假设RMAN备份时,DBWR不写脏块的话，随着DML语句执行，BUFFER CACHE一定会找不到free budder，数据库肯定就hang住了。所以肯定会写的！