手工建库 重建控制文件 报错 ORA-01092

(1)编辑.bash_profile
vi .bash_profile
export ORACLE_SID=LH
export ORACLE_BASE=/opt/oracle
export ORACLE_HOME=/opt/oracle/db
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib

(2)创建文本初始化参数文件及相应目录
oracle:
vi $ORACLE_HOME/dbs/initLH.ora
compatible='10.2.0.1.0'
db_name='lh'
sga_max_size=230000000
sga_target=  180000000
undo_management='AUTO'
undo_tablespace='UNDOTBS1'
background_dump_dest='$ORACLE_BASE/admin/LH/bdump'
user_dump_dest='$ORACLE_BASE/admin/LH/udump'
core_dump_dest='$ORACLE_BASE/admin/LH/cdump'
control_files='$ORACLE_BASE/oradata/lh/control01.ctl','$ORACLE_BASE/oradata/lh/control02.ctl','$ORACLE_BASE/oradata/lh/control03.ctl'

cd $ORACLE_BASE/admin
mkdir LH
mkdir LH/bdump
mkdir LH/cdump
mkdir LH/udump
mkdir -p $ORACLE_BASE/oradata/lh

(4)创建密码文件
ORACLE_SID=LH
cd $ORACLE_HOME/dbs
orapwd file=orapwLH password=oracle

(5)创建二进制初始化参数文件和数据库
sqlplus sys/oracle as sysdba
startup nomount pfile=initLH.ora
create spfile from pfile;
shutdown immediate
startup nomount
CREATE DATABASE lh
   USER SYS IDENTIFIED BY oracle
   USER SYSTEM IDENTIFIED BY oracle
   LOGFILE GROUP 1 ('$ORACLE_BASE/oradata/lh/redo01.log') SIZE 50M,
           GROUP 2 ('$ORACLE_BASE/oradata/lh/redo02.log') SIZE 50M,
           GROUP 3 ('$ORACLE_BASE/oradata/lh/redo03.log') SIZE 50M
   MAXLOGFILES 30      --the maximum number of groups
   MAXLOGMEMBERS 5    --the maximum number of members for each group
   MAXLOGHISTORY 200   --MAXLOGHISTORY 定义了controlfile中可以存储多少个log file的信息
   MAXDATAFILES 100
   MAXINSTANCES 1
   CHARACTER SET ZHS16GBK
   NATIONAL CHARACTER SET AL16UTF16
   DATAFILE '$ORACLE_BASE/oradata/lh/system01.dbf' SIZE 325M autoextend on next 10m maxsize unlimited
     EXTENT MANAGEMENT LOCAL
   SYSAUX DATAFILE '$ORACLE_BASE/oradata/lh/sysaux01.dbf' SIZE 325M autoextend on next 10m maxsize unlimited   
   DEFAULT TEMPORARY TABLESPACE temp
      TEMPFILE '$ORACLE_BASE/oradata/lh/temp01.dbf'
      SIZE 200M autoextend on next 10m maxsize unlimited
   UNDO TABLESPACE undotbs
      DATAFILE '$ORACLE_BASE/oradata/lh/undotbs01.dbf'
      SIZE 325M  AUTOEXTEND ON MAXSIZE UNLIMITED;

select open_mode , name from v$database;
         READ WRITE LH
select current_scn,CHECKPOINT_CHANGE# from v$database;
         8506               8411 
create tablespace users datafile '$ORACLE_BASE/oradata/lh/users01.dbf' size 100m
    autoextend on next 5m maxsize unlimited
  extent management local
  segment space management auto ;                 
alter database default tablespace users;

 

建库过程中，遇到ORA-01092 错误，根据 alter日志发现：

Mon Jul  4 15:33:54 2011
Errors in file /oracle/admin/ORATEST/udump/oratest_ora_950404.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-30012: undo tablespace 'UNDOTBS1' does not exist or of wrong type
Mon Jul  4 15:33:54 2011
Errors in file /oracle/admin/ORATEST/udump/oratest_ora_950404.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 = 950404
ORA-1092 signalled during: CREATE DATABASE ORATEST
   USER SYS IDENTIFIED BY ****USER SYSTEM IDENTIFIED BY ****LOGFILE GROUP 1 ('$ORACLE_BASE/oradata/ORATEST/redo01.log') SIZE
50M,
           GROUP 2 ('$ORACLE_BASE/oradata/ORATEST/redo02.log') SIZE 50M,
           GROUP 3 ('$ORACLE_BASE/oradata/ORATEST/redo03.log') SIZE 50M
   MAXLOGFILES 16
   MAXLOGMEMBERS 3
   MAXLOGHISTORY 1
   MAXDATAFILES 100
   MAXINSTANCES 8
   CHARACTER SET ZHS16GBK
   NATIONAL CHARACTER SET AL16UTF16
   DATAFILE '$ORACLE_BASE/oradata/ORATEST/system01.dbf' SIZE 325M autoextend on next 10m maxsize unlimited
     EXTENT MANAGEMENT LOCAL
   SYSAUX DATAFILE '$ORACLE_BASE/oradata/ORATEST/sysaux01.dbf' SIZE 325M autoextend on next 10m maxsize unlimited
   DEFAULT TEMPORARY TABLESPACE temp
      TEMPFILE '$ORACLE_BASE/oradata/ORATEST/temp01.dbf'
      SIZE 200M autoextend on next 10m maxsize unlimited
   UNDO TABLESPACE undotbs
      DATAFILE '$ORACLE_BASE/oradata/ORATEST/undotbs01.dbf'
      SIZE 325M  AUTOEXTEND ON MAXSIZE UNLIMITED...

 

问题出在 没有认真对照个参数，参数和控制文件是有关联的。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22782896/viewspace-701314/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22782896/viewspace-701314/