关于ORA-00376错误的恢复案例

转载关于ORA-00376错误的恢复案例处理。

2011-03-09去医院处理问题，突然医院科室往计算机中心打电话，软件报错。马上远程处理，看看具体报什么错误，一看报ORA-00376: "file '/data1/oradata/e_data1.dbf' cannot be read at this time"，马上在网上百度查询ORA-00376错误原因，通过百度查询找到该文章，就按照一步一步操作，问题得到解决。

SQL> select count(*) from tab_test;

ORA0-00376: "file '/data1/oradata/e_data1.dbf' cannot be read at this time"

重启数据库没有问题，数据文件都能打开。

[oracle@game ~]$ oerr ora 376

00376, 00000, "file %s cannot be read at this time"

// *Cause: attempting to read from a file that is not readable. Most likely

//          the file is offline.

// *Action: Check the state of the file. Bring it online

[oracle@game ~]$ 

经检查，操作系统下数据文件属性正常，磁盘空间正常。

怀疑数据文件有问题：

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

     FILE# STATUS

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

         1 SYSTEM

         2 ONLINE

          3 ONLINE

         4 ONLINE

         5 RECOVER 

         6 ONLINE

         7 ONLINE

         8 ONLINE

8 rows selected.

果然文件5状态为RECOVER，看来得手工恢复该文件了。

经确认，数据库未曾备份，但是归档重来没有删除过，恢复应该没有问题。

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORA-32004: obsolete and/or deprecated parameter(s) specified

ORACLE instance started.

Total System Global Area 285212672 bytes

Fixed Size                  1218968 bytes

Variable Size             121636456 bytes

Database Buffers          155189248 bytes

Redo Buffers                7168000 bytes

Database mounted.

SQL> recover datafile 5;

ORA-00279: change 357661111 generated at 10/14/2008 16:56:30 needed for thread

1

ORA-00289: suggestion :

/data1/app/oracle/oracle/oradata/eTest/arch2/3531_1_639333038.log

ORA-00280: change 357661111 for thread 1 is in sequence #3531

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

auto               --这里输入auto

ORA-00279: change 357662213 generated at 10/14/2008 16:59:49 needed for thread

1

ORA-00289: suggestion :

/data1/app/oracle/oracle/oradata/eTest/arch2/3532_1_639333038.log

ORA-00280: change 357662213 for thread 1 is in sequence #3532

ORA-00278: log file

'/data1/app/oracle/oracle/oradata/eTest/arch2/3531_1_639333038.log' no longer

needed for this recovery

Log applied.

Media recovery complete.

SQL> alter database datafile '/data1/oradata/e_data1.dbf' online;

Database altered.

SQL> alter database open;

Database altered.

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

     FILE# STATUS

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

         1 SYSTEM

         2 ONLINE

          3 ONLINE

         4 ONLINE

         5 ONLINE

         6 ONLINE

         7 ONLINE

         8 ONLINE

8 rows selected.

现在再检索数据，已经没有问题了。

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

转载于:http://blog.itpub.net/99436/viewspace-690117/