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

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

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

--前面我已经做了增大数据文件,参考链接:http://blog.itpub.net/267265/viewspace-2127386/
--这次测试减少数据文件大小看看。

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 ,lpad('A',32,'A') name from dual connect by level<=1e5;
--建立大小5M的表。

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;

SCOTT@book> select sum(bytes) from dba_extents where owner=user and segment_name like 'T%';
  SUM(BYTES)
------------
    26214400

--大约占用26214400/1024/1024=25m。

$ ls -l /mnt/ramdisk/book/sugar01.dbf
-rw-r----- 1 oracle oinstall 41951232 2016-11-01 09:41:29 /mnt/ramdisk/book/sugar01.dbf
--当前大小40M+8k。 40*1024*1024+8192=41951232

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_A_%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;

$ ls -l /u01/backup/d6_A*
-rw-r----- 1 oracle oinstall 41975808 2016-11-01 09:47:37 /u01/backup/d6_A_15rjqgkp_1_1
--可以发现是先产生备份文件的大小,然后再写入操作。

--脚本执行期间遇到
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.
--可以发现在备份期间不能shrink表。
==============
$ 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.
====================================

SCOTT@book> ALTER DATABASE DATAFILE '/mnt/ramdisk/book/sugar01.dbf' RESIZE 10M;
Database altered.

--奇怪我手工执行又ok。非常奇怪。
--说明:这里我重复多次,都是先报错ora-19567,第2次执行都可以通过,估计是bug吗?

$ ls -l /mnt/ramdisk/book/sugar01.dbf
-rw-r----- 1 oracle oinstall 41951232 2016-11-01 09:47:14 /mnt/ramdisk/book/sugar01.dbf
--但是文件大小没有改变。

RMAN> backup datafile 6 format '/u01/backup/d6_A_%U' ;

Starting backup at 2016-11-01 09:47:04
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=90 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=00006 name=/mnt/ramdisk/book/sugar01.dbf
channel ORA_DISK_1: starting piece 1 at 2016-11-01 09:47:05
channel ORA_DISK_1: finished piece 1 at 2016-11-01 09:52:30
piece handle=/u01/backup/d6_A_15rjqgkp_1_1 tag=TAG20161101T094705 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-01 09:52:30
Starting Control File and SPFILE Autobackup at 2016-11-01 09:52:30
piece handle=/u01/app/oracle/fast_recovery_area/BOOK/autobackup/2016_11_01/o1_mf_s_926761950_d1hxbyh6_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 2016-11-01 09:52:31

--需要将近5分25秒.

$ ls -l /mnt/ramdisk/book/sugar01.dbf
-rw-r----- 1 oracle oinstall 41951232 2016-11-01 09:47:14 /mnt/ramdisk/book/sugar01.dbf

--可以发现文件并没有shrink到10m。
SCOTT@book> ALTER DATABASE DATAFILE '/mnt/ramdisk/book/sugar01.dbf' RESIZE 10M;
Database altered.

$ ls -l /mnt/ramdisk/book/sugar01.dbf
-rw-r----- 1 oracle oinstall 41951232 2016-11-01 09:47:14 /mnt/ramdisk/book/sugar01.dbf
--可以发现这个时候已经出现异常,无法shrink。或者内部一些字典已经修改了。

BBED>  p /d dba 6,1  kcvfhhdr.kccfhfsz
ub4 kccfhfsz                                @44       5120

--可以发现执行ALTER DATABASE DATAFILE '/mnt/ramdisk/book/sugar01.dbf' RESIZE 10M;是失败的。
-- 5120*8192+8192=41951232

$  ls -l  /u01/backup/d6_A_15rjqgkp_1_1
-rw-r----- 1 oracle oinstall 26083328 2016-11-01 09:52:25 /u01/backup/d6_A_15rjqgkp_1_1

$ strings  /u01/backup/d6_A* | grep 'BBBB'|wc
200000  340080 7243655
$ strings  /u01/backup/d6_A* | grep 'CCCC'|wc
200000  340080 7243655
$ strings  /u01/backup/d6_A* | grep 'AAAA'|wc
100000  170040 3624269

--可以发现备份也做了T2,T3备份。

SCOTT@book> select file#,CHECKPOINT_CHANGE#,ABSOLUTE_FUZZY_CHANGE# from v$backup_datafile;

     FILE# CHECKPOINT_CHANGE# ABSOLUTE_FUZZY_CHANGE#
---------- ------------------ ----------------------
         6            2404824                      0
         0            2405169                      0

--而且备份期间没有出现高于检查点scn高于2404824的scn号。

SCOTT@book> SELECT file#, CHECKPOINT_CHANGE#, CHECKPOINT_TIME,CREATION_CHANGE#  , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy,name,tablespace_name  FROM v$datafile_header where file#=6;
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME     CREATION_CHANGE# RESETLOGS_CHANGE# STATUS  CHECKPOINT_COUNT FUZ NAME                          TABLESPACE_NAME
----- ------------------ ------------------- ---------------- ----------------- ------- ---------------- --- ----------------------------- ---------------
    6            2404875 2016-11-01 09:47:14          2404192           2002065 ONLINE                 6 YES /mnt/ramdisk/book/sugar01.dbf SUGAR

--从这里可以看出备份时视乎已经确定要备份文件的大小,而且我觉得备份期间读取了位图信息,仅仅非NULL的块已经确定,应该是从文
--件头位图确定,这个时候实际上不能缩小数据文件的。
--你可以看出我已经发出了检查点,但是T2的信息,T3的信息依旧出现备份集中。
--而且从前面的测试,明显存在问题,建议不要在备份期间做shrink数据文件的错误。

3.继续看看数据文件头在备份集什么位置:

--通过bbed观察:

BBED> set dba 6,1
        DBA             0x01800001 (25165825 6,1)

BBED> p kcvfh.kcvfhtln
ub2 kcvfhtln                                @336      0x0005

BBED> p kcvfh.kcvfhtnm
text kcvfhtnm[0]                            @338     S
text kcvfhtnm[1]                            @339     U
text kcvfhtnm[2]                            @340     G
text kcvfhtnm[3]                            @341     A
text kcvfhtnm[4]                            @342     R
text kcvfhtnm[5]                            @343
--可以发现表数据文件里面记录了表空间名字。前面一个字段kcvfh.kcvfhtln记录表空间名长度,正好是5。

$ strings -t d /u01/backup/d6_A_15rjqgkp_1_1 | grep SUGAR
26067282 SUGAR

-- 26067282/8192=3182.041259765625M,相关记录在备份集中3182块。
-- 而实际备份集文件大小26083328,26083328/8192=3184块。也就是除掉备份集块头有3183块(备份集也有1个OS块)
-- 从这里可以看出文件头先读出,但是是最后写入备份集的,当然最后还有1块不知道写的是什么。

BBED> set filename '/u01/backup/d6_A_15rjqgkp_1_1'
        FILENAME        /u01/backup/d6_A_15rjqgkp_1_1

BBED> set block 3182
        BLOCK#          3182

BBED> p kcvfh.kcvfhtln
ub2 kcvfhtln                                @336      0x0005

BBED> p kcvfh.kcvfhtnm
text kcvfhtnm[0]                            @338     S
text kcvfhtnm[1]                            @339     U
text kcvfhtnm[2]                            @340     G
text kcvfhtnm[3]                            @341     A
text kcvfhtnm[4]                            @342     R
text kcvfhtnm[5]                            @343
..

BBED> p /d kcvfh.kcvfhcrs.kscnbas
ub4 kscnbas                                 @100      2404192

BBED> p /d kcvfh.kcvfhckp.kcvcpscn.kscnbas
ub4 kscnbas                                 @484      2404824

--//这个结果与数据块对应的是:
BBED> p  /d dba 6,1 kcvfh.kcvfhcrs.kscnbas
ub4 kscnbas                                 @100      2404192

BBED> p  /d dba 6,1 kcvfh.kcvfhckp.kcvcpscn.kscnbas
ub4 kscnbas                                 @484      2404875

--看看数据文件大小:
BBED> p /d dba 6,1  kcvfhhdr.kccfhfsz
ub4 kccfhfsz                                @44       5120

BBED> p /d filename '/u01/backup/d6_A_15rjqgkp_1_1' block 3182  kcvfhhdr.kccfhfsz
ub4 kccfhfsz                                @44       5120

-- 5376*8192+8192=44048384,两者一致。

$ ls -l /mnt/ramdisk/book/sugar01.dbf
-rw-r----- 1 oracle oinstall 41951232 2016-11-01 09:47:14 /mnt/ramdisk/book/sugar01.dbf

4.数据字典不一致问题:
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  34359721984      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  34359721984      4194302         1280    816840704        99712 ONLINE
/mnt/ramdisk/book/system01.dbf         1 SYSTEM             786432000        96000 AVAILABLE            1 YES  34359721984      4194302         1280    785383424        95872 SYSTEM
/mnt/ramdisk/book/example01.dbf        5 EXAMPLE            328335360        40080 AVAILABLE            5 YES  34359721984      4194302           80    327286784        39952 ONLINE
/mnt/ramdisk/book/sugar01.dbf          6 SUGAR               10485760         1280 AVAILABLE            6 YES  34359721984      4194302         2048      9437184         1152 ONLINE
6 rows selected.

--可以发现这个时候file_id=6,bytes=10485760,blocks=1280.

SCOTT@book> column SPARE3 noprint
SCOTT@book> column SPARE4 noprint
SCOTT@book> select * from sys.file$ f where file#=6;
FILE#      STATUS$       BLOCKS          TS#     RELFILE#    MAXEXTEND          INC     CRSCNWRP     CRSCNBAS OWNERINSTANCE       SPARE1       SPARE2
----- ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------- ------------ ------------
    6            2         5120            7            6      4194302         2048            0      2404192                   25165826

--二者已经存在不一致。

我看了视图DBA_DATA_FILES定义,出现问题是:
  SELECT v.name
         ,f.file#
         ,ts.name
         ,DECODE (hc.ktfbhccval, 0, ts.blocksize * hc.ktfbhcsz, NULL)
         ,DECODE (hc.ktfbhccval, 0, hc.ktfbhcsz, NULL)
         ,DECODE (f.status$,  1, 'INVALID',  2, 'AVAILABLE',  'UNDEFINED')
         ,f.relfile#
         ,DECODE
          (
             hc.ktfbhccval
            ,0, DECODE (hc.ktfbhcinc, 0, 'NO', 'YES')
            ,NULL
          )
         ,DECODE (hc.ktfbhccval, 0, ts.blocksize * hc.ktfbhcmaxsz, NULL)
         ,DECODE (hc.ktfbhccval, 0, hc.ktfbhcmaxsz, NULL)
         ,DECODE (hc.ktfbhccval, 0, hc.ktfbhcinc, NULL)
         ,DECODE (hc.ktfbhccval, 0, hc.ktfbhcusz * ts.blocksize, NULL)
         ,DECODE (hc.ktfbhccval, 0, hc.ktfbhcusz, NULL)
         ,DECODE
          (
             fe.fetsn
            ,0, DECODE (BITAND (fe.festa, 2), 0, 'SYSOFF', 'SYSTEM')
            ,DECODE
             (
                BITAND (fe.festa, 18)
               ,0, 'OFFLINE'
               ,2, 'ONLINE'
               ,'RECOVER'
             )
          )
     FROM sys.v$dbfile v
         ,sys.file$ f
         ,sys.x$ktfbhc hc
         ,sys.ts$ ts
         ,x$kccfe fe
    WHERE     v.file# = f.file#
          AND f.spare1 IS NOT NULL
          AND v.file# = hc.ktfbhcafno
          AND hc.ktfbhctsn = ts.ts#
          AND fe.fenum = f.file#;
--访问sys.x$ktfbhc 与实际不符合。

SYS@book> select * from x$ktfbhc ;
ADDR                   INDX    INST_ID KTFBHCAFNO  KTFBHCTSN  KTFBHCFNO KTFBHCHAFNO KTFBHCHRFNO KTFBHCHBNO  KTFBHCHSZ   KTFBHCSZ  KTFBHCINC KTFBHCMAXSZ KTFBHCFREE  KTFBHCUSZ KTFBHCCVAL  KTFBHCDBS KTFBHCDWRP
---------------- ---------- ---------- ---------- ---------- ---------- ----------- ----------- ---------- ---------- ---------- ---------- ----------- ---------- ---------- ---------- ---------- ----------
00007F21956C5A08          0          1          1          0          1           1           1          2        126      96000       1280     4194302        768      95872          0    2356313          0
00007F21956C5A08          1          1          2          1          2           2           2          2        126      99840       1280     4194302       5968      99712          0    2353749          0
00007F21956C5A08          2          1          3          2          3           3           3          2        126      10880        640      131072       9936      10752          0    2383655          0
00007F21956C5A08          3          1          4          4          4           4           4          2        126       6400        160     4194302       5872       6272          0    2358965          0
00007F21956C5A08          4          1          5          6          5           5           5          2        126      40080         80     4194302        376      39952          0     942056          0
00007F21956C5A08          5          1          6          7          6           6           6          2        126       1280       2048     4194302        512       1152          0    2404867          0
6 rows selected.

--重启数据库看看。
SYS@book> select * from DBA_DATA_FILES where file_id=6;
FILE_NAME                     FILE_ID TABLESPACE_NAME      BYTES     BLOCKS STATUS    RELATIVE_FNO AUT   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS ONLINE_
----------------------------- ------- --------------- ---------- ---------- --------- ------------ --- ---------- ---------- ------------ ---------- ----------- -------
/mnt/ramdisk/book/sugar01.dbf       6 SUGAR             10485760       1280 AVAILABLE            6 YES 3.4360E+10    4194302         2048    9437184        1152 ONLINE
--这里标识使用块数量USER_BLOCKS=1152

SYS@book> select * from x$ktfbhc ;
ADDR                   INDX    INST_ID KTFBHCAFNO  KTFBHCTSN  KTFBHCFNO KTFBHCHAFNO KTFBHCHRFNO KTFBHCHBNO  KTFBHCHSZ   KTFBHCSZ  KTFBHCINC KTFBHCMAXSZ KTFBHCFREE  KTFBHCUSZ KTFBHCCVAL  KTFBHCDBS KTFBHCDWRP
---------------- ---------- ---------- ---------- ---------- ---------- ----------- ----------- ---------- ---------- ---------- ---------- ----------- ---------- ---------- ---------- ---------- ----------
00007F781B888C20          0          1          1          0          1           1           1          2        126      96000       1280     4194302        768      95872          0    2356313          0
00007F781B888C20          1          1          2          1          2           2           2          2        126      99840       1280     4194302       5968      99712          0    2353749          0
00007F781B888C20          2          1          3          2          3           3           3          2        126      10880        640      131072       9936      10752          0    2383655          0
00007F781B888C20          3          1          4          4          4           4           4          2        126       6400        160     4194302       5872       6272          0    2358965          0
00007F781B888C20          4          1          5          6          5           5           5          2        126      40080         80     4194302        376      39952          0     942056          0
00007F781B888C20          5          1          6          7          6           6           6          2        126       1280       2048     4194302        512       1152          0    2404867          0
6 rows selected.

SYS@book> select sum(blocks),sum(bytes) from DBA_EXTENTS where file_id=6;
SUM(BLOCKS)   SUM(BYTES)
------------ ------------
         640      5242880

-- 而这里看才仅仅使用640块,存在1152-640=512差距。

$ dbv file=/mnt/ramdisk/book/sugar01.dbf
DBVERIFY: Release 11.2.0.4.0 - Production on Tue Nov 1 10:42:26 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
DBVERIFY - Verification starting : FILE = /mnt/ramdisk/book/sugar01.dbf
DBVERIFY - Verification complete
Total Pages Examined         : 5120
Total Pages Processed (Data) : 2974
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 201
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 1945
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 2404872 (0.2404872)

--dbv检查没有问题。
RMAN> validate datafile 6;
Starting validate at 2016-11-01 10:44:10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=68 device type=DISK
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00006 name=/mnt/ramdisk/book/sugar01.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
6    OK     0              1945         5120            2404872
  File Name: /mnt/ramdisk/book/sugar01.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              2974
  Index      0              0
  Other      0              201
Finished validate at 2016-11-01 10:44:12

SYS@book> ALTER DATABASE DATAFILE '/mnt/ramdisk/book/sugar01.dbf' RESIZE 9M;
Database altered.

$ ls -l /mnt/ramdisk/book/sugar01.dbf
-rw-r----- 1 oracle oinstall 9445376 2016-11-01 10:46:17 /mnt/ramdisk/book/sugar01.dbf
--这样才shrink

SYS@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  34359721984      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  34359721984      4194302         1280    816840704        99712 ONLINE
/mnt/ramdisk/book/system01.dbf         1 SYSTEM             786432000        96000 AVAILABLE            1 YES  34359721984      4194302         1280    785383424        95872 SYSTEM
/mnt/ramdisk/book/example01.dbf        5 EXAMPLE            328335360        40080 AVAILABLE            5 YES  34359721984      4194302           80    327286784        39952 ONLINE
/mnt/ramdisk/book/sugar01.dbf          6 SUGAR                9437184         1152 AVAILABLE            6 YES  34359721984      4194302         2048      8388608         1024 ONLINE
6 rows selected.

--//继续做一个备份:
RMAN> backup datafile 6 format '/u01/backup/d6_B_%U' ;
Starting backup at 2016-11-01 10:49:35
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-01 10:49:35
channel ORA_DISK_1: finished piece 1 at 2016-11-01 10:49:36
piece handle=/u01/backup/d6_B_1arjqk9v_1_1 tag=TAG20161101T104935 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2016-11-01 10:49:36

Starting Control File and SPFILE Autobackup at 2016-11-01 10:49:36
piece handle=/u01/app/oracle/fast_recovery_area/BOOK/autobackup/2016_11_01/o1_mf_s_926765376_d1j0p0p1_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 2016-11-01 10:49:37

$ ls -l d6_B_1arjqk9v_1_1
-rw-r----- 1 oracle oinstall 6111232 2016-11-01 10:49:35 d6_B_1arjqk9v_1_1

4.测试恢复看看:

$ mv d6_B_1arjqk9v_1_1 d6_B
--//改名的主要目的是不使用这个备份来恢复。

SCOTT@book> alter database datafile 6 offline ;
Database altered.

RMAN> restore datafile 6;

Starting restore at 2016-11-01 10:52:26
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=68 device type=DISK

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_B_1arjqk9v_1_1
channel ORA_DISK_1: ORA-19870: error while restoring backup piece /u01/backup/d6_B_1arjqk9v_1_1
ORA-19505: failed to identify file "/u01/backup/d6_B_1arjqk9v_1_1"
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

failover to previous backup

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_A_15rjqgkp_1_1
channel ORA_DISK_1: piece handle=/u01/backup/d6_A_15rjqgkp_1_1 tag=TAG20161101T094705
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 2016-11-01 10:52:29

$ ls -l /mnt/ramdisk/book/sugar01.dbf
-rw-r----- 1 oracle oinstall 41951232 2016-11-01 10:52:28 /mnt/ramdisk/book/sugar01.dbf

RMAN> recover datafile 6 ;
Starting recover at 2016-11-01 10:53:06
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 2016-11-01 10:53:06

SCOTT@book> alter database datafile 6 online ;
Database altered.

$ ls -l /mnt/ramdisk/book/sugar01.dbf
-rw-r----- 1 oracle oinstall 9445376 2016-11-01 10:53:26 /mnt/ramdisk/book/sugar01.dbf

SCOTT@book> select rowid from t3 where rownum=1;
select rowid from t3 where rownum=1
                  *
ERROR at line 1:
ORA-00942: table or view does not exist

SCOTT@book> select rowid from t2 where rownum=1;
select rowid from t2 where rownum=1
                  *
ERROR at line 1:
ORA-00942: table or view does not exist

--看来不能在备份期间做这种操作!!!

总结:
1.测试有一些乱,包括思路都有点乱。
2.我仅仅猜测备份时,文件大小就已经确定,最多40M。而具体读取那些块,我估计已经通过位图确定下来。
  你可以看到即使我在备份drop表,后发了alter system checkpoint命令,T2,T3表的信息依旧备份。
3.而且在备份操作时提示ORA-19567: cannot shrink file /mnt/ramdisk/book/sugar01.dbf because it is being backed up or copied
  看来无论何种情况下,在备份期间做数据库维护方面的工作。
 
4.补充在没有shrink到9M前,我自己还做了1次备份(昨天做的):
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
released channel: ORA_DISK_1

RMAN> backup datafile 6 format '/u01/backup/d6_3_%U' ;

Starting backup at 2016-10-31 17:41:35
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=68 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=00006 name=/mnt/ramdisk/book/sugar01.dbf
channel ORA_DISK_1: starting piece 1 at 2016-10-31 17:41:35
channel ORA_DISK_1: finished piece 1 at 2016-10-31 17:41:36
piece handle=/u01/backup/d6_3_0urjoo2f_1_1 tag=TAG20161031T174135 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2016-10-31 17:41:36

$ ls -l  /u01/backup/d6_3_0urjoo2f_1_1
-rw-r----- 1 oracle oinstall 21897216 2016-10-31 17:41:35 /u01/backup/d6_3_0urjoo2f_1_1
--???奇怪了。大小很奇怪把,我已经drop purge表 T2,T3,备份不应该有这个大。

--实际上这个时候已经存在问题了。

--看来不能在备份期间做这种操作!!!

时间: 2024-09-20 19:30:39

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

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

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

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

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

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

[20171123]rman备份与数据文件变化6.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                  

[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备份与数据文件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>