[20171206]rman与truncate.txt

简介: [20171206]rman与truncate.txt --//昨天下班在回家的路上,突然想起以前遇到的问题,就是truncate表后,rman做备份时会备份多少truncate表的信息, --//当时在itpub上,还存在讨论,就是rman会备份空block吗?参考链接:http://www.

[20171206]rman与truncate.txt

--//昨天下班在回家的路上,突然想起以前遇到的问题,就是truncate表后,rman做备份时会备份多少truncate表的信息,
--//当时在itpub上,还存在讨论,就是rman会备份空block吗?参考链接:http://www.itpub.net/thread-2050864-9-1.html
--//zergduan兄还开了SR,答复如下:

终于有回复了

经过dump的确认,unused block compression 特性是在extent一级,而不是在block级启用:
-------------------------
BEFORE : RelFno: 7, BeginBlock: 128, Flag: 0, First: 50, Free: 63438
BEFORE : FFFFFFFFFFFF0300 0000000000000000 0000000000000000 0000000000000000

AFTER : RelFno: 7, BeginBlock: 128, Flag: 0, First: 1, Free: 63487
AFTER : 0100000000000000 0000000000000000 0000000000000000 0000000000000000

RMAN read that there is one extent to be backup from the table. and I will backup all the blocks. It does not read at BLOCK level.
......
unused block compression doesn't kick in unless all the extents are unused. Granularity can't be changed to a block.
---------------------------

truncate之后,该对象仍然存在,所以,部分extent(比如头部)中仍然有一些metadata,这样的extent被认为非空,会被整体备份出来。
由于truncate 并不删除数据,所以,该块上仍然保留有您测试的字符串等。所以,通过strings命令可以看到这部分数据。

该问题的重点,我想应该是,最后验证了 unused block compression 的粒度是在extent级别,不是block。

--//是否跟rman input memory buffer有关.通过例子来来说明:

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 d2.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 6M 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 6M 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 6M 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 6M 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 6M 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<=1e5;
create table t02 tablespace t02 as select rownum id ,to_char(rownum,'FM000000')||lpad('B',26,'B') name from dual connect by level<=1e5;
create table t03 tablespace t03 as select rownum id ,to_char(rownum,'FM000000')||lpad('C',26,'C') name from dual connect by level<=1e5;
create table t04 tablespace t04 as select rownum id ,to_char(rownum,'FM000000')||lpad('D',26,'D') name from dual connect by level<=1e5;
create table t05 tablespace t05 as select rownum id ,to_char(rownum,'FM000000')||lpad('E',26,'E') name from dual connect by level<=1e5;

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

2.测试:
--//建立如上表空间:
RMAN> CONFIGURE CHANNEL 1 DEVICE TYPE DISK clear;
old RMAN configuration parameters:
CONFIGURE CHANNEL 1 DEVICE TYPE DISK RATE 128 K;
old RMAN configuration parameters are successfully deleted

RMAN> report schema ;
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    256      USERS                ***     /mnt/ramdisk/book/users01.dbf
5    346      EXAMPLE              ***     /mnt/ramdisk/book/example01.dbf
6    40       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

SCOTT@book> truncate table t01 ;
Table truncated.

--//truncate表T01.测试备份看看:
RMAN> backup datafile 7 format '/home/oracle/backup/T1_%U';

Starting backup at 2017-12-06 08:37:56
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=80 device type=DISK
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-06 08:37:57
channel ORA_DISK_1: finished piece 1 at 2017-12-06 08:37:58
piece handle=/home/oracle/backup/T1_h7sle5j5_1_1 tag=TAG20171206T083757 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2017-12-06 08:37:58

$ strings -t d T1_h7sle5j5_1_1 | grep 'AAAAA'|wc
   9978   26939  440924

$ strings -t d T1_h7sle5j5_1_1 | grep 'AAAAAA' |head -1
1082678 K 000174AAAAAAAAAAAAAAAAAAAAAAAAAA,
$ strings -t d T1_h7sle5j5_1_1 | grep 'AAAAAA' |tail -1
1581019  009807AAAAAAAAAAAAAAAAAAAAAAAAAA

--//可以确定在备份集合中记录是那些.因为T02表结构是一样的.通过T02也可以确定备份记录的块号.

SCOTT@book> select rowid,t02.* from t02 where id in (174,9807);
ROWID                      ID NAME
------------------ ---------- --------------------------------
AAAWLjAAIAAAACDACt        174 000174BBBBBBBBBBBBBBBBBBBBBBBBBB
AAAWLjAAIAAAAC/AAA       9807 009807BBBBBBBBBBBBBBBBBBBBBBBBBB

SCOTT@book> @ &r/rowid AAAWLjAAIAAAACDACt
    OBJECT       FILE      BLOCK        ROW ROWID_DBA            DBA                  TEXT
---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------
     90851          8        131        173  0x2000083           8,131                alter system dump datafile 8 block 131 ;

SCOTT@book> @ &r/rowid AAAWLjAAIAAAAC/AAA
    OBJECT       FILE      BLOCK        ROW ROWID_DBA            DBA                  TEXT
---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------
     90851          8        191          0  0x20000BF           8,191                alter system dump datafile 8 block 191 ;

--//总共191-131+1=61,加上前面3块,就是64块,也就是 64*8192 = 524288,524288/1024=512K.
--//我以前一直的理解truncate后会备份一些块,仅仅备份一个extent,这样仅仅备份64K(8块).而实际上是512K.
--//为什么不是1M,而是512K?????

3.测试:
--//测试备份4个文件作为备份集的情况:

RMAN> backup datafile 7,8,9,10 format '/home/oracle/backup/T1234_%U';

Starting backup at 2017-12-06 08:53:53
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=94 device type=DISK
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
input datafile file number=00008 name=/mnt/ramdisk/book/T02.dbf
input datafile file number=00009 name=/mnt/ramdisk/book/T03.dbf
input datafile file number=00010 name=/mnt/ramdisk/book/T04.dbf
channel ORA_DISK_1: starting piece 1 at 2017-12-06 08:53:54
channel ORA_DISK_1: finished piece 1 at 2017-12-06 08:53:55
piece handle=/home/oracle/backup/T1234_h8sle6h2_1_1 tag=TAG20171206T085353 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2017-12-06 08:53:55


$ strings -t d T1234_h8sle6h2_1_1 | grep 'AAAAA' |head -1
4179254 K 000174AAAAAAAAAAAAAAAAAAAAAAAAAA,
$ strings -t d T1234_h8sle6h2_1_1 | grep 'AAAAA' |tail -1
4677595  009807AAAAAAAAAAAAAAAAAAAAAAAAAA
$ strings -t d T1234_h8sle6h2_1_1 | grep 'AAAAA' |wc
   9978   26939  440924

--//与备份1个数据文件一样,不展开分析了.本来还想测试备份5个数据文件的.
--//因为备份1个文件仅仅备份512K(在truncate后),我开始的理解应该是1M.why?
--//不过我的理解还是和rman input memory buffer有关.

4.测试:
SCOTT@book> drop tablespace t01 including contents and datafiles;
Tablespace dropped.

CREATE TABLESPACE t01 DATAFILE
  '/mnt/ramdisk/book/T01.dbf' SIZE 6M AUTOEXTEND OFF
LOGGING
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;
--//建立UNIFORM SIZE 1M.

create table t01 tablespace t01 as select rownum id ,to_char(rownum,'FM000000')||lpad('A',26,'A') name from dual connect by level<=1e5;

SCOTT@book> create table t01 tablespace t01 as select rownum id ,to_char(rownum,'FM000000')||lpad('A',26,'A') name from dual connect by level<=1e5;
Table created.

SCOTT@book> truncate table t01 ;
Table truncated.

RMAN> backup datafile 7 format '/home/oracle/backup/T1x_%U';
Starting backup at 2017-12-06 09:02:19
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-06 09:02:19
channel ORA_DISK_1: finished piece 1 at 2017-12-06 09:02:20
piece handle=/home/oracle/backup/T1x_h9sle70r_1_1 tag=TAG20171206T090219 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2017-12-06 09:02:20

$ strings -t d T1x_h9sle70r_1_1 | grep 'AAAAA' |wc
100000  270040 4424269

$ strings -t d T1x_h9sle70r_1_1 | grep 'AAAAA' |head -1
1090870 K 000174AAAAAAAAAAAAAAAAAAAAAAAAAA,
$ strings -t d T1x_h9sle70r_1_1 | grep 'AAAAA' |tail -1
6021081 c< 099859AAAAAAAAAAAAAAAAAAAAAAAAAA

$ strings -t d T1x_h9sle70r_1_1 | grep 'AAAAA' |wc
100000  270040 4424269
--//昏,全部备份.看来没有这么简单.

SCOTT@book> drop table t01 purge ;
Table dropped.

SCOTT@book> create table t01 tablespace t01 as select rownum id ,to_char(rownum,'FM000000')||lpad('A',26,'A') name from dual connect by level<=1e5;
Table created.

SCOTT@book> select rowid,t01.* from t01 where id in (174,99859);
ROWID                      ID NAME
------------------ ---------- --------------------------------
AAAWLuAAHAAAACEACt        174 000174AAAAAAAAAAAAAAAAAAAAAAAAAA
AAAWLuAAHAAAALdAAA      99859 099859AAAAAAAAAAAAAAAAAAAAAAAAAA

SCOTT@book> @ &r/rowid AAAWLuAAHAAAACEACt
    OBJECT       FILE      BLOCK        ROW ROWID_DBA            DBA                  TEXT
---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------
     90862          7        132        173  0x1C00084           7,132                alter system dump datafile 7 block 132 ;

SCOTT@book> @ &r/rowid AAAWLuAAHAAAALdAAA
    OBJECT       FILE      BLOCK        ROW ROWID_DBA            DBA                  TEXT
---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------
     90862          7        733          0  0x1C002DD           7,733                alter system dump datafile 7 block 733 ;

SCOTT@book> column PARTITION_NAME noprint
SCOTT@book> select * from dba_extents where segment_name='T01';
OWNER  SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID    FILE_ID   BLOCK_ID      BYTES     BLOCKS RELATIVE_FNO
------ ------------ ------------ --------------- --------- ---------- ---------- ---------- ---------- ------------
SCOTT  T01          TABLE        T01                     0          7        128    1048576        128            7
SCOTT  T01          TABLE        T01                     1          7        256    1048576        128            7
SCOTT  T01          TABLE        T01                     2          7        384    1048576        128            7
SCOTT  T01          TABLE        T01                     3          7        512    1048576        128            7
SCOTT  T01          TABLE        T01                     4          7        640    1048576        128            7

--//难道truncate后rman备份会8个extents吗?

5.测试:

SCOTT@book> drop tablespace t01 including contents and datafiles;
Tablespace dropped.

CREATE TABLESPACE t01 DATAFILE
  '/mnt/ramdisk/book/T01.dbf' SIZE 21M AUTOEXTEND OFF
LOGGING
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;
--//建立UNIFORM SIZE 1M.

create table t01 tablespace t01 as select rownum id ,to_char(rownum,'FM000000')||lpad('A',26,'A') name from dual connect by level<=4e5;

SCOTT@book> select * from dba_extents where segment_name='T01';
OWNER  SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID    FILE_ID   BLOCK_ID      BYTES     BLOCKS RELATIVE_FNO
------ ------------ ------------ --------------- --------- ---------- ---------- ---------- ---------- ------------
SCOTT  T01          TABLE        T01                     0          7        128    1048576        128            7
SCOTT  T01          TABLE        T01                     1          7        256    1048576        128            7
SCOTT  T01          TABLE        T01                     2          7        384    1048576        128            7
SCOTT  T01          TABLE        T01                     3          7        512    1048576        128            7
SCOTT  T01          TABLE        T01                     4          7        640    1048576        128            7
SCOTT  T01          TABLE        T01                     5          7        768    1048576        128            7
SCOTT  T01          TABLE        T01                     6          7        896    1048576        128            7
SCOTT  T01          TABLE        T01                     7          7       1024    1048576        128            7
SCOTT  T01          TABLE        T01                     8          7       1152    1048576        128            7
SCOTT  T01          TABLE        T01                     9          7       1280    1048576        128            7
SCOTT  T01          TABLE        T01                    10          7       1408    1048576        128            7
SCOTT  T01          TABLE        T01                    11          7       1536    1048576        128            7
SCOTT  T01          TABLE        T01                    12          7       1664    1048576        128            7
SCOTT  T01          TABLE        T01                    13          7       1792    1048576        128            7
SCOTT  T01          TABLE        T01                    14          7       1920    1048576        128            7
SCOTT  T01          TABLE        T01                    15          7       2048    1048576        128            7
SCOTT  T01          TABLE        T01                    16          7       2176    1048576        128            7
SCOTT  T01          TABLE        T01                    17          7       2304    1048576        128            7
SCOTT  T01          TABLE        T01                    18          7       2432    1048576        128            7
19 rows selected.

SCOTT@book> truncate table t01 ;
Table truncated.

RMAN> backup datafile 7 format '/home/oracle/backup/T1y_%U';
Starting backup at 2017-12-06 09:17:52
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-06 09:17:52
channel ORA_DISK_1: finished piece 1 at 2017-12-06 09:17:53
piece handle=/home/oracle/backup/T1y_hbsle7u0_1_1 tag=TAG20171206T091752 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2017-12-06 09:17:53

$ strings -t d T1y_hbsle7u0_1_1 | grep 'AAAAA' |head -1
1090870 K 000174AAAAAAAAAAAAAAAAAAAAAAAAAA,
$ strings -t d T1y_hbsle7u0_1_1 | grep 'AAAAA' |tail -1
9445337 [L 169075AAAAAAAAAAAAAAAAAAAAAAAAAA
$ strings -t d T1y_hbsle7u0_1_1 | grep 'AAAAA' |wc
169242  456998 7484144

SCOTT@book> drop table t01 purge ;
Table dropped.

SCOTT@book> create table t01 tablespace t01 as select rownum id ,to_char(rownum,'FM000000')||lpad('A',26,'A') name from dual connect by level<=4e5;
Table created.

SCOTT@book> select rowid,t01.* from t01 where id in (174,169075,169242);
ROWID                      ID NAME
------------------ ---------- --------------------------------
AAAWL0AAHAAAACEACt        174 000174AAAAAAAAAAAAAAAAAAAAAAAAAA
AAAWL0AAHAAAAR/AAA     169075 169075AAAAAAAAAAAAAAAAAAAAAAAAAA
AAAWL0AAHAAAAR/ACn     169242 169242AAAAAAAAAAAAAAAAAAAAAAAAAA

SCOTT@book> @ &r/rowid AAAWL0AAHAAAACEACt
    OBJECT       FILE      BLOCK        ROW ROWID_DBA            DBA                  TEXT
---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------
     90868          7        132        173  0x1C00084           7,132                alter system dump datafile 7 block 132 ;

SCOTT@book> @ &r/rowid AAAWL0AAHAAAAR/AAA
    OBJECT       FILE      BLOCK        ROW ROWID_DBA            DBA                  TEXT
---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------
     90868          7       1151          0  0x1C0047F           7,1151               alter system dump datafile 7 block 1151

SCOTT@book> @ &r/rowid AAAWL0AAHAAAAR/ACn
    OBJECT       FILE      BLOCK        ROW ROWID_DBA            DBA                  TEXT
---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------
     90868          7       1151        167  0x1C0047F           7,1151               alter system dump datafile 7 block 1151
       
--//昏,确实是8个extents.
--//也就是truncate后rman备份信息的量最大是8个extents.有点小疑问,我的测试使用的磁盘空间是连续的,如果离散是否也是这样,不测试了浪费时间.

目录
相关文章
|
机器学习/深度学习 Oracle 关系型数据库
[20180412]logminer使用问题(10g).txt
[20180412]logminer使用问题(10g).txt --//今天使用logminer下遇到一个问题,做一个记录: 1.环境: SYSTEM@xxx> @ &r/ver1 PORT_STRING                    VERSION...
1627 0
|
Oracle 关系型数据库 Linux
[20180115]RMAN-06820.txt
[20180115]RMAN-06820.txt --//在备库做归档备份出现RMAN-06820的问题。就是如果在备库做全表,因为要做主库日志切换,一般执行rman target /会出现问题。
1395 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问题.
937 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; .
1051 0
|
Oracle 关系型数据库 Linux
[20171208]rman与truncate3.txt
[20171208]rman与truncate3.txt --//前几天测试truncate表依旧备份一部分信息,测试几次确定备份8extent.当时的测试几个extents是相邻的.
878 0
|
Oracle 关系型数据库 Linux
[20171206]rman与truncate2.txt
[20171206]rman与truncate2.txt --//上午测试发现truncate的表在做rman备份时还要做8个extents的备份. --//不知道自己的猜测是否正确,选择一个使用UNIFORM SIZE 的表空间测试看看.
1057 0
|
Oracle 关系型数据库
[20171130]关于rman的一些总结.txt
[20171130]关于rman的一些总结.txt --//最近一直做rman相关测试,测试那个乱,没办法.无法从周围的人获得帮助,纯粹是自己的乱猜,乱测,不知道别人是否能看懂我写的东西.
1072 0
|
Oracle 关系型数据库 API
[20170208]rman tape.txt
[20170208]rman tape.txt --前一阵子,同事测试使用磁带做rman备份,留下一些备份信息在控制文件,要清除. --我依稀记得我以前也干过这些事情,晚上看了一下书: Apress.
967 0
|
监控 Oracle 关系型数据库
[20170203]关于flashback的问题.txt
[20170203]关于flashback的问题.txt --今天生产系统遇到的问题,dataguard机器磁盘空间不足,需要释放磁盘空间,无论如何删除日志,空间回收都是很少.
876 0
|
关系型数据库 Oracle Linux
[20161230]rman checksyntax2.txt
[20161230]rman checksyntax2.txt --曾经写过一篇rman checksyntax的问题,这个问题存在10g: http://blog.
744 0