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

于 2012-10-11 17:23:37 发布
阅读量287 收藏
点赞数
文章标签： 数据库
本文介绍了一种在没有备份的情况下，针对Oracle数据库中损坏的数据文件进行恢复的方法。通过创建一个新的数据文件来替代损坏的文件，并确保数据库处于归档模式。文章详细记录了恢复过程中的SQL命令及步骤。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >


数据库在没有备份的情况下的数据文件损坏的恢复。
使用办法：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/

cri3224
关注
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值