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

最后最好再做一個全備份.