未记录事实核验 · 状态：维护中
记录仅说明核验时间与维护状态，不保证适用于所有环境；使用前请留意正文中的版本、适用条件与风险提示。
warning 适用边界与维护说明
本文面向有Oracle运维经验的DBA及数据库工程师，适合作为连接、性能、空间、锁等待和数据损坏排查的流程参考与脚本讨论材料，不是可直接用于银行生产环境的自动修复工具。本次仅作全文结构审阅，未作外部事实核验或脚本执行验证。当前连接与锁诊断仍将last_call_et当作锁等待时长，自动处理过程也据此筛选终止对象；锁查询仍存在将TX锁id1直接关联对象编号的问题，不能据这些输出直接定位或终止会话。性能报告标称最近一小时，但实际混用当前快照和累计等待统计；缓存命中率计算未保护零分母，固定阈值建议不能直接作为内存调整依据。生成的RMAN脚本仍有命令及成功判断缺陷，不能据其成功提示确认验证通过。空闲一小时不能证明连接僵死，终止会话和扩容前须确认直接对象授权、系统变更权限、业务审批、事务影响、存储与文件限制，以及单实例/RAC、CDB/PDB执行范围；扩容失败后自动加文件的逻辑不通用于大文件表空间或OMF/ASM环境。状态与元数据检查不等于数据完整性验证，DBMS_REPAIR不是通用无损修复工具。19c路径、1521端口和BANKPROD仅为环境示例，日志与配置位置应按ADR及TNS_ADMIN等实际设置确认，tnsping成功不代表数据库认证或登录成功。
Oracle故障排查指南
故障排查基础方法
Oracle故障排查是确保银行核心系统稳定运行的关键技能。在高压力的金融环境中，快速准确地定位和解决问题对业务连续性至关重要。本指南提供系统化的故障排查方法和常见问题解决方案。
1. 故障分类与优先级
故障严重性等级
- P0级：系统完全不可用，影响核心业务
- P1级：核心功能受限，显著影响业务
- P2级：部分功能异常，轻微影响业务
- P3级：性能问题或非关键功能异常
故障类型分类
- 连接问题：无法连接数据库
- 性能问题：响应缓慢或超时
- 空间问题：表空间或临时空间不足
- 锁等待：会话阻塞或死锁
- 数据损坏：数据文件或索引损坏
2. 故障排查标准流程
第一步：问题确认
- 收集故障现象和时间
- 确认影响范围和严重程度
- 检查近期变更记录
第二步：环境检查
- 系统资源使用情况
- 数据库实例状态
- 网络连接状态
第三步：日志分析
- 告警日志检查
- 监听器日志分析
- 操作系统日志审查
第四步：诊断分析
- 收集诊断信息
- 分析等待事件
- 检查系统统计信息
第五步：问题解决
- 制定解决方案
- 实施修复措施
- 验证修复效果
连接问题排查
1. 连接故障诊断脚本
-- 连接问题诊断工具
CREATE OR REPLACE PROCEDURE diagnose_connection_issues AS
v_listener_status VARCHAR2(20);
v_instance_status VARCHAR2(20);
v_db_open_mode VARCHAR2(20);
v_total_sessions NUMBER;
v_active_sessions NUMBER;
v_max_sessions NUMBER;
v_process_count NUMBER;
v_max_processes NUMBER;
BEGIN
DBMS_OUTPUT.PUT_LINE('Oracle连接问题诊断');
DBMS_OUTPUT.PUT_LINE('==================');
-- 检查实例状态
SELECT status INTO v_instance_status
FROM v$instance;
SELECT open_mode INTO v_db_open_mode
FROM v$database;
DBMS_OUTPUT.PUT_LINE('实例状态: ' || v_instance_status);
DBMS_OUTPUT.PUT_LINE('数据库模式: ' || v_db_open_mode);
-- 检查会话信息
SELECT value INTO v_max_sessions
FROM v$parameter
WHERE name = 'sessions';
SELECT COUNT(*) INTO v_total_sessions
FROM v$session;
SELECT COUNT(*) INTO v_active_sessions
FROM v$session
WHERE status = 'ACTIVE';
DBMS_OUTPUT.PUT_LINE('');
DBMS_OUTPUT.PUT_LINE('会话统计:');
DBMS_OUTPUT.PUT_LINE('当前会话数: ' || v_total_sessions || ' / ' || v_max_sessions);
DBMS_OUTPUT.PUT_LINE('活跃会话数: ' || v_active_sessions);
DBMS_OUTPUT.PUT_LINE('会话使用率: ' || ROUND(v_total_sessions * 100 / v_max_sessions, 2) || '%');
-- 检查进程信息
SELECT value INTO v_max_processes
FROM v$parameter
WHERE name = 'processes';
SELECT COUNT(*) INTO v_process_count
FROM v$process
WHERE background IS NULL;
DBMS_OUTPUT.PUT_LINE('');
DBMS_OUTPUT.PUT_LINE('进程统计:');
DBMS_OUTPUT.PUT_LINE('当前进程数: ' || v_process_count || ' / ' || v_max_processes);
DBMS_OUTPUT.PUT_LINE('进程使用率: ' || ROUND(v_process_count * 100 / v_max_processes, 2) || '%');
-- 告警检查
IF v_total_sessions > v_max_sessions * 0.9 THEN
DBMS_OUTPUT.PUT_LINE('');
DBMS_OUTPUT.PUT_LINE('⚠️ 警告: 会话数接近上限，可能导致连接失败');
END IF;
IF v_process_count > v_max_processes * 0.9 THEN
DBMS_OUTPUT.PUT_LINE('');
DBMS_OUTPUT.PUT_LINE('⚠️ 警告: 进程数接近上限，可能导致连接失败');
END IF;
-- 检查阻塞会话
DBMS_OUTPUT.PUT_LINE('');
DBMS_OUTPUT.PUT_LINE('阻塞会话检查:');
FOR blocking_rec IN (
SELECT s1.sid as blocking_sid, s1.serial# as blocking_serial,
s1.username as blocking_user, s1.program as blocking_program,
s2.sid as blocked_sid, s2.serial# as blocked_serial,
s2.username as blocked_user,
s2.last_call_et as wait_time_sec
FROM v$session s1, v$session s2
WHERE s1.sid = s2.blocking_session
AND s2.blocking_session IS NOT NULL
ORDER BY s2.last_call_et DESC
) LOOP
DBMS_OUTPUT.PUT_LINE(
'阻塞: SID=' || blocking_rec.blocking_sid ||
' 用户=' || blocking_rec.blocking_user ||
' -> 被阻塞: SID=' || blocking_rec.blocked_sid ||
' 等待时间=' || blocking_rec.wait_time_sec || '秒'
);
END LOOP;
-- 检查监听器连接
DBMS_OUTPUT.PUT_LINE('');
DBMS_OUTPUT.PUT_LINE('网络连接检查:');
DBMS_OUTPUT.PUT_LINE('请在操作系统层面检查:');
DBMS_OUTPUT.PUT_LINE('1. lsnrctl status - 检查监听器状态');
DBMS_OUTPUT.PUT_LINE('2. netstat -an | grep 1521 - 检查端口监听');
DBMS_OUTPUT.PUT_LINE('3. tnsping <service_name> - 测试网络连通性');
END diagnose_connection_issues;
/
-- 清理僵死连接
CREATE OR REPLACE PROCEDURE cleanup_dead_connections AS
v_killed_count NUMBER := 0;
BEGIN
DBMS_OUTPUT.PUT_LINE('清理僵死连接');
DBMS_OUTPUT.PUT_LINE('==============');
-- 以下仅按空闲时长筛选并终止会话，不能证明连接僵死；可能影响连接池或未提交事务，不应未经业务确认直接执行
FOR dead_session IN (
SELECT sid, serial#, username, program, last_call_et,
status, machine
FROM v$session
WHERE status = 'INACTIVE'
AND last_call_et > 3600 -- 超过1小时无活动
AND username IS NOT NULL
AND username NOT IN ('SYS', 'SYSTEM', 'DBSNMP') -- 排除系统用户
ORDER BY last_call_et DESC
) LOOP
BEGIN
EXECUTE IMMEDIATE 'ALTER SYSTEM KILL SESSION ''' ||
dead_session.sid || ',' || dead_session.serial# || ''' IMMEDIATE';
DBMS_OUTPUT.PUT_LINE(
'已终止会话: SID=' || dead_session.sid ||
' 用户=' || dead_session.username ||
' 无活动时间=' || dead_session.last_call_et || '秒'
);
v_killed_count := v_killed_count + 1;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(
'终止会话失败: SID=' || dead_session.sid ||
' 错误=' || SQLERRM
);
END;
END LOOP;
DBMS_OUTPUT.PUT_LINE('');
DBMS_OUTPUT.PUT_LINE('共清理了 ' || v_killed_count || ' 个僵死连接');
END cleanup_dead_connections;
/
2. 监听器问题处理
#!/bin/bash
# 监听器故障排查脚本 - listener_troubleshoot.sh
# 设置环境变量
export ORACLE_HOME=/oracle/app/oracle/product/19c
export PATH=$ORACLE_HOME/bin:$PATH
echo "Oracle监听器故障排查"
echo "==================="
# 检查监听器状态
echo "1. 监听器状态检查"
echo "-----------------"
lsnrctl status
echo ""
# 检查端口占用
echo "2. 端口占用检查"
echo "--------------"
netstat -an | grep :1521
echo ""
# 检查监听器日志
echo "3. 监听器日志检查（最近50行）"
echo "-------------------------"
if [ -f $ORACLE_HOME/network/log/listener.log ]; then
tail -50 $ORACLE_HOME/network/log/listener.log | grep -i error
else
echo "监听器日志文件不存在"
fi
echo ""
# 检查tnsnames.ora配置
echo "4. TNS配置检查"
echo "-------------"
if [ -f $ORACLE_HOME/network/admin/tnsnames.ora ]; then
echo "tnsnames.ora 存在"
cat $ORACLE_HOME/network/admin/tnsnames.ora
else
echo "tnsnames.ora 文件不存在"
fi
echo ""
# 测试Oracle Net服务别名的解析与监听器可达性，不验证数据库认证或登录
echo "5. Oracle Net连通性测试（不代表数据库登录成功）"
echo "--------------"
tnsping BANKPROD
echo ""
# 检查防火墙
echo "6. 防火墙检查"
echo "------------"
if command -v firewall-cmd &> /dev/null; then
firewall-cmd --list-ports | grep 1521
elif command -v iptables &> /dev/null; then
iptables -L | grep 1521
else
echo "未检测到防火墙管理工具"
fi
echo ""
# 常见解决方案
echo "常见解决方案:"
echo "============"
echo "1. 重启监听器: lsnrctl stop && lsnrctl start"
echo "2. 重新注册服务: ALTER SYSTEM REGISTER;"
echo "3. 检查网络配置: ping <hostname>"
echo "4. 检查服务名: lsnrctl services"
echo "5. 重新加载配置: lsnrctl reload"
性能问题排查
1. 性能诊断工具
-- 性能问题快速诊断
CREATE OR REPLACE PROCEDURE diagnose_performance_issues AS
v_avg_response_time NUMBER;
v_cpu_usage NUMBER;
v_io_wait NUMBER;
v_top_wait_event VARCHAR2(100);
v_blocking_sessions NUMBER;
BEGIN
DBMS_OUTPUT.PUT_LINE('Oracle性能问题诊断');
DBMS_OUTPUT.PUT_LINE('==================');
-- 检查当前等待事件
DBMS_OUTPUT.PUT_LINE('1. 当前等待事件分析');
DBMS_OUTPUT.PUT_LINE('-------------------');
FOR wait_rec IN (
SELECT event,
COUNT(*) as session_count,
ROUND(AVG(wait_time_micro)/1000, 2) as avg_wait_ms
FROM v$session_wait
WHERE wait_class != 'Idle'
GROUP BY event
ORDER BY session_count DESC
FETCH FIRST 10 ROWS ONLY
) LOOP
DBMS_OUTPUT.PUT_LINE(
wait_rec.event || ': ' || wait_rec.session_count ||
' 个会话 (平均等待: ' || wait_rec.avg_wait_ms || 'ms)'
);
END LOOP;
-- 检查系统统计信息
DBMS_OUTPUT.PUT_LINE('');
DBMS_OUTPUT.PUT_LINE('2. 系统统计信息');
DBMS_OUTPUT.PUT_LINE('---------------');
FOR stat_rec IN (
SELECT name, value
FROM v$sysstat
WHERE name IN (
'physical reads', 'physical writes', 'logical reads',
'user commits', 'user rollbacks', 'parse count (total)',
'execute count', 'CPU used by this session'
)
ORDER BY name
) LOOP
DBMS_OUTPUT.PUT_LINE(stat_rec.name || ': ' || stat_rec.value);
END LOOP;
-- 检查SGA使用情况
DBMS_OUTPUT.PUT_LINE('');
DBMS_OUTPUT.PUT_LINE('3. SGA使用情况');
DBMS_OUTPUT.PUT_LINE('--------------');
FOR sga_rec IN (
SELECT component,
ROUND(current_size/1024/1024, 2) as size_mb,
ROUND(current_size * 100 / SUM(current_size) OVER(), 2) as pct
FROM v$memory_dynamic_components
WHERE current_size > 0
ORDER BY current_size DESC
) LOOP
DBMS_OUTPUT.PUT_LINE(
sga_rec.component || ': ' || sga_rec.size_mb ||
' MB (' || sga_rec.pct || '%)'
);
END LOOP;
-- 检查缓存命中率
DBMS_OUTPUT.PUT_LINE('');
DBMS_OUTPUT.PUT_LINE('4. 缓存命中率');
DBMS_OUTPUT.PUT_LINE('--------------');
DECLARE
v_buffer_hit_ratio NUMBER;
v_library_hit_ratio NUMBER;
v_dict_hit_ratio NUMBER;
BEGIN
-- Buffer Cache命中率
SELECT ROUND((1 - (physical_reads / (consistent_gets + db_block_gets))) * 100, 2)
INTO v_buffer_hit_ratio
FROM (
SELECT SUM(value) as physical_reads
FROM v$sysstat WHERE name = 'physical reads'
) p,
(
SELECT SUM(value) as consistent_gets
FROM v$sysstat WHERE name = 'consistent gets'
) c,
(
SELECT SUM(value) as db_block_gets
FROM v$sysstat WHERE name = 'db block gets'
) d;
DBMS_OUTPUT.PUT_LINE('Buffer Cache命中率: ' || v_buffer_hit_ratio || '%');
-- Library Cache命中率
SELECT ROUND(AVG(gethitratio * 100), 2)
INTO v_library_hit_ratio
FROM v$librarycache;
DBMS_OUTPUT.PUT_LINE('Library Cache命中率: ' || v_library_hit_ratio || '%');
-- Dictionary Cache命中率
SELECT ROUND((1 - SUM(getmisses)/SUM(gets)) * 100, 2)
INTO v_dict_hit_ratio
FROM v$rowcache;
DBMS_OUTPUT.PUT_LINE('Dictionary Cache命中率: ' || v_dict_hit_ratio || '%');
-- 性能评估
DBMS_OUTPUT.PUT_LINE('');
DBMS_OUTPUT.PUT_LINE('性能评估:');
IF v_buffer_hit_ratio < 90 THEN
DBMS_OUTPUT.PUT_LINE('⚠️ Buffer Cache命中率偏低，考虑增加DB_CACHE_SIZE');
END IF;
IF v_library_hit_ratio < 95 THEN
DBMS_OUTPUT.PUT_LINE('⚠️ Library Cache命中率偏低，考虑增加SHARED_POOL_SIZE');
END IF;
IF v_dict_hit_ratio < 95 THEN
DBMS_OUTPUT.PUT_LINE('⚠️ Dictionary Cache命中率偏低，检查SHARED_POOL_SIZE');
END IF;
END;
-- 检查顶级SQL
DBMS_OUTPUT.PUT_LINE('');
DBMS_OUTPUT.PUT_LINE('5. 性能最差的SQL（按CPU时间）');
DBMS_OUTPUT.PUT_LINE('----------------------------');
FOR sql_rec IN (
SELECT sql_id,
ROUND(cpu_time/1000000, 2) as cpu_seconds,
executions,
ROUND(cpu_time/executions/1000, 2) as avg_cpu_ms,
SUBSTR(sql_text, 1, 60) as sql_text_short
FROM v$sql
WHERE executions > 0
AND cpu_time > 0
ORDER BY cpu_time DESC
FETCH FIRST 10 ROWS ONLY
) LOOP
DBMS_OUTPUT.PUT_LINE(
'SQL_ID: ' || sql_rec.sql_id ||
' CPU: ' || sql_rec.cpu_seconds || 's' ||
' 执行: ' || sql_rec.executions || '次' ||
' 平均: ' || sql_rec.avg_cpu_ms || 'ms'
);
DBMS_OUTPUT.PUT_LINE(' ' || sql_rec.sql_text_short);
END LOOP;
END diagnose_performance_issues;
/
-- 性能监控报告生成
CREATE OR REPLACE PROCEDURE generate_performance_report AS
v_report CLOB;
v_start_time DATE := SYSDATE - 1/24; -- 最近1小时
v_end_time DATE := SYSDATE;
BEGIN
v_report := '数据库性能监控报告' || CHR(10) ||
'报告时间: ' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') || CHR(10) ||
'统计周期: ' || TO_CHAR(v_start_time, 'YYYY-MM-DD HH24:MI:SS') ||
' - ' || TO_CHAR(v_end_time, 'YYYY-MM-DD HH24:MI:SS') || CHR(10) ||
RPAD('=', 60, '=') || CHR(10) || CHR(10);
-- 数据库基本信息
v_report := v_report || '1. 数据库基本信息' || CHR(10);
FOR db_info IN (
SELECT name, open_mode, database_role,
TO_CHAR(created, 'YYYY-MM-DD') as created_date
FROM v$database
) LOOP
v_report := v_report ||
'数据库名: ' || db_info.name || CHR(10) ||
'打开模式: ' || db_info.open_mode || CHR(10) ||
'数据库角色: ' || db_info.database_role || CHR(10) ||
'创建日期: ' || db_info.created_date || CHR(10) || CHR(10);
END LOOP;
-- 实例信息
v_report := v_report || '2. 实例信息' || CHR(10);
FOR inst_info IN (
SELECT instance_name, host_name, version, status,
TO_CHAR(startup_time, 'YYYY-MM-DD HH24:MI:SS') as startup_time
FROM v$instance
) LOOP
v_report := v_report ||
'实例名: ' || inst_info.instance_name || CHR(10) ||
'主机名: ' || inst_info.host_name || CHR(10) ||
'版本: ' || inst_info.version || CHR(10) ||
'状态: ' || inst_info.status || CHR(10) ||
'启动时间: ' || inst_info.startup_time || CHR(10) || CHR(10);
END LOOP;
-- 会话统计
v_report := v_report || '3. 会话统计' || CHR(10);
FOR session_stats IN (
SELECT status, COUNT(*) as session_count
FROM v$session
GROUP BY status
ORDER BY session_count DESC
) LOOP
v_report := v_report ||
session_stats.status || ': ' || session_stats.session_count || ' 个' || CHR(10);
END LOOP;
v_report := v_report || CHR(10);
-- 等待事件统计
v_report := v_report || '4. 主要等待事件' || CHR(10);
FOR wait_stats IN (
SELECT event, total_waits, time_waited_micro/1000 as time_waited_ms
FROM v$system_event
WHERE wait_class != 'Idle'
AND total_waits > 0
ORDER BY time_waited_micro DESC
FETCH FIRST 10 ROWS ONLY
) LOOP
v_report := v_report ||
wait_stats.event || ': ' || wait_stats.total_waits ||
' 次等待, 总时间 ' || ROUND(wait_stats.time_waited_ms, 2) || 'ms' || CHR(10);
END LOOP;
v_report := v_report || CHR(10);
-- 表空间使用情况
v_report := v_report || '5. 表空间使用情况' || CHR(10);
FOR ts_usage IN (
SELECT tablespace_name,
ROUND(total_mb, 2) as total_mb,
ROUND(used_mb, 2) as used_mb,
ROUND(used_mb * 100 / total_mb, 2) as usage_pct
FROM (
SELECT df.tablespace_name,
SUM(df.bytes)/1024/1024 as total_mb,
(SUM(df.bytes) - NVL(SUM(fs.bytes), 0))/1024/1024 as used_mb
FROM dba_data_files df
LEFT JOIN (
SELECT file_id, SUM(bytes) AS bytes
FROM dba_free_space
GROUP BY file_id
) fs ON df.file_id = fs.file_id
GROUP BY df.tablespace_name
)
ORDER BY usage_pct DESC
) LOOP
v_report := v_report ||
ts_usage.tablespace_name || ': ' || ts_usage.used_mb || '/' ||
ts_usage.total_mb || ' MB (' || ts_usage.usage_pct || '%)' || CHR(10);
END LOOP;
-- 输出报告
DBMS_OUTPUT.PUT_LINE(v_report);
END generate_performance_report;
/
空间问题处理
1. 表空间监控与扩展
-- 表空间使用情况监控
CREATE OR REPLACE PROCEDURE monitor_tablespace_usage AS
v_alert_threshold NUMBER := 85; -- 85%使用率告警
v_critical_threshold NUMBER := 95; -- 95%使用率严重告警
BEGIN
DBMS_OUTPUT.PUT_LINE('表空间使用情况监控');
DBMS_OUTPUT.PUT_LINE('==================');
DBMS_OUTPUT.PUT_LINE(RPAD('表空间名称', 25) || RPAD('总大小(MB)', 12) ||
RPAD('已用(MB)', 12) || RPAD('可用(MB)', 12) || '使用率(%)');
DBMS_OUTPUT.PUT_LINE(RPAD('-', 75, '-'));
FOR ts_rec IN (
SELECT df.tablespace_name,
ROUND(SUM(df.bytes)/1024/1024, 2) as total_mb,
ROUND((SUM(df.bytes) - NVL(SUM(fs.bytes), 0))/1024/1024, 2) as used_mb,
ROUND(NVL(SUM(fs.bytes), 0)/1024/1024, 2) as free_mb,
ROUND((SUM(df.bytes) - NVL(SUM(fs.bytes), 0)) * 100 / SUM(df.bytes), 2) as usage_pct,
COUNT(df.file_id) as file_count
FROM dba_data_files df
LEFT JOIN (
SELECT file_id, SUM(bytes) AS bytes
FROM dba_free_space
GROUP BY file_id
) fs ON df.file_id = fs.file_id
GROUP BY df.tablespace_name
ORDER BY usage_pct DESC
) LOOP
DBMS_OUTPUT.PUT_LINE(
RPAD(ts_rec.tablespace_name, 25) ||
RPAD(ts_rec.total_mb, 12) ||
RPAD(ts_rec.used_mb, 12) ||
RPAD(ts_rec.free_mb, 12) ||
ts_rec.usage_pct ||
CASE
WHEN ts_rec.usage_pct >= v_critical_threshold THEN ' ⚠️ CRITICAL'
WHEN ts_rec.usage_pct >= v_alert_threshold THEN ' ⚠️ WARNING'
ELSE ''
END
);
-- 自动扩展建议
IF ts_rec.usage_pct >= v_critical_threshold THEN
DBMS_OUTPUT.PUT_LINE(' 建议评估扩展: ALTER DATABASE DATAFILE ... RESIZE <目标总大小>M; -- 目标总大小为当前大小加拟增加容量，需确认存储与文件限制');
ELSIF ts_rec.usage_pct >= v_alert_threshold THEN
DBMS_OUTPUT.PUT_LINE(' 建议评估预防性扩展: ALTER DATABASE DATAFILE ... RESIZE <目标总大小>M; -- RESIZE指定扩展后的总大小，不使用+200M');
END IF;
END LOOP;
-- 检查临时表空间
DBMS_OUTPUT.PUT_LINE('');
DBMS_OUTPUT.PUT_LINE('临时表空间使用情况:');
DBMS_OUTPUT.PUT_LINE('------------------');
FOR temp_rec IN (
SELECT tablespace_name,
ROUND(SUM(bytes_used)/1024/1024, 2) as used_mb,
ROUND(SUM(bytes_free)/1024/1024, 2) as free_mb,
ROUND(SUM(bytes_used) * 100 / (SUM(bytes_used) + SUM(bytes_free)), 2) as usage_pct
FROM v$temp_space_header
GROUP BY tablespace_name
ORDER BY usage_pct DESC
) LOOP
DBMS_OUTPUT.PUT_LINE(
temp_rec.tablespace_name || ': ' || temp_rec.used_mb || 'MB 已用, ' ||
temp_rec.free_mb || 'MB 可用 (' || temp_rec.usage_pct || '%)'
);
END LOOP;
END monitor_tablespace_usage;
/
-- 自动表空间扩展
CREATE OR REPLACE PROCEDURE auto_extend_tablespace(
p_tablespace_name VARCHAR2,
p_extend_size_mb NUMBER DEFAULT 500
) AS
v_datafile_name VARCHAR2(500);
v_current_size_mb NUMBER;
v_new_size_mb NUMBER;
v_extend_sql VARCHAR2(1000);
BEGIN
-- 获取表空间的第一个数据文件
SELECT file_name, ROUND(bytes/1024/1024, 2)
INTO v_datafile_name, v_current_size_mb
FROM dba_data_files
WHERE tablespace_name = UPPER(p_tablespace_name)
ORDER BY file_id
FETCH FIRST 1 ROWS ONLY;
v_new_size_mb := v_current_size_mb + p_extend_size_mb;
DBMS_OUTPUT.PUT_LINE('扩展表空间: ' || p_tablespace_name);
DBMS_OUTPUT.PUT_LINE('数据文件: ' || v_datafile_name);
DBMS_OUTPUT.PUT_LINE('当前大小: ' || v_current_size_mb || ' MB');
DBMS_OUTPUT.PUT_LINE('扩展大小: ' || p_extend_size_mb || ' MB');
DBMS_OUTPUT.PUT_LINE('新大小: ' || v_new_size_mb || ' MB');
-- 生成扩展命令
v_extend_sql := 'ALTER DATABASE DATAFILE ''' || v_datafile_name ||
''' RESIZE ' || v_new_size_mb || 'M';
DBMS_OUTPUT.PUT_LINE('执行命令: ' || v_extend_sql);
-- 执行扩展
BEGIN
EXECUTE IMMEDIATE v_extend_sql;
DBMS_OUTPUT.PUT_LINE('✓ 表空间扩展成功');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('✗ 表空间扩展失败: ' || SQLERRM);
-- 尝试添加新数据文件
DECLARE
v_new_datafile VARCHAR2(500);
v_add_sql VARCHAR2(1000);
BEGIN
v_new_datafile := REPLACE(v_datafile_name, '.dbf', '_' ||
TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MI') || '.dbf');
v_add_sql := 'ALTER TABLESPACE ' || p_tablespace_name ||
' ADD DATAFILE ''' || v_new_datafile ||
''' SIZE ' || p_extend_size_mb || 'M AUTOEXTEND ON NEXT 100M MAXSIZE 10G';
DBMS_OUTPUT.PUT_LINE('尝试添加新数据文件: ' || v_add_sql);
EXECUTE IMMEDIATE v_add_sql;
DBMS_OUTPUT.PUT_LINE('✓ 新数据文件添加成功');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('✗ 添加数据文件失败: ' || SQLERRM);
RAISE;
END;
END;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('错误: 表空间 ' || p_tablespace_name || ' 不存在');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('扩展表空间时发生错误: ' || SQLERRM);
RAISE;
END auto_extend_tablespace;
/
锁等待问题处理
1. 锁等待分析与处理
-- 锁等待问题诊断
CREATE OR REPLACE PROCEDURE diagnose_lock_issues AS
v_blocking_count NUMBER;
v_deadlock_count NUMBER;
BEGIN
DBMS_OUTPUT.PUT_LINE('锁等待问题诊断');
DBMS_OUTPUT.PUT_LINE('==============');
-- 统计阻塞情况
SELECT COUNT(*) INTO v_blocking_count
FROM v$session
WHERE blocking_session IS NOT NULL;
DBMS_OUTPUT.PUT_LINE('当前阻塞会话数: ' || v_blocking_count);
IF v_blocking_count > 0 THEN
DBMS_OUTPUT.PUT_LINE('');
DBMS_OUTPUT.PUT_LINE('阻塞会话详情:');
DBMS_OUTPUT.PUT_LINE(RPAD('阻塞SID', 10) || RPAD('用户', 15) || RPAD('等待对象', 20) ||
RPAD('等待时间', 10) || '状态');
DBMS_OUTPUT.PUT_LINE(RPAD('-', 70, '-'));
FOR lock_rec IN (
SELECT s1.sid as blocking_sid,
s1.username as blocking_user,
s1.status as blocking_status,
s1.machine as blocking_machine,
s2.sid as blocked_sid,
s2.username as blocked_user,
s2.last_call_et as wait_seconds,
l.type as lock_type,
o.object_name
FROM v$session s1, v$session s2, v$lock l, dba_objects o
WHERE s1.sid = s2.blocking_session
AND s1.sid = l.sid
AND l.id1 = o.object_id(+)
ORDER BY s2.last_call_et DESC
) LOOP
DBMS_OUTPUT.PUT_LINE(
RPAD(lock_rec.blocking_sid, 10) ||
RPAD(NVL(lock_rec.blocking_user, 'N/A'), 15) ||
RPAD(NVL(lock_rec.object_name, 'N/A'), 20) ||
RPAD(lock_rec.wait_seconds, 10) ||
lock_rec.blocking_status
);
DBMS_OUTPUT.PUT_LINE(
' -> 被阻塞: SID=' || lock_rec.blocked_sid ||
', 用户=' || NVL(lock_rec.blocked_user, 'N/A')
);
END LOOP;
DBMS_OUTPUT.PUT_LINE('');
DBMS_OUTPUT.PUT_LINE('处理建议:');
DBMS_OUTPUT.PUT_LINE('1. 检查阻塞会话是否在执行长时间操作');
DBMS_OUTPUT.PUT_LINE('2. 联系应用用户确认是否可以终止');
DBMS_OUTPUT.PUT_LINE('3. 必要时使用 ALTER SYSTEM KILL SESSION 终止阻塞会话');
ELSE
DBMS_OUTPUT.PUT_LINE('✓ 当前无阻塞会话');
END IF;
-- 检查锁竞争热点
DBMS_OUTPUT.PUT_LINE('');
DBMS_OUTPUT.PUT_LINE('锁竞争热点对象:');
DBMS_OUTPUT.PUT_LINE('----------------');
FOR hotspot_rec IN (
SELECT o.object_name, o.object_type,
COUNT(*) as lock_count,
SUM(CASE WHEN l.block = 1 THEN 1 ELSE 0 END) as blocking_locks
FROM v$lock l, dba_objects o
WHERE l.id1 = o.object_id
AND l.type IN ('TM', 'TX')
GROUP BY o.object_name, o.object_type
HAVING COUNT(*) > 5
ORDER BY lock_count DESC
) LOOP
DBMS_OUTPUT.PUT_LINE(
hotspot_rec.object_name || ' (' || hotspot_rec.object_type || '): ' ||
hotspot_rec.lock_count || ' 个锁, ' || hotspot_rec.blocking_locks || ' 个阻塞锁'
);
END LOOP;
-- 检查死锁历史
DBMS_OUTPUT.PUT_LINE('');
DBMS_OUTPUT.PUT_LINE('最近死锁检查:');
DBMS_OUTPUT.PUT_LINE('--------------');
DBMS_OUTPUT.PUT_LINE('请检查告警日志中的 ORA-00060 死锁错误');
DBMS_OUTPUT.PUT_LINE('死锁追踪文件位置: $ORACLE_BASE/diag/rdbms/.../trace/');
END diagnose_lock_issues;
/
-- 自动处理长时间阻塞
CREATE OR REPLACE PROCEDURE auto_resolve_blocking(
p_max_wait_seconds NUMBER DEFAULT 300, -- 5分钟
p_auto_kill CHAR(1) DEFAULT 'N'
) AS
v_killed_count NUMBER := 0;
BEGIN
DBMS_OUTPUT.PUT_LINE('自动处理长时间阻塞');
DBMS_OUTPUT.PUT_LINE('==================');
DBMS_OUTPUT.PUT_LINE('最大等待时间阈值: ' || p_max_wait_seconds || ' 秒');
DBMS_OUTPUT.PUT_LINE('自动终止模式: ' || CASE WHEN p_auto_kill = 'Y' THEN '开启' ELSE '关闭' END);
DBMS_OUTPUT.PUT_LINE('');
FOR blocking_rec IN (
SELECT s1.sid as blocking_sid,
s1.serial# as blocking_serial,
s1.username as blocking_user,
s1.program as blocking_program,
s1.machine as blocking_machine,
COUNT(s2.sid) as blocked_session_count,
MAX(s2.last_call_et) as max_wait_seconds
FROM v$session s1, v$session s2
WHERE s1.sid = s2.blocking_session
GROUP BY s1.sid, s1.serial#, s1.username, s1.program, s1.machine
HAVING MAX(s2.last_call_et) > p_max_wait_seconds
ORDER BY MAX(s2.last_call_et) DESC
) LOOP
DBMS_OUTPUT.PUT_LINE(
'发现长时间阻塞会话: SID=' || blocking_rec.blocking_sid ||
', 用户=' || blocking_rec.blocking_user ||
', 阻塞了' || blocking_rec.blocked_session_count || '个会话' ||
', 最长等待' || blocking_rec.max_wait_seconds || '秒'
);
DBMS_OUTPUT.PUT_LINE(' 程序: ' || blocking_rec.blocking_program);
DBMS_OUTPUT.PUT_LINE(' 机器: ' || blocking_rec.blocking_machine);
IF p_auto_kill = 'Y' THEN
BEGIN
EXECUTE IMMEDIATE 'ALTER SYSTEM KILL SESSION ''' ||
blocking_rec.blocking_sid || ',' ||
blocking_rec.blocking_serial || ''' IMMEDIATE';
DBMS_OUTPUT.PUT_LINE(' ✓ 会话已被自动终止');
v_killed_count := v_killed_count + 1;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(' ✗ 终止会话失败: ' || SQLERRM);
END;
ELSE
DBMS_OUTPUT.PUT_LINE(' 建议手动终止: ALTER SYSTEM KILL SESSION ''' ||
blocking_rec.blocking_sid || ',' ||
blocking_rec.blocking_serial || ''';');
END IF;
DBMS_OUTPUT.PUT_LINE('');
END LOOP;
IF v_killed_count > 0 THEN
DBMS_OUTPUT.PUT_LINE('共自动终止了 ' || v_killed_count || ' 个阻塞会话');
ELSE
DBMS_OUTPUT.PUT_LINE('未发现需要处理的长时间阻塞');
END IF;
END auto_resolve_blocking;
/
数据损坏处理
1. 数据完整性检查
-- 数据完整性检查工具
CREATE OR REPLACE PROCEDURE check_data_integrity AS
v_corruption_found BOOLEAN := FALSE;
v_total_errors NUMBER := 0;
BEGIN
DBMS_OUTPUT.PUT_LINE('数据完整性检查');
DBMS_OUTPUT.PUT_LINE('==============');
-- 检查数据文件状态
DBMS_OUTPUT.PUT_LINE('1. 数据文件状态检查');
DBMS_OUTPUT.PUT_LINE('------------------');
FOR df_rec IN (
SELECT d.name AS file_name, t.name AS tablespace_name, d.status, d.enabled
FROM v$datafile d
JOIN v$tablespace t ON d.ts# = t.ts# AND d.con_id = t.con_id
WHERE d.status NOT IN ('ONLINE', 'SYSTEM')
OR d.enabled NOT IN ('READ WRITE', 'READ ONLY')
) LOOP
DBMS_OUTPUT.PUT_LINE(
'⚠️ 数据文件异常: ' || df_rec.file_name ||
' (状态: ' || df_rec.status || ', 启用: ' || df_rec.enabled || ')'
);
v_corruption_found := TRUE;
v_total_errors := v_total_errors + 1;
END LOOP;
IF NOT v_corruption_found THEN
DBMS_OUTPUT.PUT_LINE('✓ 所有数据文件状态正常');
END IF;
-- 检查控制文件
DBMS_OUTPUT.PUT_LINE('');
DBMS_OUTPUT.PUT_LINE('2. 控制文件检查');
DBMS_OUTPUT.PUT_LINE('---------------');
FOR cf_rec IN (
SELECT name, status
FROM v$controlfile
WHERE status IS NOT NULL
) LOOP
DBMS_OUTPUT.PUT_LINE('⚠️ 控制文件异常: ' || cf_rec.name || ' (状态: ' || cf_rec.status || ')');
v_total_errors := v_total_errors + 1;
END LOOP;
-- 检查重做日志
DBMS_OUTPUT.PUT_LINE('');
DBMS_OUTPUT.PUT_LINE('3. 重做日志检查');
DBMS_OUTPUT.PUT_LINE('---------------');
FOR log_rec IN (
SELECT group#, thread#, sequence#, status, archived
FROM v$log
WHERE status NOT IN ('CURRENT', 'ACTIVE', 'INACTIVE')
) LOOP
DBMS_OUTPUT.PUT_LINE(
'⚠️ 重做日志异常: 组' || log_rec.group# ||
' (状态: ' || log_rec.status || ')'
);
v_total_errors := v_total_errors + 1;
END LOOP;
-- 检查归档日志间隙
DBMS_OUTPUT.PUT_LINE('');
DBMS_OUTPUT.PUT_LINE('4. 归档日志间隙检查');
DBMS_OUTPUT.PUT_LINE('------------------');
BEGIN
FOR gap_rec IN (
SELECT thread#, low_sequence#, high_sequence#
FROM v$archive_gap
) LOOP
DBMS_OUTPUT.PUT_LINE(
'⚠️ 归档日志间隙: 线程' || gap_rec.thread# ||
' 序列号 ' || gap_rec.low_sequence# || '-' || gap_rec.high_sequence#
);
v_total_errors := v_total_errors + 1;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
NULL; -- 可能没有配置Data Guard
END;
-- 检查无效对象
DBMS_OUTPUT.PUT_LINE('');
DBMS_OUTPUT.PUT_LINE('5. 无效对象检查');
DBMS_OUTPUT.PUT_LINE('---------------');
FOR invalid_rec IN (
SELECT object_type, COUNT(*) as invalid_count
FROM dba_objects
WHERE status = 'INVALID'
AND owner NOT IN ('SYS', 'SYSTEM', 'CTXSYS', 'MDSYS', 'OLAPSYS', 'ORDSYS', 'WMSYS')
GROUP BY object_type
ORDER BY invalid_count DESC
) LOOP
DBMS_OUTPUT.PUT_LINE(
'⚠️ 无效对象: ' || invalid_rec.object_type ||
' (' || invalid_rec.invalid_count || ' 个)'
);
v_total_errors := v_total_errors + 1;
END LOOP;
-- 检查约束状态
DBMS_OUTPUT.PUT_LINE('');
DBMS_OUTPUT.PUT_LINE('6. 约束状态检查');
DBMS_OUTPUT.PUT_LINE('---------------');
FOR cons_rec IN (
SELECT constraint_type, status, COUNT(*) as constraint_count
FROM dba_constraints
WHERE owner IN (
SELECT username FROM dba_users
WHERE default_tablespace NOT IN ('SYSTEM', 'SYSAUX')
)
AND status != 'ENABLED'
GROUP BY constraint_type, status
ORDER BY constraint_count DESC
) LOOP
DBMS_OUTPUT.PUT_LINE(
'⚠️ 约束状态异常: ' || cons_rec.constraint_type ||
' 状态=' || cons_rec.status ||
' (' || cons_rec.constraint_count || ' 个)'
);
v_total_errors := v_total_errors + 1;
END LOOP;
-- 总结
DBMS_OUTPUT.PUT_LINE('');
DBMS_OUTPUT.PUT_LINE('检查总结:');
DBMS_OUTPUT.PUT_LINE('========');
IF v_total_errors = 0 THEN
DBMS_OUTPUT.PUT_LINE('本脚本覆盖的状态与元数据检查未报告异常；此结果不等于数据完整性验证通过，仍需适用的RMAN验证及损坏检查。');
ELSE
DBMS_OUTPUT.PUT_LINE('✗ 本脚本有 ' || v_total_errors || ' 个状态或元数据检查项报告异常；这些异常不等同于数据损坏。');
DBMS_OUTPUT.PUT_LINE('');
DBMS_OUTPUT.PUT_LINE('建议操作:');
DBMS_OUTPUT.PUT_LINE('1. 立即检查告警日志和跟踪文件');
DBMS_OUTPUT.PUT_LINE('2. 运行 RMAN VALIDATE DATABASE 命令');
DBMS_OUTPUT.PUT_LINE('3. 确认损坏类型与恢复方案后，再评估DBMS_REPAIR的适用性；该包不是通用无损修复工具');
DBMS_OUTPUT.PUT_LINE('4. 必要时联系Oracle技术支持');
END IF;
END check_data_integrity;
/
-- RMAN验证脚本生成
CREATE OR REPLACE PROCEDURE generate_rman_validate_script AS
v_script CLOB;
BEGIN
v_script := '#!/bin/bash' || CHR(10) ||
'# RMAN数据验证脚本' || CHR(10) ||
'# 生成时间: ' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') || CHR(10) ||
CHR(10) ||
'export ORACLE_HOME=/oracle/app/oracle/product/19c' || CHR(10) ||
'export ORACLE_SID=BANKPROD' || CHR(10) ||
'export PATH=$ORACLE_HOME/bin:$PATH' || CHR(10) ||
CHR(10) ||
'LOG_FILE="/oracle/logs/rman_validate_$(date +%Y%m%d_%H%M).log"' || CHR(10) ||
CHR(10) ||
'echo "开始RMAN数据验证: $(date)" > $LOG_FILE' || CHR(10) ||
CHR(10) ||
'$ORACLE_HOME/bin/rman target / << EOF >> $LOG_FILE 2>&1' || CHR(10) ||
CHR(10) ||
'# 验证数据库' || CHR(10) ||
'VALIDATE DATABASE;' || CHR(10) ||
CHR(10) ||
'# 验证备份集' || CHR(10) ||
'VALIDATE BACKUPSET ALL;' || CHR(10) ||
CHR(10) ||
'# 检查数据文件' || CHR(10) ||
'VALIDATE DATAFILE ALL;' || CHR(10) ||
CHR(10) ||
'# 检查控制文件' || CHR(10) ||
'VALIDATE CURRENT CONTROLFILE;' || CHR(10) ||
CHR(10) ||
'# 检查SPFILE' || CHR(10) ||
'VALIDATE SPFILE;' || CHR(10) ||
CHR(10) ||
'exit;' || CHR(10) ||
'EOF' || CHR(10) ||
CHR(10) ||
'echo "RMAN验证完成: $(date)" >> $LOG_FILE' || CHR(10) ||
CHR(10) ||
'# 检查验证结果' || CHR(10) ||
'if grep -i "error\|fail" $LOG_FILE; then' || CHR(10) ||
' echo "发现验证错误，请检查日志文件: $LOG_FILE"' || CHR(10) ||
' exit 1' || CHR(10) ||
'else' || CHR(10) ||
' echo "RMAN验证成功完成"' || CHR(10) ||
' exit 0' || CHR(10) ||
'fi' || CHR(10);
DBMS_OUTPUT.PUT_LINE(v_script);
END generate_rman_validate_script;
/
Oracle故障排查是一个系统性的过程，需要丰富的经验和深入的技术理解。在银行环境中，快速准确的故障诊断和处理直接关系到业务连续性。建议建立完善的监控体系、故障处理流程和知识库，确保问题能够及时发现和解决。同时，定期进行故障演练和技能培训，提升团队的应急处理能力。