Oracle 9i 資料庫建立手冊

主要的環境變數：
ORACLE_HOME: /oracle/app/oracle/product/9.2.0
ORACLE_SID: ora9i
NLS_LANG: American_America.zhs16gbk

一、 建立前準備工作
準備好initora9i.ora檔案
#################################################################
Copyright (c) 1991, 2001, 2002 by Oracle Corporation
################################################################

###########################################
# Cache and I/O
###########################################
db_block_size=8192
db_cache_size=2147483648
db_file_multiblock_read_count=16
###########################################
# Cursors and Library Cache
###########################################
open_cursors=500
###########################################
# Database Identification
###########################################
db_domain=""
db_name=ora9i
###########################################
# Instance Identification
###########################################
instance_name=ora9i
###########################################
# Diagnostics and Statistics
###########################################
background_dump_dest=/oracle/app/oracle/admin/ora9i/bdump
core_dump_dest=/oracle/app/oracle/admin/ora9i/cdump
timed_statistics=TRUE
user_dump_dest=/oracle/app/oracle/admin/ora9i/udump
###########################################
# File Configuration
###########################################
control_files=("/dev/vg_ora1/rctrl_128m_01", "/dev/vg_ora2/rctrl_128m_02", "/dev/vg_ora3/rctrl_128m_03")
###########################################
# Job Queues
###########################################
job_queue_processes=10
###########################################
# Miscellaneous
###########################################
aq_tm_processes=1
compatible=9.2.0.0.0
###########################################
# Optimizer
###########################################
hash_join_enabled=TRUE
query_rewrite_enabled=FALSE
star_transformation_enabled=FALSE
###########################################
# Pools
###########################################
java_pool_size=0
large_pool_size=104857600
shared_pool_size=524288000
###########################################
# Processes and Sessions
###########################################
processes=1000
###########################################
# Redo Log and Recovery
###########################################
fast_start_mttr_target=300
###########################################
# Security and Auditing
###########################################
remote_login_passwordfile=exclusive
###########################################
# Sort, Hash Joins, Bitmap Indexes
###########################################
pga_aggregate_target=943718400
sort_area_size=524288
###########################################
# System Managed Undo and Rollback Segments
###########################################
undo_management=AUTO
undo_retention=108000
undo_tablespace=UNDOTBS1

此檔案應複製到$ORACLE_HOME/dbs/下

建立相應的目錄
bdump: /oracle/app/oracle/admin/ora9i/bdump
udump: /oracle/app/oracle/admin/ora9i/udump
cdump: /oracle/app/oracle/admin/ora9i/cdump

建立密碼檔案
$orapwd file=$ORACLE_HOME/dbs/orapwora9i.ora
password=change_on_install

建立資料庫所需要的邏輯卷
lvcreate -L 128 -n ctrl_128m_01 vg_ora1
lvcreate -L 128 -n ctrl_128m_02 vg_ora2
lvcreate -L 128 -n ctrl_128m_03 vg_ora3
lvcreate -L 512 -n sys_512m_01 vg_ora1
lvcreate -L 256 -n indx_256m_01 vg_ora2
lvcreate -L 256 -n user_256m_01 vg_ora2
lvcreate -L 1024 -n temp_1g_01 vg_ora1
lvcreate -L 512 -n tools_512m_01 vg_ora3
lvcreate -L 128 -n spfile_128m_01 vg_ora1
lvcreate -L 256 -n redo_256m_01 vg_ora1
lvcreate -L 256 -n redo_256m_02 vg_ora1
lvcreate -L 256 -n redo_256m_03 vg_ora1
lvcreate -L 256 -n redo_256m_11 vg_ora2
lvcreate -L 256 -n redo_256m_12 vg_ora2
lvcreate -L 256 -n redo_256m_13 vg_ora2
lvcreate -L 256 -n redo_256m_21 vg_ora3
lvcreate -L 256 -n redo_256m_22 vg_ora3
lvcreate -L 256 -n redo_256m_23 vg_ora3
lvcreate -L 1024 -n data_1g_001 vg_ora1
lvcreate -L 1024 -n data_1g_002 vg_ora2
lvcreate -L 1024 -n data_1g_003 vg_ora3
lvcreate -L 1024 -n data_1g_004 vg_ora1
lvcreate -L 1024 -n data_1g_005 vg_ora2
lvcreate -L 1024 -n data_1g_006 vg_ora3
lvcreate -L 1024 -n data_1g_007 vg_ora1
lvcreate -L 1024 -n data_1g_008 vg_ora2
lvcreate -L 1024 -n data_1g_009 vg_ora3
lvcreate -L 1024 -n data_1g_010 vg_ora1
... ... ...
二、 建立資料庫
Create DB
$sqlplus /nolog
SQL> connect / as sysdba
SQL> spool /oracle/app/oracle/product/9.2.0/assistants/dbca/logs/CreateDB.log
SQL>startup nomount
pfile="/oracle/app/oracle/product/9.2.0/dbs/initora9i.ora";
SQL>CREATE DATABASE ora9i
CONTROLFILE REUSE
MAXINSTANCES 32
MAXLOGHISTORY 0
MAXLOGFILES 192
MAXLOGMEMBERS 3
MAXDATAFILES 2048
DATAFILE '/dev/vg_ora1/rsys_512m_01' SIZE 511M REUSE
EXTENT MANAGEMENT LOCAL
DEFAULT TEMPORARY TABLESPACE TEMP
TEMPFILE '/dev/vg_ora1/rtemp_1g_01' SIZE 1023M REUSE
UNDO TABLESPACE "UNDOTBS1"
DATAFILE '/dev/vg_ora1/rdata_1g_001' SIZE 1023M REUSE
CHARACTER SET ZHS16GBK
NATIONAL CHARACTER SET AL16UTF16
LOGFILE
GROUP 1
('/dev/vg_ora1/rredo_256m_01',
'/dev/vg_ora2/rredo_256m_11',
'/dev/vg_ora3/rredo_256m_21') SIZE 255M REUSE,
GROUP 2
('/dev/vg_ora1/rredo_256m_02',
'/dev/vg_ora2/rredo_256m_12',
'/dev/vg_ora3/rredo_256m_22') SIZE 255M REUSE,
GROUP 3
('/dev/vg_ora1/rredo_256m_03',
'/dev/vg_ora2/rredo_256m_13',
'/dev/vg_ora3/rredo_256m_23') SIZE 255M REUSE;

SQL>spool off

建立系統字典表和包
SQL>connect SYS/change_on_install as SYSDBA
SQL>spool /oracle/app/oracle/product/9.2.0/assistants/dbca/logs/CreateDBCatalog.log
SQL>@/oracle/app/oracle/product/9.2.0//rdbms/admin/catalog.sql;
SQL>@/oracle/app/oracle/product/9.2.0//rdbms/admin/catexp7.sql;
SQL>@/oracle/app/oracle/product/9.2.0//rdbms/admin/catblock.sql;
SQL>@/oracle/app/oracle/product/9.2.0/rdbms/admin/catproc.sql;
SQL>@/oracle/app/oracle/product/9.2.0/rdbms/admin/catoctk.sql;
SQL>@/oracle/app/oracle/product/9.2.0/rdbms/admin/owminst.plb;
SQL>spool off

建立一些第三方軟體可能會使用到的表空間
SQL>CREATE TABLESPACE "INDX" LOGGING DATAFILE '/dev/vg_ora2/rindx_256m_01' SIZE 255M REUSE EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;
SQL>CREATE TABLESPACE "TOOLS" LOGGING DATAFILE '/dev/vg_ora3/rtools_512m_01' SIZE 511M REUSE EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;
SQL>CREATE TABLESPACE "USERS" LOGGING DATAFILE '/dev/vg_ora2/ruser_256m_01' SIZE 255M REUSE EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;

[@more@]