【 使用环境 】生产环境 or 测试环境 OB
【 OB or 其他组件 】 OB 3.2
【 使用版本 】 OB3.2
【问题描述】清晰明确描述问题
【复现路径】问题出现前后相关操作
【附件及日志】推荐使用OceanBase敏捷诊断工具obdiag收集诊断信息,详情参见链接（右键跳转查看）：
【备注】基于 LLM 和开源文档 RAG 的论坛小助手已开放测试，在发帖时输入 [@论坛小助手] 即可召唤小助手，欢迎试用！
以下SQL 可以在OB4.0 实现，但是在OB3.2 无法实现，请教各位在OB 3.2 实现这些SQL ，如果可以的话？
-- 查看备份删除策略
SELECT
t.TENANT_NAME,
p.TENANT_ID,
p.POLICY_NAME,
p.RECOVERY_WINDOW
FROM
oceanbase.CDB_OB_BACKUP_DELETE_POLICY p
JOIN
oceanbase.DBA_OB_TENANTS t
ON
p.TENANT_ID = t.TENANT_ID;
– 备份策略查询（OB 3.2）
select
b.tenant_name,
a.tenant_id,
DATE_FORMAT(start_timestamp, ‘%Y-%m-%d’) as ‘备份日期’,
CASE DATE_FORMAT(start_timestamp, ‘%w’)
WHEN 1 THEN ‘星期一’
WHEN 2 THEN ‘星期二’
WHEN 3 THEN ‘星期三’
WHEN 4 THEN ‘星期四’
WHEN 5 THEN ‘星期五’
WHEN 6 THEN ‘星期六’
WHEN 0 THEN ‘星期日’
END
as ‘星期几’,
case
when backup_type = ‘FULL’ then ‘全备’
when backup_type = ‘INC’ then ‘增量备份’
else null
end
as ‘备份类型’,
CASE
WHEN a.status = ‘SUCCESS’ THEN ‘成功’
WHEN a.status = ‘FAILED’ THEN ‘失败’
ELSE NULL
END
as ‘备份状态’,
case
when file_status = ‘DELETED’ THEN ‘备份文件已删除’
WHEN file_status = ‘AVAILABLE’ THEN ‘有效备份’
WHEN file_status = ‘INCOMPLETE’ THEN ‘备份文件缺失’
WHEN file_status = ‘EXPIRED’ THEN ‘备份文件失效’
WHEN file_status = ‘COPYING’ THEN ‘文件备份中’
WHEN file_status = ‘DELETING’ THEN ‘备份文件删除中’
WHEN file_status = ‘BROKEN’ THEN ‘备份文件不完整’
ELSE NULL
END
as ‘备份文件状态’,
SEC_TO_TIME(elapsed_secondes) AS ‘备份总时长’,
result,
round(output_bytes / 1024 / 1024 / 1024, 2) as ‘备份文件大小(G)’,
PATH
from
CDB_OB_BACKUP_SET_FILES a
LEFT JOIN dba_ob_tenants b ON a.tenant_id = b.tenant_id
order by
start_timestamp desc;
–primary zone
select tenant_id,tenant_name,PRIMARY_ZONE from oceanbase.dba_OB_tenants;
– ```
– 查看资源池和单元配置信息
select
t4.tenant_id,
t4.tenant_name,
t1.name resource_pool_name,
t2.`name` unit_config_name,
t2.max_cpu,
t2.min_cpu,
round(t2.memory_size / 1024 / 1024 / 1024, 2) mem_size_gb,
round(t2.log_disk_size / 1024 / 1024 / 1024, 2) log_disk_size_gb,
#t2.max_iops,
#t2.min_iops,
t3.unit_id,
t3.zone,
concat(t3.svr_ip, ':', t3.`svr_port`) observer
from
oceanbase.__all_resource_pool t1
join oceanbase.__all_unit_config t2 on (t1.unit_config_id = t2.unit_config_id)
join oceanbase.__all_unit t3 on (t1.`resource_pool_id` = t3.`resource_pool_id`)
left join oceanbase.__all_tenant t4 on (t1.tenant_id = t4.tenant_id)
order by
t1.`resource_pool_id`,
t2.`unit_config_id`,
t3.unit_id;
--备份恢复时间
select TENANT_ID,max(MIN_RESTORE_SCN),max(SCN_TO_TIMESTAMP(MIN_RESTORE_SCN)) from oceanbase.CDB_OB_BACKUP_SET_FILES GROUP BY TENANT_ID;
--归档日志恢复时间
select STATUS,START_SCN,START_SCN_DISPLAY,CHECKPOINT_SCN,CHECKPOINT_SCN_DISPLAY,path from oceanbase.CDB_OB_ARCHIVELOG;
-- 租户资源池分布查询（OB 3.2）
SELECT
t1.tenant_name,
rp.RESOURCE_POOL_ID,
COUNT(u.unit_id) AS unit_count,
rp.unit_config_id,
GROUP_CONCAT(DISTINCT CONCAT(u.svr_ip, ":", u.svr_port)) AS svr_ips
FROM
oceanbase.DBA_OB_TENANTS t1
JOIN oceanbase.DBA_OB_RESOURCE_POOLS rp ON t1.tenant_id = rp.tenant_id
LEFT JOIN oceanbase.DBA_OB_UNITS u ON rp.RESOURCE_POOL_ID = u.RESOURCE_POOL_ID
group by
t1.tenant_name,
rp.RESOURCE_POOL_ID,
rp.unit_config_id
order by t1.tenant_name;