### 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

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

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

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>
```

ITPub博客

ORA-00279异常处理_offline数据文件缺失日志文件问题一键修复

最新文章