Oracle Problem about UNDO Tablespace - ORA-01548 ORA-01157 ORA-01110
I want to replace this UNDO tablespace with new UNDO tablespace ( UNDOTBSN01) and drop this existing tablespace i.e (UNDOTBS1).And I met the error-ORA-01548. How to fixed this error?
The demonstrate of error #ORA-01157/#ORA-01110
The undo information:
SYS@orcl11g> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 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
SYS@orcl11g> col name for a60
SYS@orcl11g> select name,status from v$datafile;
NAME STATUS
------------------------------------------------------------ -------
/u01/app/oracle/oradata/orcl11g/system01.dbf SYSTEM
/u01/app/oracle/oradata/orcl11g/sysaux01.dbf ONLINE
/u01/app/oracle/oradata/orcl11g/users01.dbf ONLINE
/u01/app/oracle/oradata/orcl11g/data01.dbf ONLINE
/u01/app/oracle/oradata/orcl11g/undotbs02.dbf ONLINE
SYS@orcl11g> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS02
SYS@orcl11g>
Remove the undo datafile in OS level.
SYS@orcl11g> host rm /u01/app/oracle/oradata/orcl11g/undotbs02.dbf
SYS@orcl11g>
The following error message will be in the alert log file.
Errors in file /u01/app/oracle/diag/rdbms/orcl11g/orcl11g/trace/orcl11g_j000_2804.trc:
ORA-12012: error on auto execute of job 23
ORA-01116: error in opening database file 6
ORA-01110: data file 6: '/u01/app/oracle/oradata/orcl11g/undotbs02.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
The error #ORA-01157/#ORA-01110
SYS@orcl11g> shu abort;
ORACLE instance shut down.
SYS@orcl11g> startup mount;
ORACLE instance started.
Total System Global Area 1556787200 bytes
Fixed Size 1364844 bytes
Variable Size 889195668 bytes
Database Buffers 654311424 bytes
Redo Buffers 11915264 bytes
Database mounted.
SYS@orcl11g> alter database open;
alter database open
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: '/u01/app/oracle/oradata/orcl11g/undotbs02.dbf'
SYS@orcl11g>
To be continue….
Have a good day! 2018/05 via LinHong