差點丟失資料的RMAN 恢復，最後用隱藏引數開啟
差點丟失資料的RMAN 恢復，最後用隱藏引數開啟。
Oracle Error:
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/u01/oracle/oradata/dbc/data1/system01.dbf'
RMAN-00571: ===============================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS
RMAN-00571: =================================================
RMAN-03002: failure of recover command at 04/02/2009 06:18:16
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of log thread 1 seq 35332 lowscn 6103122088730 found to restore
差點丟失資料的RMAN 恢復，最後用隱藏引數開啟。
Problem Description:
succeful backup,but unable to recover ,with errors below
---------------------------------------------------------------------------------
archive log thread 1 sequence 35334 is already on disk as file /u01/oracle/oradata/dbc/data4/arch/1_35334.arc
Oracle Error:
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/u01/oracle/oradata/dbc/data1/system01.dbf'
RMAN-00571: ===============================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS
RMAN-00571: =================================================
RMAN-03002: failure of recover command at 04/02/2009 06:18:16
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of log thread 1 seq 35332 lowscn 6103122088730 found to restore
### Detailed Problem Statement ###
we preform a successful backup as the log we attached.we change the storage and tried to restore the backup,seq 35333 is the first archive log when we perform backup,and 35334 is the last archivelog.and after the backup successful,I runed "alter system archivelog current" from plsql and shutdown the datbase,so it comes an archive log 35335.we successful run " restore database" with no error,then it occres the errors below when run "recover database"
archive log thread 1 sequence 35334 is already on disk as file /u01/oracle/oradata/dbc/data4/arch/1_35334.arc
Oracle Error:
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/u01/oracle/oradata/dbc/data1/system01.dbf'
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 04/02/2009 06:18:16
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of log thread 1 seq 35332 lowscn 6103122088730 found to restore
RMAN-06025: no backup of log thread 1 seq 35331 lowscn 6103119560491 found to restore
.....many more old logs required嘗試restore歸檔回來
restore archivelog from logseq 35279 until logseq 35332;
it says mismatch logslist backup find the archivelogs backup below avaible:
-rw-r----- 1 oracle oinstall 2465792 Apr 1 21:15 vckbdj6g_1_1_22508.arc
-rw-r----- 1 oracle oinstall 26793472 Apr 1 22:51 vskbdoqh_1_1_22524.arc
resoter archivelog all and failed.
use the comand and succefull restore the archivelog
DECLARE
devtype varchar2(256);
done boolean;
BEGIN
devtype:=sys.dbms_backup_restore.deviceAllocate(type=>'',ident=>'T1');
sys.dbms_backup_restore.restoreSetArchivedLog;
sys.dbms_backup_restore.restoreArchivedLogRange;
sys.dbms_backup_restore.restoreBackupPiece(done=>done,handle=>'/nasbak_dbc/rman/dev_dbc.dev-dbc/vskbdoqh_1_1_22524.arc',params=>null);
sys.dbms_backup_restore.deviceDeallocate;
END;
/開始恢復歸檔
recover database using backup controlfile ;
it required 35334,type auto
it required 35335,copy my manul back archivelog 35335 to the directory and type auto
it required 35556, type cancel;
open resetlogs
reported:
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/u01/oracle/oradata/dbc/data1/system01.dbf'
we thought something wrong with the controlfile so we backup controlfile to trace and created the new controlfile,
open reset logs ,and the same error occured again.
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/u01/oracle/oradata/dbc/data1/system01.dbf'
we tried on a new mechine and try to restore also occure the error.
at last ,we use
_allow_terminal_recovery_corruption _allow_resetlogs_corruption on the pfile and succefully open the database.
事後跟蹤原因：
1、看看所有檔案的check_time
SQL> select file#,substr(name,1,50), substr(recover,1,1) R, substr(fuzzy,1,1) F,
2 to_char(checkpoint_time,'dd/mm/yyyy:hh24:mi:ss') ckpt_time,
3 checkpoint_change#, resetlogs_change#,
4 to_char(resetlogs_time,'dd/mm/yyyy HH24:MI:SS') tm from v$datafile_header;
71 /u01/oracle/oradata/dbc/data1/test__1.dbf N 30/03/2009:21:28:44 6102273276922 21739661701
01/12/2005 11:35:45
72 /u01/oracle/oradata/dbc/data1/tbs_03.dbf Y 09/08/2006:16:26:47 42315919273 21739661701
01/12/2005 11:35:45
73 /u01/oracle/oradata/dbc/data1/tbs__2.dbf N 30/03/2009:21:28:44 6102273276922 21739661701
01/12/2005 11:35:45
76 /u01/oracle/oradata/dbc/data2/tbs__12.dbf N 30/03/2009:21:28:44 6102273276922 21739661701
2、看看status
select file#,substr(name, 1, 50), status,enabled,to_char(creation_time,'dd/mm/yyyy:hh24:mi:ss') from v$datafile;
/u01/oracle/oradata/dbc/data2/tbs__11.dbf ONLINE READ WRITE 11/05/2006:09:45:51
71 /u01/oracle/oradata/dbc/data1/test_1.dbf ONLINE READ WRITE 22/07/2006:11:02:56
72 /u01/oracle/oradata/dbc/data1/tbs_03.dbf RECOVER READ WRITE 09/08/2006:16:26:46
73 /u01/oracle/oradata/dbc/data1/tbs__2.dbf ONLINE READ WRITE 11/09/2006:17:50:43
76 /u01/oracle/oradata/dbc/data2/tbs__12.dbf ONLINE READ WRITE 08/12/2006:09:36:08
and because of this datafile, you are getting the error.
+ You need to choose from any of the following options :
1. You need to recover this datafile till current time and bring it online and then database can be opened successfully
2. Take this datafile offline drop, open up the database and then drop the tablespace which contains this datafile. You can choose to export the data from other datafiles of the tablespace.
SQL> alter database datafile 72 offline drop;
Database altered.
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/u01/oracle/oradata/dbc/data1/system01.dbf'
SQL> recover database using backup controlfile until cancel;
如果只是用recover database using backup controlfile，還是會出現同樣的錯打不開的。
ORA-00279: change 6102273276922 generated at 03/30/2009 21:28:44 needed for
thread 1
ORA-00289: suggestion : /u01/oracle/oradata/dbc/data4/arch/1_35297.arc
ORA-00280: change 6102273276922 for thread 1 is in sequence #35297
Specify log: {
CANCEL
Media recovery cancelled.
SQL> alter database open resetlogs;
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
這裡是因為我用10.2.0.4的資料庫去開啟9.2.0.6的DB。
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@dev_dbc oracle]$ dbasql
-bash: dbasql: command not found
[oracle@dev_dbc oracle]$ sqlplus " / as sysdba"
SQL*Plus: Release 10.2.0.4.0 - Production on Fri Apr 10 13:30:25 2009
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to an idle instance.
SQL> startup upgrade pfile='/home/oracle/init.ora';
ORACLE instance started.
Total System Global Area 2785017856 bytes
Fixed Size 1265888 bytes
Variable Size 310644512 bytes
Database Buffers 2457600000 bytes
Redo Buffers 15507456 bytes
Database mounted.
Database opened.
用到的語句附錄：
如何知道資料庫是否可以開啟？
+ Following queries can be used to determine if the database can be opened or not. The output of the queries should be as mentioned :
select distinct status from v$datafile; --> should return 2 row and value will be SYSTEM and ONLINE
select distinct (to_char(checkpoint_change#)) from v$datafile; --> should return 1 row
select distinct (to_char(checkpoint_change#)) from v$datafile_header; --> should return 1 row and same value as above
select distinct (status) from v$backup; --> should return 1 row and value should be "NOT ACTIVE"
select distinct (fuzzy) from v$datafile_header; --> should return 1 row and value should be "NO"
+ If all the output of above 5 queries is as mentioned, then the database can be opened else the database opening can fail with errors.
+ If the database has been opened, the file header, based on which recovery is done and Oracle asks for archives for further recovery, would have changed and so looking at the current file header, it is not possible to say why recovery required those archives.
出現restore後打不開，查詢下面的語句，去找出原因。
set pagesize 20000
set linesize 180
set pause off
set serveroutput on
set feedback on
set echo on
set numformat 999999999999999
alter session set NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';
Spool recover.lst
archive log list
select file#,substr(name,1,50), substr(recover,1,1) R, substr(fuzzy,1,1) F,
to_char(checkpoint_time,'dd/mm/yyyy:hh24:mi:ss') ckpt_time,
checkpoint_change#, resetlogs_change#,
to_char(resetlogs_time,'dd/mm/yyyy HH24:MI:SS') tm from v$datafile_header;
select file#,substr(name, 1, 50), status,enabled,to_char(creation_time,'dd/mm/yyyy:hh24:mi:ss') from v$datafile;
select incarnation#,resetlogs_change#,resetlogs_time,prior_resetlogs_change#,
prior_resetlogs_time,status from v$database_incarnation;
select * from v$backup;
select name, open_mode, controlfile_type,controlfile_change#,
checkpoint_change#, ARCHIVE_CHANGE#,resetlogs_change#, to_char(resetlogs_time,'dd/mm/yyyy:hh24:mi:ss'),
prior_resetlogs_change#, to_char(prior_resetlogs_time,'dd/mm/yyyy:hh24:mi:ss') 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, FHTHR
from X$KCVFH;
spool off
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/668365/viewspace-1019817/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [天羽]差點丟失資料的一次RMAN恢復
- RMAN恢復案例:丟失全部資料檔案恢復
- RMAN完全恢復丟失的資料檔案
- chkdsk 後資料丟失的恢復方法
- RMAN恢復案例:丟失非系統資料檔案恢復
- 資料庫所有檔案丟失後透過RMAN實現恢復資料庫
- rman恢復--歸檔模式有備份，丟失資料檔案的恢復模式
- rman恢復--歸檔模式無備份，丟失資料檔案的恢復模式
- 控制檔案丟失的RMAN恢復
- 利用rman做資料檔案丟失的恢復實驗
- rman 恢復---歸檔丟失and資料檔案損壞
- oracle rman之丟失spfile恢復Oracle
- rman恢復--丟失聯機重做日誌的恢復
- 引數檔案控制檔案和資料檔案丟失的恢復
- RMAN_部分資料檔案丟失或者損壞的恢復
- 資料檔案丟失的恢復
- 丟失undo檔案和隱含引數_allow_resetlogs_corruption恢復
- Mac資料丟失怎麼用EasyRecovery恢復Mac
- 硬碟資料丟失如何恢復？硬碟
- 分割槽丟失資料恢復資料恢復
- 電腦硬碟資料丟失後怎麼恢復？硬碟資料恢復技巧教程硬碟資料恢復
- RMAN資料庫恢復 之歸檔模式有(無)備份-丟失資料檔案的恢復資料庫模式
- oracle歸檔日誌丟失後的資料庫恢復Oracle資料庫
- 用Oracle閃回功能恢復偶然丟失的資料Oracle
- 【RMAN】SYSTEM表空間資料檔案丟失恢復模擬
- 探索ORACLE之RMAN_07 磁碟損壞資料丟失恢復Oracle
- 伺服器資料丟失了怎麼恢復/分割槽丟失恢復教程伺服器
- 資料檔案丟失如何恢復
- 資料庫資料恢復—MongoDB資料庫檔案丟失，啟動報錯的資料恢復案例資料庫資料恢復MongoDB
- 所有除引數檔案以外的檔案都丟失，但是隻有資料檔案的 RMAN 備份的恢復
- 隱藏的資料夾怎麼找出來 恢復隱藏資料夾的辦法
- Oracle備份與恢復【丟失資料檔案的恢復】Oracle
- 北亞資料恢復-WINDOWS還原系統後原分割槽丟失的資料恢復方案資料恢復Windows
- 【伺服器資料恢復】Raid5丟失磁碟後被重建的資料恢復案例伺服器資料恢復AI
- 丟失全部控制檔案後從RMAN備份集中恢復示例
- 普通資料檔案丟失的恢復方法
- 恢復REDO Log丟失的Oracle資料庫Oracle資料庫
- 資料檔案丟失損壞的恢復--