1. 首页
  2. IT资讯

RMAN快速恢复数据库(DBA再也不担心记不住指令了)

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

修复完毕后还帮你把库open了。基本上适用于所有类型的文件丢失。 妈妈再也不担心我记不住指令了

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29578568/viewspace-2143635/,如需转载,请注明出处,否则将追究法律责任。

主题测试文章,只做测试使用。发布者:布吉卡,转转请注明出处:http://www.cxybcw.com/195240.html

联系我们

13687733322

在线咨询:点击这里给我发消息

邮件:1877088071@qq.com

工作时间:周一至周五,9:30-18:30,节假日休息

QR code