### 1. 案例介绍 ```sql ORA-00279/ORA-00289/ORA-00280,Oracle数据库由于存储异常、人为误操作、归档日志误删除等原因， 由于缺失日志文件，造成offline的数据文件无法正常online,通常修复方式为通过bbed修改文件头信息进行修复， 本文将为您演示一键修复该问题，如果您也遇到该问题，请联系我们，免费为您提供Oracle数据库修复服务。 ``` ### 2. 异常模拟 ``` sql 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. 一键恢复操作 ```sql [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> ```