问题：一个自己学习做测试的库被折腾了一些日志，今天做实验时：

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;

表已创建。

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

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

ITPub博客

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

最新文章