天达云 科技型企业 | 亚太互联网络信息中心(APNIC)成员 | 注册免费体验
天达云首页 > 互联网学习教程 > 关系型数据库 > RMAN如何快速恢复数据库

RMAN如何快速恢复数据库
更新：HHH   时间：2023-1-7

小编给大家分享一下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如何快速恢复数据库”有了一定的了解，如果想了解更多相关知识，欢迎关注天达云行业资讯频道，感谢各位的阅读！
返回关系型数据库教程...
400-837-6568
7 * 24小时全天全国服务热线400电话
天达云·专业的云计算服务平台 全国400服务电话：400-837-6568
Copyright © 2011-2025 Tdyun.com. All Rights Reserved. 天达云 版权所有 粤ICP备18083267号-7