oracle安装部署之后的参数调整

Oracle数据库运维配置参考

1. 参数配置

1.1 RAC环境参数调整：

适用于RAC集群的初始化参数，建议在创建数据库后设置，部分参数需要重启生效。

-- ============================================================
-- Oracle 数据库参数配置脚本
-- 执行角色：SYSDBA
-- 生效方式：需重启数据库（scope=spfile）
-- 适用环境：RAC集群（sid='*'），单实例可去掉 sid 参数
-- ============================================================

-- ============================================================
-- 一、基础实例参数配置
-- ============================================================

-- 数据库可打开的最大数据文件数（含临时文件）
-- 建议值：根据表空间规划预留 20% 余量，最大 65533
alter system set db_files=2000 sid='*' scope=spfile;

-- 撤销数据保留时间（秒），604800 = 7天
-- 作用：保证闪回查询、LOB  retention、一致性读所需 undo 不被覆盖
-- 注意：需配合 undo 表空间大小，保留期内数据不能覆盖
alter system set undo_retention=604800 sid='*' scope=spfile;

-- 单个会话最多同时打开的游标数（硬限制）
-- 超出将报 ORA-01000: maximum open cursors exceeded
alter system set open_cursors=2000 sid='*' scope=spfile;

-- 会话缓存的游标数，减少软解析开销，提升反复执行 SQL 性能
-- 建议为 open_cursors 的 10%，最大 2000
alter system set session_cached_cursors=200 sid='*' scope=spfile;

-- 操作系统进程数限制（含后台进程、用户连接、并行进程）
-- processes = sessions * 1.1 + 后台进程（约 50），需配合 OS 内核参数
alter system set processes=3000 sid='*' scope=spfile;

-- 重做日志缓冲区大小（字节），76103680 ≈ 72MB
-- 建议：高并发写入场景设为 10-30MB，最大通常 128MB（受 granule 限制）
-- 查看当前：show parameter log_buffer;（静态参数，需重启生效）
alter system set log_buffer=76103680 sid='*' scope=spfile;

-- DML 锁的最大数量，用于表级锁和事务锁
-- 公式：transactions + (processes * 2)，自动计算通常足够，显式设置避免动态分配
alter system set dml_locks=20064 sid='*' scope=spfile;

-- 并发事务数限制，影响 SGA 中事务表内存分配
-- 建议为 processes 的 1.5-2 倍
alter system set transactions=5016 sid='*' scope=spfile;

-- 一个会话可同时打开的远程数据库链接（dblink）总数
-- 分布式事务场景需调大，受 processes 限制
alter system set open_links=200 sid='*' scope=spfile;

-- 每个实例可同时打开的远程数据库链接总数（RAC 环境）
-- 单实例场景与 open_links 保持一致
alter system set open_links_per_instance=200 sid='*' scope=spfile;


-- ============================================================
-- 二、隐藏参数配置（"_参数名" 需加双引号）
-- 警告：以下参数为内部参数，Oracle 官方不推荐修改，升级前需评估
-- ============================================================

-- 并行执行（PX）使用 Large Pool 分配内存，避免占用 Shared Pool
-- 适用：频繁并行查询，减少 ORA-04031 错误
alter system set "_PX_use_large_pool"=true sid='*' scope=spfile;

-- 强制关闭资源管理器（Resource Manager），禁用所有资源计划
-- 注意：生产环境慎用，会导致 DBMS_RESOURCE_MANAGER 失效
alter system set "_resource_manager_always_off"=true sid='*' scope=spfile;

-- 强制禁用资源管理器始终开启模式（与上条配合使用）
alter system set "_resource_manager_always_on"=false sid='*' scope=spfile;

-- 禁用集群全局事务（RAC 专用），避免分布式事务在节点间协调开销
-- 单实例或无需 XA 事务时可设为 false
alter system set "_clusterwide_global_transactions"=false sid='*' scope=spfile;

-- 控制串行表扫描是否使用直接路径读（Direct Path Read）
-- never：禁用，使用常规缓存读（适合内存充足、重复访问场景）
-- auto/always：根据表大小自动或强制直接读（减少缓存冲击，但占 PGA）
alter system set "_serial_direct_read"=never sid='*' scope=spfile;

-- 清理回滚段条目的数量阈值，134217728 = 2^27（内部位图标记）
-- 作用：控制 SMON 清理回滚段频率，大事务场景减少开销
alter system set "_cleanup_rollback_entries"=4000 sid='*' scope=spfile;

-- 关闭优化器基数反馈（Cardinality Feedback），12c+ 默认开启
-- 设为 false 避免执行计划频繁变化，适合统计信息稳定的系统
-- 11g 无此参数，12c+ 建议配合 _optimizer_adaptive_plans=false
alter system set "_optimizer_use_feedback"=false sid='*' scope=spfile;

-- DBMS_SQL 包的安全级别，0 = 禁用增强安全检查
-- 降级兼容旧版动态 SQL 行为，存在 SQL 注入风险，需谨慎
alter system set "_dbms_sql_security_level"=0 sid='*' scope=spfile;

-- RAC 全局缓存策略时间（毫秒），0 = 禁用动态策略调整
-- 减少 GCS/GES 消息开销，适合节点间通信延迟低的同城双中心
alter system set "_gc_policy_time"=0 sid='*' scope=spfile;

-- 禁用读多数锁定（Read Mostly Locking），避免热点块在节点间频繁传输
-- 设为 false 减少 cache fusion 流量，适合写密集型业务
alter system set "_gc_read_mostly_locking"=false sid='*' scope=spfile;

-- 禁用撤销段亲和性（Undo Affinity），取消 RAC 节点与撤销段的绑定
-- 设为 false 允许跨节点使用撤销段，减少节点故障时的恢复时间
alter system set "_gc_undo_affinity"=false sid='*' scope=spfile;

-- 系统管理撤销（SMU）调试模式，134217728 = 启用特定诊断位
-- 仅故障排查时使用，生产环境建议保持默认 0
alter system set "_smu_debug_mode"=134217728 sid='*' scope=spfile;


-- ============================================================
-- 三、审计与归档配置
-- ============================================================

-- 关闭标准审计（Standard Auditing），减少 I/O 开销
-- none：完全关闭；os/db/extended/xml 为其他模式
-- 注意：需配合 unified auditing 状态检查（12c+ 默认 unified audit）
alter system set audit_trail=none sid='*' scope=spfile;

-- 归档日志目标路径 1，ASM 磁盘组示例（+arch）
-- 格式：location=本地路径 或 service=远程 TNS 别名（Data Guard）
alter system set log_archive_dest_1='location=+arch/gjorcl/ARCHIVELOG' sid='*' scope=spfile;

-- 归档日志文件名格式
-- %S：日志序列号（10位） | %R：resetlogs ID | %T：线程号 | %D：DBID
-- 示例：ARC0000000123_1234567890.1_1234567890.log
alter system set log_archive_format='ARC%S_%R.%T_%D.log' sid='*' scope=spfile;

-- 快速恢复区（Flash Recovery Area）大小限制，10GB
-- 存放归档日志、闪回日志、RMAN 备份、控制文件镜像
-- 需配合 db_recovery_file_dest 参数指定路径
alter system set db_recovery_file_dest_size=10G sid='*' scope=spfile;


-- ============================================================
-- 四、重启验证命令（执行完以上命令后执行）
-- ============================================================

/*
-- 1. 重启数据库使参数生效
srvctl stop database -d gjorcl
srvctl start database -d gjorcl

-- 2. 验证参数生效（RAC 所有节点）
SELECT name, value, isdefault, isses_modifiable, issys_modifiable, ismodified 
FROM v$parameter 
WHERE name IN ('db_files','undo_retention','open_cursors','processes','log_buffer',
            'audit_trail','log_archive_dest_1','db_recovery_file_dest_size')
ORDER BY name;

-- 3. 检查隐藏参数（需 as sysdba）
SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc DESCRIPTION
FROM x$ksppi x, x$ksppcv y
WHERE x.indx = y.indx
AND x.ksppinm LIKE '\_%' ESCAPE '\'
AND x.ksppinm IN ('_px_use_large_pool','_resource_manager_always_off',
                 '_serial_direct_read','_optimizer_use_feedback')
ORDER BY NAME;
*/

说明：

  • sessions由processes派生，通常不需要手动调整。
  • _resource_manager_always_off与_resource_manager_always_on需保持一致，此处off为true、on为false。
  • 隐藏参数需谨慎使用，仅在Oracle Support指导下或充分理解其作用后设置。

快速配置：

-- 以sysdba执行以下命令
alter system set db_files=2000 sid='*' scope=spfile;
alter system set undo_retention=604800 sid='*' scope=spfile;          -- 撤销保留时间7天
alter system set open_cursors=2000 sid='*' scope=spfile;
alter system set session_cached_cursors=200 sid='*' scope=spfile;
alter system set processes=3000 sid='*' scope=spfile;                 -- 进程数
alter system set log_buffer=76103680 sid='*' scope=spfile;            -- 日志缓冲区约72MB
alter system set dml_locks=20064 sid='*' scope=spfile;
alter system set transactions=5016 sid='*' scope=spfile;
alter system set open_links=200 sid='*' scope=spfile;                 -- 同时打开的数据库链接数
alter system set open_links_per_instance=200 sid='*' scope=spfile;

-- 审计与归档路径
alter system set audit_trail=none sid='*' scope=spfile;                -- 关闭审计
alter system set log_archive_dest_1='location=+arch/gjorcl/ARCHIVELOG' sid='*' scope=spfile;
alter system set log_archive_format='ARC%S_%R.%T_%D.log' sid='*' scope=spfile;
alter system set db_recovery_file_dest_size=10G sid='*' scope=spfile; -- 闪回区大小

添加参数配置说明

关键参数速查表

参数类别 参数名 配置值 核心作用 风险提示
连接与会话 processes 3000 最大并发进程数 需同步调整 OS 内核参数（nproc）
open_cursors 2000 单会话游标上限 应用需及时关闭游标，防泄漏
session_cached_cursors 200 软解析缓存 过高浪费 PGA，建议 10% open_cursors
存储与文件 db_files 2000 数据文件上限 无法在线降低，预留足够余量
log_buffer 72MB 重做日志缓冲 静态参数，过大可能延迟写入
事务与锁 undo_retention 7天 闪回/一致性读保障 需 undo 表空间物理空间支持
transactions 5016 并发事务数 影响 SGA 内存分配
分布式 open_links 200 单会话 dblink 数 跨库事务需关注两阶段提交
并行与资源 _PX_use_large_pool TRUE 并行内存分配 需确保 large_pool_size 充足
_resource_manager_always_off TRUE 禁用资源管理器 丧失 CPU/IO 资源管控能力
RAC 优化 _gc_policy_time 0 禁用动态缓存策略 节点增减时需手动调整
_gc_read_mostly_locking FALSE 禁用读多数锁定 适合写密集型，读密集建议 TRUE
优化器 _optimizer_use_feedback FALSE 关闭基数反馈 统计信息不准时可能选错计划
审计安全 audit_trail none 关闭审计 需满足合规要求时不可关闭
备份恢复 log_archive_dest_1 +arch 归档路径 确保 ASM 磁盘组空间充足
db_recovery_file_dest_size 10G 闪回区上限 超过 90% 将报 ORA-19809

⚠️ 重要提醒

  • 重启生效：所有 scope=spfile 参数需重启数据库才能生效，RAC 需滚动重启所有实例
  • 隐藏参数风险：带 _ 前缀的参数为内部参数，Oracle 官方不提供支持，升级前必须重置
  • UNDO 空间计算：undo_retention=7天 需确保 undo 表空间 ≥ 日均 undo 产生量 × 7，否则保留期无法保证
  • 审计关闭影响：audit_trail=none 关闭标准审计，但 12c+ 的 Unified Audit 需额外禁用：
-- 检查 Unified Audit 状态
SELECT policy_name, enabled_opt, success, failure 
FROM audit_unified_policies 
WHERE enabled_opt='BY USER';

-- 如存在强制审计，需单独关闭
NOAUDIT POLICY ORA_SECURECONFIG;

1.2 单机环境参数调整

非RAC环境的基础参数配置，精简了部分集群专用参数

alter system set db_files=2000 sid='*' scope=spfile;
alter system set undo_retention=604800 sid='*' scope=spfile;
alter system set open_cursors=2000 sid='*' scope=spfile;
alter system set session_cached_cursors=200 sid='*' scope=spfile;
alter system set log_buffer=76103680 sid='*' scope=spfile;
alter system set dml_locks=20064 sid='*' scope=spfile;
alter system set transactions=5016 sid='*' scope=spfile;
alter system set open_links=200 scope=spfile;
alter system set open_links_per_instance=200 scope=spfile;

1.3 参数验证

-- 查看当前设置
show parameter db_files;
show parameter undo_retention;
show parameter open_links;
-- 查看隐藏参数
SELECT name, value FROM v$parameter WHERE name LIKE '\_%' ESCAPE '\';

2. 归档模式与清理配置

2.1 启用归档模式步骤

-- 1. 查看当前归档状态
archive log list;

-- 2. 关闭数据库
shutdown immediate;

-- 3. 启动到mount状态
startup mount;

-- 4. 修改归档路径（根据实际环境修改）
alter system set log_archive_dest_1='location=/u01/archive' scope=spfile;

-- 5. 启用归档模式
alter database archivelog;

-- 6. 打开数据库
alter database open;

-- 7. 确认状态
archive log list;

-- 8. 手动切换日志，验证归档生成
alter system switch logfile;

2.2 归档日志自动清理脚本

使用RMAN定期删除过期归档，建议保留最近10天。
脚本位置： /home/oracle/gj_monitor/archlog_del/arch_del.sh

vi /home/oracle/gj_monitor/archlog_del/arch_del.sh

source ~/.bash_profile
$ORACLE_HOME/bin/rman target / log=/home/oracle/gj_monitor/archlog_del/rman.log <<EOF
crosscheck archivelog all;
delete noprompt expired archivelog all;
delete noprompt force archivelog all completed before 'sysdate - 10';
exit;
EOF

2.3 定时任务

通过crontab每天执行归档清理和备份脚本。

30 10 * * * /home/oracle/gj_monitor/archlog_del/arch_del.sh
35 10 * * * /home/oracle/gj_monitor/auto_backup.sh > /tmp/dump.log

3. 数据泵备份配置

3.1 备份脚本

脚本位置：vi /home/oracle/gj_monitor/archlog_del/auto_backup.sh

#!/bin/sh
source /etc/profile
source /home/oracle/.bash_profile
export PATH

#定义颜色
RED='\E[1;31m'			#红色
BLUE='\E[1;34m'			#蓝色 	
GREEN='\E[1;32m'		#绿色 
RESET='\E[0m'			#清除颜色

#定义变量 
BACKUP_DIR="/data/dump"
DATE=`date +%Y-%m-%d`
OLD_DATE=`date -d "7 days ago" +%Y-%m-%d`

USER=${2:-"HZTZ_PROD"}
DUMPDIR='dump'

START_DATE=`date "+%Y-%m-%d %H:%M:%S"`
echo -e "\n\n\n======================================= $START_DATE ====================================="
#判断用户
echo -e "$BLUE 判断当前用户是否为oracle用户  $RESET\n"
useris=`whoami`
machinename=`uname -m`
if [ "$useris" != "oracle" ]; then
    echo -e "$RED 请在oracle下执行该脚本 $RESET\n"
    exit 1
fi

#创建备份目录
mkdir $BACKUP_DIR/$DATE -p
#开始备份并删除7天前备份
for i in ${USER};do
        expdp \'/ as sysdba\' directory=dump dumpfile=$i%U.dmp SCHEMAS=$i LOGFILE=$i.log PARALLEL=8 version=11.2.0.4.0 compression=all cluster=n
	if [ $? == 0 ] ;then
		echo -e "$GREEN backup $i success! $RESET\n"
                mv /data/dump/HZTZ_PROD* $BACKUP_DIR/$DATE
	else
		echo -e "$RED backup $i failure!$RESET\n"
	fi
done
zip -r $BACKUP_DIR/$DATE.zip $BACKUP_DIR/$DATE
rm -rf /data/dump/$DATE
rm -rf /data/dump/$OLD_DATE.*
if [ "`ls -A ${OLD_DATE}`" = "" ]; then
        rm -rf /data/dump/${OLD_DATE}*
else
        echo "${OLD_DATE} 存在备份文件，不执行删除!"
fi

END_DATE=`date "+%Y-%m-%d %H:%M:%S"`
echo -e "\n======================================= $END_DATE =====================================\n\n"

说明：

  • 备份文件按schema分开，保留7天压缩包。
  • 使用cluster=n避免RAC环境下的并行问题。
  • 需提前创建Directory对象：CREATE DIRECTORY dump AS '/data/dump';

3.2 定时任务：

30 10 * * * /home/oracle/gj_monitor/archlog_del/arch_del.sh
35 10 * * * /home/oracle/gj_monitor/auto_backup.sh > /tmp/dump.log

4. 强制日志与补充日志配置

4.1 强制日志（Force Logging）

确保所有操作（包括NOLOGGING操作）都记录重做日志，保证数据可恢复。

-- 查看当前状态
SELECT FORCE_LOGGING FROM v$database;

-- 启用强制日志
ALTER DATABASE FORCE LOGGING;

-- 取消强制日志
-- ALTER DATABASE NO FORCE LOGGING;

4.2 补充日志（Supplemental Logging）

用于LogMiner、GoldenGate等工具完整解析变更前后值。

-- 启用最小补充日志
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

-- 验证
SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM v$database;
-- 应返回 YES 或 IMPLICIT

5. 其他常用配置

5.1 修改密码永不过期：

-- 查看用户的密码使用的密码策略和过期时间
select username,profile,EXPIRY_DATE
from dba_users
where username='SCOTT'

-- 查询默认密码过期时间
SELECT * FROM dba_profiles WHERE profile='DEFAULT' AND resource_name='PASSWORD_LIFE_TIME';

-- 修改对应密码策略的密码永不过期（修改后已经过期的密码）
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

--修改所有用户的密码过期时间为2年（单位是天）
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME 730;

修改之后会立即生效，还没有被提示ORA-28002警告的帐户不会再碰到同样的提示，已经被提示的帐户必须再改一次密码。
alter user scott identified by 1234; --1234为scott用户原来的密码

5.2 闪回保留时间与撤销表空间保证（如下设置为7天）

设置撤销保留时间（如7天=604800秒），并保证未提交事务不被覆盖。
7x24x60x60=604800

-- 设置撤销保留时间
show parameter undo;
ALTER SYSTEM SET undo_retention=604800 SCOPE=BOTH;

-- 强制撤销表空间保留
ALTER TABLESPACE undotbs1 RETENTION GUARANTEE;

5.3 控制文件与Redo文件检查

控制文件是数据库的核心元数据文件，增加控制文件主要是添加多路复用副本以提高可靠性。

  • 控制文件：建议至少3个，分散存储。
-- 1. 查看当前控制文件位置
SHOW PARAMETER control_files;

-- 2. 关闭数据库
SHUTDOWN IMMEDIATE;

-- 3. 复制现有控制文件到新位置（操作系统层面）
-- Linux/Unix:
cp /u01/app/oracle/oradata/ORCL/control01.ctl /u02/oradata/ORCL/control02.ctl

-- Windows:
copy C:\oracle\oradata\ORCL\control01.ctl D:\oracle\oradata\ORCL\control02.ctl

-- 4. 修改初始化参数（使用 SPFILE）
STARTUP NOMOUNT;
ALTER SYSTEM SET control_files='/u01/app/oracle/oradata/ORCL/control01.ctl',
                              '/u02/oradata/ORCL/control02.ctl' 
    SCOPE=SPFILE;

-- 5. 重启数据库
SHUTDOWN IMMEDIATE;
STARTUP;

-- 6. 验证
SHOW PARAMETER control_files;

注意事项：

  • 控制文件数量建议 2-3 个，分布在不同磁盘

  • 必须先复制文件再改参数，否则数据库无法启动

  • 使用 SPFILE 修改，PFILE 需手动编辑文本文件

  • Redo Log：每个实例至少3组，每组2个成员，大小1GB。
    Redo Log 增加分为两种情况：新增日志组 或 增加现有组成员。

SELECT group#, status, bytes/1024/1024 MB FROM v$log;
SELECT group#, member FROM v$logfile;

1. 新增日志组 (Log Group)

-- 查看当前日志配置
SELECT group#, thread#, bytes/1024/1024 MB, members, status 
FROM v$log;

-- 新增日志组（每组建议2个成员，分布在不同磁盘）
ALTER DATABASE ADD LOGFILE GROUP 4 
    ('/u01/app/oracle/oradata/ORCL/redo04a.log',
     '/u02/oradata/ORCL/redo04b.log') 
    SIZE 500M;

-- 或新增多个组
ALTER DATABASE ADD LOGFILE GROUP 5 
    ('/u01/app/oracle/oradata/ORCL/redo05a.log',
     '/u02/oradata/ORCL/redo05b.log') 
    SIZE 500M;

2. 为现有组增加成员（多路复用）

-- 为 Group 1 增加第二个成员
ALTER DATABASE ADD LOGFILE MEMBER '/u02/oradata/ORCL/redo01b.log' 
    TO GROUP 1;

-- 为 Group 2 增加成员
ALTER DATABASE ADD LOGFILE MEMBER '/u02/oradata/ORCL/redo02b.log' 
    TO GROUP 2;

3. 删除日志组/成员

-- 删除日志成员（状态不能是 CURRENT 或 ACTIVE）
ALTER DATABASE DROP LOGFILE MEMBER '/u02/oradata/ORCL/redo01b.log';

-- 删除整个日志组（组内成员都会删除，状态不能是 CURRENT）
ALTER DATABASE DROP LOGFILE GROUP 4;
操作 是否需停库 关键命令
增加控制文件 ✅ 必须停库 ALTER SYSTEM SET control_files
新增日志组 ❌ 在线操作 ALTER DATABASE ADD LOGFILE GROUP
增加日志成员 ❌ 在线操作 ALTER DATABASE ADD LOGFILE MEMBER
删除日志组 ❌ 在线操作（状态需INACTIVE） ALTER DATABASE DROP LOGFILE

5.4 数据库对象健康检查

  • 无效对象：定期检查并重新编译。
SELECT owner, object_type, object_name FROM dba_objects WHERE status = 'INVALID';

脚本：
INVAILD_OBJECT_COMPILE.sh

#!/bin/sh
source /etc/profile
source ~/.bash_profile
export PATH
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1
export PATH=$PATH:$ORACLE_HOME/bin
export ORACLE_SID=GYHZ
##envirment set
target_dir='/home/oracle/gj_monitor/invaild_object_compile'
sqlplus / as sysdba @${target_dir}/invaild_object_compile.sql
sqlplus / as sysdba @${target_dir}/invaild_object_compile.log

get_invaild_object.sql

spool "D:\gj_monitor\get_invaild_object.log";
set line 300 pages 999;
set head OFF;
col VALUE for a25;
select 'alter '||object_type||' '||owner ||'.'||object_name||' COMPILE;' from dba_objects WHERE status = 'INVALID';
SELECT 'exit' FROM DUAL;
spool off;
exit
EOF

并行度检查：确保表和索引并行度均为1（除非特殊需求）

SELECT owner, table_name, degree FROM dba_tables WHERE degree > 1;
SELECT owner, index_name, degree FROM dba_indexes WHERE degree > 1;
posted @ 2026-03-06 14:32  数据库小白（专注）  阅读(13)  评论(0)    收藏  举报