[20171208]rman与truncate3.txt
--//前几天测试truncate表依旧备份一部分信息,测试几次确定备份8extent.当时的测试几个extents是相邻的.
--//今天补充测试如果数据分布是离散的,情况是否一样?
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
CREATE TABLESPACE T01 DATAFILE
'/mnt/ramdisk/book/T01.dbf' SIZE 21M AUTOEXTEND OFF
LOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;
create table t01a tablespace t01 as select rownum id ,to_char(rownum,'FM000000')||lpad('A',26,'A') name from dual connect by level<=100;
create table t02 tablespace t01 as select rownum id ,to_char(rownum,'FM000000')||lpad('B',26,'B') name from dual connect by level<=2e4;
create table t01b tablespace t01 as select rownum id ,to_char(rownum,'FM000000')||lpad('A',26,'A') name from dual connect by level<=100;
create table t03 tablespace t01 as select rownum id ,to_char(rownum,'FM000000')||lpad('C',26,'C') name from dual connect by level<=2e4;
create table t01c tablespace t01 as select rownum id ,to_char(rownum,'FM000000')||lpad('A',26,'A') name from dual connect by level<=100;
create table t04 tablespace t01 as select rownum id ,to_char(rownum,'FM000000')||lpad('D',26,'D') name from dual connect by level<=2e4;
create table t01d tablespace t01 as select rownum id ,to_char(rownum,'FM000000')||lpad('A',26,'A') name from dual connect by level<=100;
create table t05 tablespace t01 as select rownum id ,to_char(rownum,'FM000000')||lpad('E',26,'E') name from dual connect by level<=2e4;
create table t01e tablespace t01 as select rownum id ,to_char(rownum,'FM000000')||lpad('A',26,'A') name from dual connect by level<=100;
drop table t01a purge ;
drop table t01b purge ;
drop table t01c purge ;
drop table t01d purge ;
drop table t01e purge ;
create table t01 tablespace t01 as select rownum id ,to_char(rownum,'FM000000')||lpad('Z',26,'Z') name from dual connect by level<=1e5;
SCOTT@book> column PARTITION_NAME noprint
SCOTT@book> select * from dba_extents where segment_name='T01' order by block_id;
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 264 65536 8 7
SCOTT T01 TABLE T01 2 7 400 65536 8 7
SCOTT T01 TABLE T01 3 7 536 65536 8 7
SCOTT T01 TABLE T01 4 7 672 65536 8 7
SCOTT T01 TABLE T01 5 7 680 65536 8 7
SCOTT T01 TABLE T01 6 7 688 65536 8 7
SCOTT T01 TABLE T01 7 7 696 65536 8 7
SCOTT T01 TABLE T01 8 7 704 65536 8 7
SCOTT T01 TABLE T01 9 7 712 65536 8 7
SCOTT T01 TABLE T01 10 7 720 65536 8 7
SCOTT T01 TABLE T01 11 7 728 65536 8 7
SCOTT T01 TABLE T01 12 7 736 65536 8 7
SCOTT T01 TABLE T01 13 7 744 65536 8 7
SCOTT T01 TABLE T01 14 7 752 65536 8 7
SCOTT T01 TABLE T01 15 7 760 65536 8 7
SCOTT T01 TABLE T01 16 7 768 1048576 128 7
SCOTT T01 TABLE T01 17 7 896 1048576 128 7
SCOTT T01 TABLE T01 18 7 1024 1048576 128 7
SCOTT T01 TABLE T01 19 7 1152 1048576 128 7
20 rows selected.
--//这样表T01的数据分布相对离散.看看做truncate备份那些信息.
truncate table t01 ;
alter system checkpoint;
alter system checkpoint;
alter system checkpoint;
2.备份:
RMAN> backup datafile 7 format '/home/oracle/backup/T1z_%U';
Starting backup at 2017-12-08 09:23:26
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00007 name=/mnt/ramdisk/book/T01.dbf
channel ORA_DISK_1: starting piece 1 at 2017-12-08 09:23:26
channel ORA_DISK_1: finished piece 1 at 2017-12-08 09:23:27
piece handle=/home/oracle/backup/T1z_hfsljh0e_1_1 tag=TAG20171208T092326 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2017-12-08 09:23:27
$ strings -t d T1z_hfsljh0e_1_1 | grep ZZZZZ|wc
9978 26939 440925
$ strings -t d T1z_hfsljh0e_1_1 | grep ZZZZZ|head -1
1082678 K 000174ZZZZZZZZZZZZZZZZZZZZZZZZZZ,
$ strings -t d T1z_hfsljh0e_1_1 | grep BBBB|head -1
1148214 K 000174BBBBBBBBBBBBBBBBBBBBBBBBBB,
$ strings -t d T1z_hfsljh0e_1_1 | grep CCCC|head -1
2262326 K 000174CCCCCCCCCCCCCCCCCCCCCCCCCC,
$ strings -t d T1z_hfsljh0e_1_1 | grep ZZZZZ|tail -1
5775323 009807ZZZZZZZZZZZZZZZZZZZZZZZZZZ
--//实际上也可以看出确实是8个extents.与前面测试记数一致.
SCOTT@book> drop table t01 purge ;
Table dropped.
SCOTT@book> create table t01 tablespace t01 as select rownum id ,to_char(rownum,'FM000000')||lpad('Z',26,'Z') name from dual connect by level<=1e5;
Table created.
SCOTT@book> select rowid,t01.* from t01 where id in (174,009807,9978);
ROWID ID NAME
------------------ ---------- --------------------------------
AAAWMlAAHAAAACDACt 174 000174ZZZZZZZZZZZZZZZZZZZZZZZZZZ
AAAWMlAAHAAAAK/AAA 9807 009807ZZZZZZZZZZZZZZZZZZZZZZZZZZ
AAAWMlAAHAAAAK/ACr 9978 009978ZZZZZZZZZZZZZZZZZZZZZZZZZZ
SCOTT@book> @ &r/rowid AAAWMlAAHAAAACDACt
OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT
---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------
90917 7 131 173 0x1C00083 7,131 alter system dump datafile 7 block 131 ;
SCOTT@book> @ &r/rowid AAAWMlAAHAAAAK/AAA
OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT
---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------
90917 7 703 0 0x1C002BF 7,703 alter system dump datafile 7 block 703 ;
SCOTT@book> @ &r/rowid AAAWMlAAHAAAAK/ACr
OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT
---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------
90917 7 703 171 0x1C002BF 7,703 alter system dump datafile 7 block 703 ;
SCOTT@book> column PARTITION_NAME noprint
SCOTT@book> select * from dba_extents where segment_name='T01' order by block_id;
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 264 65536 8 7
SCOTT T01 TABLE T01 2 7 400 65536 8 7
SCOTT T01 TABLE T01 3 7 536 65536 8 7
SCOTT T01 TABLE T01 4 7 672 65536 8 7
SCOTT T01 TABLE T01 5 7 680 65536 8 7
SCOTT T01 TABLE T01 6 7 688 65536 8 7
SCOTT T01 TABLE T01 7 7 696 65536 8 7
SCOTT T01 TABLE T01 8 7 704 65536 8 7
SCOTT T01 TABLE T01 9 7 712 65536 8 7
SCOTT T01 TABLE T01 10 7 720 65536 8 7
SCOTT T01 TABLE T01 11 7 728 65536 8 7
SCOTT T01 TABLE T01 12 7 736 65536 8 7
SCOTT T01 TABLE T01 13 7 744 65536 8 7
SCOTT T01 TABLE T01 14 7 752 65536 8 7
SCOTT T01 TABLE T01 15 7 760 65536 8 7
SCOTT T01 TABLE T01 16 7 768 1048576 128 7
SCOTT T01 TABLE T01 17 7 896 1048576 128 7
SCOTT T01 TABLE T01 18 7 1024 1048576 128 7
SCOTT T01 TABLE T01 19 7 1152 1048576 128 7
20 rows selected.
--//确实是8块.也就是即使truncate表,rman在做备份也要选择前8个extent做备份.
--//好像数据做的不够离散,EXTENT_ID=4,5,6,7还是连续的,不过我觉得应该能说明问题.EXTENT_ID=0,1之间间隔128块(1M)呢.