数据库down机恢复案例二

数据库版本：Oracle9i Enterprise Edition Release 9.2.0.4.0

操作系统版本：centOS4.4
SQL> alter database open;  
alter database open
*
ERROR at line 1:
ORA-01113: file 17 needs media recovery
ORA-01110: data file 17: '/oradata2/ics/ics_userinfo_001.dat'

SQL> recover datafile 17
ORA-00279: change 10084969660 generated at 10/04/2008 11:13:24 needed

for
thread 1
ORA-00289: suggestion : /oracle/ora9/product/9.2/dbs/arch1_82307.dbf
ORA-00280: change 10084969660 for thread 1 is in sequence #82307

Specify log: {=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SQL>
SQL>
SQL> alter database open; 
alter database open
*
ERROR at line 1:
ORA-01113: file 17 needs media recovery
ORA-01110: data file 17: '/oradata2/ics/ics_userinfo_001.dat'
SQL> alter database datafile 17 offline drop;

Database altered.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-16068: redo log file activation identifier mismatch
ORA-00312: online log 1 thread 1: '/oradata1/ora9/ics/redo01.log'

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- -----------

-----
FIRST_CHANGE# FIRST_TIM
------------- ---------
         1          1      83573  104857600          1 NO  INVALIDATED
   1.0132E+10 30-OCT-08

         2          1      83571  104857600          1 NO  INACTIVE
   1.0132E+10 30-OCT-08

         3          1      83572  104857600          1 NO  INACTIVE
   1.0132E+10 30-OCT-08
SQL> alter database drop logfile group 1;
alter database drop logfile group 1
*
ERROR at line 1:
ORA-01623: log 1 is current log for thread 1 - cannot drop
ORA-00312: online log 1 thread 1: '/oradata1/ora9/ics/redo01.log'

使用隐含参数_allow_resetlogs_corruption可以让数据库启动，启动之后……
SQL> select count(1) from ics001.user_srv_subscriptionlog              
ERROR at line 1:
ORA-00376: file 17 cannot be read at this time
ORA-01110: data file 17: '/oradata2/ics/ics_userinfo_001.dat'

SQL> select segment_name,owner,tablespace_name,status
  2  from dba_rollback_segs;
SEGMENT_NAME         OWNER  TABLESPACE_NAME                STATUS
-------------------- ------ ------------------------------ -----------

-----
SYSTEM               SYS    SYSTEM                         ONLINE
_SYSSMU1$            PUBLIC UNDOTBS1                       ONLINE
_SYSSMU2$            PUBLIC UNDOTBS1                       ONLINE
_SYSSMU3$            PUBLIC UNDOTBS1                       ONLINE
_SYSSMU4$            PUBLIC UNDOTBS1                       ONLINE
_SYSSMU5$            PUBLIC UNDOTBS1                       ONLINE
_SYSSMU6$            PUBLIC UNDOTBS1                       ONLINE
_SYSSMU7$            PUBLIC UNDOTBS1                       ONLINE
_SYSSMU8$            PUBLIC UNDOTBS1                       ONLINE
_SYSSMU9$            PUBLIC UNDOTBS1                       ONLINE
_SYSSMU10$           PUBLIC UNDOTBS1                       ONLINE
可知没有失效的、错误的回滚段

没办法了，找到之前的一个表的备份，恢复之，可是吃表空间的其他表进行DDL，DML操作时都会报错

ORA-00376: file 17 cannot be read at this time
ORA-01110: data file 17: '/oradata2/ics/ics_userinfo_001.dat'

在没有备份的情况，只要把损失减到最少了~~~

先找到ics_userinfo_001.dat'所在表空间的所有的表

select owner,segment_name,segment_type,sum(bytes)/1024/1024 as SIZE_M
FROM dba_segments
where  tablespace_name='tbs_name'
group by owner,segment_type,segment_name;

--查看此表是否有数据丢失（报ORA-00376，ORA-01110就说明有丢失）

select count(1) from table_name  t

--如果有数据丢失，就是使用伪列查出当前表里还有多少记录

select no from (select rownum no,t.* from table_name  t) where no >1

--建一个中间表来存放未丢失的数据，也就是上面查到的记录

create table temp_bak tablespace tbs_name
  pctfree 10
  pctused 40
  initrans 1
  maxtrans 255
  storage
  (
    initial 50M
    minextents 1
    maxextents unlimited
  ) as
(select * from
(select t.* from table_name  t
where rownum <=701)
where rownum >=1)

--备份原表的索引

drop table table_name 

create table table_name  tablespace ics_userinfo as (select t.* from temp_bak  t)

--重建原表的索引

drop table temp_bak

select count(1) from   table_name 

其他的表的恢复的方法同上，不过自我感觉这个办法过于麻烦，路过的高手有什么更好的方法~请指教！

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/14075938/viewspace-483604/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/14075938/viewspace-483604/