本文共 7994 字，大约阅读时间需要 26 分钟。
[20171122]rman backup as copy的备份问题.txt
--//以前曾经写过一篇[20160524]rman备份与检查点4.txt=>链接:
--//里面提到加入备份时间很长,这样可能会出现数据块里面的scn号大于文件头scn号.我提到视图v$backup_datafile --//的ABSOLUTE_FUZZY_CHANGE#字段记录了备份时块最大的scn号.查询视图: select recid,file#,CHECKPOINT_CHANGE#,ABSOLUTE_FUZZY_CHANGE# from v$backup_datafile;--//我当时测试的是备份集方式,今天补充测试backup as copy模式记录在那个视图,验证自己的判断.
1.环境
SYS@book> @ &r/ver1 PORT_STRING VERSION BANNER ------------------------------ -------------- -------------------------------------------------------------------------------- x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionCREATE TABLESPACE TEA DATAFILE
'/mnt/ramdisk/book/tea01.dbf' SIZE 40M AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED LOGGING ONLINE EXTENT MANAGEMENT LOCAL AUTOALLOCATE BLOCKSIZE 8K SEGMENT SPACE MANAGEMENT MANUAL FLASHBACK ON;SCOTT@book> create table t1 tablespace tea as select rownum id ,lpad('A',32,'A') name from dual connect by level<=8e5;
Table created.SCOTT@book> select SEGMENT_TYPE,TABLESPACE_NAME,HEADER_FILE,HEADER_BLOCK,BYTES,BLOCKS from dba_segments where owner=user and segment_name='T1';
SEGMENT_TYPE TABLESPACE_NAME HEADER_FILE HEADER_BLOCK BYTES BLOCKS ------------ --------------- ----------- ------------ ------------ ------------ TABLE TEA 6 128 39845888 4864--//39845888/1024/1024=38M,占用数据文件开头38M,主要目的就是填满开头部分.
SCOTT@book> create table DEMO (id number, update_scn number, commit_scn number) tablespace tea;
Table created.SCOTT@book> insert into DEMO values (1,dbms_flashback.get_system_change_number,userenv('commitscn'));
1 row created. SCOTT@book> commit ; Commit complete.SCOTT@book> select rowid,demo.* from demo;
ROWID ID UPDATE_SCN COMMIT_SCN ------------------ ------------ ------------ ------------ AAAWKAAAGAAABOBAAA 1 13279876493 13279876494SCOTT@book> @ &r/rowid AAAWKAAAGAAABOBAAA
OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT ------------ ------------ ------------ ------------ -------------------- -------------------- ---------------------------------------- 90752 6 4993 0 0x1801381 6,4993 alter system dump datafile 6 block 49932.测试前rman配置:
RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET;
using target database control file instead of recovery catalog old RMAN configuration parameters: CONFIGURE DEVICE TYPE DISK PARALLELISM 3 BACKUP TYPE TO BACKUPSET; new RMAN configuration parameters: CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; new RMAN configuration parameters are successfully storedRMAN> configure channel 1 device type disk rate 256K;
new RMAN configuration parameters: CONFIGURE CHANNEL 1 DEVICE TYPE DISK RATE 256 K; new RMAN configuration parameters are successfully stored--//主要目的就是减慢备份速度,便于控制与操作.这样大约需要40*1024/256=160秒备份完成.
3.建立测试脚本:
$ cat ins.sh #! /bin/bash sleep 10 for i in $(seq 200) do sqlplus -s scott/book <<< "insert into DEMO values ($i,dbms_flashback.get_system_change_number,userenv('commitscn'));"; sqlplus -s scott/book <<< "alter system checkpoint;" sleep 0.5 done4.开始测试:
--//session 1,首先开始执行如下命令:
$ . ins.sh 2>/dev/null &--//session 2,执行rman备份:
RMAN> backup as copy datafile 6 format '/home/oracle/backup/%b'; Starting backup at 2017-11-22 15:40:50 using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile copy input datafile file number=00006 name=/mnt/ramdisk/book/tea01.dbf output file name=/home/oracle/backup/tea01.dbf tag=TAG20171122T154050 RECID=33 STAMP=960738218 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:02:55 channel ORA_DISK_1: throttle time: 0:02:48 Finished backup at 2017-11-22 15:43:45Starting Control File and SPFILE Autobackup at 2017-11-22 15:43:45
piece handle=/u01/app/oracle/fast_recovery_area/BOOK/autobackup/2017_11_22/o1_mf_s_960738225_f1bbokcm_.bkp comment=NONE Finished Control File and SPFILE Autobackup at 2017-11-22 15:43:46 --//120+55=175秒完成.5.检查:
RMAN> list datafilecopy all;List of Datafile Copies
=======================Key File S Completion Time Ckp SCN Ckp Time
------- ---- - ------------------- ---------- ------------------- 33 6 A 2017-11-22 15:43:38 13279878864 2017-11-22 15:40:50 Name: /home/oracle/backup/tea01.dbf Tag: TAG20171122T154050--//文件头scn=13279878864.
$ dbv file=/home/oracle/backup/tea01.dbf
DBVERIFY: Release 11.2.0.4.0 - Production on Wed Nov 22 15:44:44 2017 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. DBVERIFY - Verification starting : FILE = /home/oracle/backup/tea01.dbf DBVERIFY - Verification complete Total Pages Examined : 5376 Total Pages Processed (Data) : 4762 Total Pages Failing (Data) : 0 Total Pages Processed (Index): 0 Total Pages Failing (Index): 0 Total Pages Processed (Other): 129 Total Pages Processed (Seg) : 0 Total Pages Failing (Seg) : 0 Total Pages Empty : 485 Total Pages Marked Corrupt : 0 Total Pages Influx : 0 Total Pages Encrypted : 0 Highest block SCN : 394979664 (3.394979664)SCOTT@book> select power(2,32)*3+394979664 from dual ;
POWER(2,32)*3+394979664 ----------------------- 13279881552--//通过dbv查询最大scn=13279881552.换一句话讲如果你选择不完全恢复,scn在13279878864-13279881552之间的情况
--//rman备份不会选择这个备份.SCOTT@book> select rowid,demo.* from demo where COMMIT_SCN>=13279881552 or id=192;
ROWID ID UPDATE_SCN COMMIT_SCN ------------------ ------------ ------------ ------------ AAAWKAAAGAAABOBADA 192 13279881551 13279881551 AAAWKAAAGAAABOBADB 193 13279881564 13279881564 AAAWKAAAGAAABOBADC 194 13279881577 13279881577 AAAWKAAAGAAABOBADD 195 13279881590 13279881590 AAAWKAAAGAAABOBADE 196 13279881604 13279881604 AAAWKAAAGAAABOBADF 197 13279881617 13279881617 AAAWKAAAGAAABOBADG 198 13279881631 13279881631 AAAWKAAAGAAABOBADH 199 13279881644 13279881644 AAAWKAAAGAAABOBADI 200 13279881657 13279881657 9 rows selected.--//也就是如果检查备份数据库应该仅仅看到id=192的记录.后面的记录应该无法看到.
SCOTT@book> @ &r/rowid AAAWKAAAGAAABOBADB
OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT ------------ ------------ ------------ ------------ -------------------- -------------------- ---------------------------------------- 90752 6 4993 193 0x1801381 6,4993 alter system dump datafile 6 block 4993BBED> set filename '/home/oracle/backup/tea01.dbf'
FILENAME /home/oracle/backup/tea01.dbfBBED> set block 4993
BLOCK# 4993BBED> p kdbr
... sb2 kdbr[191] @492 3785 sb2 kdbr[192] @494 3762BBED> x /rnnn *kdbr[192]
rowdata[0] @3854 ---------- flag@3854: 0x2c (KDRHFL, KDRHFF, KDRHFH) lock@3855: 0x01 cols@3856: 3col 0[3] @3857: 192
col 1[7] @3861: 13279881551 col 2[7] @3869: 13279881551BBED> x /rnnn *kdbr[193]
BBED-00401: out of range array index (193) --//也验证自己判断!!BBED> p /d kcbh
struct kcbh, 20 bytes @0 ub1 type_kcbh @0 6 ub1 frmt_kcbh @1 162 ub1 spare1_kcbh @2 0 ub1 spare2_kcbh @3 0 ub4 rdba_kcbh @4 25170817 ub4 bas_kcbh @8 394979664 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ub2 wrp_kcbh @12 3 ub1 seq_kcbh @14 3 ub1 flg_kcbh @15 6 (KCBHFDLC, KCBHFCKV) ub2 chkval_kcbh @16 1821 ub2 spare3_kcbh @18 0select 13279881552,trunc(13279881552/power(2,32)) scn_wrap,mod(13279881552,power(2,32)) scn_base from dual
13279881552 SCN_WRAP SCN_BASE SCN_WRAP16 SCN_BASE16 ------------ ------------ ------------ ---------- ---------- 13279881552 3 394979664 3 178ae950--//也能对上.
BBED> p /d block 1 kcvfh.kcvfhckp.kcvcpscn
struct kcvcpscn, 8 bytes @484 ub4 kscnbas @484 394976976 ub2 kscnwrp @488 3select 13279878864,trunc(13279878864/power(2,32)) scn_wrap,mod(13279878864,power(2,32)) scn_base from dual
13279878864 SCN_WRAP SCN_BASE SCN_WRAP16 SCN_BASE16 ------------ ------------ ------------ ---------- ---------- 13279878864 3 394976976 3 178aded0 --//文件头scn也能对上.6.到底那个视图记录这个最高的scn呢?
--//检查发现记录在v$datafile_copy视图中. SCOTT@book> colu name format a40 SCOTT@book> select recid,file#,NAME,CHECKPOINT_CHANGE#,ABSOLUTE_FUZZY_CHANGE# from v$datafile_copy where recid=33 ; RECID FILE# NAME CHECKPOINT_CHANGE# ABSOLUTE_FUZZY_CHANGE# ------------ ------------ ---------------------------------------- ------------------ ---------------------- 33 6 /home/oracle/backup/tea01.dbf 13279878864 13279881553--//ABSOLUTE_FUZZY_CHANGE#比dbv检查看到最大scn多1.
转载地址：http://yxoso.baihongyu.com/