本文共 12692 字，大约阅读时间需要 42 分钟。
[20171204]关于rman备份疑问4.txt
--//上午排除我几天在做rman测试的疑问.
--//链接如下: --//顺便测试备份集包含5个数据文件的情况(本来不想做,还是做看看),验证自己的判断是否正确.1.环境:
SCOTT@book> @ &r/ver1PORT_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 storedRMAN> 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.dbfList of Temporary Files
======================= File Size(MB) Tablespace Maxsize(MB) Tempfile Name ---- -------- -------------------- ----------- -------------------- 1 414 TEMP 32767 /mnt/ramdisk/book/temp01.dbf2.测试脚本: --//测试备份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 062500aaaaaaaaaaaaaaaaaaaaaaaaaaSCOTT@book> select rowid,t01.* from t01 where id=51800;
ROWID ID NAME ------------------ ---------- --------------------------------- AAAWKzAAHAAAAHAACd 51800 051800aaaaaaaaaaaaaaaaaaaaaaaaaaSCOTT@book> select rowid,t01.* from t01 where id=41300;
ROWID ID NAME ------------------ ---------- --------------------------------- AAAWKzAAHAAAAGCABJ 41300 041300aaaaaaaaaaaaaaaaaaaaaaaaaaSCOTT@book> select rowid,t01.* from t01 where id=100;
ROWID ID NAME ------------------ ---------- --------------------------------- AAAWKzAAHAAAACDABj 100 000100aaaaaaaaaaaaaaaaaaaaaaaaaaSCOTT@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 EEEEEEEEEEESCOTT@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的次数是能对上的.
转载地址：http://iwvja.baihongyu.com/