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

来源：互联网发布：自然语言处理源码编辑：程序博客网时间：2024/05/16 13:37

一次在群里看见有人讨论：
数据库在做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每秒执行一次JOBVAR JOB NUMBERBEGIN  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(*)----------        39SYS@ fyl>select * from system_scn_time;SCN                            SCN_TIME                                 SYSTIME------------------------------ ---------------------------------------- ------------------------------650472                         31-AUG-14 03.43.55.000000000 PM          2014-31-08 15-43-56650479                         31-AUG-14 03.44.00.000000000 PM          2014-31-08 15-44-01650500                         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-15650516                         31-AUG-14 03.44.19.000000000 PM          2014-31-08 15-44-21650525                         31-AUG-14 03.44.25.000000000 PM          2014-31-08 15-44-26650532                         31-AUG-14 03.44.29.000000000 PM          2014-31-08 15-44-31650540                         31-AUG-14 03.44.34.000000000 PM          2014-31-08 15-44-36650550                         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-46650565                         31-AUG-14 03.44.56.000000000 PM          2014-31-08 15-44-56650573                         31-AUG-14 03.44.59.000000000 PM          2014-31-08 15-45-01650581                         31-AUG-14 03.45.05.000000000 PM          2014-31-08 15-45-06650589                         31-AUG-14 03.45.10.000000000 PM          2014-31-08 15-45-11650596                         31-AUG-14 03.45.16.000000000 PM          2014-31-08 15-45-17650602                         31-AUG-14 03.45.20.000000000 PM          2014-31-08 15-45-22650612                         31-AUG-14 03.45.28.000000000 PM          2014-31-08 15-45-28650618                         31-AUG-14 03.45.33.000000000 PM          2014-31-08 15-45-33650634                         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-43650701                         31-AUG-14 03.45.48.000000000 PM          2014-31-08 15-45-48650709                         31-AUG-14 03.45.52.000000000 PM          2014-31-08 15-45-53650716                         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 clauseStarting restore at 31-AUG-14using channel ORA_DISK_1creating datafile file number=1 name=+ORADATA/fyl/datafile/system.260.845725985RMAN-00571: ===========================================================RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============RMAN-00571: ===========================================================RMAN-03002: failure of restore command at 08/31/2014 16:09:28ORA-01180: can not create datafile 1ORA-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 clauseStarting restore at 31-AUG-14using channel ORA_DISK_1channel ORA_DISK_1: starting datafile backup set restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setchannel ORA_DISK_1: restoring datafile 00001 to +ORADATA/fyl/datafile/system.260.845725985channel ORA_DISK_1: restoring datafile 00002 to +ORADATA/fyl/datafile/sysaux.261.845726017channel ORA_DISK_1: restoring datafile 00003 to +ORADATA/fyl/datafile/undotbs1.262.845726043channel ORA_DISK_1: restoring datafile 00004 to +ORADATA/fyl/datafile/users.264.845726073channel ORA_DISK_1: reading from backup piece /oracle/rman_fyl/20140831_0/fyl_1_60_1sphbau5.dbfchannel ORA_DISK_1: piece handle=/oracle/rman_fyl/20140831_0/fyl_1_60_1sphbau5.dbf tag=TAG20140831T154405channel ORA_DISK_1: restored backup piece 1channel ORA_DISK_1: restore complete, elapsed time: 00:01:25Finished restore at 31-AUG-14Starting recover at 31-AUG-14using channel ORA_DISK_1starting media recoverychannel ORA_DISK_1: starting archived log restore to default destinationchannel ORA_DISK_1: restoring archived logarchived log thread=1 sequence=4channel ORA_DISK_1: restoring archived logarchived log thread=1 sequence=5channel ORA_DISK_1: reading from backup piece /oracle/rman_fyl/20140831_0/fyl_1_65_21phbb17.arcchannel ORA_DISK_1: piece handle=/oracle/rman_fyl/20140831_0/fyl_1_65_21phbb17.arc tag=TAG20140831T154539channel ORA_DISK_1: restored backup piece 1channel ORA_DISK_1: restore complete, elapsed time: 00:00:01archived log file name=/oracle/arch_fyl/1_4_857051384.dbf thread=1 sequence=4archived log file name=/oracle/arch_fyl/1_5_857051384.dbf thread=1 sequence=5unable to find archived logarchived 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:13RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 6 and starting SCN of 650648RMAN> alter database open resetlogs;

alter日志

Sun Aug 31 16:00:32 2014Checker run found 5 new persistent data failuresSun Aug 31 16:09:48 2014Full 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 2014Full 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 2014Full 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 2014Full 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 2014alter database recover datafile list clear

6、查看system_scn_time表SYS@ fyl>select count(*) from system_scn_time;  COUNT(*)----------        24SCN                            SCN_TIME                                 SYSTIME------------------------------ ---------------------------------------- ------------------------------650451                         31-AUG-14 03.43.40.000000000 PM          2014-31-08 15-43-42650458                         31-AUG-14 03.43.45.000000000 PM          2014-31-08 15-43-46650465                         31-AUG-14 03.43.50.000000000 PM          2014-31-08 15-43-51650472                         31-AUG-14 03.43.55.000000000 PM          2014-31-08 15-43-56650479                         31-AUG-14 03.44.00.000000000 PM          2014-31-08 15-44-01650500                         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-15650516                         31-AUG-14 03.44.19.000000000 PM          2014-31-08 15-44-21650525                         31-AUG-14 03.44.25.000000000 PM          2014-31-08 15-44-26650532                         31-AUG-14 03.44.29.000000000 PM          2014-31-08 15-44-31650540                         31-AUG-14 03.44.34.000000000 PM          2014-31-08 15-44-36650550                         31-AUG-14 03.44.40.000000000 PM          2014-31-08 15-44-41650557                         31-AUG-14 03.44.45.000000000 PM          2014-31-08 15-44-46650565                         31-AUG-14 03.44.56.000000000 PM          2014-31-08 15-44-56650573                         31-AUG-14 03.44.59.000000000 PM          2014-31-08 15-45-01650581                         31-AUG-14 03.45.05.000000000 PM          2014-31-08 15-45-06650589                         31-AUG-14 03.45.10.000000000 PM          2014-31-08 15-45-11650596                         31-AUG-14 03.45.16.000000000 PM          2014-31-08 15-45-17650602                         31-AUG-14 03.45.20.000000000 PM          2014-31-08 15-45-22650612                         31-AUG-14 03.45.28.000000000 PM          2014-31-08 15-45-28650618                         31-AUG-14 03.45.33.000000000 PM          2014-31-08 15-45-33650634                         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住了。所以肯定会写的！

0 0