Oracle 執行個體建立最小參數需求與啟動驗證

Oracle 執行個體建立最小參數需求與啟動驗證

Oracle Instance 啟動時讀取參數檔（PFILE/SPFILE），理論上僅需 DB_NAME 一個參數即可建立最小可用 Instance。本文實測驗證最小參數啟動流程與預設值行為。

參數檔搜尋順序

1. spfile.ora
2. spfile.ora
3. init.ora
4. init.ora

實測：僅設定 DB_NAME 啟動 NOMOUNT

$ export ORACLE_SID=julia
$ sqlplus "/ as sysdba"

SQL> STARTUP NOMOUNT;
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/opt/oracle/product/9.2.0/dbs/initjulia.ora'

-- 建立最小參數檔
SQL> ! echo "db_name=julia" > /opt/oracle/product/9.2.0/dbs/initjulia.ora

SQL> STARTUP NOMOUNT;
Oracle instance started.

Total System Global Area  97588504 bytes
Fixed Size                   451864 bytes
Variable Size             46137344 bytes
Database Buffers          50331648 bytes
Redo Buffers                667648 bytes

預設值觀察（SHOW PARAMETER）

SQL> SHOW PARAMETER dump_dest;
NAME                 TYPE   VALUE
background_dump_dest string ?/rdbms/log
core_dump_dest       string ?/dbs
user_dump_dest       string ?/rdbms/log

SQL> SHOW PARAMETER control_files;
NAME           TYPE   VALUE
control_files  string ?/dbs/cntrl@.dbf

? 代表 ORACLE_HOME，@ 代表 SID。

Alert Log 記錄關鍵資訊

Mon May 8 11:09:04 2006
Starting Oracle instance (normal)
WARNING: EINVAL creating segment of size 0x0000000006400000
fix shm parameters in /etc/system or equivalent
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
SCN scheme 2
Using log_archive_dest parameter default value
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up Oracle RDBMS Version: 9.2.0.4.0.
System parameters with non-default values:
  db_name = julia
PMON started with pid=2
DBW0 started with pid=3
LGWR started with pid=4
CKPT started with pid=5
SMON started with pid=6
RECO started with pid=7

關鍵觀察結論

1. 最小參數：僅 DB_NAME 為必填，其餘皆有預設值
2. 背景程序：PMON、DBW0、LGWR、CKPT、SMON、RECO 自動啟動
3. 記憶體結構：SGA 依預設參數配置（共用池、大型池、Java 池、緩衝區快取、Redo Log Buffer）
4. 控制檔：預設 ORACLE_HOME/dbs/cntrl.dbf，生產環境**必須**多路徑多工
5. 授權參數：LICENSE_MAX_SESSION=0 表示無限制

生產環境最小必要參數清單（建議）

*.db_name='PRODDB'
*.control_files=('/u01/oradata/PRODDB/control01.ctl','/u02/oradata/PRODDB/control02.ctl')
*.db_block_size=8192
*.processes=300
*.sga_target=2G
*.pga_aggregate_target=1G
*.open_cursors=500
*.diagnostic_dest='/u01/app/oracle'
*.audit_trail='DB'
*.compatible='19.0.0'

驗證腳本

-- 檢查 Instance 狀態
SELECT instance_name, status, database_status FROM v$instance;

-- 檢查參數來源
SELECT name, value, isdefault, isses_modifiable, issys_modifiable
FROM   v$parameter
WHERE  name IN ('db_name','control_files','db_block_size','processes','sga_target');