[20171201]关于rman备份疑问3.txt

[20171201]关于rman备份疑问3.txt

--//上午排除我几天在做rman测试的疑问.
--//链接如下:http://blog.itpub.net/267265/viewspace-2148029/
--//顺便测试备份集包含4,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 d1.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 11M 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 11M 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 11M 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 11M 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 11M 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<=2e5;
create table t02 tablespace t02 as select rownum id ,to_char(rownum,'FM000000')||lpad('B',26,'B') name from dual connect by level<=2e5;
create table t03 tablespace t03 as select rownum id ,to_char(rownum,'FM000000')||lpad('C',26,'C') name from dual connect by level<=2e5;
create table t04 tablespace t04 as select rownum id ,to_char(rownum,'FM000000')||lpad('D',26,'D') name from dual connect by level<=2e5;
--create table t05 tablespace t05 as select rownum id ,to_char(rownum,'FM000000')||lpad('E',26,'E') name from dual connect by level<=2e5;

alter system checkpoint;
alter system checkpoint;
alter system checkpoint;

--//修改rman配置:
RMAN> CONFIGURE CHANNEL 1 DEVICE TYPE DISK RATE 256 K;
old RMAN configuration parameters:
CONFIGURE CHANNEL 1 DEVICE TYPE DISK RATE 128 K;
new RMAN configuration parameters:
CONFIGURE CHANNEL 1 DEVICE TYPE DISK RATE 256 K;
new RMAN configuration parameters are successfully stored
--//主要目的减慢备份速度。这样4秒读取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    2166     USERS                ***     /mnt/ramdisk/book/users01.dbf
5    346      EXAMPLE              ***     /mnt/ramdisk/book/example01.dbf
6    42       TEA                  ***     /mnt/ramdisk/book/tea01.dbf
7    6        T01                  ***     /mnt/ramdisk/book/T01.dbf
8    6        T02                  ***     /mnt/ramdisk/book/T02.dbf
9    6        T03                  ***     /mnt/ramdisk/book/T03.dbf
10   6        T04                  ***     /mnt/ramdisk/book/T04.dbf
11   6        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.测试脚本:
--//测试备份4个数据文件的作为一个备份集合的情况:
$ cat t4.sh
#! /bin/bash
rman target / <<EOF  >> /tmp/t4.txt &
backup  datafile 7,8,9,10 format '/home/oracle/backup/t1234_%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;
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

--//首先说明一点,一开始我认为很简单,应该每个文件缓存4M,实际上我测试多次,好像不是.因为oracle备份集建立是交错备份的,如果在一个备份集4个文件,
--//每个数据文件前面1M是os,文件头,位图区.需要4秒完成(前面我设置disk rate 256K).这样至少16秒才读取写入表数据.
--//我分别测试延迟3,7,11,15集中情况,实际上你可以不用等备份结束(当然备份文件集也不存在了,我按ctrl+c中断).只要出现小写aaaa,bbbb,cccc,dddd,eeee就ok了.
--//每次测试完成,执行d1.txt脚本,删除重新建立表空间等,重新测试.

3.测试:
$ . t4.sh 3
$ . search_abced.sh t1234_961602792_g0sl1p78_1_1
20949481        & 083700aaaaaaaaaaaaaaaaaaaaaaaaaa,
17805067  062500bbbbbbbbbbbbbbbbbbbbbbbbbb,
6279366  000100cccccccccccccccccccccccccc,
7327942  000100dddddddddddddddddddddddddd,

$ . t4.sh 7
$ . search_abced.sh t1234_961603174_g2sl1pj6_1_1
20949481        & 083700aaaaaaaaaaaaaaaaaaaaaaaaaa,
21998057        & 083700bbbbbbbbbbbbbbbbbbbbbbbbbb,
18853643  062500cccccccccccccccccccccccccc,
7327942  000100dddddddddddddddddddddddddd,

$ . t4.sh 11
$ . search_abced.sh t1234_961603503_g4sl1ptf_1_1
20949481        & 083700aaaaaaaaaaaaaaaaaaaaaaaaaa,
21998057        & 083700bbbbbbbbbbbbbbbbbbbbbbbbbb,
23046633        & 083700cccccccccccccccccccccccccc,
19902219  062500dddddddddddddddddddddddddd,

$ . search_abced.sh t1234_961603920_g6sl1qag_1_1
20949481        & 083700aaaaaaaaaaaaaaaaaaaaaaaaaa,
21998057        & 083700bbbbbbbbbbbbbbbbbbbbbbbbbb,
23046633        & 083700cccccccccccccccccccccccccc,
24095209        & 083700dddddddddddddddddddddddddd,

4.分析:
--//字母a b c e 前数字对应id,行号.注意看的数字:83700,62500,100,仅仅出现3种.表的建立方式一样的,出现在块中位置也应该一样.

SCOTT@book> column name format a33
SCOTT@book> select rowid,t01.* from t01 where id=83700;
ROWID                      ID NAME
------------------ ---------- ---------------------------------
AAAWJ+AAHAAAAKCACJ      83700 083700AAAAAAAAAAAAAAAAAAAAAAAAAA

SCOTT@book> select rowid,t01.* from t01 where id=62500;
ROWID                      ID NAME
------------------ ---------- ---------------------------------
AAAWJ+AAHAAAAICABp      62500 062500AAAAAAAAAAAAAAAAAAAAAAAAAA

SCOTT@book> select rowid,t01.* from t01 where id=100;
ROWID                      ID NAME
------------------ ---------- ---------------------------------
AAAWJ+AAHAAAACDABj        100 000100AAAAAAAAAAAAAAAAAAAAAAAAAA

SCOTT@book> @ &r/rowid AAAWJ+AAHAAAAKCACJ
    OBJECT       FILE      BLOCK        ROW ROWID_DBA            DBA                  TEXT
---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------
     90750          7        642        137  0x1C00282           7,642                alter system dump datafile 7 block 642 ;

SCOTT@book> @ &r/rowid AAAWJ+AAHAAAAICABp
    OBJECT       FILE      BLOCK        ROW ROWID_DBA            DBA                  TEXT
---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------
     90750          7        514        105  0x1C00202           7,514                alter system dump datafile 7 block 514 ;

SCOTT@book> @ &r/rowid AAAWJ+AAHAAAACDABj
    OBJECT       FILE      BLOCK        ROW ROWID_DBA            DBA                  TEXT
---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------
     90750          7        131         99  0x1C00083           7,131                alter system dump datafile 7 block 131 ;

--//642*8192/1024/1024=5.015625,0.015625*1024*1024/8192=2
--//514*8192/1024/1024=4.015625
--//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
------------------------------------
3             5      4     1    1
7             5      5     4    1
11            5      5     5    4
15            5      5     5    5
-----------------------------------

--//我不敢乱下结论,留给大家分析....
--//实际上与我想像不一样,我一般理解16M ,每个文件占4M在input buffer,不断轮询写出.
--//从上面测试实际上开始T01 进入input buffer 是5M.(我的理解)
--//我没想到oracle这样加载数据文件到input buffer的,在开始阶段.

5.插曲:我发现备份时先生成的备份文件比实际上的备份大.
$ ls -l t1234_96160*
-rw-r----- 1 oracle oinstall 44138496 2017-12-01 15:56:08 t1234_961602792_g0sl1p78_1_1
-rw-r----- 1 oracle oinstall 44138496 2017-12-01 16:02:30 t1234_961603174_g2sl1pj6_1_1
-rw-r----- 1 oracle oinstall 46170112 2017-12-01 16:05:23 t1234_961603503_g4sl1ptf_1_1
                             ~~~~~~~~
--//注意看下划线内容.最后会修改为44138496.

$ ls -l t1234_96160*
-rw-r----- 1 oracle oinstall 44138496 2017-12-01 15:56:08 t1234_961602792_g0sl1p78_1_1
-rw-r----- 1 oracle oinstall 44138496 2017-12-01 16:02:30 t1234_961603174_g2sl1pj6_1_1
-rw-r----- 1 oracle oinstall 44138496 2017-12-01 16:07:59 t1234_961603503_g4sl1ptf_1_1
-rw-r----- 1 oracle oinstall 44138496 2017-12-01 16:14:56 t1234_961603920_g6sl1qag_1_1

--//%t 时间戳,实际上按照如下计算

SCOTT@book> @ &r/stamp 961602792
     STAMP STAMP_CONV_TIME
---------- -------------------
961602792 2017-12-01 15:53:12

$ cat stamp.sql
SELECT &&1 stamp,to_date(yyyy||'/'||mm||'/'||dd||' '||hh||':'||mi||':'||ss,'yyyy-mm-dd hh24:mi:ss') stamp_conv_time  from (
SELECT &&1
        ,FLOOR (&&1 / (86400*31*12))+1988 yyyy
        ,FLOOR (MOD (&&1 / (86400*31),12))+1 mm
        ,FLOOR (MOD (&&1 / 86400, 31))+1 dd
        ,FLOOR (MOD (&&1 / 3600, 24)) hh
        ,FLOOR (MOD (&&1 / 60, 60)) mi
        ,MOD (&&1, 60) ss
        from dual);
---------------------------------