Oracle備份與恢復 (zt)

僅僅丟失一個普通使用者資料檔案的恢復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. 恢復損壞的資料檔案
使恢復完成的資料檔案聯機
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> host
E:>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'
重新使恢復的表空間聯機
SQL> alter database datafile 'D:BACKUPDBUSERS01.DBF' online;
SQL> conn lunar/lunar
SQL> select * from test1; ok.
驗證恢復結果：完全恢復
說明：
1. 用熱備覆蓋這個檔案
2. mount資料庫
3. 使損壞的資料檔案離線
4. 恢復資料檔案
5. 使恢復的資料檔案聯機
6. 開啟資料庫
7. 這時需要重新啟動資料庫，並完全恢復資料庫
8. 重新啟動資料庫，
9. 用recover database再次恢復資料庫
10. 重新使恢復的表空間聯機
shutdown abort後，丟失全部檔案(除了archive log和init.ora)即，丟失了全部資料檔案、控制檔案和redo log file
準備工作 下面的資訊說明了如果是完全恢復，可以看到insert into test1 values(16);，否則可以看到15，就是被歸檔的那個。17因為沒有提交，是不會被恢復的。
SQL> conn internal
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination d:BACKUPDBarchive
Oldest online log sequence 14
Next log sequence to archive 16
Current log sequence 16
SQL> conn lunar/lunar
SQL> select * from test1 where a>10;
SQL> insert into test1 values(15);
SQL> alter system switch logfile;
System altered.
SQL> insert into test1 values(16);
SQL> insert into test1 values(17);
新開一個session，進行shutdown abort
E:>sqlplus internal
SQL> shutdown abort
ORACLE 例程已經關閉。
把熱備的資料檔案和控制檔案複製過來
mount資料庫
E:>sqlplus internal
SQL> startup mount
ORACLE instance started.
ORA-01991: invalid password file 'd:oracle1ora81DATABASEPWDbackup.ORA'
根據提示重建口令檔案
SQL> host
E:>del d:oracle1ora81DATABASEPWDbackup.ORA
E:>orapwd file=d:oracle1ora81DATABASEPWDbackup.ORA password=oracle entries=
10
用to trace備份控制檔案
SQL> alter database backup controlfile to trace;
Database altered.
找到這個跟蹤檔案並編輯它
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> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 25856028 bytes
Fixed Size 75804 bytes
Variable Size 8925184 bytes
Database Buffers 16777216 bytes
Redo Buffers 77824 bytes
SQL> @D:BACKUPDBudumpORA02176.sql
ORA-01081: cannot start already-running ORACLE - shut it down first
CREATE CONTROLFILE REUSE DATABASE "BACKUP" NORESETLOGS ARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01565: error in identifying file 'D:BACKUPDBREDO01.LOG'
ORA-27041: unable to open file
OSD-04002: 無法開啟檔案
O/S-Error: (OS 2) 系統找不到指定的檔案。
ORA-01507: database not mounted
ALTER SYSTEM ARCHIVE LOG ALL
*
ERROR at line 1:
ORA-01507: database not mounted
ALTER DATABASE OPEN
*
ERROR at line 1:
ORA-01507: database not mounted
可見，因為缺少所有的redo，重建控制檔案是行不通的。
Mount資料庫
SQL> alter database mount;
Database altered.
用using backup controlfile進行恢復
SQL> alter database mount;
Database altered.
SQL> recover database until cancel using backup controlfile;
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
⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯⋯
ORA-00308: cannot open archived log 'D:BACKUPDBARCHIVEBACKUPT001S00017.ARC'
ORA-27041: unable to open file
OSD-04002: 無法開啟檔案
O/S-Error: (OS 2) 系統找不到指定的檔案。
用Open Resetlog 開啟資料庫
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> alter database open RESETLOGS;
Database altered.
驗證恢復結果：不完全恢復，redo裡面的資料丟失了
SQL> conn lunar/lunar
SQL> select * from test1 where a>10;
SQL> conn internal
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination d:BACKUPDBarchive
Oldest online log sequence 0
Next log sequence to archive 1
Current log sequence 1
說明：
1. 把熱備的資料檔案和控制檔案複製過來
2. mount資料庫
3. 根據提示重建口令檔案
4. 用using backup controlfile進行恢復
5. 用Open Resetlog 開啟資料庫
shutdown abort的情況，恢復全部控制檔案(不包括資料檔案和redo)
準備工作
以下說明，如果完全恢復資料庫，應該可以看到insert into test1 values(7);
SQL> insert into test1 values(3);
SQL> insert into test1 values(4);
SQL> commit;
SQL> alter system switch logfile;
SQL> conn internal
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination d:BACKUPDBarchive
Oldest online log sequence 8
Next log sequence to archive 10
Current log sequence 10
SQL> select * from test1;
SQL> insert into test1 values(5);
SQL> commit;
SQL> insert into test1 values(6);
SQL> alter system switch logfile;
System altered.
SQL> conn internal
SQL> conn lunar/lunar
SQL> insert into test1 values(7);
1 row created.
SQL> shutdown abort;
ORA-01031: insufficient privileges
SQL> conn internal
SQL> shutdown abort;
ORACLE instance shut down.
刪除那個控制檔案，把熱備的控制檔案複製過來
mount資料庫
SQL> startup mount
ORACLE instance started.
ORA-01991: invalid password file 'd:oracle1ora81DATABASEPWDbackup.ORA'
根據提示，重建口令檔案
SQL> host
E:>cd d:oracle1ora81DATABASE
E:>d:
D:oracle1ora81database>del PWDbackup.ORA
D:oracle1ora81database>dir
驅動器 D 中的卷是 Program
卷的序列號是 D0E6-FA1C
D:oracle1ora81database 的目錄
2002-10-21 00:42