Oracle 11g 手工建立資料庫完整腳本流程 (非 DBCA，含參數檔、目錄、密碼檔、字典建立)

Oracle 11g 手工建立資料庫完整腳本流程

適用於無圖形介面、自動化部署、精細控制儲存佈局、教學驗證等場景。以下以 SID=KEVIN 為例。

1. 準備初始化參數檔 (PFILE)

# $ORACLE_HOME/dbs/initkevin.ora
db_name='kevin'
memory_target=0
sga_max_size=5G
sga_target=3G
pga_aggregate_target=1G
processes=150
audit_file_dest='/u01/app/oracle/admin/kevin/adump'
audit_trail='db'
db_block_size=8192
db_domain=''
db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
db_recovery_file_dest_size=2G
diagnostic_dest='/u01/app/oracle'
dispatchers='(PROTOCOL=TCP) (SERVICE=KEVINXDB)'
open_cursors=300
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS'
control_files=('/u01/app/oracle/oradata/kevin/disk1/control01.ctl',
               '/u01/app/oracle/oradata/kevin/disk2/control02.ctl')
compatible='11.2.0'

2. 建立必要目錄結構

mkdir -p /u01/app/oracle/admin/kevin/adump
mkdir -p /u01/app/oracle/flash_recovery_area
mkdir -p /u01/app/oracle/oradata/kevin/disk1
mkdir -p /u01/app/oracle/oradata/kevin/disk2
mkdir -p /u01/app/oracle/oradata/kevin/disk3

3. 設定環境變數並建立密碼檔

export ORACLE_SID=kevin
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH

# 建立密碼檔 (允許 SYSDBA 遠程登入)
orapwd file=$ORACLE_HOME/dbs/orapwkevin password=oracle entries=5 force=y

4. 啟動 NOMOUNT 階段

sqlplus / as sysdba
SQL> STARTUP NOMOUNT PFILE=$ORACLE_HOME/dbs/initkevin.ora
ORACLE instance started.
Total System Global Area 5344731136 bytes
...

5. 建立 SPFILE (持久化參數)

SQL> CREATE SPFILE FROM PFILE;
File created.

6. 執行 CREATE DATABASE 命令

CREATE DATABASE kevin
USER SYS IDENTIFIED BY oracle
USER SYSTEM IDENTIFIED BY oracle
LOGFILE
  GROUP 1 ('/u01/app/oracle/oradata/kevin/disk1/redo01a.log',
           '/u01/app/oracle/oradata/kevin/disk2/redo01b.log') SIZE 100M BLOCKSIZE 512,
  GROUP 2 ('/u01/app/oracle/oradata/kevin/disk1/redo02a.log',
           '/u01/app/oracle/oradata/kevin/disk2/redo02b.log') SIZE 100M BLOCKSIZE 512,
  GROUP 3 ('/u01/app/oracle/oradata/kevin/disk1/redo03a.log',
           '/u01/app/oracle/oradata/kevin/disk2/redo03b.log') SIZE 100M BLOCKSIZE 512
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 100
CHARACTER SET AL32UTF8
NATIONAL CHARACTER SET AL16UTF16
EXTENT MANAGEMENT LOCAL
DATAFILE '/u01/app/oracle/oradata/kevin/disk1/system01.dbf' SIZE 325M REUSE
SYSAUX DATAFILE '/u01/app/oracle/oradata/kevin/disk2/sysaux01.dbf' SIZE 325M REUSE
DEFAULT TABLESPACE users
  DATAFILE '/u01/app/oracle/oradata/kevin/disk1/users01.dbf' SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
DEFAULT TEMPORARY TABLESPACE tempts1
  TEMPFILE '/u01/app/oracle/oradata/kevin/disk2/temp01.dbf' SIZE 20M REUSE
UNDO TABLESPACE undotbs
  DATAFILE '/u01/app/oracle/oradata/kevin/disk3/undotbs01.dbf' SIZE 200M REUSE AUTOEXTEND ON MAXSIZE 1024M;

7. 建立資料字典與標準腳本

子句用途注意
MAXLOGFILES/MEMBERS/HISTORY控制檔記錄 Redo 組/成員/歷史上限建庫後不可修改，預留充足
CHARACTER SET AL32UTF8資料庫字元集 (Unicode)建庫後不可變更，慎選
EXTENT MANAGEMENT LOCAL本地管理表空間 (預設 AUTOALLOCATE)現代標準，避免字典管理
REUSE覆蓋同名現有檔案僅用於重建測試環境，生產慎用
AUTOEXTEND ON MAXSIZE UNLIMITED資料檔自動擴展需配合磁碟監控防止寫滿


生產環境建議：手工建庫僅用於特殊需求。標準部署**強烈建議使用 DBCA (靜默模式 `dbca -silent -createDatabase -responseFile ...`)**，可確保最佳實踐、字典完整性、選項元件正確安裝。