Oracle 各種檔案丟失的恢復
一、控制檔案損壞的恢復（一個檔案）
損壞單個控制檔案是比較容易恢復的，因為一般的資料庫系統，控制檔案都不是一個，而且所有的控制檔案都互為映象，只要拷貝一個好的控制檔案替換壞的控制檔案就可以了。
1、控制檔案損壞，最典型的就是啟動資料庫出錯，不能mount資料庫
SQL>startup
ORA-00205: error in identifying controlfile, check alert log for more info
檢視報警日誌檔案，有如下資訊
alter database mount
Mon May 26 11:59:52 2003
ORA-00202: controlfile: 'D:\Oracle\oradata\chen\control01.ctl'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) 系統找不到指定的檔案。
2、停止資料庫
SQL>shutdown immediate
3、拷貝一個好的控制檔案替換壞的控制檔案或修改init.ora中的控制檔案引數，取消這個壞的控制檔案。
4、重新啟動資料
SQL>startup
說明：
1、損失單個控制檔案是比較簡單的，因為資料庫中所有的控制檔案都是映象的，只需要簡單的拷貝一個好的就可以了
2、建議映象控制檔案在不同的磁碟上
3、建議多做控制檔案的備份，長期保留一份由alter database backup control file to trace產生的控制檔案的文字備份
二、Control檔案損壞的恢復(全部檔案)
損壞多個控制檔案，或者人為的刪除了所有的控制檔案，通過控制檔案的複製已經不能解決問題，這個時候需要重新建立控制檔案。
同時注意，alter database backup control file to trace可以產生一個控制檔案的文字備份。
以下是詳細重新建立控制檔案的步驟
1、關閉資料庫
SQL>shutdown immediate;
2、刪除所有控制檔案，模擬控制檔案的丟失
3、啟動資料庫，出現錯誤，並不能啟動到mount下
SQL>startup
ORA-00205: error in identifying controlfile, check alert log for more info
檢視報警日誌檔案，有如下資訊
alter database mount
Mon May 26 11:53:15 2003
ORA-00202: controlfile: 'D:\Oracle\oradata\chen\control01.ctl'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) 系統找不到指定的檔案。
4、關閉資料庫
SQL>shutdown immediate;
5、在internal或sys下執行如下建立控制檔案的指令碼，注意完整列出聯機日誌或資料檔案的路徑，或修改由alter database backup control file to trace備份控制檔案時產生的指令碼，去掉多餘的註釋即可。
STARTUP NOMOUNT;
CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01.LOG' SIZE 50M,
GROUP 2 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO02.LOG' SIZE 50M,
GROUP 3 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO03.LOG' SIZE 50M
DATAFILE
'F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF',
'F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF',
'F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF',
'F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF'
CHARACTER SET AL32UTF8;
RECOVER DATABASE;
ALTER SYSTEM ARCHIVE LOG ALL;
ALTER DATABASE OPEN;
ALTER TABLESPACE TEMP ADD TEMPFILE 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEMP01.DBF'
SIZE 20971520 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
6、如果沒有錯誤，資料庫將啟動到open狀態下。
說明：
1、重建控制檔案用於恢復全部資料檔案的損壞，需要注意其書寫的正確性，保證包含了所有的資料檔案與聯機日誌
2、經常有這樣一種情況，因為一個磁碟損壞，我們不能再恢復(store)資料檔案到這個磁碟，因此在store到另外一個盤的時候，我們就必須重新建立控制檔案，用於識別這個新的資料檔案，這裡也可以用這種方法用於恢復
三、無備份資料檔案的恢復
流程：
1. 新建表空間及表，向表裡插入一條資料；
2. 關閉資料庫；
3. 在作業系統刪除新建表空間的資料檔案；
4. 重新啟動資料庫，報錯；
5. 手動將資料檔案下線；
6. 開啟資料庫，先讓資料正常對外工作；
7. recover datafile ..（前提是從資料檔案誕生起日誌檔案都存在）
8. 手動將資料檔案上線；
[root@test ~]#su - oracle
-bash-3.2$ sqlplus /nolog
SQL*Plus: Release 10.2.0.4.0 - Production on Sat May 26 10:25:23 2012
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 1577058304 bytes
Fixed Size 2084264 bytes
Variable Size 301990488 bytes
Database Buffers 1258291200 bytes
Redo Buffers 14692352 bytes
Database mounted.
Database opened.
SQL> create tablespace app1 datafile '/oracle/ora10/oradata/ora10g/app02.dbf' size 10m;
Tablespace created.
SQL> conn test1/test1
Connected.
SQL> create table test2(id int,name char(10)) tablespace app1;
Table created.
SQL> insert into test2 select 0,'test0' from dual;
1 row created.
SQL> commit;
Commit complete.
SQL> insert into test2 select 1,'test1' from dual;
1 row created.
SQL> commit;
Commit complete.
SQL> select * from test2;
ID NAME
---------- ----------
0 test0
1 test1
SQL>
刪除資料檔案
[root@test ora10g]#ll
total 1601624
-rw-r----- 1 oracle oinstall 20979712 May 26 10:25 app01.dbf
-rw-r----- 1 oracle oinstall 10493952 May 26 10:26 app02.dbf
drwxr-xr-x 2 oracle oinstall 4096 May 26 10:25 archive
-rw-r--r-- 1 oracle oinstall 1056768 May 26 10:25 block.dbf
-rw-r----- 1 oracle oinstall 7159808 May 26 10:30 control01.ctl
-rw-r----- 1 oracle oinstall 7159808 May 26 10:30 control02.ctl
-rw-r----- 1 oracle oinstall 7159808 May 26 10:30 control03.ctl
-rw-r----- 1 oracle oinstall 7159808 May 26 10:30 control04.ctl
-rw-r----- 1 oracle oinstall 104865792 Feb 18 19:17 mytemp01.dbf
-rw-r----- 1 oracle oinstall 209723392 May 26 10:25 sysaux01.dbf
-rw-r----- 1 oracle oinstall 387981312 May 26 10:25 system01.dbf
-rw-r----- 1 oracle oinstall 443555840 May 26 09:43 temp01.dbf
-rw-r----- 1 oracle oinstall 536879104 May 26 10:25 users01.dbf
[root@test ora10g]#rm -f app02.dbf
SQL> conn / as sysdba
Connected.
SQL> shutdown immediate;
ORA-01116: error in opening database file 12
ORA-01110: data file 12: '/oracle/ora10/oradata/ora10g/app02.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL> col name format a40
SQL> select file#,status,name from v$datafile;
FILE# STATUS NAME
---------- ------- --------------------------------------------------
1 SYSTEM /oracle/ora10/oradata/ora10g/system01.dbf
2 ONLINE /oraundo/undotbs01.dbf
3 ONLINE /oracle/ora10/oradata/ora10g/sysaux01.dbf
4 ONLINE /oracle/ora10/oradata/ora10g/users01.dbf
5 ONLINE /oradatab/task01.dbf
6 ONLINE /oradataa/task01.dbf
7 ONLINE /oracle/ora10/oradata/ora10g/block.dbf
8 ONLINE /oraundo/test01.dbf
9 ONLINE /oraundo/DP01.dbf
10 ONLINE /oradataa/TBS_TESTDATA01.dbf
11 ONLINE /oracle/ora10/oradata/ora10g/app01.dbf
FILE# STATUS NAME
---------- ------- --------------------------------------------------
12 ONLINE /oracle/ora10/oradata/ora10g/app02.dbf
12 rows selected.
SQL> alter database datafile 12 offline;
Database altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1577058304 bytes
Fixed Size 2084264 bytes
Variable Size 301990488 bytes
Database Buffers 1258291200 bytes
Redo Buffers 14692352 bytes
Database mounted.
Database opened.
SQL> conn test1/test1
Connected.
SQL>
SQL> conn / as sysdba
Connected.
SQL> select file#,status,name from v$datafile;
FILE# STATUS NAME
---------- ------- --------------------------------------------------
1 SYSTEM /oracle/ora10/oradata/ora10g/system01.dbf
2 ONLINE /oraundo/undotbs01.dbf
3 ONLINE /oracle/ora10/oradata/ora10g/sysaux01.dbf
4 ONLINE /oracle/ora10/oradata/ora10g/users01.dbf
5 ONLINE /oradatab/task01.dbf
6 ONLINE /oradataa/task01.dbf
7 ONLINE /oracle/ora10/oradata/ora10g/block.dbf
8 ONLINE /oraundo/test01.dbf
9 ONLINE /oraundo/DP01.dbf
10 ONLINE /oradataa/TBS_TESTDATA01.dbf
11 ONLINE /oracle/ora10/oradata/ora10g/app01.dbf
FILE# STATUS NAME
---------- ------- --------------------------------------------------
12 RECOVER /oracle/ora10/oradata/ora10g/app02.dbf
12 rows selected.
SQL> recover datafile 12;
ORA-00283: recovery session canceled due to errors
ORA-01110: data file 12: '/oracle/ora10/oradata/ora10g/app02.dbf'
ORA-01157: cannot identify/lock data file 12 - see DBWR trace file
ORA-01110: data file 12: '/oracle/ora10/oradata/ora10g/app02.dbf'
SQL> alter database datafile 12 offline;
Database altered.
SQL> select file#,status,name from v$datafile;
FILE# STATUS NAME
---------- ------- --------------------------------------------------
1 SYSTEM /oracle/ora10/oradata/ora10g/system01.dbf
2 ONLINE /oraundo/undotbs01.dbf
3 ONLINE /oracle/ora10/oradata/ora10g/sysaux01.dbf
4 ONLINE /oracle/ora10/oradata/ora10g/users01.dbf
5 ONLINE /oradatab/task01.dbf
6 ONLINE /oradataa/task01.dbf
7 ONLINE /oracle/ora10/oradata/ora10g/block.dbf
8 ONLINE /oraundo/test01.dbf
9 ONLINE /oraundo/DP01.dbf
10 ONLINE /oradataa/TBS_TESTDATA01.dbf
11 ONLINE /oracle/ora10/oradata/ora10g/app01.dbf
FILE# STATUS NAME
---------- ------- --------------------------------------------------
12 RECOVER /oracle/ora10/oradata/ora10g/app02.dbf
12 rows selected.
SQL> recover datafile 12;
ORA-00283: recovery session canceled due to errors
ORA-01110: data file 12: '/oracle/ora10/oradata/ora10g/app02.dbf'
ORA-01157: cannot identify/lock data file 12 - see DBWR trace file
ORA-01110: data file 12: '/oracle/ora10/oradata/ora10g/app02.dbf'
--重構資料檔案
SQL> alter database create datafile '/oracle/ora10/oradata/ora10g/app02.dbf';
Database altered.
SQL> recover datafile 12;
Media recovery complete.
SQL> select * from test1.test2;
select * from test1.test2
*
ERROR at line 1:
ORA-00376: file 12 cannot be read at this time
ORA-01110: data file 12: '/oracle/ora10/oradata/ora10g/app02.dbf'
SQL> alter database datafile 12 online;
Database altered.
SQL> select * from test1.test2;
ID NAME
---------- --------------------------------------------------
0 test0
1 test1
SQL>
已將所有的資料找回來
四、有備份資料檔案的恢復
步驟：
1. 新建表空間及表，向表裡插入一條資料；
2. 關閉資料庫，冷備此表空間；
3. 重啟資料庫，再向表裡插入一條資料，並手工發生檢查點及日誌切換；
4. 關閉資料庫，在作業系統刪除新建表空間的資料檔案；
5. 啟動資料庫，報錯；
6. 手動將資料檔案下線；
7. 開啟資料庫，先讓資料庫正常對外工作；
8. 將原備份的檔案拷貝到目錄下；
9. recover datafile ..
10. 手動將資料檔案上線；
以上前提是資料庫處於歸檔模式
[root@test ~]#su - oracle
-bash-3.2$ sqlplus /nolog
SQL*Plus: Release 10.2.0.4.0 - Production on Sat May 26 09:21:22 2012
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 1577058304 bytes
Fixed Size 2084264 bytes
Variable Size 301990488 bytes
Database Buffers 1258291200 bytes
Redo Buffers 14692352 bytes
Database mounted.
Database opened.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /oracle/ora10/oradata/ora10g/archive
Oldest online log sequence 203
Next log sequence to archive 205
Current log sequence 205
SQL> create tablespace app datafile '/oracle/ora10/oradata/ora10g/app01.dbf' size 20m;
Tablespace created.
SQL>
SQL> create user test1 identified by test1
2 default tablespace app;
User created.
SQL> conn test1/test1
ERROR:
ORA-01045: user TEST1 lacks CREATE SESSION privilege; logon denied
Warning: You are no longer connected to ORACLE.
SQL> conn / as sysdba
Connected.
SQL> grant connect,resource to test1;
Grant succeeded.
SQL> conn test1/test1
Connected.
SQL> create table test (id int,name char(20));
Table created.
SQL> insert into test select 1,'test' from dual;
1 row created.
SQL> commit;
Commit complete.
SQL> conn / as sysdba
Connected.
SQL> alter system checkpoint;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
備份資料檔案
[root@test ora10g]#pwd
/oracle/ora10/oradata/ora10g
[root@test ora10g]#ll
total 1591360
-rw-r----- 1 oracle oinstall 20979712 May 26 09:49 app01.dbf
drwxr-xr-x 2 oracle oinstall 4096 May 26 09:32 archive
-rw-r--r-- 1 oracle oinstall 1056768 May 26 09:43 block.dbf
-rw-r----- 1 oracle oinstall 7159808 May 26 09:56 control01.ctl
-rw-r----- 1 oracle oinstall 7159808 May 26 09:56 control02.ctl
-rw-r----- 1 oracle oinstall 7159808 May 26 09:56 control03.ctl
-rw-r----- 1 oracle oinstall 7159808 May 26 09:56 control04.ctl
-rw-r----- 1 oracle oinstall 104865792 Feb 18 19:17 mytemp01.dbf
-rw-r----- 1 oracle oinstall 209723392 May 26 09:55 sysaux01.dbf
-rw-r----- 1 oracle oinstall 387981312 May 26 09:56 system01.dbf
-rw-r----- 1 oracle oinstall 443555840 May 26 09:43 temp01.dbf
-rw-r----- 1 oracle oinstall 536879104 May 26 09:43 users01.dbf
[root@test oracle]#cd cold
[root@test cold]#cp /oracle/ora10/oradata/ora10g/app01.dbf .
SQL> startup;
ORACLE instance started.
Total System Global Area 1577058304 bytes
Fixed Size 2084264 bytes
Variable Size 301990488 bytes
Database Buffers 1258291200 bytes
Redo Buffers 14692352 bytes
Database mounted.
Database opened.
SQL> conn test1/test1
Connected.
SQL> select * from test;
ID NAME
---------- --------------------
1 test
SQL> insert into test select 2,'test' from dual;
1 row created.
SQL> commit;
Commit complete.
SQL> conn / as sysdba
Connected.
SQL> alter system checkpoint;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> conn test1/test1
Connected.
SQL> select * from test;
ID NAME
---------- --------------------
1 test
2 test
SQL> conn / as sysdba
Connected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
刪除資料檔案
[root@test ora10g]#rm -f app01.dbf
SQL> startup;
ORACLE instance started.
Total System Global Area 1577058304 bytes
Fixed Size 2084264 bytes
Variable Size 301990488 bytes
Database Buffers 1258291200 bytes
Redo Buffers 14692352 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 11 - see DBWR trace file
ORA-01110: data file 11: '/oracle/ora10/oradata/ora10g/app01.dbf'
在作業系統修改許可權
[root@test ora10g]#chown oracle.oinstall app01.dbf
SQL> select * from v$recover_file;
FILE# ONLINE ONLINE_ ERROR CHANGE# TIME
---------- ------- ------- ------------------ ---------- ------------------
11 ONLINE ONLINE FILE NOT FOUND 0
SQL> alter database datafile 11 offline;
Database altered.
SQL> alter database open;
Database altered.
將備份的資料檔案拷貝回來
[root@test cold]#cp app01.dbf /oracle/ora10/oradata/ora10g
SQL> recover datafile 11;
Media recovery complete.
SQL> select file#,status from v$datafile;
FILE# STATUS
---------- -------
1 SYSTEM
2 ONLINE
3 ONLINE
4 ONLINE
5 ONLINE
6 ONLINE
7 ONLINE
8 ONLINE
9 ONLINE
10 ONLINE
11 OFFLINE
11 rows selected.
SQL> alter database datafile 11 online;
Database altered.
SQL> select file#,status from v$datafile;
FILE# STATUS
---------- -------
1 SYSTEM
2 ONLINE
3 ONLINE
4 ONLINE
5 ONLINE
6 ONLINE
7 ONLINE
8 ONLINE
9 ONLINE
10 ONLINE
11 ONLINE
11 rows selected.
SQL> select * from test1.test;
ID NAME
---------- --------------------
1 test
2 test
SQL>
第二條資料已找回
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16400082/viewspace-731524/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 資料庫各種檔案丟失恢復大全。資料庫
- Oracle Password檔案丟失的恢復Oracle
- oracle丟失日誌檔案的恢復( 轉)Oracle
- 恢復丟失的控制檔案
- 控制檔案丟失恢復
- 【控制檔案丟失恢復】
- Oracle備份與恢復【丟失資料檔案的恢復】Oracle
- 【恢復】Redo日誌檔案丟失的恢復
- Oracle不同檔案丟失/損壞的恢復方法Oracle
- 資料檔案丟失的恢復
- 控制檔案全部丟失的恢復
- 控制檔案部分丟失的恢復
- 控制檔案丟失的RMAN恢復
- 控制檔案丟失恢復(二)
- 控制檔案全部丟失恢復
- 資料檔案丟失如何恢復
- 剪下的檔案還能恢復嗎，恢復剪貼丟失的檔案
- RMAN恢復案例:丟失全部資料檔案恢復
- 非歸檔丟失日誌檔案的恢復
- 探索ORACLE之RMAN_07 控制檔案丟失恢復Oracle
- 丟失的隨身碟檔案如何恢復？
- RMAN完全恢復丟失的資料檔案
- 普通資料檔案丟失的恢復方法
- 資料檔案丟失損壞的恢復--
- REDO檔案丟失或者損壞的恢復
- 恢復案例：歸檔模式下丟失全部資料檔案的恢復模式
- 當前控制檔案全部丟失恢復
- oracle丟失inactive日誌檔案的恢復操作過程Oracle
- Oracle重做日誌檔案損壞或丟失後的恢復Oracle
- 歸檔模式下資料檔案丟失的恢復模式
- 【備份恢復】恢復 丟失已歸檔重做日誌檔案
- 恢復案例：無歸檔，掉電，控制檔案全部丟失恢復
- 恢復案例：無歸檔，丟失全部控制檔案、日誌檔案恢復案例
- RMAN中各種檔案的恢復方法
- oracle控制檔案的損壞或完全丟失的恢復辦法Oracle
- Oracle Redo丟失恢復方案Oracle
- rman恢復--歸檔模式有備份，丟失資料檔案的恢復模式
- rman恢復--歸檔模式無備份，丟失資料檔案的恢復模式