度量快速开发平台-专业、快速的软件定制快开平台

标题: oracle日志备份恢复 [打印本页]
作者: 张兴康    时间: 2020-6-22 14:25
标题: oracle日志备份恢复
备份前数据：
  1. SQL> select * from duanbb.t1;  
  2.   
  3.         ID NAME  
  4. ---------- --------------------------------------------------  
  5.          1 111
复制代码
rman全备：
  1. RMAN> backup incremental level 0 database;  
  2.   
  3. Starting backup at 09-JUL-15  
  4. using target database control file instead of recovery catalog  
  5. allocated channel: ORA_DISK_1  
  6. channel ORA_DISK_1: SID=63 device type=DISK  
  7. channel ORA_DISK_1: starting incremental level 0 datafile backup set  
  8. channel ORA_DISK_1: specifying datafile(s) in backup set  
  9. input datafile file number=00002 name=/home/oracle11/app/oracle11/oradata/orcl/sysaux01.dbf  
  10. input datafile file number=00001 name=/home/oracle11/app/oracle11/oradata/orcl/system01.dbf  
  11. input datafile file number=00003 name=/home/oracle11/app/oracle11/oradata/orcl/undotbs01.dbf  
  12. input datafile file number=00004 name=/home/oracle11/app/oracle11/oradata/orcl/users01.dbf  
  13. channel ORA_DISK_1: starting piece 1 at 09-JUL-15  
  14. channel ORA_DISK_1: finished piece 1 at 09-JUL-15  
  15. piece handle=/home/oracle11/app/oracle11/flash_recovery_area/ORCL/backupset/2015_07_09/o1_mf_nnnd0_TAG20150709T022947_bsw56w9p_.bkp tag=TAG20150709T022947 comment=NONE  
  16. channel ORA_DISK_1: backup set complete, elapsed time: 00:01:05  
  17. channel ORA_DISK_1: starting incremental level 0 datafile backup set  
  18. channel ORA_DISK_1: specifying datafile(s) in backup set  
  19. including current control file in backup set  
  20. including current SPFILE in backup set  
  21. channel ORA_DISK_1: starting piece 1 at 09-JUL-15  
  22. channel ORA_DISK_1: finished piece 1 at 09-JUL-15  
  23. piece handle=/home/oracle11/app/oracle11/flash_recovery_area/ORCL/backupset/2015_07_09/o1_mf_ncsn0_TAG20150709T022947_bsw58yp1_.bkp tag=TAG20150709T022947 comment=NONE  
  24. channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01  
  25. Finished backup at 09-JUL-15  
复制代码
插入一条数据，进行日志备份：
  1. SQL> insert into duanbb.t1 values(2, '222');  
  2.   
  3. 1 row created.  
  4.   
  5. SQL> commit;  
  6.   
  7. Commit complete.  
  8.   
  9. SQL> alter system switch logfile;  
  10.   
  11. System altered.  
  12.   
  13. SQL> alter system checkpoint;  
  14.   
  15. SQL> select * from duanbb.t1;  
  16.   
  17.         ID NAME  
  18. ---------- --------------------------------------------------  
  19.          1 111  
  20.          2 222  
复制代码
  1. RMAN> backup archivelog all delete input;  
  2.   
  3. Starting backup at 09-JUL-15  
  4. current log archived  
  5. using channel ORA_DISK_1  
  6. channel ORA_DISK_1: starting archived log backup set  
  7. channel ORA_DISK_1: specifying archived log(s) in backup set  
  8. input archived log thread=1 sequence=3 RECID=473 STAMP=884557194  
  9. input archived log thread=1 sequence=4 RECID=474 STAMP=884559659  
  10. input archived log thread=1 sequence=5 RECID=475 STAMP=884568650  
  11. input archived log thread=1 sequence=6 RECID=476 STAMP=884571491  
  12. input archived log thread=1 sequence=7 RECID=477 STAMP=884572546  
  13. input archived log thread=1 sequence=8 RECID=478 STAMP=884572648  
  14. channel ORA_DISK_1: starting piece 1 at 09-JUL-15  
  15. channel ORA_DISK_1: finished piece 1 at 09-JUL-15  
  16. piece handle=/home/oracle11/app/oracle11/flash_recovery_area/ORCL/backupset/2015_07_09/o1_mf_annnn_TAG20150709T023728_bsw5o8w2_.bkp tag=TAG20150709T023728 comment=NONE  
  17. channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03  
  18. channel ORA_DISK_1: deleting archived log(s)  
  19. archived log file name=/home/oracle11/app/oracle11/flash_recovery_area/ORCL/archivelog/2015_07_08/o1_mf_1_3_bsvpl9xj_.arc RECID=473 STAMP=884557194  
  20. archived log file name=/home/oracle11/app/oracle11/flash_recovery_area/ORCL/archivelog/2015_07_08/o1_mf_1_4_bsvrzcfm_.arc RECID=474 STAMP=884559659  
  21. archived log file name=/home/oracle11/app/oracle11/flash_recovery_area/ORCL/archivelog/2015_07_09/o1_mf_1_5_bsw1rb2n_.arc RECID=475 STAMP=884568650  
  22. archived log file name=/home/oracle11/app/oracle11/flash_recovery_area/ORCL/archivelog/2015_07_09/o1_mf_1_6_bsw4k3pw_.arc RECID=476 STAMP=884571491  
  23. archived log file name=/home/oracle11/app/oracle11/flash_recovery_area/ORCL/archivelog/2015_07_09/o1_mf_1_7_bsw5l2jy_.arc RECID=477 STAMP=884572546  
  24. archived log file name=/home/oracle11/app/oracle11/flash_recovery_area/ORCL/archivelog/2015_07_09/o1_mf_1_8_bsw5o8fs_.arc RECID=478 STAMP=884572648  
  25. Finished backup at 09-JUL-15  
复制代码
再次插入一条数据，备份日志：
  1. SQL> insert into duanbb.t1 values(3, '333');  
  2.   
  3. 1 row created.  
  4.   
  5. SQL> commit;  
  6.   
  7. Commit complete.  
  8.   
  9. SQL> alter system switch logfile;  
  10.   
  11. System altered.  
  12.   
  13. SQL> alter system checkpoint;  
复制代码
  1. RMAN> backup archivelog all delete input;  
  2.   
  3. Starting backup at 09-JUL-15  
  4. current log archived  
  5. using channel ORA_DISK_1  
  6. channel ORA_DISK_1: starting archived log backup set  
  7. channel ORA_DISK_1: specifying archived log(s) in backup set  
  8. input archived log thread=1 sequence=9 RECID=479 STAMP=884572860  
  9. input archived log thread=1 sequence=10 RECID=480 STAMP=884573073  
  10. channel ORA_DISK_1: starting piece 1 at 09-JUL-15  
  11. channel ORA_DISK_1: finished piece 1 at 09-JUL-15  
  12. piece handle=/home/oracle11/app/oracle11/flash_recovery_area/ORCL/backupset/2015_07_09/o1_mf_annnn_TAG20150709T024433_bsw62kjx_.bkp tag=TAG20150709T024433 comment=NONE  
  13. channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01  
  14. channel ORA_DISK_1: deleting archived log(s)  
  15. archived log file name=/home/oracle11/app/oracle11/flash_recovery_area/ORCL/archivelog/2015_07_09/o1_mf_1_9_bsw5vwjy_.arc RECID=479 STAMP=884572860  
  16. archived log file name=/home/oracle11/app/oracle11/flash_recovery_area/ORCL/archivelog/2015_07_09/o1_mf_1_10_bsw62kbg_.arc RECID=480 STAMP=884573073  
  17. Finished backup at 09-JUL-15  
复制代码

三次备份生成的文件:

第一次:全备
  o1_mf_nnnd0_TAG20150709T022947_bsw56w9p_.bkp
  o1_mf_ncsn0_TAG20150709T022947_bsw58yp1_.bkp

第二次:日志  
  o1_mf_annnn_TAG20150709T023728_bsw5o8w2_.bkp

第三次: 日志   
  o1_mf_annnn_TAG20150709T024433_bsw62kjx_.bkp

完全恢复(自动恢复到最新状态)：
  1. RMAN> startup mount  
  2.   
  3. connected to target database (not started)  
  4. Oracle instance started  
  5. database mounted  
  6.   
  7. Total System Global Area    1221992448 bytes  
  8.   
  9. Fixed Size                     1336176 bytes  
  10. Variable Size                939527312 bytes  
  11. Database Buffers             268435456 bytes  
  12. Redo Buffers                  12693504 bytes  
复制代码
  1. RMAN> run {  
  2. 2>   allocate channel c1 type disk;  
  3. 3>   restore database;  
  4. 4>   recover database;  
  5. 5>   sql 'alter database open';  
  6. 6> }  
  7.   
  8. allocated channel: c1  
  9. channel c1: SID=63 device type=DISK  
  10.   
  11. Starting restore at 09-JUL-15  
  12.   
  13. channel c1: starting datafile backup set restore  
  14. channel c1: specifying datafile(s) to restore from backup set  
  15. channel c1: restoring datafile 00001 to /home/oracle11/app/oracle11/oradata/orcl/system01.dbf  
  16. channel c1: restoring datafile 00002 to /home/oracle11/app/oracle11/oradata/orcl/sysaux01.dbf  
  17. channel c1: restoring datafile 00003 to /home/oracle11/app/oracle11/oradata/orcl/undotbs01.dbf  
  18. channel c1: restoring datafile 00004 to /home/oracle11/app/oracle11/oradata/orcl/users01.dbf  
  19. channel c1: reading from backup piece /home/oracle11/app/oracle11/flash_recovery_area/ORCL/backupset/2015_07_09/o1_mf_nnnd0_TAG20150709T022947_bsw56w9p_.bkp  
  20. channel c1: piece handle=/home/oracle11/app/oracle11/flash_recovery_area/ORCL/backupset/2015_07_09/o1_mf_nnnd0_TAG20150709T022947_bsw56w9p_.bkp tag=TAG20150709T022947  
  21. channel c1: restored backup piece 1  
  22. channel c1: restore complete, elapsed time: 00:00:36  
  23. Finished restore at 09-JUL-15  
  24.   
  25. Starting recover at 09-JUL-15  
  26.   
  27. starting media recovery  
  28.   
  29. channel c1: starting archived log restore to default destination  
  30. channel c1: restoring archived log  
  31. archived log thread=1 sequence=7  
  32. channel c1: restoring archived log  
  33. archived log thread=1 sequence=8  
  34. channel c1: reading from backup piece /home/oracle11/app/oracle11/flash_recovery_area/ORCL/backupset/2015_07_09/o1_mf_annnn_TAG20150709T023728_bsw5o8w2_.bkp  
  35. channel c1: piece handle=/home/oracle11/app/oracle11/flash_recovery_area/ORCL/backupset/2015_07_09/o1_mf_annnn_TAG20150709T023728_bsw5o8w2_.bkp tag=TAG20150709T023728  
  36. channel c1: restored backup piece 1  
  37. channel c1: restore complete, elapsed time: 00:00:01  
  38. archived log file name=/home/oracle11/app/oracle11/flash_recovery_area/ORCL/archivelog/2015_07_09/o1_mf_1_7_bsw6gt1l_.arc thread=1 sequence=7  
  39. channel default: deleting archived log(s)  
  40. archived log file name=/home/oracle11/app/oracle11/flash_recovery_area/ORCL/archivelog/2015_07_09/o1_mf_1_7_bsw6gt1l_.arc RECID=482 STAMP=884573466  
  41. archived log file name=/home/oracle11/app/oracle11/flash_recovery_area/ORCL/archivelog/2015_07_09/o1_mf_1_8_bsw6gt1s_.arc thread=1 sequence=8  
  42. channel default: deleting archived log(s)  
  43. archived log file name=/home/oracle11/app/oracle11/flash_recovery_area/ORCL/archivelog/2015_07_09/o1_mf_1_8_bsw6gt1s_.arc RECID=481 STAMP=884573466  
  44. media recovery complete, elapsed time: 00:00:01  
  45. channel c1: starting archived log restore to default destination  
  46. channel c1: restoring archived log  
  47. archived log thread=1 sequence=9  
  48. channel c1: restoring archived log  
  49. archived log thread=1 sequence=10  
  50. channel c1: reading from backup piece /home/oracle11/app/oracle11/flash_recovery_area/ORCL/backupset/2015_07_09/o1_mf_annnn_TAG20150709T024433_bsw62kjx_.bkp  
  51. channel c1: piece handle=/home/oracle11/app/oracle11/flash_recovery_area/ORCL/backupset/2015_07_09/o1_mf_annnn_TAG20150709T024433_bsw62kjx_.bkp tag=TAG20150709T024433  
  52. channel c1: restored backup piece 1  
  53. channel c1: restore complete, elapsed time: 00:00:01  
  54. channel default: deleting archived log(s)  
  55. archived log file name=/home/oracle11/app/oracle11/flash_recovery_area/ORCL/archivelog/2015_07_09/o1_mf_1_9_bsw6gwgm_.arc RECID=483 STAMP=884573468  
  56. channel default: deleting archived log(s)  
  57. archived log file name=/home/oracle11/app/oracle11/flash_recovery_area/ORCL/archivelog/2015_07_09/o1_mf_1_10_bsw6gwgq_.arc RECID=484 STAMP=884573468  
  58. Finished recover at 09-JUL-15  
  59.   
  60. sql statement: alter database open  
  61. released channel: c1  
复制代码
还原后的数据 ：
  1. SQL> select * from duanbb.t1;  
  2.   
  3.         ID NAME  
  4. ---------- --------------------------------------------------  
  5.          1 111  
  6.          2 222  
  7.          3 333
复制代码











作者: 张兴康    时间: 2020-6-22 14:26
作者: fteair    时间: 2020-6-22 17:43
作者: fteair    时间: 2020-6-23 16:21
作者: 张兴康    时间: 2020-6-23 17:07
作者: fteair    时间: 2020-6-24 17:08
作者: 张兴康    时间: 2020-6-25 14:13
fteair 发表于 2018-1-24 17:08

作者: caixuqad    时间: 2020-6-25 14:24
作者: caixuqad    时间: 2020-6-25 14:25
作者: 张兴康    时间: 2020-6-26 14:14
作者: fteair    时间: 2020-6-26 15:25
作者: caixuqad    时间: 2020-6-26 15:48
作者: 张兴康    时间: 2020-6-29 15:31
作者: 张兴康    时间: 2020-6-30 14:47
作者: caixuqad    时间: 2020-6-30 17:47
作者: caixuqad    时间: 2020-6-30 18:05
作者: 张兴康    时间: 2020-7-1 16:34
作者: caixuqad    时间: 2020-7-1 17:54
作者: 张兴康    时间: 2020-7-2 15:13
作者: caixuqad    时间: 2020-7-2 15:41
作者: 张兴康    时间: 2020-7-5 14:32
作者: caixuqad    时间: 2020-7-5 17:08
作者: caixuqad    时间: 2020-7-6 17:23




欢迎光临 度量快速开发平台-专业、快速的软件定制快开平台 (http://bbs.delit.cn/) Powered by Discuz! X3.2