Email: service@parnassusdata.com 7 x 24 online support!

Home

ダウンロード DBRECOVER

    You are here

    • You are here:
      • Home > Blogs > PDSERVICE's blog > Oracle ora-1245 Workaround for Flashback Database fails with ORA-38753 ORA-01110

Oracle ora-1245 Workaround for Flashback Database fails with ORA-38753 ORA-01110

Oracle ora-1245 Workaround for Flashback Database fails with ORA-38753 ORA-01110

Posted by PDSERVICE on Mar 23, 2016 In

如果自己搞不定可以找诗檀软件专业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, 2023, parnassusdata.com. Copyright dbrecover.com