資料庫在沒有備份的情況下的資料檔案損壞的恢復
資料庫在沒有備份的情況下的資料檔案損壞的恢復。
使用辦法：alter database create datafile '...舊的' as '...新的';
前提:資料庫是歸檔的。
SQL> create tablespace mao datafile '/pmsdb/oracle/oradata/ora11g/ma03.dbf' size 20m autoextend on;
SQL> create table mao3r3(id int) tablespace mao;
SQL> insert into mao3r3 values(1);
SQL> create tablespace mao datafile '/pmsdb/oracle/oradata/ora11g/ma03.dbf' size 20m autoextend on;
SQL> create table mao3r3(id int) tablespace mao;
SQL> insert into mao3r3 values(1);
SQL> /
256 rows created.
SQL> /
512 rows created.
SQL> /
1024 rows created.
SQL> !vi /pmsdb/oracle/oradata/ora11g/ma03.dbf----寫入一些東西，然後儲存退出
SQL> /
2048 rows created.
SQL> /
4096 rows created.
SQL> /
8192 rows created.
SQL> /
16384 rows created.
SQL> /
32768 rows created.
SQL> /
65536 rows created.
SQL> /
131072 rows created.
SQL> /
262144 rows created.
SQL> /
/
524288 rows created.
/
524288 rows created.
SQL>
/ insert into mao3r3 select * from mao3r3---發現報錯了
*
ERROR at line 1:
ORA-01565: error in identifying file '/pmsdb/oracle/oradata/ora11g/mao3.dbf'
ORA-27046: file size is not a multiple of logical block size
Additional information: 1
/ insert into mao3r3 select * from mao3r3---發現報錯了
*
ERROR at line 1:
ORA-01565: error in identifying file '/pmsdb/oracle/oradata/ora11g/mao3.dbf'
ORA-27046: file size is not a multiple of logical block size
Additional information: 1
SQL>
insert into mao3r3 select * from mao3r3
*
ERROR at line 1:
ORA-01565: error in identifying file '/pmsdb/oracle/oradata/ora11g/mao3.dbf'
ORA-27046: file size is not a multiple of logical block size
Additional information: 1
SQL> commit;
Commit complete.
SQL> set linesize 200---檢視錶空間發現該檔案不能讀取了
SQL> col file_name for a60
SQL> select a.tablespace_name,
2 a.file_name,
3 a.total "Total(MB)",
4 round(a.total - b.Free_Space) "Used(MB)",
5 round(((a.total - b.Free_Space) / a.total) * 100, 2) "Used(%)",
6 a.auto_extend
7 from (select FILE_ID,
8 tablespace_name,
9 file_name,
10 bytes / (1024 * 1024) Total,
11 AUTOEXTENSIBLE auto_extend
12 from dba_data_files ddf) a,
13 (select file_id, sum(bytes) / (1024 * 1024) Free_Space
14 from dba_free_space
15 group by file_id) b
16 where a.file_id = b.file_id
17 order by a.tablespace_name;
SQL> col file_name for a60
SQL> select a.tablespace_name,
2 a.file_name,
3 a.total "Total(MB)",
4 round(a.total - b.Free_Space) "Used(MB)",
5 round(((a.total - b.Free_Space) / a.total) * 100, 2) "Used(%)",
6 a.auto_extend
7 from (select FILE_ID,
8 tablespace_name,
9 file_name,
10 bytes / (1024 * 1024) Total,
11 AUTOEXTENSIBLE auto_extend
12 from dba_data_files ddf) a,
13 (select file_id, sum(bytes) / (1024 * 1024) Free_Space
14 from dba_free_space
15 group by file_id) b
16 where a.file_id = b.file_id
17 order by a.tablespace_name;
TABLESPACE_NAME FILE_NAME Total(MB) Used(MB) Used(%) AUT
------------------------------ ------------------------------------------------------------ ---------- ---------- ---------- ---
MGMT_AD4J_TS /pmsdb/oracle/oradata/ora11g/mgmt_ad4j.dbf 200 2 .75 YES
MGMT_ECM_DEPOT_TS /pmsdb/oracle/oradata/ora11g/mgmt_ecm_depot1.dbf 20 13 65 YES
MGMT_TABLESPACE /pmsdb/oracle/oradata/ora11g/mgmt.dbf 550 468 85.02 YES
SYSAUX /pmsdb/oracle/oradata/ora11g/sysaux01.dbf 760 705 92.8 YES
SYSTEM /pmsdb/oracle/oradata/ora11g/system01.dbf 4970 803 16.15 YES
UNDOTBS1 /pmsdb/oracle/oradata/ora11g/undotbs01.dbf 2020 53 2.62 YES
USERS /pmsdb/oracle/oradata/ora11g/users01.dbf 1024 5 .45 YES
------------------------------ ------------------------------------------------------------ ---------- ---------- ---------- ---
MGMT_AD4J_TS /pmsdb/oracle/oradata/ora11g/mgmt_ad4j.dbf 200 2 .75 YES
MGMT_ECM_DEPOT_TS /pmsdb/oracle/oradata/ora11g/mgmt_ecm_depot1.dbf 20 13 65 YES
MGMT_TABLESPACE /pmsdb/oracle/oradata/ora11g/mgmt.dbf 550 468 85.02 YES
SYSAUX /pmsdb/oracle/oradata/ora11g/sysaux01.dbf 760 705 92.8 YES
SYSTEM /pmsdb/oracle/oradata/ora11g/system01.dbf 4970 803 16.15 YES
UNDOTBS1 /pmsdb/oracle/oradata/ora11g/undotbs01.dbf 2020 53 2.62 YES
USERS /pmsdb/oracle/oradata/ora11g/users01.dbf 1024 5 .45 YES
7 rows selected.
SQL> select file#,checkpoint_change#,status,name from v$datafile_header;
FILE# CHECKPOINT_CHANGE# STATUS
---------- ------------------ -------
NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 32862389 ONLINE
/pmsdb/oracle/oradata/ora11g/system01.dbf
---------- ------------------ -------
NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 32862389 ONLINE
/pmsdb/oracle/oradata/ora11g/system01.dbf
2 32862389 ONLINE
/pmsdb/oracle/oradata/ora11g/sysaux01.dbf
/pmsdb/oracle/oradata/ora11g/sysaux01.dbf
3 32862389 ONLINE
/pmsdb/oracle/oradata/ora11g/undotbs01.dbf
/pmsdb/oracle/oradata/ora11g/undotbs01.dbf
FILE# CHECKPOINT_CHANGE# STATUS
---------- ------------------ -------
NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
4 32862389 ONLINE
/pmsdb/oracle/oradata/ora11g/users01.dbf
5 32862389 ONLINE
/pmsdb/oracle/oradata/ora11g/mgmt_ecm_depot1.dbf
/pmsdb/oracle/oradata/ora11g/mgmt_ecm_depot1.dbf
6 32862389 ONLINE
/pmsdb/oracle/oradata/ora11g/mgmt.dbf
/pmsdb/oracle/oradata/ora11g/mgmt.dbf
FILE# CHECKPOINT_CHANGE# STATUS
---------- ------------------ -------
NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
7 32862389 ONLINE
/pmsdb/oracle/oradata/ora11g/mgmt_ad4j.dbf
8 32863839 ONLINE
/pmsdb/oracle/oradata/ora11g/mao3.dbf
8 rows selected.
SQL> select file_name,AUTOEXTENSIBLE from dba_data_files;
FILE_NAME AUT
-------------------------------------------------- ---
/pmsdb/oracle/oradata/ora11g/users01.dbf YES
/pmsdb/oracle/oradata/ora11g/undotbs01.dbf YES
/pmsdb/oracle/oradata/ora11g/sysaux01.dbf YES
/pmsdb/oracle/oradata/ora11g/system01.dbf YES
/pmsdb/oracle/oradata/ora11g/mgmt_ecm_depot1.dbf YES
/pmsdb/oracle/oradata/ora11g/mgmt.dbf YES
/pmsdb/oracle/oradata/ora11g/mgmt_ad4j.dbf YES
/pmsdb/oracle/oradata/ora11g/mao3.dbf YES
-------------------------------------------------- ---
/pmsdb/oracle/oradata/ora11g/users01.dbf YES
/pmsdb/oracle/oradata/ora11g/undotbs01.dbf YES
/pmsdb/oracle/oradata/ora11g/sysaux01.dbf YES
/pmsdb/oracle/oradata/ora11g/system01.dbf YES
/pmsdb/oracle/oradata/ora11g/mgmt_ecm_depot1.dbf YES
/pmsdb/oracle/oradata/ora11g/mgmt.dbf YES
/pmsdb/oracle/oradata/ora11g/mgmt_ad4j.dbf YES
/pmsdb/oracle/oradata/ora11g/mao3.dbf YES
8 rows selected.
SQL> alter tablespace mao online;
Tablespace altered.
SQL> set linesize 200
SQL> col file_name for a60
SQL> select a.tablespace_name,
2 a.file_name,
3 a.total "Total(MB)",
4 round(a.total - b.Free_Space) "Used(MB)",
5 round(((a.total - b.Free_Space) / a.total) * 100, 2) "Used(%)",
6 a.auto_extend
7 from (select FILE_ID,
8 tablespace_name,
9 file_name,
10 bytes / (1024 * 1024) Total,
11 AUTOEXTENSIBLE auto_extend
12 from dba_data_files ddf) a,
13 (select file_id, sum(bytes) / (1024 * 1024) Free_Space
14 from dba_free_space
15 group by file_id) b
16 where a.file_id = b.file_id
17 order by a.tablespace_name;
SQL> col file_name for a60
SQL> select a.tablespace_name,
2 a.file_name,
3 a.total "Total(MB)",
4 round(a.total - b.Free_Space) "Used(MB)",
5 round(((a.total - b.Free_Space) / a.total) * 100, 2) "Used(%)",
6 a.auto_extend
7 from (select FILE_ID,
8 tablespace_name,
9 file_name,
10 bytes / (1024 * 1024) Total,
11 AUTOEXTENSIBLE auto_extend
12 from dba_data_files ddf) a,
13 (select file_id, sum(bytes) / (1024 * 1024) Free_Space
14 from dba_free_space
15 group by file_id) b
16 where a.file_id = b.file_id
17 order by a.tablespace_name;
TABLESPACE_NAME FILE_NAME Total(MB) Used(MB) Used(%) AUT
------------------------------ ------------------------------------------------------------ ---------- ---------- ---------- ---
MGMT_AD4J_TS /pmsdb/oracle/oradata/ora11g/mgmt_ad4j.dbf 200 2 .75 YES
MGMT_ECM_DEPOT_TS /pmsdb/oracle/oradata/ora11g/mgmt_ecm_depot1.dbf 20 13 65 YES
MGMT_TABLESPACE /pmsdb/oracle/oradata/ora11g/mgmt.dbf 550 468 85.06 YES
SYSAUX /pmsdb/oracle/oradata/ora11g/sysaux01.dbf 760 705 92.8 YES
SYSTEM /pmsdb/oracle/oradata/ora11g/system01.dbf 4970 803 16.15 YES
UNDOTBS1 /pmsdb/oracle/oradata/ora11g/undotbs01.dbf 2020 53 2.62 YES
USERS /pmsdb/oracle/oradata/ora11g/users01.dbf 1024 5 .45 YES
------------------------------ ------------------------------------------------------------ ---------- ---------- ---------- ---
MGMT_AD4J_TS /pmsdb/oracle/oradata/ora11g/mgmt_ad4j.dbf 200 2 .75 YES
MGMT_ECM_DEPOT_TS /pmsdb/oracle/oradata/ora11g/mgmt_ecm_depot1.dbf 20 13 65 YES
MGMT_TABLESPACE /pmsdb/oracle/oradata/ora11g/mgmt.dbf 550 468 85.06 YES
SYSAUX /pmsdb/oracle/oradata/ora11g/sysaux01.dbf 760 705 92.8 YES
SYSTEM /pmsdb/oracle/oradata/ora11g/system01.dbf 4970 803 16.15 YES
UNDOTBS1 /pmsdb/oracle/oradata/ora11g/undotbs01.dbf 2020 53 2.62 YES
USERS /pmsdb/oracle/oradata/ora11g/users01.dbf 1024 5 .45 YES
7 rows selected.
SQL> insert into mao3r3 select * from mao3r3;
insert into mao3r3 select * from mao3r3
*
ERROR at line 1:
ORA-01565: error in identifying file '/pmsdb/oracle/oradata/ora11g/mao3.dbf'
ORA-27046: file size is not a multiple of logical block size
Additional information: 1
insert into mao3r3 select * from mao3r3
*
ERROR at line 1:
ORA-01565: error in identifying file '/pmsdb/oracle/oradata/ora11g/mao3.dbf'
ORA-27046: file size is not a multiple of logical block size
Additional information: 1
SQL> recover datafile 8;----恢復，報錯
ORA-00283: recovery session canceled due to errors
ORA-01124: cannot recover data file 8 - file is in use or recovery
ORA-01110: data file 8: '/pmsdb/oracle/oradata/ora11g/mao3.dbf'
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/pmsdb/oracle/oradata/ora11g/system01.dbf
/pmsdb/oracle/oradata/ora11g/sysaux01.dbf
/pmsdb/oracle/oradata/ora11g/undotbs01.dbf
/pmsdb/oracle/oradata/ora11g/users01.dbf
/pmsdb/oracle/oradata/ora11g/mgmt_ecm_depot1.dbf
/pmsdb/oracle/oradata/ora11g/mgmt.dbf
/pmsdb/oracle/oradata/ora11g/mgmt_ad4j.dbf
/pmsdb/oracle/oradata/ora11g/mao3.dbf
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/pmsdb/oracle/oradata/ora11g/system01.dbf
/pmsdb/oracle/oradata/ora11g/sysaux01.dbf
/pmsdb/oracle/oradata/ora11g/undotbs01.dbf
/pmsdb/oracle/oradata/ora11g/users01.dbf
/pmsdb/oracle/oradata/ora11g/mgmt_ecm_depot1.dbf
/pmsdb/oracle/oradata/ora11g/mgmt.dbf
/pmsdb/oracle/oradata/ora11g/mgmt_ad4j.dbf
/pmsdb/oracle/oradata/ora11g/mao3.dbf
8 rows selected.
SQL> alter database create datafile '/pmsdb/oracle/oradata/ora11g/mao3.dbf' as '/pmsdb/oracle/oradata/ora11g/mao4.dbf';
alter database create datafile '/pmsdb/oracle/oradata/ora11g/mao3.dbf' as '/pmsdb/oracle/oradata/ora11g/mao4.dbf'
*
ERROR at line 1:
ORA-01182: cannot create database file 8 - file is in use or recovery
ORA-01110: data file 8: '/pmsdb/oracle/oradata/ora11g/mao3.dbf'
alter database create datafile '/pmsdb/oracle/oradata/ora11g/mao3.dbf' as '/pmsdb/oracle/oradata/ora11g/mao4.dbf'
*
ERROR at line 1:
ORA-01182: cannot create database file 8 - file is in use or recovery
ORA-01110: data file 8: '/pmsdb/oracle/oradata/ora11g/mao3.dbf'
SQL>
SQL> recover datafile 8;
ORA-00283: recovery session canceled due to errors
ORA-01124: cannot recover data file 8 - file is in use or recovery
ORA-01110: data file 8: '/pmsdb/oracle/oradata/ora11g/mao3.dbf'
SQL> alter database create datafile '/pmsdb/oracle/oradata/ora11g/mao3.dbf' as '/pmsdb/oracle/oradata/ora11g/mao4.dbf';
alter database create datafile '/pmsdb/oracle/oradata/ora11g/mao3.dbf' as '/pmsdb/oracle/oradata/ora11g/mao4.dbf'
*
ERROR at line 1:
ORA-01182: cannot create database file 8 - file is in use or recovery
ORA-01110: data file 8: '/pmsdb/oracle/oradata/ora11g/mao3.dbf'
時間過了5分鐘。然後執行：
SQL> alter database create datafile '/pmsdb/oracle/oradata/ora11g/mao3.dbf' as '/pmsdb/oracle/oradata/ora11g/mao4.dbf';
Database altered.
SQL> alter database create datafile '/pmsdb/oracle/oradata/ora11g/mao3.dbf' as '/pmsdb/oracle/oradata/ora11g/mao4.dbf';
SQL> select file#,checkpoint_change#,status,name from v$datafile_header;
FILE# CHECKPOINT_CHANGE# STATUS
---------- ------------------ -------
NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 32865160 ONLINE
/pmsdb/oracle/oradata/ora11g/system01.dbf
---------- ------------------ -------
NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 32865160 ONLINE
/pmsdb/oracle/oradata/ora11g/system01.dbf
2 32865160 ONLINE
/pmsdb/oracle/oradata/ora11g/sysaux01.dbf
/pmsdb/oracle/oradata/ora11g/sysaux01.dbf
3 32865160 ONLINE
/pmsdb/oracle/oradata/ora11g/undotbs01.dbf
/pmsdb/oracle/oradata/ora11g/undotbs01.dbf
FILE# CHECKPOINT_CHANGE# STATUS
---------- ------------------ -------
NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
4 32865160 ONLINE
/pmsdb/oracle/oradata/ora11g/users01.dbf
5 32865160 ONLINE
/pmsdb/oracle/oradata/ora11g/mgmt_ecm_depot1.dbf
/pmsdb/oracle/oradata/ora11g/mgmt_ecm_depot1.dbf
6 32865160 ONLINE
/pmsdb/oracle/oradata/ora11g/mgmt.dbf
/pmsdb/oracle/oradata/ora11g/mgmt.dbf
FILE# CHECKPOINT_CHANGE# STATUS
---------- ------------------ -------
NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
7 32865160 ONLINE
/pmsdb/oracle/oradata/ora11g/mgmt_ad4j.dbf
8 32854686 OFFLINE
/pmsdb/oracle/oradata/ora11g/mao4.dbf---離線的
/pmsdb/oracle/oradata/ora11g/mao4.dbf---離線的
8 rows selected.
SQL>
SQL> alter tablespace mao online;
alter tablespace mao online
*
ERROR at line 1:
ORA-01113: file 8 needs media recovery
ORA-01110: data file 8: '/pmsdb/oracle/oradata/ora11g/mao4.dbf'
SQL> alter tablespace mao online;
alter tablespace mao online
*
ERROR at line 1:
ORA-01113: file 8 needs media recovery
ORA-01110: data file 8: '/pmsdb/oracle/oradata/ora11g/mao4.dbf'
SQL> recover datafile 8;
Media recovery complete.
SQL> alter tablespace mao online;
Tablespace altered.
SQL> insert into mao3r3 select * from mao3r3;
1048576 rows created.
SQL> commit;
Commit complete.
SQL> 完畢！
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/24500180/viewspace-746224/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- catalog損壞情況下的資料庫恢復例項資料庫
- 備份與恢復--重建控制檔案後資料檔案損壞的恢復
- 誤刪資料檔案在沒有歸檔的情況下恢復實驗
- 沒有備份的資料檔案恢復（五）
- 沒有備份的情況下處理undo損壞
- 【資料庫資料恢復】MongoDB資料庫檔案損壞的資料恢復案例資料庫資料恢復MongoDB
- 歸檔模式，恢復沒有備份的資料檔案模式
- 備份＆恢復之八：RMAN備份歸檔模式下損壞（丟失）多個資料檔案，進行整個資料庫的恢復模式資料庫
- 非系統資料檔案損壞，rman備份恢復
- 備份與恢復--資料檔案損壞或丟失
- windows重灌/資料庫物理檔案完好的情況下恢復資料庫Windows資料庫
- REDO檔案丟失的恢復__沒有任何備份的情況
- 沒有自動備份的情況下控制檔案全部丟失的恢復
- 資料檔案丟失損壞的恢復--
- System File1 File Header（資料庫System檔案1檔案頭）損壞情況的恢復Header資料庫
- u盤檔案損壞怎麼恢復資料 u盤恢復損壞資料的有效方法
- 磁碟損壞造成RMAN備份檔案有壞塊的恢復案例
- RMAN資料庫恢復 之歸檔模式有(無)備份-丟失資料檔案的恢復資料庫模式
- 使用備份的控制檔案恢復資料庫資料庫
- Oracle資料庫控制檔案在備份恢復中的作用Oracle資料庫
- 在open狀態下恢復未備份的資料檔案
- 【恢復】使用控制檔案的trace備份恢復因異常斷電導致所有控制檔案損壞的資料庫故障資料庫
- 備份＆恢復之五：歸檔模式下丟失或損壞一個資料檔案模式
- [20171225]沒有備份資料檔案的恢復.txt
- [ORACLE] 系統故障資料庫恢復--資料檔案無損壞Oracle資料庫
- Oracle 之利用BBED修改資料塊SCN----沒有備份資料檔案的資料恢復Oracle資料恢復
- rman恢復資料庫--用備份的控制檔案資料庫
- 有整庫備份的情況rman恢復全庫
- Oracle 無備份情況下undo檔案損壞處理Oracle
- 案例：在open狀態下恢復未備份的資料檔案
- rman恢復--歸檔模式有備份，丟失資料檔案的恢復模式
- Oracle資料庫UNDO損壞後的恢復Oracle資料庫
- master資料庫損壞之後的恢復AST資料庫
- 使用RMAN恢復完全損壞的資料庫資料庫
- 資料庫資料的恢復和備份資料庫
- Control File中備份資訊被覆蓋情況下的資料庫恢復案例資料庫
- 磁碟損壞導致資料檔案丟失的恢復
- 無備份的資料塊損壞恢復辦法 dbms_repairAI