对于ORA-00279 ORA-00289 ORA-00280故障,一般是由于缺少日志(比如数据库非归档模式,或者归档日志丢失),导致某些文件无法正常online,对此Oracle Recovery Tool能够一键式快速解决.参考: Oracle Recovery Tools—202008
模拟故障
非归档环境,模拟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的问题

ITPub博客

一键解决ORA-00279 ORA-00289 ORA-00280

最新文章