Oracle XE的数据库创建过程

今天安装了Oracle XE，发现并没有自动创建数据库。趁着这个机会顺便观察了把alert log，重新回顾一下数据库手工创建过程。

文章末尾附带了所用到的脚本。

1. 创建SPFILE。

?12345678910111213141516171819SQL*Plus: Release 10.2.0.1.0 - Production on Fri Sep 1 15:53:26 2006?Copyright (c) 1982, 2005, Oracle. All rights reserved.?SQL> conn / as sysdbaConnected.?SQL> create spfile from pfile='c:\pfile.ora';?File created.?SQL> startup nomountORACLE instance started.?Total System Global Area 146800640 bytesFixed Size 1286220 bytesVariable Size 58724276 bytesDatabase Buffers 83886080 bytesRedo Buffers 2904064 bytes

2. 创建数据库。

?123SQL> @C:\createdb_xe.sql?Database created.

需要注意的是，default undo tablespace必须与参数文件中的一致，否则会在创建中途导致实例异常终止。关于原因在第三步中分析。

3. 后台过程。
在创建过程中可以用记事本等文本编辑工具查看alert_xe.log文件，推荐使用PSPad，可以及时查看更新信息。

代码如下：

?123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165Starting up ORACLE RDBMS Version: 10.2.0.1.0.System parameters with non-default values:sessions = 49sga_target = 146800640control_files = D:\ORACLEXE\ORADATA\XE\CONTROL.DBFcompatible = 10.2.0.1.0db_recovery_file_dest = D:\oraclexe\app\oracle\flash_recovery_areadb_recovery_file_dest_size= 1073741824undo_management = AUTOundo_tablespace = undotbsremote_login_passwordfile= EXCLUSIVEaudit_file_dest = D:\ORACLEXE\APP\ORACLE\ADMIN\XE\ADUMPbackground_dump_dest = D:\ORACLEXE\APP\ORACLE\ADMIN\XE\BDUMPuser_dump_dest = D:\ORACLEXE\APP\ORACLE\ADMIN\XE\UDUMPcore_dump_dest = D:\ORACLEXE\APP\ORACLE\ADMIN\XE\CDUMPdb_name = XEopen_cursors = 100pga_aggregate_target = 16777216PMON started with pid=2, OS id=1008PSP0 started with pid=3, OS id=416MMAN started with pid=4, OS id=332DBW0 started with pid=5, OS id=1088LGWR started with pid=6, OS id=712CKPT started with pid=7, OS id=1028SMON started with pid=8, OS id=412RECO started with pid=9, OS id=1144MMON started with pid=10, OS id=928MMNL started with pid=11, OS id=468Fri Sep 01 15:55:08 2006Oracle Data Guard is not available in this edition of Oracle.Fri Sep 01 15:56:39 2006CREATE DATABASE xeUSER SYS IDENTIFIED BY *****USER SYSTEM IDENTIFIED BY *****LOGFILEGROUP 1 ('D:/oraclexe/oradata/xe/redo01.log') SIZE 40M,GROUP 2 ('D:/oraclexe/oradata/xe/redo02.log') SIZE 40M,GROUP 3 ('D:/oraclexe/oradata/xe/redo03.log') SIZE 40MMAXLOGFILES 5MAXLOGMEMBERS 5MAXLOGHISTORY 1MAXDATAFILES 100MAXINSTANCES 1CHARACTER SET ZHS16GBKNATIONAL CHARACTER SET AL16UTF16DATAFILE 'D:/oraclexe/oradata/xe/system01.dbf' SIZE 325M REUSEEXTENT MANAGEMENT LOCALSYSAUX DATAFILE 'D:/oraclexe/oradata/xe/sysaux01.dbf' SIZE 325M REUSEDEFAULT TABLESPACE tbs_1DATAFILE 'D:/oraclexe/oradata/xe/tbs_1.dbf' SIZE 50MEXTENT MANAGEMENT LOCALSEGMENT SPACE MANAGEMENT AUTODEFAULT TEMPORARY TABLESPACE tempts1TEMPFILE 'D:/oraclexe/oradata/xe/temp01.dbf' SIZE 20MUNDO TABLESPACE undotbsDATAFILE 'D:/oraclexe/oradata/xe/undotbs01.dbf' SIZE 100MFri Sep 01 15:56:42 2006?# 必须进入Exclusive模式。Database mounted in Exclusive ModeFri Sep 01 15:57:30 2006?#首先创建Redo。Successful mount of redo thread 1, with mount id 2481107959Assigning activation ID 2481107959 (0x93e2b3f7)Thread 1 opened at log sequence 1Current log# 1 seq# 1 mem# 0: D:\ORACLEXE\ORADATA\XE\REDO01.LOGSuccessful open of redo thread 1Fri Sep 01 15:57:31 2006SMON: enabling cache recoveryFri Sep 01 15:57:31 2006?#接着创建system 表空间。create tablespace SYSTEM datafile 'D:/oraclexe/oradata/xe/system01.dbf' SIZE 325M REUSE?EXTENT MANAGEMENT LOCAL online?Fri Sep 01 15:59:35 2006Completed: create tablespace SYSTEM datafile 'D:/oraclexe/oradata/xe/system01.dbf' SIZE 325M REUSE?EXTENT MANAGEMENT LOCAL onlineFri Sep 01 15:59:35 2006create rollback segment SYSTEM tablespace SYSTEMstorage (initial 50K next 50K)?Completed: create rollback segment SYSTEM tablespace SYSTEMstorage (initial 50K next 50K)Fri Sep 01 16:00:26 2006?#再创建undo 表空间，并使用该undo表空间。#如果这个与参数文件中的不符，将导致实例异常终止。CREATE UNDO TABLESPACE UNDOTBS DATAFILE 'D:/oraclexe/oradata/xe/undotbs01.dbf' SIZE 100M?Fri Sep 01 16:01:06 2006Successfully onlined Undo Tablespace 1.Completed: CREATE UNDO TABLESPACE UNDOTBS DATAFILE 'D:/oraclexe/oradata/xe/undotbs01.dbf' SIZE 100MFri Sep 01 16:01:06 2006create tablespace SYSAUX datafile 'D:/oraclexe/oradata/xe/sysaux01.dbf' SIZE 325M REUSE?EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO online?Fri Sep 01 16:03:09 2006?#创建sysaux 表空间。Completed: create tablespace SYSAUX datafile 'D:/oraclexe/oradata/xe/sysaux01.dbf' SIZE 325M REUSE?EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO onlineFri Sep 01 16:03:14 2006?#创建临时表空间，并激活该临时表空间。CREATE TEMPORARY TABLESPACE TEMPTS1 TEMPFILE 'D:/oraclexe/oradata/xe/temp01.dbf' SIZE 20M?Completed: CREATE TEMPORARY TABLESPACE TEMPTS1 TEMPFILE 'D:/oraclexe/oradata/xe/temp01.dbf' SIZE 20M?Fri Sep 01 16:03:15 2006ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMPTS1?Completed: ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMPTS1Fri Sep 01 16:03:15 2006CREATE TABLESPACE TBS_1 DATAFILE 'D:/oraclexe/oradata/xe/tbs_1.dbf' SIZE 50MSEGMENT SPACE MANAGEMENT AUTO?Fri Sep 01 16:03:35 2006?#最后一步才是创建用户表空间。Completed: CREATE TABLESPACE TBS_1 DATAFILE 'D:/oraclexe/oradata/xe/tbs_1.dbf' SIZE 50MSEGMENT SPACE MANAGEMENT AUTOFri Sep 01 16:03:35 2006ALTER DATABASE DEFAULT TABLESPACE TBS_1?Completed: ALTER DATABASE DEFAULT TABLESPACE TBS_1Fri Sep 01 16:03:57 2006SMON: enabling tx recoveryFri Sep 01 16:04:05 2006Threshold validation cannot be done before catproc is loaded.replication_dependency_tracking turned off (no async multimaster replication found)Starting background process QMNCQMNC started with pid=13, OS id=1092Fri Sep 01 16:04:07 2006Completed: CREATE DATABASE xeUSER SYS IDENTIFIED BY *****USER SYSTEM IDENTIFIED BY *****LOGFILEGROUP 1 ('D:/oraclexe/oradata/xe/redo01.log') SIZE 40M,GROUP 2 ('D:/oraclexe/oradata/xe/redo02.log') SIZE 40M,GROUP 3 ('D:/oraclexe/oradata/xe/redo03.log') SIZE 40MMAXLOGFILES 5MAXLOGMEMBERS 5MAXLOGHISTORY 1MAXDATAFILES 100MAXINSTANCES 1CHARACTER SET ZHS16GBKNATIONAL CHARACTER SET AL16UTF16DATAFILE 'D:/oraclexe/oradata/xe/system01.dbf' SIZE 325M REUSEEXTENT MANAGEMENT LOCALSYSAUX DATAFILE 'D:/oraclexe/oradata/xe/sysaux01.dbf' SIZE 325M REUSEDEFAULT TABLESPACE tbs_1DATAFILE 'D:/oraclexe/oradata/xe/tbs_1.dbf' SIZE 50MEXTENT MANAGEMENT LOCALSEGMENT SPACE MANAGEMENT AUTODEFAULT TEMPORARY TABLESPACE tempts1TEMPFILE 'D:/oraclexe/oradata/xe/temp01.dbf' SIZE 20MUNDO TABLESPACE undotbsDATAFILE 'D:/oraclexe/oradata/xe/undotbs01.dbf' SIZE 100MFri Sep 01 16:04:07 2006db_recovery_file_dest_size of 1024 MB is 0.00% used. This is auser-specified limit on the amount of space that will be used by thisdatabase for recovery-related files, and does not reflect the amount ofspace available in the underlying filesystem or ASM diskgroup.

至此，一个新的数据库创建完毕。
在该过程中，最好添加用户变量NLS_LANG为AMERICAN_AMERICA.ZHS16GBK，否则有可能出现ORA提示乱码。

Note:
手工创建数据库的话还必须运行catalog.sql,catproc.sql，否则会出现ora-06553错误。

附pfile.ora:

?12345678910111213141516*.audit_file_dest='D:\oraclexe\app\oracle\admin\XE\adump'*.background_dump_dest='D:\oraclexe\app\oracle\admin\XE\bdump'*.compatible='10.2.0.1.0'*.control_files='D:\oraclexe\oradata\XE\control.dbf'*.core_dump_dest='D:\oraclexe\app\oracle\admin\XE\cdump'*.db_name='XE'*.DB_RECOVERY_FILE_DEST_SIZE=1G*.DB_RECOVERY_FILE_DEST='D:\oraclexe\app\oracle\flash_recovery_area'*.open_cursors=100*.pga_aggregate_target=16M*.remote_login_passwordfile='EXCLUSIVE'*.sessions=20*.sga_target=140M*.undo_management='AUTO'*.undo_tablespace='undotbs'*.user_dump_dest='D:\oraclexe\app\oracle\admin\XE\udump'

附createdb_xe.sql:

?123456789101112131415161718192021222324CREATE DATABASE xeUSER SYS IDENTIFIED BY installUSER SYSTEM IDENTIFIED BY installLOGFILEGROUP 1 ('D:/oraclexe/oradata/xe/redo01.log') SIZE 40M,GROUP 2 ('D:/oraclexe/oradata/xe/redo02.log') SIZE 40M,GROUP 3 ('D:/oraclexe/oradata/xe/redo03.log') SIZE 40MMAXLOGFILES 5MAXLOGMEMBERS 5MAXLOGHISTORY 1MAXDATAFILES 100MAXINSTANCES 1CHARACTER SET ZHS16GBKNATIONAL CHARACTER SET AL16UTF16DATAFILE 'D:/oraclexe/oradata/xe/system01.dbf' SIZE 325M REUSEEXTENT MANAGEMENT LOCALSYSAUX DATAFILE 'D:/oraclexe/oradata/xe/sysaux01.dbf' SIZE 325M REUSEDEFAULT TABLESPACE tbs_1DATAFILE 'D:/oraclexe/oradata/xe/tbs_1.dbf' SIZE 50MEXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTODEFAULT TEMPORARY TABLESPACE tempts1TEMPFILE 'D:/oraclexe/oradata/xe/temp01.dbf' SIZE 20MUNDO TABLESPACE undotbsDATAFILE 'D:/oraclexe/oradata/xe/undotbs01.dbf' SIZE 100M;