7 x 24 在线支持！
Oracle ora-1245 Workaround for Flashback Database fails with ORA-38753 ORA-01110
如果自己搞不定可以找诗檀软件专业ORACLE数据库修复团队成员帮您恢复!
诗檀软件专业数据库修复团队
服务热线 ： 13764045638 QQ号:47079569 邮箱：service@parnassusdata.com
oerr ora 1245 01245, 00000, "offline file %s will be lost if RESETLOGS is done" // *Cause: Attempting to do an OPEN RESETLOGS with a file that will be lost // because it is offline. The file was not taken offline with the // FOR DROP option. // *Action: Either bring the file online and recover it, or take it offline // with the FOR DROP option.
症状
一个或多个表空间的闪回被关闭，以避免不必要的闪回日志的生成。
现在，有必要做一个“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 ( Tablespace Point in Time Recovery )花费的时间会更长。
原因
主要原因是闪回日志不能用于将数据文件闪回到之前的时间点。
错误: ORA-38753
Text: Cannot flashback data file %s; no flashback log data.
—————————————————————————
原因: 尝试执行FLASHBACK DATABASE 失败，因为文件没有足够的闪回日志数据来覆盖闪回的时间。要么是该文件没有启用的flashback generation，要么是在闪回期间的某个时间关闭了flashback generation。
对策: 文件不能被闪回，文件必须离线，或在用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使表空间脱离FLASHBACK 模式。 Oracle 数据库不将保存该表空间的任何闪回日志数据. 你必须使该表空间的数据文件离线或在任何后续FLASHBACK DATABASE操作之前删除它们。或者，你可使整个表空间离线。不管是哪一种情况，数据库都不会删除现存的闪回日志。 ”
解决方案
该问题的解决方法是执行’FLASHBACK DATABASE’，并以 READ ONLY 模式打开数据库以导出所需数据。然后关闭数据库
用归档日志恢复到当前时间点。这是一个比TSPITR ( Tablespace Point in Time Recovery )更快的选项。
以防有许多表空间需要恢复到以前的时间点。
下面是一个内部测试用例以实现同样效果。在该测试用例中，表空间’NISH’的闪回被关闭，其底层数据文件是名为’/home/oracle/nish01.dbf’的 file# 8.
测试用例，可作为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: {<RET>=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
Copyright © 2013, 2019, parnassusdata.com. 版权所有。 dbrecover.com
沪公网安备 31010802001377号