資料庫恢復案例大全

僅僅丟失一個普通使用者資料檔案的恢復A(聯機恢復)

（例如，丟失D:BACKUPDBUSERS01.DBF）

準備工作, 透過下面的工作，如果完全恢復，應該可以看到；insert into test1 values(2);

SQL> conn lunar/lunar

SQL> select * from tab;

TESTBACKUP3 TABLE

SQL> create table test1 (a number);

SQL> insert into test1 values(1);

SQL> alter system switch logfile;

SQL> commit;

SQL> alter system switch logfile;

SQL> insert into test1 values(2);

SQL> commit;

SQL> alter system switch logfile;

SQL> conn internal

SQL> archive log list

資料庫日誌模式 存檔模式

自動存檔 啟用

存檔終點 d:BACKUPDBarchive

最早的概要資訊日誌序列 3

下一個存檔日誌序列 5

當前日誌序列 5

shutdown abort關閉例程，模擬資料檔案丟失

SQL> shutdown abort

ORACLE 例程已經關閉。

Mount資料庫

SQL> startup mount

資料庫裝載完畢。

使損壞的資料檔案離線

SQL> alter database datafile 'D:BACKUPDBUSERS01.DBF' offline;

開啟資料庫

SQL> alter database open;

複製剛才熱備的資料檔案（USERS01.DBF）

恢復損壞的資料檔案

SQL> recover datafile 'D:BACKUPDBUSERS01.DBF';

ORA-00279: ?? 424116 (? 10/20/2002 20:42:04 ??) ???? 1 ????

ORA-00289: ??: D:BACKUPDBARCHIVEBACKUPT001S00001.ARC

ORA-00280: ?? 424116 ???? 1 ???? # 1 ???

指定日誌: {=suggested | filename | AUTO | CANCEL}

auto

ORA-00279: ?? 424125 (? 10/20/2002 20:44:14 ??) ???? 1 ????

ORA-00289: ??: D:BACKUPDBARCHIVEBACKUPT001S00002.ARC

ORA-00280: ?? 424125 ???? 1 ???? # 2 ???

ORA-00278: ??????????? 'D:BACKUPDBARCHIVEBACKUPT001S00001.ARC'

……………………..

已應用的日誌。

完成介質恢復。

使恢復完成的資料檔案聯機

SQL> alter database datafile 'D:BACKUPDBUSERS01.DBF' online;

驗證恢復的結果：完全恢復

SQL> select * from tab;

TNAME TABTYPE CLUSTERID

SQL> select * from test1;

說明：

1. shutdown abort關閉例程，模擬資料檔案丟失

2. Mount資料庫

3. 使損壞的資料檔案離線

4. 開啟資料庫

5. 複製剛才熱備的資料檔案（USERS01.DBF）

6. 恢復損壞的資料檔案

7. 使恢復完成的資料檔案聯機

shutdown immedate，恢復全部資料檔案(不包括control和redo) （把熱備的資料檔案複製回來，不包括control和redo）
SQL> conn internal
SQL> shutdown immediate;
複製全部熱備的資料檔案過來（完全恢復成功！）
mount資料庫
SQL> startup mount
完全恢復資料庫
SQL> recover database;
ORA-00279: change 424112 generated at 10/20/2002 20:40:52 needed for thread 1
ORA-00289: suggestion : D:BACKUPDBARCHIVEBACKUPT001S00001.ARC
ORA-00280: change 424112 for thread 1 is in sequence #1

Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 424125 generated at 10/20/2002 20:44:14 needed for thread 1
ORA-00289: suggestion : D:BACKUPDBARCHIVEBACKUPT001S00002.ARC
ORA-00280: change 424125 for thread 1 is in sequence #2
ORA-00278: log file 'D:BACKUPDBARCHIVEBACKUPT001S00001.ARC' no longer needed
for this recovery
……………………………………………..
Log applied.
Media recovery complete. 開啟資料庫
SQL> alter database open;
驗證恢復結果：完全恢復
SQL> conn lunar/lunar
SQL> select * from test1;

完全恢復成功！ 說明：
1. 複製全部熱備的資料檔案過來
2. mount資料庫
3. 完全恢復資料庫
4. 開啟資料庫

shutdown abort的情況，恢復全部控制檔案和資料檔案(不包括redo) 準備工作 （這樣，insert into test1 values(13);就是沒有提交的資料了，如果完全恢復，應該一直可以看到insert into test1 values(12);）
SQL> conn lunar/lunar
SQL> select * from test1;
SQL> insert into test1 values(12);

commit;
SQL> insert into test1 values(13);

單開一個session，用來shutdow abort
E:>sqlplus internal
SQL> shutdown abort
ORACLE 例程已經關閉。

複製所有的控制檔案和資料檔案（不包括redo） mount資料庫，按照提示重建口令檔案

SQL> startup mount ORACLE instance started. ORA-01991: invalid password file 'd:oracle1ora81DATABASEPWDbackup.ORA'
SQL> hostE:>cd d:oracle1ora81DATABASE D:oracle1ora81database>del PWDbackup.ORA D:oracle1ora81database>orapwd file=d:oracle1ora81DATABASEPWDbackup.ORA pa ssword=oracle entries=10
/* orapwd Usage: orapwd file= password= entries=

where

file - name of password file (mand),

password - password for SYS and INTERNAL (mand),

entries - maximum number of distinct DBAs and OPERs (opt),

There are no spaces around the equal-to (=) character. */

D:oracle1ora81database>exit 這時，試圖完全恢復資料庫是不成功的
SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done 用to trace備份控制檔案
SQL>alter database backup controlfile to trace;
SQL>shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down. 找到並且編輯控制檔案
STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE "BACKUP" NORESETLOGS ARCHIVELOG
MAXLOGFILES 32
MAXLOGMEMBERS 2
MAXDATAFILES 254
MAXINSTANCES 1
MAXLOGHISTORY 453
LOGFILE
GROUP 1 'D:BACKUPDBREDO01.LOG' SIZE 1M,
GROUP 2 'D:BACKUPDBREDO02.LOG' SIZE 1M,
GROUP 3 'D:BACKUPDBREDO03.LOG' SIZE 1M
DATAFILE
'D:BACKUPDBSYSTEM01.DBF',
'D:BACKUPDBRBS01.DBF',
'D:BACKUPDBUSERS01.DBF',
'D:BACKUPDBTEMP01.DBF',
'D:BACKUPDBTOOLS01.DBF',
'D:BACKUPDBINDX01.DBF'
CHARACTER SET ZHS16GBK;
RECOVER DATABASE
ALTER SYSTEM ARCHIVE LOG ALL;
ALTER DATABASE OPEN; 重建控制檔案
SQL> startup nomount
SQL> @D:BACKUPDBudumpORA01532.sql
ORA-01081: cannot start already-running ORACLE - shut it down first
Control file created.
ORA-00279: change 424112 generated at 10/20/2002 20:40:52 needed for thread 1
ORA-00289: suggestion : D:BACKUPDBARCHIVEBACKUPT001S00001.ARC
ORA-00280: change 424112 for thread 1 is in sequence #1
ORA-00308: cannot open archived log 'ALTER'
ORA-27041: unable to open file
OSD-04002: 無法開啟檔案
O/S-Error: (OS 2) 系統找不到指定的檔案。
ALTER DATABASE OPEN
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: 'D:BACKUPDBSYSTEM01.DBF'

shutdown immediate，然後重新恢復資料庫
SQL> shutdown immediate;
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
完全恢復資料庫
SQL> recover database;
ORA-00279: change 424112 generated at 10/20/2002 20:40:52 needed for thread 1
ORA-00289: suggestion : D:BACKUPDBARCHIVEBACKUPT001S00001.ARC
ORA-00280: change 424112 for thread 1 is in sequence #1

Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 424125 generated at 10/20/2002 20:44:14 needed for thread 1
ORA-00289: suggestion : D:BACKUPDBARCHIVEBACKUPT001S00002.ARC
ORA-00280: change 424125 for thread 1 is in sequence #2
ORA-00278: log file 'D:BACKUPDBARCHIVEBACKUPT001S00001.ARC' no longer needed
for this recovery
………………………………………..
Log applied.
Media recovery complete. 開啟資料庫
SQL> alter database open;
SQL> conn lunar/lunar
SQL> select * from test1;
說明：
1. 複製所有的控制檔案和資料檔案（不包括redo）
2. mount資料庫，按照提示重建口令檔案
3. 這時，試圖完全恢復資料庫是不成功的
4. 用to trace備份控制檔案
5. 找到並且編輯控制檔案
6. 重建控制檔案
7. shutdown immediate，然後重新恢復資料庫
8. 完全恢復資料庫
9. 開啟資料庫

僅僅丟失一個普通使用者資料檔案的恢復B(離線恢復) 準備工作 按照下面的輸入，如果全部恢復，應該可以看到insert into test1 values(13)，因為insert into test1 values(14)沒提交。
SQL> conn lunar/lunar
SQL> insert into test1 values(13);
SQL> insert into test1 values(14);

Shutdown immediate，然後模擬資料檔案丟失 單開一個session，執行shutdown immediate（保證insert into test1 values(14);沒有被隱式提交）
E:>sqlplus internal
SQL>shutdown immediate
ORACLE 例程已經關閉。 模擬資料檔案丟失，然後用熱備覆蓋這個檔案
mount資料庫
E:>sqlplus internal
SQL>shutdown immediate
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist

SQL> startup mount 使損壞的資料檔案離線
SQL>alter database datafile 'D:BACKUPDBUSERS01.DBF' offline;
Database altered. 恢復資料檔案
SQL> recover datafile 'D:BACKUPDBUSERS01.DBF';
ORA-00279: change 424116 generated at 10/20/2002 20:42:04 needed for thread 1
ORA-00289: suggestion : D:BACKUPDBARCHIVEBACKUPT001S00001.ARC
ORA-00280: change 424116 for thread 1 is in sequence #1

Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 424125 generated at 10/20/2002 20:44:14 needed for thread 1
ORA-00289: suggestion : D:BACKUPDBARCHIVEBACKUPT001S00002.ARC
ORA-00280: change 424125 for thread 1 is in sequence #2
ORA-00278: log file 'D:BACKUPDBARCHIVEBACKUPT001S00001.ARC' no longer needed
for this recovery
…………………………………………………………
Log applied.
Media recovery complete. 使恢復的資料檔案聯機
SQL>alter database datafile 'D:BACKUPDBUSERS01.DBF' online; 開啟資料庫
SQL>alter database open;
Database altered.
這時需要重新啟動資料庫，並完全恢復資料庫
SQL> conn lunar/lunar
SQL> select count(*) from test;
select count(*) from test
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> conn internal
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down. 重新啟動資料庫，
SQL> startup
ORACLE instance started.
用recover database再次恢復資料庫
SQL> conn internal
SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-01124: cannot recover data file 1 - file is in use or recovery
ORA-01110: data file 1: 'D:BACKUPDBSYSTEM01.DBF'
重新使恢復的表空間聯機

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/1724534/viewspace-982411/，如需轉載，請註明出處，否則將追究法律責任。