Oracle数据库状态信息自动化收集脚本

#!/bin/bash

# 配置变量
ORACLE_SID=CDB
ORACLE_HOME=/u01/app/oracle/product/19c/dbhome_1
PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_SID ORACLE_HOME PATH

# 数据库登录信息
DB_USER=lcsdb
DB_PASS=123456
PDB_NAME=FS3_LCS

# 输出文件
OUTPUT_DIR=/path/to/output/directory
DATE=$(date +'%Y%m%d_%H%M%S')
OUTPUT_FILE="$OUTPUT_DIR/db_monitor_$DATE.txt"

# 创建输出目录
mkdir -p $OUTPUT_DIR

# 开始记录
echo "Oracle Database Monitoring Report - $DATE" > $OUTPUT_FILE
echo "----------------------------------------" >> $OUTPUT_FILE
echo "" >> $OUTPUT_FILE

# 1. 数据库运行状态
echo "1. Database Status for PDB $PDB_NAME:" >> $OUTPUT_FILE
sqlplus -s $DB_USER/$DB_PASS@$PDB_NAME <<EOF >> $OUTPUT_FILE
SET LINESIZE 200
SET PAGESIZE 50
SELECT 'Database Status:' STATUS FROM v\$instance;
EOF
echo "" >> $OUTPUT_FILE

# 2. 内存使用情况
echo "2. Memory Usage for PDB $PDB_NAME:" >> $OUTPUT_FILE
sqlplus -s $DB_USER/$DB_PASS@$PDB_NAME <<EOF >> $OUTPUT_FILE
SET LINESIZE 200
SET PAGESIZE 50
SELECT * FROM v\$sgainfo;
EOF
echo "" >> $OUTPUT_FILE

# 3. CPU使用情况
echo "3. CPU Usage for PDB $PDB_NAME:" >> $OUTPUT_FILE
sqlplus -s $DB_USER/$DB_PASS@$PDB_NAME <<EOF >> $OUTPUT_FILE
SET LINESIZE 200
SET PAGESIZE 50
SELECT * FROM v\$osstat WHERE stat_name IN ('NUM_CPU_CORES', 'NUM_CPU_SOCKETS', 'NUM_CPU_THREADS');
EOF
echo "" >> $OUTPUT_FILE

# 4. 初始化参数
echo "4. Initialization Parameters for PDB $PDB_NAME:" >> $OUTPUT_FILE
sqlplus -s $DB_USER/$DB_PASS@$PDB_NAME <<EOF >> $OUTPUT_FILE
SET LINESIZE 200
SET PAGESIZE 50
SHOW PARAMETER;
EOF
echo "" >> $OUTPUT_FILE

# 5. 数据文件
echo "5. Data Files for PDB $PDB_NAME:" >> $OUTPUT_FILE
sqlplus -s $DB_USER/$DB_PASS@$PDB_NAME <<EOF >> $OUTPUT_FILE
SET LINESIZE 200
SET PAGESIZE 50
SELECT file_name, bytes/1024/1024 AS size_mb FROM dba_data_files;
EOF
echo "" >> $OUTPUT_FILE

# 6. 日志文件
echo "6. Log Files for PDB $PDB_NAME:" >> $OUTPUT_FILE
sqlplus -s $DB_USER/$DB_PASS@$PDB_NAME <<EOF >> $OUTPUT_FILE
SET LINESIZE 200
SET PAGESIZE 50
SELECT member FROM v\$logfile;
EOF
echo "" >> $OUTPUT_FILE

# 7. 告警日志
echo "7. Alert Log for PDB $PDB_NAME:" >> $OUTPUT_FILE
ALERT_LOG="$ORACLE_HOME/log/alert_${ORACLE_SID}.log"
if [ -f "$ALERT_LOG" ]; then
    tail -n 50 "$ALERT_LOG" >> $OUTPUT_FILE
else
    echo "Alert log not found at $ALERT_LOG" >> $OUTPUT_FILE
fi
echo "" >> $OUTPUT_FILE

# 8. 监控
echo "8. Monitoring for PDB $PDB_NAME:" >> $OUTPUT_FILE
sqlplus -s $DB_USER/$DB_PASS@$PDB_NAME <<EOF >> $OUTPUT_FILE
SET LINESIZE 200
SET PAGESIZE 50
SELECT * FROM v\$monitoring_info;
EOF
echo "" >> $OUTPUT_FILE

# 9. 表空间剩余大小
echo "9. Tablespace Usage for PDB $PDB_NAME:" >> $OUTPUT_FILE
sqlplus -s $DB_USER/$DB_PASS@$PDB_NAME <<EOF >> $OUTPUT_FILE
SET LINESIZE 200
SET PAGESIZE 50
SELECT tablespace_name, 
       ROUND(SUM(bytes) / 1024 / 1024, 2) AS total_mb,
       ROUND(SUM(free_bytes) / 1024 / 1024, 2) AS free_mb
FROM (SELECT tablespace_name, bytes AS total_bytes, 0 AS free_bytes
      FROM dba_data_files
      UNION ALL
      SELECT tablespace_name, 0 AS total_bytes, bytes AS free_bytes
      FROM dba_free_space)
GROUP BY tablespace_name;
EOF
echo "" >> $OUTPUT_FILE

# 10. 数据一致性检查
echo "10. Data Consistency Check for PDB $PDB_NAME:" >> $OUTPUT_FILE
sqlplus -s $DB_USER/$DB_PASS@$PDB_NAME <<EOF >> $OUTPUT_FILE
SET LINESIZE 200
SET PAGESIZE 50
SELECT * FROM dba_repair_summary;
EOF
echo "" >> $OUTPUT_FILE

# 11. 表和索引碎片
echo "11. Table and Index Fragmentation for PDB $PDB_NAME:" >> $OUTPUT_FILE
sqlplus -s $DB_USER/$DB_PASS@$PDB_NAME <<EOF >> $OUTPUT_FILE
SET LINESIZE 200
SET PAGESIZE 50
SELECT segment_name, segment_type, ROUND((bytes/1024/1024),2) AS size_mb
FROM dba_segments
WHERE segment_type IN ('TABLE', 'INDEX')
ORDER BY size_mb DESC;
EOF
echo "" >> $OUTPUT_FILE

# 12. 用户活动和权限管理
echo "12. User Activity and Permissions for PDB $PDB_NAME:" >> $OUTPUT_FILE
sqlplus -s $DB_USER/$DB_PASS@$PDB_NAME <<EOF >> $OUTPUT_FILE
SET LINESIZE 200
SET PAGESIZE 50
SELECT username, account_status, profile FROM dba_users;
EOF
echo "" >> $OUTPUT_FILE

# 13. 安全审计
echo "13. Security Audit for PDB $PDB_NAME:" >> $OUTPUT_FILE
sqlplus -s $DB_USER/$DB_PASS@$PDB_NAME <<EOF >> $OUTPUT_FILE
SET LINESIZE 200
SET PAGESIZE 50
SELECT * FROM dba_audit_trail;
EOF
echo "" >> $OUTPUT_FILE

# 14. 网络和连接
echo "14. Network and Connections for PDB $PDB_NAME:" >> $OUTPUT_FILE
sqlplus -s $DB_USER/$DB_PASS@$PDB_NAME <<EOF >> $OUTPUT_FILE
SET LINESIZE 200
SET PAGESIZE 50
SELECT * FROM v\$session;
EOF
echo "" >> $OUTPUT_FILE

# 结束记录
echo "----------------------------------------" >> $OUTPUT_FILE
echo "Monitoring completed at $(date)" >> $OUTPUT_FILE

echo "Monitoring report saved to $OUTPUT_FILE"