主要描述在单实例未备份的极端情况下恢复

1.undo 表空间存在多个数据文件其中一个丢失

  1. 删除默认undo数据文件
  2. [oracle@instance-ej1hspdt ORCLCDB]$ rm  -rf  undotbs01.dbf   
  3. 尝试一致性关闭数据库
  4. SQL> shutdown immediate;                                                                                                         
  5. ORA-01116: error in opening database file 4                                                                                      
  6. ORA-01110: data file 4: '/opt/oracle/oradata/ORCLCDB/undotbs01.dbf'                                                              
  7. ORA-27041: unable to open file                                                                                                   
  8. Linux-x86_64 Error: 2: No such file or directory                                                                                 
  9. Additional information: 3 
  10. ----------------------------------------------------------------
  11. 启动数据库到mount状态
  12. startup mount
  13. 脱机丢失的数据文件                                                                                                                                                                                                 
  14. SQL> alter DATABASE DATAFILE '/opt/oracle/oradata/ORCLCDB/undotbs01.dbf'   OFFLINE;                                                                                                              
  16. Database altered. 
  17. 打开数据库
  18. SQL> alter database open;                                                                                                                                                                        
  20. Database altered.  
  21. 创建新的表空间并切换删除旧的表空间
  22. create  undo TABLESPACE   UNDOTBS02 DATAFILE;
  23. ALTER SYSTEM SET UNDO_TABLESPACE = UNDOTBS02;
  26. 设置隐藏参数
  27. SQL> alter   system  set "_offline_rollback_segments"=true scope=spfile;                                                                                                                         
  29. System altered.
  30. -----------------------------------------------------------------------------
  31. 删除之前有问题undo表空间报错，无法删除。
  32. SQL> drop tablespace UNDOTBS including contents;                                                                                                                                                 
  33. drop tablespace UNDOTBS including contents                                                                                                                                                       
  34. *                                                                                                                                                                                                
  35. ERROR at line 1:                                                                                                                                                                                 
  36. ORA-01548: active rollback segment '_SYSSMU1_2350702231$' found, terminate                                                                                                                       
  37. dropping tablespace   
  38. -----------------------------------------------------------
  39. 查询needs_recovery块在pfile添加隐藏参数，之后用pfile文件启动数据库
  40. select segment_name,status,tablespace_name from dba_rollback_segs;
  41. -------------------------------------------------------------------------
  42.  vi initORCLCDB.ora  
  43. *._corrupted_rollback_segments=(_SYSSMU1_2350702231$,_SYSSMU2_830629575$,_SYSSMU3_2382401544$,_SYSSMU4_4097893920$,_SYSSMU5_3588313063$,_SYSSMU6_1225920019$,_SYSSMU7_792842843$,_SYSSMU8_3775286
  44. 582$,_SYSSMU9_677683072$,_SYSSMU10_3822963826$)   
  47. SQL> startup force pfile='/opt/oracle/product/18c/dbhome_1/dbs/initORCLCDB.ora';                                                                                                                 
  48. ORACLE instance started. 
  49. ------------------------------------------------------------------------------------
  51. SQL> drop tablespace UNDOTBS including contents and datafiles;                                                                                                                                   
  53. Tablespace dropped.

2.