oceanbase 备份恢复
#######sampe 1 恢复一台集群的数据到这个新集群
Step7. 恢复一台集群的数据到这个新集群
备份忽略，这里只考虑恢复
组合查询套餐 sys 租户4.0
select * from oceanbase.CDB_OB_BACKUP_DELETE_POLICY;
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;
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;
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;
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;
Ob3.2 系统sys租户：
select * from oceanbase.__all_backup_task_history;
select gmt_create,tenant_id,backup_dest,file_status from __all_backup_set_files a where file_status='AVAILABLE';
SELECT
a.tenant_id,
t.tenant_name,
COUNT(*) AS available_backup_count,
DATE_FORMAT(FROM_UNIXTIME(MIN(a.gmt_create)/1000000), '%Y-%m-%d') AS earliest_backup,
DATE_FORMAT(FROM_UNIXTIME(MAX(a.gmt_create)/1000000), '%Y-%m-%d') AS latest_backup
FROM
oceanbase.__all_backup_set_files a
JOIN
oceanbase.__all_tenant t ON a.tenant_id = t.tenant_id
WHERE
a.file_status = 'AVAILABLE'
GROUP BY
a.tenant_id, t.tenant_name
ORDER BY
available_backup_count DESC;
SELECT
svr_ip,
svr_port,
zone,
inner_port,
with_rootserver,
status,
start_service_time,
stop_time,
build_version
FROM
oceanbase.__all_server
ORDER BY
zone, svr_ip;
SELECT
t.tenant_id,
t.tenant_name,
t.primary_zone,
s.zone,
s.svr_ip,
s.status AS server_status
FROM
oceanbase.__all_tenant t
JOIN
oceanbase.__all_resource_pool p ON t.tenant_id = p.tenant_id
JOIN
oceanbase.__all_unit u ON p.resource_pool_id = u.resource_pool_id
JOIN
oceanbase.__all_server s ON u.svr_ip = s.svr_ip
WHERE
FIND_IN_SET(s.zone, REPLACE(t.primary_zone, ';', ',')) > 0
ORDER BY
t.tenant_id, s.zone, s.svr_ip;
SELECT
t.tenant_id,
t.tenant_name,
t.primary_zone,
p.resource_pool_id,
p.name AS pool_name,
u.unit_config_id,
u.name AS unit_config_name,
u.max_cpu,
u.min_cpu,
ROUND(u.max_memory/1024/1024/1024, 2) AS max_memory_gb,
ROUND(u.min_memory/1024/1024/1024, 2) AS min_memory_gb,
u.max_iops,
u.min_iops
FROM
oceanbase.__all_tenant t
JOIN
oceanbase.__all_resource_pool p ON t.tenant_id = p.tenant_id
JOIN
oceanbase.__all_unit_config u ON p.unit_config_id = u.unit_config_id
ORDER BY
t.tenant_id;
恢复aaa_st2 到 aaa_st3
恢复路径：/data/obbackup_obclu_test_naaa2
解析主机： 10.241.28.194
恢复时间点：今天 14:00
目标集群：obclu_test_naaa2
目标租户：源租户名的aaa_st2 改成 aaa_st3
副本设置：Unit规格aaa_st2（config_aaa_st2_zone1_CRBC_aaa_24c48g_kzd）
/aaa_pb（unit_aaa_16c48g）
/aaa_ac（unit_aaa_12c36g）
zone优先级：打散，zone1,zone2;zone3全选后一次性选到右边
目的：
这是在恢复环境做的，步骤一样，换个环境实施。
恢复aaa_pr/aaa_pb/aaa_ac
恢复路径：/data/obbackup_obclu_test_naaa_perf
解析主机：10.225.224.34
恢复时间点：今天 14:00
目标集群：obclu_test_naaa_hundun
目标租户：源租户名的pit改成hundun
副本设置：Unit规格aaa_pr（unit_aaa_4c12g）
/aaa_pb（unit_aaa_16c48g）
/aaa_ac（unit_aaa_12c36g）
zone优先级：打散，zone1~zone4全选后一次性选到右边
1.1. 新建恢复任务
进入“恢复”页面，点击“发起恢复”按钮
1.2. 解析备份文件
“存储目录”填写：/obbackup/
“解析主机”选择：25.10.0.2
点击“解析”按钮
出现“存储地址解析成功”提示为正常返回
1.3. 选择需要恢复的租户及恢复时间点，以及目标集群和目标租户名称
源集群、源租户及恢复时间依恢复需求选择
新建恢复租户
恢复aaa_pr/aaa_pb/aaa_ac
恢复路径：/data/obbackup_obclu_test_naaa_perf
解析主机：10.225.224.34
恢复时间点：今天 14:00
（源集群 存在无法搜寻到的情况，多刷几遍页面，并且解析几次）
（每个页面要填完整，不然可能都要重新填一次）
目标集群：obclu_test_naaa_hundun (只能支持单集群恢复，如果有主备关系，建议先删除被集群，恢复后再重建备集群)
目标租户：源租户名的pit改成hundun aaa_pr_hundun aaa_pb_hundun aaa_ac_hundun，(此目标租户，应该为不存在的空租户，如果覆盖，需要先将租户删除)
副本设置：Unit规格aaa_pr（unit_aaa_4c12g）
/aaa_pb（unit_aaa_16c48g）
/aaa_ac（unit_aaa_12c36g）
zone优先级：打散，zone1~zone4全选后一次性选到右边
设置zone优先级，选择所有zone，并将其放置到右侧优先级栏中
1.4. 发起恢复任务
点击“发起恢复”开始恢复任务
1.5. 确认恢复状态
回到“恢复页面”，当任务状态变为“已完成”时，恢复成功完成
租户管理里查看租户是否已经创建成功
有一种可能，新建的租户需要手工添加凭据。恢复出来的租户，root密码和源租户root一致，
查看sys 租户的用户是否创建成功
查看租户下的用户是否创建成功
查看租户下的数据库是否创建成功
浙公网安备 33010602011771号