在這裡我們將介紹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也遇到這樣的問題。
