使用中undo表空间数据文件被误删

使用中undo表空间数据文件被误删


手动删除undo数据文件


SQL> shutdown immediate;
ORA-01116: error in opening database file 3
ORA-01110: data file 3: '/data/oracle/oradata/test/undotbs01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL> select * from v$recover_file;


no rows selected


SQL> select file_name,tablespace_name,online_status,status from dba_data_files;


FILE_NAME                                                                                            TABLESPACE_NAME                ONLINE_ STATUS
---------------------------------------------------------------------------------------------------- ------------------------------ ------- ---------
/data/oracle/oradata/test/tbs_index01.dbf                                                         TBS_INDEX                      ONLINE  AVAILABLE
/data/oracle/oradata/test/tbs_pay01.dbf                                                           TBS_test                    ONLINE  AVAILABLE
/data/oracle/oradata/test/users01.dbf                                                             USERS                          ONLINE  AVAILABLE
/data/oracle/oradata/test/undotbs01.dbf                                                           UNDOTBS1                       ONLINE  AVAILABLE
/data/oracle/oradata/test/sysaux01.dbf                                                            SYSAUX                         ONLINE  AVAILABLE
/data/oracle/oradata/test/system01.dbf                                                            SYSTEM                         SYSTEM  AVAILABLE
/data/oracle/oradata/test/tbs_test01.dbf                                                          TBS_TEST                       ONLINE  AVAILABLE
/data/oracle/oradata/test/tbs_test02.dbf                                                          TBS_TEST                       ONLINE  AVAILABLE
/data/oracle/oradata/test/tbs_testa01.dbf                                                         TESTA                          ONLINE  AVAILABLE


9 rows selected.


SQL> alter tablespace undotbs1 offline;
alter tablespace undotbs1 offline
*
ERROR at line 1:
ORA-30042: Cannot offline the undo tablespace




SQL> alter database datafile '/data/oracle/oradata/test/undotbs01.dbf' offline;
alter database datafile '/data/oracle/oradata/test/undotbs01.dbf' offline
*
ERROR at line 1:
ORA-00603: ORACLE server session terminated by fatal error
ORA-00376: file 3 cannot be read at this time
ORA-01110: data file 3: '/data/oracle/oradata/test/undotbs01.dbf'
ORA-00376: file 3 cannot be read at this time
ORA-01110: data file 3: '/data/oracle/oradata/test/undotbs01.dbf'
ORA-00376: file 3 cannot be read at this time
ORA-01110: data file 3: '/data/oracle/oradata/test/undotbs01.dbf'
ORA-00376: file 3 cannot be read at this time
ORA-01110: data file 3: '/data/oracle/oradata/test/undotbs01.dbf'
Process ID: 12942
Session ID: 142 Serial number: 3545




SQL> select file_name,tablespace_name,online_status,status from dba_data_files;
ERROR:
ORA-03114: not connected to ORACLE




SQL> conn / as sysdba
Connected.
SQL> select file_name,tablespace_name,online_status,status from dba_data_files;


FILE_NAME                                                                                            TABLESPACE_NAME                ONLINE_ STATUS
---------------------------------------------------------------------------------------------------- ------------------------------ ------- ---------
/data/oracle/oradata/test/tbs_index01.dbf                                                         TBS_INDEX                      ONLINE  AVAILABLE
/data/oracle/oradata/test/tbs_pay01.dbf                                                           TBS_test                    ONLINE  AVAILABLE
/data/oracle/oradata/test/users01.dbf                                                             USERS                          ONLINE  AVAILABLE
/data/oracle/oradata/test/undotbs01.dbf                                                           UNDOTBS1                       RECOVER AVAILABLE
/data/oracle/oradata/test/sysaux01.dbf                                                            SYSAUX                         ONLINE  AVAILABLE
/data/oracle/oradata/test/system01.dbf                                                            SYSTEM                         SYSTEM  AVAILABLE
/data/oracle/oradata/test/tbs_test01.dbf                                                          TBS_TEST                       ONLINE  AVAILABLE
/data/oracle/oradata/test/tbs_test02.dbf                                                          TBS_TEST                       ONLINE  AVAILABLE
/data/oracle/oradata/test/tbs_testa01.dbf                                                         TESTA                          ONLINE  AVAILABLE


9 rows selected.


SQL> recover tablespace undotbs1
ORA-00283: recovery session canceled due to errors
ORA-01110: data file 3: '/data/oracle/oradata/test/undotbs01.dbf'
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3: '/data/oracle/oradata/test/undotbs01.dbf'






--如果是在归档模式下且有备份的情况下，可以直接恢复
[oracle@baktest130 ~]$ rman target /


Recovery Manager: Release 11.2.0.1.0 - Production on Thu Apr 9 10:11:06 2015


Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.


connected to target database: test (DBID=1119577586)


RMAN> restore datafile 3;


Starting restore at 09-APR-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=97 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=20 device type=DISK


channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00003 to /data/oracle/oradata/test/undotbs01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/app/flash_recovery_area/test/backupset/2015_04_08/o1_mf_nnndf_TAG20150408T085803_bl8z8y35_.bkp
channel ORA_DISK_1: piece handle=/home/oracle/app/flash_recovery_area/test/backupset/2015_04_08/o1_mf_nnndf_TAG20150408T085803_bl8z8y35_.bkp tag=TAG20150408T085803
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
Finished restore at 09-APR-15


RMAN> recover datafile 3;


Starting recover at 09-APR-15
using channel ORA_DISK_1
using channel ORA_DISK_2


starting media recovery


archived log for thread 1 with sequence 624 is already on disk as file /data/oracle/arch/test/1_624_874620667.dbf
archived log for thread 1 with sequence 625 is already on disk as file /data/oracle/arch/test/1_625_874620667.dbf
archived log for thread 1 with sequence 626 is already on disk as file /data/oracle/arch/test/1_626_874620667.dbf
archived log for thread 1 with sequence 627 is already on disk as file /data/oracle/arch/test/1_627_874620667.dbf
archived log for thread 1 with sequence 628 is already on disk as file /data/oracle/arch/test/1_628_874620667.dbf


……




SQL> create undo tablespace undotbs2 datafile '/data/oracle/oradata/test/undotbs201.dbf' size 200m;


Tablespace created.


SQL> show parameter undo


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1
SQL> alter system set undo_tablespace=undotbs2 scope=both;


System altered.


以下默认undo为undotbs2，数据文件被删，不使用备份进行恢复。
SQL> create user testb identified by admin;


User created.


SQL> grant resource,connect to testb;


Grant succeeded.


SQL> conn testb/admin
Connected.
SQL> create table testa as select * from user_tables;
create table testa as select * from user_tables
                                    *
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01116: error in opening database file 12
ORA-01110: data file 12: '/data/oracle/oradata/test/undotbs201.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3




SQL> conn / as sysdba
Connected.
SQL> select file_name,tablespace_name,online_status,status,file_id from dba_data_files;


FILE_NAME                                                                                            TABLESPACE_NAME                ONLINE_ STATUS       FILE_ID
---------------------------------------------------------------------------------------------------- ------------------------------ ------- --------- ----------
/data/oracle/oradata/test/tbs_index01.dbf                                                         TBS_INDEX                      ONLINE  AVAILABLE          6
/data/oracle/oradata/test/tbs_pay01.dbf                                                           TBS_test                    ONLINE  AVAILABLE          5
/data/oracle/oradata/test/users01.dbf                                                             USERS                          ONLINE  AVAILABLE          4
/data/oracle/oradata/test/sysaux01.dbf                                                            SYSAUX                         ONLINE  AVAILABLE          2
/data/oracle/oradata/test/system01.dbf                                                            SYSTEM                         SYSTEM  AVAILABLE          1
/data/oracle/oradata/test/tbs_test01.dbf                                                          TBS_TEST                       ONLINE  AVAILABLE          7
/data/oracle/oradata/test/tbs_test02.dbf                                                          TBS_TEST                       ONLINE  AVAILABLE          8
/data/oracle/oradata/test/tbs_testa01.dbf                                                         TESTA                          ONLINE  AVAILABLE          9
/data/oracle/oradata/test/undotbs201.dbf                                                          UNDOTBS2                       ONLINE  AVAILABLE         12


9 rows selected.


SQL> 


实例异常终止，重新启动至open报错，undotbs201 offline ，启动成功，创建undo tablespace，修改为默认undo，发现监听状态为blocked，（由于
undo表空间问题造成），select 'drop rollback segment "' ||segment_name||'";' from dba_rollback_segs where status='NEEDS RECOVERY';
执行以上语句发现，undotbs2状态为待恢复，且无法删除，此时不能正确关闭数据库，后台报错：
Errors in file /home/oracle/app/diag/rdbms/test/test/trace/test_smon_23892.trc:
ORA-00376: file 12 cannot be read at this time
ORA-01110: data file 12: '/data/oracle/oradata/test/undotbs201.dbf'
shutdown abort，修改pfile参数
*._corrupted_rollback_segments='_SYSSMU13_2269396976$','_SYSSMU14_427832164$','_SYSSMU15_3155338273$','_SYSSMU16_2936606490$','_SYSSMU17_2916212335$','_SYSSMU18_191330693$','_SYSSMU19_1411620066$','_SYSSMU20_2336133751$','_SYSSMU21_1433408334$','_SYSSMU22_1099825627$','_SYSSMU23_185312833$','_SYSSMU24_2343462089$','_SYSSMU25_3212480019$','_SYSSMU26_2628563624$','_SYSSMU27_2421900095$','_SYSSMU28_3847241034$','_SYSSMU29_942119998$','_SYSSMU30_2702508425$','_SYSSMU31_630318853$','_SYSSMU32_1159568200$','_SYSSMU33_2298077081$','_SYSSMU34_2257342359$','_SYSSMU35_1009860968$','_SYSSMU36_3316773606$','_SYSSMU37_2543920069$','_SYSSMU38_1036890479$','_SYSSMU39_57076833$','_SYSSMU40_1713535305$','_SYSSMU41_3921597185$','_SYSSMU42_3411442471$','_SYSSMU43_1214653536$','_SYSSMU44_3475415644$','_SYSSMU45_814708173$','_SYSSMU46_645852387$','_SYSSMU47_2711911226$','_SYSSMU48_2766610628$','_SYSSMU49_3930608170$','_SYSSMU50_3868174805$','_SYSSMU51_1713807414$','_SYSSMU52_2879300132$','_SYSSMU53_1690435334$'
然后从新启动。
然后drop rollback segment '_SYSSMU13_2269396976$';或此时直接drop tablespace undotbs2 including contents and datafiles;




处理完成后，建议全库备份数据，或创建实例，将数据全部迁入新实例中。
undo丢失在碰到checkpoint时，会造成实例终止
当我们用隐含参数_offline_rollback_segments、_corrupted_rollback_segments处理回滚段某些问题的时候，undo_management=manual为好。

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

转载于:http://blog.itpub.net/28258625/viewspace-1725750/