今天开始磁盘空间100%,删完数据后,查询表遇到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: {<RET>=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.
现在再检索数据,已经没有问题了。--End--