OCP课程实验问题1- ORA-01092

1、执行手工建库语句：

create database welson
user sys identified by oracle
user system identified by oracle
logfile group 1 ('/u01/app/oradata/welson/redo01.log') size 50M,
				group 2 ('/u01/app/oradata/welson/redo02.log') size 50M,
				group 3 ('/u01/app/oradata/welson/redo03.log') size 50M
maxlogfiles 5
maxlogmembers 5
maxloghistory 1
maxdatafiles 500
maxinstances 1
character set AL32UTF8
national character set UTF8
datafile '/u01/app/oradata/welson/system.dbf' size 500M
extent management local
sysaux datafile '/u01/app/oradata/welson/sysaux01.dbf' size 500M
default tablespace users01
  datafile '/u01/app/oradata/welson/user01.dbf' size 100M
default temporary tablespace temp
	tempfile '/u01/app/oradata/welson/temp01.dbf' size 50M
undo tablespace undotbs
	datafile '/u01/app/oradata/welson/undo.dbf' size 50M;

 报错

create database welson
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced

 查看$ORACLE_BASE/admin/welson/bdump/alert_welson.log文件，

Thu Jun 20 15:19:41 2013
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 2
Using LOG_ARCHIVE_DEST_1 parameter default value as /u01/app/oracle/dbs/arch
Autotune of undo retention is turned on. 
IMODE=BR
ILAT =10
LICENSE_MAX_USERS = 0
SYS auditing is disabled
ksdpec: called for event 13740 prior to event group initialization
Starting up ORACLE RDBMS Version: 10.2.0.1.0.
System parameters with non-default values:
  __shared_pool_size       = 83886080
  __large_pool_size        = 4194304
  __java_pool_size         = 4194304
  __streams_pool_size      = 0
  sga_target               = 285212672
  control_files            = /u01/app/oradata/welson/control01.ctl, /u01/app/oradata/welson/control02.ctl
  db_block_size            = 8192
  __db_cache_size          = 188743680
  undo_management          = AUTO
  undo_tablespace          = UNDOTBS1
  db_name                  = welson
PMON started with pid=2, OS id=3074
PSP0 started with pid=3, OS id=3076
MMAN started with pid=4, OS id=3078
DBW0 started with pid=5, OS id=3080
LGWR started with pid=6, OS id=3082
RECO started with pid=9, OS id=3088
SMON started with pid=8, OS id=3086
MMON started with pid=10, OS id=3090
MMNL started with pid=11, OS id=3092
CKPT started with pid=7, OS id=3084
Thu Jun 20 15:20:04 2013
create database welson
user sys identified by *user system identified by *logfile group 1 ('/u01/app/oradata/welson/redo01.log') size 50M,
                                group 2 ('/u01/app/oradata/welson/redo02.log') size 50M,
                                group 3 ('/u01/app/oradata/welson/redo03.log') size 50M
maxlogfiles 5
maxlogmembers 5
maxloghistory 1
maxdatafiles 500
maxinstances 1
character set AL32UTF8
national character set UTF8
datafile '/u01/app/oradata/welson/system.dbf' size 500M
extent management local
sysaux datafile '/u01/app/oradata/welson/sysaux01.dbf' size 500M
default tablespace users01
  datafile '/u01/app/oradata/welson/user01.dbf' size 100M
default temporary tablespace temp
        tempfile '/u01/app/oradata/welson/temp01.dbf' size 50M
undo tablespace undotbs
        datafile '/u01/app/oradata/welson/undotbs1.dbf' size 50M
Thu Jun 20 15:20:16 2013
Database mounted in Exclusive Mode
Thu Jun 20 15:20:42 2013
Successful mount of redo thread 1, with mount id 1925487268
Assigning activation ID 1925487268 (0x72c49aa4)
Thread 1 opened at log sequence 1
  Current log# 1 seq# 1 mem# 0: /u01/app/oradata/welson/redo01.log
Successful open of redo thread 1
Thu Jun 20 15:20:43 2013
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Thu Jun 20 15:20:43 2013
create tablespace SYSTEM datafile  '/u01/app/oradata/welson/system.dbf' size 500M

  EXTENT MANAGEMENT LOCAL online
Thu Jun 20 15:20:43 2013
SMON: enabling cache recovery
Thu Jun 20 15:21:23 2013
Completed: create tablespace SYSTEM datafile  '/u01/app/oradata/welson/system.dbf' size 500M
  EXTENT MANAGEMENT LOCAL online
Thu Jun 20 15:21:23 2013
create rollback segment SYSTEM tablespace SYSTEM
  storage (initial 50K next 50K)
Completed: create rollback segment SYSTEM tablespace SYSTEM
  storage (initial 50K next 50K)
Thu Jun 20 15:21:33 2013
CREATE UNDO TABLESPACE UNDOTBS DATAFILE  '/u01/app/oradata/welson/undotbs1.dbf' size 50M
Thu Jun 20 15:21:38 2013
ORA-30012 signalled during: CREATE UNDO TABLESPACE UNDOTBS DATAFILE  '/u01/app/oradata/welson/undotbs1.dbf' size 50M
...
Thu Jun 20 15:21:38 2013
Errors in file /u01/app/admin/welson/udump/welson_ora_3093.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-30012: undo tablespace 'UNDOTBS1' does not exist or of wrong type
Thu Jun 20 15:21:38 2013
Errors in file /u01/app/admin/welson/udump/welson_ora_3093.trc:
ORA-01501: CREATE DATABASE failed
ORA-01519: error while processing file '?/rdbms/admin/sql.bsq' near line 5792
ORA-00604: error occurred at recursive SQL level 1
ORA-30012: undo tablespace 'UNDOTBS1' does not exist or of wrong type
Error 1519 happened during db open, shutting down database
USER: terminating instance due to error 1519
Instance terminated by USER, pid = 3093
ORA-1092 signalled during: create database welson
user sys identified by *user system identified by *logfile group 1 ('/u01/app/oradata/welson/redo01.log') size 50M,
                                group 2 ('/u01/app/oradata/welson/redo02.log') size 50M,
                                group 3 ('/u01/app/oradata/welson/redo03.log') size 50M
maxlogfiles 5
maxlogmembers 5
maxloghistory 1
maxdatafiles 500
maxinstances 1
character set AL32UTF8
national character set UTF8
datafile '/u01/app/oradata/welson/system.dbf' size 500M
extent management local
sysaux datafile '/u01/app/oradata/welson/sysaux01.dbf' size 500M
default tablespace users01
  datafile '/u01/app/oradata/welson/user01.dbf' size 100M
default temporary tablespace temp
        tempfile '/u01/app/oradata/welson/temp01.dbf' size 50M
undo tablespace undotbs
        datafile '/u01/app/oradata/welson/undotbs1.dbf' size 50M...

 

 解决方案：

在init.ora中，如果undo_management设为auto，undo_tablespace等等的名字必须与手工建立的undotbs一致，此处中，undo tablespace undotbs应该改为undo tablespace undotbs1

initwelson.ora文件：

*.control_files='/u01/app/oradata/welson/control01.ctl','/u01/app/oradata/welson/control02.ctl'
*.db_name='welson'
*.db_block_size=8192
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.sga_target=285212672

 

问题解决。