在手工创建数据库的过程中遇到如下错误:
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'
删掉或修改即可。