环境：
系统：linux6.8
oracle版本：11.2.0.4
归档：非归档
查询到oracle数据库版本
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
创建备份控制文件。
SQL> alter database backup controlfile to trace as '/home/oracle/wangbb.ctl';
Database altered.
******************************************************************************
CREATE CONTROLFILE REUSE DATABASE "WANGBB" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/oracle/app/oracle/wangbb/wangbb/redo01.log' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/oracle/app/oracle/wangbb/wangbb/redo02.log' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/oracle/app/oracle/wangbb/wangbb/redo03.log' SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/oracle/app/oracle/wangbb/wangbb/system01.dbf',
'/oracle/app/oracle/wangbb/wangbb/sysaux01.dbf',
'/oracle/app/oracle/wangbb/wangbb/undotbs01.dbf',
'/oracle/app/oracle/wangbb/wangbb/users01.dbf'
CHARACTER SET ZHS16GBK
;
******************************************************************************
删除undo数据文件，控制数据文件。
$ rm oracle/app/oracle/oradata/fast_recovery_area/wangbb/
$ rm oracle/app/oracle/oradata/fast_recovery_area/wangbb/control02.ctl
$ rm oracle/app/oracle/wangbb/wangbb/undotbs01.dbf
下面正式开始操作：
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 2505338880 bytes
Fixed Size 2255832 bytes
Variable Size 637535272 bytes
Database Buffers 1845493760 bytes
Redo Buffers 20054016 bytes
SQL> @ctl.sql
注ctl.sql内容即为上方*****XXXX*****内容
SQL> @ctl.sql
CREATE CONTROLFILE REUSE DATABASE "WANGBB" RESETLOGS ARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01565: error in identifying file
'/oracle/app/oracle/wangbb/wangbb/undotbs01.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
此时报错是因为控制文件脚本中包含了刚才删除的undotbs01.dbf数据文件。将此行“ '/oracle/app/oracle/wangbb/wangbb/undotbs01.dbf',”清除。
SQL> @ctl.sql
Control file created.
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/oracle/app/oracle/wangbb/wangbb/system01.dbf'
SQL> recover database until cancel;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 987372 generated at 11/27/2019 15:51:59 needed for thread 1
ORA-00289: suggestion :
/oracle/app/oracle/oradata/fast_recovery_area/WANGBB/archivelog/2019_11_27/o1_mf
_1_10_%u_.arc
ORA-00280: change 987372 for thread 1 is in sequence #10
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/oracle/app/oracle/wangbb/wangbb/redo01.log
Log applied.
Media recovery complete.
此时再用resetlogs开启数据库。发现报错，跟踪alter日志
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-01173: data dictionary indicates missing data file from system tablespace
Process ID: 19410
Session ID: 34 Serial number: 1
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Wed Nov 27 16:09:46 2019
SMON: enabling cache recovery
Wed Nov 27 16:09:46 2019
ARC2 started with pid=28, OS id=22740
Wed Nov 27 16:09:46 2019
ARC3 started with pid=29, OS id=22742
ARC1: Archival started
ARC2: Archival started
ARC1: Becoming the 'no FAL' ARCH
ARC1: Becoming the 'no SRL' ARCH
ARC2: Becoming the heartbeat ARCH
ARC3: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
Errors in file oracle/app/oracle/diag/rdbms/wangbb/wangbb/trace/wangbb_ora_19410.trc:
ORA-01173: data dictionary indicates missing data file from system tablespace
Errors in file oracle/app/oracle/diag/rdbms/wangbb/wangbb/trace/wangbb_ora_19410.trc:
ORA-01173: data dictionary indicates missing data file from system tablespace
Error 1173 happened during db open, shutting down database
USER (ospid: 19410): terminating the instance due to error 1173
Instance terminated by USER, pid = 19410
ORA-1092 signalled during: alter database open resetlogs...
opiodr aborting process unknown ospid (19410) as a result of ORA-1092
Wed Nov 27 16:09:47 2019
ORA-1092 : opitsk aborting process
解决方案：
第一步：
使用两个参数，
*.undo_management='manual'
_corrupted_rollback_segments
补充：
_corrupted_rollback_segments （允许在rollback segments 损坏的情况下启动数据库）
在linux系统下通过以下命令查看回滚端：
$string SYSTEM.dbf|grep "_SYSMU"
SQL> create pfile='/home/oracle/wangbb.ora' from spfile;
File created
在pfile文件最后加入两行：
*._corrupted_rollback_segments=(_SYSSMU1_3724004606$,_SYSSMU2_2996391332$,_SYSSMU3_1723003836$,_SYSSMU4_1254879796$,_SYSSMU5_898567397$,_SYSSMU6_1263032392$,_SYSSMU7_2070203016$,_SYSSMU8_517538920$,_SYSSMU9_1650507775$,_SYSSMU10_1197734989$)
*.undo_management='manual'
然后使用pfile启动数据库；
SQL> startup nomount pfile='/home/oracle/wangbb.ora';
ORACLE instance started.
Total System Global Area 2505338880 bytes
Fixed Size 2255832 bytes
Variable Size 637535272 bytes
Database Buffers 1845493760 bytes
Redo Buffers 20054016 bytes
启动之后重新创建控制文件。
SQL> @ctl.sql
Control file created.
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 987719 generated at 11/27/2019 16:53:14 needed for thread 1
ORA-00289: suggestion :
/oracle/app/oracle/oradata/fast_recovery_area/WANGBB/archivelog/2019_11_27/o1_mf
_1_1_%u_.arc
ORA-00280: change 987719 for thread 1 is in sequence #1
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/oracle/app/oracle/wangbb/wangbb/redo01.log
Log applied.
Media recovery complete.
SQL> alter database open resetlogs;
Database altered.
SQL>
数据库已经起来，能起来比什么都重要。
创建undo文件并
SQL> create undo tablespace undowbb datafile '/oracle/app/oracle/wangbb/wangbb/undowbb.dbf' size 100M autoextend on next 10M maxsize 4G;
Tablespace created.
将pfile文件参数后两行删掉修改：
*.undo_tablespace='undowbb'
*.undo_management='auto'
SQL> conn / as sysdba
Connected to an idle instance.
SQL> create spfile from pfile='/home/oracle/wangbb.ora'
File created.
SQL> startup
ORACLE instance started.
Total System Global Area 2505338880 bytes
Fixed Size 2255832 bytes
Variable Size 637535272 bytes
Database Buffers 1845493760 bytes
Redo Buffers 20054016 bytes
Database mounted.
Database opened.
SQL>
最后，需要提醒的是要将oracle数据库逻辑导出到新库中以防万一。
