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

[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

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

SCOTT@book>
Table created.

SCOTT@book>
System altered.

SCOTT@book>
System altered.

SCOTT@book>
System altered.

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.

$ 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
200000  340080 7243181

--//数据文件存在发现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.

时间： 2024-10-25 21:33:50

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

[20161101]rman备份与数据文件变化7.txt

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

[20161101]rman备份与数据文件变化4.txt

[20161101]rman备份与数据文件变化4.txt --想象一下,如果备份文件时间很长,而这个时候数据文件大小发生了变化,oracle的备份如何解决这个问题呢? --前面我已经做了增大数据文件,参考链接:http://blog.itpub.net/267265/viewspace-2127386/ --这次测试减少数据文件大小看看. --早上的测试太乱了,重复做1次看看. 1.环境: SCOTT@book> @ &r/ver1 PORT_STRING                  

[20161031]rman备份与数据文件变化3.txt

[20161031]rman备份与数据文件变化3.txt --想象一下,如果备份文件时间很长,而这个时候数据文件大小发生了变化,oracle的备份如何解决这个问题呢? --前面我已经做了增大数据文件,参考链接:http://blog.itpub.net/267265/viewspace-2127386/ --这次测试减少数据文件大小看看. 1.环境: SCOTT@book> @ &r/ver1 PORT_STRING                    VERSION        BAN

[20161102]rman备份与数据文件变化5.txt

[20161102]rman备份与数据文件变化5.txt --想象一下,如果备份文件时间很长,而这个时候数据文件大小发生了变化,oracle的备份如何解决这个问题呢? --前面我已经做了增大数据文件,参考链接:http://blog.itpub.net/267265/viewspace-2127386/ --这次测试减少数据文件大小看看.相关链接: http://blog.itpub.net/267265/viewspace-2127424/ http://blog.itpub.net/2672

[20161031]rman备份与数据文件变化2.txt

[20161031]rman备份与数据文件变化2.txt --想象一下,如果备份文件时间很长,而这个时候数据文件大小发生了变化,oracle的备份如何解决这个问题呢? 1.环境: SCOTT@book> @ &r/ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- -------------------------------------

[20161031]rman备份与数据文件OS块.txt

[20161031]rman备份与数据文件OS块.txt --每个数据文件都有一个OS块,位于数据文件的第1块(也是0块).通过bbed无法访问: BBED> set dba 7,0 BBED-00205: illegal or out of range DBA (File 7, Block 0) BBED> set dba 7,1         DBA             0x01c00001 (29360129 7,1) BBED> dump File: /mnt/ramdis

在open状态下恢复未备份的数据文件

        此文讲述如何恢复未备份的数据文件,在归档日志模式,如果dba增加了新的数据文件,当没有备份新的数据文件,那么该文件出现损坏时,可以恢复该数据文件.前提是 从建立新的数据文件到丢失为止的所有归档日志必须全部存在. 一 模拟实验环境.在数据文件test 里建立t1表 并插入数据,提交,归档日志文件. SQL> create table t(num number) tablespace test; 表已创建. SQL> insert into t values(1); 已创建 1 行

[20151125]数据文件的unrecover.txt

[20151125]数据文件的unrecover.txt --前一阵子我给别人演示truncate的不完全恢复,结果非常难堪的遇到无法恢复的情况. --问题是我建立的数据库按照这个链接建立的. http://blog.itpub.net/267265/viewspace-1845062/ --而这样建立的数据库表空间example的属性NOLOGGING. CREATE TABLESPACE EXAMPLE DATAFILE   '/mnt/ramdisk/book/example01.dbf'

[20171114]恢复数据文件块头2.txt

[20171114]恢复数据文件块头2.txt --//曾经写过一篇[20161111]数据库文件头的修复.txt,但是利用大小相似的数据文件头覆盖来恢复,那是属于特种恢复. --//参考链接:http://blog.itpub.net/267265/viewspace-2128309/ --//不在正常操作范围,完全是不得已而为之.基本写那篇在一年之前,这次做一个带引号"常规恢复"看看. --//后记:纯属无聊,千万不要把这当作常规的恢复. 1.环境: SCOTT@book>