Oracle XE的数据库创建过程

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

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

1. 创建SPFILE。

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

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. 创建数据库。

?

1 2 3

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

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

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

代码如下：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165

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 (0x93e2b3f7) 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:

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

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

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

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;