1. 案例介绍
ORA-00279/ORA-00289/ORA-00280,Oracle数据库由于存储异常、人为误操作、归档日志误删除等原因，
由于缺失日志文件，造成offline的数据文件无法正常online,通常修复方式为通过bbed修改文件头信息进行修复，
本文将为您演示一键修复该问题，如果您也遇到该问题，请联系我们，免费为您提供Oracle数据库修复服务。
2. 异常模拟
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/epmsn/test01.dbf'
SQL> alter database recover datafile 5;
alter database recover datafile 5
*
ERROR at line 1:
ORA-00279: change 3258999 generated at 04/08/2020 22:59:55 needed for thread 1
ORA-00289: suggestion : /arch1/1_46_1036913352.dbf
ORA-00280: change 3258999 for thread 1 is in sequence #46
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
3. 一键恢复操作
[oracle@sourcedb arch1]$ pwd
/arch1
[oracle@sourcedb arch1]$ ls
[oracle@sourcedb arch1]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Wed Apr 8 23:02:07 2020
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> set linesize 200 pagesize 200
select name from v$datafile_header order by RFILE#;SQL>
NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/oradata/epmsn/system01.dbf
/oradata/epmsn/sysaux01.dbf
/oradata/epmsn/star01.dbf
/oradata/epmsn/users01.dbf
/oradata/epmsn/test01.dbf
/oradata/epmsn/undo1.dbf
6 rows selected.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@sourcedb arch1]$ pwd
/arch1
[oracle@sourcedb ~]$ cd xdul
[oracle@sourcedb xdul]$ ls
bak config.txt control.txt dump xdul
[oracle@sourcedb xdul]$ vi config.txt
[oracle@sourcedb xdul]$ vi control.txt
[oracle@sourcedb xdul]$ ls
bak config.txt control.txt dump xdul
[oracle@sourcedb xdul]$ ./xdul
xdul: Data Unload for Oracle version 1.1.1
Copyright(c) 2020 orastar.All rights reserved.
Wechat: xidoublestr
Email: 634025070@qq.com
loading default config.......
load config file 'config.txt' successful
loading default control file ......
ts# fn rfn bsize blocks filename kscnbas_484 kcvcptim_496 kcvfhrlc_112 kscnbas_116
---- ---- ---- ----- -------- -------------------------------------------- -------- -------- -------- --------
0 1 1 8192 97280 /oradata/epmsn/system01.dbf 31bade 3dd2d9a7 3dce0ac8 2beb3b
1 2 2 8192 94720 /oradata/epmsn/sysaux01.dbf 31bade 3dd2d9a7 3dce0ac8 2beb3b
19 3 3 8192 12800 /oradata/epmsn/star01.dbf 31bade 3dd2d9a7 3dce0ac8 2beb3b
4 4 4 8192 640 /oradata/epmsn/users01.dbf 31bade 3dd2d9a7 3dce0ac8 2beb3b
6 5 5 8192 6400 /oradata/epmsn/test01.dbf 31ba77 3dd2d96b 3dce0ac8 2beb3b
18 17 17 8192 12800 /oradata/epmsn/undo1.dbf 31bade 3dd2d9a7 3dce0ac8 2beb3b
load control file 'control.txt' successful
XDUL>
XDUL>pdfh
ts# fn rfn bsize blocks filename kscnbas_484 kcvcptim_496 kcvfhrlc_112 kscnbas_116
---- ---- ---- ----- -------- -------------------------------------------- -------- -------- -------- --------
0 1 1 8192 97280 /oradata/epmsn/system01.dbf 31bade 3dd2d9a7 3dce0ac8 2beb3b
1 2 2 8192 94720 /oradata/epmsn/sysaux01.dbf 31bade 3dd2d9a7 3dce0ac8 2beb3b
19 3 3 8192 12800 /oradata/epmsn/star01.dbf 31bade 3dd2d9a7 3dce0ac8 2beb3b
4 4 4 8192 640 /oradata/epmsn/users01.dbf 31bade 3dd2d9a7 3dce0ac8 2beb3b
6 5 5 8192 6400 /oradata/epmsn/test01.dbf 31ba77 3dd2d96b 3dce0ac8 2beb3b
18 17 17 8192 12800 /oradata/epmsn/undo1.dbf 31bade 3dd2d9a7 3dce0ac8 2beb3b
XDUL>sdfh
XDUL>rdfh
sync o(one) or a(all) datafile: o
intput source_rfn:1
intput target_rfn:5
loading default control file ......
ts# fn rfn bsize blocks filename kscnbas_484 kcvcptim_496 kcvfhrlc_112 kscnbas_116
---- ---- ---- ----- -------- -------------------------------------------- -------- -------- -------- --------
0 1 1 8192 97280 /oradata/epmsn/system01.dbf 31bade 3dd2d9a7 3dce0ac8 2beb3b
1 2 2 8192 94720 /oradata/epmsn/sysaux01.dbf 31bade 3dd2d9a7 3dce0ac8 2beb3b
19 3 3 8192 12800 /oradata/epmsn/star01.dbf 31bade 3dd2d9a7 3dce0ac8 2beb3b
4 4 4 8192 640 /oradata/epmsn/users01.dbf 31bade 3dd2d9a7 3dce0ac8 2beb3b
6 5 5 8192 6400 /oradata/epmsn/test01.dbf 31bade 3dd2d9a7 3dce0ac8 2beb3b
18 17 17 8192 12800 /oradata/epmsn/undo1.dbf 31bade 3dd2d9a7 3dce0ac8 2beb3b
load control file 'control.txt' successful
XDUL>XDUL>exit
[oracle@sourcedb xdul]$
[oracle@sourcedb xdul]$
[oracle@sourcedb xdul]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Wed Apr 8 23:04:19 2020
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> set linesize 200 pagesize 200
col ERROR for a10
col NAME for a30
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
select CHECKPOINT_CHANGE# from v$database;
select FILE#,CHECKPOINT_CHANGE#,CHECKPOINT_TIME from v$datafile;
select FILE#,name,STATUSSQL> SQL> SQL>
Session altered.
,TS#,RFILE#,RESETLOGS_CHANGE#,RESETLOGS_TIME,CHECKPOINT_CHANGE#,CHECKPOINT_TIME,CHECKPOINT_COUNT,status,ERROR,RECOVER,FUZZY from v$datafile_header;SQL>
CHECKPOINT_CHANGE#
------------------
3259102
SQL>
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME
---------- ------------------ -------------------
1 3259102 2020-04-08 23:00:55
2 3259102 2020-04-08 23:00:55
3 3259102 2020-04-08 23:00:55
4 3259102 2020-04-08 23:00:55
5 3258999 2020-04-08 22:59:55
17 3259102 2020-04-08 23:00:55
6 rows selected.
SQL>
FILE# NAME STATUS TS# RFILE# RESETLOGS_CHANGE# RESETLOGS_TIME CHECKPOINT_CHANGE# CHECKPOINT_TIME CHECKPOINT_COUNT STATUS ERROR REC FUZ
---------- ------------------------------ ------- ---------- ---------- ----------------- ------------------- ------------------ ------------------- ---------------- ------- ---------- --- ---
1 /oradata/epmsn/system01.dbf ONLINE 0 1 2878267 2020-04-05 07:29:12 3259102 2020-04-08 23:00:5 491 ONLINE NO YES
2 /oradata/epmsn/sysaux01.dbf ONLINE 1 2 2878267 2020-04-05 07:29:12 3259102 2020-04-08 23:00:5 491 ONLINE NO YES
3 /oradata/epmsn/star01.dbf ONLINE 19 3 2878267 2020-04-05 07:29:12 3259102 2020-04-08 23:00:5 35 ONLINE NO YES
4 /oradata/epmsn/users01.dbf ONLINE 4 4 2878267 2020-04-05 07:29:12 3259102 2020-04-08 23:00:5 490 ONLINE NO YES
5 /oradata/epmsn/test01.dbf OFFLINE 6 5 2878267 2020-04-05 07:29:12 3259102 2020-04-08 23:00:5 4 OFFLINE YES YES
17 /oradata/epmsn/undo1.dbf ONLINE 18 17 2878267 2020-04-05 07:29:12 3259102 2020-04-08 23:00:5 32 ONLINE NO YES
6 rows selected.
SQL> set linesize 200 pagesize 200
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
select CHECKPOINT_CHANGE# from v$database;
select FILE#,CHECKPOINT_CHANGE#,CHECKPOINT_TIME from v$datafile;
select FILE#,STATUS,TS#,RFILE#,RESETLOGS_CHANGE#,RESETLOGS_SQL>
Session altered.
SQL>
CHECKPOINT_CHANGE#
------------------
3259102
SQL> TIME,CHECKPOINT_CHANGE#,CHECKPOINT_TIME,CHECKPOINT_COUNT from v$datafile_header;
select group#,thread#,sequence#,first_change#,next_change# from v$log;
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME
---------- ------------------ -------------------
1 3259102 2020-04-08 23:00:55
2 3259102 2020-04-08 23:00:55
3 3259102 2020-04-08 23:00:55
4 3259102 2020-04-08 23:00:55
5 3258999 2020-04-08 22:59:55
17 3259102 2020-04-08 23:00:55
6 rows selected.
SQL>
FILE# STATUS TS# RFILE# RESETLOGS_CHANGE# RESETLOGS_TIME CHECKPOINT_CHANGE# CHECKPOINT_TIME CHECKPOINT_COUNT
---------- ------- ---------- ---------- ----------------- ------------------- ------------------ ------------------- ----------------
1 ONLINE 0 1 2878267 2020-04-05 07:29:12 3259102 2020-04-08 23:00:55 491
2 ONLINE 1 2 2878267 2020-04-05 07:29:12 3259102 2020-04-08 23:00:55 491
3 ONLINE 19 3 2878267 2020-04-05 07:29:12 3259102 2020-04-08 23:00:55 35
4 ONLINE 4 4 2878267 2020-04-05 07:29:12 3259102 2020-04-08 23:00:55 490
5 OFFLINE 6 5 2878267 2020-04-05 07:29:12 3259102 2020-04-08 23:00:55 4
17 ONLINE 18 17 2878267 2020-04-05 07:29:12 3259102 2020-04-08 23:00:55 32
6 rows selected.
SQL>
GROUP# THREAD# SEQUENCE# FIRST_CHANGE# NEXT_CHANGE#
---------- ---------- ---------- ------------- ------------
1 1 54 3259098 3259102
2 1 53 3259093 3259098
3 1 55 3259102 2.8147E+14
SQL> alter database recover datafile 5;
Database altered.
SQL> alter database datafile 5 online;
Database altered.
SQL> set linesize 200 pagesize 200
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
select CHECKPOINT_CHANGE# from v$database;
select FILE#,CHECKPOINT_CHANGE#,CHECKPOINT_TIME from v$datafile;
select FILE#,STATUS,TS#,RFILE#,RESETLOGS_CHANGE#,RESETLOGS_SQL>
Session altered.
SQL> TIME,CHECKPOINT_CHANGE#,CHECKPOINT_TIME,CHECKPOINT_COUNT from v$datafile_header;
select group#,thread#,sequence#,first_change#,next_change# from v$log;
CHECKPOINT_CHANGE#
------------------
3259102
SQL>
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME
---------- ------------------ -------------------
1 3259102 2020-04-08 23:00:55
2 3259102 2020-04-08 23:00:55
3 3259102 2020-04-08 23:00:55
4 3259102 2020-04-08 23:00:55
5 3259381 2020-04-08 23:05:20
17 3259102 2020-04-08 23:00:55
6 rows selected.
SQL>
FILE# STATUS TS# RFILE# RESETLOGS_CHANGE# RESETLOGS_TIME CHECKPOINT_CHANGE# CHECKPOINT_TIME CHECKPOINT_COUNT
---------- ------- ---------- ---------- ----------------- ------------------- ------------------ ------------------- ----------------
1 ONLINE 0 1 2878267 2020-04-05 07:29:12 3259102 2020-04-08 23:00:55 491
2 ONLINE 1 2 2878267 2020-04-05 07:29:12 3259102 2020-04-08 23:00:55 491
3 ONLINE 19 3 2878267 2020-04-05 07:29:12 3259102 2020-04-08 23:00:55 35
4 ONLINE 4 4 2878267 2020-04-05 07:29:12 3259102 2020-04-08 23:00:55 490
5 ONLINE 6 5 2878267 2020-04-05 07:29:12 3259381 2020-04-08 23:05:20 6
17 ONLINE 18 17 2878267 2020-04-05 07:29:12 3259102 2020-04-08 23:00:55 32
6 rows selected.
SQL>
GROUP# THREAD# SEQUENCE# FIRST_CHANGE# NEXT_CHANGE#
---------- ---------- ---------- ------------- ------------
1 1 54 3259098 3259102
2 1 53 3259093 3259098
3 1 55 3259102 2.8147E+14
SQL>
文章转载自数据库技术笔记，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
