與控制檔案有關的恢復(二)
由此可以引出，建立控制檔案時丟失資料檔案的解決方法（條件：丟失的資料檔案要有備份和備份以來的所有歸檔日誌）
SQL> CREATE CONTROLFILE REUSE DATABASE "TS" NORESETLOGS ARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 4674
7 LOGFILE
8 GROUP 1 'D:ORACLEPRODUCT10.2.0ORADATATSREDO01.LOG' SIZE 50M,
9 GROUP 2 'D:ORACLEPRODUCT10.2.0ORADATATSREDO02.LOG' SIZE 50M,
10 GROUP 3 'D:ORACLEPRODUCT10.2.0ORADATATSREDO03.LOG' SIZE 50M
11 -- STANDBY LOGFILE
12 DATAFILE
13 'D:ORACLEPRODUCT10.2.0ORADATATSSYSTEM01.DBF',
14 'D:ORACLEPRODUCT10.2.0ORADATATST.DBF',
15 'D:ORACLEPRODUCT10.2.0ORADATATSSYSAUX01.DBF',
16 'D:ORACLEPRODUCT10.2.0ORADATATSUSERS01.DBF',
17 'D:ORACLEPRODUCT10.2.0ORADATATSEXAMPLE01.DBF',
18 'D:ORACLEPRODUCT10.2.0ORADATATSBLOCK.DBF',
19 'D:ORACLEPRODUCT10.2.0ORADATATSTEST.DBF',
20 'D:ORACLEPRODUCT10.2.0ORADATATSBBED.DBF',
21 'D:ORACLEPRODUCT10.2.0ORADATATSA0.DBF',
22 'D:ORACLEPRODUCT10.2.0ORADATATSFLM.DBF',
23 'D:ORACLEPRODUCT10.2.0ORADATATSASSM.DBF',
24 'D:ORACLEPRODUCT10.2.0ORADATATSUNDOTBS.DBF',
25 'D:ORACLEPRODUCT10.2.0ORADATATST1.DBF',
26 'D:ORACLEPRODUCT10.2.0ORADATATSDEMO.DBF',
27 'D:ORACLEPRODUCT10.2.0ORADATATSDUMMY.DBF',
28 'D:ORACLEPRODUCT10.2.0ORADATATSA.DBF'
29 CHARACTER SET ZHS16GBK
30 /
Control file created.//控制檔案建立時丟失表空間b下的資料檔案b.dbf
SQL> alter database open;
Database altered.
SQL> select * from dba_tablespaces;
TABLESPACE_NAME BLOCK_SIZE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE MIN_EXTLEN STATUS CONTENTS LOGGING FOR EXTENT_MAN ALLOCATIO PLU SEGMEN DEF_TAB_ RETENTION BIG
------------------------------ ---------- -------------- ----------- ----------- ----------- ------------ ---------- --------- --------- --------- --- ---------- --------- --- ------ -------- ----------- ---
SYSTEM 8192 65536 1 2147483645 65536 ONLINE PERMANENT LOGGING NO LOCAL SYSTEM NO MANUAL DISABLED NOT APPLY NO
SYSAUX 8192 65536 1 2147483645 65536 ONLINE PERMANENT LOGGING NO LOCAL SYSTEM NO AUTO DISABLED NOT APPLY NO
USERS 8192 65536 1 2147483645 65536 ONLINE PERMANENT LOGGING NO LOCAL SYSTEM NO AUTO DISABLED NOT APPLY NO
EXAMPLE 8192 65536 1 2147483645 65536 ONLINE PERMANENT NOLOGGING NO LOCAL SYSTEM YES AUTO DISABLED NOT APPLY NO
BLOCK 8192 65536 1 2147483645 65536 ONLINE PERMANENT LOGGING NO LOCAL SYSTEM NO AUTO DISABLED NOT APPLY NO
TEST 8192 65536 1 2147483645 65536 ONLINE PERMANENT LOGGING NO LOCAL SYSTEM NO AUTO DISABLED NOT APPLY NO
BBED 8192 65536 1 2147483645 65536 ONLINE PERMANENT LOGGING NO LOCAL SYSTEM NO AUTO DISABLED NOT APPLY NO
TEMP2 8192 1048576 1048576 1 0 1048576 ONLINE TEMPORARY NOLOGGING NO LOCAL UNIFORM NO MANUAL DISABLED NOT APPLY NO
FLM 8192 65536 1 2147483645 65536 ONLINE PERMANENT LOGGING NO LOCAL SYSTEM NO MANUAL DISABLED NOT APPLY NO
ASSM 8192 65536 1 2147483645 65536 ONLINE PERMANENT LOGGING NO LOCAL SYSTEM NO AUTO DISABLED NOT APPLY NO
UNDOTBS 8192 65536 1 2147483645 65536 ONLINE UNDO LOGGING NO LOCAL SYSTEM NO MANUAL DISABLED NOGUARANTEE NO
TABLESPACE_NAME BLOCK_SIZE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE MIN_EXTLEN STATUS CONTENTS LOGGING FOR EXTENT_MAN ALLOCATIO PLU SEGMEN DEF_TAB_ RETENTION BIG
------------------------------ ---------- -------------- ----------- ----------- ----------- ------------ ---------- --------- --------- --------- --- ---------- --------- --- ------ -------- ----------- ---
T 8192 65536 1 2147483645 65536 ONLINE PERMANENT LOGGING NO LOCAL SYSTEM NO AUTO DISABLED NOT APPLY NO
DEMO 8192 65536 65536 1 2147483645 0 65536 ONLINE PERMANENT LOGGING NO LOCAL UNIFORM NO AUTO DISABLED NOT APPLY NO
DUMMY 8192 65536 1 2147483645 65536 ONLINE PERMANENT LOGGING NO LOCAL SYSTEM NO AUTO DISABLED NOT APPLY NO
A 8192 65536 1 2147483645 65536 ONLINE PERMANENT LOGGING NO LOCAL SYSTEM NO AUTO DISABLED NOT APPLY NO
B 8192 65536 1 2147483645 65536 ONLINE PERMANENT LOGGING NO LOCAL SYSTEM NO AUTO DISABLED NOT APPLY NO
16 rows selected.
SQL> select * from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
D:ORACLEPRODUCT10.2.0ORADATATSA.DBF
D:ORACLEPRODUCT10.2.0ORADATATSDUMMY.DBF
D:ORACLEPRODUCT10.2.0ORADATATSDEMO.DBF
D:ORACLEPRODUCT10.2.0ORADATATST1.DBF
D:ORACLEPRODUCT10.2.0ORADATATSUNDOTBS.DBF
D:ORACLEPRODUCT10.2.0ORADATATSASSM.DBF
D:ORACLEPRODUCT10.2.0ORADATATSFLM.DBF
D:ORACLEPRODUCT10.2.0ORADATATSA0.DBF
D:ORACLEPRODUCT10.2.0ORADATATSBBED.DBF
D:ORACLEPRODUCT10.2.0ORADATATSTEST.DBF
D:ORACLEPRODUCT10.2.0ORADATATSBLOCK.DBF
FILE_NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
D:ORACLEPRODUCT10.2.0ORADATATSEXAMPLE01.DBF
D:ORACLEPRODUCT10.2.0ORADATATSUSERS01.DBF
D:ORACLEPRODUCT10.2.0ORADATATSSYSAUX01.DBF
D:ORACLEPRODUCT10.2.0ORADATATST.DBF
D:ORACLEPRODUCT10.2.0ORADATATSSYSTEM01.DBF
D:ORACLEPRODUCT10.2.0DB_1DATABASEMISSING00010
D:ORACLEPRODUCT10.2.0DB_1DATABASEMISSING00011
D:ORACLEPRODUCT10.2.0DB_1DATABASEMISSING00019
19 rows selected.
SQL> select * from t13;
select * from t13
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> alter database create datafile 'D:ORACLEPRODUCT10.2.0DB_1DATABASEMISSING00019' as 'D:ORACLEPRODUCT10.2.0DB_1DATABASEb.dbf' reuse;
alter database create datafile 'D:ORACLEPRODUCT10.2.0DB_1DATABASEMISSING00019' as 'D:ORACLEPRODUCT10.2.0DB_1DATABASEb.dbf' reuse
*
ERROR at line 1:
ORA-01178: file 19 created before last CREATE CONTROLFILE, cannot recreate
ORA-01111: name for data file 19 is unknown - rename to correct file
ORA-01110: data file 19: 'D:ORACLEPRODUCT10.2.0DB_1DATABASEMISSING00019'
SQL> alter database rename file 'D:ORACLEPRODUCT10.2.0DB_1DATABASEMISSING00019' to 'D:ORACLEPRODUCT10.2.0DB_1DATABASEb.dbf';
Database altered.
SQL> select * from t13;
select * from t13
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> recover datafile 19;
Media recovery complete.
SQL> alter database datafile 19 online;
Database altered.
SQL> select * from t13;
select * from t13
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select * from dba_segments
2 where tablespace_name='T13';
no rows selected
SQL> select * from dba_tablespaces;
TABLESPACE_NAME BLOCK_SIZE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE MIN_EXTLEN STATUS CONTENTS LOGGING FOR EXTENT_MAN ALLOCATIO PLU SEGMEN DEF_TAB_ RETENTION BIG
------------------------------ ---------- -------------- ----------- ----------- ----------- ------------ ---------- --------- --------- --------- --- ---------- --------- --- ------ -------- ----------- ---
SYSTEM 8192 65536 1 2147483645 65536 ONLINE PERMANENT LOGGING NO LOCAL SYSTEM NO MANUAL DISABLED NOT APPLY NO
SYSAUX 8192 65536 1 2147483645 65536 ONLINE PERMANENT LOGGING NO LOCAL SYSTEM NO AUTO DISABLED NOT APPLY NO
USERS 8192 65536 1 2147483645 65536 ONLINE PERMANENT LOGGING NO LOCAL SYSTEM NO AUTO DISABLED NOT APPLY NO
EXAMPLE 8192 65536 1 2147483645 65536 ONLINE PERMANENT NOLOGGING NO LOCAL SYSTEM YES AUTO DISABLED NOT APPLY NO
BLOCK 8192 65536 1 2147483645 65536 ONLINE PERMANENT LOGGING NO LOCAL SYSTEM NO AUTO DISABLED NOT APPLY NO
TEST 8192 65536 1 2147483645 65536 ONLINE PERMANENT LOGGING NO LOCAL SYSTEM NO AUTO DISABLED NOT APPLY NO
BBED 8192 65536 1 2147483645 65536 ONLINE PERMANENT LOGGING NO LOCAL SYSTEM NO AUTO DISABLED NOT APPLY NO
TEMP2 8192 1048576 1048576 1 0 1048576 ONLINE TEMPORARY NOLOGGING NO LOCAL UNIFORM NO MANUAL DISABLED NOT APPLY NO
FLM 8192 65536 1 2147483645 65536 ONLINE PERMANENT LOGGING NO LOCAL SYSTEM NO MANUAL DISABLED NOT APPLY NO
ASSM 8192 65536 1 2147483645 65536 ONLINE PERMANENT LOGGING NO LOCAL SYSTEM NO AUTO DISABLED NOT APPLY NO
UNDOTBS 8192 65536 1 2147483645 65536 ONLINE UNDO LOGGING NO LOCAL SYSTEM NO MANUAL DISABLED NOGUARANTEE NO
TABLESPACE_NAME BLOCK_SIZE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE MIN_EXTLEN STATUS CONTENTS LOGGING FOR EXTENT_MAN ALLOCATIO PLU SEGMEN DEF_TAB_ RETENTION BIG
------------------------------ ---------- -------------- ----------- ----------- ----------- ------------ ---------- --------- --------- --------- --- ---------- --------- --- ------ -------- ----------- ---
T 8192 65536 1 2147483645 65536 ONLINE PERMANENT LOGGING NO LOCAL SYSTEM NO AUTO DISABLED NOT APPLY NO
DEMO 8192 65536 65536 1 2147483645 0 65536 ONLINE PERMANENT LOGGING NO LOCAL UNIFORM NO AUTO DISABLED NOT APPLY NO
DUMMY 8192 65536 1 2147483645 65536 ONLINE PERMANENT LOGGING NO LOCAL SYSTEM NO AUTO DISABLED NOT APPLY NO
A 8192 65536 1 2147483645 65536 ONLINE PERMANENT LOGGING NO LOCAL SYSTEM NO AUTO DISABLED NOT APPLY NO
B 8192 65536 1 2147483645 65536 ONLINE PERMANENT LOGGING NO LOCAL SYSTEM NO AUTO DISABLED NOT APPLY NO
16 rows selected.
SQL> select * from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
D:ORACLEPRODUCT10.2.0ORADATATSA.DBF
D:ORACLEPRODUCT10.2.0ORADATATSDUMMY.DBF
D:ORACLEPRODUCT10.2.0ORADATATSDEMO.DBF
D:ORACLEPRODUCT10.2.0ORADATATST1.DBF
D:ORACLEPRODUCT10.2.0ORADATATSUNDOTBS.DBF
D:ORACLEPRODUCT10.2.0ORADATATSASSM.DBF
D:ORACLEPRODUCT10.2.0ORADATATSFLM.DBF
D:ORACLEPRODUCT10.2.0ORADATATSA0.DBF
D:ORACLEPRODUCT10.2.0ORADATATSBBED.DBF
D:ORACLEPRODUCT10.2.0ORADATATSTEST.DBF
D:ORACLEPRODUCT10.2.0ORADATATSBLOCK.DBF
FILE_NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
D:ORACLEPRODUCT10.2.0ORADATATSEXAMPLE01.DBF
D:ORACLEPRODUCT10.2.0ORADATATSUSERS01.DBF
D:ORACLEPRODUCT10.2.0ORADATATSSYSAUX01.DBF
D:ORACLEPRODUCT10.2.0ORADATATST.DBF
D:ORACLEPRODUCT10.2.0ORADATATSSYSTEM01.DBF
D:ORACLEPRODUCT10.2.0DB_1DATABASEMISSING00010
D:ORACLEPRODUCT10.2.0DB_1DATABASEMISSING00011
D:ORACLEPRODUCT10.2.0DB_1DATABASEB.DBF
19 rows selected.
SQL>
SQL> alter database create datafile 'D:ORACLEPRODUCT10.2.0DB_1DATABASEMISSING00011' as 'D:ORACLEPRODUCT10.2.0ORADATATSa2.dbf';
alter database create datafile 'D:ORACLEPRODUCT10.2.0DB_1DATABASEMISSING00011' as 'D:ORACLEPRODUCT10.2.0ORADATATSa2.dbf'
*
ERROR at line 1:
ORA-01178: file 11 created before last CREATE CONTROLFILE, cannot recreate
ORA-01111: name for data file 11 is unknown - rename to correct file
ORA-01110: data file 11: 'D:ORACLEPRODUCT10.2.0DB_1DATABASEMISSING00011'
SQL> alter database rename file 'D:ORACLEPRODUCT10.2.0DB_1DATABASEMISSING00011' to 'D:ORACLEPRODUCT10.2.0ORADATATSa2.dbf';
alter database rename file 'D:ORACLEPRODUCT10.2.0DB_1DATABASEMISSING00011' to 'D:ORACLEPRODUCT10.2.0ORADATATSa2.dbf'
*
ERROR at line 1:
ORA-01511: error in renaming log/data files
ORA-01141: error renaming data file 11 - new file 'D:ORACLEPRODUCT10.2.0ORADATATSa2.dbf' not found
ORA-01111: name for data file 11 is unknown - rename to correct file
ORA-01110: data file 11: 'D:ORACLEPRODUCT10.2.0DB_1DATABASEMISSING00011'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) 系統找不到指定的檔案。
SQL> alter database rename file 'D:ORACLEPRODUCT10.2.0DB_1DATABASEMISSING00011' to 'D:ORACLEPRODUCT10.2.0ORADATATSa2.dbf';
Database altered.
SQL> recover datafile 'D:ORACLEPRODUCT10.2.0ORADATATSa2.dbf';
ORA-00283: recovery session canceled due to errors
ORA-04031: unable to allocate 32332 bytes of shared memory ("shared pool","unknown object","sga heap(1,1)","previous log names for rcv so")
SQL> SQL> alter database create datafile 'D:ORACLEPRODUCT10.2.0DB_1DATABASEMISSING00011' as 'D:ORACLEPRODUCT10.2.0ORADATATSa2.dbf';
alter database create datafile 'D:ORACLEPRODUCT10.2.0DB_1DATABASEMISSING00011' as 'D:ORACLEPRODUCT10.2.0ORADATATSa2.dbf'
*
ERROR at line 1:
ORA-01178: file 11 created before last CREATE CONTROLFILE, cannot recreate
ORA-01111: name for data file 11 is unknown - rename to correct file
ORA-01110: data file 11: 'D:ORACLEPRODUCT10.2.0DB_1DATABASEMISSING00011'
SQL> alter database rename file 'D:ORACLEPRODUCT10.2.0DB_1DATABASEMISSING00011' to 'D:ORACLEPRODUCT10.2.0ORADATATSa2.dbf';
alter database rename file 'D:ORACLEPRODUCT10.2.0DB_1DATABASEMISSING00011' to 'D:ORACLEPRODUCT10.2.0ORADATATSa2.dbf'
*
ERROR at line 1:
ORA-01511: error in renaming log/data files
ORA-01141: error renaming data file 11 - new file 'D:ORACLEPRODUCT10.2.0ORADATATSa2.dbf' not found
ORA-01111: name for data file 11 is unknown - rename to correct file
ORA-01110: data file 11: 'D:ORACLEPRODUCT10.2.0DB_1DATABASEMISSING00011'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) 系統找不到指定的檔案。
SQL> alter database rename file 'D:ORACLEPRODUCT10.2.0DB_1DATABASEMISSING00011' to 'D:ORACLEPRODUCT10.2.0ORADATATSa2.dbf';
Database altered.
SQL> recover datafile 'D:ORACLEPRODUCT10.2.0ORADATATSa2.dbf';
ORA-00283: recovery session canceled due to errors
ORA-04031: unable to allocate 32332 bytes of shared memory ("shared pool","unknown object","sga heap(1,1)","previous log names for rcv so")
SQL> recover datafile 'D:ORACLEPRODUCT10.2.0ORADATATSa2.dbf';
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/756652/viewspace-242244/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 與控制檔案有關的恢復
- 【RMAN】如果控制檔案損壞那麼如何恢復？恢復控制檔案的方式有哪幾種？
- [20210225]控制檔案序列號滿的恢復.txt
- rman恢復控制檔案的一個小錯誤
- RAC控制檔案恢復（三種不同情況）
- word怎麼恢復儲存前的檔案，word檔案恢復
- 檔案替換後怎麼恢復，恢復被覆蓋的檔案
- 同名檔案替換怎麼恢復，恢復同名檔案
- 剪下的檔案還能恢復嗎，恢復剪貼丟失的檔案
- 【/proc/檔案淺析】另類辦法恢復資料檔案和控制檔案
- 剪下後的檔案可以恢復嗎？恢復剪下檔案怎麼辦？
- word自動儲存的檔案怎麼恢復，word檔案恢復
- qq檔案失效怎麼恢復 qq已失效的檔案能不能恢復
- 360粉碎檔案可以恢復嗎，如何恢復360強力刪除的檔案
- oracle控制檔案的損壞或完全丟失的恢復辦法Oracle
- 檔案中勒索恢復
- Git恢復刪除的檔案Git
- uninstall 後的檔案如何恢復
- 行動硬碟刪除的檔案能恢復嗎，怎麼恢復硬碟刪除的檔案硬碟
- U盤檔案被隱藏怎麼恢復 U盤檔案恢復隱藏的方法
- eclipse 恢復誤刪檔案Eclipse
- Linux檔案恢復利器 ext3grep與extundeleteLinuxdelete
- 磁碟顯示沒有初始化恢復檔案方法
- XFS檔案系統的備份、恢復、修復
- 隨身碟檔案被隱藏怎麼恢復 隨身碟檔案恢復隱藏的方法
- u盤檔案變成快捷方式怎麼恢復，恢復U盤檔案的五種方法
- git恢復誤刪未提交的檔案Git
- sd卡刪除的檔案如何恢復SD卡
- 被誤刪的檔案快速恢復方法
- Shift + Delete刪除的檔案如何恢復？delete
- 如何有效恢復誤刪的HDFS檔案
- Oracle為什麼使用備份的控制檔案恢復後一定要resetlogsOracle
- mysql 透過idb 恢復檔案MySql
- Linux恢復檔案雙手鐧Linux
- 電腦檔案恢復軟體
- Oracle-無備份情況下，如何手動恢復控制檔案Oracle
- win10沒儲存的檔案怎麼恢復_win10檔案未儲存的恢復方法Win10
- 恢復ext4檔案系統被誤刪的檔案