[原创] 闪回数据库失败的解决方案(ORA-38753、ORA-01110)_MySQL, Oracle及数据库讨论区_Weblogic技术|Tuxedo技术|中间件技术|Oracle论坛|JAVA论坛|Linux/Unix技术|hadoop论坛

总帖数

每页帖数

1/1页

返回列表

发起投票

查看: 4779 | 回复: 0

主题： [原创] 闪回数据库失败的解决方案(ORA-38753、ORA-01110)

大红薯

注册用户

等级：少校
经验：1440
发帖：159
精华：0
注册：2011-7-21
状态：离线
发送短消息息给大红薯

加好友发送短消息息给大红薯

发消息

发表于：

2011-8-29 10:37:50 | [全部帖] [楼主帖]

楼主

适用于：

Oracle Server - Enterprise Edition - Version: 10.1.0.2 to 11.1.0.7 - Release: 10.1 to 11.1
Information in this document applies to any platform.

症状：

为了阻止生成不必要的闪回日志，一个或多个表空间的闪回功能被关闭。

现在有一个需要要“FLASHBACK DATABASE”到之前的一些时间点来恢复一些由于错误执行批处理作业产生的错误的数据更新。

但是，FLASHBACK DATABASE不会工作，如果任何的表空间闪回功能是关闭的话。它将产生如下错误：

SQL> flashback database to scn 8517809201835 ;
flashback database to scn 8517809201835
*
ERROR at line 1:
ORA-38753: Cannot flashback data file 8; no flashback log data.
ORA-01110: data file 8: '/home/oracle/nish01.dbf'

当查询相应表空间的数据文件的时候，我们发现闪回功能已经被关闭了（column flashback_on = NO）：

SQL> select a.file#, a.name file_name, b.ts#, b.name ts_name, b.flashback_on from v$datafile a, v$tablespace b where a.ts#=b.ts# ;
FILE# FILE_NAME TS# TS_NAME FLA
---------- -------------------------------------------------- ---------- ------------------- ---
1 /u01/app/oracle/oradata/d10gr2/system01.dbf 0 SYSTEM YES
2 /u01/app/oracle/oradata/d10gr2/undotbs01.dbf 1 UNDOTBS1 YES
3 /u01/app/oracle/oradata/d10gr2/sysaux01.dbf 2 SYSAUX YES
4 /u01/app/oracle/oradata/d10gr2/users01.dbf 4 USERS YES
5 /u01/app/oracle/oradata/d10gr2/10g_isc_corr.dbf 5 ISC_CORR YES
6 /home/oracle/tbs02.dbf 6 TBS YES
7 /home/oracle/tbs01.dbf 6 TBS YES
8 /home/oracle/nish01.dbf 7 NISH NO 9 /u01/app/oracle/oradata/d10gr2/streams_tbs.dbf 9 STREAMS_TBS YES
9 rows selected.

如果我们试着打开表空间的闪回功能，仍就出现相同的错误：

SQL> alter tablespace nish flashback on ;
Tablespace altered.
SQL> flashback database to scn 8517809201835 ;
flashback database to scn 8517809201835
*
ERROR at line 1:
ORA-38753: Cannot flashback data file 8; no flashback log data.
ORA-01110: data file 8: '/home/oracle/nish01.dbf'

再次，如果我们将该数据文件离线，我们不可以让它回来当在FLASHBACK DATABASE开启OPEN RESETLOGS。

SQL> alter database datafile 8 offline ;
Database altered.
SQL> flashback database to scn 8517809201835 ;
flashback database to scn 8517809201835
*
ERROR at line 1:
ORA-38795: warning: FLASHBACK succeeded but OPEN RESETLOGS would get error below
ORA-01245: offline file 8 will be lost if RESETLOGS is done
ORA-01110: data file 6: '/home/oracle/nish01.dbf'

执行TSPITR（表空间的恢复时间点）需要更长的时间，如果有多个表空间要恢复

原因

主要原因是由于要闪回到之前时间点的数据文件的闪回日志不可用。

Error: ORA-38753

记录：不能闪回数据文件％s;没有闪回日志数据。

原因：尝试执行FLASHBACK DATABASE失败因为文件没有足够的闪回日志数据覆盖闪回的时间。要么是文件没有启用闪回功能，要么就是闪回功能在要闪回的一些时间是关闭状态的。

动作：文件不能被闪回。该文件应该被离线或者丢弃表空间在执行FLASHBACK DATABASE

命令之前

如果对表空间的闪回功能是关闭的话，'FLASHBACK DATABASE' 的使用将会有些限制。它在ORACLE 文档中有提到：

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statement...
"flashback_mode_clause”

使用该条款连接ALTER DATABASE FLASHBACK子句到指定的表空间不管其是否可以使用FLASHBACK DATABASE操作。该子句是有用的如果你已经让数据库处于FLASHBACK模式但是你不希望oracle数据库为该表空间保留闪回日志。

该条款对临时表空间和对撤销表空间是无效的。

FLASHBACK ON

指定FLASHBACK ON 是将表空间改为FLASHBACK模式。ORACLE数据库将为该表空间保留闪回日志数据并且表空间可以使用FLASHBACK DATABASE操作。如果你忽略flashback_mode_clause，那么FLASHBACK ON是默认开启的。

FLASHBACK OFF

指定FLASHBACK OFF是将表空间改为非闪回模式，ORACLE数据库将不会为该表空间保留闪回日志。你必须将这些表空间里的数据文件离线或者丢弃它们，在你执行任何的

FLASHBACK DATABASE操作之前。或者你可以将整个表空间离线。在这两种情况下，数据库不会删除现存的闪回日志。

解决方案

这种问题的解决方案是做一个 'FLASHBACK DATABASE'并以READ ONLY模式打开数据库导出所要的数据。然后关闭数据库并且使用归档日志回复到当前时间点。这是一个比TSPITR ( Tablespace Point in Time Recovery )更快的选择，以应对大量表空间需要恢复到一些之前的时间点。

下面是内部的一个测试案例来达到相同的效果。在这个测试案例中，'NISH'表空间闪回功能是关闭的，它下面的数据文件是'/home/oracle/nish01.dbf'.

ORA-38753解决方案测试案例：

SQL> select flashback_on from v$database ;
FLASHBACK_ON
------------------
YES
1 row selected.
SQL> select name, flashback_on from v$tablespace ;
NAME FLA
------------------------------ ---
SYSTEM YES
UNDOTBS1 YES
SYSAUX YES
TEMP YES
USERS YES
ISC_CORR YES
TBS YES
NISH YES
STREAMS_TBS YES
9 rows selected.
SQL> alter database open ;
Database altered.
SQL> create table scott.fld_test ( a number ) ;
Table created.
SQL> insert into scott.fld_test values ( 1 ) ;
1 row created.
SQL> insert into scott.fld_test values ( 2) ;
1 row created.
SQL> commit ;
Commit complete.
SQL> col systimestamp format a50
SQL> /
SYSTIMESTAMP CURRENT_SCN
-------------------------------------------------- ---------------
20-DEC-09 09.17.59.820753 PM +05:30 8517809201835
1 row selected.
SQL> alter system switch logfile ;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> alter tablespace nish flashback off ;
Tablespace altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 264241152 bytes
Fixed Size 1218844 bytes
Variable Size 109053668 bytes
Database Buffers 146800640 bytes
Redo Buffers 7168000 bytes
Database mounted.
SQL> alter database open ;
Database altered.
SQL> drop table scott.fld_test purge ;
Table dropped.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 264241152 bytes
Fixed Size 1218844 bytes
Variable Size 109053668 bytes
Database Buffers 146800640 bytes
Redo Buffers 7168000 bytes
Database mounted.
SQL> flashback da
2
SQL> flashback database to scn 8517809201835 ;
flashback database to scn 8517809201835
*
ERROR at line 1:
ORA-38753: Cannot flashback data file 8; no flashback log data.
ORA-01110: data file 8: '/home/oracle/nish01.dbf'
SQL> select a.file#, a.name file_name, b.ts#, b.name ts_name, b.flashback_on from v$datafile a, v$tablespace b where a.ts#=b.ts# ;
FILE# FILE_NAME TS# TS_NAME FLA
---------- -------------------------------------------------- ---------- ------------------- ---
1 /u01/app/oracle/oradata/d10gr2/system01.dbf 0 SYSTEM YES
2 /u01/app/oracle/oradata/d10gr2/undotbs01.dbf 1 UNDOTBS1 YES
3 /u01/app/oracle/oradata/d10gr2/sysaux01.dbf 2 SYSAUX YES
4 /u01/app/oracle/oradata/d10gr2/users01.dbf 4 USERS YES
5 /u01/app/oracle/oradata/d10gr2/10g_isc_corr.dbf 5 ISC_CORR YES
6 /home/oracle/tbs02.dbf 6 TBS YES
7 /home/oracle/tbs01.dbf 6 TBS YES
8 /home/oracle/nish01.dbf 7 NISH NO 9 /u01/app/oracle/oradata/d10gr2/streams_tbs.dbf 9 STREAMS_TBS YES
9 rows selected.
SQL> alter database datafile 8 offline ;
Database altered.
SQL> flashback database to scn 8517809201835 ;
Flashback complete.
SQL> alter database open read only ;
Database altered.
SQL> select * from scott.fld_test ; ---
A
---------------
1
2
2 rows selected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 264241152 bytes
Fixed Size 1218844 bytes
Variable Size 109053668 bytes
Database Buffers 146800640 bytes
Redo Buffers 7168000 bytes
Database mounted.
SQL> recover database ;
ORA-00279: change 8517809201836 generated at 12/20/2009 21:18:00 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/D10GR2/archivelog/2009_12_20/o1_mf_1_269_%u_.arc
ORA-00280: change 8517809201836 for thread 1 is in sequence #269
Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 8517809201842 generated at 12/20/2009 21:18:09 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/D10GR2/archivelog/2009_12_20/o1_mf_1_270_%u_.arc
ORA-00280: change 8517809201842 for thread 1 is in sequence #270
ORA-00278: log file
'/u01/app/oracle/flash_recovery_area/D10GR2/archivelog/2009_12_20/o1_mf_1_269_5lwkssds_.arc' no longer needed for this recovery
ORA-00279: change 8517809201847 generated at 12/20/2009 21:18:10 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/D10GR2/archivelog/2009_12_20/o1_mf_1_271_%u_.arc
ORA-00280: change 8517809201847 for thread 1 is in sequence #271
ORA-00278: log file
'/u01/app/oracle/flash_recovery_area/D10GR2/archivelog/2009_12_20/o1_mf_1_270_5lwkstj2_.arc' no longer needed for this recovery
Log applied.
Media recovery complete.
SQL> alter database open ;
Database altered.
SQL> alter database datafile 8 online ;
Database altered.
SQL> select * from scott.fld_test ; select * from scott.fld_test
*
ERROR at line 1:
ORA-00942: table or view does not exist

参考文献

NOTE:289285.1 - OERR: ORA-38753 Cannot flashback data file %s; no flashback log data.
NOTE:304712.1 - Error During Flashback Database Ora-38753

显示相关信息

产品

* Oracle Database Products > Oracle Database > Oracle Database > Oracle Server - Enterprise Edition

关键词

V$TABLESPACE; FLASHBACK; V$TABLESPACE

错误号

ORA-38795; ORA-942; ORA-38753; ORA-280; ORA-1245; ORA-279; ORA-1110; ORA-289; ORA-278