度量快速开发平台-业界最好的全功能软件开发平台

 找回密码
 立即注册

QQ登录

只需一步,快速开始

搜索
热搜: 部件 流程 SQL
查看: 142|回复: 22

[分享] oracle日志备份恢复

[复制链接]

542

主题

5919

帖子

1万

积分

作者

Rank: 7Rank: 7Rank: 7

积分
13589
发表于 2018-1-22 14:25:46 | 显示全部楼层 |阅读模式
备份前数据:
  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
复制代码











回复

使用道具 举报

542

主题

5919

帖子

1万

积分

作者

Rank: 7Rank: 7Rank: 7

积分
13589
 楼主| 发表于 2018-1-22 14:26:31 | 显示全部楼层
回复 支持 反对

使用道具 举报

231

主题

2541

帖子

5807

积分

论坛元老

Rank: 8Rank: 8

积分
5807
发表于 2018-1-22 17:43:55 | 显示全部楼层
回复 支持 反对

使用道具 举报

231

主题

2541

帖子

5807

积分

论坛元老

Rank: 8Rank: 8

积分
5807
发表于 2018-1-23 16:21:44 | 显示全部楼层
回复 支持 反对

使用道具 举报

542

主题

5919

帖子

1万

积分

作者

Rank: 7Rank: 7Rank: 7

积分
13589
 楼主| 发表于 2018-1-23 17:07:13 | 显示全部楼层
回复 支持 反对

使用道具 举报

231

主题

2541

帖子

5807

积分

论坛元老

Rank: 8Rank: 8

积分
5807
发表于 2018-1-24 17:08:41 | 显示全部楼层
回复 支持 反对

使用道具 举报

542

主题

5919

帖子

1万

积分

作者

Rank: 7Rank: 7Rank: 7

积分
13589
 楼主| 发表于 2018-1-25 14:13:39 | 显示全部楼层
回复 支持 反对

使用道具 举报

141

主题

1551

帖子

3573

积分

论坛元老

Rank: 8Rank: 8

积分
3573
发表于 2018-1-25 14:24:00 | 显示全部楼层
回复 支持 反对

使用道具 举报

141

主题

1551

帖子

3573

积分

论坛元老

Rank: 8Rank: 8

积分
3573
发表于 2018-1-25 14:25:57 | 显示全部楼层
回复 支持 反对

使用道具 举报

542

主题

5919

帖子

1万

积分

作者

Rank: 7Rank: 7Rank: 7

积分
13589
 楼主| 发表于 2018-1-26 14:14:12 | 显示全部楼层
回复 支持 反对

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

手机版|小黑屋|重庆度量科技  本站关键词:快速开发平台

GMT+8, 2018-5-23 15:27 , Processed in 0.353752 second(s), 27 queries .

Powered by Discuz! X3.2

© 2001-2013 Comsenz Inc.

快速回复 返回顶部 返回列表