# 实施方案

1. 创建清理存储过程
$ obclient -e ”
CREATE OR REPLACE PROCEDURE fgedudb.sp_cleanup_old_data
AS
BEGIN
— 清理操作日志
DELETE FROM fgedudb.fgedu_operation_log
WHERE create_time < DATE_SUB(NOW(), INTERVAL 30 DAY); -- 清理登录日志 DELETE FROM fgedudb.fgedu_login_log WHERE login_time < DATE_SUB(NOW(), INTERVAL 90 DAY); -- 清理临时表 FOR rec IN ( SELECT table_name FROM information_schema.tables WHERE table_schema = 'fgedudb' AND table_name LIKE 'tmp_%' AND create_time < DATE_SUB(NOW(), INTERVAL 7 DAY) ) LOOP EXECUTE IMMEDIATE 'DROP TABLE fgedudb.' || rec.table_name; END LOOP; COMMIT; END; " 2. 创建定时作业 $ obclient -e " BEGIN DBMS_SCHEDULER.create_job( job_name => ‘fgedu_daily_cleanup’,
job_type => ‘STORED_PROCEDURE’,
job_action => ‘fgedudb.sp_cleanup_old_data’,
start_date => SYSTIMESTAMP,
repeat_interval => ‘FREQ=DAILY; BYHOUR=2; BYMINUTE=0’,
enabled => TRUE
);
END;
”

3. 验证作业执行
$ obclient -e ”
SELECT
job_name,
last_start_date,
last_run_duration,
run_count,
failure_count
FROM user_scheduler_jobs
WHERE job_name = ‘fgedu_daily_cleanup’;
”

4. 监控清理效果
#!/bin/bash
# cleanup_monitor.sh
# from:www.itpux.com.qq113257174.wx:itpux-com

echo “=== Cleanup Monitor ===”

# 检查日志表行数
obclient -e ”
SELECT
‘operation_log’ as table_name,
COUNT(*) as row_count
FROM fgedudb.fgedu_operation_log
UNION ALL
SELECT
‘login_log’ as table_name,
COUNT(*) as row_count
FROM fgedudb.fgedu_login_log;
”

# 检查磁盘空间
df -h /ob/fgdata

echo “=======================”

# 实施方案

1. 创建收集存储过程
$ obclient -e ”
CREATE OR REPLACE PROCEDURE fgedudb.sp_gather_stats
AS
BEGIN
— 收集所有表统计信息
FOR rec IN (
SELECT table_name
FROM information_schema.tables
WHERE table_schema = ‘fgedudb’
AND table_type = ‘BASE TABLE’
) LOOP
EXECUTE IMMEDIATE ‘ANALYZE TABLE fgedudb.’ || rec.table_name;
END LOOP;

— 收集特定大表统计信息
EXECUTE IMMEDIATE ‘ANALYZE TABLE fgedudb.fgedu_order’;
EXECUTE IMMEDIATE ‘ANALYZE TABLE fgedudb.fgedu_user’;

COMMIT;
END;
”

2. 创建定时作业
$ obclient -e ”
BEGIN
DBMS_SCHEDULER.create_job(
job_name => ‘fgedu_stats_gather’,
job_type => ‘STORED_PROCEDURE’,
job_action => ‘fgedudb.sp_gather_stats’,
start_date => SYSTIMESTAMP,
repeat_interval => ‘FREQ=DAILY; BYHOUR=3; BYMINUTE=0’,
enabled => TRUE
);
END;
”

3. 验证统计信息
$ obclient -e ”
SELECT
table_name,
last_analyzed,
num_rows,
sample_size
FROM information_schema.tables
WHERE table_schema = ‘fgedudb’
ORDER BY last_analyzed DESC
LIMIT 10;
”

4. 性能监控
#!/bin/bash
# stats_monitor.sh
# from:www.itpux.com.qq113257174.wx:itpux-com

echo “=== Stats Gather Monitor ===”

# 检查未分析的表
obclient -e ”
SELECT
table_name,
last_analyzed
FROM information_schema.tables
WHERE table_schema = ‘fgedudb’
AND (last_analyzed IS NULL
OR last_analyzed < DATE_SUB(NOW(), INTERVAL 7 DAY)); " echo "============================"

# 实施方案

1. 创建备份脚本
#!/bin/bash
# db_backup.sh
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: http://www.fgedu.net.cn

BACKUP_DIR=”/backup/ob”
DATE=$(date +%Y%m%d_%H%M%S)
LOG_FILE=”/var/log/ob_backup_${DATE}.log”

mkdir -p $BACKUP_DIR

echo “[$(date)] Starting backup…” > $LOG_FILE

# 执行逻辑备份
obdumper -h192.168.1.101 -P2881 -ufgedu -p \
-D fgedudb \
-f ${BACKUP_DIR}/fgedudb_${DATE} \
>> $LOG_FILE 2>&1

if [ $? -eq 0 ]; then
echo “[$(date)] Backup completed successfully” >> $LOG_FILE

# 压缩备份
cd $BACKUP_DIR
tar -czf fgedudb_${DATE}.tar.gz fgedudb_${DATE}
rm -rf fgedudb_${DATE}

# 清理7天前的备份
find $BACKUP_DIR -name “fgedudb_*.tar.gz” -mtime +7 -delete
else
echo “[$(date)] Backup failed” >> $LOG_FILE
# 发送告警
mail -s “[ALERT] Backup Failed” dba@fgedu.net.cn < $LOG_FILE fi 2. 配置Cron $ crontab -e # 每日2点执行备份 0 2 * * * /ob/scripts/db_backup.sh 3. 备份验证脚本 #!/bin/bash # backup_verify.sh # from:www.itpux.com.qq113257174.wx:itpux-com BACKUP_DIR="/backup/ob" LATEST_BACKUP=$(ls -t $BACKUP_DIR/*.tar.gz | head -1) echo "Verifying backup: $LATEST_BACKUP" # 解压验证 tar -tzf $LATEST_BACKUP > /dev/null

if [ $? -eq 0 ]; then
echo “Backup verification PASSED”
else
echo “Backup verification FAILED”
mail -s “[ALERT] Backup Verification Failed” dba@fgedu.net.cn
fi

4. 备份监控
#!/bin/bash
# backup_monitor.sh
# from:www.itpux.com.qq113257174.wx:itpux-com

BACKUP_DIR=”/backup/ob”

# 检查最新备份时间
LATEST_BACKUP=$(ls -t $BACKUP_DIR/*.tar.gz 2>/dev/null | head -1)

if [ -z “$LATEST_BACKUP” ]; then
echo “No backup found”
exit 1
fi

BACKUP_TIME=$(stat -c %Y $LATEST_BACKUP)
CURRENT_TIME=$(date +%s)
DIFF_HOURS=$(( (CURRENT_TIME – BACKUP_TIME) / 3600 ))

if [ $DIFF_HOURS -gt 25 ]; then
echo “Last backup is $DIFF_HOURS hours old”
mail -s “[ALERT] Backup Overdue” dba@fgedu.net.cn
fi