在这里我们将介绍Oracle下利用RMAN恢复数据库的相关操作，51CTO数据库频道向您推荐《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)
- Tue Feb 21 11:10:24 2006
- CREATE TABLESPACE tzgl DATAFILE 'tzgl' SIZE 100M
- Tue Feb 21 11:10:29 2006
- Completed: CREATE TABLESPACE tzgl DATAFILE 'tzgl' SIZE 100M
- Tue Feb 21 21:58:23 2006
- Thread 1 advanced to log sequence 277
- Current log# 3 seq# 277 mem# 0: /oradata/ora9roro/redo03.log
- Tue Feb 21 21:58:23 2006
3. 用户说没有备份(没有确实检查是否有备份),创建空文件,因为有全部的归档文件存在,考虑建空文件, 使用归档文件恢复.
- alter database create datafile '/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> recover datafile 12; 失败
- archive log thread 1 sequence 1116 is already on disk as file /oradata/ora9roro/archive/1_1116.dbf
- archive log thread 1 sequence 1117 is already on disk as file /oradata/ora9roro/archive/1_1117.dbf
- archive log thread 1 sequence 1118 is already on disk as file /oradata/ora9roro/archive/1_1118.dbf
- ...
- RMAN-06102: no channel to restore a backup or copy of log thread 1 seq 728 scn 1757357012
- RMAN-06102: no channel to restore a backup or copy of log thread 1 seq 727 scn 1757357009
- RMAN-06102: no channel to restore a backup or copy of log thread 1 seq 726 scn 1757142927
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.
- alter database register logfile '/oradata/ora9roro/archive/1_726.dbf';
- ERROR at line 1:
- ORA-01665: controlfile is not a standby controlfile
7. 尝试通过sqlplus 恢复, 也失败. ---但这里并没有查看原因, 想当然认为rman恢复不行,sqlplus 执行也不行. 在step 16找到了原因.
- SQL> conn / as sysdba
- Connected.
- SQL> alter database recover datafile 12;
- alter database recover datafile 12
- *
- ERROR at line 1:
- ORA-00279: change 1181419363 generated at 02/21/2006 11:10:29 needed for thread
- 1
- ORA-00289: suggestion : /oradata/ora9roro/archive/1_276.dbf
- ORA-00280: change 1181419363 for thread 1 is in sequence #276
8. 验证问题是否出在归档文件, 尝试移动/oradata/ora9roro/archive/1_276.dbf到其他目录, 然后进行恢复alter database recover datafile 12;
错误同上.所以, 不是归档文件本身故障的问题. 继续查无法读取归档文件的原因.
9. 查看是否重建过controlfile或open resetlogs操作.
从2006年2月21日开始，有没有做过重建controlfile或open resetlogs之类的操作？
- select RESETLOGS_TIME from v$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
- 0 1 * * * /usr/tivoli/tsm/client/oracle/orabackup.sh >/dev/null#
- # cat /usr/tivoli/tsm/client/oracle/orabackup.sh
- export dt=`date +%Y%m%d%H%M`
- su - oracle -c "rman target / nocatalog cmdfile /oracle/sched/bkdb.scr msglog /tmp/bkdb_$dt.log"
- /oracle/sched/bkdb.scr备份脚本内容: bkdb_200612290100.log
- RUN{
- ALLOCATE CHANNEL ch00 TYPE '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'
- FILESPERSET 2
- DATABASE;
- RELEASE CHANNEL ch00;
- }
11. 尝试恢复:
- RMAN> RUN{
- ALLOCATE CHANNEL ch00 TYPE 'SBT_TAPE' parms =
- 'EN2> 3> V=(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)';
- 4> restore datafile 12;
- 5> release channel ch00
- 6> ;
- 7> }
- using target database controlfile instead of recovery catalog
- allocated channel: ch00
- channel ch00: sid=11 devtype=SBT_TAPE
- channel ch00: Tivoli Data Protection for Oracle: version 5.2.0.0
- Starting restore at 07-APR-08
- channel ch00: starting datafile backupset restore
- channel ch00: specifying datafile(s) to restore from backup set
- restoring datafile 00012 to /oracle/product/9.2.0/dbs/tzgl
- released channel: ch00
- RMAN-00571: ===========================================================
- RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
- RMAN-00571: ===========================================================
- RMAN-03002: failure of restore command at 04/07/2008 14:20:09
- ORA-19501: read error on file "df_T20061229_s2660_p1_t610419667", blockno 1 (blocksize=512)
- ORA-27190: skgfrd: sbtread2 returned error
- ORA-19511: Error received from media manager layer, error text:
- ANS1314E (RC14) File data currently unavailable on server
12. 带库有问题, 或者数据已经被覆写. 所以, 不再考虑使用备份做恢复.
13. 查看v$recover_file, v$recovery_log, v$log_history,看系统状态,信息存储在recover.lst中
- set pagesize 20000
- set linesize 180
- set pause off
- set serveroutput on
- set feedback on
- set echo on
- set numformat 999999999999999
- Spool recover.lst
- select substr(name, 1, 50), status from v$datafile;
- select file#,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/yyyy HH24:MI:SS')
- tm from v$datafile_header;
- select * from v$backup;
- select name, open_mode, checkpoint_change#, ARCHIVE_CHANGE# from v$database;
- select GROUP#,THREAD#,SEQUENCE#,MEMBERS,ARCHIVED,STATUS,FIRST_CHANGE# from v$log;
- select GROUP#,substr(member,1,60) from v$logfile;
- select * from v$log_history;
- select * from v$recover_file;
- select * from v$recovery_log;
- select HXFIL File_num,substr(HXFNM,1,40) File_name,FHTYP Type,HXERR Validity,
- FHSCN SCN, FHTNM TABLESPACE_NAME,FHSTA status ,FHRBA_SEQ Sequence
- from X$KCVFH;
- spool off
14恢复方案:
- 1. alter database create datafile 12 as '/oracle/product/9.2.0/dbs/tzgl'; ----ÒѾ×öÁËin step3
- 2. recover datafile 12;
- 3. now start applying archives from the time of creation of datafile.
- 4. once the archives are applied and redo logs are applied, you can issue the command, "alter database open".
15. 注册归档文件到controlfile (和catalog db 可选)
- rman target /
- RMAN> list archivelog all; --[1_1116.dbf, 1_1164.dbf]
- RMAN> CATALOG ARCHIVELOG '/oradata/ora9roro/archive/1_1.dbf'; ---添加到v$archived_log
- RMAN> list archivelog all; --多显示1_1.dbf记录
- Key Thrd Seq S Low Time Name
- ------- ---- ------- - --------- ----
- 1165 1 1 A 16-APR-05 /oradata/ora9roro/archive/1_1.dbf
16. 在step7中登陆sqlplus恢复失败, 查原因:
- metalink<352617.1> 'Alter Database recover datafile ' Fails with ORA-279
- Noticable difference between 9.2.0.6 and other versions is that the "recover datafile" syntax does not auotmatically
- perform auto recover, Oracle prompts for each log. On other versions this is not the case.
- This is reported as Bug: 4178579 - ALTER DATABASE RECOVER DATAFILE IS BROKEN IN 9.2.0.6
解决方法:
- Use different syntax:
- recover datafile x
- recover automatic datafile x
- alter database recover automatic datafile x
或者打补丁:Apply 9.2.0.7.0 patchset
不过, 在网上看到有人在10.2.0.2也遇到这样的问题。
【编辑推荐】
