精华内容
下载资源
问答
  • 临时表空间数据文件损坏的解决

    千次阅读 2013-10-27 18:25:43
    临时表空间数据文件丢失的恢复 临时表空间数据文件丢失数据库可以打开,可以通过重建一个临时表空间并设置为默认或者新建一个属于临时表空间的数据文件,并删除已经丢失的数据的方法来恢复。 1.丢失临时表空间的...

    临时表空间数据文件丢失的恢复

    临时表空间数据文件丢失数据库可以打开,可以通过重建一个临时表空间并设置为默认或者新建一个属于临时表空间的数据文件,并删除已经丢失的数据的方法来恢复。

    1.丢失临时表空间的数据文件 ,数据库可以正常打开。

    SQL> col file for a25

    SQL> select  f.file#,t.ts#,f.name "file",t.name"tablespace" from v$tempfile f,v$tablespace t where f.ts#=t.ts#;

        FILE#        TS# file                      tablespace

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

            1          3 E:\SYSORADATA\TEMP01.DBF  TEMP

    SQL> show user

    USER 为 "SYS"

    SQL> shutdown immediate;

    数据库已经关闭。

    已经卸载数据库。

    ORACLE 例程已经关闭。

    SQL> host move e:\sysoradata\temp01.dbf  e:\sysoradata\temp01.dbfa


    SQL> startup;

    ORACLE 例程已经启动。

    Total System Global Area  431038464 bytes

    Fixed Size                  1375088 bytes

    Variable Size             331351184 bytes

    Database Buffers           92274688 bytes

    Redo Buffers                6037504 bytes

    数据库装载完毕。

    数据库已经打开。


    SQL> select  tablespace_name,status,contents,extent_management  from dba_tablespaces;

    TABLESPACE_NAME      STATUS   CONTENTS  EXTENT_MAN

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

    SYSTEM               ONLINE    PERMANENT LOCAL

    SYSAUX               ONLINE    PERMANENT LOCAL

    UNDOTBS1             ONLINE    UNDO     LOCAL

    TEMP                ONLINE    TEMPORARY LOCAL

    USERS                ONLINE    PERMANENT LOCAL

    BYS_UNDO             ONLINE    UNDO     LOCAL

    数据库OPEN阶段部分ALERT日志:

    SMON: enabling tx recovery

    Re-creating tempfileE:\SYSORADATA\TEMP01.DBF

    Database Characterset is ZHS16GBK

    No Resource Manager plan active

    ##########################################################

    2.不停机维护临时表空间

    如大规模排序的SQL语句查询时临时文件出问题,临时表空间对应数据文件出错引起ORACLE出错。

    此时,可以为临时表空间增加一个数据文件,并且删除出错的原临时表空间数据文件。

    SQL> selecttablespace_name,status,contents,extent_management from dba_tablespaces;

    TABLESPACE_NAME      STATUS   CONTENTS  EXTENT_MAN

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

    SYSTEM               ONLINE    PERMANENT LOCAL

    SYSAUX               ONLINE    PERMANENT LOCAL

    UNDOTBS1             ONLINE    UNDO     LOCAL

    TEMP                 ONLINE    TEMPORARY LOCAL

    USERS                ONLINE    PERMANENT LOCAL

    BYS_UNDO             ONLINE    UNDO     LOCAL

    SQL> alter  tablespace temp add tempfile 'e:\sysoradata\temp02.dbf' size 20m;

    表空间已更改。

    SQL> select  f.file#,t.ts#,f.name"file",t.name "tablespace"  from  v$tempfile f,v$tablespace t where f.ts#=t.ts#;

        FILE#        TS# file                      tablespace

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

            2          3 E:\SYSORADATA\TEMP02.DBF  TEMP

            1          3E:\SYSORADATA\TEMP01.DBF  TEMP

    SQL> selecttablespace_name,status,contents,extent_management from dba_tablespaces;

    TABLESPACE_NAME      STATUS   CONTENTS  EXTENT_MAN

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

    SYSTEM               ONLINE    PERMANENT LOCAL

    SYSAUX               ONLINE    PERMANENT LOCAL

    UNDOTBS1             ONLINE    UNDO     LOCAL

    TEMP                 ONLINE    TEMPORARY LOCAL

    USERS                ONLINE    PERMANENT LOCAL

    BYS_UNDO             ONLINE    UNDO     LOCAL

    SQL> alter tablespace temp drop tempfile 'e:\sysoradata\temp01.dbf';

    表空间已更改。

    SQL> select  f.file#,t.ts#,f.name"file",t.name "tablespace" from v$tempfile f,v$tablespace t where  f.ts#=t.ts#;

         FILE#       TS# file                     tablespace

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

            2          3E:\SYSORADATA\TEMP02.DBF  TEMP

    SQL> select  tablespace_name,status,contents,extent_management from dba_tablespaces;

    TABLESPACE_NAME      STATUS   CONTENTS  EXTENT_MAN

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

    SYSTEM               ONLINE    PERMANENT LOCAL

    SYSAUX               ONLINE    PERMANENT LOCAL

    UNDOTBS1             ONLINE    UNDO     LOCAL

    TEMP                 ONLINE    TEMPORARY LOCAL

    USERS                ONLINE    PERMANENT LOCAL

    BYS_UNDO             ONLINE    UNDO     LOCAL

    SQL> col tablespace for a20

    SQL> select   f.file#,t.ts#,f.name"file",t.name "tablespace",bytes/1024/1024 MB from v$tempfile f,v$tablespace t wheref.ts#=t.ts#;

        FILE#        TS# file                      tablespace                   MB

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

            2          3E:\SYSORADATA\TEMP02.DBF  TEMP                         20

    相关ALERT日志 :

    alter tablespace temp add tempfile'e:\sysoradata\temp02.dbf' size 20m

    Completed: alter tablespace temp addtempfile 'e:\sysoradata\temp02.dbf' size 20m

    Sun Feb 10 20:16:44 2013

     alter tablespace temp drop tempfile'e:\sysoradata\temp01.dbf'

    WARNING: Cannot delete fileE:\SYSORADATA\TEMP01.DBF

    Errors in filec:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_ora_1716.trc:

    ORA-01265: 无法删除 TEMP FILE E:\SYSORADATA\TEMP01.DBF

    ORA-27056: 无法删除文件

    OSD-04024: 无法删除文件。

    O/S-Error: (OS 32) 另一个程序正在使用此文件,进程无法访问。

    Completed: alter tablespace temp drop tempfile 'e:\sysoradata\temp01.dbf'

    展开全文
  • 个控制文件,接下来又提示,说控制文件和数据文件不一致:控制文件比数据文件旧。控制文件是当天的,但数据文件有 7 月 3 号的(一个月前的),也有当天日期但比控制文件新的, 3 个联机日志文件都是 7 月 3 号的。...

    平台:windows xp

    Oracle 817

    非归档模式

    时间:86日 周三下午3点后,7日 周四一天

     

    实例1:ORCL的恢复:

     

    打开数据库时提示信息,两个控制文件不一致(从外面看两个文件的大小和日期还是一样的),但实际显示的字节数不同。

     

        首先,试图备份整个数据文件目录,但总不成功,显示检验和,或者循环冗余之类的错误。只好备份能备份的文件,继续下一步。

        同事没有记住2个控制文件的字节差别,应该用大的那个去替代别的控制文件,貌似我用其中一个,可能是小的control02.ctl替代了其他2个控制文件,接下来又提示,说控制文件和数据文件不一致:控制文件比数据文件旧。控制文件是当天的,但数据文件有73号的(一个月前的),也有当天日期但比控制文件新的,3个联机日志文件都是73号的。查询网上,这类型情形只能重建控制文件。生成建立控制文件的脚本,装载数据库,备份控制文件,创建成功。

     

    --创建控制文件的脚本(/***r database backup controlfile to ‘E:\trace\ctl.bak’;*/)

    alter database backup controlfile to trace

    Completed: alter database backup controlfile to trace

    Wed Jul 30 17:00:48 2014

     

    然后开始重建控制文件:

    SQL>shutdown immediate;

    SQL>STARTUP NOMOUNT;

    SQL>CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS  NOARCHIVELOG

        MAXLOGFILES 16

        MAXLOGMEMBERS 3

        MAXDATAFILES 100

        MAXINSTANCES 8

        MAXLOGHISTORY 292

    LOGFILE

      GROUP 1 'E:\oracle\oradata\orcl\redo01.log'  SIZE 50M BLOCKSIZE 512,

      GROUP 2 'E:\oracle\oradata\orcl\redo02.log'  SIZE 50M BLOCKSIZE 512,

      GROUP 3 'E:\oracle\oradata\orcl\redo03.log'  SIZE 50M BLOCKSIZE 512

    -- STANDBY LOGFILE

    DATAFILE

      'E:\ORACLE\ORADATA\ORCL\SYSTEM01.DBF',

      'E:\ORACLE\ORADATA\ORCL\RBS01.DBF',

      'E:\ORACLE\ORADATA\ORCL\USERS01.DBF',

      'E:\ORACLE\ORADATA\ORCL\TEMP01.DBF',

      'E:\ORACLE\ORADATA\ORCL\TOOLS01.DBF',

      'E:\ORACLE\ORADATA\ORCL\INDX01.DBF',

      'E:\ORACLE\ORADATA\ORCL\DR01.DBF'

    CHARACTER SET ZHS16GBK

    ;

    Completed: CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLO

    Wed Jul 30 17:04:31 2014

    SQL>alter database mount;

    出现错误 ORA-1100 signalled during: alter database mount,无法装载数据库

     

    尝试恢复数据库:Wed Jul 30 17:06:24 2014

    SQLRECOVER DATABASE

    Wed Jul 30 17:04:44 2014

    ALTER DATABASE RECOVER  database 

    Wed Jul 30 17:04:44 2014

    Media Recovery Start

    Media Recovery Log

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

      Mem# 0 errs 0: E:\ORACLE\ORADATA\ORCL\REDO02.LOG

    Media Recovery failed with error 1115

    ORA-283 signalled during: ALTER DATABASE RECOVER  database  ...

    Wed Jul 30 17:06:24 2014

    alter database mount

     

    Wed Jul 30 17:06:24 2014

    ORA-1100 signalled during: alter database mount

    ...

    Wed Jul 30 17:06:31 2014

    alter database open

     

    ORA-1113 signalled during: alter database open

    出现错误ORA-01110 ORA-0113,于是依次恢复出错的数据文件

    SQL>recover datafile 'E:\ORACLE\ORADATA\ORCL\SYSTEM01.DBF';

    完成介质恢复。

    SQL>recover datafile 'E:\ORACLE\ORADATA\ORCL\RBS01.DBF';

    第 1 行出现错误:
    ORA-00283: 恢复会话因错误而取消
    ORA-01115: 从文件 2读取块时出现 IO 错误 (块 # 31####)
    ORA-01110: 数据文件 2: ' E:\ORACLE\ORADATA\ORCL\RBS01.DBF '
    ORA-27069: skgfdisp: 尝试在文件范围外执行 I/O
    OSD-04026: 传递的参数无效。 (OS 3145977)

    ALERT文件显示:

    ALTER DATABASE RECOVER  datafile 'e:\oracle\oradata\orcl\rbs01.dbf' 

    Wed Jul 30 17:09:28 2014

    Media Recovery Datafile: 'e:\oracle\oradata\orcl\rbs01.dbf'

    Media Recovery Start

    Media Recovery Log

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

      Mem# 0 errs 0: E:\ORACLE\ORADATA\ORCL\REDO02.LOG

    Media Recovery failed with error 1115

    ORA-283 signalled during: ALTER DATABASE RECOVER  datafile 'e:\oracle\oradat...

    Thu Jul 31 09:14:48 2014

    继续恢复TEMP01.DBF 文件:

    SQL>recover datafile 'E:\ORACLE\ORADATA\ORCL\TEMP01.DBF';

    出错:

    ALTER DATABASE RECOVER  datafile 'E:\ORACLE\ORADATA\ORCL\TEMP01.DBF' 

    Thu Jul 31 09:15:13 2014

    Media Recovery Datafile: 'E:\ORACLE\ORADATA\ORCL\TEMP01.DBF'

    Media Recovery Start

    Media Recovery Log

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

      Mem# 0 errs 0: E:\ORACLE\ORADATA\ORCL\REDO02.LOG

    Media recovery buffers written to disk due to log corruption.媒介损坏

    Some changes at scn 13114573485700 may be on disk

    Media Recovery failed with error 355

    ORA-283 signalled during: ALTER DATABASE RECOVER  datafile 'E:\ORACLE\ORADAT...

    Thu Jul 31 09:16:12 2014

     

    SQL>recover datafile 'E:\ORACLE\ORADATA\ORCL\USERS01.DBF';

    完成介质恢复。

    SQL>recover datafile 'E:\ORACLE\ORADATA\ORCL\TEMP01.DBF';

    SQL>recover datafile 'E:\ORACLE\ORADATA\ORCL\INDX01.DBF';

    完成介质恢复。

    SQL>recover datafile 'E:\ORACLE\ORADATA\ORCL\DR01.DBF';

    完成介质恢复。

    SQL>recover datafile 'E:\ORACLE\ORADATA\ORCL\TOOLS01.DBF';

    发现恢复TOOLS01.DBF';提示也有坏块

    ALTER DATABASE RECOVER  datafile 'E:\ORACLE\ORADATA\ORCL\TOOLS01.DBF' 

    Thu Jul 31 09:16:12 2014

    Media Recovery Datafile: 'E:\ORACLE\ORADATA\ORCL\TOOLS01.DBF'

    Media Recovery Start

    Media Recovery Log

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

      Mem# 0 errs 0: E:\ORACLE\ORADATA\ORCL\REDO02.LOG

    Media recovery buffers written to disk due to log corruption.

    Some changes at scn 13114573485700 may be on disk

    Media Recovery failed with error 355

    ORA-283 signalled during: ALTER DATABASE RECOVER  datafile 'E:\ORACLE\ORADAT...

    Thu Jul 31 09:16:37 2014

     

    于是干脆摘除该文件:

    SQL> alter database datafile 'E:\ORACLE\ORADATA\ORCL\TOOLS01.DBF' offline drop;

    再次尝试打开数据库,使用和不用resetlogs选项:

    SQL> alter database open resetlogs;

    Thu Jul 31 09:31:12 2014

    ORA-1139 signalled during: alter database open resetlogs

    ...

    Thu Jul 31 09:31:24 2014

    alter database open

    Thu Jul 31 09:31:24 2014

    ORA-1113 signalled during: alter database open

     

    还是不行,于是接下来处理联机日志的问题,按照联机日志文件都丢掉处理:

    SQL>Select * from v$logfile;

    SQL>Select * from v$log; --2号日志为当前日志

    SQL> select * from v$log;

        GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME                   

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

             1          1          0    1048576          1 YES UNUSED                       0                              

             2          1          1    1048576          1 NO  CURRENT             1.3115E+13 31-7?  -14                   

             3          1          0    1048576          1 YES UNUSED                       0      

    清除 1 3号日志:

    SQL> alter database clear unarchived logfile group 3;

    数据库已更改。

    SQL> alter database clear unarchived logfile group 1;

    数据库已更改。

    SQL> recover database until cancel;

    ORA-00279: 更改 13114573465651 ( 07/03/2014 16:40:00 生成) 对于线程 1 是必需的

    ORA-00289: 建议: E:\ORACLE\ORA81\RDBMS\ARC07253.001

    ORA-00280: 更改 13114573465651 对于线程 1 是按序列 # 7253 进行的

    指定日志: {=suggested | filename | AUTO | CANCEL}

    此处输入:auto

    ORA-00308: 无法打开存档日志 'E:\ORACLE\ORA81\RDBMS\ARC07253.001'

    ORA-27041: 无法打开文件

    OSD-04002: 无法打开文件

    O/S-Error: (OS 2) 系统找不到指定的文件。

    ORA-01547: 警告: RECOVER 成功但 OPEN RESETLOGS 将出现如下错误

    ORA-01152: 文件 1 没有从完备的旧备份中恢复

    ORA-01110: 数据文件 1: 'E:\ORACLE\ORADATA\ORCL\SYSTEM01.DBF'

    SQL> recover database until cancel;

    ORA-00279: 更改 13114573465651 ( 07/03/2014 16:40:00 生成) 对于线程 1 是必需的

    ORA-00289: 建议: E:\ORACLE\ORA81\RDBMS\ARC07253.001

    ORA-00280: 更改 13114573465651 对于线程 1 是按序列 # 7253 进行的

    指定日志: {=suggested | filename | AUTO | CANCEL}

    此处输入:cancel

    ORA-01547: 警告: RECOVER 成功但 OPEN RESETLOGS 将出现如下错误

    ORA-01152: 文件 1 没有从完备的旧备份中恢复

    ORA-01110: 数据文件 1: 'E:\ORACLE\ORADATA\ORCL\SYSTEM01.DBF'

    ORA-01112: 未启动媒体恢复

    SQL> alter database open resetlogs;

    alter database open resetlogs

    *

    ERROR 位于第 1 :

    ORA-01152: 文件 1 没有从完备的旧备份中恢复

    ORA-01110: 数据文件 1: 'E:\ORACLE\ORADATA\ORCL\SYSTEM01.DBF'

    SQL> shutdown immediate;

    ORA-01109: 数据库未打开

    已经卸载数据库。

    ORACLE 例程已经关闭。

    增加隐含参数到INIT.ORA文件中:

    _allow_resetlogs_corrupton = true

    _corrupted_rollback_segments = (rbs0,rbs1,rbs2,rbs3,rbs3,rbs4,rbs5,rbs6)

    重启数据库:

    SQL> startup mount;

    ORACLE 例程已经启动。

    Total System Global Area  237856796 bytes                                                                              

    Fixed Size                    75804 bytes                                                                               

    Variable Size              80416768 bytes                                                                              

    Database Buffers          157286400 bytes                                                                               

    Redo Buffers                  77824 bytes                                                                              

    数据库装载完毕。

    SQL> alter database open;

    alter database open

    *

    ERROR 位于第 1 :

    ORA-01589: ??????????? RESETLOGS ? NORESETLOGS ??

    ORA-1589要打开数据库则必须使用 RESETLOGS 或 NORESETLOGS 选项

    SQL> alter database open reserlogs;

    alter database open reserlogs

                        *

    ERROR 位于第 1 :

    ORA-02288: ??? OPEN ??

    SQL> alter database open resetlogs;

    alter database open resetlogs

    *

    ERROR 位于第 1 :

    ORA-00604: ?? SQL ? 1 ????

    ORA-01555: ????: ????  ???? "" ??

                           

    SQL> shutdown immediate;

    ORA-01109: ??????

    已经卸载数据库。

    ORACLE 例程已经关闭。

    SQL> startup mount;

    ORACLE 例程已经启动。

    Total System Global Area  237856796 bytes                                                                              

    Fixed Size                    75804 bytes                                                                               

    Variable Size              80416768 bytes                                                                              

    Database Buffers          157286400 bytes                                                                               

    Redo Buffers                  77824 bytes                                                                              

    数据库装载完毕。

    SQL> alter database open resetlogs;

    alter database open resetlogs

    *

    ERROR 位于第 1 :

    ORA-01139: RESETLOGS ???????????????

    SQL> alter database open;

    alter database open

    *

    ERROR 位于第 1 :

    ORA-00600: ??????????: [3668], [1], [2], [12057], [12057], [4], [], []

    ORCL服务重新启动几次,再次运行命令:

    SQL> recover database;

    完成介质恢复。

    SQL> alter database open;

    数据库已更改。

    查看ALERT文件,终于打开数据库了:

    Thu Jul 31 09:58:55 2014

    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 13114573465651

    Thu Jul 31 09:58:56 2014

    Thread 1 opened at log sequence 1

      Current log# 2 seq# 1 mem# 0: E:\ORACLE\ORADATA\ORCL\REDO02.LOG

    Successful open of redo thread 1.

    Thu Jul 31 09:58:56 2014

    SMON: enabling cache recovery

    SMON: disabling cache recovery

    Thu Jul 31 09:58:56 2014

    ORA-604 signalled during: alter database open resetlogs

    ...

    Thu Jul 31 10:01:00 2014

    Shutting down instance (immediate)

    SQL> conn test/test;

    已连接。

    SQL>

     

        数据库能够打开后,赶紧备份数据,但是发现应用进去后,没有任何操作菜单显示出来,联系应用的人,跟踪下应用,并把相应的错误信息显示出来,才发现是TEMP无法使用回滚段的错误。

        将上述两个隐含参数去掉,重新启动数据库,用DBA  STUDIO,忘记TEMP表空间的数据文件是否脱机了。重建了一个类似的临时表空间TEMP2,摘除原有的TEMP 表空间。但是select * from v$tempfile;显示未选定行,在DBA  STUDIO中可正常显示相应的数据文件。

        我开始给TEMP 表空间新增了一个数据文件TEMP02.DBF,然后尝试去摘除原来的数据文件TEMP01DBF,出错!

        接着发现现有回滚段RBS0RBS1RBS2RBS3RBS4RBS5RBS6都是脱机状态,于是重建RBS10RBS11RBS12RBS13RBS14RBS15RBS16新的回滚段,按照系统原有的RBS0等回滚段的参数,512  512  4096:

    CREATE ROLLBACK SEGMENT "RBS10"

        TABLESPACE "RBS"

        STORAGE ( INITIAL 512K NEXT 512K MAXEXTENTS 4096);

    ALTER ROLLBACK SEGMENT "RBS10" ONLINE;

     

    CREATE ROLLBACK SEGMENT "RBS11"

        TABLESPACE "RBS"

        STORAGE ( INITIAL 512K NEXT 512K MAXEXTENTS 4096);

    ALTER ROLLBACK SEGMENT "RBS11" ONLINE;

     

    CREATE ROLLBACK SEGMENT "RBS12"

        TABLESPACE "RBS"

        STORAGE ( INITIAL 512K NEXT 512K MAXEXTENTS 4096);

    ALTER ROLLBACK SEGMENT "RBS12" ONLINE;

     

    CREATE ROLLBACK SEGMENT "RBS13"

        TABLESPACE "RBS"

        STORAGE ( INITIAL 512K NEXT 512K MAXEXTENTS 4096);

    ALTER ROLLBACK SEGMENT "RBS13" ONLINE;

     

    CREATE ROLLBACK SEGMENT "RBS14"

        TABLESPACE "RBS"

        STORAGE ( INITIAL 512K NEXT 512K MAXEXTENTS 4096);

    ALTER ROLLBACK SEGMENT "RBS14" ONLINE;

     

    CREATE ROLLBACK SEGMENT "RBS15"

        TABLESPACE "RBS"

        STORAGE ( INITIAL 512K NEXT 512K MAXEXTENTS 4096);

    ALTER ROLLBACK SEGMENT "RBS15" ONLINE;

     

    CREATE ROLLBACK SEGMENT "RBS16"

        TABLESPACE "RBS"

        STORAGE ( INITIAL 512K NEXT 512K MAXEXTENTS 4096);

     

    然后摘除RBS0RBS1RBS2RBS3RBS4RBS5RBS6等回滚段(已经是脱机状态),相应命令忘记了,我用的DBA STUTIO

    --ALTER ROLLBACK SEGMENT "RBS0" OFFLINE;

    drop rollback segment "RBS0";

    drop rollback segment "RBS1";

    drop rollback segment "RBS2";

    drop rollback segment "RBS3";

    drop rollback segment "RBS4";

    drop rollback segment "RBS5";

    drop rollback segment "RBS6";

     

     

        修改init.ora文件,将参数rollback_Segments=( RBS10RBS11RBS12RBS13RBS14RBS15RBS16),原来为rollback_Segments=( RBS0RBS1RBS2RBS3RBS4RBS5RBS6)

    重新关闭数据库,打开数据库,O了。

     

    来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7177735/viewspace-1253657/,如需转载,请注明出处,否则将追究法律责任。

    转载于:http://blog.itpub.net/7177735/viewspace-1253657/

    展开全文
  • 目的:在临时表空间数据文件损坏的前提下,进行临时文件的恢复,更确切的说是修复 临时数据文件的损坏或丢失会造成需要使用临时表空间的命令执行失败,但不会造成实例崩溃。由于临时表空间存放的是临时数据,...
    目的:在临时表空间的数据文件损坏的前提下,进行临时文件的恢复,更确切的说是修复

    临时数据文件的损坏或丢失会造成需要使用临时表空间的命令执行失败,但不会造成实例崩溃。由于临时表空间存放的是临时数据,RMAN不会对其进行备份,一旦损坏采用的恢复方法是重建或替换
    如果在数据库运行过程中,发现临时数据文件损坏或丢失,可以采用替换的方法恢复,不需要重启数据库:
    如出现下列的错误:
    SQL> select * from v$tempfile;
    select * from v$tempfile
    *
    ERROR at line 1:
    ORA-00600: internal error code, arguments: [krhcvt_filhdr_v10_01], [], [], [], [], [], [], []
    恢复步骤如下:

    1.向临时表空间中添加新的临时数据文件
    SQL> alter tablespace temp add tempfile '/u01/oradata/radius/temp02.dbf' size 100M;

    Tablespace altered.
    2.删除掉损坏的临时文件
    SQL> alter tablespace temp drop tempfile '/u01/oradata/radius/temp01.dbf';

    Tablespace altered.
    3.重新进行一样的查询,来确认是否临时表空间内的数据文件修复成功,从而可以让查询使用临时表空间
    SQL> select file#, name from v$tempfile;

    FILE# NAME
    ---------- ---------------------------------------------
    2 /u01/oradata/radius/temp02.dbf
    4.修复成功

    另外,如果在启动实例的情况下,发现临时文件丢失了数据库会自动重建这个临时文件,用户不会有任何的错误信息提示,但是在alert日志中,有相关提示:
    如:
    启动实例,没有任何的错误提示:
    SQL> startup
    ORACLE instance started.

    Total System Global Area 599785472 bytes
    Fixed Size 2085776 bytes
    Variable Size 163581040 bytes
    Database Buffers 427819008 bytes
    Redo Buffers 6299648 bytes
    Database mounted.
    Database opened.
    监控警告日志alert_radius.log,如下:
    Wed Sep 3 18:50:01 2014
    Re-creating tempfile /u01/oradata/radius/temp02.dbf
    Database Characterset is US7ASCII
    Opening with internal Resource Manager plan
    where NUMA PG = 1, CPUs = 1
    replication_dependency_tracking turned off (no async multimaster replication found)
    Starting background process QMNC
    QMNC started with pid=22, OS id=4637
    Wed Sep 3 18:50:06 2014
    db_recovery_file_dest_size of 2048 MB is 0.69% 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.
    Wed Sep 3 18:50:06 2014
    Completed: ALTER DATABASE OPEN
    【说明】
    Re-creating tempfile /u01/oradata/radius/temp02.dbf 代表了重新创建临时文件

    如果,在启动实例时,监测到临时文件损坏,而不是丢失,因为这个文件的存在,不能重新创建同名的文件,启动时没有任何的错误提示,但是在警告日志中,会有下列的提示:
    Cannot re-create tempfile /u01/oradata/radius/temp02.dbf, the same name file exists
    Wed Sep 3 18:56:26 2014
    Errors in file /u01/app/oracle/admin/radius/bdump/radius_dbw0_4679.trc:
    ORA-01157: cannot identify/lock data file 202 - see DBWR trace file
    ORA-01110: data file 202: '/u01/oradata/radius/temp02.dbf'
    ORA-27046: file size is not a multiple of logical block size
    Additional information: 1
    Database Characterset is US7ASCII
    ARC5 started with pid=21, OS id=4711
    Wed Sep 3 18:56:26 2014
    Opening with internal Resource Manager plan
    where NUMA PG = 1, CPUs = 1
    replication_dependency_tracking turned off (no async multimaster replication found)
    Starting background process QMNC
    QMNC started with pid=22, OS id=4713
    Wed Sep 3 18:56:26 2014
    db_recovery_file_dest_size of 2048 MB is 0.69% 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.
    Wed Sep 3 18:56:26 2014
    Completed: ALTER DATABASE OPEN
    【说明】
    临时文件重建失败,为了不在数据库的整个运行过程中,出现问题,需要关闭实例,删除损坏的临时文件,重新启动实例,就会重新创建出一个同名的临时文件,避免在数据库正常运行的过程中,出现问题。

    --END--




    展开全文
  • 普通数据文件损坏恢复实验记录(有备份的情况)前些天写了《关键数据文件损坏恢复实验记录》,今天就看一下普通数据文件损坏恢复的情况。普通数据文件包括:非关键数据文件、非临时表空间和只读表空间的数据文件,普通...

    c2d07298c37dde11ba23780216906c1e.png

    普通数据文件损坏恢复实验记录(有备份的情况)

    前些天写了《关键数据文件损坏恢复实验记录》,今天就看一下普通数据文件损坏恢复的情况。

    普通数据文件包括:非关键数据文件、非临时表空间和只读表空间的数据文件,普通文件的损坏不会导致数据库实例崩溃、也没有数据库不恢复就无法启动的说法,顶多会影响用户数据不可访问。

    我们可以在OPEN状态下进行恢复普通数据文件。

    sys@MAA> select name from v$datafile where file# not in (1,3);

    NAME

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

    +MSDATA/maa/datafile/sysaux.261.792009871

    +MSDATA/maa/datafile/users.264.792009897

    +MSDATA/maa/datafile/l.266.798569345

    +MSDATA/maa/datafile/l.267.798570165

    +MSDATA/maa/datafile/l.268.798572257

    本文讨论有备份情况下的普通数据文件恢复

    RMAN> backup tablespace luocs;

    Starting backup at 21-DEC-2012 07:53:26

    using target database control file instead of recovery catalog

    allocated channel: ORA_DISK_1

    channel ORA_DISK_1: SID=323 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=00035 name=/u01/app/oracle/oradata/SA7N67B11G/luocs02.dbf

    input datafile file number=00034 name=/u01/app/oracle/oradata/SA7N67B11G/luocs01.dbf

    channel ORA_DISK_1: starting piece 1 at 21-DEC-2012 07:53:31

    channel ORA_DISK_1: finished piece 1 at 21-DEC-2012 07:53:35

    piece handle=/u01/app/oracle/product/11.2.0.1/dbhome_1/dbs/01ntdb3r_1_1 tag=TAG20121221T075331 comment=NONE

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

    Finished backup at 21-DEC-2012 07:53:35

    操作环境:

    • OS : CentOS Linux 5.6 64Bit

    • DB Type : Single Instance Database

    • DB Version : 11.2.0.1

    下面我构造下面几个场景:

    1)非数据文件头部损坏的场景

    2)数据文件头部损坏的场景

    场景1:制造数据坏块儿,非文件头部损坏

    sys@SA7N67B11G> col FILE_NAME for a65

    sys@SA7N67B11G> select file_id, file_name from dba_data_files where tablespace_name='LUOCS';

    FILE_ID FILE_NAME

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

    34 /u01/app/oracle/oradata/SA7N67B11G/luocs01.dbf

    35 /u01/app/oracle/oradata/SA7N67B11G/luocs02.dbf

    -- 我的系统里有LUOCS用户,其默认表空间为LUOCS

    luocs用户创建表,灌入数据

    luocs@SA7N67B11G> create table ltb(id number, name varchar2(200));

    luocs@SA7N67B11G> insert into ltb select object_id, object_name from all_objects;

    luocs@SA7N67B11G> commit;

    查询一条记录

    luocs@SA7N67B11G> select rowid, a.* from ltb a where name='LTB';

    ROWID ID NAME

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

    AAAW+0AAjAAAAIMAC9 94132 LTB

    利用下面脚本,我们创建一个通过ROWID查找其记录的数据存储信息

    create or replace function get_rowid(l_rowid in varchar2) return varchar2 is

    ls_my_rowid varchar2(200);

    rowid_type number;

    object_number number;

    relative_fno number;

    block_number number;

    row_number number;

    begin

    dbms_rowid.rowid_info(l_rowid,

    rowid_type,

    object_number,

    relative_fno,

    block_number,

    row_number);

    ls_my_rowid := 'Row_id type is :' || to_char(rowid_type) || chr(10) ||

    'Object# is :' || to_char(object_number) || chr(10) ||

    'Relative_fno is :' || to_char(relative_fno) || chr(10) ||

    'Block number is :' || to_char(block_number) || chr(10) ||

    'Row number is :' || to_char(row_number);

    return ls_my_rowid;

    end;

    /

    luocs@SA7N67B11G> select get_rowid('AAAW+0AAjAAAAIMAC9') ROW_ID from dual;

    ROW_ID

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

    Row_id type is :1

    Object# is :94132

    Relative_fno is :35

    Block number is :524

    Row number is :189

    -- ID为94132的记录在35号文件524号块儿

    我们通过BBED工具破坏524号块儿

    [oracle@localhost ~]$ cat bbed.par

    34 /u01/app/oracle/oradata/SA7N67B11G/luocs01.dbf

    35 /u01/app/oracle/oradata/SA7N67B11G/luocs02.dbf

    [oracle@localhost lib]$ bbed listfile=/home/oracle/bbed.par blocksize=8192 mode=edit

    Password:

    BBED: Release 2.0.0.0.0 - Limited Production on Fri Dec 21 08:02:33 2012

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

    ************* !!! For Oracle Internal Use only !!! ***************

    BBED> info

    File# Name Size(blks)

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

    34 /u01/app/oracle/oradata/SA7N67B11G/luocs01.dbf 0

    35 /u01/app/oracle/oradata/SA7N67B11G/luocs02.dbf 0

    BBED> show

    FILE# 34

    BLOCK# 1

    OFFSET 0

    DBA 0x08800001 (142606337 34,1)

    FILENAME /u01/app/oracle/oradata/SA7N67B11G/luocs01.dbf

    BIFILE bifile.bbd

    LISTFILE /home/oracle/bbed.par

    BLOCKSIZE 8192

    MODE Edit

    EDIT Unrecoverable

    IBASE Dec

    OBASE Dec

    WIDTH 80

    COUNT 512

    LOGFILE log.bbd

    SPOOL No

    目前是没有坏块儿的

    BBED> verify

    DBVERIFY - Verification starting

    FILE = /u01/app/oracle/oradata/SA7N67B11G/luocs01.dbf

    BLOCK = 1

    DBVERIFY - Verification complete

    Total Blocks Examined : 1

    Total Blocks Processed (Data) : 0

    Total Blocks Failing (Data) : 0

    Total Blocks Processed (Index): 0

    Total Blocks Failing (Index): 0

    Total Blocks Empty : 0

    Total Blocks Marked Corrupt : 0

    Total Blocks Influx : 0

    Message 531 not found; product=RDBMS; facility=BBED

    我强制copy块儿

    BBED> copy file 34 block 235 to file 35 block 524

    Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) Y

    File: /u01/app/oracle/oradata/SA7N67B11G/luocs02.dbf (35)

    Block: 524 Offsets: 0 to 511 Dba:0x08c0020c

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

    06a20000 eb008008 0df62301 00000104 aa060000 01000000 d8640100 0df62301

    00000000 03003201 e0008008 ffff0000 00000000 00000000 00000000 00800000

    c0d92301 0a001a00 c52f0100 bd04c000 093b1b00 00a00000 c4f32301 00000000

    00000000 00000000 00000000 00000000 00000000 00000000 00000000 00015100

    0000b400 1104cc1e cc1e0000 51000100 02000300 04000500 06000700 08000900

    0a000b00 0c000d00 0e000f00 10001100 12001300 14001500 16001700 18001900

    1a001b00 1c001d00 1e001f00 20002100 22002300 24002500 26002700 28002900

    2a002b00 2c002d00 2e002f00 30003100 32003300 34003500 36003700 38003900

    3a003b00 3c003d00 3e003f00 40004100 42004300 44004500 46004700 48004900

    4a004b00 4c004d00 4e004f00 5000ffff 00000000 00000000 00000000 00000000

    00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

    00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

    00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

    00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

    00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

    00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

    <32 bytes per line>

    BBED> sum apply

    Check value for File 35, Block 524:

    current = 0x06aa, required = 0x06aa

    这时候检查到有一个数据坏块儿了

    BBED> verify

    DBVERIFY - Verification starting

    FILE = /u01/app/oracle/oradata/SA7N67B11G/luocs02.dbf

    BLOCK = 524

    Block 524 is corrupt

    Corrupt block relative dba: 0x0880020c (file 0, block 524)

    Bad header found during verification

    Data in bad block:

    type: 6 format: 2 rdba: 0x088000eb

    last change scn: 0x0000.0123f60d seq: 0x1 flg: 0x04

    spare1: 0x0 spare2: 0x0 spare3: 0x0

    consistency value in tail: 0xf60d0601

    check value in block header: 0x6aa

    computed block checksum: 0x0

    DBVERIFY - Verification complete

    Total Blocks Examined : 1

    Total Blocks Processed (Data) : 0

    Total Blocks Failing (Data) : 0

    Total Blocks Processed (Index): 0

    Total Blocks Failing (Index): 0

    Total Blocks Empty : 0

    Total Blocks Marked Corrupt : 1

    Total Blocks Influx : 0

    Message 531 not found; product=RDBMS; facility=BBED

    为了看到效果,我们需要刷新高速缓冲区

    sys@SA7N67B11G> alter system flush buffer_cache;

    System altered.

    我们查该块儿上的记录的时候会报错

    luocs@SA7N67B11G> select rowid, a.* from ltb a where name='LTB';

    select rowid, a.* from ltb a where name='LTB'

    *

    ERROR at line 1:

    ORA-01578: ORACLE data block corrupted (file # 35, block # 524)

    ORA-01110: data file 35: '/u01/app/oracle/oradata/SA7N67B11G/luocs02.dbf'

    告警日志输出:

    Hex dump of (file 35, block 524) in trace file /u01/app/oracle/diag/rdbms/SA7N67B11G/SA7N67B11G/trace/luocs11g_ora_18320.trc

    Corrupt block relative dba: 0x08c0020c (file 35, block 524)

    Bad header found during multiblock buffer read

    Data in bad block:

    type: 6 format: 2 rdba: 0x0880020c

    last change scn: 0x0000.0123f60d seq: 0x1 flg: 0x04

    spare1: 0x0 spare2: 0x0 spare3: 0x0

    consistency value in tail: 0xf60d0601

    check value in block header: 0xc245

    computed block checksum: 0x0

    Reading datafile '/u01/app/oracle/oradata/SA7N67B11G/luocs02.dbf' for corruption at rdba: 0x08c0020c (file 35, block 524)

    Reread (file 35, block 524) found same corrupt data

    Fri Dec 21 08:33:29 2012

    Corrupt Block Found

    TSN = 36, TSNAME = LUOCS

    RFN = 35, BLK = 524, RDBA = 146801164

    OBJN = 94132, OBJD = 94132, OBJECT = LTB, SUBOBJECT =

    SEGMENT OWNER = LUOCS, SEGMENT TYPE = Table Segment

    Errors in file /u01/app/oracle/diag/rdbms/SA7N67B11G/SA7N67B11G/trace/luocs11g_ora_18320.trc (incident=32441):

    ORA-01578: ORACLE data block corrupted (file # 35, block # 524)

    ORA-01110: data file 35: '/u01/app/oracle/oradata/SA7N67B11G/luocs02.dbf'

    Incident details in: /u01/app/oracle/diag/rdbms/SA7N67B11G/SA7N67B11G/incident/incdir_32441/luocs11g_ora_18320_i32441.trc

    Fri Dec 21 08:33:32 2012

    Sweep [inc][32441]: completed

    Hex dump of (file 35, block 524) in trace file /u01/app/oracle/diag/rdbms/SA7N67B11G/SA7N67B11G/incident/incdir_32441/luocs11g_m000_20721_i32441_a.trc

    Corrupt block relative dba: 0x08c0020c (file 35, block 524)

    Bad header found during validation

    Data in bad block:

    type: 6 format: 2 rdba: 0x0880020c

    last change scn: 0x0000.0123f60d seq: 0x1 flg: 0x04

    spare1: 0x0 spare2: 0x0 spare3: 0x0

    consistency value in tail: 0xf60d0601

    check value in block header: 0xc245

    computed block checksum: 0x0

    Reread of blocknum=524, file=/u01/app/oracle/oradata/SA7N67B11G/luocs02.dbf. found same corrupt data

    Reread of blocknum=524, file=/u01/app/oracle/oradata/SA7N67B11G/luocs02.dbf. found same corrupt data

    Reread of blocknum=524, file=/u01/app/oracle/oradata/SA7N67B11G/luocs02.dbf. found same corrupt data

    Reread of blocknum=524, file=/u01/app/oracle/oradata/SA7N67B11G/luocs02.dbf. found same corrupt data

    Reread of blocknum=524, file=/u01/app/oracle/oradata/SA7N67B11G/luocs02.dbf. found same corrupt data

    Fri Dec 21 08:33:32 2012

    Trace dumping is performing id=[cdmp_20121221083332]

    Fri Dec 21 08:33:32 2012

    Sweep [inc2][32441]: completed

    普通数据文件的坏块儿不会导致实例崩溃

    sys@SA7N67B11G> select status from v$instance;

    STATUS

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

    OPEN

    恢复方法非常简单,因为我们有备份文件,所以利用RMAN块恢复功能针对损坏块儿进行恢复就可以了。

    RMAN> recover datafile 35 block 524;

    Starting recover at 21-DEC-2012 08:38:03

    using target database control file instead of recovery catalog

    allocated channel: ORA_DISK_1

    channel ORA_DISK_1: SID=323 device type=DISK

    channel ORA_DISK_1: restoring block(s)

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

    restoring blocks of datafile 00035

    channel ORA_DISK_1: reading from backup piece /u01/app/oracle/product/11.2.0.1/dbhome_1/dbs/02ntdbhq_1_1

    channel ORA_DISK_1: piece handle=/u01/app/oracle/product/11.2.0.1/dbhome_1/dbs/02ntdbhq_1_1 tag=TAG20121221T080058

    channel ORA_DISK_1: restored block(s) from backup piece 1

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

    starting media recovery

    media recovery complete, elapsed time: 00:00:04

    Finished recover at 21-DEC-2012 08:38:12

    再次查看

    sys@SA7N67B11G> alter system flush buffer_cache;

    System altered.

    luocs@SA7N67B11G> select rowid, a.* from ltb a where name='LTB';

    ROWID ID NAME

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

    AAAW+0AAjAAAAIMAC9 94132 LTB

    OK,数据访问正常。

    下面我再用dd方式来制造数据坏块儿

    上面我们通过get_rowid函数定位了ID 为94132的记录在35号数据文件的524号块上。

    我用BBED找出偏移字节位

    BBED> set file 35 block 524

    FILE# 35

    BLOCK# 524

    BBED> find /c LTB

    File: /u01/app/oracle/oradata/SA7N67B11G/luocs02.dbf (35)

    Block: 524 Offsets: 4993 to 5504 Dba:0x08c0020c

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

    4c54422c 010204c3 08121619 41504558 5f415050 4c494341 54494f4e 5f504147

    455f5250 542c0102 04c30812 171e4150 45585f41 50504c49 43415449 4f4e5f50

    4147455f 5250545f 434f4c53 2c010204 c3081218 1e415045 585f4150 504c4943

    4154494f 4e5f5041 47455f42 52414e43 4845532c 010204c3 0812191a 41504558

    5f415050 4c494341 54494f4e 5f504147 455f5052 4f432c01 0204c308 121a1a41

    5045585f 4150504c 49434154 494f4e5f 50414745 5f434f4d 502c0102 04c30812

    1b194150 45585f41 50504c49 43415449 4f4e5f50 4147455f 56414c2c 010204c3

    08121c1d 41504558 5f415050 4c494341 54494f4e 5f504147 455f4255 54544f4e

    532c0102 04c30812 1d1e4150 45585f41 50504c49 43415449 4f4e5f42 55494c44

    5f4f5054 494f4e53 2c010204 c308121e 1a415045 585f4150 504c4943 4154494f

    4e5f5445 4d504c41 5445532c 010204c3 08121f15 41504558 5f415050 4c494341

    54494f4e 5f544142 532c0102 04c30812 201c4150 45585f41 50504c49 43415449

    4f4e5f50 4152454e 545f5441 42532c01 0204c308 12211641 5045585f 4150504c

    49434154 494f4e5f 4954454d 532c0102 04c30812 221a4150 45585f41 50504c49

    43415449 4f4e5f50 524f4345 53534553 2c010204 c3081223 1d415045 585f4150

    504c4943 4154494f 4e5f434f 4d505554 4154494f 4e532c01 0204c308 12241a41

    <32 bytes per line>

    OK,我们获得偏移字节位4993,这里B(LTB表名称里的B,16进制42)的精确位置为:

    luocs@SA7N67B11G> select 8192*524+4995 from dual;

    8192*524+4995

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

    4297603

    我就用dd把B对应的42修改为4f(O的16进制)就达到了破坏目的。

    首先我用下面的方法生成内容为4f的二进制文件x

    [oracle@localhost ~]$ dd if=/dev/zero of=x bs=1 count=1

    [oracle@localhost ~]$ hexdump x

    0000000 0000

    0000001

    二进制文件强制编辑

    [oracle@localhost ~]$ vi -b x

    进入命令模式(shift+:),输入%!xxd,回车,会显示

    0000000: 00

    把最后两个00替换成4f

    再进入命令模式(shift+:),输入%!xxd -r,回车

    :wq退出

    [oracle@localhost ~]$ hexdump x

    0000000 004f

    0000001

    然后我使用x将4297604字节"42"修改成"4f"

    [oracle@localhost ~]$ dd if=x of=/u01/app/oracle/oradata/SA7N67B11G/luocs02.dbf bs=1 count=1 seek=4297603 conv=notrunc

    1+0 records in

    1+0 records out

    1 byte (1 B) copied, 2.9052e-05 seconds, 34.4 kB/s

    拿DBV工具检查,发现有一个数据坏块儿

    [oracle@localhost ~]$ dbv file=/u01/app/oracle/oradata/SA7N67B11G/luocs02.dbf

    DBVERIFY: Release 11.2.0.1.0 - Production on Fri Dec 21 12:38:53 2012

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

    DBVERIFY - Verification starting : FILE = /u01/app/oracle/oradata/SA7N67B11G/luocs02.dbf

    Page 524 is marked corrupt

    Corrupt block relative dba: 0x08c0020c (file 35, block 524)

    Bad check value found during dbv:

    Data in bad block:

    type: 6 format: 2 rdba: 0x08c0020c

    last change scn: 0x0000.01632245 seq: 0x1 flg: 0x04

    spare1: 0x0 spare2: 0x0 spare3: 0x0

    consistency value in tail: 0x22450601

    check value in block header: 0xa92e

    computed block checksum: 0xd00

    DBVERIFY - Verification complete

    Total Pages Examined : 64000

    Total Pages Processed (Data) : 1220

    Total Pages Failing (Data) : 0

    Total Pages Processed (Index): 1

    Total Pages Failing (Index): 0

    Total Pages Processed (Other): 62766

    Total Pages Processed (Seg) : 0

    Total Pages Failing (Seg) : 0

    Total Pages Empty : 12

    Total Pages Marked Corrupt : 1

    Total Pages Influx : 0

    Total Pages Encrypted : 0

    Highest block SCN : 23319185 (0.23319185)

    BBED里也只能找到LTO(原位LTB)

    BBED> find /c LTO

    File: /u01/app/oracle/oradata/SA7N67B11G/luocs02.dbf (35)

    Block: 524 Offsets: 4993 to 5504 Dba:0x08c0020c

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

    4c544f2c 000204c3 08121619 41504558 5f415050 4c494341 54494f4e 5f504147

    455f5250 542c0002 04c30812 171e4150 45585f41 50504c49 43415449 4f4e5f50

    4147455f 5250545f 434f4c53 2c000204 c3081218 1e415045 585f4150 504c4943

    4154494f 4e5f5041 47455f42 52414e43 4845532c 000204c3 0812191a 41504558

    5f415050 4c494341 54494f4e 5f504147 455f5052 4f432c00 0204c308 121a1a41

    5045585f 4150504c 49434154 494f4e5f 50414745 5f434f4d 502c0002 04c30812

    1b194150 45585f41 50504c49 43415449 4f4e5f50 4147455f 56414c2c 000204c3

    08121c1d 41504558 5f415050 4c494341 54494f4e 5f504147 455f4255 54544f4e

    532c0002 04c30812 1d1e4150 45585f41 50504c49 43415449 4f4e5f42 55494c44

    5f4f5054 494f4e53 2c000204 c308121e 1a415045 585f4150 504c4943 4154494f

    4e5f5445 4d504c41 5445532c 000204c3 08121f15 41504558 5f415050 4c494341

    54494f4e 5f544142 532c0002 04c30812 201c4150 45585f41 50504c49 43415449

    4f4e5f50 4152454e 545f5441 42532c00 0204c308 12211641 5045585f 4150504c

    49434154 494f4e5f 4954454d 532c0002 04c30812 221a4150 45585f41 50504c49

    43415449 4f4e5f50 524f4345 53534553 2c000204 c3081223 1d415045 585f4150

    504c4943 4154494f 4e5f434f 4d505554 4154494f 4e532c00 0204c308 12241a41

    <32 bytes per line>

    恢复方法也是RMAN的块级别恢复,略!

    下面我构造段头损坏

    查看LTB表段头部信息

    sys@SA7N67B11G> select header_file, header_block from dba_segments where owner='LUOCS' and segment_name='LTB';

    HEADER_FILE HEADER_BLOCK

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

    35 258

    sys@SA7N67B11G> select

    2 (select name from v$datafile where file#=a.file_id) file_name,

    3 file_id,

    4 block_id,

    5 blocks

    6 from dba_extents a

    7 where owner='LUOCS' and segment_name='LTB'

    8 /

    FILE_NAME FILE_ID BLOCK_ID BLOCKS

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

    /u01/app/oracle/oradata/SA7N67B11G/luocs02.dbf 35 256 8

    /u01/app/oracle/oradata/SA7N67B11G/luocs02.dbf 35 264 8

    /u01/app/oracle/oradata/SA7N67B11G/luocs02.dbf 35 272 8

    /u01/app/oracle/oradata/SA7N67B11G/luocs02.dbf 35 280 8

    /u01/app/oracle/oradata/SA7N67B11G/luocs02.dbf 35 288 8

    /u01/app/oracle/oradata/SA7N67B11G/luocs02.dbf 35 296 8

    /u01/app/oracle/oradata/SA7N67B11G/luocs02.dbf 35 304 8

    /u01/app/oracle/oradata/SA7N67B11G/luocs02.dbf 35 312 8

    /u01/app/oracle/oradata/SA7N67B11G/luocs02.dbf 35 320 8

    /u01/app/oracle/oradata/SA7N67B11G/luocs02.dbf 35 328 8

    /u01/app/oracle/oradata/SA7N67B11G/luocs02.dbf 35 336 8

    /u01/app/oracle/oradata/SA7N67B11G/luocs02.dbf 35 344 8

    /u01/app/oracle/oradata/SA7N67B11G/luocs02.dbf 35 352 8

    /u01/app/oracle/oradata/SA7N67B11G/luocs02.dbf 35 360 8

    /u01/app/oracle/oradata/SA7N67B11G/luocs02.dbf 35 368 8

    /u01/app/oracle/oradata/SA7N67B11G/luocs02.dbf 35 376 8

    /u01/app/oracle/oradata/SA7N67B11G/luocs02.dbf 35 384 128

    /u01/app/oracle/oradata/SA7N67B11G/luocs02.dbf 35 512 128

    18 rows selected.

    从上面两个查询中我们可以获得"LTB"段的第一级位图块为256号,第二级位图块为257号,258号则是LTB表的段头。

    OK,我破坏段头,然后看报错提示。

    BBED> copy file 35 block 10 to file 35 block 258

    Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) Y

    File: /u01/app/oracle/oradata/SA7N67B11G/luocs02.dbf (35)

    Block: 258 Offsets: 0 to 511 Dba:0x08c00102

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

    1ea20000 0a00c008 80683101 00000104 70090000 23000000 80403600 00000000

    00000000 00f80000 00000000 00000000 00000000 00000000 00000000 00000000

    00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

    00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

    00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

    00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

    00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

    00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

    00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

    00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

    00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

    00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

    00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

    00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

    00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

    00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

    <32 bytes per line>

    BBED> verify

    DBVERIFY - Verification starting

    FILE = /u01/app/oracle/oradata/SA7N67B11G/luocs02.dbf

    BLOCK = 258

    Block 258 is corrupt

    Corrupt block relative dba: 0x08c00102 (file 0, block 258)

    Bad header found during verification

    Data in bad block:

    type: 30 format: 2 rdba: 0x08c0000a

    last change scn: 0x0000.01316880 seq: 0x1 flg: 0x04

    spare1: 0x0 spare2: 0x0 spare3: 0x0

    consistency value in tail: 0x68801e01

    check value in block header: 0x970

    computed block checksum: 0x0

    DBVERIFY - Verification complete

    Total Blocks Examined : 1

    Total Blocks Processed (Data) : 0

    Total Blocks Failing (Data) : 0

    Total Blocks Processed (Index): 0

    Total Blocks Failing (Index): 0

    Total Blocks Empty : 0

    Total Blocks Marked Corrupt : 1

    Total Blocks Influx : 0

    Message 531 not found; product=RDBMS; facility=BBED

    BBED> sum apply

    Check value for File 35, Block 258:

    current = 0x0970, required = 0x0970

    这次查询该表的时候就报错

    luocs@SA7N67B11G> select count(*) from LTB;

    select count(*) from LTB

    *

    ERROR at line 1:

    ORA-01578: ORACLE data block corrupted (file # 35, block # 258)

    ORA-01110: data file 35: '/u01/app/oracle/oradata/SA7N67B11G/luocs02.dbf'

    恢复方法依然是RMAN的块级别恢复:

    RMAN> recover datafile 35 block 258;

    场景2:制造数据坏块儿,文件头部损坏

    我们通过BBED修改目标数据文件头信息即可。

    [oracle@localhost lib]$ bbed listfile=/home/oracle/bbed.par blocksize=8192 mode=edit

    Password:

    BBED: Release 2.0.0.0.0 - Limited Production on Fri Dec 21 08:02:33 2012

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

    ************* !!! For Oracle Internal Use only !!! ***************

    BBED> dump /v

    File: /u01/app/oracle/oradata/SA7N67B11G/luocs01.dbf (34)

    Block: 1 Offsets: 0 to 511 Dba:0x08800001

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

    0ba20000 01008008 00000000 00000104 l ................

    d1f60000 00000000 0000200b 73078a57 l .......... .s..W

    4c554f43 53313147 a2e10100 00190000 l SA7N67B11G........

    00200000 22000300 00000000 00000000 l . .."...........

    00000000 00000000 00000000 00000000 l ................

    00000000 00000000 00000000 00000000 l ................

    08024000 92d82301 00000000 2ec2a42f l ..@...#......../

    04c6a42f fcf32301 00000000 00000000 l .../..#.........

    00000000 00000000 00000400 b8000000 l ................

    dbd5d62f b7000000 00000000 00000000 l .../............

    00000000 00000000 00000000 00000000 l ................

    00000000 00000000 00000000 00000000 l ................

    00000000 00000000 00000000 00000000 l ................

    00000000 00000000 00000000 00000000 l ................

    00000000 00000000 00000000 00000000 l ................

    00000000 00000000 00000000 00000000 l ................

    00000000 00000000 00000000 00000000 l ................

    00000000 00000000 00000000 00000000 l ................

    00000000 00000000 00000000 00000000 l ................

    00000000 00000000 00000000 00000000 l ................

    00000000 00000000 00000000 24000000 l ............$...

    05004c55 4f435300 00000000 00000000 l ..LUOCS.........

    00000000 00000000 00000000 00000000 l ................

    22000000 00000000 00000000 0db2d62f l "............../

    00000000 00000000 00000000 00000000 l ................

    00000000 00000000 00000000 00000000 l ................

    b6d0932e 38830b00 00000000 00000000 l ....8...........

    00000000 00000000 00000000 00000000 l ................

    00000000 00000000 00000000 00000000 l ................

    00000000 00000000 00000000 00000000 l ................

    00000000 8fc86301 00000000 5ed7d62f l ......c.....^../

    0100cdbf a8000000 6e170000 10000000 l ........n.......

    <16 bytes per line>

    强制修改

    BBED> m /c 罗成网·新闻资讯服务北大门

    Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) Y

    File: /u01/app/oracle/oradata/SA7N67B11G/luocs01.dbf (34)

    Block: 1 Offsets: 0 to 511 Dba:0x08800001

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

    7777772e 6c756f63 732e636f 6d000104 d1f60000 00000000 0000200b 73078a57

    4c554f43 53313147 a2e10100 00190000 00200000 22000300 00000000 00000000

    00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

    08024000 92d82301 00000000 2ec2a42f 04c6a42f fcf32301 00000000 00000000

    00000000 00000000 00000400 b8000000 dbd5d62f b7000000 00000000 00000000

    00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

    00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

    00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

    00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

    00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

    00000000 00000000 00000000 24000000 05004c55 4f435300 00000000 00000000

    00000000 00000000 00000000 00000000 22000000 00000000 00000000 0db2d62f

    00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

    b6d0932e 38830b00 00000000 00000000 00000000 00000000 00000000 00000000

    00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

    00000000 8fc86301 00000000 5ed7d62f 0100cdbf a8000000 6e170000 10000000

    <32 bytes per line>

    BBED> sum apply

    Check value for File 34, Block 1:

    current = 0x5225, required = 0x5225

    BBED> verify

    DBVERIFY - Verification starting

    FILE = /u01/app/oracle/oradata/SA7N67B11G/luocs01.dbf

    BLOCK = 1

    Block 1 is corrupt

    Corrupt block relative dba: 0x63400001 (file 0, block 1)

    Bad header found during verification

    Data in bad block:

    type: 119 format: 7 rdba: 0x636f756c

    last change scn: 0x006d.6f632e73 seq: 0x1 flg: 0x04

    spare1: 0x77 spare2: 0x2e spare3: 0x0

    consistency value in tail: 0x00000b01

    check value in block header: 0x5225

    computed block checksum: 0x0

    DBVERIFY - Verification complete

    Total Blocks Examined : 1

    Total Blocks Processed (Data) : 0

    Total Blocks Failing (Data) : 0

    Total Blocks Processed (Index): 0

    Total Blocks Failing (Index): 0

    Total Blocks Empty : 0

    Total Blocks Marked Corrupt : 1

    Total Blocks Influx : 0

    Message 531 not found; product=RDBMS; facility=BBED

    当我们触发检查点的时候,系统检测到数据文件头部损坏,并自动将其下线处理

    sys@SA7N67B11G> alter system checkpoint;

    System altered.

    告警日志里报ORA-01210错误:

    Fri Dec 21 11:18:36 2012

    Read of datafile '/u01/app/oracle/oradata/SA7N67B11G/luocs01.dbf' (fno 34) header failed with ORA-01210

    Hex dump of (file 34, block 1) in trace file /u01/app/oracle/diag/rdbms/SA7N67B11G/SA7N67B11G/trace/luocs11g_ckpt_14686.trc

    Corrupt block relative dba: 0x08800001 (file 34, block 1)

    Bad header found during datafile header read

    Data in bad block:

    type: 119 format: 7 rdba: 0x636f756c

    last change scn: 0x006d.6f632e73 seq: 0x1 flg: 0x04

    spare1: 0x77 spare2: 0x2e spare3: 0x0

    consistency value in tail: 0x00000b01

    check value in block header: 0x5225

    computed block checksum: 0x0

    Rereading datafile 34 header failed with ORA-01210

    Errors in file /u01/app/oracle/diag/rdbms/SA7N67B11G/SA7N67B11G/trace/luocs11g_ckpt_14686.trc:

    ORA-01171: datafile 34 going offline due to error advancing checkpoint

    ORA-01122: database file 34 failed verification check

    ORA-01110: data file 34: '/u01/app/oracle/oradata/SA7N67B11G/luocs01.dbf'

    ORA-01210: data file header is media corrupt

    Fri Dec 21 11:18:37 2012

    Checker run found 1 new persistent data failures

    查看数据文件状态

    sys@SA7N67B11G> col FILE_NAME for a55

    sys@SA7N67B11G> select file_id, file_name, online_status from dba_data_files where tablespace_name='LUOCS';

    FILE_ID FILE_NAME ONLINE_STATUS

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

    34 /u01/app/oracle/oradata/SA7N67B11G/luocs01.dbf RECOVER

    35 /u01/app/oracle/oradata/SA7N67B11G/luocs02.dbf ONLINE

    查看该数据文件里的对象都会报错

    sys@SA7N67B11G> select count(*) from luocs.t1;

    select count(*) from luocs.t1

    *

    ERROR at line 1:

    ORA-00376: file 34 cannot be read at this time

    ORA-01110: data file 34: '/u01/app/oracle/oradata/SA7N67B11G/luocs01.dbf'

    这时候我们恢复需要如下过程:

    1)通过操作系统命令删除损坏的数据文件

    2)使用"alter database datafile xx offline"命令将数据文件OFFLINE处理(其实已经下线,但我们可以在此做一次)

    3)restore datafile xx 还原数据文件

    4)recover datafile xx 恢复数据文件

    5)再次使用"alter database datafile xx online"命令将数据文件ONLINE处理

    – 整个过程都在OPEN状态下进行。

    删除损坏的数据文件,刺猬步骤1

    [oracle@localhost ~]$ rm -rf /u01/app/oracle/oradata/SA7N67B11G/luocs01.dbf

    下面脚本表示步骤2到5:

    RMAN> run{

    2> sql 'alter database datafile 34 offline';

    3> restore datafile 34;

    4> recover datafile 34;

    5> sql 'alter database datafile 34 online';

    6> }

    sql statement: alter database datafile 34 offline

    Starting restore at 21-DEC-2012 11:30:03

    using channel ORA_DISK_1

    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 00034 to /u01/app/oracle/oradata/SA7N67B11G/luocs01.dbf

    channel ORA_DISK_1: reading from backup piece /u01/app/oracle/product/11.2.0.1/dbhome_1/dbs/02ntdbhq_1_1

    channel ORA_DISK_1: piece handle=/u01/app/oracle/product/11.2.0.1/dbhome_1/dbs/02ntdbhq_1_1 tag=TAG20121221T080058

    channel ORA_DISK_1: restored backup piece 1

    channel ORA_DISK_1: restore complete, elapsed time: 00:00:03

    Finished restore at 21-DEC-2012 11:30:06

    Starting recover at 21-DEC-2012 11:30:06

    using channel ORA_DISK_1

    starting media recovery

    archived log for thread 1 with sequence 161 is already on disk as file /u01/arch/1_161_799327748.dbf

    archived log for thread 1 with sequence 162 is already on disk as file /u01/arch/1_162_799327748.dbf

    archived log for thread 1 with sequence 163 is already on disk as file /u01/arch/1_163_799327748.dbf

    archived log for thread 1 with sequence 164 is already on disk as file /u01/arch/1_164_799327748.dbf

    archived log for thread 1 with sequence 165 is already on disk as file /u01/arch/1_165_799327748.dbf

    archived log for thread 1 with sequence 166 is already on disk as file /u01/arch/1_166_799327748.dbf

    archived log for thread 1 with sequence 167 is already on disk as file /u01/arch/1_167_799327748.dbf

    archived log file name=/u01/arch/1_161_799327748.dbf thread=1 sequence=161

    archived log file name=/u01/arch/1_162_799327748.dbf thread=1 sequence=162

    archived log file name=/u01/arch/1_163_799327748.dbf thread=1 sequence=163

    archived log file name=/u01/arch/1_164_799327748.dbf thread=1 sequence=164

    archived log file name=/u01/arch/1_165_799327748.dbf thread=1 sequence=165

    media recovery complete, elapsed time: 00:00:01

    Finished recover at 21-DEC-2012 11:30:09

    sql statement: alter database datafile 34 online

    OK,恢复成功,检查

    sys@SA7N67B11G> select file_id, file_name, online_status from dba_data_files where tablespace_name='LUOCS';

    FILE_ID FILE_NAME ONLINE_STATUS

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

    34 /u01/app/oracle/oradata/SA7N67B11G/luocs01.dbf ONLINE

    35 /u01/app/oracle/oradata/SA7N67B11G/luocs02.dbf ONLINE

    sys@SA7N67B11G> select count(*) from luocs.t1;

    COUNT(*)

    ----------

    161860

    假如我们在启动的时候发现数据文件头损坏了,这时候系统将无法open

    sys@SA7N67B11G> startup force

    ORACLE instance started.

    Total System Global Area 2042241024 bytes

    Fixed Size 1337548 bytes

    Variable Size 1342179124 bytes

    Database Buffers 687865856 bytes

    Redo Buffers 10858496 bytes

    Database mounted.

    ORA-01122: database file 34 failed verification check

    ORA-01110: data file 34: '/u01/app/oracle/oradata/SA7N67B11G/luocs01.dbf'

    ORA-01210: data file header is media corrupt

    这时候我们恢复需要如下过程:

    1)通过操作系统命令删除损坏的数据文件

    2)数据库启动到MOUNT状态,使用"alter database datafile xx online"命令将数据文件ONLINE处理

    3)restore datafile xx 还原数据文件

    4)recover datafile xx 恢复数据文件

    5)数据库OPEN

    [oracle@localhost ~]$ rm -rf /u01/app/oracle/oradata/SA7N67B11G/luocs01.dbf

    RMAN> run{

    2> shutdown abort;

    3> startup mount;

    4> sql 'alter database datafile 34 online';

    5> restore datafile 34;

    6> recover datafile 34;

    7> alter database open;

    8> }

    using target database control file instead of recovery catalog

    Oracle instance shut down

    connected to target database (not started)

    Oracle instance started

    database mounted

    Total System Global Area 2042241024 bytes

    Fixed Size 1337548 bytes

    Variable Size 1342179124 bytes

    Database Buffers 687865856 bytes

    Redo Buffers 10858496 bytes

    sql statement: alter database datafile 34 online

    Starting restore at 21-DEC-2012 12:22:05

    allocated channel: ORA_DISK_1

    channel ORA_DISK_1: SID=1 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 00034 to /u01/app/oracle/oradata/SA7N67B11G/luocs01.dbf

    channel ORA_DISK_1: reading from backup piece /u01/app/oracle/product/11.2.0.1/dbhome_1/dbs/02ntdbhq_1_1

    channel ORA_DISK_1: piece handle=/u01/app/oracle/product/11.2.0.1/dbhome_1/dbs/02ntdbhq_1_1 tag=TAG20121221T080058

    channel ORA_DISK_1: restored backup piece 1

    channel ORA_DISK_1: restore complete, elapsed time: 00:00:03

    Finished restore at 21-DEC-2012 12:22:09

    Starting recover at 21-DEC-2012 12:22:09

    using channel ORA_DISK_1

    starting media recovery

    archived log for thread 1 with sequence 161 is already on disk as file /u01/arch/1_161_799327748.dbf

    archived log for thread 1 with sequence 162 is already on disk as file /u01/arch/1_162_799327748.dbf

    archived log for thread 1 with sequence 163 is already on disk as file /u01/arch/1_163_799327748.dbf

    archived log for thread 1 with sequence 164 is already on disk as file /u01/arch/1_164_799327748.dbf

    archived log for thread 1 with sequence 165 is already on disk as file /u01/arch/1_165_799327748.dbf

    archived log for thread 1 with sequence 166 is already on disk as file /u01/arch/1_166_799327748.dbf

    archived log for thread 1 with sequence 167 is already on disk as file /u01/arch/1_167_799327748.dbf

    archived log for thread 1 with sequence 168 is already on disk as file /u01/arch/1_168_799327748.dbf

    archived log for thread 1 with sequence 169 is already on disk as file /u01/arch/1_169_799327748.dbf

    archived log file name=/u01/arch/1_161_799327748.dbf thread=1 sequence=161

    archived log file name=/u01/arch/1_162_799327748.dbf thread=1 sequence=162

    archived log file name=/u01/arch/1_163_799327748.dbf thread=1 sequence=163

    archived log file name=/u01/arch/1_164_799327748.dbf thread=1 sequence=164

    archived log file name=/u01/arch/1_165_799327748.dbf thread=1 sequence=165

    archived log file name=/u01/arch/1_166_799327748.dbf thread=1 sequence=166

    archived log file name=/u01/arch/1_167_799327748.dbf thread=1 sequence=167

    media recovery complete, elapsed time: 00:00:02

    Finished recover at 21-DEC-2012 12:22:12

    database opened

    我们再看一下数据库启动的时候找不到普通数据文件的情况

    [oracle@localhost ~]$ rm -rf /u01/app/oracle/oradata/SA7N67B11G/luocs01.dbf

    sys@SA7N67B11G> startup force

    ORACLE instance started.

    Total System Global Area 2042241024 bytes

    Fixed Size 1337548 bytes

    Variable Size 1342179124 bytes

    Database Buffers 687865856 bytes

    Redo Buffers 10858496 bytes

    Database mounted.

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

    ORA-01110: data file 34: '/u01/app/oracle/oradata/SA7N67B11G/luocs01.dbf'

    恢复方法和上面一样,略!

    最后我说一下数据文件头部损坏在Oracle Restart环境下的不同之处。

    前面构造数据文件头部坏块儿等操作都略,我已经通过DBV工具检测到数据文件里有数据坏块儿

    [oracle@maa3 ~]$ dbv file=/u01/app/oracle/oradata/luocs01.dbf

    DBVERIFY: Release 11.2.0.3.0 - Production on Fri Dec 21 14:20:52 2012

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

    DBVERIFY - Verification starting : FILE = /u01/app/oracle/oradata/luocs01.dbf

    Page 1 is marked corrupt

    Corrupt block relative dba: 0x02000001 (file 8, block 1)

    Bad header found during dbv:

    Data in bad block:

    type: 119 format: 7 rdba: 0x636f756c

    last change scn: 0x006d.6f632e73 seq: 0x1 flg: 0x04

    spare1: 0x77 spare2: 0x2e spare3: 0x0

    consistency value in tail: 0x00000b01

    check value in block header: 0xe7db

    computed block checksum: 0x0

    DBVERIFY - Verification complete

    Total Pages Examined : 12800

    Total Pages Processed (Data) : 249

    Total Pages Failing (Data) : 0

    Total Pages Processed (Index): 6

    Total Pages Failing (Index): 0

    Total Pages Processed (Other): 12543

    Total Pages Processed (Seg) : 0

    Total Pages Failing (Seg) : 0

    Total Pages Empty : 1

    Total Pages Marked Corrupt : 1

    Total Pages Influx : 0

    Total Pages Encrypted : 0

    Highest block SCN : 572571 (0.572571)

    当触发检查点的时候,Oracle Restart的保护资源特性会将数据库重启,而在Single Instance Database环境下是只会让数据文件下线处理。

    告警日志记录了发现坏块儿到重启过程:

    Read of datafile '/u01/app/oracle/oradata/luocs01.dbf' (fno 8) header failed with ORA-01210

    Hex dump of (file 8, block 1) in trace file /u01/app/oracle/diag/rdbms/maa/maa/trace/maa_ckpt_12212.trc

    Corrupt block relative dba: 0x02000001 (file 8, block 1)

    Bad header found during datafile header read

    Data in bad block:

    type: 119 format: 7 rdba: 0x636f756c

    last change scn: 0x006d.6f632e73 seq: 0x1 flg: 0x04

    spare1: 0x77 spare2: 0x2e spare3: 0x0

    consistency value in tail: 0x00000b01

    check value in block header: 0xe7db

    computed block checksum: 0x0

    Rereading datafile 8 header failed with ORA-01210

    Errors in file /u01/app/oracle/diag/rdbms/maa/maa/trace/maa_ckpt_12212.trc:

    ORA-63999: data file suffered media failure

    ORA-01122: database file 8 failed verification check

    ORA-01110: data file 8: '/u01/app/oracle/oradata/luocs01.dbf'

    ORA-01210: data file header is media corrupt

    Errors in file /u01/app/oracle/diag/rdbms/maa/maa/trace/maa_ckpt_12212.trc:

    ORA-63999: data file suffered media failure

    ORA-01122: database file 8 failed verification check

    ORA-01110: data file 8: '/u01/app/oracle/oradata/luocs01.dbf'

    ORA-01210: data file header is media corrupt

    CKPT (ospid: 12212): terminating the instance due to error 63999

    Fri Dec 21 14:23:28 2012

    System state dump requested by (instance=1, osid=12212 (CKPT)), summary=[abnormal instance termination].

    System State dumped to trace file /u01/app/oracle/diag/rdbms/maa/maa/trace/maa_diag_12200.trc

    Dumping diagnostic data in directory=[cdmp_20121221142328], requested by (instance=1, osid=12212 (CKPT)), summary=[abnormal instance termination].

    Instance terminated by CKPT, pid = 12212

    Fri Dec 21 14:23:29 2012

    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 =51

    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: maa3.luocs.com

    Release: 2.6.18-194.el5

    Version: #1 SMP Mon Mar 29 22:10:29 EDT 2010

    Machine: x86_64

    VM name: VMWare Version: 6

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

    System parameters with non-default values:

    processes = 300

    sessions = 472

    spfile = "+MSDATA/maa/spfilemaa.ora"

    memory_target = 1200M

    control_files = "+MSDATA/maa/controlfile/current.256.792009855"

    db_block_size = 8192

    compatible = "11.2.0.0.0"

    db_create_file_dest = "+MSDATA"

    db_recovery_file_dest = "/u01/recovery"

    db_recovery_file_dest_size= 5G

    undo_tablespace = "UNDOTBS1"

    remote_login_passwordfile= "EXCLUSIVE"

    db_domain = ""

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

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

    audit_trail = "DB"

    db_name = "maa"

    open_cursors = 300

    diagnostic_dest = "/u01/app/oracle"

    Fri Dec 21 14:23:30 2012

    PMON started with pid=2, OS id=21481

    Fri Dec 21 14:23:30 2012

    PSP0 started with pid=3, OS id=21483

    Fri Dec 21 14:23:30 2012

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

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

    Fri Dec 21 14:23:30 2012

    GEN0 started with pid=5, OS id=21489

    Fri Dec 21 14:23:30 2012

    DIAG started with pid=6, OS id=21491

    Fri Dec 21 14:23:30 2012

    DBRM started with pid=7, OS id=21493

    Fri Dec 21 14:23:30 2012

    DIA0 started with pid=8, OS id=21495

    Fri Dec 21 14:23:30 2012

    MMAN started with pid=9, OS id=21497

    Fri Dec 21 14:23:30 2012

    DBW0 started with pid=10, OS id=21499

    Fri Dec 21 14:23:30 2012

    LGWR started with pid=11, OS id=21501

    Fri Dec 21 14:23:30 2012

    CKPT started with pid=12, OS id=21503

    Fri Dec 21 14:23:30 2012

    SMON started with pid=13, OS id=21505

    Fri Dec 21 14:23:30 2012

    RECO started with pid=14, OS id=21507

    Fri Dec 21 14:23:30 2012

    RBAL started with pid=15, OS id=21509

    Fri Dec 21 14:23:30 2012

    ASMB started with pid=16, OS id=21511

    Fri Dec 21 14:23:30 2012

    MMON started with pid=17, OS id=21513

    NOTE: initiating MARK startup

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

    Fri Dec 21 14:23:30 2012

    MMNL started with pid=18, OS id=21517

    Starting background process MARK

    Fri Dec 21 14:23:30 2012

    MARK started with pid=19, OS id=21519

    NOTE: MARK has subscribed

    starting up 1 shared server(s) ...

    ORACLE_BASE not set in environment. It is recommended

    that ORACLE_BASE be set in the environment

    Reusing ORACLE_BASE from an earlier startup = /u01/app/oracle

    Fri Dec 21 14:23:31 2012

    ALTER SYSTEM SET local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1521))))' SCOPE=MEMORY SID='maa';

    ALTER DATABASE MOUNT /* db agent *//* {0:4:22} */

    NOTE: Loaded library: /opt/oracle/extapi/64/asm/orcl/1/libasm.so

    NOTE: Loaded library: System

    SUCCESS: diskgroup MSDATA was mounted

    NOTE: dependency between database maa and diskgroup resource ora.MSDATA.dg is established

    Successful mount of redo thread 1, with mount id 681236003

    Database mounted in Exclusive Mode

    Lost write protection disabled

    Completed: ALTER DATABASE MOUNT /* db agent *//* {0:4:22} */

    ALTER DATABASE OPEN /* db agent *//* {0:4:22} */

    Read of datafile '/u01/app/oracle/oradata/luocs01.dbf' (fno 8) header failed with ORA-01210

    Hex dump of (file 8, block 1) in trace file /u01/app/oracle/diag/rdbms/maa/maa/trace/maa_ora_21529.trc

    Corrupt block relative dba: 0x02000001 (file 8, block 1)

    Bad header found during datafile header read

    Data in bad block:

    type: 119 format: 7 rdba: 0x636f756c

    last change scn: 0x006d.6f632e73 seq: 0x1 flg: 0x04

    spare1: 0x77 spare2: 0x2e spare3: 0x0

    consistency value in tail: 0x00000b01

    check value in block header: 0xe7db

    computed block checksum: 0x0

    Rereading datafile 8 header failed with ORA-01210

    Errors in file /u01/app/oracle/diag/rdbms/maa/maa/trace/maa_ora_21529.trc:

    ORA-01122: database file 8 failed verification check

    ORA-01110: data file 8: '/u01/app/oracle/oradata/luocs01.dbf'

    ORA-01210: data file header is media corrupt

    ORA-1122 signalled during: ALTER DATABASE OPEN /* db agent *//* {0:4:22} */...

    Fri Dec 21 14:23:38 2012

    Checker run found 1 new persistent data failures

    Fri Dec 21 14:23:40 2012

    Shutting down instance (abort)

    License high water mark = 1

    USER (ospid: 21609): terminating the instance

    Instance terminated by USER, pid = 21609

    Fri Dec 21 14:23:40 2012

    Instance shutdown complete

    展开全文
  • 临时文件损坏

    2018-08-02 11:18:00
    临时数据文件的损坏或丢失会造成...如果在数据库运行过程中,发现临时数据文件损坏或丢失,可以采用替换的方法恢复,不需要重启数据库: A、向临时表空间中添加新的临时数据文件 SQL> alter tablespace temp ...
  • 18->数据文件损坏修复

    2017-04-26 17:02:00
    1.数据文件恢复原理: 由于数据的修改会体现在联机重做日志中 所以数据可以通过重做日志恢复到数据文件中 ...添加一个表空间和临时表空间 并且创建一个用户 指定该表空间和默认表空间 create tablespace mydata d...
  • 临时表空间丢失处理

    2016-12-13 21:53:30
    控制文件,数据文件需要恢复 归档日志,spfile不需要恢复 文件丢失的原因: 用户错误 应用程序错误 介质故障 关键性和非关键性: 非关键性文件丢失是指数据库可以继续运行的故障。 ·通过创建一个新文件/重建文件/...
  • 前段时间做了一个操作,迁移数据表空间,在迁移数据文件.dbf的时候出现了一个意外。在Linux系统下操作,因某些原因在迁移dbf的时候使用的是mv(移动)操作,导致移动到目标目录下后dbf有损坏,数据库在重启的时候...
  • 普通数据文件指:非system表空间、undo_tablespace表空间、临时表空间和只读表空间的数据文件。它们损坏导致用户数据不能访问,不会导致db自身异常、实例崩溃、数据库不恢复就无法启动的情况。 恢复这样的数据文件...
  • 能脱机的数据文件:用户表空间,sysaux表空间,临时表空间 中的数据文件 不能脱机的数据文件:system表空间,nudo表空间中的数据文件 可脱机的数据文件损坏 1、如果现在数据库已经open了, 让数据文件离线 可以用...
  • 系统表空间 临时表空间 回滚表空间 数据表空间   表空间的好处: ①避免磁盘的突然耗竭 ②规划数据更灵活 ③提高数据库性能 ④提高数据库安全性(磁盘损坏:不同表空间对应不同的数据文件) ...
  • 普通数据文件指 非 system表空间、udo表空间、临时表空间、只读表空间的文件。普通数据文件的恢复可用采用高可用恢复策略,  即能够在OPEN状态下恢复。  1、实例启动时,如果普通数据文件丢失、无法访问、头部损坏...
  • 表空间、表的管理

    2010-11-09 16:07:00
    1.表空间管理:永久、临时、撤销三种类型  ①准则  使用多个表空间:不同空间/分区存... 新建表空间,调整数据文件大小,添加数据文件表空间;  设置和修改缺省的数据段存储设置;
  • 管理表空间

    千次阅读 2007-06-11 20:32:00
    管理表空间的几个基本准则 使用多个表空间使用多个表空间是有好处的,从硬件上说:我们可以将不同应用的数据存放在不同的表空间内,并且放在不同的分区上,这样可以防止多个应用...同样的临时表空间也是需要创建多个,
  • ORACLE表空间管理

    千次阅读 2009-10-15 16:22:00
    管理表空间的几个基本准则 使用多个表空间使用多个表空间是有好处的,从硬件上说:我们可以将不同应用的数据存放在不同的表空间内,并且放在不同的分区上,这样可以防止多个...同样的临时表空间也是需要创建多个,用来
  • 将不同的用户数据从数据字典中分离,减少数据文件的数据字典对象和模式对象竞争 指定表空间的存储参数 为表空间指定限额:防资源浪费 ②可进行的操作 新建表空间,调整数据文件大小,添加数据文件表空间;...
  • 13.5 恢复临时文件 临时文件没有也不应该备份。...由于临时表空间不用保存永久性数据,所以RMAN不会对其备份,一旦损坏采用的恢复策略是替换或者重建。 SQL> ALTER TABLESPACE TEMP ADD TEMPFILE .....
  • 将不同的用户数据从数据字典中分离,减少数据文件的数据字典对象和模式对象竞争 指定表空间的存储参数 为表空间指定限额:防资源浪费 ②可进行的操作 新建表空间,调整数据文件大小,添加数据文件表空间;...
  • temp损坏

    2014-01-08 21:18:08
    临时表空间即使不存在 系统也能运行 只是部分功能需要临时表空间的不能使用系统默认临时表空间系统恢复后 可能和临时文件脱节temp历史 oracle 8没有临时表空间 排序用datafile oracle 8I有临时表空间 但要指明用哪个...
  • 今天正常的导压缩文件,发现一个问题,当数量据大点时,比如我的有25万个字时,就提示我解压失败 后来我慢慢排查,我放删除临时文件的代码注释掉,让程序生成临时文件。 发现,临时文件只有65kb,且是可以解...
  • 控制文件损坏使用备库控制文件恢复数据文件损坏坏块使用备库数据文件做bmr,语法: blockrecover datafile xx block xxx 文件损坏1~3使用备库数据文件恢复 1. 系统表空间 2. Undo表空间 3. 普通表空间 4. ...
  • Oracle数据库 控制文件

    2016-08-29 17:06:00
    二、所含有的信息1、数据库名2、数据库标识符(DBID)3、数据库创建时间戳4、数据库字符集5、数据文件信息6、临时文件信息7、在线重做日志信息8、近期的归档日志信息9、表空间信息10、RMAN备份文件信息,即RMAN资料...
  • RMAN恢复的学习

    2010-07-21 17:07:00
    rman备份的时候并不会备份临时表空间数据文件,所以完全恢复之后,如果临时表空间数据文件也丢失,需要手工重新创建临时表空间数据文件。 Oracle数据库的恢复实际上包含了两个概念:数据库修复(RESTORE)与...
  •  22.6 数据文件损坏的恢复  22.7 的恢复(用户错误的恢复) 第8篇 Oracle程序设计  第23章 数据库通用语言——SQL语句  23.1 结构化查询语言简介  23.2 SELECT语句  23.2.1 理解SELECT 语句  23.2.2 ...
  •  22.6 数据文件损坏的恢复  22.7 的恢复(用户错误的恢复) 第8篇 oracle程序设计  第23章 数据库通用语言——sql语句  23.1 结构化查询语言简介  23.2 select语句  23.2.1 理解select 语句  23.2.2 ...
  •  22.6 数据文件损坏的恢复  22.7 的恢复(用户错误的恢复) 第8篇 oracle程序设计  第23章 数据库通用语言——sql语句  23.1 结构化查询语言简介  23.2 select语句  23.2.1 理解select 语句  23.2.2 ...

空空如也

空空如也

1 2 3 4
收藏数 67
精华内容 26
关键字:

临时表空间数据文件损坏