记录8.0.5数据库恢复过程

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

标题:记录8.0.5数据库恢复过程

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

某银行需要恢复2004年磁带备份数据库 8.0.5,当时因为开发商使用begin backup命令进行热备,然后压缩到磁带上.在硬盘紧张,rman不成熟的时代,使用这样的方法备份本身没有大问题,可是因为没有备份归档日志,给现在的恢复带来了很多的问题.
尝试不完全恢复启动库

SVRMGR> startup pfile='init.ora' mount
ORACLE instance started.
Total System Global Area                        141826208 bytes
Fixed Size                                          47264 bytes
Variable Size                                   124829696 bytes
Database Buffers                                 16777216 bytes
Redo Buffers                                       172032 bytes
Database mounted.
SVRMGR> recover database using backup controlfile until cancel;
ORA-00279: change 613561556 generated at 08/21/04 22:42:48 needed for thread 1
ORA-00289: suggestion : /oracle/product/8.0.5/dbs/arch1_55329.dbf
ORA-00280: change 613561556 for thread 1 is in sequence #55329
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SVRMGR> alter database open resetlogs;
alter database open resetlogs
*
ORA-00600: internal error code, arguments: [4146], [31400], [31370], [], [], [], [], []

这个错误是因为回滚段corruption导致,客户已经没有了一致性要求,所以解决办法是通过一些手段找出回滚段并屏蔽

屏蔽回滚段重启库

SVRMGR> shutdown abort
ORACLE instance shut down.
SVRMGR> startup  pfile='init.ora' mount
ORACLE instance started.
Total System Global Area                        141826208 bytes
Fixed Size                                          47264 bytes
Variable Size                                   124829696 bytes
Database Buffers                                 16777216 bytes
Redo Buffers                                       172032 bytes
Database mounted.
SVRMGR> alter database open;
alter database open
*
ORA-00600: internal error code, arguments: [3668], [1], [2], [55992], [55992], [4], [], []

根据ORA-00600[3668]错误的提示,因为重建控制文件,有一个数据文件因为在磁带恢复丢失,所以控制文件在上次重建的时候没有包含进去,根据经验在8.0中可以通过resetlogs解决

resetlogs重新打开数据库

SVRMGR> alter database open resetlogs;
alter database open resetlogs
*
ORA-01139: RESETLOGS option only valid after an incomplete database recovery
SVRMGR> recover datatabase using backup controlfile until cancel;
ORA-00274: illegal recovery option DATATABASE
SVRMGR> recover database using backup controlfile until cancel;
ORA-00279: change 613561558 generated at 07/30/12 09:14:49 needed for thread 1
ORA-00289: suggestion : /oracle/product/8.0.5/dbs/arch1_1.dbf
ORA-00280: change 613561558 for thread 1 is in sequence #1
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SVRMGR> alter database open resetlogs;
alter database open resetlogs
*
ORA-00604: error occurred at recursive SQL level 1
ORA-01555: snapshot too old: rollback segment number  with name "" too small

根据经验可能是屏蔽了undo或者undo损坏,然后有事务可能需要读undo,无法读到对应记录,可能出现此错误,因为无trace文件,尝试推进scn解决.当然这个问题可以通过open时做10046然后深入分析也许可以找到原因.

open数据库成功
根据上面的分析,重启库,推进scn,resetlogs库解决问题

SVRMGR> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SVRMGR> startup pfile='init.ora' mount;
ORACLE instance started.
Total System Global Area                        141826208 bytes
Fixed Size                                          47264 bytes
Variable Size                                   124829696 bytes
Database Buffers                                 16777216 bytes
Redo Buffers                                       172032 bytes
Database mounted.
SVRMGR> alter database open ;
alter database open 
*
ORA-00600: internal error code, arguments: [3668], [1], [2], [55994], [55994], [4], [], []
SVRMGR> recover database using backup controlfile until cancel;
ORA-00279: change 613561560 generated at 07/30/12 09:17:11 needed for thread 1
ORA-00289: suggestion : /oracle/product/8.0.5/dbs/arch1_1.dbf
ORA-00280: change 613561560 for thread 1 is in sequence #1
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SVRMGR> alter database open resetlogs;
Statement processed.

这次恢复比较幸运,在备份到磁带过程中被覆盖的一个同名的数据文件,因为是index,通过查询dba_extents,然后删除并重建相关index,s数据库算恢复完全.有些时候,对数据库多一份备份,可能成为最后的救命稻草,哪怕是一份不是十分完整的备份.再次重复:对dba而言,数据库备份重于一切

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

记录8.0.5数据库恢复过程》有 1 条评论

  1. 惜分飞 说:
    ERROR:
      ORA-600 [3668] [a] [b] [c] [d] [e]
    
    VERSIONS:
      versions 7.0 to 9.2
    
    DESCRIPTION:
    
      This is the FIRST time an attempt has been made to start an instance 
      after a CREATE CONTROLFILE command has been issued.
    
      At least one data file needs MEDIA RECOVERY.
    
      The error has been externalised in Oracle 9.2.x as :
    
          ORA-1113: file <name> needs media recovery.
    
    ARGUMENTS:
       Arg [a]  File number needing media recovery
       Arg [b]  Control file status 
       Arg [c]  Control file checkpoint count
       Arg [d]  File header checkpoint count
       Arg [e]  File header status
     
    FUNCTIONALITY:
      CACHE RECOVERY COMPONENT
     
    IMPACT:
      INSTANCE FAILURE - Usually unable to start the instance when
                         receiving this error.
     
    SUGGESTIONS:        
    
      This should only ever be signalled if a CREATE CONTROLFILE has just been 
      performed and one or more files need media recovery.
    
      In this case it is best to:
    
      o  recreate the controlfile again.
    
            Prior to doing this, check that the create controlfile statement
            is accurate in all respects.
    
            For example, check that it contains the correct number of 
            datafiles for each tablespace and that these files exist.
    
            Check that it contains the correct number of redo log groups.
    
      o  Perform the appropriate media recovery
    
             The type of recovery to be performed depends on the circumstances. 
    
             Doing a complete media recovery :
    
                 RECOVER DATABASE (if create controlfile was NORESETLOGS)
                 ALTER DATABASE OPEN;
    
             Doing an incomplete recovery (for example time based, change based
             or until CANCEL) :
    
                 RECOVER DATABASE USING BACKUP CONTROLFILE (if using RESETLOGS)
                 ALTER DATABASE OPEN RESETLOGS;
    
                 You may need to supply the names of the ONLINE logs to complete 
                 the 'BACKUP CONTROLFILE' recovery.