原文地址:点击打开链接
这是一个关乎细节的问题,有朋友在手工完成数据库创建的过程中反复收到报错信息,在没有仔细研究报错信息内容的情况下浪费了大量的时间。记录在此,供参考。
1.报错信息如下
SQL> CREATE DATABASE secdb
USER SYS IDENTIFIED BY oracle
USER SYSTEM IDENTIFIED BY oracle
LOGFILE GROUP 1 ('/u01/app/oracle/oradata/secdb/redo01.log') SIZE 100M,
GROUP 2 ('/u01/app/oracle/oradata/secdb/redo02.log') SIZE 100M,
GROUP 3 ('/u01/app/oracle/oradata/secdb/redo03.log') SIZE 100M
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 100
MAXINSTANCES 1
CHARACTER SET US7ASCII
NATIONAL CHARACTER SET AL16UTF16
DATAFILE '/u01/app/oracle/oradata/secdb/system01.dbf' SIZE 325M REUSE
EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE '/u01/app/oracle/oradata/secdb/sysaux01.dbf' SIZE 325M REUSE
DEFAULT TABLESPACE tbs_1 DATAFILE '/u01/app/oracle/oradata/secdb/tbs_1.dbf' SIZE 50M
DEFAULT TEMPORARY TABLESPACE tempts1
TEMPFILE '/u01/app/oracle/oradata/secdb/temp01.dbf'
SIZE 20M REUSE
UNDO TABLESPACE undotbs
DATAFILE '/u01/app/oracle/oradata/secdb/undotbs01.dbf'
SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 CREATE DATABASE secdb
*
ERROR at line 1:
ORA-30014: operation only supported in Automatic Undo Management mode
2.问题原因
使用oerr工具探寻关于ORA-30014错误的描述信息
[oracle@secdb1 ~]$ oerr ora 30014
30014, 00000, "operation only supported in Automatic Undo Management mode"
// *Cause: the operation is only supported in automatic undo mode.
// *Action: restart instance in Automatic Undo Management mode before
// retrying operation.
无论是简短的报错信息还是oerr工具给出的提示信息,如果稍微自己阅读,问题原因便不言自明。
该问题是由于UNDO未处于自动管理模式导致的。
3.问题处理
如果将UNDO置于自动管理模式下呢?这与undo_management参数(该参数的Oracle官方文档参考信息请访问:http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/initparams221.htm#CHDBHHJC)有关,该参数默认是MANUAL,需要手工将其调整为AUTO。
SQL> show parameter undo_management
NAME TYPE VALUE
------------------------------------ ----------- ---------------
undo_management string MANUAL
SQL> alter system set undo_management=AUTO scope=spfile;
System altered.
SQL> startup force nomount;
ORACLE instance started.
Total System Global Area 314572800 bytes
Fixed Size 1219184 bytes
Variable Size 96470416 bytes
Database Buffers 213909504 bytes
Redo Buffers 2973696 bytes
SQL> show parameter undo_management
NAME TYPE VALUE
------------------------------------ ----------- ---------------
undo_management string AUTO
4.重新尝试手工创建数据库
注意:在执行创建数据库语句之前需要清理一下已经创建的控制文件。
1)清理报错之前创建的文件
[oracle@secdb1 ~]$ rm -f /u01/app/oracle/oradata/secdb/*
2)创建数据库
SQL> CREATE DATABASE secdb
USER SYS IDENTIFIED BY oracle
USER SYSTEM IDENTIFIED BY oracle
LOGFILE GROUP 1 ('/u01/app/oracle/oradata/secdb/redo01.log') SIZE 100M,
GROUP 2 ('/u01/app/oracle/oradata/secdb/redo02.log') SIZE 100M,
GROUP 3 ('/u01/app/oracle/oradata/secdb/redo03.log') SIZE 100M
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 100
MAXINSTANCES 1
CHARACTER SET US7ASCII
NATIONAL CHARACTER SET AL16UTF16
DATAFILE '/u01/app/oracle/oradata/secdb/system01.dbf' SIZE 325M REUSE
EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE '/u01/app/oracle/oradata/secdb/sysaux01.dbf' SIZE 325M REUSE
DEFAULT TABLESPACE tbs_1 DATAFILE '/u01/app/oracle/oradata/secdb/tbs_1.dbf' SIZE 50M
DEFAULT TEMPORARY TABLESPACE tempts1
TEMPFILE '/u01/app/oracle/oradata/secdb/temp01.dbf'
SIZE 20M REUSE
UNDO TABLESPACE undotbs
DATAFILE '/u01/app/oracle/oradata/secdb/undotbs01.dbf'
SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23
Database created.
SQL> select open_mode from v$database;
OPEN_MODE
----------
READ WRITE
OK,此时,数据库创建成功。可以在此基础上完成后续的数据字典及存储过程的创建了。
5.小结
之所以记录该问题,只是想提醒各位好朋友,在遇到问题时,即使时间非常的紧迫,也要认真分析报错提示信息,这样可以防止浪费更多的宝贵时间。
Good luck.
secooler
10.08.09
-- The End --