[20171204]关于rman备份疑问4.txt
--//上午排除我几天在做rman测试的疑问.
--//链接如下:http://blog.itpub.net/267265/viewspace-2148029/
--//顺便测试备份集包含5个数据文件的情况(本来不想做,还是做看看),验证自己的判断是否正确.
1.环境:
SCOTT@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
$ cat d2.txt
drop tablespace t01 including contents and datafiles;
drop tablespace t02 including contents and datafiles;
drop tablespace t03 including contents and datafiles;
drop tablespace t04 including contents and datafiles;
drop tablespace t05 including contents and datafiles;
CREATE TABLESPACE T01 DATAFILE '/mnt/ramdisk/book/T01.dbf' SIZE 6M AUTOEXTEND OFF LOGGING ONLINE EXTENT MANAGEMENT LOCAL AUTOALLOCATE BLOCKSIZE 8K SEGMENT SPACE MANAGEMENT AUTO FLASHBACK ON;
CREATE TABLESPACE T02 DATAFILE '/mnt/ramdisk/book/T02.dbf' SIZE 6M AUTOEXTEND OFF LOGGING ONLINE EXTENT MANAGEMENT LOCAL AUTOALLOCATE BLOCKSIZE 8K SEGMENT SPACE MANAGEMENT AUTO FLASHBACK ON;
CREATE TABLESPACE T03 DATAFILE '/mnt/ramdisk/book/T03.dbf' SIZE 6M AUTOEXTEND OFF LOGGING ONLINE EXTENT MANAGEMENT LOCAL AUTOALLOCATE BLOCKSIZE 8K SEGMENT SPACE MANAGEMENT AUTO FLASHBACK ON;
CREATE TABLESPACE T04 DATAFILE '/mnt/ramdisk/book/T04.dbf' SIZE 6M AUTOEXTEND OFF LOGGING ONLINE EXTENT MANAGEMENT LOCAL AUTOALLOCATE BLOCKSIZE 8K SEGMENT SPACE MANAGEMENT AUTO FLASHBACK ON;
CREATE TABLESPACE T05 DATAFILE '/mnt/ramdisk/book/T05.dbf' SIZE 6M AUTOEXTEND OFF LOGGING ONLINE EXTENT MANAGEMENT LOCAL AUTOALLOCATE BLOCKSIZE 8K SEGMENT SPACE MANAGEMENT AUTO FLASHBACK ON;
create table t01 tablespace t01 as select rownum id ,to_char(rownum,'FM000000')||lpad('A',26,'A') name from dual connect by level<=1e5;
create table t02 tablespace t02 as select rownum id ,to_char(rownum,'FM000000')||lpad('B',26,'B') name from dual connect by level<=1e5;
create table t03 tablespace t03 as select rownum id ,to_char(rownum,'FM000000')||lpad('C',26,'C') name from dual connect by level<=1e5;
create table t04 tablespace t04 as select rownum id ,to_char(rownum,'FM000000')||lpad('D',26,'D') name from dual connect by level<=1e5;
create table t05 tablespace t05 as select rownum id ,to_char(rownum,'FM000000')||lpad('E',26,'E') name from dual connect by level<=1e5;
alter system checkpoint;
alter system checkpoint;
alter system checkpoint;
--//修改rman配置:
RMAN> CONFIGURE CHANNEL 1 DEVICE TYPE DISK RATE 128 K;
new RMAN configuration parameters:
CONFIGURE CHANNEL 1 DEVICE TYPE DISK RATE 128 K;
new RMAN configuration parameters are successfully stored
released channel: ORA_DISK_1
--//主要目的减慢备份速度。这样8秒读取1M.
RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 1 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> report schema;
using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name BOOK
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 760 SYSTEM *** /mnt/ramdisk/book/system01.dbf
2 940 SYSAUX *** /mnt/ramdisk/book/sysaux01.dbf
3 1075 UNDOTBS1 *** /mnt/ramdisk/book/undotbs01.dbf
4 256 USERS *** /mnt/ramdisk/book/users01.dbf
5 346 EXAMPLE *** /mnt/ramdisk/book/example01.dbf
6 40 TEA *** /mnt/ramdisk/book/tea01.dbf
7 11 T01 *** /mnt/ramdisk/book/T01.dbf
8 11 T02 *** /mnt/ramdisk/book/T02.dbf
9 11 T03 *** /mnt/ramdisk/book/T03.dbf
10 11 T04 *** /mnt/ramdisk/book/T04.dbf
11 11 T05 *** /mnt/ramdisk/book/T05.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 414 TEMP 32767 /mnt/ramdisk/book/temp01.dbf
2.测试脚本:
--//测试备份5个数据文件的作为一个备份集合的情况:
$ cat t5.sh
#! /bin/bash
rman target / <<EOF >> /tmp/t5.txt &
backup datafile 7,8,9,10,11 format '/home/oracle/backup/t12345_%t_%U' ;
quit
EOF
echo "sleep $1 "
sleep $1
sqlplus -s scott/book <<EOF
set numw 12
update t01 set name=lower(name) where mod(id,100)=0;
update t02 set name=lower(name) where mod(id,100)=0;
update t03 set name=lower(name) where mod(id,100)=0;
update t04 set name=lower(name) where mod(id,100)=0;
update t05 set name=lower(name) where mod(id,100)=0;
commit;
alter system checkpoint;
alter system checkpoint;
alter system checkpoint;
alter system checkpoint;
@ &r/scn
EOF
$ cat search_abced.sh
#! /bin/bash
strings -t d /home/oracle/backup/$1 | grep "aaaaa"|head -1
strings -t d /home/oracle/backup/$1 | grep "bbbbb"|head -1
strings -t d /home/oracle/backup/$1 | grep "ccccc"|head -1
strings -t d /home/oracle/backup/$1 | grep "ddddd"|head -1
strings -t d /home/oracle/backup/$1 | grep "eeeee"|head -1
--//首先说明一点,一开始我认为很简单,应该每个文件缓存2M,一个缓存512K实际上我测试多次,好像不是.因为oracle备份集建立是交错
--//备份的,如果在一个备份集5个文件,
--//每个数据文件前面1M是os,文件头,位图区.需要8秒完成(前面我设置disk rate 128).这样至少40秒才读取写入表数据.
--//我分别测试延迟3,7,11,15,19,23,27,31,35,39的情况,实际上你可以不用等备份结束(当然备份文件集也不存在了,我按ctrl+c中断).
--//只要出现小写aaaa,bbbb,cccc,dddd,eeee就ok了.
--//每次测试完成,执行d2.txt脚本,删除重新建立表空间等,重新测试.
3.测试:
$ . t5.sh 3
$ . search_abced.sh t12345_961839133_ggsl900t_1_1
18294455 051800aaaaaaaaaaaaaaaaaaaaaaaaaa,
16217130 041300bbbbbbbbbbbbbbbbbbbbbbbbbb,
6262982 000100cccccccccccccccccccccccccc,
6787270 000100dddddddddddddddddddddddddd,
7311558 000100eeeeeeeeeeeeeeeeeeeeeeeeee,
$ . t5.sh 7
$ . search_abced.sh t12345_961839430_gisl90a6_1_1
18294455 051800aaaaaaaaaaaaaaaaaaaaaaaaaa,
18818743 051800bbbbbbbbbbbbbbbbbbbbbbbbbb,
16741418 041300cccccccccccccccccccccccccc,
6787270 000100dddddddddddddddddddddddddd,
7311558 000100eeeeeeeeeeeeeeeeeeeeeeeeee,
$ . t5.sh 11
$ . search_abced.sh t12345_961839730_gksl90ji_1_1
18294455 051800aaaaaaaaaaaaaaaaaaaaaaaaaa,
18818743 051800bbbbbbbbbbbbbbbbbbbbbbbbbb,
19343031 051800cccccccccccccccccccccccccc,
17265706 041300dddddddddddddddddddddddddd,
7311558 000100eeeeeeeeeeeeeeeeeeeeeeeeee,
$ . t5.sh 15
$ . search_abced.sh t12345_961840059_gmsl90tr_1_1
18294455 051800aaaaaaaaaaaaaaaaaaaaaaaaaa,
18818743 051800bbbbbbbbbbbbbbbbbbbbbbbbbb,
19343031 051800cccccccccccccccccccccccccc,
19867319 051800dddddddddddddddddddddddddd,
17789994 041300eeeeeeeeeeeeeeeeeeeeeeeeee,
$ . t5.sh 19
$ . search_abced.sh t12345_961840369_gosl917h_1_1
18294455 051800aaaaaaaaaaaaaaaaaaaaaaaaaa,
18818743 051800bbbbbbbbbbbbbbbbbbbbbbbbbb,
19343031 051800cccccccccccccccccccccccccc,
19867319 051800dddddddddddddddddddddddddd,
20391607 051800eeeeeeeeeeeeeeeeeeeeeeeeee,
$ . t5.sh 23
$ . search_abced.sh t12345_961840671_gqsl91gv_1_1
20934411 062500aaaaaaaaaaaaaaaaaaaaaaaaaa,
18818743 051800bbbbbbbbbbbbbbbbbbbbbbbbbb,
19343031 051800cccccccccccccccccccccccccc,
19867319 051800dddddddddddddddddddddddddd,
20391607 051800eeeeeeeeeeeeeeeeeeeeeeeeee,
$ . t5.sh 27
$ . search_abced.sh t12345_961840984_gssl91qo_1_1
20934411 062500aaaaaaaaaaaaaaaaaaaaaaaaaa,
21458699 062500bbbbbbbbbbbbbbbbbbbbbbbbbb,
19343031 051800cccccccccccccccccccccccccc,
19867319 051800dddddddddddddddddddddddddd,
20391607 051800eeeeeeeeeeeeeeeeeeeeeeeeee,
--//后面的延迟不做了.最后补充测试延迟39的情况:
$ . search_abced.sh t12345_961842370_gusl9362_1_1
20934411 062500aaaaaaaaaaaaaaaaaaaaaaaaaa,
21458699 062500bbbbbbbbbbbbbbbbbbbbbbbbbb,
21982987 062500cccccccccccccccccccccccccc,
22507275 062500dddddddddddddddddddddddddd,
23031563 062500eeeeeeeeeeeeeeeeeeeeeeeeee,
4.分析:
--//字母a b c d e 前数字对应id,行号.注意看的数字:62500,51800,41300,100,仅仅出现4种.表的建立方式一样的,出现在块中位置也应该一样.
SCOTT@book> column name format a33
SCOTT@book> select rowid,t01.* from t01 where id=62500;
ROWID ID NAME
------------------ ---------- ---------------------------------
AAAWKzAAHAAAAICABp 62500 062500aaaaaaaaaaaaaaaaaaaaaaaaaa
SCOTT@book> select rowid,t01.* from t01 where id=51800;
ROWID ID NAME
------------------ ---------- ---------------------------------
AAAWKzAAHAAAAHAACd 51800 051800aaaaaaaaaaaaaaaaaaaaaaaaaa
SCOTT@book> select rowid,t01.* from t01 where id=41300;
ROWID ID NAME
------------------ ---------- ---------------------------------
AAAWKzAAHAAAAGCABJ 41300 041300aaaaaaaaaaaaaaaaaaaaaaaaaa
SCOTT@book> select rowid,t01.* from t01 where id=100;
ROWID ID NAME
------------------ ---------- ---------------------------------
AAAWKzAAHAAAACDABj 100 000100aaaaaaaaaaaaaaaaaaaaaaaaaa
SCOTT@book> @ &r/rowid AAAWKzAAHAAAAICABp
OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT
---------- ---------- ---------- ---------- -------------------- ------- ----------------------------------------
90803 7 514 105 0x1C00202 7,514 alter system dump datafile 7 block 514 ;
SCOTT@book> @ &r/rowid AAAWKzAAHAAAAHAACd
OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT
---------- ---------- ---------- ---------- -------------------- ------- ----------------------------------------
90803 7 448 157 0x1C001C0 7,448 alter system dump datafile 7 block 448 ;
SCOTT@book> @ &r/rowid AAAWKzAAHAAAAGCABJ
OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT
---------- ---------- ---------- ---------- -------------------- ------- ----------------------------------------
90803 7 386 73 0x1C00182 7,386 alter system dump datafile 7 block 386 ;
SCOTT@book> @ &r/rowid AAAWKzAAHAAAACDABj
OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT
---------- ---------- ---------- ---------- -------------------- ------- ----------------------------------------
90803 7 131 99 0x1C00083 7,131 alter system dump datafile 7 block 131 ;
--//514*8192/1024/1024 = 4.015625,0.015625*1024*1024/8192=2
--//448*8192/1024/1024 = 3.5
--//386*8192/1024/1024 = 3.015625,0.015625*1024*1024/8192=2
--//131*8192/1024/1024=1.0234375 0.0234375*1024*1024/8192 = 3
--//为什么是3,因为128,129,130 都是assm的位图区.130是段头(三级位图).也就是131前面3块没有aaaa字符串.
SCOTT@book> select SEGMENT_NAME,HEADER_FILE,HEADER_BLOCK from dba_segments where segment_name='T01';
SEGMENT_NAME HEADER_FILE HEADER_BLOCK
-------------------- ----------- ------------
T01 7 130
--//画一个表格(出现小写位置):
延迟时间 t01 t02 t03 t04 t05
---------------------------------------
3 3.5 3 1 1 1
7 3.5 3.5 3 1 1
11 3.5 3.5 3.5 3 1
15 3.5 3.5 3.5 3.5 3
19 3.5 3.5 3.5 3.5 3.5
23 4 3.5 3.5 3.5 3.5
27 4 4 3.5 3.5 3.5
39 4 4 4 4 4
-------------------------------------------
--//我不敢乱下结论,留给大家分析....
--//实际上与我想像不一样,我一般理解16M(最大) ,每个文件占2M在input buffer(每个文件4个缓存,单个512K),不断轮询写出.
--//从上面测试实际上开始T01 进入input buffer 是3.5M.t02 是3M(我的理解),最后再慢慢平衡到每个文件2M的input buffer.
--//我没想到oracle这样加载数据文件到input buffer的,在开始阶段.
--//补充测试:
$ strings t12345_961839133_ggsl900t_1_1 | tr 'abcde' 'ABCDE' | egrep 'AAAAA|BBBBB|CCCCC|DDDDD|EEEE'| cut -c20-30 | uniq -c
9978 AAAAAAAAAAA
9978 BBBBBBBBBBB
9978 CCCCCCCCCCC
9978 DDDDDDDDDDD
9978 EEEEEEEEEEE
10080 AAAAAAAAAAA
10080 BBBBBBBBBBB
10080 CCCCCCCCCCC
10080 DDDDDDDDDDD
10080 EEEEEEEEEEE
10416 AAAAAAAAAAA
10416 BBBBBBBBBBB
10416 CCCCCCCCCCC
10416 DDDDDDDDDDD
10416 EEEEEEEEEEE
10752 AAAAAAAAAAA
10752 BBBBBBBBBBB
10752 CCCCCCCCCCC
10752 DDDDDDDDDDD
10752 EEEEEEEEEEE
10416 AAAAAAAAAAA
10416 BBBBBBBBBBB
10416 CCCCCCCCCCC
10416 DDDDDDDDDDD
10416 EEEEEEEEEEE
10752 AAAAAAAAAAA
10752 BBBBBBBBBBB
10752 CCCCCCCCCCC
10752 DDDDDDDDDDD
10752 EEEEEEEEEEE
10416 AAAAAAAAAAA
10416 BBBBBBBBBBB
10416 CCCCCCCCCCC
10416 DDDDDDDDDDD
10416 EEEEEEEEEEE
10752 AAAAAAAAAAA
10752 BBBBBBBBBBB
10752 CCCCCCCCCCC
10752 DDDDDDDDDDD
10752 EEEEEEEEEEE
10416 AAAAAAAAAAA
10416 BBBBBBBBBBB
10416 CCCCCCCCCCC
10416 DDDDDDDDDDD
10416 EEEEEEEEEEE
6022 AAAAAAAAAAA
6022 BBBBBBBBBBB
6022 CCCCCCCCCCC
6022 DDDDDDDDDDD
6022 EEEEEEEEEEE
SCOTT@book> column PARTITION_NAME noprint
SCOTT@book> select * from dba_extents where segment_name='T01';
OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
------ ------------ ------------ --------------- ---------- ---------- ---------- ---------- ---------- ------------
SCOTT T01 TABLE T01 0 7 128 65536 8 7
SCOTT T01 TABLE T01 1 7 136 65536 8 7
SCOTT T01 TABLE T01 2 7 144 65536 8 7
SCOTT T01 TABLE T01 3 7 152 65536 8 7
SCOTT T01 TABLE T01 4 7 160 65536 8 7
SCOTT T01 TABLE T01 5 7 168 65536 8 7
SCOTT T01 TABLE T01 6 7 176 65536 8 7
SCOTT T01 TABLE T01 7 7 184 65536 8 7
SCOTT T01 TABLE T01 8 7 192 65536 8 7
SCOTT T01 TABLE T01 9 7 200 65536 8 7
SCOTT T01 TABLE T01 10 7 208 65536 8 7
SCOTT T01 TABLE T01 11 7 216 65536 8 7
SCOTT T01 TABLE T01 12 7 224 65536 8 7
SCOTT T01 TABLE T01 13 7 232 65536 8 7
SCOTT T01 TABLE T01 14 7 240 65536 8 7
SCOTT T01 TABLE T01 15 7 248 65536 8 7
SCOTT T01 TABLE T01 16 7 256 1048576 128 7
SCOTT T01 TABLE T01 17 7 384 1048576 128 7
SCOTT T01 TABLE T01 18 7 512 1048576 128 7
SCOTT T01 TABLE T01 19 7 640 1048576 128 7
20 rows selected.
--//512K,占512/8 = 64块.
SCOTT@book> select count(*) from t01 where DBMS_ROWID.ROWID_BLOCK_NUMBER (rowid) between 128 and 128+64-1 ;
COUNT(*)
----------
9978
--//出现A的次数是能对上的.
