Oracle闪回区与归档的关系解析及协同运维指南

-- 查看闪回区配置
SELECT 
    name,
    space_limit/1024/1024/1024 as space_limit_gb,
    space_used/1024/1024/1024 as space_used_gb,
    space_reclaimable/1024/1024/1024 as space_reclaimable_gb,
    number_of_files
FROM v$recovery_file_dest;

-- 配置闪回区参数
ALTER SYSTEM SET 
    db_recovery_file_dest_size = 500G,
    db_recovery_file_dest = '+FRA_DISK_GROUP' 
    SCOPE=BOTH;
 
-- 设置归档保留策略
RMAN> CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 2 TIMES TO DEVICE TYPE DISK;

-- 查看归档状态
SELECT 
    thread#,
    sequence#,
    name,
    archived,
    status,
    completion_time
FROM v$archived_log 
WHERE first_time >= SYSDATE-1
ORDER BY sequence#;

-- 优化归档性能参数
ALTER SYSTEM SET 
    log_archive_max_processes = 8,
    log_archive_buffer_size = 1048576,
    log_archive_buffers = 4
    SCOPE=BOTH;
 
-- 监控归档性能
SELECT 
    process,
    status,
    log_sequence,
    round((blocks*block_size)/1024/1024,2) as mb_archived
FROM v$archive_processes;

-- 监控协同空间使用
SELECT 
    file_type,
    percent_space_used,
    percent_space_reclaimable,
    number_of_files
FROM v$flash_recovery_area_usage
WHERE file_type IN ('ARCHIVED LOG', 'BACKUP PIECE', 'FLASHBACK LOG')
ORDER BY percent_space_used DESC;

-- TRAE IDE智能提示：输入"flashback"自动补全
FLASHBACK TABLE employees TO TIMESTAMP 
    (SYSTIMESTAMP - INTERVAL '2' HOUR);
 
-- TRAE IDE语法检查：实时检测归档路径配置
ALTER SYSTEM SET 
    log_archive_dest_1 = 'LOCATION=+FRA_DISK_GROUP MANDORY REOPEN=60'
    SCOPE=BOTH;

#!/bin/bash
# TRAE IDE生成的归档空间监控脚本
 
# 获取闪回区使用率
FRA_USAGE=$(sqlplus -s / as sysdba <<EOF
SET PAGESIZE 0 FEEDBACK OFF VERIFY OFF HEADING OFF ECHO OFF
SELECT ROUND((space_used/space_limit)*100,2) 
FROM v\$recovery_file_dest;
EXIT;
EOF
)
 
# 告警阈值判断
if (( $(echo "$FRA_USAGE > 85" | bc -l) )); then
    echo "警告：闪回区使用率已达 ${FRA_USAGE}%"
    # 调用TRAE IDE集成的企业微信API发送告警
    curl -X POST "https://qyapi.weixin.qq.com/cgi-bin/webhook/send?key=${WEBHOOK_KEY}" \
         -H "Content-Type: application/json" \
         -d "{\"msgtype\":\"text\",\"text\":{\"content\":\"Oracle FRA告警：使用率${FRA_USAGE}%\"}}"
fi

// TRAE IDE监控面板配置
const fraMonitor = {
    metrics: [
        'v$recovery_file_dest.space_used',
        'v$recovery_file_dest.space_limit', 
        'v$archive_dest.status',
        'v$archived_log.archived'
    ],
    alerts: {
        fra_usage_threshold: 85,
        archive_lag_threshold: 300,
        failed_archive_threshold: 1
    },
    // 集成TRAE IDE智能告警
    ai_anomaly_detection: true
};

-- 步骤1：验证闪回可用性
SELECT 
    oldest_flashback_scn,
    oldest_flashback_time,
    retention_target,
    flashback_size/1024/1024 as mb_used
FROM v$flashback_database_log;
 
-- 步骤2：创建恢复点（TRAE IDE智能提示）
CREATE RESTORE POINT before_trading_update 
    GUARANTEE FLASHBACK DATABASE;
 
-- 步骤3：闪回表恢复（TRAE IDE代码生成）
FLASHBACK TABLE trading_records TO TIMESTAMP 
    (SYSTIMESTAMP - INTERVAL '2' HOUR);
 
-- 步骤4：验证数据一致性
SELECT COUNT(*), SUM(amount), MAX(trade_time)
FROM trading_records 
WHERE trade_time >= SYSTIMESTAMP - INTERVAL '2' HOUR;

-- TRAE IDE自动生成的性能监控SQL
SELECT 
    operation,
    status,
    to_char(start_time,'yyyy-mm-dd hh24:mi:ss') as start_time,
    to_char(end_time,'yyyy-mm-dd hh24:mi:ss') as end_time,
    round((end_time-start_time)*24*60,2) as minutes_used
FROM v$flashback_database_stat 
WHERE start_time >= SYSDATE-1
ORDER BY start_time DESC;

-- TRAE IDE智能监控视图
CREATE OR REPLACE VIEW fra_health_check AS
SELECT 
    a.name,
    ROUND((a.space_used/a.space_limit)*100,2) as usage_pct,
    b.archived,
    b.applied,
    b.status,
    CASE 
        WHEN (a.space_used/a.space_limit)*100 > 90 THEN 'CRITICAL'
        WHEN (a.space_used/a.space_limit)*100 > 80 THEN 'WARNING'
        ELSE 'NORMAL'
    END as health_status
FROM v$recovery_file_dest a, v$archive_dest_status b
WHERE a.name = b.destination;

# TRAE IDE自动化演练脚本
#!/bin/bash
echo "开始Oracle灾难恢复演练..."
 
# 1. 创建测试表
sqlplus -s / as sysdba <<EOF
CREATE TABLE test_flashback AS SELECT * FROM dba_objects WHERE ROWNUM <= 1000;
EOF
 
# 2. 记录当前SCN
current_scn=$(sqlplus -s / as sysdba <<EOF
SELECT current_scn FROM v\$database;
EOF
)
 
# 3. 模拟数据损坏
sqlplus -s / as sysdba <<EOF
DELETE FROM test_flashback WHERE ROWNUM <= 500;
COMMIT;
EOF
 
# 4. 执行闪回恢复
sqlplus -s / as sysdba <<EOF
FLASHBACK TABLE test_flashback TO SCN ${current_scn};
EOF
 
echo "演练完成，验证数据一致性..."