数据库在没有备份的情况下的数据文件损坏的恢复

数据库在没有备份的情况下的数据文件损坏的恢复。

使用办法：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> /

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.

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>
 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;

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

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

         2           32862389 ONLINE
/pmsdb/oracle/oradata/ora11g/sysaux01.dbf

         3           32862389 ONLINE
/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

         6           32862389 ONLINE
/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

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;

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

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

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

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'

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

         2           32865160 ONLINE
/pmsdb/oracle/oradata/ora11g/sysaux01.dbf

         3           32865160 ONLINE
/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

         6           32865160 ONLINE
/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---离线的

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> 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/