Oracle 11g rman备份相关

jgfhrt

                      1.验证备份是否可以被成功还原，完全还原确定需应用哪些备份集和归档日志。

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74

RMAN> restore database preview; Starting restore at 07-OCT-15 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=202 device type=DISK allocated channel: ORA_DISK_2 channel ORA_DISK_2: SID=138 device type=DISK List of Backup Sets =================== BS Key  Type LV Size       Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 57      Incr 0  363.33M    DISK        00:01:26     05-OCT-15         List of Datafiles in backup set 57   File LV Type Ckp SCN    Ckp Time  Name   ---- -- ---- ---------- --------- ----   1    0  Incr 3687271    05-OCT-15 +DATA/phub/datafile/system.271.891340857   2    0  Incr 3687271    05-OCT-15 +DATA/phub/datafile/sysaux.272.891340857   4    0  Incr 3687271    05-OCT-15 +DATA/phub/datafile/users.269.891340843   7    0  Incr 3687271    05-OCT-15 +DATA/phub/datafile/idx01.dbf   Backup Set Copy #1 of backup set 57   Device Type Elapsed Time Completion Time Compressed Tag   ----------- ------------ --------------- ---------- ---   DISK        00:01:26     05-OCT-15       YES        DB0     List of Backup Pieces for backup set 57 Copy #1     BP Key  Pc# Status      Piece Name     ------- --- ----------- ----------     64      1   AVAILABLE   /home/oracle/backup/data_51qiv187_1_1.bak     65      2   AVAILABLE   /home/oracle/backup/data_51qiv187_2_1.bak     66      3   AVAILABLE   /home/oracle/backup/data_51qiv187_3_1.bak     67      4   AVAILABLE   /home/oracle/backup/data_51qiv187_4_1.bak BS Key  Type LV Size       Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 55      Incr 0  215.65M    DISK        00:01:06     05-OCT-15         List of Datafiles in backup set 55   File LV Type Ckp SCN    Ckp Time  Name   ---- -- ---- ---------- --------- ----   3    0  Incr 3687269    05-OCT-15 +DATA/phub/datafile/undotbs1.260.891340857   5    0  Incr 3687269    05-OCT-15 +DATA/phub/datafile/example.287.891340843   6    0  Incr 3687269    05-OCT-15 +DATA/phub/datafile/llc01.dbf   Backup Set Copy #1 of backup set 55   Device Type Elapsed Time Completion Time Compressed Tag   ----------- ------------ --------------- ---------- ---   DISK        00:01:06     05-OCT-15       YES        DB0     List of Backup Pieces for backup set 55 Copy #1     BP Key  Pc# Status      Piece Name     ------- --- ----------- ----------     60      1   AVAILABLE   /home/oracle/backup/data_50qiv187_1_1.bak     61      2   AVAILABLE   /home/oracle/backup/data_50qiv187_2_1.bak     62      3   AVAILABLE   /home/oracle/backup/data_50qiv187_3_1.bak using channel ORA_DISK_1 using channel ORA_DISK_2 List of Archived Log Copies for database with db_unique_name PHUB ===================================================================== Key     Thrd Seq     S Low Time ------- ---- ------- - --------- 552     1    276     A 05-OCT-15         Name: +DATA/phub/archivelog/2015_10_05/thread_1_seq_276.374.892332021 554     1    277     A 05-OCT-15         Name: +DATA/phub/archivelog/2015_10_06/thread_1_seq_277.372.892352657 556     1    278     A 06-OCT-15         Name: +DATA/phub/archivelog/2015_10_06/thread_1_seq_278.370.892370685 557     1    279     A 06-OCT-15         Name: +DATA/phub/archivelog/2015_10_06/thread_1_seq_279.368.892385187 559     1    280     A 06-OCT-15         Name: +DATA/phub/archivelog/2015_10_06/thread_1_seq_280.365.892385193 562     1    281     A 06-OCT-15         Name: +DATA/phub/archivelog/2015_10_06/thread_1_seq_281.285.892411985 564     1    282     A 06-OCT-15         Name: +DATA/phub/archivelog/2015_10_07/thread_1_seq_282.290.892428501 566     1    283     A 07-OCT-15         Name: +DATA/phub/archivelog/2015_10_07/thread_1_seq_283.300.892458049 Media recovery start SCN is 3687269 Recovery must be done beyond SCN 3687271 to clear datafile fuzziness Finished restore at 07-OCT-15

2.检查用于恢复数据库的的最新备份集，以确认备份是否完整，检查恢复所需要的数据文件副本和归档日志备份集。

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30

RMAN> restore database validate; Starting restore at 07-OCT-15 using channel ORA_DISK_1 using channel ORA_DISK_2 channel ORA_DISK_1: starting validation of datafile backup set channel ORA_DISK_2: starting validation of datafile backup set channel ORA_DISK_1: reading from backup piece /home/oracle/backup/data_50qiv187_1_1.bak channel ORA_DISK_2: reading from backup piece /home/oracle/backup/data_51qiv187_1_1.bak channel ORA_DISK_1: piece handle=/home/oracle/backup/data_50qiv187_1_1.bak tag=DB0 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: reading from backup piece /home/oracle/backup/data_50qiv187_2_1.bak channel ORA_DISK_2: piece handle=/home/oracle/backup/data_51qiv187_1_1.bak tag=DB0 channel ORA_DISK_2: restored backup piece 1 channel ORA_DISK_2: reading from backup piece /home/oracle/backup/data_51qiv187_2_1.bak channel ORA_DISK_2: piece handle=/home/oracle/backup/data_51qiv187_2_1.bak tag=DB0 channel ORA_DISK_2: restored backup piece 2 channel ORA_DISK_2: reading from backup piece /home/oracle/backup/data_51qiv187_3_1.bak channel ORA_DISK_1: piece handle=/home/oracle/backup/data_50qiv187_2_1.bak tag=DB0 channel ORA_DISK_1: restored backup piece 2 channel ORA_DISK_1: reading from backup piece /home/oracle/backup/data_50qiv187_3_1.bak channel ORA_DISK_2: piece handle=/home/oracle/backup/data_51qiv187_3_1.bak tag=DB0 channel ORA_DISK_2: restored backup piece 3 channel ORA_DISK_2: reading from backup piece /home/oracle/backup/data_51qiv187_4_1.bak channel ORA_DISK_1: piece handle=/home/oracle/backup/data_50qiv187_3_1.bak tag=DB0 channel ORA_DISK_1: restored backup piece 3 channel ORA_DISK_1: validation complete, elapsed time: 00:01:03 channel ORA_DISK_2: piece handle=/home/oracle/backup/data_51qiv187_4_1.bak tag=DB0 channel ORA_DISK_2: restored backup piece 4 channel ORA_DISK_2: validation complete, elapsed time: 00:01:12 Finished restore at 07-OCT-15

3.校验数据文件的是否存在坏块：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94

RMAN> backup check logical validate database; Starting backup at 07-OCT-15 using channel ORA_DISK_1 using channel ORA_DISK_2 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=+DATA/phub/datafile/llc01.dbf input datafile file number=00005 name=+DATA/phub/datafile/example.287.891340843 input datafile file number=00003 name=+DATA/phub/datafile/undotbs1.260.891340857 channel ORA_DISK_2: starting full datafile backup set channel ORA_DISK_2: specifying datafile(s) in backup set input datafile file number=00007 name=+DATA/phub/datafile/idx01.dbf input datafile file number=00002 name=+DATA/phub/datafile/sysaux.272.891340857 input datafile file number=00001 name=+DATA/phub/datafile/system.271.891340857 input datafile file number=00004 name=+DATA/phub/datafile/users.269.891340843 channel ORA_DISK_2: backup set complete, elapsed time: 00:00:55 List of Datafiles ================= File Status Marked Corrupt Empty Blocks Blocks Examined High SCN ---- ------ -------------- ------------ --------------- ---------- 1    OK     0              14660        98639           3880208      File Name: +DATA/phub/datafile/system.271.891340857   Block Type Blocks Failing Blocks Processed   ---------- -------------- ----------------   Data       0              65879              Index      0              13480              Other      0              4541             File Status Marked Corrupt Empty Blocks Blocks Examined High SCN ---- ------ -------------- ------------ --------------- ---------- 2    OK     0              23933        120381          3880206      File Name: +DATA/phub/datafile/sysaux.272.891340857   Block Type Blocks Failing Blocks Processed   ---------- -------------- ----------------   Data       0              35531              Index      0              30258              Other      0              30598            File Status Marked Corrupt Empty Blocks Blocks Examined High SCN ---- ------ -------------- ------------ --------------- ---------- 4    OK     0              4478         87390           3006034      File Name: +DATA/phub/datafile/users.269.891340843   Block Type Blocks Failing Blocks Processed   ---------- -------------- ----------------   Data       0              81242              Index      0              730               Other      0              910             File Status Marked Corrupt Empty Blocks Blocks Examined High SCN ---- ------ -------------- ------------ --------------- ---------- 7    OK     0              98073        131072          3010614      File Name: +DATA/phub/datafile/idx01.dbf   Block Type Blocks Failing Blocks Processed   ---------- -------------- ----------------   Data       0              0                  Index      0              32360              Other      0              639             channel ORA_DISK_2: starting full datafile backup set channel ORA_DISK_2: specifying datafile(s) in backup set including current control file in backup set channel ORA_DISK_2: backup set complete, elapsed time: 00:00:01 List of Control File and SPFILE =============================== File Type    Status Blocks Failing Blocks Examined ------------ ------ -------------- --------------- Control File OK     0              646             channel ORA_DISK_1: backup set complete, elapsed time: 00:00:59 List of Datafiles ================= File Status Marked Corrupt Empty Blocks Blocks Examined High SCN ---- ------ -------------- ------------ --------------- ---------- 3    OK     0              1            18560           3880208      File Name: +DATA/phub/datafile/undotbs1.260.891340857   Block Type Blocks Failing Blocks Processed   ---------- -------------- ----------------   Data       0              0                  Index      0              0                  Other      0              18559            File Status Marked Corrupt Empty Blocks Blocks Examined High SCN ---- ------ -------------- ------------ --------------- ---------- 5    OK     2192           31371        44325           3819558      File Name: +DATA/phub/datafile/example.287.891340843   Block Type Blocks Failing Blocks Processed   ---------- -------------- ----------------   Data       0              4394               Index      0              1149               Other      0              7406             File Status Marked Corrupt Empty Blocks Blocks Examined High SCN ---- ------ -------------- ------------ --------------- ---------- 6    OK     0              166327       393216          3015852      File Name: +DATA/phub/datafile/llc01.dbf   Block Type Blocks Failing Blocks Processed   ---------- -------------- ----------------   Data       0              192240            Index      0              32080              Other      0              2569             Finished backup at 07-OCT-15

4.校验单个数据文件：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29

RMAN> backup check logical validate datafile 1; Starting backup at 07-OCT-15 using channel ORA_DISK_1 using channel ORA_DISK_2 channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00001 name=+DATA/phub/datafile/system.271.891340857 channel ORA_DISK_2: starting full datafile backup set channel ORA_DISK_2: specifying datafile(s) in backup set including current control file in backup set channel ORA_DISK_2: backup set complete, elapsed time: 00:00:01 List of Control File and SPFILE =============================== File Type    Status Blocks Failing Blocks Examined ------------ ------ -------------- --------------- Control File OK     0              646             channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07 List of Datafiles ================= File Status Marked Corrupt Empty Blocks Blocks Examined High SCN ---- ------ -------------- ------------ --------------- ---------- 1    OK     0              14660        98639           3880343      File Name: +DATA/phub/datafile/system.271.891340857   Block Type Blocks Failing Blocks Processed   ---------- -------------- ----------------   Data       0              65879              Index      0              13480              Other      0              4541             Finished backup at 07-OCT-15

5.备份进度查询：

1 2 3 4 5 6 7 8 9 10 11

select case          when opname like '%aggregate%' then           'total'          else           opname        end opname,        trunc(sofar * 100 / totalwork, 2) || '%' progress,        units   from v$session_longops where opname like 'RMAN%'    and totalwork > sofar;

1 2 3 4 5

OPNAME     PROGRESS       UNITS ------------------------------------------------------------ ----------------------------------------- -------------------------------- RMAN: incremental datafile backup     62.28%       Blocks total     0%        Blocks RMAN: incremental datafile backup     75.47%       Blocks

6.恢复进度查询：

1 2 3 4 5 6 7 8 9

SELECT sid,        serial#,        CONTEXT,        sofar,        totalwork,        round(sofar / totalwork * 100, 2) "% Complete"   FROM v$session_longops WHERE opname LIKE 'RMAN:%'       AND opname NOT LIKE 'RMAN: aggregate%';

1 2 3 4

SID    SERIAL# CONTEXT      SOFAR  TOTALWORK % Complete ---------- ---------- ---------- ---------- ---------- ---------- 8    5       1     329181437312    75.27        192   31       1     281220456096    61.66

恢复完成：

1 2 3 4 5 6 7 8 9 10 11 12 13

SELECT sid,        serial#,        CONTEXT,        sofar,        totalwork,        round(sofar / totalwork * 100, 2) "% Complete"   FROM v$session_longops WHERE opname LIKE 'RMAN:%'   9     AND opname NOT LIKE 'RMAN: aggregate%';        SID    SERIAL# CONTEXT      SOFAR  TOTALWORK % Complete ---------- ---------- ---------- ---------- ---------- ---------- 8    5       1     437312437312      100        192   31       1     456096456096      100

简单备份脚本：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

run { allocate channel C1 device type disk maxpiecesize=51200M; allocate channel C2 device type disk maxpiecesize=51200M; sql 'alter system switch logfile'; crosscheck archivelog all; delete noprompt expired archivelog all; delete noprompt archivelog until time 'sysdate-15'; backup incremental level=0 tag='db0' as compressed backupset database include current controlfile format '/back/level0/df_%U.bak'; release channel C1; release channel C2; crosscheck backup; delete noprompt obsolete; delete noprompt expired backup; }