[20171123]rman备份与数据文件变化6.txt

最新推荐文章于 2021-06-08 11:41:09 发布

cuixun7780

最新推荐文章于 2021-06-08 11:41:09 发布

阅读量121

点赞数

文章标签：操作系统数据库

本文通过实验探讨了Oracle RMAN在进行image copy备份时遇到数据文件大小变化的情况。测试结果显示，在备份过程中新增的数据块并未被包含在备份中，这与通过SNAPSHOT CONTROL FILE确定的备份范围有关。

摘要由CSDN通过智能技术生成

[20171123]rman备份与数据文件变化6.txt

--//想象一下，如果备份文件时间很长，而这个时候数据文件大小发生了变化，oracle的备份如何解决这个问题呢？
--//去年已经测试了建立备份集的情况,一直想做一次image copy的测试,一直脱,主要原因自己不想做这个测试....
--//而且当时的测试很乱,自己主要一边做一边想....
--//链接:
http://blog.itpub.net/267265/viewspace-2127386/
http://blog.itpub.net/267265/viewspace-2127569/
http://blog.itpub.net/267265/viewspace-2127424/
http://blog.itpub.net/267265/viewspace-2127396/

--//本次测试在做image copy时,数据文件增加的情况,实际上根据前面的测试可以想像备份文件的大小不应该随数据文件大小而变化,
--//因为备份前要建立SNAPSHOT CONTROLFILE,一般以这个控制文件为准做的备份.

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 10M AUTOEXTEND ON NEXT 16M MAXSIZE UNLIMITED
LOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

--//注意要选择LOGGING。第1次没有选择，测试存在错误，浪费了时间。

SCOTT@book> create table t1 tablespace sugar as select rownum id ,lpad('A',32,'A') name from dual connect by level<=1e5;
Table created.

SCOTT@book> select sum(bytes) from dba_extents where owner=user and segment_name='T1';
SUM(BYTES)
------------
5242880

--//大约占用5242880/1024/1024=5M.
$ ls -l /mnt/ramdisk/book/sugar01.dbf
-rw-r----- 1 oracle oinstall 10493952 2017-11-23 09:14:20 /mnt/ramdisk/book/sugar01.dbf
--//当前大小10M+8k。 10*1024*1024+8192=10493952.

2.备份前准备以及备份：

RMAN> CONFIGURE CHANNEL 1 DEVICE TYPE DISK RATE 128 K;
using target database control file instead of recovery catalog
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

3.开始备份：
--//建立测试脚本:
$ cat df_change.sh
#! /bin/bash
rman target / <<EOF &
backup as copy datafile 7 format '/home/oracle/backup/%b' ;
quit
EOF
echo 'sleep 56 '
sleep 56
sqlplus scott/book <<EOF
create table t2 tablespace sugar as select rownum id ,lpad('B',32,'B') name from dual connect by level<=2e5;
create table t3 tablespace sugar as select rownum id ,lpad('C',32,'C') name from dual connect by level<=2e5;
alter system checkpoint;
alter system checkpoint;
alter system checkpoint;
alter system checkpoint;
EOF

--//说明备份10M文件,前面1M是OS块,文件头,位图区,加上t1表5M,我选择备份到7M时开始建立表并且扩展表空间.
--//7*1024/128=56,备份到这个位置需要56秒.T2,T3大约每个占10M,这样需要1+5+10+10=26M,因为这个过程可能要触发pre-allocation,
--//这样最后的数据文件要再加16M(表空间定义AUTOEXTEND ON NEXT 16M),大约42M上下.

$ . df_change.sh
sleep 56
Recovery Manager: Release 11.2.0.4.0 - Production on Thu Nov 23 09:15:07 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: BOOK (DBID=1337401710)

RMAN>
Starting backup at 2017-11-23 09:15:08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=144 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00007 name=/mnt/ramdisk/book/sugar01.dbf
--//.....等56秒
SQL*Plus: Release 11.2.0.4.0 Production on Thu Nov 23 09:16:03 2017

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SCOTT@book>
Table created.

SCOTT@book>
System altered.

SCOTT@book> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

--//等剩下的备份结束...

With the Partitioning, OLAP, Data Mining and Real Application Testing options
$ output file name=/home/oracle/backup/sugar01.dbf tag=TAG20171123T091509 RECID=35 STAMP=960801389
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:25
channel ORA_DISK_1: throttle time: 0:01:20
Finished backup at 2017-11-23 09:16:34

Starting Control File and SPFILE Autobackup at 2017-11-23 09:16:34
piece handle=/u01/app/oracle/fast_recovery_area/BOOK/autobackup/2017_11_23/o1_mf_s_960801394_f1d8cl9h_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 2017-11-23 09:16:35

RMAN>

Recovery Manager complete.

[1]+ Done rman target / <<EOF
backup as copy datafile 7 format '/home/oracle/backup/%b' ;
quit
EOF

--//在备份中间观察:
$ ls -l sugar01.dbf
-rw-r----- 1 oracle oinstall 10493952 2017-11-23 09:03:26 sugar01.dbf
--//可以发现先生成10M的image copy.

--//完成后分析:
$ ls -l /mnt/ramdisk/book/sugar01.dbf /home/oracle/backup/sugar01.dbf
-rw-r----- 1 oracle oinstall 10493952 2017-11-23 09:16:29 /home/oracle/backup/sugar01.dbf
-rw-r----- 1 oracle oinstall 27271168 2017-11-23 09:16:03 /mnt/ramdisk/book/sugar01.dbf

--//数据文件现在是27271168/1024/1024=26.0078125M.而备份映像10M+8K.

--//数据文件存在发现BBBB，CCCC字符串.

$ strings /home/oracle/backup/sugar01.dbf | grep "AAAA" |wc
100000 170040 3624077
$ strings /home/oracle/backup/sugar01.dbf | grep "BBBB" |wc
0 0 0
$ strings /home/oracle/backup/sugar01.dbf | grep "CCCC" |wc
0 0 0
--//查询备份映像发现BBBB，CCCC字符串可以发现根本不存在，也就是讲这部分的备份并没有做,这点我觉得有点出乎我的意外!!
--//可以猜测oracle 做映像备份与建立备份集一样,也是根据数据文件位图区确定读取那些数据块,这样在备份开始后新建立的块不会备份.

RMAN> list datafilecopy all;
using target database control file instead of recovery catalog
List of Datafile Copies
=======================

Key     File S Completion Time     Ckp SCN    Ckp Time
------- ---- - ------------------- ---------- -------------------
35      7    A 2017-11-23 09:16:29 13279893698 2017-11-23 09:15:09
        Name: /home/oracle/backup/sugar01.dbf
        Tag: TAG20171123T091509

RMAN> validate copy of datafile 7 ;

Starting validate at 2017-11-23 09:37:11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=67 device type=DISK
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: including datafile copy of datafile 00007 in backup set
input file name=/home/oracle/backup/sugar01.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
List of Datafile Copies
=======================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
7    OK     0              541          1280            13279893572
File Name: /home/oracle/backup/sugar01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data       0              594
Index      0              0
Other      0              145
Finished validate at 2017-11-23 09:37:12

SCOTT@book> select recid,file#,NAME,CHECKPOINT_CHANGE#,ABSOLUTE_FUZZY_CHANGE# from v$datafile_copy where recid=35 ;
RECID FILE# NAME CHECKPOINT_CHANGE# ABSOLUTE_FUZZY_CHANGE#
----- ----- ------------------------------- ------------------ ----------------------
35 7 /home/oracle/backup/sugar01.dbf 13279893698 0

--//而且备份期间没有出现高于检查点scn高于13279893698的scn号。ABSOLUTE_FUZZY_CHANGE#的记录是0.
--//换一句话T2,T3占用的数据库根本不备份.

--//这样更前面测试使用备份集的模式一样,备份时建立SNAPSHOT CONTROLFILE,备份文件大小已经确定,备份期间读取了数据块位图信息，
--//这样需要备份的块也确定,而T2,T3表占用块是后面的建立的,即使我发了检查点,oracle也不会读取,写入备份集合.

4.观察一个数据块看看.
SCOTT@book> select rowid,t2.* from t2 where id=1;
ROWID ID NAME
------------------ --- --------------------------------
AAAWKKAAHAAAAMDAAA 1 BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB

SCOTT@book> @ &r/rowid AAAWKKAAHAAAAMDAAA
OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT
------------ ------------ ------------ ------------ -------------------- -------------------- ----------------------------------------
90762 7 771 0 0x1C00303 7,771 alter system dump datafile 7 block 771 ;

BBED> dump /v filename '/home/oracle/backup/sugar01.dbf' block 771 offset 0 count 256
File: /home/oracle/backup/sugar01.dbf (0)
Block: 771 Offsets: 0 to 255 Dba:0x00000000
-----------------------------------------------------------------------------------------------------------
00a20000 0303c001 00000000 00000105 c3a50000 00000000 00000000 00000000 l ................................
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 l ................................
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 l ................................
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 l ................................
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 l ................................
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 l ................................
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 l ................................
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 l ................................
<32 bytes per line>

BBED> dump /v filename '/home/oracle/backup/sugar01.dbf' block 771 offset 8000 count 192
File: /home/oracle/backup/sugar01.dbf (0)
Block: 771 Offsets: 8000 to 8191 Dba:0x00000000
-----------------------------------------------------------------------------------------------------------
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 l ................................
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 l ................................
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 l ................................
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 l ................................
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 l ................................
00000000 00000000 00000000 00000000 00000000 00000000 00000000 01000000 l ................................
<32 bytes per line>

--//可以发现备份映像对应的块是空的,也就是根本没有读取,我估计是"构造"出来的.
BBED> dump /v filename '/mnt/ramdisk/book/sugar01.dbf' block 771 offset 0 count 256
File: /mnt/ramdisk/book/sugar01.dbf (7)
Block: 771 Offsets: 0 to 255 Dba:0x01c00303
-----------------------------------------------------------------------------------------------------------
06a20000 0303c001 04198b17 03000204 84400000 01000000 8a620100 fe188b17 l .................@.......b......
03000000 03003200 0003c001 ffff0000 00000000 00000000 00000000 00800300 l ......2.........................
fe188b17 00000000 00000000 00000000 00000000 00000000 00000000 00000000 l ................................
00000000 00000000 00000000 00000000 00000000 00000000 00000000 0001ae00 l ................................
ffff6e01 b4044603 46030000 ae00591f 321f0b1f e41ebd1e 961e6f1e 481e211e l ..n...F.F.....Y.2.........o.H.!.
fa1dd31d ac1d851d 5e1d371d 101de91c c21c9b1c 741c4d1c 261cff1b d81bb11b l ........^.7.........t.M.&.......
8a1b631b 3c1b151b ee1ac71a a01a791a 521a2b1a 041add19 b6198f19 68194119 l ..c.<.........y.R.+.........h.A.
1a19f318 cc18a518 7e185718 30180918 e217bb17 94176d17 46171f17 f816d116 l ........~.W.0.........m.F.......
<32 bytes per line>

BBED> dump /v filename '/mnt/ramdisk/book/sugar01.dbf' block 771 offset 8000 count 192
File: /mnt/ramdisk/book/sugar01.dbf (7)
Block: 771 Offsets: 8000 to 8191 Dba:0x01c00303
-----------------------------------------------------------------------------------------------------------
42424242 42424242 42424242 42424242 42424242 42424242 42424242 42424242 l BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB
2c000202 c1052042 42424242 42424242 42424242 42424242 42424242 42424242 l ,..... BBBBBBBBBBBBBBBBBBBBBBBBB
42424242 4242422c 000202c1 04204242 42424242 42424242 42424242 42424242 l BBBBBBB,..... BBBBBBBBBBBBBBBBBB
42424242 42424242 42424242 42422c00 0202c103 20424242 42424242 42424242 l BBBBBBBBBBBBBB,..... BBBBBBBBBBB
42424242 42424242 42424242 42424242 42424242 422c0002 02c10220 42424242 l BBBBBBBBBBBBBBBBBBBBB,..... BBBB
42424242 42424242 42424242 42424242 42424242 42424242 42424242 02060419 l BBBBBBBBBBBBBBBBBBBBBBBBBBBB....
<32 bytes per line>

--//可以发现对应数据文件的数据块.

总结：
1.我仅仅猜测备份时，文件大小就已经确定(估计通过SNAPSHOT CONTROLFILE)，最多10M。而具体读取那些块，我估计已经通过位图确定下来。
这个与前面测试备份集的情况一样.
你可以看到即使我发了alter system checkpoint命令，T2,T3表的信息依旧没有备份。
2.我做了数据文件增加的情况，数据文件缩小有发生什么情况呢？看下一篇blog.
3.做了这么多这类测试,我的感觉最好建立一个大的数据文件不要扩展,也不要做收缩操作(即使做也最好避开rman备份窗口),特别是收缩操作!!
4.在写这篇blog快结束时:
$ ls -l /mnt/ramdisk/book/sugar01.dbf /home/oracle/backup/sugar01.dbf
-rw-r----- 1 oracle oinstall 10493952 2017-11-23 09:16:29 /home/oracle/backup/sugar01.dbf
-rw-r----- 1 oracle oinstall 44048384 2017-11-23 09:36:37 /mnt/ramdisk/book/sugar01.dbf

--//可以发现数据文件变成了42M,而前面测试结束时是26M:
$ ls -l /mnt/ramdisk/book/sugar01.dbf /home/oracle/backup/sugar01.dbf
-rw-r----- 1 oracle oinstall 10493952 2017-11-23 09:16:29 /home/oracle/backup/sugar01.dbf
-rw-r----- 1 oracle oinstall 27271168 2017-11-23 09:16:03 /mnt/ramdisk/book/sugar01.dbf

--//注意看文件时间戳不一样,也就是在这期间触发pre-allocation.oracle在数据文件快满的时候,触发pre-allocation,扩展了16M.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/267265/viewspace-2147642/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/267265/viewspace-2147642/