Oracle备份恢复-手动不完全恢复

dba基础课程：Oracle备份恢复-手动不完全恢复

平台：redhat linux as5,oracle10g

不完全恢复是指把数据库恢复到备份点和失败点之间某个时刻的状态,而且不完全恢复只适用于archivelog模式,不完全恢复可以基于时间点,基于SCN,基于控制文件或基于取消.下面用实例演示一下四种不完全恢复的用法.

一.基于时间的不完全恢复

一般当用户误删除表,误载断表,提交了错误数据后,DBA可以基于时间把数据库恢复到提交数据前的某一个状态

现在数据库中有一个表chenxy

SQL> select * from chenxy;

I

----------------------

10

20

先做个全备份(数据文件)

SQL> shutdown immediate;

SQL> ! cp /u01/oradata/denver/*.dbf /u01/backup

现看当前时间并载断表

SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'YY

-------------------

2009-05-05 23:23:11

SQL> truncate table t;

Table truncated.

现在我们把表chenxy恢复到2009-05-05 23:23:11

1.关闭数据库

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

2.装载数据库

SQL> startup mount;

ORACLE instance started.

Total System Global Area 420549952 bytes

Fixed Size 451904 bytes

Variable Size 385875968 bytes

Database Buffers 33554432 bytes

Redo Buffers 667648 bytes

Database mounted.

3.复制所有数据文件备份

SQL> ! cp /u01/backup/*.dbf /u01/oradata/denver/

注意备份的时间点要在2009-05-05 23:23:11之前,查看备份时间

SQL> select file#,to_char(time,'yyyy-mm-dd hh24:mi:ss') from v$recover_file;

FILE# TO_CHAR(TIME,'YYYY-

------------- -------------------

1 2009-05-05 23:20:18

2 2009-05-05 23:20:18

3 2009-05-05 23:20:18

4 2009-05-05 23:20:18

5 2009-05-05 23:20:18

6 2009-05-05 23:20:18

4.恢复到特定时间点

SQL> recover database until time '2009-05-05 23:23:24';

Media recovery complete.

5.以resetlogs方式打开数据库,并查看表chenxy

SQL> alter database open resetlogs;

Database altered.

SQL> select * from chenxy;

I

--------------------

10

20

当以resetlogs方式打开数据库时,会重新建立重做日志,清空原有重做日志的内容(同时归档日志也全部删除了),而且过去的备份也不能直接使用了,现在重新备份所有数据文件和控制文件,并归档当前日志组

SQL> shutdown immediate;

SQL> ! cp /u01/oradata/denver/*.dbf /u01/backup

SQL> ! cp /u01/oradata/denver/*.ctl /u01/backup

SQL>startup

SQL> alter system switch logfile;

6.简单排错

因为选项resetlogs要清空重做日志,当出现如下错误ORA-00338时可用resetlogs打开数据库

SQL> startup

ORACLE instance started.

Total System Global Area 420549952 bytes

Fixed Size 451904 bytes

Variable Size 385875968 bytes

Database Buffers 33554432 bytes

Redo Buffers 667648 bytes

Database mounted.

ORA-00338: log 2 of thread 1 is more recent than controlfile

ORA-00312: online log 2 thread 1: '/u01/oradata/denver/redo02.log'

使用resetlogs打开后一定要做备份.

SQL> alter database open resetlogs;

Database altered.

SQL> select status from v$instance;

STATUS

------------

OPEN

二.基于SCN恢复

1.查看当前SCN号

SQL> select current_scn from v$database;

#或

SQL> select dbms_flashback.get_system_change_number scn from dual;

SCN

--------------------

1673513

1.关闭数据库

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

2装载数据库

SQL> startup mount;

ORACLE instance started.

Total System Global Area 420549952 bytes

Fixed Size 451904 bytes

Variable Size 385875968 bytes

Database Buffers 33554432 bytes

Redo Buffers 667648 bytes

Database mounted.

3.复制所有数据文件备份

SQL> ! cp /u01/backup/*.dbf /u01/oradata/denver/

注意备份的SCN号要小于1673513,查看备份的SCN

SQL> select file#,change# from v$recover_file;

FILE# CHANGE#

---------- ----------

1 1673087

2 1673087

3 1673087

4 1673087

5 1673087

6 1673087

7 1673087

4.恢复到特定SCN

SQL> recover database until change 1673513;

Media recovery complete.

5.以resetlogs方式打开数据库,并查看表chenxy

SQL> alter database open resetlogs;

Database altered.

SQL> select * from chenxy;

I

--------------------

10

20

6.重新备份所有数据文件和控制文件,并归档当前日志组

SQL> shutdown immediate;

SQL> ! cp /u01/oradata/denver/*.dbf /u01/backup

SQL> ! cp /u01/oradata/denver/*.ctl /u01/backup

SQL>startup

SQL> alter system switch logfile;

三.基于取消不完全恢复

其于取消恢复主要适用于因归档日志丢失,只能恢复到某一个时间的情况,恢复方法和前面两种一样,只是恢复时点不同.

恢复

SQL>shutdown immediate;

SQL>startup mount;

SQL>! cp /u01/backup/*.dbf /u01/oradata/denver/

SQL> recover database until cancel;

Media recovery complete.

SQL>alter database open resetlogs;

四.基于备份控制文件恢复

原理:当用户误删除一个表空间时,当前的控制文件就把此表空间的信息都删了,但是在备份的控制文件里还是它的信息,所以可以用备份的控制文件恢复表空间

删除一个表空间

SQL> drop tablespace chenxy including contents;

Tablespace dropped.

SQL> select * from chenxy;

select * from chenxy

ERROR at line 1:

ORA-00942: table or view does not exist

到alter日志找到删除时间

SQL> show parameter background_dump_dest

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

background_dump_dest string /u01/admin/denver/bdump

SQL>! less /u01/admin/denver/bdump/alert_denver.log

Wed May 6 00:10:19 2009

drop tablespace chenxy including contents

Completed: drop tablespace chenxy including contents

恢复

SQL>shutdown immediate;

SQL>startup mount;

#此时把控制文件也复制过来

SQL>! cp /u01/backup/*.dbf /u01/oradata/denver/

SQL>! cp /u01/backup/*.bak /u01/oradata/denver/

SQL>recover database until time '2009-05-05 23:50:24' using backup controlfile

SQL>alter database open resetlogs

最后最好再做一个全备份.

来源：Oracle培训