ORA-00376，ORA-01647一次自己测试库数据文件错误的处理

最新推荐文章于 2023-11-29 21:25:15 发布

ye_minhua

最新推荐文章于 2023-11-29 21:25:15 发布

阅读量1.3k

点赞数

分类专栏：其它文章标签：测试 oracle sql system testing database

本文链接：https://blog.csdn.net/ye_minhua/article/details/7740003

版权

其它专栏收录该内容

5 篇文章 0 订阅

订阅专栏

问题：一个自己学习做测试的库被折腾了一些日志，今天做实验时：
SQL> select * from scott.emp;
select * from scott.emp
                   *
第 1 行出现错误:

ORA-00376: 此时无法读取文件 4

ORA-01110: 数据文件 4: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF'

诊断：
SQL> select hxfil FILENUMBER,fhsta STATUS,fhscn SCN,fhrba_Seq SEQUENCE from x$kcvfh;

FILENUMBER    STATUS SCN               SEQUENCE
---------- ---------- ---------------- ----------

        1      8196 5341290                182

        2         4 5341290                182

        3         4 5341290                182

        4         4 5341643                182

        5         4 5341290                182

        6         4 5341290                182

        7         4 5341290                182

        8         0 0                        0

        9         0 0                        0

       10         0 0                        0

       11         0 0                        0

       12         0 0                        0

       13         0 0                        0

       14         0 0                        0

       15         0 0                        0

       16         0 3874783                127

       17         0 0                        0

       18         4 5341290                182

       19         4 5341290                182

       20         0 0                        0

       21         0 0                        0

       22         4 5341290                182

       23         0 0                        0

SQL> select file#, status,checkpoint_change#,last_change#,name from v$datafile;

    FILE# STATUS CHECKPOINT_CHANGE# LAST_CHANGE# NAME
---------- ------- ------------------ ------------ -----------------------------------------------------------------

        1 SYSTEM            5341290             D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF

        2 ONLINE            5341290             D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF

        3 ONLINE            5341290             D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF

        4 ONLINE            5341643             D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF

        5 ONLINE            5341290             D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\COMPLAINT.ORA

        6 ONLINE            5341290             D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\JLGTJG.ORA

        7 ONLINE            5341290             D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\GTOA.ORA

        8 OFFLINE           4979281     4979281 D:\ORACLE\PRODUCT\10.2.0\ORADATA\NBTBS01.DBF

        9 OFFLINE           4979281     4979281 D:\ORACLE\PRODUCT\10.2.0\ORADATA\NBTBS02.DBF

       10 OFFLINE           4979281     4979281 D:\ORACLE\PRODUCT\10.2.0\ORADATA\TBS8K.DBF

       11 OFFLINE           4979281     4979281 D:\ORACLE\PRODUCT\10.2.0\ORADATA\TBS16K.DBF

       12 OFFLINE           4979281     4979281 D:\ORACLE\PRODUCT\10.2.0\ORADATA\TBS16K1.DBF

       13 OFFLINE           4979281     4979281 D:\LOCAL.DBF

       14 OFFLINE           4979281     4979281 D:\TESTING_LMT.DBF

       15 OFFLINE           4979281     4979281 D:\TESTING.DBF

       16 ONLINE            3874783     3874783 D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\DATA_TBS

       17 OFFLINE           4985270     4985270 D:\YMH_TEST.DBF

       18 ONLINE            5341290             D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\JLGTZSK.DBF

       19 ONLINE            5341290             E:\TBS1.DBF

       20 RECOVER           5298469     5319772 D:\YMH_TEST2.DBF

       21 OFFLINE           4985270     4985270 D:\YMH_ORCL.DBF

       22 ONLINE            5341290             D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\JLGTJGXMK.DBF

       23 RECOVER           5225680     5225715 D:\YMH2.DBF

已选择23行。

SQL> select file#, recover, fuzzy, checkpoint_change# from v$datafile_header;

    FILE# REC FUZ CHECKPOINT_CHANGE#
---------- --- --- ------------------

        1 NO YES           5341290

        2 NO YES           5341290

        3 NO YES           5341290

        4 NO YES           5341643

        5 NO YES           5341290

        6 NO YES           5341290

        7 NO YES           5341290

        8                         0

        9                         0

       10                         0

       11                         0

       12                         0

       13                         0

       14                         0

       15                         0

       16 NO NO            3874783

       17                         0

       18 NO YES           5341290

       19 NO YES           5341290

       20                         0

       21                         0

       22 NO YES           5341290

       23                         0

已选择23行。

=====》发现数据文件和控制文件的状态差很大，数据文件头缺了很多数据文件信息，因为数据文件头的信息来自数据文件本身，所以可能是数据文件丢失。

SQL> recover datafile 8;

ORA-00283: 恢复会话因错误而取消

ORA-01110: 数据文件 8: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\NBTBS01.DBF'

ORA-01157: 无法标识/锁定数据文件 8 - 请参阅 DBWR 跟踪文件

ORA-01110: 数据文件 8: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\NBTBS01.DBF'

=====》测试并倒文件夹中检查，确实是丢失了物理文件，是之前清理磁盘时，把无用的测试文件都删了。

解决：
SQL> select * from dba_data_files;

FILE_NAME                                                      FILE_ID TABLESPACE_NAME                 BYTES    BLOCKS STATUS
------------------------------------------------------------ ---------- ------------------------------ ---------- ---------- ---------
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF                    4 USERS                                           AVAILABLE

D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF                   3 SYSAUX                      346030080     42240 AVAILABLE

D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF                  2 UNDOTBS1                     94371840     11520 AVAILABLE

D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF                   1 SYSTEM                      713031680     87040 AVAILABLE

D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\COMPLAINT.ORA                  5 COMPLAINT                    20971520      2560 AVAILABLE

D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\JLGTJG.ORA                     6 JLGTJG                       20971520      2560 AVAILABLE

D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\GTOA.ORA                       7 GTOA                         20971520      2560 AVAILABLE

D:\ORACLE\PRODUCT\10.2.0\ORADATA\NBTBS01.DBF                         8 NB                                              AVAILABLE

D:\ORACLE\PRODUCT\10.2.0\ORADATA\NBTBS02.DBF                         9 NB                                              AVAILABLE

D:\ORACLE\PRODUCT\10.2.0\ORADATA\TBS8K.DBF                          10 TBS8K                                           AVAILABLE

D:\ORACLE\PRODUCT\10.2.0\ORADATA\TBS16K.DBF                         11 TBS16K                                          AVAILABLE

D:\ORACLE\PRODUCT\10.2.0\ORADATA\TBS16K1.DBF                        12 TBS16K1                                         AVAILABLE

D:\LOCAL.DBF                                                        13 LOCAL                                           AVAILABLE

D:\TESTING_LMT.DBF                                                  14 TESTING_LMT_MSSM                                AVAILABLE

D:\TESTING.DBF                                                      15 TESTING_LMT_ASSM                                AVAILABLE

D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\JLGTZSK.DBF                   18 JLGTZSK                      52428800      6400 AVAILABLE

D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\DATA_TBS                     16 DATA_TBS                    104857600     12800 AVAILABLE

D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\JLGTJGXMK.DBF                 22 JLGTJGXMK                   104857600     12800 AVAILABLE

D:\YMH_TEST.DBF                                                     17 YMH_TEST                                        AVAILABLE

E:\TBS1.DBF                                                         19 TBS1                         20971520      2560 AVAILABLE

D:\YMH_TEST2.DBF                                                    20 YMH_TEST                                        AVAILABLE

D:\YMH_ORCL.DBF                                                     21 YMH_ORCL                                        AVAILABLE

D:\YMH2.DBF                                                         23 YMH2                                            AVAILABLE
========》从dba_data_files里检查数据文件与表空间的对照关系，删除不存在的数据文件。

SQL> drop tablespace nb including contents and datafiles;

表空间已删除。

SQL> drop tablespace TBS16K1 including contents and datafiles;

表空间已删除。

SQL> drop tablespace TESTING_LMT_ASSM including contents and datafiles;

表空间已删除。
。。。。。。

SQL> drop tablespace TESTING_LMT_MSSM including contents and datafiles;

表空间已删除。

=========》如果是某个表空间下的多个数据文件之一，使用：alter tablespace drop datafile XXX；

SQL> select file#, recover, fuzzy, checkpoint_change# from v$datafile_header;

    FILE# REC FUZ CHECKPOINT_CHANGE#
---------- --- --- ------------------

        1 NO YES           5341290

        2 NO YES           5341290

        3 NO YES           5341290

        4 NO YES           5341643

        5 NO YES           5341290

        6 NO YES           5341290

        7 NO YES           5341290

       13                        0

       16 NO NO            3874783

       18 NO YES           5341290

       19 NO YES           5341290

       22 NO YES           5341290

已选择12行。

SQL> drop tablespace LOCAL including contents and datafiles;

表空间已删除。

SQL> select file#, recover, fuzzy, checkpoint_change#,name from v$datafile_header;

    FILE# REC FUZ CHECKPOINT_CHANGE# NAME
---------- --- --- ------------------ ---------------------------------------------------------

        1 NO YES           5343025 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF

        2 NO YES           5343025 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF

        3 NO YES           5343025 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF

        4 NO YES           5343025 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF

        5 NO YES           5343025 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\COMPLAINT.ORA

        6 NO YES           5343025 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\JLGTJG.ORA

        7 NO YES           5343025 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\GTOA.ORA

       16 NO NO            3874783 D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\DATA_TBS

       18 NO YES           5343025 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\JLGTZSK.DBF

       19 NO YES           5343025 E:\TBS1.DBF

       22 NO YES           5343025 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\JLGTJGXMK.DBF

SQL> select file#, status,checkpoint_change#,last_change# from v$datafile;

    FILE# STATUS CHECKPOINT_CHANGE# LAST_CHANGE#
---------- ------- ------------------ ------------

        1 SYSTEM            5341290

        2 ONLINE            5341290

        3 ONLINE            5341290

        4 ONLINE            5341643

        5 ONLINE            5341290

        6 ONLINE            5341290

        7 ONLINE            5341290

       16 ONLINE            3874783     3874783

       18 ONLINE            5341290

       19 ONLINE            5341290

       22 ONLINE            5341290

SQL> select hxfil FILENUMBER,fhsta STATUS,fhscn SCN,fhrba_Seq SEQUENCE from x$kcvfh;

FILENUMBER    STATUS SCN               SEQUENCE

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

        1      8196 5343025                182

        2         4 5343025                182

        3         4 5343025                182

        4         4 5343025                182

        5         4 5343025                182

        6         4 5343025                182

        7         4 5343025                182

       16         0 3874783                127

       18         4 5343025                182

       19         4 5343025                182

       22         4 5343025                182

=========》删除完成后继续做检查，发现16号文件的SCN很旧，4号文件的数据文件中的SCN与其它不同但是控制文件中的SCN相同

SQL> create table tet(a int) tablespace DATA_TBS;

create table tet(a int) tablespace DATA_TBS

*

第 1 行出现错误:

ORA-01647: 表空间 'DATA_TBS' 是只读, 无法在其中分配空间

=========》在16号文件行测试创建一张表报错

SQL> select file#, status,ENABLED,checkpoint_change#,last_change# from v$datafile;

    FILE# STATUS ENABLED   CHECKPOINT_CHANGE# LAST_CHANGE#

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

        1 SYSTEM READ WRITE           5343025

        2 ONLINE READ WRITE           5343025

        3 ONLINE READ WRITE           5343025

        4 ONLINE DISABLED             5343025

        5 ONLINE READ WRITE           5343025

        6 ONLINE READ WRITE           5343025

        7 ONLINE READ WRITE           5343025

       16 ONLINE READ ONLY            3874783     3874783

       18 ONLINE READ WRITE           5343025

       19 ONLINE READ WRITE           5343025

       22 ONLINE READ WRITE           5343025

已选择11行。

=========》检查ENABLED的值，这是启用状态的的属性值

SQL> alter tablespace DATA_TBS read write;

表空间已更改。

SQL> select file#, status,ENABLED,checkpoint_change#,last_change# from v$datafile;

    FILE# STATUS ENABLED   CHECKPOINT_CHANGE# LAST_CHANGE#

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

        1 SYSTEM READ WRITE           5343025

        2 ONLINE READ WRITE           5343025

        3 ONLINE READ WRITE           5343025

        4 ONLINE DISABLED             5343025

        5 ONLINE READ WRITE           5343025

        6 ONLINE READ WRITE           5343025

        7 ONLINE READ WRITE           5343025

       16 ONLINE READ WRITE           5343483

       18 ONLINE READ WRITE           5343025

       19 ONLINE READ WRITE           5343025

       22 ONLINE READ WRITE           5343025

已选择11行。

SQL> alter database datafile 4 online;

数据库已更改。

SQL> select file#, status,ENABLED,checkpoint_change#,last_change# from v$datafile;

    FILE# STATUS ENABLED   CHECKPOINT_CHANGE# LAST_CHANGE#

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

        1 SYSTEM READ WRITE           5343025

        2 ONLINE READ WRITE           5343025

        3 ONLINE READ WRITE           5343025

        4 ONLINE DISABLED             5343025

        5 ONLINE READ WRITE           5343025

        6 ONLINE READ WRITE           5343025

        7 ONLINE READ WRITE           5343025

       16 ONLINE READ WRITE           5343483

       18 ONLINE READ WRITE           5343025

       19 ONLINE READ WRITE           5343025

       22 ONLINE READ WRITE           5343025

已选择11行。

=========>4号数据文件的是联机的，似乎没有问题，但却是不可用的，怀疑表空间有问题

SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME               STATUS

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

SYSTEM                        ONLINE

UNDOTBS1                      ONLINE

SYSAUX                        ONLINE

TEMP                          ONLINE

USERS                         OFFLINE

COMPLAINT                     ONLINE

JLGTJG                        ONLINE

GTOA                          ONLINE

DATA_TBS                      ONLINE

JLGTZSK                       ONLINE

TBS1                          ONLINE

JLGTJGXMK                     ONLINE

已选择12行。

============》表空间offline了，数据文件online了也无法使用

SQL> alter tablespace users online;

表空间已更改。

SQL> select file#, status,ENABLED,checkpoint_change#,last_change# from v$datafile;

    FILE# STATUS ENABLED   CHECKPOINT_CHANGE# LAST_CHANGE#

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

        1 SYSTEM READ WRITE           5343025

        2 ONLINE READ WRITE           5343025

        3 ONLINE READ WRITE           5343025

        4 ONLINE READ WRITE           5343025

        5 ONLINE READ WRITE           5343025

        6 ONLINE READ WRITE           5343025

        7 ONLINE READ WRITE           5343025

       16 ONLINE READ WRITE           5343483

       18 ONLINE READ WRITE           5343025

       19 ONLINE READ WRITE           5343025

       22 ONLINE READ WRITE           5343025

已选择11行。

SQL> select file#, status,checkpoint_change#,last_change# from v$datafile;

    FILE# STATUS CHECKPOINT_CHANGE# LAST_CHANGE#

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

        1 SYSTEM            5343025

        2 ONLINE            5343025

        3 ONLINE            5343025

        4 ONLINE            5343025

        5 ONLINE            5343025

        6 ONLINE            5343025

        7 ONLINE            5343025

       16 ONLINE            5343483

       18 ONLINE            5343025

       19 ONLINE            5343025

       22 ONLINE            5343025

已选择11行。

SQL> select file#, recover, fuzzy, checkpoint_change# from v$datafile_header;

    FILE# REC FUZ CHECKPOINT_CHANGE#

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

        1 NO YES           5343025

        2 NO YES           5343025

        3 NO YES           5343025

        4 NO YES           5343025

        5 NO YES           5343025

        6 NO YES           5343025

        7 NO YES           5343025

       16 NO YES           5343483

       18 NO YES           5343025

       19 NO YES           5343025

       22 NO YES           5343025

已选择11行。

SQL> create table tet(a int) tablespace DATA_TBS;

表已创建。

SQL> create table tet2(a int) tablespace users;

表已创建。

==========》检查恢复正常

学习备份恢复有一段时间了，总算能够自己分析一点问题，就是磕磕碰碰了些，可惜没有实际工作经验，还要继续努力。