[20171204]关于rman备份疑问4.txt

[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的次数是能对上的.