在这里我们将介绍Oracle下利用RMAN恢复数据库的相关操作， 数据库频道向您推荐《Oracle 11g新特性与应用详解 》。
数据文件丢失, 没有备份, 拥有文件创建以来的全部归档,使用RMAN恢复, 报错RMAN-06102: no channel to restore a backup or copy of log thread 1 seq 726 scn 1757142927; 使用sqlplus恢复, 执行 ‘Alter Database recover datafile ‘ Fails with ORA-279
总结: RMAN备份没有使用catalog, controlfile默认保留7天的备份/归档信息,v$archived_log没有记录足够多的归档信息, 所以报RMAN-06102, 需要通过CATALOG命令注册.
SQLPLUS 执行 ‘Alter Database recover datafile ‘ Fails with ORA-279 因为9.2.0.6版本使用这个命令不能自动执行recover. 用户版本9.2.0.1也遇到同样的问题, 使用RECOVER DATAFILE即可. 处理步骤:
1. 生成controlfile 备份到文本ora9roro_ora_479268.trc
2. 查询该文件创建时间为Feb 21 11:10:24 2006 (查询alert.log)
TueFeb2111:10:242006 CREATETABLESPACEtzglDATAFILE'tzgl'SIZE100M TueFeb2111:10:292006 Completed:CREATETABLESPACEtzglDATAFILE'tzgl'SIZE100M TueFeb2121:58:232006 Thread1advancedtologsequence277 Currentlog#3seq#277mem#0:/oradata/ora9roro/redo03.log TueFeb2121:58:232006
3. 用户说没有备份(没有确实检查是否有备份),创建空文件,因为有全部的归档文件存在,考虑建空文件, 使用归档文件恢复.
alterdatabasecreatedatafile'/oracle/product/9.2.0/dbs/tzgl'as'/oracle/product/9.2.0/dbs/tzgl';
或者就写alter database create datafile 12 as ‘/oracle/product/9.2.0/dbs/tzgl’; 该文件file#=12.
4.rman target /
rman>recoverdatafile12;失败 archivelogthread1sequence1116isalreadyondiskasfile/oradata/ora9roro/archive/1_1116.dbf archivelogthread1sequence1117isalreadyondiskasfile/oradata/ora9roro/archive/1_1117.dbf archivelogthread1sequence1118isalreadyondiskasfile/oradata/ora9roro/archive/1_1118.dbf ... RMAN-06102:nochanneltorestoreabackuporcopyoflogthread1seq728scn1757357012 RMAN-06102:nochanneltorestoreabackuporcopyoflogthread1seq727scn1757357009 RMAN-06102:nochanneltorestoreabackuporcopyoflogthread1seq726scn1757142927
5. 检查归档文件是否存在, 发现从1_1.dbf直到现在, 所有的ARCHIVELOGS都存在.但RMAN-06102错误信息表明controlfile并没有记录下所有的归档信息.查看参数control_file_record_keep_time 为默认值7. 所以只保留7天的备份信息.
调整control_file_record_keep_time=365以保证以后的备份可以保留更长的时间.
6. 企图注册归档文件到controlfile. 但不支持, 只针对standby controlfile.
alterdatabaseregisterlogfile'/oradata/ora9roro/archive/1_726.dbf'; ERRORatline1: ORA-01665:controlfileisnotastandbycontrolfile
7. 尝试通过sqlplus 恢复, 也失败. —但这里并没有查看原因, 想当然认为rman恢复不行,sqlplus 执行也不行. 在step 16找到了原因.
SQL>conn/assysdba Connected. SQL>alterdatabaserecoverdatafile12; alterdatabaserecoverdatafile12 * ERRORatline1: ORA-00279:change1181419363generatedat02/21/200611:10:29neededforthread 1 ORA-00289:suggestion:/oradata/ora9roro/archive/1_276.dbf ORA-00280:change1181419363forthread1isinsequence#276
8. 验证问题是否出在归档文件, 尝试移动/oradata/ora9roro/archive/1_276.dbf到其他目录, 然后进行恢复alter database recover datafile 12;
错误同上.所以, 不是归档文件本身故障的问题. 继续查无法读取归档文件的原因.
9. 查看是否重建过controlfile或open resetlogs操作.
从2006年2月21日开始，有没有做过重建controlfile或open resetlogs之类的操作？
selectRESETLOGS_TIMEfromv$database; RESETLOGS ---------
16-APR-05
结果表示, 没有resetlog过, 也没有重建过controlfile
10.查看v$bakcup_piece, v$backup_datafile发现有记录, 是2006年12月29日的备份记录,备份到带库.
原来并非像用户所说没有备份
root身份查看 #crontab -l找到备份,每天执行,可是查看/tmp/bkdb_$dt.log文件, 最后一次成功备份是bkdb_200612250100.log
01***/usr/tivoli/tsm/client/oracle/orabackup.sh>/dev/null#
#cat/usr/tivoli/tsm/client/oracle/orabackup.sh
exportdt=`date+%Y%m%d%H%M`
su-oracle-c"rmantarget/nocatalogcmdfile/oracle/sched/bkdb.scrmsglog/tmp/bkdb_$dt.log"
/oracle/sched/bkdb.scr备份脚本内容:bkdb_200612290100.log
RUN{
ALLOCATECHANNELch00TYPE'SBT_TAPE'parms=
'ENV=(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)';
BACKUP
FORMAT'df_T%T_s%s_p%p_t%t'
FILESPERSET2
DATABASE;
RELEASECHANNELch00;
}
11. 尝试恢复:
RMAN>RUN{
ALLOCATECHANNELch00TYPE'SBT_TAPE'parms=
'EN2>3>V=(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)';
4>restoredatafile12;
5>releasechannelch00
6>;
7>}
usingtargetdatabasecontrolfileinsteadofrecoverycatalog
allocatedchannel:ch00
channelch00:sid=11devtype=SBT_TAPE
channelch00:TivoliDataProtectionforOracle:version5.2.0.0
Startingrestoreat07-APR-08
channelch00:startingdatafilebackupsetrestore
channelch00:specifyingdatafile(s)torestorefrombackupset
restoringdatafile00012to/oracle/product/9.2.0/dbs/tzgl
releasedchannel:ch00
RMAN-00571:===========================================================
RMAN-00569:===============ERRORMESSAGESTACKFOLLOWS===============
RMAN-00571:===========================================================
RMAN-03002:failureofrestorecommandat04/07/200814:20:09
ORA-19501:readerroronfile"df_T20061229_s2660_p1_t610419667",blockno1(blocksize=512)
ORA-27190:skgfrd:sbtread2returnederror
ORA-19511:Errorreceivedfrommediamanagerlayer,errortext:
ANS1314E(RC14)Filedatacurrentlyunavailableonserver
12. 带库有问题, 或者数据已经被覆写. 所以, 不再考虑使用备份做恢复.
13. 查看v$recover_file, v$recovery_log, v$log_history,看系统状态,信息存储在recover.lst中
setpagesize20000 setlinesize180 setpauseoff setserveroutputon setfeedbackon setechoon setnumformat999999999999999 Spoolrecover.lst selectsubstr(name,1,50),statusfromv$datafile; selectfile#,substr(name,1,50),recover,fuzzy,to_char(checkpoint_time,'dd/mm/yyyy:hh24:mi:ss')ckpt_time,checkpo int_change#,resetlogs_change#,to_char(resetlogs_time,'dd/mm/yyyyHH24:MI:SS') tmfromv$datafile_header; select*fromv$backup; selectname,open_mode,checkpoint_change#,ARCHIVE_CHANGE#fromv$database; selectGROUP#,THREAD#,SEQUENCE#,MEMBERS,ARCHIVED,STATUS,FIRST_CHANGE#fromv$log; selectGROUP#,substr(member,1,60)fromv$logfile; select*fromv$log_history; select*fromv$recover_file; select*fromv$recovery_log; selectHXFILFile_num,substr(HXFNM,1,40)File_name,FHTYPType,HXERRValidity, FHSCNSCN,FHTNMTABLESPACE_NAME,FHSTAstatus,FHRBA_SEQSequence fromX$KCVFH; spooloff
14恢复方案:
1.alterdatabasecreatedatafile12as'/oracle/product/9.2.0/dbs/tzgl';----ÒѾ×öÁËinstep3 2.recoverdatafile12; 3.nowstartapplyingarchivesfromthetimeofcreationofdatafile. 4.oncethearchivesareappliedandredologsareapplied,youcanissuethecommand,"alterdatabaseopen".
15. 注册归档文件到controlfile (和catalog db 可选)
rmantarget/ RMAN>listarchivelogall;--[1_1116.dbf,1_1164.dbf] RMAN>CATALOGARCHIVELOG'/oradata/ora9roro/archive/1_1.dbf';---添加到v$archived_log RMAN>listarchivelogall;--多显示1_1.dbf记录 KeyThrdSeqSLowTimeName -------------------------------- 116511A16-APR-05/oradata/ora9roro/archive/1_1.dbf
16. 在step7中登陆sqlplus恢复失败, 查原因:
metalink<352617.1>'AlterDatabaserecoverdatafile'FailswithORA-279 Noticabledifferencebetween9.2.0.6andotherversionsisthatthe"recoverdatafile"syntaxdoesnotauotmatically performautorecover,Oraclepromptsforeachlog.Onotherversionsthisisnotthecase. ThisisreportedasBug:4178579-ALTERDATABASERECOVERDATAFILEISBROKENIN9.2.0.6
解决方法:
Usedifferentsyntax: recoverdatafilex recoverautomaticdatafilex alterdatabaserecoverautomaticdatafilex
或者打补丁:Apply 9.2.0.7.0 patchset 不过, 在网上看到有人在10.2.0.2也遇到这样的问题。
