昨日に引き続きまたしてもOracle 10g Express Edition のお話。
OREATE DATABASE が動かないのなんでかな〜と調べていたら、どうも
default tablespace USERS
としてデフォルト表領域を指定しているのに実体となるデータファイルを指定していなかったからでした。手本にした資料が、OMF（Oracle Managed Files）を利用していたから書いていなかったのですね。自分はあまりOMFは使わないので…。ファイル名指定したいしね。
と言う事で無事サンプルデータベースを作成する事ができました。
流したSQL文は以下の通り（アイドルインスタンスに接続して実行・途中で初期化パラメータファイルに追加書き込みしてます）。
shutdown abort
connect sys/password as SYSDBA
host echo startup oracle (nomount)
startup nomount pfile=C:/\/ioraclexe/\/iapp/\/ioracle/\/iproduct/\/i10.2.0/\/iserver/\/iDatabase/\/iinitORCL.ora
host echo spool start
spool create_db.log
host echo create database
create database ORCL
user sys identified by password
user system identified by password
logfile
'C:/\/ioraclexe/\/ioradata/\/iORCL/\/iORCL_log1.log' size 50M reuse
, 'C:/\/ioraclexe/\/ioradata/\/iORCL/\/iORCL_log2.log' size 50M reuse
, 'C:/\/ioraclexe/\/ioradata/\/iORCL/\/iORCL_log3.log' size 50M reuse
maxinstances 1
maxlogfiles 16
maxlogmembers 5
maxdatafiles 100
datafile 'C:/\/ioraclexe/\/ioradata/\/iORCL/\/iORCL_SYS_1.dbf' size 250M reuse
autoextend on next 10M maxsize 500M extent management local
sysaux datafile 'C:/\/ioraclexe/\/ioradata/\/iORCL/\/iORCL_SYSAUX_1.dbf' size 100M reuse
autoextend on next 10M maxsize unlimited
default tablespace USERS
datafile 'C:/\/ioraclexe/\/ioradata/\/iORCL/\/iORCL_USERS01.dbf' size 100M reuse
autoextend on next 10M maxsize unlimited
default temporary tablespace TEMP tempfile 'C:/\/ioraclexe/\/ioradata/\/iORCL/\/iORCL_TEMP_1.dbf' size 50M reuse
undo tablespace UNDOTBS01 datafile 'C:/\/ioraclexe/\/ioradata/\/iORCL/\/iORCL_undotbs_1.dbf' size 200M reuse autoextend on maxsize unlimited
archivelog
character set JA16SJIS
;
host echo ########################################### >> C:/\/ioraclexe/\/iapp/\/ioracle/\/iproduct/\/i10.2.0/\/iserver/\/iDatabase/\/iinitORCL.ora
host echo # MTS >> C:/\/ioraclexe/\/iapp/\/ioracle/\/iproduct/\/i10.2.0/\/iserver/\/iDatabase/\/iinitORCL.ora
host echo ########################################### >> C:/\/ioraclexe/\/iapp/\/ioracle/\/iproduct/\/i10.2.0/\/iserver/\/iDatabase/\/iinitORCL.ora
host echo dispatchers="(PROTOCOL=TCP)" >> C:/\/ioraclexe/\/iapp/\/ioracle/\/iproduct/\/i10.2.0/\/iserver/\/iDatabase/\/iinitORCL.ora
host echo shared_servers=4 >> C:/\/ioraclexe/\/iapp/\/ioracle/\/iproduct/\/i10.2.0/\/iserver/\/iDatabase/\/iinitORCL.ora
create tablespace USERIDX
datafile 'C:/\/ioraclexe/\/ioradata/\/iORCL/\/iORCL_IDX01.dbf' size 20M reuse
autoextend on next 10M maxsize unlimited;
@%ORACLE_HOME%/\/irdbms/\/iadmin/\/icatalog.sql
@%ORACLE_HOME%/\/irdbms/\/iadmin/\/icatproc.sql
connect system/password
@%ORACLE_HOME%/\/irdbms/\/iadmin/\/icatdbsyn.sql
@%ORACLE_HOME%/\/isqlplus/\/iadmin/\/ipupbld.sql
@%ORACLE_HOME%/\/irdbms/\/iadmin/\/iutlxplan.sql
grant select on SYSTEM.PLAN_TABLE to public;
grant insert on SYSTEM.PLAN_TABLE to public;
grant update on SYSTEM.PLAN_TABLE to public;
grant delete on SYSTEM.PLAN_TABLE to public;
spool off
quit
初期化パラメータファイルは次のような感じ（まだちょっとテキトウ・どっかからパクって来たものをベースに切り張り）
###########################################
# Oracle database initialize file
###########################################
###########################################
# Database and Instance Identification
###########################################
db_name=ORCL
db_domain=localdomain
instance_name=ORCL
###########################################
# Redo Log and Recovery
###########################################
fast_start_mttr_target=300
###########################################
# Miscellaneous
###########################################
compatible=10.2.0.1.0
###########################################
# Optimizer
###########################################
query_rewrite_enabled=FALSE
query_rewrite_integrity=ENFORCED
star_transformation_enabled=TRUE
###########################################
# Cursors and Library Cache
###########################################
open_cursors=300
###########################################
# Cache and I/O
###########################################
db_block_size=8192
db_file_multiblock_read_count=4
###########################################
# Memory Managed Undo and Rollback Segments
###########################################
sga_target=200M
sga_max_size=200M
pga_aggregate_target=10M
sort_area_size=524288
###########################################
# Undo Managed
###########################################
undo_management=AUTO
undo_retention=3600
undo_tablespace=UNDOTBS01
###########################################
# Job Queues
###########################################
job_queue_processes=10
###########################################
# Security and Auditing
###########################################
remote_login_passwordfile=EXCLUSIVE
###########################################
# File Configuration
###########################################
control_files=("C:/\/ioraclexe/\/ioradata/\/iORCL/\/iORCL_CONTROL01.CTL")
###########################################
# Processes and Sessions
###########################################
processes=150
###########################################
# Diagnostics and Statistics
###########################################
background_dump_dest=C:/\/ioraclexe/\/iapp/\/ioracle/\/iproduct/\/i10.2.0/\/iserver/\/iadmin/\/iORCL/\/ibdump
core_dump_dest=C:/\/ioraclexe/\/iapp/\/ioracle/\/iproduct/\/i10.2.0/\/iserver/\/iadmin/\/iORCL/\/icdump
timed_statistics=FALSE
user_dump_dest=C:/\/ioraclexe/\/iapp/\/ioracle/\/iproduct/\/i10.2.0/\/iserver/\/iadmin/\/iORCL/\/iudump
###########################################
# Archive log mode
###########################################
log_archive_dest=C:/\/ioraclexe/\/ioradata/\/iORCL/\/iArchive
log_archive_format="ORCL_T%tS%sR%r.arc"
とりあえず覚え書きとして。
OREATE DATABASE が動かないのなんでかな〜と調べていたら、どうも
default tablespace USERS
としてデフォルト表領域を指定しているのに実体となるデータファイルを指定していなかったからでした。手本にした資料が、OMF（Oracle Managed Files）を利用していたから書いていなかったのですね。自分はあまりOMFは使わないので…。ファイル名指定したいしね。
と言う事で無事サンプルデータベースを作成する事ができました。
流したSQL文は以下の通り（アイドルインスタンスに接続して実行・途中で初期化パラメータファイルに追加書き込みしてます）。
shutdown abort
connect sys/password as SYSDBA
host echo startup oracle (nomount)
startup nomount pfile=C:/\/ioraclexe/\/iapp/\/ioracle/\/iproduct/\/i10.2.0/\/iserver/\/iDatabase/\/iinitORCL.ora
host echo spool start
spool create_db.log
host echo create database
create database ORCL
user sys identified by password
user system identified by password
logfile
'C:/\/ioraclexe/\/ioradata/\/iORCL/\/iORCL_log1.log' size 50M reuse
, 'C:/\/ioraclexe/\/ioradata/\/iORCL/\/iORCL_log2.log' size 50M reuse
, 'C:/\/ioraclexe/\/ioradata/\/iORCL/\/iORCL_log3.log' size 50M reuse
maxinstances 1
maxlogfiles 16
maxlogmembers 5
maxdatafiles 100
datafile 'C:/\/ioraclexe/\/ioradata/\/iORCL/\/iORCL_SYS_1.dbf' size 250M reuse
autoextend on next 10M maxsize 500M extent management local
sysaux datafile 'C:/\/ioraclexe/\/ioradata/\/iORCL/\/iORCL_SYSAUX_1.dbf' size 100M reuse
autoextend on next 10M maxsize unlimited
default tablespace USERS
datafile 'C:/\/ioraclexe/\/ioradata/\/iORCL/\/iORCL_USERS01.dbf' size 100M reuse
autoextend on next 10M maxsize unlimited
default temporary tablespace TEMP tempfile 'C:/\/ioraclexe/\/ioradata/\/iORCL/\/iORCL_TEMP_1.dbf' size 50M reuse
undo tablespace UNDOTBS01 datafile 'C:/\/ioraclexe/\/ioradata/\/iORCL/\/iORCL_undotbs_1.dbf' size 200M reuse autoextend on maxsize unlimited
archivelog
character set JA16SJIS
;
host echo ########################################### >> C:/\/ioraclexe/\/iapp/\/ioracle/\/iproduct/\/i10.2.0/\/iserver/\/iDatabase/\/iinitORCL.ora
host echo # MTS >> C:/\/ioraclexe/\/iapp/\/ioracle/\/iproduct/\/i10.2.0/\/iserver/\/iDatabase/\/iinitORCL.ora
host echo ########################################### >> C:/\/ioraclexe/\/iapp/\/ioracle/\/iproduct/\/i10.2.0/\/iserver/\/iDatabase/\/iinitORCL.ora
host echo dispatchers="(PROTOCOL=TCP)" >> C:/\/ioraclexe/\/iapp/\/ioracle/\/iproduct/\/i10.2.0/\/iserver/\/iDatabase/\/iinitORCL.ora
host echo shared_servers=4 >> C:/\/ioraclexe/\/iapp/\/ioracle/\/iproduct/\/i10.2.0/\/iserver/\/iDatabase/\/iinitORCL.ora
create tablespace USERIDX
datafile 'C:/\/ioraclexe/\/ioradata/\/iORCL/\/iORCL_IDX01.dbf' size 20M reuse
autoextend on next 10M maxsize unlimited;
@%ORACLE_HOME%/\/irdbms/\/iadmin/\/icatalog.sql
@%ORACLE_HOME%/\/irdbms/\/iadmin/\/icatproc.sql
connect system/password
@%ORACLE_HOME%/\/irdbms/\/iadmin/\/icatdbsyn.sql
@%ORACLE_HOME%/\/isqlplus/\/iadmin/\/ipupbld.sql
@%ORACLE_HOME%/\/irdbms/\/iadmin/\/iutlxplan.sql
grant select on SYSTEM.PLAN_TABLE to public;
grant insert on SYSTEM.PLAN_TABLE to public;
grant update on SYSTEM.PLAN_TABLE to public;
grant delete on SYSTEM.PLAN_TABLE to public;
spool off
quit
初期化パラメータファイルは次のような感じ（まだちょっとテキトウ・どっかからパクって来たものをベースに切り張り）
###########################################
# Oracle database initialize file
###########################################
###########################################
# Database and Instance Identification
###########################################
db_name=ORCL
db_domain=localdomain
instance_name=ORCL
###########################################
# Redo Log and Recovery
###########################################
fast_start_mttr_target=300
###########################################
# Miscellaneous
###########################################
compatible=10.2.0.1.0
###########################################
# Optimizer
###########################################
query_rewrite_enabled=FALSE
query_rewrite_integrity=ENFORCED
star_transformation_enabled=TRUE
###########################################
# Cursors and Library Cache
###########################################
open_cursors=300
###########################################
# Cache and I/O
###########################################
db_block_size=8192
db_file_multiblock_read_count=4
###########################################
# Memory Managed Undo and Rollback Segments
###########################################
sga_target=200M
sga_max_size=200M
pga_aggregate_target=10M
sort_area_size=524288
###########################################
# Undo Managed
###########################################
undo_management=AUTO
undo_retention=3600
undo_tablespace=UNDOTBS01
###########################################
# Job Queues
###########################################
job_queue_processes=10
###########################################
# Security and Auditing
###########################################
remote_login_passwordfile=EXCLUSIVE
###########################################
# File Configuration
###########################################
control_files=("C:/\/ioraclexe/\/ioradata/\/iORCL/\/iORCL_CONTROL01.CTL")
###########################################
# Processes and Sessions
###########################################
processes=150
###########################################
# Diagnostics and Statistics
###########################################
background_dump_dest=C:/\/ioraclexe/\/iapp/\/ioracle/\/iproduct/\/i10.2.0/\/iserver/\/iadmin/\/iORCL/\/ibdump
core_dump_dest=C:/\/ioraclexe/\/iapp/\/ioracle/\/iproduct/\/i10.2.0/\/iserver/\/iadmin/\/iORCL/\/icdump
timed_statistics=FALSE
user_dump_dest=C:/\/ioraclexe/\/iapp/\/ioracle/\/iproduct/\/i10.2.0/\/iserver/\/iadmin/\/iORCL/\/iudump
###########################################
# Archive log mode
###########################################
log_archive_dest=C:/\/ioraclexe/\/ioradata/\/iORCL/\/iArchive
log_archive_format="ORCL_T%tS%sR%r.arc"
とりあえず覚え書きとして。
