SMON: ignoring slave err,downgrading to serial rollback ORA-01110问题一例

Maclean Liu(刘相兵

9I有个问题啊             物理上面有个数据文件被删掉了，现在数据库起来后把它offline,现在一直报：


Fri Dec 16 19:41:10 2011
Errors in file /oracle/admin/n7css/bdump/nbss7_smon_42356.trc:
ORA-00376: file 54 cannot be read at this time
ORA-01110: data file 54: '/oradatafile:///C:\Users\MACLEA~1\AppData\Local\Temp\GEI_OKV1~H6{SFXQ0_AC6G2.gifr_tdr_data/ss7_cdr_12.dbf'
SMON: Restarting fast_start parallel rollback
SMON: ignoring slave err,downgrading to serial rollback
ORACLE Instance nbss7 (pid = 6) - Error 376 encountered while recovering transaction (4, 17) on object 123033.

数据库已经启起来了

最关键原来的事务还在里面做恢复操作
现在数据文件没有了


一发现就看到数据库宕掉了
然后startup mount

后面看日志，发现有事务在恢复
alter database datafile 'file_name' offline /offline drop (noarchive)

alter database open

Maclean Liu(刘相兵

Action Plan;

1. shutdown immediate;
   
2. startup mount
   
3. alter system set events '10513 trace name context forever, level 2';
   
4. alter database datafile  'file_name' offline drop;
   
5. alter database open;

复制代码

如有问题上传alert.log 告警日志

killer-hujinhui

file:///C:/DOCUME%7E1/ADMINI%7E1.PC-/LOCALS%7E1/Temp/moz-screenshot.png

killer-hujinhui

故障时间段的数据无法查询，这是什么原因啊？

killer-hujinhui

Fri Dec 16 20:48:56 2011
SMON: Restarting fast_start parallel rollback
SMON: ignoring slave err,downgrading to serial rollback
ORACLE Instance nbss7 (pid = 6) - Error 376 encountered while recovering transaction (4, 17) on object 123033.
Fri Dec 16 20:48:56 2011
Errors in file /oracle/admin/n7css/bdump/nbss7_smon_37972.trc:
ORA-00376: file 54 cannot be read at this time
ORA-01110: data file 54: '/oradata/cdr_tdr_data/ss7_cdr_12.dbf'

Maclean Liu(刘相兵

何谓 无法查询？
是查询报错　还是查询hang住

killer-hujinhui

是查询报错，附件里面有报错图

kevinlin.ora

猜还是在用原来的表，估计得新建个表

Maclean Liu(刘相兵

可以参考

http://www.oracledatabase12g.com ... %20from%20ROWID.htm
和
http://www.vmcd.org/2011/10/%E4% ... %E6%95%B0%E6%8D%AE/

利用rowid 获取未丢失的数据，以便重建该表

Maclean Liu(刘相兵

查下 见数据文件的时间 和 现在最早在线日志文件(online redo log)的时间 ，因为你没有归档archive log isn't enabled

10g 如果日志在的话 可以create出来的

1. SQL> select * from v$version;
   
2. 
   
3. BANNER
   
4. ----------------------------------------------------------------
   
5. Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
   
6. PL/SQL Release 10.2.0.1.0 - Production
   
7. CORE    10.2.0.1.0      Production
   
8. TNS for Linux: Version 10.2.0.1.0 - Production
   
9. NLSRTL Version 10.2.0.1.0 - Production
   
10. 
    
11. SQL> create tablespace recreate datafile size 20M;
    
12. 
    
13. Tablespace created.
    
14. 
    
15. SQL> select name from v$datafile;
    
16. 
    
17. NAME
    
18. --------------------------------------------------------------------------------
    
19. /s01/oradata/G10R21/datafile/o1_mf_system_7ch8004t_.dbf
    
20. /s01/oradata/G10R21/datafile/o1_mf_undotbs1_7ch80081_.dbf
    
21. /s01/oradata/G10R21/datafile/o1_mf_sysaux_7ch8004x_.dbf
    
22. /s01/oradata/G10R21/datafile/o1_mf_users_7ch80085_.dbf
    
23. /s01/oradata/G10R21/datafile/o1_mf_example_7ch81ch3_.dbf
    
24. /s01/oradata/G10R21/datafile/o1_mf_rec_7gflyjwd_.dbf
    
25. /s01/rec_71.dbf
    
26. /s01/oradata/G10R21/datafile/o1_mf_recreate_7gr8xljr_.dbf
    
27. 
    
28. 8 rows selected.
    
29. 
    
30. SQL> alter database datafile '/s01/oradata/G10R21/datafile/o1_mf_recreate_7gr8xljr_.dbf' offline;
    
31. 
    
32. Database altered.
    
33. 
    
34. SQL> ! rm -rf '/s01/oradata/G10R21/datafile/o1_mf_recreate_7gr8xljr_.dbf'
    
35. 
    
36. SQL> ! ls -l '/s01/oradata/G10R21/datafile/o1_mf_recreate_7gr8xljr_.dbf'
    
37. ls: /s01/oradata/G10R21/datafile/o1_mf_recreate_7gr8xljr_.dbf: No such file or directory
    
38. 
    
39. 
    
40. 
    
41. SQL> alter database create datafile '/s01/oradata/G10R21/datafile/o1_mf_recreate_7gr8xljr_.dbf' as '/s01/oradata/G10R21/datafile/recreate_me.dbf';
    
42. 
    
43. Database altered.
    
44. 
    
45. 
    
46. SQL> recover datafile '/s01/oradata/G10R21/datafile/recreate_me.dbf';
    
47. Media recovery complete.
    
48. 
    
49. SQL> alter database datafile  '/s01/oradata/G10R21/datafile/recreate_me.dbf' online;
    
50. 
    
51. Database altered.

复制代码

在数据文件无备份的 情况下 recover
但是前提是  创建数据文件之前的 日志还在


Last chance:
    Please note if you have lost all copies of a datafile but DO still have
    the ARCHIVE logs from when the file was first created it is still possible
    to recover the file.
    Eg:
        ALTER DATABASE CREATE DATAFILE '....' [as '...']  ;
        RECOVER DATAFILE '....'
        ALTER DATABASE DATAFILE '....' ONLINE;

    If you are in this scenario try to recover the datafile using these
    steps before proceeding below.