BigdataLyn's TECH Blog! 刀不磨要生锈,人不学习要落后 - Thinking ahead

Oracle Problem about UNDO Tablespace - ORA-01548 ORA-01157 ORA-01110

2018-05-23

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


Similar Posts

Comments