Oracle XE的数据库创建过程

Oracle September 1st, 2006

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

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

1. 创建SPFILE。

SQL*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 sysdba
Connected.

SQL> create spfile from pfile='c:\pfile.ora';

File created.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 146800640 bytes
Fixed Size 1286220 bytes
Variable Size 58724276 bytes
Database Buffers 83886080 bytes
Redo Buffers 2904064 bytes

2. 创建数据库。

SQL> @C:\createdb_xe.sql

Database created.

需要注意的是，default undo tablespace必须与参数文件中的一致，否则会在创建中途导致实例异常终止。关于原因在第三步中分析。3. 后台过程。
在创建过程中可以用记事本等文本编辑工具查看alert_xe.log文件，推荐使用PSPad，可以及时查看更新信息。

代码如下：

Starting up ORACLE RDBMS Version: 10.2.0.1.0.
System parameters with non-default values:
sessions = 49
sga_target = 146800640
control_files = D:\ORACLEXE\ORADATA\XE\CONTROL.DBF
compatible = 10.2.0.1.0
db_recovery_file_dest = D:\oraclexe\app\oracle\flash_recovery_area
db_recovery_file_dest_size= 1073741824
undo_management = AUTO
undo_tablespace = undotbs
remote_login_passwordfile= EXCLUSIVE
audit_file_dest = D:\ORACLEXE\APP\ORACLE\ADMIN\XE\ADUMP
background_dump_dest = D:\ORACLEXE\APP\ORACLE\ADMIN\XE\BDUMP
user_dump_dest = D:\ORACLEXE\APP\ORACLE\ADMIN\XE\UDUMP
core_dump_dest = D:\ORACLEXE\APP\ORACLE\ADMIN\XE\CDUMP
db_name = XE
open_cursors = 100
pga_aggregate_target = 16777216
PMON started with pid=2, OS id=1008
PSP0 started with pid=3, OS id=416
MMAN started with pid=4, OS id=332
DBW0 started with pid=5, OS id=1088
LGWR started with pid=6, OS id=712
CKPT started with pid=7, OS id=1028
SMON started with pid=8, OS id=412
RECO started with pid=9, OS id=1144
MMON started with pid=10, OS id=928
MMNL started with pid=11, OS id=468
Fri Sep 01 15:55:08 2006
Oracle Data Guard is not available in this edition of Oracle.
Fri Sep 01 15:56:39 2006
CREATE DATABASE xe
USER SYS IDENTIFIED BY *****USER SYSTEM IDENTIFIED BY *****LOGFILE
GROUP 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 40M
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 100
MAXINSTANCES 1
CHARACTER SET ZHS16GBK
NATIONAL CHARACTER SET AL16UTF16
DATAFILE ‘D:/oraclexe/oradata/xe/system01.dbf’ SIZE 325M REUSE
EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE ‘D:/oraclexe/oradata/xe/sysaux01.dbf’ SIZE 325M REUSE
DEFAULT TABLESPACE tbs_1
DATAFILE ‘D:/oraclexe/oradata/xe/tbs_1.dbf’ SIZE 50M
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO
DEFAULT TEMPORARY TABLESPACE tempts1
TEMPFILE ‘D:/oraclexe/oradata/xe/temp01.dbf’ SIZE 20M
UNDO TABLESPACE undotbs
DATAFILE ‘D:/oraclexe/oradata/xe/undotbs01.dbf’ SIZE 100M
Fri Sep 01 15:56:42 2006

# 必须进入Exclusive模式。
Database mounted in Exclusive Mode
Fri Sep 01 15:57:30 2006

#首先创建Redo。
Successful mount of redo thread 1, with mount id 2481107959
Assigning activation ID 2481107959 (0×93e2b3f7)
Thread 1 opened at log sequence 1
Current log# 1 seq# 1 mem# 0: D:\ORACLEXE\ORADATA\XE\REDO01.LOG
Successful open of redo thread 1
Fri Sep 01 15:57:31 2006
SMON: enabling cache recovery
Fri 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 2006
Completed: create tablespace SYSTEM datafile ‘D:/oraclexe/oradata/xe/system01.dbf’ SIZE 325M REUSE

EXTENT MANAGEMENT LOCAL online
Fri Sep 01 15:59:35 2006
create rollback segment SYSTEM tablespace SYSTEM
storage (initial 50K next 50K)

Completed: create rollback segment SYSTEM tablespace SYSTEM
storage (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 2006
Successfully onlined Undo Tablespace 1.
Completed: CREATE UNDO TABLESPACE UNDOTBS DATAFILE ‘D:/oraclexe/oradata/xe/undotbs01.dbf’ SIZE 100M
Fri Sep 01 16:01:06 2006
create 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 online
Fri 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 2006
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMPTS1

Completed: ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMPTS1
Fri Sep 01 16:03:15 2006
CREATE TABLESPACE TBS_1 DATAFILE ‘D:/oraclexe/oradata/xe/tbs_1.dbf’ SIZE 50M
SEGMENT SPACE MANAGEMENT AUTO

Fri Sep 01 16:03:35 2006

#最后一步才是创建用户表空间。
Completed: CREATE TABLESPACE TBS_1 DATAFILE ‘D:/oraclexe/oradata/xe/tbs_1.dbf’ SIZE 50M
SEGMENT SPACE MANAGEMENT AUTO
Fri Sep 01 16:03:35 2006
ALTER DATABASE DEFAULT TABLESPACE TBS_1

Completed: ALTER DATABASE DEFAULT TABLESPACE TBS_1
Fri Sep 01 16:03:57 2006
SMON: enabling tx recovery
Fri Sep 01 16:04:05 2006
Threshold validation cannot be done before catproc is loaded.
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
QMNC started with pid=13, OS id=1092
Fri Sep 01 16:04:07 2006
Completed: CREATE DATABASE xe
USER SYS IDENTIFIED BY *****USER SYSTEM IDENTIFIED BY *****LOGFILE
GROUP 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 40M
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 100
MAXINSTANCES 1
CHARACTER SET ZHS16GBK
NATIONAL CHARACTER SET AL16UTF16
DATAFILE ‘D:/oraclexe/oradata/xe/system01.dbf’ SIZE 325M REUSE
EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE ‘D:/oraclexe/oradata/xe/sysaux01.dbf’ SIZE 325M REUSE
DEFAULT TABLESPACE tbs_1
DATAFILE ‘D:/oraclexe/oradata/xe/tbs_1.dbf’ SIZE 50M
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO
DEFAULT TEMPORARY TABLESPACE tempts1
TEMPFILE ‘D:/oraclexe/oradata/xe/temp01.dbf’ SIZE 20M
UNDO TABLESPACE undotbs
DATAFILE ‘D:/oraclexe/oradata/xe/undotbs01.dbf’ SIZE 100M
Fri Sep 01 16:04:07 2006
db_recovery_file_dest_size of 1024 MB is 0.00% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.

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

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

附pfile.ora:

*.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:

CREATE DATABASE xe
USER SYS IDENTIFIED BY install
USER SYSTEM IDENTIFIED BY install
LOGFILE
GROUP 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 40M
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 100
MAXINSTANCES 1
CHARACTER SET ZHS16GBK
NATIONAL CHARACTER SET AL16UTF16
DATAFILE 'D:/oraclexe/oradata/xe/system01.dbf' SIZE 325M REUSE
EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE 'D:/oraclexe/oradata/xe/sysaux01.dbf' SIZE 325M REUSE
DEFAULT TABLESPACE tbs_1
DATAFILE 'D:/oraclexe/oradata/xe/tbs_1.dbf' SIZE 50M
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO
DEFAULT TEMPORARY TABLESPACE tempts1
TEMPFILE 'D:/oraclexe/oradata/xe/temp01.dbf' SIZE 20M
UNDO TABLESPACE undotbs
DATAFILE 'D:/oraclexe/oradata/xe/undotbs01.dbf' SIZE 100M;

2 Responses to “Oracle XE的数据库创建过程”

  1. l1t Says:
    April 27th, 2007 at 12:19 pm

    请问这样创建的数据库可以用xe的web图形界面管理吗

  2. Zeeno Says:
    April 27th, 2007 at 12:25 pm

    我没用过WEB图形界面，不好说。
    既然Oracle使用Apache，估计还需要进行一些配置才能使用。

Leave a Reply