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
|
修复完毕后还帮你把库open了。基本上适用于所有类型的文件丢失。
妈妈再也不担心我记不住指令了
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29578568/viewspace-2143635/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29578568/viewspace-2143635/