ORA-30012: undo tablespace ‘UNDOTBS1’ does not exist or of wrong type

 Xử lý như sau

sqlplus sys/sysdba as sysdba

1. SQL> startup mount;
2. SQL>show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1

3. SQL> alter system set undo_management='MANUAL' scope=spfile;

4. SQL>shutdown immediate;

5. SQL> conn / as sysdba

6. startup;

Execute following query to find undo tablespace name.

SELECT VALUE, name
FROM gv$parameter
WHERE name LIKE '%undo_tablespace%';

execute following query and verify undo tablespace path and name and note the information path can be used to create new file in coming steps.

select tablespace_name,file_name
from dba_data_files
where tablespace_name like 'UNDO%'

if the undo tablespace name is different than the name returned by query at step 5 then execute following query and drop the tablespace returned by above query.

SQL> DROP TABLESPACE Tablespace name INCLUDING CONTENTS AND DATAFILES;

Example

-- SQL> DROP TABLESPACE UNDOTBS  INCLUDING CONTENTS AND DATAFILES;

9. SQL>create undo tablespace UNDOTBS1 datafile '/location/filename' size 50M autoextend on;

10. SQL> alter system set undo_management='AUTO' scope=spfile;

11. SQL>shutdown immediate;
12 SQL>conn  / as sysdba
13. SQL> startup mount;
14. SQL> show parameter undo;
14 SQL> alter database open;
16. SQL> alter system set undo_tablespace='UNDOTBS1' scope=BOTH;

--------------------------------------------

If database is running and undo_management='AUTO'.

sqlplus sys/sysdba as sysdba

create undo tablespace UNDOTBS datafile 'location/filename' size 50M autoextend on;

alter system set undo_tablespace='UNDOTBS' scope=BOTH;