对于ORA-00279 ORA-00289 ORA-00280故障,一般是由于缺少日志(比如数据库非归档模式,或者归档日志丢失),导致某些文件无法正常online,对此Oracle Recovery Tool能够一键式快速解决.
模拟故障
非归档环境,模拟datafile 4 offline,然后多次切换日志,导致该文件无法正常online
SQL> select owner,segment_name from dba_segments where tablespace_name='USERS' AND OWNER='SYSTEM';
OWNER SEGMENT_NAME
------------------------------ ------------------------------
SYSTEM T_XIFENFEI
SQL> SELECT COUNT(1) FROM SYSTEM.T_XIFENFEI;
COUNT(1)
----------
87752
SQL> ALTER DATABASE DATAFILE 4 OFFLINE DROP;
Database altered.
SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination D:\app\Administrator\product\11.2.0\dbhome_1\RDBMS
Oldest online log sequence 22
Current log sequence 24
SQL> select group#,status,sequence# from v$log;
GROUP# STATUS SEQUENCE#
---------- ---------------- ----------
1 INACTIVE 22
2 INACTIVE 23
3 CURRENT 24
SQL> RECOVER DATAFILE 4;
ORA-00279: change 1525881 generated at 09/06/2020 13:46:17 needed for thread 1
ORA-00289: suggestion :
D:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\RDBMS\ARC0000000019_1046476315.0001
ORA-00280: change 1525881 for thread 1 is in sequence #19
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
使用Oracle Recovery Tools快速修复
online文件成功
SQL> recover datafile 4;
Media recovery complete.
SQL> alter database datafile 4 online;
Database altered.
SQL> SELECT COUNT(1) FROM SYSTEM.T_XIFENFEI;
COUNT(1)
----------
87752
通过该工具就可以快速解决oracle丢失归档数据文件online的问题
