Oracle 环境标准化配置

适用版本：Oracle Database 19c 文档版本：v1.0 / 2026-07

1. 概述

环境标准化提升运维效率[1]：

标准化内容：

目录结构
用户权限
参数配置
命名规范

2. 目录结构

2.1 标准目录

/u01
├── app
│   ├── oracle                    # ORACLE_BASE
│   │   ├── product
│   │   │   └── 19.0.0
│   │   │       └── dbhome_1      # ORACLE_HOME
│   │   ├── oradata               # 数据文件
│   │   │   └── orcl
│   │   ├── fast_recovery_area    # FRA
│   │   ├── admin
│   │   │   └── orcl
│   │   │       ├── adump
│   │   │       ├── bdump
│   │   │       ├── cdump
│   │   │       └── udump
│   │   ├── cfgtoollogs           # 工具日志
│   │   ├── checkpoints
│   │   └── diag                  # ADR
│   └── oraInventory              # Inventory
└── backup                        # 备份
    ├── rman
    ├── archive
    └── dump

2.2 ASM

+DATA  - 数据
+FRA   - 归档/备份
+REDO  - Redo
+OCR   - OCR/Voting

详细见：Oracle ASM 详解。

3. 用户与组

3.1 标准组

组	GID	说明
oinstall	54321	基本组
dba	54322	DBA
oper	54323	OPER
backupdba	54324	Backup
dgdba	54325	Data Guard
kmdba	54326	Key Management
racdba	54327	RAC
asmadmin	54328	ASM Admin
asmdba	54329	ASM DBA
asmoper	54330	ASM Oper

3.2 标准用户

用户	UID	主组	附加组
oracle	54321	oinstall	dba, oper, backupdba, dgdba, kmdba, racdba
grid	54331	oinstall	asmadmin, asmdba, asmoper, dba

3.3 创建

# 组
for g in oinstall:54321 dba:54322 oper:54323 backupdba:54324 dgdba:54325 kmdba:54326 racdba:54327 asmadmin:54328 asmdba:54329 asmoper:54330; do
  groupadd -g ${g#*:} ${g%:*}
done

# 用户
useradd -u 54321 -g oinstall -G dba,oper,backupdba,dgdba,kmdba,racdba oracle
useradd -u 54331 -g oinstall -G asmadmin,asmdba,asmoper,dba grid

passwd oracle
passwd grid

4. 命名规范

4.1 数据库

SID: orcl, hrprd, findev
DB_NAME: orcl, hrprd, findev
DB_UNIQUE_NAME: orcl, orcldg, orclstby
GLOBAL_NAME: orcl.example.com

4.2 PDB

HRPDB, FINPDB, SALESPDB

4.3 表空间

SYSTEM, SYSAUX, UNDOTBS1, TEMP, USERS
业务: TS_HR_DATA, TS_HR_INDEX

4.4 数据文件

/u01/app/oracle/oradata/<db_unique_name>/<tablespace>_<n>.dbf
如：/u01/app/oracle/oradata/orcl/users01.dbf

4.5 Redo

/u01/app/oracle/oradata/<db_unique_name>/redo<group>_<member>.log
如：redo01_a.log, redo01_b.log

4.6 归档

/u01/app/oracle/fast_recovery_area/<db_unique_name>/archivelog/<yyyy-mm-dd>/

5. 参数文件

5.1 SPFILE

$ORACLE_HOME/dbs/spfile<sid>.ora

5.2 PFILE 备份

$ORACLE_HOME/dbs/init<sid>.ora

5.3 标准参数

-- 内存
sga_target = 50% 物理内存
pga_aggregate_target = 20% 物理内存
pga_aggregate_limit = 2 * pga_aggregate_target

-- 进程
processes = 500
sessions = 750
open_cursors = 1000

-- 优化器
optimizer_mode = ALL_ROWS
optimizer_adaptive_plans = TRUE
optimizer_adaptive_statistics = FALSE
cursor_sharing = EXACT

-- 安全
failed_login_attempts = 10
password_lock_time = 1
sec_case_sensitive_logon = TRUE

-- 网络
local_listener = ''
remote_listener = ''

-- 备份
db_recovery_file_dest_size = 100G
db_flashback_retention_target = 1440

详细见：Oracle 数据库参数调优。

6. 网络配置

6.1 listener.ora

LISTENER = 
  (DESCRIPTION_LIST = (DESCRIPTION = 
    (ADDRESS = (PROTOCOL = tcp)(HOST = <hostname>)(PORT = 1521))
  ))

ADR_BASE_LISTENER = /u01/app/oracle

6.2 tnsnames.ora

ORCL = (DESCRIPTION = (ADDRESS = (PROTOCOL = tcp)(HOST = ...)(PORT = 1521))
  (CONNECT_DATA = (SERVICE_NAME = orcl.example.com)))

6.3 sqlnet.ora

NAMES.DIRECTORY_PATH = (TNSNAMES, EZCONNECT)
SQLNET.INBOUND_CONNECT_TIMEOUT = 60
SQLNET.EXPIRE_TIME = 10
DEFAULT_SDU_SIZE = 32767
DIAG_ADR_ENABLED = ON

详细见：Oracle 网络配置与监听器。

7. 环境变量

7.1 oracle .bash_profile

# Oracle
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=$ORACLE_BASE/product/19.0.0/dbhome_1
ORACLE_SID=orcl
PATH=$ORACLE_HOME/bin:$PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib
export ORACLE_BASE ORACLE_HOME ORACLE_SID PATH LD_LIBRARY_PATH

# Locale
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
export NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'

# Alias
alias sql='sqlplus / as sysdba'
alias lsn='lsnrctl status'

7.2 grid .bash_profile

ORACLE_BASE=/u01/app/grid
ORACLE_HOME=/u01/app/19.0.0/grid
ORACLE_SID=+ASM1
PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_BASE ORACLE_HOME ORACLE_SID PATH

8. 监控配置

8.1 EM Express

EXEC DBMS_XDB_CONFIG.SETHTTPSPORT(5500);

8.2 告警

-- 表空间阈值
EXEC DBMS_SERVER_ALERT.SET_THRESHOLD(
  metrics_id => DBMS_SERVER_ALERT.TABLESPACE_PCT_FULL,
  warning_operator => DBMS_SERVER_ALERT.OPERATOR_GE,
  warning_value => 80,
  critical_operator => DBMS_SERVER_ALERT.OPERATOR_GE,
  critical_value => 95,
  observation_period => 1,
  consecutive_occurrences => 1,
  instance_name => NULL,
  object_type => DBMS_SERVER_ALERT.OBJECT_TYPE_TABLESPACE,
  object_name => NULL
);

详细见：Oracle 监控告警配置。

9. 备份策略

9.1 RMAN 配置

RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u01/backup/rman/%F';
RMAN> CONFIGURE BACKUP OPTIMIZATION ON;
RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 4;
RMAN> CONFIGURE CHANNEL 1 DEVICE TYPE DISK FORMAT '/u01/backup/rman/%U';
RMAN> CONFIGURE CHANNEL 2 DEVICE TYPE DISK FORMAT '/u02/backup/rman/%U';

9.2 调度

# crontab
0 2 * * 0 /u01/scripts/rman_full.sh    # 周日全备
0 2 * * 1-6 /u01/scripts/rman_incr.sh  # 周一至六增量

详细见：Oracle RMAN 备份恢复。

10. 自动化脚本

10.1 监控脚本

#!/bin/bash
# /u01/scripts/check_db.sh

sqlplus -s / as sysdba <<EOF
SET HEADING OFF FEEDBACK OFF
SELECT 'TABLESPACE ' || tablespace_name || ' at ' || ROUND(used/total*100, 2) || '%'
FROM (
  SELECT df.tablespace_name, SUM(df.bytes) total, SUM(df.bytes - NVL(fs.bytes, 0)) used
  FROM dba_data_files df, dba_free_space fs
  WHERE df.file_id = fs.file_id(+)
  GROUP BY df.tablespace_name
)
WHERE used/total > 0.8;
EXIT;
EOF

10.2 备份脚本

#!/bin/bash
# /u01/scripts/rman_full.sh

rman target / <<EOF
RUN {
  CROSSCHECK BACKUP;
  DELETE NOPROMPT EXPIRED BACKUP;
  BACKUP DATABASE PLUS ARCHIVELOG DELETE INPUT;
  DELETE NOPROMPT OBSOLETE;
}
EOF

11. 文档化

11.1 配置文档

- 主机名
- IP
- ORACLE_HOME
- 数据库 SID
- 端口
- 字符集
- 备份策略

11.2 变更记录

日期 | 变更 | 操作人 | 备注

12. 常见坑与排错

12.1 命名不一致

- 多 DBA 不同习惯
- 标准化
- 文档

12.2 路径混乱

- 标准目录
- 不混用

12.3 权限错误

- 标准组
- 文件权限

13. 最佳实践

标准目录：统一
标准用户/组：UID/GID
命名规范：清晰
标准参数：合理
网络配置：模板
环境变量：模板
监控告警：自动
备份策略：标准
自动化脚本：效率
文档化：维护

14. 参考资料

[1] Oracle Database Administrator’s Guide 19c https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/