天天看点
返回

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

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

--//想象一下，如果备份文件时间很长，而这个时候数据文件大小发生了变化，oracle的备份如何解决这个问题呢？

--//去年已经测试了建立备份集的情况,一直想做一次image copy的测试,一直脱,主要原因自己不想做这个测试....

--//而且当时的测试很乱,自己主要一边做一边想....

--//链接:

<a href="http://blog.itpub.net/267265/viewspace-2127386/">http://blog.itpub.net/267265/viewspace-2127386/</a>

<a href="http://blog.itpub.net/267265/viewspace-2127569/">http://blog.itpub.net/267265/viewspace-2127569/</a>

<a href="http://blog.itpub.net/267265/viewspace-2127424/">http://blog.itpub.net/267265/viewspace-2127424/</a>

<a href="http://blog.itpub.net/267265/viewspace-2127396/">http://blog.itpub.net/267265/viewspace-2127396/</a>

--//本次测试在做image copy时,数据文件增加的情况,实际上根据前面的测试可以想像备份文件的大小不应该随数据文件大小而变化,

--//因为备份前要建立SNAPSHOT CONTROLFILE,一般以这个控制文件为准做的备份.

1.环境：

SCOTT@book&gt; @ &amp;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&gt; create table t1 tablespace sugar as select rownum id ,lpad('A',32,'A') name from dual connect by level&lt;=1e5;

Table created.

SCOTT@book&gt; 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&gt; 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&gt; CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET;

old RMAN configuration parameters:

CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET;

3.开始备份：

--//建立测试脚本:

$ cat df_change.sh

#! /bin/bash

rman target / &lt;&lt;EOF &amp;

backup as copy datafile 7 format '/home/oracle/backup/%b' ;

quit

EOF

echo 'sleep 56 '

sleep 56

sqlplus scott/book &lt;&lt;EOF

create table t2 tablespace sugar as select rownum id ,lpad('B',32,'B') name from dual connect by level&lt;=2e5;

create table t3 tablespace sugar as select rownum id ,lpad('C',32,'C') name from dual connect by level&lt;=2e5;

alter system checkpoint;

--//说明备份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

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&gt;

Starting backup at 2017-11-23 09:15:08

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

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

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&gt;

System altered.

SCOTT@book&gt; Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

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

$ 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

Recovery Manager complete.

[1]+  Done                    rman target /  &lt;&lt;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.

$ strings /mnt/ramdisk/book/sugar01.dbf | grep "AAAA" |wc

100000  170040 3624077

$ strings /mnt/ramdisk/book/sugar01.dbf | grep "BBBB" |wc

200000  340080 7243181

$ strings /mnt/ramdisk/book/sugar01.dbf | grep "CCCC" |wc

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

$ strings /home/oracle/backup/sugar01.dbf | grep "AAAA" |wc

$ strings /home/oracle/backup/sugar01.dbf | grep "BBBB" |wc

      0       0       0

$ strings /home/oracle/backup/sugar01.dbf | grep "CCCC" |wc

--//查询备份映像发现BBBB，CCCC字符串可以发现根本不存在，也就是讲这部分的备份并没有做,这点我觉得有点出乎我的意外!!

--//可以猜测oracle 做映像备份与建立备份集一样,也是根据数据文件位图区确定读取那些数据块,这样在备份开始后新建立的块不会备份.

RMAN&gt; list datafilecopy all;

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&gt; validate copy of datafile 7 ;

Starting validate at 2017-11-23 09:37:11

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

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&gt; 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&gt; select rowid,t2.* from t2 where id=1;

ROWID               ID NAME

------------------ --- --------------------------------

AAAWKKAAHAAAAMDAAA   1 BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB

SCOTT@book&gt; @ &amp;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&gt; 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 ................................

&lt;32 bytes per line&gt;

BBED&gt; dump /v filename '/home/oracle/backup/sugar01.dbf' block 771 offset 8000 count 192

Block: 771                               Offsets: 8000 to 8191                            Dba:0x00000000

00000000 00000000 00000000 00000000 00000000 00000000 00000000 01000000 l ................................

--//可以发现备份映像对应的块是空的,也就是根本没有读取,我估计是"构造"出来的.

BBED&gt; 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 [email protected]......

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.&amp;.......

8a1b631b 3c1b151b ee1ac71a a01a791a 521a2b1a 041add19 b6198f19 68194119 l ..c.&lt;.........y.R.+.........h.A.

1a19f318 cc18a518 7e185718 30180918 e217bb17 94176d17 46171f17 f816d116 l ........~.W.0.........m.F.......

BBED&gt; dump /v filename '/mnt/ramdisk/book/sugar01.dbf' block 771 offset 8000 count 192

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....

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

总结：

1.我仅仅猜测备份时，文件大小就已经确定(估计通过SNAPSHOT CONTROLFILE)，最多10M。而具体读取那些块，我估计已经通过位图确定下来。

  这个与前面测试备份集的情况一样.

  你可以看到即使我发了alter system checkpoint命令，T2,T3表的信息依旧没有备份。

2.我做了数据文件增加的情况，数据文件缩小有发生什么情况呢？看下一篇blog.

3.做了这么多这类测试,我的感觉最好建立一个大的数据文件不要扩展,也不要做收缩操作(即使做也最好避开rman备份窗口),特别是收缩操作!!

4.在写这篇blog快结束时:

-rw-r----- 1 oracle oinstall 44048384 2017-11-23 09:36:37 /mnt/ramdisk/book/sugar01.dbf

--//可以发现数据文件变成了42M,而前面测试结束时是26M:

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

Oracle 关系型数据库 olap 数据库管理 Linux shell 数据库 sql RMA备份文件 rman备份txt rman文件 txt文件数 rman备份文件
上一篇: [20161101]rman备份与数据文件变化7.txt
下一篇: [20171121]rman使用copy image恢复.txt

继续阅读