[20171208]rman与truncate3.txt

简介: [20171208]rman与truncate3.txt --//前几天测试truncate表依旧备份一部分信息,测试几次确定备份8extent.当时的测试几个extents是相邻的.

[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)呢.

目录
相关文章
|
Oracle 关系型数据库 Linux
[20180115]RMAN-06820.txt
[20180115]RMAN-06820.txt --//在备库做归档备份出现RMAN-06820的问题。就是如果在备库做全表,因为要做主库日志切换,一般执行rman target /会出现问题。
1416 0
|
Oracle 关系型数据库 测试技术
[20171225]RMAN-06808.txt
[20171225]RMAN-06808: SECTION SIZE cannot be used when piece limit is in effect.txt --//朋友拿我的一些例子来测试遇到的RMAN-06808: SECTION SIZE cannot be used when piece limit is in effect问题.
947 0
|
关系型数据库
[20171221]RMAN-05501.txt
[20171221]RMAN-05501 aborting duplication of target database.txt --//昨天使用rman duplicate建立dg,出现如下错误: rman > duplicate target database for standby from active database; .
1068 0
|
Oracle 关系型数据库 Linux
[20171206]rman与truncate2.txt
[20171206]rman与truncate2.txt --//上午测试发现truncate的表在做rman备份时还要做8个extents的备份. --//不知道自己的猜测是否正确,选择一个使用UNIFORM SIZE 的表空间测试看看.
1072 0
|
Oracle 关系型数据库 测试技术
[20171206]rman与truncate.txt
[20171206]rman与truncate.txt --//昨天下班在回家的路上,突然想起以前遇到的问题,就是truncate表后,rman做备份时会备份多少truncate表的信息, --//当时在itpub上,还存在讨论,就是rman会备份空block吗?参考链接:http://www.
998 0
|
Oracle 关系型数据库
[20171130]关于rman的一些总结.txt
[20171130]关于rman的一些总结.txt --//最近一直做rman相关测试,测试那个乱,没办法.无法从周围的人获得帮助,纯粹是自己的乱猜,乱测,不知道别人是否能看懂我写的东西.
1087 0
|
Oracle 关系型数据库 数据库
[20170616]recover copy of datafile 6.txt
[20170616]no copy of datafile 6 found to recover.txt --//最近几天一直被这个问题纠缠,我虽然不知道问题在哪来,还是找到简单的解决方法,做1个记录: --//链接:http://www.
1151 0
|
Oracle 关系型数据库 API
[20170208]rman tape.txt
[20170208]rman tape.txt --前一阵子,同事测试使用磁带做rman备份,留下一些备份信息在控制文件,要清除. --我依稀记得我以前也干过这些事情,晚上看了一下书: Apress.
983 0
|
监控 Oracle 关系型数据库
[20170203]关于flashback的问题.txt
[20170203]关于flashback的问题.txt --今天生产系统遇到的问题,dataguard机器磁盘空间不足,需要释放磁盘空间,无论如何删除日志,空间回收都是很少.
895 0
|
关系型数据库 Oracle Linux
[20161230]rman checksyntax2.txt
[20161230]rman checksyntax2.txt --曾经写过一篇rman checksyntax的问题,这个问题存在10g: http://blog.
757 0