1
# 步骤1: 筛选符合条件的租户（修改超1个月，数据量<1G）
2
log "步骤1: 筛选待处理租户"
3
TENANTS_TO_PROCESS=$(execute_sql "$DB_CONNECTION" "
4
    SELECT T2.TENANT_NAME,
5
           SUM(T1.MAX_CPU) AS CPU_CORES,
6
           ROUND(SUM(T4.MEMORY_SIZE)/1024/1024/1024) AS MEMORY_GB,
7
           ROUND(SUM(T1.LOG_DISK_IN_USE)/1024/1024/1024,2) AS LOG_USAGE_GB,
8
           ROUND(SUM(T1.DATA_DISK_IN_USE)/1024/1024/1024,2)AS DATA_USAGE_GB,
9
           T4.NAME
10
    FROM OCEANBASE.GV\$OB_UNITS T1
11
    JOIN OCEANBASE.DBA_OB_TENANTS T2
12
      ON T1.TENANT_ID=T2.TENANT_ID
13
    JOIN OCEANBASE.DBA_OB_RESOURCE_POOLS T3
14
      ON T2.TENANT_ID=T3.TENANT_ID
15
    JOIN OCEANBASE.DBA_OB_UNIT_CONFIGS T4
16
      ON T3.UNIT_CONFIG_ID=T4.UNIT_CONFIG_ID
17
    WHERE 1=1
18
      AND T2.TENANT_TYPE = 'USER'
19
      AND LOCKED='NO'
20
      AND T2.MODIFY_TIME <= DATE_SUB(NOW(), INTERVAL 2 MONTH)
21
      AND TENANT_NAME NOT IN (SELECT TENANT_NAME
22
                                FROM OCEANBASE.GV\$OB_SQL_AUDIT
23
                                WHERE TENANT_NAME NOT IN ('SYS',' ')
24
                                  AND DB_NAME  NOT IN ('information_schema','mysql','oceanbase','test','SYS','LBACSYS','ORAAUDITOR')
25
                               -- AND USER_NAME NOT IN ('root','SYS') #有些人喜欢用系统用户连业务数据库，不能当过滤条件
26
                               -- AND SUBSTR(USEC_TO_TIME(REQUEST_TIME),1,10) > NOW()-INTERVAL 30 DAY #意义不大，还慢
27
                                GROUP BY TENANT_NAME)
28
            --  AND TENANT_NAME NOT IN ('CS_MGAS2','sit_ebank2','CS_BOVIS','amcp_sit','amcp_uat')
29
    GROUP BY T2.TENANT_NAME,T4.NAME
30
    HAVING SUM(T1.LOG_DISK_IN_USE)/1024/1024/1024 < 52
31
       AND SUM(T1.DATA_DISK_IN_USE)/1024/1024/1024 < 1
32
    ORDER BY 2,1;
33
")
34

35
if [ -z "$TENANTS_TO_PROCESS" ]; then
36
    log "当前集群无符合处理条件的租户，跳过锁定和减配步骤"
37
else
38
    # 记录备份信息
39
    echo "$TENANTS_TO_PROCESS" | while IFS=$'\t' read -r tenant_name cpu memory log_use data_use conf_name; do
40
        echo "$(date '+%Y-%m-%d')|$CLUSTER_NAME|$tenant_name|${cpu}C|${memory}G|${log_use}G|${data_use}G" >> "$BACKUP_FILE"
41
    done
42

43
    # 步骤2: 锁定租户并减配至1c1g
44
    log "步骤2: 锁定并减配租户"
45
    echo "$TENANTS_TO_PROCESS" | while IFS=$'\t' read -r tenant_name cpu memory log_use data_use conf_name; do
46
        log "正在处理租户: $tenant_name"
47

48
        # 锁定租户
49
        execute_sql "$DB_CONNECTION" "ALTER TENANT $tenant_name LOCK;" >> "$LOG_FILE" 2>&1
50
        if [ $? -eq 0 ]; then
51
            log "成功锁定租户: $tenant_name"
52
        else
53
            log "错误: 锁定租户 $tenant_name 失败"
54
            continue
55
        fi
56

57
        # 减配资源（这里需要根据实际资源池配置调整SQL）
58
        execute_sql "$DB_CONNECTION" "ALTER RESOURCE UNIT $conf_name MAX_CPU=1, MIN_CPU=1, MEMORY_SIZE='5G';" >> "$LOG_FILE" 2>&1
59
        if [ $? -eq 0 ]; then
60
            log "成功减配$tenant_name的UNIT: $conf_name"
61
        else
62
            log "警告: 减配$tenant_name的UNIT $conf_name 失败"
63
        fi
64
    done
65
fi
66

67
# 步骤3: 筛选锁定超1个月的租户（始终执行）
68
log "步骤3: 筛选锁定超1个月的租户"
69
TENANTS_TO_DELETE=$(execute_sql "$DB_CONNECTION" "
70
    SELECT TENANT_NAME
71
    FROM OCEANBASE.DBA_OB_TENANTS
72
    WHERE TENANT_TYPE='USER'
73
    AND LOCKED='YES'
74
    AND MODIFY_TIME <= DATE_SUB(NOW(), INTERVAL 1 MONTH);
75
")
76

77
# 步骤4: 生成删除语句（不实际执行）
78
if [ -n "$TENANTS_TO_DELETE" ]; then
79
    echo "-- ===========================================" >> "pending_deletions_$(date +%Y%m%d).sql"
80
    echo "-- 连接信息: $(echo "$DB_CONNECTION")" >> "pending_deletions_$(date +%Y%m%d).sql"
81
    echo "-- 生成时间: $(date '+%Y-%m-%d %H:%M:%S')" >> "pending_deletions_$(date +%Y%m%d).sql"
82
    log "步骤4: 为以下租户生成删除语句（请手动确认后执行）"
83
    echo "$TENANTS_TO_DELETE" | while read -r tenant_name; do
84
        # 使用PURGE或FORCE选项确保直接删除
85
        echo "DROP TENANT $tenant_name FORCE;" >> "pending_deletions_$(date +%Y%m%d).sql"
86
        log "生成删除语句: DROP TENANT $tenant_name FORCE;"
87
    done
88
    echo "-- ===========================================" >> "pending_deletions_$(date +%Y%m%d).sql"
89
    log "删除语句已保存至 pending_deletions_$(date +%Y%m%d).sql，请手动审核后执行"
90
else
91
    log "当前集群无符合删除条件的租户（锁定超过1个月）"
92
fi