本文共 7119 字，大约阅读时间需要 23 分钟。
[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 ProductionCREATE 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 009978ZZZZZZZZZZZZZZZZZZZZZZZZZZSCOTT@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)呢.转载地址：http://eouhl.baihongyu.com/