ITPub博客

首页 > 数据库 > Oracle > 【BBED】丢失归档文件情况下的恢复

【BBED】丢失归档文件情况下的恢复

Oracle 作者:路途中的人2012 时间:2018-03-06 11:50:17 0 删除 编辑

BBED】丢失归档文件情况下的数据文件的恢复

 

1.1  BLOG文档结构图

wps54F5.tmp 

 

1.2  前言部分

 

1.2.1  导读和注意事项

各位技术爱好者,看完本文后,你可以掌握如下的技能,也可以学到一些其它你所不知道的知识,~O(∩_∩)O~:

若丢失归档情况下数据文件的恢复,bbed和隐含参数(重点)

数据库启动过程中的介质恢复,scn号的关系

BBED如何修改文件头

归档和非归档模式下数据库的全备

 

  Tips:

       ① 若文章代码格式有错乱,推荐使用QQ、搜狗或360浏览器,也可以下载pdf格式的文档来查看,pdf文档下载地址:http://yunpan.cn/cdEQedhCs2kFz (提取码:ed9b 

       ② 本篇BLOG中命令的输出部分需要特别关注的地方我都用灰色背景和粉红色字体来表示,比如下边的例子中,thread 1的最大归档日志号为33thread 2的最大归档日志号为43是需要特别关注的地方;而命令一般使用黄色背景和红色字体注;对代码或代码输出部分的注释一般采用蓝色字体表示

 

  List of Archived Logs in backup set 11

  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time

  ---- ------- ---------- ------------------- ---------- ---------

  1    32      1621589    2015-05-29 11:09:52 1625242    2015-05-29 11:15:48

  1    33      1625242    2015-05-29 11:15:48 1625293    2015-05-29 11:15:58

  2    42      1613951    2015-05-29 10:41:18 1625245    2015-05-29 11:15:49

  2    43      1625245    2015-05-29 11:15:49 1625253    2015-05-29 11:15:53

 

 

 

 

[ZFXXDB1:root]:/>lsvg -o

T_XDESK_APP1_vg

rootvg

[ZFXXDB1:root]:/>

00:27:22 SQL> alter tablespace idxtbs read write;

 

 

====》2097152*512/1024/1024/1024=1G 

 

 

 

本文如有错误或不完善的地方请大家多多指正,ITPUB留言或QQ皆可,您的批评指正是我写作的最大动力。

 

 

1.2.2  相关参考文章链接

 

ORACLE 11g TSPITR恢复被删除的表空间 http://blog.itpub.net/26736162/viewspace-1681706/

RMANRMAN脚本中使用替换变量--windows rman全备脚本 http://blog.itpub.net/26736162/viewspace-1673725/

TSPITRRMAN表空间基于时间点的自动恢复 http://blog.itpub.net/26736162/viewspace-1671741/

【推荐】 【RMANrm -rf 误操作的恢复过程 http://blog.itpub.net/26736162/viewspace-1623938/

【推荐】 【RMAN】利用备份片还原数据库()-附加 http://blog.itpub.net/26736162/viewspace-1621938/

【推荐】 【RMAN】利用备份片还原数据库() http://blog.itpub.net/26736162/viewspace-1621672/

【推荐】 【RMAN】利用备份片还原数据库() http://blog.itpub.net/26736162/viewspace-1621661/

【推荐】 【RMAN】利用备份片还原数据库() http://blog.itpub.net/26736162/viewspace-1621581/

【推荐】 【RMANRMAN跨版本恢复() http://blog.itpub.net/26736162/viewspace-1562583/

【推荐】  Oracle 组件 系列 小结 http://blog.itpub.net/26736162/viewspace-1562441/

【推荐】 【RMANRMAN跨版本恢复() http://blog.itpub.net/26736162/viewspace-1561352/

【推荐】 【RMANRMAN跨版本恢复() http://blog.itpub.net/26736162/viewspace-1561185/

【推荐】  关于在不同版本和平台之间进行还原或复制的常见问题 http://blog.itpub.net/26736162/viewspace-1549041/

【推荐】 undo表空间文件丢失恢复(4--无备份无recover的情况下恢复 http://blog.itpub.net/26736162/viewspace-1458787/

【推荐】 undo表空间文件丢失恢复(3--无备份无redo的情况下恢复 http://blog.itpub.net/26736162/viewspace-1458750/

【推荐】 undo表空间文件丢失恢复(2--无备份有redo的情况下恢复 http://blog.itpub.net/26736162/viewspace-1458663/

【推荐】 undo表空间文件丢失恢复(1--有备份 http://blog.itpub.net/26736162/viewspace-1458654/

【推荐】 oracle控制文件在缺失归档日志的情况下的恢复 http://blog.itpub.net/26736162/viewspace-1426552/

【推荐】 ORACLE 只读数据文件备份与恢复 http://blog.itpub.net/26736162/viewspace-1425283/

 

 

1.2.3  本文简介

前段时间公司小y给我们培训了下dul恢复truncate的数据,接下来几天我一直在研究truncate的恢复,想总结一下truncate的恢复方法,但是碰到了BBED,可以通过BBED来恢复,可是这个工具不熟悉,之前没用过,其实老早就听说了这个工具,一直想学BBED,但因为一直没碰到实际用途,也一直没有学习,现在碰到了就先研究了BBED了,truncate的恢复过段时间再发blog吧。

本文先给大家介绍了下数据库启动过程中的介质恢复的一些知识点,然后介绍了BBED修改数据文件头的办法推进SCN号来实现完全恢复,而不用resetlogs来打开库,利用隐含参数_allow_resetlogs_corruption来打开数据库只是一个插曲。

我们做实验的时候分2种情况来实验,一种是linux环境,一种是aix环境,linux下我们采用bbed及隐含参数_allow_resetlogs_corruption来恢复,其中推进scn号的时候直接推进到最新的scn号,和其它的数据文件头的scn号保持一致,aix环境下我们采用删除其中的一个归档文件,修改scn的时候修改到丢失的归档文件的scn号,这样可以模拟尽可能少的数据丢失的情况,加深对redo apply的进一步了解。

 

 

1.3  相关知识点扫盲(摘自网络)

 

这个章节相关知识点还是需要了解一下的,小麦苗已经整理好了,和以往blog不太一样,内容有点多。

 

1.3.1  数据库启动过程中的介质恢复

scn号与oracle数据库恢复过程有着密切的关系,只有很好地理解了这层关系,才能深刻地理解恢复的原理,从而才能很好地解决这方面的问题。

1.3.1.1  SCNCHECKPOINT

CKPT进程在checkpoint发生时,将当时的SCN号写入数据文件头和控制文件,同时通知DBWR进程将数据块写到数据文件。

CKPT进程也会在控制文件中记录RBA(redo block address),以标志Recovery需要从日志中哪个地方开始。

checkpoint相关的SCN号有四个,其中三个存在控制文件中,一个存放在数据文件头中。

这四个分别是:

1.System Checkpoint SCN

checkpoint完成后,ORACLESystem Checkpoint SCN号存放在控制文件中。

我们可以通过下面SQL语句查询:

select checkpoint_change# from v$database;

2.Datafile Checkpoint SCN

checkpoint完成后,ORACLEDatafile Checkpoint SCN号存放在控制文件中。

我们可以通过下面SQL语句查询所有数据文件的Datafile Checkpoinnt SCN号。

select name,checkpoint_change# from v$datafile;

3.Start SCN

ORACLEStart SCN号存放在数据文件头中。

这个SCN用于检查数据库启动过程是否需要做media recovery.

我们可以通过以下SQL语句查询:

select name,checkpoint_change# from v$datafile_header;

4.End SCN

ORACLEEnd SCN号存放在控制文件中。

这个SCN号用于检查数据库启动过程是否需要做instance recovery.

我们可以通过以下SQL语句查询:

select name,last_change# from v$datafile;

在数据库正常运行的情况下,对可读写的,online的数据文件,该SCN号为NULL.

 

select checkpoint_change# from v$database;

SELECT a.FILE#, a.NAME,a.CHECKPOINT_CHANGE#,a.LAST_CHANGE#,status FROM v$datafile a;

SELECT a.FILE#,a.NAME,a.RECOVER,a.CHECKPOINT_CHANGE#,status FROM v$datafile_header a;

select file#,online_status,change# from v$recover_file;

 

 

1.3.1.2  SCN号与数据库启动、关闭

在数据库启动过程中,当System Checkpoint SCNDatafile Checkpoint SCNStart SCN号都相同时,数据库可以正常启动,不需要做media recovery.三者当中有一个不同时,则需要做media recovery.

ORACLE在启动过程中首先检查是否需要media recovery,然后再检查是否需要instance recovery.

 

如果数据库的正常关闭的话,将会触发一个checkpoint,同时将数据文件的END SCN号设置为相应数据文件的Start SCN号。当数据库启动时,发现它们是一致的,则不需要做instance recovery。在数据库正常启动后,ORACLE会将END SCN号设置为NULL.

如果数据库异常关闭的话,则END SCN号将为NULL.则需要做instance recovery.

 

1.3.1.3  为什么需要System checkpoint SCN号与Datafile Checkpoint SCN

为什么ORACLE会在控制文件中记录System checkpoint SCN号的同时,还需要为每个数据文件记录Datafile Checkpoint SCN号?

原因有二:

1.对只读表空间,其数据文件的Datafile Checkpoint SCNStart SCNEND SCN号均相同。

这三个SCN在表空间处于只读期间都将被冻结。

2.如果控制文件不是当前的控制文件,则System checkpoint会小于Start SCNEND SCN号。

记录这些SCN号,可以区分控制文件是否是当前的控制文件。

1.3.1.4  recover database using backup controlfile

当有一个Start SCN号超过了System Checkpoint SCN号时,则说明控制文件不是当前的控制文件,因此在做recover时需要采用using backup controlfile。这是为什么需要记录SystemCheckpoint SCN的原因之一。

这里需要一提的是,当重建控制文件的时候,System Checkpoint SCN0Datafile Checkpoint SCN的数据来自于Start SCN。根据上述的描述,此时需要采用using backup controlfilerecovery.

 

1.3.2  修改文件头推进SCN,跳过归档实现完全恢复

 

  一个表空间的数据文件损坏,使用以前的备份进行恢复,但是需要的归档文件无法提供,而且该表空间存放的是历史的数据,很少改变,但由于其它的原因(定时的往该表空间存储新数据),无法变成只读模式。

  这种情况下由于缺少归档,数据库无法recovery,但是跟该表空间相关的数据改变很少或者没有,这种情况下我们可以通过改变数据文件头的检查点号,让oracle避开对该文件的检查,实现完全恢复,及时打开数据库。

1、 数据库异常断电,导致users表空间的数据文件损坏

2、 从早期的备份中转储数据文件到目标位置

3、 对数据库进行recovery,假如归档日志损坏,该表空间没有被修改,但是没有变成read only,数据库无法做完全recovery,所以无法打开:

SQL> alter database open;

alter database open * ERROR at line 1:

ORA-01113: file 4 needs media recovery

ORA-01110: data file 4: '/u01/app/oracle/oradata/TIANJIN/users01.dbf'

SQL> recover database;

ORA-00279: change 1951719 generated at 08/25/2012 13:52:08 needed for thread 1 ORA-00289: suggestion :

/home/oracle/oracle/product/10.2.0/db_1/dbs/arch1_107_783745676.dbf

ORA-00280: change 1951719 for thread 1 is in sequence #107

Specify log: {=suggested | filename | AUTO | CANCEL}

auto

ORA-00308: cannot open archived log

'/home/oracle/oracle/product/10.2.0/db_1/dbs/arch1_107_783745676.dbf'

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

4、 由于该表空间没有被修改,归档日志里面应该没有记录有关该表空间被改变的信息,但是oracle不知道,所以它必须要用到归档,才能够进行recovery,此时,我们的解决办法是:把该表空间的数据文件头的检查点号改成跟控制文件记录的一样,那么oracle就不会做介质恢复,只做实例恢复,则就能够recovery成功,可以使用bbed来实现。

1.3.3  若文件在windowsASM中怎么办

将数据库文件copylinuxaixFS中,BBED修改完成后在copy回数据库的文件位置。

1.3.4  RBA(Redo Block Address)

RBA就是redo entries在重做日志文件中所对应的地址

       A"Redo Block Address" (RBA) describes a physical location within aredo log file.

 

RBA 由以下三部分组成:

       (1)the log file sequence number (4 bytes)

       (2)the log file block number (4 bytes)

       (3)the byte offset into the block at which the redo record starts (2bytes)

 

如:RBA[0x19.2.10] 代表 Log squence25, Block number 2 with byte offset 16.

注意这里的格式:都是16进行。

 

Redo 与checkpoint 关系很大,当设置log_checkpoints_to_alert 参数为true后,checkpoint 发生时会写入alert log里。

 

 

---------------------------------------------------------------------------------------------------------------------

 

 

第二章 实验部分()

2.1  实验环境介绍

项目

db

db 类型

单实例

db version

11.2.0.3.0

db 存储

FS

主机IP地址/hosts配置

192.168.59.129

OS版本及kernel版本

Linux rhel5 2.6.18-194.el5 64位

归档模式

Archive Mode

ORACLE_SID

ora11g

 

 

2.2  实验目标

首先我们OS级别删除system文件,删除所有归档文件,然后利用以前的备份来还原system文件,最后利用BBED修改文件头,推进SCN号来打开数据库,另外一个实验是利用隐含参数_allow_resetlogs_corruption来打开数据库,能正常打开数据库就说明实验成功,关于丢不丢失数据这个另当别论,多一种恢复手段总是好事。

 

2.3  实验过程

2.3.1  模拟system数据文件丢失且删除归档

首先,rman备份1system数据文件:

 

[oracle@rhel5:/home/oracle]# rman target /

 

Recovery Manager: Release 11.2.0.3.0 - Production on Thu Apr 7 17:00:17 2016

 

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

 

connected to target database: ORA11G (DBID=8302811)

 

RMAN> list backupset;

 

using target database control file instead of recovery catalog

specification does not match any backup in the repository

 

RMAN> list copy;

 

specification does not match any datafile copy in the repository

specification does not match any control file copy in the repository

specification does not match any archived log in the repository

 

RMAN> backup datafile 1;

 

Starting backup at 2016-04-07 17:00:35

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=133 device type=DISK

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00001 name=/u01/app/oracle/oradata/ora11g/system01.dbf

channel ORA_DISK_1: starting piece 1 at 2016-04-07 17:00:36

channel ORA_DISK_1: finished piece 1 at 2016-04-07 17:03:24

piece handle=/u01/app/oracle/fast_recovery_area/ORA11G/backupset/2016_04_07/o1_mf_nnndf_TAG20160407T170035_cjd8fn42_.bkp tag=TAG20160407T170035 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:02:49

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

including current control file in backup set

including current SPFILE in backup set

channel ORA_DISK_1: starting piece 1 at 2016-04-07 17:03:26

channel ORA_DISK_1: finished piece 1 at 2016-04-07 17:03:29

piece handle=/u01/app/oracle/fast_recovery_area/ORA11G/backupset/2016_04_07/o1_mf_ncsnf_TAG20160407T170035_cjd8lygf_.bkp tag=TAG20160407T170035 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03

Finished backup at 2016-04-07 17:03:29

 

RMAN>

 

 

删除1号数据文件且删除归档,重启报错:

[oracle@rhel5:/home/oracle]# sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.3.0 Production on Thu Apr 7 17:05:26 2016

 

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

 

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

SYS@ora11g> drop table T_LHR_20160407_02 ;

 

drop table T_LHR_20160407_02

           *

ERROR at line 1:

ORA-00942: table or view does not exist

 

 

SYS@ora11g>  create table T_LHR_20160407_02 as select * from all_objects;

 

 

Table created.

 

SYS@ora11g> update T_LHR_20160407_02 set object_id=100;

 

 

72458 rows updated.

 

SYS@ora11g> commit;

 

Commit complete.

 

SYS@ora11g>  alter database datafile 1 offline;

alter database datafile 1 offline

*

ERROR at line 1:

ORA-01541: system tablespace cannot be brought offline; shut down if necessary

 

====》 SYSTEM表空间不能被offline,普通文件可以,我们os级别删除即可

 

SYS@ora11g> select count(1) from T_LHR_20160407_02;

 

  COUNT(1)

----------

     72458

 

SYS@ora11g> col name format a60

SYS@ora11g> select checkpoint_change# from v$database;

 

CHECKPOINT_CHANGE#

------------------

           2217678

 

SYS@ora11g> SELECT a.FILE#, a.NAME,a.CHECKPOINT_CHANGE#,a.LAST_CHANGE#,status FROM v$datafile a;

 

 

     FILE# NAME                                                         CHECKPOINT_CHANGE# LAST_CHANGE# STATUS

---------- ------------------------------------------------------------ ------------------ ------------ -------

         1 /u01/app/oracle/oradata/ora11g/system01.dbf                             2218426              SYSTEM

         2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf                             2217678              ONLINE

         3 /u01/app/oracle/oradata/ora11g/undotbs01.dbf                            2217678              ONLINE

         4 /u01/app/oracle/oradata/ora11g/users01.dbf                              2217678              ONLINE

         5 /u01/app/oracle/oradata/ora11g/test_dul01.dbf                           2217678              ONLINE

         6 /u01/app/oracle/oradata/ora11g/TS_TRUNCATE01.dbf                        2217678              ONLINE

         7 /u01/app/oracle/oradata/ora11g/lxtbs01.dbf                              2217678              ONLINE

 

7 rows selected.

 

SYS@ora11g> SELECT a.FILE#,a.NAME,a.RECOVER,a.CHECKPOINT_CHANGE#,status  FROM v$datafile_header a;

 

     FILE# NAME                                                         REC CHECKPOINT_CHANGE# STATUS

---------- ------------------------------------------------------------ --- ------------------ -------

         1 /u01/app/oracle/oradata/ora11g/system01.dbf                  NO             2218426 ONLINE

         2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf                  NO             2217678 ONLINE

         3 /u01/app/oracle/oradata/ora11g/undotbs01.dbf                 NO             2217678 ONLINE

         4 /u01/app/oracle/oradata/ora11g/users01.dbf                   NO             2217678 ONLINE

         5 /u01/app/oracle/oradata/ora11g/test_dul01.dbf                NO             2217678 ONLINE

         6 /u01/app/oracle/oradata/ora11g/TS_TRUNCATE01.dbf             NO             2217678 ONLINE

         7 /u01/app/oracle/oradata/ora11g/lxtbs01.dbf                   NO             2217678 ONLINE

 

7 rows selected.

 

SYS@ora11g> select file#,online_status,change# from v$recover_file;

 

no rows selected

 

SYS@ora11g>  alter system switch logfile;

/

/

 

 

System altered.

 

SYS@ora11g>

System altered.

 

SYS@ora11g>

 

 

System altered.

 

SYS@ora11g> col name format a60

SYS@ora11g> select checkpoint_change# from v$database;

 

CHECKPOINT_CHANGE#

------------------

           2220979

 

SYS@ora11g> SELECT a.FILE#, a.NAME,a.CHECKPOINT_CHANGE#,a.LAST_CHANGE#,status FROM v$datafile a;

 

     FILE# NAME                                                         CHECKPOINT_CHANGE# LAST_CHANGE# STATUS

---------- ------------------------------------------------------------ ------------------ ------------ -------

         1 /u01/app/oracle/oradata/ora11g/system01.dbf                             2220979              SYSTEM

         2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf                             2220979              ONLINE

         3 /u01/app/oracle/oradata/ora11g/undotbs01.dbf                            2220979              ONLINE

         4 /u01/app/oracle/oradata/ora11g/users01.dbf                              2220979              ONLINE

         5 /u01/app/oracle/oradata/ora11g/test_dul01.dbf                           2220979              ONLINE

         6 /u01/app/oracle/oradata/ora11g/TS_TRUNCATE01.dbf                        2220979              ONLINE

         7 /u01/app/oracle/oradata/ora11g/lxtbs01.dbf                              2220979              ONLINE

 

7 rows selected.

 

SYS@ora11g> SELECT a.FILE#,a.NAME,a.RECOVER,a.CHECKPOINT_CHANGE#,status  FROM v$datafile_header a;

 

     FILE# NAME                                                         REC CHECKPOINT_CHANGE# STATUS

---------- ------------------------------------------------------------ --- ------------------ -------

         1 /u01/app/oracle/oradata/ora11g/system01.dbf                  NO             2220979 ONLINE

         2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf                  NO             2220979 ONLINE

         3 /u01/app/oracle/oradata/ora11g/undotbs01.dbf                 NO             2220979 ONLINE

         4 /u01/app/oracle/oradata/ora11g/users01.dbf                   NO             2220979 ONLINE

         5 /u01/app/oracle/oradata/ora11g/test_dul01.dbf                NO             2220979 ONLINE

         6 /u01/app/oracle/oradata/ora11g/TS_TRUNCATE01.dbf             NO             2220979 ONLINE

         7 /u01/app/oracle/oradata/ora11g/lxtbs01.dbf                   NO             2220979 ONLINE

 

7 rows selected.

 

SYS@ora11g> select file#,online_status,change# from v$recover_file;

 

no rows selected

 

 

====》切换日志后我们可以看到scn号都保持一致,都是2220979 ,下边我们来删除1号文件

 

 

SYS@ora11g> ! rm /u01/app/oracle/oradata/ora11g/system01.dbf

 

SYS@ora11g> alter system switch logfile;

 

System altered.

 

SYS@ora11g> /

 

System altered.

 

SYS@ora11g> /

 

====》切换日志过程中有一定几率数据库down掉,若没有就shutdown abort掉,然后重启

 

SYS@ora11g> shutdown abort

ORACLE instance shut down.

SYS@ora11g> startup

ORACLE instance started.

 

Total System Global Area  513585152 bytes

Fixed Size                  2229864 bytes

Variable Size             390072728 bytes

Database Buffers          113246208 bytes

Redo Buffers                8036352 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 1 - see DBWR trace file

ORA-01110: data file 1: '/u01/app/oracle/oradata/ora11g/system01.dbf'

 

 

SYS@ora11g> select file#,online_status,change# from v$recover_file;

 

     FILE# ONLINE_    CHANGE#

---------- ------- ----------

         1 ONLINE           0

 

SYS@ora11g>

SYS@ora11g> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     1

Next log sequence to archive   2

Current log sequence           2

SYS@ora11g>

SYS@ora11g> show parameter  recovery

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_recovery_file_dest                string      /u01/app/oracle/fast_recovery_

                                                 area

db_recovery_file_dest_size           big integer 4122M

recovery_parallelism                 integer     0

====》可以看到1号数据文件找不到,然后我们删除归档,确保不能通过recover来恢复

 

[oracle@rhel5:/home/oracle]# cd /u01/app/oracle/fast_recovery_area/ORA11G/archivelog

[oracle@rhel5:/u01/app/oracle/fast_recovery_area/ORA11G/archivelog]# ll

total 4

drwxr-x--- 2 oracle asmadmin 4096 Apr  7 17:09 2016_04_07

[oracle@rhel5:/u01/app/oracle/fast_recovery_area/ORA11G/archivelog]# rm -rf *

[oracle@rhel5:/u01/app/oracle/fast_recovery_area/ORA11G/archivelog]# cd

[oracle@rhel5:/home/oracle]#

 

接下来,我们通过rman来还原1号数据文件,由于归档丢失,不能recover,若非systemundo的数据文件,我们可以offline 掉,然后就可以打开数据库了,但是system必须online才能打开数据库,所以目前数据库不能打开:

[oracle@rhel5:/home/oracle]# rman target /     

 

Recovery Manager: Release 11.2.0.3.0 - Production on Thu Apr 7 17:10:32 2016

 

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

 

connected to target database: ORA11G (DBID=8302811, not open)

 

RMAN> restore datafile 1;

 

Starting restore at 2016-04-07 17:10:40

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=129 device type=DISK

 

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/ora11g/system01.dbf

channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2016_04_07/o1_mf_nnndf_TAG20160407T170035_cjd8fn42_.bkp

channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/ORA11G/backupset/2016_04_07/o1_mf_nnndf_TAG20160407T170035_cjd8fn42_.bkp tag=TAG20160407T170035

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:01:55

Finished restore at 2016-04-07 17:12:36

 

RMAN> recover datafile 1;

 

Starting recover at 2016-04-07 17:14:48

using channel ORA_DISK_1

 

starting media recovery

 

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of recover command at 04/07/2016 17:14:49

RMAN-06053: unable to perform media recovery because of missing log

RMAN-06025: no backup of archived log for thread 1 with sequence 11 and starting SCN of 2221005 found to restore

RMAN-06025: no backup of archived log for thread 1 with sequence 10 and starting SCN of 2221002 found to restore

RMAN-06025: no backup of archived log for thread 1 with sequence 9 and starting SCN of 2220999 found to restore

RMAN-06025: no backup of archived log for thread 1 with sequence 8 and starting SCN of 2220996 found to restore

RMAN-06025: no backup of archived log for thread 1 with sequence 7 and starting SCN of 2220979 found to restore

RMAN-06025: no backup of archived log for thread 1 with sequence 6 and starting SCN of 2220975 found to restore

RMAN-06025: no backup of archived log for thread 1 with sequence 5 and starting SCN of 2220972 found to restore

RMAN-06025: no backup of archived log for thread 1 with sequence 4 and starting SCN of 2217677 found to restore

 

 

RMAN> recover datafile 1 until sequence 3 thread 1;

 

Starting recover at 2016-04-07 17:20:09

using channel ORA_DISK_1

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of recover command at 04/07/2016 17:20:09

RMAN-06556: datafile 1 must be restored from backup older than SCN 2197424

 

====》可以看到由于归档丢失,不能执行recover操作,rman的不完全恢复也不能完成,下边通过SQL的不完全恢复试试

 

 

[oracle@rhel5:/home/oracle]# sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.3.0 Production on Thu Apr 7 17:20:49 2016

 

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

 

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

SYS@ora11g> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     10

Next log sequence to archive   12

Current log sequence           12

SYS@ora11g> alter database open;

alter database open

*

ERROR at line 1:

ORA-01113: file 1 needs media recovery

ORA-01110: data file 1: '/u01/app/oracle/oradata/ora11g/system01.dbf'

 

 

SYS@ora11g>  recover database using backup controlfile until cancel;

ORA-00279: change 2218426 generated at 04/07/2016 17:00:36 needed for thread 1

ORA-00289: suggestion : /u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2016_04_07/o1_mf_1_4_cjd8sssx_.arc

ORA-00280: change 2218426 for thread 1 is in sequence #4

 

 

Specify log: {=suggested | filename | AUTO | CANCEL}

auto

ORA-00308: cannot open archived log '/u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2016_04_07/o1_mf_1_4_cjd8sssx_.arc'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

 

 

ORA-00308: cannot open archived log '/u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2016_04_07/o1_mf_1_4_cjd8sssx_.arc'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

 

 

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below

ORA-01194: file 2 needs more recovery to be consistent

ORA-01110: data file 2: '/u01/app/oracle/oradata/ora11g/sysaux01.dbf'

 

 

SYS@ora11g> alter database open;

alter database open

*

ERROR at line 1:

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

 

 

SYS@ora11g> alter database open ressetlogs;

alter database open ressetlogs

                    *

ERROR at line 1:

ORA-02288: invalid OPEN mode

 

 

SYS@ora11g> alter database open resetlogs;

alter database open resetlogs

*

ERROR at line 1:

ORA-01194: file 2 needs more recovery to be consistent

ORA-01110: data file 2: '/u01/app/oracle/oradata/ora11g/sysaux01.dbf'

 

 

SYS@ora11g> col name format a60

SYS@ora11g> select checkpoint_change# from v$database;

 

 

CHECKPOINT_CHANGE#

------------------

           2221005

 

SYS@ora11g> SELECT a.FILE#, a.NAME,a.CHECKPOINT_CHANGE#,a.LAST_CHANGE#,status FROM v$datafile a;

 

     FILE# NAME                                                         CHECKPOINT_CHANGE# LAST_CHANGE# STATUS

---------- ------------------------------------------------------------ ------------------ ------------ -------

         1 /u01/app/oracle/oradata/ora11g/system01.dbf                             2221005              SYSTEM

         2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf                             2221005              ONLINE

         3 /u01/app/oracle/oradata/ora11g/undotbs01.dbf                            2221005              ONLINE

         4 /u01/app/oracle/oradata/ora11g/users01.dbf                              2221005              ONLINE

         5 /u01/app/oracle/oradata/ora11g/test_dul01.dbf                           2221005              ONLINE

         6 /u01/app/oracle/oradata/ora11g/TS_TRUNCATE01.dbf                        2221005              ONLINE

         7 /u01/app/oracle/oradata/ora11g/lxtbs01.dbf                              2221005              ONLINE

 

7 rows selected.

 

SYS@ora11g> SELECT a.FILE#,a.NAME,a.RECOVER,a.CHECKPOINT_CHANGE#,status  FROM v$datafile_header a;

     FILE# NAME                                                         REC CHECKPOINT_CHANGE# STATUS

---------- ------------------------------------------------------------ --- ------------------ -------

         1 /u01/app/oracle/oradata/ora11g/system01.dbf                                 2218426 ONLINE

         2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf                                 2221005 ONLINE

         3 /u01/app/oracle/oradata/ora11g/undotbs01.dbf                                2221005 ONLINE

         4 /u01/app/oracle/oradata/ora11g/users01.dbf                                  2221005 ONLINE

         5 /u01/app/oracle/oradata/ora11g/test_dul01.dbf                               2221005 ONLINE

         6 /u01/app/oracle/oradata/ora11g/TS_TRUNCATE01.dbf                            2221005 ONLINE

         7 /u01/app/oracle/oradata/ora11g/lxtbs01.dbf                                  2221005 ONLINE

 

7 rows selected.

 

SYS@ora11g> select file#,online_status,change# from v$recover_file;

     FILE# ONLINE_    CHANGE#

---------- ------- ----------

         1 ONLINE     2218426

 

SYS@ora11g>

 

可以看到1号文件和其他文件的Ckp SCN 不同,所以数据库不能打开,下边分别介绍基于隐含参数_allow_resetlogs_corruption BBED修改文件头来恢复打开数据库。

2.3.1.1  方法一:隐含参数_allow_resetlogs_corruption

[oracle@rhel5:/home/oracle]# sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.3.0 Production on Thu Apr 7 17:20:49 2016

 

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

 

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

SYS@ora11g> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     10

Next log sequence to archive   12

Current log sequence           12

SYS@ora11g> alter database open;

alter database open

*

ERROR at line 1:

ORA-01113: file 1 needs media recovery

ORA-01110: data file 1: '/u01/app/oracle/oradata/ora11g/system01.dbf'

 

 

SYS@ora11g>  recover database using backup controlfile until cancel;

ORA-00279: change 2218426 generated at 04/07/2016 17:00:36 needed for thread 1

ORA-00289: suggestion : /u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2016_04_07/o1_mf_1_4_cjd8sssx_.arc

ORA-00280: change 2218426 for thread 1 is in sequence #4

 

 

Specify log: {=suggested | filename | AUTO | CANCEL}

auto

ORA-00308: cannot open archived log '/u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2016_04_07/o1_mf_1_4_cjd8sssx_.arc'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

 

 

ORA-00308: cannot open archived log '/u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2016_04_07/o1_mf_1_4_cjd8sssx_.arc'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

 

 

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below

ORA-01194: file 2 needs more recovery to be consistent

ORA-01110: data file 2: '/u01/app/oracle/oradata/ora11g/sysaux01.dbf'

 

 

SYS@ora11g> alter database open;

alter database open

*

ERROR at line 1:

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

 

 

 

SYS@ora11g> alter database open resetlogs;

alter database open resetlogs

*

ERROR at line 1:

ORA-01194: file 2 needs more recovery to be consistent

ORA-01110: data file 2: '/u01/app/oracle/oradata/ora11g/sysaux01.dbf'

 

 

SYS@ora11g> col name format a60

SYS@ora11g> select checkpoint_change# from v$database;

 

 

CHECKPOINT_CHANGE#

------------------

           2221005

 

SYS@ora11g> SELECT a.FILE#, a.NAME,a.CHECKPOINT_CHANGE#,a.LAST_CHANGE#,status FROM v$datafile a;

 

     FILE# NAME                                                         CHECKPOINT_CHANGE# LAST_CHANGE# STATUS

---------- ------------------------------------------------------------ ------------------ ------------ -------

         1 /u01/app/oracle/oradata/ora11g/system01.dbf                             2221005              SYSTEM

         2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf                             2221005              ONLINE

         3 /u01/app/oracle/oradata/ora11g/undotbs01.dbf                            2221005              ONLINE

         4 /u01/app/oracle/oradata/ora11g/users01.dbf                              2221005              ONLINE

         5 /u01/app/oracle/oradata/ora11g/test_dul01.dbf                           2221005              ONLINE

         6 /u01/app/oracle/oradata/ora11g/TS_TRUNCATE01.dbf                        2221005              ONLINE

         7 /u01/app/oracle/oradata/ora11g/lxtbs01.dbf                              2221005              ONLINE

 

7 rows selected.

 

SYS@ora11g> SELECT a.FILE#,a.NAME,a.RECOVER,a.CHECKPOINT_CHANGE#,status  FROM v$datafile_header a;

 

     FILE# NAME                                                         REC CHECKPOINT_CHANGE# STATUS

---------- ------------------------------------------------------------ --- ------------------ -------

         1 /u01/app/oracle/oradata/ora11g/system01.dbf                                 2218426 ONLINE

         2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf                                 2221005 ONLINE

         3 /u01/app/oracle/oradata/ora11g/undotbs01.dbf                                2221005 ONLINE

         4 /u01/app/oracle/oradata/ora11g/users01.dbf                                  2221005 ONLINE

         5 /u01/app/oracle/oradata/ora11g/test_dul01.dbf                               2221005 ONLINE

         6 /u01/app/oracle/oradata/ora11g/TS_TRUNCATE01.dbf                            2221005 ONLINE

         7 /u01/app/oracle/oradata/ora11g/lxtbs01.dbf                                  2221005 ONLINE

 

7 rows selected.

 

SYS@ora11g> select file#,online_status,change# from v$recover_file;

 

     FILE# ONLINE_    CHANGE#

---------- ------- ----------

         1 ONLINE     2218426

 

SYS@ora11g>

SYS@ora11g>

SYS@ora11g> alter system set "_allow_resetlogs_corruption"=true scope=spfile;

 

System altered.

 

SYS@ora11g> recover database using backup controlfile until cancel;

ORA-00279: change 2218426 generated at 04/07/2016 17:00:36 needed for thread 1

ORA-00289: suggestion : /u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2016_04_07/o1_mf_1_4_cjd8sssx_.arc

ORA-00280: change 2218426 for thread 1 is in sequence #4

 

 

Specify log: {=suggested | filename | AUTO | CANCEL}

auto

ORA-00308: cannot open archived log '/u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2016_04_07/o1_mf_1_4_cjd8sssx_.arc'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

 

 

ORA-00308: cannot open archived log '/u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2016_04_07/o1_mf_1_4_cjd8sssx_.arc'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

 

 

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below

ORA-01194: file 2 needs more recovery to be consistent

ORA-01110: data file 2: '/u01/app/oracle/oradata/ora11g/sysaux01.dbf'

 

 

SYS@ora11g> alter database open;

alter database open

*

ERROR at line 1:

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

 

 

 

SYS@ora11g>

SYS@ora11g> alter database open resetlogs;

 

alter database open resetlogs

*

ERROR at line 1:

ORA-01194: file 2 needs more recovery to be consistent

ORA-01110: data file 2: '/u01/app/oracle/oradata/ora11g/sysaux01.dbf'

 

 

====》 不完全恢复不能打开数据库,只能采用隐含参数了

 

SYS@ora11g>

SYS@ora11g> startup force

ORACLE instance started.

 

Total System Global Area  513585152 bytes

Fixed Size                  2229864 bytes

Variable Size             390072728 bytes

Database Buffers          113246208 bytes

Redo Buffers                8036352 bytes

Database mounted.

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

 

 

SYS@ora11g> alter database open resetlogs;

alter database open resetlogs

*

ERROR at line 1:

ORA-00603: ORACLE server session terminated by fatal error

ORA-00600: internal error code, arguments: [2662], [0], [2218438], [0], [2218967], [12583040], [], [], [], [], [], []

ORA-00600: internal error code, arguments: [2662], [0], [2218437], [0], [2218967], [12583040], [], [], [], [], [], []

ORA-01092: ORACLE instance terminated. Disconnection forced

ORA-00600: internal error code, arguments: [2662], [0], [2218434], [0], [2218967], [12583040], [], [], [], [], [], []

Process ID: 15341

Session ID: 191 Serial number: 3

 

 

SYS@ora11g> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@rhel5:/home/oracle]# sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.3.0 Production on Thu Apr 7 17:28:54 2016

 

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

 

Connected to an idle instance.

 

SYS@ora11g> startup mount

ORACLE instance started.

 

Total System Global Area  513585152 bytes

Fixed Size                  2229864 bytes

Variable Size             390072728 bytes

Database Buffers          113246208 bytes

Redo Buffers                8036352 bytes

Database mounted.

SYS@ora11g> alter database open;

 

Database altered.

 

SYS@ora11g> col name format a60

SYS@ora11g> select checkpoint_change# from v$database;

 

CHECKPOINT_CHANGE#

------------------

           2238438

 

SYS@ora11g> SELECT a.FILE#, a.NAME,a.CHECKPOINT_CHANGE#,a.LAST_CHANGE#,status FROM v$datafile a;

 

     FILE# NAME                                                         CHECKPOINT_CHANGE# LAST_CHANGE# STATUS

---------- ------------------------------------------------------------ ------------------ ------------ -------

         1 /u01/app/oracle/oradata/ora11g/system01.dbf                             2238438              SYSTEM

         2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf                             2238438              ONLINE

         3 /u01/app/oracle/oradata/ora11g/undotbs01.dbf                            2238438              ONLINE

         4 /u01/app/oracle/oradata/ora11g/users01.dbf                              2238438              ONLINE

         5 /u01/app/oracle/oradata/ora11g/test_dul01.dbf                           2238438              ONLINE

         6 /u01/app/oracle/oradata/ora11g/TS_TRUNCATE01.dbf                        2238438              ONLINE

         7 /u01/app/oracle/oradata/ora11g/lxtbs01.dbf                              2238438              ONLINE

 

7 rows selected.

 

SYS@ora11g> SELECT a.FILE#,a.NAME,a.RECOVER,a.CHECKPOINT_CHANGE#,status  FROM v$datafile_header a;

 

     FILE# NAME                                                         REC CHECKPOINT_CHANGE# STATUS

---------- ------------------------------------------------------------ --- ------------------ -------

         1 /u01/app/oracle/oradata/ora11g/system01.dbf                  NO             2238438 ONLINE

         2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf                  NO             2238438 ONLINE

         3 /u01/app/oracle/oradata/ora11g/undotbs01.dbf                 NO             2238438 ONLINE

         4 /u01/app/oracle/oradata/ora11g/users01.dbf                   NO             2238438 ONLINE

         5 /u01/app/oracle/oradata/ora11g/test_dul01.dbf                NO             2238438 ONLINE

         6 /u01/app/oracle/oradata/ora11g/TS_TRUNCATE01.dbf             NO             2238438 ONLINE

         7 /u01/app/oracle/oradata/ora11g/lxtbs01.dbf                   NO             2238438 ONLINE

 

7 rows selected.

 

====》ckp scn 一致,数据库打开,下边我们把_allow_resetlogs_corruption这个参数从spfile中删除,然后备份数据库,我们测试就把该参数删除即可

 

SYS@ora11g> alter system set "_allow_resetlogs_corruption"=false scope=spfile;

 

System altered.

 

SYS@ora11g> alter system reset "_allow_resetlogs_corruption"  scope=spfile sid='*';

 

System altered.

 

SYS@ora11g> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SYS@ora11g> startup

ORACLE instance started.

 

Total System Global Area  513585152 bytes

Fixed Size                  2229864 bytes

Variable Size             390072728 bytes

Database Buffers          113246208 bytes

Redo Buffers                8036352 bytes

Database mounted.

Database opened.

SYS@ora11g> show parameter _allow_resetlogs_corruption

SYS@ora11g>

 

====》隐含参数已从spfile中清除掉了,备份数据库即可

 

 

告警日志:

ALTER DATABASE OPEN

Errors in file /u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_15341.trc:

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

ORA-1589 signalled during: ALTER DATABASE OPEN...

alter database open resetlogs

RESETLOGS is being done without consistancy checks. This may result

in a corrupted database. The database should be recreated.

RESETLOGS after incomplete recovery UNTIL CHANGE 2218426

Archived Log entry 46 added for thread 1 sequence 10 ID 0x94f151 dest 1:

Thu Apr 07 17:28:15 2016

Archived Log entry 47 added for thread 1 sequence 11 ID 0x94f151 dest 1:

Archived Log entry 48 added for thread 1 sequence 12 ID 0x94f151 dest 1:

Clearing online redo logfile 1 /u01/app/oracle/oradata/ora11g/redo01.log

Clearing online log 1 of thread 1 sequence number 10

Clearing online redo logfile 1 complete

Clearing online redo logfile 2 /u01/app/oracle/oradata/ora11g/redo02.log

Clearing online log 2 of thread 1 sequence number 11

Clearing online redo logfile 2 complete

Clearing online redo logfile 3 /u01/app/oracle/oradata/ora11g/redo03.log

Clearing online log 3 of thread 1 sequence number 12

Clearing online redo logfile 3 complete

Resetting resetlogs activation ID 9761105 (0x94f151)

Online log /u01/app/oracle/oradata/ora11g/redo01.log: Thread 1 Group 1 was previously cleared

Online log /u01/app/oracle/oradata/ora11g/redo02.log: Thread 1 Group 2 was previously cleared

Online log /u01/app/oracle/oradata/ora11g/redo03.log: Thread 1 Group 3 was previously cleared

Thu Apr 07 17:28:25 2016

Setting recovery target incarnation to 4

Thu Apr 07 17:28:25 2016

Assigning activation ID 9776087 (0x952bd7)

LGWR: STARTING ARCH PROCESSES

Thu Apr 07 17:28:25 2016

ARC0 started with pid=21, OS id=15440

ARC0: Archival started

LGWR: STARTING ARCH PROCESSES COMPLETE

ARC0: STARTING ARCH PROCESSES

Thu Apr 07 17:28:26 2016

ARC1 started with pid=22, OS id=15447

Thu Apr 07 17:28:26 2016

ARC2 started with pid=23, OS id=15450

Thread 1 opened at log sequence 1

  Current log# 1 seq# 1 mem# 0: /u01/app/oracle/oradata/ora11g/redo01.log

Successful open of redo thread 1

Thu Apr 07 17:28:27 2016

MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set

Thu Apr 07 17:28:27 2016

SMON: enabling cache recovery

Thu Apr 07 17:28:27 2016

ARC3 started with pid=24, OS id=15452

ARC1: Archival started

ARC2: Archival started

ARC1: Becoming the 'no FAL' ARCH

ARC1: Becoming the 'no SRL' ARCH

ARC2: Becoming the heartbeat ARCH

ARC3: Archival started

ARC0: STARTING ARCH PROCESSES COMPLETE

Errors in file /u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_15341.trc  (incident=39761):

ORA-00600: internal error code, arguments: [2662], [0], [2218434], [0], [2218967], [12583040], [], [], [], [], [], []

Incident details in: /u01/app/oracle/diag/rdbms/ora11g/ora11g/incident/incdir_39761/ora11g_ora_15341_i39761.trc

Errors in file /u01/app/oracle/diag/rdbms/ora11g/ora11g/incident/incdir_39761/ora11g_ora_15341_i39761.trc:

ORA-00308: cannot open archived log '/u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2016_04_07/o1_mf_1_10_cjd8vcy1_.arc'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

ORA-00600: internal error code, arguments: [2662], [0], [2218434], [0], [2218967], [12583040], [], [], [], [], [], []

Thu Apr 07 17:28:35 2016

Dumping diagnostic data in directory=[cdmp_20160407172835], requested by (instance=1, osid=15341), summary=[incident=39761].

Use ADRCI or Support Workbench to package the incident.

See Note 411.1 at My Oracle Support for error and packaging details.

Errors in file /u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_15341.trc:

ORA-00600: internal error code, arguments: [2662], [0], [2218434], [0], [2218967], [12583040], [], [], [], [], [], []

Errors in file /u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_15341.trc:

ORA-00600: internal error code, arguments: [2662], [0], [2218434], [0], [2218967], [12583040], [], [], [], [], [], []

Error 600 happened during db open, shutting down database

USER (ospid: 15341): terminating the instance due to error 600

Thu Apr 07 17:28:35 2016

ORA-1092 : opitsk aborting process

Thu Apr 07 17:28:35 2016

ORA-1092 : opitsk aborting process

Instance terminated by USER, pid = 15341

ORA-1092 signalled during: alter database open resetlogs...

Errors in file /u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_15341.trc  (incident=39762):

ORA-00600: internal error code, arguments: [2662], [0], [2218437], [0], [2218967], [12583040], [], [], [], [], [], []

ORA-01092: ORACLE instance terminated. Disconnection forced

ORA-00600: internal error code, arguments: [2662], [0], [2218434], [0], [2218967], [12583040], [], [], [], [], [], []

Incident details in: /u01/app/oracle/diag/rdbms/ora11g/ora11g/incident/incdir_39762/ora11g_ora_15341_i39762.trc

Use ADRCI or Support Workbench to package the incident.

See Note 411.1 at My Oracle Support for error and packaging details.

Errors in file /u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_15341.trc  (incident=39763):

ORA-00600: internal error code, arguments: [2662], [0], [2218438], [0], [2218967], [12583040], [], [], [], [], [], []

ORA-00600: internal error code, arguments: [2662], [0], [2218437], [0], [2218967], [12583040], [], [], [], [], [], []

ORA-01092: ORACLE instance terminated. Disconnection forced

ORA-00600: internal error code, arguments: [2662], [0], [2218434], [0], [2218967], [12583040], [], [], [], [], [], []

Incident details in: /u01/app/oracle/diag/rdbms/ora11g/ora11g/incident/incdir_39763/ora11g_ora_15341_i39763.trc

Thu Apr 07 17:28:37 2016

Use ADRCI or Support Workbench to package the incident.

See Note 411.1 at My Oracle Support for error and packaging details.

Errors in file /u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_15341.trc:

ORA-00600: internal error code, arguments: [2662], [0], [2218438], [0], [2218967], [12583040], [], [], [], [], [], []

ORA-00600: internal error code, arguments: [2662], [0], [2218437], [0], [2218967], [12583040], [], [], [], [], [], []

ORA-01092: ORACLE instance terminated. Disconnection forced

ORA-00600: internal error code, arguments: [2662], [0], [2218434], [0], [2218967], [12583040], [], [], [], [], [], []

Errors in file /u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_15341.trc:

ORA-27300: OS system dependent operation:semctl failed with status: 22

ORA-27301: OS failure message: Invalid argument

ORA-27302: failure occurred at: sskgpwpost1

ORA-27303: additional information: semid = 4292611

ORA-00600: internal error code, arguments: [2662], [0], [2218438], [0], [2218967], [12583040], [], [], [], [], [], []

ORA-00600: internal error code, arguments: [2662], [0], [2218437], [0], [2218967], [12583040], [], [], [], [], [], []

ORA-01092: ORACLE instance terminated. Disconnection forced

ORA-00600: internal error code, arguments: [2662], [0], [2218434], [0], [2218967], [12583040], [], [], [], [], [], []

Errors in file /u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_15341.trc  (incident=39764):

ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], [], [], [], [], []

ORA-00600: internal error code, arguments: [2662], [0], [2218438], [0], [2218967], [12583040], [], [], [], [], [], []

ORA-00600: internal error code, arguments: [2662], [0], [2218437], [0], [2218967], [12583040], [], [], [], [], [], []

ORA-01092: ORACLE instance terminated. Disconnection forced

ORA-00600: internal error code, arguments: [2662], [0], [2218434], [0], [2218967], [12583040], [], [], [], [], [], []

Incident details in: /u01/app/oracle/diag/rdbms/ora11g/ora11g/incident/incdir_39764/ora11g_ora_15341_i39764.trc

Use ADRCI or Support Workbench to package the incident.

See Note 411.1 at My Oracle Support for error and packaging details.

Errors in file /u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_15341.trc:

ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], [], [], [], [], []

ORA-00600: internal error code, arguments: [2662], [0], [2218438], [0], [2218967], [12583040], [], [], [], [], [], []

ORA-00600: internal error code, arguments: [2662], [0], [2218437], [0], [2218967], [12583040], [], [], [], [], [], []

ORA-01092: ORACLE instance terminated. Disconnection forced

ORA-00600: internal error code, arguments: [2662], [0], [2218434], [0], [2218967], [12583040], [], [], [], [], [], []

Errors in file /u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_15341.trc:

ORA-00600: internal error code, arguments: [2662], [0], [2218438], [0], [2218967], [12583040], [], [], [], [], [], []

ORA-00600: internal error code, arguments: [2662], [0], [2218437], [0], [2218967], [12583040], [], [], [], [], [], []

ORA-01092: ORACLE instance terminated. Disconnection forced

ORA-00600: internal error code, arguments: [2662], [0], [2218434], [0], [2218967], [12583040], [], [], [], [], [], []

Thu Apr 07 17:28:39 2016

Errors in file /u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_15341.trc  (incident=40809):

ORA-00603: ORACLE server session terminated by fatal error

ORA-00600: internal error code, arguments: [2662], [0], [2218438], [0], [2218967], [12583040], [], [], [], [], [], []

ORA-00600: internal error code, arguments: [2662], [0], [2218437], [0], [2218967], [12583040], [], [], [], [], [], []

ORA-01092: ORACLE instance terminated. Disconnection forced

ORA-00600: internal error code, arguments: [2662], [0], [2218434], [0], [2218967], [12583040], [], [], [], [], [], []

Incident details in: /u01/app/oracle/diag/rdbms/ora11g/ora11g/incident/incdir_40809/ora11g_ora_15341_i40809.trc

Errors in file /u01/app/oracle/diag/rdbms/ora11g/ora11g/incident/incdir_40809/ora11g_ora_15341_i40809.trc:

ORA-00603: ORACLE server session terminated by fatal error

ORA-00600: internal error code, arguments: [2662], [0], [2218438], [0], [2218967], [12583040], [], [], [], [], [], []

ORA-00600: internal error code, arguments: [2662], [0], [2218437], [0], [2218967], [12583040], [], [], [], [], [], []

ORA-01092: ORACLE instance terminated. Disconnection forced

ORA-00600: internal error code, arguments: [2662], [0], [2218434], [0], [2218967], [12583040], [], [], [], [], [], []

Thu Apr 07 17:29:01 2016

Adjusting the default value of parameter parallel_max_servers

from 160 to 135 due to the value of parameter processes (150)

Starting ORACLE instance (normal)

LICENSE_MAX_SESSION = 0

LICENSE_SESSIONS_WARNING = 0

Picked latch-free SCN scheme 3

Using LOG_ARCHIVE_DEST_1 parameter default value as USE_DB_RECOVERY_FILE_DEST

Autotune of undo retention is turned on.

IMODE=BR

ILAT =27

LICENSE_MAX_USERS = 0

SYS auditing is disabled

Starting up:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options.

ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1

System name:    Linux

Node name:      rhel5

Release:        2.6.18-194.el5

Version:        #1 SMP Tue Mar 16 21:52:39 EDT 2010

Machine:        x86_64

VM name:        VMWare Version: 6

Using parameter settings in server-side spfile /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileora11g.ora

System parameters with non-default values:

  processes                = 150

  memory_target            = 492M

  control_files            = "/u01/app/oracle/oradata/ora11g/control01.ctl"

  control_files            = "/u01/app/oracle/fast_recovery_area/ora11g/control02.ctl"

  db_block_size            = 8192

  compatible               = "11.2.0.0.0"

  db_recovery_file_dest    = "/u01/app/oracle/fast_recovery_area"

  db_recovery_file_dest_size= 4122M

  _allow_resetlogs_corruption= TRUE

  undo_tablespace          = "UNDOTBS1"

  remote_login_passwordfile= "EXCLUSIVE"

  db_domain                = ""

  dispatchers              = "(PROTOCOL=TCP) (SERVICE=ora11gXDB)"

  audit_file_dest          = "/u01/app/oracle/admin/ora11g/adump"

  audit_trail              = "DB"

  db_name                  = "ora11g"

  open_cursors             = 300

  diagnostic_dest          = "/u01/app/oracle"

Thu Apr 07 17:29:01 2016

PMON started with pid=2, OS id=15634

Thu Apr 07 17:29:01 2016

PSP0 started with pid=3, OS id=15636

Thu Apr 07 17:29:02 2016

VKTM started with pid=4, OS id=15641 at elevated priority

VKTM running at (1)millisec precision with DBRM quantum (100)ms

Thu Apr 07 17:29:02 2016

GEN0 started with pid=5, OS id=15645

Thu Apr 07 17:29:02 2016

DIAG started with pid=6, OS id=15647

Thu Apr 07 17:29:02 2016

DBRM started with pid=7, OS id=15649

Thu Apr 07 17:29:02 2016

DIA0 started with pid=8, OS id=15651

Thu Apr 07 17:29:02 2016

MMAN started with pid=9, OS id=15655

Thu Apr 07 17:29:02 2016

DBW0 started with pid=10, OS id=15657

Thu Apr 07 17:29:02 2016

LGWR started with pid=11, OS id=15660

Thu Apr 07 17:29:02 2016

CKPT started with pid=12, OS id=15662

Thu Apr 07 17:29:02 2016

SMON started with pid=13, OS id=15664

Thu Apr 07 17:29:02 2016

RECO started with pid=14, OS id=15666

Thu Apr 07 17:29:02 2016

MMON started with pid=15, OS id=15668

Thu Apr 07 17:29:02 2016

MMNL started with pid=16, OS id=15670

starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...

starting up 1 shared server(s) ...

ORACLE_BASE from environment = /u01/app/oracle

Thu Apr 07 17:29:03 2016

ALTER DATABASE   MOUNT

Thu Apr 07 17:29:06 2016

Sweep [inc][40809]: completed

Sweep [inc][39764]: completed

Sweep [inc][39763]: completed

Sweep [inc][39762]: completed

Sweep [inc][39761]: completed

Sweep [inc2][40809]: completed

Sweep [inc2][39764]: completed

Sweep [inc2][39763]: completed

Sweep [inc2][39762]: completed

Sweep [inc2][39761]: completed

Successful mount of redo thread 1, with mount id 9775647

Database mounted in Exclusive Mode

Lost write protection disabled

Completed: ALTER DATABASE   MOUNT

Thu Apr 07 17:29:11 2016

alter database open

Beginning crash recovery of 1 threads

parallel recovery started with 3 processes

Started redo scan

Completed redo scan

read 1 KB redo, 3 data blocks need recovery

Started redo application at

Thread 1: logseq 1, block 3, scn 2218432

Recovery of Online Redo Log: Thread 1 Group 1 Seq 1 Reading mem 0

  Mem# 0: /u01/app/oracle/oradata/ora11g/redo01.log

Completed redo application of 0.00MB

Completed crash recovery at

Thread 1: logseq 1, block 5, scn 2238435

3 data blocks read, 3 data blocks written, 1 redo k-bytes read

LGWR: STARTING ARCH PROCESSES

Thu Apr 07 17:29:11 2016

ARC0 started with pid=24, OS id=15751

ARC0: Archival started

LGWR: STARTING ARCH PROCESSES COMPLETE

ARC0: STARTING ARCH PROCESSES

Thu Apr 07 17:29:12 2016

ARC1 started with pid=25, OS id=15756

Thread 1 advanced to log sequence 2 (thread open)

Thu Apr 07 17:29:12 2016

ARC2 started with pid=26, OS id=15758

Thread 1 opened at log sequence 2

  Current log# 2 seq# 2 mem# 0: /u01/app/oracle/oradata/ora11g/redo02.log

Successful open of redo thread 1

MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set

SMON: enabling cache recovery

Thu Apr 07 17:29:12 2016

ARC3 started with pid=27, OS id=15760

ARC1: Archival started

ARC2: Archival started

ARC1: Becoming the 'no FAL' ARCH

ARC1: Becoming the 'no SRL' ARCH

ARC2: Becoming the heartbeat ARCH

Archived Log entry 49 added for thread 1 sequence 1 ID 0x952bd7 dest 1:

ARC3: Archival started

ARC0: STARTING ARCH PROCESSES COMPLETE

[15725] Successfully onlined Undo Tablespace 2.

Undo initialization finished serial:0 start:85654704 end:85655584 diff:880 (8 seconds)

Dictionary check beginning

Dictionary check complete

Verifying file header compatibility for 11g tablespace encryption..

Verifying 11g file header compatibility for tablespace encryption completed

Thu Apr 07 17:29:13 2016

SMON: enabling tx recovery

Database Characterset is AL32UTF8

No Resource Manager plan active

replication_dependency_tracking turned off (no async multimaster replication found)

Starting background process QMNC

Thu Apr 07 17:29:16 2016

QMNC started with pid=28, OS id=15774

LOGSTDBY: Validating controlfile with logical metadata

LOGSTDBY: Validation complete

Completed: alter database open

Thu Apr 07 17:29:24 2016

db_recovery_file_dest_size of 4122 MB is 18.36% used. This is a

user-specified limit on the amount of space that will be used by this

database for recovery-related files, and does not reflect the amount of

space available in the underlying filesystem or ASM diskgroup.

Thu Apr 07 17:29:24 2016

Starting background process CJQ0

Thu Apr 07 17:29:24 2016

CJQ0 started with pid=31, OS id=15820

 

 

通过_allow_resetlogs_corruption跳过数据库的一致性检查,成功打开数据库,但也有些案例由于各种原因即使加上该参数也不能打开库,这个时候就得靠我们的BBED了,切记resetlogs后立即备份数据库

2.3.1.2  方法二:bbed 恢复--修改文件头推进SCN,跳过归档实现完全恢复

首先查看system文件头的scn号:

[oracle@rhel5:/home/oracle]# sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.3.0 Production on Thu Apr 7 17:41:50 2016

 

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

 

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

SYS@ora11g> col name format a60

SYS@ora11g> select checkpoint_change# from v$database;

 

CHECKPOINT_CHANGE#

------------------

           2239089

 

SYS@ora11g> SELECT a.FILE#, a.NAME,a.CHECKPOINT_CHANGE#,a.LAST_CHANGE#,status FROM v$datafile a;

 

 

     FILE# NAME                                                         CHECKPOINT_CHANGE# LAST_CHANGE# STATUS

---------- ------------------------------------------------------------ ------------------ ------------ -------

         1 /u01/app/oracle/oradata/ora11g/system01.dbf                             2239089              SYSTEM

         2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf                             2239089              ONLINE

         3 /u01/app/oracle/oradata/ora11g/undotbs01.dbf                            2239089              ONLINE

         4 /u01/app/oracle/oradata/ora11g/users01.dbf                              2239089              ONLINE

         5 /u01/app/oracle/oradata/ora11g/test_dul01.dbf                           2239089              ONLINE

         6 /u01/app/oracle/oradata/ora11g/TS_TRUNCATE01.dbf                        2239089              ONLINE

         7 /u01/app/oracle/oradata/ora11g/lxtbs01.dbf                              2239089              ONLINE

 

7 rows selected.

 

SYS@ora11g> SELECT a.FILE#,a.NAME,a.RECOVER,a.CHECKPOINT_CHANGE#,status  FROM v$datafile_header a;

 

 

     FILE# NAME                                                         REC CHECKPOINT_CHANGE# STATUS

---------- ------------------------------------------------------------ --- ------------------ -------

         1 /u01/app/oracle/oradata/ora11g/system01.dbf                                 2218426 ONLINE

         2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf                  NO             2239089 ONLINE

         3 /u01/app/oracle/oradata/ora11g/undotbs01.dbf                 NO             2239089 ONLINE

         4 /u01/app/oracle/oradata/ora11g/users01.dbf                   NO             2239089 ONLINE

         5 /u01/app/oracle/oradata/ora11g/test_dul01.dbf                NO             2239089 ONLINE

         6 /u01/app/oracle/oradata/ora11g/TS_TRUNCATE01.dbf             NO             2239089 ONLINE

         7 /u01/app/oracle/oradata/ora11g/lxtbs01.dbf                   NO             2239089 ONLINE

 

7 rows selected.

 

SYS@ora11g> select file#,online_status,change# from v$recover_file;