Oracle手工創建數據庫完整實戰：參數文件、實例啟動、CREATE DATABASE、數據字典構建

步驟4：CREATE DATABASE 核心語句

sqlplus / as sysdba <<'EOF'
CREATE DATABASE MYNEWDB
  USER SYS IDENTIFIED BY "SysPassword#2024"
  USER SYSTEM IDENTIFIED BY "SystemPassword#2024"
  LOGFILE GROUP 1 ('/u02/oradata/MYNEWDB/redo01a.log','/u02/oradata/MYNEWDB/redo01b.log') SIZE 200M BLOCKSIZE 512,
          GROUP 2 ('/u02/oradata/MYNEWDB/redo02a.log','/u02/oradata/MYNEWDB/redo02b.log') SIZE 200M BLOCKSIZE 512,
          GROUP 3 ('/u02/oradata/MYNEWDB/redo03a.log','/u02/oradata/MYNEWDB/redo03b.log') SIZE 200M BLOCKSIZE 512
  MAXLOGFILES 16
  MAXLOGMEMBERS 3
  MAXLOGHISTORY 1000
  MAXDATAFILES 1024
  MAXINSTANCES 8
  CHARACTER SET AL32UTF8
  NATIONAL CHARACTER SET AL16UTF16
  EXTENT MANAGEMENT LOCAL
  DATAFILE '/u02/oradata/MYNEWDB/system01.dbf' SIZE 700M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
  SYSAUX DATAFILE '/u02/oradata/MYNEWDB/sysaux01.dbf' SIZE 550M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
  DEFAULT TABLESPACE users
    DATAFILE '/u02/oradata/MYNEWDB/users01.dbf' SIZE 100M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
    EXTENT MANAGEMENT LOCAL AUTOALLOCATE
  DEFAULT TEMPORARY TABLESPACE temp
    TEMPFILE '/u02/oradata/MYNEWDB/temp01.dbf' SIZE 100M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
  UNDO TABLESPACE undotbs1
    DATAFILE '/u02/oradata/MYNEWDB/undotbs01.dbf' SIZE 200M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;
EOF

步驟5：創建附加表空間

sqlplus / as sysdba <<'EOF'
-- 應用表空間
CREATE TABLESPACE app_data DATAFILE '/u02/oradata/MYNEWDB/app_data01.dbf' SIZE 1G AUTOEXTEND ON NEXT 100M MAXSIZE 10G EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE app_index DATAFILE '/u02/oradata/MYNEWDB/app_idx01.dbf' SIZE 500M AUTOEXTEND ON NEXT 50M MAXSIZE 5G EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO;
-- 大對象表空間
CREATE TABLESPACE lob_data DATAFILE '/u02/oradata/MYNEWDB/lob01.dbf' SIZE 1G AUTOEXTEND ON NEXT 100M MAXSIZE 10G EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO;
-- 唯讀/歸檔表空間 (可選)
CREATE TABLESPACE archive_data DATAFILE '/u02/oradata/MYNEWDB/archive01.dbf' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 1G EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO;
EOF

步驟6：運行數據字典構建腳本

sqlplus / as sysdba <<'EOF'
@$ORACLE_HOME/rdbms/admin/catalog.sql
@$ORACLE_HOME/rdbms/admin/catproc.sql
-- 12c+ 還需
@$ORACLE_HOME/rdbms/admin/catcdb.sql
-- 安裝OWM/APEX/ORDS等組件按需運行相應腳本
EOF

注意：catalog.sql 創建標準視圖、同義詞、PL/SQL包；catproc.sql 創建內建包體；耗時5-30分鐘視硬件而定。

步驟7：創建應用用戶與權限

sqlplus / as sysdba <<'EOF'
CREATE USER app_user IDENTIFIED BY "AppPass#2024"
  DEFAULT TABLESPACE app_data TEMPORARY TABLESPACE temp
  QUOTA UNLIMITED ON app_data QUOTA UNLIMITED ON app_index;
GRANT CREATE SESSION, CREATE TABLE, CREATE SEQUENCE, CREATE PROCEDURE, CREATE VIEW, CREATE TRIGGER TO app_user;
GRANT SELECT ON v_$session TO app_user;  -- 監控需要
EOF

步驟8：配置監聽器與服務註冊

cat > $ORACLE_HOME/network/admin/listener.ora <<'EOF'
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = $(hostname))(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = MYNEWDB)
      (ORACLE_HOME = $ORACLE_HOME)
      (SID_NAME = MYNEWDB)
    )
  )
EOF

lsnrctl start
sqlplus / as sysdba <<'EOF'
ALTER SYSTEM REGISTER;
EOF

步驟9：驗收檢查清單

常見報錯對策

手工建庫雖繁瑣，但每一步都映射到實例核心結構。建議將上述流程封裝為create_db.sh參數化腳本，納入版本控制，實現「基礎設施即代碼」。