ORA-01157 ORA-01110 ORA-01548 Oracle误删undo数据文件

1、现象：

启动数据库时，报错：

SQL> startup ORACLE instance started.   Total System Global Area 1152450560 bytes Fixed Size 2252584 bytes Variable Size 956301528 bytes Database Buffers 184549376 bytes Redo Buffers 9347072 bytes Database mounted. ORA-01157: cannot identify/lock data file 3 - see DBWR trace file ORA-01110: data file 3: '/u01/app/oracle/oradata/SYD/datafile/o1_mf_undotbs1_cvdwnl7t_.dbf'

2、原因：

o1_mf_undotbs1_cvdwnl7t_.dbf 数据文件损坏或丢失。

3、解决方式：

将有问题的undo数据文件从数据库中离线或删除，打开数据库后，重建undo表空间。

1）确认数据库开启状态 SQL> select open_mode from v$database; OPEN_MODE -------------------- MOUNTED   2）数据库为归档模式时执行如下命令，使数据文件离线后可启动数据库。 SQL> alter database datafile '/u01/app/oracle/oradata/SYD/datafile/o1_mf_undotbs1_cvdwnl7t_.dbf' offline; （归档模式） alter database datafile '/u01/app/oracle/oradata/SYD/datafile/o1_mf_undotbs1_cvdwnl7t_.dbf' offline * ERROR at line 1: ORA-01145: offline immediate disallowed unless media recovery enabled   本次实验环境为非归档模式，需使用如下命令删除数据文件。 SQL> archive log list Database log mode No Archive Mode Automatic archival Disabled Archive destination /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch Oldest online log sequence 35 Current log sequence 37 SQL> alter database datafile '/u01/app/oracle/oradata/SYD/datafile/o1_mf_undotbs1_cvdwnl7t_.dbf' offline drop;（非归档） Database altered.   3）打开数据库 SQL> alter database open ; Database altered.   4）确认当前有问题数据文件所在的undo表空间 SQL> show parameter undo NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ undo_management string AUTO undo_retention integer 900 undo_tablespace string UNDOTBS1   5）重建新的undo表空间UNDOTBS2，将数据库默认undo表空间切换为新建表空间 SQL> CREATE UNDO TABLESPACE "UNDOTBS2" DATAFILE '/u01/app/oracle/oradata/SYD/datafile/undotbs01' SIZE 10m autoextend on; Tablespace created.   SQL> alter system set undo_tablespace=UNDOTBS2 scope=both; System altered.   SQL> show parameter undo NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ undo_management string AUTO undo_retention integer 900 undo_tablespace string UNDOTBS2

至此，数据库恢复完成，可测业务是否正常，若仍存在之前undo表空间信息的报错，可参考如下步骤。

删除之前的undo表空间。 SQL> drop tablespace undotbs1 including contents and datafiles; drop tablespace undotbs1 including contents and datafiles * ERROR at line 1: ORA-01548: active rollback segment '_SYSSMU1_3724004606$' found, terminate dropping tablespace 若报如上错误，表示之前undo表空间中存在未回滚的事务。   查询需要回滚的回滚段信息 SQL> select SEGMENT_NAME,TABLESPACE_NAME,STATUS from dba_rollback_segs;   SEGMENT_NAME TABLESPACE_NAME STATUS ------------------------------ ------------------------- ---------------- SYSTEM SYSTEM ONLINE _SYSSMU10_1197734989$ UNDOTBS1 NEEDS RECOVERY _SYSSMU9_1650507775$ UNDOTBS1 NEEDS RECOVERY _SYSSMU8_517538920$ UNDOTBS1 NEEDS RECOVERY _SYSSMU7_2070203016$ UNDOTBS1 NEEDS RECOVERY _SYSSMU6_1263032392$ UNDOTBS1 NEEDS RECOVERY _SYSSMU5_898567397$ UNDOTBS1 NEEDS RECOVERY _SYSSMU4_1254879796$ UNDOTBS1 NEEDS RECOVERY _SYSSMU3_1723003836$ UNDOTBS1 NEEDS RECOVERY _SYSSMU2_2996391332$ UNDOTBS1 NEEDS RECOVERY _SYSSMU1_3724004606$ UNDOTBS1 NEEDS RECOVERY _SYSSMU20_4055497039$ UNDOTBS2 ONLINE _SYSSMU19_939638859$ UNDOTBS2 ONLINE _SYSSMU18_2091397745$ UNDOTBS2 ONLINE _SYSSMU17_3633325321$ UNDOTBS2 ONLINE _SYSSMU16_1545911387$ UNDOTBS2 ONLINE _SYSSMU15_3486956598$ UNDOTBS2 ONLINE _SYSSMU14_336203531$ UNDOTBS2 ONLINE _SYSSMU13_1164891431$ UNDOTBS2 ONLINE _SYSSMU12_2252700889$ UNDOTBS2 ONLINE _SYSSMU11_767382873$ UNDOTBS2 ONLINE   21 rows selected.   将以上查询到的回滚段信息加入参数文件中，跳过回滚段恢复（_CORRUPTED_ROLLBACK_SEGMENTS参数控制） [oracle@dj dbs]$ vi initsyd.ora syd.__db_cache_size=754974720 syd.__java_pool_size=16777216 syd.__large_pool_size=33554432 syd.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment syd.__pga_aggregate_target=301989888 syd.__sga_target=1157627904 syd.__shared_io_pool_size=0 syd.__shared_pool_size=335544320 syd.__streams_pool_size=0 *.audit_file_dest='/u01/app/oracle/admin/syd/adump' *.audit_trail='db' *.compatible='11.2.0.4.0' *.control_files='/u01/app/oracle/oradata/SYD/controlfile/o1_mf_cvdwphpf_.ctl' *.db_block_size=8192 *.db_create_file_dest='/u01/app/oracle/oradata' *.db_domain='' *.db_name='syd' *.diagnostic_dest='/u01/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=sydXDB)' *.open_cursors=300 *.pga_aggregate_target=288358400 *.processes=1500 *.remote_login_passwordfile='EXCLUSIVE' *.sessions=1655 *.sga_target=1153433600 *.undo_tablespace='UNDOTBS2' _CORRUPTED_ROLLBACK_SEGMENTS=(_SYSSMU10_1197734989$,_SYSSMU9_1650507775$,_SYSSMU8_517538920$,_SYSSMU7_2070203016$,_SYSSMU6_1263032392$,_SYSSMU5_898567397$,_SYSSMU4_1254879796$,_SYSSMU3_1723003836$,_SYSSMU2_2996391332$,_SYSSMU1_3724004606$)   利用修改的参数文件重启数据库 SQL> shu immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> exit SQL> startup pfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initsyd.ora'; ORACLE instance started.   Total System Global Area 1152450560 bytes Fixed Size 2252584 bytes Variable Size 956301528 bytes Database Buffers 184549376 bytes Redo Buffers 9347072 bytes Database mounted. Database opened.   删除之前的undo表空间 SQL> drop tablespace undotbs1 including contents and datafiles; Tablespace dropped.   正常重启数据库，验证业务。 SQL> shu immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options [oracle@dj ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Sat Jun 2 19:17:20 2018 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to an idle instance. SQL> startup ORACLE instance started. Total System Global Area 1152450560 bytes Fixed Size 2252584 bytes Variable Size 956301528 bytes Database Buffers 184549376 bytes Redo Buffers 9347072 bytes Database mounted. Database opened. SQL>