文章详情

短信预约-IT技能 免费直播动态提醒

请输入下面的图形验证码

提交验证

短信预约提醒成功

RMAN如何快速恢复数据库

2024-04-02 19:55

关注

小编给大家分享一下RMAN如何快速恢复数据库,希望大家阅读完这篇文章之后都有所收获,下面让我们一起去探讨吧!

10g会使用RMAN备份恢复一般是DBA的工作,对技术要求较高,且对oracle的组织结构有较深的理解才可以进行操作,而且由于数据库故障不易发生,大部分DBA也不会记住命令,需要的手查一下,各种文件丢失的脚本又都不一样,例如

控制文件丢失恢复指令:restore controlfile from autobackup;

redolog 丢失的情况:alter database clear (unarchived) logfile;

不完全恢复指令:recover database until cancel;

11g后rman有了更丰富的指令集和修复方法,使得普通运维人员也能迅速快速修复数据库故障,(list 、advise、repair)

见如下实验。
第一种情况,模拟控制文件丢失,删除controlfile

1

2

3

4

5

6

7

8

9

SQL> startup

ORACLE instance started.

 

Total SystemGlobalArea  510554112 bytes

FixedSize                 1345968 bytes

VariableSize            171968080 bytes

DatabaseBuffers          331350016 bytes

Redo Buffers                5890048 bytes

ORA-00205: errorinidentifying control file,checkalert logformore info



启动数据库发现数据库已经无法启动,现在我们用两种方法来尝试恢复下:

传统的方法:

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

RMAN>restore controlfile from autobackup;

 

Starting restoreat30-AUG-16

using targetdatabasecontrol fileinsteadofrecovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=20 device type=DISK

 

recovery area destination: /u01/app/oracle/fra

databasename(ordatabaseuniquename) usedforsearch: PROD2

channel ORA_DISK_1: AUTOBACKUP /u01/app/oracle/fra/PROD2/autobackup/2016_08_24/o1_mf_s_920718874_cvt48tkl_.bkp foundinthe recovery area

AUTOBACKUP searchwithformat"%F"notattempted because DBID wasnotset

channel ORA_DISK_1: restoring control filefromAUTOBACKUP /u01/app/oracle/fra/PROD2/autobackup/2016_08_24/o1_mf_s_920718874_cvt48tkl_.bkp

channel ORA_DISK_1: control file restorefromAUTOBACKUP complete

outputfilename=/u01/app/oracle/oradata/PROD2/control01.ctl

outputfilename=/u01/app/oracle/fast_recovery_area/PROD2/control02.ctl

Finished restoreat30-AUG-16


11g 的快速恢复方法:

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

RMAN> list failure;

 

using targetdatabasecontrol fileinsteadofrecovery catalog

ListofDatabaseFailures

=========================

 

Failure ID Priority Status   TimeDetected Summary

---------- -------- --------- ------------- -------

712        CRITICALOPEN     30-AUG-16     Control file /u01/app/oracle/oradata/PROD2/control01.ctlismissing

 

RMAN> advise failure;

 

ListofDatabaseFailures

=========================

 

Failure ID Priority Status   TimeDetected Summary

---------- -------- --------- ------------- -------

712        CRITICALOPEN     30-AUG-16     Control file /u01/app/oracle/oradata/PROD2/control01.ctlismissing

 

analyzing automatic repair options; this may takesometime

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=20 device type=DISK

analyzing automatic repair options complete

 

Mandatory Manual Actions

========================

nomanual actions available

 

Optional Manual Actions

=======================

nomanual actions available

 

Automated Repair Options

========================

OptionRepair Description

------ ------------------

1      Use a multiplexed copytorestore control file /u01/app/oracle/oradata/PROD2/control01.ctl 

  Strategy: The repair includes complete media recoverywithnodata loss

  Repair script: /u01/app/oracle/diag/rdbms/prod2/PROD2/hm/reco_1499999453.hm

 

RMAN> repair failure;

 

Strategy: The repair includes complete media recoverywithnodata loss

Repair script: /u01/app/oracle/diag/rdbms/prod2/PROD2/hm/reco_1499999453.hm

 

contentsofrepair script:

   # restore control file using multiplexed copy

   restore controlfilefrom'/u01/app/oracle/fast_recovery_area/PROD2/control02.ctl';

   sql'alter database mount';

 

Do you really wanttoexecutethe above repair (enter YESorNO)? yes

executing repair script

 

Starting restoreat30-AUG-16

using channel ORA_DISK_1

 

channel ORA_DISK_1: copied control file copy

outputfilename=/u01/app/oracle/oradata/PROD2/control01.ctl

outputfilename=/u01/app/oracle/fast_recovery_area/PROD2/control02.ctl

Finished restoreat30-AUG-16

 

sql statement:alterdatabasemount

released channel: ORA_DISK_1

repair failure complete



从以上方法还看不出自动修复的好处,那我们再增加点难度,删除所有的数据文件(不包括参数文件),对比下吧

1

2

3

4

5

6

7

8

9

SQL> startup

ORACLE instance started.

 

Total SystemGlobalArea  510554112 bytes

FixedSize                 1345968 bytes

VariableSize            171968080 bytes

DatabaseBuffers          331350016 bytes

Redo Buffers                5890048 bytes

ORA-00205: errorinidentifying control file,checkalert logformore



传统处理方法,使用以下脚本可以恢复数据库到启动状态,这里就需要比较专业的知识了



1

2

3

4

5

6

7

run{

restore controlfile from autobackup;

alter database mount;

restore database;

recover database until cancel;

alter database open resetlogs;

};


接下来是11g的恢复方法:list-advise-repair

1

2

3

4

5

6

7

8

9

10

11

12

13

14

RMAN> list failure;

 

using targetdatabasecontrol fileinsteadofrecovery catalog

ListofDatabaseFailures

=========================

 

Failure ID Priority Status   TimeDetected Summary

---------- -------- --------- ------------- -------

958        CRITICALOPEN     30-AUG-16     System datafile 1:'/u01/app/oracle/oradata/PROD2/system01.dbf'ismissing

915        CRITICALOPEN     30-AUG-16     Control file /u01/app/oracle/oradata/PROD2/control01.ctlismissing

838        CRITICALOPEN     30-AUG-16     System datafile 1:'/u01/app/oracle/oradata/PROD2/system01.dbf'needs media recovery

835        CRITICALOPEN     30-AUG-16     Control file needs media recovery

415        HIGH    OPEN     30-AUG-16     Oneormore non-system datafiles are missing

841        HIGH    OPEN     30-AUG-16     Oneormore non-system datafiles need media recovery


可以发先已经告诉我们这些文件丢失了

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

RMAN> advise failure;

 

ListofDatabaseFailures

=========================

 

Failure ID Priority Status   TimeDetected Summary

---------- -------- --------- ------------- -------

958        CRITICALOPEN     30-AUG-16     System datafile 1:'/u01/app/oracle/oradata/PROD2/system01.dbf'ismissing

915        CRITICALOPEN     30-AUG-16     Control file /u01/app/oracle/oradata/PROD2/control01.ctlismissing

838        CRITICALOPEN     30-AUG-16     System datafile 1:'/u01/app/oracle/oradata/PROD2/system01.dbf'needs media recovery

835        CRITICALOPEN     30-AUG-16     Control file needs media recovery

415        HIGH    OPEN     30-AUG-16     Oneormore non-system datafiles are missing

841        HIGH    OPEN     30-AUG-16     Oneormore non-system datafiles need media recovery

 

analyzing automatic repair options; this may takesometime

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=20 device type=DISK

analyzing automatic repair options complete

 

Notallspecified failures can currently be repaired.

The following failures must be repaired before adviseforothers can be given.

 

Failure ID Priority Status   TimeDetected Summary

---------- -------- --------- ------------- -------

915        CRITICALOPEN     30-AUG-16     Control file /u01/app/oracle/oradata/PROD2/control01.ctlismissing

 

Mandatory Manual Actions

========================

nomanual actions available

 

Optional Manual Actions

=======================

nomanual actions available

 

Automated Repair Options

========================

OptionRepair Description

------ ------------------

1      Use a multiplexed copytorestore control file /u01/app/oracle/oradata/PROD2/control01.ctl 

  Strategy: The repair includes complete media recoverywithnodata loss

  Repair script: /u01/app/oracle/diag/rdbms/prod2/PROD2/hm/reco_3157315699.hm


rman已经给出建议及执行的脚本。

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

RMAN> repair failure;

 

Strategy: The repair includes complete media recoverywithnodata loss

Repair script: /u01/app/oracle/diag/rdbms/prod2/PROD2/hm/reco_3157315699.hm

 

contentsofrepair script:

   # restore control file using multiplexed copy

   restore controlfilefrom'/u01/app/oracle/fast_recovery_area/PROD2/control02.ctl';

   sql'alter database mount';

 

Do you really wanttoexecutethe above repair (enter YESorNO)?yes

executing repair script

 

Starting restoreat30-AUG-16

using channel ORA_DISK_1

 

 

channel ORA_DISK_1: copied control file copy

outputfilename=/u01/app/oracle/oradata/PROD2/control01.ctl

outputfilename=/u01/app/oracle/fast_recovery_area/PROD2/control02.ctl

Finished restoreat30-AUG-16

 

 

sql statement:alterdatabasemount

released channel: ORA_DISK_1

repair failure complete

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

RMAN> list failure;

 

ListofDatabaseFailures

=========================

 

Failure ID Priority Status   TimeDetected Summary

---------- -------- --------- ------------- -------

1230       CRITICALOPEN     30-AUG-16     Redo loggroup3isunavailable

1224       CRITICALOPEN     30-AUG-16     Redo loggroup2isunavailable

1218       CRITICALOPEN     30-AUG-16     Redo loggroup1isunavailable

958        CRITICALOPEN     30-AUG-16     System datafile 1:'/u01/app/oracle/oradata/PROD2/system01.dbf'ismissing

838        CRITICALOPEN     30-AUG-16     System datafile 1:'/u01/app/oracle/oradata/PROD2/system01.dbf'needs media recovery

1233       HIGH    OPEN     30-AUG-16     Redo log file /u01/app/oracle/oradata/PROD2/redo03.logismissing

1227       HIGH    OPEN     30-AUG-16     Redo log file /u01/app/oracle/oradata/PROD2/redo02.logismissing

1221       HIGH    OPEN     30-AUG-16     Redo log file /u01/app/oracle/oradata/PROD2/redo01.logismissing

415        HIGH    OPEN     30-AUG-16     Oneormore non-system datafiles are missing

841        HIGH    OPEN     30-AUG-16     Oneormore non-system datafiles need media recovery

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

RMAN> advise failure;

 

ListofDatabaseFailures

=========================

 

Failure ID Priority Status   TimeDetected Summary

---------- -------- --------- ------------- -------

1230       CRITICALOPEN     30-AUG-16     Redo loggroup3isunavailable

1224       CRITICALOPEN     30-AUG-16     Redo loggroup2isunavailable

1218       CRITICALOPEN     30-AUG-16     Redo loggroup1isunavailable

958        CRITICALOPEN     30-AUG-16     System datafile 1:'/u01/app/oracle/oradata/PROD2/system01.dbf'ismissing

838        CRITICALOPEN     30-AUG-16     System datafile 1:'/u01/app/oracle/oradata/PROD2/system01.dbf'needs media recovery

1233       HIGH    OPEN     30-AUG-16     Redo log file /u01/app/oracle/oradata/PROD2/redo03.logismissing

1227       HIGH    OPEN     30-AUG-16     Redo log file /u01/app/oracle/oradata/PROD2/redo02.logismissing

1221       HIGH    OPEN     30-AUG-16     Redo log file /u01/app/oracle/oradata/PROD2/redo01.logismissing

415        HIGH    OPEN     30-AUG-16     Oneormore non-system datafiles are missing

841        HIGH    OPEN     30-AUG-16     Oneormore non-system datafiles need media recovery

 

analyzing automatic repair options; this may takesometime

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=20 device type=DISK

analyzing automatic repair options complete

 

Mandatory Manual Actions

========================

nomanual actions available

 

Optional Manual Actions

=======================

1. If file /u01/app/oracle/oradata/PROD2/redo03.log was unintentionally renamedormoved, restore it

2. If file /u01/app/oracle/oradata/PROD2/redo02.log was unintentionally renamedormoved, restore it

3. If file /u01/app/oracle/oradata/PROD2/redo01.log was unintentionally renamedormoved, restore it

 

Automated Repair Options

========================

OptionRepair Description

------ ------------------

1      Perform incompletedatabaserecoverytoSCN 1206859 

  Strategy: The repair includes point-in-timerecoverywithsomedata loss

  Repair script: /u01/app/oracle/diag/rdbms/prod2/PROD2/hm/reco_3316371170.hm

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

RMAN> repair failure;

 

Strategy: The repair includes point-in-timerecoverywithsomedata loss

Repair script: /u01/app/oracle/diag/rdbms/prod2/PROD2/hm/reco_3316371170.hm

 

contentsofrepair script:

   #databasepoint-in-timerecovery

   resetdatabasetoincarnation 5;

   restoredatabaseuntil scn 1206859;

   recoverdatabaseuntil scn 1206859;

   alterdatabaseopenresetlogs;

 

Do you really wanttoexecutethe above repair (enter YESorNO)? YES

executing repair script

 

databaseresettoincarnation 5

 

Starting restoreat30-AUG-16

using channel ORA_DISK_1

 

channel ORA_DISK_1: starting datafile backupsetrestore

channel ORA_DISK_1: specifying datafile(s)torestorefrombackupset

channel ORA_DISK_1: restoring datafile 00001to/u01/app/oracle/oradata/PROD2/system01.dbf

channel ORA_DISK_1: restoring datafile 00002to/u01/app/oracle/oradata/PROD2/sysaux01.dbf

channel ORA_DISK_1: restoring datafile 00003to/u01/app/oracle/oradata/PROD2/undotbs01.dbf

channel ORA_DISK_1: restoring datafile 00004to/u01/app/oracle/oradata/PROD2/users01.dbf

channel ORA_DISK_1: readingfrombackup piece /u01/app/oracle/fra/PROD2/backupset/2016_08_24/o1_mf_nnndf_TAG20160824T111405_cvt47yrv_.bkp

channel ORA_DISK_1: piece handle=/u01/app/oracle/fra/PROD2/backupset/2016_08_24/o1_mf_nnndf_TAG20160824T111405_cvt47yrv_.bkp tag=TAG20160824T111405

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsedtime: 00:00:15

Finished restoreat30-AUG-16

 

Starting recoverat30-AUG-16

using channel ORA_DISK_1

 

starting media recovery

 

archived logforthread 1withsequence3isalreadyondiskasfile /u01/app/oracle/fra/PROD2/archivelog/2016_08_24/o1_mf_1_3_cvt48qv1_.arc

archived logforthread 1withsequence4isalreadyondiskasfile /u01/app/oracle/fra/PROD2/archivelog/2016_08_24/o1_mf_1_4_cvvbdhx0_.arc

archived logforthread 1withsequence5isalreadyondiskasfile /u01/app/oracle/fra/PROD2/archivelog/2016_08_30/o1_mf_1_5_cw9m2no2_.arc

archived log filename=/u01/app/oracle/fra/PROD2/archivelog/2016_08_24/o1_mf_1_3_cvt48qv1_.arc thread=1sequence=3

archived log filename=/u01/app/oracle/fra/PROD2/archivelog/2016_08_24/o1_mf_1_4_cvvbdhx0_.arc thread=1sequence=4

archived log filename=/u01/app/oracle/fra/PROD2/archivelog/2016_08_30/o1_mf_1_5_cw9m2no2_.arc thread=1sequence=5

media recovery complete, elapsedtime: 00:00:02

Finished recoverat30-AUG-16

 

databaseopened

repair failure complete

看完了这篇文章,相信你对“RMAN如何快速恢复数据库”有了一定的了解,如果想了解更多相关知识,欢迎关注亿速云行业资讯频道,感谢各位的阅读!

阅读原文内容投诉

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

软考中级精品资料免费领

  • 历年真题答案解析
  • 备考技巧名师总结
  • 高频考点精准押题
  • 2024年上半年信息系统项目管理师第二批次真题及答案解析(完整版)

    难度     807人已做
    查看
  • 【考后总结】2024年5月26日信息系统项目管理师第2批次考情分析

    难度     351人已做
    查看
  • 【考后总结】2024年5月25日信息系统项目管理师第1批次考情分析

    难度     314人已做
    查看
  • 2024年上半年软考高项第一、二批次真题考点汇总(完整版)

    难度     433人已做
    查看
  • 2024年上半年系统架构设计师考试综合知识真题

    难度     221人已做
    查看

相关文章

发现更多好内容

猜你喜欢

AI推送时光机
位置:首页-资讯-数据库
咦!没有更多了?去看看其它编程学习网 内容吧
首页课程
资料下载
问答资讯