Oracle XE的資料庫建立過程

yangzibin發表於2010-12-02
Oracle資料庫

今天安裝了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;

相關文章