這是一個關乎細節的問題，有朋友在手工完成資料庫建立的過程中反覆收到報錯資訊，在沒有仔細研究報錯資訊內容的情況下浪費了大量的時間。
記錄在此，供參考。

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官方文件參考資訊請訪問：）有關，該引數預設是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 --

【手工建庫】確保資料庫處於自動 UNDO管理模式

相關文章