记数据库数据文件损坏恢复ORA-00376+ORA-01110

时间：2023-05-02 20:08:14

　　现象：业务平台无法登陆，日志报错为ORACLE的错误。

　　查看oracle日志的报错，

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

ORA-01110: data file 5: '/oradata/users02.dbf'

　　看一下oracle状态，

SQL> select status from v$instance;

STATUS

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

OPEN

　　奇怪了，数据库状态正常，还报错，应该是数据文件有问题，查一下数据文件的SCN，发现确实是datafile 5有问题，其SCN与其他的不一致。

记数据库数据文件损坏恢复ORA-00376+ORA-01110

　　接下来开始要恢复了。尝试恢复数据文件5，

SQL> recovere datafile 5;

SP2-0734: unknown command beginning "recovere d..." - rest of line ignored.

SQL> recover datafile 5;

ORA-00279: change 14945741822997 generated at 03/22/2018 22:13:55 needed for

thread 1

ORA-00289: suggestion :

/usr/local/oracle/flash_recovery_area/ORCL/archivelog/2018_03_26/o1_mf_1_298748_

%u_.arc

ORA-00280: change 14945741822997 for thread 1 is in sequence #298748

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

redo03.log;

ORA-00308: cannot open archived log 'redo03.log;'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

　　加上until cancle参数再次尝试恢复，恢复数据原则要进程不断尝试

SQL> recover datafile 5 until cancel;

ORA-00274: illegal recovery option UNTIL

　　尝试将数据文件5 online，

SQL> alter database datafile 5 online;

alter database datafile 5 online

*

ERROR at line 1:

ORA-01113: file 5 needs media recovery

ORA-01110: data file 5: '/oradata/users02.dbf'

　　这里报错，应该是数据库已经OPEN了，无法进行数据文件恢复。将数据库关闭，启动至mount状态，

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 6747725824 bytes

Fixed Size                  2213976 bytes

Variable Size            4160751528 bytes

Database Buffers         2550136832 bytes

Redo Buffers               34623488 bytes

Database mounted.

　　再次加上until cancle参数再次尝试恢复，

SQL> recover database until cancel;

Media recovery complete.

　　打开数据库，完成介质恢复，

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> alter database open RESETLOGS;

Database altered.

SQL> select status from v$instance;

STATUS

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

OPEN

　　这时，介质恢复应该就完成了，再去查一下SCN，

SQL> select checkpoint_change# from v$datafile;

CHECKPOINT_CHANGE#

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

        1.4946E+13

        1.4946E+13

        1.4946E+13

        1.4946E+13

        1.4946E+13

        1.4946E+13

        1.4946E+13

        1.4946E+13

        1.4946E+13

        1.4946E+13

        1.4946E+13

CHECKPOINT_CHANGE#

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

        1.4946E+13

        1.4946E+13

        1.4946E+13

14 rows selected.

SQL>

　　记数据库数据文件损坏恢复ORA-00376+ORA-01110

一致了，验证一下业务平台，正常了。

相关文章