如何把Oracle数据库恢复到2天前的状态？

ALLSTARS_ORACLE

服务器出了问题，重启后系统的时间该为2003年的某日，发现后，再重新启动数据库就启动不起来了，错误提示如下：
SQL> startup
ORACLE 例程已经启动。

Total System Global Area  135338868 bytes
Fixed Size                   453492 bytes
Variable Size             109051904 bytes
Database Buffers           25165824 bytes
Redo Buffers                 667648 bytes
数据库装载完毕。
ORA-00399: 重做日志中的更改说明已损坏
ORA-00353: 日志损坏接近块 3708 更改 66050163 时间 08/15/2005 15:06:01
ORA-00312: 联机日志 2 线程 1: 'E:\ORACLE\ORADATA\HEJNEWDB\REDO02.LOG'

我觉得可能是因为系统时间更改，使重做日志文件出了问题，那么，现在，我怎样恢复到系统出问题之前的数据库呢？


没有备份，我用sys连接上去看不到表

没有干净得shutdown，因为是意外重启，不过如果没有改时间得话，意外重启是可以启动数据库的


我试着按  melocy 前辈的话恢复了以下，但出现如下错误：

指定日志: {<RET>=suggested | filename | AUTO | CANCEL}
E:\oracle\oradata\HejNewDB\redo02.log
ORA-01547: 警告: RECOVER 成功但 OPEN RESETLOGS 将出现如下错误
ORA-01194: 文件1需要更多的恢复来保持一致性
ORA-01110: 数据文件 1: 'E:\ORACLE\ORADATA\HEJNEWDB\SYSTEM01.DBF'


坏的日志刚好是当前使用的日志，我按照时间点恢复到了20050814，之后启动出现了如下错误：
SQL> startup
ORACLE 例程已经启动。

Total System Global Area  135338868 bytes
Fixed Size                   453492 bytes
Variable Size             109051904 bytes
Database Buffers           25165824 bytes
Redo Buffers                 667648 bytes
数据库装载完毕。
ORA-01589: 要打开数据库则必须使用 RESETLOGS 或 NORESETLOGS 选项

Liu Maclean(刘相兵

尝试 ORA-01589: 要打开数据库则必须使用 RESETLOGS 或 NORESETLOGS 选项  
alter database open resetlogs ;

ALLSTARS_ORACLE

OERR: ORA-353 "log corruption near block %s change %s time %s" Reference Note

PURPOSE

This is a brief reference note to show the meaning of database error "ORA-353". It includes the error text, "Cause" and "Action" from message files for each database version from 9.2 onwards, along with search links and details of any database bug issues related to the error.
SCOPE

This note is intended for general audience as a starting point to check the meaning of "ORA-353". It does not give detailed information about the error and does not give error message text for versions below 9.2 .
DETAILS

Error Text, Cause and Action from Message File/s for ORA-00353

Versions 9.2, 10.1, 10.2, 11.1, 11.2, 12.1

Error:  ORA-00353 log corruption near block %s change %s time %s
---------------------------------------------------------------------------
Cause:  Some type of redo log corruption has been discovered. This error
        describes the location of the corruption. Accompanying errors describe
        the type of corruption.
Action: Do recovery with a good version of the log or do incomplete recovery
        up to the indicated change or time.
Search Links for ORA-00353

The links below can be used to locate ORA-353 in the documentation, and to search for documents that give more information about the error.
Search MOS for "ORA-00353 Troubleshooting"

Search 12.1 documentation for ORA-00353
Search 11.2 documentation for ORA-00353
Search 11.1 documentation for ORA-00353
Search 10.2 documentation for ORA-00353
Database Bugs Related to ORA-00353

Errors are usually due to usage, application or configuration issues but in some cases they may be caused by a bug issue. This section lists any database bugs that have been linked to error "ORA-353" .
You can restrict the list below to issues likely to affect one of the following versions by clicking the relevant button:
10.2.0.2 10.2.0.3 10.2.0.4 11.1.0.6 11.1.0.7 11.2.0.1 11.2.0.2 11.2.0.3 12.2.0.1 Show all Bugs


NB        Prob        Bug        Fixed        Description
II        14533497        11.2.0.4, 12.1.0.1        ORA-353 Redo Log Corruption messages from ASYNC Redo Shipping or Log Miner
III        13840711        11.2.0.3.14, 11.2.0.3.BP27, 11.2.0.4, 12.1.0.1        ORA-353 in Standby / Streams Data Capture or ORA-272 in PRIMARY: Redo log corruption by ASYNC redo shipping
I        9549707        11.2.0.2, 12.1.0.1        11g with COMPATIBLE=10.2 produces redo which is not 10.2 compatible (ORA-399)
II        9098116        10.2.0.5, 11.2.0.2, 12.1.0.1        ORA-368 ORA-353 in Standby for incomplete archive log when max_connections > 1
*        III        8768374        10.2.0.5, 11.1.0.7.8, 11.2.0.1.BP11, 11.2.0.2, 12.1.0.1        RFS in Standby with a wrong location for archived log corrupting/overwriting database files when max_connections > 1
I        13357662        11.2.0.2        ORA-353 ORA-354 Redo corruption by Archive although Dump Logfile is sucessfully done
II        6039415        10.2.0.4, 11.1.0.7, 11.2.0.1        ORA-355 can occur during real time apply
*        -        3806172        10.1.0.4, 10.2.0.1        Logical standby apply may stop with ORA-355 / ORA-353
*        -        554516        7.3.4.1        Silently corrupt ARCH logs using Async IO

ALLSTARS_ORACLE

ORA-00399: 重做日志中的更改说明已损坏
ORA-00353: 日志损坏接近块 3708 更改 66050163 时间 08/15/2005 15:06:01
ORA-00312: 联机日志 2 线程 1: 'E:\ORACLE\ORADATA\HEJNEWDB\REDO02.LOG'

一般说明 日志文件有损坏， 可能需要强制开库

biotwang

1. ORA-01589: 要打开数据库则必须使用 RESETLOGS 或 NORESETLOGS 选项

复制代码

你可以首先尝试是否可以使用alter database open resetlogs开库。

但是我估计应该是不行的。你的情况没有描述清楚，你宕机后数据库文件应该是当天的，而且你没有备份，那么你怎么通过recover将日志应用到昨天？？？还是说你通过的flashback database？

1. [oracle@m1 ~]$ oerr ora 00399
   
2. 00399, 0000, "corrupt change description in redo log"
   
3. // *Cause:  A change vector in the redo log failed validation checks.
   
4. // *Action: Do recovery with a good version of the log or do time based
   
5. //          recovery up to the indicated time.

复制代码

在线重做日志损坏了，说明当前redo日志不可用，且为突发情况的数据库关闭。因此，你只能尝试强行开库了。

biotwang

下文类似情况可做参考：

问题: 数据库启动时发生ORA-00354错误：

ORA-00354: corrupt redo log block header
ORA-00353: log corruption near block 32768 change 6715041179 time 04/23/2012 13:33:51

如何解决此ORA-00354报错?

回答: 你的日志文件头存在讹误损坏。如果这是个生产库，那么可以在MOS上开个SR寻求帮助。
oerr显示的ORA-00354信息如下：

1. ORA-00354: Corrupt redo log block header.
   
2. Cause: The block header on the redo block indicated by the accompanying error, is not reasonable.
   
3. Action: Do recovery with a good version of the log or do time based recovery up to the indicated time.
   
4. If this happens when archiving, archiving of the problem log can be skipped by clearing the log with the UNARCHIVED option.
   
5. This must be followed by a backup of every datafile to insure recoverability of the database.

复制代码

Oracle会推荐你使用隐藏参数来开库：

1. alter system set "_allow_restlogs_corruption"=true scope=both;

复制代码

之后检查为归档redo日志:
SQL> select * from v$log;

如果看到未归档日志文件，那么尝试使用以下命令进行归档清理：:

1. SQL> alter database clear unarchived logfile 'logilename';

复制代码

这样做，很明显你会丢一个重做日志，因此在开库后，一个全备是需要的。

123456

首先要有备份，然后归档不能丢，基于时间点恢复就行

123456

必须要有全备