oracle ORA-30012: undo tablespace 'UNDOTBS1' does not exist or of wrong type

最新推荐文章于 2021-09-21 11:35:44 发布

原创最新推荐文章于 2021-09-21 11:35:44 发布 · 3.4k 阅读

0 ·

CC 4.0 BY-SA版权

oracle 专栏收录该内容

21 篇文章

订阅专栏

本文记录了在手工创建ORACLE数据库过程中遇到的错误及解决方法。主要问题为ORA-01519和ORA-30012等错误，涉及未定义的UNDOTBS1表空间。通过检查pfile设置发现并修正了问题。

在手工创建数据库的过程中遇到如下错误：

SQL> @/home/oracle/scripts/create.sql
CREATE DATABASE PROD
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-01501: CREATE DATABASE failed
ORA-01519: error while processing file '?/rdbms/admin/dtxnspc.bsq' near line 5
ORA-00604: error occurred at recursive SQL level 1
ORA-30012: undo tablespace 'UNDOTBS1' does not exist or of wrong type
Process ID: 6250
Session ID: 1 Serial number: 3

奇怪了，这UNDOTBS1是哪里来的，我的建库脚本里也没有这个呀。

[oracle@localhost scripts]$ cat create.sql
CREATE DATABASE PROD
   USER SYS IDENTIFIED BY oracle1
   USER SYSTEM IDENTIFIED BY oracle1
   LOGFILE GROUP 1 ('/u01/app/oracle/oradata/PROD/Disk1/redo01a.log','/u01/app/oracle/oradata/PROD/Disk2/redo01b.log') SIZE 100M BLOCKSIZE 512,
           GROUP 2 ('/u01/app/oracle/oradata/PROD/Disk1/redo02a.log','/u01/app/oracle/oradata/PROD/Disk2/redo02b.log') SIZE 100M BLOCKSIZE 512,
           GROUP 3 ('/u01/app/oracle/oradata/PROD/Disk1/redo03a.log','/u01/app/oracle/oradata/PROD/Disk2/redo03b.log') SIZE 100M BLOCKSIZE 512
   MAXLOGFILES 5
   MAXLOGMEMBERS 5
   MAXLOGHISTORY 1
   MAXDATAFILES 100
   CHARACTER SET AL32UTF8
   NATIONAL CHARACTER SET AL16UTF16
   EXTENT MANAGEMENT LOCAL
   DATAFILE '/u01/app/oracle/oradata/PROD/Disk1/system01.dbf' SIZE 325M REUSE
   SYSAUX DATAFILE '/u01/app/oracle/oradata/PROD/Disk1/sysaux01.dbf' SIZE 325M REUSE
   DEFAULT TABLESPACE users
      DATAFILE '/u01/app/oracle/oradata/PROD/Disk1/users01.dbf'
      SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
   DEFAULT TEMPORARY TABLESPACE tempts1
      TEMPFILE '/u01/app/oracle/oradata/PROD/Disk1/temp01.dbf'
      SIZE 20M REUSE
   UNDO TABLESPACE undotbs
      DATAFILE '/u01/app/oracle/oradata/PROD/Disk1/undotbs01.dbf'
      SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

只有undotbs这个tablespace呀！看看错误信息：ORA-01519: error while processing file '?/rdbms/admin/dtxnspc.bsq' near line 5，把它打开看看第五行写的啥。

[oracle@localhost PROD]$ cat $ORACLE_HOME/rdbms/admin/dtxnspc.bsq|more
REM jklein 08/01/05 - creation
REM
rem Create default undo tablespace
"D_DUTSCR"
/
REM

说实话我看不懂这啥意思，最后经调查得知，原来是是在pfile中设置了undo表空间，且表空间名与所创建的不一致，导致了以上错误。

*.undo_tablespace='UNDOTBS1'
删掉或修改即可。