Oracle DB备份恢复篇之丢失控制文件

Oracle 前端之家
前端之家收集整理的这篇文章主要介绍了Oracle DB备份恢复篇之丢失控制文件，前端之家小编觉得挺不错的，现在分享给大家，也给大家做个参考。

一、实验目的

本篇主要模拟控制文件丢失后，如何根据实际情况恢复数据库，才能使数据库尽可能不丢失数据。

二、实验环境

1）Linux系统环境

[oracle@DG1~]$lsb_release-a
LSBVersion::core-3.1-ia32:core-3.1-noarch:graphics-3.1-ia32:graphics-3.1-noarch
DistributorID:RedHatEnterpriseServer
Description:RedHatEnterpriseLinuxServerrelease5.4(Tikanga)
Release:5.4
Codename:Tikanga

2）Oracle数据库版本信息

sql>select*fromv$version;

BANNER
----------------------------------------------------------------
OracleDatabase10gEnterpriseEditionRelease10.2.0.1.0-Prod
PL/sqlRelease10.2.0.1.0-Production
CORE10.2.0.1.0Production
TNSforLinux:Version10.2.0.1.0-Production
NLSRTLVersion10.2.0.1.0�CProduction

3）查看数据库是否归档

[oracle@DG1~]$sqlplus/assysdba
sql>archivelogfilelist;
SP2-0734:unknowncommandbeginning"archivelo..."-restoflineignored.
sql>archivelist;
SP2-0734:unknowncommandbeginning"archiveli..."-restoflineignored.
sql>archiveloglist
DatabaselogmodeArchiveMode
AutomaticarchivalEnabled
ArchivedestinationUSE_DB_RECOVERY_FILE_DEST
Oldestonlinelogsequence1
Nextlogsequencetoarchive1
Currentlogsequence1

三、实验模拟种类及解决方案

1）丢失部分控制文件，其余控制文件还在

解决方案：一致性关库后，通过copy剩下的控制文件恢复

2）在无备份的情况下丢失了所有的控制文件，但对控制文做了追踪备份

解决方案：通过相应的trace文件，生成脚本，重新创建controlfile

3）在归档模式下，对数据库有完备，丢失全部控制文件

解决方案：通过备份集中的控制文件进行恢复

四、实验过程

1）丢失部分控制文件，其余控制文件还在

查看数据库中控制文件

sql>select'!rm'||namefromv$controlfile;

'!RM'||NAME
-------------------------------------------------------------------------------
!rm/u01/app/oracle/oradata/lzcdb/control01.ctl
!rm/u01/app/oracle/oradata/lzcdb/control02.ctl
!rm/u01/app/oracle/oradata/lzcdb/control03.ctl

删除control02.ctl和control03.ctl控制文件

sql>!rm/u01/app/oracle/oradata/lzcdb/control02.ctl
!rm/u01/app/oracle/oradata/lzcdb/control03.ctl

查看控制文件

sql>!ls/u01/app/oracle/oradata/DG1/
control01.ctlredo01.logredo02.logredo03.logsystem01.dbfundotbs01.dbf
redo01_a.logredo02_a.logredo03_a.logsysaux01.dbftemp01.dbfusers01.dbf

关闭数据库再重新启动数据库（关闭时要执行一致性关闭）

sql>shutdownimmediate;
Databaseclosed.
Databasedismounted.
ORACLEinstanceshutdown.

重新启动数据库

sql>startup
ORACLEinstancestarted.
TotalSystemGlobalArea285212672bytes
FixedSize1218992bytes
VariableSize88082000bytes
DatabaseBuffers192937984bytes
RedoBuffers2973696bytes
ORA-00205:errorinidentifyingcontrolfile,checkalertlogformoreinfo

警告日志信息如下

Wed Jun 13 23:38:18 2012

ALTER DATABASE MOUNT

Wed Jun 13 23:38:18 2012

ORA-00202: control file: '/u01/app/oracle/oradata/DG1/control02.ctl'

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

Wed Jun 13 23:38:18 2012

ORA-205 signalled during: ALTER DATABASE MOUNT在由nomount启动到mount时错误

解决方案：将数据库一致性关闭之后把control01.ctl复制两份，然后改名成删除的控制文件

sql>shutdownimmediate;
ORA-01507:databasenotmounted
ORACLEinstanceshutdown.

sql>!cp/u01/app/oracle/oradata/lzcdb/control01.ctl/u01/app/oracle/oradata/lzcdb/control02.ctl
sql>!cp/u01/app/oracle/oradata/lzcdb/control01.ctl/u01/app/oracle/oradata/lzcdb/control03.ctl

再次重新启动数据库

sql>startup
ORACLEinstancestarted.
TotalSystemGlobalArea285212672bytes
FixedSize1218992bytes
VariableSize83887696bytes
DatabaseBuffers197132288bytes
RedoBuffers2973696bytes
Databasemounted.
DatabaSEOpened.

成功启动！这种恢复控制文件，一定要在控制文件丢失后一致性关闭数据库，这样才能保证恢复出的控制文件课数据文件的SCN一致，打开数据库是不会出错（但如果是在归档模式子，即便不一致性关库，通过此方式也应该能恢复数据库吧！但我还没做实验验证！）。

2）在没有备份的情况下丢失了所有的控制文件，但是对控制文件做了追踪备份

在这里罗嗦一点，注意一定要先将控制文件做追踪备份到trace文件中，才能删除全部控制，否则你删除全部控制文件，数据库必然挂掉了，你怎么可能再去将控制文件做追踪备份到trace文件中呢？千万不要犯这样的低级错误。

将控制文件备份到跟踪文件

sql>alterdatabasebackupcontrolfiletotrace;
Databasealtered.

查看跟踪文件的位置

sql>showparameteruser;
NAMETYPEVALUE
-----------------------------------------------------------------------------
license_max_usersinteger0
parallel_adaptive_multi_userbooleanTRUE
user_dump_deststring/u01/app/oracle/admin/DG1/udump

找到刚刚生成的trace文件，将trace文件中创建控制文件的sql语句读取出来

[oracle@DG1~]$cd/u01/app/oracle/admin/
dataguard1/DG1/
[oracle@DG1~]$cd/u01/app/oracle/admin/DG1/udump/
[oracle@DG1udump]$ls-lrt|tail-1
-rw-r-----1oracleoinstall8397Jun1323:04dg1_ora_30712.trc

查看生成的读取的trace内容

[oracle@DG1udump]$catdg1_ora_30712.trc
/u01/app/oracle/admin/DG1/udump/dg1_ora_30712.trc
OracleDatabase10gEnterpriseEditionRelease10.2.0.1.0-Production
WiththePartitioning,OLAPandDataMiningoptions
ORACLE_HOME=/u01/app/oracle/product/10.2.0.1/db_1
Systemname:Linux
Nodename:DG1
Release:2.6.18-164.el5
Version:#1SMPTueAug1815:51:54EDT2009
Machine:i686
Instancename:DG1
Redothreadmountedbythisinstance:1
Oracleprocessnumber:15
Unixprocesspid:30712,image:oracle@DG1(TNSV1-V3)

***SERVICENAME:(SYS$USERS)2012-06-1323:04:23.363
***SESSIONID:(159.3)2012-06-1323:04:23.363
***2012-06-1323:04:23.363
--ThefollowingarecurrentSystem-scopeREDOLogArchivalrelated
--parametersandcanbeincludedinthedatabaseinitializationfile.
--
--LOG_ARCHIVE_DEST=''
--LOG_ARCHIVE_DUPLEX_DEST=''
--
--LOG_ARCHIVE_FORMAT=%t_%s_%r.dbf
--
--DB_UNIQUE_NAME="DG1"
--
--LOG_ARCHIVE_CONFIG='SEND,RECEIVE,NODG_CONFIG'
--LOG_ARCHIVE_MAX_PROCESSES=2
--STANDBY_FILE_MANAGEMENT=MANUAL
--STANDBY_ARCHIVE_DEST=?/dbs/arch
--FAL_CLIENT=''
--FAL_SERVER=''
--
--LOG_ARCHIVE_DEST_10='LOCATION=USE_DB_RECOVERY_FILE_DEST'
--LOG_ARCHIVE_DEST_10='OPTIONALREOPEN=300NODELAY'
--LOG_ARCHIVE_DEST_10='ARCHNOAFFIRMNOEXPEDITENOVERIFYSYNC'
--LOG_ARCHIVE_DEST_10='REGISTERNOALTERNATENODEPENDENCY'
--LOG_ARCHIVE_DEST_10='NOMAX_FAILURENOQUOTA_SIZENOQUOTA_USEDNODB_UNIQUE_NAME'
--LOG_ARCHIVE_DEST_10='VALID_FOR=(PRIMARY_ROLE,ONLINE_LOGFILES)'
--LOG_ARCHIVE_DEST_STATE_10=ENABLE
--
--Belowaretwosetsofsqlstatements,eachofwhichcreatesanew
--controlfileandusesittoopenthedatabase.Thefirstsetopens
--thedatabasewiththeNORESETLOGSoptionandshouldbeusedonlyif
--thecurrentversionsofallonlinelogsareavailable.Thesecond
--setopensthedatabasewiththeRESETLOGSoptionandshouldbeused
--ifonlinelogsareunavailable.
--Theappropriatesetofstatementscanbecopiedfromthetraceinto
--ascriptfile,editedasnecessary,andexecutedwhenthereisa
--needtore-createthecontrolfile.
--
--Set#1.NORESETLOGScase
--
--Thefollowingcommandswillcreateanewcontrolfileanduseit
--toopenthedatabase.
--DatausedbyRecoveryManagerwillbelost.
--Additionallogsmayberequiredformediarecoveryofoffline
--Usethisonlyifthecurrentversionsofallonlinelogsare
--available.
--Aftermountingthecreatedcontrolfile,thefollowingsql
--statementwillplacethedatabaseintheappropriate
--protectionmode:
--ALTERDATABASESETSTANDBYDATABASETOMAXIMIZEPERFORMANCE
STARTUPNOMOUNT
CREATECONTROLFILEREUSEDATABASE"DG1"NORESETLOGSARCHIVELOG
MAXLOGFILES16
MAXLOGMEMBERS3
MAXDATAFILES100
MAXINSTANCES8
MAXLOGHISTORY292
LOGFILE
GROUP1(
'/u01/app/oracle/oradata/DG1/redo01.log','/u01/app/oracle/oradata/DG1/redo01_a.log'
)SIZE50M,GROUP2(
'/u01/app/oracle/oradata/DG1/redo02.log','/u01/app/oracle/oradata/DG1/redo02_a.log'
)SIZE50M,GROUP3(
'/u01/app/oracle/oradata/DG1/redo03.log','/u01/app/oracle/oradata/DG1/redo03_a.log'
)SIZE50M
--STANDBYLOGFILE
DATAFILE
'/u01/app/oracle/oradata/DG1/system01.dbf','/u01/app/oracle/oradata/DG1/undotbs01.dbf','/u01/app/oracle/oradata/DG1/sysaux01.dbf','/u01/app/oracle/oradata/DG1/users01.dbf','/u01/app/oracle/product/10.2.0.1/db_1/dbs/soe.dbf'
CHARACTERSETUS7ASCII
;
--ConfigureRMANconfigurationrecord1
VARIABLERECNONUMBER;
EXECUTE:RECNO:=SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CHANNEL','DEVICETYPEDISKFORMAT''/home/oracle/DiskBackupLocation/%U''');
--ConfigureRMANconfigurationrecord2
VARIABLERECNONUMBER;
EXECUTE:RECNO:=SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILEAUTOBACKUP','ON');
--ConfigureRMANconfigurationrecord3
VARIABLERECNONUMBER;
EXECUTE:RECNO:=SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILEAUTOBACKUPFORMATFORDEVICETYPE','DISKTO''/home/oracle/DiskBackupLocation/%F''');
--ConfigureRMANconfigurationrecord4
VARIABLERECNONUMBER;
EXECUTE:RECNO:=SYS.DBMS_BACKUP_RESTORE.SETCONFIG('RETENTIONPOLICY','TONONE');
--Commandstore-createincarnationtable
--BelowlognamesMUSTbechangedtoexistingfilenameson
--disk.Anyonelogfilefromeachbranchcanbeusedto
--re-createincarnationrecords.
--ALTERDATABASEREGISTERLOGFILE'/home/oracle/FlashRecovery/DG1/archivelog/2012_06_13/o1_mf_1_1_%u_.arc';
--ALTERDATABASEREGISTERLOGFILE'/home/oracle/FlashRecovery/DG1/archivelog/2012_06_13/o1_mf_1_1_%u_.arc';
--ALTERDATABASEREGISTERLOGFILE'/home/oracle/FlashRecovery/DG1/archivelog/2012_06_13/o1_mf_1_1_%u_.arc';
--Recoveryisrequiredifanyofthedatafilesarerestoredbackups,--orifthelastshutdownwasnotnormalorimmediate.
RECOVERDATABASE
--Alllogsneedarchivingandalogswitchisneeded.
ALTERSYSTEMARCHIVELOGALL;
--Databasecannowbeopenednormally.
ALTERDATABASEOPEN;
--Commandstoaddtempfilestotemporarytablespaces.
--Onlinetempfileshavecompletespaceinformation.
--Othertempfilesmayrequireadjustment.
ALTERTABLESPACETEMPADDTEMPFILE'/u01/app/oracle/oradata/DG1/temp01.dbf'
SIZE419430400REUSEAUTOEXTENDONNEXT655360MAXSIZE32767M;
--Endoftempfileadditions.
--
--Set#2.RESETLOGScase
--
--Thefollowingcommandswillcreateanewcontrolfileanduseit
--toopenthedatabase.
--DatausedbyRecoveryManagerwillbelost.
--Thecontentsofonlinelogswillbelostandallbackupswill
--beinvalidated.Usethisonlyifonlinelogsaredamaged.
--Aftermountingthecreatedcontrolfile,thefollowingsql
--statementwillplacethedatabaseintheappropriate
--protectionmode:
--ALTERDATABASESETSTANDBYDATABASETOMAXIMIZEPERFORMANCE
STARTUPNOMOUNT
CREATECONTROLFILEREUSEDATABASE"DG1"RESETLOGSARCHIVELOG
MAXLOGFILES16
MAXLOGMEMBERS3
MAXDATAFILES100
MAXINSTANCES8
MAXLOGHISTORY292
LOGFILE
GROUP1(
'/u01/app/oracle/oradata/DG1/redo01.log',--orifthelastshutdownwasnotnormalorimmediate.
RECOVERDATABASEUSINGBACKUPCONTROLFILE
--Databasecannowbeopenedzeroingtheonlinelogs.
ALTERDATABASEOPENRESETLOGS;
--Commandstoaddtempfilestotemporarytablespaces.
--Onlinetempfileshavecompletespaceinformation.
--Othertempfilesmayrequireadjustment.
ALTERTABLESPACETEMPADDTEMPFILE'/u01/app/oracle/oradata/DG1/temp01.dbf'
SIZE419430400REUSEAUTOEXTENDONNEXT655360MAXSIZE32767M;
--Endoftempfileadditions.
--

创建用于恢复控制文件的sql脚本

[oracle@DG1udump]$vidg1_ora_30712.trc
：setnu
53,125w!/home/oracle/controlfile_trace1_1.sql

查看用于恢复控制文件的sql脚本

[oracle@DG1~]$ls
10201_database_linux32.zipdatabaseDiskBackupLocation
controlfile_trace1_1.sqlDesktopFlashRecovery

删除所有控制文件

sql>select'!rm'||namefromv$controlfile;
'!RM'||NAME
-------------------------------------------------------------------------------
!rm/u01/app/oracle/oradata/lzcdb/control01.ctl
!rm/u01/app/oracle/oradata/lzcdb/control02.ctl
!rm/u01/app/oracle/oradata/lzcdb/control03.ctl
sql>!rm/u01/app/oracle/oradata/DG1/control01.ctl
!rm/u01/app/oracle/oradata/DG1/control02.ctl
!rm/u01/app/oracle/oradata/DG1/control03.ctl

查看控制文件是否被删除

sql>!ls/u01/app/oracle/oradata/DG1/
redo01_a.logredo02_a.logredo03_a.logsysaux01.dbftemp01.dbfusers01.dbf
redo01.logredo02.logredo03.logsystem01.dbfundotbs01.dbf

关闭数据库

sql>shutdownimmediate;
Databaseclosed.
Databasedismounted.
ORACLEinstanceshutdown.

重新启动

sql>startup
ORACLEinstancestarted.
TotalSystemGlobalArea285212672bytes
FixedSize1218992bytes
VariableSize83887696bytes
DatabaseBuffers197132288bytes
RedoBuffers2973696bytes
ORA-00205:errorinidentifyingcontrolfile,checkalertlogformoreinfo

警告日志出现如下错误

Wed Jun 13 23:20:37 2012

ALTER DATABASE MOUNT

Wed Jun 13 23:20:37 2012

ORA-00202: control file: '/u01/app/oracle/oradata/DG1/control01.ctl'

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

Wed Jun 13 23:20:40 2012

ORA-205 signalled during: ALTER DATABASE MOUNT...

现在解决问题

将实例关闭，执行上面创建的恢复控制文件的脚本controlfile_trace1_1.sql

sql>@controlfile_trace1_1.sql
ORACLEinstancestarted.
TotalSystemGlobalArea285212672bytes
FixedSize1218992bytes
VariableSize83887696bytes
DatabaseBuffers197132288bytes
RedoBuffers2973696bytes
Controlfilecreated.
PL/sqlproceduresuccessfullycompleted.
PL/sqlproceduresuccessfullycompleted.
PL/sqlproceduresuccessfullycompleted.
PL/sqlproceduresuccessfullycompleted.
ORA-00283:recoverysessioncanceledduetoerrors
ORA-00264:norecoveryrequired
ALTERSYSTEMARCHIVELOGALL
*
ERRORatline1:
ORA-00271:therearenologsthatneedarchiving
Databasealtered.
Tablespacealtered.

恢复成功

查看数据库当前状态

sql>selectstatusfromv$instance
STATUS
------------
OPEN

恢复成功！为了确保数据不丢失，我们应该定期将控制文件做追踪备份到trace文件，生成恢复控制文件的sql脚本，以防控制文件全部丢失之后，在没有归档模式下的全备份，我们可以通过这种方法恢复数据库。

3）在归档模式下，对数据库有完备，丢失全部控制文件

注意这里一定要清楚自己备份集中控制文件备份存放的位置，否则当你干掉全部控制文件后，进入RMAN模式是在NOMOUNT状态，此时你无法查用RMAN来看备份集中控制文件的备份位置，更何谈用备份集中的控制文件来恢复干掉的所有控制文件然后再恢复数据库。

先进入RMAN模式查看备份集中控制文件存放的位置

[oracle@DG1~]$rmantargetsys/oracle@DG1
RecoveryManager:Release10.2.0.1.0-ProductiononWedJun1322:09:162012
Copyright(c)1982,2005,Oracle.Allrightsreserved.
connectedtotargetdatabase:DG1(DBID=1762320829)
RMAN>listbackup;
ListofBackupSets
===================
BSKeyTypeLVSizeDeviceTypeElapsedTimeCompletionTime
-------------------------------------------------------------
2Incr03.39GDISK00:08:2213-JUN-12
BPKey:2Status:AVAILABLECompressed:NOTag:BACKUP_DG1_000001_061312022053
PieceName:/home/oracle/DiskBackupLocation/02ndeh2i_1_1
ListofDatafilesinbackupset2
FileLVTypeCkpSCNCkpTimeName
---------------------------------
10Incr67512613-JUN-12/u01/app/oracle/oradata/DG1/system01.dbf
20Incr67512613-JUN-12/u01/app/oracle/oradata/DG1/undotbs01.dbf
30Incr67512613-JUN-12/u01/app/oracle/oradata/DG1/sysaux01.dbf
40Incr67512613-JUN-12/u01/app/oracle/oradata/DG1/users01.dbf
50Incr67512613-JUN-12/u01/app/oracle/product/10.2.0.1/db_1/dbs/soe.dbf
BSKeyTypeLVSizeDeviceTypeElapsedTimeCompletionTime
-------------------------------------------------------------
3Full7.08MDISK00:00:0513-JUN-12
BPKey:3Status:AVAILABLECompressed:NOTag:TAG20120613T142932
PieceName:/home/oracle/DiskBackupLocation/c-1762320829-20120613-00
ControlFileIncluded:CkpSCN:677193Ckptime:13-JUN-12
SPFILEIncluded:Modificationtime:13-JUN-12
BSKeySizeDeviceTypeElapsedTimeCompletionTime
-------------------------------------------------------
41.14GDISK00:03:4713-JUN-12
BPKey:4Status:AVAILABLECompressed:NOTag:BACKUP_DG1_000001_061312022053
PieceName:/home/oracle/DiskBackupLocation/04ndehir_1_1
ListofArchivedLogsinbackupset4
ThrdSeqLowSCNLowTimeNextSCNNextTime
-------------------------------------------------
1652459720-MAY-1252887513-JUN-12
1752887513-JUN-1252887713-JUN-12
1852887713-JUN-1252888013-JUN-12
1952888013-JUN-1252911513-JUN-12
11052911513-JUN-1252911713-JUN-12
11152911713-JUN-1252912013-JUN-12
11252912013-JUN-1252912913-JUN-12
11352912913-JUN-1252913113-JUN-12
11452913113-JUN-1252913413-JUN-12
11552913413-JUN-1252913613-JUN-12
11652913613-JUN-1252913813-JUN-12
11752913813-JUN-1252914013-JUN-12
11852914013-JUN-1252914213-JUN-12
11952914213-JUN-1252914413-JUN-12
12052914413-JUN-1252915413-JUN-12
12152915413-JUN-1252915613-JUN-12
12252915613-JUN-1252915813-JUN-12
12352915813-JUN-1252916013-JUN-12
12452916013-JUN-1252916213-JUN-12
12552916213-JUN-1252916413-JUN-12
12652916413-JUN-1252943113-JUN-12
12752943113-JUN-1252943313-JUN-12
12852943313-JUN-1252943613-JUN-12
12952943613-JUN-1252943813-JUN-12
13052943813-JUN-1252944013-JUN-12
13152944013-JUN-1252944213-JUN-12
13252944213-JUN-1252944413-JUN-12
13352944413-JUN-1252944613-JUN-12
13452944613-JUN-1252944813-JUN-12
13552944813-JUN-1252945013-JUN-12
13652945013-JUN-1252945213-JUN-12
13752945213-JUN-1252945413-JUN-12
13852945413-JUN-1252945613-JUN-12
13952945613-JUN-1252945813-JUN-12
14052945813-JUN-1252946013-JUN-12
14152946013-JUN-1252946213-JUN-12
14252946213-JUN-1252946413-JUN-12
14352946413-JUN-1252946613-JUN-12
14452946613-JUN-1252946813-JUN-12
14552946813-JUN-1252947013-JUN-12
14652947013-JUN-1253464513-JUN-12
14753464513-JUN-1253905613-JUN-12
14853905613-JUN-1254350513-JUN-12
14954350513-JUN-1254789713-JUN-12
15054789713-JUN-1255231013-JUN-12
15155231013-JUN-1255668813-JUN-12
15255668813-JUN-1256108413-JUN-12
15356108413-JUN-1256547313-JUN-12
15456547313-JUN-1256985413-JUN-12
15556985413-JUN-1257430213-JUN-12
15657430213-JUN-1257986013-JUN-12
15757986013-JUN-1258608913-JUN-12
15858608913-JUN-1259223313-JUN-12
15959223313-JUN-1259839113-JUN-12
16059839113-JUN-1260455313-JUN-12
16160455313-JUN-1261070413-JUN-12
16261070413-JUN-1261684013-JUN-12
16361684013-JUN-1262369613-JUN-12
16462369613-JUN-1263115913-JUN-12
16563115913-JUN-1263799313-JUN-12
16663799313-JUN-1264441813-JUN-12
16764441813-JUN-1265077513-JUN-12
16865077513-JUN-1265981013-JUN-12
16965981013-JUN-1267728513-JUN-12
BSKeyTypeLVSizeDeviceTypeElapsedTimeCompletionTime
-------------------------------------------------------------
5Full7.08MDISK00:00:0313-JUN-12
BPKey:5Status:AVAILABLECompressed:NOTag:TAG20120613T143358
PieceName:/home/oracle/DiskBackupLocation/c-1762320829-20120613-01
ControlFileIncluded:CkpSCN:679052Ckptime:13-JUN-12
SPFILEIncluded:Modificationtime:13-JUN-12

可以看出上面红色部分BP Key 5是备份集中最新控制文件的存放位置，我们记住这个目录，稍后恢复控制文件时要使用。

查看控制文件

sql>select'!rm'||namefromv$controlfile;
'!RM'||NAME
-------------------------------------------------------------------------------
!rm/u01/app/oracle/oradata/DG1/control01.ctl
!rm/u01/app/oracle/oradata/DG1/control02.ctl
!rm/u01/app/oracle/oradata/DG1/control03.ctl

将控制文件全部删除

sql>!rm/u01/app/oracle/oradata/DG1/control01.ctl
!rm/u01/app/oracle/oradata/DG1/control02.ctl
!rm/u01/app/oracle/oradata/DG1/control03.ctl

查看控制文件是否还存在

sql>!ls/u01/app/oracle/oradata/DG1
redo01_a.logredo02.logsysaux01.dbfundotbs01.dbf
redo01.logredo03_a.logsystem01.dbfusers01.dbf
redo02_a.logredo03.logtemp01.dbf

将数据库关闭，然后重新启动

sql>shutdownimmediate;
ORA-00210:cannotopenthespecifiedcontrolfile
ORA-00202:controlfile:'/u01/app/oracle/oradata/DG1/control01.ctl'
ORA-27041:unabletoopenfile
LinuxError:2:Nosuchfileordirectory
Additionalinformation:3

竟然正常关闭不了（之前做测试都能正常关闭），直接强制关闭

sql>shutdownabort;
ORACLEinstanceshutdown.

重新启动数据库

sql>startup
ORACLEinstancestarted.
TotalSystemGlobalArea285212672bytes
FixedSize1218992bytes
VariableSize88082000bytes
DatabaseBuffers192937984bytes
RedoBuffers2973696bytes
ORA-00205:errorinidentifyingcontrolfile,checkalertlogformoreinfo

出现报错信息，不能识别控制文件，这是肯定的，由于缺少控制文件，数据库在由nomount状态启动到mount状态时要读取控制文件中的内容，控制文件都木有了，怎么能启动到mount，还别说open了，所以在数据库只能启动到nomount状态。由于我们有归档模式下的RMAN全备，所以我们可以借助RMAN备份集来恢复参数文件。

sql>selectstatusfromv$instance;
STATUS
------------
STARTED

进入RMAN模式

[oracle@DG1~]$rmantargetsys/oracle@DG1
RecoveryManager:Release10.2.0.1.0-ProductiononWedJun1322:01:192012
Copyright(c)1982,Oracle.Allrightsreserved.
connectedtotargetdatabase:DG1(notmounted)

现在从RMAN完备数据库的备份集中进行控制文件的恢复

RMAN>restorecontrolfilefrom'/home/oracle/DiskBackupLocation/c-1762320829-20120613-01';
Startingrestoreat13-JUN-12
usingtargetdatabasecontrolfileinsteadofrecoverycatalog
allocatedchannel:ORA_DISK_1
channelORA_DISK_1:sid=155devtype=DISK
channelORA_DISK_1:restoringcontrolfile
channelORA_DISK_1:restorecomplete,elapsedtime:00:00:06
outputfilename=/u01/app/oracle/oradata/DG1/control01.ctl
outputfilename=/u01/app/oracle/oradata/DG1/control02.ctl
outputfilename=/u01/app/oracle/oradata/DG1/control03.ctl
Finishedrestoreat13-JUN-12

可以看到控制文件已经由全备份集中的控制文件成功恢复

恢复控制完毕，查看控制文件

[oracle@DG1~]$ls/u01/app/oracle/oradata/DG1/
control01.ctlredo01_a.logredo02.logsysaux01.dbfundotbs01.dbf
control02.ctlredo01.logredo03_a.logsystem01.dbfusers01.dbf
control03.ctlredo02_a.logredo03.logtemp01.dbf

将数据库启动到mount状态恢复数据库

RMAN>alterdatabasemount;
databasemounted
releasedchannel:ORA_DISK_1

成功启动到mount状态

执行RMAN恢复数据库的操作

RMAN>recoverdatabase;
Startingrecoverat13-JUN-12
Startingimplicitcrosscheckbackupat13-JUN-12
allocatedchannel:ORA_DISK_1
channelORA_DISK_1:sid=155devtype=DISK
Crosschecked3objects
Finishedimplicitcrosscheckbackupat13-JUN-12
Startingimplicitcrosscheckcopyat13-JUN-12
usingchannelORA_DISK_1
Finishedimplicitcrosscheckcopyat13-JUN-12
searchingforallfilesintherecoveryarea
catalogingfiles...
nofilescataloged
usingchannelORA_DISK_1
startingmediarecovery
archivelogthread1sequence70isalreadyondiskasfile/u01/app/oracle/oradata/DG1/redo03.log
archivelogthread1sequence71isalreadyondiskasfile/u01/app/oracle/oradata/DG1/redo01.log
archivelogfilename=/u01/app/oracle/oradata/DG1/redo03.logthread=1sequence=70
archivelogfilename=/u01/app/oracle/oradata/DG1/redo01.logthread=1sequence=71
mediarecoverycomplete,elapsedtime:00:00:01
Finishedrecoverat13-JUN-12

数据库恢复成功

打开数据库

RMAN>alterdatabaSEOpenresetlogs;
databaSEOpened
RMAN>

成功恢复

由于数据库一直处于归档模式，所以从归档下的完备份集恢复数据库，可以保证数据不丢失，当由备份集恢复控制文件之后，接着会从数据库的归档日志和undo日志文件中读取SCN信息，将恢复的控制文件更新到最新状态。

五、总结

没有不丢失数据的数据库，所以经常备份数据，是很有必要的，也许某一天数据库就挂掉了，如果有备份，恢复当然容易，如果没有，也许结果就不一样了………无论你的数据库是以何种方式恢复的，一定切记恢复后，做一次归档下的全备，这样就可以尽可能降低恢复数据库后数据库再次挂掉后丢失数据的可能性。


原文来自：http://blog.sina.com.cn/s/blog_70e5638f01016s8b.html

猜你在找的Oracle相关文章

[Oracle]记一次由sequence引发的enq sv-contention等待事件
数据库版本：11.2.0.4 RAC(1)问题现象从EM里面可以看到，在23号早上8：45~8：55时，数据库...
oracle统计信息的锁定与解锁
（一）问题背景最近在对一个大约200万行数据的表查看执行计划时，发现存在异常，理论上应该...
【转】RMAN删除过期备份或非过期备份
（一）删除备份--DELETE命令用于删除RMAN备份记录及相应的物理文件。当使用RMAN执行备份操...
数据恢复顾问（DRA）
（1）DRA介绍 数据恢复顾问（Data Recovery Advise）是一个诊断和修复数据库的工具，DRA能...
RMAN备份与恢复（一）--认识RMAN
RMAN（Recovery Manager）是Oracle恢复管理器的简称，是集数据库备份（backup）、修复（re...
RMAN备份与恢复（二）--RMAN常用操作学习
（1）连接目标数据库 在RMAN中可以建立与目标数据库或恢复目录数据库的连接。与目标数据库...
ORA-00245问题总结
（1）问题描述 在进行数据库归档备份时（备份归档日志文件和控制文件），有时成功，有时失...
使用RMAN对数据文件进行恢复
（1）备份数据库 在使用RMAN进行数据库恢复之前，先用RMAN进行全库备份 （2）删除数据文件...
Oracle使用fy_recover_data恢复truncate删除的数据
(一)truncate操作概述在生产中，truncate是使用较多的命令，在使用不当的情况下，往往会造...
备份与恢复（四）--RMAN使用恢复目录catalog来保存档案资料库
（一）恢复目录概述RMAN档案资料库用于存储数据库备份、修复以及恢复所需的信息。这些信息...
MySQLMsSQLOracleSqlitePostgre SQLMariadbMongoDBNoSQLHBaseJDBC
undo日志persistent-cmysql-error-postal-codesql-match-almysql-5.6mysql-8.0database-tri安装路径系统错误data_dir丢失文件主从同步sql_mode数据库目录匿名用户character_seID归零数据库位置查询表重复字段查询字段截断日志SUSPECT7391Remote ServeLinked Serve玄学问题登录不上开启远程访问