RMAN如何快速恢复数据库
小编给大家分享一下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
FixedSize1345968 bytes
VariableSize171968080 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 StatusTimeDetected Summary
---------- -------- --------- ------------- -------
712 CRITICALOPEN30-AUG-16 Control file /u01/app/oracle/oradata/PROD2/control01.ctlismissing
RMAN> advise failure;
ListofDatabaseFailures
=========================
Failure ID Priority StatusTimeDetected Summary
---------- -------- --------- ------------- -------
712 CRITICALOPEN30-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
FixedSize1345968 bytes
VariableSize171968080 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 StatusTimeDetected Summary
---------- -------- --------- ------------- -------
958 CRITICALOPEN30-AUG-16 System datafile 1:'/u01/app/oracle/oradata/PROD2/system01.dbf'ismissing
915 CRITICALOPEN30-AUG-16 Control file /u01/app/oracle/oradata/PROD2/control01.ctlismissing
838 CRITICALOPEN30-AUG-16 System datafile 1:'/u01/app/oracle/oradata/PROD2/system01.dbf'needs media recovery
835 CRITICALOPEN30-AUG-16 Control file needs media recovery
415 HIGHOPEN30-AUG-16 Oneormore non-system datafiles are missing
841 HIGHOPEN30-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 StatusTimeDetected Summary
---------- -------- --------- ------------- -------
958 CRITICALOPEN30-AUG-16 System datafile 1:'/u01/app/oracle/oradata/PROD2/system01.dbf'ismissing
915 CRITICALOPEN30-AUG-16 Control file /u01/app/oracle/oradata/PROD2/control01.ctlismissing
838 CRITICALOPEN30-AUG-16 System datafile 1:'/u01/app/oracle/oradata/PROD2/system01.dbf'needs media recovery
835 CRITICALOPEN30-AUG-16 Control file needs media recovery
415 HIGHOPEN30-AUG-16 Oneormore non-system datafiles are missing
841 HIGHOPEN30-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 StatusTimeDetected Summary
---------- -------- --------- ------------- -------
915 CRITICALOPEN30-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 StatusTimeDetected Summary
---------- -------- --------- ------------- -------
1230 CRITICALOPEN30-AUG-16 Redo loggroup3isunavailable
1224 CRITICALOPEN30-AUG-16 Redo loggroup2isunavailable
1218 CRITICALOPEN30-AUG-16 Redo loggroup1isunavailable
958 CRITICALOPEN30-AUG-16 System datafile 1:'/u01/app/oracle/oradata/PROD2/system01.dbf'ismissing
838 CRITICALOPEN30-AUG-16 System datafile 1:'/u01/app/oracle/oradata/PROD2/system01.dbf'needs media recovery
1233 HIGHOPEN30-AUG-16 Redo log file /u01/app/oracle/oradata/PROD2/redo03.logismissing
1227 HIGHOPEN30-AUG-16 Redo log file /u01/app/oracle/oradata/PROD2/redo02.logismissing
1221 HIGHOPEN30-AUG-16 Redo log file /u01/app/oracle/oradata/PROD2/redo01.logismissing
415 HIGHOPEN30-AUG-16 Oneormore non-system datafiles are missing
841 HIGHOPEN30-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 StatusTimeDetected Summary
---------- -------- --------- ------------- -------
1230 CRITICALOPEN30-AUG-16 Redo loggroup3isunavailable
1224 CRITICALOPEN30-AUG-16 Redo loggroup2isunavailable
1218 CRITICALOPEN30-AUG-16 Redo loggroup1isunavailable
958 CRITICALOPEN30-AUG-16 System datafile 1:'/u01/app/oracle/oradata/PROD2/system01.dbf'ismissing
838 CRITICALOPEN30-AUG-16 System datafile 1:'/u01/app/oracle/oradata/PROD2/system01.dbf'needs media recovery
1233 HIGHOPEN30-AUG-16 Redo log file /u01/app/oracle/oradata/PROD2/redo03.logismissing
1227 HIGHOPEN30-AUG-16 Redo log file /u01/app/oracle/oradata/PROD2/redo02.logismissing
1221 HIGHOPEN30-AUG-16 Redo log file /u01/app/oracle/oradata/PROD2/redo01.logismissing
415 HIGHOPEN30-AUG-16 Oneormore non-system datafiles are missing
841 HIGHOPEN30-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如何快速恢复数据库”有了一定的了解,如果想了解更多相关知识,欢迎关注亿速云行业资讯频道,感谢各位的阅读!
声明:本站所有文章资源内容,如无特殊说明或标注,均为采集网络资源。如若本站内容侵犯了原著者的合法权益,可联系本站删除。