[20161102]rman备份与数据文件变化5.txt
--想象一下，如果备份文件时间很长，而这个时候数据文件大小发生了变化，oracle的备份如何解决这个问题呢？
--前面我已经做了增大数据文件，参考链接：http://blog.itpub.net/267265/viewspace-2127386/
--这次测试减少数据文件大小看看。相关链接：
http://blog.itpub.net/267265/viewspace-2127424/
http://blog.itpub.net/267265/viewspace-2127396/
http://blog.itpub.net/267265/viewspace-2127386/
--昨天的测试思路很乱,我有点搞不清楚为什么第2次备份依旧很大,跟我对rman的理解不吻合.
$ ls -l /u01/backup/d6_*
-rw-r----- 1 oracle oinstall 26083328 2016-11-01 11:25:05 /u01/backup/d6_X_1drjqm2h_1_1
-rw-r----- 1 oracle oinstall 21880832 2016-11-01 11:42:16 /u01/backup/d6_Y_1grjqn2o_1_1
--在回家的路上我才想起来,问题在那里,两个备份大约相差4M，实际上10M以上的非空块,虽然在前面的位图块已经为0(标识未使用),
--但是属于异常情况,oracle认为只要是格式化的块,就都做了备份.还是重复做例子来说明问题.
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 SUGAR DATAFILE
'/mnt/ramdisk/book/sugar01.dbf' SIZE 40M AUTOEXTEND ON NEXT 16M MAXSIZE UNLIMITED
LOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;
create table t1 tablespace sugar as select rownum id ,to_char(rownum,'000000')||lpad('A',26,'A') name from dual connect by level<=1e5;
--建立大小5M的表。
create table t2 tablespace sugar as select rownum id ,to_char(rownum,'000000')||lpad('B',26,'B') name from dual connect by level<=2e5;
create table t3 tablespace sugar as select rownum id ,to_char(rownum,'000000')||lpad('C',26,'C') name from dual connect by level<=2e5;
alter system checkpoint;
--一些细节不再重复了.....
2.备份：
RMAN> CONFIGURE CHANNEL 1 DEVICE TYPE DISK RATE 128 K;
new RMAN configuration parameters:
CONFIGURE CHANNEL 1 DEVICE TYPE DISK RATE 128 K;
new RMAN configuration parameters are successfully stored
--//主要目的减慢备份速度。
RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET;
old RMAN configuration parameters:
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> backup datafile 6 format '/u01/backup/d6_AA_%U' ;
.....
--切换会话删除表T2,T3,操作有点多，写入1个脚本abc.sql执行它。
drop table t2 purge ;
host sleep 1.5
drop table t3 purge ;
host sleep 1
alter system checkpoint;
ALTER DATABASE DATAFILE '/mnt/ramdisk/book/sugar01.dbf' RESIZE 10M;
alter system checkpoint;
--脚本执行期间遇到
SCOTT@book> @ abc.sql
Table dropped.
Table dropped.
System altered.
ALTER DATABASE DATAFILE '/mnt/ramdisk/book/sugar01.dbf' RESIZE 10M
*
ERROR at line 1:
ORA-19567: cannot shrink file /mnt/ramdisk/book/sugar01.dbf because it is being backed up or copied
System altered.
$ ls -l /mnt/ramdisk/book/sugar01.dbf
-rw-r----- 1 oracle oinstall 41951232 2016-11-02 11:46:32 /mnt/ramdisk/book/sugar01.dbf
==============
$ oerr ora 19567
19567, 00000, "cannot shrink file %s because it is being backed up or copied"
// *Cause: An ALTER statement attempted to reduce the size of the indicated
// file while the same file is being backed up or copied.
// *Action: Retry the resize after the backup or copy is complete.
====================================
--我个人认为这个是一个bug.实际上这个时候查询视图DBA_DATA_FILES已经出现异常.
SCOTT@book> select * from DBA_DATA_FILES;
FILE_NAME FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS ONLINE_
------------------------------- ------- --------------- ---------- ---------- --------- ------------ --- ---------- ---------- ------------ ---------- ----------- -------
/mnt/ramdisk/book/users01.dbf 4 USERS 52428800 6400 AVAILABLE 4 YES 3.4360E+10 4194302 160 51380224 6272 ONLINE
/mnt/ramdisk/book/undotbs01.dbf 3 UNDOTBS1 89128960 10880 AVAILABLE 3 YES 1073741824 131072 640 88080384 10752 ONLINE
/mnt/ramdisk/book/sysaux01.dbf 2 SYSAUX 817889280 99840 AVAILABLE 2 YES 3.4360E+10 4194302 1280 816840704 99712 ONLINE
/mnt/ramdisk/book/system01.dbf 1 SYSTEM 786432000 96000 AVAILABLE 1 YES 3.4360E+10 4194302 1280 785383424 95872 SYSTEM
/mnt/ramdisk/book/example01.dbf 5 EXAMPLE 328335360 40080 AVAILABLE 5 YES 3.4360E+10 4194302 80 327286784 39952 ONLINE
/mnt/ramdisk/book/sugar01.dbf 6 SUGAR 10485760 1280 AVAILABLE 6 YES 3.4360E+10 4194302 2048 9437184 1152 ONLINE
6 rows selected.
--但是文件大小没有改变。
RMAN> backup datafile 6 format '/u01/backup/d6_AA_%U' ;
Starting backup at 2016-11-02 11:46:21
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=00006 name=/mnt/ramdisk/book/sugar01.dbf
channel ORA_DISK_1: starting piece 1 at 2016-11-02 11:46:21
channel ORA_DISK_1: finished piece 1 at 2016-11-02 11:51:46
piece handle=/u01/backup/d6_AA_1srjtc0d_1_1 tag=TAG20161102T114621 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:05:25
channel ORA_DISK_1: throttle time: 0:05:20
Finished backup at 2016-11-02 11:51:46
Starting Control File and SPFILE Autobackup at 2016-11-02 11:51:46
piece handle=/u01/app/oracle/fast_recovery_area/BOOK/autobackup/2016_11_02/o1_mf_s_926855506_d1lrpll2_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 2016-11-02 11:51:47
--需要将近5分20秒.
$ ls -l /mnt/ramdisk/book/sugar01.dbf
-rw-r----- 1 oracle oinstall 41951232 2016-11-02 11:46:32 /mnt/ramdisk/book/sugar01.dbf
--可以发现文件并没有shrink到10m。
$ strings -t d /u01/backup/d6_AA_1srjtc0d_1_1 | grep AAAA |wc
100000 301360 4524254
$ strings -t d /u01/backup/d6_AA_1srjtc0d_1_1 | grep BBBB |wc
200000 602720 9169972
$ strings -t d /u01/backup/d6_AA_1srjtc0d_1_1 | grep CCCC |wc
200000 602720 9243626
--可以发现T2,T3表的信息也做了备份。
3.继续做一个备份：
RMAN> backup datafile 6 format '/u01/backup/d6_BB_%U' diskratio=0;
Starting backup at 2016-11-02 12:00:38
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=00006 name=/mnt/ramdisk/book/sugar01.dbf
channel ORA_DISK_1: starting piece 1 at 2016-11-02 12:00:38
channel ORA_DISK_1: finished piece 1 at 2016-11-02 12:06:03
piece handle=/u01/backup/d6_BB_1urjtcr6_1_1 tag=TAG20161102T120038 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:05:25
channel ORA_DISK_1: throttle time: 0:05:20
Finished backup at 2016-11-02 12:06:03
Starting Control File and SPFILE Autobackup at 2016-11-02 12:06:03
piece handle=/u01/app/oracle/fast_recovery_area/BOOK/autobackup/2016_11_02/o1_mf_s_926856363_d1lskcp2_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 2016-11-02 12:06:04
--忘记重新设置disk rate了。
$ ls -l /u01/backup/d6*
-rw-r----- 1 oracle oinstall 26681344 2016-11-02 11:51:41 /u01/backup/d6_AA_1srjtc0d_1_1
-rw-r----- 1 oracle oinstall 22478848 2016-11-02 12:05:58 /u01/backup/d6_BB_1urjtcr6_1_1
$ strings -t d /u01/backup/d6_BB_1urjtcr6_1_1 | grep AAAA |wc
100000 301360 4524254
[oracle@gxqyydg4 IP=100.78 ~/0729/0901 103]$ strings -t d /u01/backup/d6_BB_1urjtcr6_1_1 | grep BBBB |wc
118416 356880 5401085
[oracle@gxqyydg4 IP=100.78 ~/0729/0901 104]$ strings -t d /u01/backup/d6_BB_1urjtcr6_1_1 | grep CCCC |wc
200000 602720 9243626
--可以发现备份依旧备份T2.T3表的块。备份文件大小并没有缩小多少。
-- 26681344 - 22478848=4202496 , 4202496/8192=513块，大约4M。
$ strings -t d /u01/backup/d6_BB_1urjtcr6_1_1 | grep BBBB | head
6227229 1! 081748BBBBBBBBBBBBBBBBBBBBBBBBBB,
6227271 0! 081747BBBBBBBBBBBBBBBBBBBBBBBBBB,
6227313 /! 081746BBBBBBBBBBBBBBBBBBBBBBBBBB,
6227355 .! 081745BBBBBBBBBBBBBBBBBBBBBBBBBB,
6227397 -! 081744BBBBBBBBBBBBBBBBBBBBBBBBBB,
6227439 ,! 081743BBBBBBBBBBBBBBBBBBBBBBBBBB,
6227481 +! 081742BBBBBBBBBBBBBBBBBBBBBBBBBB,
6227523 *! 081741BBBBBBBBBBBBBBBBBBBBBBBBBB,
6227565 )! 081740BBBBBBBBBBBBBBBBBBBBBBBBBB,
6227607 (! 081739BBBBBBBBBBBBBBBBBBBBBBBBBB,
)
-- 6227229/8192=760.1597900390625,可以发现在备份块760.
-- 如果前面10M按照正常的读取位图区的话，那么10*1024*1024/8192+1=1281,那么备份块760备份应该是数据文件的128x块(可能有assm的位图)。
-- 验证看看我的判断是否正常。
BBED> set filename '/u01/backup/d6_BB_1urjtcr6_1_1'
FILENAME /u01/backup/d6_BB_1urjtcr6_1_1
BBED> set block 760
BLOCK# 760
BBED> p rdba_kcbh
ub4 rdba_kcbh @4 0x018005f8
SCOTT@book> @ &r/dfb16 0x018005f8
RFILE# BLOCK# TEXT
---------- ---------- ------------------------------------------------------------
6 1528 alter system dump datafile 6 block 1528 ;
--we@#$%,不对。
$ ls -l /u01/backup/d6*
-rw-r----- 1 oracle oinstall 26681344 2016-11-02 11:51:41 /u01/backup/d6_AA_1srjtc0d_1_1
-rw-r----- 1 oracle oinstall 22478848 2016-11-02 12:05:58 /u01/backup/d6_BB_1urjtcr6_1_1
--26681344 - 22478848=4202496, 4202496/8192=513 相差513，大约4M。
--备份表T2的记录是118416，占118416/200000=.59208,0.59*10=5.9M,大约占6M，证明我的分析方法没有问题。
--直接扫描数据文件看看。
$ strings -t d /mnt/ramdisk/book/sugar01.dbf | grep 081748BBBBBBBBBBBBBBBBBBBBBBBBBB
10503453 1! 081748BBBBBBBBBBBBBBBBBBBBBBBBBB,
SCOTT@book> select 10503453/8192 from dual;
10503453/8192
-------------
1282.15979
BBED> x /rnc dba 6,1282 *kdbr[163]
rowdata[0] @1302
----------
flag@1302: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@1303: 0x00
cols@1304: 2
col 0[4] @1305: 81748
col 1[33] @1310: 081748BBBBBBBBBBBBBBBBBBBBBBBBBB
--说明数据从块底部开始插入，所以这条记录应该是这块的最后1条。所以访问的是*kdbr[163]
--这里有问题，为什么这条记录的数据文件的偏移在1282块，这个是符合我的推测的，但是rman备份集的记录为什么是1528块呢？
BBED> set filename '/u01/backup/d6_BB_1urjtcr6_1_1'
FILENAME /u01/backup/d6_BB_1urjtcr6_1_1
BBED> p block 760 rdba_kcbh
ub4 rdba_kcbh @4 0x018005f8
BBED> p block 761 rdba_kcbh
ub4 rdba_kcbh @4 0x018005f9
BBED> p block 762 rdba_kcbh
ub4 rdba_kcbh @4 0x018005fa
BBED> p block 763 rdba_kcbh
ub4 rdba_kcbh @4 0x018005fb
BBED> p block 764 rdba_kcbh
ub4 rdba_kcbh @4 0x018005fc
BBED> p block 765 rdba_kcbh
ub4 rdba_kcbh @4 0x018005fd
BBED> p block 766 rdba_kcbh
ub4 rdba_kcbh @4 0x018005fe
BBED> p block 767 rdba_kcbh
ub4 rdba_kcbh @4 0x018005ff
BBED> p block 768 rdba_kcbh
ub4 rdba_kcbh @4 0x01800580
BBED> p block 769 rdba_kcbh
ub4 rdba_kcbh @4 0x01800581
--出现一个我认为奇怪的情况备份的块dba是0x01800580,比前面0x018005f8小。
BBED> set block 768
BLOCK# 768
BBED> x /rnc *kdbr[0]
rowdata[6844] @8146
-------------
flag@8146: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8147: 0x00
cols@8148: 2
col 0[4] @8149: 82897
col 1[33] @8154: 082897BBBBBBBBBBBBBBBBBBBBBBBBBB
--而这个记录id=82897.明显在数据文件这个块应该在name=' 081748BBBBBBBBBBBBBBBBBBBBBBBBBB'之后。
--为此我建立的相似的表空间，按照前面的顺序建立表。
CREATE TABLESPACE tea DATAFILE
'/mnt/ramdisk/book/sugar01.dbf' SIZE 40M AUTOEXTEND ON NEXT 16M MAXSIZE UNLIMITED
LOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;
create table ta tablespace sugar as select rownum id ,to_char(rownum,'000000')||lpad('A',26,'A') name from dual connect by level<=1e5;
create table tb tablespace sugar as select rownum id ,to_char(rownum,'000000')||lpad('B',26,'B') name from dual connect by level<=2e5;
create table tc tablespace sugar as select rownum id ,to_char(rownum,'000000')||lpad('C',26,'C') name from dual connect by level<=2e5;
alter system checkpoint;
SCOTT@book> select rowid x,tb.* from tb where id in (82897,81748);
X ID NAME
------------------------------ ---------- ---------------------------------
AAAVsjAAHAAAAUCACj 81748 081748BBBBBBBBBBBBBBBBBBBBBBBBBB
AAAVsjAAHAAAAUKAAA 82897 082897BBBBBBBBBBBBBBBBBBBBBBBBBB
SCOTT@book> @ &r/rowid AAAVsjAAHAAAAUCACj
OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT
---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------
88867 7 1282 163 0x1C00502 7,1282 alter system dump datafile 7 block 1282
SCOTT@book> @ &r/rowid AAAVsjAAHAAAAUKAAA
OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT
---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------
88867 7 1290 0 0x1C0050A 7,1290 alter system dump datafile 7 block 1290
--很明显位置不对。
4.利用备份恢复就明白了：
SCOTT@book> alter tablespace sugar offline ;
Tablespace altered.
--移动做保留，方便比较。
$ mv sugar01.dbf /u01/backup/
RMAN> restore tablespace sugar;
Starting restore at 2016-11-03 09:42:11
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00006 to /mnt/ramdisk/book/sugar01.dbf
channel ORA_DISK_1: reading from backup piece /u01/backup/d6_BB_1urjtcr6_1_1
channel ORA_DISK_1: piece handle=/u01/backup/d6_BB_1urjtcr6_1_1 tag=TAG20161102T120038
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
Finished restore at 2016-11-03 09:42:13
BBED> x /rnc dba 6,1528 *kdbr[163]
rowdata[0] @1301
----------
flag@1301: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@1302: 0x00
cols@1303: 2
col 0[4] @1304: 121764
col 1[33] @1309: 121764BBBBBBBBBBBBBBBBBBBBBBBBBB
BBED> x /rnc dba 6,1282 *kdbr[163]
rowdata[0] @1302
----------
flag@1302: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@1303: 0x00
cols@1304: 2
col 0[4] @1305: 81748
col 1[33] @1310: 081748BBBBBBBBBBBBBBBBBBBBBBBBBB
BBED> x /rnc dba 6,1290 *kdbr[0]
rowdata[6844] @8146
-------------
flag@8146: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8147: 0x00
cols@8148: 2
col 0[4] @8149: 82897
col 1[33] @8154: 082897BBBBBBBBBBBBBBBBBBBBBBBBBB
--写回来还是对的。
--从这里可以看出：数据文件10M以前的信息应该通过位图确定来备份，而10M以上的块这个时候按照已经格式化的块做备份。
5.再看看前面的第1次备份的情况：
$ strings -t d /u01/backup/d6_AA_1srjtc0d_1_1 | grep 081748BBBBBBBBBBBBBBBBBBBBBBBBBB
10413341 1! 081748BBBBBBBBBBBBBBBBBBBBBBBBBB,
-- 10413341/8192=1271.1597900390625,位于1271块中。
BBED> set filename '/u01/backup/d6_AA_1srjtc0d_1_1'
FILENAME /u01/backup/d6_AA_1srjtc0d_1_1
BBED> p block 1271 rdba_kcbh
ub4 rdba_kcbh @4 0x018005f7
BBED> set dba 0x018005f7
DBA 0x018005f7 (25167351 6,1527)
--在dba6，1527明显也是不对的。正常记录应该在6，1282.
BBED> x /rnc filename '/u01/backup/d6_AA_1srjtc0d_1_1' block 1271 *kdbr[163]
rowdata[0] @1302
----------
flag@1302: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@1303: 0x00
cols@1304: 2
col 0[4] @1305: 81748
col 1[33] @1310: 081748BBBBBBBBBBBBBBBBBBBBBBBBBB
BBED> x /rnc dba 6,1282 *kdbr[163]
rowdata[0] @1302
----------
flag@1302: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@1303: 0x00
cols@1304: 2
col 0[4] @1305: 81748
col 1[33] @1310: 081748BBBBBBBBBBBBBBBBBBBBBBBBBB
$ strings -t d /u01/backup/d6_AA_1srjtc0d_1_1 | grep 099990AAAAAAAAAAAA
6191558 d[! 099990AAAAAAAAAAAAAAAAAAAAAAAAAA,
--6191558/8192=755.805419921875
BBED> x /rnc filename '/u01/backup/d6_AA_1srjtc0d_1_1' block 755 *kdbr[37]
rowdata[418] @6592
------------
flag@6592: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@6593: 0x00
cols@6594: 2
col 0[4] @6595: 99990
col 1[33] @6600: 099990AAAAAAAAAAAAAAAAAAAAAAAAAA
BBED> p filename '/u01/backup/d6_AA_1srjtc0d_1_1' block 755 rdba_kcbh
ub4 rdba_kcbh @4 0x018002f3
BBED> x /rnc dba 0x018002f3 *kdbr[37]
BBED-00400: invalid blocktype (00)
BBED> x /rnc dba 0x018002f2 *kdbr[37]
rowdata[418] @6592
------------
flag@6592: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@6593: 0x00
cols@6594: 2
col 0[4] @6595: 99990
col 1[33] @6600: 099990AAAAAAAAAAAAAAAAAAAAAAAAAA
--也不对，难道备份是要重新编码吗？也许这点是我错了。而且为什么变成这样，搞不懂................