Oracle 10g 手動建庫完整步驟：從目錄規劃到腳本編寫

Oracle 10g 手動建庫完整步驟：從目錄規劃到腳本編寫

Oracle 10g 手動建庫流程與 11g 大同小異，核心差異在於默認參數值、字符集建議、管理工具 (EM Database Control vs Cloud Control)。本文記錄 10g 環境下的標準化操作步驟。

第一步：目錄規劃

cd $ORACLE_BASE/oradata
mkdir HYL
cd HYL
mkdir dump disk1 disk2 disk3 disk4 disk5
cd dump
mkdir bdump cdump udump

第二步：權限授予

chown -R oracle:oinstall /u01/app/oracle/oradata/HYL
chmod -R 755 /u01/app/oracle/oradata/HYL

第三步：初始化參數文件 (initHYL.ora)

cd $ORACLE_HOME/dbs
vi initHYL.ora

10g 典型參數：

*.sga_target=400M
*.db_name=HYL
*.control_files='/u01/app/oracle/oradata/HYL/disk1/control01.ctl',
               '/u01/app/oracle/oradata/HYL/disk2/control01.ctl',
               '/u01/app/oracle/oradata/HYL/disk3/control01.ctl'
*.undo_management=AUTO
*.undo_tablespace=UNDOTBS
*.user_dump_dest=/u01/app/oracle/oradata/HYL/dump/udump
*.background_dump_dest=/u01/app/oracle/oradata/HYL/dump/bdump
*.core_dump_dest=/u01/app/oracle/oradata/HYL/dump/cdump
*.compatible=10.2.0.0.0
*.db_block_size=8192
*.processes=150
*.open_cursors=300
*.diagnostic_dest=/u01/app/oracle  # 10g 新增 ADR 基礎路徑

第四步：啟動實例至 NOMOUNT

export ORACLE_SID=HYL
sqlplus / as sysdba
SQL> STARTUP NOMOUNT;

第五步：編寫 CREATE DATABASE 腳本

參考 $ORACLE_HOME/rdbms/admin/dbcreate.sql 或官方文檔《Oracle Database Administrator's Guide - Creating a Database Manually》編寫腳本 create_hyl.sql：

CREATE DATABASE HYL
USER SYS IDENTIFIED BY oracle
USER SYSTEM IDENTIFIED BY oracle
LOGFILE GROUP 1 ('/u01/app/oracle/oradata/HYL/disk1/redo01.log',
                 '/u01/app/oracle/oradata/HYL/disk2/redo01.log') SIZE 100M,
        GROUP 2 ('/u01/app/oracle/oradata/HYL/disk3/redo02.log',
                 '/u01/app/oracle/oradata/HYL/disk4/redo02.log') SIZE 100M,
        GROUP 3 ('/u01/app/oracle/oradata/HYL/disk5/redo03.log',
                 '/u01/app/oracle/oradata/HYL/disk1/redo03.log') SIZE 100M
DATAFILE '/u01/app/oracle/oradata/HYL/disk1/system01.dbf' SIZE 500M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED
SYSAUX DATAFILE '/u01/app/oracle/oradata/HYL/disk2/sysaux01.dbf' SIZE 300M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED
DEFAULT TABLESPACE users
  DATAFILE '/u01/app/oracle/oradata/HYL/disk3/users01.dbf' SIZE 100M AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED
DEFAULT TEMPORARY TABLESPACE temp
  TEMPFILE '/u01/app/oracle/oradata/HYL/disk4/temp01.dbf' SIZE 100M AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED
UNDO TABLESPACE undotbs
  DATAFILE '/u01/app/oracle/oradata/HYL/disk5/undotbs01.dbf' SIZE 200M AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED
CHARACTER SET ZHS16GBK
NATIONAL CHARACTER SET AL16UTF16
EXTENT MANAGEMENT LOCAL;

10g 注意點：建議字符集用 AL32UTF8；10g 默認啟用 AUDIT_TRAIL=DB，若不需要可設為 NONE。

第六步：執行建庫腳本

SQL> @create_hyl.sql
-- 等待完成，提示 "Database created."

第七步：建立數據字典與內置包

SQL> @?/rdbms/admin/catalog.sql
SQL> @?/rdbms/admin/catproc.sql
SQL> @?/rdbms/admin/utlrp.sql

第八步：配置監聽與 EM Database Control

$ netca   # 圖形/靜默配置 listener.ora
$ emca -config dbcontrol db -repos create  # 配置 EM Database Control

常見坑點提醒

• 參數文件路徑拼寫錯誤 → ORA-01078
• 控制文件目錄不存在或權限不足 → ORA-00210
• 字符集與國家字符集不兼容 → ORA-12714
• 未運行 catalog.sql 導致 DBA_* 視圖缺失

總結

10g 手動建庫核心在於：目錄預先創建並授權 → PFILE 參數精確 → NOMOUNT 啟動 → CREATE DATABASE 多路復用 → 字典腳本跑全 → 監聽/EM 配置。建議將上述步驟封裝為 Shell + SQL 腳本組合，實現可重複、可審計的標準化交付。