A-A+
非归档下，单一数据文件OFFLINE 报ORA-01145解决方案
群里的一个哥们反应他们开发库的一个问题。
问题描述：
非归档，未备份，报错如下：
ORA-01113: file 17 needs media recovery
ORA-01110: data file 17: '/home/oracle/oradata/employ/TS_WB_BASIC_1.dat'
该问题是单数据文件（非系统数据文件）在数据库打开时出现问题，数据库使其自动offline之后的单数据文件恢复一例。
这个可以先将数据库open对外服务，然后再对那个问题文件进行恢复即可。
但是问题的是非归档，正常的数据文件offline会报错。
当然，实在不行也可以使用隐含参数将数据库不一致性打开，将数据导出，再导入到新的库也可绕过该问题。
不过还是建议将库置为归档模式，即使是开发库。这样以后很多问题都好处理。
下边是一个可用的小demo仅供参考。
查看数据库版本
SQL> select * from v$version; BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod PL/SQL Release 10.2.0.1.0 - Production CORE 10.2.0.1.0 Production TNS for Linux: Version 10.2.0.1.0 - Production NLSRTL Version 10.2.0.1.0 - Production
查看归档模式
SQL> ARCHIVE LOG LIST; Database log mode No Archive Mode Automatic archival Disabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 27 Current log sequence 29
查看测试数据
SQL> SELECT TABLESPACE_NAME,FILE_NAME FROM dba_data_files where tablespace_name like 'TEST';
TABLESPACE_NAME FILE_NAME
-------------------- --------------------------------------------------
TEST /u01/app/oracle/oradata/ora10gr2/test01.dbf
SQL> select TABLESPACE_NAME,table_name,owner from dba_tables where table_name like 'TEST';
TABLESPACE_NAME TABLE_NAME OWNER
-------------------- ------------------------------ ------------------------------
TEST TEST TEST
SQL> INSERT INTO TEST.TEST VALUES (1,'YALLONKING');
1 row created.
SQL> COMMIT;
Commit complete.
SQL> SELECT * FROM TEST.TEST;
ID NAME
---------- --------------------
1 YALLONKING
模拟数据库其中一个文件在正常启动时出现问题
SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> ! [oracle@ora10gr2 ~]$ cp /u01/app/oracle/oradata/ora10gr2/test01.dbf /u01/test01.dbf [oracle@ora10gr2 ~]$ rm -f /u01/app/oracle/oradata/ora10gr2/test01.dbf [oracle@ora10gr2 ~]$ exit exit SQL> startup ORACLE instance started. Total System Global Area 608174080 bytes Fixed Size 1220844 bytes Variable Size 205524756 bytes Database Buffers 398458880 bytes Redo Buffers 2969600 bytes Database mounted. ORA-01157: cannot identify/lock data file 5 - see DBWR trace file ORA-01110: data file 5: '/u01/app/oracle/oradata/ora10gr2/test01.dbf'
查看数据文件头部信息
SQL> select FILE#,STATUS,ERROR,RECOVER,CHECKPOINT_CHANGE#,LAST_DEALLOC_SCN from V$DATAFILE_HEADER;
FILE# STATUS ERROR REC CHECKPOINT_CHANGE# LAST_DEALLOC_SCN
---------- ------- ----------------------------------------------------------------- --- ------------------ ----------------
1 ONLINE NO 502271
2 ONLINE NO 502271
3 ONLINE NO 502271
4 ONLINE NO 502271
5 ONLINE FILE NOT FOUND 0
6 ONLINE NO 502271
6 rows selected.
将相关文件离线并使数据库正常读写
SQL> select open_mode from v$database; OPEN_MODE ---------- MOUNTED SQL> alter database datafile 5 offline; alter database datafile 5 offline * ERROR at line 1: ORA-01145: offline immediate disallowed unless media recovery enabled
注：非归档下，不能直接将文件立即离线。
SQL> alter database datafile 5 offline drop; Database altered. SQL> alter database open; Database altered.
开始恢复
SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount; ORACLE instance started. Total System Global Area 608174080 bytes Fixed Size 1220844 bytes Variable Size 205524756 bytes Database Buffers 398458880 bytes Redo Buffers 2969600 bytes Database mounted.
模拟问题文件已经恢复完成
SQL> ! [oracle@ora10gr2 ~]$ cp /u01/test01.dbf /u01/app/oracle/oradata/ora10gr2/test01.dbf [oracle@ora10gr2 ~]$ exit exit
再次查看文件头信息
SQL> select FILE#,STATUS,ERROR,RECOVER,CHECKPOINT_CHANGE#,LAST_DEALLOC_SCN from V$DATAFILE_HEADER;
FILE# STATUS ERROR REC CHECKPOINT_CHANGE# LAST_DEALLOC_SCN
---------- ------- ----------------------------------------------------------------- --- ------------------ ----------------
1 ONLINE NO 502709
2 ONLINE NO 502709
3 ONLINE NO 502709
4 ONLINE NO 502709
5 OFFLINE YES 502271
6 ONLINE NO 502709
6 rows selected.
注：发现此处恢复的文件滞后不同步，且需进行该文件的恢复，如果该处所有可用的日志文件均未覆盖，则可进行完全恢复，否则只能将文件restore做不完全介质恢复。
SQL> recover datafile 5; Media recovery complete. SQL> alter database datafile 5 online; Database altered.
发现此时文件头还没有及时同步，也可见ckpt不是实时运行的。
SQL> select FILE#,STATUS,ERROR,RECOVER,CHECKPOINT_CHANGE#,LAST_DEALLOC_SCN from V$DATAFILE_HEADER;
FILE# STATUS ERROR REC CHECKPOINT_CHANGE# LAST_DEALLOC_SCN
---------- ------- ----------------------------------------------------------------- --- ------------------ ----------------
1 ONLINE NO 502709
2 ONLINE NO 502709
3 ONLINE NO 502709
4 ONLINE NO 502709
5 ONLINE NO 502536
6 ONLINE NO 502709
6 rows selected.
SQL> alter database open;
Database altered.
SQL> select * from test.test;
ID NAME
---------- --------------------
1 YALLONKING
再次查看文件头信息，可见已经发生checkpoint使文件头同步。
SQL> select FILE#,STATUS,ERROR,RECOVER,CHECKPOINT_CHANGE#,LAST_DEALLOC_SCN from V$DATAFILE_HEADER;
FILE# STATUS ERROR REC CHECKPOINT_CHANGE# LAST_DEALLOC_SCN
---------- ------- ----------------------------------------------------------------- --- ------------------ ----------------
1 ONLINE NO 502710
2 ONLINE NO 502710
3 ONLINE NO 502710
4 ONLINE NO 502710
5 ONLINE NO 502710
6 ONLINE NO 502710
6 rows selected.
