[20171122]rman backup as copy的备份问题.txt
--//以前曾经写过一篇[20160524]rman备份与检查点4.txt=>链接:http://blog.itpub.net/267265/viewspace-2106087/
--//里面提到加入备份时间很长,这样可能会出现数据块里面的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 Production
CREATE 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 13279876494
SCOTT@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 4993
2.测试前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 stored
RMAN> 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
done
4.开始测试:
--//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:45
Starting 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 4993
BBED> set filename '/home/oracle/backup/tea01.dbf'
FILENAME /home/oracle/backup/tea01.dbf
BBED> set block 4993
BLOCK# 4993
BBED> p kdbr
...
sb2 kdbr[191] @492 3785
sb2 kdbr[192] @494 3762
BBED> x /rnnn *kdbr[192]
rowdata[0] @3854
----------
flag@3854: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@3855: 0x01
cols@3856: 3
col 0[3] @3857: 192
col 1[7] @3861: 13279881551
col 2[7] @3869: 13279881551
BBED> 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 0
select 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 3
select 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.