A-A+

非归档下,单一数据文件OFFLINE 报ORA-01145解决方案

2013年02月18日 Backup&Recovery, TroubleShooting 暂无评论 阅读 1,213 次

群里的一个哥们反应他们开发库的一个问题。
问题描述:
非归档,未备份,报错如下:
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.

给我留言

Copyright © YallonKing 保留所有权利.   Theme  Ality

用户登录

分享到: