[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);
---------------------------------
