数据库在没有备份的情况下的数据文件损坏的恢复。
使用办法: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/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/24500180/viewspace-746224/