oracle 恢复指定时间点,利用归档日志恢复到指定时间点

由于误操作删除数据库中的重要数据或者删除了某个表，这时需要根据时间点进行恢复。

关闭数据库，将出错的数据库的数据文件(oradata或者database目录)进行物理全备份；

用最近备份的数据文件覆盖现在的数据文件(磁盘或者磁带上的备份)；copy

d:\backup\*.dbf d:\oracle\oradata\oracle\

将这一时间段产生的归档日志文件恢复到数据库的归档目录；

打开服务管理器。

SQL>connect sys/oracle as

sysdba -―以SYS用户连接

Connected.

SQL>shutdown

immediate――关闭数据库

ORA-01109: database not open

Database dismounted.

Oracle instance shut down.

SQL>startup

mount

――启动数据库到mount状态

Oracle instance started.

Total System Global

Area 130368780 bytes

Fixed

Size 70924 bytes

Variable

Size 51814400 bytes

Database

Buffers 78405632 bytes

Redo

Buffers 77824 bytes

Database mounted.

SQL>recover database until time

‘yyyy-mm-dd:hh:mm:ss’;

――指定恢复时间点

ORA-00279: change 220618 generated at 09/10/2002 13:59:06

needed for thread 1

ORA-00289: suggestion : F:\Oracle\ORADATA\Oracle\ARCHIVE

\ORCLT001S00009.ARC

ORA-00280: change 220618 for thread 1 is in sequence

#9

Specify log: {=suggested | filename | AUTO |

CANCEL}

Auto

――用自动选项恢复

Log applied.

ORA-00279: change 220720 generated at 09/10/2002 15:20:15

needed for thread 1

ORA-00289: suggestion :

F:\Oracle\ORADATA\Oracle\ARCHIVE\ORCLT001S00010.ARC

ORA-00280: change 220720 for thread 1 is in sequence

#10

ORA-00278: log file

'F:\Oracle\ORADATA\Oracle\ARCHIVE\ORCLT001S00009.ARC' no longer

needed for this recovery

Log applied.

ORA-00279: change 220768 generated at 09/10/2002 15:20:20

needed for thread 1

ORA-00289: suggestion :

F:\Oracle\ORADATA\Oracle\ARCHIVE\ORCLT001S00011.ARC

ORA-00280: change 220768 for thread 1 is in sequence

#11

ORA-00278: log file

'F:\Oracle\ORADATA\Oracle\ARCHIVE\ORCLT001S00010.ARC' no longer

needed for this recovery

ORA-00308: cannot open archived log

Media recovery complete.

SQL>alter database open

resetlogs

――用resetlogs打开数据库

数据库已更改

这时赶紧对数据库进行一次物理全备份。