使用_allow_resetlogs_corruption打开无归档日志rman备份库

联系:手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题:使用_allow_resetlogs_corruption打开无归档日志rman备份库

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

rman还原恢复操作

--还原数据库
RMAN> restore database;
--恢复数据库
RMAN> recover database;

Starting recover at 2012-03-08 21:20:45
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=65 device type=DISK

starting media recovery

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 03/08/2012 21:20:47
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of archived log for thread 1 with sequence 2936 and starting SCN of 25991695 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 2935 and starting SCN of 25991652 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 2934 and starting SCN of 25991649 found to restore
……………………
RMAN-06025: no backup of archived log for thread 1 with sequence 2902 and starting SCN of 25991156 found to restore
这里报日志缺少,实际上是备份的数据库文件后,没有备份归档日志,归档日志全部丢失

进行不完全恢复

SQL> recover database until cancel;
ORA-00279: change 25991194 generated at 03/08/2012 20:33:58 needed for thread 1
ORA-00289: suggestion : /opt/oracle/oradata/archivelog/chf/1_2902_752334071.dbf
ORA-00280: change 25991194 for thread 1 is in sequence #2902


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/opt/oracle/oradata/chf/system01.dbf'


ORA-01112: media recovery not started


SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/opt/oracle/oradata/chf/system01.dbf'

查看相关SCN

SQL> select file#,to_char(checkpoint_change#,'999999999999') from v$datafile;

     FILE# TO_CHAR(CHECK
---------- -------------
         1      25992214
         2      25992214
         3      25992214
         4      25992214
         5      25992214
         6      25992214
         7      25992214
         8      25992214
         9      25992214
        10      25992214
        11      25992214

     FILE# TO_CHAR(CHECK
---------- -------------
        13      25992214
        14      25992214

13 rows selected.

SQL> select file#,online_status,to_char(change#,'999999999999') from v$recover_file;

     FILE# ONLINE_ TO_CHAR(CHANG
---------- ------- -------------
         1 ONLINE       25991194
         2 ONLINE       25991194
         3 ONLINE       25991194
         4 ONLINE       25991194
         5 ONLINE       25991194
         6 ONLINE       25991194
         7 ONLINE       25991194
         8 ONLINE       25991194
         9 ONLINE       25991194
        10 ONLINE       25991194
        11 ONLINE       25991194

     FILE# ONLINE_ TO_CHAR(CHANG
---------- ------- -------------
        13 ONLINE       25991194
        14 ONLINE       25991194

13 rows selected.

SQL> select file#,to_char(checkpoint_change#,'999999999999') from v$datafile_header;

     FILE# TO_CHAR(CHECK
---------- -------------
         1      25991194
         2      25991194
         3      25991194
         4      25991194
         5      25991194
         6      25991194
         7      25991194
         8      25991194
         9      25991194
        10      25991194
        11      25991194

     FILE# TO_CHAR(CHECK
---------- -------------
        13      25991194
        14      25991194

13 rows selected.

--发现数据文件scn和控制文件不一致,重建控制文件,然后查询相关scn
SQL> select file#,to_char(checkpoint_change#,'999999999999') from v$datafile;

     FILE# TO_CHAR(CHECK
---------- -------------
         1      25991194
         2      25991194
         3      25991194
         4      25991194
         5      25991194
         6      25991194
         7      25991194
         8      25991194
         9      25991194
        10      25991194
        11      25991194

     FILE# TO_CHAR(CHECK
---------- -------------
        13      25991194
        14      25991194

13 rows selected.

SQL> select file#,online_status,to_char(change#,'999999999999') from v$recover_file;

     FILE# ONLINE_ TO_CHAR(CHANG
---------- ------- -------------
         1 ONLINE       25991194
         2 ONLINE       25991194
         3 ONLINE       25991194
         4 ONLINE       25991194
         5 ONLINE       25991194
         6 ONLINE       25991194
         7 ONLINE       25991194
         8 ONLINE       25991194
         9 ONLINE       25991194
        10 ONLINE       25991194
        11 ONLINE       25991194

     FILE# ONLINE_ TO_CHAR(CHANG
---------- ------- -------------
        13 ONLINE       25991194
        14 ONLINE       25991194

13 rows selected.

SQL> select file#,to_char(checkpoint_change#,'999999999999') from v$datafile_header;

     FILE# TO_CHAR(CHECK
---------- -------------
         1      25991194
         2      25991194
         3      25991194
         4      25991194
         5      25991194
         6      25991194
         7      25991194
         8      25991194
         9      25991194
        10      25991194
        11      25991194

     FILE# TO_CHAR(CHECK
---------- -------------
        13      25991194
        14      25991194

13 rows selected.
--此时所有scn均一致

尝试打开数据库

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/opt/oracle/oradata/chf/system01.dbf'


SQL> recover database using backup controlfile until cancel;
ORA-00279: change 25991194 generated at 03/08/2012 20:33:58 needed for thread 1
ORA-00289: suggestion : /opt/oracle/oradata/archivelog/chf/1_2902_752334071.dbf
ORA-00280: change 25991194 for thread 1 is in sequence #2902


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/opt/oracle/oradata/chf/system01.dbf'


ORA-01112: media recovery not started

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/opt/oracle/oradata/chf/system01.dbf'

使用隐含参数打开数据库

SQL> create pfile='/tmp/pfile' from spfile;

File created.

-------/tmp/pfile中加上----------
_allow_resetlogs_corruption= TRUE
---------------------------------

SQL> startup mount pfile='/tmp/pfile' force
ORACLE instance started.

Total System Global Area  622149632 bytes
Fixed Size                  2230912 bytes
Variable Size             419431808 bytes
Database Buffers          192937984 bytes
Redo Buffers                7548928 bytes
Database mounted.
SQL> alter database open resetlogs;

Database altered.

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

总结
这次的试验没有多少实际意义,但是可以说明几个问题:
1.所有的数据文件的scn都一致,甚至和控制文件的也一致,数据库不一定可以open成功
(怀疑是数据文件中的scn大于data header scn)
2.对于这样的问题,如果使用bbed修改所有数据文件header的scn不知道是否可以解决
3.如果rman只备份了数据文件而没有任何一个归档日志,数据库通过隐含参数还是可以open,抢救数据

此条目发表在 rman备份/恢复 分类目录,贴了 标签。将固定链接加入收藏夹。

使用_allow_resetlogs_corruption打开无归档日志rman备份库》有 1 条评论

  1. 惜分飞 说:

    补充一次无归档日志,scn不一致,有offline文件的恢复

    SQL> select file#,online_status,to_char(change#,'999999999999') from v$recover_file;
    
         FILE# ONLINE_STATUS  TO_CHAR(CHANGE#,'999999999
    ---------- -------------- --------------------------
             1 ONLINE                869697
             2 ONLINE                869732
             3 ONLINE                869768
             4 ONLINE                869768
             5 OFFLINE               868415
    
    SQL> select file#,to_char(checkpoint_change#,'999999999999'),
      2  to_char(last_change#,'999999999999') from v$datafile;
    
         FILE# TO_CHAR(CHECKPOINT_CHANGE# TO_CHAR(LAST_CHANGE#,'9999
    ---------- -------------------------- --------------------------
             1        870009                     870009
             2        870009                     870009
             3        870009                     870009
             4        870009                     870009
             5        868415                     868810
    
    SQL> recover database until cancel;
    ORA-00279: change 869697 generated at 03/15/2012 12:11:57 needed for thread 1
    ORA-00289: suggestion :
    /u01/oracle/oradata/archivelog/ora11g/1_29_777766629.dbf
    ORA-00280: change 869697 for thread 1 is in sequence #29
    
    
    Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
    cancel
    ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
    ORA-01152: file 2 was not restored from a sufficiently old backup
    ORA-01110: data file 2: '/u01/oracle/oradata/ora11g/sysaux01.dbf'
    
    
    ORA-01112: media recovery not started
    
    
    SQL> select file#,to_char(checkpoint_change#,'999999999999'),
      2  to_char(RESETLOGS_CHANGE#,'999999999999')
      3  from v$datafile_header;
    
         FILE# TO_CHAR(CHECKPOINT_CHANGE# TO_CHAR(RESETLOGS_CHANGE#,
    ---------- -------------------------- --------------------------
             1        869697                     787897
             2        869732                     787897
             3        869768                     787897
             4        869768                     787897
             5        868415                     787897
    
    SQL> create pfile='/tmp/pfile' from spfile;
     
    File created.
     
    在pfile中增加
    _allow_resetlogs_corruption=true
    _allow_error_simulation=TRUE(10g及其以上版本需要)
    
    SQL> shutdown immediate;
    ORA-01109: database not open
    
    
    Database dismounted.
    ORACLE instance shut down.
    
    
    SQL> startup pfile='/tmp/pfile' mount;
    ORACLE instance started.
    
    Total System Global Area  368263168 bytes
    Fixed Size                  1345016 bytes
    Variable Size             293603848 bytes
    Database Buffers           67108864 bytes
    Redo Buffers                6205440 bytes
    Database mounted.
    SQL> alter session set events '10015 trace name adjust_scn level 2';
    
    Session altered.
    
    SQL> recover database until cancel;
    ORA-00279: change 869697 generated at 03/15/2012 12:11:57 needed for thread 1
    ORA-00289: suggestion :
    /u01/oracle/oradata/archivelog/ora11g/1_29_777766629.dbf
    ORA-00280: change 869697 for thread 1 is in sequence #29
    
    
    Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
    cancel
    ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
    ORA-01152: file 2 was not restored from a sufficiently old backup
    ORA-01110: data file 2: '/u01/oracle/oradata/ora11g/sysaux01.dbf'
    
    
    ORA-01112: media recovery not started
    
    SQL> alter database datafile 5 online;
    
    Database altered.
    
    SQL> alter database open resetlogs;
    
    Database altered.
    
    SQL> select file#,online_status,to_char(change#,'999999999999') 
    2         from v$recover_file;
    
    no rows selected