dba基礎(chǔ)課程：Oracle備份恢復(fù)-手動(dòng)完全恢復(fù)

平臺(tái)：redhat linux as5,oracle10g

備份和恢復(fù)對(duì)于數(shù)據(jù)庫(kù)來說是非常重要的,如果沒有有效的備份,那么數(shù)據(jù)的安全就是只空談,所以說備份重于一切,在oracle備份恢復(fù)(手動(dòng)備份)里已經(jīng)討論如何手動(dòng)備份整個(gè)數(shù)據(jù)庫(kù)以及單獨(dú)備份控制文件等,現(xiàn)在討論當(dāng)數(shù)據(jù)庫(kù)有損壞時(shí)如何利用備份恢復(fù)數(shù)據(jù).

恢復(fù)分為完全恢復(fù)和不完全恢復(fù),完全恢復(fù)是把數(shù)據(jù)庫(kù)恢復(fù)到失敗點(diǎn),不完全恢復(fù)是當(dāng)數(shù)據(jù)庫(kù)出現(xiàn)文件損壞時(shí),把數(shù)據(jù)庫(kù)恢復(fù)到備份點(diǎn)和失敗點(diǎn)之間的某個(gè)時(shí)間,

本文討論如何完全恢復(fù),先介紹在mount狀態(tài)及open狀態(tài)恢復(fù)數(shù)據(jù)的步驟,然后通過兩個(gè)實(shí)例看如何一步步的把損壞的數(shù)據(jù)恢復(fù)回來.

一.Mount狀態(tài)恢復(fù)步驟

1.啟動(dòng)數(shù)據(jù)庫(kù)到mount狀態(tài)

SQL>startup mount;

SQL>startup force mount;

2.轉(zhuǎn)儲(chǔ)備份的數(shù)據(jù)文件

SQL>! cp ‘backup.dbf’ ‘/xxx/aaa’

3.恢復(fù)數(shù)據(jù)文件

SQL>recover datafile file#

SQL>recover from ‘/u01/oracle/xxx.log’ datafile file#

SQL>recover database databasename

4.恢復(fù)數(shù)據(jù)庫(kù)為open狀態(tài)

SQL> alter database open;

Database altered.

二.open狀態(tài)恢復(fù)步驟

1,數(shù)據(jù)庫(kù)到open狀態(tài),損壞的數(shù)據(jù)文件脫機(jī)

如果現(xiàn)在數(shù)據(jù)庫(kù)為shutdown狀態(tài),先到mount狀態(tài)脫機(jī)損壞的數(shù)據(jù)文件,然后把數(shù)據(jù)庫(kù)open

SQL>startup mount;

SQL>startup force mount;

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

SQL>alter database datafile file# offline;

SQL>alter database open

如果現(xiàn)在數(shù)據(jù)庫(kù)為open狀態(tài),接在把損壞的數(shù)據(jù)文件脫機(jī)

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

SQL>alter database datafile file# offline;

2.轉(zhuǎn)儲(chǔ)備份的數(shù)據(jù)文件

SQL>! cp ‘backup.dbf’ ‘/xxx/aaa’

3.恢復(fù)數(shù)據(jù)文件

SQL>recover datafile file#

SQL>recover from ‘/u01/oracle/xxx.log’ datafile file#

SQL>recover tablespace tablespacename;

4.數(shù)據(jù)文件聯(lián)機(jī)

SQL>alter database datafile file# online;

三.實(shí)例

1.mount狀態(tài)恢復(fù)system表空間

在system表空間建一個(gè)表t,并插入數(shù)據(jù)

SQL> alter system switch logfile;

SQL> create table t(i number) tablespace system;

SQL> insert into t values(1);

SQL> insert into t values(2);

SQL> insert into t values(3);

SQL> commit;

SQL> alter system switch logfile;

SQL> select * from t;

I

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

1

2

3

關(guān)閉數(shù)據(jù)庫(kù),刪除數(shù)據(jù)文件system01.dbf

SQL> shutdown immediate;

SQL> ! mv /u01/oradata/denver/system01.dbf /u01/oradata/denver/system01.dbf.bak

啟動(dòng)數(shù)據(jù)庫(kù)

SQL> startup;

ORA-01157: cannot identify/lock data file 1 - see DBWR trace file

ORA-01110: data file 1: '/u01/oradata/denver/system01.dbf'

SQL> select status from v$instance;

STATUS

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

MOUNTED

#只能啟動(dòng)到mounted狀態(tài)

查看需要恢復(fù)的數(shù)據(jù)文件

SQL> col error format a30

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

FILE# ERROR CHANGE#

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

1 FILE NOT FOUND 0

查看一下恢復(fù)需要的archivelog

SQL> col archive_name format a30

SQL> select sequence#,archive_name from v$recovery_log;

SEQUENCE# ARCHIVE_NAME

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

29 /u01/oradata/denver/archive/1_29.dbf

#只要?dú)w檔日志1_29.dbf還存在就能恢復(fù)

轉(zhuǎn)儲(chǔ)備份的數(shù)據(jù)文件

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

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

FILE# ERROR CHANGE#

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

1 1661187

恢復(fù)

#1_29.dbf不在默認(rèn)歸檔目錄下

SQL> recover from '/u01/oradata/denver/archive/1_29.dbf datafile 1;

#1_29.dbf在默認(rèn)歸檔目錄下

SQL> recover datafile 1;

ORA-00279: change 1661187 generated at 05/05/2009 21:51:54 needed for thread 1

ORA-00289: suggestion : /u01/oradata/denver/archive/1_29.dbf

ORA-00280: change 1661187 for thread 1 is in sequence #29

Specify log: {=suggested | filename | AUTO | CANCEL}

auto

Log applied.

Media recovery complete.

改變數(shù)據(jù)庫(kù)為open狀態(tài),查看是否恢復(fù)成功

SQL> alter database open;

Database altered.

SQL> select * from t;

I

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

1

2

3

#可見恢復(fù)成功了

2.openu狀態(tài)恢復(fù)未備份的數(shù)據(jù)文件

先新建一個(gè)表空間chenxy,并在上面建表chenxy,如下:

SQL> create tablespace chenxy datafile '/u01/oradata/denver/chenxy.dbf' size 2M;

SQL> create table chenxy(i number) tablespace chenxy;

SQL> insert into chenxy values(10);

SQL> insert into chenxy values(20);

SQL> commit;

SQL> alter system switch logfile;

SQL> select * from chenxy;

I

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

10

20

刪除數(shù)據(jù)文件chenxy.dbf

SQL> ! mv /u01/oradata/denver/chenxy.dbf /u01/oradata/denver/chenxy.dbf.bak;

再查表chenxy就報(bào)錯(cuò)了

SQL> select * from chenxy;

ERROR at line 1:

ORA-00376: file 13 cannot be read at this time

ORA-01110: data file 13: '/u01/oradata/denver/chenxy.dbf'

查看需要恢復(fù)的文件

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

FILE# ERROR CHANGE#

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

13 FILE NOT FOUND 0

SQL> select status from v$datafile where file#=13;

STATUS

-------

RECOVER

因?yàn)闆]有備份,使用控制文件新建數(shù)據(jù)文件

SQL> alter database datafile 13 offline;

#如果有備份的數(shù)據(jù)文件

SQL>! cp backup.dbf '/u01/oradata/denver/chenxy.dbf';

#l因?yàn)闆]有備份,現(xiàn)在用控制文件新建數(shù)據(jù)文件

SQL> alter database create datafile '/u01/oradata/denver/chenxy.dbf';

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

FILE# ERROR CHANGE#

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

13 1662171

恢復(fù)并把數(shù)據(jù)文件13聯(lián)機(jī)

SQL> recover datafile 13;

Media recovery complete.

SQL> alter database datafile 13 online;

現(xiàn)在查chenxy表

SQL> select * from chenxy;

I

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

10

20

恢復(fù)成功了,可見只要?dú)w檔日志沒有丟失,沒有備份的數(shù)據(jù)文件也可以恢復(fù)回來.

其它

完全恢復(fù)的三個(gè)語(yǔ)句

recover database 用來恢復(fù)整個(gè)數(shù)據(jù)庫(kù)的所有數(shù)據(jù)文件,只能在mount狀態(tài)運(yùn)行

recover tablespace 用來恢復(fù)某個(gè)表空間的所有數(shù)據(jù)文件,只能在open狀態(tài)運(yùn)行

recover datafile 用來恢復(fù)某個(gè)數(shù)據(jù)文件,在mount,open狀態(tài)都可以運(yùn)行

恢復(fù)數(shù)據(jù)庫(kù)時(shí)用到的動(dòng)態(tài)性能視圖

v$recover_file 顯示需要恢復(fù)的數(shù)據(jù)文件

v$recovery_log 顯示恢復(fù)所需的歸檔日志,如果恢復(fù)所需的日志丟了,也就別想恢復(fù)了,

v$archived_log 顯示所有的歸檔日志

v$loghist 顯示歸檔日志SCN信息